Mechanics of Select
От | Willem Buitendyk |
---|---|
Тема | Mechanics of Select |
Дата | |
Msg-id | 47AF850B.9010701@pcfish.ca обсуждение исходный текст |
Ответы |
Re: Mechanics of Select
Re: Mechanics of Select Re: Mechanics of Select Re: Mechanics of Select |
Список | 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;
В списке pgsql-general по дате отправления: