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