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>
orTABLE()
in aRETURNS
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 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.You need to set
row
local variable to theRETURN NEXT
statement otherwise there is error.You can replace
row person%ROWTYPE;
withrow RECORD;
.If you use the
SELECT
statement with anINTO
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()
andSETOF TABLE()[]
don't exist.You need to remove
row
local variable from theRETURN NEXT
statement otherwise there is the error.You need set
id
,name
andage
parameter to theFOR
statement instead ofrow
local variable otherwise empty rows are returned when you callmy_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
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
row person%ROWTYPE;
withrow RECORD;
.You can replace
name VARCHAR(20)
withname VARCHAR
.You can remove
RETURN NEXT /* row */;
without error but no rows are returned frommy_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
androw.age
toid
,name
andage
parameter respectively in theFOR
statement otherwise empty rows are returned when you callmy_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;
withrow 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
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 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
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 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)
Top comments (0)