Question

102
Views
vamsik8000 Member since 2018 2 posts
Amazon.com Inc
Posted: March 4, 2019
Last activity: March 7, 2019
Closed

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)

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***

Low-Code App Development Data Integration
Moderation Team has archived post
Share this page LinkedIn