Re: For each statement trigger and update table

Have you printed out what the value of pg_trigger_depth() is? Without
testing, my guess is that it is starting with a value of 1, not 0, and as
such prevents execution from the start.

On Fri, Jan 3, 2020 at 10:46 PM Mike Martin <mike(at)redtux(dot)plus(dot)com> 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(filearr1[cardinality(filearr1)],'(.*)\.(.*)')
> filearr
> FROM arrfile)
>
> UPDATE tagfile tf SET filearr=a2.filearr
> FROM arrfile2 a2
> WHERE EXISTS (SELECT 1 FROM arrfile2 af WHERE tf.fileid=af.fileid AND
> af.tagfile != 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 <
> rene(dot)romero(dot)b(at)gmail(dot)com> 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 <
>> rene(dot)romero(dot)b(at)gmail(dot)com> 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 <mike(at)redtux(dot)plus(dot)com> 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, <
>>>> rene(dot)romero(dot)b(at)gmail(dot)com> 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 <mike(at)redtux(dot)plus(dot)com>
>>>>> 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ón y 99% transpiración.
>>>>> Thomas Alva Edison
>>>>> http://pglearn.blogspot.mx/
>>>>>
>>>>>
>>>
>>> --
>>> El genio es 1% inspiración y 99% transpiración.
>>> Thomas Alva Edison
>>> http://pglearn.blogspot.mx/
>>>
>>>
>>
>> --
>> El genio es 1% inspiración y 99% transpiración.
>> Thomas Alva Edison
>> http://pglearn.blogspot.mx/
>>
>>

--
*Erik Brandsberg*
erik(at)heimdalldata(dot)com

www.heimdalldata.com
+1 (866) 433-2824 x 700
[image: AWS Competency Program]
<https://aws.amazon.com/partners/find/partnerdetails/?n=Heimdall%20Data&id=001E000001d9pndIAA>