Re: For each statement trigger and update table
Mike Martin <[email protected]> Sat, 4 Jan 2020 03:45:49 +0000
| Newsgroups | gmane.comp.db.postgresql.sql |
|---|---|
| Message-ID | <CAOwYNKZ3pEghi366gvCQGhEiOdkMF0a3Pkhnr6f=PPUu-yOUTQ@mail.gmail.com> |
--00000000000057fbb0059b48452c
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
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(filearr1=
[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 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 colum=
n
>>> 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 togeth=
er
>>>>>
>>>>> 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/
>
>
--00000000000057fbb0059b48452c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
<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_spl=
it_to_array(tagfile,'/'))[2:] filearr1 FROM tagfile_new),<br>arrfil=
e2 AS(SELECT fileid,tagfile,filearr1[1:cardinality(filearr1)-1]||regexp_mat=
ches(filearr1[cardinality(filearr1)],'(.*)\.(.*)') filearr<br>FROM =
arrfile)<br><br>UPDATE tagfile =C2=A0tf SET filearr=3Da2.filearr<br>FROM ar=
rfile2 a2<br>WHERE EXISTS (SELECT 1 FROM arrfile2 af WHERE tf.fileid=3Daf.f=
ileid AND af.tagfile !=3D tf.tagfile);</div><div>END</div><div><br></div><d=
iv>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 definiti=
on 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 REF=
ERENCING OLD TABLE tagfile_old NEW TABLE AS tagfile_new<br>=C2=A0 =C2=A0 FO=
R EACH STATEMENT<br> --WHEN (pg_trigger_depth() < 1)<br>=C2=A0 =C2=A0 EX=
ECUTE PROCEDURE public.tagfile_upd_su()<br> ;</div><div>(please note commen=
ted 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 Sa=
t, 4 Jan 2020 at 00:26, Rene Romero Benavides <<a href=3D"mailto:rene.ro=
[email protected]">[email protected]</a>> wrote:<br></div><blockquo=
te 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">Mike, please incl=
ude 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 ca=
se, even if it's simple stuff, please share with us your code.=C2=A0</d=
iv><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:ren=
[email protected]" target=3D"_blank">[email protected]</a>> wro=
te:<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">Oh, so you're defining transition relations (REFERENCING NEW TABLE=
, OLD TABLE ) as in here?<div><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;background-color:rgb(248,249,250);padding:0.8rem">CREATE TRI=
GGER 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>
--00000000000057fbb0059b48452c--