From: "Josh Berkus" <josh@agliodbs.com>
Subject: Re: Get A Tree from a table
To: gzscott2001@yahoo.ca (Scott Yaung), pgsql-sql@postgresql.org
Date: Mon, 07 Oct 2002 09:40:37 -0700
Message-ID: <web-1773971@davinci.ethosmedia.com>
In-Reply-To: <341834e1.0210052137.7cbd6342@posting.google.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 8bit

Scott,

> NODES__________________________________
>  ParentID    Varchar(20)
>  ParentType  varchar(20)
>  ChildID     varchar(20)
>  ChildType   varchar(20)

This looks difficult... mainly becuase, in your shema, a "parent" node
can be its own child or grandchild, resulting in an endless loop.

If that was not your intention, you may wish to think of using a
different structure.

There are two main ways for you to approach this problem in 7.2.x:
1) Buy Joe Celko's "SQL for Smarties" and read up on pure-SQL tree
implementations, which he covers in far more depth than I want to here;
2) Explore the tree module in /contrib in your Postgresql source.

I understand from Joe Conway that in 7.3, you will have another choice,
as Postgres will offer support for Oracle's tree-like "IS CONNECTED BY"
expression.

Good luck!

-Josh Berkus

P.S. also, there's some articles on tree structures up at techdocs:
 http://techdocs.postgresql.org/