How can I return multiple rows of records in PL/pgSQL
The function needs to return a SETOF RECORD
instead of RECORD
and have one RETURN NEXT
per row instead of a single RETURN
, as in:
CREATE FUNCTION test() RETURNS SETOF RECORD AS $$
DECLARE
rec record;
BEGIN
select 1,2 into rec;
return next rec;
select 3,4 into rec;
return next rec;
END $$ language plpgsql;
Caller:
=> select * from test() as x(a int ,b int) ; a | b ---+--- 1 | 2 3 | 4 (2 rows)
Note that SQL being strongly and statically typed, the RECORD
pseudo-type is hard to work with.
Often it's less cumbersome to use right from the start a composite type with a full definition of names and type for each column, either with the TABLE(...)
syntax for an anonymous type or with CREATE TYPE
for a persistent named type.
Use setof record
and return next rec
if you want to return multiple records from a function, example:
create or replace function test_function()
returns setof record
language plpgsql as $$
declare
rec record;
begin
for rec in
select i, format('str%s', i), i/2*2 = i
from generate_series(1, 3) i
loop
return next rec;
end loop;
end $$;
Such a function needs to be called in the FROM clause with a column definition list:
select test_function(); -- NO
ERROR: set-valued function called in context that cannot accept a set
select * from test_function(); -- NO
ERROR: a column definition list is required for functions returning "record"
select * from test_function() as (id int, str text, is_even boolean);
id | str | is_even
----+------+---------
1 | str1 | f
2 | str2 | t
3 | str3 | f
(3 rows)
A better option is to use returns table(...)
and return query
:
drop function if exists test_function();
create or replace function test_function()
returns table (id int, str text, is_even boolean)
language plpgsql as $$
begin
return query
select i, format('str%s', i), i/2*2 = i
from generate_series(1, 3) i;
-- you can use return query multiple times
-- or assign values to columns
-- and return the row:
id = 100;
str = 'extra';
is_even = true;
return next; -- without a parameter
end $$;
Usage:
select test_function(); -- possible but rather impractical
test_function
---------------
(1,str1,f)
(2,str2,t)
(3,str3,f)
(100,extra,t)
(4 rows)
select * from test_function();
id | str | is_even
-----+-------+---------
1 | str1 | f
2 | str2 | t
3 | str3 | f
100 | extra | t
(4 rows)