dynamic sql query in postgres

Question

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

Answer 1

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:

• PL/PgSQL's EXECUTE ... USING; to
• SQL's EXECUTE

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.

Answer 2

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)