Re: setting time zone in a function

Steve Rogerson <steve.pg@yewtc.demon.co.uk> writes:
> I want the time zone if a function  a bit like ...
> CREATE OR REPLACE FUNCTION
>    short_name (timestamp with time zone, varchar) RETURNS varchar  AS $$
>     DECLARE
>         ...
>     BEGIN
>         SET LOCAL TIME ZONE $2;

Nope, that won't work: in general you can't put parameters into
utility commands.  Use set_config():

    PERFORM set_config('timezone', $2, true);

Also, I think "SET LOCAL" has transaction duration, not function duration,
so you're going to have to work harder than this if you want the setting
to be local to this function.  SET LOCAL/set_config(true) inside an
exception block might work --- and you may well want an exception block
anyway, to trap bad timezone names.  Or you could explicitly save and
restore the previous setting, which is more code but might be faster
than an exception block.

            regards, tom lane