Re: For each statement trigger and update table

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

Try with pg_trigger_depth() =3D 1
pg_trigger_depth() int current nesting level of PostgreSQL triggers (0 if
not called, directly or indirectly, from inside a trigger)because you're
calling your procedure by means of a trigger
https://www.postgresql.org/docs/12/functions-info.html

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

> This is the function
>
> CREATE OR REPLACE FUNCTION public.tagfile_upd_su()
>     RETURNS trigger
>     LANGUAGE 'plpgsql'
>     COST 100
>     VOLATILE NOT LEAKPROOF
> AS $BODY$
>     BEGIN
>
> WITH arrfile AS(SELECT
> fileid,tagfile,(regexp_split_to_array(tagfile,'/'))[2:] filearr1 FROM
> tagfile_new),
> arrfile2 AS(SELECT
> fileid,tagfile,filearr1[1:cardinality(filearr1)-1]||regexp_matches(filear=
r1[cardinality(filearr1)],'(.*)\.(.*)')
> filearr
> FROM arrfile)
>
> UPDATE tagfile  tf SET filearr=3Da2.filearr
> FROM arrfile2 a2
> WHERE EXISTS (SELECT 1 FROM arrfile2 af WHERE tf.fileid=3Daf.fileid AND
> af.tagfile !=3D tf.tagfile);
> END
>
> Would really prefer not to have a row level function. The Insert version
> works perfefectly.
> I have tried using pg_trigger_depth, but that stops the trigger running a=
t
> all
>
> Trigger definition is
>
> CREATE TRIGGER tagfile_uas
>     AFTER UPDATE
>     ON public.tagfile
>     REFERENCING OLD TABLE tagfile_old NEW TABLE AS tagfile_new
>     FOR EACH STATEMENT
> --WHEN (pg_trigger_depth() < 1)
>     EXECUTE PROCEDURE public.tagfile_upd_su()
> ;
> (please note commented out pg_trigger_depth which stopped trigger firing
> at all
>
> On Sat, 4 Jan 2020 at 00:26, Rene Romero Benavides <
> [email protected]> wrote:
>
>> Mike, please include to the mailing list as well, so others can help you
>> out too. Why do you need the trigger to be FOR EACH STATEMENT? so I can
>> understand your use case, even if it's simple stuff, please share with u=
s
>> your code.
>>
>> On Fri, Jan 3, 2020 at 6:06 PM Rene Romero Benavides <
>> [email protected]> wrote:
>>
>>> 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/
>>>>>
>>>>>
>>>
>>> --
>>> El genio es 1% inspiraci=C3=B3n y 99% transpiraci=C3=B3n.
>>> Thomas Alva Edison
>>> http://pglearn.blogspot.mx/
>>>
>>>
>>
>> --
>> 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/

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

<div dir=3D"ltr">Try with pg_trigger_depth() =3D 1<div><table border=3D"1" =
class=3D"gmail-CALSTABLE" style=3D"box-sizing:border-box;border-collapse:co=
llapse;background-color:rgb(224,236,239);border:2px solid rgb(222,226,230);=
border-spacing:0px;width:initial;margin:2ex 0px;color:rgb(51,103,145);font-=
family:&quot;Open Sans&quot;,sans-serif;font-size:14.4px;font-weight:900"><=
tbody style=3D"box-sizing:border-box"><tr style=3D"box-sizing:border-box"><=
td style=3D"box-sizing:border-box;background-color:rgb(255,255,255);padding=
:0.5ex"><tt class=3D"gmail-LITERAL" style=3D"box-sizing:border-box;border-r=
adius:0.25rem;margin:0px;font-weight:300;padding:0px;word-break:unset;font-=
size:inherit;color:inherit;background-color:inherit"><code class=3D"gmail-F=
UNCTION" style=3D"box-sizing:border-box;font-family:monospace,monospace;wor=
d-break:unset;border-radius:0.25rem;margin:0px;padding:0px;font-size:inheri=
t;color:inherit;background-color:inherit">pg_trigger_depth()</code></tt></t=
d><td style=3D"box-sizing:border-box;background-color:rgb(255,255,255);padd=
ing:0.5ex"><tt class=3D"gmail-TYPE" style=3D"box-sizing:border-box;margin:0=
px;padding:0px;word-break:unset;background-color:inherit;color:inherit;font=
-size:inherit">int</tt></td><td style=3D"box-sizing:border-box;background-c=
olor:rgb(255,255,255);padding:0.5ex">current nesting level of=C2=A0<span cl=
ass=3D"gmail-PRODUCTNAME" style=3D"box-sizing:border-box">PostgreSQL</span>=
=C2=A0triggers (0 if not called, directly or indirectly, from inside a trig=
ger)</td></tr></tbody></table>because you&#39;re calling your procedure by =
means of a trigger=C2=A0</div><div><a href=3D"https://www.postgresql.org/do=
cs/12/functions-info.html">https://www.postgresql.org/docs/12/functions-inf=
o.html</a>=C2=A0=C2=A0<br></div></div><br><div class=3D"gmail_quote"><div d=
ir=3D"ltr" class=3D"gmail_attr">On Fri, Jan 3, 2020 at 9:46 PM Mike Martin =
&lt;<a href=3D"mailto:[email protected]">[email protected]</a>&gt; wr=
ote:<br></div><blockquote class=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"><div>This is the function</div><div><br></div><div>C=
REATE OR REPLACE FUNCTION public.tagfile_upd_su()<br>=C2=A0 =C2=A0 RETURNS =
trigger<br>=C2=A0 =C2=A0 LANGUAGE &#39;plpgsql&#39;<br>=C2=A0 =C2=A0 COST 1=
00<br>=C2=A0 =C2=A0 VOLATILE NOT LEAKPROOF<br>AS $BODY$<br>=C2=A0 =C2=A0 BE=
GIN<br><br>		WITH arrfile AS(SELECT fileid,tagfile,(regexp_split_to_array(t=
agfile,&#39;/&#39;))[2:] filearr1 FROM tagfile_new),<br>arrfile2 AS(SELECT =
fileid,tagfile,filearr1[1:cardinality(filearr1)-1]||regexp_matches(filearr1=
[cardinality(filearr1)],&#39;(.*)\.(.*)&#39;) filearr<br>FROM arrfile)<br><=
br>UPDATE tagfile =C2=A0tf SET filearr=3Da2.filearr<br>FROM arrfile2 a2<br>=
WHERE EXISTS (SELECT 1 FROM arrfile2 af WHERE tf.fileid=3Daf.fileid AND af.=
tagfile !=3D tf.tagfile);</div><div>END</div><div><br></div><div>Would real=
ly prefer not to have a row level function. The Insert version works perfef=
ectly.</div><div>I have tried using pg_trigger_depth, but that stops the tr=
igger running at all</div><div><br></div><div>Trigger definition is <br></d=
iv><div><br></div><div>CREATE TRIGGER tagfile_uas<br>=C2=A0 =C2=A0 AFTER UP=
DATE<br>=C2=A0 =C2=A0 ON public.tagfile<br>=C2=A0 =C2=A0 REFERENCING OLD TA=
BLE tagfile_old NEW TABLE AS tagfile_new<br>=C2=A0 =C2=A0 FOR EACH STATEMEN=
T<br>	--WHEN (pg_trigger_depth() &lt; 1)<br>=C2=A0 =C2=A0 EXECUTE PROCEDURE=
 public.tagfile_upd_su()<br>	;</div><div>(please note commented out pg_trig=
ger_depth which stopped trigger firing at all<br></div></div><br><div class=
=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Sat, 4 Jan 2020 a=
t 00:26, Rene Romero Benavides &lt;<a href=3D"mailto:[email protected]=
m" target=3D"_blank">[email protected]</a>&gt; wrote:<br></div><block=
quote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1=
px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">Mike, please i=
nclude to the mailing list as well, so others can help you out too. Why do =
you need the trigger to be FOR EACH STATEMENT? so I can understand your use=
 case, even if it&#39;s simple stuff, please share with us your code.=C2=A0=
</div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">=
On Fri, Jan 3, 2020 at 6:06 PM Rene Romero Benavides &lt;<a href=3D"mailto:=
[email protected]" target=3D"_blank">[email protected]</a>&gt; =
wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0=
px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=
=3D"ltr">Oh, so you&#39;re defining transition relations (REFERENCING NEW T=
ABLE, OLD TABLE ) as in here?<div><pre style=3D"box-sizing:border-box;font-=
family:monospace,monospace;font-size:14.4px;overflow:auto;color:rgb(13,10,1=
1);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]" target=
=3D"_blank">[email protected]</a>&gt; wrote:<br></div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><div dir=3D"auto">According to the docs, n=
ot possible to use a transition table and column list together=C2=A0</div><=
br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Fri,=
 3 Jan 2020, 23:39 Rene Romero Benavides, &lt;<a href=3D"mailto:rene.romero=
[email protected]" target=3D"_blank">[email protected]</a>&gt; wrote:<br><=
/div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo=
rder-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><di=
v dir=3D"ltr">&gt;=C2=A0 I can give code when I get home, but it&#39;s pret=
ty simple stuff=C2=A0</div><div>please do so, along with your trigger defin=
ition. Are you aware that you can define your update trigger to fire on a s=
pecific column?</div><div><br></div><div><a href=3D"https://www.postgresql.=
org/docs/current/sql-createtrigger.html" rel=3D"noreferrer" target=3D"_blan=
k">https://www.postgresql.org/docs/current/sql-createtrigger.html</a>=C2=A0=
=C2=A0<br></div><div><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 0=
px 1rem 2rem">For=C2=A0<tt style=3D"box-sizing:border-box;border-radius:0.2=
5rem;margin:0.6rem 0px;font-size:0.9rem;color:inherit;background-color:rgb(=
248,249,250)">UPDATE</tt>=C2=A0events, it is possible to specify a list of =
columns using this syntax:</p><pre style=3D"box-sizing:border-box;font-fami=
ly:monospace,monospace;font-size:14.4px;overflow:auto;color:rgb(13,10,11);b=
order-radius:0.25rem;border:1px solid rgb(206,212,218);margin-top:1rem;marg=
in-bottom:1rem;margin-left:2rem;background-color:rgb(248,249,250);padding:0=
.8rem">UPDATE OF <tt style=3D"box-sizing:border-box;font-weight:900;font-st=
yle:italic;border-radius:0.25rem;margin:0.6rem 0px;font-size:0.9rem;color:i=
nherit">column_name1</tt> [, <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_name2</tt> ... ]</pre></div><div><br></div></div=
><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Fr=
i, Jan 3, 2020 at 5:21 PM Mike Martin &lt;<a href=3D"mailto:[email protected]=
s.com" rel=3D"noreferrer" target=3D"_blank">[email protected]</a>&gt; wr=
ote:<br></div><blockquote class=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=
"auto">Not sure if this is possible<div dir=3D"auto">Basically I want to ha=
ve a trigger which 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 no=
t as per statement</div><div dir=3D"auto">I have hit the recursive issue (w=
here update fires update trigger which fires etc)</div><div dir=3D"auto">Ac=
cording to the docs I cannot use columns and relative tables together</div>=
<div dir=3D"auto"><br></div><div dir=3D"auto">So any suggestions? I can giv=
e code when I get 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"=
>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/" target=3D"_blank">http://p=
glearn.blogspot.mx/</a><br><div style=3D"padding:0px;margin-left:0px;margin=
-top:0px;overflow:hidden;color:black;font-size:10px;text-align:left;line-he=
ight:130%"></div><div><br></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/" target=3D"_blank">http://p=
glearn.blogspot.mx/</a><br><div style=3D"padding:0px;margin-left:0px;margin=
-top:0px;overflow:hidden;color:black;font-size:10px;text-align:left;line-he=
ight:130%"></div><div><br></div></div>
</blockquote></div></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>

--000000000000763b2d059b5ac1af--