Can SQL return a threaded-comment-view result set? - Mailing list pgsql-general

[I also posted this to comp.databases but since I'm actually using
PostgreSQL I would be content with a pgsql specific answer, so I
also posted this to comp.databases.postgresql.novice because I'm
a novice and it looked like a good group, but then I realized that
this might not be a novice question even though I'm a novice, so
I guess this group comp.databases.postgresql.general is the last
that I'll try.]

I think I already know that the answer is that this can't be done, but
I'll ask anyways.

Suppose you want to use an RDBMS to store messages for a threaded
message forum like usenet and then display the messages. A toy table
definition (that I've tried to make standards compliant) might look
like:

create table messages (
       message_id integer,
       in_reply_to integer,
       created date,
       author varchar(20),
       title varchar(30),
       message varchar(256),
       primary key (message_id)
);


The in_reply_to field, if not null, means that the message is a reply
to the message with the message_id it has stored. Suppose now that we
populate the database with a 5 message discussion.


insert into messages values
 (1, null, '2003-09-01', 'John', 'Favorite DB?',
                     'What is your favorite database?');
insert into messages values
 (2, null, '2003-09-02', 'Mike', 'New DB2 benchmarks',
                     'I just posted some new DB2 benchmarks.');
insert into messages values
 (3,    1, '2003-09-03', 'Mike', 'Re: Favorite DB?',
                 'I\'d say DB2.');
insert into messages values
 (4,    1, '2003-09-05', 'Dave', 'Re: Favorite DB?',
                     'I\'m an Oracle man myself.');
insert into messages values
 (5,    3, '2003-09-07', 'John', 'Re: Favorite DB?',
                 'DB2? I thought you liked free databases?');


If we rendered an oldest-first threaded view of the discussion it
would look like:

Author: John
Title:  Favorite DB?
Date:   2003-09-01
What is your favorite database?

    Author: Mike
    Title:  Re: Favorite DB?
    Date:   2003-09-03
    I'd say DB2.

        Author: John
        Title:  Re: Favorite DB?
        Date:   2003-09-07
        DB2? I thought you liked free databases?.

    Author: Dave
    Title:  Re: Favorite DB?
    Date:   2003-09-05
    I'm an Oracle man myself.

Author: Mike
Title:  New DB2 benchmarks
Date:   2003-09-02
I just posted some new DB2 benchmarks.


My question is: is it possible to use pure SQL to return a result set
that would make rendering a threaded view like the above really easy?
That is, is there an SQL query that would return something like:


 i | r |  created   | auth |       title        |  message  | nesting
---+---+------------+------+--------------------+-----------+---------
 1 |   | 2003-09-01 | John | Favorite DB?       | What is y | 0
 3 | 1 | 2003-09-03 | Mike | Re: Favorite DB?   | I'd say D | 1
 5 | 3 | 2003-09-07 | John | Re: Favorite DB?   | DB2? I th | 2
 4 | 1 | 2003-09-05 | Dave | Re: Favorite DB?   | I'm an Or | 1
 2 |   | 2003-09-02 | Mike | New DB2 benchmarks | I just po | 0


If I had an SQL query that could return that then it would be very
easy to have a computer program print threaded views like the one
above.

If this can't be done, then do any of you have recommendations about
the best way to accomplish this with the least amount of inefficient
back-and-forth between the database and, say, Java or some other
language?

Thank you very much in advance for any answers! This has been a
frustrating matter for me.

Chris