From: | Debajyoti Saha <debajyoti(dot)saha(at)infosys(dot)com> |
---|---|
To: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | returning multiple refcursors from inner function |
Date: | 2019-03-29 08:11:17 |
Message-ID: | bf81ef38c88344999f371a3619c46f3c@HYDHTCMBX34.ad.infosys.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alexandre Lessard | 2019-04-11 14:05:13 | Partitioning table on tztsrange |
Previous Message | Andreas Kretschmer | 2019-03-29 05:42:52 | Re: Archival process of partition tables with filtering few rows from tables. |