Actually, you can even use this approach for frequently updated data.
Here is the pseudo-code for a pl/pgsql function that adds a new node
to the tree.
* SELECT MAX(node_id) FROM table WHERE parent_id = node_parent
* UPDATE table SET node_id=node_id+1 WHERE node_id > max_node_id
* INSERT new node with node_id = max_node_id+1 and parent_id = node_parent
Inserting new nodes into the chain would be more complex, but not impossible.
Burak
On 5/31/07, Burak Seydioglu <buraks78@gmail.com> wrote:
> What is the frequency that this data is updated?
>
> If the data is static (or if you can get away with running a cron job
> every now and then), you can write a recursive pl/pgslq function to
> get level information for each node on the tree and assign a specific
> "incremental" node_id for each record. Due to the nature of the
> recursive function, a node_id is assigned to the children of a
> specific node instead of its siblings. You should end up with data as
> illustrated below.
>
> id info parent_id level node_id
> 1 Name1 Null 1 1
> 2 Name2 1 2 2
> 3 Name3 2 3 3
> 4 Name4 3 4 4
> 5 Name5 4 5 5
> 6 Name5 1 2 6
> 7 Name6 6 3 7
> 8 Name7 1 2 8
>
> Then you can simply retrieve the children of node (N) on level (L)
> with a single statement.
>
> SELECT * FROM table WHERE node_id > N AND node_id < (SELECT node_id
> FROM table WHERE level = L AND node_id > N ORDER BY node_id LIMIT 1
> OFFSET 0);
>
> Refrain from using MIN() as performance suffers unbelievably.
>
> Hope this helps,
>
> Burak
>
>
> On 5/29/07, psql-novice@netzach.co.il <psql-novice@netzach.co.il> wrote:
> >
> > Hi,
> >
> > I have a table which looks like this:
> >
> > id info parentid
> > 0 <God> 0
> > 1 Adam 0
> > 2 Cain 1
> > 3 Abel 1
> > 4 Seth 1
> > 5 Enosh 4
> > ....
> >
> >
> >
> > I am looking for a fast and efficient way of finding ALL the descendents
> > of any particular node, to unlimited depth.
> >
> > Is there a standard database trick for doing this efficiently ? Writing
> > a recursive function would be extremely inefficient for repeated
> > queries.
> >
> > Thanks,
> >
> >
> > Netzach
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> > choose an index scan if your joining column's datatypes do not
> > match
> >
>
