I've been working on increasing the performance of a large query by
writing a function that returns a table summary. The original table is
orders (product_code TEXT FK products(id), date DATE, branch_id INTEGER
FK branches(id), qty INTEGER)
I'd like to get a summary of total qty sold for each item during a
given period, such as
select product_code, qty from orders
where date between '2003-07-01' and '2003-07-31'
group by product_code
So I made this function:
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;
with corresponding type
create type orders_sum_qty as (product_code text, qty integer);
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.
I thought what I was doing is pretty straightforward, and am at a loss
as to what's wrong. Any ideas or suggestions of where to look for
solutions would be most welcome.
Thanks!
Michael
