Michael Glaesmann <grzm@myrealbox.com> writes:
> create function orders_monthly(date) returns orders_sum_qty as '
> select product_code, sum(qty)::integer as qty from orders
> where date between $1 and ($1::date _ ''1 month'' - ''1day''::interval)
> group by product_code
> ' language sql;
> I'm able to create the function, but it doesn't seem to work. Trying
> select product_code, sum(qty)::integer as qty from orders
> where date between '2003-07-01' and ('2003-07-01':: date + ''1 month''
> - ''1day''::interval)
> group by product_code;
> works just fine, and pretty quickly too. But trying
> select * from orders_monthly('2003-07-01');
> grinds away for a minute and then just gives me the first item and
> quantity, not the whole table.
The reason you get only one output is you declared the function to
return orders_sum_qty, rather than setof orders_sum_qty.
The reason it's slow is probably that you've been sloppy about
datatypes, preventing the planner from optimizing the query into an
indexscan. Adding an interval to a date produces a timestamp not a
date. You need to compare the date column to date constants. Try
coercing the result of the date/interval expression back to date.
regards, tom lane
