Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit244ad54

Browse files
committed
Support for unnest(multirange)
It has been spotted that multiranges lack of ability to decompose them intoindividual ranges. Subscription and proper expanded object representationrequire substantial work, and it's too late for v14. This commitprovides the implementation of unnest(multirange), which is quite trivial.unnest(multirange) is defined as a polymorphic procedure.Catversion is bumped.Reported-by: Jonathan S. KatzDiscussion:https://postgr.es/m/flat/60258efe-bd7e-4886-82e1-196e0cac5433%40postgresql.orgAuthor: Alexander KorotkovReviewed-by: Justin Pryzby, Jonathan S. Katz, Zhihong Yu, Tom LaneReviewed-by: Alvaro Herrera
1 parentd8f3b02 commit244ad54

File tree

6 files changed

+132
-1
lines changed

6 files changed

+132
-1
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+
typedefstruct
2654+
{
2655+
MultirangeType*mr;
2656+
TypeCacheEntry*typcache;
2657+
intindex;
2658+
}multirange_unnest_fctx;
2659+
2660+
FuncCallContext*funcctx;
2661+
multirange_unnest_fctx*fctx;
2662+
MemoryContextoldcontext;
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/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO202106151
56+
#defineCATALOG_VERSION_NO202107181
5757

5858
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10537,6 +10537,10 @@
1053710537
proname => 'range_intersect_agg', prokind => 'a', proisstrict => 'f',
1053810538
prorettype => 'anymultirange', proargtypes => 'anymultirange',
1053910539
prosrc => 'aggregate_dummy' },
10540+
{ oid => '1293', descr => 'expand multirange to set of ranges',
10541+
proname => 'unnest', prorows => '100',
10542+
proretset => 't', prorettype => 'anyrange', proargtypes => 'anymultirange',
10543+
prosrc => 'multirange_unnest' },
1054010544

1054110545
# date, time, timestamp constructors
1054210546
{ 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
droptype textrange1;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp