Re: query to select a linked list

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
       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?