Re: PostgreSQL code for nested sets - Mailing list pgsql-general

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