Re: unnesting multirange data types

On 6/10/21 1:24 PM, Alexander Korotkov wrote:
> 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.

Agreed, the subscripting functionality is too late for v14. (Though
perhaps someone ambitious could bridge that gap temporarily with the
ability to add subscripting to types!).

>  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 :)

Awesome. I'll defer to others on the implementation. I'll try to test
out the patch in a bit to see how it works.

Are there any objections adding this as a v14 open item?

Thanks,

Jonathan