Skip to content

Commit 29854ee

Browse files
akorotkovakorotkov
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 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
1 parent 4daa140 commit 29854ee

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
Oid rangeOid, Oid subtype);
115115
static void makeMultirangeConstructors(const char *name, Oid namespace,
116116
Oid multirangeOid, Oid rangeOid,
117-
Oid rangeArrayOid, Oid *castFuncOid);
117+
Oid rangeArrayOid,
118+
Oid *oneArgContructorOid);
119+
static void makeMultirangeCasts(const char *name, Oid namespace,
120+
Oid multirangeOid, Oid rangeOid,
121+
Oid rangeArrayOid, Oid singleArgContructorOid);
118122
static Oid findTypeInputFunction(List *procname, Oid typeOid);
119123
static Oid findTypeOutputFunction(List *procname, Oid typeOid);
120124
static Oid findTypeReceiveFunction(List *procname, Oid typeOid);
@@ -1365,7 +1369,7 @@ DefineRange(CreateRangeStmt *stmt)
13651369
ListCell *lc;
13661370
ObjectAddress address;
13671371
ObjectAddress mltrngaddress PG_USED_FOR_ASSERTS_ONLY;
1368-
Oid castFuncOid;
1372+
Oid singleArgContructorOid;
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-
* Sets castFuncOid to the oid of the new constructor that can be used
1817+
* Sets oneArgContructorOid to the oid of the new constructor that can be used
18121818
* to cast from a range to a multirange.
18131819
*/
18141820
static void
18151821
makeMultirangeConstructors(const char *name, Oid namespace,
18161822
Oid multirangeOid, Oid rangeOid, Oid rangeArrayOid,
1817-
Oid *castFuncOid)
1823+
Oid *oneArgContructorOid)
18181824
{
18191825
ObjectAddress myself,
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+
static void
1964+
makeMultirangeCasts(const char *name, Oid namespace,
1965+
Oid multirangeOid, Oid rangeOid, Oid rangeArrayOid,
1966+
Oid singleArgContructorOid)
1967+
{
1968+
ObjectAddress myself,
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+
int i;
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+
typedef struct
2687+
{
2688+
MultirangeType *mr;
2689+
TypeCacheEntry *typcache;
2690+
int index;
2691+
} multirange_unnest_fctx;
2692+
2693+
FuncCallContext *funcctx;
2694+
multirange_unnest_fctx *fctx;
2695+
MemoryContext oldcontext;
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-
#define CATALOG_VERSION_NO 202106151
56+
#define CATALOG_VERSION_NO 202106152
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)