From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Dilger <mark.dilger@enterprisedb.com>
cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>,
        =?utf-8?Q?Juan_Jos=C3=A9_Santamar=C3=ADa_Flecha?=
 <juanjo.santamaria@gmail.com>,
        Alvaro Herrera <alvherre@2ndquadrant.com>,
        Arthur Zakirov <zaartur@gmail.com>,
        Tomas Vondra <tomas.vondra@2ndquadrant.com>,
        PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Allow to_date() and to_timestamp() to accept localized names
In-reply-to: <4144A0A0-4820-458D-A105-D5EC0E0D133B@enterprisedb.com>
References: <20200124161814.GA8989@alvherre.pgsql>
 <32487.1579882979@sss.pgh.pa.us>
 <2f83150e-a2c0-6318-3125-d9b86e421aa2@2ndquadrant.com>
 <964.1579884408@sss.pgh.pa.us>
 <CAC+AXB1+dHo_DhCTySrqoVaq9WUta9uA+2Ens-bEqDw8X4UbTA@mail.gmail.com>
 <ba83b5a7-e63e-5aaf-ac29-745a549f9d7d@2ndquadrant.com>
 <aa5d8d96-0eb6-07b9-2caf-0910b313cb56@2ndquadrant.com>
 <7212.1580177517@sss.pgh.pa.us>
 <2E3D8D71-3B55-4735-A047-1FECE1506CD1@enterprisedb.com>
 <d2b20ae0-a377-5edc-504b-80b1792a6bf0@2ndquadrant.com>
 <6642.1580223087@sss.pgh.pa.us>
 <4144A0A0-4820-458D-A105-D5EC0E0D133B@enterprisedb.com>
Comments: In-reply-to Mark Dilger <mark.dilger@enterprisedb.com>
	message dated "Tue, 28 Jan 2020 07:06:15 -0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <15574.1580232642.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 28 Jan 2020 12:30:42 -0500
Message-ID: <15575.1580232642@sss.pgh.pa.us>
Precedence: bulk

Mark Dilger <mark.dilger@enterprisedb.com> writes:
> But then the manual page goes on to say:

>> %E* %O*
>> POSIX locale extensions.  The sequences %Ec %EC %Ex %EX %Ey %EY %Od %Oe=
 %OH %OI %Om %OM %OS %Ou %OU %OV %Ow %OW %Oy are supposed to provide alter=
nate representations.
>> =

>> Additionally %OB implemented to represent alternative months names (use=
d standalone, without day mentioned).

> This is the part I haven=E2=80=99t played with, but it sounds like it ca=
n handle at least one alternate name.  Perhaps you can get the alternates =
this way?

This sounded promising, but the POSIX strftime spec doesn't mention %OB,
so I'm afraid we can't count on it to do much.  At this point I'm not
really convinced that there are no languages with more than two forms,
anyway :-(.

I also wondered whether we could get any further by using strptime() to
convert localized month and day names on-the-fly, rather than the patch's
current approach of re-using strftime() results.  If strptime() fails
to support alternative names, it's their bug not ours.  Unfortunately,
glibc has got said bug (AFAICS anyway), so in practice this would only
offer us plausible deniability and not much of any real functionality.

In the end it seems like we could only handle alternative names by
keeping our own lists of them.  There are probably few enough cases
that that wouldn't be a tremendous maintenance problem, but what
I'm not quite seeing is how we'd decide which list to use when.
Right now, locale identifiers are pretty much opaque to us ... do
we really want to get into the business of recognizing that such a
name refers to German, or Greek?

A brute-force answer, if there are few enough cases, is to recognize
them regardless of the specific value of LC_TIME.  Do we think
anybody would notice or care if to_date('Sonnabend', 'TMDay') works
even when in a non-German locale?

			regards, tom lane