From: | Todd Kover <kovert(at)omniscient(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | splitting up a row in a table |
Date: | 2004-10-06 21:54:48 |
Message-ID: | 200410062154.i96Lsmrb005508@guinness.omniscient.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
I'm running postgresql-7.4.3.
I have a table that resembles:
create table foo (
event_id integer primary key,
start timestamp,
finish timestamp
);
It contains a bunch of rows, some that straddle months. (so the start
event may be in August but the end may be in September, for example).
There are multiple events for a given month.
I need to generate a report that shows the sum of the duration of all
the events for a given month (or range of months). For events that
extend over multiple months, I only want the portion that overlaps the
part of the month the report is for (so with the above example, a report
for september would cut off the part from august).
I've found lots of references to functions where I can do something like
select * from chopped_report();
and have written one for this purpose. However, most of the reports
will be for one month (or a six month period) and the only way I can
find to limit the work the function does is to pass a date range into
the function. I'm concerned that I effectively end up generating a new
version of the 'foo' table doing the above if I don't pass in a date
range.
Having the date range restiction makes it impossible to ultimately
code this as a view, which I want to do. Although I could probably
do a materialized view, the activity levels on the table make this
undesireable.
What would be really nice is if I could do something like:
select chop_nodes(*) from foo;
and have chop_nodes do the work on the events that match the criteria in
a where clause and end up returning multiple rows that way. This would
likely address my performance concerns.
near as I can tell, this isn't possible, but I figured I'd ask just in
case I'm missing something in the docs/google searching.
thanks in advance,
-Todd
From | Date | Subject | |
---|---|---|---|
Next Message | John Browne | 2004-10-07 05:05:12 | Conditional Relationships? |
Previous Message | Joe Erickson | 2004-10-06 20:16:37 | Re: Brand New User (I hope) |