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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend