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
