On Mon, Sep 28, 2020 at 2:44 PM Jesse Zhang <sbjesse@gmail.com> wrote:
On Sun, Sep 27, 2020 at 7:52 PM Andy Fan wrote: > On Mon, Sep 28, 2020 at 9:15 AM Tom Lane wrote: >> >> Yeah. It depends on the lc_time setting, and possibly also the timezone >> GUC. (Admittedly, common values of the format string would not have >> any lc_time dependency, but the immutability property is not fine-grained >> enough to recognize that.) > > Thanks for your reply. Even it has something on GUC or lc_time setting, suppose > it should be decided at planning time. Do we have concerns about changes > between planning and execution?
Immutable functions need to produce the same output for the same argument values. None of the functions changed in the patch is immutable: they are all stable because they all depend on GUC settings (e.g. to_tsvector depends on default_text_search_config).
Thanks, how can I misunderstand Tom's comment above!!
To make data type formatting functions immutable you would need a third input argument with the locale used for that specific function call, as proposed in [1].
As for the original use case, in the documentation there is a tip about using to_date() to handle input formats that cannot be converted by simple casting, and that for most standard date/time formats a cast is the easier way to do so [2], I may also add that is better performing in Postgres.
