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

Commit29854ee

Browse files
committed
Support for unnest(multirange) and cast multirange as an array of ranges
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) and cast multirange asan array of ranges, which is quite trivial.unnest(multirange) is defined as a polymorphic procedure. The catalogdescription of the cast underlying procedure is duplicated for each multirangetype because we don't have anyrangearray polymorphic type to use here.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
1 parent4daa140 commit29854ee

File tree

11 files changed

+354
-9
lines changed

11 files changed

+354
-9
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>

‎doc/src/sgml/rangetypes.sgml‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -266,6 +266,18 @@ SELECT '[4,4)'::int4range;
266266
SELECT '{}'::int4multirange;
267267
SELECT '{[3,7)}'::int4multirange;
268268
SELECT '{[3,7), [8,9)}'::int4multirange;
269+
</programlisting>
270+
</para>
271+
272+
<para>
273+
A multirange can be cast to an array of ranges of the same type.
274+
</para>
275+
276+
<para>
277+
Examples:
278+
<programlisting>
279+
SELECT '{[3,7), [8,9)}'::int4multirange::int4range[];
280+
SELECT '{[1.0,14.0), [20.0,25.0)}'::nummultirange::numrange[];
269281
</programlisting>
270282
</para>
271283

‎src/backend/commands/typecmds.c‎

Lines changed: 84 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -114,7 +114,11 @@ static void makeRangeConstructors(const char *name, Oid namespace,
114114
OidrangeOid,Oidsubtype);
115115
staticvoidmakeMultirangeConstructors(constchar*name,Oidnamespace,
116116
OidmultirangeOid,OidrangeOid,
117-
OidrangeArrayOid,Oid*castFuncOid);
117+
OidrangeArrayOid,
118+
Oid*oneArgContructorOid);
119+
staticvoidmakeMultirangeCasts(constchar*name,Oidnamespace,
120+
OidmultirangeOid,OidrangeOid,
121+
OidrangeArrayOid,OidsingleArgContructorOid);
118122
staticOidfindTypeInputFunction(List*procname,OidtypeOid);
119123
staticOidfindTypeOutputFunction(List*procname,OidtypeOid);
120124
staticOidfindTypeReceiveFunction(List*procname,OidtypeOid);
@@ -1365,7 +1369,7 @@ DefineRange(CreateRangeStmt *stmt)
13651369
ListCell*lc;
13661370
ObjectAddressaddress;
13671371
ObjectAddressmltrngaddressPG_USED_FOR_ASSERTS_ONLY;
1368-
OidcastFuncOid;
1372+
OidsingleArgContructorOid;
13691373

13701374
/* Convert list of names to a name and namespace */
13711375
typeNamespace=QualifiedNameGetCreationNamespace(stmt->typeName,
@@ -1717,10 +1721,12 @@ DefineRange(CreateRangeStmt *stmt)
17171721
makeRangeConstructors(typeName,typeNamespace,typoid,rangeSubtype);
17181722
makeMultirangeConstructors(multirangeTypeName,typeNamespace,
17191723
multirangeOid,typoid,rangeArrayOid,
1720-
&castFuncOid);
1724+
&singleArgContructorOid);
17211725

1722-
/* Create cast from the range type to its multirange type */
1723-
CastCreate(typoid,multirangeOid,castFuncOid,'e','f',DEPENDENCY_INTERNAL);
1726+
/* Create casts for this multirange type */
1727+
makeMultirangeCasts(multirangeTypeName,typeNamespace,
1728+
multirangeOid,typoid,rangeArrayOid,
1729+
singleArgContructorOid);
17241730

