Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

dynamic sql query in postgres

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

like image 925
psteelk Avatar asked Oct 08 '12 10:10

psteelk


People also ask

How do I run a SQL query in 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 .

Does PostgreSQL support dynamic SQL?

You cannot use dynamic SQL directly in PostgreSQL's SQL dialect.

How do I run a dynamic SQL in PostgreSQL?

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; ...


2 Answers

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.

like image 132
Craig Ringer Avatar answered Oct 11 '22 23:10

Craig Ringer


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) 
like image 35
Inshua Avatar answered Oct 11 '22 21:10

Inshua