unnesting multirange data types

Поиск
Список
Период
Сортировка
От Jonathan S. Katz
Тема unnesting multirange data types
Дата
Msg-id 60258efe-bd7e-4886-82e1-196e0cac5433@postgresql.org
обсуждение исходный текст
Ответы Re: unnesting multirange data types  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-hackers
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


Вложения

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Character expansion with ICU collations
Следующее
От: Andres Freund
Дата:
Сообщение: Re: pg14b1 stuck in lazy_scan_prune/heap_page_prune of pg_statistic