postgresql 从 plpgsql 函数返回一个选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10723006/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Return a select from a plpgsql function
提问by j.gardner117
I need to return a dynamically generated select statement from a plpgsql function. This is what I have so far:
我需要从 plpgsql 函数返回一个动态生成的 select 语句。这是我到目前为止：
CREATE OR REPLACE FUNCTION qa_scf(cname character varying, tname character varying)
RETURNS text AS
$BODY$
BEGIN
return '* from ' ||tname|| 'where ' ||cname ||' != ''AL''';
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
The caller, ran from a batch file:
调用者从批处理文件中运行：
select qa_scf('state', 'testtable')
This returns the literal text "qa_scf * from testtable where state != 'AL'". I need to run this query from an sql batch file, but I cannot seem to find the right return statement to have this function return a string and then have the sql batch execute it. I'm using Postgres 9.0.
这将返回文字文本“qa_scf * from testtable where state != 'AL'”。我需要从 sql 批处理文件运行此查询，但我似乎找不到正确的 return 语句让此函数返回一个字符串，然后让 sql 批处理执行它。我正在使用 Postgres 9.0。
回答by Eelke
The return type should be SETOF RECORD. Executing and returning the SQL would become RETURN QUERY EXECUTE. Your query is missing SELECT. There was also whitespace missing before the where.
返回类型应该是 SETOF RECORD。执行并返回 SQL 将变为 RETURN QUERY EXECUTE。您的查询缺少 SELECT。在 where 之前也缺少空格。
CREATE OR REPLACE FUNCTION qa_scf(cname character varying, tname character varying)
RETURNS SETOF RECORD AS
$BODY$
BEGIN
RETURN QUERY EXECUTE 'SELECT * from ' ||tname|| ' where ' ||cname ||' != ''AL''';
END;
$BODY$
LANGUAGE plpgsql;
Calling this function will get a little complicated as you will have to specify the columns you expect in the result. Goes like this:
调用此函数会变得有点复杂，因为您必须在结果中指定您期望的列。是这样的：
SELECT *
FROM qa_scf('foo', 'bar') AS t(col1_name col1_type, ...);
