Question
Amazon.com Inc
US
Last activity: 7 Mar 2019 15:13 EST
Calling Postgres stored proc with OUT params
How to call postgres stored proc (which has multiple out params ) from Pega.
Stored proc signature:
CREATE OR REPLACE FUNCTION Test_postgres_SP(P_arg1 text, P_arg2 text, OUT p_out_arg_1 text, OUT p_out_arg_2 text, OUT p_out_arg_3 text)
RETURNS RECORD
LANGUAGE plpgsql
AS
$body$
BEGIN
--Do some logic here
p_out_arg_1 :='true';
p_out_arg_2 :='test123';
p_out_arg_3 :='test456';
END;
$body$
VOLATILE
COST 100;
COMMIT;
In Pega :
Create connect -SQL RULE
with class : as per your requirement
PackageName: Postgres
in the rule in Open TAB:
{SQLPage:TempConnectSQLPage}
Select p_out_arg_1 AS "TempConnectSQLPage.testOutParam1" ,p_out_arg_2 AS "TempConnectSQLPage.testOutParam2 ",p_out_arg_3 AS "TempConnectSQLPage.testOutParam3" from garc.getnext_task_wrapper( {TempConnectSQLPage.testParam1},{TempConnectSQLPage.testParam2})
Save the rule
in the calling activity
TestPostgresSP
Pages & Classes
TempConnectSQLPage : with class what ever you want but input and out params are set on this page.
RDBTestPage : with class on which the Connect-SQL rule is present. (When you call connect-SQL rule it resets the page on which it is called. So do not use the same page on which you are setting input params and expecting out put params. Check the RDB-open Page below)
How to call postgres stored proc (which has multiple out params ) from Pega.
Stored proc signature:
CREATE OR REPLACE FUNCTION Test_postgres_SP(P_arg1 text, P_arg2 text, OUT p_out_arg_1 text, OUT p_out_arg_2 text, OUT p_out_arg_3 text)
RETURNS RECORD
LANGUAGE plpgsql
AS
$body$
BEGIN
--Do some logic here
p_out_arg_1 :='true';
p_out_arg_2 :='test123';
p_out_arg_3 :='test456';
END;
$body$
VOLATILE
COST 100;
COMMIT;
In Pega :
Create connect -SQL RULE
with class : as per your requirement
PackageName: Postgres
in the rule in Open TAB:
{SQLPage:TempConnectSQLPage}
Select p_out_arg_1 AS "TempConnectSQLPage.testOutParam1" ,p_out_arg_2 AS "TempConnectSQLPage.testOutParam2 ",p_out_arg_3 AS "TempConnectSQLPage.testOutParam3" from garc.getnext_task_wrapper( {TempConnectSQLPage.testParam1},{TempConnectSQLPage.testParam2})
Save the rule
in the calling activity
TestPostgresSP
Pages & Classes
TempConnectSQLPage : with class what ever you want but input and out params are set on this page.
RDBTestPage : with class on which the Connect-SQL rule is present. (When you call connect-SQL rule it resets the page on which it is called. So do not use the same page on which you are setting input params and expecting out put params. Check the RDB-open Page below)
Step1: Page-New : TempConnectSQLPage
Step2: Property-Set : TempConnectSQLPage.testParam1 && TempConnectSQLPage.testParam2
Step3: RDB-open Step Page : RDBTestPage (use different page here, as this page will be reset on this step)
Pass the args (standard way)
Step 4 : Get the values from the TempConnectSQLPage like Property-Set
***Edited by Moderator Marissa to update platform capability tags; update Content Type from Discussion to Question***