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

Commit5eed8ce

Browse files
committed
Add range_minus_multi and multirange_minus_multi functions
The existing range_minus function raises an exception when the range is"split", because then the result can't be represented by a single range.For example '[0,10)'::int4range - '[4,5)' would be '[0,4)' and '[5,10)'.This commit adds new set-returning functions so that callers can getresults even in the case of splits. There is no risk of an exception formultiranges, but a set-returning function lets us handle them the sameway we handle ranges.Both functions return zero results if the subtraction would give anempty range/multirange.The main use-case for these functions is to implement UPDATE/DELETE FORPORTION OF, which must compute the application-time of "temporalleftovers": the part of history in an updated/deleted row that was notchanged. To preserve the untouched history, we will implicitly insertone record for each result returned by range/multirange_minus_multi.Using a set-returning function will also let us support user-definedtypes for application-time update/delete in the future.Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>Reviewed-by: Peter Eisentraut <peter@eisentraut.org>Reviewed-by: Chao Li <li.evan.chao@gmail.com>Discussion:https://www.postgresql.org/message-id/flat/ec498c3d-5f2b-48ec-b989-5561c8aa2024%40illuminatedcomputing.com
1 parent0dceba2 commit5eed8ce

File tree

10 files changed

+493
-1
lines changed

10 files changed

+493
-1
lines changed

‎doc/src/sgml/func/func-range.sgml‎

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -842,6 +842,29 @@
842842
<returnvalue>[1,4)</returnvalue>
843843
</para></entry>
844844
</row>
845+
846+
<row>
847+
<entry role="func_table_entry"><para role="func_signature">
848+
<indexterm>
849+
<primary>range_minus_multi</primary>
850+
</indexterm>
851+
<function>range_minus_multi</function> ( <type>anyrange</type>, <type>anyrange</type> )
852+
<returnvalue>setof anyrange</returnvalue>
853+
</para>
854+
<para>
855+
Returns the non-empty range(s) remaining after subtracting the second range from the first.
856+
One row is returned for each range, so if the second range splits the first into two parts,
857+
there will be two results. If the subtraction yields an empty range, no rows are returned.
858+
</para>
859+
<para>
860+
<literal>range_minus_multi('[0,10)'::int4range, '[3,4)'::int4range)</literal>
861+
<returnvalue></returnvalue>
862+
<programlisting>
863+
[0,3)
864+
[4,10)
865+
</programlisting>
866+
</para></entry>
867+
</row>
845868
</tbody>
846869
</tgroup>
847870
</table>
@@ -1041,6 +1064,25 @@
10411064
</programlisting>
10421065
</para></entry>
10431066
</row>
1067+
1068+
<row>
1069+
<entry role="func_table_entry"><para role="func_signature">
1070+
<indexterm>
1071+
<primary>multirange_minus_multi</primary>
1072+
</indexterm>
1073+
<function>multirange_minus_multi</function> ( <type>anymultirange</type>, <type>anymultirange</type> )
1074+
<returnvalue>setof anymultirange</returnvalue>
1075+
</para>
1076+
<para>
1077+
Returns the non-empty multirange(s) remaining after subtracting the second multirange from the first.
1078+
If the subtraction yields an empty multirange, no rows are returned.
1079+
Two rows are never returned, because a single multirange can always accommodate any result.
1080+
</para>
1081+
<para>
1082+
<literal>multirange_minus_multi('{[0,10)}'::int4multirange, '{[3,4)}'::int4multirange)</literal>
1083+
<returnvalue>{[0,3), [4,10)}</returnvalue>
1084+
</para></entry>
1085+
</row>
10441086
</tbody>
10451087
</tgroup>
10461088
</table>

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

Lines changed: 71 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1227,6 +1227,77 @@ multirange_minus_internal(Oid mltrngtypoid, TypeCacheEntry *rangetyp,
12271227
returnmake_multirange(mltrngtypoid,rangetyp,range_count3,ranges3);
12281228
}
12291229

