Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h9qHy-0008KO-Tu for pgsql-sql@arkaria.postgresql.org; Fri, 29 Mar 2019 12:07:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1h9qHx-0007xT-K6 for pgsql-sql@arkaria.postgresql.org; Fri, 29 Mar 2019 12:07:13 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h9mbp-00036n-22 for pgsql-sql@lists.postgresql.org; Fri, 29 Mar 2019 08:11:29 +0000 Received: from kecgate06.infosys.com ([122.98.14.33]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h9mbl-0008Tj-IX for pgsql-sql@postgresql.org; Fri, 29 Mar 2019 08:11:28 +0000 Received: from KECGATE06.infosys.com (unknown [127.0.0.1]) by IMSVA (Postfix) with ESMTP id 7FD15E6029 for ; Fri, 29 Mar 2019 13:41:08 +0530 (IST) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=infosys.com; s=default; t=1553847068; bh=2PpTCBd+X9TuTTdc9nZL/VErU6MzL9pwInJLIv80JS8=; h=From:To:Date; b=RS+Y56ZlU8rG9PATWrL0TRvjUNp5DdKe3qUJpHsjxhKqUsEOJbpFvjtXXVZzc9xdC PZjnh5hIzGJ9uruceL2aJS2DcvTrhU3/j5b8bLohwd36kzYzYFC6Y9vpHGdvwNYc1M JJdRuoUtSzENEYS4KS8YyHlj928vha5CY9Rq5LS4= Received: from KECGATE06.infosys.com (unknown [127.0.0.1]) by IMSVA (Postfix) with ESMTP id D849DE6024 for ; Fri, 29 Mar 2019 13:41:07 +0530 (IST) Received: from BLRKECHUB18.ad.infosys.com (unknown [10.66.236.41]) by KECGATE06.infosys.com (Postfix) with ESMTP for ; Fri, 29 Mar 2019 13:41:07 +0530 (IST) Received: from HYDHTCMBX34.ad.infosys.com (10.136.68.131) by BLRKECHUB18.ad.infosys.com (10.66.236.41) with Microsoft SMTP Server (TLS) id 15.0.1395.4; Fri, 29 Mar 2019 13:41:18 +0530 Received: from HYDHTCMBX34.ad.infosys.com (10.136.68.131) by HYDHTCMBX34.ad.infosys.com (10.136.68.131) with Microsoft SMTP Server (TLS) id 15.0.1395.4; Fri, 29 Mar 2019 13:41:17 +0530 Received: from HYDHTCMBX34.ad.infosys.com ([fe80::8da9:8c8d:6e1f:9e88]) by HYDHTCMBX34.ad.infosys.com ([fe80::8da9:8c8d:6e1f:9e88%18]) with mapi id 15.00.1395.000; Fri, 29 Mar 2019 13:41:17 +0530 From: Debajyoti Saha To: "pgsql-sql@postgresql.org" Subject: returning multiple refcursors from inner function Thread-Topic: returning multiple refcursors from inner function Thread-Index: AdTmBooucBbId83eR1y5G+araw1qjg== Date: Fri, 29 Mar 2019 08:11:17 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: MSIP_Label_be4b3411-284d-4d31-bd4f-bc13ef7f1fd6_Enabled=True; MSIP_Label_be4b3411-284d-4d31-bd4f-bc13ef7f1fd6_SiteId=63ce7d59-2f3e-42cd-a8cc-be764cff5eb6; MSIP_Label_be4b3411-284d-4d31-bd4f-bc13ef7f1fd6_Owner=debajyoti.saha@ad.infosys.com; MSIP_Label_be4b3411-284d-4d31-bd4f-bc13ef7f1fd6_SetDate=2019-03-29T08:11:16.9854772Z; MSIP_Label_be4b3411-284d-4d31-bd4f-bc13ef7f1fd6_Name=Internal; MSIP_Label_be4b3411-284d-4d31-bd4f-bc13ef7f1fd6_Application=Microsoft Azure Information Protection; MSIP_Label_be4b3411-284d-4d31-bd4f-bc13ef7f1fd6_Extended_MSFT_Method=Automatic; MSIP_Label_a0819fa7-4367-4500-ba88-dd630d977609_Enabled=True; MSIP_Label_a0819fa7-4367-4500-ba88-dd630d977609_SiteId=63ce7d59-2f3e-42cd-a8cc-be764cff5eb6; MSIP_Label_a0819fa7-4367-4500-ba88-dd630d977609_Owner=debajyoti.saha@ad.infosys.com; MSIP_Label_a0819fa7-4367-4500-ba88-dd630d977609_SetDate=2019-03-29T08:11:16.9854772Z; MSIP_Label_a0819fa7-4367-4500-ba88-dd630d977609_Name=Companywide usage; MSIP_Label_a0819fa7-4367-4500-ba88-dd630d977609_Application=Microsoft Azure Information Protection; MSIP_Label_a0819fa7-4367-4500-ba88-dd630d977609_Parent=be4b3411-284d-4d31-bd4f-bc13ef7f1fd6; MSIP_Label_a0819fa7-4367-4500-ba88-dd630d977609_Extended_MSFT_Method=Automatic; Sensitivity=Internal Companywide usage x-ms-exchange-transport-fromentityheader: Hosted x-originating-ip: [10.219.6.11] Content-Type: multipart/alternative; boundary="_000_bf81ef38c88344999f371a3619c46f3cHYDHTCMBX34adinfosyscom_" MIME-Version: 1.0 X-TM-AS-GCONF: 00 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_bf81ef38c88344999f371a3619c46f3cHYDHTCMBX34adinfosyscom_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 i= nner 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 :=3D 'rs1'; rs2 :=3D'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:=3D'rs1'; rs2 refcursor:=3D'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 --_000_bf81ef38c88344999f371a3619c46f3cHYDHTCMBX34adinfosyscom_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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 multi= ple refcursors and out function consuming that and returning 2 refcursors w= hich is returned from inner function.

 

How to handle that scenario please guide me

 

This is the sample functions

DROP FUNCTION optix."inner"();<= /p>

 

CREATE OR REPLACE FUNCTION optix.inner(OUT rs1 refcu= rsor, OUT rs2 refcursor)

    RETURNS record

    LANGUAGE 'plpgsql'

 

    COST 100

    VOLATILE SECURITY DEFINER

AS $BODY$

 

declare

 

BEGIN

        &nbs= p;       rs1 :=3D 'rs1';

        &nbs= p;       rs2 :=3D'rs2';

        &nbs= p;       open rs1 for

        &nbs= p;       select 'Debajyoti' as Name,

        &nbs= p;            &= nbsp;             'I= nfosys' As Company;

        &nbs= p;        

        &nbs= p;            &= nbsp;            &nb= sp;

        &nbs= p;       open rs2 for

        &nbs= p;       select 'Bharath' as Name,=

        &nbs= p;            &= nbsp;             'I= nfosys' as Company;

 

END;

 

$BODY$;

 

ALTER FUNCTION optix."inner"()<= /p>

    OWNER TO nddba;

 

 

        &nbs= p;      

        &nbs= p;      

DROP FUNCTION optix."outer"();<= /p>

 

CREATE OR REPLACE FUNCTION optix.outer(

        &nbs= p;       )

    RETURNS SETOF refcursor

    LANGUAGE 'plpgsql'

 

    COST 100

    VOLATILE SECURITY DEFINER

AS $BODY$

 

declare

rs1 refcursor:=3D'rs1';

rs2 refcursor:=3D'rs2';

begin

 

select optix.inner() into rs1, rs2;

return next rs1;

return next rs2;

        &nbs= p;      

end;

 

$BODY$;

 

ALTER FUNCTION optix."outer"()<= /p>

    OWNER TO nddba;

 

        &nbs= p;      

 

Regards,

Debajyoti Saha

--_000_bf81ef38c88344999f371a3619c46f3cHYDHTCMBX34adinfosyscom_--