Re: For each statement trigger and update table

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() = 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=a2.filearr
FROM arrfile2 a2
WHERE tf.fileid=a2.fileid AND a2.tagfilenew != 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 <rene(dot)romero(dot)b(at)gmail(dot)com>
wrote:

> Try with pg_trigger_depth() = 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 <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/
>>>
>>>
>
> --
> El genio es 1% inspiración y 99% transpiración.
> Thomas Alva Edison
> http://pglearn.blogspot.mx/
>
>