Re: range_agg - Mailing list pgsql-hackers

On Sat, Jul 6, 2019 at 12:13 PM Jeff Davis <pgsql@j-davis.com> wrote:
>
> On Fri, 2019-07-05 at 09:58 -0700, Paul A Jungwirth wrote:
> > user-defined range types. So how about I start on it and see how it
> > goes? I expect I can follow the existing code for range types pretty
> > closely, so maybe it won't be too hard.
>
> That would be great to at least take a look. If it starts to look like
> a bad idea, then we can re-evaluate and see if it's better to just
> return arrays.

I made some progress over the weekend. I don't have a patch yet but I
thought I'd ask for opinions on the approach I'm taking:

- A multirange type is an extra thing you get when you define a range
(just like how you get a tstzrange[]). Therefore....

- I don't need separate commands to add/drop multirange types. You get
one when you define a range type, and if you drop a range type it gets
dropped automatically.
- I'm adding a new typtype for multiranges. ('m' in pg_type).
- I'm just adding a mltrngtypeid column to pg_range. I don't think I
need a new pg_multirange table.
- You can have a multirange[].
- Multirange in/out work just like arrays, e.g. '{"[1,3)", "[5,6)"}'
- I'll add an anymultirange pseudotype. When it's the return type of a
function that has an "anyrange" parameter, it will use the same base
element type. (So basically anymultirange : anyrange :: anyarray ::
anyelement.)
- You can cast from a multirange to an array. The individual ranges
are always sorted in the result array.
- You can cast from an array to a multirange but it will error if
there are overlaps (or not?). The array's ranges don't have to be
sorted but they will be after a "round trip".
- Interesting functions:
  - multirange_length
  - range_agg (range_union_agg if you like)
  - range_intersection_agg
- You can subscript a multirange like you do an array (? This could be
a function instead.)
- operators:
  - union (++) and intersection (*):
    - We already have + for range union but it raises an error if
there is a gap, so ++ is the same but with no errors.
    - r ++ r = mr (commutative, associative)
    - mr ++ r = mr
    - r ++ mr = mr
    - r * r = r (unchanged)
    - mr * r = r
    - r * mr = r
    - mr - r = mr
    - r - mr = mr
    - mr - mr = mr
  - comparison
    - mr = mr
    - mr @> x
    - mr @> r
    - mr @> mr
    - x <@ mr
    - r <@ mr
    - mr <@ mr
    - mr << mr (strictly left of)
    - mr >> mr (strictly right of)
    - mr &< mr (does not extend to the right of)
    - mr &> mr (does not extend to the left of)
  - inverse operator?:
    - the inverse of {"[1,2)"} would be {"[null, 1)", "[2, null)"}.
    - not sure we want this or what the symbol should be. I don't like
-mr as an inverse because then mr - mr != mr ++ -mr.

Anything in there you think should be different?

Thanks,
Paul