v5-0005-Add-cross-type-comparisons-for-string-types.patch

From 3c4b4421d5985976f4b4f065365aa1f66152bdc5 Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Wed, 2 Jul 2025 13:36:07 -0400
Subject: [PATCH v5 5/6] Add cross-type comparisons for string types.

(Only these two cases appear in the catalogs.)
---
 contrib/btree_gin/btree_gin--1.3--1.4.sql | 20 ++++++++
 contrib/btree_gin/btree_gin.c             | 49 ++++++++++++++++---
 contrib/btree_gin/expected/name.out       | 59 +++++++++++++++++++++++
 contrib/btree_gin/expected/text.out       | 50 +++++++++++++++++++
 contrib/btree_gin/sql/name.sql            | 11 +++++
 contrib/btree_gin/sql/text.sql            |  9 ++++
 6 files changed, 192 insertions(+), 6 deletions(-)

diff --git a/contrib/btree_gin/btree_gin--1.3--1.4.sql b/contrib/btree_gin/btree_gin--1.3--1.4.sql
index c68fe2b0da2..67edd42faa6 100644
--- a/contrib/btree_gin/btree_gin--1.3--1.4.sql
+++ b/contrib/btree_gin/btree_gin--1.3--1.4.sql
@@ -81,3 +81,23 @@ ADD
     OPERATOR        0x14    >= (float8, float4),
     OPERATOR        0x15    > (float8, float4)
 ;
+
+ALTER OPERATOR FAMILY text_ops USING gin
+ADD
+    -- Code 1: RHS is name
+    OPERATOR        0x11    < (text, name),
+    OPERATOR        0x12    <= (text, name),
+    OPERATOR        0x13    = (text, name),
+    OPERATOR        0x14    >= (text, name),
+    OPERATOR        0x15    > (text, name)
+;
+
+ALTER OPERATOR FAMILY name_ops USING gin
+ADD
+    -- Code 1: RHS is text
+    OPERATOR        0x11    < (name, text),
+    OPERATOR        0x12    <= (name, text),
+    OPERATOR        0x13    = (name, text),
+    OPERATOR        0x14    >= (name, text),
+    OPERATOR        0x15    > (name, text)
+;
diff --git a/contrib/btree_gin/btree_gin.c b/contrib/btree_gin/btree_gin.c
index be5b89a3081..3d668ad313f 100644
--- a/contrib/btree_gin/btree_gin.c
+++ b/contrib/btree_gin/btree_gin.c
@@ -6,6 +6,7 @@
 #include <limits.h>
 
 #include "access/stratnum.h"
+#include "mb/pg_wchar.h"
 #include "utils/builtins.h"
 #include "utils/date.h"
 #include "utils/float.h"
@@ -13,6 +14,7 @@
 #include "utils/numeric.h"
 #include "utils/timestamp.h"
 #include "utils/uuid.h"
+#include "varatt.h"
 
 PG_MODULE_MAGIC_EXT(
 					.name = "btree_gin",
@@ -630,13 +632,24 @@ leftmostvalue_text(void)
 	return PointerGetDatum(cstring_to_text_with_len("", 0));
 }
 
+static Datum
+cvt_name_text(Datum input)
+{
+	Name		val = DatumGetName(input);
+
+	return PointerGetDatum(cstring_to_text(NameStr(*val)));
+}
+
 static const bool text_rhs_is_varlena[] =
-{true};
+{true, false};
+
+static const btree_gin_convert_function text_cvt_fns[] =
+{NULL, cvt_name_text};
 
 static const PGFunction text_cmp_fns[] =
-{bttextcmp};
+{bttextcmp, btnametextcmp};
 
-GIN_SUPPORT(text, leftmostvalue_text, text_rhs_is_varlena, NULL, text_cmp_fns)
+GIN_SUPPORT(text, leftmostvalue_text, text_rhs_is_varlena, text_cvt_fns, text_cmp_fns)
 
 static const bool bpchar_rhs_is_varlena[] =
 {true};
@@ -836,13 +849,37 @@ leftmostvalue_name(void)
 	return NameGetDatum(result);
 }
 
+static Datum
+cvt_text_name(Datum input)
+{
+	text	   *val = DatumGetTextPP(input);
+	NameData   *result = (NameData *) palloc0(NAMEDATALEN);
+	int			len = VARSIZE_ANY_EXHDR(val);
+
+	/*
+	 * Truncate oversize input.  We're assuming this will produce a result
+	 * considered less than the original.  That could be a bad assumption in
+	 * some collations, but fortunately an index on "name" is generally going
+	 * to use C collation.
+	 */
+	if (len >= NAMEDATALEN)
+		len = pg_mbcliplen(VARDATA_ANY(val), len, NAMEDATALEN - 1);
+
+	memcpy(NameStr(*result), VARDATA_ANY(val), len);
+
+	return NameGetDatum(result);
+}
+
 static const bool name_rhs_is_varlena[] =
-{false};
+{false, true};
+
+static const btree_gin_convert_function name_cvt_fns[] =
+{NULL, cvt_text_name};
 
 static const PGFunction name_cmp_fns[] =
-{btnamecmp};
+{btnamecmp, bttextnamecmp};
 
-GIN_SUPPORT(name, leftmostvalue_name, name_rhs_is_varlena, NULL, name_cmp_fns)
+GIN_SUPPORT(name, leftmostvalue_name, name_rhs_is_varlena, name_cvt_fns, name_cmp_fns)
 
 static Datum
 leftmostvalue_bool(void)
diff --git a/contrib/btree_gin/expected/name.out b/contrib/btree_gin/expected/name.out
index 174de6576f0..3a30f62519c 100644
--- a/contrib/btree_gin/expected/name.out
+++ b/contrib/btree_gin/expected/name.out
@@ -95,3 +95,62 @@ EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i>'abc' ORDER BY i;
                Index Cond: (i > 'abc'::name)
 (6 rows)
 
