Skip site navigation (1) Skip section navigation (2)

Re: Processing Tables containing tree-like data

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 (view raw or flat)
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



In response to

pgsql-novice by date

Next:From: Michael SwierczekDate: 2007-05-29 18:06:23
Subject: Re: problems with SELECT query results
Previous:From: Richard Broersma JrDate: 2007-05-29 17:39:44
Subject: Re: Processing Tables containing tree-like data

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group