RETURN QUERY statement in PostgreSQL
Tags: postgres, returnquery, return, query
Published: 2024-02-14 16:49:41

### A [RETURN QUERY 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 at once and the rows cannot be modified.

- cannot exit a function while a [RETURN statement](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-RETURN) can.

- can be used with [SELECT](https://www.postgresql.org/docs/current/sql-select.html) or [VALUES](https://www.postgresql.org/docs/current/queries-values.html) statement.

- can be used with [INSERT](https://www.postgresql.org/docs/current/sql-insert.html), [UPDATE](https://www.postgresql.org/docs/current/sql-update.html) or [DELETE](https://www.postgresql.org/docs/current/sql-delete.html) statement with [RETURNING clause](https://www.postgresql.org/docs/current/dml-returning.html).

- can be used with [EXECUTE statement](https://www.postgresql.org/docs/current/sql-execute.html) to be dynamic. *[My post](https://dev.to/hyperkai/prepare-and-execute-statements-in-postgresql-3bhc) explains how to use `EXECUTE` statement in a function.

- 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 QUERY` statement in detail.

*[My post](https://dev.to/hyperkai/return-next-statement-in-postgresql-1lmi) explains [RETURN NEXT 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 `RETURN QUERY` statement as shown below:

```sql
CREATE FUNCTION my_func() RETURNS SETOF person AS $$
BEGIN
  RETURN QUERY SELECT * FROM person; -- Here
END;
$$ LANGUAGE plpgsql;
```

*Memos:

- `RETURN QUERY` statement must be used with `SETOF <sometype>` or `TABLE()` otherwise there are [the error](https://stackoverflow.com/questions/55688418/what-is-a-non-setof-function-in-postgresql#77985413) and [the error](https://stackoverflow.com/questions/27968966/return-cannot-have-a-parameter-in-function-returning-set-use-return-next-at-or). 

- 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).

- `SETOF VOID` type exists but you cannot use it with a `RETURN QUERY` statement otherwise there is error when you call the function while you can use it without a `RETURN QUERY` statement.

- If you use the `SELECT` statement with an `INTO` clause, there is error.

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 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 $$
BEGIN -- ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑
  RETURN QUERY SELECT * FROM person;
END;
$$ LANGUAGE plpgsql;
```

*Memos:

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

- 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 `name VARCHAR(20)` with `name VARCHAR`.

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 $$
BEGIN                          -- ↑ ↑ Here ↑ ↑
  RETURN QUERY SELECT * FROM person;
END;
$$ LANGUAGE plpgsql;
```

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 multiple `RETURN QUERY` statements in `my_func()` as shown below. *A `RETURN QUERY` statement cannot exit a function or procedure:

```sql
CREATE FUNCTION my_func() RETURNS SETOF person AS $$
BEGIN
  RETURN QUERY SELECT * FROM person; -- Here
  RETURN QUERY SELECT * FROM person; -- Here
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 QUERY` 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 QUERY` statements as shown below:

```sql
CREATE FUNCTION my_func() RETURNS SETOF person AS $$
BEGIN
  RETURN QUERY SELECT * FROM person; -- Here

  RETURN; -- Here  

  RETURN QUERY SELECT * FROM person; -- Here
END;
$$ LANGUAGE plpgsql;
```

Then, calling `my_func()` returns 2 rows running only one `RETURN QUERY` 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/sql-execute.html) with a `RETURN QUERY` statement as shown below:

```sql
CREATE FUNCTION my_func(age INT, id INT) RETURNS SETOF person AS $$
BEGIN
  RETURN QUERY EXECUTE 'UPDATE person SET age = $1 WHERE id = $2 RETURNING *' USING age, id;
END;
$$ LANGUAGE plpgsql;
```

*Memos:

- If you use the [UPDATE statement](https://www.postgresql.org/docs/current/sql-update.html) with a [RETURNING clause](https://www.postgresql.org/docs/current/dml-returning.html), there is error.

*[My post](https://dev.to/hyperkai/prepare-and-execute-statements-in-postgresql-3bhc) explains `EXECUTE` statement:

Then, calling `my_func()` returns the row updated, then `age` of `David` is updated to `56` as shown below:

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

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

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

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

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

```sql
CREATE FUNCTION my_func() RETURNS SETOF TEXT[] AS $$
BEGIN                          -- ↑ ↑ Here ↑ ↑
  RETURN QUERY VALUES (ARRAY['a','b']), (ARRAY['c','d']);
END; -- Here
$$ 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.

- [My question and the answers](https://stackoverflow.com/questions/77995262/escaping-to-return-text-type-with-a-return-query-execute-and-values-stat) explain how to create the value of `SETOF TEXT[]` with a `RETURN QUERY EXECUTE` and `VALUES` statement by escape.

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)
```