Re: Trouble with pg_dumpall import with 7.2

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