v5-0004-Add-SQL-function-pg_icu_language_tag.patch

From aa3d9164177742e8a8c1ad25a3613d3ade1aa7b1 Mon Sep 17 00:00:00 2001
From: Jeff Davis <jeff@j-davis.com>
Date: Wed, 15 Mar 2023 11:27:12 -0700
Subject: [PATCH v5 4/5] Add SQL function pg_icu_language_tag().

Account for locales "C" and "POSIX", which correspond to the
language tag "en-US-u-va-posix".

Also, don't rely on a fixed-size buffer for language tags, as there is
no defined upper limit (cf. RFC5646 section 4.4).
---
 doc/src/sgml/func.sgml                        | 15 +++++
 src/backend/commands/collationcmds.c          | 44 +++++++-------
 src/backend/utils/adt/pg_locale.c             | 59 +++++++++++++++++++
 src/bin/pg_dump/t/002_pg_dump.pl              |  4 +-
 src/include/catalog/catversion.h              |  2 +-
 src/include/catalog/pg_proc.dat               |  5 ++
 src/include/utils/pg_locale.h                 |  1 +
 .../regress/expected/collate.icu.utf8.out     | 55 +++++++++++++++++
 src/test/regress/sql/collate.icu.utf8.sql     | 10 ++++
 9 files changed, 171 insertions(+), 24 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 15314aa3ee..dd073b7e26 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -27453,6 +27453,21 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset
         Use of this function is restricted to superusers.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_icu_language_tag</primary>
+        </indexterm>
+        <function>pg_icu_language_tag</function> ( <parameter>locale</parameter> <type>text</type> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Canonicalizes the given <parameter>locale</parameter> string into a
+        BCP 47 language tag (see <xref
+        linkend="collation-managing-create-icu"/>).
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/commands/collationcmds.c b/src/backend/commands/collationcmds.c
index b8f2e7059f..c6dea866da 100644
--- a/src/backend/commands/collationcmds.c
+++ b/src/backend/commands/collationcmds.c
@@ -576,26 +576,6 @@ cmpaliases(const void *a, const void *b)
 
 
 #ifdef USE_ICU
-/*
- * Get the ICU language tag for a locale name.
- * The result is a palloc'd string.
- */
-static char *
-get_icu_language_tag(const char *localename)
-{
-	char		buf[ULOC_FULLNAME_CAPACITY];
-	UErrorCode	status;
-
-	status = U_ZERO_ERROR;
-	uloc_toLanguageTag(localename, buf, sizeof(buf), true, &status);
-	if (U_FAILURE(status))
-		ereport(ERROR,
-				(errmsg("could not convert locale name \"%s\" to language tag: %s",
-						localename, u_errorName(status))));
-
-	return pstrdup(buf);
-}
-
 /*
  * Get a comment (specifically, the display name) for an ICU locale.
  * The result is a palloc'd string, or NULL if we can't get a comment
@@ -957,7 +937,7 @@ pg_import_system_collations(PG_FUNCTION_ARGS)
 			else
 				name = uloc_getAvailable(i);
 
-			langtag = get_icu_language_tag(name);
+			langtag = icu_language_tag(name, false);
 
 			/*
 			 * Be paranoid about not allowing any non-ASCII strings into
@@ -1014,3 +994,25 @@ pg_import_system_collations(PG_FUNCTION_ARGS)
 
 	PG_RETURN_INT32(ncreated);
 }
+
+/*
+ * pg_icu_language_tag
+ *
+ * Return the BCP47 language tag representation of the given locale string.
+ */
+Datum
+pg_icu_language_tag(PG_FUNCTION_ARGS)
+{
+#ifdef USE_ICU
+	text	*locale_text = PG_GETARG_TEXT_PP(0);
+	char	*locale_cstr = text_to_cstring(locale_text);
+	char	*langtag	 = icu_language_tag(locale_cstr, false);
+
+	PG_RETURN_TEXT_P(cstring_to_text(langtag));
+#else
+	ereport(ERROR,
+			(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+			 errmsg("ICU is not supported in this build")));
+	PG_RETURN_NULL();
+#endif
+}
diff --git a/src/backend/utils/adt/pg_locale.c b/src/backend/utils/adt/pg_locale.c
index a8cc8ffcb1..0ac7ce9a80 100644
--- a/src/backend/utils/adt/pg_locale.c
+++ b/src/backend/utils/adt/pg_locale.c
@@ -2810,6 +2810,65 @@ check_icu_locale(const char *icu_locale)
 #endif
 }
 
