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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
