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 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>
orTABLE()
in aRETURNS
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 withSETOF <sometype>
orTABLE()
otherwise there are the error and the error.You can use
SETOF <sometype>
only in aRETURNS
clause so if you useSETOF <sometype>
for a local variable or parameter, there is error.SETOF RECORD[]
,SETOF TABLE()
andSETOF TABLE()[]
don't exist in PostgreSQL so if you use them in aRETURNS
clause, there is error. *RECORD[]
itself doesn't exist in PostgreSQL so if you useRECORD[]
, there is the error.SETOF VOID
type exists but you cannot use it with aRETURN QUERY
statement otherwise there is error when you call the function while you can use it without aRETURN QUERY
statement.If you use the
SELECT
statement with anINTO
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()
andSETOF 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
andage
inTABLE()
without error but you should use the same parameter names asperson
table's columns inTABLE()
for clarity.You can set other types to
id
,name
andage
parameter but there is error sometimes and it is unclear so you should set the same types asperson
table's columns to them.You can replace
name VARCHAR(20)
withname 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
andage
in theAS
clause without error but you should use the same parameter names asperson
table's columns inAS
clause for clarity.You need to set the same types as
person
table's columns toid
,name
andage
parameter in theAS
clause otherwise there is error. *You can replacename VARCHAR(20)
withname VARCHAR
, then there is no error but you should set the same type asperson
table's column toname
in theAS
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
orRETURN QUERY
statements in a function.My answer has the examples of using a
RETURN NEXT
andRETURN 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 aRETURN QUERY EXECUTE
andVALUES
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)
Top comments (0)