Postgres creating a local temp table (on commit drop) from a dynamic sql string
Use instead:
EXECUTE '
CREATE TEMP TABLE query_result ON COMMIT DROP AS '|| query_string_;
EXECUTE
the whole statement.
The syntax formCREATE TABLE foo AS EXECUTE <query>
isn't valid.LOCAL
is just a noise word and ignored in this context.
More details in the manual.
Hassan Syed
Member of Technical Staff 2 @ eBay. Interested in Bazel, K8s, ISTIO, gRPC, Golang, Rust, Kotlin, Java and all orchestrations thereof.
Updated on June 18, 2022Comments
-
Hassan Syed almost 2 years
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
LIMIT
andOFFSET
, 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.CREATE LOCAL TEMP TABLE query_result ON COMMIT DROP AS EXECUTE query_string_;
But I get the following error notice :
********** 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
Additionally, I tried preparing the statemen, but I couldn't get the syntax right either.
The UDF in question is :
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 > ''' || from_date_ || ''''; END IF; IF NOT to_date_ IS NULL THEN to_where_date := ' AND fp.posted_at < ''' || 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;
And it works when the statement in question is removed.
-
Hassan Syed over 11 yearsAhh the joys of "levels of indirection" =D Much appreciated.