Re: For each statement trigger and update table - Mailing list pgsql-sql
| From | Mike Martin |
|---|---|
| Subject | Re: For each statement trigger and update table |
| Date | |
| Msg-id | CAOwYNKZ3pEghi366gvCQGhEiOdkMF0a3Pkhnr6f=PPUu-yOUTQ@mail.gmail.com Whole thread Raw |
| In response to | Re: For each statement trigger and update table (Rene Romero Benavides <rene.romero.b@gmail.com>) |
| Responses |
Re: For each statement trigger and update table
Re: For each statement trigger and update table |
| List | pgsql-sql |
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);
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()
;
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 togetherOn 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 stuffplease 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 <mike@redtux.plus.com> wrote:Not sure if this is possibleBasically I want to have a trigger which updates an array column in the same table when a column is updatedThis works as a row level trigger, but not as per statementI have hit the recursive issue (where update fires update trigger which fires etc)According to the docs I cannot use columns and relative tables togetherSo any suggestions? I can give code when I get home, but it's pretty simple stuff------