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(at)apartia(dot)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.