From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | harding(dot)ian(at)gmail(dot)com, General PostgreSQL List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: AFTER UPDATE trigger updating other records |
Date: | 2018-01-25 18:51:08 |
Message-ID: | e8f7f474-1b99-35ba-f317-8ede242b229c@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 01/24/2018 09:45 PM, Ian Harding wrote:
> I have a conditional after update trigger on a table that issues an
> update statement on the same table. The trigger does not fire
> recursively, and I know the rows are being updated, but the update is
> not happening in the same way the statement does when run from outside
> the trigger.
>
> --8<----------
>
> create extension if not exists ltree;
>
> create table area (
> areaid serial primary key,
> parentid int null references area (areaid),
> areapath ltree not null unique);
>
> insert into area (areapath) values ('Top');
> insert into area (parentid, areapath) values (1,'Top.Foo');
> insert into area (parentid, areapath) values (1,'Top.Bar');
> insert into area (parentid, areapath) values (3,'Top.Bar.Blah');
> insert into area (parentid, areapath) values (4,'Top.Bar.Blah.Scooby');
>
> select areaid, parentid, areapath from area order by areapath;
>
> CREATE OR REPLACE FUNCTION trig_areapath_u() RETURNS trigger AS
> $$
> BEGIN
> IF TG_OP = 'UPDATE' THEN
> IF (COALESCE(OLD.parentid,0) != COALESCE(NEW.parentid,0)) THEN
> UPDATE area SET areapath = (select areapath from area a
> where areaid = area.parentid) || subpath(areapath, nlevel(areapath) - 1)
> WHERE OLD.areapath @> areapath;
> END IF;
> END IF;
>
> RETURN NULL;
> END
> $$
> LANGUAGE 'plpgsql' VOLATILE;
>
> CREATE TRIGGER trig01_update_area_node_path AFTER UPDATE OF parentid ON
> area FOR EACH ROW
> EXECUTE PROCEDURE trig_areapath_u();
>
> update area set parentid = 2 where areaid = 4;
>
> select areaid, parentid, areapath from area order by areapath;
Was there supposed to be results shown for the above queries?
>
> -- This is not what I expect to see. I have even tried running the update
> -- unrestricted from within the trigger but I get the same result. From
> -- outside the trigger I run the update unrestricted...
>
> UPDATE area SET areapath = (select areapath from area a where areaid =
> area.parentid) || subpath(areapath, nlevel(areapath) - 1) where parentid
> is not null;
>
> -- And I see what I expected.
>
> select areaid, parentid, areapath from area order by areapath;
Have you verified that this condition:
WHERE OLD.areapath @> areapath;
is actually being met?
>
> --------->8-----
>
> I know this is simple, but I can't see it.
>
> Thank you!
>
> - Ian
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2018-01-25 19:10:26 | AFTER UPDATE trigger updating other records |
Previous Message | Rakesh Kumar | 2018-01-25 18:23:54 | Re: Sync replication |