Willem Buitendyk <willem@xxxxxxxxx> 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? An explain of the query would help you answer that question. > If so > any ideas how I could make this only occur once? Don't mark it as VOLITILE. Sounds like an IMMUTABLE function to me. PostgreSQL is doing exactly what you told it to do. > 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; > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster -- Bill Moran http://www.potentialtech.com ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
