From: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
To: pgsql-sql@postgresql.org
Subject: Re: query to select a linked list
Date: Thu, 10 May 2007 10:05:09 +0200
Message-ID: <20070510080509.GA17255@apartia.fr> (raw)
In-Reply-To: <46425E0C.8080804@cs.anu.edu.au>
References: <20070509125520.GA17497@apartia.fr>
<46425E0C.8080804@cs.anu.edu.au>
On Thu, May 10, 2007 at 09:49:32AM +1000, Robert Edwards wrote:
>
> Hi Louis-David,
>
> I also have written a forum application using PostgreSQL.
>
> My schema has a "threadid" for each posting, which is actually also the
> "messageid" of the first posting in the thread, but that is irrelevant.
>
> I can then just select all messages belonging to that thread. The actual
> hierarchy of messages (which posting is in response to which) is dealt
> with by a "parentid", identifying the messageid of the post being
> responded to. Sorting that out is done by the middleware (PHP in this
> case) - the SQL query simply returns all messages in the thread in a
> single query. Because our database is somewhat busy, I have opted to
> keep the queries to the database simple and let the middleware sort
> out the heirarchical structure (which it is quite good at).
>
> I hope this helps.
This helps a lot, thanks.
I just wrote a little pl/sql function to compensate for the absence of a
threadid in my schema:
create or replace function forum_children(integer) returns setof forum as $$
declare
rec record;
subrec record;
begin
for rec in select * from forum where id_parent=$1 loop
return next rec;
for subrec in select * from forum_children(rec.id_forum) loop
return next subrec;
end loop;
end loop;
return;
end;
$$ language 'plpgsql';
But in the end it might just be more convenient and clear to have that
threadid column as you did.
Sorting in middleware (perl in my case) also seems like good compromise.
Cheers,
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: <20070510080509.GA17255@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