See Simon's reply... timestamptz math is *not* IMMUTABLE, because sessions are free to change their timezone at any time. I bet you can get some invalid results using that function with a clever test case.

On Mar 26, 2007, at 3:48 PM, Weslee Bilodeau wrote:

Weslee Bilodeau wrote:
Mainly its because the value comes from a reporting system that has
minimal brains, it passes values it gets from the user directly into a

IE, they enter '1 month', which I use to populate the interval value,
"ts > ( NOW() - $VALUE )"

But, in the example I did a "timestamp - interval", the exact date, not
NOW() - Still didn't work.

I'm guessing anything that has to think, math, etc is not valid for

Its not in the docs anywhere, so trying to isolate what can and can't be

This works -

CREATE FUNCTION now_interval( interval ) RETURNS timestamptz IMMUTABLE

SELECT count(*) FROM master WHERE var_ts > now_interval( '1 month' );

This doesn't work -

SELECT count(*) FROM master WHERE var_ts > ( NOW() - '1 month'::interval );

This works for me, as the reporting system I know doesn't change
timezones, and function cache doesn't last longer then the current select?

But, its basically the exact same logic in both cases?


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
       message can get through to the mailing list cleanly

Jim Nasby                                            [EMAIL PROTECTED]
EnterpriseDB      512.569.9461 (cell)

---------------------------(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

Reply via email to