Support for unnest(multirange) and cast multirange as an array of ranges

It has been spotted that multiranges lack of ability to decompose them into
individual ranges.  Subscription and proper expanded object representation
require substantial work, and it's too late for v14.  This commit
provides the implementation of unnest(multirange) and cast multirange as
an array of ranges, which is quite trivial.

unnest(multirange) is defined as a polymorphic procedure.  The catalog
description of the cast underlying procedure is duplicated for each multirange
type because we don't have anyrangearray polymorphic type to use here.

Catversion is bumped.

Reported-by: Jonathan S. Katz
Discussion: https://postgr.es/m/flat/60258efe-bd7e-4886-82e1-196e0cac5433%40postgresql.org
Author: Alexander Korotkov
Reviewed-by: Justin Pryzby, Jonathan S. Katz, Zhihong Yu

3 files changed, 44 insertions, 1 deletions

diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 1b23c7c253b..7f483936dbc 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202106151
+#define CATALOG_VERSION_NO	202106152
 
 #endif
diff --git a/src/include/catalog/pg_cast.dat b/src/include/catalog/pg_cast.dat
index 67f73cb6fb2..d0b37edb605 100644
--- a/src/include/catalog/pg_cast.dat
+++ b/src/include/catalog/pg_cast.dat
@@ -548,4 +548,24 @@
 { castsource => 'tstzrange', casttarget => 'tstzmultirange',
   castfunc => 'tstzmultirange(tstzrange)', castcontext => 'e',
   castmethod => 'f' },
+
+# multirange to array
+{ castsource => 'int4multirange', casttarget => '_int4range',
+  castfunc => 'multirange_to_array(int4multirange)', castcontext => 'e',
+  castmethod => 'f' }
+{ castsource => 'int8multirange', casttarget => '_int8range',
+  castfunc => 'multirange_to_array(int8multirange)', castcontext => 'e',
+  castmethod => 'f' }
+{ castsource => 'nummultirange', casttarget => '_numrange',
+  castfunc => 'multirange_to_array(nummultirange)', castcontext => 'e',
+  castmethod => 'f' }
+{ castsource => 'datemultirange', casttarget => '_daterange',
+  castfunc => 'multirange_to_array(datemultirange)', castcontext => 'e',
+  castmethod => 'f' }
+{ castsource => 'tsmultirange', casttarget => '_tsrange',
+  castfunc => 'multirange_to_array(tsmultirange)', castcontext => 'e',
+  castmethod => 'f' }
+{ castsource => 'tstzmultirange', casttarget => '_tstzrange',
+  castfunc => 'multirange_to_array(tstzmultirange)', castcontext => 'e',
+  castmethod => 'f' }
 ]
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fde251fa4f3..5af721ee3df 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10537,6 +10537,29 @@
   proname => 'range_intersect_agg', prokind => 'a', proisstrict => 'f',
   prorettype => 'anymultirange', proargtypes => 'anymultirange',
   prosrc => 'aggregate_dummy' },
+{ oid => '1293', descr => 'expand multirange to set of ranges',
+  proname => 'unnest', prorows => '100',
+  proretset => 't', prorettype => 'anyrange', proargtypes => 'anymultirange',
+  prosrc => 'multirange_unnest' },
+
+{ oid => '4544', descr => 'convert multirange to array of ranges',
+  proname => 'multirange_to_array', prorettype => '_int4range',
+  proargtypes => 'int4multirange', prosrc => 'multirange_to_array' },
+{ oid => '4545', descr => 'convert multirange to array of ranges',
+  proname => 'multirange_to_array', prorettype => '_int8range',
+  proargtypes => 'int8multirange', prosrc => 'multirange_to_array' },
+{ oid => '4546', descr => 'convert multirange to array of ranges',
+  proname => 'multirange_to_array', prorettype => '_numrange',
+  proargtypes => 'nummultirange', prosrc => 'multirange_to_array' },
+{ oid => '4547', descr => 'convert multirange to array of ranges',
+  proname => 'multirange_to_array', prorettype => '_daterange',
+  proargtypes => 'datemultirange', prosrc => 'multirange_to_array' },
+{ oid => '4548', descr => 'convert multirange to array of ranges',
+  proname => 'multirange_to_array', prorettype => '_tsrange',
+  proargtypes => 'tsmultirange', prosrc => 'multirange_to_array' },
+{ oid => '4549', descr => 'convert multirange to array of ranges',
+  proname => 'multirange_to_array', prorettype => '_tstzrange',
+  proargtypes => 'tstzmultirange', prosrc => 'multirange_to_array' },
 
 # date, time, timestamp constructors
 { oid => '3846', descr => 'construct date',