Re: For each statement trigger and update table

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/
>
>