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