[SQL] query to select a linked list
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 the replied
post (empty if first post).
How can I build an elegant query to select all messages in a thread?
Thanks,
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] query to select a linked list
"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 a patch out there to add support but I don't think it's up-to-date with 8.2 and in any case the resulting queries can be quite intense. I would recommend you look into the contrib module named "ltree". It's easy to use and works well with the gist indexes. It does require changing your data model denormalizing it slightly which makes it hard to "reparent" children, but if that isn't an operation you have to support I think it makes most other operations you might want to do much easier to support. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] query to select a linked list
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? > > You would need recursive queries which Postgres doesn't support. There is a > patch out there to add support but I don't think it's up-to-date with 8.2 and > in any case the resulting queries can be quite intense. > > I would recommend you look into the contrib module named "ltree". It's easy to > use and works well with the gist indexes. It does require changing your data > model denormalizing it slightly which makes it hard to "reparent" children, > but if that isn't an operation you have to support I think it makes most other > operations you might want to do much easier to support. After looking around a little I came to the same conclusions. Thanks for you help, Cheers, ---(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
Re: [SQL] query to select a linked list
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 |
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?
Thanks,
Unlike Oracle, PostgreSQL doesn't have anything like a connect by so you
would need to write your own stored procedure for that (if new versions of
PostgreSQL will have connect by, let me know guys).
What I did was add a little redundancy to my forum tables and had a table
structure kind of like this:
forum
forum_id BIGSERIAL PK,
name VARCHAR(50)
forum_topic
forum_topic_id BIGSERIAL PK,
forum_id BIGINT FK to forum
forum_post
forum_post_id BIGSERIAL PK,
create_dt TIMESTAMP,
subject VARCHAR(255),
message TEXT,
forum_topic_id BIGINT FK to forum_topic
and if you want threading, you add a parent_forum_post_id to forum_post
(this is where you get the redundancy since only the top forum_post record
needs a reference to forum_topic and forum_topic wouldn't even really be
needed.
-Aaron
--
==
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==
Re: [SQL] query to select a linked list
Στις Τετάρτη 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
> message | text |
>
> 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?
>
Your question is about storing hierarchies in postgresql.
One way is to use the genealogical approach, where we store for
any node the path to its root.
I have used this technique to store description of tanker vessels machinery
(over 1M items) and the plan maintenance on them, and the performance is very
good, while the representation is highly intuitive and flexible,
unlike some wierd approcahes i have hit on.
When i did a small research on the complexity/index usage on various
operations (UPDATE, INSERT, DELETE, SELECT), the performance
was at least as good as the "nested pair" approch that many seemed to
promote.
You add a column "parents" (rather than just the parentid) as an integer[].
For every node you store the path to the root node starting from the most
immediate ancestor.
Then you just make an index on this column using the intarray contrib package.
Then you can easily query for nodes under a specific node, or for nodes just
one level below a specific node, nodes with no descendents (leaf nodes)
etc...
Of course you could do smth simpler, but in the long run,
representing data in the correct way will certainly pay off.
> Thanks,
>
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly
--
Achilleas Mantzios
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] query to select a linked list
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
> message | text |
>
> 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?
I am trying to write a recursive pl/sql function to return all thread
children:
create or replace function forum_children(integer) returns setof forum as $$
declare
rec record;
begin
for rec in select * from forum where $1 in (id_parent,id_forum) loop
select * from forum_children(rec.id_forum);
return next rec;
end loop;
return;
end;
$$ language 'plpgsql';
But it does not work as intended (infinite loop?).
What did I miss?
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] query to select a linked list
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
> > 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 the replied
> > post (empty if first post).
> >
> > How can I build an elegant query to select all messages in a thread?
>
> I am trying to write a recursive pl/sql function to return all thread
> children:
>
> create or replace function forum_children(integer) returns setof forum as $$
> declare
> rec record;
> begin
>
> for rec in select * from forum where $1 in (id_parent,id_forum) loop
Oops, I meant :
for rec in select * from forum where id_parent=$1 loop
which works fine.
Sorry,
> select * from forum_children(rec.id_forum);
> return next rec;
>
> end loop;
>
> return;
>
> end;
> $$ language 'plpgsql';
>
>
> But it does not work as intended (infinite loop?).
>
> What did I miss?
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] query to select a linked list
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 recursive queries which Postgres doesn't support. There is a > patch out there to add support but I don't think it's up-to-date with 8.2 and > in any case the resulting queries can be quite intense. > > I would recommend you look into the contrib module named "ltree". It's easy to > use and works well with the gist indexes. It does require changing your data > model denormalizing it slightly which makes it hard to "reparent" children, > but if that isn't an operation you have to support I think it makes most other > operations you might want to do much easier to support. 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 tried using them. I would think that connectby is at least worth looking into. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] query to select a linked list
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 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
> the replied
> post (empty if first post).
>
> How can I build an elegant query to select all messages in a
> thread?
>
> Thanks,
>
> Unlike Oracle, PostgreSQL doesn't have anything like a connect by so
> you would need to write your own stored procedure for that (if new
> versions of PostgreSQL will have connect by, let me know guys).
Take a look at the tablefunc in contrib, that seems to provide
connectby. I've only ever used the crosstab functions in there, but I
can't see what would have broken in connectby with 8.2 or anything.
---(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
Re: [SQL] query to select a linked list
"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 tried using them. > > I would think that connectby is at least worth looking into. Uhm, no, I guess I'm not sure. I didn't realize it was in the tablefunc module either. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] query to select a linked list
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
hierarchy of messages (which posting is in response to which) is dealt
with by a "parentid", identifying the messageid of the post being
responded to. Sorting that out is done by the middleware (PHP in this
case) - the SQL query simply returns all messages in the thread in a
single query. Because our database is somewhat busy, I have opted to
keep the queries to the database simple and let the middleware sort
out the heirarchical structure (which it is quite good at).
I hope this helps.
Bob Edwards.
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
message | text |
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?
Thanks,
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
[SQL] Passing input to a view?
Is it possible to define a view to use input parameters rather than a hard-wired value in a where clause? I.e. I have the following view: CREATE OR REPLACE VIEW invoiced_repairs AS SELECT ro_header.ro_number, ro_header.received_date, vehicles.vehicle_id, vehicles.model, vehicles.engine, vehicles.transmission, ro_header.odometer_reading, ro_header.service_description, ( CASE WHEN (vehicles.month_of_manufacture <> '' AND vehicles.year_of_manufacture <> '') THEN (vehicles.month_of_manufacture || '/' || vehicles.year_of_manufacture) ELSE '' END ) AS date_of_manufacture, vehicles.identification_number, vehicles.engine_number from ro_header INNER JOIN vehicles USING (vehicle_address) WHERE ro_header.received_date between '1-jan-2007' AND '10-May-2007'; ALTER TABLE invoiced_repairs OWNER TO postgres; Instead of having it set to 1-Jan-2007 and 10-May-2007 for the dates, can I specify that those values should be passed to the view? Or would I need to change this to an SQL language function? I can't find anything in the documentation that answers this, but maybe I'm not looking hard enough. Thanks in advance. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Passing input to a view?
am Thu, dem 10.05.2007, um 14:03:53 +0800 mailte Paul Lambert folgendes: > Is it possible to define a view to use input parameters rather than a > hard-wired value in a where clause? As far as i know, no. > Instead of having it set to 1-Jan-2007 and 10-May-2007 for the dates, > can I specify that those values should be passed to the view? Or would I > need to change this to an SQL language function? > > I can't find anything in the documentation that answers this, but maybe > I'm not looking hard enough. You can write a so called 'set returning function' instead. Read more: http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Passing input to a view?
Remove the WHERE clause that specifies the date so the view includes all dates, then apply the WHERE clause when selecting the view. SELECT * FROMinvoiced_repairs WHERE invoiced_repairs.received_date BETWEEN '1 Jan 2007' AND '10 May 2007'; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Paul Lambert Sent: Thursday, 10 May 2007 16:04 To: pgsql-sql@postgresql.org Subject: [SQL] Passing input to a view? Is it possible to define a view to use input parameters rather than a hard-wired value in a where clause? I.e. I have the following view: CREATE OR REPLACE VIEW invoiced_repairs AS SELECT ro_header.ro_number, ro_header.received_date, vehicles.vehicle_id, vehicles.model, vehicles.engine, vehicles.transmission, ro_header.odometer_reading, ro_header.service_description, ( CASE WHEN (vehicles.month_of_manufacture <> '' AND vehicles.year_of_manufacture <> '') THEN (vehicles.month_of_manufacture || '/' || vehicles.year_of_manufacture) ELSE '' END ) AS date_of_manufacture, vehicles.identification_number, vehicles.engine_number from ro_header INNER JOIN vehicles USING (vehicle_address) WHERE ro_header.received_date between '1-jan-2007' AND '10-May-2007'; ALTER TABLE invoiced_repairs OWNER TO postgres; Instead of having it set to 1-Jan-2007 and 10-May-2007 for the dates, can I specify that those values should be passed to the view? Or would I need to change this to an SQL language function? I can't find anything in the documentation that answers this, but maybe I'm not looking hard enough. Thanks in advance. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
