Re: Trees: maintaining pathnames

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