Add geometry/range functions to support BRIN inclusion

This commit adds the following functions:
    box(point) -> box
    bound_box(box, box) -> box
    inet_same_family(inet, inet) -> bool
    inet_merge(inet, inet) -> cidr
    range_merge(anyrange, anyrange) -> anyrange

The first of these is also used to implement a new assignment cast from
point to box.

These functions are the first part of a base to implement an "inclusion"
operator class for BRIN, for multidimensional data types.

Author: Emre Hasegeli
Reviewed by: Andreas Karlsson

8 files changed, 167 insertions, 2 deletions

diff --git a/src/test/regress/expected/geometry.out b/src/test/regress/expected/geometry.out
index 21ad555c798..1271395d4ea 100644
--- a/src/test/regress/expected/geometry.out
+++ b/src/test/regress/expected/geometry.out
@@ -278,6 +278,40 @@ SELECT '' AS twenty, b.f1 / p.f1 AS rotation
         | (0.3,0),(0.3,0)
 (20 rows)
 
+SELECT f1::box
+	FROM POINT_TBL;
+          f1           
+-----------------------
+ (0,0),(0,0)
+ (-10,0),(-10,0)
+ (-3,4),(-3,4)
+ (5.1,34.5),(5.1,34.5)
+ (-5,-12),(-5,-12)
+ (10,10),(10,10)
+(6 rows)
+
+SELECT bound_box(a.f1, b.f1)
+	FROM BOX_TBL a, BOX_TBL b;
+      bound_box      
+---------------------
+ (2,2),(0,0)
+ (3,3),(0,0)
+ (2.5,3.5),(0,0)
+ (3,3),(0,0)
+ (3,3),(0,0)
+ (3,3),(1,1)
+ (3,3.5),(1,1)
+ (3,3),(1,1)
+ (2.5,3.5),(0,0)
+ (3,3.5),(1,1)
+ (2.5,3.5),(2.5,2.5)
+ (3,3.5),(2.5,2.5)
+ (3,3),(0,0)
+ (3,3),(1,1)
+ (3,3.5),(2.5,2.5)
+ (3,3),(3,3)
+(16 rows)
+
 --
 -- Paths
 --
diff --git a/src/test/regress/expected/geometry_1.out b/src/test/regress/expected/geometry_1.out
index 65037dc8b03..fad246c2b93 100644
--- a/src/test/regress/expected/geometry_1.out
+++ b/src/test/regress/expected/geometry_1.out
@@ -278,6 +278,40 @@ SELECT '' AS twenty, b.f1 / p.f1 AS rotation
         | (0.3,0),(0.3,0)
 (20 rows)
 
+SELECT f1::box
+	FROM POINT_TBL;
+          f1           
+-----------------------
+ (0,0),(0,0)
+ (-10,0),(-10,0)
+ (-3,4),(-3,4)
+ (5.1,34.5),(5.1,34.5)
+ (-5,-12),(-5,-12)
+ (10,10),(10,10)
+(6 rows)
+
+SELECT bound_box(a.f1, b.f1)
+	FROM BOX_TBL a, BOX_TBL b;
+      bound_box      
+---------------------
+ (2,2),(0,0)
+ (3,3),(0,0)
+ (2.5,3.5),(0,0)
+ (3,3),(0,0)
+ (3,3),(0,0)
+ (3,3),(1,1)
+ (3,3.5),(1,1)
+ (3,3),(1,1)
+ (2.5,3.5),(0,0)
+ (3,3.5),(1,1)
+ (2.5,3.5),(2.5,2.5)
+ (3,3.5),(2.5,2.5)
+ (3,3),(0,0)
+ (3,3),(1,1)
+ (3,3.5),(2.5,2.5)
+ (3,3),(3,3)
+(16 rows)
+
 --
 -- Paths
 --
diff --git a/src/test/regress/expected/geometry_2.out b/src/test/regress/expected/geometry_2.out
index 1878a37b33c..c938e66418a 100644
--- a/src/test/regress/expected/geometry_2.out
+++ b/src/test/regress/expected/geometry_2.out
@@ -278,6 +278,40 @@ SELECT '' AS twenty, b.f1 / p.f1 AS rotation
         | (0.3,0),(0.3,0)
 (20 rows)
 
+SELECT f1::box
+	FROM POINT_TBL;
+          f1           
+-----------------------
+ (0,0),(0,0)
+ (-10,0),(-10,0)
+ (-3,4),(-3,4)
+ (5.1,34.5),(5.1,34.5)
+ (-5,-12),(-5,-12)
+ (10,10),(10,10)
+(6 rows)
+
+SELECT bound_box(a.f1, b.f1)
+	FROM BOX_TBL a, BOX_TBL b;
+      bound_box      
+---------------------
+ (2,2),(0,0)
+ (3,3),(0,0)
+ (2.5,3.5),(0,0)
+ (3,3),(0,0)
+ (3,3),(0,0)
+ (3,3),(1,1)
+ (3,3.5),(1,1)
+ (3,3),(1,1)
+ (2.5,3.5),(0,0)
+ (3,3.5),(1,1)
+ (2.5,3.5),(2.5,2.5)
+ (3,3.5),(2.5,2.5)
+ (3,3),(0,0)
+ (3,3),(1,1)
+ (3,3.5),(2.5,2.5)
+ (3,3),(3,3)
+(16 rows)
+
 --
 -- Paths
 --
