RETURN NEXT statement in PostgreSQL

A RETURN NEXT statement:

• can append zero or more rows to the function's result set one by one and the rows can be modified one by one.

• cannot exit a function while a RETURN statement can.

• can work only in a PL/pgSQL function. *My post explains a PL/pgSQL function.

• can work only with SETOF <sometype> or TABLE() in a RETURNS clause otherwise there are the error and the error.

*The doc explains RETURN NEXT statement in detail.

*My post explains RETURN QUERY statement.

For example, you create person table as shown below:

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

Then, you insert 2 rows into person table as shown below:

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;

*Memos:

• You can use SETOF <sometype> only in a RETURNS clause so if you use SETOF <sometype> for a local variable or parameter, there is error.

• SETOF RECORD[], SETOF TABLE() and SETOF TABLE()[] don't exist in PostgreSQL so if you use them in a RETURNS clause, there is error. *RECORD[] itself doesn't exist in PostgreSQL so if you use RECORD[], there is the error.

• You need to set row local variable to the RETURN NEXT statement otherwise there is error.

• You can replace row person%ROWTYPE; with row RECORD;.

• If you use the SELECT statement with an INTO clause, there is the error(10).

• My post explains FOR statement.

• My post explains %ROWTYPE.

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)

And, you can modify name to Tom in the FOR statement as shown below:

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

Then, name is modified to Tom as shown below:

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

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

And, you can use the TABLE() with id, name and age parameter in a RETURNS clause as shown below:

CREATE FUNCTION my_func()
RETURNS TABLE(id INT, name VARCHAR(20), age INT) AS $$
DECLARE -- ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑
  row person%ROWTYPE;
BEGIN -- ↓ ↓ ↓ ↓ ↓
  FOR id, name, age IN SELECT * FROM person LOOP
    RETURN NEXT /* row */;
  END LOOP;  -- ↑ ↑ ↑ ↑ ↑
END;    
$$ LANGUAGE plpgsql;

*Memos:

• SETOF TABLE() and SETOF TABLE()[] don't exist.

• You need to remove row local variable from the RETURN NEXT statement otherwise there is the error.

• You need set id, name and age parameter to the FOR statement instead of row local variable otherwise empty rows are returned when you call my_func() as I explain it in my answer.

• You need to set both a parameter name and type to TABLE() otherwise there is error.

• You can use other parameter names instead of id, name and age in TABLE() without error but you should use the same parameter names as person table's columns in TABLE() for clarity.

• You can set other types to id, name and age parameter but there is error sometimes and it is unclear so you should set the same types as person table's columns to them.

• You can replace row person%ROWTYPE; with row RECORD;.

• You can replace name VARCHAR(20) with name VARCHAR.

• You can remove RETURN NEXT /* row */; without error but no rows are returned from my_func().

Or:

CREATE FUNCTION my_func()
RETURNS TABLE(id INT, name VARCHAR(20), age INT) AS $$
DECLARE
  row person%ROWTYPE;
BEGIN
  FOR row IN SELECT * FROM person LOOP
    id := row.id; -- Here
    name := row.name; -- Here
    age := row.age; -- Here
    RETURN NEXT /* row */;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

*Memos:

• You need to assign row.id, row.name and row.age to id, name and age parameter respectively in the FOR statement otherwise empty rows are returned when you call my_func() as I explain it in my answer.

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)

And, you can use SETOF RECORD as shown below:

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

*Memos:

• You can replace row person%ROWTYPE; with row RECORD;.

Then, calling my_func() in the FROM clause returns 2 rows as shown below:

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

*Memos:

• Running SELECT * FROM my_func(); gets the error.

• Running SELECT my_func() AS (id INT, name VARCHAR(20), age INT); gets a syntax error.

• Running SELECT my_func(); gets the error.

• You need to set both a parameter name and type to the AS clause otherwise there is error.

• You can use other parameter names instead of id, name and age in the AS clause without error but you should use the same parameter names as person table's columns in AS clause for clarity.

• You need to set the same types as person table's columns to id, name and age parameter in the AS clause otherwise there is error. *You can replace name VARCHAR(20) with name VARCHAR, then there is no error but you should set the same type as person table's column to name in the AS clause for clarity.

And, you can use OUT parameters with SETOF RECORD as shown below. *You can replace row person%ROWTYPE; with row RECORD;:

CREATE FUNCTION my_func(
  OUT id INT, -- Here 
  OUT name VARCHAR(20), -- Here 
  OUT age INT -- Here
) RETURNS SETOF RECORD AS $$
DECLARE -- ↑↑ Here ↑↑  
  row person%ROWTYPE;
BEGIN -- ↓ ↓ ↓ ↓ ↓
  FOR id, name, age IN SELECT * FROM person LOOP
    RETURN NEXT /* row */;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Or:

CREATE FUNCTION my_func(
  OUT id INT, -- Here 
  OUT name VARCHAR(20), -- Here 
  OUT age INT -- Here
) RETURNS SETOF RECORD AS $$
DECLARE -- ↑↑ Here ↑↑  
  row person%ROWTYPE;
BEGIN -- ↓ ↓ ↓ ↓ ↓
  FOR row IN SELECT * FROM person LOOP
    id := row.id;
    name := row.name;
    age := row.age; 
    RETURN NEXT /* row */;
  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)

And, you can use multiple RETURN NEXT statements in my_func() as shown below. *A RETURN NEXT statement cannot exit a function:

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;
  FOR row IN SELECT * FROM person LOOP
    RETURN NEXT row; -- Here
  END LOOP;
END;
$$ LANGUAGE plpgsql;

*Memos:

• My answer has the examples of using multiple RETURN NEXT or RETURN QUERY statements in a function.

• My answer has the examples of using a RETURN NEXT and RETURN QUERY statement together in a function.

Then, calling my_func() returns 4 rows running two RETURN NEXT statements as shown below:

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

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

And, you can use a RETURN statement to exit my_func() with multiple RETURN NEXT statements 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;

  RETURN; -- Here

  FOR row IN SELECT * FROM person LOOP
    RETURN NEXT row; -- Here
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Then, calling my_func() returns 2 rows running only one RETURN NEXT statement 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)

And, you can use an EXECUTE statement to be dynamic as shown below. *My post explains EXECUTE statement:

CREATE FUNCTION my_func(min INT, max INT) RETURNS SETOF person AS $$
DECLARE
  row person%ROWTYPE;
BEGIN
  FOR row IN EXECUTE 'SELECT * FROM person WHERE age BETWEEN $1 AND $2' USING min, max LOOP
    RETURN NEXT row; -- Here
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Then, calling my_func() returns 2 rows running only one RETURN NEXT statement as shown below:

postgres=# SELECT my_func(30, 35);
   my_func
--------------
 (2,David,32)
(1 row)

postgres=# SELECT * FROM my_func(30, 35);
 id | name  | age
----+-------+-----
  2 | David |  32
(1 row)

In addition, you can use SETOF TEXT[] with a RETURN NEXT statement as shown below:

CREATE FUNCTION my_func() RETURNS SETOF TEXT[] AS $$
DECLARE                        -- ↑ ↑ Here ↑ ↑
  row RECORD;
BEGIN
  FOR row IN VALUES (ARRAY['a','b']), (ARRAY['c','d']) LOOP
    RETURN NEXT row; -- Here
  END LOOP;
END;
$$ LANGUAGE plpgsql;

*Memos:

• My answer explains how to create the value of SETOF <sometype> type.

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

postgres=# SELECT * FROM my_func();
 my_func
---------
 {a,b}
 {c,d}
(2 rows

postgres=# SELECT my_func();
 my_func
---------
 {a,b}
 {c,d}
(2 rows)

And, you can use a RETURN NEXT statement without a FOR statement as shown below:

CREATE FUNCTION my_func() RETURNS SETOF person AS $$
DECLARE 
  row person%ROWTYPE;
BEGIN
  SELECT * INTO row FROM person WHERE id = 2;
  RETURN NEXT row; -- Here
END;    
$$ LANGUAGE plpgsql;

Then, calling my_func() returns a row as shown below:

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

postgres=# SELECT my_func();
   my_func
--------------
 (2,David,32)
(1 row)

And, you can return 2 with a RETURN NEXT statement as shown below. *You must use SETOF INT instead of INT in the RETRUNS clause, otherwise there is the error:

CREATE FUNCTION my_func() RETURNS INT AS $$
BEGIN
  RETURN NEXT 2; -- Here
END;    
$$ LANGUAGE plpgsql;

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

postgres=# SELECT my_func();
 my_func
---------
       2
(1 row)

postgres=# SELECT * FROM my_func();
 my_func
---------
       2
(1 row)

And, you can use a FOREACH statement with a RETURN NEXT statement as shown below. *My post explains FOREACH statement:

CREATE FUNCTION my_func() RETURNS SETOF VARCHAR AS $$
DECLARE 
  temp VARCHAR;
  _1d_arr VARCHAR[] := ARRAY['a','b','c'];
BEGIN
  FOREACH temp SLICE 0 IN ARRAY _1d_arr LOOP
    RETURN NEXT temp; -- Here
  END LOOP;
END;    
$$ LANGUAGE plpgsql;

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

postgres=# SELECT * FROM my_func();
 my_func
---------
 a
 b
 c
(3 rows)

postgres=# SELECT my_func();
 my_func
---------
 a
 b
 c
(3 rows)