[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