Re: PostgreSQL code for nested sets

> 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 28

Getting 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.