Skip to content

Commit 9e3c217

Browse files
akorotkovakorotkov
committed
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
1 parent ba62076 commit 9e3c217

File tree

5 files changed

+131
-0
lines changed

5 files changed

+131
-0
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -19181,6 +19181,29 @@ SELECT NULLIF(value, '(none)') ...
1918119181
<returnvalue>{[1,2)}</returnvalue>
1918219182
</para></entry>
1918319183
</row>
19184+
19185+
<row>
19186+
<entry role="func_table_entry"><para role="func_signature">
19187+
<indexterm>
19188+
<primary>unnest</primary>
19189+
<secondary>for multirange</secondary>
19190+
</indexterm>
19191+
<function>unnest</function> ( <type>anymultirange</type> )
19192+
<returnvalue>setof anyrange</returnvalue>
19193+
</para>
19194+
<para>
19195+
Expands a multirange into a set of ranges.
19196+
The ranges are read out in storage order (ascending).
19197+
</para>
19198+
<para>
19199+
<literal>unnest('{[1,2), [3,4)}'::int4multirange)</literal>
19200+
<returnvalue></returnvalue>
19201+
<programlisting>
19202+
[1,2)
19203+
[3,4)
19204+
</programlisting>
19205+
</para></entry>
19206+
</row>
1918419207
</tbody>
1918519208
</tgroup>
1918619209
</table>

‎src/backend/utils/adt/multirangetypes.c

Lines changed: 73 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -34,6 +34,7 @@
3434

3535
#include "access/tupmacs.h"
3636
#include "common/hashfn.h"
37+
#include "funcapi.h"
3738
#include "lib/stringinfo.h"
3839
#include "libpq/pqformat.h"
3940
#include "miscadmin.h"
@@ -2645,6 +2646,78 @@ range_merge_from_multirange(PG_FUNCTION_ARGS)
26452646
PG_RETURN_RANGE_P(result);
26462647
}
26472648

2649+
/* Turn multirange into a set of ranges */
2650+
Datum
2651+
multirange_unnest(PG_FUNCTION_ARGS)
2652+
{
2653+
typedef struct
2654+
{
2655+
MultirangeType *mr;
2656+
TypeCacheEntry *typcache;
2657+
int index;
2658+
} multirange_unnest_fctx;
2659+
2660+
FuncCallContext *funcctx;
2661+
multirange_unnest_fctx *fctx;
2662+
MemoryContext oldcontext;
2663+
2664+
/* stuff done only on the first call of the function */
2665+
if (SRF_IS_FIRSTCALL())
2666+
{
2667+
MultirangeType *mr;
2668+
2669+
/* create a function context for cross-call persistence */
2670+
funcctx = SRF_FIRSTCALL_INIT();
2671+
2672+
/*
2673+
* switch to memory context appropriate for multiple function calls
2674+
*/
2675+
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
2676+
2677+
/*
2678+
* Get the multirange value and detoast if needed. We can't do this
2679+
* earlier because if we have to detoast, we want the detoasted copy
2680+
* to be in multi_call_memory_ctx, so it will go away when we're done
2681+
* and not before. (If no detoast happens, we assume the originally
2682+
* passed multirange will stick around till then.)
2683+
*/
2684+
mr = PG_GETARG_MULTIRANGE_P(0);
2685+
2686+
/* allocate memory for user context */
2687+
fctx = (multirange_unnest_fctx *) palloc(sizeof(multirange_unnest_fctx));
2688+
2689+
/* initialize state */
2690+
fctx->mr = mr;
2691+
fctx->index = 0;
2692+
fctx->typcache = lookup_type_cache(MultirangeTypeGetOid(mr),
2693+
TYPECACHE_MULTIRANGE_INFO);
2694+
2695+
funcctx->user_fctx = fctx;
2696+
MemoryContextSwitchTo(oldcontext);
2697+
}
2698+
2699+
/* stuff done on every call of the function */
2700+
funcctx = SRF_PERCALL_SETUP();
2701+
fctx = funcctx->user_fctx;
2702+
2703+
if (fctx->index < fctx->mr->rangeCount)
2704+
{
2705+
RangeType *range;
2706+
2707+
range = multirange_get_range(fctx->typcache->rngtype,
2708+
fctx->mr,
2709+
fctx->index);
2710+
fctx->index++;
2711+
2712+
SRF_RETURN_NEXT(funcctx, RangeTypePGetDatum(range));
2713+
}
2714+
else
2715+
{
2716+
/* do when there is no more left */
2717+
SRF_RETURN_DONE(funcctx);
2718+
}
2719+
}
2720+
26482721
/* Hash support */
26492722

