Re: Oracle error log table equivalent in postgresql

[Mukesh Rajpurohit <vivasvan1902@xxxxxxxxx>]

Thanks Pepe/ Farooq.

Thanks,

Mukesh

On Fri, Aug 6, 2021, 11:22 PM FAROOQ SIDDIQUI <fas65@xxxxxxxxx> 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:

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