v20-0005-Add-multi-range_without_portion-proc-operator.patch

From 2ae071622b419286261a518d493e1a05d5ed525b Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Sat, 30 Dec 2023 23:10:59 -0800
Subject: [PATCH v20 5/8] Add {multi,}range_without_portion proc & operator

These return an array of their input type are work like minus but don't
fail on splits. They never contain empty elements. We will use this to
support FOR PORTION OF.
---
 src/backend/utils/adt/multirangetypes.c       |  49 +++++++
 src/backend/utils/adt/rangetypes.c            | 124 ++++++++++++++++++
 src/include/catalog/pg_operator.dat           |   6 +
 src/include/catalog/pg_proc.dat               |   6 +
 src/include/utils/rangetypes.h                |   2 +
 src/test/regress/expected/multirangetypes.out | 121 +++++++++++++++++
 src/test/regress/expected/rangetypes.out      |  54 ++++++++
 src/test/regress/sql/multirangetypes.sql      |  22 ++++
 src/test/regress/sql/rangetypes.sql           |  10 ++
 9 files changed, 394 insertions(+)

diff --git a/src/backend/utils/adt/multirangetypes.c b/src/backend/utils/adt/multirangetypes.c
index 9443c2b884..0906345159 100644
--- a/src/backend/utils/adt/multirangetypes.c
+++ b/src/backend/utils/adt/multirangetypes.c
@@ -1226,6 +1226,55 @@ multirange_minus_internal(Oid mltrngtypoid, TypeCacheEntry *rangetyp,
 	return make_multirange(mltrngtypoid, rangetyp, range_count3, ranges3);
 }
 
+/*
+ * multirange minus but returning an array of one,
+ * or zero if the result would be empty.
+ */
+
+Datum
+multirange_without_portion(PG_FUNCTION_ARGS)
+{
+	MultirangeType *mr1 = PG_GETARG_MULTIRANGE_P(0);
+	MultirangeType *mr2 = PG_GETARG_MULTIRANGE_P(1);
+	Oid			mltrngtypoid = MultirangeTypeGetOid(mr1);
+	TypeCacheEntry *typcache;
+	TypeCacheEntry *rangetyp;
+	int32		range_count1;
+	int32		range_count2;
+	RangeType **ranges1;
+	RangeType **ranges2;
+	MultirangeType *mr;
+	Datum	datums[1];
+	ArrayType *ret;
+
+	typcache = multirange_get_typcache(fcinfo, mltrngtypoid);
+	rangetyp = typcache->rngtype;
+
+	if (MultirangeIsEmpty(mr1) || MultirangeIsEmpty(mr2))
+		mr = mr1;
+	else
+	{
+		multirange_deserialize(typcache->rngtype, mr1, &range_count1, &ranges1);
+		multirange_deserialize(typcache->rngtype, mr2, &range_count2, &ranges2);
+
+		mr = multirange_minus_internal(mltrngtypoid,
+									   rangetyp,
+									   range_count1,
+									   ranges1,
+									   range_count2,
+									   ranges2);
+	}
+
+	if (MultirangeIsEmpty(mr))
+		PG_RETURN_ARRAYTYPE_P(construct_empty_array(typcache->type_id));
+	else
+	{
+		datums[0] = MultirangeTypePGetDatum(mr);
+		ret = construct_array(datums, 1, typcache->type_id, typcache->typlen, typcache->typbyval, typcache->typalign);
+		PG_RETURN_ARRAYTYPE_P(ret);
+	}
+}
+
 /* multirange intersection */
 Datum
 multirange_intersect(PG_FUNCTION_ARGS)
diff --git a/src/backend/utils/adt/rangetypes.c b/src/backend/utils/adt/rangetypes.c
index 24bad52923..4eda39676b 100644
--- a/src/backend/utils/adt/rangetypes.c
+++ b/src/backend/utils/adt/rangetypes.c
@@ -37,6 +37,7 @@
 #include "miscadmin.h"
 #include "nodes/miscnodes.h"
 #include "port/pg_bitutils.h"
+#include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/date.h"
 #include "utils/lsyscache.h"
@@ -1206,6 +1207,129 @@ range_split_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeT
 	return false;
 }
 
