-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message
Instead of storing the path in each row, why not let Postgres
take care of computing it with a function? Then make a view
and you've got the same table, without all the triggers.
CREATE TABLE tree (id INTEGER NOT NULL,parent_id INTEGER,"name" TEXT NOT NULL,PRIMARY KEY (id)
);
INSERT INTO tree VALUES (1,NULL,'');
INSERT INTO tree VALUES (2,1,'usr');
INSERT INTO tree VALUES (3,1,'tmp');
INSERT INTO tree VALUES (4,1,'home');
INSERT INTO tree VALUES (5,4,'greg');
INSERT INTO tree VALUES (6,5,'etc');
CREATE OR REPLACE FUNCTION pathname(INTEGER)
RETURNS TEXT AS
'
DECLARE mypath TEXT; myname TEXT; myid INTEGER;
BEGIN
SELECT parent_id,name FROM tree WHERE id=$1 INTO myid,mypath; IF mypath IS NULL THEN RETURN ''No such id\n''; END
IF;
LOOP SELECT parent_id,name FROM tree WHERE id=myid INTO myid,myname; mypath := ''/'' || mypath; EXIT WHEN myid
ISNULL; mypath := myname || mypath; END LOOP;
RETURN mypath;
END;
' LANGUAGE 'plpgsql';
CREATE VIEW mytree AS SELECT *, PATHNAME(id) AS path FROM tree;
SELECT * FROM tree ORDER BY id;
id | parent_id | name
----+-----------+------ 1 | | 2 | 1 | usr 3 | 1 | tmp 4 | 1 | home 5 | 4 |
greg6 | 5 | etc
(6 rows)
SELECT * FROM mytree ORDER BY id;
id | parent_id | name | path
----+-----------+------+---------------- 1 | | | / 2 | 1 | usr | /usr 3 | 1 | tmp |
/tmp4 | 1 | home | /home 5 | 4 | greg | /home/greg 6 | 5 | etc | /home/greg/etc
(6 rows)
UPDATE tree SET name='users' WHERE id=4;
SELECT * FROM mytree ORDER BY id;
id | parent_id | name | path
----+-----------+-------+----------------- 1 | | | / 2 | 1 | usr | /usr 3 | 1 | tmp
|/tmp 4 | 1 | users | /users 5 | 4 | greg | /users/greg 6 | 5 | etc | /users/greg/etc
(6 rows)
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200211172015
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iD8DBQE92D9RvJuQZxSWSsgRAn2oAKDyIcrtgB8v1fAMY3B/ITKZ+lBlYgCfXRMe
W/xntabEsfuEdseo44cAXbY=
=MANm
-----END PGP SIGNATURE-----