RETURN NEXT statement in PostgreSQL
Tags: postgres, returnnext, return, nextjs
Published: 2024-02-08 19:27:43

### A [RETURN NEXT statement](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-RETURN-NEXT):

- 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](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-RETURN) can.

- can work only in a PL/pgSQL function. *[My post](https://dev.to/hyperkai/a-plpgsql-function-in-postgresql-3j9f) explains a PL/pgSQL function.

- can work only with `SETOF <sometype>` or `TABLE()` in a `RETURNS` clause otherwise there are [the error](https://stackoverflow.com/questions/17570450/error-cannot-use-return-next-in-a-non-setof-function#77975932) and [the error](https://stackoverflow.com/questions/27968966/return-cannot-have-a-parameter-in-function-returning-set-use-return-next-at-or).

*[The doc](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-RETURN-NEXT) explains `RETURN NEXT` statement in detail.

*[My post](https://dev.to/hyperkai/return-query-statement-in-postgresql-2j4i) explains [RETURN QUERY statement](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-RETURN-NEXT).

For example, you create `person` table as shown below:

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

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

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

Now, you can create `my_func()` with a [FOR](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING) and `RETURN NEXT` statement as shown below:

```sql
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](https://stackoverflow.com/questions/77969232/declaring-a-record-variable-gets-variable-has-pseudo-type-record-e).

- 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](https://dev.to/hyperkai/for-statement-in-postgresql-5d19) explains `FOR` statement.

- [My post](https://dev.to/hyperkai/declarations-in-postgresql-357o) explains `%ROWTYPE`.

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

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

```sql
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:

```sql
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:

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

```sql
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:

```sql
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](https://stackoverflow.com/questions/14039720/plpgsql-error-return-next-cannot-have-a-parameter-in-function-with-out-paramete#77958122).

- 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](https://stackoverflow.com/questions/14039720/plpgsql-error-return-next-cannot-have-a-parameter-in-function-with-out-paramete#77958122).

- 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:

```sql
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](https://stackoverflow.com/questions/14039720/plpgsql-error-return-next-cannot-have-a-parameter-in-function-with-out-paramete#77958122).

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

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

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

And, you can use `SETOF RECORD` as shown below:

```sql
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:

```sql
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](https://stackoverflow.com/questions/8605174/postgresql-error-42601-a-column-definition-list-is-required-for-functions-ret/77990872#77990872).

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

- Running `SELECT my_func();` gets [the error](https://stackoverflow.com/questions/77949369/materialize-mode-required-but-it-is-not-allowed-in-this-context-error-in-post).

- 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;`:

```sql
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:

```sql
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:

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

```sql
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:

```sql
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](https://stackoverflow.com/questions/756689/postgresql-function-returning-multiple-result-sets#77977660) has the examples of using multiple `RETURN NEXT` or `RETURN QUERY` statements in a function.

- [My answer](https://stackoverflow.com/questions/23249390/using-return-next-and-return-query-in-the-same-postgres-function#77983325) 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:

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

```sql
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:

```sql
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:

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

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

And, you can use an [EXECUTE statement](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN) to be dynamic as shown below. *[My post](https://dev.to/hyperkai/prepare-and-execute-statements-in-postgresql-3bhc) explains `EXECUTE` statement:

```sql
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:

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

```sql
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:

```sql
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](https://stackoverflow.com/questions/77944893/how-to-create-the-value-of-setof-sometype-type-by-hand-in-postgresql#77957084) explains how to create the value of `SETOF <sometype>` type.

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

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

```sql
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:

```sql
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:

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

```sql
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](https://stackoverflow.com/questions/14039720/plpgsql-error-return-next-cannot-have-a-parameter-in-function-with-out-paramete#77958122):

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

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

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

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

And, you can use a [FOREACH statement](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY) with a `RETURN NEXT` statement as shown below. *[My post](https://dev.to/hyperkai/foreach-and-for-statements-with-arrays-in-postgresql-2bnc) explains `FOREACH` statement:

```sql
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:

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

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