Re: unnesting multirange data types

Hi, all!

On Thu, Jun 10, 2021 at 2:00 AM Jonathan S. Katz <jkatz@postgresql.org> wrote:
> On 6/9/21 4:56 PM, Alvaro Herrera wrote:
> > On 2021-Jun-09, Jonathan S. Katz wrote:
> >
> >> I did a couple more tests around this.
> >>
> >> As suspected, in PL/pgSQL, there is no way to unpack or iterate over a
> >> multirange type.
> >
> > Uh.  This is disappointing; the need for some way to unnest or unpack a
> > multirange was mentioned multiple times in the range_agg thread.  I had
> > assumed that there was some way to cast the multirange to a range array,
> > or somehow convert it, but apparently that doesn't work.
>
> Just to be pedantic with examples:
>
>   SELECT datemultirange(
>     daterange(current_date, current_date + 2),
>     daterange(current_date + 5, current_date + 7))::daterange[];
>
>   ERROR:  cannot cast type datemultirange to daterange[]
>   LINE 1: ...2), daterange(current_date + 5, current_date + 7))::daterang...
>
> IF there was an array to cast it into an array, we could then use the
> array looping construct in PL/pgSQL, but if we could only choose one, I
> think it'd be more natural/less verbose to have an "unnest".
>
> > If the supporting pieces are mostly there, then I opine we should add
> > something.
>
> Agreed.

I agree that unnest(), cast to array and subscription are missing
points.  Proper subscription support requires expanded object
handling.  And that seems too late for v14.  But unnset() and cast to
array seems trivial.  I've drafted unnest support (attached).  I'm
going to add also cast to the array, tests, and docs within a day.
Stay tuned :)

------
Regards,
Alexander Korotkov