returning multiple refcursors from inner function

Debajyoti Saha <[email protected]> Fri, 29 Mar 2019 08:11:17 +0000
Newsgroups gmane.comp.db.postgresql.sql
Message-ID <[email protected]>
Hi,

I am using working on Migration task from SQL server to Postgresql.
I have a problem statement.

Suppose, I have Inner function which returning multiple refcursors and out function consuming that and returning 2 refcursors which is returned from inner function.

How to handle that scenario please guide me

This is the sample functions
DROP FUNCTION optix."inner"();

CREATE OR REPLACE FUNCTION optix.inner(OUT rs1 refcursor, OUT rs2 refcursor)
    RETURNS record
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE SECURITY DEFINER
AS $BODY$

declare

BEGIN
                rs1 := 'rs1';
                rs2 :='rs2';
                open rs1 for
                select 'Debajyoti' as Name,
                                   'Infosys' As Company;


                open rs2 for
                select 'Bharath' as Name,
                                   'Infosys' as Company;

END;

$BODY$;

ALTER FUNCTION optix."inner"()
    OWNER TO nddba;




DROP FUNCTION optix."outer"();

CREATE OR REPLACE FUNCTION optix.outer(
                )
    RETURNS SETOF refcursor
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE SECURITY DEFINER
AS $BODY$

declare
rs1 refcursor:='rs1';
rs2 refcursor:='rs2';
begin

select optix.inner() into rs1, rs2;
return next rs1;
return next rs2;

end;

$BODY$;

ALTER FUNCTION optix."outer"()
    OWNER TO nddba;



Regards,
Debajyoti Saha