Date: Wed, 9 May 2007 16:33:01 +0200
From: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
To: pgsql-sql@postgresql.org
Subject: Re: query to select a linked list
Message-ID: <20070509143301.GA20327@apartia.fr>
Mail-Followup-To: pgsql-sql@postgresql.org
References: <20070509125520.GA17497@apartia.fr>
	<20070509143021.GA20210@apartia.fr>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20070509143021.GA20210@apartia.fr>
User-Agent: Mutt/1.5.13 (2006-08-11)

On Wed, May 09, 2007 at 04:30:21PM +0200, Louis-David Mitterrand wrote:
> On Wed, May 09, 2007 at 02:55:20PM +0200, Louis-David Mitterrand 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?
> 
> I am trying to write a recursive pl/sql function to return all thread 
> children:
> 
> create or replace function forum_children(integer) returns setof forum as $$
> declare
>     rec record;
> begin
> 
>     for rec in select * from forum where $1 in (id_parent,id_forum) loop

Oops, I meant :

	for rec in select * from forum where id_parent=$1 loop

which works fine.

Sorry,

>         select * from forum_children(rec.id_forum);
>         return next rec;
> 
>     end loop;
> 
>     return;
> 
> end;
> $$ language 'plpgsql';
> 
> 
> But it does not work as intended (infinite loop?).
> 
> What did I miss?
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend