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.