Re: unnesting multirange data types

On 6/9/21 3:25 PM, Tom Lane wrote:
> "Jonathan S. Katz" <jkatz(at)postgresql(dot)org> writes:
>> I would like to decompose the returned multirange into its individual
>> ranges, similarly to how I would "unnest" an array:
>
> +1 for adding such a feature, but I suppose it's too late for v14.

Well, the case I would make for v14 is that, as of right now, the onus
is on the driver writers / application developers to be able to unpack
the multiranges.

Maybe it's not terrible as of this moment -- I haven't tried testing it
that far yet -- but it may make it a bit more challenging to work with
these types outside of Postgres. I recall a similar issue when initially
trying to integrate range types into my apps back in the v9.2 days, and
I ended up writing some grotty code to handle it. Yes, I worked around
it, but I preferably wouldn't have had to.

An "unnest" at least lets us bridge the gap a bit, i.e. if you really
need to introspect a multirange type, you have a way of getting it into
a familiar format.

I haven't tried manipulating a multirange in a PL like Python, maybe
some exploration there would unveil more or less pain, or if it could be
iterated over in PL/pgSQL (I'm suspecting no).

That all said, for writing queries within Postgres, the multiranges make
a lot of operations way easier. I do think a missing "unnest" function
does straddle the line of "omission" and "new feature," so I can
understand if it does not make it into v14.

> AFAICS, "unnest(anymultirange) returns setof anyrange" could coexist
> alongside the existing variants of unnest(), so I don't see any
> fundamental stumbling block to having it.

Cool. I was initially throwing out "unnest" as the name as it mirrors
what we currently have with arrays, and seems to be doing something
similar. Open to other names, but this was the one that I was drawn to.
"multirange" is an "ordered array of ranges" after all.

Thanks,

Jonathan