MIME-Version: 1.0
References: <CACg0vTnm2cxCmmkMutEL+=4hac0MnVgWykYFVndO8WV_WQMN8Q@mail.gmail.com>
 <6f7fff13-fbc6-423b-b8cb-73762b0cc28b@cloud.gatewaynet.com>
 <efd95608-75cf-4b38-9b05-8600c5a2a791@cloud.gatewaynet.com>
 <CACg0vTkWs2huWDyp6EJHcsAcdcDSyo6CRMD36z4sr7S7ZY-8Qg@mail.gmail.com> <e76ecadc-abfa-4084-ae2b-8d7c08711178@cloud.gatewaynet.com>
In-Reply-To: <e76ecadc-abfa-4084-ae2b-8d7c08711178@cloud.gatewaynet.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Tue, 11 Mar 2025 10:29:05 -0400
Message-ID: <CAKAnmmLpoCh=rRLA3k9Qypk34uH5appTa-RULq26-KS3kozGvw@mail.gmail.com>
Subject: Re: exclusion constraint question
To: Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
Cc: "Rhys A.D. Stewart" <rhys.stewart@gmail.com>, pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000005aec23063011ebeb"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmLpoCh%3DrRLA3k9Qypk34uH5appTa-RULq26-KS3kozGvw%40mail.gmail.com>
Precedence: bulk

--0000000000005aec23063011ebeb
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Mar 11, 2025 at 3:06=E2=80=AFAM 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 delet=
e
on shelves for each row execute function add_a_mug();


Cheers,
Greg

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

--0000000000005aec23063011ebeb
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Mar 11, 2025 at 3:06=E2=80=AFAM A=
chilleas Mantzios asked:</div><div class=3D"gmail_quote gmail_quote_contain=
er"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bor=
der-left:1px solid rgb(204,204,204);padding-left:1ex"><div><blockquote type=
=3D"cite"><pre></pre>
      </blockquote><p><span style=3D"font-family:monospace">is it still har=
der than the
        trigger ?<br>
      </span></p>
    </div></blockquote></div><div>I think the trigger wins: no extension ne=
eded, arguably better error output, easier to understand at a glance, and c=
an quickly change the business logic by adjusting the function. Pretty shor=
t too. Don&#39;t know what the op tried, but here&#39;s a version I came up=
 with. Note that this trigger allows you to remove all the UNIQUE column sp=
ecifiers and the CHECK clause from the original table.</div><div><br></div>=
<div>create table mugs (<br>=C2=A0 mug_id bigint PRIMARY KEY,<br>=C2=A0 she=
lf bigint -- not needed for the trigger to work, but nice to have<br>);</di=
v><div><br></div><div>create or replace function add_a_mug() returns trigge=
r<br>language plpgsql as $$<br>begin<br>=C2=A0 if tg_op in (&#39;UPDATE&#39=
;,&#39;DELETE&#39; then<br>=C2=A0 =C2=A0 delete from mugs where mug_id in (=
old.l_mug_id, old.c_mug_id, old.r_mug_id);<br>=C2=A0 end if;<br>=C2=A0 if t=
g_op in (&#39;UPDATE&#39;,&#39;INSERT&#39;) then<br>=C2=A0 =C2=A0 with mugs=
 as (select unnest(array[new.l_mug_id,new.c_mug_id,new.r_mug_id]) as mug)<b=
r>=C2=A0 =C2=A0 =C2=A0 insert into mugs(mug_id, shelf) select mug, new.shel=
f_id<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 from mugs where mug is not null;<br>=C2=
=A0 end if;<br>=C2=A0 return null;<br>end<br>$$;</div><div><br></div><div>c=
reate trigger take_one_down_pass_it_around after insert or update or delete=
<br>on shelves for each row execute function add_a_mug();<br></div><div><br=
></div><div><br></div><div dir=3D"ltr" class=3D"gmail_signature"><div dir=
=3D"ltr"><div>Cheers,</div><div>Greg</div><div><br></div><div>--</div><div>=
Crunchy Data - <a href=3D"https://www.crunchydata.com" target=3D"_blank">ht=
tps://www.crunchydata.com</a></div><div>Enterprise Postgres Software Produc=
ts &amp; Tech Support</div><div><br></div></div></div></div>

--0000000000005aec23063011ebeb--