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$;

Accomplishment

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;

Unkempt

Answer 2

What @Craig already explained.

Plus, if you really need a loop, you can have this simpler / cheaper. You don't need to declare an additional record variable and assign repeatedly. Assignments are comparatively expensive in plpgsql. Assign to the OUT variables declared in RETURNS TABLE directly. Those are visible everywhere in the code and the FOR loop can also assign to a list of variables. The manual:

The target is a record variable, row variable, or comma-separated list of scalar variables.

CREATE OR REPLACE FUNCTION test0()
  RETURNS TABLE(y integer, result text)
  LANGUAGE plpgsql AS
$func$
DECLARE
    yr RECORD;  -- now unneeded
BEGIN
   FOR y, result IN
      SELECT g, 'text_'::text || g
      FROM   generate_series(1,10) g
   LOOP
      RAISE NOTICE 'Computing %', y;
      RETURN NEXT;
   END LOOP;
END
$func$;

Additional points

• Do not use the identifier y twice (as OUT param and column alias) while you can easily avoid it. That's a loaded footgun. If this can't be avoided, table-qualify columns.

• A final RETURN without params is good form, but totally optional. When control reaches the final END, the complete result is returned automatically.

• g in FROM generate_series(1,10) g is both table alias and column alias automatically, unless an explicit column alias is given. It is effectively the same as FROM generate_series(1,10) g(g).

Spongin

Answer 3

One way to do it

CREATE OR REPLACE FUNCTION test0()
 RETURNS TABLE(y integer, result text) AS $$
BEGIN
    FOR y, result IN 
        SELECT s.y, 'hi' result FROM generate_series(1,10,1) AS s(y)
    LOOP
        RETURN NEXT;
    END LOOP;
END
$$ LANGUAGE plpgsql;

SELECT * FROM test0();

Outcome:

|  Y | RESULT |
|----|--------|
|  1 |     hi |
|  2 |     hi |
|  3 |     hi |
|  4 |     hi |
|  5 |     hi |
|  6 |     hi |
|  7 |     hi |
|  8 |     hi |
|  9 |     hi |
| 10 |     hi |

Here is a SQLFiddle demo

Varied

Useful, but it'd be nice if you could explain why it works, as the original poster is clearly a bit confused about output variables. – Unkempt

Answer 4

For example, you create person table, then insert 2 rows into it as shown below:

CREATE TABLE person (
  id INT,
  name VARCHAR(20),
  age INT
);

INSERT INTO person (id, name, age) 
VALUES (1, 'John', 27), (2, 'David', 32);

Now, you can create my_func() with a FOR and RETURN NEXT statement as shown below:

CREATE FUNCTION my_func() RETURNS SETOF person AS $$
DECLARE 
  row person%ROWTYPE;
BEGIN
  FOR row IN SELECT * FROM person LOOP
    RETURN NEXT row; -- Here
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Then, calling my_func() returns 2 rows as shown below:

postgres=# SELECT * FROM my_func();
 id | name  | age
----+-------+-----
  1 | John  |  27
  2 | David |  32
(2 rows)

postgres=# SELECT my_func();
   my_func
--------------
 (1,John,27)
 (2,David,32)
(2 rows)

Dextrorotation