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,