Date: Thu, 10 May 2007 10:05:09 +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: <20070510080509.GA17255@apartia.fr>
Mail-Followup-To: pgsql-sql@postgresql.org
References: <20070509125520.GA17497@apartia.fr>
	<46425E0C.8080804@cs.anu.edu.au>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <46425E0C.8080804@cs.anu.edu.au>
User-Agent: Mutt/1.5.13 (2006-08-11)

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,