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.
The solution can be very postgresql specific, because I don't intend to run it
on any other db server.
Any ideas anyone?
THX
UC
--
Open Source Solutions 4U, LLC 1618 Kelly St
Phone: +1 707 568 3056 Santa Rosa, CA 95401
Cell: +1 650 302 2405 United States
Fax: +1 707 568 6416
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
