For each statement trigger and update table

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

For each statement trigger and update table

Mike Martin-2
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 
Reply | Threaded
Open this post in threaded view
|

Re: For each statement trigger and update table

ichbinrene
>  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?

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 <[hidden email]> 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/

Reply | Threaded
Open this post in threaded view
|

Re: For each statement trigger and update table

Mike Martin-2
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, <[hidden email]> 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?

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 <[hidden email]> 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/

Reply | Threaded
Open this post in threaded view
|

Re: For each statement trigger and update table

ichbinrene
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 <[hidden email]> 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, <[hidden email]> 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?

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 <[hidden email]> 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/

Reply | Threaded
Open this post in threaded view
|

Re: For each statement trigger and update table

ichbinrene
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 <[hidden email]> 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 <[hidden email]> 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, <[hidden email]> 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?

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 <[hidden email]> 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/

Reply | Threaded
Open this post in threaded view
|

Re: For each statement trigger and update table

Mike Martin-2
Will do tomorrow 

On Sat, 4 Jan 2020, 00:26 Rene Romero Benavides, <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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, <[hidden email]> 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?

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 <[hidden email]> 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/

Reply | Threaded
Open this post in threaded view
|

Re: For each statement trigger and update table

Mike Martin-2
In reply to this post by ichbinrene
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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, <[hidden email]> 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?

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 <[hidden email]> 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/

Reply | Threaded
Open this post in threaded view
|

Re: For each statement trigger and update table

ichbinrene
Try with pg_trigger_depth() = 1
pg_trigger_depth()intcurrent 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 

On Fri, Jan 3, 2020 at 9:46 PM Mike Martin <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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, <[hidden email]> 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?

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 <[hidden email]> 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/

Reply | Threaded
Open this post in threaded view
|

Re: For each statement trigger and update table

Mike Martin-2

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 <[hidden email]> wrote:
Try with pg_trigger_depth() = 1
pg_trigger_depth()intcurrent 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 

On Fri, Jan 3, 2020 at 9:46 PM Mike Martin <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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, <[hidden email]> 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?

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 <[hidden email]> 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/

Reply | Threaded
Open this post in threaded view
|

Re: For each statement trigger and update table

ichbinrene
Ok, great you figured it out. 

On Sun, Jan 5, 2020 at 10:11 AM Mike Martin <[hidden email]> 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 <[hidden email]> wrote:
Try with pg_trigger_depth() = 1
pg_trigger_depth()intcurrent 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 

On Fri, Jan 3, 2020 at 9:46 PM Mike Martin <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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, <[hidden email]> 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?

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 <[hidden email]> 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/

Reply | Threaded
Open this post in threaded view
|

Re: For each statement trigger and update table

Erik Brandsberg
In reply to this post by Mike Martin-2
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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, <[hidden email]> 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?

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 <[hidden email]> 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
[hidden email]

www.heimdalldata.com
+1 (866) 433-2824 x 700
AWS Competency Program