Re: ERROR: functions in index expression must be marked IMMUTABLE

On 01.03.2017 14:40, Geoff Winkless wrote:
> On 1 March 2017 at 13:36, Sven R. Kunze <srkunze(at)mail(dot)de
> <mailto:srkunze(at)mail(dot)de>>wrote:
>
> On 28.02.2017 17:50, David G. Johnston wrote:
>> Supposedly one could provide a version of to_date that accepts a
>> locale in which to interpret names in the input data - or extend
>> the format string with some kind of "{locale=en_US}" syntax to
>> avoid changing the function signature.
> if this is acceptable, I would be willing to contribute a patch to
> enable exactly this.
>
>
> ​I don't see how that would help. You can't set a function to be
> immutable for "some" inputs (ie where locale is specified in the
> format string).
>
> The only way to do it would be to add to_date(string, string, string)
> where the third string specifies the locale, at which point I don't
> really see why you would gain anything over creating your own UDF.

I don't consider rolling an UDF the best alternative especially after
having looked through many solution proposals on the Web which just take
an mutable expression and wrap them up in an immutable function.

An additional variant of to_date/to_timestamp would have the following
advantages (point 2 is most important):

1) a single, recommended and easy way to parse date(times)
2) make people aware of the locale/timezone issue but give them a
standard tool to solve it
3) make all those and related Google entries
(https://www.google.de/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=postgresql+to_date+immutable&*)
point to the same and safe solution eventually

Sven