Re: query to select a linked list

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,