Hi,
On Fri, Oct 30, 2020 at 04:01:27PM +0000, Georgios Kokolatos wrote:
Hi,
thank you for your contribution.
I did notice that the cfbot [1] is failing for this patch.
Please try to address the issues if you can for the upcoming commitfest.
I took a look at the patch today - the regression failure was trivial,
the expected output for one query was added to the wrong place, a couple
lines off the proper place. Attached is an updated version of the patch,
fixing that.
I also reviewed the code - it seems pretty clean and in line with the
surrounding code in rangetypes.c. Good job Esteban! I'll do a bit more
review next week, and I'll see if I can get it committed.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 414aefb911308b39ffefbd2190db66f2ee24c26c Mon Sep 17 00:00:00 2001
From: Tomas Vondra <to...@2ndquadrant.com>
Date: Fri, 30 Oct 2020 22:44:57 +0100
Subject: [PATCH] range-ext
---
doc/src/sgml/func.sgml | 140 ++++++++++
src/backend/utils/adt/rangetypes.c | 318 ++++++++++++++++++++++-
src/include/catalog/pg_operator.dat | 54 ++++
src/include/catalog/pg_proc.dat | 31 +++
src/include/utils/rangetypes.h | 7 +
src/test/regress/expected/rangetypes.out | 246 ++++++++++++++++++
src/test/regress/sql/rangetypes.sql | 42 +++
7 files changed, 837 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index d8eee3a826..3498716c7e 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18016,6 +18016,34 @@ SELECT NULLIF(value, '(none)') ...
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>anyrange</type> <literal><<</literal>
<type>anyelement</type>
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Is the range strictly left of the element?
+ </para>
+ <para>
+ <literal>int8range(1,10) << 100::int8</literal>
+ <returnvalue>t</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>anyelement</type> <literal><<</literal>
<type>anyrange</type>
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Is the element strictly left of the range?
+ </para>
+ <para>
+ <literal>10::int8 << int8range(100,110)</literal>
+ <returnvalue>t</returnvalue>
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<type>anyrange</type> <literal>>></literal> <type>anyrange</type>
@@ -18030,6 +18058,34 @@ SELECT NULLIF(value, '(none)') ...
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>anyrange</type> <literal>>></literal>
<type>anyelement</type>
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Is the range strictly right of the element?
+ </para>
+ <para>
+ <literal>int8range(50,60) >> 20::int8</literal>
+ <returnvalue>t</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>anyelement</type> <literal>>></literal>
<type>anyrange</type>
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Is the element strictly right of the element?
+ </para>
+ <para>
+ <literal>60::int8 >> int8range(20,30)</literal>
+ <returnvalue>t</returnvalue>
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<type>anyrange</type> <literal>&<</literal>
<type>anyrange</type>
@@ -18044,6 +18100,34 @@ SELECT NULLIF(value, '(none)') ...
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>anyrange</type> <literal>&<</literal>
<type>anyelement</type>
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Does the range not extend to the right of the element?
+ </para>
+ <para>
+ <literal>int8range(1,20) &< 20::int8</literal>
+ <returnvalue>t</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>anyelement</type> <literal>&<</literal>
<type>anyrange</type>
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Does the element not extend to the right of the range?
+ </para>
+ <para>
+ <literal>20::int8 &< int8range(18,20)</literal>
+ <returnvalue>t</returnvalue>
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<type>anyrange</type> <literal>&></literal>
<type>anyrange</type>
@@ -18058,6 +18142,34 @@ SELECT NULLIF(value, '(none)') ...
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>anyrange</type> <literal>&></literal>
<type>anyelement</type>
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Does the range not extend to the left of the element?
+ </para>
+ <para>
+ <literal>int8range(7,20) &> 5::int8</literal>
+ <returnvalue>t</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>anyelement</type> <literal>&></literal>
<type>anyrange</type>
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Does the element not extend to the left of the range?
+ </para>
+ <para>
+ <literal>7::int8 &> int8range(5,10)</literal>
+ <returnvalue>t</returnvalue>
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<type>anyrange</type> <literal>-|-</literal> <type>anyrange</type>
@@ -18072,6 +18184,34 @@ SELECT NULLIF(value, '(none)') ...
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>anyrange</type> <literal>-|-</literal> <type>anyelement</type>
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Is the range adjacent to the element?
+ </para>
+ <para>
+ <literal>numrange(1.1,2.2) -|- 2.2</literal>
+ <returnvalue>t</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>anyelement</type> <literal>-|-</literal> <type>anyrange</type>
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Is the element adjacent to the range?
+ </para>
+ <para>
+ <literal>2.2 -|- numrange(2.2,3.3,'(]')</literal>
+ <returnvalue>t</returnvalue>
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<type>anyrange</type> <literal>+</literal> <type>anyrange</type>
diff --git a/src/backend/utils/adt/rangetypes.c
b/src/backend/utils/adt/rangetypes.c
index 01ad8bc240..954f3d2a46 100644
--- a/src/backend/utils/adt/rangetypes.c
+++ b/src/backend/utils/adt/rangetypes.c
@@ -536,6 +536,323 @@ range_contains_elem(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(range_contains_elem_internal(typcache, r, val));
}
+/* strictly left of element? (internal version) */
+bool
+range_before_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val)
+{
+ RangeBound lower,
+ upper;
+ bool empty;
+ int32 cmp;
+
+ range_deserialize(typcache, r, &lower, &upper, &empty);
+
+ /* An empty range is neither left nor right any other range */
+ if (empty)
+ return false;
+
+ if (!upper.infinite)
+ {
+ cmp =
DatumGetInt32(FunctionCall2Coll(&typcache->rng_cmp_proc_finfo,
+
typcache->rng_collation,
+
upper.val, val));
+ if (cmp < 0 ||
+ (cmp == 0 && !upper.inclusive))
+ return true;
+ }
+
+ return false;
+}
+
+/* strictly left of element? */
+Datum
+range_before_elem(PG_FUNCTION_ARGS)
+{
+ RangeType *r = PG_GETARG_RANGE_P(0);
+ Datum val = PG_GETARG_DATUM(1);
+ TypeCacheEntry *typcache;
+
+ typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r));
+
+ PG_RETURN_BOOL(range_before_elem_internal(typcache, r, val));
+}
+
+/* does not extend to right of element? (internal version) */
+bool
+range_overleft_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val)
+{
+ RangeBound lower,
+ upper;
+ bool empty;
+
+ range_deserialize(typcache, r, &lower, &upper, &empty);
+
+ /* An empty range is neither left nor right any element */
+ if (empty)
+ return false;
+
+ if (!upper.infinite)
+ {
+ if
(DatumGetInt32(FunctionCall2Coll(&typcache->rng_cmp_proc_finfo,
+
typcache->rng_collation,
+
upper.val, val)) <= 0)
+ return true;
+ }
+
+ return false;
+}
+
+/* does not extend to right of element? */
+Datum
+range_overleft_elem(PG_FUNCTION_ARGS)
+{
+ RangeType *r = PG_GETARG_RANGE_P(0);
+ Datum val = PG_GETARG_DATUM(1);
+ TypeCacheEntry *typcache;
+
+ typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r));
+
+ PG_RETURN_BOOL(range_overleft_elem_internal(typcache, r, val));
+}
+
+/* strictly right of element? (internal version) */
+bool
+range_after_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val)
+{
+ RangeBound lower,
+ upper;
+ bool empty;
+ int32 cmp;
+
+ range_deserialize(typcache, r, &lower, &upper, &empty);
+
+ /* An empty range is neither left nor right any other range */
+ if (empty)
+ return false;
+
+ if (!lower.infinite)
+ {
+ cmp =
DatumGetInt32(FunctionCall2Coll(&typcache->rng_cmp_proc_finfo,
+
typcache->rng_collation,
+
lower.val, val));
+ if (cmp > 0 ||
+ (cmp == 0 && !lower.inclusive))
+ return true;
+ }
+
+ return false;
+}
+
+/* strictly right of element? */
+Datum
+range_after_elem(PG_FUNCTION_ARGS)
+{
+ RangeType *r = PG_GETARG_RANGE_P(0);
+ Datum val = PG_GETARG_DATUM(1);
+ TypeCacheEntry *typcache;
+
+ typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r));
+
+ PG_RETURN_BOOL(range_after_elem_internal(typcache, r, val));
+}
+
+/* does not extend to left of element? (internal version) */
+bool
+range_overright_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum
val)
+{
+ RangeBound lower,
+ upper;
+ bool empty;
+
+ range_deserialize(typcache, r, &lower, &upper, &empty);
+
+ /* An empty range is neither left nor right any element */
+ if (empty)
+ return false;
+
+ if (!lower.infinite)
+ {
+ if
(DatumGetInt32(FunctionCall2Coll(&typcache->rng_cmp_proc_finfo,
+
typcache->rng_collation,
+
lower.val, val)) >= 0)
+ return true;
+ }
+
+ return false;
+}
+
+/* does not extend to left of element? */
+Datum
+range_overright_elem(PG_FUNCTION_ARGS)
+{
+ RangeType *r = PG_GETARG_RANGE_P(0);
+ Datum val = PG_GETARG_DATUM(1);
+ TypeCacheEntry *typcache;
+
+ typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r));
+
+ PG_RETURN_BOOL(range_overright_elem_internal(typcache, r, val));
+}
+
+/* adjacent to element (but not overlapping)? (internal version) */
+bool
+range_adjacent_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val)
+{
+ RangeBound lower,
+ upper;
+ bool empty;
+ RangeBound elembound;
+ bool isadj;
+
+ range_deserialize(typcache, r, &lower, &upper, &empty);
+
+ /* An empty range is not adjacent to any element */
+ if (empty)
+ return false;
+
+ /*
+ * A range A..B and a value V are adjacent if and only if
+ * B is adjacent to V, or V is adjacent to A.
+ */
+ elembound.val = val;
+ elembound.infinite = false;
+ elembound.inclusive = true;
+ elembound.lower = true;
+ isadj = bounds_adjacent(typcache, upper, elembound);
+ elembound.lower = false;
+ return (isadj || bounds_adjacent(typcache, elembound, lower));
+}
+
+/* adjacent to element (but not overlapping)? */
+Datum
+range_adjacent_elem(PG_FUNCTION_ARGS)
+{
+ RangeType *r = PG_GETARG_RANGE_P(0);
+ Datum val = PG_GETARG_DATUM(1);
+ TypeCacheEntry *typcache;
+
+ typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r));
+
+ PG_RETURN_BOOL(range_adjacent_elem_internal(typcache, r, val));
+}
+
+/******************************************************************************/
+
+/* element strictly left of? */
+Datum
+elem_before_range(PG_FUNCTION_ARGS)
+{
+ Datum val = PG_GETARG_DATUM(0);
+ RangeType *r = PG_GETARG_RANGE_P(1);
+ TypeCacheEntry *typcache;
+
+ typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r));
+
+ PG_RETURN_BOOL(range_after_elem_internal(typcache, r, val));
+}
+
+/* element does not extend to right of? (internal version) */
+bool
+elem_overleft_range_internal(TypeCacheEntry *typcache, Datum val, RangeType *r)
+{
+ RangeBound lower,
+ upper;
+ bool empty;
+
+ range_deserialize(typcache, r, &lower, &upper, &empty);
+
+ /* An empty range is neither left nor right any element */
+ if (empty)
+ return false;
+
+ if (!upper.infinite)
+ {
+ if
(DatumGetInt32(FunctionCall2Coll(&typcache->rng_cmp_proc_finfo,
+
typcache->rng_collation,
+
val, upper.val)) <= 0)
+ return true;
+ }
+
+ return false;
+}
+
+/* element does not extend to right of? */
+Datum
+elem_overleft_range(PG_FUNCTION_ARGS)
+{
+ Datum val = PG_GETARG_DATUM(0);
+ RangeType *r = PG_GETARG_RANGE_P(1);
+ TypeCacheEntry *typcache;
+
+ typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r));
+
+ PG_RETURN_BOOL(elem_overleft_range_internal(typcache, val, r));
+}
+
+/* element strictly right of? */
+Datum
+elem_after_range(PG_FUNCTION_ARGS)
+{
+ Datum val = PG_GETARG_DATUM(0);
+ RangeType *r = PG_GETARG_RANGE_P(1);
+ TypeCacheEntry *typcache;
+
+ typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r));
+
+ PG_RETURN_BOOL(range_before_elem_internal(typcache, r, val));
+}
+
+/* element does not extend to left of? (internal version) */
+bool
+elem_overright_range_internal(TypeCacheEntry *typcache, Datum val, RangeType
*r)
+{
+ RangeBound lower,
+ upper;
+ bool empty;
+
+ range_deserialize(typcache, r, &lower, &upper, &empty);
+
+ /* An empty range is neither left nor right any element */
+ if (empty)
+ return false;
+
+ if (!lower.infinite)
+ {
+ if
(DatumGetInt32(FunctionCall2Coll(&typcache->rng_cmp_proc_finfo,
+
typcache->rng_collation,
+
val, lower.val)) >= 0)
+ return true;
+ }
+
+ return false;
+}
+
+/* element does not extend the left of? */
+Datum
+elem_overright_range(PG_FUNCTION_ARGS)
+{
+ Datum val = PG_GETARG_DATUM(0);
+ RangeType *r = PG_GETARG_RANGE_P(1);
+ TypeCacheEntry *typcache;
+
+ typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r));
+
+ PG_RETURN_BOOL(elem_overright_range_internal(typcache, val, r));
+}
+
+/* element adjacent to? */
+Datum
+elem_adjacent_range(PG_FUNCTION_ARGS)
+{
+ Datum val = PG_GETARG_DATUM(0);
+ RangeType *r = PG_GETARG_RANGE_P(1);
+ TypeCacheEntry *typcache;
+
+ typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r));
+
+ PG_RETURN_BOOL(range_adjacent_elem_internal(typcache, r, val));
+}
+
/* contained by? */
Datum
elem_contained_by_range(PG_FUNCTION_ARGS)
@@ -549,7 +866,6 @@ elem_contained_by_range(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(range_contains_elem_internal(typcache, r, val));
}
-
/* range, range -> bool functions */
/* equality (internal version) */
diff --git a/src/include/catalog/pg_operator.dat
b/src/include/catalog/pg_operator.dat
index 7cc812adda..ddad6c4745 100644
--- a/src/include/catalog/pg_operator.dat
+++ b/src/include/catalog/pg_operator.dat
@@ -3332,5 +3332,59 @@
oprname => '@@', oprleft => 'jsonb', oprright => 'jsonpath',
oprresult => 'bool', oprcode => 'jsonb_path_match_opr(jsonb,jsonpath)',
oprrest => 'matchingsel', oprjoin => 'matchingjoinsel' },
+{ oid => '4231', oid_symbol => 'OID_RANGE_ADJACENT_ELEM_OP',
+ descr => 'range is adjacent to element',
+ oprname => '-|-', oprleft => 'anyrange', oprright => 'anyelement',
+ oprresult => 'bool', oprcom => '-|-(anyelement,anyrange)',
+ oprcode => 'range_adjacent_elem', oprrest => 'contsel', oprjoin =>
'contjoinsel' },
+{ oid => '4232', oid_symbol => 'OID_ELEM_ADJACENT_RANGE_OP',
+ descr => 'element is adjacent to range',
+ oprname => '-|-', oprleft => 'anyelement', oprright => 'anyrange',
+ oprresult => 'bool', oprcom => '-|-(anyrange,anyelement)',
+ oprcode => 'elem_adjacent_range', oprrest => 'contsel', oprjoin =>
'contjoinsel' },
+{ oid => '4233', oid_symbol => 'OID_RANGE_LEFT_ELEM_OP',
+ descr => 'is left of element',
+ oprname => '<<', oprleft => 'anyrange', oprright => 'anyelement',
+ oprresult => 'bool', oprcom => '>>(anyelement,anyrange)',
+ oprcode => 'range_before_elem', oprrest => 'rangesel',
+ oprjoin => 'scalarltjoinsel' },
+{ oid => '4234', oid_symbol => 'OID_ELEM_LEFT_RANGE_OP',
+ descr => 'element is left of range',
+ oprname => '<<', oprleft => 'anyelement', oprright => 'anyrange',
+ oprresult => 'bool', oprcom => '>>(anyrange,anyelement)',
+ oprcode => 'elem_before_range', oprrest => 'rangesel',
+ oprjoin => 'scalarltjoinsel' },
+{ oid => '4235', oid_symbol => 'OID_RANGE_RIGHT_ELEM_OP',
+ descr => 'is right of element',
+ oprname => '>>', oprleft => 'anyrange', oprright => 'anyelement',
+ oprresult => 'bool', oprcom => '<<(anyelement,anyrange)',
+ oprcode => 'range_after_elem', oprrest => 'rangesel',
+ oprjoin => 'scalargtjoinsel' },
+{ oid => '4236', oid_symbol => 'OID_ELEM_RIGHT_RANGE_OP',
+ descr => 'element is right of range',
+ oprname => '>>', oprleft => 'anyelement', oprright => 'anyrange',
+ oprresult => 'bool', oprcom => '<<(anyrange,anyelement)',
+ oprcode => 'elem_after_range', oprrest => 'rangesel',
+ oprjoin => 'scalargtjoinsel' },
+{ oid => '4237', oid_symbol => 'OID_RANGE_OVERLAPS_LEFT_ELEM_OP',
+ descr => 'overlaps or is left of element',
+ oprname => '&<', oprleft => 'anyrange', oprright => 'anyelement',
+ oprresult => 'bool', oprcode => 'range_overleft_elem', oprrest => 'rangesel',
+ oprjoin => 'scalarltjoinsel' },
+{ oid => '4238', oid_symbol => 'OID_ELEM_OVERLAPS_LEFT_RANGE_OP',
+ descr => 'element overlaps or is left of range',
+ oprname => '&<', oprleft => 'anyelement', oprright => 'anyrange',
+ oprresult => 'bool', oprcode => 'elem_overleft_range', oprrest => 'rangesel',
+ oprjoin => 'scalarltjoinsel' },
+{ oid => '4239', oid_symbol => 'OID_RANGE_OVERLAPS_RIGHT_ELEM_OP',
+ descr => 'overlaps or is right of element',
+ oprname => '&>', oprleft => 'anyrange', oprright => 'anyelement',
+ oprresult => 'bool', oprcode => 'range_overright_elem', oprrest =>
'rangesel',
+ oprjoin => 'scalarltjoinsel' },
+{ oid => '4240', oid_symbol => 'OID_ELEM_OVERLAPS_RIGHT_RANGE_OP',
+ descr => 'element overlaps or is right of range',
+ oprname => '&>', oprleft => 'anyelement', oprright => 'anyrange',
+ oprresult => 'bool', oprcode => 'elem_overright_range', oprrest =>
'rangesel',
+ oprjoin => 'scalarltjoinsel' },
]
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d9770bbadd..bf985b61f6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9971,6 +9971,37 @@
proname => 'int8range', proisstrict => 'f', prorettype => 'int8range',
proargtypes => 'int8 int8 text', prosrc => 'range_constructor3' },
+{ oid => '4241',
+ proname => 'range_adjacent_elem', prorettype => 'bool',
+ proargtypes => 'anyrange anyelement', prosrc => 'range_adjacent_elem' },
+{ oid => '4242',
+ proname => 'elem_adjacent_range', prorettype => 'bool',
+ proargtypes => 'anyelement anyrange', prosrc => 'elem_adjacent_range' },
+{ oid => '4243',
+ proname => 'range_before_elem', prorettype => 'bool',
+ proargtypes => 'anyrange anyelement', prosrc => 'range_before_elem' },
+{ oid => '4244',
+ proname => 'elem_before_range', prorettype => 'bool',
+ proargtypes => 'anyelement anyrange', prosrc => 'elem_before_range' },
+{ oid => '4245',
+ proname => 'range_after_elem', prorettype => 'bool',
+ proargtypes => 'anyrange anyelement', prosrc => 'range_after_elem' },
+{ oid => '4246',
+ proname => 'elem_after_range', prorettype => 'bool',
+ proargtypes => 'anyelement anyrange', prosrc => 'elem_after_range' },
+{ oid => '4247',
+ proname => 'range_overleft_elem', prorettype => 'bool',
+ proargtypes => 'anyrange anyelement', prosrc => 'range_overleft_elem' },
+{ oid => '4248',
+ proname => 'elem_overleft_range', prorettype => 'bool',
+ proargtypes => 'anyelement anyrange', prosrc => 'elem_overleft_range' },
+{ oid => '4249',
+ proname => 'range_overright_elem', prorettype => 'bool',
+ proargtypes => 'anyrange anyelement', prosrc => 'range_overright_elem' },
+{ oid => '4250',
+ proname => 'elem_overright_range', prorettype => 'bool',
+ proargtypes => 'anyelement anyrange', prosrc => 'elem_overright_range' },
+
# date, time, timestamp constructors
{ oid => '3846', descr => 'construct date',
proname => 'make_date', prorettype => 'date', proargtypes => 'int4 int4
int4',
diff --git a/src/include/utils/rangetypes.h b/src/include/utils/rangetypes.h
index b77c41cf1b..262d5cc8f8 100644
--- a/src/include/utils/rangetypes.h
+++ b/src/include/utils/rangetypes.h
@@ -93,6 +93,13 @@ typedef struct
*/
extern bool range_contains_elem_internal(TypeCacheEntry *typcache, const
RangeType *r, Datum val);
+extern bool range_before_elem_internal(TypeCacheEntry *typcache, RangeType *r,
Datum val);
+extern bool range_overleft_elem_internal(TypeCacheEntry *typcache, RangeType
*r, Datum val);
+extern bool range_after_elem_internal(TypeCacheEntry *typcache, RangeType *r,
Datum val);
+extern bool range_overright_elem_internal(TypeCacheEntry *typcache, RangeType
*r, Datum val);
+extern bool range_adjacent_elem_internal(TypeCacheEntry *typcache, RangeType
*r, Datum val);
+extern bool elem_overleft_range_internal(TypeCacheEntry *typcache, Datum val,
RangeType *r);
+extern bool elem_overright_range_internal(TypeCacheEntry *typcache, Datum val,
RangeType *r);
/* internal versions of the above */
extern bool range_eq_internal(TypeCacheEntry *typcache, const RangeType *r1,
diff --git a/src/test/regress/expected/rangetypes.out
b/src/test/regress/expected/rangetypes.out
index c421f5394f..5b904b4f9e 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -378,6 +378,18 @@ select range_adjacent(numrange(2.0, 3.0, '(]'),
numrange(1.0, 2.0, '(]'));
t
(1 row)
+select numrange(1.0, 2.0) -|- 2.0;
+ ?column?
+----------
+ t
+(1 row)
+
+select 2.0 -|- numrange(2.0, 3.0,'()');
+ ?column?
+----------
+ t
+(1 row)
+
select numrange(1.1, 3.3) <@ numrange(0.1,10.1);
?column?
----------
@@ -432,6 +444,18 @@ select numrange(1.0, 2.0) << numrange(3.0, 4.0);
t
(1 row)
+select numrange(1.0, 2.0) << 3.0;
+ ?column?
+----------
+ t
+(1 row)
+
+select 2.0 << numrange(3.0, 4.0);
+ ?column?
+----------
+ t
+(1 row)
+
select numrange(1.0, 3.0,'[]') << numrange(3.0, 4.0,'[]');
?column?
----------
@@ -450,12 +474,54 @@ select numrange(1.0, 2.0) >> numrange(3.0, 4.0);
f
(1 row)
+select numrange(1.0, 2.0) >> 3.0;
+ ?column?
+----------
+ f
+(1 row)
+
+select 2.0 >> numrange(3.0, 4.0);
+ ?column?
+----------
+ f
+(1 row)
+
select numrange(3.0, 70.0) &< numrange(6.6, 100.0);
?column?
----------
t
(1 row)
+select numrange(3.0, 70.0) &< 6.6;
+ ?column?
+----------
+ f
+(1 row)
+
+select 70.0 &< numrange(6.6, 100.0);
+ ?column?
+----------
+ t
+(1 row)
+
+select numrange(3.0, 70.0) &> numrange(6.6, 100.0);
+ ?column?
+----------
+ f
+(1 row)
+
+select numrange(3.0, 70.0) &> 6.6;
+ ?column?
+----------
+ f
+(1 row)
+
+select 70.0 &> numrange(6.6, 100.0);
+ ?column?
+----------
+ t
+(1 row)
+
select numrange(1.1, 2.2) < numrange(1.0, 200.2);
?column?
----------
@@ -819,30 +885,60 @@ select count(*) from test_range_gist where ir <<
int4range(100,500);
189
(1 row)
+select count(*) from test_range_gist where ir << 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_gist where ir >> int4range(100,500);
count
-------
3554
(1 row)
+select count(*) from test_range_gist where ir >> 100;
+ count
+-------
+ 4791
+(1 row)
+
select count(*) from test_range_gist where ir &< int4range(100,500);
count
-------
1029
(1 row)
+select count(*) from test_range_gist where ir &< 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_gist where ir &> int4range(100,500);
count
-------
4794
(1 row)
+select count(*) from test_range_gist where ir &> 100;
+ count
+-------
+ 4794
+(1 row)
+
select count(*) from test_range_gist where ir -|- int4range(100,500);
count
-------
5
(1 row)
+select count(*) from test_range_gist where ir -|- 100;
+ count
+-------
+ 6
+(1 row)
+
-- now check same queries using index
SET enable_seqscan = f;
SET enable_indexscan = t;
@@ -889,30 +985,60 @@ select count(*) from test_range_gist where ir <<
int4range(100,500);
189
(1 row)
+select count(*) from test_range_gist where ir << 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_gist where ir >> int4range(100,500);
count
-------
3554
(1 row)
+select count(*) from test_range_gist where ir >> 100;
+ count
+-------
+ 4791
+(1 row)
+
select count(*) from test_range_gist where ir &< int4range(100,500);
count
-------
1029
(1 row)
+select count(*) from test_range_gist where ir &< 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_gist where ir &> int4range(100,500);
count
-------
4794
(1 row)
+select count(*) from test_range_gist where ir &> 100;
+ count
+-------
+ 4794
+(1 row)
+
select count(*) from test_range_gist where ir -|- int4range(100,500);
count
-------
5
(1 row)
+select count(*) from test_range_gist where ir -|- 100;
+ count
+-------
+ 6
+(1 row)
+
-- now check same queries using a bulk-loaded index
drop index test_range_gist_idx;
create index test_range_gist_idx on test_range_gist using gist (ir);
@@ -958,30 +1084,60 @@ select count(*) from test_range_gist where ir <<
int4range(100,500);
189
(1 row)
+select count(*) from test_range_gist where ir << 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_gist where ir >> int4range(100,500);
count
-------
3554
(1 row)
+select count(*) from test_range_gist where ir >> 100;
+ count
+-------
+ 4791
+(1 row)
+
select count(*) from test_range_gist where ir &< int4range(100,500);
count
-------
1029
(1 row)
+select count(*) from test_range_gist where ir &< 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_gist where ir &> int4range(100,500);
count
-------
4794
(1 row)
+select count(*) from test_range_gist where ir &> 100;
+ count
+-------
+ 4794
+(1 row)
+
select count(*) from test_range_gist where ir -|- int4range(100,500);
count
-------
5
(1 row)
+select count(*) from test_range_gist where ir -|- 100;
+ count
+-------
+ 6
+(1 row)
+
-- test SP-GiST index that's been built incrementally
create table test_range_spgist(ir int4range);
create index test_range_spgist_idx on test_range_spgist using spgist (ir);
@@ -1038,30 +1194,60 @@ select count(*) from test_range_spgist where ir <<
int4range(100,500);
189
(1 row)
+select count(*) from test_range_spgist where ir << 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_spgist where ir >> int4range(100,500);
count
-------
3554
(1 row)
+select count(*) from test_range_spgist where ir >> 100;
+ count
+-------
+ 4791
+(1 row)
+
select count(*) from test_range_spgist where ir &< int4range(100,500);
count
-------
1029
(1 row)
+select count(*) from test_range_spgist where ir &< 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_spgist where ir &> int4range(100,500);
count
-------
4794
(1 row)
+select count(*) from test_range_spgist where ir &> 100;
+ count
+-------
+ 4794
+(1 row)
+
select count(*) from test_range_spgist where ir -|- int4range(100,500);
count
-------
5
(1 row)
+select count(*) from test_range_spgist where ir -|- 100;
+ count
+-------
+ 6
+(1 row)
+
-- now check same queries using index
SET enable_seqscan = f;
SET enable_indexscan = t;
@@ -1108,30 +1294,60 @@ select count(*) from test_range_spgist where ir <<
int4range(100,500);
189
(1 row)
+select count(*) from test_range_spgist where ir << 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_spgist where ir >> int4range(100,500);
count
-------
3554
(1 row)
+select count(*) from test_range_spgist where ir >> 100;
+ count
+-------
+ 4791
+(1 row)
+
select count(*) from test_range_spgist where ir &< int4range(100,500);
count
-------
1029
(1 row)
+select count(*) from test_range_spgist where ir &< 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_spgist where ir &> int4range(100,500);
count
-------
4794
(1 row)
+select count(*) from test_range_spgist where ir &> 100;
+ count
+-------
+ 4794
+(1 row)
+
select count(*) from test_range_spgist where ir -|- int4range(100,500);
count
-------
5
(1 row)
+select count(*) from test_range_spgist where ir -|- 100;
+ count
+-------
+ 6
+(1 row)
+
-- now check same queries using a bulk-loaded index
drop index test_range_spgist_idx;
create index test_range_spgist_idx on test_range_spgist using spgist (ir);
@@ -1177,30 +1393,60 @@ select count(*) from test_range_spgist where ir <<
int4range(100,500);
189
(1 row)
+select count(*) from test_range_spgist where ir << 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_spgist where ir >> int4range(100,500);
count
-------
3554
(1 row)
+select count(*) from test_range_spgist where ir >> 100;
+ count
+-------
+ 4791
+(1 row)
+
select count(*) from test_range_spgist where ir &< int4range(100,500);
count
-------
1029
(1 row)
+select count(*) from test_range_spgist where ir &< 100;
+ count
+-------
+ 189
+(1 row)
+
select count(*) from test_range_spgist where ir &> int4range(100,500);
count
-------
4794
(1 row)
+select count(*) from test_range_spgist where ir &> 100;
+ count
+-------
+ 4794
+(1 row)
+
select count(*) from test_range_spgist where ir -|- int4range(100,500);
count
-------
5
(1 row)
+select count(*) from test_range_spgist where ir -|- 100;
+ count
+-------
+ 6
+(1 row)
+
-- test index-only scans
explain (costs off)
select ir from test_range_spgist where ir -|- int4range(10,20) order by ir;
diff --git a/src/test/regress/sql/rangetypes.sql
b/src/test/regress/sql/rangetypes.sql
index 4048b1d185..75e70e9144 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -87,6 +87,9 @@ select numrange(2.0, 3.0, '[]') -|- numrange(3.0, 4.0, '()');
select numrange(1.0, 2.0) -|- numrange(2.0, 3.0,'[]');
select range_adjacent(numrange(2.0, 3.0, '(]'), numrange(1.0, 2.0, '(]'));
+select numrange(1.0, 2.0) -|- 2.0;
+select 2.0 -|- numrange(2.0, 3.0,'()');
+
select numrange(1.1, 3.3) <@ numrange(0.1,10.1);
select numrange(0.1, 10.1) <@ numrange(1.1,3.3);
@@ -98,10 +101,19 @@ select range_minus(numrange(10.1,12.2,'[]'),
numrange(0.0,120.2,'(]'));
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, 2.0) << 3.0;
+select 2.0 << numrange(3.0, 4.0);
select numrange(1.0, 3.0,'[]') << numrange(3.0, 4.0,'[]');
select numrange(1.0, 3.0,'()') << numrange(3.0, 4.0,'()');
select numrange(1.0, 2.0) >> numrange(3.0, 4.0);
+select numrange(1.0, 2.0) >> 3.0;
+select 2.0 >> numrange(3.0, 4.0);
select numrange(3.0, 70.0) &< numrange(6.6, 100.0);
+select numrange(3.0, 70.0) &< 6.6;
+select 70.0 &< numrange(6.6, 100.0);
+select numrange(3.0, 70.0) &> numrange(6.6, 100.0);
+select numrange(3.0, 70.0) &> 6.6;
+select 70.0 &> numrange(6.6, 100.0);
select numrange(1.1, 2.2) < numrange(1.0, 200.2);
select numrange(1.1, 2.2) < numrange(1.1, 1.2);
@@ -222,10 +234,15 @@ select count(*) from test_range_gist where ir @>
int4range(10,20);
select count(*) from test_range_gist where ir && int4range(10,20);
select count(*) from test_range_gist where ir <@ int4range(10,50);
select count(*) from test_range_gist where ir << int4range(100,500);
+select count(*) from test_range_gist where ir << 100;
select count(*) from test_range_gist where ir >> int4range(100,500);
+select count(*) from test_range_gist where ir >> 100;
select count(*) from test_range_gist where ir &< int4range(100,500);
+select count(*) from test_range_gist where ir &< 100;
select count(*) from test_range_gist where ir &> int4range(100,500);
+select count(*) from test_range_gist where ir &> 100;
select count(*) from test_range_gist where ir -|- int4range(100,500);
+select count(*) from test_range_gist where ir -|- 100;
-- now check same queries using index
SET enable_seqscan = f;
@@ -239,10 +256,15 @@ select count(*) from test_range_gist where ir @>
int4range(10,20);
select count(*) from test_range_gist where ir && int4range(10,20);
select count(*) from test_range_gist where ir <@ int4range(10,50);
select count(*) from test_range_gist where ir << int4range(100,500);
+select count(*) from test_range_gist where ir << 100;
select count(*) from test_range_gist where ir >> int4range(100,500);
+select count(*) from test_range_gist where ir >> 100;
select count(*) from test_range_gist where ir &< int4range(100,500);
+select count(*) from test_range_gist where ir &< 100;
select count(*) from test_range_gist where ir &> int4range(100,500);
+select count(*) from test_range_gist where ir &> 100;
select count(*) from test_range_gist where ir -|- int4range(100,500);
+select count(*) from test_range_gist where ir -|- 100;
-- now check same queries using a bulk-loaded index
drop index test_range_gist_idx;
@@ -255,10 +277,15 @@ select count(*) from test_range_gist where ir @>
int4range(10,20);
select count(*) from test_range_gist where ir && int4range(10,20);
select count(*) from test_range_gist where ir <@ int4range(10,50);
select count(*) from test_range_gist where ir << int4range(100,500);
+select count(*) from test_range_gist where ir << 100;
select count(*) from test_range_gist where ir >> int4range(100,500);
+select count(*) from test_range_gist where ir >> 100;
select count(*) from test_range_gist where ir &< int4range(100,500);
+select count(*) from test_range_gist where ir &< 100;
select count(*) from test_range_gist where ir &> int4range(100,500);
+select count(*) from test_range_gist where ir &> 100;
select count(*) from test_range_gist where ir -|- int4range(100,500);
+select count(*) from test_range_gist where ir -|- 100;
-- test SP-GiST index that's been built incrementally
create table test_range_spgist(ir int4range);
@@ -284,10 +311,15 @@ select count(*) from test_range_spgist where ir @>
int4range(10,20);
select count(*) from test_range_spgist where ir && int4range(10,20);
select count(*) from test_range_spgist where ir <@ int4range(10,50);
select count(*) from test_range_spgist where ir << int4range(100,500);
+select count(*) from test_range_spgist where ir << 100;
select count(*) from test_range_spgist where ir >> int4range(100,500);
+select count(*) from test_range_spgist where ir >> 100;
select count(*) from test_range_spgist where ir &< int4range(100,500);
+select count(*) from test_range_spgist where ir &< 100;
select count(*) from test_range_spgist where ir &> int4range(100,500);
+select count(*) from test_range_spgist where ir &> 100;
select count(*) from test_range_spgist where ir -|- int4range(100,500);
+select count(*) from test_range_spgist where ir -|- 100;
-- now check same queries using index
SET enable_seqscan = f;
@@ -301,10 +333,15 @@ select count(*) from test_range_spgist where ir @>
int4range(10,20);
select count(*) from test_range_spgist where ir && int4range(10,20);
select count(*) from test_range_spgist where ir <@ int4range(10,50);
select count(*) from test_range_spgist where ir << int4range(100,500);
+select count(*) from test_range_spgist where ir << 100;
select count(*) from test_range_spgist where ir >> int4range(100,500);
+select count(*) from test_range_spgist where ir >> 100;
select count(*) from test_range_spgist where ir &< int4range(100,500);
+select count(*) from test_range_spgist where ir &< 100;
select count(*) from test_range_spgist where ir &> int4range(100,500);
+select count(*) from test_range_spgist where ir &> 100;
select count(*) from test_range_spgist where ir -|- int4range(100,500);
+select count(*) from test_range_spgist where ir -|- 100;
-- now check same queries using a bulk-loaded index
drop index test_range_spgist_idx;
@@ -317,10 +354,15 @@ select count(*) from test_range_spgist where ir @>
int4range(10,20);
select count(*) from test_range_spgist where ir && int4range(10,20);
select count(*) from test_range_spgist where ir <@ int4range(10,50);
select count(*) from test_range_spgist where ir << int4range(100,500);
+select count(*) from test_range_spgist where ir << 100;
select count(*) from test_range_spgist where ir >> int4range(100,500);
+select count(*) from test_range_spgist where ir >> 100;
select count(*) from test_range_spgist where ir &< int4range(100,500);
+select count(*) from test_range_spgist where ir &< 100;
select count(*) from test_range_spgist where ir &> int4range(100,500);
+select count(*) from test_range_spgist where ir &> 100;
select count(*) from test_range_spgist where ir -|- int4range(100,500);
+select count(*) from test_range_spgist where ir -|- 100;
-- test index-only scans
explain (costs off)
--
2.26.2
