On Wed, 20 Feb 2002 14:06:46 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.
Okey.It turned out that the setting time zone was insufficient -- but I alsounderstand that users need to avoid the
operationsfor which robustness/reliability is lost.
>
> 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).
Thanks a lot. there are likely to be opportunities of making frequentuse of it.
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
Regards,
Masaru Sugawara