Execute SQL returned as a select query result string in PostgreSQL

Question

I've got a function that I run using SELECT functionName. It returns me a single row with a single text cell containing another SQL query that I'd like to execute right away. Is there any way to do this in PostgreSQL?

Answer 1

You'll have to use PL/pgSQL to execute dynamic SQL

In case that the returned cursor of the queries has a fixed structure (i.e. same column names and data types) you may define a function retruning a TABLE

CREATE OR REPLACE FUNCTION exec_query(text)
RETURNS table ( id int)
LANGUAGE 'plpgsql'
AS $$
BEGIN 
    RETURN QUERY  EXECUTE $1 ; 
END 
$$;

The usage is as follows

select * from exec_query('select id from generate_series(1,3) t(id)')

id|
--+
 1|
 2|
 3|

In case the dynamic queries var yin teh returned structure, define a function returning SETOF RECORD

CREATE OR REPLACE FUNCTION exec_query(text)
RETURNS SETOF RECORD
LANGUAGE 'plpgsql'
AS $$
BEGIN 
    RETURN QUERY  EXECUTE $1 ; 
END 
$$;

But you will have to add the column definition list to the call

select * from exec_query('select id from generate_series(1,3) t(id)') as t(id int);

Otherwise a error is raised ERROR: a column definition list is required for functions returning "record"

Similar question