CODEWITHSUNDEEP
postgresqlexceptiontriggersplpgsql
jpp1jpp1
jpp1jpp1

Reputation: 181

Perform action before exception call in Postgres trigger function

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.

Upvotes: 8

Views: 9544

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656882

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:

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
  LANGUAGE plpgsql 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$;

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 FUNCTION emp_stamp();

Upvotes: 10

Aret
Aret

Reputation: 475

You have two options actually.

  1. You can log into the postgresql log by using some level on raise Check the manual
  2. Log the error on your application, not in Db transaction. For example, negative salary should not be checked in trigger function like this. Or the exception should be handled on the insert invocations.

Upvotes: 1

Related Questions