On 18 Nov 2002 at 1:09, [EMAIL PROTECTED] wrote: > > -----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.
This is how it is now done. I wanted to be able to so this fairly quickly: select * from tree where pathname like '/usr/local/%' in order to get the subtree below a given point. Sorry I didn't mention that before. > > 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) That's good. Thank you. -- Dan Langille : http://www.langille.org/ ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
