returning multiple refcursors from inner function

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

Browse pgsql-sql by date

  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.