How to use RETURN NEXT in a PL/pgSQL function?

Question

I am trying to write a loop in a PL/pgSQL function in PostgreSQL 9.3 that returns a table. I used RETURN NEXT; with no parameters after each query in the loop, following examples I found, like:

• plpgsql error "RETURN NEXT cannot have a parameter in function with OUT parameters" in table-returning function

However, I am still getting an error:

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?

Minimal example:

CREATE OR REPLACE FUNCTION test0()
  RETURNS TABLE(y integer, result text)
  LANGUAGE plpgsql 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$;

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;