Thread: 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

    Thomas Kellerer wrote:

>    arthur=> \set AUTOCOMMIT off

Alternatively, start an explicit transaction block with BEGIN.
The point is that the lifespan of the cursor is the transaction block
in which it's instantiated.

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

Friendlier names may be used by assigning them in the function,
i.e. plpgsql does support:

 declare
  c1 refcursor := 'mycursorname';

Then the caller might simply hardcode the cursor names in the FETCH
statements rather than building them dynamically at runtime.

Also it allows to put the server-side code into an anymous DO block
instead of creating a function, because it doesn't have to return
any refcursor when the caller already knows the cursor names.

BEGIN;
DO $$
declare c refcursor := 'c_pgclass';
begin
  open c for select relname from pg_class;
end $$ LANGUAGE plpgsql;

FETCH ALL from c_pgclass;
...
COMMIT;

This form might be closer to how inline blocks of code are
written with some other db engines, when they produce resultsets
without an explicit cursor interface.


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: https://www.manitou-mail.org
Twitter: @DanielVerite

Daniel Verite schrieb am 19.11.2020 um 13:06:
>>    arthur=> select * from get_results();
>>        get_results
>>    --------------------
>>     <unnamed portal 1>
>>     <unnamed portal 2>
>
> Friendlier names may be used by assigning them in the function,
> i.e. plpgsql does support:
>
>  declare
>   c1 refcursor := 'mycursorname';
>
> Then the caller might simply hardcode the cursor names in the FETCH
> statements rather than building them dynamically at runtime.

Ah, cool.

That is indeed much easier to work with (in case I ever have to)

Thomas