v2-0005-Allow-schema-qualifying-the-comparison-operator-o.patch

From b0cdce84f7825651a6bda1a371bcc2458311545f Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Micha=C5=82=20Pasternak?= <michal.dtz@gmail.com>
Date: Tue, 7 Jul 2026 10:30:40 +0200
Subject: [PATCH v2 5/5] Allow schema-qualifying the comparison operator of a
 simple CASE

A simple CASE, CASE x WHEN y THEN ..., is defined to compare the test
expression against each WHEN value exactly as if "x = y" had been written,
and each of those "=" operators is resolved independently by unqualified
name.  Two arms of one CASE can therefore resolve to different operators:
in

    CASE x WHEN 4 THEN ... WHEN 5.0 THEN ... END

the first arm resolves integer equality while the second resolves numeric
equality, and either could belong to a schema that is not on the search
path.  ruleutils.c could not reproduce that at all: when it recognized the
implicit "CaseTestExpr = RHS" form it printed just the RHS and silently
dropped the operator, so a dumped-and-reloaded view reparsed every arm with
whatever "=" the restore-time search path happened to resolve -- changed
semantics, no error.

Give the simple CASE an optional per-arm operator syntax mirroring the one
added for NULLIF and IS [NOT] DISTINCT FROM,

    CASE x WHEN y USING OPERATOR(schema.=) THEN ...

which pins the comparison operator for that arm (and only that arm).  A new
raw-only List field CaseWhen.opname carries the written name from the
grammar to parse analysis, where transformCaseExpr expands the arm into the
chosen operator instead of a bare "="; the field is reset to NIL in analyzed
trees, and using the clause on a searched CASE (which has no test expression)
is rejected.

When deparsing, each recognized arm now emits USING OPERATOR() honestly:
whenever a bare "WHEN value" would not reparse to the same operator -- the
operator is not reachable by an unqualified lookup of its own name, or is
not named "=" -- the clause is printed, schema-qualified as needed, per arm.
Output is unchanged whenever the bare form already recovered the operator.

CaseWhen now serializes an additional field, so bump the catalog version.

Discussion: https://postgr.es/m/3856834.1783087886@sss.pgh.pa.us
---
 doc/src/sgml/func/func-conditional.sgml       |  22 ++-
 doc/src/sgml/syntax.sgml                      |   6 +-
 src/backend/parser/gram.y                     |  10 ++
 src/backend/parser/parse_expr.c               |  23 ++-
 src/backend/utils/adt/ruleutils.c             |  29 ++++
 src/include/catalog/catversion.h              |   2 +-
 src/include/nodes/primnodes.h                 |   6 +
 .../regress/expected/operator_qualify.out     | 139 ++++++++++++++++++
 src/test/regress/sql/operator_qualify.sql     |  75 ++++++++++
 9 files changed, 304 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/func/func-conditional.sgml b/doc/src/sgml/func/func-conditional.sgml
index 137d5c7e43..6729788e3a 100644
--- a/doc/src/sgml/func/func-conditional.sgml
+++ b/doc/src/sgml/func/func-conditional.sgml
@@ -99,7 +99,7 @@ SELECT a,
 
 <synopsis>
 CASE <replaceable>expression</replaceable>
-    WHEN <replaceable>value</replaceable> THEN <replaceable>result</replaceable>
+    WHEN <replaceable>value</replaceable> <optional> USING OPERATOR(<replaceable>operator</replaceable>) </optional> THEN <replaceable>result</replaceable>
     <optional>WHEN ...</optional>
     <optional>ELSE <replaceable>result</replaceable></optional>
 END
@@ -114,6 +114,26 @@ END
    to the <function>switch</function> statement in C.
   </para>
 
