Re: Processing Tables containing tree-like data

["Burak Seydioglu" <buraks78(at)gmail(dot)com>]

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(at)netzach(dot)co(dot)il <psql-novice(at)netzach(dot)co(dot)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