Re: setting time zone in a function

On 21/04/16 14:47, Tom Lane wrote:
> Steve Rogerson <steve(dot)pg(at)yewtc(dot)demon(dot)co(dot)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():
>

That works. Thanks.

> 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.

I wonder what counts as a valid time zone, I wasn't expecting this:

# set timezone = '==2.77';
SET
# select now();
now
-------------------------------
2016-04-18 09:40:52.089375-77
(1 row)

In my context I'm expecting an Olson type designation, "Europe/Madrid", I
guess that's hard to check for. I *think* I can live with the consequences, or
rather let a higher level deal with the problem. I would consider the above
time zone to be invalid.

Steve