Re: Time zone offset in to_char()

From: Adrian Klaver <adrian.klaver@aklaver.com>

> I didn’t succeed in calling SET LOCAL TIMEZONE from within the function. Could be I missed something, then Google (stackoverflow) pointed me to set_config().

CREATE OR REPLACE FUNCTION public.tz_fnc()
  RETURNS void
  LANGUAGE plpgsql
AS $function$
BEGIN
     SET LOCAL  TIMEZONE = 'UTC';
     RAISE NOTICE '%', to_char(now(), 'OF');
END;
$function$
Turns out that the reason I cannot get this to work is that in my case the time zone value comes from a text parameter:

ciscache=> create or replace function ciscache.ToDatetimeOffset(ts_ timestamptz, tz_ text)
returns varchar(34)
language plpgsql
as $$
begin
        --perform set_config('timezone', tz_, true /* local */);
        set local timezone to tz_;
        return to_char(ts_, 'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM');
end;
$$;
CREATE FUNCTION

ciscache=> select ToDatetimeOffset(current_timestamp, 'Europe/Amsterdam'), to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM');
ERROR:  invalid value for parameter "TimeZone": "tz_"
CONTEXT:  SQL statement "set local timezone to tz_"
PL/pgSQL function todatetimeoffset(timestamp with time zone,text) line 4 at SQL statement

But set_config() doesn't have that issue.