From a20f3ca457e20546fed9f6dad19163dac4a8ecca Mon Sep 17 00:00:00 2001 From: Jeff Davis Date: Wed, 11 Dec 2024 23:46:43 -0800 Subject: [PATCH v5 2/2] Add SQL function CASEFOLD(). --- CATVERSION --- Useful for case-insensitive string comparison. Avoids some of the edge-case problems with using LOWER() for that purpose. Discussion: https://postgr.es/m/a1886ddfcd8f60cb3e905c93009b646b4cfb74c5.camel%40j-davis.com Reviewed-by: Ian Lawrence Barwick --- doc/src/sgml/func.sgml | 44 +++++++++++++- src/backend/utils/adt/formatting.c | 69 ++++++++++++++++++++++ src/backend/utils/adt/oracle_compat.c | 16 +++++ src/backend/utils/adt/pg_locale.c | 24 ++++++++ src/backend/utils/adt/pg_locale_builtin.c | 10 ++++ src/backend/utils/adt/pg_locale_icu.c | 52 ++++++++++++++++ src/include/catalog/pg_proc.dat | 3 + src/include/utils/formatting.h | 1 + src/include/utils/pg_locale.h | 3 + src/test/regress/expected/collate.utf8.out | 14 +++++ src/test/regress/sql/collate.utf8.sql | 6 ++ 11 files changed, 240 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 47370e581ae..2fedb8b6d39 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -2596,7 +2596,7 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in - + lower lower ( text ) @@ -2657,7 +2657,7 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in - + normalize @@ -3109,6 +3109,46 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in + + + + casefold + + casefold ( text ) + text + + + Performs case folding of the input string according to the collation. + Case folding is similar to case conversion, but the purpose of case + folding is to facilitate case-insensitive comparison of strings, + whereas the purpose of case conversion is to convert to a particular + cased form. This function can only be used when the server encoding + is UTF8. + + + Ordinarily, case folding simply converts to lowercase, but there are a + few notable exceptions. For instance, the character + Σ (U+03A3) has two lowercase forms: + σ (U+03C3) and ς (U+03C2); case + folding in the PG_C_UTF8 collation maps all three + forms to σ. + + + Another benefit of case folding is that the results don't change for + existing characters in new versions of Unicode. + + + casefold can be used for Unicode Default Caseless + Matching. It does not always preserve the normalized form of the + input string (see ). + + + The libc provider doesn't support case folding, so + casefold is identical to . + + + diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c index 3960235e14e..eff225370d8 100644 --- a/src/backend/utils/adt/formatting.c +++ b/src/backend/utils/adt/formatting.c @@ -1773,6 +1773,75 @@ str_initcap(const char *buff, size_t nbytes, Oid collid) return result; } +/* + * collation-aware, wide-character-aware case folding + * + * We pass the number of bytes so we can pass varlena and char* + * to this function. The result is a palloc'd, null-terminated string. + */ +char * +str_casefold(const char *buff, size_t nbytes, Oid collid) +{ + char *result; + pg_locale_t mylocale; + + if (!buff) + return NULL; + + if (!OidIsValid(collid)) + { + /* + * This typically means that the parser could not resolve a conflict + * of implicit collations, so report it that way. + */ + ereport(ERROR, + (errcode(ERRCODE_INDETERMINATE_COLLATION), + errmsg("could not determine which collation to use for %s function", + "lower()"), + errhint("Use the COLLATE clause to set the collation explicitly."))); + } + + if (GetDatabaseEncoding() != PG_UTF8) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("Unicode case folding can only be performed if server encoding is UTF8"))); + + mylocale = pg_newlocale_from_collation(collid); + + /* C/POSIX collations use this path regardless of database encoding */ + if (mylocale->ctype_is_c) + { + result = asc_tolower(buff, nbytes); + } + else + { + const char *src = buff; + size_t srclen = nbytes; + size_t dstsize; + char *dst; + size_t needed; + + /* first try buffer of equal size plus terminating NUL */ + dstsize = srclen + 1; + dst = palloc(dstsize); + + needed = pg_strfold(dst, dstsize, src, srclen, mylocale); + if (needed + 1 > dstsize) + { + /* grow buffer if needed and retry */ + dstsize = needed + 1; + dst = repalloc(dst, dstsize); + needed = pg_strfold(dst, dstsize, src, srclen, mylocale); + Assert(needed + 1 <= dstsize); + } + + Assert(dst[needed] == '\0'); + result = dst; + } + + return result; +} + /* * ASCII-only lower function * diff --git a/src/backend/utils/adt/oracle_compat.c b/src/backend/utils/adt/oracle_compat.c index 2cba7cd1621..a24a2d208fb 100644 --- a/src/backend/utils/adt/oracle_compat.c +++ b/src/backend/utils/adt/oracle_compat.c @@ -126,6 +126,22 @@ initcap(PG_FUNCTION_ARGS) PG_RETURN_TEXT_P(result); } +Datum +casefold(PG_FUNCTION_ARGS) +{ + text *in_string = PG_GETARG_TEXT_PP(0); + char *out_string; + text *result; + + out_string = str_casefold(VARDATA_ANY(in_string), + VARSIZE_ANY_EXHDR(in_string), + PG_GET_COLLATION()); + result = cstring_to_text(out_string); + pfree(out_string); + + PG_RETURN_TEXT_P(result); +} + /******************************************************************** * diff --git a/src/backend/utils/adt/pg_locale.c b/src/backend/utils/adt/pg_locale.c index 94444acd2c5..7d92f580a57 100644 --- a/src/backend/utils/adt/pg_locale.c +++ b/src/backend/utils/adt/pg_locale.c @@ -106,6 +106,8 @@ extern size_t strtitle_builtin(char *dst, size_t dstsize, const char *src, ssize_t srclen, pg_locale_t locale); extern size_t strupper_builtin(char *dst, size_t dstsize, const char *src, ssize_t srclen, pg_locale_t locale); +extern size_t strfold_builtin(char *dst, size_t dstsize, const char *src, + ssize_t srclen, pg_locale_t locale); extern size_t strlower_icu(char *dst, size_t dstsize, const char *src, ssize_t srclen, pg_locale_t locale); @@ -113,6 +115,8 @@ extern size_t strtitle_icu(char *dst, size_t dstsize, const char *src, ssize_t srclen, pg_locale_t locale); extern size_t strupper_icu(char *dst, size_t dstsize, const char *src, ssize_t srclen, pg_locale_t locale); +extern size_t strfold_icu(char *dst, size_t dstsize, const char *src, + ssize_t srclen, pg_locale_t locale); extern size_t strlower_libc(char *dst, size_t dstsize, const char *src, ssize_t srclen, pg_locale_t locale); @@ -1447,6 +1451,26 @@ pg_strupper(char *dst, size_t dstsize, const char *src, ssize_t srclen, return 0; /* keep compiler quiet */ } +size_t +pg_strfold(char *dst, size_t dstsize, const char *src, ssize_t srclen, + pg_locale_t locale) +{ + if (locale->provider == COLLPROVIDER_BUILTIN) + return strfold_builtin(dst, dstsize, src, srclen, locale); +#ifdef USE_ICU + else if (locale->provider == COLLPROVIDER_ICU) + return strfold_icu(dst, dstsize, src, srclen, locale); +#endif + /* for libc, just use strlower */ + else if (locale->provider == COLLPROVIDER_LIBC) + return strlower_libc(dst, dstsize, src, srclen, locale); + else + /* shouldn't happen */ + PGLOCALE_SUPPORT_ERROR(locale->provider); + + return 0; /* keep compiler quiet */ +} + /* * pg_strcoll * diff --git a/src/backend/utils/adt/pg_locale_builtin.c b/src/backend/utils/adt/pg_locale_builtin.c index 436e32c0ca0..33ad20bbf07 100644 --- a/src/backend/utils/adt/pg_locale_builtin.c +++ b/src/backend/utils/adt/pg_locale_builtin.c @@ -31,6 +31,8 @@ extern size_t strtitle_builtin(char *dst, size_t dstsize, const char *src, ssize_t srclen, pg_locale_t locale); extern size_t strupper_builtin(char *dst, size_t dstsize, const char *src, ssize_t srclen, pg_locale_t locale); +extern size_t strfold_builtin(char *dst, size_t dstsize, const char *src, + ssize_t srclen, pg_locale_t locale); struct WordBoundaryState @@ -107,6 +109,14 @@ strupper_builtin(char *dest, size_t destsize, const char *src, ssize_t srclen, locale->info.builtin.casemap_full); } +size_t +strfold_builtin(char *dest, size_t destsize, const char *src, ssize_t srclen, + pg_locale_t locale) +{ + return unicode_strfold(dest, destsize, src, srclen, + locale->info.builtin.casemap_full); +} + pg_locale_t create_pg_locale_builtin(Oid collid, MemoryContext context) { diff --git a/src/backend/utils/adt/pg_locale_icu.c b/src/backend/utils/adt/pg_locale_icu.c index 5185b0f7289..b175f096281 100644 --- a/src/backend/utils/adt/pg_locale_icu.c +++ b/src/backend/utils/adt/pg_locale_icu.c @@ -54,6 +54,8 @@ extern size_t strtitle_icu(char *dst, size_t dstsize, const char *src, ssize_t srclen, pg_locale_t locale); extern size_t strupper_icu(char *dst, size_t dstsize, const char *src, ssize_t srclen, pg_locale_t locale); +extern size_t strfold_icu(char *dst, size_t dstsize, const char *src, + ssize_t srclen, pg_locale_t locale); #ifdef USE_ICU @@ -117,6 +119,10 @@ static int32_t u_strToTitle_default_BI(UChar *dest, int32_t destCapacity, const UChar *src, int32_t srcLength, const char *locale, UErrorCode *pErrorCode); +static int32_t u_strFoldCase_default(UChar *dest, int32_t destCapacity, + const UChar *src, int32_t srcLength, + const char *locale, + UErrorCode *pErrorCode); static const struct collate_methods collate_methods_icu = { .strncoll = strncoll_icu, @@ -439,6 +445,26 @@ strupper_icu(char *dest, size_t destsize, const char *src, ssize_t srclen, return result_len; } +size_t +strfold_icu(char *dest, size_t destsize, const char *src, ssize_t srclen, + pg_locale_t locale) +{ + int32_t len_uchar; + int32_t len_conv; + UChar *buff_uchar; + UChar *buff_conv; + size_t result_len; + + len_uchar = icu_to_uchar(&buff_uchar, src, srclen); + len_conv = icu_convert_case(u_strFoldCase_default, locale, + &buff_conv, buff_uchar, len_uchar); + result_len = icu_from_uchar(dest, destsize, buff_conv, len_conv); + pfree(buff_uchar); + pfree(buff_conv); + + return result_len; +} + /* * strncoll_icu_utf8 * @@ -673,6 +699,32 @@ u_strToTitle_default_BI(UChar *dest, int32_t destCapacity, NULL, locale, pErrorCode); } +static int32_t +u_strFoldCase_default(UChar *dest, int32_t destCapacity, + const UChar *src, int32_t srcLength, + const char *locale, + UErrorCode *pErrorCode) +{ + uint32 options = U_FOLD_CASE_DEFAULT; + char lang[3]; + UErrorCode status; + + status = U_ZERO_ERROR; + uloc_getLanguage(locale, lang, 3, &status); + if (U_SUCCESS(status)) + { + /* + * The option name is confusing, but it causes u_strFoldCase to use + * the 'T' mappings, which are ignored for U_FOLD_CASE_DEFAULT. + */ + if (strcmp(lang, "tr") == 0 || strcmp(lang, "az") == 0) + options = U_FOLD_CASE_EXCLUDE_SPECIAL_I; + } + + return u_strFoldCase(dest, destCapacity, src, srcLength, + options, pErrorCode); +} + /* * strncoll_icu * diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 18560755d26..2aafdbc3e93 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -3623,6 +3623,9 @@ { oid => '872', descr => 'capitalize each word', proname => 'initcap', prorettype => 'text', proargtypes => 'text', prosrc => 'initcap' }, +{ oid => '9569', descr => 'fold case', + proname => 'casefold', prorettype => 'text', proargtypes => 'text', + prosrc => 'casefold' }, { oid => '873', descr => 'left-pad string to length', proname => 'lpad', prorettype => 'text', proargtypes => 'text int4 text', prosrc => 'lpad' }, diff --git a/src/include/utils/formatting.h b/src/include/utils/formatting.h index 5fa49539aaa..835307dac09 100644 --- a/src/include/utils/formatting.h +++ b/src/include/utils/formatting.h @@ -21,6 +21,7 @@ extern char *str_tolower(const char *buff, size_t nbytes, Oid collid); extern char *str_toupper(const char *buff, size_t nbytes, Oid collid); extern char *str_initcap(const char *buff, size_t nbytes, Oid collid); +extern char *str_casefold(const char *buff, size_t nbytes, Oid collid); extern char *asc_tolower(const char *buff, size_t nbytes); extern char *asc_toupper(const char *buff, size_t nbytes); diff --git a/src/include/utils/pg_locale.h b/src/include/utils/pg_locale.h index 2bc3a7df2d9..0d5f0513ceb 100644 --- a/src/include/utils/pg_locale.h +++ b/src/include/utils/pg_locale.h @@ -134,6 +134,9 @@ extern size_t pg_strtitle(char *dest, size_t destsize, extern size_t pg_strupper(char *dest, size_t destsize, const char *src, ssize_t srclen, pg_locale_t locale); +extern size_t pg_strfold(char *dest, size_t destsize, + const char *src, ssize_t srclen, + pg_locale_t locale); extern int pg_strcoll(const char *arg1, const char *arg2, pg_locale_t locale); extern int pg_strncoll(const char *arg1, ssize_t len1, const char *arg2, ssize_t len2, pg_locale_t locale); diff --git a/src/test/regress/expected/collate.utf8.out b/src/test/regress/expected/collate.utf8.out index 8b7176a2756..5508622b16d 100644 --- a/src/test/regress/expected/collate.utf8.out +++ b/src/test/regress/expected/collate.utf8.out @@ -160,6 +160,13 @@ SELECT 'δ' ~* '[Γ-Λ]' COLLATE PG_C_UTF8; -- same as above with cases reversed t (1 row) +-- case folding +select casefold('AbCd 123 #$% ıiIİ ẞ ß DŽDždž Σσς' collate PG_C_UTF8); + casefold +------------------------------- + abcd 123 #$% ıiiİ ß ß dždždž σσσ +(1 row) + -- -- Test PG_UNICODE_FAST -- @@ -320,3 +327,10 @@ SELECT 'δ' ~* '[Γ-Λ]' COLLATE PG_UNICODE_FAST; -- same as above with cases re t (1 row) +-- case folding +select casefold('AbCd 123 #$% ıiIİ ẞ ß DŽDždž Σσς' collate PG_UNICODE_FAST); + casefold +--------------------------------- + abcd 123 #$% ıiii̇ ss ss dždždž σσσ +(1 row) + diff --git a/src/test/regress/sql/collate.utf8.sql b/src/test/regress/sql/collate.utf8.sql index 46e9c5232ad..6c7c7aec9ec 100644 --- a/src/test/regress/sql/collate.utf8.sql +++ b/src/test/regress/sql/collate.utf8.sql @@ -81,6 +81,9 @@ SELECT 'xAb' !~* '[c-d]' COLLATE PG_C_UTF8; SELECT 'Δ' ~* '[γ-λ]' COLLATE PG_C_UTF8; SELECT 'δ' ~* '[Γ-Λ]' COLLATE PG_C_UTF8; -- same as above with cases reversed +-- case folding +select casefold('AbCd 123 #$% ıiIİ ẞ ß DŽDždž Σσς' collate PG_C_UTF8); + -- -- Test PG_UNICODE_FAST -- @@ -140,3 +143,6 @@ SELECT 'xAb' ~* '[W-Y]' COLLATE PG_UNICODE_FAST; SELECT 'xAb' !~* '[c-d]' COLLATE PG_UNICODE_FAST; SELECT 'Δ' ~* '[γ-λ]' COLLATE PG_UNICODE_FAST; SELECT 'δ' ~* '[Γ-Λ]' COLLATE PG_UNICODE_FAST; -- same as above with cases reversed + +-- case folding +select casefold('AbCd 123 #$% ıiIİ ẞ ß DŽDždž Σσς' collate PG_UNICODE_FAST); -- 2.34.1