Perform action before exception call in Postgres trigger function

Question

Postgres 8.4 here. Imagine this code snippet from Postgres doc:

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
    -- Check that empname and salary are given
    IF NEW.empname IS NULL THEN
        RAISE EXCEPTION 'empname cannot be null';
    END IF;
    IF NEW.salary IS NULL THEN
        RAISE EXCEPTION '% cannot have null salary', NEW.empname;
    END IF;

    -- Who works for us when she must pay for it?
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
    END IF;

    -- Remember who changed the payroll when
    NEW.last_date := current_timestamp;
    NEW.last_user := current_user;
    RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;

If we want to do something like logging in a custom table these exceptions:

-- Check that empname and salary are given
IF NEW.empname IS NULL THEN
    INSERT INTO my_log_table ('User didn't supplied empname')
    RAISE EXCEPTION 'empname cannot be null';
END IF;

It won't work because anything we put before a RAISE EXCEPTION call is undone by the rollback RAISE EXCEPTION implies, i.e. the my_log_table row we create will be deleted as soon as RAISE EXCEPTION is called.

What's is the best way to accomplish something like this? Maybe catching our custom exception?

Turning off rollback @ TRIGGER is not an option, I need it.

Answer 1

You can trap errors / catch exceptions.

In the EXCEPTION block you can do anything else, like INSERT into another table. Afterwards you could re-raise the exception to propagate out, but that would roll back the whole transaction including the INSERT to the log table (unless the exception is wrapped and caught in an outer function).

You could:

• use tricks like a dblink call to emulate an autonomous transaction, which isn't undone, when the wrapping transaction is rolled back. Related:

  • How to use (install) dblink in PostgreSQL?
  • dblink can't update a table on the same database in an after UPDATE trigger
  • Does Postgres support nested or autonomous transactions?

• RAISE a NOTICE or WARNING additionally, which also isn't undone by ROOLBACK.

• RAISE a different EXCEPTION with your own text.

Alternatively, you can just cancel the row that triggered the trigger function and not raise an exception. Everything else in the transaction goes through normally.

Assuming this is a trigger ON UPDATE and you have another table with identical structure to write failed INSERTs to:

CREATE OR REPLACE FUNCTION emp_stamp()
  RETURNS trigger AS
$func$
BEGIN
    -- Check that empname and salary are given
    IF NEW.empname IS NULL THEN
         RAISE EXCEPTION 'empname cannot be null';
    END IF;

    IF ...

    RETURN NEW;    -- regular end

EXCEPTION WHEN others THEN  -- or be more specific
    INSERT INTO log_tbl VALUES (NEW.*); -- identical table structure
    RETURN NULL;   -- cancel row
END
$func$ LANGUAGE plpgsql;

Note that NEW contains the state of the row right before the exception occurred, including previous successful statements in the same function.

Trigger:

CREATE TRIGGER emp_stamp
BEFORE INSERT OR UPDATE ON tbl
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();