[SQL] query to select a linked list

2007-05-09 Thread Louis-David Mitterrand
Hi, To build a threaded forum application I came up the following schema: forum -- id_forum | integer| not null default nextval('forum_id_forum_seq'::regclass) id_parent| integer| subject | text | not null message | text | Each message a unique id_forum and an id_parent pointing to

Re: [SQL] query to select a linked list

2007-05-09 Thread Gregory Stark
Louis-David Mitterrand [EMAIL PROTECTED] 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

Re: [SQL] query to select a linked list

2007-05-09 Thread Louis-David Mitterrand
On Wed, May 09, 2007 at 02:24:22PM +0100, Gregory Stark wrote: Louis-David Mitterrand [EMAIL PROTECTED] 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?

Re: [SQL] query to select a linked list

2007-05-09 Thread Aaron Bono
On 5/9/07, Louis-David Mitterrand [EMAIL PROTECTED] wrote: Hi, To build a threaded forum application I came up the following schema: forum -- id_forum | integer| not null default nextval('forum_id_forum_seq'::regclass) id_parent| integer| subject | text | not null message | text |

Re: [SQL] query to select a linked list

2007-05-09 Thread Achilleas Mantzios
Στις Τετάρτη 09 Μάιος 2007 15:55, ο/η Louis-David Mitterrand έγραψε: Hi, To build a threaded forum application I came up the following schema: forum -- id_forum | integer| not null default nextval('forum_id_forum_seq'::regclass) id_parent| integer| subject | text | not null

Re: [SQL] query to select a linked list

2007-05-09 Thread Louis-David Mitterrand
On Wed, May 09, 2007 at 02:55:20PM +0200, Louis-David Mitterrand wrote: Hi, To build a threaded forum application I came up the following schema: forum -- id_forum | integer| not null default nextval('forum_id_forum_seq'::regclass) id_parent| integer| subject | text | not null

Re: [SQL] query to select a linked list

2007-05-09 Thread Louis-David Mitterrand
On Wed, May 09, 2007 at 04:30:21PM +0200, Louis-David Mitterrand wrote: On Wed, May 09, 2007 at 02:55:20PM +0200, Louis-David Mitterrand wrote: Hi, To build a threaded forum application I came up the following schema: forum -- id_forum | integer| not null default

Re: [SQL] query to select a linked list

2007-05-09 Thread Scott Marlowe
On Wed, 2007-05-09 at 08:24, Gregory Stark wrote: Louis-David Mitterrand [EMAIL PROTECTED] 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

Re: [SQL] query to select a linked list

2007-05-09 Thread Scott Marlowe
On Wed, 2007-05-09 at 08:29, Aaron Bono wrote: On 5/9/07, Louis-David Mitterrand [EMAIL PROTECTED] wrote: Hi, To build a threaded forum application I came up the following schema: forum -- id_forum | integer| not null

Re: [SQL] query to select a linked list

2007-05-09 Thread Gregory Stark
Scott Marlowe [EMAIL PROTECTED] writes: 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

Re: [SQL] query to select a linked list

2007-05-09 Thread Robert Edwards
Hi Louis-David, I also have written a forum application using PostgreSQL. My schema has a threadid for each posting, which is actually also the messageid of the first posting in the thread, but that is irrelevant. I can then just select all messages belonging to that thread. The actual