Re: Trees: maintaining pathnames

-----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 IS NULL;
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 | greg
6 | 5 | etc
(6 rows)

SELECT * FROM mytree ORDER BY id;

id | parent_id | name | path
----+-----------+------+----------------
1 | | | /
2 | 1 | usr | /usr
3 | 1 | tmp | /tmp
4 | 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(at)turnstep(dot)com
PGP Key: 0x14964AC8 200211172015

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE92D9RvJuQZxSWSsgRAn2oAKDyIcrtgB8v1fAMY3B/ITKZ+lBlYgCfXRMe
W/xntabEsfuEdseo44cAXbY=
=MANm
-----END PGP SIGNATURE-----