17251731
pfree(multirangeTypeName);
17261732
pfree(multirangeArrayName);
@@ -1808,13 +1814,13 @@ makeRangeConstructors(const char *name, Oid namespace,
18081814
* If we had an anyrangearray polymorphic type we could use it here,
18091815
* but since each type has its own constructor name there's no need.
18101816
*
1811-
* SetscastFuncOid to the oid of the new constructor that can be used
1817+
* SetsoneArgContructorOid to the oid of the new constructor that can be used
18121818
* to cast from a range to a multirange.
18131819
*/
18141820
staticvoid
18151821
makeMultirangeConstructors(constchar*name,Oidnamespace,
18161822
OidmultirangeOid,OidrangeOid,OidrangeArrayOid,
1817-
Oid*castFuncOid)
1823+
Oid*oneArgContructorOid)
18181824
{
18191825
ObjectAddressmyself,
18201826
referenced;
@@ -1904,7 +1910,7 @@ makeMultirangeConstructors(const char *name, Oid namespace,
19041910
/* ditto */
19051911
recordDependencyOn(&myself,&referenced,DEPENDENCY_INTERNAL);
19061912
pfree(argtypes);
1907-
*castFuncOid=myself.objectId;
1913+
*oneArgContructorOid=myself.objectId;
19081914

19091915
/* n-arg constructor - vararg */
19101916
argtypes=buildoidvector(&rangeArrayOid,1);
@@ -1949,6 +1955,76 @@ makeMultirangeConstructors(const char *name, Oid namespace,
19491955
pfree(parameterModes);
19501956
}
19511957

1958+
/*
1959+
* Create casts for the multirange type. The first cast makes multirange from
1960+
* range, and it's based on the single-argument constructor. The second cast
1961+
* makes an array of ranges from multirange.
1962+
*/
1963+
staticvoid
1964+
makeMultirangeCasts(constchar*name,Oidnamespace,
1965+
OidmultirangeOid,OidrangeOid,OidrangeArrayOid,
1966+
OidsingleArgContructorOid)
1967+
{
1968+
ObjectAddressmyself,
1969+
referenced;
1970+
oidvector*argtypes;
1971+
1972+
/*
1973+
* Create cast from range to multirange using the existing single-argument
1974+
* constructor procedure.
1975+
*/
1976+
CastCreate(rangeOid,multirangeOid,singleArgContructorOid,'e','f',
1977+
DEPENDENCY_INTERNAL);
1978+
1979+
referenced.classId=TypeRelationId;
1980+
referenced.objectId=multirangeOid;
1981+
referenced.objectSubId=0;
1982+
1983+
/* multirange_to_array() function */
1984+
argtypes=buildoidvector(&multirangeOid,1);
1985+
myself=ProcedureCreate("multirange_to_array",/* name */
1986+
namespace,
1987+
false,/* replace */
1988+
false,/* returns set */
1989+
rangeArrayOid,/* return type */
1990+
BOOTSTRAP_SUPERUSERID,/* proowner */
1991+
INTERNALlanguageId,/* language */
1992+
F_FMGR_INTERNAL_VALIDATOR,
1993+
"multirange_to_array",/* prosrc */
1994+
NULL,/* probin */
1995+
NULL,/* prosqlbody */
1996+
PROKIND_FUNCTION,
1997+
false,/* security_definer */
1998+
false,/* leakproof */
1999+
true,/* isStrict */
2000+
PROVOLATILE_IMMUTABLE,/* volatility */
2001+
PROPARALLEL_SAFE,/* parallel safety */
2002+
argtypes,/* parameterTypes */
2003+
PointerGetDatum(NULL),/* allParameterTypes */
2004+
PointerGetDatum(NULL),/* parameterModes */
2005+
PointerGetDatum(NULL),/* parameterNames */
2006+
NIL,/* parameterDefaults */
2007+
PointerGetDatum(NULL),/* trftypes */
2008+
PointerGetDatum(NULL),/* proconfig */
2009+
InvalidOid,/* prosupport */
2010+
1.0,/* procost */
2011+
0.0);/* prorows */
2012+
2013+
/*
2014+
* Make the multirange_to_array() function internally-dependent on the
2015+
* multirange type so that they go away silently when the type is dropped.
2016+
*/
2017+
recordDependencyOn(&myself,&referenced,DEPENDENCY_INTERNAL);
2018+
pfree(argtypes);
2019+
2020+
/*
2021+
* Create cast from multirange to the array of ranges using
2022+
* multirange_to_array() function.
2023+
*/
2024+
CastCreate(multirangeOid,rangeArrayOid,myself.objectId,'e','f',
2025+
DEPENDENCY_INTERNAL);
2026+
}
2027+
19522028
/*
19532029
* Find suitable I/O and other support functions for a type.
19542030
*

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

Lines changed: 106 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"
@@ -1068,6 +1069,39 @@ multirange_constructor0(PG_FUNCTION_ARGS)
10681069
PG_RETURN_MULTIRANGE_P(make_multirange(mltrngtypid,rangetyp,0,NULL));
10691070
}
10701071

1072+
/*
1073+
* Cast multirange to an array of ranges.
1074+
*/
1075+
Datum
1076+
multirange_to_array(PG_FUNCTION_ARGS)
1077+
{
1078+
ArrayBuildState*astate=NULL;
1079+
MultirangeType*mr=PG_GETARG_MULTIRANGE_P(0);
1080+
TypeCacheEntry*typcache;
1081+
inti;
1082+
1083+
typcache=multirange_get_typcache(fcinfo,MultirangeTypeGetOid(mr));
1084+
1085+
astate=initArrayResult(typcache->rngtype->type_id,
1086+
CurrentMemoryContext,
1087+
false);
1088+
1089+
for (i=0;i<mr->rangeCount;i++)
1090+
{
1091+
RangeType*r;
1092+
1093+
r=multirange_get_range(typcache->rngtype,mr,i);
1094+
astate=accumArrayResult(astate,
1095+
RangeTypePGetDatum(r),
1096+
false,
1097+
typcache->rngtype->type_id,
1098+
CurrentMemoryContext);
1099+
}
1100+
1101+
PG_RETURN_ARRAYTYPE_P(makeArrayResult(astate,CurrentMemoryContext));
1102+
}
1103+
1104+
10711105

10721106
/* multirange, multirange -> multirange type functions */
10731107

@@ -2645,6 +2679,78 @@ range_merge_from_multirange(PG_FUNCTION_ARGS)
26452679
PG_RETURN_RANGE_P(result);
26462680
}
26472681

2682+
/* Turn multirange into a set of ranges */
2683+
Datum
2684+
multirange_unnest(PG_FUNCTION_ARGS)
2685+
{
2686+
typedefstruct
2687+
{
2688+
MultirangeType*mr;
2689+
TypeCacheEntry*typcache;
2690+
intindex;
2691+
}multirange_unnest_fctx;
2692+
2693+
FuncCallContext*funcctx;
2694+
multirange_unnest_fctx*fctx;
2695+
MemoryContextoldcontext;
2696+
2697+
/* stuff done only on the first call of the function */
2698+
if (SRF_IS_FIRSTCALL())
2699+
{
2700+
MultirangeType*mr;
2701+
2702+
/* create a function context for cross-call persistence */
2703+
funcctx=SRF_FIRSTCALL_INIT();
2704+
2705+
/*
2706+
* switch to memory context appropriate for multiple function calls
2707+
*/
2708+
oldcontext=MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
2709+
2710+
/*
2711+
* Get the multirange value and detoast if needed. We can't do this
2712+
* earlier because if we have to detoast, we want the detoasted copy
2713+
* to be in multi_call_memory_ctx, so it will go away when we're done
2714+
* and not before. (If no detoast happens, we assume the originally
2715+
* passed multirange will stick around till then.)
2716+
*/
2717+
mr=PG_GETARG_MULTIRANGE_P(0);
2718+
2719+
/* allocate memory for user context */
2720+
fctx= (multirange_unnest_fctx*)palloc(sizeof(multirange_unnest_fctx));
2721+
2722+
/* initialize state */
2723+
fctx->mr=mr;
2724+
fctx->index=0;
2725+
fctx->typcache=lookup_type_cache(MultirangeTypeGetOid(mr),
2726+
TYPECACHE_MULTIRANGE_INFO);
2727+
2728+
funcctx->user_fctx=fctx;
2729+
MemoryContextSwitchTo(oldcontext);
2730+
}
2731+
2732+
/* stuff done on every call of the function */
2733+
funcctx=SRF_PERCALL_SETUP();
2734+
fctx=funcctx->user_fctx;
2735+
2736+
if (fctx->index<fctx->mr->rangeCount)
2737+
{
2738+
RangeType*range;
2739+
2740+
range=multirange_get_range(fctx->typcache->rngtype,
2741+
fctx->mr,
2742+
fctx->index);
2743+
fctx->index++;
2744+
2745+
SRF_RETURN_NEXT(funcctx,RangeTypePGetDatum(range));
2746+
}
2747+
else
2748+
{
2749+
/* do when there is no more left */
2750+
SRF_RETURN_DONE(funcctx);
2751+
}
2752+
}
2753+
26482754
/* Hash support */
26492755

26502756
/* 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_NO202106152
5757

5858
#endif

‎src/include/catalog/pg_cast.dat‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -548,4 +548,24 @@
548548
{ castsource => 'tstzrange', casttarget => 'tstzmultirange',
549549
castfunc => 'tstzmultirange(tstzrange)', castcontext => 'e',
550550
castmethod => 'f' },
551+
552+
# multirange to array
553+
{ castsource => 'int4multirange', casttarget => '_int4range',
554+
castfunc => 'multirange_to_array(int4multirange)', castcontext => 'e',
555+
castmethod => 'f' }
556+
{ castsource => 'int8multirange', casttarget => '_int8range',
557+
castfunc => 'multirange_to_array(int8multirange)', castcontext => 'e',
558+
castmethod => 'f' }
559+
{ castsource => 'nummultirange', casttarget => '_numrange',
560+
castfunc => 'multirange_to_array(nummultirange)', castcontext => 'e',
561+
castmethod => 'f' }
562+
{ castsource => 'datemultirange', casttarget => '_daterange',
563+
castfunc => 'multirange_to_array(datemultirange)', castcontext => 'e',
564+
castmethod => 'f' }
565+
{ castsource => 'tsmultirange', casttarget => '_tsrange',
566+
castfunc => 'multirange_to_array(tsmultirange)', castcontext => 'e',
567+
castmethod => 'f' }
568+
{ castsource => 'tstzmultirange', casttarget => '_tstzrange',
569+
castfunc => 'multirange_to_array(tstzmultirange)', castcontext => 'e',
570+
castmethod => 'f' }
551571
]

‎src/include/catalog/pg_proc.dat‎

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10537,6 +10537,29 @@
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' },
10544+
10545+
{ oid => '4544', descr => 'convert multirange to array of ranges',
10546+
proname => 'multirange_to_array', prorettype => '_int4range',
10547+
proargtypes => 'int4multirange', prosrc => 'multirange_to_array' },
10548+
{ oid => '4545', descr => 'convert multirange to array of ranges',
10549+
proname => 'multirange_to_array', prorettype => '_int8range',
10550+
proargtypes => 'int8multirange', prosrc => 'multirange_to_array' },
10551+
{ oid => '4546', descr => 'convert multirange to array of ranges',
10552+
proname => 'multirange_to_array', prorettype => '_numrange',
10553+
proargtypes => 'nummultirange', prosrc => 'multirange_to_array' },
10554+
{ oid => '4547', descr => 'convert multirange to array of ranges',
10555+
proname => 'multirange_to_array', prorettype => '_daterange',
10556+
proargtypes => 'datemultirange', prosrc => 'multirange_to_array' },
10557+
{ oid => '4548', descr => 'convert multirange to array of ranges',
10558+
proname => 'multirange_to_array', prorettype => '_tsrange',
10559+
proargtypes => 'tsmultirange', prosrc => 'multirange_to_array' },
10560+
{ oid => '4549', descr => 'convert multirange to array of ranges',
10561+
proname => 'multirange_to_array', prorettype => '_tstzrange',
10562+
proargtypes => 'tstzmultirange', prosrc => 'multirange_to_array' },
1054010563

1054110564
# date, time, timestamp constructors
1054210565
{ oid => '3846', descr => 'construct date',

0 commit comments

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