Returning multiple result sets from server-side procedures in Postgres 11
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.
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.