Re: Processing Tables containing tree-like data

On May 29, 2007, at 11:58 , psql-novice@netzach.co.il wrote:

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

What you've got there is often called an adjacency list. Check out
the connect_by function in the tablefunc contrib module (contrib/
tablefunc). You might also want to google for SQL nested sets to find
an alternative method of doing handling trees.

Michael Glaesemann
grzm seespotcode net