Extensions
- pgerror 0.2.1
- Tools for better error handling
README
Contents
Examples
CREATE SCHEMA error;
CREATE EXTENSION pgerror WITH SCHEMA error;
CREATE TABLE error_log(
error_time timestamptz NOT NULL DEFAULT now()
, error_data error.error_data NOT NULL
);
CREATE TABLE
INSERT INTO error_log(error_data) SELECT error FROM error.try('SELECT 1') WHERE error IS NOT NULL;
INSERT 0 0
INSERT INTO error_log(error_data) SELECT error FROM error.try('SELECT bad') WHERE error IS NOT NULL;
INSERT 0 1
\x
Expanded display is on.
SELECT error_time, (error_data).* FROM error_log;
-[ RECORD 1 ]---+--------------------------------------------------------------
error_time | 2016-08-11 18:50:24.105764-05
sqlstate | 42703
message | column "bad" does not exist
hint |
detail |
context | PL/pgSQL function error.try(text) line 3 at EXECUTE statement
schema_name |
table_name |
column_name |
constraint_name |
type_name |
\x
SELECT error.raise(error_data, 'WARNING') FROM error_log;
WARNING: column "bad" does not exist
CONTEXT: SQL statement "SELECT pg_temp.raise_error_internal()"
PL/pgSQL function raise(text,text,text,text,text,text,text,text,text,text) line 47 at PERFORM
SQL function "raise" statement 1
raise
-------
(1 row)
SELECT error.raise(message := 'something bad', detail := 'Something bad happened!', hint := $$Don't do that!$$);
ERROR: something bad
DETAIL: Something bad happened!
HINT: Don't do that!
CONTEXT: SQL statement "SELECT pg_temp.raise_error_internal()"
PL/pgSQL function raise(text,text,text,text,text,text,text,text,text,text) line 47 at PERFORM
INCOMPATIBILITIES
Some of the fields in the error_data
type have different names than those
used in the PLPGSQL RAISE command. This was done because the PLPGSQL names are
reserved words, which makes referring to them difficult.
error_data
This is a compound type that can be passed around or stored in a table. It contains fields that correspond to every available detail for an exception.
error_data()
Convenience function for constructing an error_data
type. Note that it
intentionally defaults all the fields to an empty string (''
) instead of
NULL. This matches the behavior of what PLPGSQL’s GET DIAGNOSTICS
does.
raise()
These functions will raise the specified error. You may pass in either
individual fields or an error_data
type. Any fields that are NULL
or an
empty string (''
) are not added to the RAISE
command and will not appear in
the output.
Note
|
Unlike PLPGSQL, these functions treat an empty string ('' ) and NULL the
same. This means it’s not possible to add an empty string to an exception. |
try()
This function will execute the SQL in the code
parameter and return the
number of rows affected (NULL on an error), or an error_data
record if an
error occurred (NULL if no error).
try_cursor()
Similar to try()
, except query
is opened as a cursor, and a reference to
that cursor is returned in result
. If cursor_name
isn’t specified an
un-named cursor is created.
try_into()
Similar to try()
, except code
is executed using EXECUTE INTO
and the
result is returned via result
. You must pass an appropriate data type to
result
, but it can be just a NULL
, ie: NULL::int
. If strict
is set to
TRUE
, the STRICT
option to EXECUTE INTO
is used.
Copyright and License
pgerror is released under a MIT liscense.
Copyright (c) 2016 Jim Nasby <Jim.Nasby@BlueTreble.com>.