Обсуждение: Custom function problems
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
On Tue, 21 Oct 2003, Michael Glaesmann wrote: > 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; I think you want setof orders_sum_qty for the return type (otherwise you're only returning one row as below). I'd also wonder if this ends up using a sequence scan rather than an index scan because it doesn't know which will be better for an arbitrary $1 which might explain a difference in performance. How does the following run in comparison? create or replace function orders_monthly(date) returns setof orders_sum_qty as ' DECLARE r record; BEGIN FOR r IN EXECUTE ''select product_code, sum(qty)::integer as qty from orders where date between '''''' || $1 || '''''' and ('''''' || $1 || ''''''::date + ''''1 month''''::interval - ''''1 day''''::interval) group by product_code'' LOOP RETURN NEXT r; END LOOP; RETURN; END;' language 'plpgsql';
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