Bruno Wolff III <bruno@wolff.to> writes:
> I am not sure how to check if the supplied function for converting
> a timestamp with time zone to a timestamp without timezone using a
> specified time zone is immutable. I think this function should be
> immutable, but that it probably isn't.
Yup. In 7.4:
regression=# select provolatile from pg_proc where oid = 'timezone(text,timestamptz)'::regprocedure;
provolatile
-------------
s
(1 row)
regression=#
This is a thinko that's already been corrected for 8.0:
regression=# select provolatile from pg_proc where oid = 'timezone(text,timestamptz)'::regprocedure;
provolatile
-------------
i
(1 row)
regression=#
If you wanted you could just UPDATE pg_proc to correct this mistake.
Another possibility is to create a function that's an IMMUTABLE
wrapper around the standard function.
Looking at this, I realize that date_trunc() is mismarked: the
timestamptz variant is strongly dependent on the timezone setting
and so should be STABLE not IMMUTABLE. Ooops.
regards, tom lane