title: Postgres creating a local temp table (on commit drop) from a dynamic sql string
body: <p>I have a query string generated in a postgres UDF, i'd like to put it's result in a temp table to perform joins against (I'm using <code>LIMIT</code> and <code>OFFSET</code>, and I don't want to join against other ttables only to end up choping the data off at the end --i.e., the LIMIT operator in the query plan). I attempt to create the temp table with the following statement.</p>
  
  <pre><code>CREATE LOCAL TEMP TABLE query_result ON COMMIT DROP AS EXECUTE query_string_;
  </code></pre>
  
  <p>But I get the following error notice :</p>
  
  <pre><code>********** Error **********
  
  ERROR: prepared statement "query_string_" does not exist
  SQL state: 26000
  Context: SQL statement "CREATE LOCAL TEMP TABLE query_result ON COMMIT DROP AS EXECUTE query_string_"
  PL/pgSQL function "search_posts_unjoined" line 48 at SQL statement
  </code></pre>
  
  <p>Additionally, I tried preparing the statemen, but I couldn't get the syntax right either.</p>
  
  <p>The UDF in question is : </p>
  
  <pre><code>CREATE OR REPLACE FUNCTION search_posts_unjoined(
      forum_id_ INTEGER,
      query_    CHARACTER VARYING,
      offset_ INTEGER DEFAULT NULL,
      limit_ INTEGER DEFAULT NULL,
      from_date_ TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
      to_date_ TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
      in_categories_ INTEGER[] DEFAULT '{}'
      )
  RETURNS SETOF forum_posts AS $$
  DECLARE
      join_string CHARACTER VARYING := ' ';
      from_where_date CHARACTER VARYING := ' ';
      to_where_date CHARACTER VARYING := ' ';
      query_string_ CHARACTER VARYING := ' ';
      offset_str_ CHARACTER VARYING := ' ';
      limit_str_ CHARACTER VARYING := ' ';
  BEGIN
      IF NOT from_date_ IS NULL THEN
          from_where_date := ' AND fp.posted_at &gt; ''' || from_date_ || '''';
      END IF;
  
      IF NOT to_date_ IS NULL THEN
          to_where_date := ' AND fp.posted_at &lt; ''' || to_date_ || '''';
      END IF;
  
      IF NOT offset_ IS NULL THEN
          offset_str_ := ' OFFSET ' || offset_; 
      END IF;
  
      IF NOT limit_ IS NULL THEN
          limit_str_ := ' LIMIT ' || limit_;
      END IF;
  
      IF NOT limit_ IS NULL THEN
      END IF;
  
      CREATE LOCAL TEMP TABLE un_cat(id) ON COMMIT DROP AS (select * from unnest(in_categories_)) ;
  
      if in_categories_ != '{}' THEN
          join_string := ' INNER JOIN un_cat uc ON uc.id = fp.category_id ' ;
      END IF;
  
      query_string_ := '
      SELECT fp.*
      FROM forum_posts fp' ||
          join_string
      ||
      'WHERE fp.forum_id = ' || forum_id_ || ' AND
      to_tsvector(''english'',fp.post_text) @@ to_tsquery(''english'','''|| query_||''')' || 
          from_where_date || 
          to_where_date ||
          offset_str_ ||
          limit_str_ 
      ||  ';';
  
      CREATE LOCAL TEMP TABLE query_result ON COMMIT DROP AS EXECUTE query_string_;
  
      RAISE NOTICE '%', query_string_;
  
      RETURN QUERY
      EXECUTE query_string_;
  END;
  $$ LANGUAGE plpgsql;
  </code></pre>
  
  <p>And it works when the statement in question is removed.</p>