Re: Time zone offset in to_char()

On 1/11/24 07:06, Alban Hertroijs wrote:
> Hi all,
> 

> In the above, I worked around the issue using a couple of user-defined 
> functions in PG. That should give a reasonable idea of the desired 
> functionality, but it's not an ideal solution to my problem:
> 1). The first function has as a drawback that it changes the time zone 
> for the entire transaction (not sufficiently isolated to my tastes), while
> 2). The second function has the benefit that it doesn't leak the time 
> zone change, but has as drawback that the time zone is now hardcoded 
> into the function definition, while

I don't think the set_config and SET are acting the way you think they are:

set_config(https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SET)

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

SET(https://www.postgresql.org/docs/current/sql-set.html)

"If SET (or equivalently SET SESSION) is issued within a transaction 
that is later aborted, the effects of the SET command disappear when the 
transaction is rolled back. Once the surrounding transaction is 
committed, the effects will persist until the end of the session, unless 
overridden by another SET.

The effects of SET LOCAL last only till the end of the current 
transaction, whether committed or not. A special case is SET followed by 
SET LOCAL within a single transaction: the SET LOCAL value will be seen 
until the end of the transaction, but afterwards (if the transaction is 
committed) the SET value will take effect.

The effects of SET or SET LOCAL are also canceled by rolling back to a 
savepoint that is earlier than the command.

If SET LOCAL is used within a function that has a SET option for the 
same variable (see CREATE FUNCTION), the effects of the SET LOCAL 
command disappear at function exit; that is, the value in effect when 
the function was called is restored anyway. This allows SET LOCAL to be 
used for dynamic or repeated changes of a parameter within a function, 
while still having the convenience of using the SET option to save and 
restore the caller's value. However, a regular SET command overrides any 
surrounding function's SET option; its effects will persist unless 
rolled back.
"

> 3). Both functions need to be created in the caching database before we 
> can use them, while we have several environments where they would apply 
> (DEV, pre-PROD, PROD).


-- 
Adrian Klaver
adrian.klaver@aklaver.com