Re: exclusion constraint question - Mailing list pgsql-general

On Tue, Mar 11, 2025 at 3:06 AM Achilleas Mantzios asked:

I think the trigger wins: no extension needed, arguably better error output, easier to understand at a glance, and can quickly change the business logic by adjusting the function. Pretty short too. Don't know what the op tried, but here's a version I came up with. Note that this trigger allows you to remove all the UNIQUE column specifiers and the CHECK clause from the original table.

create table mugs (
  mug_id bigint PRIMARY KEY,
  shelf bigint -- not needed for the trigger to work, but nice to have
);

create or replace function add_a_mug() returns trigger
language plpgsql as $$
begin
  if tg_op in ('UPDATE','DELETE' then
    delete from mugs where mug_id in (old.l_mug_id, old.c_mug_id, old.r_mug_id);
  end if;
  if tg_op in ('UPDATE','INSERT') then
    with mugs as (select unnest(array[new.l_mug_id,new.c_mug_id,new.r_mug_id]) as mug)
      insert into mugs(mug_id, shelf) select mug, new.shelf_id
        from mugs where mug is not null;
  end if;
  return null;
end
$$;

create trigger take_one_down_pass_it_around after insert or update or delete
on shelves for each row execute function add_a_mug();