+  <para>
+   Each comparison is performed exactly as if you had
+   written <literal><replaceable>expression</replaceable>
+   = <replaceable>value</replaceable></literal>, so a
+   suitable <literal>=</literal> operator must be available, and it is
+   resolved by name through the current search path independently for each
+   <token>WHEN</token> clause.  To pin a specific operator for a
+   given clause instead &mdash; for example one belonging to an extension whose
+   schema is not on the search path &mdash; give its name with the
+   optional <literal>USING OPERATOR()</literal> clause (see
+   <xref linkend="sql-expressions-operator-calls"/> for
+   the <literal>OPERATOR()</literal> notation).  When a view or rule that uses
+   the simple <token>CASE</token> form is dumped,
+   <application>pg_dump</application> emits this clause automatically for any
+   clause whose bare name <literal>=</literal> would not resolve to the same
+   operator under the search path used at restore time, or whose intended
+   operator is not named <literal>=</literal> at all, so that the definition
+   reloads unambiguously.
+  </para>
+
    <para>
     The example above can be written using the simple
     <token>CASE</token> syntax:
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 5165533813..f06b98e728 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -1141,7 +1141,11 @@ SELECT 3 OPERATOR(pg_catalog.+) 4;
     of operators inside <literal>OPERATOR()</literal>, one for each column of
     the compared rows (see <xref linkend="row-wise-comparison"/>); this
     includes row-constructor comparisons against a subquery
-    (see <xref linkend="functions-subquery"/>).
+    (see <xref linkend="functions-subquery"/>).  The simple
+    <token>CASE</token> form accepts the decoration on each
+    <token>WHEN</token> clause with <literal>USING OPERATOR()</literal>,
+    pinning the comparison operator for that arm
+    (see <xref linkend="functions-case"/>).
    </para>
 
    <note>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 30bd69c01f..f28adc2b55 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -17900,6 +17900,16 @@ when_clause:
 					w->location = @1;
 					$$ = (Node *) w;
 				}
+			| WHEN a_expr USING OPERATOR '(' any_operator ')' THEN a_expr
+				{
+					CaseWhen   *w = makeNode(CaseWhen);
+
+					w->opname = $6;
+					w->expr = (Expr *) $2;
+					w->result = (Expr *) $9;
+					w->location = @1;
+					$$ = (Node *) w;
+				}
 		;
 
 case_default:
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index a8630e6c34..09d91c4378 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1766,12 +1766,25 @@ transformCaseExpr(ParseState *pstate, CaseExpr *c)
 		warg = (Node *) w->expr;
 		if (placeholder)
 		{
-			/* shorthand form was specified, so expand... */
-			warg = (Node *) makeSimpleA_Expr(AEXPR_OP, "=",
-											 (Node *) placeholder,
-											 warg,
-											 w->location);
+			/*
+			 * Shorthand form was specified, so expand it into an equality
+			 * comparison between the CASE test expression and the WHEN value.
+			 * The comparison operator defaults to "=", but the user may pin a
+			 * specific (possibly schema-qualified) operator with the optional
+			 * USING OPERATOR() clause.
+			 */
+			warg = (Node *) makeA_Expr(AEXPR_OP,
+									   w->opname ? w->opname :
+									   list_make1(makeString("=")),
+									   (Node *) placeholder, warg,
+									   w->location);
 		}
