Re: Trouble with pg_dumpall import with 7.2

On Wed, 20 Feb 2002 14:06:46 -0500
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Masaru Sugawara <rk73(at)echna(dot)ne(dot)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 also
understand that users need to avoid the operations for 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 frequent
use 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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

Regards,
Masaru Sugawara