> set_config ( setting_name text, new_value text, is_local boolean ) → text
>
> Sets the parameter setting_name to new_value, and returns that value. If is_local is true, the new value will only apply during the current transaction. If you want the new value to apply for the rest of the current session, use false instead. This function corresponds to the SQL command SET.
>
> set_config('log_statement_stats', 'off', false) → off"
> "
I tried this like so:
select ToDatetimeOffset(current_timestamp, 'Europe/Amsterdam'), to_char(current_timestamp, ‘YYYY-MM-DD HH24:MI:SS.SU0 TZH:TZM’).
The result of the second call was based on time zone ‘Europe/Amsterdam’, where it wasn’t when called outside the transaction (when it was based on UTC corresponding to the server time zone).
So the time zone set with set_config(…, …, true) appeared to leak out of function scope and applied to transaction scope (as described in the quoted text).
For brevity I could run that query tomorrow when I’m back at work.
Following up on my own mail from yesterday evening, here's the output that shows the function using set_config 'leaking' the timezone change to outside the function (the first select vs. the 2nd select) into the (same) transaction, whereas the function with the time zone bound to the header does not (the 3rd select).
These are all from a single session, ran consecutively - a straight copy-paste from psql.
ciscache=> select ToDatetimeOffset(current_timestamp, 'Europe/Amsterdam'), to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM');
todatetimeoffset | to_char
------------------------------------+------------------------------------
2024-01-12 09:41:44.7019350 +01:00 | 2024-01-12 09:41:44.7019350 +01:00
(1 row)
ciscache=> select to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM');
to_char
------------------------------------
2024-01-12 08:41:54.4739800 +00:00
(1 row)
ciscache=> select ToDatetimeOffsetNL(current_timestamp), to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS.US0 TZH:TZM');
todatetimeoffsetnl | to_char
------------------------------------+------------------------------------
2024-01-12 09:42:44.1989210 +01:00 | 2024-01-12 08:42:44.1989210 +00:00
(1 row)
Regards,
Alban Hertroijs.