+		else if (w->opname != NIL)
+			ereport(ERROR,
+					(errcode(ERRCODE_SYNTAX_ERROR),
+					 errmsg("CASE/WHEN ... USING OPERATOR requires a CASE test expression"),
+					 parser_errposition(pstate, w->location)));
+		neww->opname = NIL;		/* raw-only; keep analyzed trees clean */
 		neww->expr = (Expr *) transformExprRecurse(pstate, warg);
 
 		neww->expr = (Expr *) coerce_to_boolean(pstate,
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 487ee4b6e0..aea9628106 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -10433,6 +10433,7 @@ get_rule_expr(Node *node, deparse_context *context,
 				{
 					CaseWhen   *when = (CaseWhen *) lfirst(temp);
 					Node	   *w = (Node *) when->expr;
+					OpExpr	   *usingop = NULL;
 
 					if (caseexpr->arg)
 					{
@@ -10455,7 +10456,10 @@ get_rule_expr(Node *node, deparse_context *context,
 							if (list_length(args) == 2 &&
 								IsA(strip_implicit_coercions(linitial(args)),
 									CaseTestExpr))
+							{
+								usingop = (OpExpr *) w;
 								w = (Node *) lsecond(args);
+							}
 						}
 					}
 
@@ -10464,6 +10468,31 @@ get_rule_expr(Node *node, deparse_context *context,
 					appendContextKeyword(context, "WHEN ",
 										 0, 0, 0);
 					get_rule_expr(w, context, false);
+
+					/*
+					 * If we recognized the implicit-equality WHEN form, the
+					 * comparison operator was resolved from the bare name "="
+					 * against the CASE test expression and the WHEN value.  A
+					 * bare "WHEN value" reparses by resolving "=" again, so
+					 * we must emit an explicit USING OPERATOR() clause
+					 * whenever that would not recover the same operator: i.e.
+					 * the operator is not reachable by an unqualified lookup
+					 * of its own name (then it appears schema-qualified), or
+					 * it is named something other than "=".
+					 */
+					if (usingop)
+					{
+						bool		needs_qual;
+						char	   *opname;
+
+						opname = generate_operator_name_extended(usingop->opno,
+																 exprType(linitial(usingop->args)),
+																 exprType(lsecond(usingop->args)),
+																 &needs_qual);
+						if (needs_qual || strcmp(opname, "=") != 0)
+							appendStringInfo(buf, " USING OPERATOR(%s)", opname);
+					}
+
 					appendStringInfoString(buf, " THEN ");
 					get_rule_expr((Node *) when->result, context, true);
 				}
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 7571c5cd08..79291aaaf5 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202607061
+#define CATALOG_VERSION_NO	202607071
 
 #endif
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index bf343cbec0..00f37c2295 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1340,6 +1340,12 @@ typedef struct CaseExpr
 typedef struct CaseWhen
 {
 	Expr		xpr;
+
+	/*
+	 * possibly-qualified operator name given with USING OPERATOR(), or NIL;
+	 * consumed by parse analysis, always NIL in analyzed trees
+	 */
+	List	   *opname pg_node_attr(query_jumble_ignore);
 	Expr	   *expr;			/* condition expression */
 	Expr	   *result;			/* substitution result */
 	ParseLoc	location;		/* token location, or -1 if unknown */
diff --git a/src/test/regress/expected/operator_qualify.out b/src/test/regress/expected/operator_qualify.out
index c0887e6311..f13496ef78 100644
--- a/src/test/regress/expected/operator_qualify.out
+++ b/src/test/regress/expected/operator_qualify.out
@@ -752,3 +752,142 @@ DROP VIEW oq_v_any_reload, oq_v_in_reload, oq_v_ne_all_reload,
           oq_v_in_mixed_reload, oq_v_any_mixed_reload;
 -- NOTE: the new oq_v_* base views are intentionally kept (like the others)
 -- so the pg_upgrade suite dump/restores them and exercises the deparse.
+--
+-- Simple CASE: each "CASE x WHEN y" arm resolves its own "=" operator by
+-- unqualified name, independently per WHEN clause.  The comparison operator
+-- can be pinned per arm with USING OPERATOR(); the deparse decorates only the
+-- arms whose bare "=" would not recover the same operator at restore time.
+--
+-- Built while alt_ops shadows pg_catalog: the implicit "=" resolves alt_ops.=.
+SET search_path = alt_ops, pg_catalog;
+CREATE VIEW public.oq_v_case AS
+  SELECT CASE a WHEN b THEN 'eq' ELSE 'ne' END AS r FROM public.oq_t;
+RESET search_path;
+-- Off the path, the arm's operator must be qualified: the bare form would
+-- reparse "=" (pg_catalog.=), a different operator.
+SELECT pg_get_viewdef('oq_v_case'::regclass, true);
+                        pg_get_viewdef                        
+--------------------------------------------------------------
+  SELECT                                                     +
+         CASE a                                              +
+             WHEN b USING OPERATOR(alt_ops.=) THEN 'eq'::text+
+             ELSE 'ne'::text                                 +
+         END AS r                                            +
+    FROM oq_t;
+(1 row)
+
+-- With alt_ops reachable again, the plain simple-CASE syntax comes back.
+SET search_path = alt_ops, pg_catalog, public;
+SELECT pg_get_viewdef('oq_v_case'::regclass, true);
+           pg_get_viewdef           
+------------------------------------
+  SELECT                           +
+         CASE a                    +
+             WHEN b THEN 'eq'::text+
+             ELSE 'ne'::text       +
+         END AS r                  +
+    FROM oq_t;
+(1 row)
+
+RESET search_path;
+-- A bare-resolvable operator whose name is not "=" must still be decorated:
+-- reparsing "WHEN b" would resolve "=", not this operator.  pg_catalog.< is on
+-- the default search_path, so its name prints unqualified, but it is wrapped in
+-- OPERATOR() so the reparsed semantics stay identical.
+CREATE VIEW public.oq_v_case_lt AS
+  SELECT CASE a WHEN b USING OPERATOR(<) THEN 'lt' ELSE 'ge' END AS r
+  FROM public.oq_t;
+SELECT pg_get_viewdef('oq_v_case_lt'::regclass, true);
+                    pg_get_viewdef                    
+------------------------------------------------------
+  SELECT                                             +
+         CASE a                                      +
+             WHEN b USING OPERATOR(<) THEN 'lt'::text+
+             ELSE 'ge'::text                         +
+         END AS r                                    +
+    FROM oq_t;
+(1 row)
+
+-- Per-arm operators may differ within one CASE.  Built under the alt_ops path:
+-- the first arm's implicit "=" resolves alt_ops.= (off the default path -> must
+-- be qualified), while the second arm pins pg_catalog.= explicitly (reachable
+-- bare -> stays undecorated).  The deparse decorates only the first arm.
+SET search_path = alt_ops, pg_catalog;
+CREATE VIEW public.oq_v_case_mixed AS
+  SELECT CASE a WHEN b THEN 'x'
+                WHEN id USING OPERATOR(pg_catalog.=) THEN 'y'
+                ELSE 'z' END AS r
+  FROM public.oq_t;
+RESET search_path;
+SELECT pg_get_viewdef('oq_v_case_mixed'::regclass, true);
+                       pg_get_viewdef                        
+-------------------------------------------------------------
+  SELECT                                                    +
+         CASE a                                             +
+             WHEN b USING OPERATOR(alt_ops.=) THEN 'x'::text+
+             WHEN id THEN 'y'::text                         +
+             ELSE 'z'::text                                 +
+         END AS r                                           +
+    FROM oq_t;
+(1 row)
+
+-- The decorated syntax is directly acceptable, and it pins the given operator.
+SELECT CASE 1 WHEN 2 USING OPERATOR(alt_ops.=) THEN 'x' ELSE 'y' END AS y;
+ y 
+---
+ y
+(1 row)
+
+SELECT CASE 1 WHEN 1 USING OPERATOR(pg_catalog.=) THEN 'x' ELSE 'y' END AS x;
+ x 
+---
+ x
+(1 row)
+
+-- an unqualified name inside the decoration is fine too
+SELECT CASE 1 WHEN 1 USING OPERATOR(=) THEN 'x' ELSE 'y' END AS x;
+ x 
+---
+ x
+(1 row)
+
+-- USING OPERATOR() only makes sense for the simple form; the searched CASE
+-- (no CASE test expression) rejects it, pointing at the offending WHEN.
+SELECT CASE WHEN 1=1 USING OPERATOR(=) THEN 1 END;
+ERROR:  CASE/WHEN ... USING OPERATOR requires a CASE test expression
+LINE 1: SELECT CASE WHEN 1=1 USING OPERATOR(=) THEN 1 END;
+                    ^
+-- The deparsed simple-CASE definitions must reparse under a restricted
+-- search_path (the pg_dump scenario), pinning the same operator OIDs.
+BEGIN; SET LOCAL search_path = pg_catalog;
+DO $$ BEGIN
+  EXECUTE 'CREATE VIEW public.oq_v_case_reload AS ' || pg_get_viewdef('public.oq_v_case'::regclass);
+  EXECUTE 'CREATE VIEW public.oq_v_case_lt_reload AS ' || pg_get_viewdef('public.oq_v_case_lt'::regclass);
+  EXECUTE 'CREATE VIEW public.oq_v_case_mixed_reload AS ' || pg_get_viewdef('public.oq_v_case_mixed'::regclass);
+END $$; COMMIT;
+-- The reloaded views pin alt_ops.= for the arms that were decorated: the whole
+-- CASE view (arm 1) and the mixed view (arm 1).  The simple CASE expands each
+-- arm into an OpExpr, serialized with ":opno".
+SELECT ev_class::regclass FROM pg_rewrite
+WHERE ev_action LIKE '%:opno ' || 'alt_ops.=(int4,int4)'::regoperator::oid || ' %'
+  AND ev_class::regclass::text LIKE 'oq_v_case%reload'
+ORDER BY 1;
+        ev_class        
+------------------------
+ oq_v_case_reload
+ oq_v_case_mixed_reload
+(2 rows)
+
+-- The non-"=" operator OPERATOR(<) pins int4lt across the reload.
+SELECT ev_class::regclass FROM pg_rewrite
+WHERE ev_action LIKE '%:opno ' || 'pg_catalog.<(int4,int4)'::regoperator::oid || ' %'
+  AND ev_class::regclass::text LIKE 'oq_v_case%lt_reload'
+ORDER BY 1;
+      ev_class       
+---------------------
+ oq_v_case_lt_reload
+(1 row)
+
+DROP VIEW oq_v_case_reload, oq_v_case_lt_reload, oq_v_case_mixed_reload;
+-- NOTE: the oq_v_case* base views are intentionally kept (like the others) so
+-- the pg_upgrade suite dump/restores them and exercises the deparse.
diff --git a/src/test/regress/sql/operator_qualify.sql b/src/test/regress/sql/operator_qualify.sql
index f029d0eb73..a2b35e3b0b 100644
--- a/src/test/regress/sql/operator_qualify.sql
+++ b/src/test/regress/sql/operator_qualify.sql
@@ -424,3 +424,78 @@ DROP VIEW oq_v_any_reload, oq_v_in_reload, oq_v_ne_all_reload,
           oq_v_in_mixed_reload, oq_v_any_mixed_reload;
 -- NOTE: the new oq_v_* base views are intentionally kept (like the others)
 -- so the pg_upgrade suite dump/restores them and exercises the deparse.
+
+--
+-- Simple CASE: each "CASE x WHEN y" arm resolves its own "=" operator by
+-- unqualified name, independently per WHEN clause.  The comparison operator
+-- can be pinned per arm with USING OPERATOR(); the deparse decorates only the
+-- arms whose bare "=" would not recover the same operator at restore time.
+--
+-- Built while alt_ops shadows pg_catalog: the implicit "=" resolves alt_ops.=.
+SET search_path = alt_ops, pg_catalog;
+CREATE VIEW public.oq_v_case AS
+  SELECT CASE a WHEN b THEN 'eq' ELSE 'ne' END AS r FROM public.oq_t;
+RESET search_path;
+-- Off the path, the arm's operator must be qualified: the bare form would
+-- reparse "=" (pg_catalog.=), a different operator.
+SELECT pg_get_viewdef('oq_v_case'::regclass, true);
+-- With alt_ops reachable again, the plain simple-CASE syntax comes back.
+SET search_path = alt_ops, pg_catalog, public;
+SELECT pg_get_viewdef('oq_v_case'::regclass, true);
+RESET search_path;
+
+-- A bare-resolvable operator whose name is not "=" must still be decorated:
+-- reparsing "WHEN b" would resolve "=", not this operator.  pg_catalog.< is on
+-- the default search_path, so its name prints unqualified, but it is wrapped in
+-- OPERATOR() so the reparsed semantics stay identical.
+CREATE VIEW public.oq_v_case_lt AS
+  SELECT CASE a WHEN b USING OPERATOR(<) THEN 'lt' ELSE 'ge' END AS r
+  FROM public.oq_t;
+SELECT pg_get_viewdef('oq_v_case_lt'::regclass, true);
+
+-- Per-arm operators may differ within one CASE.  Built under the alt_ops path:
+-- the first arm's implicit "=" resolves alt_ops.= (off the default path -> must
+-- be qualified), while the second arm pins pg_catalog.= explicitly (reachable
+-- bare -> stays undecorated).  The deparse decorates only the first arm.
+SET search_path = alt_ops, pg_catalog;
+CREATE VIEW public.oq_v_case_mixed AS
+  SELECT CASE a WHEN b THEN 'x'
+                WHEN id USING OPERATOR(pg_catalog.=) THEN 'y'
+                ELSE 'z' END AS r
+  FROM public.oq_t;
+RESET search_path;
+SELECT pg_get_viewdef('oq_v_case_mixed'::regclass, true);
+
+-- The decorated syntax is directly acceptable, and it pins the given operator.
+SELECT CASE 1 WHEN 2 USING OPERATOR(alt_ops.=) THEN 'x' ELSE 'y' END AS y;
+SELECT CASE 1 WHEN 1 USING OPERATOR(pg_catalog.=) THEN 'x' ELSE 'y' END AS x;
+-- an unqualified name inside the decoration is fine too
+SELECT CASE 1 WHEN 1 USING OPERATOR(=) THEN 'x' ELSE 'y' END AS x;
+
+-- USING OPERATOR() only makes sense for the simple form; the searched CASE
+-- (no CASE test expression) rejects it, pointing at the offending WHEN.
+SELECT CASE WHEN 1=1 USING OPERATOR(=) THEN 1 END;
+
+-- The deparsed simple-CASE definitions must reparse under a restricted
+-- search_path (the pg_dump scenario), pinning the same operator OIDs.
+BEGIN; SET LOCAL search_path = pg_catalog;
+DO $$ BEGIN
+  EXECUTE 'CREATE VIEW public.oq_v_case_reload AS ' || pg_get_viewdef('public.oq_v_case'::regclass);
+  EXECUTE 'CREATE VIEW public.oq_v_case_lt_reload AS ' || pg_get_viewdef('public.oq_v_case_lt'::regclass);
+  EXECUTE 'CREATE VIEW public.oq_v_case_mixed_reload AS ' || pg_get_viewdef('public.oq_v_case_mixed'::regclass);
+END $$; COMMIT;
+-- The reloaded views pin alt_ops.= for the arms that were decorated: the whole
+-- CASE view (arm 1) and the mixed view (arm 1).  The simple CASE expands each
+-- arm into an OpExpr, serialized with ":opno".
+SELECT ev_class::regclass FROM pg_rewrite
+WHERE ev_action LIKE '%:opno ' || 'alt_ops.=(int4,int4)'::regoperator::oid || ' %'
+  AND ev_class::regclass::text LIKE 'oq_v_case%reload'
+ORDER BY 1;
+-- The non-"=" operator OPERATOR(<) pins int4lt across the reload.
+SELECT ev_class::regclass FROM pg_rewrite
+WHERE ev_action LIKE '%:opno ' || 'pg_catalog.<(int4,int4)'::regoperator::oid || ' %'
+  AND ev_class::regclass::text LIKE 'oq_v_case%lt_reload'
+ORDER BY 1;
+DROP VIEW oq_v_case_reload, oq_v_case_lt_reload, oq_v_case_mixed_reload;
+-- NOTE: the oq_v_case* base views are intentionally kept (like the others) so
+-- the pg_upgrade suite dump/restores them and exercises the deparse.
-- 
2.54.0