CODEWITHSUNDEEP
postgresqlexceptionplpgsql
Gokhan
Gokhan

Reputation: 289

Postgres function logging and raising an exception

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;

Upvotes: 1

Views: 1697

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656666

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:

Upvotes: 1

Related Questions