CODEWITHSUNDEEP
postgresqltriggersplpgsql
mla
mla

Reputation: 304

Postgresql trigger operating on both NEW and OLD records

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?

Upvotes: 1

Views: 1286

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

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;

Related:

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.

Upvotes: 2

Related Questions