Re: For each statement trigger and update table

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

Oh, so you're defining transition relations (REFERENCING NEW TABLE, OLD
TABLE ) as in here?

CREATE TRIGGER paired_items_update
    AFTER UPDATE ON paired_items
    REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab
    FOR EACH ROW
    EXECUTE FUNCTION check_matching_pairs();


On Fri, Jan 3, 2020 at 5:55 PM Mike Martin <[email protected]> wrote:

> According to the docs, not possible to use a transition table and column
> list together
>
> On Fri, 3 Jan 2020, 23:39 Rene Romero Benavides, <[email protected]=
>
> wrote:
>
>> >  I can give code when I get home, but it's pretty simple stuff
>> please do so, along with your trigger definition. Are you aware that you
>> can define your update trigger to fire on a specific column?
>>
>> https://www.postgresql.org/docs/current/sql-createtrigger.html
>>
>> For UPDATE events, it is possible to specify a list of columns using
>> this syntax:
>>
>> UPDATE OF column_name1 [, column_name2 ... ]
>>
>>
>>
>> On Fri, Jan 3, 2020 at 5:21 PM Mike Martin <[email protected]> wrote:
>>
>>> Not sure if this is possible
>>> Basically I want to have a trigger which updates an array column in the
>>> same table when a column is updated
>>> This works as a row level trigger, but not as per statement
>>> I have hit the recursive issue (where update fires update trigger which
>>> fires etc)
>>> According to the docs I cannot use columns and relative tables together
>>>
>>> So any suggestions? I can give code when I get home, but it's pretty
>>> simple stuff
>>>
>>
>>
>> --
>> El genio es 1% inspiraci=C3=B3n y 99% transpiraci=C3=B3n.
>> Thomas Alva Edison
>> http://pglearn.blogspot.mx/
>>
>>

--=20
El genio es 1% inspiraci=C3=B3n y 99% transpiraci=C3=B3n.
Thomas Alva Edison
http://pglearn.blogspot.mx/

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

<div dir=3D"ltr">Oh, so you&#39;re defining transition relations (REFERENCI=
NG NEW TABLE, OLD TABLE ) as in here?<div><pre class=3D"gmail-programlistin=
g" style=3D"box-sizing:border-box;font-family:monospace,monospace;font-size=
:14.4px;overflow:auto;color:rgb(13,10,11);border-radius:0.25rem;border:1px =
solid rgb(206,212,218);margin-top:1rem;margin-bottom:1rem;background-color:=
rgb(248,249,250);padding:0.8rem">CREATE TRIGGER paired_items_update
    AFTER UPDATE ON paired_items
    REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab
    FOR EACH ROW
    EXECUTE FUNCTION check_matching_pairs();</pre></div></div><br><div clas=
s=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, Jan 3, 2020=
 at 5:55 PM Mike Martin &lt;<a href=3D"mailto:[email protected]">mike@re=
dtux.plus.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" sty=
le=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddi=
ng-left:1ex"><div dir=3D"auto">According to the docs, not possible to use a=
 transition table and column list together=C2=A0</div><br><div class=3D"gma=
il_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, 3 Jan 2020, 23:39 R=
ene Romero Benavides, &lt;<a href=3D"mailto:[email protected]" target=
=3D"_blank">[email protected]</a>&gt; wrote:<br></div><blockquote cla=
ss=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid =
rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">&gt;=
=C2=A0 I can give code when I get home, but it&#39;s pretty simple stuff=C2=
=A0</div><div>please do so, along with your trigger definition. Are you awa=
re that you can define your update trigger to fire on a specific column?</d=
iv><div><br></div><div><a href=3D"https://www.postgresql.org/docs/current/s=
ql-createtrigger.html" rel=3D"noreferrer" target=3D"_blank">https://www.pos=
tgresql.org/docs/current/sql-createtrigger.html</a>=C2=A0=C2=A0<br></div><d=
iv><p style=3D"box-sizing:border-box;color:rgb(13,10,11);font-family:&quot;=
Open Sans&quot;,sans-serif;font-size:14.4px;margin:1rem 0px 1rem 2rem">For=
=C2=A0<tt style=3D"box-sizing:border-box;border-radius:0.25rem;margin:0.6re=
m 0px;font-size:0.9rem;color:inherit;background-color:rgb(248,249,250)">UPD=
ATE</tt>=C2=A0events, it is possible to specify a list of columns using thi=
s syntax:</p><pre style=3D"box-sizing:border-box;font-family:monospace,mono=
space;font-size:14.4px;overflow:auto;color:rgb(13,10,11);border-radius:0.25=
rem;border:1px solid rgb(206,212,218);margin-top:1rem;margin-bottom:1rem;ma=
rgin-left:2rem;background-color:rgb(248,249,250);padding:0.8rem">UPDATE OF =
<tt style=3D"box-sizing:border-box;font-weight:900;font-style:italic;border=
-radius:0.25rem;margin:0.6rem 0px;font-size:0.9rem;color:inherit">column_na=
me1</tt> [, <tt style=3D"box-sizing:border-box;font-weight:900;font-style:i=
talic;border-radius:0.25rem;margin:0.6rem 0px;font-size:0.9rem;color:inheri=
t">column_name2</tt> ... ]</pre></div><div><br></div></div><br><div class=
=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, Jan 3, 2020 =
at 5:21 PM Mike Martin &lt;<a href=3D"mailto:[email protected]" rel=3D"n=
oreferrer" target=3D"_blank">[email protected]</a>&gt; wrote:<br></div><=
blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"auto">Not sure=
 if this is possible<div dir=3D"auto">Basically I want to have a trigger wh=
ich updates an array column in the same table when a column is updated</div=
><div dir=3D"auto">This works as a row level trigger, but not as per statem=
ent</div><div dir=3D"auto">I have hit the recursive issue (where update fir=
es update trigger which fires etc)</div><div dir=3D"auto">According to the =
docs I cannot use columns and relative tables together</div><div dir=3D"aut=
o"><br></div><div dir=3D"auto">So any suggestions? I can give code when I g=
et home, but it&#39;s pretty simple stuff=C2=A0</div></div>
</blockquote></div><br clear=3D"all"><div><br></div>-- <br><div dir=3D"ltr"=
>El genio es 1% inspiraci=C3=B3n y 99% transpiraci=C3=B3n.<br>Thomas Alva E=
dison<br><a href=3D"http://pglearn.blogspot.mx/" rel=3D"noreferrer" target=
=3D"_blank">http://pglearn.blogspot.mx/</a><br><div style=3D"padding:0px;ma=
rgin-left:0px;margin-top:0px;overflow:hidden;color:black;font-size:10px;tex=
t-align:left;line-height:130%"></div><div><br></div></div>
</blockquote></div>
</blockquote></div><br clear=3D"all"><div><br></div>-- <br><div dir=3D"ltr"=
 class=3D"gmail_signature">El genio es 1% inspiraci=C3=B3n y 99% transpirac=
i=C3=B3n.<br>Thomas Alva Edison<br><a href=3D"http://pglearn.blogspot.mx/" =
target=3D"_blank">http://pglearn.blogspot.mx/</a><br><div style=3D"padding:=
0px;margin-left:0px;margin-top:0px;overflow:hidden;color:black;font-size:10=
px;text-align:left;line-height:130%"></div><div><br></div></div>

--00000000000082cf32059b453449--