From: | Michael Glaesemann <grzm(at)seespotcode(dot)net> |
---|---|
To: | psql-novice(at)netzach(dot)co(dot)il |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Processing Tables containing tree-like data |
Date: | 2007-05-29 17:48:45 |
Message-ID: | CA0A01F9-5465-4D8F-87BD-4263D843FE2F@seespotcode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On May 29, 2007, at 11:58 , psql-novice(at)netzach(dot)co(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Swierczek | 2007-05-29 18:06:23 | Re: problems with SELECT query results |
Previous Message | Richard Broersma Jr | 2007-05-29 17:39:44 | Re: Processing Tables containing tree-like data |