How to use `RETURN NEXT`in PL/pgSQL correctly?

Question

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?

Thanks in advance.

Minimal example:

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;

Answer 1

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;

which will be a lot faster.

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

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.

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;