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

Cheerless

You may want to consider composing your SQL outside of PostgreSQL, e.g. contra.com/p/AqZWWoUB-writing-composable-sql-using-java-script. – Pupil

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.

Joiner

Just to complement, a DO block always returns void and accepts no parameters so I think the OP is restricted to a function. – Howenstein

@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. – Joiner

@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 question if you want. Thank you – Chrysalis

"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://mcmap.net/q/28814/-how-do-you-find-the-row-count-for-all-your-tables-in-postgres – Liberalism

Is there any extension for postgres to generate dynamic sql given parameters programmatically? – Trot

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)

Changeling

This is simple and real good for day-by-day... Show how to use the dynamic query as an usual query! – Sheepshearing

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; – String

Answer 3

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/

Prosthodontist

Answer 4

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.

Christalchristalle

This is not a python related question. Hence, this answer should not be here – Salientian

Answer 5

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.

Symbolic

The text of the dynamic query, sure, but it won't execute the query. See prior linked post. – Joiner

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. – Symbolic

No worries. It just doesn't answer the question, which is how to execute dynamic SQL. – Joiner

then the the above statement should be in pl/pqsql block, which i mentioned in first line itself. – Symbolic

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. – Terrellterrena