Thread: 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