Re: For the SQL gurus out there

Uwe C. Schroeder wrote:
> Ok, something I've been toying around with for a while.
> Here's the scenario:
> Imagine a blogging app.
> I have a table for blogs with a blog_id (primary key)
> and a table blog_comments also with a comment_id as primary key and a foreign
> key holding the blog_id the post belongs to.
> The comments table also has a field that holds a self-reference to comment id
> for commments on comments (on comments) of a blog.
>
> What I would like to do is to create a view that sucks the comments for a
> given blog_id in the order they should be displayed (very commonly seen in
> pretty much all blogging apps), i.e.
>
> Blog
> comment 1
>   comment on comment 1
>     comment on comment on comment 1
> comment 2
> etc.
>
>
> Question is, is there a smart way I'm not able to figure out to create a
> single query on the blog comment table that will return the comments in the
> right order? Sure I could write a recursive method that assembles the data in
> correct order, but I'd prefer to leave that to the database to handle in a
> view.

What you are looking for is the 'connectby' function found in contrib as
part of the tablefunc package.  On my CentOS 5.1 box, it's part of this RPM:

----------------------------------------
 > rpm -qi postgresql-contrib
Name        : postgresql-contrib           Relocations: (not relocatable)
Version     : 8.2.4                             Vendor: (none)
Release     : 1PGDG                         Build Date: Fri 20 Apr 2007
01:58:54 PM CDT
Install Date: Sun 16 Sep 2007 12:27:55 AM CDT      Build Host:
rhel5x8664.gunduz.org
Group       : Applications/Databases        Source RPM:
postgresql-8.2.4-1PGDG.src.rpm
Size        : 1724563                          License: BSD
Signature   : DSA/SHA1, Fri 20 Apr 2007 02:14:40 PM CDT, Key ID
a667b5d820579f11
URL         : http://www.postgresql.org/
Summary     : Contributed source and binaries distributed with PostgreSQL
Description :
The postgresql-contrib package contains contributed packages that are
included in the PostgreSQL distribution.
----------------------------------------

And the files you want to look at is here:

   /usr/share/doc/postgresql-contrib-8.2.4/README.tablefunc

It talks about connectby here:

----------------------------------------
...
Name

connectby(text, text, text[, text], text, text, int[, text]) - returns a
set representing a hierarchy (tree structure)

Synopsis

connectby(text relname, text keyid_fld, text parent_keyid_fld
             [, text orderby_fld], text start_with, int max_depth
                                                 [, text branch_delim])
...
----------------------------------------

Do some searching for 'connectby' and PostgreSQL in google.

-- Dante