From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Gregory Stark <stark@enterprisedb.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: query to select a linked list
Date: Wed, 09 May 2007 13:03:06 -0500
Message-ID: <1178733786.7497.57.camel@state.g2switchworks.com> (raw)
In-Reply-To: <878xbyi0zd.fsf@oxford.xeocode.com>
References: <20070509125520.GA17497@apartia.fr>
<878xbyi0zd.fsf@oxford.xeocode.com>
On Wed, 2007-05-09 at 08:24, Gregory Stark wrote:
> "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.
Are you sure the tablefunc functions, which include both connectby and
crosstab functions, aren't up to date with 8.2? They certainly are up
to 8.1, where I'm running them right now on my workstation. They built
for 8.2 and installed, but I haven't tried using them.
I would think that connectby is at least worth looking into.
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: smarlowe@g2switchworks.com, stark@enterprisedb.com
Subject: Re: query to select a linked list
In-Reply-To: <1178733786.7497.57.camel@state.g2switchworks.com>
* 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