Обсуждение: execute a procedure from another procudure?

Поиск
Список
Период
Сортировка

execute a procedure from another procudure?

От
Pepe TD Vo
Дата:
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
   ----
   if v_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' );
   end if;

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

or
PERFORM 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

Re: execute a procedure from another procudure?

От
Peter Eisentraut
Дата:
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

Re: execute a procedure from another procudure?

От
Pepe TD Vo
Дата:
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