From: Pepe TD Vo <pepevo@yahoo.com>
To: Pgsql-admin <pgsql-admin@postgresql.org>
Subject: execute a procedure from another procudure?
Date: Wed, 7 Nov 2018 14:12:17 +0000 (UTC)
Message-ID: <720744224.300433.1541599937254@mail.yahoo.com> (raw)
References: <720744224.300433.1541599937254.ref@mail.yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: pepevo@yahoo.com
Subject: Re: execute a procedure from another procudure?
In-Reply-To: <720744224.300433.1541599937254@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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