Re: Custom function problems

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';