4

I'm writing a plpgsql function in PostgreSQL (version 10) which is called by a TRIGGER after an UPDATE

  CREATE TRIGGER trigger_1
   AFTER UPDATE
   ON entities
   FOR EACH ROW
   WHEN ( condition_on_new_and_old )
   EXECUTE PROCEDURE function_1();

entities is a table which has a column data of type JSONB.

The code of function_1 is essentially (I modified the code to isolate the RAISE in the IF, originally the condition was the inverse):

CREATE OR REPLACE FUNCTION function_1() RETURNS TRIGGER AS
$$
BEGIN
  IF (
       TG_OP <> 'UPDATE' 
       OR TG_WHEN <> 'AFTER'
       OR NOT (NEW.data ? 'XXX')
       OR NOT (OLD.data ? 'XXX')
       OR NOT (NEW.object_id = OLD.object_id)
       OR NOT (NEW.workspace = OLD.workspace)) THEN

         RAISE EXCEPTION 'XXX not found';

  END IF;

  -- SOME INSERTs

  -- SOME DELETEs

  RETURN NULL;
END
$$ LANGUAGE plpgsql;

As everyone can expect if the condition in the IF is true we raise the exception. The problem is the RAISE is always executed despite the value of the condition. In all my tests the values of OLD and NEW have always been the same.

Even more surprising was the fact that if I did something like

CREATE OR REPLACE FUNCTION function_1() RETURNS TRIGGER AS
$$
BEGIN
  IF (
       TG_OP <> 'UPDATE' 
       OR TG_WHEN <> 'AFTER'
       OR NOT (NEW.data ? 'XXX')
       OR NOT (OLD.data ? 'XXX')
       OR NOT (NEW.object_id = OLD.object_id)
       OR NOT (NEW.workspace = OLD.workspace)) THEN

         RAISE EXCEPTION 'XXX not found';

  END IF;

  RAISE EXCEPTION 'TEST'

  -- SOME INSERTs

  -- SOME DELETEs

  RETURN NULL;
END
$$ LANGUAGE plpgsql;

I had the exception 'TEST' raised but if I wrote:

CREATE OR REPLACE FUNCTION function_1() RETURNS TRIGGER AS
$$
BEGIN
  IF (
       TG_OP <> 'UPDATE' 
       OR TG_WHEN <> 'AFTER'
       OR NOT (NEW.data ? 'XXX')
       OR NOT (OLD.data ? 'XXX')
       OR NOT (NEW.object_id = OLD.object_id)
       OR NOT (NEW.workspace = OLD.workspace)) THEN

         RAISE EXCEPTION 'XXX not found';

  END IF;

  -- RAISE EXCEPTION 'TEST' (I commented the RAISE)

  -- SOME INSERTs

  -- SOME DELETEs

  RETURN NULL;
END
$$ LANGUAGE plpgsql;

I had the exception 'XXX not found' thrown.

To notice the trigger used to be on BEFORE UPDATE and it did work as expected, the problem arrived when we set it to AFTER.

I'm quite sure I'm missing something about how AFTER triggers behave. Do you have any ideas?

Thank you all in advance

4
  • Do a RAISE NOTICE to show all the variables you use in your IF condition. Commented Sep 23, 2022 at 14:41
  • Also provide the UPDATE data you are using. Commented Sep 23, 2022 at 15:14
  • Can you share further info about WHEN ( condition_on_new_and_old ) . You code is working as intended. Commented Sep 23, 2022 at 19:20
  • dbfiddle.uk/fnM7Y0Us my test demo. Commented Sep 23, 2022 at 19:27

1 Answer 1

0

Thanks for the comments on the question. I'm sorry but I didn't notice in the logs that there was actually a problem in the WHEN condition of the TRIGGER.

After fixing it it's working.

Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.