1230+
/*
1231+
* multirange_minus_multi - like multirange_minus but returning the result as a
1232+
* SRF, with no rows if the result would be empty.
1233+
*/
1234+
Datum
1235+
multirange_minus_multi(PG_FUNCTION_ARGS)
1236+
{
1237+
FuncCallContext*funcctx;
1238+
MemoryContextoldcontext;
1239+
1240+
if (!SRF_IS_FIRSTCALL())
1241+
{
1242+
/* We never have more than one result */
1243+
funcctx=SRF_PERCALL_SETUP();
1244+
SRF_RETURN_DONE(funcctx);
1245+
}
1246+
else
1247+
{
1248+
MultirangeType*mr1;
1249+
MultirangeType*mr2;
1250+
Oidmltrngtypoid;
1251+
TypeCacheEntry*typcache;
1252+
TypeCacheEntry*rangetyp;
1253+
int32range_count1;
1254+
int32range_count2;
1255+
RangeType**ranges1;
1256+
RangeType**ranges2;
1257+
MultirangeType*mr;
1258+
1259+
funcctx=SRF_FIRSTCALL_INIT();
1260+
1261+
/*
1262+
* switch to memory context appropriate for multiple function calls
1263+
*/
1264+
oldcontext=MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
1265+
1266+
/* get args, detoasting into multi-call memory context */
1267+
mr1=PG_GETARG_MULTIRANGE_P(0);
1268+
mr2=PG_GETARG_MULTIRANGE_P(1);
1269+
1270+
mltrngtypoid=MultirangeTypeGetOid(mr1);
1271+
typcache=lookup_type_cache(mltrngtypoid,TYPECACHE_MULTIRANGE_INFO);
1272+
if (typcache->rngtype==NULL)
1273+
elog(ERROR,"type %u is not a multirange type",mltrngtypoid);
1274+
rangetyp=typcache->rngtype;
1275+
1276+
if (MultirangeIsEmpty(mr1)||MultirangeIsEmpty(mr2))
1277+
mr=mr1;
1278+
else
1279+
{
1280+
multirange_deserialize(rangetyp,mr1,&range_count1,&ranges1);
1281+
multirange_deserialize(rangetyp,mr2,&range_count2,&ranges2);
1282+
1283+
mr=multirange_minus_internal(mltrngtypoid,
1284+
rangetyp,
1285+
range_count1,
1286+
ranges1,
1287+
range_count2,
1288+
ranges2);
1289+
}
1290+
1291+
MemoryContextSwitchTo(oldcontext);
1292+
1293+
funcctx=SRF_PERCALL_SETUP();
1294+
if (MultirangeIsEmpty(mr))
1295+
SRF_RETURN_DONE(funcctx);
1296+
else
1297+
SRF_RETURN_NEXT(funcctx,MultirangeTypePGetDatum(mr));
1298+
}
1299+
}
1300+
12301301
/* multirange intersection */
12311302
Datum
12321303
multirange_intersect(PG_FUNCTION_ARGS)

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

Lines changed: 167 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -31,6 +31,7 @@
3131
#include"postgres.h"
3232

3333
#include"common/hashfn.h"
34+
#include"funcapi.h"
3435
#include"libpq/pqformat.h"
3536
#include"miscadmin.h"
3637
#include"nodes/makefuncs.h"
@@ -1216,6 +1217,172 @@ range_split_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeT
12161217
return false;
12171218
}
12181219

