Skip to content

Commit 4d7684c

Browse files
akorotkovakorotkov
committed
Implement operators for checking if the range contains a multirange
We have operators for checking if the multirange contains a range but don't have the opposite. This commit improves completeness of the operator set by adding two new operators: @> (anyrange,anymultirange) and <@(anymultirange,anyrange). Catversion is bumped.
1 parent a5b81b6 commit 4d7684c

File tree

9 files changed

+381
-3
lines changed

9 files changed

+381
-3
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -18182,6 +18182,20 @@ SELECT NULLIF(value, '(none)') ...
1818218182
</para></entry>
1818318183
</row>
1818418184

18185+
<row>
18186+
<entry role="func_table_entry"><para role="func_signature">
18187+
<type>anyrange</type> <literal>@&gt;</literal> <type>anymultirange</type>
18188+
<returnvalue>boolean</returnvalue>
18189+
</para>
18190+
<para>
18191+
Does the range contain the multirange?
18192+
</para>
18193+
<para>
18194+
<literal>'[2,4)'::int4range @&gt; '{[2,3)}'::int4multirange</literal>
18195+
<returnvalue>t</returnvalue>
18196+
</para></entry>
18197+
</row>
18198+
1818518199
<row>
1818618200
<entry role="func_table_entry"><para role="func_signature">
1818718201
<type>anymultirange</type> <literal>&lt;@</literal> <type>anymultirange</type>

‎src/backend/utils/adt/multirangetypes.c

Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1631,6 +1631,18 @@ multirange_contains_range(PG_FUNCTION_ARGS)
16311631
PG_RETURN_BOOL(multirange_contains_range_internal(typcache, mr, r));
16321632
}
16331633

1634+
Datum
1635+
range_contains_multirange(PG_FUNCTION_ARGS)
1636+
{
1637+
RangeType *r = PG_GETARG_RANGE_P(0);
1638+
MultirangeType *mr = PG_GETARG_MULTIRANGE_P(1);
1639+
TypeCacheEntry *typcache;
1640+
1641+
typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr));
1642+
1643+
PG_RETURN_BOOL(range_contains_multirange_internal(typcache, r, mr));
1644+
}
1645+
16341646
/* contained by? */
16351647
Datum
16361648
range_contained_by_multirange(PG_FUNCTION_ARGS)
@@ -1644,6 +1656,18 @@ range_contained_by_multirange(PG_FUNCTION_ARGS)
16441656
PG_RETURN_BOOL(multirange_contains_range_internal(typcache, mr, r));
16451657
}
16461658

1659+
Datum
1660+
multirange_contained_by_range(PG_FUNCTION_ARGS)
1661+
{
1662+
MultirangeType *mr = PG_GETARG_MULTIRANGE_P(0);
1663+
RangeType *r = PG_GETARG_RANGE_P(1);
1664+
TypeCacheEntry *typcache;
1665+
1666+
typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr));
1667+
1668+
PG_RETURN_BOOL(range_contains_multirange_internal(typcache, r, mr));
1669+
}
1670+
16471671
/*
16481672
* Comparison function for checking if any range of multirange contains given
16491673
* key range using binary search.
@@ -1701,6 +1725,42 @@ multirange_contains_range_internal(TypeCacheEntry *typcache, MultirangeType *mr,
17011725
multirange_range_contains_bsearch_comparison);
17021726
}
17031727

1728+
/*
1729+
* Test whether range r contains a multirange mr.
1730+
*/
1731+
bool
1732+
range_contains_multirange_internal(TypeCacheEntry *typcache, RangeType *r,
1733+
MultirangeType *mr)
1734+
{
1735+
TypeCacheEntry *rangetyp;
1736+
RangeBound lower1,
1737+
upper1,
1738+
lower2,
1739+
upper2,
1740+
tmp;
1741+
bool empty;
1742+
1743+
rangetyp = typcache->rngtype;
1744+
1745+
/*
1746+
* Every range contains an infinite number of empty multiranges, even an
1747+
* empty one.
1748+
*/
1749+
if (MultirangeIsEmpty(mr))
1750+
return true;
1751+
1752+
if (RangeIsEmpty(r))
1753+
return false;
1754+
1755+
/* Range contains multirange iff it contains its union range. */
1756+
range_deserialize(rangetyp, r, &lower1, &upper1, &empty);
1757+
Assert(!empty);
1758+
multirange_get_bounds(rangetyp, mr, 0, &lower2, &tmp);
1759+
multirange_get_bounds(rangetyp, mr, mr->rangeCount - 1, &tmp, &upper2);
1760+
1761+
return range_bounds_contains(rangetyp, &lower1, &upper1, &lower2, &upper2);
1762+
}
1763+
17041764

17051765
/* multirange, multirange -> bool functions */
17061766

‎src/backend/utils/adt/multirangetypes_selfuncs.c

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -86,6 +86,8 @@ default_multirange_selectivity(Oid operator)
8686
case OID_RANGE_OVERLAPS_MULTIRANGE_OP:
8787
return 0.01;
8888

