Re: For each statement trigger and update table
Mike Martin <[email protected]> Sun, 5 Jan 2020 16:10:47 +0000
| Newsgroups | gmane.comp.db.postgresql.sql |
|---|---|
| Message-ID | <CAOwYNKaM-s5Agms5nxT7On3La9gJm-+G49p750t1ousqspJ2WQ@mail.gmail.com> |
--0000000000005cb637059b66cb98 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thanks I had looked at that before, but it turns out I had a small error in my criteria. altered to IF pg_trigger_depth() =3D 1 THEN WITH arrfile AS(SELECT a.fileid,a.tagfile tagfilenew,(regexp_split_to_array(a.tagfile,'/'))[2:] filearr1 FROM tagfile_new a), arrfile2 AS(SELECT fileid,o.tagfile tagfileold,tagfilenew,filearr1[1:cardinality(filearr1)-1]||regexp_matches(f= ilearr1[cardinality(filearr1)],'(.*)\.(.*)') filearr FROM arrfile a JOIN tagfile_old o using(fileid) ) UPDATE tagfile tf SET filearr=3Da2.filearr FROM arrfile2 a2 WHERE tf.fileid=3Da2.fileid AND a2.tagfilenew !=3D a2.tagfileold; END IF; I misunderstood the docs and realised I needed to compare old table and new table rather than tagfile and new table On Sun, 5 Jan 2020 at 01:49, Rene Romero Benavides <[email protected]= > wrote: > 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(filea= rr1[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 >> at 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 yo= u >>> 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 = us >>> 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, OL= D >>>> 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 prett= y >>>>>>> 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/ >>> >>> > > -- > El genio es 1% inspiraci=C3=B3n y 99% transpiraci=C3=B3n. > Thomas Alva Edison > http://pglearn.blogspot.mx/ > > --0000000000005cb637059b66cb98 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr"><div><br></div><div>Thanks I had looked at that before, bu= t it turns out I had a small error in my criteria. altered to</div><div> I= F pg_trigger_depth() =3D 1 THEN<br> </div><div><br></div><div>WITH arrfile= AS(SELECT a.fileid,a.tagfile tagfilenew,(regexp_split_to_array(a.tagfile,&= #39;/'))[2:] filearr1 <br></div><div>FROM <br></div><div>tagfile_new a)= ,</div><div><br></div><div>arrfile2 AS(SELECT fileid,o.tagfile tagfileold,t= agfilenew,filearr1[1:cardinality(filearr1)-1]||regexp_matches(filearr1[card= inality(filearr1)],'(.*)\.(.*)') filearr<br>FROM arrfile a <br></di= v><div>JOIN <br></div><div>tagfile_old o using(fileid)</div><div>)<br></div= ><div><br></div><div>UPDATE tagfile =C2=A0tf SET filearr=3Da2.filearr<br>FR= OM arrfile2 a2<br> WHERE tf.fileid=3Da2.fileid =C2=A0AND a2.tagfilenew !=3D= a2.tagfileold;<br>END IF;</div><div><br></div><div>I misunderstood the doc= s and realised I needed to compare old table and new table rather than tagf= ile and new table<br></div></div><br><div class=3D"gmail_quote"><div dir=3D= "ltr" class=3D"gmail_attr">On Sun, 5 Jan 2020 at 01:49, Rene Romero Benavid= es <<a href=3D"mailto:[email protected]">[email protected]</= a>> wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0p= x 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><d= iv dir=3D"ltr">Try with pg_trigger_depth() =3D 1<div><table style=3D"box-si= zing:border-box;border-collapse:collapse;background-color:rgb(224,236,239);= border:2px solid rgb(222,226,230);border-spacing:0px;width:initial;margin:2= ex 0px;color:rgb(51,103,145);font-family:"Open Sans",sans-serif;f= ont-size:14.4px;font-weight:900" border=3D"1"><tbody style=3D"box-sizing:bo= rder-box"><tr style=3D"box-sizing:border-box"><td style=3D"box-sizing:borde= r-box;background-color:rgb(255,255,255);padding:0.5ex"><tt style=3D"box-siz= ing:border-box;border-radius:0.25rem;margin:0px;font-weight:300;padding:0px= ;word-break:unset;font-size:inherit;color:inherit;background-color:inherit"= ><code style=3D"box-sizing:border-box;font-family:monospace,monospace;word-= break:unset;border-radius:0.25rem;margin:0px;padding:0px;font-size:inherit;= color:inherit;background-color:inherit">pg_trigger_depth()</code></tt></td>= <td style=3D"box-sizing:border-box;background-color:rgb(255,255,255);paddin= g:0.5ex"><tt style=3D"box-sizing:border-box;margin:0px;padding:0px;word-bre= ak:unset;background-color:inherit;color:inherit;font-size:inherit">int</tt>= </td><td style=3D"box-sizing:border-box;background-color:rgb(255,255,255);p= adding:0.5ex">current nesting level of=C2=A0<span style=3D"box-sizing:borde= r-box">PostgreSQL</span>=C2=A0triggers (0 if not called, directly or indire= ctly, from inside a trigger)</td></tr></tbody></table>because you're ca= lling your procedure by means of a trigger=C2=A0</div><div><a href=3D"https= ://www.postgresql.org/docs/12/functions-info.html" target=3D"_blank">https:= //www.postgresql.org/docs/12/functions-info.html</a>=C2=A0=C2=A0<br></div><= /div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">O= n Fri, Jan 3, 2020 at 9:46 PM Mike Martin <<a href=3D"mailto:mike@redtux= .plus.com" target=3D"_blank">[email protected]</a>> 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"ltr"><div dir= =3D"ltr"><div>This is the function</div><div><br></div><div>CREATE OR REPLA= CE FUNCTION public.tagfile_upd_su()<br>=C2=A0 =C2=A0 RETURNS trigger<br>=C2= =A0 =C2=A0 LANGUAGE 'plpgsql'<br>=C2=A0 =C2=A0 COST 100<br>=C2=A0 = =C2=A0 VOLATILE NOT LEAKPROOF<br>AS $BODY$<br>=C2=A0 =C2=A0 BEGIN<br><br> = WITH arrfile AS(SELECT fileid,tagfile,(regexp_split_to_array(tagfile,'/= '))[2:] filearr1 FROM tagfile_new),<br>arrfile2 AS(SELECT fileid,tagfil= e,filearr1[1:cardinality(filearr1)-1]||regexp_matches(filearr1[cardinality(= filearr1)],'(.*)\.(.*)') filearr<br>FROM arrfile)<br><br>UPDATE tag= file =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 really prefer not= to have a row level function. The Insert version works perfefectly.</div><= div>I have tried using pg_trigger_depth, but that stops the trigger running= at all</div><div><br></div><div>Trigger definition is <br></div><div><br><= /div><div>CREATE TRIGGER tagfile_uas<br>=C2=A0 =C2=A0 AFTER UPDATE<br>=C2= =A0 =C2=A0 ON public.tagfile<br>=C2=A0 =C2=A0 REFERENCING OLD TABLE tagfile= _old NEW TABLE AS tagfile_new<br>=C2=A0 =C2=A0 FOR EACH STATEMENT<br> --WHE= N (pg_trigger_depth() < 1)<br>=C2=A0 =C2=A0 EXECUTE PROCEDURE public.tag= file_upd_su()<br> ;</div><div>(please note commented out pg_trigger_depth w= hich stopped trigger firing at all<br></div></div><br><div class=3D"gmail_q= uote"><div dir=3D"ltr" class=3D"gmail_attr">On Sat, 4 Jan 2020 at 00:26, Re= ne Romero Benavides <<a href=3D"mailto:[email protected]" target= =3D"_blank">[email protected]</a>> 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">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, ev= en if it'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, J= an 3, 2020 at 6:06 PM Rene Romero Benavides <<a href=3D"mailto:rene.rome= [email protected]" target=3D"_blank">[email protected]</a>> wrote:<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">O= h, so you're defining transition relations (REFERENCING NEW TABLE, OLD = TABLE ) as in here?<div><pre style=3D"box-sizing:border-box;font-family:mon= ospace,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-bot= tom:1rem;background-color:rgb(248,249,250);padding:0.8rem">CREATE TRIGGER p= aired_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 <<a href=3D"mailto:[email protected]" target= =3D"_blank">[email protected]</a>> 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, <<a href=3D"mailto:rene.romero= [email protected]" target=3D"_blank">[email protected]</a>> 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">>=C2=A0 I can give code when I get home, but it'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:"Open Sans",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 <<a href=3D"mailto:[email protected]= s.com" rel=3D"noreferrer" target=3D"_blank">[email protected]</a>> 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'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"= >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> --0000000000005cb637059b66cb98--