On 1/11/24 08:04, Alban Hertroijs wrote:
> 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
> 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).
Would a function that dispatches its calls to a suitable array of
hard-coded functions based on an IN parameter help any ?
Karsten
Well, probably, but we don't have many time zones that are relevant to
us. For that, the current functions would be sufficient.
The drawback, as mentioned, being that we need to maintain those
functions in each deployment, which is a bit of a hassle (albeit a minor
one) because we need to customise both the TDV side and the PostgreSQL
side in that case. Our preferred solution would be to just add a few
entries to the TDV database-specific capabilities file (as described in
my initial message)
Are you referring to?:
"It currently have this:
ToDatetimeOffsetNL(~any) : ciscache.ToDatetimeOffsetNL($1)
ToDatetimeOffset(~any,~any) : ciscache.ToDatetimeOffset($1, $2)
"
I thought the issue there was maintaining the two Postgres functions?
Provided that such a solution is possible, that is. If not, my current
approach may have to suffice.
The reason I decided to ask on the ML is that I'm finding it hard to
believe that this transformation would be this difficult, so I expect
that I must be missing something.
Regards,
Alban Hertroys
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
