英文:
Execute SQL returned as a select query result string in PostgreSQL
问题
我有一个使用 SELECT functionName
运行的函数。它会返回一个包含另一个我想立即执行的SQL查询的单行,其中包含一个文本单元。在PostgreSQL中是否有执行这个操作的方法?
英文:
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?
答案1
得分: 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 returning 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 an error is raised ERROR: a column definition list is required for functions returning "record"
Similar question
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论