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
