Received: from brasileiro.net (slxwy.dorms.usu.edu [129.123.236.160]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9TMmDP14204 for ; Mon, 29 Oct 2001 17:48:13 -0500 (EST) (envelope-from roberto@brasileiro.net) Received: from roberto by brasileiro.net with local (Exim 3.32 #1 (Debian)) id 15yLCE-00033j-00; Mon, 29 Oct 2001 15:47:54 -0700 Date: Mon, 29 Oct 2001 15:47:54 -0700 From: Roberto Mello To: Jason Kwok Cc: pgsql-sql@postgresql.org Subject: Re: Recursive select Message-ID: <20011029154754.A11730@cc.usu.edu> References: <00af01c15deb$74289940$0201a8c0@newhonest.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <00af01c15deb$74289940$0201a8c0@newhonest.com> User-Agent: Mutt/1.3.22i X-Archive-Number: 200110/405 X-Sequence-Number: 5357 On Fri, Oct 26, 2001 at 02:57:18PM +0800, Jason Kwok wrote: > I have a table : > > MyID parentID > ================ > 5 6 > 6 7 > 3 13 > 7 3 > > Is there any simple select statement that can get all rows with MyID = 5 and > all its parents? Dan Wickstrom, of the OpenACS.org team, implemented a bunch of PL/pgSQL functions to do what you're after. It simulates the CONNECT BY behaviour in Oracle. OpenACS 4 is GPL'd, so you can look at the source. I need to extract the relevant sections and post them in the PostgreSQL Cookbook (actually, I think there are a couple tree functions in there - http://www.brasileiro.net/postgres/) -Roberto -- +----| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Always forgive your enemies. They hate that!