unnesting multirange data types

[Jonathan S. Katz]

Hi,

I have been exploring multirange data types using PostgreSQL 14 Beta 1.
Thus far I'm really happy with the user experience, and it has allowed
me to simplify some previously onerous queries!

I do have a question about trying to "unnest" a multirange type into its
individual ranges. For example, I have a query where I want to find the
availability over a given week. This query may look something like:

  SELECT datemultirange(daterange(CURRENT_DATE, CURRENT_DATE + 7))
    - datemultirange(daterange(CURRENT_DATE + 2, CURRENT_DATE + 4))
    as availability;

                     availability
  ---------------------------------------------------
   {[2021-06-09,2021-06-11),[2021-06-13,2021-06-16)}
  (1 row)

I would like to decompose the returned multirange into its individual
ranges, similarly to how I would "unnest" an array:

  SELECT * FROM unnest(ARRAY[1,2,3]);
   unnest
  --------
        1
        2
        3
  (3 rows)

So something like:

 SELECT unnest('{[2021-06-09,2021-06-11),
                 [2021-06-13,2021-06-16)}')::datemultirange;

           unnest
  -------------------------
   [2021-06-09,2021-06-11)
   [2021-06-13,2021-06-16)
  (2 rows)

I looked at the various functions + operators available for the
multirange types in the documentation but could not find anything that
could perform this action.

Does this functionality exist?

Thanks,

Jonathan

OpenPGP_signature
Description: OpenPGP digital signature