Search Postgresql Archives

Re: Mechanics of Select

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux