On 21/04/16 14:47, Tom Lane wrote:
> 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():
>
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