From: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
To: pgsql-sql@postgresql.org
Subject: Re: query to select a linked list
Date: Wed, 9 May 2007 16:30:21 +0200
Message-ID: <20070509143021.GA20210@apartia.fr> (raw)
In-Reply-To: <20070509125520.GA17497@apartia.fr>
References: <20070509125520.GA17497@apartia.fr>
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: vindex+lists-pgsql-sql@apartia.org
Subject: Re: query to select a linked list
In-Reply-To: <20070509143021.GA20210@apartia.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox