adding an immutable variant of to_date

Hello everybody,

following up on this thread:
https://www.postgresql.org/message-id/flat/d297e048-ac49-9bed-32e3-9dd4e65d0978%40mail.de
specifically on this mail:
https://www.postgresql.org/message-id/baef819f-acf0-a64d-c1eb-d2c5da1e5030%40mail.de
I hope this idea fulfills the requirements. So let's see what you have
to say.

to_date is not an immutable function but the best way to parse dates
besides casting. Unfortunately, creating an functional index with
to_date or casting will fail with an error.

Researching the topic in the past and currently, the situation hasn't
improved. Many SO solutions or blog posts suggest to roll your own UDF
to wrap up an mutable expression and marking it immutable by doing so.
This approach has several disadvantages:

1) unawareness of the locale and timezone issues date/timestamp parsing
involves
2) hiding errors in production when environment variables are
missing/set differently than on dev machines
3) many re-implementations of the same problem

I consider 1) the biggest issue. Developers need to be aware of these
kinds of issues to solve them properly. This is especially problematic
when defining functional indexes where you don't have access to the
actual date values.

For this purpose, I would like to adhere to the PostgreSQL roadmap and
"scratch my own itch" by implementing the following solution which I
think best solves the issue at hand:

** Idea **

An additional, immutable variant of to_date with a third parameter to
specify the locale by which the date should be parsed. The additional
documentation line
(https://www.postgresql.org/docs/9.6/static/functions-formatting.html)
would look like this:

to_date(text, text, text) date convert string to date given a
specific locale to_date('05 Dez 2000', 'DD Mon YYYY', 'de_DE')

I think this approach has the following advantages:

1) a single, recommended and safe way to parse dates
2) make people aware of the locale/timezone issue but give them a
standard tool to solve it
3) eventually 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

** Rejected Ideas **
Geoff suggest to simply mark to_date as stable and being able to parse
dates by trying all locales. Two arguments work against this ideas: 1)
it would create prohibitively large lookup tables and 2) some mappings
are ambiguous:
https://www.postgresql.org/message-id/aba44f78-2b84-e752-9b6f-3784bd0f981c%40mail.de

Another suggestion from Geoff >>format string with an additional locale
"{locale=en_US}"<< couldn't fly as well because a function cannot be set
immutable for some inputs.

What do you think?

Regards,
Sven