Thread: looping over multirange segments?

I'm really, really liking the multirange types in PG14. Thank you for making them! Unfortunately I am struggling with how to loop over the segments of a multirange. There doesn't seem to be a way to convert them to arrays, and I can't just use plpgsql's FOREACH on one. Am I missing something obvious? It seems like a reasonable thing to want to do.

FWIW, my actual end goal is to take something like this:

select int8range(1,10)::int8multirange - int8range(4,6)::int8multirange;
    ?column?
────────────────
 {[1,4),[6,10)}

...and turn it into this:

select ???;
 int8range │ ?column?
───────────┼──────────
 [1,4)     │        0
 [6,10)    │        0

On Wed, Jul 14, 2021 at 04:19:48PM -0700, Ben Chobot wrote:
> I'm really, really liking the multirange types in PG14. Thank you for making
> them! Unfortunately I am struggling with how to loop over the segments of a
> multirange. There doesn't seem to be a way to convert them to arrays, and I
> can't just use plpgsql's FOREACH on one. Am I missing something obvious? It
> seems like a reasonable thing to want to do.
> 
> FWIW, my actual end goal is to take something like this:
> 
> select int8range(1,10)::int8multirange - int8range(4,6)::int8multirange;
>     ?column?
> ────────────────
>  {[1,4),[6,10)}
> 
> ...and turn it into this:
> 
> select ???;
>  int8range │ ?column?
> ───────────┼──────────
>  [1,4)     │        0
>  [6,10)    │        0

There isn't anything nice now. There was, for a brief moment, unnest()
over multiranges, but it got reverted. While it's not there, you can use
regexps to split it. I know it's ugly, but it should work, for now.

For example you can:

select * from regexp_matches(_YOUR_MULTIRANGE_::text, '[\[(][^\])]+[\])]', 'g');

I wrote more, including explanation, and ready-to-use function, in here:
https://www.depesz.com/2021/07/15/how-to-get-list-of-elements-from-multiranges/

depesz

hubert depesz lubaczewski wrote on 7/15/21 5:15 AM:
> select * from regexp_matches(_YOUR_MULTIRANGE_::text, '[\[(][^\])]+[\])]', 'g');
>
> I wrote more, including explanation, and ready-to-use function, in here:
> https://www.depesz.com/2021/07/15/how-to-get-list-of-elements-from-multiranges/
>

So ugly.... and also so effective for my use case. Thanks!