Re: Mechanics of Select

Willem Buitendyk wrote:
> I have the following function that returns the first day of the next
> month from whatever date is inserted.  If I use this as part of a select
> statement then it takes almost twice as long to perform.  Is this
> because for each scanned record this function is being called?  If so
> any ideas how I could make this only occur once?
>
> For instance:
>
> select * from track where datetime >= '2007-04-01' and datetime <
> '2007-05-01'; takes about 30 ms to return 650K rows.
>
> select * from track where datetime >= '2007-04-01' and datetime <
> first_day_next_month'2007-04-01'; takes about 55 ms to return 650K rows
>
> CREATE OR REPLACE FUNCTION first_day_next_month(inputdate date)
>  RETURNS date AS
> $BODY$
> declare
> inputmonth1 integer;
> inputyear1 integer;
> inputmonth2 integer;
> inputyear2 integer;
> resultdate date;
> BEGIN
> inputmonth1 = extract(month from inputdate)::integer;  inputyear1 =
> extract(year from inputdate)::integer;
>
> if inputmonth1 = 12 then
> inputyear2 = inputyear1 + 1;
> else
> inputyear2 = inputyear1;
> end if;
>
> if inputmonth1 = 12 then
> inputmonth2 = 1;
> else
> inputmonth2 = inputmonth1 + 1;
> end if;
>
> resultdate = (inputyear2)::text || '-' || (inputmonth2)::text || '-' ||
> '01';
> resultdate = to_date(resultdate::text,'yyyy-MM-DD');
>
> RETURN resultdate;
> END;
> $BODY$
>  LANGUAGE 'plpgsql' VOLATILE
>  COST 100;
>


Try:

CREATE OR REPLACE FUNCTION first_day_next_month(inputdate date)
  RETURNS date AS
$BODY$
DECLARE
    resultdate date;
BEGIN
    SELECT INTO resultdate to_date(to_char((inputdate + interval \
'1 month'), 'yyyy-MM') || '-01', 'yyyy-mm-dd');
    RETURN resultdate;
END;
$BODY$
LANGUAGE 'plpgsql';

Mind the wrap.