Subject: Re: reversion? Recursion question
From: Brad Hilton <bhilton@vpop.net>
To: Patrick Hatcher <PHatcher@macys.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: 
 <OFE1236E52.91368B73-ON88256CFD.007BF75E-88256D09.007D1BBA@fds.com>
References: 
 <OFE1236E52.91368B73-ON88256CFD.007BF75E-88256D09.007D1BBA@fds.com>
Content-Type: text/plain
Organization: 
Message-Id: <1050451222.13093.22.camel@aragorn.vpop.net>
Mime-Version: 1.0
Date: 15 Apr 2003 17:00:22 -0700
Content-Transfer-Encoding: 7bit

On Tue, 2003-04-15 at 15:41, Patrick Hatcher wrote:
> Hello,
> For lack of a better title, I need to a reverse recursion.  Where I have
> the parent ID, but then I need to find all it's children, grandchildren,
> great-grandchildern, etc for the parent-id.  I guess this would be like a
> directory and all its sub-directories.

You may want to look at another way of doing this.  Joe Celko gives an
example of "nested sets" which allows you to select tree-like sets from
your database very efficiently.  His book "SQL for Smarties" has a
chapter on this, or google for "celko sql tree" and you'll find some
articles he's written on it.  Here's a link to one:
http://www.intelligententerprise.com/001020/celko.shtml.

I just converted a project from the approach you outlined to the nested
sets approach, and it has really helped.  The general idea is to think
of your tree as a bunch of ovals which fall inside or next to one
another.  I'll let you read his article - he explains things fairly
well.

The problem with your recursive query approach is that you'll have to do
one query per item in your tree which quickly gets out of hand.  With
the nested sets approach you can get everything in one simple query.  It
also allows you to do nice things like find all ancestors of a given
node (parent, grandparent, great-grandparent, etc), or find all
leaf-nodes (items that have no children).  The only tricky part can be
writing the appropriate triggers to insert, remove, and move items in
your tree.

I hope this helps,
-Brad