How to execute a dynamic query in PostgreSQL?

Question

I am trying to execute the following dynamic sql, but I could not figure out how to do it:

DROP FUNCTION f_mycross(text, text);

EXECUTE ('CREATE OR REPLACE FUNCTION f_mycross(text, text)
   RETURNS TABLE ("registration_id" integer, '
   || (SELECT string_agg(DISTINCT pivot_headers, ',' order by pivot_headers)
       FROM (SELECT DISTINCT '"' || qid::text || '" text' AS pivot_headers
             FROM answers) x)
   || ') AS ''$libdir/tablefunc'',''crosstab_hash'' LANGUAGE C STABLE STRICT;')

I am relatively new to PostgreSQL.

Show us the complete code of the function that runs the dynamic SQL. — , Mar 03 '17 at 06:52
`execute` (dynymic SQL) can't be used outside of a PL/pgSQL function or `DO` block. — , Mar 03 '17 at 09:37
This is the complete code. This code creates a dynamic crosstalk function that pivots the answers table. Answers tables has three columns (survey_id int, question_id int, answer text). Desired output of the function would be (survey_id, answer to question 1, answer to question 2, etc). What is between the () builds the create statement for my dynamic function perefectly...I just want to execute it instead of selecting and then copy/pasting it form results window into the SQL window and then running it to create the function — jlimited, Mar 03 '17 at 09:40
As I said: you need to put that into a `DO` block. You can't do that in "plain SQL" — , Mar 03 '17 at 09:56

score 4 · Answer 1 · answered Aug 20 '17 at 00:51

Like a_horse commented, EXECUTE is not an SQL command. It's a PL/pgSQL command and can only be used in a function body or DO statement using this procedural language. Like:

DROP FUNCTION IF EXISTS f_mycross(text, text);

DO
$do$
BEGIN

EXECUTE (
   SELECT 'CREATE OR REPLACE FUNCTION f_mycross(text, text)
             RETURNS TABLE (registration_id integer, '
       || string_agg(pivot_header || ' text', ', ')
       || $$) AS '$libdir/tablefunc', 'crosstab_hash' LANGUAGE C STABLE STRICT$$
   FROM  (SELECT DISTINCT quote_ident(qid::text) AS pivot_header FROM answers ORDER BY 1) x
   );

END
$do$;  -- LANGUAGE plpgsql is the default

I added some improvements and simplified the nested SELECT query.

Major points

Add IF EXISTS to DROP FUNCTION unless you are certain the function exists or you want to raise an exception if it does not.
DISTINCT in the subquery is enough, no need for another DISTINCT in the outer SELECT.
Use quote_ident() to automatically double-quote identifiers where necessary.
No parentheses required around the string we feed to EXECUTE.
Simpler nested quoting with $-quotes.
- Insert text with single quotes in PostgreSQL
We can apply ORDER BY in the subquery, which is typically much faster than adding ORDER BY in the outer aggregate function.

How to execute a dynamic query in PostgreSQL?

1 Answers1

Major points