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?
-
Why does the function return SQL code as text in the first place?Bergi– Bergi2023-03-09 13:47:18 +00:00Commented Mar 9, 2023 at 13:47
Add a comment
|
1 Answer
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 vary in the 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