+explain (costs off)
+SELECT * FROM test_name WHERE i<'abc'::text ORDER BY i;
+                 QUERY PLAN                  
+---------------------------------------------
+ Sort
+   Sort Key: i
+   ->  Bitmap Heap Scan on test_name
+         Recheck Cond: (i < 'abc'::text)
+         ->  Bitmap Index Scan on idx_name
+               Index Cond: (i < 'abc'::text)
+(6 rows)
+
+SELECT * FROM test_name WHERE i<'abc'::text ORDER BY i;
+  i  
+-----
+ a
+ ab
+ abb
+(3 rows)
+
+SELECT * FROM test_name WHERE i<='abc'::text ORDER BY i;
+  i  
+-----
+ a
+ ab
+ abb
+ abc
+(4 rows)
+
+SELECT * FROM test_name WHERE i='abc'::text ORDER BY i;
+  i  
+-----
+ abc
+(1 row)
+
+SELECT * FROM test_name WHERE i>='abc'::text ORDER BY i;
+  i  
+-----
+ abc
+ axy
+ xyz
+(3 rows)
+
+SELECT * FROM test_name WHERE i>'abc'::text ORDER BY i;
+  i  
+-----
+ axy
+ xyz
+(2 rows)
+
+SELECT * FROM test_name WHERE i<=repeat('abc', 100) ORDER BY i;
+  i  
+-----
+ a
+ ab
+ abb
+ abc
+(4 rows)
+
diff --git a/contrib/btree_gin/expected/text.out b/contrib/btree_gin/expected/text.out
index 3e31ad744d6..7f52f3db7b3 100644
--- a/contrib/btree_gin/expected/text.out
+++ b/contrib/btree_gin/expected/text.out
@@ -42,3 +42,53 @@ SELECT * FROM test_text WHERE i>'abc' ORDER BY i;
  xyz
 (2 rows)
 
+explain (costs off)
+SELECT * FROM test_text WHERE i<'abc'::name COLLATE "default" ORDER BY i;
+                          QUERY PLAN                           
+---------------------------------------------------------------
+ Sort
+   Sort Key: i
+   ->  Bitmap Heap Scan on test_text
+         Recheck Cond: (i < 'abc'::name COLLATE "default")
+         ->  Bitmap Index Scan on idx_text
+               Index Cond: (i < 'abc'::name COLLATE "default")
+(6 rows)
+
+SELECT * FROM test_text WHERE i<'abc'::name COLLATE "default" ORDER BY i;
+  i  
+-----
+ a
+ ab
+ abb
+(3 rows)
+
+SELECT * FROM test_text WHERE i<='abc'::name COLLATE "default" ORDER BY i;
+  i  
+-----
+ a
+ ab
+ abb
+ abc
+(4 rows)
+
+SELECT * FROM test_text WHERE i='abc'::name COLLATE "default" ORDER BY i;
+  i  
+-----
+ abc
+(1 row)
+
+SELECT * FROM test_text WHERE i>='abc'::name COLLATE "default" ORDER BY i;
+  i  
+-----
+ abc
+ axy
+ xyz
+(3 rows)
+
+SELECT * FROM test_text WHERE i>'abc'::name COLLATE "default" ORDER BY i;
+  i  
+-----
+ axy
+ xyz
+(2 rows)
+
diff --git a/contrib/btree_gin/sql/name.sql b/contrib/btree_gin/sql/name.sql
index c11580cdf96..551d9289407 100644
--- a/contrib/btree_gin/sql/name.sql
+++ b/contrib/btree_gin/sql/name.sql
@@ -19,3 +19,14 @@ EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i<='abc' ORDER BY i;
 EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i='abc' ORDER BY i;
 EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i>='abc' ORDER BY i;
 EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i>'abc' ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_name WHERE i<'abc'::text ORDER BY i;
+
+SELECT * FROM test_name WHERE i<'abc'::text ORDER BY i;
+SELECT * FROM test_name WHERE i<='abc'::text ORDER BY i;
+SELECT * FROM test_name WHERE i='abc'::text ORDER BY i;
+SELECT * FROM test_name WHERE i>='abc'::text ORDER BY i;
+SELECT * FROM test_name WHERE i>'abc'::text ORDER BY i;
+
+SELECT * FROM test_name WHERE i<=repeat('abc', 100) ORDER BY i;
diff --git a/contrib/btree_gin/sql/text.sql b/contrib/btree_gin/sql/text.sql
index d5b3b398989..978b21376fd 100644
--- a/contrib/btree_gin/sql/text.sql
+++ b/contrib/btree_gin/sql/text.sql
@@ -13,3 +13,12 @@ SELECT * FROM test_text WHERE i<='abc' ORDER BY i;
 SELECT * FROM test_text WHERE i='abc' ORDER BY i;
 SELECT * FROM test_text WHERE i>='abc' ORDER BY i;
 SELECT * FROM test_text WHERE i>'abc' ORDER BY i;
+
+explain (costs off)
+SELECT * FROM test_text WHERE i<'abc'::name COLLATE "default" ORDER BY i;
+
+SELECT * FROM test_text WHERE i<'abc'::name COLLATE "default" ORDER BY i;
+SELECT * FROM test_text WHERE i<='abc'::name COLLATE "default" ORDER BY i;
+SELECT * FROM test_text WHERE i='abc'::name COLLATE "default" ORDER BY i;
+SELECT * FROM test_text WHERE i>='abc'::name COLLATE "default" ORDER BY i;
+SELECT * FROM test_text WHERE i>'abc'::name COLLATE "default" ORDER BY i;
-- 
2.43.5