returning multiple refcursors from inner function

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

returning multiple refcursors from inner function

Debajyoti Saha

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