plpgsql错误“RETURN NEXT不能在OUT参数的函数中具有参数”在表返回功能 [英] plpgsql error "RETURN NEXT cannot have a parameter in function with OUT parameters" in table-returning function

问题描述

我在PostgreSQL 9.2中有一个plpgsql函数返回一个表。该函数运行多个SELECT，返回与函数相同的列，然后返回这些结果或引发异常，具体取决于某些检查。我可以看到这样做的唯一方法是使用FOR ... LOOP，但是我无法找到一种方便的方式来返回该行。

我想做这样的事情：

 创建或替换函数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出了问题 
 END IF; 
 
返回下一页result_row; 
 END LOOP; 
 END 
 $ BODY $ LANGUAGE plpgsql STABLE; 
  

这给我一个错误：

错误：RETURN NEXT在OUT参数的功能中不能有参数

我不知道为什么Postgres在这里抱怨，因为我的代码看起来很像中的示例文档，除了我的函数返回TABLE而不是SETOF。没有OUT参数。

我最终设法使用

 返回查询选择result_row.column1，result_row.column2，...; 
  

但是必须列出所有的列都是丑陋和难以维护。我确定必须有一个更好的方法。

解决方案

返回下一个只返回在 RETURNS 子句（column1，column2，..）中声明的参数。您不能为此表单提供参数。

没有OUT参数。

在中声明的参数RETURNS TABLE（column1 integer，column2 boolean，...）有效地 作为 OUT 参数。

应该这样做：

  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出错'; 
 END IF; 
 
返回下一个; 
 END LOOP; 
 END 
 $ BODY $ LANGUAGE plpgsql STABLE; 
  

更简单的注册类型

你可以使用注册的组合类型进一步简化：

  CREATE TYPE mytype（column1 integer，column2 boolean，...）; 
  

或者，如果您的类型与表定义匹配，则您已具有该类型，因为每个表名称可以用作PostgreSQL中的类型名称。然后简化：

 创建或替换功能my_function（）
返回设置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出了问题 
 END IF; 
 
返回下一个_r; 
 END LOOP; 
 END 
 $ func $; 
  

重组！

我很确定所有这些都可以更有效地组织起来。

如果您整合了 RAISE 命令进入你的帮助函数 something_wrong_with（）并且更方便地命名它（或它是邪恶的双胞胎） everything_groovy（）那么您可以用这个简单的查询完全替换 my_function（）：

  SELECT * 
 FROM other_function_returning_same_columns（）f 
 WHERE everything_groovy（f）; 
  

或将 RAISE 集成到基本功能 other_function_returning_same_columns（）以进一步简化（并使其更快）。如果您在某些情况下只想要 RAISE EXCEPTION ，您可以随时添加一个参数（默认值）来打开/关闭。

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.

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

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.

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.

解决方案

RETURN NEXT just returns what the parameters declared in your RETURNS clause (column1, column2, ..) presently hold. You cannot provide a parameter for this form.

There are no OUT parameters.

Parameters declared in RETURNS TABLE(column1 integer, column2 boolean, ...) are effectively the same as OUT parameters.

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:

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 much more efficiently.

If you integrate the RAISE command 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:

SELECT *
FROM   other_function_returning_same_columns() f
WHERE  everything_groovy(f);

Or integrate the RAISE into the base function other_function_returning_same_columns() to further simplify (and make it faster). If you only want to RAISE EXCEPTION in certain situations, you can always add a parameter (with a default) to switch it on / off.

这篇关于plpgsql错误“RETURN NEXT不能在OUT参数的函数中具有参数”在表返回功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！