[18+] improve upgrade pre-check for Unicode updates

From e7ee22230d4c14f0d1e83982647c7b04e1be3bf9 Mon Sep 17 00:00:00 2001
From: Jeff Davis <jeff@j-davis.com>
Date: Fri, 22 Aug 2025 13:40:36 -0700
Subject: [PATCH v1] Check for more Unicode functions during upgrade.

When checking for expression indexes that may be affected by a Unicode
update during upgrade, check for a few more functions. Specifically,
check for documented regexp functions, as well as the new CASEFOLD()
function.

Also, fully-qualify pg_catalog references to the text type as well as
regtype.

Backpatch-through: 18
---
 src/bin/pg_upgrade/check.c | 13 +++++++++----
 1 file changed, 9 insertions(+), 4 deletions(-)

diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index 93ef3c42c4c..322f2bb6c82 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -1971,14 +1971,19 @@ check_for_unicode_update(ClusterInfo *cluster)
 		"  SELECT oper.oid, oper.oprcode, collid FROM pg_operator oper, collations "
 		"  WHERE oprname IN ('~', '~*', '!~', '!~*', '~~*', '!~~*') AND "
 		"        oprnamespace='pg_catalog'::regnamespace AND "
-		"        oprright='text'::regtype "
+		"        oprright='pg_catalog.text'::pg_catalog.regtype "
 		"), "
 	/* functions that use the input collation for character semantics */
 		"coll_functions(procid, collid) AS ( "
 		"  SELECT proc.oid, collid FROM pg_proc proc, collations "
-		"  WHERE proname IN ('lower','initcap','upper') AND "
-		"        pronamespace='pg_catalog'::regnamespace AND "
-		"        proargtypes[0] = 'text'::regtype "
+		"  WHERE pronamespace='pg_catalog'::regnamespace AND "
+		"        ((proname IN ('lower','initcap','upper','casefold') AND "
+		"          pronargs = 1 AND "
+		"          proargtypes[0] = 'pg_catalog.text'::pg_catalog.regtype) OR "
+		"         (proname = 'substring' AND pronargs = 2 AND "
+		"          proargtypes[0] = 'pg_catalog.text'::pg_catalog.regtype AND "
+		"          proargtypes[1] = 'pg_catalog.text'::pg_catalog.regtype) OR "
+		"         proname LIKE 'regexp_%')"
 	/* include functions behind the operators listed above */
 		"  UNION "
 		"  SELECT procid, collid FROM coll_operators "
-- 
2.43.0