Thread: different results selecting from base tables or views

I discovered different results when selecting data from base tables or
their views. The only thing I can image for this strange behaviour is using
the date constant 'today' when creating the view. Does 'today' in a view
use the current day time of the view creation or the time the query is made
?

magic moving pixel s.a.   http://www.mmp.lu

> I discovered different results when selecting data from base tables or
> their views. The only thing I can image for this strange behaviour is 
> using the date constant 'today' when creating the view. Does 'today' 
> in a view use the current day time of the view creation or the time 
> the query is made?

That is probably the problem. For most data types, a string constant is
really constant, and Postgres evaluates it once, during parsing. For a
few data types this is not correct behavior, since the "constant" should
be evaluated at run time.

The workaround, at least in some cases, is to force the string constant
to be a real string type. Postgres will then do the conversion to your
intended type at run time.

I'm not sure what your view looks like, but the same issue comes up when
defining default values for columns:
 create table t1 (d datetime default 'now');

gives unexpected results, while
 create table t2 (d datetime default text 'now');

does what you would (presumably) prefer.
                   - Tom