From: | Willem Buitendyk <willem(at)pcfish(dot)ca> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Mechanics of Select |
Date: | 2008-02-10 23:13:15 |
Message-ID: | 47AF850B.9010701@pcfish.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Klint Gore | 2008-02-10 23:22:39 | copy question - fixed width? |
Previous Message | Willem Buitendyk | 2008-02-10 22:57:52 | Re: pg_restore seems slow |