Re: For each statement trigger and update table

Ok, great you figured it out.

On Sun, Jan 5, 2020 at 10:11 AM Mike Martin <mike(at)redtux(dot)plus(dot)com> wrote:

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

--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/