Horton

Thread

  1. For each statement trigger and update table

    Mike Martin <mike@redtux.plus.com> — 2020-01-03T23:09:23Z

    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

  2. Re: For each statement trigger and update table

    Rene Romero Benavides <rene.romero.b@gmail.com> — 2020-01-03T23:39:18Z

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

  3. Re: For each statement trigger and update table

    Mike Martin <mike@redtux.plus.com> — 2020-01-03T23:55:18Z

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

  4. Re: For each statement trigger and update table

    Rene Romero Benavides <rene.romero.b@gmail.com> — 2020-01-04T00:06:48Z

    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/

  5. Re: For each statement trigger and update table

    Rene Romero Benavides <rene.romero.b@gmail.com> — 2020-01-04T00:26:34Z

    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/

  6. Re: For each statement trigger and update table

    Mike Martin <mike@redtux.plus.com> — 2020-01-04T00:32:32Z

    Will do tomorrow

On Sat, 4 Jan 2020, 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/
>
>

  7. Re: For each statement trigger and update table

    Mike Martin <mike@redtux.plus.com> — 2020-01-04T03:45:49Z

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

  8. Re: For each statement trigger and update table

    Rene Romero Benavides <rene.romero.b@gmail.com> — 2020-01-05T01:49:28Z

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

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

  9. Re: For each statement trigger and update table

    Mike Martin <mike@redtux.plus.com> — 2020-01-05T16:10:47Z

    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.romero.b@gmail.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@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/
>>>
>>>
>
> --
> El genio es 1% inspiración y 99% transpiración.
> Thomas Alva Edison
> http://pglearn.blogspot.mx/
>
>

  10. Re: For each statement trigger and update table

    Rene Romero Benavides <rene.romero.b@gmail.com> — 2020-01-05T19:42:30Z

    Ok, great you figured it out.

On Sun, Jan 5, 2020 at 10:11 AM Mike Martin <mike@redtux.plus.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.romero.b@gmail.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@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/
>>>>
>>>>
>>
>> --
>> 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/

  11. Re: For each statement trigger and update table

    Erik Brandsberg <erik@heimdalldata.com> — 2020-01-06T13:53:26Z

    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>