diff --git a/src/test/regress/expected/inet.out b/src/test/regress/expected/inet.out
index d25e5e42a79..9447e03ab56 100644
--- a/src/test/regress/expected/inet.out
+++ b/src/test/regress/expected/inet.out
@@ -614,3 +614,31 @@ SELECT '127::1'::inet - '127::2'::inet;
        -1
 (1 row)
 
+-- insert one more row with addressed from different families
+INSERT INTO INET_TBL (c, i) VALUES ('10', '10::/8');
+-- now, this one should fail
+SELECT inet_merge(c, i) FROM INET_TBL;
+ERROR:  cannot merge addresses from different families
+-- fix it by inet_same_family() condition
+SELECT inet_merge(c, i) FROM INET_TBL WHERE inet_same_family(c, i);
+   inet_merge    
+-----------------
+ 192.168.1.0/24
+ 192.168.1.0/24
+ 192.168.1.0/24
+ 192.168.1.0/24
+ 192.168.1.0/24
+ 192.168.1.0/24
+ 10.0.0.0/8
+ 10.0.0.0/8
+ 10.1.2.3/32
+ 10.1.2.0/24
+ 10.1.0.0/16
+ 10.0.0.0/8
+ 10.0.0.0/7
+ 8.0.0.0/6
+ 10:23::/64
+ 10:23::8000/113
+ ::/24
+(17 rows)
+
diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out
index 8654e031dae..23cdbc3902b 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -480,8 +480,26 @@ select numrange(1.0, 2.0) + numrange(1.5, 3.0);
  [1.0,3.0)
 (1 row)
 
-select numrange(1.0, 2.0) + numrange(2.5, 3.0);
+select numrange(1.0, 2.0) + numrange(2.5, 3.0); -- should fail
 ERROR:  result of range union would not be contiguous
+select range_merge(numrange(1.0, 2.0), numrange(2.0, 3.0));
+ range_merge 
+-------------
+ [1.0,3.0)
+(1 row)
+
+select range_merge(numrange(1.0, 2.0), numrange(1.5, 3.0));
+ range_merge 
+-------------
+ [1.0,3.0)
+(1 row)
+
+select range_merge(numrange(1.0, 2.0), numrange(2.5, 3.0)); -- shouldn't fail
+ range_merge 
+-------------
+ [1.0,3.0)
+(1 row)
+
 select numrange(1.0, 2.0) * numrange(2.0, 3.0);
  ?column? 
 ----------
diff --git a/src/test/regress/sql/geometry.sql b/src/test/regress/sql/geometry.sql
index af7f8a51cc7..1429ee772a3 100644
--- a/src/test/regress/sql/geometry.sql
+++ b/src/test/regress/sql/geometry.sql
@@ -79,6 +79,12 @@ SELECT '' AS twenty, b.f1 / p.f1 AS rotation
    FROM BOX_TBL b, POINT_TBL p
    WHERE (p.f1 <-> point '(0,0)') >= 1;
 
+SELECT f1::box
+	FROM POINT_TBL;
+
+SELECT bound_box(a.f1, b.f1)
+	FROM BOX_TBL a, BOX_TBL b;
+
 --
 -- Paths
 --
diff --git a/src/test/regress/sql/inet.sql b/src/test/regress/sql/inet.sql
index 2034d3e86f6..007741e9359 100644
--- a/src/test/regress/sql/inet.sql
+++ b/src/test/regress/sql/inet.sql
@@ -116,3 +116,10 @@ SELECT '127::1'::inet - '126::2'::inet;
 -- but not these
 SELECT '127::1'::inet + 10000000000;
 SELECT '127::1'::inet - '127::2'::inet;
+
+-- insert one more row with addressed from different families
+INSERT INTO INET_TBL (c, i) VALUES ('10', '10::/8');
+-- now, this one should fail
+SELECT inet_merge(c, i) FROM INET_TBL;
+-- fix it by inet_same_family() condition
+SELECT inet_merge(c, i) FROM INET_TBL WHERE inet_same_family(c, i);
diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql
index af1335264dc..6d2696f7bf2 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -108,7 +108,11 @@ select numrange(1.1, 2.2) < numrange(1.1, 1.2);
 
 select numrange(1.0, 2.0) + numrange(2.0, 3.0);
 select numrange(1.0, 2.0) + numrange(1.5, 3.0);
-select numrange(1.0, 2.0) + numrange(2.5, 3.0);
+select numrange(1.0, 2.0) + numrange(2.5, 3.0); -- should fail
+
+select range_merge(numrange(1.0, 2.0), numrange(2.0, 3.0));
+select range_merge(numrange(1.0, 2.0), numrange(1.5, 3.0));
+select range_merge(numrange(1.0, 2.0), numrange(2.5, 3.0)); -- shouldn't fail
 
 select numrange(1.0, 2.0) * numrange(2.0, 3.0);
 select numrange(1.0, 2.0) * numrange(1.5, 3.0);