Re: [SQL] date_trunc'd timestamp index possible?

Bruno Wolff III <bruno(at)wolff(dot)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