Re: UPDATE/DELETE FOR PORTION OF fire FOR EACH STATEMENT more than once - Mailing list pgsql-hackers

Hi.

CREATE OR REPLACE FUNCTION trigger_info()
RETURNS TRIGGER LANGUAGE plpgsql AS
$$
BEGIN
  RAISE NOTICE 'trigger name: %: TG_OP: % WHEN: % TG_LEVEL: %',
    TG_NAME, TG_WHEN, TG_OP, TG_LEVEL;
 RAISE NOTICE '  old: %', OLD;
 RAISE NOTICE '  old: %', NEW;
  IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    RETURN OLD;
  END IF;
END;
$$;

drop table if exists ts;
create table ts(a int4range, b int);
insert into ts values('[1,10)', 2), ('[1,10)', 3);
CREATE TRIGGER ts_trig1
  BEFORE INSERT OR UPDATE OR DELETE ON ts
  FOR EACH STATEMENT EXECUTE PROCEDURE trigger_info();

update ts for portion of a from 2 to 4 set b = 12;

The above UPDATE statement is triggering the BEFORE FOR EACH STATEMENT
action four times.
This appears to contradict the documentation mentioned below.
Am I missing something?

https://www.postgresql.org/docs/devel/sql-createtrigger.html
"""
In contrast, a trigger that is marked FOR EACH STATEMENT only executes
once for any given operation, regardless of how many rows it modifies
(in particular, an operation that modifies zero rows will still result
in the execution of any applicable FOR EACH STATEMENT triggers).
"""