From: | Shakti Singh <shakti(dot)0123(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | How to get an exception detail in a function called in exception handler |
Date: | 2017-01-24 16:09:43 |
Message-ID: | CAH19PkkikuE_MVtMYvd_3g-gmEWy4U8uuKHMW9JzkpZaVcjN3Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2017-01-24 16:13:40 | Re: How to get an exception detail in a function called in exception handler |
Previous Message | Adrian Klaver | 2017-01-24 15:46:34 | Re: recovery dump on database with different timezone |