Re: [SQL] Trees: maintaining pathnames

[Joe Conway]

Dan Langille wrote:

Given that I'm considering adding a new field path_name to the tree, I can't see the ltree package will give me anything more than I can get from like. My main reason for adding path_name was doing queries such as:

select * from tree where path_name like '/path/to/parent/%'

which will return me all the descendants of a give node (in this case '/path/to/parent/'.[2]

FWIW, you could also do this with connectby() in contrib/tablefunc (new in 7.3; see the README for syntax details):

test=# SELECT t.* FROM connectby('tree', 'id', 'parent_id', '1', 0, '~') AS c(id int, parent_id int, level int, branch text), tree t WHERE t.id = c.id;
id | parent_id | name
----+-----------+--------------------
1 | | Top
2 | 1 | Science
3 | 2 | Astronomy
4 | 3 | Astrophysics
5 | 3 | Cosmology
6 | 1 | Hobbies
7 | 6 | Amateurs_Astronomy
8 | 1 | Collections
9 | 8 | Pictures
10 | 9 | Astronomy
11 | 10 | Stars
12 | 10 | Galaxies
13 | 10 | Astronauts
(13 rows)

test=# SELECT t.* FROM connectby('tree', 'id', 'parent_id', '6', 0, '~') AS c(id int, parent_id int, level int, branch text), tree t WHERE t.id = c.id;
id | parent_id | name
----+-----------+--------------------
6 | 1 | Hobbies
7 | 6 | Amateurs_Astronomy
(2 rows)

test=# SELECT t.* FROM connectby('tree', 'id', 'parent_id', '8', 0, '~') AS c(id int, parent_id int, level int, branch text), tree t WHERE t.id = c.id;
id | parent_id | name
----+-----------+-------------
8 | 1 | Collections
9 | 8 | Pictures
10 | 9 | Astronomy
11 | 10 | Stars
12 | 10 | Galaxies
13 | 10 | Astronauts


You could also do:

CREATE OR REPLACE FUNCTION node_id(text) returns int as 'select id from tree where name = $1' language 'sql';

test=# SELECT t.* FROM connectby('tree', 'id', 'parent_id', node_id('Science'), 0) AS c(id int, parent_id int, level int), tree t WHERE t.id = c.id;
id | parent_id | name
----+-----------+--------------
2 | 1 | Science
3 | 2 | Astronomy
4 | 3 | Astrophysics
5 | 3 | Cosmology
(4 rows)

I have discussed [offlist] the option of using a secondary table to store the pathname (i.e. a cach table) which would be updated using a loop in the tigger instead of using cascading triggers. I would prefer to keep the pathname in the same table.

In my application, I have about 120,000 nodes in the tree. I am using PL/pgSQL quite a lot. Perhaps moving the triggers to C at a later date may provide a speed increase if the tree expands considerably.

I've tested connectby() on a table with about 220,000 nodes. It is pretty fast (about 1 sec to return a branch with 3500 nodes), and is entirely dynamic (requires no triggers).

Joe


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster