Jamie Tufnell <die...@googlemail.com> writes: > AFAIK current_date is standard and 'now' and 'today', etc are not... > so that's one reason to continuing using current_date. However, I > wonder why 'today' and current_date don't generate the same query > plan?
'today'::date is a special string that is recognized by the date type's input routine, but what it produces is a constant, for instance '2009-02-05'::date if I executed it today. current_date is a function that produces the current date whenever it is executed. Consider for instance regression=# create view v as select current_date as d1, 'today'::date as d2; CREATE VIEW regression=# select * from v; d1 | d2 ------------+------------ 2009-02-05 | 2009-02-05 (1 row) regression=# \d v View "public.v" Column | Type | Modifiers --------+------+----------- d1 | date | d2 | date | View definition: SELECT 'now'::text::date AS d1, '2009-02-05'::date AS d2; As the view definition printout suggests, tomorrow this view will produce d1 | d2 ------------+------------ 2009-02-06 | 2009-02-05 because the constant isn't going to change. As you can see from both this example and your own, our current implementation of current_date is ('now'::text)::date. (Which sucks, we ought to hide it better ;-)) What that actually means is you have a constant of type text containing the string 'now', and when the expression is executed there's a *run time* cast to type date, causing the date input converter to get invoked at that time. So it'll still work tomorrow. Of course, the cost of the runtime interpretation of the string is what accounts for your speed difference. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql