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

回答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.

回答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)

回答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/

回答4:

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.

回答5:

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.