+#ifdef USE_ICU
+/*
+ * Return the BCP47 language tag representation of the requested locale.
+ *
+ * This function should be called before passing the string to ucol_open(),
+ * because conversion to a language tag also performs "level 2
+ * canonicalization". In addition to producing a consistent format, level 2
+ * canonicalization is able to more accurately interpret different input
+ * locale string formats, such as POSIX and .NET IDs.
+ */
+char *
+icu_language_tag(const char *loc_str, bool noError)
+{
+	UErrorCode	 status;
+	char		*langtag;
+	size_t		 buflen = 32;	/* arbitrary starting buffer size */
+	const bool	 strict = true;
+
+	/* C/POSIX locales aren't handled by uloc_getLanguageTag() */
+	if ((pg_strncasecmp(loc_str, "c", 1) == 0 && !isalnum(loc_str[1])) ||
+		(pg_strncasecmp(loc_str, "posix", 5) == 0 && !isalnum(loc_str[5])))
+		return pstrdup("en-US-u-va-posix");
+
+	/*
+	 * A BCP47 language tag doesn't have a clearly-defined upper limit
+	 * (cf. RFC5646 section 4.4). Additionally, in older ICU versions,
+	 * uloc_toLanguageTag() doesn't always return the ultimate length on the
+	 * first call, necessitating a loop.
+	 */
+	langtag = palloc(buflen);
+	while (true)
+	{
+		int32_t		len;
+
+		status = U_ZERO_ERROR;
+		len = uloc_toLanguageTag(loc_str, langtag, buflen, strict, &status);
+		if (len < buflen || buflen >= MaxAllocSize)
+			break;
+
+		buflen = Min(buflen * 2, MaxAllocSize);
+		langtag = repalloc(langtag, buflen);
+	}
+
+	if (U_FAILURE(status))
+	{
+		pfree(langtag);
+		if (noError)
+			return NULL;
+
+		ereport(ERROR,
+				(errmsg("could not convert locale name \"%s\" to language tag: %s",
+						loc_str, u_errorName(status))));
+	}
+
+	return langtag;
+}
+
+#endif							/* USE_ICU */
+
 /*
  * These functions convert from/to libc's wchar_t, *not* pg_wchar_t.
  * Therefore we keep them here rather than with the mbutils code.
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index a22f27f300..0b38c0537b 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -1837,9 +1837,9 @@ my %tests = (
 
 	'CREATE COLLATION icu_collation' => {
 		create_order => 76,
-		create_sql   => "CREATE COLLATION icu_collation (PROVIDER = icu, LOCALE = 'C');",
+		create_sql   => "CREATE COLLATION icu_collation (PROVIDER = icu, LOCALE = 'en-US-u-va-posix');",
 		regexp =>
-		  qr/CREATE COLLATION public.icu_collation \(provider = icu, locale = 'C'(, version = '[^']*')?\);/m,
+		  qr/CREATE COLLATION public.icu_collation \(provider = icu, locale = 'en-US-u-va-posix'(, version = '[^']*')?\);/m,
 		icu => 1,
 		like      => { %full_runs, section_pre_data => 1, },
 	},
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index b2eed22d46..8b205ab161 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202303151
+#define CATALOG_VERSION_NO	202303161
 
 #endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fbc4aade49..063f77f70c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11817,6 +11817,11 @@
   proname => 'pg_database_collation_actual_version', procost => '100',
   provolatile => 'v', prorettype => 'text', proargtypes => 'oid',
   prosrc => 'pg_database_collation_actual_version' },
+{ oid => '6273',
+  descr => 'get BCP47 language tag representation of locale',
+  proname => 'pg_icu_language_tag', procost => '100',
+  provolatile => 's', prorettype => 'text', proargtypes => 'text',
+  prosrc => 'pg_icu_language_tag' },
 
 # system management/monitoring related functions
 { oid => '3353', descr => 'list files in the log directory',
diff --git a/src/include/utils/pg_locale.h b/src/include/utils/pg_locale.h
index f9ce428233..b78fe0d72c 100644
--- a/src/include/utils/pg_locale.h
+++ b/src/include/utils/pg_locale.h
@@ -119,6 +119,7 @@ extern size_t pg_strnxfrm_prefix(char *dest, size_t destsize, const char *src,
 #ifdef USE_ICU
 extern int32_t icu_to_uchar(UChar **buff_uchar, const char *buff, size_t nbytes);
 extern int32_t icu_from_uchar(char **result, const UChar *buff_uchar, int32_t len_uchar);
+extern char *icu_language_tag(const char *locale_str, bool noError);
 #endif
 extern void check_icu_locale(const char *icu_locale);
 
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index f135200c99..ae608c29be 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -11,6 +11,61 @@ SELECT getdatabaseencoding() <> 'UTF8' OR
 SET client_encoding TO UTF8;
 CREATE SCHEMA collate_tests;
 SET search_path = collate_tests;
+-- test language tag canonicalization
+SELECT pg_icu_language_tag('en_US');
+ pg_icu_language_tag 
+---------------------
+ en-US
+(1 row)
+
+SELECT pg_icu_language_tag('nonsense');
+ pg_icu_language_tag 
+---------------------
+ nonsense
+(1 row)
+
+SELECT pg_icu_language_tag('C.UTF-8');
+ pg_icu_language_tag 
+---------------------
+ en-US-u-va-posix
+(1 row)
+
+SELECT pg_icu_language_tag('POSIX');
+ pg_icu_language_tag 
+---------------------
+ en-US-u-va-posix
+(1 row)
+
+SELECT pg_icu_language_tag('en_US_POSIX');
+ pg_icu_language_tag 
+---------------------
+ en-US-u-va-posix
+(1 row)
+
+SELECT pg_icu_language_tag('@colStrength=secondary');
+ pg_icu_language_tag 
+---------------------
+ und-u-ks-level2
+(1 row)
+
+SELECT pg_icu_language_tag('');
+ pg_icu_language_tag 
+---------------------
+ und
+(1 row)
+
+SELECT pg_icu_language_tag('fr_CA.UTF-8');
+ pg_icu_language_tag 
+---------------------
+ fr-CA
+(1 row)
+
+SELECT pg_icu_language_tag('en_US@colStrength=primary');
+ pg_icu_language_tag 
+---------------------
+ en-US-u-ks-level1
+(1 row)
+
 CREATE TABLE collate_test1 (
     a int,
     b text COLLATE "en-x-icu" NOT NULL
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index 8105ebc8ae..1cd823ef37 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -15,6 +15,16 @@ SET client_encoding TO UTF8;
 CREATE SCHEMA collate_tests;
 SET search_path = collate_tests;
 
+-- test language tag canonicalization
+SELECT pg_icu_language_tag('en_US');
+SELECT pg_icu_language_tag('nonsense');
+SELECT pg_icu_language_tag('C.UTF-8');
+SELECT pg_icu_language_tag('POSIX');
+SELECT pg_icu_language_tag('en_US_POSIX');
+SELECT pg_icu_language_tag('@colStrength=secondary');
+SELECT pg_icu_language_tag('');
+SELECT pg_icu_language_tag('fr_CA.UTF-8');
+SELECT pg_icu_language_tag('en_US@colStrength=primary');
 
 CREATE TABLE collate_test1 (
     a int,
-- 
2.34.1