Multiple result set to be returned in procedure/function

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Multiple result set to be returned in procedure/function

Muthukumar.GK
Hi team,

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.

regards
muthu
9894438403
Reply | Threaded
Open this post in threaded view
|

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

Thomas Kellerer-4
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



Reply | Threaded
Open this post in threaded view
|

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

Daniel Verite
        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


Reply | Threaded
Open this post in threaded view
|

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

Thomas Kellerer-4
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