Allow to_date/to_timestamp to recognize non-English month/day names.

to_char() has long allowed the TM (translation mode) prefix to
specify output of translated month or day names; but that prefix
had no effect in input format strings.  Now it does.  to_date()
and to_timestamp() will now recognize the same month or day names
that to_char() would output for the same format code.  Matching is
case-insensitive (per the active collation's notion of what that
means), just as it has always been for English month/day names
without the TM prefix.

(As per the discussion thread, there are lots of cases that this
feature will not handle, such as alternate day names.  But being
able to accept what to_char() will output seems useful enough.)

In passing, fix some shaky English and violations of message
style guidelines in jsonpath errors for the .datetime() method,
which depends on this code.

Juan José Santamaría Flecha, reviewed and modified by me,
with other commentary from Alvaro Herrera, Tomas Vondra,
Arthur Zakirov, Peter Eisentraut, Mark Dilger.

Discussion: https://postgr.es/m/CAC+AXB3u1jTngJcoC1nAHBf=M3v-jrEfo86UFtCqCjzbWS9QhA@mail.gmail.com

4 files changed, 26 insertions, 4 deletions

diff --git a/src/test/regress/expected/collate.linux.utf8.out b/src/test/regress/expected/collate.linux.utf8.out
index 37c6add5b51..f06ae543e49 100644
--- a/src/test/regress/expected/collate.linux.utf8.out
+++ b/src/test/regress/expected/collate.linux.utf8.out
@@ -461,6 +461,22 @@ SELECT to_char(date '2010-04-01', 'DD TMMON YYYY' COLLATE "tr_TR");
  01 NİS 2010
 (1 row)
 
+-- to_date
+SELECT to_date('01 ŞUB 2010', 'DD TMMON YYYY');
+  to_date   
+------------
+ 02-01-2010
+(1 row)
+
+SELECT to_date('01 Şub 2010', 'DD TMMON YYYY');
+  to_date   
+------------
+ 02-01-2010
+(1 row)
+
+SELECT to_date('1234567890ab 2010', 'TMMONTH YYYY'); -- fail
+ERROR:  invalid value "1234567890ab" for "MONTH"
+DETAIL:  The given value did not match any of the allowed values for this field.
 -- backwards parsing
 CREATE VIEW collview1 AS SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc';
 CREATE VIEW collview2 AS SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C";
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index ef8db2d060a..83a050d3e29 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -1674,9 +1674,9 @@ select jsonb_path_query('[]', 'strict $.datetime()');
 ERROR:  jsonpath item method .datetime() can only be applied to a string
 select jsonb_path_query('{}', '$.datetime()');
 ERROR:  jsonpath item method .datetime() can only be applied to a string
-select jsonb_path_query('""', '$.datetime()');
-ERROR:  datetime format is not unrecognized
-HINT:  use datetime template argument for explicit format specification
+select jsonb_path_query('"bogus"', '$.datetime()');
+ERROR:  datetime format is not recognized: "bogus"
+HINT:  Use a datetime template argument to specify the input data format.
 select jsonb_path_query('"12:34"', '$.datetime("aaa")');
 ERROR:  invalid datetime format separator: "a"
 select jsonb_path_query('"aaaa"', '$.datetime("HH24")');
diff --git a/src/test/regress/sql/collate.linux.utf8.sql b/src/test/regress/sql/collate.linux.utf8.sql
index 8c26f16f5d5..cbbd2203e41 100644
--- a/src/test/regress/sql/collate.linux.utf8.sql
+++ b/src/test/regress/sql/collate.linux.utf8.sql
@@ -182,6 +182,12 @@ SELECT to_char(date '2010-02-01', 'DD TMMON YYYY' COLLATE "tr_TR");
 SELECT to_char(date '2010-04-01', 'DD TMMON YYYY');
 SELECT to_char(date '2010-04-01', 'DD TMMON YYYY' COLLATE "tr_TR");
 
+-- to_date
+
+SELECT to_date('01 ŞUB 2010', 'DD TMMON YYYY');
+SELECT to_date('01 Şub 2010', 'DD TMMON YYYY');
+SELECT to_date('1234567890ab 2010', 'TMMONTH YYYY'); -- fail
+
 
 -- backwards parsing
 
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index 591be00278f..731b4d444cc 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -352,7 +352,7 @@ select jsonb_path_query('1', '$.datetime()');
 select jsonb_path_query('[]', '$.datetime()');
 select jsonb_path_query('[]', 'strict $.datetime()');
 select jsonb_path_query('{}', '$.datetime()');
-select jsonb_path_query('""', '$.datetime()');
+select jsonb_path_query('"bogus"', '$.datetime()');
 select jsonb_path_query('"12:34"', '$.datetime("aaa")');
 select jsonb_path_query('"aaaa"', '$.datetime("HH24")');