patternsqlMinor

Returning multiple result sets from server-side procedures in Postgres 11

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

resultprocedurespostgressidesetsreturningmultipleserverfrom

Problem

This blog post by a Postgres consulting company mentions that the new server-side procedure support in Postgres 11 would be able to return multiple result sets.

➥ Did this feature indeed appear in the Postgres 11 release?

If so, can you explain how it works briefly? Show some code example?

Solution

you can return multiple result sets from a procedure - similar to the way it has always been possible with a function:

create procedure getdata(result_one inout refcursor, result_two inout refcursor)
as
$
begin
  open result_one for 
    select * 
    from (values (1,2,3), (4,5,6)) as t(a,b,c);

  open result_two for 
    select * 
    from (values ('one'),('two'),('three'),('four')) as p(name);
end;
$
language plpgsql;

However, displaying the results is a bit cumbersome - at least in psql:

postgres@localhost/postgres> \set AUTOCOMMIT off

postgres@localhost/postgres> call getdata(null, null);
     result_one     |     result_two
--------------------+--------------------
  | 
(1 row)

postgres@localhost/postgres> fetch all "";
 a | b | c
---+---+---
 1 | 2 | 3
 4 | 5 | 6
(2 rows)

postgres@localhost/postgres> fetch all "";
 name
-------
 one
 two
 three
 four
(4 rows)

postgres@localhost/postgres>

Some SQL clients can display the results automatically though.

Code Snippets

create procedure getdata(result_one inout refcursor, result_two inout refcursor)
as
$$
begin
  open result_one for 
    select * 
    from (values (1,2,3), (4,5,6)) as t(a,b,c);

  open result_two for 
    select * 
    from (values ('one'),('two'),('three'),('four')) as p(name);
end;
$$
language plpgsql;

Context

StackExchange Database Administrators Q#236741, answer score: 9

Revisions (0)

No revisions yet.