Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gKOZI-0003OF-6W for pgsql-admin@arkaria.postgresql.org; Wed, 07 Nov 2018 14:12:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gKOZG-0000Ox-9n for pgsql-admin@arkaria.postgresql.org; Wed, 07 Nov 2018 14:12:26 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gKOZF-0000N2-CV for pgsql-admin@lists.postgresql.org; Wed, 07 Nov 2018 14:12:26 +0000 Received: from sonic313-13.consmr.mail.bf2.yahoo.com ([74.6.133.123]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gKOZB-000209-VH for pgsql-admin@postgresql.org; Wed, 07 Nov 2018 14:12:24 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1541599940; bh=6ngi/CgHUxEy3W/0N+t1e8KccfINDvLyHHJTKhaz1v8=; h=Date:From:Reply-To:To:Subject:References:From:Subject; b=UHiffcecB+NS08RmfyKrSVxudHg+4Te7GTEiWp+6lxvWYWA6OO+Q9xUKKr8lgSIVjCVIZOwZOhITrHE54HdVRTFsYtAikFC+nNLEJh4lCEaFxSQtqVdt7524KIMro1RBOtosOFdfhaLZS/5xV2R3ckbAjp8bpue9k4GANRZ4JiB+E0onNjPr1jKh+MkrGtpGCHC/P+wkL174+TB1yauiCK+xank+eNz51V5zfIWRppNlAcwJEGIxUqEdl0eh9bEs+AuHF3yfpR3lOoipeFGOZq15kiXcTnUjHYBx8yWUKmEXpRdou+2BGaW1ea2ldyvjAuKJMExdF6bVE17HZbua+Q== X-YMail-OSG: fRPZovMVM1mA.Du7oR8A4UkXcZVbqv86t5Ts4hvA12QeBMMjkWy2oYGPsZJzQpT CTe10fPRjwasU6o0lwantj6MIZfIwogMYqqpdUZ8UHDS8Ib8uJMuuIACLfrbAo_DIJyFNcP.p6rS 8rlExiroGddkCQm_vqPgldRpqFxtfuX.e79Z0ES6b1LbVd_qn4pE6NtQeDsVJNxMmUkiPed8wCZj _p1oCJHKuMMcFmpUaagEHimA9e71trI6.gfIkIq6EXtInZ9W1UpyYAnpDdMYDQ66C3xkW6nWuJ_D 6565KTuWr_sIuW7OT996GaRKuEnfe6moknSgLGgWeMe6EtQQABl2d8M8nDLuf2R45eOo1EVX2i1B rA4QYpV2e1csKfuSmUXrmltKuWZMa6d64wzRG2u4RhQjxCaIzQeVA9MWOPpr_qxhXcIbEySGk.Ir NlXCiEigWMVHZchlTv8bKnux7VNKIX_hcd5BSD0yPz.TYrLAGUsmCd5ybHJfFtlnf8qPb.tMRJRh KklTT157zCDrJm1Ba2jz4g7PNBu92myCZrmhdxgI5c3IbPeiQySiWGueZhDcYcPWHnj9KC3i9wYw 7pm3Ro6PV1uOTPB5HY63bdPmLxXpVpsYYHKtm8wIbGZkpMDr63z1tU.KVzIL755TTaWzdFqx59xB .sAmkFi.rnqraNSYpJTDPYD9Ti356NGCDt47dLmsR5z1bqWXlBtKS1RO_uB3xN1QACay7kFT4Kmd omIlrE0BFcPQ5tt0ACAqMTwA5__sYijHdbQeqNMAIYwNrbNEUu3ishLVzI8UjQvg_sd0.AajO5So jrpYTgrxhnc_BiuuLdEUp1deNF03_aPDl_0wpTnRWCGBy5wMHonjzNWp5G6btpYHRiMJT5uGvvzn 8V2hqERzE2fXZQHUDLvPALw2gCLLdvafkKE.KtRx7YhIQkSZNpwfxfHj9freblpPN0lFIdhoDLq_ .cEh4CiD6aUdBBLGJ5E0CqO0uVWHvwj0TLxP6ZambIB7Z9sw1ftbqCNDwgkc- Received: from sonic.gate.mail.ne1.yahoo.com by sonic313.consmr.mail.bf2.yahoo.com with HTTP; Wed, 7 Nov 2018 14:12:20 +0000 Date: Wed, 7 Nov 2018 14:12:17 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: Pgsql-admin Message-ID: <720744224.300433.1541599937254@mail.yahoo.com> Subject: execute a procedure from another procudure? MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_300432_1756289491.1541599937245" References: <720744224.300433.1541599937254.ref@mail.yahoo.com> X-Mailer: WebService/1.1.12710 YahooMailNeo Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.100 Safari/537.36 Content-Length: 44707 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_300432_1756289491.1541599937245 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hello, Most of my posts were deleted and I don't know and actually, I'm not sure w= here and how to post a new question.=C2=A0 =C2=A0 Someone told me to join t= he pssql group and I received so many email post and reply from different p= osts 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 no= t 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=C2=A0 =C2=A0 =C2=A0 =C2=A0 PRAGMA AUTONOMOUS_TRANSACTION;begin=C2=A0 = =C2=A0insert into cidrmgmt.errorlog(=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0tstamp, os_user,host,mo= dule,errorcode,errormsg)=C2=A0 =C2=A0 =C2=A0 =C2=A0 values=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 (sysdate, v_os_user, v_host, v_modul= e, v_ErrorCode, v_ErrorMsg );=C2=A0 =C2=A0 =C2=A0 =C2=A0 commit;end;/ and another procedure to call transaction procedure:CREATE OR REPLACE EDITI= ONABLE PROCEDURE "CIDR_STAGING"."PR_MIG_STG_FORMS" ( v_Ret OUT number )as= =C2=A0 =C2=A0 =C2=A0 =C2=A0 v_ErrorCode=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0number;=C2=A0 =C2=A0 =C2=A0 =C2=A0 v_ErrorMsg=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 varchar2(512);=C2=A0 =C2=A0 =C2=A0 =C2=A0 v= _Module=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 varchar2(32) :=3D 'PR_MIG_STG_FORMS';begin =C2=A0 =C2=A0 =C2=A0 =C2=A0 ----=C2=A0 =C2=A0 =C2=A0 =C2=A0 -- Simply delet= e the data from production table=C2=A0 =C2=A0 =C2=A0 =C2=A0 ----=C2=A0 =C2= =A0 =C2=A0 =C2=A0 delete from cidrdba.ref_forms where 1=3D1; =C2=A0 =C2=A0 =C2=A0 =C2=A0 ----=C2=A0 =C2=A0 =C2=A0 =C2=A0 -- Simply copy = the data from staging into production=C2=A0 =C2=A0 =C2=A0 =C2=A0 ----=C2=A0= =C2=A0 =C2=A0 =C2=A0 insert into cidrdba.ref_forms(=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0form_number, form_title, mig_filename=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 )=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 select form_number, form_title, mig_filename=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 from cidr_staging.stg_ref_for= ms;=C2=A0 =C2=A0 =C2=A0 =C2=A0 ----=C2=A0 =C2=A0 =C2=A0 =C2=A0 -- Set the r= eturn code to 0=C2=A0 =C2=A0 =C2=A0 =C2=A0 ----=C2=A0 =C2=A0 =C2=A0 =C2=A0 = v_Ret :=3D SQLCODE; =C2=A0 =C2=A0pr_write_error_log( sys_context('userenv','session_user'), =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0sys_context('userenv','host'), v_Module, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A00, 'Starting MERGE Process' ); =C2=A0 =C2=A0=C2=A0v_Ret :=3D 0; =C2=A0 =C2=A0=C2=A0 ---- =C2=A0=C2=A0 ---- =C2=A0=C2=A0 --STG_REF_ACTION_CODES =C2=A0=C2=A0 ---- =C2=A0=C2=A0 ifv_Ret =3D 0 then =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0---- =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-- Perform the merge on REF_ACTION_CODES =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0---- =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0pr_write_error_log( sys_context('userenv','se= ssion_user'), =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0sys_context('userenv','host= '), v_Module, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A00, 'Started merging STG_REF= _ACTION_CODES' ); =C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0cidr_staging.pr_mig_stg_action_codes( v_Ret )= ; =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0v_Ret :=3D SQLCODE; =C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0pr_write_error_log( sys_context('userenv','se= ssion_user'), =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0sys_context('userenv','host= '), v_Module, =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A00, 'Finished merging STG_RE= F_ACTION_CODES' ); =C2=A0=C2=A0 endif; ------ Exception error handler----exception=C2=A0 =C2=A0 =C2=A0 =C2=A0 when= others then=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 v_Error= Code :=3D SQLCODE;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 v= _ErrorMsg=C2=A0 :=3D SQLERRM;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 v_Ret=C2=A0 =C2=A0 =C2=A0 =C2=A0:=3D v_ErrorCode; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ----=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -- Commit the record into the Err= orLog=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ----=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 pr_write_error_log( sys_conte= xt('userenv','session_user'),=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 sys_context('userenv','host'), v_Module,=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0v_ErrorCode, v_ErrorMsg );=C2=A0 =C2=A0 =C2=A0 =C2=A0 ----=C2=A0 =C2= =A0 =C2=A0 =C2=A0 -- Intentionally leaving the "commit" to application=C2= =A0 =C2=A0 =C2=A0 =C2=A0 ----end;/ and I converted it to Postgres Pragma autonomous_transaction and I'm not su= re 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=C2=A0varchar(4000), v_module IN varchar(4000), v_errorcode IN = int, v_errormsg IN varchar(4000) )=C2=A0=C2=A0RETURNS VOID=C2=A0as $$ BEGIN START TRANSACTION;=C2=A0 =C2=A0insert into cidrmgmt.errorlog(=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0tstamp, os_user,host,module,errorcode,errormsg)=C2=A0 =C2=A0 =C2= =A0 =C2=A0 values=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 (c= urrent_timestamp, v_os_user, v_host, v_module, v_ErrorCode, v_ErrorMsg ); =C2=A0 =C2=A0 =C2=A0 =C2=A0 /* commit; */ end;$$ LANGUAGE plpgsql;=C2=A0 CREATE OR REPLACE FUNCTION "CIDR_STAGING"."PR_MIG_STG_FORMS" ( v_Ret OUT in= t ) RETURNS integeras $$declare=C2=A0 =C2=A0 =C2=A0 =C2=A0 v_ErrorCode=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0int;=C2=A0 =C2=A0 =C2=A0 =C2= =A0 v_ErrorMsg=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 varchar(512)= ;=C2=A0 =C2=A0 =C2=A0 =C2=A0 v_Module=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 varchar(32) =3D 'PR_MIG_STG_FORMS'; begin=C2=A0 =C2=A0 =C2=A0 =C2=A0 ----=C2=A0 =C2=A0 =C2=A0 =C2=A0 -- Simply = delete the data from production table=C2=A0 =C2=A0 =C2=A0 =C2=A0 ----=C2=A0= =C2=A0 =C2=A0 =C2=A0 delete from cidrdba.ref_forms where 1=3D1; =C2=A0 =C2=A0 =C2=A0 =C2=A0 ----=C2=A0 =C2=A0 =C2=A0 =C2=A0 -- Simply copy = the data from staging into production=C2=A0 =C2=A0 =C2=A0 =C2=A0 ----=C2=A0= =C2=A0 =C2=A0 =C2=A0 insert into cidrdba.ref_forms(=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0form_number, form_title, mig_filename=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 )=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 select form_number, form_title, mig_filename=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 from cidr_staging.stg_ref_for= ms;=C2=A0 =C2=A0 =C2=A0 =C2=A0 ----=C2=A0 =C2=A0 =C2=A0 =C2=A0 -- Set the r= eturn code to 0=C2=A0 =C2=A0 =C2=A0 =C2=A0 ----=C2=A0 =C2=A0 =C2=A0 =C2=A0 = v_Ret :=3D SQLCODE; =C2=A0 =C2=A0RAISE INFO 'calling "pr_write_error_log"(%)',( sys_context('us= erenv','session_user'),=C2=A0 =C2=A0 =C2=A0 sys_context('userenv','host'), = v_Module,=C2=A0 =C2=A0 =C2=A0 0, 'Starting MERGE Process' ); =C2=A0 =C2=A0v_Ret :=3D 0; =C2=A0 =C2=A0----=C2=A0 =C2=A0----=C2=A0 =C2=A0-- STG_REF_ACTION_CODES=C2= =A0 =C2=A0----=C2=A0 =C2=A0if v_Ret =3D 0 then=C2=A0 =C2=A0 =C2=A0 ----=C2= =A0 =C2=A0 =C2=A0 -- Perform the merge on REF_ACTION_CODES=C2=A0 =C2=A0 =C2= =A0 ----=C2=A0 =C2=A0 =C2=A0 RAISE INFO 'calling "pr_write_error_log"(%)',(= sys_context('userenv','session_user'),=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0sy= s_context('userenv','host'), v_Module,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00, = 'Started merging STG_REF_ACTION_CODES' ); or? PERFORM pr_write_error_log( sys_context('userenv','session_user'),=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 sys_context(= 'userenv','host'), v_Module,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0v_ErrorCode, v_ErrorMsg ); =C2=A0 =C2=A0 =C2=A0 PERFORM cidr_staging.pr_mig_stg_action_codes( v_Ret );= =C2=A0 --> should I use RAISE INFO to call this procedure too?=C2=A0 =C2=A0= =C2=A0 v_Ret :=3D SQLCODE; =C2=A0 =C2=A0 =C2=A0 RAISE INFO 'calling "pr_write_error_log"(%)',( sys_con= text('userenv','session_user'),=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0sys_contex= t('userenv','host'), v_Module,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00, 'Finishe= d merging STG_REF_ACTION_CODES' );=C2=A0 =C2=A0end if; or? PERFORM pr_write_error_log( sys_context('userenv','session_user'),=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 sys_context(= 'userenv','host'), v_Module,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0v_ErrorCode, v_ErrorMsg ); ------ Exception error handler----exception=C2=A0 =C2=A0 =C2=A0 =C2=A0 when= others then=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 v_Error= Code :=3D SQLCODE;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 v= _ErrorMsg=C2=A0 :=3D SQLERRM;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 v_Ret=C2=A0 =C2=A0 =C2=A0 =C2=A0:=3D v_ErrorCode;=C2=A0=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ----=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -- Commit the record into the ErrorLog= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ----RAISE NOTICE 'C= alling "CIDR_STAGING"."PR_WRITE_ERROR_LOG"(%)', ( sys_context('userenv','se= ssion_user'),=C2=A0 =C2=A0 =C2=A0 =C2=A0 sys_context('userenv','host'), v_M= odule,=C2=A0 =C2=A0 =C2=A0 =C2=A0 v_ErrorCode, v_ErrorMsg ); orPERFORM pr_write_error_log( sys_context('userenv','session_user'),=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 sys_context(= 'userenv','host'), v_Module,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0v_ErrorCode, v_ErrorMsg );=C2= =A0 =C2=A0 =C2=A0 =C2=A0 --=C2=A0 =C2=A0 =C2=A0 =C2=A0 -- Intentionally lea= ving the "commit" to application=C2=A0 =C2=A0 =C2=A0 =C2=A0 ----end;$$ LANG= UAGE plpgsql; and how should I execute another function within the function like Oracle?= =C2=A0 I have searched and found parameters raise log, raise info, raise be= fore and perform.=C2=A0 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. =C2=A0v/r, Bach-Nga No one in this world is pure and perfect.=C2=A0 If you avoid people for the= ir 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 t= o 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 ------=_Part_300432_1756289491.1541599937245 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
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 pgs= ql-admin for new question.

Please= let me know if this is not a right place to do since the forum is not as s= ame as oracle and/or mysql.


<= /div>
I have script in Or= acle procedure as Pragma autonomous_transaction:

CREATE OR REP= LACE EDITIONABLE PROCEDURE "CIDR_STAGING"."PR_WRITE_ERROR_LOG" is
        = PRAGMA AUTONOMOUS_TRANSACTION;
begin
&= nbsp;  insert into cidrmgmt.errorlog(
              &n= bsp;          tstamp, os_user,host,module,errorcod= e,errormsg)
  =       values
                (sysdate, = v_os_user, v_host, v_module, v_ErrorCode, v_ErrorMsg );
        commit;
end;
/

and another procedur= e to call transaction procedure:
CREATE OR REPLACE EDITIONABLE PROCEDURE "CIDR_STAGING"."PR_MI= G_STG_FORMS" ( v_Ret OUT number )
as
&= nbsp;       v_ErrorCode          &n= bsp;  number;
=         v_ErrorMsg          &n= bsp;   varchar2(512);
        v_Module        &nb= sp;               varchar2(32) :=3D 'PR_= MIG_STG_FORMS';
beg= in

        ----
        -- Simply delete = the data from production table
        ----
        delete from cidrdba.r= ef_forms where 1=3D1;

        ----
       = ; -- Simply copy the data from staging into production
        ----
<= div id=3D"yui_3_16_0_ym19_1_1541536740079_173062">      &nbs= p; insert into cidrdba.ref_forms(
           form_number, form_t= itle, mig_filename
=                 )
          &= nbsp;     select form_number, form_title, mig_filename
        &= nbsp;       from cidr_staging.stg_ref_forms;
        ----
     =   -- Set the return code to 0
        ----
        v_Ret :=3D SQLCO= DE;

   pr_write_error_log( sys_context('userenv','= session_user'),
 &= nbsp;    sys_context('userenv','host'), v_Module,
      0, 'Starting MERGE Process' );
 
 &n= bsp; v_Ret :=3D 0;
 
   ----
   ----
   -- STG_REF_ACTION_CODES
&n= bsp;  ----
 &= nbsp; if v_Ret =3D 0 then
 =      ----=
   =    -- Perform the merge on REF_ACTION_CODES
      ----=
   =    pr_write_error_log( sys_context('userenv','session_user'),
      &nb= sp;  sys_context('userenv','host'), v_Module,
         0, 'Started merging STG_REF_ACTION_CODES' );
<= span style=3D"font-family:"Courier New"" id=3D"yui_3_16_0_ym19_1_= 1541536740079_177511">&n= bsp;
      cidr_staging.pr_mig_stg_action_codes( v_Ret );
      v_Ret :=3D SQLCODE;
 <= /div>
   &= nbsp;  pr_write_error_log( sys_context('userenv','session_user'),
      &nb= sp;  sys_context('userenv','host'), v_Module,
         0, 'Finished merging STG_REF_ACTION_CODES' );
 =   end if;<= /div>

----
-- Exception error handler
----
exception
        when others then
        &nb= sp;       v_ErrorCode :=3D SQLCODE;
            &= nbsp;   v_ErrorMsg  :=3D SQLERRM;
              &= nbsp; v_Ret       :=3D v_ErrorCode;

 =               ----
          &nbs= p;     -- Commit the record into the ErrorLog
          &nb= sp;     ----
                pr_write_error_= log( sys_context('userenv','session_user'),
              &= nbsp;     sys_context('userenv','host'), v_Module,
        &nb= sp;                v_ErrorCode, v_E= rrorMsg );
  &= nbsp;     ----
        -- 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_ho= st IN
 varchar= (4000), v_module IN varchar(4000), v_errorcode IN int, v_errormsg IN varcha= r(4000) ) 
