Thread: 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?
--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Jan 16, 2005, at 15:20, Jim C. Nasby wrote:

At least two. Here's one (blatant plug):

http://www.grzm.com/fornow/archives/2004/07/10/static_hierarchies

The other (which preceded mine) in the archives:

http://archives.postgresql.org/pgsql-general/2003-12/msg00247.php

The later Tropashko stuff is interesting as well, though still limited
by the same issues as this one afaics.

Hope that helps.

Michael Glaesemann
grzm myrealbox com

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