logic/db question

Typically, a temp table is used for this.

Beware of circular references.

Fred -> Wilma -> Betty -> Barney -> Fred

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of bruce
Sent: Wednesday, August 02, 2006 11:55 AM
To: 'PgSQL-General'
Subject: [GENERAL] logic/db question

hi...

i have a tbl
fooTBL
name
parentID
ID

so a name can have might have a parentID, as well as an ID. 'name's

are

associated with other 'name's via the parentID. in other words, if a
name's
parentID == a name's ID, name1 is the parent of name2.

name parentID ID
foo - 1
cat 1 2
dog 2 3

my question, how can i come up with a sql query that will list all the
children (and children's children...) of a top level item?

i had done this awhile ago.. but can't recall how i did it..

thanks

---------------------------(end of

broadcast)---------------------------

On 8/2/06, bruce <bedouglas@earthlink.net> wrote:

hi...

i have a tbl
fooTBL
name
parentID
ID

so a name can have might have a parentID, as well as an ID. 'name's are
associated with other 'name's via the parentID. in other words, if a name's
parentID == a name's ID, name1 is the parent of name2.

name parentID ID
foo - 1
cat 1 2
dog 2 3

my question, how can i come up with a sql query that will list all the
children (and children's children...) of a top level item?

i had done this awhile ago.. but can't recall how i did it..

you can try tablefunc contrib module, or recursive pl/pgsql

http://people.planetpostgresql.org/merlin/index.php?/archives/2-Dealing-With-Recursive-Sets-With-PLPGSQL.html

merlin

If table reorganization is an option for you, you could consider using
integer ranges for describing parant/children relationships.

I have seen them in one of Joe Celko books:

http://www.amazon.com/gp/product/1558609202/sr=8-2/qid=1154595988/ref=pd_bbs_2/104-2243146-1376759?ie=UTF8

In that way, you can find all the children with just one query.

Regards
Marco

On 8/2/06, bruce <bedouglas@earthlink.net> wrote:

hi...

i have a tbl
fooTBL
name
parentID
ID

so a name can have might have a parentID, as well as an ID. 'name's are
associated with other 'name's via the parentID. in other words, if a name's
parentID == a name's ID, name1 is the parent of name2.

name parentID ID
foo - 1
cat 1 2
dog 2 3

my question, how can i come up with a sql query that will list all the
children (and children's children...) of a top level item?

i had done this awhile ago.. but can't recall how i did it..

thanks

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--
Marco Bizzarri
http://notenotturne.blogspot.com/