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
);
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
$$;
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();
on shelves for each row execute function add_a_mug();
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
