Support for unnest(multirange)

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), which is quite trivial.
unnest(multirange) is defined as a polymorphic procedure.

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, Tom Lane
Reviewed-by: Alvaro Herrera

2 files changed, 31 insertions, 0 deletions

diff --git a/src/test/regress/expected/multirangetypes.out b/src/test/regress/expected/multirangetypes.out
index af3ef4a258c..7f689ad41b4 100644
--- a/src/test/regress/expected/multirangetypes.out
+++ b/src/test/regress/expected/multirangetypes.out
@@ -347,6 +347,23 @@ select textrange(null, null)::textmultirange;
 (1 row)
 
 --
+-- test unnest(multirange) function
+--
+select unnest(int4multirange(int4range('5', '6'), int4range('1', '2')));
+ unnest 
+--------
+ [1,2)
+ [5,6)
+(2 rows)
+
+select unnest(textmultirange(textrange('a', 'b'), textrange('d', 'e')));
+ unnest 
+--------
+ [a,b)
+ [d,e)
+(2 rows)
+
+--
 -- create some test data and test the operators
 --
 CREATE TABLE nummultirange_test (nmr NUMMULTIRANGE);
@@ -2938,6 +2955,13 @@ LINE 1: select multirange_of_text(textrange2('a','Z'));
 HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
 select multirange_of_text(textrange1('a','Z')) @> 'b'::text;
 ERROR:  range lower bound must be less than or equal to range upper bound
+select unnest(multirange_of_text(textrange1('a','b'), textrange1('d','e')));
+ unnest 
+--------
+ [a,b)
+ [d,e)
+(2 rows)
+
 select _textrange1(textrange2('a','z')) @> 'b'::text;
  ?column? 
 ----------
diff --git a/src/test/regress/sql/multirangetypes.sql b/src/test/regress/sql/multirangetypes.sql
index b91a23e0d5a..ad50afc0f55 100644
--- a/src/test/regress/sql/multirangetypes.sql
+++ b/src/test/regress/sql/multirangetypes.sql
@@ -78,6 +78,12 @@ select textrange('a', null)::textmultirange;
 select textrange(null, null)::textmultirange;
 
 --
+-- test unnest(multirange) function
+--
+select unnest(int4multirange(int4range('5', '6'), int4range('1', '2')));
+select unnest(textmultirange(textrange('a', 'b'), textrange('d', 'e')));
+
+--
 -- create some test data and test the operators
 --
 
@@ -658,6 +664,7 @@ create type textrange2 as range(subtype=text, multirange_type_name=_textrange1,
 
 select multirange_of_text(textrange2('a','Z'));  -- should fail
 select multirange_of_text(textrange1('a','Z')) @> 'b'::text;
+select unnest(multirange_of_text(textrange1('a','b'), textrange1('d','e')));
 select _textrange1(textrange2('a','z')) @> 'b'::text;
 
 drop type textrange1;