&nb= sp;RETURNS VOID 
as $$

BEGIN
=09START TRANSACTION;
   insert into cidrmgmt.errorlog(
      &= nbsp;                  tstamp,= os_user,host,module,errorcode,errormsg)
        values
          &n= bsp;     (current_timestamp, v_os_user, v_host, v_module, v_Error= Code, v_ErrorMsg );

        /* commit; */<= /div>
=09=09
end;
$$ LANGUAGE plpgsql;
 

CREATE = OR REPLACE FUNCTION "CIDR_STAGING"."PR_MIG_STG_FORMS" ( v_Ret OUT int ) RET= URNS integer
as $$<= /div>
declare
        v_= ErrorCode             int;
        v_E= rrorMsg              varchar(512);
=
      &nb= sp; v_Module                varchar= (32) =3D 'PR_MIG_STG_FORMS';

begin
        ----
        --= Simply delete the data from production table
        ----
        delete= from cidrdba.ref_forms where 1=3D1;

<= div id=3D"yui_3_16_0_ym19_1_1541536740079_173137">      &nbs= p; ----
  &nbs= p;     -- Simply copy the data from staging into production
=
      &nb= sp; ----
  &nb= sp;     insert into cidrdba.ref_forms(
           form= _number, form_title, mig_filename
                )
      =           select form_number, form_title, mig_file= name
    =             from cidr_staging.stg_ref_forms;<= /div>
     = ;   ----
 = ;       -- Set the return code to 0
        ----
        v= _Ret :=3D SQLCODE;
