PostgreSQL constraint trigger to do processing a single time at end of transaction in response to changes

constraintTrigger.sql

-- This script sketches out a way of doing some processing in response to one or
-- more changes being made to tables throughout the transaction. The processing
-- is done just once before the transaction commits.

begin;

-- Create a table that stores a marker indicating whether or not a change has
-- happened in this transaction. We aren't concerned with what the change
-- specifically was, just that there was a change.
drop table if exists changed;
create table changed(tx_id bigint primary key);

-- Trigger function to populate the "changed" table. This function must only be
-- used with FOR EACH STATEMENT triggers that have a transition table defined
-- with the name "transition". If the transition table has any rows, that's our
-- signal that a change did indeed occur. We need this check, as FOR EACH
-- STATEMENT triggers are run regardless of whether or not actual table changes
-- have occurred.
create or replace function trg_fn_mark_changed() returns trigger as $$
declare
  is_changed boolean not null := (select exists(select from transition));
begin
  if is_changed then
    insert into changed (tx_id) select txid_current() on conflict do nothing;
  else
    raise notice 'No changes';
  end if;
  return null;
end;
$$ language plpgsql strict;

-- Trigger function to do some processing just before the transaction commits.
create or replace function trg_fn_do_stuff_at_end_of_tx() returns trigger as $$
begin
  raise notice 'Doing stuff at end of transaction %', new.tx_id;
  -- Be sure to clean up the entry for this transaction in the "changed" table.
  delete from changed where tx_id = new.tx_id;
  return null;
end;
$$ language plpgsql strict;

-- Create a deferred constraint trigger that does our processing just before the
-- transaction commits.
drop trigger if exists trg_do_stuff_at_end_of_tx on changed;
create constraint trigger trg_do_stuff_at_end_of_tx
  after insert on changed
  deferrable initially deferred
  for each row execute function trg_fn_do_stuff_at_end_of_tx();

--------------------------------------------------------------------------------

-- Create a "foo" table, which is one of two tables we'd like to track
-- throughout a transaction if it's been changed at all.
drop table if exists foo;
create table foo(x int not null);

-- Create FOR EACH STATEMENT triggers that call the function we previously
-- defined to mark that a change has occurred. Even though each trigger calls
-- the same function, we must specify insert/update/delete triggers separately
-- because we are using transition tables. Transition tables cannot be defined
-- for triggers with more than one event.

drop trigger if exists trg_on_foo_insert on foo;
create trigger trg_on_foo_insert
  after insert on foo
  referencing new table as transition
  for each statement execute function trg_fn_mark_changed();

drop trigger if exists trg_on_foo_update on foo;
create trigger trg_on_foo_update
  after update on foo
  referencing old table as transition -- N.B. New table transition isn't needed
  for each statement execute function trg_fn_mark_changed();

drop trigger if exists trg_on_foo_delete on foo;
create trigger trg_on_foo_delete
  after delete on foo
  referencing old table as transition
  for each statement execute function trg_fn_mark_changed();

--------------------------------------------------------------------------------

-- Same as the "foo" table stuff above, just for a "bar" table instead.

drop table if exists bar;
create table bar(y int not null);

drop trigger if exists trg_on_bar_insert on bar;
create trigger trg_on_bar_insert
  after insert on bar
  referencing new table as transition
  for each statement execute function trg_fn_mark_changed();

drop trigger if exists trg_on_bar_update on bar;
create trigger trg_on_bar_update
  after update on bar
  referencing old table as transition -- N.B. New table transition isn't needed
  for each statement execute function trg_fn_mark_changed();

drop trigger if exists trg_on_bar_delete on bar;
create trigger trg_on_bar_delete
  after delete on bar
  referencing old table as transition
  for each statement execute function trg_fn_mark_changed();

commit;

begin;

-- The statement-level triggers defined on "foo" and "bar" fire after every line
-- here. The associated trigger function inserts a row into "changed" for this
-- transaction if a row isn't already present, and only if there truly were
-- changes (e.g. the UPDATE below does not actually change anything). The
-- constraint trigger defined on "change" is deferred and so will run just
-- before the transaction is committed, giving us a hook where we can do some
-- processing.
insert into foo (values (3), (4), (8));
insert into bar (values (1));
insert into foo (values (9));
update foo set x = 42 where x = 3;
insert into bar (values (2));
delete from bar where y = 2;
insert into bar (values (2));

commit;