Skip to content

Commit 7ae1619

Browse files
peterepetere
committed
Add range_agg with multirange inputs
range_agg for normal ranges already existed. A lot of code can be shared. Author: Paul Jungwirth <pj@illuminatedcomputing.com> Reviewed-by: Chapman Flack <chap@anastigmatix.net> Discussion: https://www.postgresql.org/message-id/flat/007ef255-35ef-fd26-679c-f97e7a7f30c2@illuminatedcomputing.com
1 parent ff50bae commit 7ae1619

File tree

8 files changed

+156
-2
lines changed

8 files changed

+156
-2
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20007,6 +20007,11 @@ SELECT NULLIF(value, '(none)') ...
2000720007
<type>anyrange</type> )
2000820008
<returnvalue>anymultirange</returnvalue>
2000920009
</para>
20010+
<para role="func_signature">
20011+
<function>range_agg</function> ( <parameter>value</parameter>
20012+
<type>anymultirange</type> )
20013+
<returnvalue>anymultirange</returnvalue>
20014+
</para>
2001020015
<para>
2001120016
Computes the union of the non-null input values.
2001220017
</para></entry>

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

Lines changed: 61 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1361,6 +1361,9 @@ range_agg_transfn(PG_FUNCTION_ARGS)
13611361

13621362
/*
13631363
* range_agg_finalfn: use our internal array to merge touching ranges.
1364+
*
1365+
* Shared by range_agg_finalfn(anyrange) and
1366+
* multirange_agg_finalfn(anymultirange).
13641367
*/
13651368
Datum
13661369
range_agg_finalfn(PG_FUNCTION_ARGS)
@@ -1396,6 +1399,64 @@ range_agg_finalfn(PG_FUNCTION_ARGS)
13961399
PG_RETURN_MULTIRANGE_P(make_multirange(mltrngtypoid, typcache->rngtype, range_count, ranges));
13971400
}
13981401

