postgresqljointriggersplpgsqldatabase-trigger

Joins around if statement for PostgreSQL trigger function

I have a table a with 3 triggers that insert, update, or delete corresponding rows in b whenever a row in a is inserted, updated, or deleted. All 3 triggers use the same trigger function p.

CREATE OR REPLACE FUNCTION p ()
RETURNS TRIGGER
AS $$
BEGIN
  IF (TG_OP = 'INSERT') THEN
    -- INSERT INTO b ...
    RETURN NEW;
  ELSIF (TG_OP = 'UPDATE') THEN
    -- UPDATE b ...
    RETURN NEW;
  ELSIF (TG_OP = 'DELETE') THEN
    -- DELETE FROM b ...
    RETURN NEW;
  ELSE
    RETURN NULL;
  END IF;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER i AFTER INSERT ON a FOR EACH ROW EXECUTE PROCEDURE p ();
CREATE TRIGGER u AFTER UPDATE ON a FOR EACH ROW EXECUTE PROCEDURE p ();
CREATE TRIGGER d AFTER DELETE ON a FOR EACH ROW EXECUTE PROCEDURE p ();

a also has a foreign key a1 into c (with primary key c1), and I would like to alter p in such a way that it enters the IF/ELSIF branches also depending on a column c2 in c: if that joined column changed, enter the INSERT and UPDATE branches; if it stayed the same, enter the UPDATE branch. In effect, something like this:

  IF (TG_OP = 'INSERT') OR ((TG_OP = 'UPDATE') AND (oldC.c2 <> newC.c2)) THEN
    -- ...
  ELSIF (TG_OP = 'UPDATE') OR (oldC.c2 = newC.c2) THEN
    -- ...
  ELSIF (TG_OP = 'DELETE') OR ((TG_OP = 'UPDATE') AND (oldC.c2 <> newC.c2)) THEN
    -- ...
  ELSE
    -- ...
  END IF;

where oldC and newC would result from joins similar to these (with approx. syntax):

SELECT oldC.* FROM a, c AS oldC WHERE OLD.a1 = c.c1;
SELECT newC.* FROM a, c AS newC WHERE NEW.a1 = c.c1;

So what is needed in effect are two joins outside the IF statement, which would allow it to refer to oldC and newC (or something analogous). Is this possible and how would the altered version of p look (with correct PostgreSQL syntax)?

Solution

  • First off, there is no NEW in case of a DELETE, so RETURN NEW; doesn't make sense. This would raise an exception before Postgres 11, where this was changed:

    • In PL/pgSQL trigger functions, the OLD and NEW variables now read as NULL when not assigned (Tom Lane)

    Previously, references to these variables could be parsed but not executed.

    It doesn't matter what you return for AFTER triggers anyway. Might as well be RETURN NULL;

    There is no OLD in case of an INSERT, either.

    You would only need a single trigger the way you have it right now:

    CREATE TRIGGER a_i_u_d   -- *one* trigger
AFTER INSERT OR UPDATE OR DELETE ON a
FOR EACH ROW EXECUTE FUNCTION p ();

    However, I suggest separate trigger functions for INSERT, UPDATE and DELETE to avoid complications. Then you need three separate triggers, each calling its respective trigger function.

    The case you want to add can only affect UPDATE. Nothing can "change" like you describe with INSERT or DELETE. And strictly speaking, what you ask for is impossible even for an UPDATE trigger:

    depending on a column c2 in c: if that joined column changed ...

    A trigger function on table a only sees a single snapshot of table c. There is no way to detect any "change" in that table. If you really meant to write:

    depending on column a.a1: if that changed so that the referenced value c.c2 is different now ...

    .. then there is a way:

    Since a BEFORE trigger is less prone to endless loops and other complications, I demonstrate a BEFORE UPDATE trigger. (Changing to AFTER is trivial.):

    CREATE OR REPLACE FUNCTION p_upbef()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW.a1 <> OLD.a1 THEN  -- assuming a1 is defined NOT NULL
      IF (SELECT count(DISTINCT c.c2) > 1  -- covers possible NULL in c2 as well
          FROM   c
          WHERE  c.c1 IN (NEW.a1, OLD.a1)) THEN
            -- do something
      END IF;
   END IF;

   RETURN NEW;
END
$func$;

    If a1 can be NULL and you need to track changes from / to NULL as well, you need to do more ...

    Trigger:

    CREATE TRIGGER upbef
BEFORE UPDATE ON a
FOR EACH ROW EXECUTE FUNCTION p_upbef ();

    Since everything depends on a change in a.a1 now (and you don't have other things in the trigger) you can move the outer IF to the trigger itself (cheaper):

    CREATE OR REPLACE FUNCTION p_upbef()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF (SELECT count(DISTINCT c.c2) > 1  -- covers NULL as well
       FROM   c
       WHERE  c.c1 IN (NEW.a1, OLD.a1)) THEN  -- assuming a1 is NOT NULL!
      -- do something
   END IF;

   RETURN NEW;
END
$func$;

    Trigger:

    CREATE TRIGGER upbef
BEFORE UPDATE OF a1 ON a  -- !
FOR EACH ROW EXECUTE FUNCTION p_upbef();

    It's not exactly the same, since an UPDATE involving the column a1 might actually leave the value unchanged , but it's good enough either way for our purpose: to only run the expensive check on c.c2 in relevant cases.

    Related: