Extending range type operators to cope with elements
Started by Esteban Zimanyiover 6 years ago15 messages
Dear all
While developing MobilityDB we needed to extend the range type operators so
they cope with elements. In the same way that currently the range types
support both
- @> contains range/element
- <@ element/range is contained by
we extended the left (<<), overleft (&<), right (>>), and overright (&>)
operators so they can cope with both elements and ranges at the left- or
right-hand side. These can be seen in github
https://github.com/ULB-CoDE-WIT/MobilityDB/blob/master/src/rangetypes_ext.c
If you think that these extensions could be useful for the community at
large, I can prepare a PR. Please let me know.
Esteban
Re: Extending range type operators to cope with elements
On Fri, Sep 13, 2019 at 08:50:18AM +0200, Esteban Zimanyi wrote:
Dear all
While developing MobilityDB we needed to extend the range type operators so
they cope with elements. In the same way that currently the range types
support both
- @> contains range/element
- <@ element/range is contained by
we extended the left (<<), overleft (&<), right (>>), and overright (&>)
operators so they can cope with both elements and ranges at the left- or
right-hand side. These can be seen in github
https://github.com/ULB-CoDE-WIT/MobilityDB/blob/master/src/rangetypes_ext.cIf you think that these extensions could be useful for the community at
large, I can prepare a PR. Please let me know.
Well, we don't really use pull requests, but other than that I don't see
why not to at least consider such improvement.
I'm not a heavy user or range types, so I can't really judge how useful
that is in practice, but it seems like a fairly natural extension of the
existing operators. I mean, if I understand it correctly, the proposed
behavior is equal to treating the element as a "collapsed range".
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Extending range type operators to cope with elements
- @> contains range/element
- <@ element/range is contained by
I'm not a heavy user or range types, so I can't really judge how useful
that is in practice, but it seems like a fairly natural extension of the
existing operators. I mean, if I understand it correctly, the proposed
behavior is equal to treating the element as a "collapsed range".
I used to give a talk on ranges and partitioning, prior to postgresql
getting native partitioning (see:
https://wiki.postgresql.org/images/1/1b/Ranges%2C_Partitioning_and_Limitations.pdf
)
In that talk, I mention the need for exactly these operators, specifically
for an extension called range_partitioning which had some logic for "If I
were to insert a row with this value, what partition would it end up in?"
which allowed for a subsequent COPY operation directly to that partition.
That logic essentially binary-searched a series of ranges, so it needed an
"elem <@ range" as well as << and >>.
Yes, constructing a collapsed range was the work-around I used in the
absence of real functions.
That extension has been replaced by real table partitioning and the planner
itself now does similar logic for partition pruning.
So yes, I've had a need for those operators in the past. What I don't know
is whether adding these functions will be worth the catalog clutter.
Re: Extending range type operators to cope with elements
So yes, I've had a need for those operators in the past. What I don't know
is whether adding these functions will be worth the catalog clutter.
The operators are tested and running within MobilityDB. It concerns lines
231-657 for the C code in file
https://github.com/ULB-CoDE-WIT/MobilityDB/blob/master/src/rangetypes_ext.c
and lines 32-248 for the SQL code in file
https://github.com/ULB-CoDE-WIT/MobilityDB/blob/master/src/sql/07_rangetypes_ext.in.sql
Since you don't really use PR, please let me know whether I can be of
any help.
Regards
Esteban
--
------------------------------------------------------------
Prof. Esteban Zimanyi
Department of Computer & Decision Engineering (CoDE) CP 165/15
Universite Libre de Bruxelles
Avenue F. D. Roosevelt 50
B-1050 Brussels, Belgium
fax: + 32.2.650.47.13
tel: + 32.2.650.31.85
e-mail: ezimanyi@ulb.ac.be
Internet: http://code.ulb.ac.be/
------------------------------------------------------------
Fwd: Extending range type operators to cope with elements
So yes, I've had a need for those operators in the past. What I don't
know is whether adding these functions will be worth the catalog clutter.
The operators are tested and running within MobilityDB. It concerns lines
231-657 for the C code in file
https://github.com/ULB-CoDE-WIT/MobilityDB/blob/master/src/rangetypes_ext.c
and lines 32-248 for the SQL code in file
https://github.com/ULB-CoDE-WIT/MobilityDB/blob/master/src/sql/07_rangetypes_ext.in.sql
Since you don't really use PR, please let me know whether I can be of
any help.
Regards
Esteban
--
------------------------------------------------------------
Prof. Esteban Zimanyi
Department of Computer & Decision Engineering (CoDE) CP 165/15
Universite Libre de Bruxelles
Avenue F. D. Roosevelt 50
B-1050 Brussels, Belgium
fax: + 32.2.650.47.13
tel: + 32.2.650.31.85
e-mail: ezimanyi@ulb.ac.be
Internet: http://code.ulb.ac.be/
------------------------------------------------------------
Re: Fwd: Extending range type operators to cope with elements
On Sun, Sep 15, 2019 at 04:30:52PM +0200, Esteban Zimanyi wrote:
So yes, I've had a need for those operators in the past. What I don't
know is whether adding these functions will be worth the catalog clutter.
The operators are tested and running within MobilityDB. It concerns lines
231-657 for the C code in file
https://github.com/ULB-CoDE-WIT/MobilityDB/blob/master/src/rangetypes_ext.cand lines 32-248 for the SQL code in file
https://github.com/ULB-CoDE-WIT/MobilityDB/blob/master/src/sql/07_rangetypes_ext.in.sqlSince you don't really use PR, please let me know whether I can be of
any help.
It's not done by pull request at this time. Instead, it is done by sending
patches to this mailing list.
http://wiki.postgresql.org/wiki/Development_information
http://wiki.postgresql.org/wiki/Submitting_a_Patch
https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_developer%3F
http://www.interdb.jp/pg/
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Re: Fwd: Extending range type operators to cope with elements
On Tue, Sep 17, 2019 at 5:18 AM David Fetter <david@fetter.org> wrote:
It's not done by pull request at this time. Instead, it is done by sending
patches to this mailing list.
Dear all
You will find enclosed the patch that extends the range type operators so
they cope with elements.
Any comments most welcome.
Esteban
Attachments:
zimanyi.patchtext/x-patch; charset=US-ASCII; name=zimanyi.patchDownload
diff -urdN postgresql-11.5-orig/doc/src/sgml/func.sgml postgresql-11.5-ranges/doc/src/sgml/func.sgml
--- postgresql-11.5-orig/doc/src/sgml/func.sgml 2019-09-21 11:28:11.836309263 +0200
+++ postgresql-11.5-ranges/doc/src/sgml/func.sgml 2019-09-21 10:32:53.320004000 +0200
@@ -13228,6 +13228,20 @@
</row>
<row>
+ <entry> <literal><<</literal> </entry>
+ <entry>strictly left of element</entry>
+ <entry><literal>int8range(1,10) << 100</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
+ <row>
+ <entry> <literal><<</literal> </entry>
+ <entry>element strictly left of</entry>
+ <entry><literal>10 << int8range(100,110)</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
+ <row>
<entry> <literal>>></literal> </entry>
<entry>strictly right of</entry>
<entry><literal>int8range(50,60) >> int8range(20,30)</literal></entry>
@@ -13235,6 +13249,20 @@
</row>
<row>
+ <entry> <literal>>></literal> </entry>
+ <entry>strictly right of element</entry>
+ <entry><literal>int8range(50,60) >> 20</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
+ <row>
+ <entry> <literal>>></literal> </entry>
+ <entry>element strictly right of</entry>
+ <entry><literal>50 >> int8range(20,30)</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
+ <row>
<entry> <literal>&<</literal> </entry>
<entry>does not extend to the right of</entry>
<entry><literal>int8range(1,20) &< int8range(18,20)</literal></entry>
@@ -13242,6 +13270,20 @@
</row>
<row>
+ <entry> <literal>&<</literal> </entry>
+ <entry>does not extend to the right of element</entry>
+ <entry><literal>int8range(1,20) &< 20</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
+ <row>
+ <entry> <literal>&<</literal> </entry>
+ <entry>element does not extend to the right of</entry>
+ <entry><literal>19 &< int8range(18,20)</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
+ <row>
<entry> <literal>&></literal> </entry>
<entry>does not extend to the left of</entry>
<entry><literal>int8range(7,20) &> int8range(5,10)</literal></entry>
@@ -13249,12 +13291,40 @@
</row>
<row>
+ <entry> <literal>&></literal> </entry>
+ <entry>does not extend to the left of element</entry>
+ <entry><literal>int8range(7,20) &> 5</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
+ <row>
+ <entry> <literal>&></literal> </entry>
+ <entry>element does not extend to the left of</entry>
+ <entry><literal>7 &> int8range(5,10)</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
+ <row>
<entry> <literal>-|-</literal> </entry>
<entry>is adjacent to</entry>
<entry><literal>numrange(1.1,2.2) -|- numrange(2.2,3.3)</literal></entry>
<entry><literal>t</literal></entry>
</row>
+ <row>
+ <entry> <literal>-|-</literal> </entry>
+ <entry>is adjacent to element</entry>
+ <entry><literal>numrange(1.1,2.2) -|- 2.2</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
+ <row>
+ <entry> <literal>-|-</literal> </entry>
+ <entry>element is adjacent to</entry>
+ <entry><literal>2.2 -|- numrange(2.2,3.3, '()')</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
<row>
<entry> <literal>+</literal> </entry>
<entry>union</entry>
diff -urdN postgresql-11.5-orig/src/backend/utils/adt/rangetypes.c postgresql-11.5-ranges/src/backend/utils/adt/rangetypes.c
--- postgresql-11.5-orig/src/backend/utils/adt/rangetypes.c 2019-09-21 11:28:11.628205263 +0200
+++ postgresql-11.5-ranges/src/backend/utils/adt/rangetypes.c 2019-09-21 10:32:53.320004000 +0200
@@ -548,6 +548,322 @@
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));
+}
/* range, range -> bool functions */
diff -urdN postgresql-11.5-orig/src/include/catalog/pg_operator.dat postgresql-11.5-ranges/src/include/catalog/pg_operator.dat
--- postgresql-11.5-orig/src/include/catalog/pg_operator.dat 2019-09-21 11:28:11.484133263 +0200
+++ postgresql-11.5-ranges/src/include/catalog/pg_operator.dat 2019-09-21 17:05:20.566965777 +0200
@@ -3336,5 +3336,59 @@
{ oid => '3287', descr => 'delete path',
oprname => '#-', oprleft => 'jsonb', oprright => '_text',
oprresult => 'jsonb', oprcode => 'jsonb_delete_path' },
+{ oid => '4001', 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 => '4002', 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 => '4003', 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 => '4004', 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 => '4005', 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 => '4006', 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 => '4007', 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 => '4008', 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 => '4009', 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 => '4010', 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 -urdN postgresql-11.5-orig/src/include/catalog/pg_proc.dat postgresql-11.5-ranges/src/include/catalog/pg_proc.dat
--- postgresql-11.5-orig/src/include/catalog/pg_proc.dat 2019-09-21 11:28:11.476129264 +0200
+++ postgresql-11.5-ranges/src/include/catalog/pg_proc.dat 2019-09-21 16:00:11.649542000 +0200
@@ -9591,6 +9591,36 @@
{ oid => '3169', descr => 'restriction selectivity for range operators',
proname => 'rangesel', provolatile => 's', prorettype => 'float8',
proargtypes => 'internal oid internal int4', prosrc => 'rangesel' },
+{ oid => '4142',
+ proname => 'range_adjacent_elem', prorettype => 'bool',
+ proargtypes => 'anyrange anyelement', prosrc => 'range_adjacent_elem' },
+{ oid => '4143',
+ proname => 'elem_adjacent_range', prorettype => 'bool',
+ proargtypes => 'anyelement anyrange', prosrc => 'elem_adjacent_range' },
+{ oid => '4144',
+ proname => 'range_before_elem', prorettype => 'bool',
+ proargtypes => 'anyrange anyelement', prosrc => 'range_before_elem' },
+{ oid => '4145',
+ proname => 'elem_before_range', prorettype => 'bool',
+ proargtypes => 'anyelement anyrange', prosrc => 'elem_before_range' },
+{ oid => '4146',
+ proname => 'range_after_elem', prorettype => 'bool',
+ proargtypes => 'anyrange anyelement', prosrc => 'range_after_elem' },
+{ oid => '4147',
+ proname => 'elem_after_range', prorettype => 'bool',
+ proargtypes => 'anyelement anyrange', prosrc => 'elem_after_range' },
+{ oid => '4148',
+ proname => 'range_overleft_elem', prorettype => 'bool',
+ proargtypes => 'anyrange anyelement', prosrc => 'range_overleft_elem' },
+{ oid => '4149',
+ proname => 'elem_overleft_range', prorettype => 'bool',
+ proargtypes => 'anyelement anyrange', prosrc => 'elem_overleft_range' },
+{ oid => '4150',
+ proname => 'range_overright_elem', prorettype => 'bool',
+ proargtypes => 'anyrange anyelement', prosrc => 'range_overright_elem' },
+{ oid => '4151',
+ proname => 'elem_overright_range', prorettype => 'bool',
+ proargtypes => 'anyelement anyrange', prosrc => 'elem_overright_range' },
{ oid => '3914', descr => 'convert an int4 range to canonical form',
proname => 'int4range_canonical', prorettype => 'int4range',
diff -urdN postgresql-11.5-orig/src/include/utils/rangetypes.h postgresql-11.5-ranges/src/include/utils/rangetypes.h
--- postgresql-11.5-orig/src/include/utils/rangetypes.h 2019-09-21 11:28:11.472127264 +0200
+++ postgresql-11.5-ranges/src/include/utils/rangetypes.h 2019-09-21 10:32:53.320004000 +0200
@@ -94,6 +94,18 @@
extern bool range_contains_elem_internal(TypeCacheEntry *typcache, 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_before_range_internal(TypeCacheEntry *typcache, Datum r, RangeType *val);
+extern bool elem_overleft_range_internal(TypeCacheEntry *typcache, Datum r, RangeType *val);
+extern bool elem_after_range_internal(TypeCacheEntry *typcache, Datum r, RangeType *val);
+extern bool elem_overright_range_internal(TypeCacheEntry *typcache, Datum r, RangeType *val);
+extern bool elem_adjacent_range_internal(TypeCacheEntry *typcache, Datum r, RangeType *val);
+
/* internal versions of the above */
extern bool range_eq_internal(TypeCacheEntry *typcache, RangeType *r1,
RangeType *r2);
diff -urdN postgresql-11.5-orig/src/test/regress/expected/rangetypes.out postgresql-11.5-ranges/src/test/regress/expected/rangetypes.out
--- postgresql-11.5-orig/src/test/regress/expected/rangetypes.out 2019-09-21 11:28:11.536159263 +0200
+++ postgresql-11.5-ranges/src/test/regress/expected/rangetypes.out 2019-09-21 17:26:44.324523000 +0200
@@ -378,6 +378,18 @@
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 @@
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 @@
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?
----------
@@ -732,30 +798,60 @@
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;
@@ -802,30 +898,60 @@
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);
@@ -871,30 +997,60 @@
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);
@@ -951,30 +1107,60 @@
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;
@@ -1021,30 +1207,60 @@
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);
@@ -1090,30 +1306,60 @@
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 -urdN postgresql-11.5-orig/src/test/regress/sql/rangetypes.sql postgresql-11.5-ranges/src/test/regress/sql/rangetypes.sql
--- postgresql-11.5-orig/src/test/regress/sql/rangetypes.sql 2019-09-21 11:28:11.512147263 +0200
+++ postgresql-11.5-ranges/src/test/regress/sql/rangetypes.sql 2019-09-21 10:32:53.320004000 +0200
@@ -87,6 +87,9 @@
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 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);
@@ -194,10 +206,15 @@
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;
@@ -211,10 +228,15 @@
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;
@@ -227,10 +249,15 @@
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);
@@ -256,10 +283,15 @@
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;
@@ -273,10 +305,15 @@
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;
@@ -289,10 +326,15 @@
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)
Re: Fwd: Extending range type operators to cope with elements
Dear all
After a long time (as you can imagine, this year everything has been upside
down ...), you will find enclosed the patch for extending the range
operators so they can cope with range <op> element and element <op> range
in addition to the existing range <op> range.
Best regards
Esteban
------------------------------------------------------------
Prof. Esteban Zimanyi
Department of Computer & Decision Engineering (CoDE) CP 165/15
Universite Libre de Bruxelles
Avenue F. D. Roosevelt 50
B-1050 Brussels, Belgium
fax: + 32.2.650.47.13
tel: + 32.2.650.31.85
e-mail: ezimanyi@ulb.ac.be
Internet: http://cs.ulb.ac.be/members/esteban/
------------------------------------------------------------
On Tue, Sep 17, 2019 at 5:18 AM David Fetter <david@fetter.org> wrote:
Show quoted text
On Sun, Sep 15, 2019 at 04:30:52PM +0200, Esteban Zimanyi wrote:
So yes, I've had a need for those operators in the past. What I don't
know is whether adding these functions will be worth the catalog clutter.
The operators are tested and running within MobilityDB. It concerns lines
231-657 for the C code in filehttps://github.com/MobilityDB/MobilityDB/blob/master/src/rangetypes_ext.c
<https://github.com/ULB-CoDE-WIT/MobilityDB/blob/master/src/rangetypes_ext.c>and lines 32-248 for the SQL code in file
https://github.com/ULB-CoDE-WIT/MobilityDB/blob/master/src/sql/07_rangetypes_ext.in.sql
Since you don't really use PR, please let me know whether I can be of
any help.It's not done by pull request at this time. Instead, it is done by sending
patches to this mailing list.http://wiki.postgresql.org/wiki/Development_information
http://wiki.postgresql.org/wiki/Submitting_a_Patch
https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_developer%3F
http://www.interdb.jp/pg/Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Attachments:
range-ext.patchapplication/octet-stream; name=range-ext.patchDownload
diff -urdN postgresql-13.0-orig/doc/src/sgml/func.sgml postgresql-13.0-range-ext/doc/src/sgml/func.sgml
--- postgresql-13.0-orig/doc/src/sgml/func.sgml 2020-09-21 22:47:36.000000000 +0200
+++ postgresql-13.0-range-ext/doc/src/sgml/func.sgml 2020-09-27 14:13:51.621421300 +0200
@@ -18024,6 +18024,34 @@
<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>
<returnvalue>boolean</returnvalue>
</para>
@@ -18038,6 +18066,34 @@
<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>
<returnvalue>boolean</returnvalue>
</para>
@@ -18052,6 +18108,34 @@
<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>
<returnvalue>boolean</returnvalue>
</para>
@@ -18066,6 +18150,34 @@
<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>
<returnvalue>boolean</returnvalue>
</para>
@@ -18077,6 +18189,34 @@
<returnvalue>t</returnvalue>
</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">
diff -urdN postgresql-13.0-orig/src/backend/utils/adt/rangetypes.c postgresql-13.0-range-ext/src/backend/utils/adt/rangetypes.c
--- postgresql-13.0-orig/src/backend/utils/adt/rangetypes.c 2020-09-21 22:47:36.000000000 +0200
+++ postgresql-13.0-range-ext/src/backend/utils/adt/rangetypes.c 2020-09-27 12:39:35.221421300 +0200
@@ -536,6 +536,323 @@
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 @@
PG_RETURN_BOOL(range_contains_elem_internal(typcache, r, val));
}
-
/* range, range -> bool functions */
/* equality (internal version) */
diff -urdN postgresql-13.0-orig/src/include/catalog/pg_operator.dat postgresql-13.0-range-ext/src/include/catalog/pg_operator.dat
--- postgresql-13.0-orig/src/include/catalog/pg_operator.dat 2020-09-21 22:47:36.000000000 +0200
+++ postgresql-13.0-range-ext/src/include/catalog/pg_operator.dat 2020-09-27 13:01:08.681421300 +0200
@@ -3327,5 +3327,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 -urdN postgresql-13.0-orig/src/include/catalog/pg_proc.dat postgresql-13.0-range-ext/src/include/catalog/pg_proc.dat
--- postgresql-13.0-orig/src/include/catalog/pg_proc.dat 2020-09-21 22:47:36.000000000 +0200
+++ postgresql-13.0-range-ext/src/include/catalog/pg_proc.dat 2020-09-27 13:31:54.351421300 +0200
@@ -9913,6 +9913,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 -urdN postgresql-13.0-orig/src/include/utils/rangetypes.h postgresql-13.0-range-ext/src/include/utils/rangetypes.h
--- postgresql-13.0-orig/src/include/utils/rangetypes.h 2020-09-21 22:47:36.000000000 +0200
+++ postgresql-13.0-range-ext/src/include/utils/rangetypes.h 2020-09-27 12:36:10.811421300 +0200
@@ -93,6 +93,13 @@
*/
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 -urdN postgresql-13.0-orig/src/test/regress/expected/rangetypes.out postgresql-13.0-range-ext/src/test/regress/expected/rangetypes.out
--- postgresql-13.0-orig/src/test/regress/expected/rangetypes.out 2020-09-21 22:47:36.000000000 +0200
+++ postgresql-13.0-range-ext/src/test/regress/expected/rangetypes.out 2020-09-27 15:28:52.221421300 +0200
@@ -372,6 +372,18 @@
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 range_adjacent(numrange(2.0, 3.0, '(]'), numrange(1.0, 2.0, '(]'));
range_adjacent
----------------
@@ -432,6 +444,18 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 -urdN postgresql-13.0-orig/src/test/regress/sql/rangetypes.sql postgresql-13.0-range-ext/src/test/regress/sql/rangetypes.sql
--- postgresql-13.0-orig/src/test/regress/sql/rangetypes.sql 2020-09-21 22:47:36.000000000 +0200
+++ postgresql-13.0-range-ext/src/test/regress/sql/rangetypes.sql 2020-09-27 15:28:52.231421300 +0200
@@ -87,6 +87,9 @@
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 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,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,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,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,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,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,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)
Re: Fwd: Extending range type operators to cope with elements
Esteban Zimanyi <ezimanyi@ulb.ac.be> writes:
After a long time (as you can imagine, this year everything has been upside
down ...), you will find enclosed the patch for extending the range
operators so they can cope with range <op> element and element <op> range
in addition to the existing range <op> range.
Cool. Please add this to the open commitfest list [1]https://commitfest.postgresql.org/30/ to ensure we don't
lose track of it.
regards, tom lane
Re: Extending range type operators to cope with elements
Hi,
thank you for your contribution.
I did notice that the cfbot [1]http://cfbot.cputube.org/esteban-zimanyi.html is failing for this patch.
Please try to address the issues if you can for the upcoming commitfest.
Cheers,
//Georgios
Re: Extending range type operators to cope with elements
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
Attachments:
0001-range-ext-20201030.patchtext/plain; charset=us-asciiDownload
From 414aefb911308b39ffefbd2190db66f2ee24c26c Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas@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
Re: Extending range type operators to cope with elements
On 31.10.2020 01:08, Tomas Vondra wrote:
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
CFM reminder. Just in case you forgot about this thread)
The commitfest is heading to the end. Tomas, will you have time to push
this patch?
The patch still applies and passes all cfbot checks. I also took a quick
look at the code and everything looks good to me.
--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Re: Extending range type operators to cope with elements
On Fri, Oct 30, 2020 at 11:08:19PM +0100, Tomas Vondra wrote:
Hi,
+ <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>
should say "of the range" ?
+++ b/src/backend/utils/adt/rangetypes.c
+ /* An empty range is neither left nor right any other range */ + /* An empty range is neither left nor right any element */ + /* An empty range is neither left nor right any other range */ + /* An empty range is neither left nor right any element */ + /* An empty range is neither left nor right any element */ + /* An empty range is neither left nor right any element */
I these comments should all say ".. left nor right OF any ..."
--
Justin
Re: Extending range type operators to cope with elements
On Sun, Feb 28, 2021 at 1:36 AM Justin Pryzby <pryzby@telsasoft.com> wrote:
On Fri, Oct 30, 2020 at 11:08:19PM +0100, Tomas Vondra wrote:
Hi,
+ <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>should say "of the range" ?
+++ b/src/backend/utils/adt/rangetypes.c+ /* An empty range is neither left nor right any other range */ + /* An empty range is neither left nor right any element */ + /* An empty range is neither left nor right any other range */ + /* An empty range is neither left nor right any element */ + /* An empty range is neither left nor right any element */ + /* An empty range is neither left nor right any element */I these comments should all say ".. left nor right OF any ..."
--
Justin
This patch set no longer applies.
http://cfbot.cputube.org/patch_32_2747.log
Can we get a rebase?
I am marking the patch "Waiting on Author"
--
Ibrar Ahmed
Re: Extending range type operators to cope with elements
On 3/4/21 6:11 AM, Ibrar Ahmed wrote:
This patch set no longer applies.
http://cfbot.cputube.org/patch_32_2747.log
<http://cfbot.cputube.org/patch_32_2747.log>Can we get a rebase?
I am marking the patch "Waiting on Author"
This patch needs updates and a rebase and there has been no new patch
six months, so marking Returned with Feedback.
Please resubmit to the next CF when you have a new patch.
Regards,
--
-David
david@pgmasters.net