Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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"}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment