From: Debajyoti Saha <debajyoti.saha@infosys.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: returning multiple refcursors from inner function
Date: Fri, 29 Mar 2019 08:11:17 +0000
Message-ID: <bf81ef38c88344999f371a3619c46f3c@HYDHTCMBX34.ad.infosys.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: debajyoti.saha@infosys.com
Subject: Re: returning multiple refcursors from inner function
In-Reply-To: <bf81ef38c88344999f371a3619c46f3c@HYDHTCMBX34.ad.infosys.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox