From: Gregory Stark <stark@enterprisedb.com>
To: pgsql-sql@postgresql.org
Subject: Re: query to select a linked list
In-Reply-To: <20070509125520.GA17497@apartia.fr> (Louis-David Mitterrand's
	message of "Wed, 9 May 2007 14:55:20 +0200")
Organization: EnterpriseDB
References: <20070509125520.GA17497@apartia.fr>
Date: Wed, 09 May 2007 14:24:22 +0100
Message-ID: <878xbyi0zd.fsf@oxford.xeocode.com>
User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/21.4 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

"Louis-David Mitterrand" <vindex+lists-pgsql-sql@apartia.org> writes:

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

You would need recursive queries which Postgres doesn't support. There is a
patch out there to add support but I don't think it's up-to-date with 8.2 and
in any case the resulting queries can be quite intense.

I would recommend you look into the contrib module named "ltree". It's easy to
use and works well with the gist indexes. It does require changing your data
model denormalizing it slightly which makes it hard to "reparent" children,
but if that isn't an operation you have to support I think it makes most other
operations you might want to do much easier to support.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com