Postgres function logging and raising an exception

Question

I would like to catch the error raised log some info into a table (INSERT ErrorLog ...) related to the error and raise the error back to the caller. I don't want the logged info to be rolled back, how can I accomplish this? We are running Postgres version 8.3

DECLARE
  text_var1 text;
  text_var2 text;
  text_var3 text;

INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
BEGIN
    UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
    x := x + 1;
    y := x / 0;
EXCEPTION WHEN OTHERS THEN
 GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
                          text_var2 = PG_EXCEPTION_DETAIL,
                          text_var3 = PG_EXCEPTION_HINT;
    INSERT ErrorLog(message, detail) values(text_var1, text_var2);
    RAISE EXCEPTION 'Error Occured %\n%', text_var1, text_var2;
    RETURN x;
END;

It's ["Postgres" or "PostgreSQL", never "Postgre"](https://wiki.postgresql.org/wiki/Identity_Guidelines). And version 8.3 is no typo? That version is ancient. — Erwin Brandstetter, Oct 23 '18 at 00:38

score 1 · Answer 1 · answered Oct 23 '18 at 00:46

Not possible the way you display it, because the exception you raise (after trapping the other one) rolls back the transaction an hence also the INSERT in the EXCEPTION block. The function won't return anything, either, this way.

Ways around this include:

Raise a WARNING instead of the EXCEPTION.
In Postgres 11 or later use a procedure instead of the function, where you can manually control transactions.
Use dblink or a similar tool to emulate an "autonomous transaction". Example:
- How do I do large non-blocking updates in PostgreSQL?

Postgres function logging and raising an exception

1 Answers1