current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 5 down vote favorite

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

share|improve this question

3 Answers 3

up vote 7 down vote accepted

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.

share|improve this answer
    
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 8 '12 at 11:31
    
@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 8 '12 at 11:32
up vote 0 down vote

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.

share|improve this answer
    
The text of the dynamic query, sure, but it won't execute the query. See prior linked post. – Craig Ringer Oct 8 '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 8 '12 at 11:10
    
No worries. It just doesn't answer the question, which is how to execute dynamic SQL. – Craig Ringer Oct 8 '12 at 11:17
    
then the the above statement should be in pl/pqsql block, which i mentioned in first line itself. – solaimuruganv Oct 8 '12 at 11:19
up vote 0 down vote

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/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.