89+
case OID_RANGE_CONTAINS_MULTIRANGE_OP:
90+
case OID_RANGE_MULTIRANGE_CONTAINED_OP:
8991
case OID_MULTIRANGE_CONTAINS_RANGE_OP:
9092
case OID_MULTIRANGE_CONTAINS_MULTIRANGE_OP:
9193
case OID_MULTIRANGE_RANGE_CONTAINED_OP:
@@ -224,7 +226,8 @@ multirangesel(PG_FUNCTION_ARGS)
224226
1, &constrange);
225227
}
226228
}
227-
else if (operator == OID_MULTIRANGE_CONTAINS_RANGE_OP ||
229+
else if (operator == OID_RANGE_MULTIRANGE_CONTAINED_OP ||
230+
operator == OID_MULTIRANGE_CONTAINS_RANGE_OP ||
228231
operator == OID_MULTIRANGE_OVERLAPS_RANGE_OP ||
229232
operator == OID_MULTIRANGE_OVERLAPS_LEFT_RANGE_OP ||
230233
operator == OID_MULTIRANGE_OVERLAPS_RIGHT_RANGE_OP ||
@@ -248,6 +251,7 @@ multirangesel(PG_FUNCTION_ARGS)
248251
operator == OID_RANGE_OVERLAPS_RIGHT_MULTIRANGE_OP ||
249252
operator == OID_RANGE_LEFT_MULTIRANGE_OP ||
250253
operator == OID_RANGE_RIGHT_MULTIRANGE_OP ||
254+
operator == OID_RANGE_CONTAINS_MULTIRANGE_OP ||
251255
operator == OID_MULTIRANGE_ELEM_CONTAINED_OP ||
252256
operator == OID_MULTIRANGE_RANGE_CONTAINED_OP)
253257
{

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 202012201
56+
#define CATALOG_VERSION_NO 202012291
5757

5858
#endif

‎src/include/catalog/pg_operator.dat

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3360,6 +3360,18 @@
33603360
oprresult => 'bool', oprcom => '@>(anymultirange,anymultirange)',
33613361
oprcode => 'multirange_contained_by_multirange', oprrest => 'multirangesel',
33623362
oprjoin => 'contjoinsel' },
3363+
{ oid => '4539', oid_symbol => 'OID_RANGE_CONTAINS_MULTIRANGE_OP',
3364+
descr => 'contains',
3365+
oprname => '@>', oprleft => 'anyrange', oprright => 'anymultirange',
3366+
oprresult => 'bool', oprcom => '<@(anymultirange,anyrange)',
3367+
oprcode => 'range_contains_multirange', oprrest => 'multirangesel',
3368+
oprjoin => 'contjoinsel' },
3369+
{ oid => '4540', oid_symbol => 'OID_RANGE_MULTIRANGE_CONTAINED_OP',
3370+
descr => 'is contained by',
3371+
oprname => '<@', oprleft => 'anymultirange', oprright => 'anyrange',
3372+
oprresult => 'bool', oprcom => '@>(anyrange,anymultirange)',
3373+
oprcode => 'multirange_contained_by_range', oprrest => 'multirangesel',
3374+
oprjoin => 'contjoinsel' },
33633375
{ oid => '2875', oid_symbol => 'OID_RANGE_OVERLAPS_LEFT_MULTIRANGE_OP',
33643376
descr => 'overlaps or is left of',
33653377
oprname => '&<', oprleft => 'anyrange', oprright => 'anymultirange',

‎src/include/catalog/pg_proc.dat

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10085,6 +10085,12 @@
1008510085
{ oid => '4253',
1008610086
proname => 'range_contained_by_multirange', prorettype => 'bool',
1008710087
proargtypes => 'anyrange anymultirange', prosrc => 'range_contained_by_multirange' },
10088+
{ oid => '4541',
10089+
proname => 'range_contains_multirange', prorettype => 'bool',
10090+
proargtypes => 'anyrange anymultirange', prosrc => 'range_contains_multirange' },
10091+
{ oid => '4542',
10092+
proname => 'multirange_contained_by_range', prorettype => 'bool',
10093+
proargtypes => 'anymultirange anyrange', prosrc => 'multirange_contained_by_range' },
1008810094
{ oid => '4254',
1008910095
proname => 'multirange_contained_by_multirange', prorettype => 'bool',
1009010096
proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_contained_by_multirange' },

‎src/include/utils/multirangetypes.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -64,6 +64,8 @@ extern bool multirange_contains_elem_internal(TypeCacheEntry *typcache, Multiran
6464
Datum elem);
6565
extern bool multirange_contains_range_internal(TypeCacheEntry *typcache, MultirangeType *mr,
6666
RangeType *r);
67+
extern bool range_contains_multirange_internal(TypeCacheEntry *typcache, RangeType *r,
68+
MultirangeType *mr);
6769
extern bool multirange_contains_multirange_internal(TypeCacheEntry *typcache,
6870
MultirangeType *mr1,
6971
MultirangeType *mr2);
@@ -77,7 +79,7 @@ extern bool range_before_multirange_internal(TypeCacheEntry *typcache, RangeType
7779
extern bool range_after_multirange_internal(TypeCacheEntry *typcache, RangeType *r,
7880
MultirangeType *mr);
7981
extern bool range_adjacent_multirange_internal(TypeCacheEntry *typcache, RangeType *r,
80-
MultirangeType *mr);
82+
MultirangeType *mr);
8183
extern bool multirange_before_multirange_internal(TypeCacheEntry *typcache,
8284
MultirangeType *mr1,
8385
MultirangeType *mr2);

0 commit comments

Comments
 (0)