I was attempting to use Dynamic SQL to run some queries in postgres.
Example:
EXECUTE format('SELECT * from result_%s_table', quote_ident((select id from ids where condition = some_condition)))
I have to query a table, which is of the form result_%s_table wherein, I need to substitute the correct table name (an id) from an another table.
I get the error ERROR: prepared statement "format" does not exist
Link: string substitution with query result postgresql
To do this in PL/pgSQL, use the PERFORM statement: PERFORM query ; This executes query and discards the result. Write the query the same way you would write an SQL SELECT command, but replace the initial keyword SELECT with PERFORM .
You cannot use dynamic SQL directly in PostgreSQL's SQL dialect.
36.5.3.To execute an SQL statement with a single result row, EXECUTE can be used. To save the result, add an INTO clause. EXEC SQL BEGIN DECLARE SECTION; const char *stmt = "SELECT a, b, c FROM test1 WHERE a > ?"; int v1, v2; VARCHAR v3[50]; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE mystmt FROM :stmt; ...
EXECUTE ... USING
only works in PL/PgSQL - ie within functions or DO
blocks written in the PL/PgSQL language. It does not work in plain SQL; the EXECUTE
in plain SQL is completely different, for executing prepared statements. You cannot use dynamic SQL directly in PostgreSQL's SQL dialect.
Compare:
EXECUTE ... USING
; toEXECUTE
See the 2nd last par in my prior answer.
In addition to not running except in PL/PgSQL your SQL statement is wrong, it won't do what you expect. If (select id from ids where condition = some_condition)
returns say 42
, the statement would fail if id
is an integer. If it's cast to text you'd get:
EXECUTE format('SELECT * from result_%s_table', quote_ident('42')); EXECUTE format('SELECT * from result_%s_table', '"42"'); EXECUTE 'SELECT * from result_"42"_table';
That's invalid. You actually want result_42_table
or "result_42_table"
. You'd have to write something more like:
EXECUTE format('SELECT * from %s', quote_ident('result_'||(select id from ids where condition = some_condition)||'_table'))
... if you must use quote_ident
.
CREATE OR REPLACE FUNCTION public.exec( text) RETURNS SETOF RECORD LANGUAGE 'plpgsql' AS $BODY$ BEGIN RETURN QUERY EXECUTE $1 ; END $BODY$;
usage:
select * from exec('select now()') as t(dt timestamptz)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With