Re: Custom function problems

Michael Glaesmann <grzm(at)myrealbox(dot)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