41

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

psteelk
  • 1,305
  • 3
  • 16
  • 24
  • You may want to consider composing your SQL outside of PostgreSQL, e.g. https://contra.com/p/AqZWWoUB-writing-composable-sql-using-java-script. – Gajus Sep 30 '22 at 17:16

5 Answers5

53

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:

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.

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 3
    Just to complement, a `DO` block always returns void and accepts no parameters so I think the OP is restricted to a function. – Clodoaldo Neto Oct 08 '12 at 11:31
  • 2
    @Clodoaldo Good point - they can execute the `SELECT` but it won't do them any good unless they were to do something really roundabout like `SELECT ... INTO` a temp table. – Craig Ringer Oct 08 '12 at 11:32
  • 1
    @CraigRinger Hi there, I know I am a little late to the party, but can you suggest any good tutorial for dynamic sql in postgreSQL ? I cannot find any. I want to create an all-in-one dynamic query. Check [this](https://stackoverflow.com/questions/48216935/pl-pgsql-for-all-in-one-dynamic-query) question if you want. Thank you – slevin Jan 11 '18 at 22:41
  • 1
    "You cannot use dynamic SQL directly in PostgreSQL's SQL dialect." Well, as a matter of fact you can, at least using query_to_xml. See https://stackoverflow.com/a/38684225/3935325 – villasv Apr 10 '19 at 14:31
  • Is there any extension for postgres to generate dynamic sql given parameters programmatically? – Behnam Esmaili May 14 '20 at 08:46
26
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)
Inshua
  • 1,355
  • 1
  • 12
  • 12
  • 4
    This is simple and real good for day-by-day... Show how to use the dynamic query as an usual query! – Peter Krauss Nov 29 '18 at 20:11
  • Wonderful! safely read only run a dynamic SQL. . select * from run_select(‘update a set i = 5;’) as t(account_id int); ERROR: cannot open UPDATE query as cursor; – Charlie 木匠 Jul 22 '21 at 22:47
6

Try using 

RETURN QUERY EXECUTE '<SQL Command>'

This will return data into form of table. You have to use this into stored function of PostgreSQL.

I have already created on full demonstration on custom filter and custom sorting using dynamic query of PostgreSQL. Please visit this url: http://www.dbrnd.com/2015/05/postgresql-dynamic-sql/

Anvesh
  • 7,103
  • 3
  • 45
  • 43
1

These all look more complicated than the OP's question. A different formatting should do the trick.. but it could absolutely the case that I don't understand.

From how I read OP's question, I think others in a similar situation may benefit from how I got it.

I am using Postgre on Redshift, and I ran into this issue and found a solution.

I was trying to create a dynamic query, putting in my own date.

date = dt.date(2018, 10, 30)

query = ''' select * from table where date >= ''' + str(my_date) + ''' order by date '''

But, the query entirely ignores the condition when typing it this way.

However, if you use the percent sign (%), you can insert the date correctly.

One correct way to write the above statement is:

query = ''' select * from table where date >= ''' + ''' '%s' ''' % my_date + ''' order by date '''

So, maybe this is helpful, or maybe it is not. I hope it helps at least one person in my situation!

Best wishes.

spen.smith
  • 576
  • 2
  • 16
-1

EXECUTE will work only on pl/pqsql environment.

instead of EXECUTE try with SELECT 

 SELECT format('SELECT * from result_%s_table', quote_ident((select id from ids where condition = some_condition))

output would be the dynamic query.

solaimuruganv
  • 27,177
  • 1
  • 18
  • 23
  • 3
    The text of the dynamic query, sure, but it won't *execute* the query. See prior linked post. – Craig Ringer Oct 08 '12 at 11:07
  • yes i've gone through ur detailed dynamic qry execution, here just what i mentioned is EXECUTE will work only in pl/pqsql environment, and when i posted my answer i really didn't noticed ur response. – solaimuruganv Oct 08 '12 at 11:10
  • No worries. It just doesn't answer the question, which is how to *execute* dynamic SQL. – Craig Ringer Oct 08 '12 at 11:17
  • then the the above statement should be in pl/pqsql block, which i mentioned in first line itself. – solaimuruganv Oct 08 '12 at 11:19
  • 2
    I considered downvoting, because as said, this doesn't *execute* the block. BUT it's not hard to take the result of the query and feed it again as another query call, the dynamic content is trusted. So this solves the problem and avoids injection in theory. – John White Apr 27 '19 at 11:57