Thread: For each statement trigger and update table

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 

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

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/

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/

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/

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/

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/

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/

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/