> 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.
