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.klaver@xxxxxxxxxxx
