2

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?

2

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
--------------------+--------------------
 <unnamed portal 1> | <unnamed portal 2>
(1 row)

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

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

postgres@localhost/postgres>

Some SQL clients can display the results automatically though.

1

Did this feature indeed appear in the Postgres 11 release?

No. I think the blog post refers to the "Dynamic result sets from procedures" patch, which did not make it into the released version.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.