I'm wondering if anyone has taken the code from http://www.dbazine.com/tropashko4.shtml and converted it to PostgreSQL?
You can use the contrib/ltree type, which represents a path, and will be easier and faster to use.
http://www.sai.msu.su/~megera/postgres/gist/ltree/
Create a table with : node_id SERIAL PRIMARY KEY, parent_id INTEGER NULL REFERENCES yourtable( node_id ) ON DELETE CASCADE; full_path ltree NOT NULL
Create a gist index on ltree parent_id IS NULL implies the node is in the root of the tree
Add an ON INSERT/UPDATE TRIGGER which will fill the full_path with the parent's full_path + the node_id
Then you can use the ltree operators for very efficient querying !
Example :
folder_id | parent_id | full_path | title
-----------+-----------+-------------+-----------
1 | | 1 | root
10 | 1 | 1.10 | folder 9
109 | 10 | 1.10.109 | sub 68
139 | 10 | 1.10.139 | sub 98
29 | 10 | 1.10.29 | sub 8
128 | 29 | 1.10.29.128 | sub 87
158 | 29 | 1.10.29.158 | sub 117
68 | 29 | 1.10.29.68 | sub 27
98 | 29 | 1.10.29.98 | sub 57
49 | 10 | 1.10.49 | sub 8
79 | 10 | 1.10.79 | sub 38
11 | 1 | 1.11 | folder 10
110 | 11 | 1.11.110 | sub 69
140 | 11 | 1.11.140 | sub 99
30 | 11 | 1.11.30 | sub 9
129 | 30 | 1.11.30.129 | sub 88
159 | 30 | 1.11.30.159 | sub 118
69 | 30 | 1.11.30.69 | sub 28Getting the path to an element :
select folder_id, parent_id, full_path, title from folders WHERE full_path @> '1.10.29.128';
folder_id | parent_id | full_path | title
-----------+-----------+-------------+----------
1 | | 1 | root
10 | 1 | 1.10 | folder 9
29 | 10 | 1.10.29 | sub 8
128 | 29 | 1.10.29.128 | sub 87
Getting all children from a node (recursively) :
select folder_id, parent_id, full_path, title from folders WHERE full_path <@ '1.10';
folder_id | parent_id | full_path | title
-----------+-----------+-------------+----------
10 | 1 | 1.10 | folder 9
29 | 10 | 1.10.29 | sub 8
49 | 10 | 1.10.49 | sub 8
68 | 29 | 1.10.29.68 | sub 27
79 | 10 | 1.10.79 | sub 38
98 | 29 | 1.10.29.98 | sub 57
109 | 10 | 1.10.109 | sub 68
128 | 29 | 1.10.29.128 | sub 87
139 | 10 | 1.10.139 | sub 98
158 | 29 | 1.10.29.158 | sub 117
Isn't it nice ? Thanks to the gist/ltree team ! This contrib is great.
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