1220+
/*
1221+
* range_minus_multi - like range_minus but as a SRF to accommodate splits,
1222+
* with no result rows if the result would be empty.
1223+
*/
1224+
Datum
1225+
range_minus_multi(PG_FUNCTION_ARGS)
1226+
{
1227+
structrange_minus_multi_fctx
1228+
{
1229+
RangeType*rs[2];
1230+
intn;
1231+
};
1232+
1233+
FuncCallContext*funcctx;
1234+
structrange_minus_multi_fctx*fctx;
1235+
MemoryContextoldcontext;
1236+
1237+
/* stuff done only on the first call of the function */
1238+
if (SRF_IS_FIRSTCALL())
1239+
{
1240+
RangeType*r1;
1241+
RangeType*r2;
1242+
Oidrngtypid;
1243+
TypeCacheEntry*typcache;
1244+
1245+
/* create a function context for cross-call persistence */
1246+
funcctx=SRF_FIRSTCALL_INIT();
1247+
1248+
/*
1249+
* switch to memory context appropriate for multiple function calls
1250+
*/
1251+
oldcontext=MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
1252+
1253+
r1=PG_GETARG_RANGE_P(0);
1254+
r2=PG_GETARG_RANGE_P(1);
1255+
1256+
/* Different types should be prevented by ANYRANGE matching rules */
1257+
if (RangeTypeGetOid(r1)!=RangeTypeGetOid(r2))
1258+
elog(ERROR,"range types do not match");
1259+
1260+
/* allocate memory for user context */
1261+
fctx= (structrange_minus_multi_fctx*)palloc(sizeof(structrange_minus_multi_fctx));
1262+
1263+
/*
1264+
* Initialize state. We can't store the range typcache in fn_extra
1265+
* because the caller uses that for the SRF state.
1266+
*/
1267+
rngtypid=RangeTypeGetOid(r1);
1268+
typcache=lookup_type_cache(rngtypid,TYPECACHE_RANGE_INFO);
1269+
if (typcache->rngelemtype==NULL)
1270+
elog(ERROR,"type %u is not a range type",rngtypid);
1271+
range_minus_multi_internal(typcache,r1,r2,fctx->rs,&fctx->n);
1272+
1273+
funcctx->user_fctx=fctx;
1274+
MemoryContextSwitchTo(oldcontext);
1275+
}
1276+
1277+
/* stuff done on every call of the function */
1278+
funcctx=SRF_PERCALL_SETUP();
1279+
fctx=funcctx->user_fctx;
1280+
1281+
if (funcctx->call_cntr<fctx->n)
1282+
{
1283+
/*
1284+
* We must keep these on separate lines because SRF_RETURN_NEXT does
1285+
* call_cntr++:
1286+
*/
1287+
RangeType*ret=fctx->rs[funcctx->call_cntr];
1288+
1289+
SRF_RETURN_NEXT(funcctx,RangeTypePGetDatum(ret));
1290+
}
1291+
else
1292+
/* do when there is no more left */
1293+
SRF_RETURN_DONE(funcctx);
1294+
}
1295+
1296+
/*
1297+
* range_minus_multi_internal - Subtracts r2 from r1
1298+
*
1299+
* The subtraction can produce zero, one, or two resulting ranges. We return
1300+
* the results by setting outputs and outputn to the ranges remaining and their
1301+
* count (respectively). The results will never contain empty ranges and will
1302+
* be ordered. Caller should set outputs to a two-element array of RangeType
1303+
* pointers.
1304+
*/
1305+
void
1306+
range_minus_multi_internal(TypeCacheEntry*typcache,RangeType*r1,
1307+
RangeType*r2,RangeType**outputs,int*outputn)
1308+
{
1309+
intcmp_l1l2,
1310+
cmp_l1u2,
1311+
cmp_u1l2,
1312+
cmp_u1u2;
1313+
RangeBoundlower1,
1314+
lower2;
1315+
RangeBoundupper1,
1316+
upper2;
1317+
boolempty1,
1318+
empty2;
1319+
1320+
range_deserialize(typcache,r1,&lower1,&upper1,&empty1);
1321+
range_deserialize(typcache,r2,&lower2,&upper2,&empty2);
1322+
1323+
if (empty1)
1324+
{
1325+
/* if r1 is empty then r1 - r2 is empty, so return zero results */
1326+
*outputn=0;
1327+
return;
1328+
}
1329+
elseif (empty2)
1330+
{
1331+
/* r2 is empty so the result is just r1 (which we know is not empty) */
1332+
outputs[0]=r1;
1333+
*outputn=1;
1334+
return;
1335+
}
1336+
1337+
/*
1338+
* Use the same logic as range_minus_internal, but support the split case
1339+
*/
1340+
cmp_l1l2=range_cmp_bounds(typcache,&lower1,&lower2);
1341+
cmp_l1u2=range_cmp_bounds(typcache,&lower1,&upper2);
1342+
cmp_u1l2=range_cmp_bounds(typcache,&upper1,&lower2);
1343+
cmp_u1u2=range_cmp_bounds(typcache,&upper1,&upper2);
1344+
1345+
if (cmp_l1l2<0&&cmp_u1u2>0)
1346+
{
1347+
lower2.inclusive= !lower2.inclusive;
1348+
lower2.lower= false;/* it will become the upper bound */
1349+
outputs[0]=make_range(typcache,&lower1,&lower2, false,NULL);
1350+
1351+
upper2.inclusive= !upper2.inclusive;
1352+
upper2.lower= true;/* it will become the lower bound */
1353+
outputs[1]=make_range(typcache,&upper2,&upper1, false,NULL);
1354+
1355+
*outputn=2;
1356+
}
1357+
elseif (cmp_l1u2>0||cmp_u1l2<0)
1358+
{
1359+
outputs[0]=r1;
1360+
*outputn=1;
1361+
}
1362+
elseif (cmp_l1l2 >=0&&cmp_u1u2 <=0)
1363+
{
1364+
*outputn=0;
1365+
}
1366+
elseif (cmp_l1l2 <=0&&cmp_u1l2 >=0&&cmp_u1u2 <=0)
1367+
{
1368+
lower2.inclusive= !lower2.inclusive;
1369+
lower2.lower= false;/* it will become the upper bound */
1370+
outputs[0]=make_range(typcache,&lower1,&lower2, false,NULL);
1371+
*outputn=1;
1372+
}
1373+
elseif (cmp_l1l2 >=0&&cmp_u1u2 >=0&&cmp_l1u2 <=0)
1374+
{
1375+
upper2.inclusive= !upper2.inclusive;
1376+
upper2.lower= true;/* it will become the lower bound */
1377+
outputs[0]=make_range(typcache,&upper2,&upper1, false,NULL);
1378+
*outputn=1;
1379+
}
1380+
else
1381+
{
1382+
elog(ERROR,"unexpected case in range_minus_multi");
1383+
}
1384+
}
1385+
12191386
/* range -> range aggregate functions */
12201387

12211388
Datum

‎src/include/catalog/catversion.h‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/*yyyymmddN */
60-
#defineCATALOG_VERSION_NO202511181
60+
#defineCATALOG_VERSION_NO202511221
6161

6262
#endif

‎src/include/catalog/pg_proc.dat‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10939,6 +10939,10 @@
1093910939
{ oid => '3869',
1094010940
proname => 'range_minus', prorettype => 'anyrange',
1094110941
proargtypes => 'anyrange anyrange', prosrc => 'range_minus' },
10942+
{ oid => '8412', descr => 'remove portion from range',
10943+
proname => 'range_minus_multi', prorows => '2',
10944+
proretset => 't', prorettype => 'anyrange',
10945+
proargtypes => 'anyrange anyrange', prosrc => 'range_minus_multi' },
1094210946
{ oid => '3870', descr => 'less-equal-greater',
1094310947
proname => 'range_cmp', prorettype => 'int4',
1094410948
proargtypes => 'anyrange anyrange', prosrc => 'range_cmp' },
@@ -11229,6 +11233,10 @@
1122911233
{ oid => '4271',
1123011234
proname => 'multirange_minus', prorettype => 'anymultirange',
1123111235
proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_minus' },
11236+
{ oid => '8411', descr => 'remove portion from multirange',
11237+
proname => 'multirange_minus_multi', prorows => '1',
11238+
proretset => 't', prorettype => 'anymultirange',
11239+
proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_minus_multi' },
1123211240
{ oid => '4272',
1123311241
proname => 'multirange_intersect', prorettype => 'anymultirange',
1123411242
proargtypes => 'anymultirange anymultirange',

‎src/include/utils/rangetypes.h‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -164,5 +164,7 @@ extern RangeType *make_empty_range(TypeCacheEntry *typcache);
164164
externboolrange_split_internal(TypeCacheEntry*typcache,constRangeType*r1,
165165
constRangeType*r2,RangeType**output1,
166166
RangeType**output2);
167+
externvoidrange_minus_multi_internal(TypeCacheEntry*typcache,RangeType*r1,
168+
RangeType*r2,RangeType**outputs,int*outputn);
167169

168170
#endif/* RANGETYPES_H */

0 commit comments

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