Обсуждение: setting time zone in a function

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;
        SELECT to_char($1, 'TZ')::varchar
        ...
    END $$
LANGUAGE 'plpgsql' VOLATILE;


But as written is doesn't compile complaining about the "naked" $2. If I quote
the $2 it sets the time zone to $2 literally (I didn't know you could do that,
but that's another issue). Escaping the quotes either as \' or '' doesn't compile.

Help.


Steve

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

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

Steve Rogerson <steve.pg@yewtc.demon.co.uk> writes:
> I wonder what counts as a valid time zone

http://www.postgresql.org/docs/9.5/static/datatype-datetime.html#DATATYPE-TIMEZONES

> ... I wasn't expecting this:

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

Postgres is *very* lax about what is a valid timezone abbreviation in
the POSIX notation, mainly because the underlying Olson code is too.
I think it's taking that as '==' being the standard zone abbreviation
and '.' being the DST zone abbreviation.

If you wanted to restrict input to be the Olson-style zone names,
you could do some kind of precheck, maybe insist on only letters/
slashes/underscores.

            regards, tom lane