Re: query to select a linked list

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