v46-0001-Add-without_portion-GiST-support-proc.patch
text/x-patch
Filename: v46-0001-Add-without_portion-GiST-support-proc.patch
Type: text/x-patch
Part: 0
Message:
Re: SQL:2011 application time
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v46-0001
Subject: Add without_portion GiST support proc
| File | + | − |
|---|---|---|
| contrib/bloom/blvalidate.c | 1 | 1 |
| doc/src/sgml/gist.sgml | 106 | 1 |
| doc/src/sgml/xindex.sgml | 7 | 1 |
| src/backend/access/brin/brin_validate.c | 4 | 4 |
| src/backend/access/gin/ginvalidate.c | 6 | 6 |
| src/backend/access/gist/gistvalidate.c | 15 | 10 |
| src/backend/access/hash/hashvalidate.c | 2 | 2 |
| src/backend/access/index/amvalidate.c | 5 | 4 |
| src/backend/access/nbtree/nbtvalidate.c | 4 | 4 |
| src/backend/access/spgist/spgvalidate.c | 4 | 4 |
| src/backend/utils/adt/multirangetypes.c | 71 | 0 |
| src/backend/utils/adt/rangetypes.c | 166 | 0 |
| src/include/access/amvalidate.h | 2 | 2 |
| src/include/access/gist.h | 2 | 1 |
| src/include/catalog/pg_amproc.dat | 6 | 0 |
| src/include/catalog/pg_proc.dat | 8 | 0 |
| src/include/utils/rangetypes.h | 2 | 0 |
| src/test/regress/expected/multirangetypes.out | 116 | 0 |
| src/test/regress/expected/rangetypes.out | 54 | 0 |
| src/test/regress/sql/multirangetypes.sql | 22 | 0 |
| src/test/regress/sql/rangetypes.sql | 10 | 0 |
From 5c9d6389b30ffcbb940429c0237a9c19c7686593 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Sat, 30 Dec 2023 23:10:59 -0800
Subject: [PATCH v46 1/8] Add without_portion GiST support proc
This new support proc is used by RESTRICT foreign keys to compute the
portion of history that was lost when the application-time bounds of a
record change. This commit defines implementations for ranges and
multiranges.
The procs return SETOF their input type and work like minus but don't
fail on splits. The results never contain empty elements.
When we implement FOR PORTION OF, we will use these procs simiarly: to
compute leftovers that weren't touched by the UPDATE/DELETE.
Author: Paul Jungwirth
---
contrib/bloom/blvalidate.c | 2 +-
doc/src/sgml/gist.sgml | 107 ++++++++++-
doc/src/sgml/xindex.sgml | 8 +-
src/backend/access/brin/brin_validate.c | 8 +-
src/backend/access/gin/ginvalidate.c | 12 +-
src/backend/access/gist/gistvalidate.c | 25 +--
src/backend/access/hash/hashvalidate.c | 4 +-
src/backend/access/index/amvalidate.c | 9 +-
src/backend/access/nbtree/nbtvalidate.c | 8 +-
src/backend/access/spgist/spgvalidate.c | 8 +-
src/backend/utils/adt/multirangetypes.c | 71 ++++++++
src/backend/utils/adt/rangetypes.c | 166 ++++++++++++++++++
src/include/access/amvalidate.h | 4 +-
src/include/access/gist.h | 3 +-
src/include/catalog/pg_amproc.dat | 6 +
src/include/catalog/pg_proc.dat | 8 +
src/include/utils/rangetypes.h | 2 +
src/test/regress/expected/multirangetypes.out | 116 ++++++++++++
src/test/regress/expected/rangetypes.out | 54 ++++++
src/test/regress/sql/multirangetypes.sql | 22 +++
src/test/regress/sql/rangetypes.sql | 10 ++
21 files changed, 613 insertions(+), 40 deletions(-)
diff --git a/contrib/bloom/blvalidate.c b/contrib/bloom/blvalidate.c
index acaf9d2be48..0efd51f2e3e 100644
--- a/contrib/bloom/blvalidate.c
+++ b/contrib/bloom/blvalidate.c
@@ -103,7 +103,7 @@ blvalidate(Oid opclassoid)
switch (procform->amprocnum)
{
case BLOOM_HASH_PROC:
- ok = check_amproc_signature(procform->amproc, INT4OID, false,
+ ok = check_amproc_signature(procform->amproc, INT4OID, false, false,
1, 1, opckeytype);
break;
case BLOOM_OPTIONS_PROC:
diff --git a/doc/src/sgml/gist.sgml b/doc/src/sgml/gist.sgml
index 638d912dc2d..1fbddf1bfb0 100644
--- a/doc/src/sgml/gist.sgml
+++ b/doc/src/sgml/gist.sgml
@@ -266,7 +266,7 @@ CREATE INDEX ON my_table USING GIST (my_inet_column inet_ops);
<para>
There are five methods that an index operator class for
- <acronym>GiST</acronym> must provide, and seven that are optional.
+ <acronym>GiST</acronym> must provide, and eight that are optional.
Correctness of the index is ensured
by proper implementation of the <function>same</function>, <function>consistent</function>
and <function>union</function> methods, while efficiency (size and speed) of the
@@ -294,6 +294,9 @@ CREATE INDEX ON my_table USING GIST (my_inet_column inet_ops);
<filename>src/include/access/stratnum.h</filename>) into strategy numbers
used by the operator class. This lets the core code look up operators for
temporal constraint indexes.
+ The optional thirteenth method <function>without_portion</function> is used by
+ <literal>RESTRICT</literal> foreign keys to compute the portion of history
+ that was lost.
</para>
<variablelist>
@@ -1235,6 +1238,108 @@ my_stratnum(PG_FUNCTION_ARGS)
</para>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><function>without_portion</function></term>
+ <listitem>
+ <para>
+ Given two values of this opclass, it subtracts the second for the first
+ and returns an array of the results.
+ </para>
+ <para>
+ This is used by temporal foreign keys to compute the part
+ of history that was lost by an update.
+ </para>
+
+ <para>
+ The <acronym>SQL</acronym> declaration of the function must look like
+ this (using <literal>my_range_without_portion</literal> as an example):
+
+<programlisting>
+CREATE OR REPLACE FUNCTION my_range_without_portion(anyrange, anyrange)
+RETURNS anyarray
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT;
+</programlisting>
+ </para>
+
+ <para>
+ The matching code in the C module could then follow this example:
+
+<programlisting>
+Datum
+my_range_without_portion(PG_FUNCTION_ARGS)
+{
+ typedef struct {
+ RangeType *rs[2];
+ int n;
+ } range_without_portion_fctx;
+
+ FuncCallContext *funcctx;
+ range_without_portion_fctx *fctx;
+ MemoryContext oldcontext;
+
+ /* stuff done only on the first call of the function */
+ if (SRF_IS_FIRSTCALL())
+ {
+ RangeType *r1;
+ RangeType *r2;
+ Oid rngtypid;
+ TypeCacheEntry *typcache;
+
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /*
+ * switch to memory context appropriate for multiple function calls
+ */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ r1 = PG_GETARG_RANGE_P(0);
+ r2 = PG_GETARG_RANGE_P(1);
+
+ /* Different types should be prevented by ANYRANGE matching rules */
+ if (RangeTypeGetOid(r1) != RangeTypeGetOid(r2))
+ elog(ERROR, "range types do not match");
+
+ /* allocate memory for user context */
+ fctx = (range_without_portion_fctx *) palloc(sizeof(range_without_portion_fctx));
+
+ /*
+ * Initialize state.
+ * We can't store the range typcache in fn_extra because the caller
+ * uses that for the SRF state.
+ */
+ rngtypid = RangeTypeGetOid(r1);
+ typcache = lookup_type_cache(rngtypid, TYPECACHE_RANGE_INFO);
+ if (typcache->rngelemtype == NULL)
+ elog(ERROR, "type %u is not a range type", rngtypid);
+ range_without_portion_internal(typcache, r1, r2, fctx->rs, &fctx->n);
+
+ funcctx->user_fctx = fctx;
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+ fctx = funcctx->user_fctx;
+
+ if (funcctx->call_cntr < fctx->n)
+ {
+ /*
+ * We must keep these on separate lines
+ * because SRF_RETURN_NEXT does call_cntr++:
+ */
+ RangeType *ret = fctx->rs[funcctx->call_cntr];
+ SRF_RETURN_NEXT(funcctx, RangeTypePGetDatum(ret));
+ }
+ else
+ /* do when there is no more left */
+ SRF_RETURN_DONE(funcctx);
+}
+</programlisting>
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
<para>
diff --git a/doc/src/sgml/xindex.sgml b/doc/src/sgml/xindex.sgml
index 3a19dab15e0..85ef539d07c 100644
--- a/doc/src/sgml/xindex.sgml
+++ b/doc/src/sgml/xindex.sgml
@@ -508,7 +508,7 @@
</table>
<para>
- GiST indexes have twelve support functions, seven of which are optional,
+ GiST indexes have thirteen support functions, eight of which are optional,
as shown in <xref linkend="xindex-gist-support-table"/>.
(For more information see <xref linkend="gist"/>.)
</para>
@@ -596,6 +596,12 @@
used by the operator class (optional)</entry>
<entry>12</entry>
</row>
+ <row>
+ <entry><function>without_portion</function></entry>
+ <entry>computes remaining duration(s) after deleting
+ second parameter from first (optional)</entry>
+ <entry>13</entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/access/brin/brin_validate.c b/src/backend/access/brin/brin_validate.c
index a49a9009626..21ed7242d6e 100644
--- a/src/backend/access/brin/brin_validate.c
+++ b/src/backend/access/brin/brin_validate.c
@@ -87,21 +87,21 @@ brinvalidate(Oid opclassoid)
switch (procform->amprocnum)
{
case BRIN_PROCNUM_OPCINFO:
- ok = check_amproc_signature(procform->amproc, INTERNALOID, true,
+ ok = check_amproc_signature(procform->amproc, INTERNALOID, false, true,
1, 1, INTERNALOID);
break;
case BRIN_PROCNUM_ADDVALUE:
- ok = check_amproc_signature(procform->amproc, BOOLOID, true,
+ ok = check_amproc_signature(procform->amproc, BOOLOID, false, true,
4, 4, INTERNALOID, INTERNALOID,
INTERNALOID, INTERNALOID);
break;
case BRIN_PROCNUM_CONSISTENT:
- ok = check_amproc_signature(procform->amproc, BOOLOID, true,
+ ok = check_amproc_signature(procform->amproc, BOOLOID, false, true,
3, 4, INTERNALOID, INTERNALOID,
INTERNALOID, INT4OID);
break;
case BRIN_PROCNUM_UNION:
- ok = check_amproc_signature(procform->amproc, BOOLOID, true,
+ ok = check_amproc_signature(procform->amproc, BOOLOID, false, true,
3, 3, INTERNALOID, INTERNALOID,
INTERNALOID);
break;
diff --git a/src/backend/access/gin/ginvalidate.c b/src/backend/access/gin/ginvalidate.c
index 6bd94e44854..662461b9c94 100644
--- a/src/backend/access/gin/ginvalidate.c
+++ b/src/backend/access/gin/ginvalidate.c
@@ -104,37 +104,37 @@ ginvalidate(Oid opclassoid)
switch (procform->amprocnum)
{
case GIN_COMPARE_PROC:
- ok = check_amproc_signature(procform->amproc, INT4OID, false,
+ ok = check_amproc_signature(procform->amproc, INT4OID, false, false,
2, 2, opckeytype, opckeytype);
break;
case GIN_EXTRACTVALUE_PROC:
/* Some opclasses omit nullFlags */
- ok = check_amproc_signature(procform->amproc, INTERNALOID, false,
+ ok = check_amproc_signature(procform->amproc, INTERNALOID, false, false,
2, 3, opcintype, INTERNALOID,
INTERNALOID);
break;
case GIN_EXTRACTQUERY_PROC:
/* Some opclasses omit nullFlags and searchMode */
- ok = check_amproc_signature(procform->amproc, INTERNALOID, false,
+ ok = check_amproc_signature(procform->amproc, INTERNALOID, false, false,
5, 7, opcintype, INTERNALOID,
INT2OID, INTERNALOID, INTERNALOID,
INTERNALOID, INTERNALOID);
break;
case GIN_CONSISTENT_PROC:
/* Some opclasses omit queryKeys and nullFlags */
- ok = check_amproc_signature(procform->amproc, BOOLOID, false,
+ ok = check_amproc_signature(procform->amproc, BOOLOID, false, false,
6, 8, INTERNALOID, INT2OID,
opcintype, INT4OID,
INTERNALOID, INTERNALOID,
INTERNALOID, INTERNALOID);
break;
case GIN_COMPARE_PARTIAL_PROC:
- ok = check_amproc_signature(procform->amproc, INT4OID, false,
+ ok = check_amproc_signature(procform->amproc, INT4OID, false, false,
4, 4, opckeytype, opckeytype,
INT2OID, INTERNALOID);
break;
case GIN_TRICONSISTENT_PROC:
- ok = check_amproc_signature(procform->amproc, CHAROID, false,
+ ok = check_amproc_signature(procform->amproc, CHAROID, false, false,
7, 7, INTERNALOID, INT2OID,
opcintype, INT4OID,
INTERNALOID, INTERNALOID,
diff --git a/src/backend/access/gist/gistvalidate.c b/src/backend/access/gist/gistvalidate.c
index 499ed8c8748..0cdbea7484d 100644
--- a/src/backend/access/gist/gistvalidate.c
+++ b/src/backend/access/gist/gistvalidate.c
@@ -106,36 +106,36 @@ gistvalidate(Oid opclassoid)
switch (procform->amprocnum)
{
case GIST_CONSISTENT_PROC:
- ok = check_amproc_signature(procform->amproc, BOOLOID, false,
+ ok = check_amproc_signature(procform->amproc, BOOLOID, false, false,
5, 5, INTERNALOID, opcintype,
INT2OID, OIDOID, INTERNALOID);
break;
case GIST_UNION_PROC:
- ok = check_amproc_signature(procform->amproc, opckeytype, false,
+ ok = check_amproc_signature(procform->amproc, opckeytype, false, false,
2, 2, INTERNALOID, INTERNALOID);
break;
case GIST_COMPRESS_PROC:
case GIST_DECOMPRESS_PROC:
case GIST_FETCH_PROC:
- ok = check_amproc_signature(procform->amproc, INTERNALOID, true,
+ ok = check_amproc_signature(procform->amproc, INTERNALOID, false, true,
1, 1, INTERNALOID);
break;
case GIST_PENALTY_PROC:
- ok = check_amproc_signature(procform->amproc, INTERNALOID, true,
+ ok = check_amproc_signature(procform->amproc, INTERNALOID, false, true,
3, 3, INTERNALOID,
INTERNALOID, INTERNALOID);
break;
case GIST_PICKSPLIT_PROC:
- ok = check_amproc_signature(procform->amproc, INTERNALOID, true,
+ ok = check_amproc_signature(procform->amproc, INTERNALOID, false, true,
2, 2, INTERNALOID, INTERNALOID);
break;
case GIST_EQUAL_PROC:
- ok = check_amproc_signature(procform->amproc, INTERNALOID, false,
+ ok = check_amproc_signature(procform->amproc, INTERNALOID, false, false,
3, 3, opckeytype, opckeytype,
INTERNALOID);
break;
case GIST_DISTANCE_PROC:
- ok = check_amproc_signature(procform->amproc, FLOAT8OID, false,
+ ok = check_amproc_signature(procform->amproc, FLOAT8OID, false, false,
5, 5, INTERNALOID, opcintype,
INT2OID, OIDOID, INTERNALOID);
break;
@@ -143,13 +143,17 @@ gistvalidate(Oid opclassoid)
ok = check_amoptsproc_signature(procform->amproc);
break;
case GIST_SORTSUPPORT_PROC:
- ok = check_amproc_signature(procform->amproc, VOIDOID, true,
+ ok = check_amproc_signature(procform->amproc, VOIDOID, false, true,
1, 1, INTERNALOID);
break;
case GIST_STRATNUM_PROC:
- ok = check_amproc_signature(procform->amproc, INT2OID, true,
+ ok = check_amproc_signature(procform->amproc, INT2OID, false, true,
1, 1, INT2OID);
break;
+ case GIST_WITHOUT_PORTION_PROC:
+ ok = check_amproc_signature(procform->amproc, opcintype, true, true,
+ 2, 2, opcintype, opcintype);
+ break;
default:
ereport(INFO,
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
@@ -271,7 +275,7 @@ gistvalidate(Oid opclassoid)
if (i == GIST_DISTANCE_PROC || i == GIST_FETCH_PROC ||
i == GIST_COMPRESS_PROC || i == GIST_DECOMPRESS_PROC ||
i == GIST_OPTIONS_PROC || i == GIST_SORTSUPPORT_PROC ||
- i == GIST_STRATNUM_PROC)
+ i == GIST_STRATNUM_PROC || i == GIST_WITHOUT_PORTION_PROC)
continue; /* optional methods */
ereport(INFO,
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
@@ -344,6 +348,7 @@ gistadjustmembers(Oid opfamilyoid,
case GIST_OPTIONS_PROC:
case GIST_SORTSUPPORT_PROC:
case GIST_STRATNUM_PROC:
+ case GIST_WITHOUT_PORTION_PROC:
/* Optional, so force it to be a soft family dependency */
op->ref_is_hard = false;
op->ref_is_family = true;
diff --git a/src/backend/access/hash/hashvalidate.c b/src/backend/access/hash/hashvalidate.c
index b54ecde9224..21ff483ef26 100644
--- a/src/backend/access/hash/hashvalidate.c
+++ b/src/backend/access/hash/hashvalidate.c
@@ -104,11 +104,11 @@ hashvalidate(Oid opclassoid)
switch (procform->amprocnum)
{
case HASHSTANDARD_PROC:
- ok = check_amproc_signature(procform->amproc, INT4OID, true,
+ ok = check_amproc_signature(procform->amproc, INT4OID, false, true,
1, 1, procform->amproclefttype);
break;
case HASHEXTENDED_PROC:
- ok = check_amproc_signature(procform->amproc, INT8OID, true,
+ ok = check_amproc_signature(procform->amproc, INT8OID, false, true,
2, 2, procform->amproclefttype, INT8OID);
break;
case HASHOPTIONS_PROC:
diff --git a/src/backend/access/index/amvalidate.c b/src/backend/access/index/amvalidate.c
index 4cf237019ad..fd7b653716a 100644
--- a/src/backend/access/index/amvalidate.c
+++ b/src/backend/access/index/amvalidate.c
@@ -149,7 +149,7 @@ identify_opfamily_groups(CatCList *oprlist, CatCList *proclist)
* In any case the function result type must match restype exactly.
*/
bool
-check_amproc_signature(Oid funcid, Oid restype, bool exact,
+check_amproc_signature(Oid funcid, Oid restype, bool retset, bool exact,
int minargs, int maxargs,...)
{
bool result = true;
@@ -163,8 +163,9 @@ check_amproc_signature(Oid funcid, Oid restype, bool exact,
elog(ERROR, "cache lookup failed for function %u", funcid);
procform = (Form_pg_proc) GETSTRUCT(tp);
- if (procform->prorettype != restype || procform->proretset ||
- procform->pronargs < minargs || procform->pronargs > maxargs)
+ if ((procform->prorettype != restype && OidIsValid(restype))
+ || procform->proretset != retset || procform->pronargs < minargs
+ || procform->pronargs > maxargs)
result = false;
va_start(ap, maxargs);
@@ -191,7 +192,7 @@ check_amproc_signature(Oid funcid, Oid restype, bool exact,
bool
check_amoptsproc_signature(Oid funcid)
{
- return check_amproc_signature(funcid, VOIDOID, true, 1, 1, INTERNALOID);
+ return check_amproc_signature(funcid, VOIDOID, false, true, 1, 1, INTERNALOID);
}
/*
diff --git a/src/backend/access/nbtree/nbtvalidate.c b/src/backend/access/nbtree/nbtvalidate.c
index b87c959a2fd..7905c389408 100644
--- a/src/backend/access/nbtree/nbtvalidate.c
+++ b/src/backend/access/nbtree/nbtvalidate.c
@@ -91,16 +91,16 @@ btvalidate(Oid opclassoid)
switch (procform->amprocnum)
{
case BTORDER_PROC:
- ok = check_amproc_signature(procform->amproc, INT4OID, true,
+ ok = check_amproc_signature(procform->amproc, INT4OID, false, true,
2, 2, procform->amproclefttype,
procform->amprocrighttype);
break;
case BTSORTSUPPORT_PROC:
- ok = check_amproc_signature(procform->amproc, VOIDOID, true,
+ ok = check_amproc_signature(procform->amproc, VOIDOID, false, true,
1, 1, INTERNALOID);
break;
case BTINRANGE_PROC:
- ok = check_amproc_signature(procform->amproc, BOOLOID, true,
+ ok = check_amproc_signature(procform->amproc, BOOLOID, false, true,
5, 5,
procform->amproclefttype,
procform->amproclefttype,
@@ -108,7 +108,7 @@ btvalidate(Oid opclassoid)
BOOLOID, BOOLOID);
break;
case BTEQUALIMAGE_PROC:
- ok = check_amproc_signature(procform->amproc, BOOLOID, true,
+ ok = check_amproc_signature(procform->amproc, BOOLOID, false, true,
1, 1, OIDOID);
break;
case BTOPTIONS_PROC:
diff --git a/src/backend/access/spgist/spgvalidate.c b/src/backend/access/spgist/spgvalidate.c
index 7d05e43720b..7d9cda73f54 100644
--- a/src/backend/access/spgist/spgvalidate.c
+++ b/src/backend/access/spgist/spgvalidate.c
@@ -109,7 +109,7 @@ spgvalidate(Oid opclassoid)
switch (procform->amprocnum)
{
case SPGIST_CONFIG_PROC:
- ok = check_amproc_signature(procform->amproc, VOIDOID, true,
+ ok = check_amproc_signature(procform->amproc, VOIDOID, false, true,
2, 2, INTERNALOID, INTERNALOID);
configIn.attType = procform->amproclefttype;
memset(&configOut, 0, sizeof(configOut));
@@ -164,11 +164,11 @@ spgvalidate(Oid opclassoid)
case SPGIST_CHOOSE_PROC:
case SPGIST_PICKSPLIT_PROC:
case SPGIST_INNER_CONSISTENT_PROC:
- ok = check_amproc_signature(procform->amproc, VOIDOID, true,
+ ok = check_amproc_signature(procform->amproc, VOIDOID, false, true,
2, 2, INTERNALOID, INTERNALOID);
break;
case SPGIST_LEAF_CONSISTENT_PROC:
- ok = check_amproc_signature(procform->amproc, BOOLOID, true,
+ ok = check_amproc_signature(procform->amproc, BOOLOID, false, true,
2, 2, INTERNALOID, INTERNALOID);
break;
case SPGIST_COMPRESS_PROC:
@@ -177,7 +177,7 @@ spgvalidate(Oid opclassoid)
ok = false;
else
ok = check_amproc_signature(procform->amproc,
- configOutLeafType, true,
+ configOutLeafType, false, true,
1, 1, procform->amproclefttype);
break;
case SPGIST_OPTIONS_PROC:
diff --git a/src/backend/utils/adt/multirangetypes.c b/src/backend/utils/adt/multirangetypes.c
index cd84ced5b48..dd7d05aa0f3 100644
--- a/src/backend/utils/adt/multirangetypes.c
+++ b/src/backend/utils/adt/multirangetypes.c
@@ -1225,6 +1225,77 @@ multirange_minus_internal(Oid mltrngtypoid, TypeCacheEntry *rangetyp,
return make_multirange(mltrngtypoid, rangetyp, range_count3, ranges3);
}
+/*
+ * multirange_without_portion - multirange minus but returning the result as a SRF,
+ * with no rows if the result would be empty.
+ */
+Datum
+multirange_without_portion(PG_FUNCTION_ARGS)
+{
+ FuncCallContext *funcctx;
+ MemoryContext oldcontext;
+
+ if (!SRF_IS_FIRSTCALL())
+ {
+ /* We never have more than one result */
+ funcctx = SRF_PERCALL_SETUP();
+ SRF_RETURN_DONE(funcctx);
+ }
+ else
+ {
+ MultirangeType *mr1;
+ MultirangeType *mr2;
+ Oid mltrngtypoid;
+ TypeCacheEntry *typcache;
+ TypeCacheEntry *rangetyp;
+ int32 range_count1;
+ int32 range_count2;
+ RangeType **ranges1;
+ RangeType **ranges2;
+ MultirangeType *mr;
+
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /*
+ * switch to memory context appropriate for multiple function calls
+ */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* get args, detoasting into multi-call memory context */
+ mr1 = PG_GETARG_MULTIRANGE_P(0);
+ mr2 = PG_GETARG_MULTIRANGE_P(1);
+
+ mltrngtypoid = MultirangeTypeGetOid(mr1);
+ typcache = lookup_type_cache(mltrngtypoid, TYPECACHE_MULTIRANGE_INFO);
+ if (typcache->rngtype == NULL)
+ elog(ERROR, "type %u is not a multirange type", mltrngtypoid);
+ rangetyp = typcache->rngtype;
+
+ if (MultirangeIsEmpty(mr1) || MultirangeIsEmpty(mr2))
+ mr = mr1;
+ else
+ {
+ multirange_deserialize(rangetyp, mr1, &range_count1, &ranges1);
+ multirange_deserialize(rangetyp, mr2, &range_count2, &ranges2);
+
+ mr = multirange_minus_internal(mltrngtypoid,
+ rangetyp,
+ range_count1,
+ ranges1,
+ range_count2,
+ ranges2);
+ }
+
+ MemoryContextSwitchTo(oldcontext);
+
+ funcctx = SRF_PERCALL_SETUP();
+ if (MultirangeIsEmpty(mr))
+ SRF_RETURN_DONE(funcctx);
+ else
+ SRF_RETURN_NEXT(funcctx, MultirangeTypePGetDatum(mr));
+ }
+}
+
/* multirange intersection */
Datum
multirange_intersect(PG_FUNCTION_ARGS)
diff --git a/src/backend/utils/adt/rangetypes.c b/src/backend/utils/adt/rangetypes.c
index 5f9fb23871a..433543ada06 100644
--- a/src/backend/utils/adt/rangetypes.c
+++ b/src/backend/utils/adt/rangetypes.c
@@ -31,6 +31,7 @@
#include "postgres.h"
#include "common/hashfn.h"
+#include "funcapi.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
@@ -39,6 +40,7 @@
#include "optimizer/clauses.h"
#include "optimizer/cost.h"
#include "optimizer/optimizer.h"
+#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/date.h"
#include "utils/lsyscache.h"
@@ -1213,6 +1215,170 @@ range_split_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeT
return false;
}
+/*
+ * range_without_portion - subtraction but as a SRF to accommodate splits,
+ * with no result rows if the result would be empty.
+ */
+Datum
+range_without_portion(PG_FUNCTION_ARGS)
+{
+ typedef struct {
+ RangeType *rs[2];
+ int n;
+ } range_without_portion_fctx;
+
+ FuncCallContext *funcctx;
+ range_without_portion_fctx *fctx;
+ MemoryContext oldcontext;
+
+ /* stuff done only on the first call of the function */
+ if (SRF_IS_FIRSTCALL())
+ {
+ RangeType *r1;
+ RangeType *r2;
+ Oid rngtypid;
+ TypeCacheEntry *typcache;
+
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /*
+ * switch to memory context appropriate for multiple function calls
+ */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ r1 = PG_GETARG_RANGE_P(0);
+ r2 = PG_GETARG_RANGE_P(1);
+
+ /* Different types should be prevented by ANYRANGE matching rules */
+ if (RangeTypeGetOid(r1) != RangeTypeGetOid(r2))
+ elog(ERROR, "range types do not match");
+
+ /* allocate memory for user context */
+ fctx = (range_without_portion_fctx *) palloc(sizeof(range_without_portion_fctx));
+
+ /*
+ * Initialize state.
+ * We can't store the range typcache in fn_extra because the caller
+ * uses that for the SRF state.
+ */
+ rngtypid = RangeTypeGetOid(r1);
+ typcache = lookup_type_cache(rngtypid, TYPECACHE_RANGE_INFO);
+ if (typcache->rngelemtype == NULL)
+ elog(ERROR, "type %u is not a range type", rngtypid);
+ range_without_portion_internal(typcache, r1, r2, fctx->rs, &fctx->n);
+
+ funcctx->user_fctx = fctx;
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+ fctx = funcctx->user_fctx;
+
+ if (funcctx->call_cntr < fctx->n)
+ {
+ /*
+ * We must keep these on separate lines
+ * because SRF_RETURN_NEXT does call_cntr++:
+ */
+ RangeType *ret = fctx->rs[funcctx->call_cntr];
+ SRF_RETURN_NEXT(funcctx, RangeTypePGetDatum(ret));
+ }
+ else
+ /* do when there is no more left */
+ SRF_RETURN_DONE(funcctx);
+}
+
+/*
+ * range_without_portion_internal - Sets outputs and outputn to the ranges
+ * remaining and their count (respectively) after subtracting r2 from r1.
+ * The array should never contain empty ranges.
+ * The outputs will be ordered. We expect that outputs is an array of
+ * RangeType pointers, already allocated with two elements.
+ */
+void
+range_without_portion_internal(TypeCacheEntry *typcache, RangeType *r1,
+ RangeType *r2, RangeType **outputs, int *outputn)
+{
+ int cmp_l1l2,
+ cmp_l1u2,
+ cmp_u1l2,
+ cmp_u1u2;
+ RangeBound lower1,
+ lower2;
+ RangeBound upper1,
+ upper2;
+ bool empty1,
+ empty2;
+
+ range_deserialize(typcache, r1, &lower1, &upper1, &empty1);
+ range_deserialize(typcache, r2, &lower2, &upper2, &empty2);
+
+ if (empty1)
+ {
+ /* if r1 is empty then r1 - r2 is empty, so return zero results */
+ *outputn = 0;
+ return;
+ }
+ else if (empty2)
+ {
+ /* r2 is empty so the result is just r1 (which we know is not empty) */
+ outputs[0] = r1;
+ *outputn = 1;
+ return;
+ }
+
+ /*
+ * Use the same logic as range_minus_internal,
+ * but support the split case
+ */
+ cmp_l1l2 = range_cmp_bounds(typcache, &lower1, &lower2);
+ cmp_l1u2 = range_cmp_bounds(typcache, &lower1, &upper2);
+ cmp_u1l2 = range_cmp_bounds(typcache, &upper1, &lower2);
+ cmp_u1u2 = range_cmp_bounds(typcache, &upper1, &upper2);
+
+ if (cmp_l1l2 < 0 && cmp_u1u2 > 0)
+ {
+ lower2.inclusive = !lower2.inclusive;
+ lower2.lower = false; /* it will become the upper bound */
+ outputs[0] = make_range(typcache, &lower1, &lower2, false, NULL);
+
+ upper2.inclusive = !upper2.inclusive;
+ upper2.lower = true; /* it will become the lower bound */
+ outputs[1] = make_range(typcache, &upper2, &upper1, false, NULL);
+
+ *outputn = 2;
+ }
+ else if (cmp_l1u2 > 0 || cmp_u1l2 < 0)
+ {
+ outputs[0] = r1;
+ *outputn = 1;
+ }
+ else if (cmp_l1l2 >= 0 && cmp_u1u2 <= 0)
+ {
+ *outputn = 0;
+ }
+ else if (cmp_l1l2 <= 0 && cmp_u1l2 >= 0 && cmp_u1u2 <= 0)
+ {
+ lower2.inclusive = !lower2.inclusive;
+ lower2.lower = false; /* it will become the upper bound */
+ outputs[0] = make_range(typcache, &lower1, &lower2, false, NULL);
+ *outputn = 1;
+ }
+ else if (cmp_l1l2 >= 0 && cmp_u1u2 >= 0 && cmp_l1u2 <= 0)
+ {
+ upper2.inclusive = !upper2.inclusive;
+ upper2.lower = true; /* it will become the lower bound */
+ outputs[0] = make_range(typcache, &upper2, &upper1, false, NULL);
+ *outputn = 1;
+ }
+ else
+ {
+ elog(ERROR, "unexpected case in range_without_portion");
+ }
+}
+
/* range -> range aggregate functions */
Datum
diff --git a/src/include/access/amvalidate.h b/src/include/access/amvalidate.h
index 43b1692b079..cea95284e94 100644
--- a/src/include/access/amvalidate.h
+++ b/src/include/access/amvalidate.h
@@ -28,8 +28,8 @@ typedef struct OpFamilyOpFuncGroup
/* Functions in access/index/amvalidate.c */
extern List *identify_opfamily_groups(CatCList *oprlist, CatCList *proclist);
-extern bool check_amproc_signature(Oid funcid, Oid restype, bool exact,
- int minargs, int maxargs,...);
+extern bool check_amproc_signature(Oid funcid, Oid restype, bool retset,
+ bool exact, int minargs, int maxargs,...);
extern bool check_amoptsproc_signature(Oid funcid);
extern bool check_amop_signature(Oid opno, Oid restype,
Oid lefttype, Oid righttype);
diff --git a/src/include/access/gist.h b/src/include/access/gist.h
index 8ccaecfa1f4..37c2f0a50ec 100644
--- a/src/include/access/gist.h
+++ b/src/include/access/gist.h
@@ -40,7 +40,8 @@
#define GIST_OPTIONS_PROC 10
#define GIST_SORTSUPPORT_PROC 11
#define GIST_STRATNUM_PROC 12
-#define GISTNProcs 12
+#define GIST_WITHOUT_PORTION_PROC 13
+#define GISTNProcs 13
/*
* Page opaque data in a GiST index page.
diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat
index b334a5fb882..ad58bb56c79 100644
--- a/src/include/catalog/pg_amproc.dat
+++ b/src/include/catalog/pg_amproc.dat
@@ -609,6 +609,9 @@
{ amprocfamily => 'gist/range_ops', amproclefttype => 'anyrange',
amprocrighttype => 'anyrange', amprocnum => '12',
amproc => 'gist_stratnum_identity' },
+{ amprocfamily => 'gist/range_ops', amproclefttype => 'anyrange',
+ amprocrighttype => 'anyrange', amprocnum => '13',
+ amproc => 'range_without_portion(anyrange,anyrange)' },
{ amprocfamily => 'gist/network_ops', amproclefttype => 'inet',
amprocrighttype => 'inet', amprocnum => '1',
amproc => 'inet_gist_consistent' },
@@ -649,6 +652,9 @@
{ amprocfamily => 'gist/multirange_ops', amproclefttype => 'anymultirange',
amprocrighttype => 'anymultirange', amprocnum => '12',
amproc => 'gist_stratnum_identity' },
+{ amprocfamily => 'gist/multirange_ops', amproclefttype => 'anymultirange',
+ amprocrighttype => 'anymultirange', amprocnum => '13',
+ amproc => 'multirange_without_portion(anymultirange,anymultirange)' },
# gin
{ amprocfamily => 'gin/array_ops', amproclefttype => 'anyarray',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index b37e8a6f882..49c4ed5ce39 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10766,6 +10766,10 @@
{ oid => '3869',
proname => 'range_minus', prorettype => 'anyrange',
proargtypes => 'anyrange anyrange', prosrc => 'range_minus' },
+{ oid => '8408', descr => 'remove portion from range',
+ proname => 'range_without_portion', prorows => '2',
+ proretset => 't', prorettype => 'anyrange',
+ proargtypes => 'anyrange anyrange', prosrc => 'range_without_portion' },
{ oid => '3870', descr => 'less-equal-greater',
proname => 'range_cmp', prorettype => 'int4',
proargtypes => 'anyrange anyrange', prosrc => 'range_cmp' },
@@ -11053,6 +11057,10 @@
{ oid => '4271',
proname => 'multirange_minus', prorettype => 'anymultirange',
proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_minus' },
+{ oid => '8406', descr => 'remove portion from multirange',
+ proname => 'multirange_without_portion', prorows => '1',
+ proretset => 't', prorettype => 'anymultirange',
+ proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_without_portion' },
{ oid => '4272',
proname => 'multirange_intersect', prorettype => 'anymultirange',
proargtypes => 'anymultirange anymultirange',
diff --git a/src/include/utils/rangetypes.h b/src/include/utils/rangetypes.h
index 50adb3c8c13..34e7790fee3 100644
--- a/src/include/utils/rangetypes.h
+++ b/src/include/utils/rangetypes.h
@@ -164,5 +164,7 @@ extern RangeType *make_empty_range(TypeCacheEntry *typcache);
extern bool range_split_internal(TypeCacheEntry *typcache, const RangeType *r1,
const RangeType *r2, RangeType **output1,
RangeType **output2);
+extern void range_without_portion_internal(TypeCacheEntry *typcache, RangeType *r1,
+ RangeType *r2, RangeType **outputs, int *outputn);
#endif /* RANGETYPES_H */
diff --git a/src/test/regress/expected/multirangetypes.out b/src/test/regress/expected/multirangetypes.out
index c6363ebeb24..11aa282ff35 100644
--- a/src/test/regress/expected/multirangetypes.out
+++ b/src/test/regress/expected/multirangetypes.out
@@ -2200,6 +2200,122 @@ SELECT nummultirange(numrange(1,2), numrange(4,5)) - nummultirange(numrange(-2,0
{[1,2),[4,5)}
(1 row)
+-- without_portion
+SELECT multirange_without_portion(nummultirange(), nummultirange());
+ multirange_without_portion
+----------------------------
+(0 rows)
+
+SELECT multirange_without_portion(nummultirange(), nummultirange(numrange(1,2)));
+ multirange_without_portion
+----------------------------
+(0 rows)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange());
+ multirange_without_portion
+----------------------------
+ {[1,2)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(3,4)), nummultirange());
+ multirange_without_portion
+----------------------------
+ {[1,2),[3,4)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange(numrange(1,2)));
+ multirange_without_portion
+----------------------------
+(0 rows)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange(numrange(2,4)));
+ multirange_without_portion
+----------------------------
+ {[1,2)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange(numrange(3,4)));
+ multirange_without_portion
+----------------------------
+ {[1,2)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(1,2)));
+ multirange_without_portion
+----------------------------
+ {[2,4)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(2,3)));
+ multirange_without_portion
+----------------------------
+ {[1,2),[3,4)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(0,8)));
+ multirange_without_portion
+----------------------------
+(0 rows)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(0,2)));
+ multirange_without_portion
+----------------------------
+ {[2,4)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,8)), nummultirange(numrange(0,2), numrange(3,4)));
+ multirange_without_portion
+----------------------------
+ {[2,3),[4,8)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,8)), nummultirange(numrange(2,3), numrange(5,null)));
+ multirange_without_portion
+----------------------------
+ {[1,2),[3,5)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(-2,0)));
+ multirange_without_portion
+----------------------------
+ {[1,2),[4,5)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(2,4)));
+ multirange_without_portion
+----------------------------
+ {[1,2),[4,5)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(3,5)));
+ multirange_without_portion
+----------------------------
+ {[1,2)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(0,9)));
+ multirange_without_portion
+----------------------------
+(0 rows)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,3), numrange(4,5)), nummultirange(numrange(2,9)));
+ multirange_without_portion
+----------------------------
+ {[1,2)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(8,9)));
+ multirange_without_portion
+----------------------------
+ {[1,2),[4,5)}
+(1 row)
+
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(-2,0), numrange(8,9)));
+ multirange_without_portion
+----------------------------
+ {[1,2),[4,5)}
+(1 row)
+
-- intersection
SELECT nummultirange() * nummultirange();
?column?
diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out
index a7cc220bf0d..ab2309e8c1d 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -481,6 +481,60 @@ select range_minus(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]'));
empty
(1 row)
+select range_without_portion('empty'::numrange, numrange(2.0, 3.0));
+ range_without_portion
+-----------------------
+(0 rows)
+
+select range_without_portion(numrange(1.1, 2.2), 'empty'::numrange);
+ range_without_portion
+-----------------------
+ [1.1,2.2)
+(1 row)
+
+select range_without_portion(numrange(1.1, 2.2), numrange(2.0, 3.0));
+ range_without_portion
+-----------------------
+ [1.1,2.0)
+(1 row)
+
+select range_without_portion(numrange(1.1, 2.2), numrange(2.2, 3.0));
+ range_without_portion
+-----------------------
+ [1.1,2.2)
+(1 row)
+
+select range_without_portion(numrange(1.1, 2.2,'[]'), numrange(2.0, 3.0));
+ range_without_portion
+-----------------------
+ [1.1,2.0)
+(1 row)
+
+select range_without_portion(numrange(1.0, 3.0), numrange(1.5, 2.0));
+ range_without_portion
+-----------------------
+ [1.0,1.5)
+ [2.0,3.0)
+(2 rows)
+
+select range_without_portion(numrange(10.1,12.2,'[]'), numrange(110.0,120.2,'(]'));
+ range_without_portion
+-----------------------
+ [10.1,12.2]
+(1 row)
+
+select range_without_portion(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]'));
+ range_without_portion
+-----------------------
+(0 rows)
+
+select range_without_portion(numrange(1.0,3.0,'[]'), numrange(1.5,2.0,'(]'));
+ range_without_portion
+-----------------------
+ [1.0,1.5]
+ (2.0,3.0]
+(2 rows)
+
select numrange(4.5, 5.5, '[]') && numrange(5.5, 6.5);
?column?
----------
diff --git a/src/test/regress/sql/multirangetypes.sql b/src/test/regress/sql/multirangetypes.sql
index 41d5524285a..0bfa71caca0 100644
--- a/src/test/regress/sql/multirangetypes.sql
+++ b/src/test/regress/sql/multirangetypes.sql
@@ -414,6 +414,28 @@ SELECT nummultirange(numrange(1,3), numrange(4,5)) - nummultirange(numrange(2,9)
SELECT nummultirange(numrange(1,2), numrange(4,5)) - nummultirange(numrange(8,9));
SELECT nummultirange(numrange(1,2), numrange(4,5)) - nummultirange(numrange(-2,0), numrange(8,9));
+-- without_portion
+SELECT multirange_without_portion(nummultirange(), nummultirange());
+SELECT multirange_without_portion(nummultirange(), nummultirange(numrange(1,2)));
+SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange());
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(3,4)), nummultirange());
+SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange(numrange(1,2)));
+SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange(numrange(2,4)));
+SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange(numrange(3,4)));
+SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(1,2)));
+SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(2,3)));
+SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(0,8)));
+SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(0,2)));
+SELECT multirange_without_portion(nummultirange(numrange(1,8)), nummultirange(numrange(0,2), numrange(3,4)));
+SELECT multirange_without_portion(nummultirange(numrange(1,8)), nummultirange(numrange(2,3), numrange(5,null)));
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(-2,0)));
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(2,4)));
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(3,5)));
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(0,9)));
+SELECT multirange_without_portion(nummultirange(numrange(1,3), numrange(4,5)), nummultirange(numrange(2,9)));
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(8,9)));
+SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(-2,0), numrange(8,9)));
+
-- intersection
SELECT nummultirange() * nummultirange();
SELECT nummultirange() * nummultirange(numrange(1,2));
diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql
index a5ecdf5372f..7fc805d9ffa 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -107,6 +107,16 @@ select numrange(1.1, 2.2,'[]') - numrange(2.0, 3.0);
select range_minus(numrange(10.1,12.2,'[]'), numrange(110.0,120.2,'(]'));
select range_minus(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]'));
+select range_without_portion('empty'::numrange, numrange(2.0, 3.0));
+select range_without_portion(numrange(1.1, 2.2), 'empty'::numrange);
+select range_without_portion(numrange(1.1, 2.2), numrange(2.0, 3.0));
+select range_without_portion(numrange(1.1, 2.2), numrange(2.2, 3.0));
+select range_without_portion(numrange(1.1, 2.2,'[]'), numrange(2.0, 3.0));
+select range_without_portion(numrange(1.0, 3.0), numrange(1.5, 2.0));
+select range_without_portion(numrange(10.1,12.2,'[]'), numrange(110.0,120.2,'(]'));
+select range_without_portion(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]'));
+select range_without_portion(numrange(1.0,3.0,'[]'), numrange(1.5,2.0,'(]'));
+
select numrange(4.5, 5.5, '[]') && numrange(5.5, 6.5);
select numrange(1.0, 2.0) << numrange(3.0, 4.0);
select numrange(1.0, 3.0,'[]') << numrange(3.0, 4.0,'[]');
--
2.42.0