pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedexecute a procedure from another procudure?
3+ messages / 2 participants
[nested] [flat]
* execute a procedure from another procudure?
@ 2018-11-07 14:12 Pepe TD Vo <pepevo@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Pepe TD Vo @ 2018-11-07 14:12 UTC (permalink / raw)
To: pgsql-admin
Hello,
Most of my posts were deleted and I don't know and actually, I'm not sure where and how to post a new question. Someone told me to join the pssql group and I received so many email post and reply from different posts and/or email pgsql-admin for new question.
Please let me know if this is not a right place to do since the forum is not as same as oracle and/or mysql.
I have script in Oracle procedure as Pragma autonomous_transaction:
CREATE OR REPLACE EDITIONABLE PROCEDURE "CIDR_STAGING"."PR_WRITE_ERROR_LOG" is PRAGMA AUTONOMOUS_TRANSACTION;begin insert into cidrmgmt.errorlog( tstamp, os_user,host,module,errorcode,errormsg) values (sysdate, v_os_user, v_host, v_module, v_ErrorCode, v_ErrorMsg ); commit;end;/
and another procedure to call transaction procedure:CREATE OR REPLACE EDITIONABLE PROCEDURE "CIDR_STAGING"."PR_MIG_STG_FORMS" ( v_Ret OUT number )as v_ErrorCode number; v_ErrorMsg varchar2(512); v_Module varchar2(32) := 'PR_MIG_STG_FORMS';begin
---- -- Simply delete the data from production table ---- delete from cidrdba.ref_forms where 1=1;
---- -- Simply copy the data from staging into production ---- insert into cidrdba.ref_forms( form_number, form_title, mig_filename ) select form_number, form_title, mig_filename from cidr_staging.stg_ref_forms; ---- -- Set the return code to 0 ---- v_Ret := SQLCODE;
pr_write_error_log( sys_context('userenv','session_user'),
sys_context('userenv','host'), v_Module,
0, 'Starting MERGE Process' );
v_Ret := 0;
----
----
--STG_REF_ACTION_CODES
----
ifv_Ret = 0 then
----
-- Perform the merge on REF_ACTION_CODES
----
pr_write_error_log( sys_context('userenv','session_user'),
sys_context('userenv','host'), v_Module,
0, 'Started merging STG_REF_ACTION_CODES' );
cidr_staging.pr_mig_stg_action_codes( v_Ret );
v_Ret := SQLCODE;
pr_write_error_log( sys_context('userenv','session_user'),
sys_context('userenv','host'), v_Module,
0, 'Finished merging STG_REF_ACTION_CODES' );
endif;
------ Exception error handler----exception when others then v_ErrorCode := SQLCODE; v_ErrorMsg := SQLERRM; v_Ret := v_ErrorCode;
---- -- Commit the record into the ErrorLog ---- pr_write_error_log( sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, v_ErrorCode, v_ErrorMsg ); ---- -- Intentionally leaving the "commit" to application ----end;/
and I converted it to Postgres Pragma autonomous_transaction and I'm not sure its corrected but it didn't give any error.
create or replace FUNCTION "PR_WRITE_ERROR_LOG" ( v_os_user IN varchar(4000), v_host IN varchar(4000), v_module IN varchar(4000), v_errorcode IN int, v_errormsg IN varchar(4000) ) RETURNS VOID as $$
BEGIN START TRANSACTION; insert into cidrmgmt.errorlog( tstamp, os_user,host,module,errorcode,errormsg) values (current_timestamp, v_os_user, v_host, v_module, v_ErrorCode, v_ErrorMsg );
/* commit; */ end;$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION "CIDR_STAGING"."PR_MIG_STG_FORMS" ( v_Ret OUT int ) RETURNS integeras $$declare v_ErrorCode int; v_ErrorMsg varchar(512); v_Module varchar(32) = 'PR_MIG_STG_FORMS';
begin ---- -- Simply delete the data from production table ---- delete from cidrdba.ref_forms where 1=1;
---- -- Simply copy the data from staging into production ---- insert into cidrdba.ref_forms( form_number, form_title, mig_filename ) select form_number, form_title, mig_filename from cidr_staging.stg_ref_forms; ---- -- Set the return code to 0 ---- v_Ret := SQLCODE;
RAISE INFO 'calling "pr_write_error_log"(%)',( sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Starting MERGE Process' );
v_Ret := 0;
---- ---- -- STG_REF_ACTION_CODES ---- if v_Ret = 0 then ---- -- Perform the merge on REF_ACTION_CODES ---- RAISE INFO 'calling "pr_write_error_log"(%)',( sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Started merging STG_REF_ACTION_CODES' );
or?
PERFORM pr_write_error_log( sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, v_ErrorCode, v_ErrorMsg );
PERFORM cidr_staging.pr_mig_stg_action_codes( v_Ret ); --> should I use RAISE INFO to call this procedure too? v_Ret := SQLCODE;
RAISE INFO 'calling "pr_write_error_log"(%)',( sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, 0, 'Finished merging STG_REF_ACTION_CODES' ); end if;
or?
PERFORM pr_write_error_log( sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, v_ErrorCode, v_ErrorMsg );
------ Exception error handler----exception when others then v_ErrorCode := SQLCODE; v_ErrorMsg := SQLERRM; v_Ret := v_ErrorCode; ---- -- Commit the record into the ErrorLog ----RAISE NOTICE 'Calling "CIDR_STAGING"."PR_WRITE_ERROR_LOG"(%)', ( sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, v_ErrorCode, v_ErrorMsg );
orPERFORM pr_write_error_log( sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, v_ErrorCode, v_ErrorMsg ); -- -- Intentionally leaving the "commit" to application ----end;$$ LANGUAGE plpgsql;
and how should I execute another function within the function like Oracle? I have searched and found parameters raise log, raise info, raise before and perform. I want to understand how to call the function w/in a function like Oracle would you please explain it?
thank you for all your help.
v/r,
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: execute a procedure from another procudure?
@ 2018-11-08 08:59 Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
parent: Pepe TD Vo <pepevo@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Peter Eisentraut @ 2018-11-08 08:59 UTC (permalink / raw)
To: Pepe TD Vo <pepevo@yahoo.com>; pgsql-admin
On 07/11/2018 15:12, Pepe TD Vo wrote:
> and I converted it to Postgres Pragma autonomous_transaction and I'm not
> sure its corrected but it didn't give any error.
PostgreSQL does not support autonomous transactions. Some people use
dblink as a workaround.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: execute a procedure from another procudure?
@ 2018-11-08 20:13 Pepe TD Vo <pepevo@yahoo.com>
parent: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
0 siblings, 0 replies; 3+ messages in thread
From: Pepe TD Vo @ 2018-11-08 20:13 UTC (permalink / raw)
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; pgsql-admin
I worked around with the transaction and it gives me no error and I assumed it works.
create or replace FUNCTION "CIDR_STAGING"."PR_WRITE_ERROR_LOG" ( v_os_user IN varchar(4000), v_host IN varchar(4000), v_module IN varchar(4000), v_errorcode IN int, v_errormsg IN varchar(4000) ) RETURNS VOID as $$
BEGIN START TRANSACTION; insert into cidrmgmt.errorlog( tstamp, os_user,host,module,errorcode,errormsg) values (current_timestamp, v_os_user, v_host, v_module, v_ErrorCode, v_ErrorMsg );
/* commit; */ end;$$ LANGUAGE plpgsql;
CREATE FUNCTION
Query returned successfully in 107 msec.
thank you all.
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
On Thursday, November 8, 2018 4:00 AM, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote:
On 07/11/2018 15:12, Pepe TD Vo wrote:
> and I converted it to Postgres Pragma autonomous_transaction and I'm not
> sure its corrected but it didn't give any error.
PostgreSQL does not support autonomous transactions. Some people use
dblink as a workaround.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2018-11-08 20:13 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-11-07 14:12 execute a procedure from another procudure? Pepe TD Vo <pepevo@yahoo.com>
2018-11-08 08:59 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2018-11-08 20:13 ` Pepe TD Vo <pepevo@yahoo.com>
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