Postgresql – way to query all error messages in Postgres

error handlingpostgresql

Postgres has a catalogue of possible error messages here:

http://www.postgresql.org/docs/8.2/static/errcodes-appendix.html#ERRCODES-TABLE

However, I would like to query this information from a system view instead of looking it up on web page (in order to check that I handle all messages correctly in my code).

Is there a way to ask Postgres via a pg_something view or table what error messages it can raise? Also, where does Postgres store the translation (if any) of these messages?

Best Answer

It looks like there is no easy to use SQL to query error codes inside Postgres. However, the source does contain the following file:

src/backend/utils/errcodes.txt

Which has the code listed in an easily parsable format:

01000    W    ERRCODE_WARNING                                                warning
0100C    W    ERRCODE_WARNING_DYNAMIC_RESULT_SETS_RETURNED                   dynamic_result_sets_returned
01008    W    ERRCODE_WARNING_IMPLICIT_ZERO_BIT_PADDING                      implicit_zero_bit_padding
01003    W    ERRCODE_WARNING_NULL_VALUE_ELIMINATED_IN_SET_FUNCTION          null_value_eliminated_in_set_function
01007    W    ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED                          privilege_not_granted
01006    W    ERRCODE_WARNING_PRIVILEGE_NOT_REVOKED                          privilege_not_revoked
01004    W    ERRCODE_WARNING_STRING_DATA_RIGHT_TRUNCATION                   string_data_right_truncation
01P01    W    ERRCODE_WARNING_DEPRECATED_FEATURE                             deprecated_feature

Related Solutions

How to create custom error messages in APEX

In Apex specifically, use validations. You can specify them per field or use a custom one that checks all the fields at once.

http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21674/bldr_validate.htm#BABCDBDE

Postgresql – Inherit audit columns and triggers

From http://postgresql.1045698.n5.nabble.com/DELETE-and-UPDATE-triggers-on-parent-table-of-partioned-table-not-firing-td5683717.html:

The DELETE and UPDATE triggers need to be on the child tables. An operation on a child doesn't fire the triggers of the parent.

As such, the following code must also be executed:

CREATE TRIGGER ${name}_delete BEFORE DELETE ON $name
    FOR EACH ROW EXECUTE PROCEDURE audit_delete();

The audit_delete() function won't work as written in the question: setting the deleted date is not possible using OLD.deleted = current_timestamp. Instead, assuming that the table has a primary key field of id:

CREATE OR REPLACE FUNCTION audit_delete()
  RETURNS trigger AS
$BODY$
BEGIN
  EXECUTE
    'UPDATE ' || TG_TABLE_NAME || ' SET deleted = current_timestamp WHERE id = ' || OLD.id;
  RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1;

Best Answer

Related Solutions

How to create custom error messages in APEX

Postgresql – Inherit audit columns and triggers

Related Question