Support for ISO 8601 in the jsonpath .datetime() method

The SQL standard doesn't require jsonpath .datetime() method to support the
ISO 8601 format.  But our to_json[b]() functions convert timestamps to text in
the ISO 8601 format in the sake of compatibility with javascript.  So, we add
support of the  ISO 8601 to the jsonpath .datetime() in the sake compatibility
with to_json[b]().

The standard mode of datetime parsing currently supports just template patterns
and separators in the format string.  In order to implement ISO 8601, we have to
add support of the format string double quotes to the standard parsing mode.

Discussion: https://postgr.es/m/94321be0-cc96-1a81-b6df-796f437f7c66%40postgrespro.ru
Author: Nikita Glukhov, revised by me
Backpatch-through: 13

1 files changed, 17 insertions, 3 deletions

diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index 16768b28c30..75c85a30070 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -1381,10 +1381,12 @@ parse_format(FormatNode *node, const char *str, const KeyWord *kw,
 		{
 			int			chlen;
 
-			if (flags & STD_FLAG)
+			if ((flags & STD_FLAG) && *str != '"')
 			{
 				/*
-				 * Standard mode, allow only following separators: "-./,':; "
+				 * Standard mode, allow only following separators: "-./,':; ".
+				 * However, we support double quotes even in standard mode
+				 * (see below).  This is our extension of standard mode.
 				 */
 				if (strchr("-./,':; ", *str) == NULL)
 					ereport(ERROR,
@@ -3347,7 +3349,19 @@ DCH_from_char(FormatNode *node, const char *in, TmFromChar *out,
 			}
 			else
 			{
-				s += pg_mblen(s);
+				int			chlen = pg_mblen(s);
+
+				/*
+				 * Standard mode requires strict match of format characters.
+				 */
+				if (std && n->type == NODE_TYPE_CHAR &&
+					strncmp(s, n->character, chlen) != 0)
+					RETURN_ERROR(ereport(ERROR,
+										 (errcode(ERRCODE_INVALID_DATETIME_FORMAT),
+										  errmsg("unmatched format character \"%s\"",
+												 n->character))));
+
+				s += chlen;
 			}
 			continue;
 		}