Обсуждение: [GENERAL] How to get an exception detail in a function called in exception handler

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

[GENERAL] How to get an exception detail in a function called in exception handler

От
Shakti Singh
Дата:
Hello,

I am porting Oracle to PostgreSQL.

In oracle sqlcode and sqlerrm can be accessed in a function called from an exception block.
How do I do this in PostgreSQL

For example:

How do I get exception details in function "myschema"."testerror" () in function  "myschema"."logerror"().
I understand that GET STACKED DIAGNOSTICS does not work here, but is there any way to achieve this?

This is a function that will always generate an error (since columnName does not exist in table)


CREATE OR REPLACE FUNCTION  "myschema"."testerror" ()
  RETURNS void  AS  $$
 DECLARE  
 BEGIN
     -- source data
       select sirv.columnName
         from "myschema"."tableName" sirv;

      
 EXCEPTION
      WHEN OTHERS THEN
      -- log exception details like SQLERRM, SQLSTATE from function "myschema"."logerror"()
        PERFORM "myschema"."logerror"();
                          
END;  $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION  "myschema"."logerror" ()
  RETURNS void  AS  $$
 DECLARE  
     the_sqlcode int := 0;
     the_sqlerrormessage varchar ;
 BEGIN
  GET STACKED DIAGNOSTICS the_sqlerrormessage = MESSAGE_TEXT,
                          the_sqlcode = RETURNED_SQLSTATE,

   INSERT into "myschema"."error_trace"( 
                            errorCode,
                            error_messaage)
                  VALUES (
                           the_sqlcode,
                           the_sqlerrormessage);
 
                          
END;  $$ LANGUAGE plpgsql;

Thanks,

Shakti Singh

Re: [GENERAL] How to get an exception detail in a function called in exception handler

От
Tom Lane
Дата:
Shakti Singh <shakti.0123@gmail.com> writes:
> In oracle sqlcode and sqlerrm can be accessed in a function called from an
> exception block.
> How do I do this in PostgreSQL

In PG those are local variables within an exception block.  You'd have to
pass their values to the error-logging function explicitly.

            regards, tom lane

Re: [GENERAL] How to get an exception detail in a function called inexception handler

От
Shakti Singh
Дата:
Thanks for the reply Tom!

The log_error_function is being called by thousands of functions and that is why I thought it would be great if there was a way without making changes to it and subsequently writing code to pass the parameters in all these exception block.

Would have been awesome if error logging function could get the previous exception details somehow.

Thanks,


Shakti Singh


On Tue, Jan 24, 2017 at 11:13 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Shakti Singh <shakti.0123@gmail.com> writes:
> In oracle sqlcode and sqlerrm can be accessed in a function called from an
> exception block.
> How do I do this in PostgreSQL

In PG those are local variables within an exception block.  You'd have to
pass their values to the error-logging function explicitly.

                        regards, tom lane