Re: Oracle error log table equivalent in postgresql

Thanks Pepe/ Farooq.

Thanks,
Mukesh

On Fri, Aug 6, 2021, 11:22 PM FAROOQ SIDDIQUI <fas65(at)yahoo(dot)com> wrote:

> Please find below code, looks like, relate to your issue:
>
>
> Refrence:
> https://stackoverflow.com/questions/53504234/pgsql-trigger-function-write-exception-to-log-table
>
> Here's an example, using a normal function rather than a trigger, though
> it's really the same thing in as far as how to log:
>
> Table to store errors:
>
> CREATE TABLE errors (id SERIAL, sql_state TEXT, message TEXT, detail TEXT,
> hint TEXT, context TEXT);
> Function which does work and has the exception handling/logging:
>
> CREATE OR REPLACE FUNCTION my_func()
> RETURNS VOID AS
> $BODY$
> DECLARE
> _sql_state TEXT;
> _message TEXT;
> _detail TEXT;
> _hint TEXT;
> _context TEXT;
> BEGIN
> PERFORM 1 / 0;
> EXCEPTION
> WHEN OTHERS THEN
> GET STACKED DIAGNOSTICS
> _sql_state := RETURNED_SQLSTATE,
> _message := MESSAGE_TEXT,
> _detail := PG_EXCEPTION_DETAIL,
> _hint := PG_EXCEPTION_HINT,
> _context := PG_EXCEPTION_CONTEXT;
>
> INSERT INTO errors (sql_state, message, detail, hint, context)
> VALUES (_sql_state, _message, _detail, _hint, _context);
> END
> $BODY$
> LANGUAGE plpgsql;
>
> After calling the function, the errors table contains:
>
> [image: Inline image]
>
>
> enter image description here
>
> See https://rextester.com/BQPG27732
>
> Context shows a call stack of sorts. You could add more error-related
> fields of course, I only chose a handful of those available in GET STACKED
> DIAGNOSTICS
>
>