postgresql plpgsql 在表返回函数中出现错误“RETURN NEXT 不能在带有 OUT 参数的函数中包含参数”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14039720/
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
plpgsql error "RETURN NEXT cannot have a parameter in function with OUT parameters" in table-returning function
提问by EM0
I have a plpgsql function in PostgreSQL 9.2 which returns a table. The function runs several SELECTs that return the same columns as the function and then either returns those results or raises an exception, depending on some checks. The only way I can see of doing this is with FOR ... LOOP, but I can't figure out a convenient way of returning the row.
我在 PostgreSQL 9.2 中有一个 plpgsql 函数，它返回一个表。该函数运行多个 SELECT，这些 SELECT 返回与函数相同的列，然后返回这些结果或引发异常，具体取决于某些检查。我可以看到这样做的唯一方法是使用 FOR ... LOOP，但我无法找到返回行的便捷方法。
I want to do something like this:
我想做这样的事情：
CREATE OR REPLACE FUNCTION my_function()
RETURNS TABLE(column1 integer, column2 boolean, ...)
AS $BODY$
DECLARE
result_row RECORD;
BEGIN
FOR result_row IN (SELECT * FROM other_function_returning_same_columns()) LOOP
IF something_wrong_with(result_row) THEN
RAISE EXCEPTION 'Something went wrong';
END IF;
RETURN NEXT result_row;
END LOOP;
END
$BODY$ LANGUAGE plpgsql STABLE;
This gives me an error:
这给了我一个错误：
ERROR: RETURN NEXT cannot have a parameter in function with OUT parameters
错误：RETURN NEXT 不能在带有 OUT 参数的函数中使用参数
I'm not sure why Postgres is complaining here, because my code looks a lot like the example in the documentation, except that my function returns TABLE instead of SETOF. There are no OUT parameters.
我不知道为什么 Postgres 在这里抱怨，因为我的代码看起来很像文档中的示例，只是我的函数返回 TABLE 而不是 SETOF。没有 OUT 参数。
I eventually managed to get it to work using
我最终设法让它工作使用
RETURN QUERY SELECT result_row.column1, result_row.column2, ...;
but having to list all the columns all the time is ugly and harder to maintain. I'm sure there must be a better way.
但是必须一直列出所有的列是丑陋的并且更难维护。我相信一定有更好的方法。
回答by Erwin Brandstetter
RETURN NEXTjust returns what the parameters declared in your RETURNSclause (column1, column2, ..)presently hold. You cannot provide a parameter for this form.
RETURN NEXT只返回在您的RETURNS子句中声明的参数(column1, column2, ..)目前持有的内容。您不能为此表单提供参数。
There are no OUT parameters.
没有 OUT 参数。
Parameters declared in RETURNS TABLE(column1 integer, column2 boolean, ...)are effectively the sameas OUTparameters.
在声明的参数RETURNS TABLE(column1 integer, column2 boolean, ...)是有效相同的OUT参数。
This should do it:
这应该这样做：
CREATE OR REPLACE FUNCTION my_function()
RETURNS TABLE(column1 integer, column2 boolean, ...) AS
$BODY$
BEGIN
FOR column1, column2, ... IN
SELECT * FROM other_function_returning_same_columns()
LOOP
IF something_wrong_with(column1, column2, ...) THEN
RAISE EXCEPTION 'Something went wrong';
END IF;
RETURN NEXT;
END LOOP;
END
$BODY$ LANGUAGE plpgsql STABLE;
Simpler with a registered type
注册类型更简单
You can further simplify with a registered composite type:
您可以使用注册的复合类型进一步简化：
CREATE TYPE mytype (column1 integer, column2 boolean, ...);
Or, if your type happens to match a table definition, you already have that type, because every table name can be used as type name in PostgreSQL. Then simplify:
或者，如果您的类型恰好与表定义匹配，则您已经拥有该类型，因为每个表名都可以用作 PostgreSQL 中的类型名。然后简化：
CREATE OR REPLACE FUNCTION my_function()
RETURNS SETOF mytype LANGUAGE plpgsql STABLE AS
$func$
DECLARE
_r mytype;
BEGIN
FOR _r IN
SELECT * FROM other_function_returning_same_columns()
LOOP
IF something_wrong_with(_r) THEN
RAISE EXCEPTION 'Something went wrong';
END IF;
RETURN NEXT _r;
END LOOP;
END
$func$;
Reorganize!
改组！
I am pretty sure that all of this can be organized muchmore efficiently.
我很确定所有这些都可以更有效地组织起来。
If you integrate the RAISEcommand into your helper function something_wrong_with()and more conveniently name it (or it's evil twin) everything_groovy(), then you can completely replace my_function()with this simple query:
如果您将RAISE命令集成到您的辅助函数中something_wrong_with()并更方便地命名它（或者它是邪恶的双胞胎）everything_groovy()，那么您可以完全替换my_function()为这个简单的查询：
SELECT *
FROM other_function_returning_same_columns() f
WHERE everything_groovy(f);
Or integrate the RAISEinto the base function other_function_returning_same_columns()to further simplify (and make it faster). If you only want to RAISE EXCEPTIONin certain situations, you can always add a parameter (with a default) to switch it on / off.
或者将 集成RAISE到基本函数中other_function_returning_same_columns()以进一步简化（并使其更快）。如果您只想RAISE EXCEPTION在某些情况下使用，您可以随时添加一个参数（带有默认值）来打开/关闭它。
