Re: Trees: maintaining pathnames
От | Joe Conway |
---|---|
Тема | Re: Trees: maintaining pathnames |
Дата | |
Msg-id | 3DDC053A.8060300@joeconway.com обсуждение исходный текст |
Ответ на | Trees: maintaining pathnames (Dan Langille <dan@langille.org>) |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: