如何在PL/pgSQL中正确使用`RETURN NEXT`? [英] How to use `RETURN NEXT`in PL/pgSQL correctly?

问题描述

我正在尝试使用PL/pgSQL(PostgreSQL 9.3)函数编写一个返回表的循环.我在循环中的每个查询之后都使用了不带参数的RETURN NEXT;，下面的示例类似于 plpgsql错误" RETURN NEXT不能在函数中使用参数" OUT参数"在返回表功能中以及其他地方.但是，我仍然收到一条错误消息:

I am trying to write a loop using PL/pgSQL (PostgreSQL 9.3) function that returns a table. I used RETURN NEXT; with no parameters after each query in the loop, following examples found like plpgsql error "RETURN NEXT cannot have a parameter in function with OUT parameters" in table-returning function, and elsewhere. However, I am still getting an error that says:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.

下面是重现该问题的最小代码示例.谁能帮忙解释一下如何修复测试代码以返回表?

A minimal code example to reproduce the problem is below. Can anyone please help explain how to fix the test code to return a table?

提前谢谢.

最小示例:

CREATE OR REPLACE FUNCTION test0()
 RETURNS TABLE(y integer, result text) AS $func$
DECLARE
    yr RECORD;
BEGIN
    FOR yr IN SELECT * FROM generate_series(1,10,1) AS y_(y) 
    LOOP
        RAISE NOTICE 'Computing %', yr.y;
        SELECT yr.y, 'hi';
        RETURN NEXT;
    END LOOP;
    RETURN;
END
$func$ LANGUAGE plpgsql;

推荐答案

给出的示例可以完全替换为RETURN QUERY:

The example given may be wholly replaced with RETURN QUERY:

BEGIN
    RETURN QUERY SELECT y_.y, 'hi' FROM generate_series(1,10,1) AS y_(y)
END;

，将比很多更快.

通常，您应该尽可能避免迭代，而应该支持面向集合的操作.

In general you should avoid iteration wherever possible, and instead favour set-oriented operations.

必须避免在循环中通过return next的情况(这种情况很少见，并且主要限于需要异常处理的情况)，您必须设置OUT参数值或表参数，然后设置return next不带参数.

Where return next over a loop is unavoidable (which is very rare, and mostly confined to when you need exception handling) you must set OUT parameter values or table parameters, then return next without arguments.

在这种情况下，您的问题是没有执行任何操作的行SELECT yr.y, 'hi';.您假设SELECT的隐式目标是out参数，但事实并非如此.您必须像@peterm一样将out参数用作循环变量，使用赋值或使用SELECT INTO:

In this case your problem is the line SELECT yr.y, 'hi'; which does nothing. You're assuming that the implicit destination of a SELECT is the out parameters, but that's not the case. You'd have to use the out parameters as loop variables like @peterm did, use assignments or use SELECT INTO:

FOR yr IN SELECT * FROM generate_series(1,10,1) AS y_(y) 
LOOP
    RAISE NOTICE 'Computing %', yr.y;
    y := yr.y;
    result := 'hi';
    RETURN NEXT;
END LOOP;
RETURN;

这篇关于如何在PL/pgSQL中正确使用`RETURN NEXT`?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！