Re: For each statement trigger and update table
Rene Romero Benavides <[email protected]> Sun, 5 Jan 2020 13:42:30 -0600
| Newsgroups | gmane.comp.db.postgresql.sql |
|---|---|
| Message-ID | <CANaGW08Ph41i3cUoVoiD4r2yMC0ipF+fWONHrxdc=6HG5dEhDg@mail.gmail.com> |
--000000000000060994059b69bf6b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Ok, great you figured it out. On Sun, Jan 5, 2020 at 10:11 AM Mike Martin <[email protected]> wrote: > > 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= (filearr1[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(file= arr1[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 versio= n >>> 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 firin= g >>> 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= 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, >>>>> 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 tha= t >>>>>>> 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 i= n >>>>>>>> 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/ >>>> >>>> >> >> -- >> 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/ --000000000000060994059b69bf6b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr">Ok, great you figured it out.=C2=A0</div><br><div class=3D= "gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Sun, Jan 5, 2020 at = 10:11 AM Mike Martin <<a href=3D"mailto:[email protected]">mike@redtu= x.plus.com</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"><div><br></div><div>Thanks I had looked at that= before, but it turns out I had a small error in my criteria. altered to</d= iv><div> IF pg_trigger_depth() =3D 1 THEN<br> </div><div><br></div><div>W= ITH arrfile AS(SELECT a.fileid,a.tagfile tagfilenew,(regexp_split_to_array(= a.tagfile,'/'))[2:] filearr1 <br></div><div>FROM <br></div><div>tag= file_new a),</div><div><br></div><div>arrfile2 AS(SELECT fileid,o.tagfile t= agfileold,tagfilenew,filearr1[1:cardinality(filearr1)-1]||regexp_matches(fi= learr1[cardinality(filearr1)],'(.*)\.(.*)') filearr<br>FROM arrfile= a <br></div><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.fi= learr<br>FROM arrfile2 a2<br> WHERE tf.fileid=3Da2.fileid =C2=A0AND a2.tagf= ilenew !=3D a2.tagfileold;<br>END IF;</div><div><br></div><div>I misunderst= ood the docs and realised I needed to compare old table and new table rathe= r than tagfile 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 Rom= ero Benavides <<a href=3D"mailto:[email protected]" target=3D"_bla= nk">[email protected]</a>> wrote:<br></div><blockquote class=3D"gm= ail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,= 204,204);padding-left:1ex"><div dir=3D"ltr">Try with pg_trigger_depth() =3D= 1<div><table style=3D"box-sizing:border-box;border-collapse:collapse;backg= round-color:rgb(224,236,239);border:2px solid rgb(222,226,230);border-spaci= ng:0px;width:initial;margin:2ex 0px;color:rgb(51,103,145);font-family:"= ;Open Sans",sans-serif;font-size:14.4px;font-weight:900" border=3D"1">= <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);paddin= g:0.5ex"><tt style=3D"box-sizing:border-box;border-radius:0.25rem;margin:0p= x;font-weight:300;padding:0px;word-break:unset;font-size:inherit;color:inhe= rit;background-color:inherit"><code style=3D"box-sizing:border-box;font-fam= ily:monospace,monospace;word-break:unset;border-radius:0.25rem;margin:0px;p= adding:0px;font-size:inherit;color:inherit;background-color:inherit">pg_tri= gger_depth()</code></tt></td><td style=3D"box-sizing:border-box;background-= color:rgb(255,255,255);padding:0.5ex"><tt style=3D"box-sizing:border-box;ma= rgin:0px;padding:0px;word-break:unset;background-color:inherit;color:inheri= t;font-size:inherit">int</tt></td><td style=3D"box-sizing:border-box;backgr= ound-color:rgb(255,255,255);padding:0.5ex">current nesting level of=C2=A0<s= pan style=3D"box-sizing:border-box">PostgreSQL</span>=C2=A0triggers (0 if n= ot called, directly or indirectly, from inside a trigger)</td></tr></tbody>= </table>because you're calling 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.h= tml</a>=C2=A0=C2=A0<br></div></div><br><div class=3D"gmail_quote"><div dir= =3D"ltr" class=3D"gmail_attr">On Fri, Jan 3, 2020 at 9:46 PM Mike Martin &l= t;<a href=3D"mailto:[email protected]" target=3D"_blank">[email protected]= s.com</a>> wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"ma= rgin: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>CREATE OR REPLACE 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>ar= rfile2 AS(SELECT fileid,tagfile,filearr1[1:cardinality(filearr1)-1]||regexp= _matches(filearr1[cardinality(filearr1)],'(.*)\.(.*)') filearr<br>F= ROM arrfile)<br><br>UPDATE tagfile =C2=A0tf SET filearr=3Da2.filearr<br>FRO= M arrfile2 a2<br>WHERE EXISTS (SELECT 1 FROM arrfile2 af WHERE tf.fileid=3D= af.fileid AND af.tagfile !=3D tf.tagfile);</div><div>END</div><div><br></di= v><div>Would really prefer not to have a row level function. The Insert ver= sion 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 defi= nition 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> --WHEN (pg_trigger_depth() < 1)<br>=C2=A0 =C2= =A0 EXECUTE PROCEDURE public.tagfile_upd_su()<br> ;</div><div>(please note = commented out pg_trigger_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 at 00:26, Rene Romero Benavides <<a href=3D"mailto:r= [email protected]" target=3D"_blank">[email protected]</a>> w= rote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0p= x 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 he= lp 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 w= ith 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= <<a href=3D"mailto:[email protected]" target=3D"_blank">rene.rome= [email protected]</a>> wrote:<br></div><blockquote class=3D"gmail_quote" st= yle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padd= ing-left:1ex"><div dir=3D"ltr">Oh, so you're defining transition relati= ons (REFERENCING NEW TABLE, OLD TABLE ) as in here?<div><pre style=3D"box-s= izing: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 <<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> </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> --000000000000060994059b69bf6b--