log_exception_to_table

log_exception_to_table

postgres=# truncate table t;
TRUNCATE TABLE
postgres=# truncate table error_log_table;
TRUNCATE TABLE
postgres=#
postgres=# \d t
                 Table "public.t"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 col1   | integer |           |          |
 col2   | text    |           |          |
Indexes:
    "t_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "t_col1_check" CHECK (col1 > 100)

postgres=# \d error_log_table
                             Table "public.error_log_table"
 Column  |  Type   | Collation | Nullable |                   Default
---------+---------+-----------+----------+---------------------------------------------
 id      | integer |           | not null | nextval('error_log_table_id_seq'::regclass)
 err_msg | jsonb   |           |          |
Indexes:
    "error_log_table_pkey" PRIMARY KEY, btree (id)

postgres=# do $$
declare _column_name text;
        _constraint_name text;
        _datatype_name text;
        _table_name text;
        _schema_name text;
        _state   text;
        _msg     text;
        _detail  text;
        _hint    text;
        _context text;
begin
insert into t values (1, 101, 1::text), (2, 2, 2::text);
exception
when others then
  get stacked diagnostics
        _state   = RETURNED_SQLSTATE,
        _msg     = MESSAGE_TEXT,
        _detail  = PG_EXCEPTION_DETAIL,
        _hint    = PG_EXCEPTION_HINT,
        _context = PG_EXCEPTION_CONTEXT,
        _column_name = column_name,
        _constraint_name = constraint_name,
        _datatype_name = pg_datatype_name,
        _table_name = table_name,
        _schema_name = schema_name;
        insert into error_log_table(err_msg) select json_build_object(
          '_state',_state,
          '_msg',_msg,
          '_detail',_detail,
          '_hint',_hint,
          '_context',_context,
          '_column_name',_column_name,
          '_constraint_name',_constraint_name,
          '_datatype_name',_datatype_name,
          '_table_name',_table_name,
          '_schema_name',_schema_name); --re reraise exception later
end; $$ language plpgsql;
DO
postgres=# \x
Expanded display is on.
postgres=# table t;
(0 rows)

postgres=# table error_log_table;
-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id      | 2
err_msg | {"_msg": "new row for relation \"t\" violates check constraint \"t_col1_check\"", "_hint": "", "_state": "23514", "_detail": "Failing row contains (2, 2, 2).", "_context": "SQL statement \"insert into t values (1, 101, 1::text), (2, 2, 2::text)\"\nPL/pgSQL function inline_code_block line 13 at SQL statement", "_table_name": "t", "_column_name": "", "_schema_name": "public", "_datatype_name": "", "_constraint_name": "t_col1_check"}