Horton

unnesting multirange data types

Jonathan S. Katz <jkatz@postgresql.org>

View thread View on postgr.es
From: "Jonathan S. Katz" <jkatz@postgresql.org>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2021-06-09T18:33:56Z
Lists: 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

Commits

  1. Fix small inconsistencies in catalog definition of multirange operators

  2. Revert 29854ee8d1 due to buildfarm failures

  3. Copy-edit text for the pg_terminate_backend() "timeout" parameter.

  4. Add missing pg_description strings for pg_type entries.

  5. Fix pg_description entries for jsonb_to_record() and jsonb_to_recordset().

  6. docs: clarify JSONB operator descriptions

  7. Set procost to 10 for each of the pg_foo_is_visible() functions.

  8. Make use of LATERAL in information_schema.sequences view.

  9. Add pg_trigger_depth() function

  10. Add database comments to template0 and postgres databases, and improve