public inbox for [email protected]  

help / color / mirror / Atom feed

From: Greg Sabino Mullane <[email protected]>
To: Achilleas Mantzios - cloud <[email protected]>
Cc: Rhys A.D. Stewart <[email protected]>
Cc: [email protected]
Subject: Re: exclusion constraint question
Date: Tue, 11 Mar 2025 10:29:05 -0400
Message-ID: <CAKAnmmLpoCh=rRLA3k9Qypk34uH5appTa-RULq26-KS3kozGvw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CACg0vTnm2cxCmmkMutEL+=4hac0MnVgWykYFVndO8WV_WQMN8Q@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CACg0vTkWs2huWDyp6EJHcsAcdcDSyo6CRMD36z4sr7S7ZY-8Qg@mail.gmail.com>
	<[email protected]>

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: exclusion constraint question
  In-Reply-To: <CAKAnmmLpoCh=rRLA3k9Qypk34uH5appTa-RULq26-KS3kozGvw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox