RETURN QUERY statement in PostgreSQL

Buy Me a Coffee☕

A RETURN QUERY statement:

• 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 can.

• can be used with SELECT or VALUES statement.

• can be used with INSERT, UPDATE or DELETE statement with RETURNING clause.

• can be used with EXECUTE statement to be dynamic. *My post explains how to use EXECUTE statement in a function.

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

*My post explains RETURN NEXT 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 RETURN QUERY statement as shown below:

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 and the error.

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

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

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

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

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

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 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 QUERY 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 QUERY statements as shown below:

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:

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 with a RETURN QUERY statement as shown below:

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 with a RETURNING clause, there is error.

*My post explains EXECUTE statement:

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

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)

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:

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 explains how to create the value of SETOF <sometype> type.

• My question and the answers 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:

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)