=
   RAISE INFO 'calling "pr_write_= error_log"(%)',( sys_context('userenv','session_user'),
      sys_context('user= env','host'), v_Module,
      0, 'Starting MERGE Process' );

 = ;  v_Ret :=3D 0;

   ----
   ----
   -- STG_REF_ACTION_CODES
=
   ----
<= div id=3D"yui_3_16_0_ym19_1_1541536740079_175285">   if v_Ret =3D= 0 then
  &nbs= p;   ----
&nbs= p;     -- Perform the merge on REF_ACTION_CODES
      ----
      RAISE INFO 'c= alling "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,
  &nb= sp;                     &= nbsp;v_ErrorCode, v_ErrorMsg );

      PERFORM = cidr_staging.pr_mig_stg_action_codes( v_Ret );  --> should I use RA= ISE INFO to call this procedure too?
      v_Ret :=3D SQLCODE;

      RAISE INFO 'calling "pr_wr= ite_error_log"(%)',( sys_context('userenv','session_user'),
&nb= sp;        sys_context('userenv','host'), v_Module,
     =    0, 'Finished merging STG_REF_ACTION_CODES' );
   end if;

or?

PERFORM pr_write_error_log( sys_context('useren= v','session_user'),
                    sy= s_context('userenv','host'), v_Module,
           =              v_ErrorCode, v_ErrorMsg );=