26502723
/* hash a multirange value */

‎src/include/catalog/pg_proc.dat

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10541,6 +10541,10 @@
1054110541
proname => 'range_intersect_agg', prokind => 'a', proisstrict => 'f',
1054210542
prorettype => 'anymultirange', proargtypes => 'anymultirange',
1054310543
prosrc => 'aggregate_dummy' },
10544+
{ oid => '1293', descr => 'expand multirange to set of ranges',
10545+
proname => 'unnest', prorows => '100',
10546+
proretset => 't', prorettype => 'anyrange', proargtypes => 'anymultirange',
10547+
prosrc => 'multirange_unnest' },
1054410548

1054510549
# date, time, timestamp constructors
1054610550
{ oid => '3846', descr => 'construct date',

‎src/test/regress/expected/multirangetypes.out

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -346,6 +346,23 @@ select textrange(null, null)::textmultirange;
346346
{(,)}
347347
(1 row)
348348

349+
--
350+
-- test unnest(multirange) function
351+
--
352+
select unnest(int4multirange(int4range('5', '6'), int4range('1', '2')));
353+
unnest
354+
--------
355+
[1,2)
356+
[5,6)
357+
(2 rows)
358+
359+
select unnest(textmultirange(textrange('a', 'b'), textrange('d', 'e')));
360+
unnest
361+
--------
362+
[a,b)
363+
[d,e)
364+
(2 rows)
365+
349366
--
350367
-- create some test data and test the operators
351368
--
@@ -2938,6 +2955,13 @@ LINE 1: select multirange_of_text(textrange2('a','Z'));
29382955
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
29392956
select multirange_of_text(textrange1('a','Z')) @> 'b'::text;
29402957
ERROR: range lower bound must be less than or equal to range upper bound
2958+
select unnest(multirange_of_text(textrange1('a','b'), textrange1('d','e')));
2959+
unnest
2960+
--------
2961+
[a,b)
2962+
[d,e)
2963+
(2 rows)
2964+
29412965
select _textrange1(textrange2('a','z')) @> 'b'::text;
29422966
?column?
29432967
----------

‎src/test/regress/sql/multirangetypes.sql

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -77,6 +77,12 @@ select textrange('a', 'c')::textmultirange;
7777
select textrange('a', null)::textmultirange;
7878
select textrange(null, null)::textmultirange;
7979

80+
--
81+
-- test unnest(multirange) function
82+
--
83+
select unnest(int4multirange(int4range('5', '6'), int4range('1', '2')));
84+
select unnest(textmultirange(textrange('a', 'b'), textrange('d', 'e')));
85+
8086
--
8187
-- create some test data and test the operators
8288
--
@@ -658,6 +664,7 @@ create type textrange2 as range(subtype=text, multirange_type_name=_textrange1,
658664

659665
select multirange_of_text(textrange2('a','Z')); -- should fail
660666
select multirange_of_text(textrange1('a','Z')) @> 'b'::text;
667+
select unnest(multirange_of_text(textrange1('a','b'), textrange1('d','e')));
661668
select _textrange1(textrange2('a','z')) @> 'b'::text;
662669

663670
drop type textrange1;

0 commit comments

Comments
 (0)