From 7bfb960aa885de5834b4a0bdcce57ead7eaf8962 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Sat, 21 Sep 2019 21:28:47 -0700 Subject: [PATCH v7 04/13] multirange docs --- doc/src/sgml/extend.sgml | 28 +++- doc/src/sgml/func.sgml | 302 +++++++++++++++++++++++++++++++---- doc/src/sgml/rangetypes.sgml | 47 +++++- 3 files changed, 342 insertions(+), 35 deletions(-) diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml index a3046f22d0..7ea2f65778 100644 --- a/doc/src/sgml/extend.sgml +++ b/doc/src/sgml/extend.sgml @@ -229,9 +229,9 @@ - Five pseudo-types of special interest are anyelement, + Six pseudo-types of special interest are anyelement, anyarray, anynonarray, anyenum, - and anyrange, + anyrange, and anymultirange. which are collectively called polymorphic types. Any function declared using these types is said to be a polymorphic function. A polymorphic function can @@ -250,15 +250,15 @@ position declared as anyarray can have any array data type, but similarly they must all be the same type. And similarly, positions declared as anyrange must all be the same range - type. Furthermore, if there are + type. Likewise for anymultirange. + + + + Furthermore, if there are positions declared anyarray and others declared anyelement, the actual array type in the anyarray positions must be an array whose elements are the same type appearing in the anyelement positions. - Similarly, if there are positions declared anyrange - and others declared anyelement, the actual range type in - the anyrange positions must be a range whose subtype is - the same type appearing in the anyelement positions. anynonarray is treated exactly the same as anyelement, but adds the additional constraint that the actual type must not be an array type. @@ -267,6 +267,20 @@ be an enum type. + + Similarly, if there are positions declared anyrange + and others declared anyelement, the actual range type in + the anyrange positions must be a range whose subtype is + the same type appearing in the anyelement positions. + The type anymultirange accepts a multirange + whose contents match the other polymorphic types. + That is it must hold ranges matching anyrange + and base type elements matching anyelement (if either of + those are present). If anyarray is present its elements + must match the base type of the anyrange and/or the base + type of the ranges in an anymultirange. + + Thus, when more than one argument position is declared with a polymorphic type, the net effect is that only certain combinations of actual argument diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 57a1539506..af547a2232 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -14566,7 +14566,9 @@ NULL baz(3 rows) shows the operators - available for range types. + available for range and multirange types. + Many of these operators will accept either a range or multirange + on either side. @@ -14576,7 +14578,7 @@ NULL baz(3 rows) Operator Description - Example + Examples Result @@ -14584,136 +14586,247 @@ NULL baz(3 rows) = equal - int4range(1,5) = '[1,4]'::int4range + int4range(1,5) = '[1,4]'::int4range +'{[1,5)}'::int4multirange = '{[1,4]}'::int4multirange t <> not equal - numrange(1.1,2.2) <> numrange(1.1,2.3) + numrange(1.1,2.2) <> numrange(1.1,2.3) +'{[1.1,2.2)}'::nummultirange <> '{[1.1,2.3)}'::nummultirange t < less than - int4range(1,10) < int4range(2,3) + int4range(1,10) < int4range(2,3) +'{[1,10)}'::int4multirange < '{[2,3)}'::int4multirange + t > greater than - int4range(1,10) > int4range(1,5) + + int4range(1,10) > int4range(1,5) +'{[1,10)}'::int4multirange > '{[1,5)}'::int4multirange + t <= less than or equal - numrange(1.1,2.2) <= numrange(1.1,2.2) + + numrange(1.1,2.2) <= numrange(1.1,2.2) +'{[1.1,2.2)}'::nummultirange <= '{[1.1,2.2)}'::nummultirange + t >= greater than or equal - numrange(1.1,2.2) >= numrange(1.1,2.0) + + numrange(1.1,2.2) >= numrange(1.1,2.0) +'{[1.1,2.2)}'::nummultirange >= '{[1.1,2.0)}'::nummultirange + + t + + + + @> + contains multirange + + int4range(2,4) @> '{[2,3)}'::int4multirange +'{[2,4)}'::int4multirange @> '{[2,3)}'::int4multirange + t @> contains range - int4range(2,4) @> int4range(2,3) + + int4range(2,4) @> int4range(2,3) +'{[2,4)}'::int4multirange @> int4range(2,3) + t @> contains element - '[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestamp + + '[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestamp +'{[2011-01-01,2011-03-01)}'::tsmultirange @> '2011-01-10'::timestamp + + t + + + + <@ + multirange is contained by + + '{[2,4)}'::int4multirange <@ int4range(1,7) +'{[2,4)}'::int4multirange <@ '{[1,7)}'::int4multirange + t <@ range is contained by - int4range(2,4) <@ int4range(1,7) + + int4range(2,4) <@ int4range(1,7) +int4range(2,4) <@ '{[1,7)}'::int4multirange + t <@ element is contained by - 42 <@ int4range(1,7) + + 42 <@ int4range(1,7) +42 <@ '{[1,7)}'::int4multirange + f && overlap (have points in common) - int8range(3,7) && int8range(4,12) + + int8range(3,7) && int8range(4,12) +int8range(3,7) && '{[4,12)}'::int8multirange +'{[3,7)}'::int8multirange && int8range(4,12) +'{[3,7)}'::int8multirange && '{[4,12)}'::int8multirange + t << strictly left of - int8range(1,10) << int8range(100,110) + + int8range(1,10) << int8range(100,110) +int8range(1,10) << '{[100,110)}'::int8multirange +'{[1,10)}'::int8multirange << int8range(100,110) +'{[1,10)}'::int8multirange << '{[100,110)}'::int8multirange + t >> strictly right of - int8range(50,60) >> int8range(20,30) + + int8range(50,60) >> int8range(20,30) +int8range(50,60) >> '{[20,30)}'::int8multirange +'{[50,60)}'::int8multirange >> int8range(20,30) +'{[50,60)}'::int8multirange >> '{[20,30)}'::int8multirange + t &< does not extend to the right of - int8range(1,20) &< int8range(18,20) + + int8range(1,20) &< int8range(18,20) +int8range(1,20) &< '{[18,20)}'::int8multirange +'{[1,20)}'::int8multirange &< int8range(18,20) +'{[1,20)}'::int8multirange &< '{[18,20)}'::int8multirange + t &> does not extend to the left of - int8range(7,20) &> int8range(5,10) + + int8range(7,20) &> int8range(5,10) +int8range(7,20) &> '{[5,10)}'::int8multirange +'{[7,20)}'::int8multirange &> int8range(5,10) +'{[7,20)}'::int8multirange &> '{[5,10)}'::int8multirange + t -|- is adjacent to - numrange(1.1,2.2) -|- numrange(2.2,3.3) + + numrange(1.1,2.2) -|- numrange(2.2,3.3) +numrange(1.1,2.2) -|- '{[2.2,3.3)}'::nummultirange +'{[1.1,2.2)}'::nummultirange -|- numrange(2.2,3.3) +'{[1.1,2.2)}'::nummultirange -|- '{[2.2,3.3)}'::nummultirange + t + union - numrange(5,15) + numrange(10,20) + numrange(5,15) + numrange(10,20) [5,20) + + @+ + safe union + + numrange(5,10) @+ numrange(15,20) +numrange(5,10) @+ '{[15,20)}'::nummultirange +'{[5,10)}'::nummultirange @+ numrange(15,20) +'{[5,10)}'::nummultirange @+ '{[15,20)}'::nummultirange + + {[5,10), [15,20)} + + * intersection - int8range(5,15) * int8range(10,20) + int8range(5,15) * int8range(10,20) [10,15) + + @* + safe intersection + + int8range(5,15) @* int8range(10,20) +int8range(5,15) @* '{[10,20)}'::int8multirange +'{[5,15)}'::int8multirange @* int8range(10,20) +'{[5,15)}'::int8multirange @* '{[10,20)}'::int8multirange + + {[10,15)} + + - difference - int8range(5,15) - int8range(10,20) + int8range(5,15) - int8range(10,20) [5,10) + + @- + safe difference + + int8range(5,20) @- int8range(10,15) +int8range(5,20) @- '{[10,15)}'::int8multirange +'{[5,20)}'::int8multirange @- int8range(10,15) +'{[5,20)}'::int8multirange @- '{[10,15)}'::int8multirange + + {[5,10), [15,20)} + +
@@ -14729,19 +14842,28 @@ NULL baz(3 rows) The left-of/right-of/adjacent operators always return false when an empty - range is involved; that is, an empty range is not considered to be either - before or after any other range. + range or multirange is involved; that is, an empty range is not considered to + be either before or after any other range. + + + + Elsewhere empty ranges and multiranges are treated as the additive identity: + anything unioned with an empty value is itself. Anything minus an empty + value is itself. An empty multirange has exactly the same points as an empty + range. Every range contains the empty range. Every multirange contains as many + empty ranges as you like. The union and difference operators will fail if the resulting range would need to contain two disjoint sub-ranges, as such a range cannot be - represented. + represented. The safe versions of union, intersection, and difference + return multiranges, so they can handle gaps without failing. shows the functions - available for use with range types. + available for use with range and multirange types. @@ -14790,6 +14912,17 @@ NULL baz(3 rows) lower(numrange(1.1,2.2)) 1.1 + + + + lower(anymultirange) + + + multirange's element type + lower bound of multirange + lower('{[1.1,2.2)}'::nummultirange) + 1.1 + @@ -14801,6 +14934,17 @@ NULL baz(3 rows) upper(numrange(1.1,2.2)) 2.2 + + + + upper(anymultirange) + + + multirange's element type + upper bound of multirange + upper('{[1.1,2.2)}'::nummultirange) + 2.2 + @@ -14812,6 +14956,17 @@ NULL baz(3 rows) isempty(numrange(1.1,2.2)) false + + + + isempty(anymultirange) + + + boolean + is the multirange empty? + isempty('{[1.1,2.2)}'::nummultirange) + false + @@ -14823,6 +14978,17 @@ NULL baz(3 rows) lower_inc(numrange(1.1,2.2)) true + + + + lower_inc(anymultirange) + + + boolean + is the lower bound inclusive? + lower_inc('{[1.1,2.2)}'::nummultirange) + true + @@ -14834,6 +15000,17 @@ NULL baz(3 rows) upper_inc(numrange(1.1,2.2)) false + + + + upper_inc(anymultirange) + + + boolean + is the upper bound inclusive? + upper_inc('{[1.1,2.2)}'::nummultirange) + false + @@ -14845,6 +15022,17 @@ NULL baz(3 rows) lower_inf('(,)'::daterange) true + + + + lower_inf(anymultirange) + + + boolean + is the lower bound infinite? + lower_inf('{(,)}'::datemultirange) + true + @@ -14856,6 +15044,17 @@ NULL baz(3 rows) upper_inf('(,)'::daterange) true + + + + upper_inf(anymultirange) + + + boolean + is the upper bound infinite? + upper_inf('{(,)}'::datemultirange) + true + @@ -14867,16 +15066,27 @@ NULL baz(3 rows) range_merge('[1,2)'::int4range, '[3,4)'::int4range) [1,4) + + + + range_merge(anymultirange) + + + anyrange + the smallest range which includes the entire multirange + range_merge('{[1,2), [3,4)}'::int4multirange) + [1,4) + The lower and upper functions return null - if the range is empty or the requested bound is infinite. + if the input range/multirange is empty or the requested bound is infinite. The lower_inc, upper_inc, lower_inf, and upper_inf - functions all return false for an empty range. + functions all return false for an empty range or multirange. @@ -15195,6 +15405,44 @@ NULL baz(3 rows) + + + + range_agg + + + range_agg(expression) + + + + anyrange + + + multirange of the argument range type + + Yes + union of the non-null input values + + + + + + range_intersect_agg + + + range_intersect_agg(expression) + + + + anyrange + + + multirange of the argument range type + + No + intersection of the non-null input values + + diff --git a/doc/src/sgml/rangetypes.sgml b/doc/src/sgml/rangetypes.sgml index 3a034d9b06..3e37b352fc 100644 --- a/doc/src/sgml/rangetypes.sgml +++ b/doc/src/sgml/rangetypes.sgml @@ -27,6 +27,13 @@ ranges from an instrument, and so forth can also be useful. + + Every range type has a corresponding multirange type. A multirange is + an ordered list of non-continguous, non-empty, non-null ranges. Most + range operators also work on multiranges, and they have a few functions + of their own. + + Built-in Range Types @@ -235,10 +242,30 @@ SELECT '[4,4]'::int4range; SELECT '[4,4)'::int4range; + + + The input for a multirange is curly brackets ({ and + }) containing zero or more valid ranges, + separated by commas. Whitespace is permitted around the brackets and + commas. This is intended to be reminiscent of array syntax, although + multiranges are much simpler: they have just one dimension and there is + no need to quote their contents. (The bounds of their ranges may be + quoted as above however.) + + + + Examples: + +SELECT '{}'::int4multirange; +SELECT '{[3,7)}'::int4multirange; +SELECT '{[3,7), [8,9)}'::int4multirange; + + + - Constructing Ranges + Constructing Ranges and Multiranges Each range type has a constructor function with the same name as the range @@ -270,6 +297,19 @@ SELECT int8range(1, 14, '(]'); -- Using NULL for either bound causes the range to be unbounded on that side. SELECT numrange(NULL, 2.2); + + + + + Each range type also has a multirange constructor with the same name as the + multirange type. The constructor function takes zero or more arguments + which are all ranges of the appropriate type. + For example: + + +SELECT nummultirange(); +SELECT nummultirange(numrange(1.0, 14.0)); +SELECT nummultirange(numrange(1.0, 14.0), numrange(20.0, 25.0)); @@ -344,6 +384,11 @@ SELECT '[1.234, 5.678]'::floatrange; function in this example. + + When you define your own range your automatically get a corresponding + multirange type. + + Defining your own range type also allows you to specify a different subtype B-tree operator class or collation to use, so as to change the sort -- 2.20.1