Returning Multiple Record sets using refcursor in PostgreSQL

Question

-- FUNCTION: public.asyncmultiplerecs()

-- DROP FUNCTION public.asyncmultiplerecs();

CREATE OR REPLACE FUNCTION public.asyncmultiplerecs()
    RETURNS SETOF refcursor 
    LANGUAGE 'plpgsql'
    COST 100.0
AS $function$
DECLARE
  ref1 refcursor;           -- Declare cursor variables
  ref2 refcursor;  
  ref3 refcursor; 
  ref4 refcursor; 
BEGIN
  OPEN ref1 FOR  SELECT bk_channel_id,promotion_id FROM  cs_promotion_offer_exclusions; 
  RETURN NEXT ref1;                                                                              

  OPEN ref2 FOR SELECT  mastergroup,promo_grp_id FROM  cs_promotion_group_master;
  RETURN NEXT ref2; 

  OPEN ref3 FOR SELECT  promotion_usoc,promotion_duration FROM  cs_promotion_target_details;
  RETURN NEXT ref2; 

  OPEN ref4 FOR SELECT  promotion_id,offer_id FROM  cs_promotion_details;
  RETURN NEXT ref4; 
END;
$function$;

Above is my Function, i want to execute all the records sets from the above function.

Laurenz Albe · Accepted Answer · 2018-07-02 15:42:44Z

1

You get all the cursors with

SELECT * FROM asyncmultiplerecs();

Then you use FETCH to fetch results from the cursors.

You forgot to assign names to the cursors, so they will be unnamed.

Here is a complete example how this could be done:

CREATE FUNCTION asyncmultiplerecs() RETURNS SETOF refcursor
   LANGUAGE plpgsql AS 
$$DECLARE
   ref1 refcursor;
BEGIN
   ref1 := 'c1';
   OPEN ref1 FOR VALUES (1), (2);
   RETURN NEXT ref1;

   ref1 := 'c2';
   OPEN ref1 FOR VALUES (3), (4);
   RETURN NEXT ref1;
END;$$;

Now you have to call the function in a transaction, because cursors will be closed at commit time:

BEGIN;

SELECT * FROM asyncmultiplerecs();
 asyncmultiplerecs 
-------------------
 c1
 c2
(2 rows)

FETCH ALL FROM c1;
 column1 
---------
       1
       2
(2 rows)

FETCH ALL FROM c2;
 column1 
---------
       3
       4
(2 rows)

COMMIT;

answered Jul 2, 2018 at 15:42

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user7688717 Over a year ago

Thanks for your response. Helps me a lot.

Collectives™ on Stack Overflow

Returning Multiple Record sets using refcursor in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related