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@redtux.plus.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.romero.b@gmail.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.romero.b@gmail.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@redtux.plus.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.romero.b@gmail.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@redtux.plus.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@heimdalldata.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>