Re: exclusion constraint question

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

> is it still harder than the trigger ?
>
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();

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support