+/* subtraction but returning an array to accommodate splits */
+Datum
+range_without_portion(PG_FUNCTION_ARGS)
+{
+	RangeType  *r1 = PG_GETARG_RANGE_P(0);
+	RangeType  *r2 = PG_GETARG_RANGE_P(1);
+	RangeType  *rs[2];
+	int			n;
+	TypeCacheEntry *typcache;
+
+	/* Different types should be prevented by ANYRANGE matching rules */
+	if (RangeTypeGetOid(r1) != RangeTypeGetOid(r2))
+		elog(ERROR, "range types do not match");
+
+	typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r1));
+
+	range_without_portion_internal(typcache, r1, r2, rs, &n);
+
+	if (n == 0)
+		PG_RETURN_ARRAYTYPE_P(construct_empty_array(typcache->type_id));
+	else
+	{
+		Datum		xs[2];
+		int			i;
+		ArrayType	*ret;
+
+		for (i = 0; i < n; i++)
+			xs[i] = RangeTypePGetDatum(rs[i]);
+		
+		ret = construct_array(xs, n, typcache->type_id, typcache->typlen, typcache->typbyval, typcache->typalign);
+		PG_RETURN_ARRAYTYPE_P(ret);
+	}
+}
+
+/*
+ * range_without_portion_internal - Sets outputs and outputn to the ranges
+ * remaining and their count (respectively) after subtracting r2 from r1.
+ * The array should never contain empty ranges.
+ * The outputs will be ordered. We expect that outputs is an array of
+ * RangeType pointers, already allocated with two slots.
+ */
+void
+range_without_portion_internal(TypeCacheEntry *typcache, RangeType *r1,
+							   RangeType *r2, RangeType **outputs, int *outputn)
+{
+	int			cmp_l1l2,
+				cmp_l1u2,
+				cmp_u1l2,
+				cmp_u1u2;
+	RangeBound	lower1,
+				lower2;
+	RangeBound	upper1,
+				upper2;
+	bool		empty1,
+				empty2;
+
+	range_deserialize(typcache, r1, &lower1, &upper1, &empty1);
+	range_deserialize(typcache, r2, &lower2, &upper2, &empty2);
+
+	if (empty1)
+	{
+		/* if r1 is empty then r1 - r2 is empty, so return zero results */
+		*outputn = 0;
+		return;
+	}
+	else if (empty2)
+	{
+		/* r2 is empty so the result is just r1 (which we know is not empty) */
+		outputs[0] = r1;
+		*outputn = 1;
+		return;
+	}
+
+	/*
+	 * Use the same logic as range_minus_internal,
+	 * but support the split case
+	 */
+	cmp_l1l2 = range_cmp_bounds(typcache, &lower1, &lower2);
+	cmp_l1u2 = range_cmp_bounds(typcache, &lower1, &upper2);
+	cmp_u1l2 = range_cmp_bounds(typcache, &upper1, &lower2);
+	cmp_u1u2 = range_cmp_bounds(typcache, &upper1, &upper2);
+
+	if (cmp_l1l2 < 0 && cmp_u1u2 > 0)
+	{
+		lower2.inclusive = !lower2.inclusive;
+		lower2.lower = false;	/* it will become the upper bound */
+		outputs[0] = make_range(typcache, &lower1, &lower2, false, NULL);
+
+		upper2.inclusive = !upper2.inclusive;
+		upper2.lower = true;	/* it will become the lower bound */
+		outputs[1] = make_range(typcache, &upper2, &upper1, false, NULL);
+
+		*outputn = 2;
+	}
+	else if (cmp_l1u2 > 0 || cmp_u1l2 < 0)
+	{
+		outputs[0] = r1;
+		*outputn = 1;
+	}
+	else if (cmp_l1l2 >= 0 && cmp_u1u2 <= 0)
+	{
+		*outputn = 0;
+	}
+	else if (cmp_l1l2 <= 0 && cmp_u1l2 >= 0 && cmp_u1u2 <= 0)
+	{
+		lower2.inclusive = !lower2.inclusive;
+		lower2.lower = false;	/* it will become the upper bound */
+		outputs[0] = make_range(typcache, &lower1, &lower2, false, NULL);
+		*outputn = 1;
+	}
+	else if (cmp_l1l2 >= 0 && cmp_u1u2 >= 0 && cmp_l1u2 <= 0)
+	{
+		upper2.inclusive = !upper2.inclusive;
+		upper2.lower = true;	/* it will become the lower bound */
+		outputs[0] = make_range(typcache, &upper2, &upper1, false, NULL);
+		*outputn = 1;
+	}
+	else
+	{
+		elog(ERROR, "unexpected case in range_without_portion");
+	}
+}
+
 /* range -> range aggregate functions */
 
 Datum
diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat
index b2cdea66c4..9b783ce326 100644
--- a/src/include/catalog/pg_operator.dat
+++ b/src/include/catalog/pg_operator.dat
@@ -3457,5 +3457,11 @@
   oprresult => 'bool', oprcom => '<<(anymultirange,anymultirange)',
   oprcode => 'multirange_after_multirange', oprrest => 'multirangesel',
   oprjoin => 'scalargtjoinsel' },
+{ oid => '8405', descr => 'range without portion',
+  oprname => '@-', oprleft => 'anyrange', oprright => 'anyrange',
+  oprresult => 'anyarray', oprcode => 'range_without_portion' },
+{ oid => '8407', descr => 'multirange without portion',
+  oprname => '@-', oprleft => 'anymultirange', oprright => 'anymultirange',
+  oprresult => 'anyarray', oprcode => 'multirange_without_portion' },
 
 ]
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 874ca98ed6..727eb35087 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10570,6 +10570,9 @@
 { oid => '3869',
   proname => 'range_minus', prorettype => 'anyrange',
   proargtypes => 'anyrange anyrange', prosrc => 'range_minus' },
+{ oid => '8404',
+  proname => 'range_without_portion', prorettype => 'anyarray',
+  proargtypes => 'anyrange anyrange', prosrc => 'range_without_portion' },
 { oid => '3870', descr => 'less-equal-greater',
   proname => 'range_cmp', prorettype => 'int4',
   proargtypes => 'anyrange anyrange', prosrc => 'range_cmp' },
