Re: Get A Tree from a table

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/