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