Postgresql trigger operating on both NEW and OLD records

Question

This is primarily a style question.

I have an AFTER INSERT OR UPDATE trigger. I want to run the same query against whichever of the the NEW/OLD records are available. Rather than have conditionals checking TG_OP and duplicating the query, what is the cleanest way to determine which are available and to loop over them?

Example code:

CREATE FUNCTION myfunc() RETURNS TRIGGER AS $$
  DECLARE
    id int;
    ids int[];
  BEGIN
    IF TG_OP IN ('INSERT', 'UPDATE') THEN
      ids := ids || NEW.id;
    END IF;

    IF TG_OP IN ('UPDATE', 'DELETE') THEN
      ids := ids || OLD.id;
    END IF;

    FOREACH id IN ARRAY ARRAY(SELECT DISTINCT UNNEST(ids))
    LOOP
      RAISE NOTICE 'myfunc called for % on id %', TG_OP, id;
      /* RUN QUERY REFERENCING id */
    END LOOP;

    RETURN NULL;
  END;
$$ LANGUAGE plpgsql;

Is there a shorter/simpler/more idiomatic way to achieve that?

are you looking for `ids := ids || new.id`? (but you have to initialize `ids` to an empty array, not `null`) See the manual for details: https://www.postgresql.org/docs/current/static/functions-array.html — , Mar 29 '18 at 20:00
I was intending it just as pseudo-code since I don't know if using an array is the best method or not. But thanks. I edited the code so it actually works now. But is there a simpler way? Seems awfully complicated to achieve this end. — mla, Mar 29 '18 at 21:52

score 1 · Answer 1 · answered Mar 29 '18 at 23:00

Array handling and a separate SELECT DISTINCT seem too expensive for the job. This should be cheaper:

CREATE FUNCTION myfunc()
  RETURNS TRIGGER AS
$func$
DECLARE
   _id int;
BEGIN
   CASE TG_OP
      WHEN 'INSERT', 'UPDATE' THEN
         _id := NEW.id;
      WHEN 'DELETE' THEN
         _id := OLD.id;
   END CASE;

   FOR i IN 1..2  -- max 2 iterations
   LOOP
      RAISE NOTICE 'myfunc called for % on id %', TG_OP, _id;
      /* RUN QUERY REFERENCING _id */

      EXIT WHEN TG_OP <> 'UPDATE' OR i = 2;  -- only continue in 1st round for UPDATE  
      EXIT WHEN _id = OLD.id;                -- only continue for different value
      _id := OLD.id;
   END LOOP;
   RETURN NULL;
END
$func$  LANGUAGE plpgsql;

BREAK statement in PL/pgSQL

But I would probably just write a separate trigger function & trigger for each DML statement. Rather three very simple and faster functions than one generic but complex and slower one.

Thank you. Yes, that is a bit complex. Ok then, as a corollary question, how would you name/manage the different objects then? Would you have a myfunc(int) that does the actual query, and then myfunc_new_trig and myfunc_old_trig? Any pointers to best practices there? — mla, Mar 30 '18 at 15:54
Not `myfunc_new_trig` & `myfunc_old_trig`, since I would have three separate triggers (and respective trigger functions) for `INSERT`, `UPDATE` and `DELETE` - the `UPDATE` trigger being the only one to deal with `OLD` *and* `NEW`. (Depending on the actual query in `myfunc(int)` all of this might be simplified after all. But that remained undisclosed.) — Erwin Brandstetter, Mar 30 '18 at 16:05

Postgresql trigger operating on both NEW and OLD records

1 Answers1

Linked