从 PostgreSQL 函数返回 SETOF 行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17864911/
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 SETOF rows from PostgreSQL function
提问by user433023
I have a situation where I want to return the join between two views. and that's a lot of columns. It was pretty easy in sql server. But in PostgreSQL when I do the join. I get the error "a column definition list is required".
我有一种情况，我想返回两个视图之间的连接。这是很多列。在 sql server 中这很容易。但是在 PostgreSQL 中，当我进行连接时。我收到错误“需要列定义列表”。
Is there any way I can bypass this, I don't want to provide the definitions of returning columns.
有什么办法可以绕过这个，我不想提供返回列的定义。
CREATE OR REPLACE FUNCTION functionA(username character varying DEFAULT ''::character varying, databaseobject character varying DEFAULT ''::character varying)
RETURNS SETOF ???? AS
$BODY$
Declare
SqlString varchar(4000) = '';
BEGIN
IF(UserName = '*') THEN
Begin
SqlString := 'select * from view1 left join ' + databaseobject + ' as view2 on view1.id = view2.id';
End;
ELSE
Begin
SqlString := 'select * from view3 left join ' + databaseobject + ' as view2 on view3.id = view2.id';
End;
END IF;
execute (SqlString );
END;
$BODY$
回答by Erwin Brandstetter
Sanitize function
消毒功能
The manualhas all the basics for PL/pgSQL. Basically, what you have can be simplified / sanitized to:
该手册包含 PL/pgSQL 的所有基础知识。基本上，您所拥有的可以简化/清理为：
CREATE OR REPLACE FUNCTION func_a(username text = '', databaseobject text = '')
RETURNS ???? AS
$func$
BEGIN
RETURN QUERY EXECUTE
format ('SELECT * FROM %s v1 LEFT JOIN %I v2 USING (id)'
, CASE WHEN username = '*' THEN 'view1' ELSE 'view3' END, databaseobject);
END
$func$ LANGUAGE plpgsql;
You don't need additional instances of
BEGIN .. ENDin the function bodyexcept to start a separate code block with its own scope, which is rarely needed.The standard SQL concatenation operator is
||.+is a "creative" addition of your former vendor.Don't use CaMeL-case identifiersunless you double-quote them. Best you don't use them at all:
varchar(4000)is also tailored to a specific limitation of SQL Server. This data type has no performance benefit whatsoever in Postgres. Only use it if you actually need a limit of 4000 characters. I would just usetext- except that we don't need any variables at allhere, after I simplified the function.If you have not used
format(), yet, consult the manual here.
您不需要
BEGIN .. END在函数体中添加额外的实例，除非用自己的作用域开始一个单独的代码块，这很少需要。标准的 SQL 连接运算符是
||.+是您以前的供应商的“创造性”补充。不要使用CaMeL-case 标识符，除非你用双引号引用它们。最好你根本不使用它们：
varchar(4000)还针对 SQL Server 的特定限制进行了定制。这种数据类型在 Postgres 中没有任何性能优势。仅当您确实需要 4000 个字符的限制时才使用它。我只想用text-除了我们不需要任何变量都在这里，在我简化了功能。如果您还没有使用过
format()，请在此处查阅手册。
Return type
返回类型
Now, for your actual question: The return type for a dynamic query is a bit tricky, since SQL requires the function to return a well defined type. If you have a table or view or composite type in your database already that matches the column definition list you want to return you can just use that:
现在，对于您的实际问题：动态查询的返回类型有点棘手，因为 SQL 要求该函数返回一个明确定义的类型。如果您的数据库中已经有一个与您想要返回的列定义列表匹配的表或视图或复合类型，则可以使用它：
CREATE FUNCTION foo()
RETURNS SETOF my_view AS
...
If you are making the type up as you go, you can either return anonymous records:
如果您正在制作类型，您可以返回匿名记录：
CREATE FUNCTION foo()
RETURNS SETOF record AS
...
or provide a column definition list with (simplest) RETURNS TABLE:
或提供一个列定义列表（最简单）RETURNS TABLE：
CREATE FUNCTION foo()
RETURNS TABLE (col1 int, col2 text, ...) AS
...
The downside for anonymous records: you then have to provide a column definition list with every call, so I hardly ever use that.
匿名记录的缺点：然后你必须在每次调用时提供一个列定义列表，所以我几乎从不使用它。
I wouldn't use SELECT *to begin with. Use a definitive list of columns to return and declare your return type accordingly:
我不会用SELECT *开始。使用明确的列列表返回并相应地声明您的返回类型：
CREATE OR REPLACE FUNCTION func_a(username text = '', databaseobject text = '')
RETURNS TABLE(col1 int, col2 text, col3 date) AS
$func$
BEGIN
RETURN QUERY EXECUTE
format ('SELECT v1.col1, v1.col2, v2.col3
FROM %s v1 LEFT JOIN %I v2 USING (id)$f$
, CASE WHEN username = '*' THEN 'view1' ELSE 'view3' END, databaseobject);
END
$func$;
For completely dynamic queries, I'd rather use a plain SQL query to begin with. Not a function.
对于完全动态的查询，我宁愿使用普通的 SQL 查询开始。不是函数。
There are more advanced options, but you may need to study the basics first.
有更多高级选项，但您可能需要先学习基础知识。
