execute a procedure from another procudure? - Mailing list 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;

----

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

as $$

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'),

or?

      PERFORM cidr_staging.pr_mig_stg_action_codes( v_Ret );  --> should I use RAISE INFO to call this procedure too?

      v_Ret := SQLCODE;

or?

----

-- Exception error handler

----

exception

        when others then

                v_ErrorCode := SQLCODE;

                v_ErrorMsg  := SQLERRM;

                v_Ret       := v_ErrorCode;

 

                ----

                -- Commit the record into the ErrorLog

                ----

or

        --

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