Re: query to select a linked list

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.