Masaru Sugawara <rk73@echna.ne.jp> writes:
> (1)create function mydate(timestamp) returns date as '
> select date($1);
> ' language 'sql' with (iscachable);
If you do it that way then you are simply opening yourself up to exactly
the error that the noncachability check is trying to save you from
making.
You could probably do it safely by hard-wiring the time zone to be used
into the function. I think something like this would work:
create function mydate(timestamp with time zone) returns date as ' select date($1 AT TIME ZONE ''EST'');
' language 'sql' with (iscachable);
(substitute time zone of your choice, of course).
BTW, if the table is at all large then you'd probably be better off to
use a plpgsql function instead. SQL-language functions are rather
inefficient IIRC.
regards, tom lane
