Re: Multiple result set to be returned in procedure/function

Muthukumar.GK schrieb am 19.11.2020 um 09:27:
> is it possible to return Multiple results set from procedure/function
> on single execution. Please advise me on this. we are planning to
> migrate things from sqlserver to postgresql where my existing sql
> stored procs will return multiple result set. so we need achieve same
> thing in postgresql.

You can achieve something like that, but it's a bit cumbersome to consume/use the results:

create function get_results()
returns setof refcursor
as
$$
declare
c1 refcursor;
c2 refcursor;
begin
open c1 for select * from (values (1,2,3), (4,5,6)) as t(a,b,c);
return next c1;
open c2 for select * from (values ('one'),('two'),('three'),('four')) as p(name);
return next c2;
end;
$$
language plpgsql;

You have to turn off autocommit in order to be able to consume the results.

In psql you would get a result with two "unnamed portals" that you need to fetch
manually

arthur=> \set AUTOCOMMIT off

arthur=> select * from get_results();
get_results
--------------------
<unnamed portal 1>
<unnamed portal 2>

arthur=> fetch all in fetch all in "<unnamed portal 1>";
a | b | c
---+---+---
1 | 2 | 3
4 | 5 | 6
(2 rows)

arthur=> fetch all in fetch all in "<unnamed portal 2>";
name
-------
one
two
three
four
(4 rows)

Other SQL clients might make this a bit easier.

How exactly you deal with that in your application depends on the
programming language you use.

I would recommend to take the opportunity of the migration project
and refactor your code so that you don't need this.

Thomas