----
-- Exception error handler
----
ex= ception
  &nbs= p;     when others then
                v_Er= rorCode :=3D SQLCODE;
                v_ErrorMsg = :=3D SQLERRM;
&nbs= p;               v_Ret    &nbs= p;  :=3D v_ErrorCode;
 
&nbs= p;               ----
          &n= bsp;     -- Commit the record into the ErrorLog
          &= nbsp;     ----
RAISE NOTICE 'Calling "CIDR_STAGING"."PR_WRITE_ERROR_LOG"(%)', ( sys_conte= xt('userenv','session_user'),
        sys_c= ontext('userenv','host'), v_Module,
       = v_ErrorCode, v_ErrorMsg );

or
PERFORM pr_write_error_log( sys_context('userenv= ','session_user'),
                    sys= _context('userenv','host'), v_Module,
               = ;          v_ErrorCode, v_ErrorMsg );
=
      &nb= sp; --
   = ;     -- Intentionally leaving the "commit" to application
<= div id=3D"yui_3_16_0_ym19_1_1541536740079_173173">      &nbs= p; ----
end;
<= div id=3D"yui_3_16_0_ym19_1_1541536740079_173175">$$ 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 functi= on w/in a function like Oracle would you please explain it?

th= ank you for all your help.

 v/r,
Bach-Nga

No one in this world is pure and perfect.  If you av= oid people for their mistakes you will be alone. So judge less, love and fo= rgive 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 wel= l, he was a perfect gentleman (Hermione Gingold)
=
**Live simply **Love generously **Care deeply *= *Speak kindly.
*** Genuinely rich *** Faithful talent *** Sh= aring success
------=_Part_300432_1756289491.1541599937245--