@@ -10857,6 +10860,9 @@
 { oid => '4271',
   proname => 'multirange_minus', prorettype => 'anymultirange',
   proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_minus' },
+{ oid => '8406',
+  proname => 'multirange_without_portion', prorettype => 'anyarray',
+  proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_without_portion' },
 { oid => '4272',
   proname => 'multirange_intersect', prorettype => 'anymultirange',
   proargtypes => 'anymultirange anymultirange',
diff --git a/src/include/utils/rangetypes.h b/src/include/utils/rangetypes.h
index 6b420a8618..e1b51a1017 100644
--- a/src/include/utils/rangetypes.h
+++ b/src/include/utils/rangetypes.h
@@ -164,5 +164,7 @@ extern RangeType *make_empty_range(TypeCacheEntry *typcache);
 extern bool range_split_internal(TypeCacheEntry *typcache, const RangeType *r1,
 								 const RangeType *r2, RangeType **output1,
 								 RangeType **output2);
+extern void range_without_portion_internal(TypeCacheEntry *typcache, RangeType *r1,
+									RangeType *r2, RangeType **outputs, int *outputn);
 
 #endif							/* RANGETYPES_H */
diff --git a/src/test/regress/expected/multirangetypes.out b/src/test/regress/expected/multirangetypes.out
index a0cb875492..297aa0cc85 100644
--- a/src/test/regress/expected/multirangetypes.out
+++ b/src/test/regress/expected/multirangetypes.out
@@ -2200,6 +2200,127 @@ SELECT nummultirange(numrange(1,2), numrange(4,5)) - nummultirange(numrange(-2,0
  {[1,2),[4,5)}
 (1 row)
 
+-- without_portion
+SELECT nummultirange() @- nummultirange();
+ ?column? 
+----------
+ {}
+(1 row)
+
+SELECT nummultirange() @- nummultirange(numrange(1,2));
+ ?column? 
+----------
+ {}
+(1 row)
+
+SELECT nummultirange(numrange(1,2)) @- nummultirange();
+  ?column?   
+-------------
+ {"{[1,2)}"}
+(1 row)
+
+SELECT nummultirange(numrange(1,2), numrange(3,4)) @- nummultirange();
+     ?column?      
+-------------------
+ {"{[1,2),[3,4)}"}
+(1 row)
+
+SELECT nummultirange(numrange(1,2)) @- nummultirange(numrange(1,2));
+ ?column? 
+----------
+ {}
+(1 row)
+
+SELECT nummultirange(numrange(1,2)) @- nummultirange(numrange(2,4));
+  ?column?   
+-------------
+ {"{[1,2)}"}
+(1 row)
+
+SELECT nummultirange(numrange(1,2)) @- nummultirange(numrange(3,4));
+  ?column?   
+-------------
+ {"{[1,2)}"}
+(1 row)
+
+SELECT nummultirange(numrange(1,4)) @- nummultirange(numrange(1,2));
+  ?column?   
+-------------
+ {"{[2,4)}"}
+(1 row)
+
+SELECT nummultirange(numrange(1,4)) @- nummultirange(numrange(2,3));
+     ?column?      
+-------------------
+ {"{[1,2),[3,4)}"}
+(1 row)
+
+SELECT nummultirange(numrange(1,4)) @- nummultirange(numrange(0,8));
+ ?column? 
+----------
+ {}
+(1 row)
+
+SELECT nummultirange(numrange(1,4)) @- nummultirange(numrange(0,2));
+  ?column?   
+-------------
+ {"{[2,4)}"}
+(1 row)
+
+SELECT nummultirange(numrange(1,8)) @- nummultirange(numrange(0,2), numrange(3,4));
+     ?column?      
+-------------------
+ {"{[2,3),[4,8)}"}
+(1 row)
+
+SELECT nummultirange(numrange(1,8)) @- nummultirange(numrange(2,3), numrange(5,null));
+     ?column?      
+-------------------
+ {"{[1,2),[3,5)}"}
+(1 row)
+
+SELECT nummultirange(numrange(1,2), numrange(4,5)) @- nummultirange(numrange(-2,0));
+     ?column?      
+-------------------
+ {"{[1,2),[4,5)}"}
+(1 row)
+
+SELECT nummultirange(numrange(1,2), numrange(4,5)) @- nummultirange(numrange(2,4));
+     ?column?      
+-------------------
+ {"{[1,2),[4,5)}"}
+(1 row)
+
+SELECT nummultirange(numrange(1,2), numrange(4,5)) @- nummultirange(numrange(3,5));
+  ?column?   
+-------------
+ {"{[1,2)}"}
+(1 row)
+
+SELECT nummultirange(numrange(1,2), numrange(4,5)) @- nummultirange(numrange(0,9));
+ ?column? 
+----------
+ {}
+(1 row)
+
+SELECT nummultirange(numrange(1,3), numrange(4,5)) @- nummultirange(numrange(2,9));
+  ?column?   
+-------------
+ {"{[1,2)}"}
+(1 row)
+
+SELECT nummultirange(numrange(1,2), numrange(4,5)) @- nummultirange(numrange(8,9));
+     ?column?      
+-------------------
+ {"{[1,2),[4,5)}"}
+(1 row)
+
+SELECT nummultirange(numrange(1,2), numrange(4,5)) @- nummultirange(numrange(-2,0), numrange(8,9));
+     ?column?      
+-------------------
+ {"{[1,2),[4,5)}"}
+(1 row)
+
 -- intersection
 SELECT nummultirange() * nummultirange();
  ?column? 
diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out
index ee02ff0163..1f14e96802 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -481,6 +481,60 @@ select range_minus(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]'));
  empty
 (1 row)
 
+select 'empty'::numrange @- numrange(2.0, 3.0);
+ ?column? 
+----------
+ {}
+(1 row)
+
+select numrange(1.1, 2.2) @- 'empty'::numrange;
+   ?column?    
+---------------
+ {"[1.1,2.2)"}
+(1 row)
+
+select numrange(1.1, 2.2) @- numrange(2.0, 3.0);
+   ?column?    
+---------------
+ {"[1.1,2.0)"}
+(1 row)
+
+select numrange(1.1, 2.2) @- numrange(2.2, 3.0);
+   ?column?    
+---------------
+ {"[1.1,2.2)"}
+(1 row)
+
+select numrange(1.1, 2.2,'[]') @- numrange(2.0, 3.0);
+   ?column?    
+---------------
+ {"[1.1,2.0)"}
+(1 row)
+
+select numrange(1.0, 3.0) @- numrange(1.5, 2.0);
+         ?column?          
+---------------------------
+ {"[1.0,1.5)","[2.0,3.0)"}
+(1 row)
+
+select range_without_portion(numrange(10.1,12.2,'[]'), numrange(110.0,120.2,'(]'));
+ range_without_portion 
+-----------------------
+ {"[10.1,12.2]"}
+(1 row)
+
+select range_without_portion(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]'));
+ range_without_portion 
+-----------------------
+ {}
+(1 row)
+
+select range_without_portion(numrange(1.0,3.0,'[]'), numrange(1.5,2.0,'(]'));
+   range_without_portion   
+---------------------------
+ {"[1.0,1.5]","(2.0,3.0]"}
+(1 row)
+
 select numrange(4.5, 5.5, '[]') && numrange(5.5, 6.5);
  ?column? 
 ----------
diff --git a/src/test/regress/sql/multirangetypes.sql b/src/test/regress/sql/multirangetypes.sql
index fefb4b4d42..659272791b 100644
--- a/src/test/regress/sql/multirangetypes.sql
+++ b/src/test/regress/sql/multirangetypes.sql
@@ -414,6 +414,28 @@ SELECT nummultirange(numrange(1,3), numrange(4,5)) - nummultirange(numrange(2,9)
 SELECT nummultirange(numrange(1,2), numrange(4,5)) - nummultirange(numrange(8,9));
 SELECT nummultirange(numrange(1,2), numrange(4,5)) - nummultirange(numrange(-2,0), numrange(8,9));
 
+-- without_portion
+SELECT nummultirange() @- nummultirange();
+SELECT nummultirange() @- nummultirange(numrange(1,2));
+SELECT nummultirange(numrange(1,2)) @- nummultirange();
+SELECT nummultirange(numrange(1,2), numrange(3,4)) @- nummultirange();
+SELECT nummultirange(numrange(1,2)) @- nummultirange(numrange(1,2));
+SELECT nummultirange(numrange(1,2)) @- nummultirange(numrange(2,4));
+SELECT nummultirange(numrange(1,2)) @- nummultirange(numrange(3,4));
+SELECT nummultirange(numrange(1,4)) @- nummultirange(numrange(1,2));
+SELECT nummultirange(numrange(1,4)) @- nummultirange(numrange(2,3));
+SELECT nummultirange(numrange(1,4)) @- nummultirange(numrange(0,8));
+SELECT nummultirange(numrange(1,4)) @- nummultirange(numrange(0,2));
+SELECT nummultirange(numrange(1,8)) @- nummultirange(numrange(0,2), numrange(3,4));
+SELECT nummultirange(numrange(1,8)) @- nummultirange(numrange(2,3), numrange(5,null));
+SELECT nummultirange(numrange(1,2), numrange(4,5)) @- nummultirange(numrange(-2,0));
+SELECT nummultirange(numrange(1,2), numrange(4,5)) @- nummultirange(numrange(2,4));
+SELECT nummultirange(numrange(1,2), numrange(4,5)) @- nummultirange(numrange(3,5));
+SELECT nummultirange(numrange(1,2), numrange(4,5)) @- nummultirange(numrange(0,9));
+SELECT nummultirange(numrange(1,3), numrange(4,5)) @- nummultirange(numrange(2,9));
+SELECT nummultirange(numrange(1,2), numrange(4,5)) @- nummultirange(numrange(8,9));
+SELECT nummultirange(numrange(1,2), numrange(4,5)) @- nummultirange(numrange(-2,0), numrange(8,9));
+
 -- intersection
 SELECT nummultirange() * nummultirange();
 SELECT nummultirange() * nummultirange(numrange(1,2));
diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql
index c23be928c3..562d46c5da 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -107,6 +107,16 @@ select numrange(1.1, 2.2,'[]') - numrange(2.0, 3.0);
 select range_minus(numrange(10.1,12.2,'[]'), numrange(110.0,120.2,'(]'));
 select range_minus(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]'));
 
+select 'empty'::numrange @- numrange(2.0, 3.0);
+select numrange(1.1, 2.2) @- 'empty'::numrange;
+select numrange(1.1, 2.2) @- numrange(2.0, 3.0);
+select numrange(1.1, 2.2) @- numrange(2.2, 3.0);
+select numrange(1.1, 2.2,'[]') @- numrange(2.0, 3.0);
+select numrange(1.0, 3.0) @- numrange(1.5, 2.0);
+select range_without_portion(numrange(10.1,12.2,'[]'), numrange(110.0,120.2,'(]'));
+select range_without_portion(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]'));
+select range_without_portion(numrange(1.0,3.0,'[]'), numrange(1.5,2.0,'(]'));
+
 select numrange(4.5, 5.5, '[]') && numrange(5.5, 6.5);
 select numrange(1.0, 2.0) << numrange(3.0, 4.0);
 select numrange(1.0, 3.0,'[]') << numrange(3.0, 4.0,'[]');
-- 
2.42.0