1402+
/*
1403+
* multirange_agg_transfn: combine adjacent/overlapping multiranges.
1404+
*
1405+
* All we do here is gather the input multiranges' ranges into an array so
1406+
* that the finalfn can sort and combine them.
1407+
*/
1408+
Datum
1409+
multirange_agg_transfn(PG_FUNCTION_ARGS)
1410+
{
1411+
MemoryContext aggContext;
1412+
Oid mltrngtypoid;
1413+
TypeCacheEntry *typcache;
1414+
TypeCacheEntry *rngtypcache;
1415+
ArrayBuildState *state;
1416+
1417+
if (!AggCheckCallContext(fcinfo, &aggContext))
1418+
elog(ERROR, "multirange_agg_transfn called in non-aggregate context");
1419+
1420+
mltrngtypoid = get_fn_expr_argtype(fcinfo->flinfo, 1);
1421+
if (!type_is_multirange(mltrngtypoid))
1422+
elog(ERROR, "range_agg must be called with a multirange");
1423+
1424+
typcache = multirange_get_typcache(fcinfo, mltrngtypoid);
1425+
rngtypcache = typcache->rngtype;
1426+
1427+
if (PG_ARGISNULL(0))
1428+
state = initArrayResult(rngtypcache->type_id, aggContext, false);
1429+
else
1430+
state = (ArrayBuildState *) PG_GETARG_POINTER(0);
1431+
1432+
/* skip NULLs */
1433+
if (!PG_ARGISNULL(1))
1434+
{
1435+
MultirangeType *current;
1436+
int32 range_count;
1437+
RangeType **ranges;
1438+
1439+
current = PG_GETARG_MULTIRANGE_P(1);
1440+
multirange_deserialize(rngtypcache, current, &range_count, &ranges);
1441+
if (range_count == 0)
1442+
{
1443+
/*
1444+
* Add an empty range so we get an empty result (not a null result).
1445+
*/
1446+
accumArrayResult(state,
1447+
RangeTypePGetDatum(make_empty_range(rngtypcache)),
1448+
false, rngtypcache->type_id, aggContext);
1449+
}
1450+
else
1451+
{
1452+
for (int32 i = 0; i < range_count; i++)
1453+
accumArrayResult(state, RangeTypePGetDatum(ranges[i]), false, rngtypcache->type_id, aggContext);
1454+
}
1455+
}
1456+
1457+
PG_RETURN_POINTER(state);
1458+
}
1459+
13991460
Datum
14001461
multirange_intersect_agg_transfn(PG_FUNCTION_ARGS)
14011462
{

‎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 202203291
56+
#define CATALOG_VERSION_NO 202203301
5757

5858
#endif

‎src/include/catalog/pg_aggregate.dat

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -563,6 +563,9 @@
563563
{ aggfnoid => 'range_agg(anyrange)', aggtransfn => 'range_agg_transfn',
564564
aggfinalfn => 'range_agg_finalfn', aggfinalextra => 't',
565565
aggtranstype => 'internal' },
566+
{ aggfnoid => 'range_agg(anymultirange)', aggtransfn => 'multirange_agg_transfn',
567+
aggfinalfn => 'multirange_agg_finalfn', aggfinalextra => 't',
568+
aggtranstype => 'internal' },
566569

567570
# json
568571
{ aggfnoid => 'json_agg', aggtransfn => 'json_agg_transfn',

‎src/include/catalog/pg_proc.dat

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10688,6 +10688,17 @@
1068810688
proname => 'range_agg', prokind => 'a', proisstrict => 'f',
1068910689
prorettype => 'anymultirange', proargtypes => 'anyrange',
1069010690
prosrc => 'aggregate_dummy' },
10691+
{ oid => '8205', descr => 'aggregate transition function',
10692+
proname => 'multirange_agg_transfn', proisstrict => 'f', prorettype => 'internal',
10693+
proargtypes => 'internal anymultirange', prosrc => 'multirange_agg_transfn' },
10694+
{ oid => '8206', descr => 'aggregate final function',
10695+
proname => 'multirange_agg_finalfn', proisstrict => 'f',
10696+
prorettype => 'anymultirange', proargtypes => 'internal anymultirange',
10697+
prosrc => 'range_agg_finalfn' },
10698+
{ oid => '8207', descr => 'combine aggregate input into a multirange',
10699+
proname => 'range_agg', prokind => 'a', proisstrict => 'f',
10700+
prorettype => 'anymultirange', proargtypes => 'anymultirange',
10701+
prosrc => 'aggregate_dummy' },
1069110702
{ oid => '4388', descr => 'range aggregate by intersecting',
1069210703
proname => 'multirange_intersect_agg_transfn', prorettype => 'anymultirange',
1069310704
proargtypes => 'anymultirange anymultirange',

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

Lines changed: 61 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2784,6 +2784,67 @@ FROM (VALUES
27842784
{[a,f],[g,j)}
27852785
(1 row)
27862786

2787+
-- range_agg with multirange inputs
2788+
select range_agg(nmr) from nummultirange_test;
2789+
range_agg
2790+
-----------
2791+
{(,)}
2792+
(1 row)
2793+
2794+
select range_agg(nmr) from nummultirange_test where false;
2795+
range_agg
2796+
-----------
2797+
2798+
(1 row)
2799+
2800+
select range_agg(null::nummultirange) from nummultirange_test;
2801+
range_agg
2802+
-----------
2803+
2804+
(1 row)
2805+
2806+
select range_agg(nmr) from (values ('{}'::nummultirange)) t(nmr);
2807+
range_agg
2808+
-----------
2809+
{}
2810+
(1 row)
2811+
2812+
select range_agg(nmr) from (values ('{}'::nummultirange), ('{}'::nummultirange)) t(nmr);
2813+
range_agg
2814+
-----------
2815+
{}
2816+
(1 row)
2817+
2818+
select range_agg(nmr) from (values ('{[1,2]}'::nummultirange)) t(nmr);
2819+
range_agg
2820+
-----------
2821+
{[1,2]}
2822+
(1 row)
2823+
2824+
select range_agg(nmr) from (values ('{[1,2], [5,6]}'::nummultirange)) t(nmr);
2825+
range_agg
2826+
---------------
2827+
{[1,2],[5,6]}
2828+
(1 row)
2829+
2830+
select range_agg(nmr) from (values ('{[1,2], [2,3]}'::nummultirange)) t(nmr);
2831+
range_agg
2832+
-----------
2833+
{[1,3]}
2834+
(1 row)
2835+
2836+
select range_agg(nmr) from (values ('{[1,2]}'::nummultirange), ('{[5,6]}'::nummultirange)) t(nmr);
2837+
range_agg
2838+
---------------
2839+
{[1,2],[5,6]}
2840+
(1 row)
2841+
2842+
select range_agg(nmr) from (values ('{[1,2]}'::nummultirange), ('{[2,3]}'::nummultirange)) t(nmr);
2843+
range_agg
2844+
-----------
2845+
{[1,3]}
2846+
(1 row)
2847+
27872848
--
27882849
-- range_intersect_agg function
27892850
--

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

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -201,7 +201,8 @@ ORDER BY 1, 2;
201201
timestamp without time zone | timestamp with time zone
202202
bit | bit varying
203203
txid_snapshot | pg_snapshot
204-
(4 rows)
204+
anyrange | anymultirange
205+
(5 rows)
205206

206207
SELECT DISTINCT p1.proargtypes[2]::regtype, p2.proargtypes[2]::regtype
207208
FROM pg_proc AS p1, pg_proc AS p2

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

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -572,6 +572,18 @@ FROM (VALUES
572572
('[h,j)'::textrange)
573573
) t(r);
574574

575+
-- range_agg with multirange inputs
576+
select range_agg(nmr) from nummultirange_test;
577+
select range_agg(nmr) from nummultirange_test where false;
578+
select range_agg(null::nummultirange) from nummultirange_test;
579+
select range_agg(nmr) from (values ('{}'::nummultirange)) t(nmr);
580+
select range_agg(nmr) from (values ('{}'::nummultirange), ('{}'::nummultirange)) t(nmr);
581+
select range_agg(nmr) from (values ('{[1,2]}'::nummultirange)) t(nmr);
582+
select range_agg(nmr) from (values ('{[1,2], [5,6]}'::nummultirange)) t(nmr);
583+
select range_agg(nmr) from (values ('{[1,2], [2,3]}'::nummultirange)) t(nmr);
584+
select range_agg(nmr) from (values ('{[1,2]}'::nummultirange), ('{[5,6]}'::nummultirange)) t(nmr);
585+
select range_agg(nmr) from (values ('{[1,2]}'::nummultirange), ('{[2,3]}'::nummultirange)) t(nmr);
586+
575587
--
576588
-- range_intersect_agg function
577589
--

0 commit comments

Comments
 (0)