Subject: Re: query to select a linked list
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Aaron Bono <postgresql@aranya.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <bf05e51c0705090629l7232a6d2we87170b2168c1c82@mail.gmail.com>
References: <20070509125520.GA17497@apartia.fr>
	<bf05e51c0705090629l7232a6d2we87170b2168c1c82@mail.gmail.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1178733840.7497.59.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Wed, 09 May 2007 13:04:01 -0500

On Wed, 2007-05-09 at 08:29, Aaron Bono wrote:
> On 5/9/07, Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
> wrote:
>         Hi,
>         
>         To build a threaded forum application I came up the following
>         schema:
>         
>         forum
>         ------
>         id_forum | integer| not null  default
>         nextval('forum_id_forum_seq'::regclass)
>         id_parent| integer|
>         subject  | text   | not null 
>         message  | text   |
>         
>         Each message a unique id_forum and an id_parent pointing to
>         the replied
>         post (empty if first post).
>         
>         How can I build an elegant query to select all messages in a
>         thread?
>         
>         Thanks, 
> 
> Unlike Oracle, PostgreSQL doesn't have anything like a connect by so
> you would need to write your own stored procedure for that (if new
> versions of PostgreSQL will have connect by, let me know guys). 

Take a look at the tablefunc in contrib, that seems to provide
connectby.  I've only ever used the crosstab functions in there, but I
can't see what would have broken in connectby with 8.2 or anything.