From 57d3a34cc7415ef878d1d613836e7452527b0a2d Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Sat, 21 Sep 2019 21:28:20 -0700 Subject: [PATCH v7 02/13] multirange operators and functions --- src/backend/catalog/pg_type.c | 2 - src/backend/commands/typecmds.c | 6 +- src/backend/parser/parse_coerce.c | 2 + src/backend/utils/adt/multirangetypes.c | 1389 +++++++++- src/backend/utils/adt/rangetypes.c | 236 +- src/backend/utils/fmgr/funcapi.c | 97 +- src/include/catalog/pg_aggregate.dat | 11 + src/include/catalog/pg_amproc.dat | 5 +- src/include/catalog/pg_operator.dat | 169 ++ src/include/catalog/pg_proc.dat | 185 ++ src/include/utils/multirangetypes.h | 34 + src/include/utils/rangetypes.h | 31 +- src/test/regress/expected/dependency.out | 1 + src/test/regress/expected/multirangetypes.out | 2397 +++++++++++++++++ src/test/regress/expected/opr_sanity.out | 4 +- src/test/regress/expected/rangetypes.out | 38 +- src/test/regress/expected/sanity_check.out | 2 + src/test/regress/sql/multirangetypes.sql | 607 +++++ src/test/regress/sql/rangetypes.sql | 13 + 19 files changed, 5095 insertions(+), 134 deletions(-) diff --git a/src/backend/catalog/pg_type.c b/src/backend/catalog/pg_type.c index 8a85d3412a..73b8ace4c5 100644 --- a/src/backend/catalog/pg_type.c +++ b/src/backend/catalog/pg_type.c @@ -876,7 +876,6 @@ makeMultirangeTypeName(const char *rangeTypeName, Oid typeNamespace) char mltrng[NAMEDATALEN]; char *mltrngunique = (char *) palloc(NAMEDATALEN); int namelen = strlen(rangeTypeName); - int rangelen; char *rangestr; int rangeoffset; int underscores; @@ -892,7 +891,6 @@ makeMultirangeTypeName(const char *rangeTypeName, Oid typeNamespace) if (rangestr) { rangeoffset = rangestr - rangeTypeName; - rangelen = strlen(rangestr); strlcpy(mltrng + rangeoffset, "multi", NAMEDATALEN - rangeoffset); strlcpy(mltrng + rangeoffset + 5, rangestr, NAMEDATALEN - rangeoffset - 5); namelen += 5; diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 14a6857062..7a440cafd1 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -1783,13 +1783,11 @@ makeMultirangeConstructors(const char *name, Oid namespace, "multirange_constructor1"}; static const int pronargs[2] = {0, 1}; - Oid constructorArgTypes[0]; + Oid constructorArgTypes = rangeArrayOid; ObjectAddress myself, referenced; int i; - constructorArgTypes[0] = rangeArrayOid; - Datum allParamTypes[1] = {ObjectIdGetDatum(rangeArrayOid)}; ArrayType *allParameterTypes = construct_array(allParamTypes, 1, OIDOID, sizeof(Oid), true, 'i'); @@ -1808,7 +1806,7 @@ makeMultirangeConstructors(const char *name, Oid namespace, { oidvector *constructorArgTypesVector; - constructorArgTypesVector = buildoidvector(constructorArgTypes, + constructorArgTypesVector = buildoidvector(&constructorArgTypes, pronargs[i]); myself = ProcedureCreate(name, /* name: same as multirange type */ diff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_coerce.c index 4c7a5f7fbe..49c75dc12d 100644 --- a/src/backend/parser/parse_coerce.c +++ b/src/backend/parser/parse_coerce.c @@ -1918,6 +1918,8 @@ enforce_generic_type_consistency(const Oid *actual_arg_types, format_type_be(range_typeid), format_type_be(elem_typeid)))); } + else + range_typelem = InvalidOid; } /* Get the range type based on the multirange type, if we have one */ diff --git a/src/backend/utils/adt/multirangetypes.c b/src/backend/utils/adt/multirangetypes.c index 5323a6a635..7717acb3b1 100644 --- a/src/backend/utils/adt/multirangetypes.c +++ b/src/backend/utils/adt/multirangetypes.c @@ -91,7 +91,6 @@ multirange_in(PG_FUNCTION_ARGS) Oid mltrngtypoid = PG_GETARG_OID(1); Oid typmod = PG_GETARG_INT32(2); TypeCacheEntry *rangetyp; - Oid rngtypoid; int32 ranges_seen = 0; int32 range_count = 0; int32 range_capacity = 8; @@ -101,13 +100,12 @@ multirange_in(PG_FUNCTION_ARGS) MultirangeType *ret; MultirangeParseState parse_state; const char *ptr = input_str; - const char *range_str; + const char *range_str = NULL; int32 range_str_len; char *range_str_copy; cache = get_multirange_io_data(fcinfo, mltrngtypoid, IOFunc_input); rangetyp = cache->typcache->rngtype; - rngtypoid = rangetyp->type_id; /* consume whitespace */ while (*ptr != '\0' && isspace((unsigned char) *ptr)) @@ -351,9 +349,12 @@ multirange_send(PG_FUNCTION_ARGS) for (i = 0; i < range_count; i++) { Datum range = RangeTypePGetDatum(ranges[i]); + uint32 range_len; + char *range_data; + range = PointerGetDatum(SendFunctionCall(&cache->proc, range)); - uint32 range_len = VARSIZE(range) - VARHDRSZ; - char *range_data = VARDATA(range); + range_len = VARSIZE(range) - VARHDRSZ; + range_data = VARDATA(range); pq_sendint32(buf, range_len); pq_sendbytes(buf, range_data, range_len); @@ -726,6 +727,829 @@ multirange_constructor0(PG_FUNCTION_ARGS) } +/* multirange, multirange -> multirange type functions */ + +/* multirange union */ +Datum +range_union_range(PG_FUNCTION_ARGS) +{ + RangeType *r1 = PG_GETARG_RANGE_P(0); + RangeType *r2 = PG_GETARG_RANGE_P(1); + Oid mltrngtypoid = get_fn_expr_rettype(fcinfo->flinfo); + TypeCacheEntry *typcache = multirange_get_typcache(fcinfo, mltrngtypoid); + MultirangeType *mr = make_multirange(mltrngtypoid, typcache->rngtype, 1, &r2); + + PG_RETURN_MULTIRANGE_P(range_union_multirange_internal(typcache, r1, mr)); +} + +Datum +range_union_multirange(PG_FUNCTION_ARGS) +{ + RangeType *r = PG_GETARG_RANGE_P(0); + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(1); + TypeCacheEntry *typcache; + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr)); + + PG_RETURN_MULTIRANGE_P(range_union_multirange_internal(typcache, r, mr)); +} + +Datum +multirange_union_range(PG_FUNCTION_ARGS) +{ + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(0); + RangeType *r = PG_GETARG_RANGE_P(1); + TypeCacheEntry *typcache; + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr)); + + PG_RETURN_MULTIRANGE_P(range_union_multirange_internal(typcache, r, mr)); +} + +Datum +multirange_union_multirange(PG_FUNCTION_ARGS) +{ + MultirangeType *mr1 = PG_GETARG_MULTIRANGE_P(0); + MultirangeType *mr2 = PG_GETARG_MULTIRANGE_P(1); + TypeCacheEntry *typcache; + int32 range_count1; + int32 range_count2; + int32 range_count3; + RangeType **ranges1; + RangeType **ranges2; + RangeType **ranges3; + + if (MultirangeIsEmpty(mr1)) + PG_RETURN_MULTIRANGE_P(mr2); + if (MultirangeIsEmpty(mr2)) + PG_RETURN_MULTIRANGE_P(mr1); + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr1)); + + multirange_deserialize(mr1, &range_count1, &ranges1); + multirange_deserialize(mr2, &range_count2, &ranges2); + + range_count3 = range_count1 + range_count2; + ranges3 = palloc0(range_count3 * sizeof(RangeType *)); + memcpy(ranges3, ranges1, range_count1 * sizeof(RangeType *)); + memcpy(ranges3 + range_count1, ranges2, range_count2 * sizeof(RangeType *)); + PG_RETURN_MULTIRANGE_P(make_multirange(typcache->type_id, typcache->rngtype, + range_count3, ranges3)); +} + +MultirangeType * +range_union_multirange_internal(TypeCacheEntry *typcache, RangeType *r, + MultirangeType * mr) +{ + int32 range_count; + RangeType **ranges1; + RangeType **ranges2; + + if (RangeIsEmpty(r)) + return mr; + if (MultirangeIsEmpty(mr)) + return make_multirange(typcache->type_id, typcache->rngtype, 1, &r); + + multirange_deserialize(mr, &range_count, &ranges1); + + ranges2 = palloc0((range_count + 1) * sizeof(RangeType *)); + + memcpy(ranges2, ranges1, range_count * sizeof(RangeType *)); + ranges2[range_count] = r; + return make_multirange(typcache->type_id, typcache->rngtype, range_count + 1, ranges2); +} + +/* multirange minus */ +Datum +range_minus_range(PG_FUNCTION_ARGS) +{ + RangeType *r1 = PG_GETARG_RANGE_P(0); + RangeType *r2 = PG_GETARG_RANGE_P(1); + Oid mltrngtypoid = get_fn_expr_rettype(fcinfo->flinfo); + TypeCacheEntry *typcache; + TypeCacheEntry *rangetyp; + + typcache = multirange_get_typcache(fcinfo, mltrngtypoid); + rangetyp = typcache->rngtype; + + if (RangeIsEmpty(r1) || RangeIsEmpty(r2)) + PG_RETURN_MULTIRANGE_P(make_multirange(mltrngtypoid, rangetyp, 1, &r1)); + + PG_RETURN_MULTIRANGE_P(multirange_minus_multirange_internal(mltrngtypoid, + rangetyp, + 1, + &r1, + 1, + &r2)); +} + +Datum +range_minus_multirange(PG_FUNCTION_ARGS) +{ + RangeType *r = PG_GETARG_RANGE_P(0); + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(1); + Oid mltrngtypoid = MultirangeTypeGetOid(mr); + TypeCacheEntry *typcache; + TypeCacheEntry *rangetyp; + int32 range_count; + RangeType **ranges; + + typcache = multirange_get_typcache(fcinfo, mltrngtypoid); + rangetyp = typcache->rngtype; + + if (RangeIsEmpty(r) || MultirangeIsEmpty(mr)) + PG_RETURN_MULTIRANGE_P(make_multirange(mltrngtypoid, rangetyp, 1, &r)); + + multirange_deserialize(mr, &range_count, &ranges); + + PG_RETURN_MULTIRANGE_P(multirange_minus_multirange_internal(mltrngtypoid, + rangetyp, + 1, + &r, + range_count, + ranges)); +} + +Datum +multirange_minus_range(PG_FUNCTION_ARGS) +{ + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(0); + RangeType *r = PG_GETARG_RANGE_P(1); + Oid mltrngtypoid = MultirangeTypeGetOid(mr); + TypeCacheEntry *typcache; + TypeCacheEntry *rangetyp; + int32 range_count; + RangeType **ranges; + + typcache = multirange_get_typcache(fcinfo, mltrngtypoid); + rangetyp = typcache->rngtype; + + if (MultirangeIsEmpty(mr) || RangeIsEmpty(r)) + PG_RETURN_MULTIRANGE_P(mr); + + multirange_deserialize(mr, &range_count, &ranges); + + PG_RETURN_MULTIRANGE_P(multirange_minus_multirange_internal(mltrngtypoid, + rangetyp, + range_count, + ranges, + 1, + &r)); +} + +Datum +multirange_minus_multirange(PG_FUNCTION_ARGS) +{ + MultirangeType *mr1 = PG_GETARG_MULTIRANGE_P(0); + MultirangeType *mr2 = PG_GETARG_MULTIRANGE_P(1); + Oid mltrngtypoid = MultirangeTypeGetOid(mr1); + TypeCacheEntry *typcache; + TypeCacheEntry *rangetyp; + int32 range_count1; + int32 range_count2; + RangeType **ranges1; + RangeType **ranges2; + + typcache = multirange_get_typcache(fcinfo, mltrngtypoid); + rangetyp = typcache->rngtype; + + if (MultirangeIsEmpty(mr1) || MultirangeIsEmpty(mr2)) + PG_RETURN_MULTIRANGE_P(mr1); + + multirange_deserialize(mr1, &range_count1, &ranges1); + multirange_deserialize(mr2, &range_count2, &ranges2); + + PG_RETURN_MULTIRANGE_P(multirange_minus_multirange_internal(mltrngtypoid, + rangetyp, + range_count1, + ranges1, + range_count2, + ranges2)); +} + +MultirangeType * +multirange_minus_multirange_internal(Oid mltrngtypoid, TypeCacheEntry *rangetyp, + int32 range_count1, RangeType **ranges1, int32 range_count2, RangeType **ranges2) +{ + RangeType *r1; + RangeType *r2; + RangeType **ranges3; + int32 range_count3; + int32 i1; + int32 i2; + + /* + * Worst case: every range in ranges1 makes a different cut to some range + * in ranges2. + */ + ranges3 = palloc0((range_count1 + range_count2) * sizeof(RangeType *)); + range_count3 = 0; + + /* + * For each range in mr1, keep subtracting until it's gone or the ranges + * in mr2 have passed it. After a subtraction we assign what's left back + * to r1. The parallel progress through mr1 and mr2 is similar to + * multirange_overlaps_multirange_internal. + */ + r2 = ranges2[0]; + for (i1 = 0, i2 = 0; i1 < range_count1; i1++) + { + r1 = ranges1[i1]; + + /* Discard r2s while r2 << r1 */ + while (r2 != NULL && range_before_internal(rangetyp, r2, r1)) + { + r2 = ++i2 >= range_count2 ? NULL : ranges2[i2]; + } + + while (r2 != NULL) + { + if (range_split_internal(rangetyp, r1, r2, &ranges3[range_count3], &r1)) + { + /* + * If r2 takes a bite out of the middle of r1, we need two + * outputs + */ + range_count3++; + r2 = ++i2 >= range_count2 ? NULL : ranges2[i2]; + + } + else if (range_overlaps_internal(rangetyp, r1, r2)) + { + /* + * If r2 overlaps r1, replace r1 with r1 - r2. + */ + r1 = range_minus_internal(rangetyp, r1, r2); + + /* + * If r2 goes past r1, then we need to stay with it, in case + * it hits future r1s. Otherwise we need to keep r1, in case + * future r2s hit it. Since we already subtracted, there's no + * point in using the overright/overleft calls. + */ + if (RangeIsEmpty(r1) || range_before_internal(rangetyp, r1, r2)) + break; + else + r2 = ++i2 >= range_count2 ? NULL : ranges2[i2]; + + } + else + { + /* + * This and all future r2s are past r1, so keep them. Also + * assign whatever is left of r1 to the result. + */ + break; + } + } + + /* + * Nothing else can remove anything from r1, so keep it. Even if r1 is + * empty here, make_multirange will remove it. + */ + ranges3[range_count3++] = r1; + } + + return make_multirange(mltrngtypoid, rangetyp, range_count3, ranges3); +} + +/* multirange intersection */ +Datum +range_intersect_range(PG_FUNCTION_ARGS) +{ + RangeType *r1 = PG_GETARG_RANGE_P(0); + RangeType *r2 = PG_GETARG_RANGE_P(1); + Oid mltrngtypoid = get_fn_expr_rettype(fcinfo->flinfo); + TypeCacheEntry *typcache; + TypeCacheEntry *rangetyp; + + typcache = multirange_get_typcache(fcinfo, mltrngtypoid); + rangetyp = typcache->rngtype; + + if (RangeIsEmpty(r1) || RangeIsEmpty(r2)) + PG_RETURN_MULTIRANGE_P(make_empty_multirange(mltrngtypoid, rangetyp)); + + PG_RETURN_MULTIRANGE_P(multirange_intersect_multirange_internal(mltrngtypoid, + rangetyp, + 1, + &r1, + 1, + &r2)); +} + +Datum +range_intersect_multirange(PG_FUNCTION_ARGS) +{ + RangeType *r1 = PG_GETARG_RANGE_P(0); + MultirangeType *mr2 = PG_GETARG_MULTIRANGE_P(1); + Oid mltrngtypoid = MultirangeTypeGetOid(mr2); + TypeCacheEntry *typcache; + TypeCacheEntry *rangetyp; + int32 range_count2; + RangeType **ranges2; + + typcache = multirange_get_typcache(fcinfo, mltrngtypoid); + rangetyp = typcache->rngtype; + + if (RangeIsEmpty(r1) || MultirangeIsEmpty(mr2)) + PG_RETURN_MULTIRANGE_P(make_empty_multirange(mltrngtypoid, rangetyp)); + + multirange_deserialize(mr2, &range_count2, &ranges2); + + PG_RETURN_MULTIRANGE_P(multirange_intersect_multirange_internal(mltrngtypoid, + rangetyp, + 1, + &r1, + range_count2, + ranges2)); +} + +Datum +multirange_intersect_range(PG_FUNCTION_ARGS) +{ + MultirangeType *mr1 = PG_GETARG_MULTIRANGE_P(0); + RangeType *r2 = PG_GETARG_RANGE_P(1); + Oid mltrngtypoid = MultirangeTypeGetOid(mr1); + TypeCacheEntry *typcache; + TypeCacheEntry *rangetyp; + int32 range_count1; + RangeType **ranges1; + + typcache = multirange_get_typcache(fcinfo, mltrngtypoid); + rangetyp = typcache->rngtype; + + if (MultirangeIsEmpty(mr1) || RangeIsEmpty(r2)) + PG_RETURN_MULTIRANGE_P(make_empty_multirange(mltrngtypoid, rangetyp)); + + multirange_deserialize(mr1, &range_count1, &ranges1); + + PG_RETURN_MULTIRANGE_P(multirange_intersect_multirange_internal(mltrngtypoid, + rangetyp, + range_count1, + ranges1, + 1, + &r2)); +} + +Datum +multirange_intersect_multirange(PG_FUNCTION_ARGS) +{ + MultirangeType *mr1 = PG_GETARG_MULTIRANGE_P(0); + MultirangeType *mr2 = PG_GETARG_MULTIRANGE_P(1); + Oid mltrngtypoid = MultirangeTypeGetOid(mr1); + TypeCacheEntry *typcache; + TypeCacheEntry *rangetyp; + int32 range_count1; + int32 range_count2; + RangeType **ranges1; + RangeType **ranges2; + + typcache = multirange_get_typcache(fcinfo, mltrngtypoid); + rangetyp = typcache->rngtype; + + if (MultirangeIsEmpty(mr1) || MultirangeIsEmpty(mr2)) + PG_RETURN_MULTIRANGE_P(make_empty_multirange(mltrngtypoid, rangetyp)); + + multirange_deserialize(mr1, &range_count1, &ranges1); + multirange_deserialize(mr2, &range_count2, &ranges2); + + PG_RETURN_MULTIRANGE_P(multirange_intersect_multirange_internal(mltrngtypoid, + rangetyp, + range_count1, + ranges1, + range_count2, + ranges2)); +} + +MultirangeType * +multirange_intersect_multirange_internal(Oid mltrngtypoid, TypeCacheEntry *rangetyp, + int32 range_count1, RangeType **ranges1, + int32 range_count2, RangeType **ranges2) +{ + RangeType *r1; + RangeType *r2; + RangeType **ranges3; + int32 range_count3; + int32 i1; + int32 i2; + + if (range_count1 == 0 || range_count2 == 0) + return make_multirange(mltrngtypoid, rangetyp, 0, NULL); + + /* + * Worst case is a stitching pattern like this: + * + * mr1: --- --- --- --- + * mr2: --- --- --- + * mr3: - - - - - - + * + * That seems to be range_count1 + range_count2 - 1, but one extra won't + * hurt. + */ + ranges3 = palloc0((range_count1 + range_count2) * sizeof(RangeType *)); + range_count3 = 0; + + /* + * For each range in mr1, keep intersecting until the ranges in mr2 have + * passed it. The parallel progress through mr1 and mr2 is similar to + * multirange_minus_multirange_internal, but we don't have to assign back + * to r1. + */ + r2 = ranges2[0]; + for (i1 = 0, i2 = 0; i1 < range_count1; i1++) + { + r1 = ranges1[i1]; + + /* Discard r2s while r2 << r1 */ + while (r2 != NULL && range_before_internal(rangetyp, r2, r1)) + { + r2 = ++i2 >= range_count2 ? NULL : ranges2[i2]; + } + + while (r2 != NULL) + { + if (range_overlaps_internal(rangetyp, r1, r2)) + { + /* Keep the overlapping part */ + ranges3[range_count3++] = range_intersect_internal(rangetyp, r1, r2); + + /* If we "used up" all of r2, go to the next one... */ + if (range_overleft_internal(rangetyp, r2, r1)) + r2 = ++i2 >= range_count2 ? NULL : ranges2[i2]; + + /* ...otherwise go to the next r1 */ + else + break; + } + else + /* We're past r1, so move to the next one */ + break; + } + + /* If we're out of r2s, there can be no more intersections */ + if (r2 == NULL) + break; + } + + return make_multirange(mltrngtypoid, rangetyp, range_count3, ranges3); +} + +/* + * range_agg_transfn: combine adjacent/overlapping ranges. + * + * All we do here is gather the input ranges into an array + * so that the finalfn can sort and combine them. + */ +Datum +range_agg_transfn(PG_FUNCTION_ARGS) +{ + MemoryContext aggContext; + Oid rngtypoid; + ArrayBuildState *state; + + if (!AggCheckCallContext(fcinfo, &aggContext)) + elog(ERROR, "range_agg_transfn called in non-aggregate context"); + + rngtypoid = get_fn_expr_argtype(fcinfo->flinfo, 1); + if (!type_is_range(rngtypoid)) + ereport(ERROR, (errmsg("range_agg must be called with a range"))); + + if (PG_ARGISNULL(0)) + state = initArrayResult(rngtypoid, aggContext, false); + else + state = (ArrayBuildState *) PG_GETARG_POINTER(0); + + /* skip NULLs */ + if (!PG_ARGISNULL(1)) + accumArrayResult(state, PG_GETARG_DATUM(1), false, rngtypoid, aggContext); + + PG_RETURN_POINTER(state); +} + +/* + * range_agg_finalfn: use our internal array to merge touching ranges. + */ +Datum +range_agg_finalfn(PG_FUNCTION_ARGS) +{ + MemoryContext aggContext; + Oid mltrngtypoid; + TypeCacheEntry *typcache; + ArrayBuildState *state; + int32 range_count; + RangeType **ranges; + int i; + + if (!AggCheckCallContext(fcinfo, &aggContext)) + elog(ERROR, "range_agg_finalfn called in non-aggregate context"); + + state = PG_ARGISNULL(0) ? NULL : (ArrayBuildState *) PG_GETARG_POINTER(0); + if (state == NULL) + /* This shouldn't be possible, but just in case.... */ + PG_RETURN_NULL(); + + /* Also return NULL if we had zero inputs, like other aggregates */ + range_count = state->nelems; + if (range_count == 0) + PG_RETURN_NULL(); + + mltrngtypoid = get_fn_expr_rettype(fcinfo->flinfo); + typcache = multirange_get_typcache(fcinfo, mltrngtypoid); + + ranges = palloc0(range_count * sizeof(RangeType *)); + for (i = 0; i < range_count; i++) + ranges[i] = DatumGetRangeTypeP(state->dvalues[i]); + + PG_RETURN_MULTIRANGE_P(make_multirange(mltrngtypoid, typcache->rngtype, range_count, ranges)); +} + +Datum +multirange_intersect_agg_transfn(PG_FUNCTION_ARGS) +{ + MemoryContext aggContext; + Oid mltrngtypoid; + TypeCacheEntry *typcache; + MultirangeType *result; + MultirangeType *current; + int32 range_count1; + int32 range_count2; + RangeType **ranges1; + RangeType **ranges2; + + if (!AggCheckCallContext(fcinfo, &aggContext)) + elog(ERROR, "multirange_intersect_agg_transfn called in non-aggregate context"); + + mltrngtypoid = get_fn_expr_argtype(fcinfo->flinfo, 1); + if (!type_is_multirange(mltrngtypoid)) + ereport(ERROR, (errmsg("range_intersect_agg must be called with a multirange"))); + + typcache = multirange_get_typcache(fcinfo, mltrngtypoid); + + /* strictness ensures these are non-null */ + result = PG_GETARG_MULTIRANGE_P(0); + current = PG_GETARG_MULTIRANGE_P(1); + + multirange_deserialize(result, &range_count1, &ranges1); + multirange_deserialize(current, &range_count2, &ranges2); + + result = multirange_intersect_multirange_internal(mltrngtypoid, + typcache->rngtype, + range_count1, + ranges1, + range_count2, + ranges2); + PG_RETURN_RANGE_P(result); +} + + +/* multirange -> element type functions */ + +/* extract lower bound value */ +Datum +multirange_lower(PG_FUNCTION_ARGS) +{ + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(0); + TypeCacheEntry *typcache; + int32 range_count; + RangeType **ranges; + bool isnull; + Datum result; + + if (MultirangeIsEmpty(mr)) + PG_RETURN_NULL(); + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr)); + + multirange_deserialize(mr, &range_count, &ranges); + + result = range_lower_internal(typcache->rngtype, ranges[0], &isnull); + + if (isnull) + PG_RETURN_NULL(); + else + PG_RETURN_DATUM(result); +} + +/* extract upper bound value */ +Datum +multirange_upper(PG_FUNCTION_ARGS) +{ + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(0); + TypeCacheEntry *typcache; + int32 range_count; + RangeType **ranges; + bool isnull; + Datum result; + + if (MultirangeIsEmpty(mr)) + PG_RETURN_NULL(); + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr)); + + multirange_deserialize(mr, &range_count, &ranges); + + result = range_upper_internal(typcache->rngtype, ranges[range_count - 1], &isnull); + + if (isnull) + PG_RETURN_NULL(); + else + PG_RETURN_DATUM(result); +} + + +/* multirange -> bool functions */ + +/* is multirange empty? */ +Datum +multirange_empty(PG_FUNCTION_ARGS) +{ + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(0); + + PG_RETURN_BOOL(mr->rangeCount == 0); +} + +/* is lower bound inclusive? */ +Datum +multirange_lower_inc(PG_FUNCTION_ARGS) +{ + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(0); + int32 range_count; + RangeType **ranges; + + if (MultirangeIsEmpty(mr)) + PG_RETURN_BOOL(false); + + multirange_deserialize(mr, &range_count, &ranges); + + PG_RETURN_BOOL(range_has_flag(ranges[0], RANGE_LB_INC)); +} + +/* is upper bound inclusive? */ +Datum +multirange_upper_inc(PG_FUNCTION_ARGS) +{ + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(0); + int32 range_count; + RangeType **ranges; + + if (MultirangeIsEmpty(mr)) + PG_RETURN_BOOL(false); + + multirange_deserialize(mr, &range_count, &ranges); + + PG_RETURN_BOOL(range_has_flag(ranges[range_count - 1], RANGE_UB_INC)); +} + +/* is lower bound infinite? */ +Datum +multirange_lower_inf(PG_FUNCTION_ARGS) +{ + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(0); + int32 range_count; + RangeType **ranges; + + if (MultirangeIsEmpty(mr)) + PG_RETURN_BOOL(false); + + multirange_deserialize(mr, &range_count, &ranges); + + PG_RETURN_BOOL(range_has_flag(ranges[0], RANGE_LB_INF)); +} + +/* is upper bound infinite? */ +Datum +multirange_upper_inf(PG_FUNCTION_ARGS) +{ + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(0); + int32 range_count; + RangeType **ranges; + + if (MultirangeIsEmpty(mr)) + PG_RETURN_BOOL(false); + + multirange_deserialize(mr, &range_count, &ranges); + + PG_RETURN_BOOL(range_has_flag(ranges[range_count - 1], RANGE_UB_INF)); +} + + + +/* multirange, element -> bool functions */ + +/* contains? */ +Datum +multirange_contains_elem(PG_FUNCTION_ARGS) +{ + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(0); + Datum val = PG_GETARG_DATUM(1); + TypeCacheEntry *typcache; + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr)); + + PG_RETURN_BOOL(multirange_contains_elem_internal(typcache, mr, val)); +} + +/* contained by? */ +Datum +elem_contained_by_multirange(PG_FUNCTION_ARGS) +{ + Datum val = PG_GETARG_DATUM(0); + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(1); + TypeCacheEntry *typcache; + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr)); + + PG_RETURN_BOOL(multirange_contains_elem_internal(typcache, mr, val)); +} + +/* + * Test whether multirange mr contains a specific element value. + */ +bool +multirange_contains_elem_internal(TypeCacheEntry *typcache, MultirangeType * mr, Datum val) +{ + TypeCacheEntry *rangetyp; + int32 range_count; + RangeType **ranges; + RangeType *r; + int i; + + rangetyp = typcache->rngtype; + + multirange_deserialize(mr, &range_count, &ranges); + + for (i = 0; i < range_count; i++) + { + r = ranges[i]; + if (range_contains_elem_internal(rangetyp, r, val)) + return true; + } + + return false; +} + +/* multirange, range -> bool functions */ + +/* contains? */ +Datum +multirange_contains_range(PG_FUNCTION_ARGS) +{ + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(0); + RangeType *r = PG_GETARG_RANGE_P(1); + TypeCacheEntry *typcache; + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr)); + + PG_RETURN_BOOL(multirange_contains_range_internal(typcache, mr, r)); +} + +/* contained by? */ +Datum +range_contained_by_multirange(PG_FUNCTION_ARGS) +{ + RangeType *r = PG_GETARG_RANGE_P(0); + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(1); + TypeCacheEntry *typcache; + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr)); + + PG_RETURN_BOOL(multirange_contains_range_internal(typcache, mr, r)); +} + +/* + * Test whether multirange mr contains a specific range r. + */ +bool +multirange_contains_range_internal(TypeCacheEntry *typcache, MultirangeType * mr, RangeType *r) +{ + TypeCacheEntry *rangetyp; + int32 range_count; + RangeType **ranges; + RangeType *mrr; + int i; + + rangetyp = typcache->rngtype; + + /* + * Every multirange contains an infinite number of empty ranges, even an + * empty one. + */ + if (RangeIsEmpty(r)) + return true; + + multirange_deserialize(mr, &range_count, &ranges); + + for (i = 0; i < range_count; i++) + { + mrr = ranges[i]; + if (range_contains_internal(rangetyp, mrr, r)) + return true; + } + + return false; +} + + /* multirange, multirange -> bool functions */ /* equality (internal version) */ @@ -795,6 +1619,538 @@ multirange_ne(PG_FUNCTION_ARGS) PG_RETURN_BOOL(multirange_ne_internal(typcache, mr1, mr2)); } +/* overlaps? */ +Datum +range_overlaps_multirange(PG_FUNCTION_ARGS) +{ + RangeType *r = PG_GETARG_RANGE_P(0); + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(1); + TypeCacheEntry *typcache; + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr)); + + PG_RETURN_BOOL(range_overlaps_multirange_internal(typcache, r, mr)); +} + +Datum +multirange_overlaps_range(PG_FUNCTION_ARGS) +{ + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(0); + RangeType *r = PG_GETARG_RANGE_P(1); + TypeCacheEntry *typcache; + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr)); + + PG_RETURN_BOOL(range_overlaps_multirange_internal(typcache, r, mr)); +} + +Datum +multirange_overlaps_multirange(PG_FUNCTION_ARGS) +{ + MultirangeType *mr1 = PG_GETARG_MULTIRANGE_P(0); + MultirangeType *mr2 = PG_GETARG_MULTIRANGE_P(1); + TypeCacheEntry *typcache; + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr1)); + + PG_RETURN_BOOL(multirange_overlaps_multirange_internal(typcache, mr1, mr2)); +} + +bool +range_overlaps_multirange_internal(TypeCacheEntry *typcache, RangeType *r, MultirangeType * mr) +{ + TypeCacheEntry *rangetyp; + int32 range_count; + int32 i; + RangeType **ranges; + RangeType *mrr; + + /* + * Empties never overlap, even with empties. (This seems strange since + * they *do* contain each other, but we want to follow how ranges work.) + */ + if (RangeIsEmpty(r) || MultirangeIsEmpty(mr)) + return false; + + rangetyp = typcache->rngtype; + + multirange_deserialize(mr, &range_count, &ranges); + + /* Scan through mrr and once it gets to r they either overlap or not */ + mrr = ranges[0]; + + /* Discard mrrs while mrr << r */ + i = 0; + while (range_before_internal(rangetyp, mrr, r)) + { + if (++i >= range_count) + return false; + mrr = ranges[i]; + } + + /* Now either we overlap or we passed r */ + return range_overlaps_internal(rangetyp, mrr, r); +} + +bool +multirange_overlaps_multirange_internal(TypeCacheEntry *typcache, MultirangeType * mr1, + MultirangeType * mr2) +{ + TypeCacheEntry *rangetyp; + int32 range_count1; + int32 range_count2; + int32 i1; + int32 i2; + RangeType **ranges1; + RangeType **ranges2; + RangeType *r1; + RangeType *r2; + + /* + * Empties never overlap, even with empties. (This seems strange since + * they *do* contain each other, but we want to follow how ranges work.) + */ + if (MultirangeIsEmpty(mr1) || MultirangeIsEmpty(mr2)) + return false; + + rangetyp = typcache->rngtype; + + multirange_deserialize(mr1, &range_count1, &ranges1); + multirange_deserialize(mr2, &range_count2, &ranges2); + + /* + * Every range in mr1 gets a chance to overlap with the ranges in mr2, but + * we can use their ordering to avoid O(n^2). This is similar to + * range_overlaps_multirange where r1 : r2 :: mrr : r, but there if we + * don't find an overlap with r we're done, and here if we don't find an + * overlap with r2 we try the next r2. + */ + r1 = ranges1[0]; + for (i1 = 0, i2 = 0; i2 < range_count2; i2++) + { + r2 = ranges2[i2]; + + /* Discard r1s while r1 << r2 */ + while (range_before_internal(rangetyp, r1, r2)) + { + if (++i1 >= range_count1) + return false; + r1 = ranges1[i1]; + } + + /* + * If r1 && r2, we're done, otherwise we failed to find an overlap for + * r2, so go to the next one. + */ + if (range_overlaps_internal(rangetyp, r1, r2)) + return true; + } + + /* We looked through all of mr2 without finding an overlap */ + return false; +} + +/* does not extend to right of? */ +Datum +range_overleft_multirange(PG_FUNCTION_ARGS) +{ + RangeType *r = PG_GETARG_RANGE_P(0); + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(1); + TypeCacheEntry *typcache; + int32 range_count; + RangeType **ranges; + + if (RangeIsEmpty(r) || MultirangeIsEmpty(mr)) + PG_RETURN_BOOL(false); + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr)); + + multirange_deserialize(mr, &range_count, &ranges); + + PG_RETURN_BOOL(range_overleft_internal(typcache->rngtype, r, ranges[range_count - 1])); +} + +Datum +multirange_overleft_range(PG_FUNCTION_ARGS) +{ + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(0); + RangeType *r = PG_GETARG_RANGE_P(1); + TypeCacheEntry *typcache; + int32 range_count; + RangeType **ranges; + + if (RangeIsEmpty(r) || MultirangeIsEmpty(mr)) + PG_RETURN_BOOL(false); + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr)); + + multirange_deserialize(mr, &range_count, &ranges); + + PG_RETURN_BOOL(range_overleft_internal(typcache->rngtype, ranges[range_count - 1], r)); +} + +Datum +multirange_overleft_multirange(PG_FUNCTION_ARGS) +{ + MultirangeType *mr1 = PG_GETARG_MULTIRANGE_P(0); + MultirangeType *mr2 = PG_GETARG_MULTIRANGE_P(1); + TypeCacheEntry *typcache; + int32 range_count1; + int32 range_count2; + RangeType **ranges1; + RangeType **ranges2; + + if (MultirangeIsEmpty(mr1) || MultirangeIsEmpty(mr2)) + PG_RETURN_BOOL(false); + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr1)); + + multirange_deserialize(mr1, &range_count1, &ranges1); + multirange_deserialize(mr2, &range_count2, &ranges2); + + PG_RETURN_BOOL(range_overleft_internal(typcache->rngtype, ranges1[range_count1 - 1], ranges2[range_count2 - 1])); +} + +/* does not extend to left of? */ +Datum +range_overright_multirange(PG_FUNCTION_ARGS) +{ + RangeType *r = PG_GETARG_RANGE_P(0); + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(1); + TypeCacheEntry *typcache; + int32 range_count; + RangeType **ranges; + + if (RangeIsEmpty(r) || MultirangeIsEmpty(mr)) + PG_RETURN_BOOL(false); + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr)); + + multirange_deserialize(mr, &range_count, &ranges); + + PG_RETURN_BOOL(range_overright_internal(typcache->rngtype, r, ranges[0])); +} + +Datum +multirange_overright_range(PG_FUNCTION_ARGS) +{ + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(0); + RangeType *r = PG_GETARG_RANGE_P(1); + TypeCacheEntry *typcache; + int32 range_count; + RangeType **ranges; + + if (RangeIsEmpty(r) || MultirangeIsEmpty(mr)) + PG_RETURN_BOOL(false); + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr)); + + multirange_deserialize(mr, &range_count, &ranges); + + PG_RETURN_BOOL(range_overright_internal(typcache->rngtype, ranges[0], r)); +} + +Datum +multirange_overright_multirange(PG_FUNCTION_ARGS) +{ + MultirangeType *mr1 = PG_GETARG_MULTIRANGE_P(0); + MultirangeType *mr2 = PG_GETARG_MULTIRANGE_P(1); + TypeCacheEntry *typcache; + int32 range_count1; + int32 range_count2; + RangeType **ranges1; + RangeType **ranges2; + + if (MultirangeIsEmpty(mr1) || MultirangeIsEmpty(mr2)) + PG_RETURN_BOOL(false); + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr1)); + + multirange_deserialize(mr1, &range_count1, &ranges1); + multirange_deserialize(mr2, &range_count2, &ranges2); + + PG_RETURN_BOOL(range_overright_internal(typcache->rngtype, ranges1[0], ranges2[0])); +} + +/* contains? */ +Datum +multirange_contains_multirange(PG_FUNCTION_ARGS) +{ + MultirangeType *mr1 = PG_GETARG_MULTIRANGE_P(0); + MultirangeType *mr2 = PG_GETARG_MULTIRANGE_P(1); + TypeCacheEntry *typcache; + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr1)); + + PG_RETURN_BOOL(multirange_contains_multirange_internal(typcache, mr1, mr2)); +} + +/* contained by? */ +Datum +multirange_contained_by_multirange(PG_FUNCTION_ARGS) +{ + MultirangeType *mr1 = PG_GETARG_MULTIRANGE_P(0); + MultirangeType *mr2 = PG_GETARG_MULTIRANGE_P(1); + TypeCacheEntry *typcache; + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr1)); + + PG_RETURN_BOOL(multirange_contains_multirange_internal(typcache, mr2, mr1)); +} + +/* + * Test whether multirange mr1 contains every range from another multirange mr2. + */ +bool +multirange_contains_multirange_internal(TypeCacheEntry *typcache, + MultirangeType * mr1, MultirangeType * mr2) +{ + TypeCacheEntry *rangetyp; + int32 range_count1; + int32 range_count2; + RangeType **ranges1; + RangeType **ranges2; + RangeType *r1; + RangeType *r2; + int i1, + i2; + + rangetyp = typcache->rngtype; + + multirange_deserialize(mr1, &range_count1, &ranges1); + multirange_deserialize(mr2, &range_count2, &ranges2); + + /* + * We follow the same logic for empties as ranges: - an empty multirange + * contains an empty range/multirange. - an empty multirange can't contain + * any other range/multirange. - an empty multirange is contained by any + * other range/multirange. + */ + + if (range_count2 == 0) + return true; + if (range_count1 == 0) + return false; + + /* + * Every range in mr2 must be contained by some range in mr1. To avoid + * O(n^2) we walk through both ranges in tandem. + */ + r1 = ranges1[0]; + for (i1 = 0, i2 = 0; i2 < range_count2; i2++) + { + r2 = ranges2[i2]; + + /* Discard r1s while r1 << r2 */ + while (range_before_internal(rangetyp, r1, r2)) + { + if (++i1 >= range_count1) + return false; + r1 = ranges1[i1]; + } + + /* + * If r1 @> r2, go to the next r2, otherwise return false (since every + * r1[n] and r1[n+1] must have a gap). Note this will give weird + * answers if you don't canonicalize, e.g. with a custom + * int2multirange {[1,1], [2,2]} there is a "gap". But that is + * consistent with other range operators, e.g. '[1,1]'::int2range -|- + * '[2,2]'::int2range is false. + */ + if (!range_contains_internal(rangetyp, r1, r2)) + return false; + } + + /* All ranges in mr2 are satisfied */ + return true; +} + +/* strictly left of? */ +Datum +range_before_multirange(PG_FUNCTION_ARGS) +{ + RangeType *r = PG_GETARG_RANGE_P(0); + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(1); + TypeCacheEntry *typcache; + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr)); + + PG_RETURN_BOOL(range_before_multirange_internal(typcache, r, mr)); +} + +Datum +multirange_before_range(PG_FUNCTION_ARGS) +{ + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(0); + RangeType *r = PG_GETARG_RANGE_P(1); + TypeCacheEntry *typcache; + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr)); + + PG_RETURN_BOOL(range_after_multirange_internal(typcache, r, mr)); +} + +Datum +multirange_before_multirange(PG_FUNCTION_ARGS) +{ + MultirangeType *mr1 = PG_GETARG_MULTIRANGE_P(0); + MultirangeType *mr2 = PG_GETARG_MULTIRANGE_P(1); + TypeCacheEntry *typcache; + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr1)); + + PG_RETURN_BOOL(multirange_before_multirange_internal(typcache, mr1, mr2)); +} + +/* strictly right of? */ +Datum +range_after_multirange(PG_FUNCTION_ARGS) +{ + RangeType *r = PG_GETARG_RANGE_P(0); + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(1); + TypeCacheEntry *typcache; + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr)); + + PG_RETURN_BOOL(range_after_multirange_internal(typcache, r, mr)); +} + +Datum +multirange_after_range(PG_FUNCTION_ARGS) +{ + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(0); + RangeType *r = PG_GETARG_RANGE_P(1); + TypeCacheEntry *typcache; + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr)); + + PG_RETURN_BOOL(range_before_multirange_internal(typcache, r, mr)); +} + +Datum +multirange_after_multirange(PG_FUNCTION_ARGS) +{ + MultirangeType *mr1 = PG_GETARG_MULTIRANGE_P(0); + MultirangeType *mr2 = PG_GETARG_MULTIRANGE_P(1); + TypeCacheEntry *typcache; + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr1)); + + PG_RETURN_BOOL(multirange_before_multirange_internal(typcache, mr2, mr1)); +} + +/* strictly left of? (internal version) */ +bool +range_before_multirange_internal(TypeCacheEntry *typcache, RangeType *r, + MultirangeType * mr) +{ + int32 range_count; + RangeType **ranges; + + if (RangeIsEmpty(r) || MultirangeIsEmpty(mr)) + return false; + + multirange_deserialize(mr, &range_count, &ranges); + + return range_before_internal(typcache->rngtype, r, ranges[0]); +} + +bool +multirange_before_multirange_internal(TypeCacheEntry *typcache, MultirangeType * mr1, + MultirangeType * mr2) +{ + int32 range_count1; + int32 range_count2; + RangeType **ranges1; + RangeType **ranges2; + + if (MultirangeIsEmpty(mr1) || MultirangeIsEmpty(mr2)) + return false; + + multirange_deserialize(mr1, &range_count1, &ranges1); + multirange_deserialize(mr2, &range_count2, &ranges2); + + return range_before_internal(typcache->rngtype, ranges1[range_count1 - 1], + ranges2[0]); +} + +/* strictly right of? (internal version) */ +bool +range_after_multirange_internal(TypeCacheEntry *typcache, RangeType *r, + MultirangeType * mr) +{ + int32 range_count; + RangeType **ranges; + + if (RangeIsEmpty(r) || MultirangeIsEmpty(mr)) + return false; + + multirange_deserialize(mr, &range_count, &ranges); + + return range_after_internal(typcache->rngtype, r, ranges[range_count - 1]); +} + +/* adjacent to? */ +Datum +range_adjacent_multirange(PG_FUNCTION_ARGS) +{ + RangeType *r = PG_GETARG_RANGE_P(0); + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(1); + TypeCacheEntry *typcache; + int32 range_count; + RangeType **ranges; + + if (RangeIsEmpty(r) || MultirangeIsEmpty(mr)) + return false; + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr)); + + multirange_deserialize(mr, &range_count, &ranges); + + PG_RETURN_BOOL(range_adjacent_internal(typcache->rngtype, r, ranges[0])); +} + +Datum +multirange_adjacent_range(PG_FUNCTION_ARGS) +{ + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(0); + RangeType *r = PG_GETARG_RANGE_P(1); + TypeCacheEntry *typcache; + int32 range_count; + RangeType **ranges; + + if (RangeIsEmpty(r) || MultirangeIsEmpty(mr)) + return false; + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr)); + + multirange_deserialize(mr, &range_count, &ranges); + + PG_RETURN_BOOL(range_adjacent_internal(typcache->rngtype, ranges[range_count - 1], r)); +} + +Datum +multirange_adjacent_multirange(PG_FUNCTION_ARGS) +{ + MultirangeType *mr1 = PG_GETARG_MULTIRANGE_P(0); + MultirangeType *mr2 = PG_GETARG_MULTIRANGE_P(1); + TypeCacheEntry *typcache; + int32 range_count1; + int32 range_count2; + RangeType **ranges1; + RangeType **ranges2; + + if (MultirangeIsEmpty(mr1) || MultirangeIsEmpty(mr2)) + return false; + + typcache = multirange_get_typcache(fcinfo, MultirangeTypeGetOid(mr1)); + + multirange_deserialize(mr1, &range_count1, &ranges1); + multirange_deserialize(mr2, &range_count2, &ranges2); + + PG_RETURN_BOOL(range_adjacent_internal(typcache->rngtype, ranges1[range_count1 - 1], ranges2[0])); +} + /* Btree support */ /* btree comparator */ @@ -891,6 +2247,29 @@ multirange_gt(PG_FUNCTION_ARGS) PG_RETURN_BOOL(cmp > 0); } +/* multirange -> range functions */ + +/* Find the smallest range that includes everything in the multirange */ +Datum +range_merge_from_multirange(PG_FUNCTION_ARGS) +{ + MultirangeType *mr = PG_GETARG_MULTIRANGE_P(0); + Oid mltrngtypoid = MultirangeTypeGetOid(mr); + TypeCacheEntry *typcache; + int32 range_count; + RangeType **ranges; + + typcache = multirange_get_typcache(fcinfo, mltrngtypoid); + + if (MultirangeIsEmpty(mr)) + PG_RETURN_RANGE_P(make_empty_range(typcache->rngtype)); + + multirange_deserialize(mr, &range_count, &ranges); + + PG_RETURN_RANGE_P(range_union_internal(typcache->rngtype, ranges[0], + ranges[range_count - 1], false)); +} + /* Hash support */ /* hash a multirange value */ diff --git a/src/backend/utils/adt/rangetypes.c b/src/backend/utils/adt/rangetypes.c index 65f68614c1..0651618363 100644 --- a/src/backend/utils/adt/rangetypes.c +++ b/src/backend/utils/adt/rangetypes.c @@ -429,19 +429,37 @@ range_lower(PG_FUNCTION_ARGS) { RangeType *r1 = PG_GETARG_RANGE_P(0); TypeCacheEntry *typcache; + bool isnull; + Datum result; + + typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r1)); + + result = range_lower_internal(typcache, r1, &isnull); + + if (isnull) + PG_RETURN_NULL(); + else + PG_RETURN_DATUM(result); +} + +Datum +range_lower_internal(TypeCacheEntry *typcache, const RangeType *r1, bool *isnull) +{ RangeBound lower; RangeBound upper; bool empty; - typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r1)); - range_deserialize(typcache, r1, &lower, &upper, &empty); /* Return NULL if there's no finite lower bound */ if (empty || lower.infinite) - PG_RETURN_NULL(); + { + *isnull = true; + return 0; + } - PG_RETURN_DATUM(lower.val); + *isnull = false; + return lower.val; } /* extract upper bound value */ @@ -450,19 +468,37 @@ range_upper(PG_FUNCTION_ARGS) { RangeType *r1 = PG_GETARG_RANGE_P(0); TypeCacheEntry *typcache; + bool isnull; + Datum result; + + typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r1)); + + result = range_upper_internal(typcache, r1, &isnull); + + if (isnull) + PG_RETURN_NULL(); + else + PG_RETURN_DATUM(result); +} + +Datum +range_upper_internal(TypeCacheEntry *typcache, const RangeType *r1, bool *isnull) +{ RangeBound lower; RangeBound upper; bool empty; - typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r1)); - range_deserialize(typcache, r1, &lower, &upper, &empty); /* Return NULL if there's no finite upper bound */ if (empty || upper.infinite) - PG_RETURN_NULL(); + { + *isnull = true; + return 0; + } - PG_RETURN_DATUM(upper.val); + *isnull = false; + return upper.val; } @@ -473,9 +509,8 @@ Datum range_empty(PG_FUNCTION_ARGS) { RangeType *r1 = PG_GETARG_RANGE_P(0); - char flags = range_get_flags(r1); - PG_RETURN_BOOL(flags & RANGE_EMPTY); + PG_RETURN_BOOL(range_has_flag(r1, RANGE_EMPTY)); } /* is lower bound inclusive? */ @@ -483,9 +518,8 @@ Datum range_lower_inc(PG_FUNCTION_ARGS) { RangeType *r1 = PG_GETARG_RANGE_P(0); - char flags = range_get_flags(r1); - PG_RETURN_BOOL(flags & RANGE_LB_INC); + PG_RETURN_BOOL(range_has_flag(r1, RANGE_LB_INC)); } /* is upper bound inclusive? */ @@ -493,9 +527,8 @@ Datum range_upper_inc(PG_FUNCTION_ARGS) { RangeType *r1 = PG_GETARG_RANGE_P(0); - char flags = range_get_flags(r1); - PG_RETURN_BOOL(flags & RANGE_UB_INC); + PG_RETURN_BOOL(range_has_flag(r1, RANGE_UB_INC)); } /* is lower bound infinite? */ @@ -503,9 +536,8 @@ Datum range_lower_inf(PG_FUNCTION_ARGS) { RangeType *r1 = PG_GETARG_RANGE_P(0); - char flags = range_get_flags(r1); - PG_RETURN_BOOL(flags & RANGE_LB_INF); + PG_RETURN_BOOL(range_has_flag(r1, RANGE_LB_INF)); } /* is upper bound infinite? */ @@ -513,9 +545,8 @@ Datum range_upper_inf(PG_FUNCTION_ARGS) { RangeType *r1 = PG_GETARG_RANGE_P(0); - char flags = range_get_flags(r1); - PG_RETURN_BOOL(flags & RANGE_UB_INF); + PG_RETURN_BOOL(range_has_flag(r1, RANGE_UB_INF)); } @@ -955,7 +986,25 @@ range_minus(PG_FUNCTION_ARGS) { RangeType *r1 = PG_GETARG_RANGE_P(0); RangeType *r2 = PG_GETARG_RANGE_P(1); + RangeType *ret; TypeCacheEntry *typcache; + + /* Different types should be prevented by ANYRANGE matching rules */ + if (RangeTypeGetOid(r1) != RangeTypeGetOid(r2)) + elog(ERROR, "range types do not match"); + + typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r1)); + + ret = range_minus_internal(typcache, r1, r2); + if (ret) + PG_RETURN_RANGE_P(ret); + else + PG_RETURN_NULL(); +} + +RangeType * +range_minus_internal(TypeCacheEntry *typcache, RangeType *r1, RangeType *r2) +{ RangeBound lower1, lower2; RangeBound upper1, @@ -967,18 +1016,12 @@ range_minus(PG_FUNCTION_ARGS) cmp_u1l2, cmp_u1u2; - /* Different types should be prevented by ANYRANGE matching rules */ - if (RangeTypeGetOid(r1) != RangeTypeGetOid(r2)) - elog(ERROR, "range types do not match"); - - typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r1)); - range_deserialize(typcache, r1, &lower1, &upper1, &empty1); range_deserialize(typcache, r2, &lower2, &upper2, &empty2); /* if either is empty, r1 is the correct answer */ if (empty1 || empty2) - PG_RETURN_RANGE_P(r1); + return r1; cmp_l1l2 = range_cmp_bounds(typcache, &lower1, &lower2); cmp_l1u2 = range_cmp_bounds(typcache, &lower1, &upper2); @@ -991,34 +1034,34 @@ range_minus(PG_FUNCTION_ARGS) errmsg("result of range difference would not be contiguous"))); if (cmp_l1u2 > 0 || cmp_u1l2 < 0) - PG_RETURN_RANGE_P(r1); + return r1; if (cmp_l1l2 >= 0 && cmp_u1u2 <= 0) - PG_RETURN_RANGE_P(make_empty_range(typcache)); + return make_empty_range(typcache); if (cmp_l1l2 <= 0 && cmp_u1l2 >= 0 && cmp_u1u2 <= 0) { lower2.inclusive = !lower2.inclusive; lower2.lower = false; /* it will become the upper bound */ - PG_RETURN_RANGE_P(make_range(typcache, &lower1, &lower2, false)); + return make_range(typcache, &lower1, &lower2, false); } if (cmp_l1l2 >= 0 && cmp_u1u2 >= 0 && cmp_l1u2 <= 0) { upper2.inclusive = !upper2.inclusive; upper2.lower = true; /* it will become the lower bound */ - PG_RETURN_RANGE_P(make_range(typcache, &upper2, &upper1, false)); + return make_range(typcache, &upper2, &upper1, false); } elog(ERROR, "unexpected case in range_minus"); - PG_RETURN_NULL(); + return NULL; } /* * Set union. If strict is true, it is an error that the two input ranges * are not adjacent or overlapping. */ -static RangeType * +RangeType * range_union_internal(TypeCacheEntry *typcache, RangeType *r1, RangeType *r2, bool strict) { @@ -1099,6 +1142,19 @@ range_intersect(PG_FUNCTION_ARGS) RangeType *r1 = PG_GETARG_RANGE_P(0); RangeType *r2 = PG_GETARG_RANGE_P(1); TypeCacheEntry *typcache; + + /* Different types should be prevented by ANYRANGE matching rules */ + if (RangeTypeGetOid(r1) != RangeTypeGetOid(r2)) + elog(ERROR, "range types do not match"); + + typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r1)); + + PG_RETURN_RANGE_P(range_intersect_internal(typcache, r1, r2)); +} + +RangeType * +range_intersect_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeType *r2) +{ RangeBound lower1, lower2; RangeBound upper1, @@ -1108,17 +1164,11 @@ range_intersect(PG_FUNCTION_ARGS) RangeBound *result_lower; RangeBound *result_upper; - /* Different types should be prevented by ANYRANGE matching rules */ - if (RangeTypeGetOid(r1) != RangeTypeGetOid(r2)) - elog(ERROR, "range types do not match"); - - typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r1)); - range_deserialize(typcache, r1, &lower1, &upper1, &empty1); range_deserialize(typcache, r2, &lower2, &upper2, &empty2); - if (empty1 || empty2 || !DatumGetBool(range_overlaps(fcinfo))) - PG_RETURN_RANGE_P(make_empty_range(typcache)); + if (empty1 || empty2 || !range_overlaps_internal(typcache, r1, r2)) + return make_empty_range(typcache); if (range_cmp_bounds(typcache, &lower1, &lower2) >= 0) result_lower = &lower1; @@ -1130,54 +1180,83 @@ range_intersect(PG_FUNCTION_ARGS) else result_upper = &upper2; - PG_RETURN_RANGE_P(make_range(typcache, result_lower, result_upper, false)); + return make_range(typcache, result_lower, result_upper, false); } -/* Btree support */ +/* range, range -> range, range functions */ -/* btree comparator */ -Datum -range_cmp(PG_FUNCTION_ARGS) +/* + * range_split_internal - if r2 intersects the middle of r1, leaving non-empty + * ranges on both sides, then return true and set output1 and output2 to the + * results of r1 - r2 (in order). Otherwise return false and don't set output1 + * or output2. Neither input range should be empty. + */ +bool +range_split_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeType *r2, + RangeType **output1, RangeType **output2) { - RangeType *r1 = PG_GETARG_RANGE_P(0); - RangeType *r2 = PG_GETARG_RANGE_P(1); - TypeCacheEntry *typcache; RangeBound lower1, lower2; RangeBound upper1, upper2; bool empty1, empty2; - int cmp; - - check_stack_depth(); /* recurses when subtype is a range type */ - - /* Different types should be prevented by ANYRANGE matching rules */ - if (RangeTypeGetOid(r1) != RangeTypeGetOid(r2)) - elog(ERROR, "range types do not match"); - - typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r1)); range_deserialize(typcache, r1, &lower1, &upper1, &empty1); range_deserialize(typcache, r2, &lower2, &upper2, &empty2); - /* For b-tree use, empty ranges sort before all else */ - if (empty1 && empty2) - cmp = 0; - else if (empty1) - cmp = -1; - else if (empty2) - cmp = 1; - else + if (range_cmp_bounds(typcache, &lower1, &lower2) < 0 && + range_cmp_bounds(typcache, &upper1, &upper2) > 0) { - cmp = range_cmp_bounds(typcache, &lower1, &lower2); - if (cmp == 0) - cmp = range_cmp_bounds(typcache, &upper1, &upper2); + /* + * Need to invert inclusive/exclusive for the lower2 and upper2 + * points. They can't be infinite though. We're allowed to overwrite + * these RangeBounds since they only exist locally. + */ + lower2.inclusive = !lower2.inclusive; + lower2.lower = false; + upper2.inclusive = !upper2.inclusive; + upper2.lower = true; + + *output1 = make_range(typcache, &lower1, &lower2, false); + *output2 = make_range(typcache, &upper2, &upper1, false); + return true; } - return cmp; + return false; } +/* range -> range aggregate functions */ + +Datum +range_intersect_agg_transfn(PG_FUNCTION_ARGS) +{ + MemoryContext aggContext; + Oid rngtypoid; + TypeCacheEntry *typcache; + RangeType *result; + RangeType *current; + + if (!AggCheckCallContext(fcinfo, &aggContext)) + elog(ERROR, "range_intersect_agg_transfn called in non-aggregate context"); + + rngtypoid = get_fn_expr_argtype(fcinfo->flinfo, 1); + if (!type_is_range(rngtypoid)) + ereport(ERROR, (errmsg("range_intersect_agg must be called with a range"))); + + typcache = range_get_typcache(fcinfo, rngtypoid); + + /* strictness ensures these are non-null */ + result = PG_GETARG_RANGE_P(0); + current = PG_GETARG_RANGE_P(1); + + result = range_intersect_internal(typcache, result, current); + PG_RETURN_RANGE_P(result); +} + + +/* Btree support */ + /* btree comparator */ Datum range_cmp(PG_FUNCTION_ARGS) @@ -1207,7 +1286,7 @@ range_cmp(PG_FUNCTION_ARGS) * Internal version of range_cmp */ int -range_cmp_internal(TypeCacheEntry *typcache, RangeType *r1, RangeType *r2) +range_cmp_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeType *r2) { RangeBound lower1, lower2; @@ -1273,7 +1352,7 @@ range_gt(PG_FUNCTION_ARGS) /* Hash support */ uint32 -hash_range_internal(TypeCacheEntry *typcache, RangeType *r) +hash_range_internal(TypeCacheEntry *typcache, const RangeType *r) { uint32 result; TypeCacheEntry *scache; @@ -1343,7 +1422,7 @@ hash_range(PG_FUNCTION_ARGS) } uint64 -hash_range_extended_internal(TypeCacheEntry *typcache, RangeType *r, Datum seed) +hash_range_extended_internal(TypeCacheEntry *typcache, const RangeType *r, Datum seed) { uint64 result; TypeCacheEntry *scache; @@ -1837,6 +1916,21 @@ range_get_flags(const RangeType *range) return *((char *) range + VARSIZE(range) - 1); } +/* + * range_has_flag: set whether a range has a specific flag. + * + * This lets expose some of our functions that just check flags + * to the rest of the code base (like to multiranges) + * without writing full-fledged *_internal versions. + */ +bool +range_has_flag(const RangeType *r1, char flag) +{ + char flags = range_get_flags(r1); + + return flags & flag; +} + /* * range_set_contain_empty: set the RANGE_CONTAIN_EMPTY bit in the value. * diff --git a/src/backend/utils/fmgr/funcapi.c b/src/backend/utils/fmgr/funcapi.c index d65968d4eb..25623461bb 100644 --- a/src/backend/utils/fmgr/funcapi.c +++ b/src/backend/utils/fmgr/funcapi.c @@ -573,18 +573,20 @@ resolve_polymorphic_tupdesc(TupleDesc tupdesc, oidvector *declared_args, if (OidIsValid(anymultirange_type)) { - Oid rngtype = resolve_generic_type(ANYRANGEOID, - anymultirange_type, - ANYMULTIRANGEOID); + Oid rngtype; + Oid subtype; + + rngtype = resolve_generic_type(ANYRANGEOID, + anymultirange_type, + ANYMULTIRANGEOID); /* check for inconsistent range and multirange results */ if (OidIsValid(anyrange_type) && anyrange_type != rngtype) return false; anyrange_type = rngtype; - - Oid subtype = resolve_generic_type(ANYELEMENTOID, - anyrange_type, - ANYRANGEOID); + subtype = resolve_generic_type(ANYELEMENTOID, + anyrange_type, + ANYRANGEOID); /* check for inconsistent array and multirange results */ if (OidIsValid(anyelement_type) && anyelement_type != subtype) @@ -628,18 +630,21 @@ resolve_polymorphic_tupdesc(TupleDesc tupdesc, oidvector *declared_args, { if (OidIsValid(anymultirange_type)) { - Oid rngtype = resolve_generic_type(ANYRANGEOID, - anymultirange_type, - ANYMULTIRANGEOID); + Oid rngtype; + Oid subtype; + + rngtype = resolve_generic_type(ANYRANGEOID, + anymultirange_type, + ANYMULTIRANGEOID); /* check for inconsistent range and multirange results */ if (OidIsValid(anyrange_type) && anyrange_type != rngtype) return false; anyrange_type = rngtype; - Oid subtype = resolve_generic_type(ANYELEMENTOID, - anyrange_type, - ANYRANGEOID); + subtype = resolve_generic_type(ANYELEMENTOID, + anyrange_type, + ANYRANGEOID); /* check for inconsistent array and multirange results */ if (OidIsValid(anyelement_type) && anyelement_type != subtype) @@ -660,21 +665,25 @@ resolve_polymorphic_tupdesc(TupleDesc tupdesc, oidvector *declared_args, { if (OidIsValid(anyrange_type)) { - Oid subtype = resolve_generic_type(ANYELEMENTOID, - anyrange_type, - ANYRANGEOID); + Oid subtype; + Oid mltrngtype; + Oid rngtype; + + subtype = resolve_generic_type(ANYELEMENTOID, + anyrange_type, + ANYRANGEOID); /* check for inconsistent array and range results */ if (OidIsValid(anyelement_type) && anyelement_type != subtype) return false; anyelement_type = subtype; - Oid mltrngtype = resolve_generic_type(ANYMULTIRANGEOID, - anyrange_type, - ANYRANGEOID); + mltrngtype = resolve_generic_type(ANYMULTIRANGEOID, + anyrange_type, + ANYRANGEOID); /* check for inconsistent range and multirange results */ - Oid rngtype = get_multirange_subtype(mltrngtype); + rngtype = get_multirange_subtype(mltrngtype); if (OidIsValid(anyrange_type) && anyrange_type != rngtype) return false; @@ -900,18 +909,21 @@ resolve_polymorphic_argtypes(int numargs, Oid *argtypes, char *argmodes, if (OidIsValid(anymultirange_type)) { - Oid rngtype = resolve_generic_type(ANYRANGEOID, - anymultirange_type, - ANYMULTIRANGEOID); + Oid rngtype; + Oid subtype; + + rngtype = resolve_generic_type(ANYRANGEOID, + anymultirange_type, + ANYMULTIRANGEOID); /* check for inconsistent range and multirange results */ if (OidIsValid(anyrange_type) && anyrange_type != rngtype) return false; anyrange_type = rngtype; - Oid subtype = resolve_generic_type(ANYELEMENTOID, - anyrange_type, - ANYRANGEOID); + subtype = resolve_generic_type(ANYELEMENTOID, + anyrange_type, + ANYRANGEOID); /* check for inconsistent array and multirange results */ if (OidIsValid(anyelement_type) && anyelement_type != subtype) @@ -955,18 +967,21 @@ resolve_polymorphic_argtypes(int numargs, Oid *argtypes, char *argmodes, { if (OidIsValid(anymultirange_type)) { - Oid rngtype = resolve_generic_type(ANYRANGEOID, - anymultirange_type, - ANYMULTIRANGEOID); + Oid rngtype; + Oid subtype; + + rngtype = resolve_generic_type(ANYRANGEOID, + anymultirange_type, + ANYMULTIRANGEOID); /* check for inconsistent range and multirange results */ if (OidIsValid(anyrange_type) && anyrange_type != rngtype) return false; anyrange_type = rngtype; - Oid subtype = resolve_generic_type(ANYELEMENTOID, - anyrange_type, - ANYRANGEOID); + subtype = resolve_generic_type(ANYELEMENTOID, + anyrange_type, + ANYRANGEOID); /* check for inconsistent array and multirange results */ if (OidIsValid(anyelement_type) && anyelement_type != subtype) @@ -987,21 +1002,25 @@ resolve_polymorphic_argtypes(int numargs, Oid *argtypes, char *argmodes, { if (OidIsValid(anyrange_type)) { - Oid subtype = resolve_generic_type(ANYELEMENTOID, - anyrange_type, - ANYRANGEOID); + Oid subtype; + Oid mltrngtype; + Oid rngtype; + + subtype = resolve_generic_type(ANYELEMENTOID, + anyrange_type, + ANYRANGEOID); /* check for inconsistent array and range results */ if (OidIsValid(anyelement_type) && anyelement_type != subtype) return false; anyelement_type = subtype; - Oid mltrngtype = resolve_generic_type(ANYMULTIRANGEOID, - anyrange_type, - ANYRANGEOID); + mltrngtype = resolve_generic_type(ANYMULTIRANGEOID, + anyrange_type, + ANYRANGEOID); /* check for inconsistent range and multirange results */ - Oid rngtype = get_multirange_subtype(mltrngtype); + rngtype = get_multirange_subtype(mltrngtype); if (OidIsValid(anyrange_type) && anyrange_type != rngtype) return false; diff --git a/src/include/catalog/pg_aggregate.dat b/src/include/catalog/pg_aggregate.dat index 242d843347..07a7211c15 100644 --- a/src/include/catalog/pg_aggregate.dat +++ b/src/include/catalog/pg_aggregate.dat @@ -538,6 +538,17 @@ aggtransfn => 'bytea_string_agg_transfn', aggfinalfn => 'bytea_string_agg_finalfn', aggtranstype => 'internal' }, +# range +{ aggfnoid => 'range_intersect_agg(anyrange)', + aggtransfn => 'range_intersect_agg_transfn', + aggcombinefn => 'range_intersect_agg_transfn', aggtranstype => 'anyrange' }, +{ aggfnoid => 'range_intersect_agg(anymultirange)', + aggtransfn => 'multirange_intersect_agg_transfn', + aggcombinefn => 'multirange_intersect_agg_transfn', aggtranstype => 'anymultirange' }, +{ aggfnoid => 'range_agg(anyrange)', aggtransfn => 'range_agg_transfn', + aggfinalfn => 'range_agg_finalfn', aggfinalextra => 't', + aggtranstype => 'internal' }, + # json { aggfnoid => 'json_agg', aggtransfn => 'json_agg_transfn', aggfinalfn => 'json_agg_finalfn', aggtranstype => 'internal' }, diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat index b7cb2cb000..32965d6b3d 100644 --- a/src/include/catalog/pg_amproc.dat +++ b/src/include/catalog/pg_amproc.dat @@ -1210,12 +1210,13 @@ amprocrighttype => 'anyrange', amprocnum => '4', amproc => 'brin_inclusion_union' }, { amprocfamily => 'brin/range_inclusion_ops', amproclefttype => 'anyrange', - amprocrighttype => 'anyrange', amprocnum => '11', amproc => 'range_merge' }, + amprocrighttype => 'anyrange', amprocnum => '11', + amproc => 'range_merge(anyrange,anyrange)' }, { amprocfamily => 'brin/range_inclusion_ops', amproclefttype => 'anyrange', amprocrighttype => 'anyrange', amprocnum => '13', amproc => 'range_contains' }, { amprocfamily => 'brin/range_inclusion_ops', amproclefttype => 'anyrange', - amprocrighttype => 'anyrange', amprocnum => '14', amproc => 'isempty' }, + amprocrighttype => 'anyrange', amprocnum => '14', amproc => 'isempty(anyrange)' }, # minmax pg_lsn { amprocfamily => 'brin/pg_lsn_minmax_ops', amproclefttype => 'pg_lsn', diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat index 7b7ce38076..1afdc1db7a 100644 --- a/src/include/catalog/pg_operator.dat +++ b/src/include/catalog/pg_operator.dat @@ -3330,5 +3330,174 @@ oprresult => 'bool', oprcom => '<(anymultirange,anymultirange)', oprnegate => '<=(anymultirange,anymultirange)', oprcode => 'multirange_gt', oprrest => 'scalargtsel', oprjoin => 'scalargtjoinsel' }, +{ oid => '8073', oid_symbol => 'OID_RANGE_OVERLAPS_MULTIRANGE_OP', + descr => 'overlaps', + oprname => '&&', oprleft => 'anyrange', oprright => 'anymultirange', + oprresult => 'bool', oprcom => '&&(anymultirange,anyrange)', + oprcode => 'range_overlaps_multirange', oprrest => 'areasel', + oprjoin => 'areajoinsel' }, +{ oid => '8074', oid_symbol => 'OID_MULTIRANGE_OVERLAPS_RANGE_OP', descr => 'contains', + oprname => '&&', oprleft => 'anymultirange', oprright => 'anyrange', + oprresult => 'bool', oprcom => '&&(anyrange,anymultirange)', + oprcode => 'multirange_overlaps_range', oprrest => 'areasel', + oprjoin => 'areajoinsel' }, +{ oid => '8075', oid_symbol => 'OID_MULTIRANGE_OVERLAPS_MULTIRANGE_OP', descr => 'contains', + oprname => '&&', oprleft => 'anymultirange', oprright => 'anymultirange', + oprresult => 'bool', oprcom => '&&(anymultirange,anymultirange)', + oprcode => 'multirange_overlaps_multirange', oprrest => 'areasel', + oprjoin => 'areajoinsel' }, +{ oid => '8064', oid_symbol => 'OID_MULTIRANGE_CONTAINS_ELEM_OP', + descr => 'contains', + oprname => '@>', oprleft => 'anymultirange', oprright => 'anyelement', + oprresult => 'bool', oprcom => '<@(anyelement,anymultirange)', + oprcode => 'multirange_contains_elem', oprrest => 'contsel', + oprjoin => 'contjoinsel' }, +{ oid => '8065', oid_symbol => 'OID_MULTIRANGE_CONTAINS_RANGE_OP', descr => 'contains', + oprname => '@>', oprleft => 'anymultirange', oprright => 'anyrange', + oprresult => 'bool', oprcom => '<@(anyrange,anymultirange)', + oprcode => 'multirange_contains_range', oprrest => 'contsel', + oprjoin => 'contjoinsel' }, +{ oid => '8066', oid_symbol => 'OID_MULTIRANGE_CONTAINS_MULTIRANGE_OP', descr => 'contains', + oprname => '@>', oprleft => 'anymultirange', oprright => 'anymultirange', + oprresult => 'bool', oprcom => '<@(anymultirange,anymultirange)', + oprcode => 'multirange_contains_multirange', oprrest => 'contsel', + oprjoin => 'contjoinsel' }, +{ oid => '8067', oid_symbol => 'OID_MULTIRANGE_ELEM_CONTAINED_OP', + descr => 'is contained by', + oprname => '<@', oprleft => 'anyelement', oprright => 'anymultirange', + oprresult => 'bool', oprcom => '@>(anymultirange,anyelement)', + oprcode => 'elem_contained_by_multirange', oprrest => 'contsel', + oprjoin => 'contjoinsel' }, +{ oid => '8068', oid_symbol => 'OID_MULTIRANGE_RANGE_CONTAINED_OP', + descr => 'is contained by', + oprname => '<@', oprleft => 'anyrange', oprright => 'anymultirange', + oprresult => 'bool', oprcom => '@>(anymultirange,anyrange)', + oprcode => 'range_contained_by_multirange', oprrest => 'contsel', + oprjoin => 'contjoinsel' }, +{ oid => '8069', oid_symbol => 'OID_MULTIRANGE_MULTIRANGE_CONTAINED_OP', + descr => 'is contained by', + oprname => '<@', oprleft => 'anymultirange', oprright => 'anymultirange', + oprresult => 'bool', oprcom => '@>(anymultirange,anymultirange)', + oprcode => 'multirange_contained_by_multirange', oprrest => 'contsel', + oprjoin => 'contjoinsel' }, +{ oid => '8106', oid_symbol => 'OID_RANGE_OVERLAPS_LEFT_MULTIRANGE_OP', + descr => 'overlaps or is left of', + oprname => '&<', oprleft => 'anyrange', oprright => 'anymultirange', + oprresult => 'bool', oprcode => 'range_overleft_multirange', oprrest => 'scalarltsel', + oprjoin => 'scalarltjoinsel' }, +{ oid => '8107', oid_symbol => 'OID_MULTIRANGE_OVERLAPS_LEFT_RANGE_OP', + descr => 'overlaps or is left of', + oprname => '&<', oprleft => 'anymultirange', oprright => 'anyrange', + oprresult => 'bool', oprcode => 'multirange_overleft_range', oprrest => 'scalarltsel', + oprjoin => 'scalarltjoinsel' }, +{ oid => '8108', oid_symbol => 'OID_MULTIRANGE_OVERLAPS_LEFT_MULTIRANGE_OP', + descr => 'overlaps or is left of', + oprname => '&<', oprleft => 'anymultirange', oprright => 'anymultirange', + oprresult => 'bool', oprcode => 'multirange_overleft_multirange', oprrest => 'scalarltsel', + oprjoin => 'scalarltjoinsel' }, +{ oid => '8109', oid_symbol => 'OID_RANGE_OVERLAPS_RIGHT_MULTIRANGE_OP', + descr => 'overlaps or is right of', + oprname => '&>', oprleft => 'anyrange', oprright => 'anymultirange', + oprresult => 'bool', oprcode => 'range_overright_multirange', oprrest => 'scalargtsel', + oprjoin => 'scalargtjoinsel' }, +{ oid => '8110', oid_symbol => 'OID_MULTIRANGE_OVERLAPS_RIGHT_RANGE_OP', + descr => 'overlaps or is right of', + oprname => '&>', oprleft => 'anymultirange', oprright => 'anyrange', + oprresult => 'bool', oprcode => 'multirange_overright_range', oprrest => 'scalargtsel', + oprjoin => 'scalargtjoinsel' }, +{ oid => '8111', oid_symbol => 'OID_MULTIRANGE_OVERLAPS_RIGHT_MULTIRANGE_OP', + descr => 'overlaps or is right of', + oprname => '&>', oprleft => 'anymultirange', oprright => 'anymultirange', + oprresult => 'bool', oprcode => 'multirange_overright_multirange', oprrest => 'scalargtsel', + oprjoin => 'scalargtjoinsel' }, +{ oid => '8091', oid_symbol => 'OID_RANGE_ADJACENT_MULTIRANGE_OP', descr => 'is adjacent to', + oprname => '-|-', oprleft => 'anyrange', oprright => 'anymultirange', + oprresult => 'bool', oprcom => '-|-(anymultirange,anyrange)', + oprcode => 'range_adjacent_multirange', oprrest => 'scalarltsel', + oprjoin => 'scalarltjoinsel' }, +{ oid => '8092', oid_symbol => 'OID_MULTIRANGE_ADJACENT_RANGE_OP', descr => 'is adjacent to', + oprname => '-|-', oprleft => 'anymultirange', oprright => 'anyrange', + oprresult => 'bool', oprcom => '-|-(anyrange,anymultirange)', + oprcode => 'multirange_adjacent_range', oprrest => 'scalarltsel', + oprjoin => 'scalarltjoinsel' }, +{ oid => '8093', oid_symbol => 'OID_MULTIRANGE_ADJACENT_MULTIRANGE_OP', descr => 'is adjacent to', + oprname => '-|-', oprleft => 'anymultirange', oprright => 'anymultirange', + oprresult => 'bool', oprcom => '-|-(anymultirange,anymultirange)', + oprcode => 'multirange_adjacent_multirange', oprrest => 'scalarltsel', + oprjoin => 'scalarltjoinsel' }, +{ oid => '8138', descr => 'multirange union', + oprname => '@+', oprleft => 'anyrange', oprright => 'anyrange', + oprresult => 'anymultirange', oprcom => '@+(anyrange,anyrange)', + oprcode => 'range_union_range' }, +{ oid => '8115', descr => 'multirange union', + oprname => '@+', oprleft => 'anyrange', oprright => 'anymultirange', + oprresult => 'anymultirange', oprcom => '@+(anymultirange,anyrange)', + oprcode => 'range_union_multirange' }, +{ oid => '8116', descr => 'multirange union', + oprname => '@+', oprleft => 'anymultirange', oprright => 'anyrange', + oprresult => 'anymultirange', oprcom => '@+(anyrange,anymultirange)', + oprcode => 'multirange_union_range' }, +{ oid => '8117', descr => 'multirange union', + oprname => '@+', oprleft => 'anymultirange', oprright => 'anymultirange', + oprresult => 'anymultirange', oprcom => '@+(anymultirange,anymultirange)', + oprcode => 'multirange_union_multirange' }, +{ oid => '8140', descr => 'multirange minus', + oprname => '@-', oprleft => 'anyrange', oprright => 'anyrange', + oprresult => 'anymultirange', oprcode => 'range_minus_range' }, +{ oid => '8121', descr => 'multirange minus', + oprname => '@-', oprleft => 'anyrange', oprright => 'anymultirange', + oprresult => 'anymultirange', oprcode => 'range_minus_multirange' }, +{ oid => '8122', descr => 'multirange minus', + oprname => '@-', oprleft => 'anymultirange', oprright => 'anyrange', + oprresult => 'anymultirange', oprcode => 'multirange_minus_range' }, +{ oid => '8123', descr => 'multirange minus', + oprname => '@-', oprleft => 'anymultirange', oprright => 'anymultirange', + oprresult => 'anymultirange', oprcode => 'multirange_minus_multirange' }, +{ oid => '8142', descr => 'multirange intersect', + oprname => '@*', oprleft => 'anyrange', oprright => 'anyrange', + oprresult => 'anymultirange', oprcom => '@*(anyrange,anyrange)', + oprcode => 'range_intersect_range' }, +{ oid => '8127', descr => 'multirange intersect', + oprname => '@*', oprleft => 'anyrange', oprright => 'anymultirange', + oprresult => 'anymultirange', oprcom => '@*(anymultirange,anyrange)', + oprcode => 'range_intersect_multirange' }, +{ oid => '8128', descr => 'multirange intersect', + oprname => '@*', oprleft => 'anymultirange', oprright => 'anyrange', + oprresult => 'anymultirange', oprcom => '@*(anyrange,anymultirange)', + oprcode => 'multirange_intersect_range' }, +{ oid => '8129', descr => 'multirange intersect', + oprname => '@*', oprleft => 'anymultirange', oprright => 'anymultirange', + oprresult => 'anymultirange', oprcom => '@*(anymultirange,anymultirange)', + oprcode => 'multirange_intersect_multirange' }, +{ oid => '8082', oid_symbol => 'OID_RANGE_BEFORE_MULTIRANGE_OP', descr => 'is left of', + oprname => '<<', oprleft => 'anyrange', oprright => 'anymultirange', + oprresult => 'bool', oprcom => '>>(anymultirange,anyrange)', + oprcode => 'range_before_multirange', oprrest => 'scalarltsel', + oprjoin => 'scalarltjoinsel' }, +{ oid => '8083', oid_symbol => 'OID_MULTIRANGE_BEFORE_RANGE_OP', descr => 'is left of', + oprname => '<<', oprleft => 'anymultirange', oprright => 'anyrange', + oprresult => 'bool', oprcom => '>>(anyrange,anymultirange)', + oprcode => 'multirange_before_range', oprrest => 'scalarltsel', + oprjoin => 'scalarltjoinsel' }, +{ oid => '8084', oid_symbol => 'OID_MULTIRANGE_BEFORE_MULTIRANGE_OP', descr => 'is left of', + oprname => '<<', oprleft => 'anymultirange', oprright => 'anymultirange', + oprresult => 'bool', oprcom => '>>(anymultirange,anymultirange)', + oprcode => 'multirange_before_multirange', oprrest => 'scalarltsel', + oprjoin => 'scalarltjoinsel' }, +{ oid => '8085', oid_symbol => 'OID_RANGE_AFTER_MULTIRANGE_OP', descr => 'is right of', + oprname => '>>', oprleft => 'anyrange', oprright => 'anymultirange', + oprresult => 'bool', oprcom => '<<(anymultirange,anyrange)', + oprcode => 'range_after_multirange', oprrest => 'scalargtsel', + oprjoin => 'scalargtjoinsel' }, +{ oid => '8086', oid_symbol => 'OID_MULTIRANGE_AFTER_RANGE_OP', descr => 'is right of', + oprname => '>>', oprleft => 'anymultirange', oprright => 'anyrange', + oprresult => 'bool', oprcom => '<<(anyrange,anymultirange)', + oprcode => 'multirange_after_range', oprrest => 'scalargtsel', + oprjoin => 'scalargtjoinsel' }, +{ oid => '8087', oid_symbol => 'OID_MULTIRANGE_AFTER_MULTIRANGE_OP', descr => 'is right of', + oprname => '>>', oprleft => 'anymultirange', oprright => 'anymultirange', + oprresult => 'bool', oprcom => '<<(anymultirange,anymultirange)', + oprcode => 'multirange_after_multirange', oprrest => 'scalargtsel', + oprjoin => 'scalargtjoinsel' }, ] diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index abb956514e..4311db01ff 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -9605,6 +9605,10 @@ descr => 'the smallest range which includes both of the given ranges', proname => 'range_merge', prorettype => 'anyrange', proargtypes => 'anyrange anyrange', prosrc => 'range_merge' }, +{ oid => '8143', + descr => 'the smallest range which includes the whole multirange', + proname => 'range_merge', prorettype => 'anyrange', + proargtypes => 'anymultirange', prosrc => 'range_merge_from_multirange' }, { oid => '3868', proname => 'range_intersect', prorettype => 'anyrange', proargtypes => 'anyrange anyrange', prosrc => 'range_intersect' }, @@ -9654,6 +9658,13 @@ { oid => '3169', descr => 'restriction selectivity for range operators', proname => 'rangesel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int4', prosrc => 'rangesel' }, +{ oid => '8133', descr => 'range aggregate by intersecting', + proname => 'range_intersect_agg_transfn', prorettype => 'anyrange', + proargtypes => 'anyrange anyrange', prosrc => 'range_intersect_agg_transfn'}, +{ oid => '8134', descr => 'range aggregate by intersecting', + proname => 'range_intersect_agg', prokind => 'a', proisstrict => 'f', + prorettype => 'anyrange', proargtypes => 'anyrange', + prosrc => 'aggregate_dummy'}, { oid => '3914', descr => 'convert an int4 range to canonical form', proname => 'int4range_canonical', prorettype => 'int4range', @@ -9741,9 +9752,165 @@ { oid => '8007', descr => 'I/O', proname => 'multirange_send', provolatile => 's', prorettype => 'bytea', proargtypes => 'anymultirange', prosrc => 'multirange_send' }, +{ oid => '8094', descr => 'lower bound of multirange', + proname => 'lower', prorettype => 'anyelement', proargtypes => 'anymultirange', + prosrc => 'multirange_lower' }, +{ oid => '8095', descr => 'upper bound of multirange', + proname => 'upper', prorettype => 'anyelement', proargtypes => 'anymultirange', + prosrc => 'multirange_upper' }, +{ oid => '8057', descr => 'is the multirange empty?', + proname => 'isempty', prorettype => 'bool', + proargtypes => 'anymultirange', prosrc => 'multirange_empty' }, +{ oid => '8096', descr => 'is the multirange\'s lower bound inclusive?', + proname => 'lower_inc', prorettype => 'bool', proargtypes => 'anymultirange', + prosrc => 'multirange_lower_inc' }, +{ oid => '8097', descr => 'is the multirange\'s upper bound inclusive?', + proname => 'upper_inc', prorettype => 'bool', proargtypes => 'anymultirange', + prosrc => 'multirange_upper_inc' }, +{ oid => '8098', descr => 'is the multirange\'s lower bound infinite?', + proname => 'lower_inf', prorettype => 'bool', proargtypes => 'anymultirange', + prosrc => 'multirange_lower_inf' }, +{ oid => '8099', descr => 'is the multirange\'s upper bound infinite?', + proname => 'upper_inf', prorettype => 'bool', proargtypes => 'anymultirange', + prosrc => 'multirange_upper_inf' }, { oid => '8008', descr => 'multirange typanalyze', proname => 'multirange_typanalyze', provolatile => 's', prorettype => 'bool', proargtypes => 'internal', prosrc => 'multirange_typanalyze' }, +{ oid => '8033', + proname => 'multirange_eq', prorettype => 'bool', + proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_eq' }, +{ oid => '8034', + proname => 'multirange_ne', prorettype => 'bool', + proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_ne' }, +{ oid => '8070', + proname => 'range_overlaps_multirange', prorettype => 'bool', + proargtypes => 'anyrange anymultirange', prosrc => 'range_overlaps_multirange' }, +{ oid => '8071', + proname => 'multirange_overlaps_range', prorettype => 'bool', + proargtypes => 'anymultirange anyrange', prosrc => 'multirange_overlaps_range' }, +{ oid => '8072', + proname => 'multirange_overlaps_multirange', prorettype => 'bool', + proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_overlaps_multirange' }, +{ oid => '8058', + proname => 'multirange_contains_elem', prorettype => 'bool', + proargtypes => 'anymultirange anyelement', prosrc => 'multirange_contains_elem' }, +{ oid => '8059', + proname => 'multirange_contains_range', prorettype => 'bool', + proargtypes => 'anymultirange anyrange', prosrc => 'multirange_contains_range' }, +{ oid => '8060', + proname => 'multirange_contains_multirange', prorettype => 'bool', + proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_contains_multirange' }, +{ oid => '8061', + proname => 'elem_contained_by_multirange', prorettype => 'bool', + proargtypes => 'anyelement anymultirange', prosrc => 'elem_contained_by_multirange' }, +{ oid => '8062', + proname => 'range_contained_by_multirange', prorettype => 'bool', + proargtypes => 'anyrange anymultirange', prosrc => 'range_contained_by_multirange' }, +{ oid => '8063', + proname => 'multirange_contained_by_multirange', prorettype => 'bool', + proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_contained_by_multirange' }, +{ oid => '8088', + proname => 'range_adjacent_multirange', prorettype => 'bool', + proargtypes => 'anyrange anymultirange', prosrc => 'range_adjacent_multirange' }, +{ oid => '8089', + proname => 'multirange_adjacent_multirange', prorettype => 'bool', + proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_adjacent_multirange' }, +{ oid => '8090', + proname => 'multirange_adjacent_range', prorettype => 'bool', + proargtypes => 'anymultirange anyrange', prosrc => 'multirange_adjacent_range' }, +{ oid => '8076', + proname => 'range_before_multirange', prorettype => 'bool', + proargtypes => 'anyrange anymultirange', prosrc => 'range_before_multirange' }, +{ oid => '8077', + proname => 'multirange_before_range', prorettype => 'bool', + proargtypes => 'anymultirange anyrange', prosrc => 'multirange_before_range' }, +{ oid => '8078', + proname => 'multirange_before_multirange', prorettype => 'bool', + proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_before_multirange' }, +{ oid => '8079', + proname => 'range_after_multirange', prorettype => 'bool', + proargtypes => 'anyrange anymultirange', prosrc => 'range_after_multirange' }, +{ oid => '8080', + proname => 'multirange_after_range', prorettype => 'bool', + proargtypes => 'anymultirange anyrange', prosrc => 'multirange_after_range' }, +{ oid => '8081', + proname => 'multirange_after_multirange', prorettype => 'bool', + proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_after_multirange' }, +{ oid => '8100', + proname => 'range_overleft_multirange', prorettype => 'bool', + proargtypes => 'anyrange anymultirange', prosrc => 'range_overleft_multirange' }, +{ oid => '8101', + proname => 'multirange_overleft_range', prorettype => 'bool', + proargtypes => 'anymultirange anyrange', prosrc => 'multirange_overleft_range' }, +{ oid => '8102', + proname => 'multirange_overleft_multirange', prorettype => 'bool', + proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_overleft_multirange' }, +{ oid => '8103', + proname => 'range_overright_multirange', prorettype => 'bool', + proargtypes => 'anyrange anymultirange', prosrc => 'range_overright_multirange' }, +{ oid => '8104', + proname => 'multirange_overright_range', prorettype => 'bool', + proargtypes => 'anymultirange anyrange', prosrc => 'multirange_overright_range' }, +{ oid => '8105', + proname => 'multirange_overright_multirange', prorettype => 'bool', + proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_overright_multirange' }, +{ oid => '8137', + proname => 'range_union_range', prorettype => 'anymultirange', + proargtypes => 'anyrange anyrange', prosrc => 'range_union_range' }, +{ oid => '8112', + proname => 'range_union_multirange', prorettype => 'anymultirange', + proargtypes => 'anyrange anymultirange', prosrc => 'range_union_multirange' }, +{ oid => '8113', + proname => 'multirange_union_range', prorettype => 'anymultirange', + proargtypes => 'anymultirange anyrange', prosrc => 'multirange_union_range' }, +{ oid => '8114', + proname => 'multirange_union_multirange', prorettype => 'anymultirange', + proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_union_multirange' }, +{ oid => '8139', + proname => 'range_minus_range', prorettype => 'anymultirange', + proargtypes => 'anyrange anyrange', prosrc => 'range_minus_range' }, +{ oid => '8118', + proname => 'range_minus_multirange', prorettype => 'anymultirange', + proargtypes => 'anyrange anymultirange', prosrc => 'range_minus_multirange' }, +{ oid => '8119', + proname => 'multirange_minus_range', prorettype => 'anymultirange', + proargtypes => 'anymultirange anyrange', prosrc => 'multirange_minus_range' }, +{ oid => '8120', + proname => 'multirange_minus_multirange', prorettype => 'anymultirange', + proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_minus_multirange' }, +{ oid => '8141', + proname => 'range_intersect_range', prorettype => 'anymultirange', + proargtypes => 'anyrange anyrange', prosrc => 'range_intersect_range' }, +{ oid => '8124', + proname => 'range_intersect_multirange', prorettype => 'anymultirange', + proargtypes => 'anyrange anymultirange', prosrc => 'range_intersect_multirange' }, +{ oid => '8125', + proname => 'multirange_intersect_range', prorettype => 'anymultirange', + proargtypes => 'anymultirange anyrange', prosrc => 'multirange_intersect_range' }, +{ oid => '8126', + proname => 'multirange_intersect_multirange', prorettype => 'anymultirange', + proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_intersect_multirange' }, +{ oid => '8022', descr => 'less-equal-greater', + proname => 'multirange_cmp', prorettype => 'int4', + proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_cmp' }, +{ oid => '8023', + proname => 'multirange_lt', prorettype => 'bool', + proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_lt' }, +{ oid => '8024', + proname => 'multirange_le', prorettype => 'bool', + proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_le' }, +{ oid => '8025', + proname => 'multirange_ge', prorettype => 'bool', + proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_ge' }, +{ oid => '8026', + proname => 'multirange_gt', prorettype => 'bool', + proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_gt' }, +{ oid => '8038', descr => 'hash a multirange', + proname => 'hash_multirange', prorettype => 'int4', proargtypes => 'anymultirange', + prosrc => 'hash_multirange' }, +{ oid => '8039', descr => 'hash a multirange', + proname => 'hash_multirange_extended', prorettype => 'int8', + proargtypes => 'anymultirange int8', prosrc => 'hash_multirange_extended' }, { oid => '8045', descr => 'int4multirange constructor', proname => 'int4multirange', proisstrict => 'f', @@ -9799,6 +9966,24 @@ prorettype => 'int8multirange', proargtypes => '_int8range', proallargtypes => '{_int8range}', proargmodes => '{v}', prosrc => 'multirange_constructor1' }, +{ oid => '8130', descr => 'aggregate transition function', + proname => 'range_agg_transfn', proisstrict => 'f', prorettype => 'internal', + proargtypes => 'internal anyrange', prosrc => 'range_agg_transfn' }, +{ oid => '8131', descr => 'aggregate final function', + proname => 'range_agg_finalfn', proisstrict => 'f', prorettype => 'anymultirange', + proargtypes => 'internal anyrange', prosrc => 'range_agg_finalfn' }, +{ oid => '8132', descr => 'combine aggregate input into a multirange', + proname => 'range_agg', prokind => 'a', proisstrict => 'f', + prorettype => 'anymultirange', proargtypes => 'anyrange', + prosrc => 'aggregate_dummy' }, +{ oid => '8135', descr => 'range aggregate by intersecting', + proname => 'multirange_intersect_agg_transfn', prorettype => 'anymultirange', + proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_intersect_agg_transfn'}, +{ oid => '8136', descr => 'range aggregate by intersecting', + proname => 'range_intersect_agg', prokind => 'a', proisstrict => 'f', + prorettype => 'anymultirange', proargtypes => 'anymultirange', + prosrc => 'aggregate_dummy'}, + # date, time, timestamp constructors { oid => '3846', descr => 'construct date', proname => 'make_date', prorettype => 'date', proargtypes => 'int4 int4 int4', diff --git a/src/include/utils/multirangetypes.h b/src/include/utils/multirangetypes.h index ed2e19aafa..cc4f82b0ba 100644 --- a/src/include/utils/multirangetypes.h +++ b/src/include/utils/multirangetypes.h @@ -59,6 +59,40 @@ extern bool multirange_eq_internal(TypeCacheEntry *typcache, MultirangeType * mr MultirangeType * mr2); extern bool multirange_ne_internal(TypeCacheEntry *typcache, MultirangeType * mr1, MultirangeType * mr2); +extern bool multirange_contains_elem_internal(TypeCacheEntry *typcache, MultirangeType * mr, + Datum elem); +extern bool multirange_contains_range_internal(TypeCacheEntry *typcache, MultirangeType * mr, + RangeType *r); +extern bool multirange_contains_multirange_internal(TypeCacheEntry *typcache, + MultirangeType * mr1, + MultirangeType * mr2); +extern bool range_overlaps_multirange_internal(TypeCacheEntry *typcache, RangeType *r, + MultirangeType * mr); +extern bool multirange_overlaps_multirange_internal(TypeCacheEntry *typcache, + MultirangeType * mr1, + MultirangeType * mr2); +extern bool range_before_multirange_internal(TypeCacheEntry *typcache, RangeType *r, + MultirangeType * mr); +extern bool range_after_multirange_internal(TypeCacheEntry *typcache, RangeType *r, + MultirangeType * mr); +extern bool multirange_before_multirange_internal(TypeCacheEntry *typcache, + MultirangeType * mr1, + MultirangeType * mr2); +extern MultirangeType * range_union_multirange_internal(TypeCacheEntry *typcache, + RangeType *r, + MultirangeType * mr); +extern MultirangeType * multirange_minus_multirange_internal(Oid mltrngtypoid, + TypeCacheEntry *rangetyp, + int32 range_count1, + RangeType **ranges1, + int32 range_count2, + RangeType **ranges2); +extern MultirangeType * multirange_intersect_multirange_internal(Oid mltrngtypoid, + TypeCacheEntry *rangetyp, + int32 range_count1, + RangeType **ranges1, + int32 range_count2, + RangeType **ranges2); /* assorted support functions */ extern TypeCacheEntry *multirange_get_typcache(FunctionCallInfo fcinfo, diff --git a/src/include/utils/rangetypes.h b/src/include/utils/rangetypes.h index 521710d71f..0bee724886 100644 --- a/src/include/utils/rangetypes.h +++ b/src/include/utils/rangetypes.h @@ -94,13 +94,21 @@ typedef struct * prototypes for functions defined in rangetypes.c */ -extern bool range_contains_elem_internal(TypeCacheEntry *typcache, const RangeType *r, Datum val); - /* internal versions of the above */ +extern int range_cmp_internal(TypeCacheEntry *typcache, const RangeType *r1, + const RangeType *r2); +extern uint32 hash_range_internal(TypeCacheEntry *typcache, const RangeType *r); +extern uint64 hash_range_extended_internal(TypeCacheEntry *typcache, const RangeType *r, + Datum seed); +extern Datum range_lower_internal(TypeCacheEntry *typcache, const RangeType *r1, + bool *isnull); +extern Datum range_upper_internal(TypeCacheEntry *typcache, const RangeType *r1, + bool *isnull); extern bool range_eq_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeType *r2); extern bool range_ne_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeType *r2); +extern bool range_contains_elem_internal(TypeCacheEntry *typcache, const RangeType *r, Datum val); extern bool range_contains_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeType *r2); extern bool range_contained_by_internal(TypeCacheEntry *typcache, const RangeType *r1, @@ -117,6 +125,12 @@ extern bool range_overleft_internal(TypeCacheEntry *typcache, const RangeType *r const RangeType *r2); extern bool range_overright_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeType *r2); +extern RangeType *range_union_internal(TypeCacheEntry *typcache, RangeType *r1, + RangeType *r2, bool strict); +extern RangeType *range_minus_internal(TypeCacheEntry *typcache, RangeType *r1, + RangeType *r2); +extern RangeType *range_intersect_internal(TypeCacheEntry *typcache, const RangeType *r1, + const RangeType *r2); /* assorted support functions */ extern TypeCacheEntry *range_get_typcache(FunctionCallInfo fcinfo, @@ -127,15 +141,20 @@ extern void range_deserialize(TypeCacheEntry *typcache, const RangeType *range, RangeBound *lower, RangeBound *upper, bool *empty); extern char range_get_flags(const RangeType *range); +extern bool range_has_flag(const RangeType *range, char flag); extern void range_set_contain_empty(RangeType *range); extern RangeType *make_range(TypeCacheEntry *typcache, RangeBound *lower, RangeBound *upper, bool empty); extern int range_cmp_bounds(TypeCacheEntry *typcache, const RangeBound *b1, - const RangeBound *b2); + const RangeBound *b2); extern int range_cmp_bound_values(TypeCacheEntry *typcache, const RangeBound *b1, - const RangeBound *b2); -extern bool bounds_adjacent(TypeCacheEntry *typcache, RangeBound bound1, - RangeBound bound2); + const RangeBound *b2); +extern int range_compare(const void *key1, const void *key2, void *arg); +extern bool bounds_adjacent(TypeCacheEntry *typcache, const RangeBound bound1, + const RangeBound bound2); extern RangeType *make_empty_range(TypeCacheEntry *typcache); +extern bool range_split_internal(TypeCacheEntry *typcache, const RangeType *r1, + const RangeType *r2, RangeType **output1, + RangeType **output2); #endif /* RANGETYPES_H */ diff --git a/src/test/regress/expected/dependency.out b/src/test/regress/expected/dependency.out index b4a11b8aa9..778699a961 100644 --- a/src/test/regress/expected/dependency.out +++ b/src/test/regress/expected/dependency.out @@ -141,6 +141,7 @@ owner of table deptest owner of function deptest_func() owner of type deptest_enum owner of type deptest_range +owner of type deptest_multirange owner of table deptest2 owner of sequence ss1 owner of type deptest_t diff --git a/src/test/regress/expected/multirangetypes.out b/src/test/regress/expected/multirangetypes.out index 5749537635..2276d6d002 100644 --- a/src/test/regress/expected/multirangetypes.out +++ b/src/test/regress/expected/multirangetypes.out @@ -290,3 +290,2400 @@ select textmultirange(textrange('a', 'c'), textrange('b', 'd')); {[a,d)} (1 row) +-- +-- create some test data and test the operators +-- +CREATE TABLE nummultirange_test (nmr NUMMULTIRANGE); +CREATE INDEX nummultirange_test_btree ON nummultirange_test(nmr); +INSERT INTO nummultirange_test VALUES('{}'); +INSERT INTO nummultirange_test VALUES('{[,)}'); +INSERT INTO nummultirange_test VALUES('{[3,]}'); +INSERT INTO nummultirange_test VALUES('{[,), [3,]}'); +INSERT INTO nummultirange_test VALUES('{[, 5)}'); +INSERT INTO nummultirange_test VALUES(nummultirange()); +INSERT INTO nummultirange_test VALUES(nummultirange(variadic '{}'::numrange[])); +INSERT INTO nummultirange_test VALUES(nummultirange(numrange(1.1, 2.2))); +INSERT INTO nummultirange_test VALUES('{empty}'); +INSERT INTO nummultirange_test VALUES(nummultirange(numrange(1.7, 1.7, '[]'), numrange(1.7, 1.9))); +INSERT INTO nummultirange_test VALUES(nummultirange(numrange(1.7, 1.7, '[]'), numrange(1.9, 2.1))); +SELECT nmr, isempty(nmr), lower(nmr), upper(nmr) FROM nummultirange_test ORDER BY nmr; + nmr | isempty | lower | upper +-----------------------+---------+-------+------- + {} | t | | + {} | t | | + {} | t | | + {} | t | | + {(,5)} | f | | 5 + {(,)} | f | | + {(,)} | f | | + {[1.1,2.2)} | f | 1.1 | 2.2 + {[1.7,1.7],[1.9,2.1)} | f | 1.7 | 2.1 + {[1.7,1.9)} | f | 1.7 | 1.9 + {[3,)} | f | 3 | +(11 rows) + +SELECT nmr, lower_inc(nmr), lower_inf(nmr), upper_inc(nmr), upper_inf(nmr) FROM nummultirange_test ORDER BY nmr; + nmr | lower_inc | lower_inf | upper_inc | upper_inf +-----------------------+-----------+-----------+-----------+----------- + {} | f | f | f | f + {} | f | f | f | f + {} | f | f | f | f + {} | f | f | f | f + {(,5)} | f | t | f | f + {(,)} | f | t | f | t + {(,)} | f | t | f | t + {[1.1,2.2)} | t | f | f | f + {[1.7,1.7],[1.9,2.1)} | t | f | f | f + {[1.7,1.9)} | t | f | f | f + {[3,)} | t | f | f | t +(11 rows) + +SELECT * FROM nummultirange_test WHERE nmr = '{}'; + nmr +----- + {} + {} + {} + {} +(4 rows) + +SELECT * FROM nummultirange_test WHERE nmr = '{(,5)}'; + nmr +-------- + {(,5)} +(1 row) + +SELECT * FROM nummultirange_test WHERE nmr = '{[3,)}'; + nmr +-------- + {[3,)} +(1 row) + +SELECT * FROM nummultirange_test WHERE nmr = '{[1.7,1.7]}'; + nmr +----- +(0 rows) + +SELECT * FROM nummultirange_test WHERE nmr = '{[1.7,1.7],[1.9,2.1)}'; + nmr +----------------------- + {[1.7,1.7],[1.9,2.1)} +(1 row) + +SELECT * FROM nummultirange_test WHERE nmr < '{}'; + nmr +----- +(0 rows) + +SELECT * FROM nummultirange_test WHERE nmr < '{[-1000.0, -1000.0]}'; + nmr +-------- + {} + {(,)} + {(,)} + {(,5)} + {} + {} + {} +(7 rows) + +SELECT * FROM nummultirange_test WHERE nmr < '{[0.0, 1.0]}'; + nmr +-------- + {} + {(,)} + {(,)} + {(,5)} + {} + {} + {} +(7 rows) + +SELECT * FROM nummultirange_test WHERE nmr < '{[1000.0, 1001.0]}'; + nmr +----------------------- + {} + {(,)} + {[3,)} + {(,)} + {(,5)} + {} + {} + {[1.1,2.2)} + {} + {[1.7,1.9)} + {[1.7,1.7],[1.9,2.1)} +(11 rows) + +SELECT * FROM nummultirange_test WHERE nmr <= '{}'; + nmr +----- + {} + {} + {} + {} +(4 rows) + +SELECT * FROM nummultirange_test WHERE nmr <= '{[3,)}'; + nmr +----------------------- + {} + {(,)} + {[3,)} + {(,)} + {(,5)} + {} + {} + {[1.1,2.2)} + {} + {[1.7,1.9)} + {[1.7,1.7],[1.9,2.1)} +(11 rows) + +SELECT * FROM nummultirange_test WHERE nmr >= '{}'; + nmr +----------------------- + {} + {(,)} + {[3,)} + {(,)} + {(,5)} + {} + {} + {[1.1,2.2)} + {} + {[1.7,1.9)} + {[1.7,1.7],[1.9,2.1)} +(11 rows) + +SELECT * FROM nummultirange_test WHERE nmr >= '{[3,)}'; + nmr +-------- + {[3,)} +(1 row) + +SELECT * FROM nummultirange_test WHERE nmr > '{}'; + nmr +----------------------- + {(,)} + {[3,)} + {(,)} + {(,5)} + {[1.1,2.2)} + {[1.7,1.9)} + {[1.7,1.7],[1.9,2.1)} +(7 rows) + +SELECT * FROM nummultirange_test WHERE nmr > '{[-1000.0, -1000.0]}'; + nmr +----------------------- + {[3,)} + {[1.1,2.2)} + {[1.7,1.9)} + {[1.7,1.7],[1.9,2.1)} +(4 rows) + +SELECT * FROM nummultirange_test WHERE nmr > '{[0.0, 1.0]}'; + nmr +----------------------- + {[3,)} + {[1.1,2.2)} + {[1.7,1.9)} + {[1.7,1.7],[1.9,2.1)} +(4 rows) + +SELECT * FROM nummultirange_test WHERE nmr > '{[1000.0, 1001.0]}'; + nmr +----- +(0 rows) + +SELECT * FROM nummultirange_test WHERE nmr <> '{}'; + nmr +----------------------- + {(,)} + {[3,)} + {(,)} + {(,5)} + {[1.1,2.2)} + {[1.7,1.9)} + {[1.7,1.7],[1.9,2.1)} +(7 rows) + +SELECT * FROM nummultirange_test WHERE nmr <> '{(,5)}'; + nmr +----------------------- + {} + {(,)} + {[3,)} + {(,)} + {} + {} + {[1.1,2.2)} + {} + {[1.7,1.9)} + {[1.7,1.7],[1.9,2.1)} +(10 rows) + +select nummultirange(numrange(2.0, 1.0)); +ERROR: range lower bound must be less than or equal to range upper bound +select nummultirange(numrange(5.0, 6.0), numrange(1.0, 2.0)); + nummultirange +----------------------- + {[1.0,2.0),[5.0,6.0)} +(1 row) + +-- overlaps +SELECT * FROM nummultirange_test WHERE range_overlaps_multirange(numrange(4.0, 4.2), nmr); + nmr +-------- + {(,)} + {[3,)} + {(,)} + {(,5)} +(4 rows) + +SELECT * FROM nummultirange_test WHERE numrange(4.0, 4.2) && nmr; + nmr +-------- + {(,)} + {[3,)} + {(,)} + {(,5)} +(4 rows) + +SELECT * FROM nummultirange_test WHERE multirange_overlaps_range(nmr, numrange(4.0, 4.2)); + nmr +-------- + {(,)} + {[3,)} + {(,)} + {(,5)} +(4 rows) + +SELECT * FROM nummultirange_test WHERE nmr && numrange(4.0, 4.2); + nmr +-------- + {(,)} + {[3,)} + {(,)} + {(,5)} +(4 rows) + +SELECT * FROM nummultirange_test WHERE multirange_overlaps_multirange(nmr, nummultirange(numrange(4.0, 4.2), numrange(6.0, 7.0))); + nmr +-------- + {(,)} + {[3,)} + {(,)} + {(,5)} +(4 rows) + +SELECT * FROM nummultirange_test WHERE nmr && nummultirange(numrange(4.0, 4.2), numrange(6.0, 7.0)); + nmr +-------- + {(,)} + {[3,)} + {(,)} + {(,5)} +(4 rows) + +SELECT * FROM nummultirange_test WHERE nmr && nummultirange(numrange(6.0, 7.0)); + nmr +-------- + {(,)} + {[3,)} + {(,)} +(3 rows) + +SELECT * FROM nummultirange_test WHERE nmr && nummultirange(numrange(6.0, 7.0), numrange(8.0, 9.0)); + nmr +-------- + {(,)} + {[3,)} + {(,)} +(3 rows) + +-- mr contains x +SELECT * FROM nummultirange_test WHERE multirange_contains_elem(nmr, 4.0); + nmr +-------- + {(,)} + {[3,)} + {(,)} + {(,5)} +(4 rows) + +SELECT * FROM nummultirange_test WHERE nmr @> 4.0; + nmr +-------- + {(,)} + {[3,)} + {(,)} + {(,5)} +(4 rows) + +SELECT * FROM nummultirange_test WHERE multirange_contains_range(nmr, numrange(4.0, 4.2)); + nmr +-------- + {(,)} + {[3,)} + {(,)} + {(,5)} +(4 rows) + +SELECT * FROM nummultirange_test WHERE nmr @> numrange(4.0, 4.2); + nmr +-------- + {(,)} + {[3,)} + {(,)} + {(,5)} +(4 rows) + +SELECT * FROM nummultirange_test WHERE multirange_contains_multirange(nmr, '{[4.0,4.2), [6.0, 8.0)}'); + nmr +-------- + {(,)} + {[3,)} + {(,)} +(3 rows) + +SELECT * FROM nummultirange_test WHERE nmr @> '{[4.0,4.2), [6.0, 8.0)}'::nummultirange; + nmr +-------- + {(,)} + {[3,)} + {(,)} +(3 rows) + +-- x is contained by mr +SELECT * FROM nummultirange_test WHERE elem_contained_by_multirange(4.0, nmr); + nmr +-------- + {(,)} + {[3,)} + {(,)} + {(,5)} +(4 rows) + +SELECT * FROM nummultirange_test WHERE 4.0 <@ nmr; + nmr +-------- + {(,)} + {[3,)} + {(,)} + {(,5)} +(4 rows) + +SELECT * FROM nummultirange_test WHERE range_contained_by_multirange(numrange(4.0, 4.2), nmr); + nmr +-------- + {(,)} + {[3,)} + {(,)} + {(,5)} +(4 rows) + +SELECT * FROM nummultirange_test WHERE numrange(4.0, 4.2) <@ nmr; + nmr +-------- + {(,)} + {[3,)} + {(,)} + {(,5)} +(4 rows) + +SELECT * FROM nummultirange_test WHERE multirange_contained_by_multirange('{[4.0,4.2), [6.0, 8.0)}', nmr); + nmr +-------- + {(,)} + {[3,)} + {(,)} +(3 rows) + +SELECT * FROM nummultirange_test WHERE '{[4.0,4.2), [6.0, 8.0)}'::nummultirange <@ nmr; + nmr +-------- + {(,)} + {[3,)} + {(,)} +(3 rows) + +-- overlaps +SELECT 'empty'::numrange && nummultirange(); + ?column? +---------- + f +(1 row) + +SELECT 'empty'::numrange && nummultirange(numrange(1,2)); + ?column? +---------- + f +(1 row) + +SELECT nummultirange() && 'empty'::numrange; + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(1,2)) && 'empty'::numrange; + ?column? +---------- + f +(1 row) + +SELECT nummultirange() && nummultirange(); + ?column? +---------- + f +(1 row) + +SELECT nummultirange() && nummultirange(numrange(1,2)); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(1,2)) && nummultirange(); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(3,4)) && nummultirange(numrange(1,2), numrange(7,8)); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(1,2), numrange(7,8)) && nummultirange(numrange(3,4)); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(3,4)) && nummultirange(numrange(1,2), numrange(3.5,8)); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(1,2), numrange(3.5,8)) && numrange(3,4); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(1,2), numrange(3.5,8)) && nummultirange(numrange(3,4)); + ?column? +---------- + t +(1 row) + +-- contains +SELECT nummultirange() @> nummultirange(); + ?column? +---------- + t +(1 row) + +SELECT nummultirange() @> 'empty'::numrange; + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(null,null)) @> numrange(1,2); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(null,null)) @> numrange(null,2); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(null,null)) @> numrange(2,null); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(null,5)) @> numrange(null,3); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(null,5)) @> numrange(null,8); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(5,null)) @> numrange(8,null); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(5,null)) @> numrange(3,null); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(1,5)) @> numrange(8,9); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(1,5)) @> numrange(3,9); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(1,5)) @> numrange(1,4); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(1,5)) @> numrange(1,5); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(-4,-2), numrange(1,5)) @> numrange(1,5); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(1,5), numrange(8,9)) @> numrange(1,5); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(1,5), numrange(8,9)) @> numrange(6,7); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(1,5), numrange(6,9)) @> numrange(6,7); + ?column? +---------- + t +(1 row) + +SELECT '{[1,5)}'::nummultirange @> '{[1,5)}'; + ?column? +---------- + t +(1 row) + +SELECT '{[-4,-2), [1,5)}'::nummultirange @> '{[1,5)}'; + ?column? +---------- + t +(1 row) + +SELECT '{[1,5), [8,9)}'::nummultirange @> '{[1,5)}'; + ?column? +---------- + t +(1 row) + +SELECT '{[1,5), [8,9)}'::nummultirange @> '{[6,7)}'; + ?column? +---------- + f +(1 row) + +SELECT '{[1,5), [6,9)}'::nummultirange @> '{[6,7)}'; + ?column? +---------- + t +(1 row) + +-- is contained by +SELECT nummultirange() <@ nummultirange(); + ?column? +---------- + t +(1 row) + +SELECT 'empty'::numrange <@ nummultirange(); + ?column? +---------- + t +(1 row) + +SELECT numrange(1,2) <@ nummultirange(numrange(null,null)); + ?column? +---------- + t +(1 row) + +SELECT numrange(null,2) <@ nummultirange(numrange(null,null)); + ?column? +---------- + t +(1 row) + +SELECT numrange(2,null) <@ nummultirange(numrange(null,null)); + ?column? +---------- + t +(1 row) + +SELECT numrange(null,3) <@ nummultirange(numrange(null,5)); + ?column? +---------- + t +(1 row) + +SELECT numrange(null,8) <@ nummultirange(numrange(null,5)); + ?column? +---------- + f +(1 row) + +SELECT numrange(8,null) <@ nummultirange(numrange(5,null)); + ?column? +---------- + t +(1 row) + +SELECT numrange(3,null) <@ nummultirange(numrange(5,null)); + ?column? +---------- + f +(1 row) + +SELECT numrange(8,9) <@ nummultirange(numrange(1,5)); + ?column? +---------- + f +(1 row) + +SELECT numrange(3,9) <@ nummultirange(numrange(1,5)); + ?column? +---------- + f +(1 row) + +SELECT numrange(1,4) <@ nummultirange(numrange(1,5)); + ?column? +---------- + t +(1 row) + +SELECT numrange(1,5) <@ nummultirange(numrange(1,5)); + ?column? +---------- + t +(1 row) + +SELECT numrange(1,5) <@ nummultirange(numrange(-4,-2), numrange(1,5)); + ?column? +---------- + t +(1 row) + +SELECT numrange(1,5) <@ nummultirange(numrange(1,5), numrange(8,9)); + ?column? +---------- + t +(1 row) + +SELECT numrange(6,7) <@ nummultirange(numrange(1,5), numrange(8,9)); + ?column? +---------- + f +(1 row) + +SELECT numrange(6,7) <@ nummultirange(numrange(1,5), numrange(6,9)); + ?column? +---------- + t +(1 row) + +SELECT '{[1,5)}' <@ '{[1,5)}'::nummultirange; + ?column? +---------- + t +(1 row) + +SELECT '{[1,5)}' <@ '{[-4,-2), [1,5)}'::nummultirange; + ?column? +---------- + t +(1 row) + +SELECT '{[1,5)}' <@ '{[1,5), [8,9)}'::nummultirange; + ?column? +---------- + t +(1 row) + +SELECT '{[6,7)}' <@ '{[1,5), [8,9)}'::nummultirange; + ?column? +---------- + f +(1 row) + +SELECT '{[6,7)}' <@ '{[1,5), [6,9)}'::nummultirange; + ?column? +---------- + t +(1 row) + +-- overleft +SELECT 'empty'::numrange &< nummultirange(); + ?column? +---------- + f +(1 row) + +SELECT 'empty'::numrange &< nummultirange(numrange(1,2)); + ?column? +---------- + f +(1 row) + +SELECT nummultirange() &< 'empty'::numrange; + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(1,2)) &< 'empty'::numrange; + ?column? +---------- + f +(1 row) + +SELECT nummultirange() &< nummultirange(); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(1,2)) &< nummultirange(); + ?column? +---------- + f +(1 row) + +SELECT nummultirange() &< nummultirange(numrange(1,2)); + ?column? +---------- + f +(1 row) + +SELECT numrange(6,7) &< nummultirange(numrange(3,4)); + ?column? +---------- + f +(1 row) + +SELECT numrange(1,2) &< nummultirange(numrange(3,4)); + ?column? +---------- + t +(1 row) + +SELECT numrange(1,4) &< nummultirange(numrange(3,4)); + ?column? +---------- + t +(1 row) + +SELECT numrange(1,6) &< nummultirange(numrange(3,4)); + ?column? +---------- + f +(1 row) + +SELECT numrange(3.5,6) &< nummultirange(numrange(3,4)); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(6,7)) &< numrange(3,4); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(1,2)) &< numrange(3,4); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(1,4)) &< numrange(3,4); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(1,6)) &< numrange(3,4); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(3.5,6)) &< numrange(3,4); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(6,7)) &< nummultirange(numrange(3,4)); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(1,2)) &< nummultirange(numrange(3,4)); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(1,4)) &< nummultirange(numrange(3,4)); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(1,6)) &< nummultirange(numrange(3,4)); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(3.5,6)) &< nummultirange(numrange(3,4)); + ?column? +---------- + f +(1 row) + +-- overright +SELECT nummultirange() &> 'empty'::numrange; + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(1,2)) &> 'empty'::numrange; + ?column? +---------- + f +(1 row) + +SELECT 'empty'::numrange &> nummultirange(); + ?column? +---------- + f +(1 row) + +SELECT 'empty'::numrange &> nummultirange(numrange(1,2)); + ?column? +---------- + f +(1 row) + +SELECT nummultirange() &> nummultirange(); + ?column? +---------- + f +(1 row) + +SELECT nummultirange() &> nummultirange(numrange(1,2)); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(1,2)) &> nummultirange(); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(3,4)) &> numrange(6,7); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(3,4)) &> numrange(1,2); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(3,4)) &> numrange(1,4); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(3,4)) &> numrange(1,6); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(3,4)) &> numrange(3.5,6); + ?column? +---------- + f +(1 row) + +SELECT numrange(3,4) &> nummultirange(numrange(6,7)); + ?column? +---------- + f +(1 row) + +SELECT numrange(3,4) &> nummultirange(numrange(1,2)); + ?column? +---------- + t +(1 row) + +SELECT numrange(3,4) &> nummultirange(numrange(1,4)); + ?column? +---------- + t +(1 row) + +SELECT numrange(3,4) &> nummultirange(numrange(1,6)); + ?column? +---------- + t +(1 row) + +SELECT numrange(3,4) &> nummultirange(numrange(3.5,6)); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(3,4)) &> nummultirange(numrange(6,7)); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(3,4)) &> nummultirange(numrange(1,2)); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(3,4)) &> nummultirange(numrange(1,4)); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(3,4)) &> nummultirange(numrange(1,6)); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(3,4)) &> nummultirange(numrange(3.5,6)); + ?column? +---------- + f +(1 row) + +-- meets +SELECT 'empty'::numrange -|- nummultirange(); + ?column? +---------- + f +(1 row) + +SELECT 'empty'::numrange -|- nummultirange(numrange(1,2)); + ?column? +---------- + f +(1 row) + +SELECT nummultirange() -|- 'empty'::numrange; + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(1,2)) -|- 'empty'::numrange; + ?column? +---------- + f +(1 row) + +SELECT nummultirange() -|- nummultirange(); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(1,2)) -|- nummultirange(); + ?column? +---------- + f +(1 row) + +SELECT nummultirange() -|- nummultirange(numrange(1,2)); + ?column? +---------- + f +(1 row) + +SELECT numrange(1,2) -|- nummultirange(numrange(2,4)); + ?column? +---------- + t +(1 row) + +SELECT numrange(1,2) -|- nummultirange(numrange(3,4)); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(1,2)) -|- numrange(2,4); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(1,2)) -|- numrange(3,4); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(1,2)) -|- nummultirange(numrange(2,4)); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(1,2)) -|- nummultirange(numrange(3,4)); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(1,2), numrange(5,6)) -|- nummultirange(numrange(3,4)); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(1,2), numrange(5,6)) -|- nummultirange(numrange(6,7)); + ?column? +---------- + t +(1 row) + +SELECT nummultirange(numrange(1,2), numrange(5,6)) -|- nummultirange(numrange(8,9)); + ?column? +---------- + f +(1 row) + +SELECT nummultirange(numrange(1,2)) -|- nummultirange(numrange(2,4), numrange(6,7)); + ?column? +---------- + t +(1 row) + +-- strictly left +select 'empty'::numrange << nummultirange(); + ?column? +---------- + f +(1 row) + +select numrange(1,2) << nummultirange(); + ?column? +---------- + f +(1 row) + +select numrange(1,2) << nummultirange(numrange(3,4)); + ?column? +---------- + t +(1 row) + +select numrange(1,2) << nummultirange(numrange(0,4)); + ?column? +---------- + f +(1 row) + +select numrange(1,2) << nummultirange(numrange(0,4), numrange(7,8)); + ?column? +---------- + f +(1 row) + +select nummultirange() << 'empty'::numrange; + ?column? +---------- + f +(1 row) + +select nummultirange() << numrange(1,2); + ?column? +---------- + f +(1 row) + +select nummultirange(numrange(3,4)) << numrange(3,6); + ?column? +---------- + f +(1 row) + +select nummultirange(numrange(0,2)) << numrange(3,6); + ?column? +---------- + t +(1 row) + +select nummultirange(numrange(0,2), numrange(7,8)) << numrange(3,6); + ?column? +---------- + f +(1 row) + +select nummultirange(numrange(-4,-2), numrange(0,2)) << numrange(3,6); + ?column? +---------- + t +(1 row) + +select nummultirange() << nummultirange(); + ?column? +---------- + f +(1 row) + +select nummultirange() << nummultirange(numrange(1,2)); + ?column? +---------- + f +(1 row) + +select nummultirange(numrange(1,2)) << nummultirange(); + ?column? +---------- + f +(1 row) + +select nummultirange(numrange(1,2)) << nummultirange(numrange(1,2)); + ?column? +---------- + f +(1 row) + +select nummultirange(numrange(1,2)) << nummultirange(numrange(3,4)); + ?column? +---------- + t +(1 row) + +select nummultirange(numrange(1,2)) << nummultirange(numrange(3,4), numrange(7,8)); + ?column? +---------- + t +(1 row) + +select nummultirange(numrange(1,2), numrange(4,5)) << nummultirange(numrange(3,4), numrange(7,8)); + ?column? +---------- + f +(1 row) + +-- strictly right +select nummultirange() >> 'empty'::numrange; + ?column? +---------- + f +(1 row) + +select nummultirange() >> numrange(1,2); + ?column? +---------- + f +(1 row) + +select nummultirange(numrange(3,4)) >> numrange(1,2); + ?column? +---------- + t +(1 row) + +select nummultirange(numrange(0,4)) >> numrange(1,2); + ?column? +---------- + f +(1 row) + +select nummultirange(numrange(0,4), numrange(7,8)) >> numrange(1,2); + ?column? +---------- + f +(1 row) + +select 'empty'::numrange >> nummultirange(); + ?column? +---------- + f +(1 row) + +select numrange(1,2) >> nummultirange(); + ?column? +---------- + f +(1 row) + +select numrange(3,6) >> nummultirange(numrange(3,4)); + ?column? +---------- + f +(1 row) + +select numrange(3,6) >> nummultirange(numrange(0,2)); + ?column? +---------- + t +(1 row) + +select numrange(3,6) >> nummultirange(numrange(0,2), numrange(7,8)); + ?column? +---------- + f +(1 row) + +select numrange(3,6) >> nummultirange(numrange(-4,-2), numrange(0,2)); + ?column? +---------- + t +(1 row) + +select nummultirange() >> nummultirange(); + ?column? +---------- + f +(1 row) + +select nummultirange(numrange(1,2)) >> nummultirange(); + ?column? +---------- + f +(1 row) + +select nummultirange() >> nummultirange(numrange(1,2)); + ?column? +---------- + f +(1 row) + +select nummultirange(numrange(1,2)) >> nummultirange(numrange(1,2)); + ?column? +---------- + f +(1 row) + +select nummultirange(numrange(3,4)) >> nummultirange(numrange(1,2)); + ?column? +---------- + t +(1 row) + +select nummultirange(numrange(3,4), numrange(7,8)) >> nummultirange(numrange(1,2)); + ?column? +---------- + t +(1 row) + +select nummultirange(numrange(3,4), numrange(7,8)) >> nummultirange(numrange(1,2), numrange(4,5)); + ?column? +---------- + f +(1 row) + +-- union +SELECT 'empty'::numrange @+ 'empty'::numrange; + ?column? +---------- + {} +(1 row) + +SELECT 'empty'::numrange @+ nummultirange(); + ?column? +---------- + {} +(1 row) + +SELECT nummultirange() @+ 'empty'::numrange; + ?column? +---------- + {} +(1 row) + +SELECT nummultirange() @+ nummultirange(); + ?column? +---------- + {} +(1 row) + +SELECT 'empty'::numrange @+ numrange(1,2); + ?column? +---------- + {[1,2)} +(1 row) + +SELECT 'empty'::numrange @+ nummultirange(numrange(1,2)); + ?column? +---------- + {[1,2)} +(1 row) + +SELECT numrange(1,2) @+ nummultirange(); + ?column? +---------- + {[1,2)} +(1 row) + +SELECT nummultirange(numrange(1,2)) @+ 'empty'::numrange; + ?column? +---------- + {[1,2)} +(1 row) + +SELECT numrange(1,2) @+ 'empty'::numrange; + ?column? +---------- + {[1,2)} +(1 row) + +SELECT nummultirange() @+ numrange(1,2); + ?column? +---------- + {[1,2)} +(1 row) + +SELECT nummultirange() @+ nummultirange(numrange(1,2)); + ?column? +---------- + {[1,2)} +(1 row) + +SELECT nummultirange(numrange(1,2)) @+ nummultirange(); + ?column? +---------- + {[1,2)} +(1 row) + +SELECT numrange(1,2) @+ numrange(1,2); + ?column? +---------- + {[1,2)} +(1 row) + +SELECT numrange(1,2) @+ numrange(2,4); + ?column? +---------- + {[1,4)} +(1 row) + +SELECT numrange(1,2) @+ numrange(3,4); + ?column? +--------------- + {[1,2),[3,4)} +(1 row) + +SELECT nummultirange(numrange(1,2)) @+ nummultirange(numrange(1,2)); + ?column? +---------- + {[1,2)} +(1 row) + +SELECT nummultirange(numrange(1,2)) @+ nummultirange(numrange(2,4)); + ?column? +---------- + {[1,4)} +(1 row) + +SELECT nummultirange(numrange(1,2)) @+ nummultirange(numrange(3,4)); + ?column? +--------------- + {[1,2),[3,4)} +(1 row) + +SELECT nummultirange(numrange(1,2), numrange(4,5)) @+ nummultirange(numrange(2,4)); + ?column? +---------- + {[1,5)} +(1 row) + +SELECT nummultirange(numrange(1,2), numrange(4,5)) @+ nummultirange(numrange(3,4)); + ?column? +--------------- + {[1,2),[3,5)} +(1 row) + +SELECT nummultirange(numrange(1,2), numrange(4,5)) @+ nummultirange(numrange(0,9)); + ?column? +---------- + {[0,9)} +(1 row) + +-- merge +SELECT range_merge(nummultirange()); + range_merge +------------- + empty +(1 row) + +SELECT range_merge(nummultirange(numrange(1,2))); + range_merge +------------- + [1,2) +(1 row) + +SELECT range_merge(nummultirange(numrange(1,2), numrange(7,8))); + range_merge +------------- + [1,8) +(1 row) + +-- minus +SELECT 'empty'::numrange @- 'empty'::numrange; + ?column? +---------- + {} +(1 row) + +SELECT 'empty'::numrange @- nummultirange(); + ?column? +---------- + {} +(1 row) + +SELECT nummultirange() @- 'empty'::numrange; + ?column? +---------- + {} +(1 row) + +SELECT nummultirange() @- nummultirange(); + ?column? +---------- + {} +(1 row) + +SELECT 'empty'::numrange @- numrange(1,2); + ?column? +---------- + {} +(1 row) + +SELECT 'empty'::numrange @- nummultirange(numrange(1,2)); + ?column? +---------- + {} +(1 row) + +SELECT numrange(1,2) @- nummultirange(); + ?column? +---------- + {[1,2)} +(1 row) + +SELECT nummultirange(numrange(1,2)) @- 'empty'::numrange; + ?column? +---------- + {[1,2)} +(1 row) + +SELECT 'empty'::numrange @- numrange(1,2); + ?column? +---------- + {} +(1 row) + +SELECT numrange(1,2) @- 'empty'::numrange; + ?column? +---------- + {[1,2)} +(1 row) + +SELECT nummultirange() @- numrange(1,2); + ?column? +---------- + {} +(1 row) + +SELECT nummultirange() @- nummultirange(numrange(1,2)); + ?column? +---------- + {} +(1 row) + +SELECT nummultirange(numrange(1,2)) @- nummultirange(); + ?column? +---------- + {[1,2)} +(1 row) + +SELECT numrange(1,3) @- numrange(1,3); + ?column? +---------- + {} +(1 row) + +SELECT numrange(1,3) @- numrange(1,2); + ?column? +---------- + {[2,3)} +(1 row) + +SELECT numrange(1,3) @- numrange(2,4); + ?column? +---------- + {[1,2)} +(1 row) + +SELECT numrange(1,3) @- numrange(3,4); + ?column? +---------- + {[1,3)} +(1 row) + +SELECT numrange(1,3) @- nummultirange(numrange(1,3)); + ?column? +---------- + {} +(1 row) + +SELECT numrange(1,3) @- nummultirange(numrange(1,2)); + ?column? +---------- + {[2,3)} +(1 row) + +SELECT numrange(1,3) @- nummultirange(numrange(2,4)); + ?column? +---------- + {[1,2)} +(1 row) + +SELECT numrange(1,3) @- nummultirange(numrange(3,4)); + ?column? +---------- + {[1,3)} +(1 row) + +SELECT numrange(1,10) @- nummultirange(numrange(1,2), numrange(4,5)); + ?column? +---------------- + {[2,4),[5,10)} +(1 row) + +SELECT nummultirange(numrange(1,10)) @- numrange(0,2); + ?column? +---------- + {[2,10)} +(1 row) + +SELECT nummultirange(numrange(1,10)) @- numrange(1,2); + ?column? +---------- + {[2,10)} +(1 row) + +SELECT nummultirange(numrange(1,10)) @- numrange(3,4); + ?column? +---------------- + {[1,3),[4,10)} +(1 row) + +SELECT nummultirange(numrange(1,10)) @- numrange(13,18); + ?column? +---------- + {[1,10)} +(1 row) + +SELECT nummultirange(numrange(1,2), numrange(3,4)) @- nummultirange(); + ?column? +--------------- + {[1,2),[3,4)} +(1 row) + +SELECT nummultirange(numrange(1,2)) @- nummultirange(numrange(1,2)); + ?column? +---------- + {} +(1 row) + +SELECT nummultirange(numrange(1,2)) @- nummultirange(numrange(2,4)); + ?column? +---------- + {[1,2)} +(1 row) + +SELECT nummultirange(numrange(1,2)) @- nummultirange(numrange(3,4)); + ?column? +---------- + {[1,2)} +(1 row) + +SELECT nummultirange(numrange(1,4)) @- nummultirange(numrange(1,2)); + ?column? +---------- + {[2,4)} +(1 row) + +SELECT nummultirange(numrange(1,4)) @- nummultirange(numrange(2,3)); + ?column? +--------------- + {[1,2),[3,4)} +(1 row) + +SELECT nummultirange(numrange(1,4)) @- nummultirange(numrange(0,8)); + ?column? +---------- + {} +(1 row) + +SELECT nummultirange(numrange(1,4)) @- nummultirange(numrange(0,2)); + ?column? +---------- + {[2,4)} +(1 row) + +SELECT nummultirange(numrange(1,8)) @- nummultirange(numrange(0,2), numrange(3,4)); + ?column? +--------------- + {[2,3),[4,8)} +(1 row) + +SELECT nummultirange(numrange(1,8)) @- nummultirange(numrange(2,3), numrange(5,null)); + ?column? +--------------- + {[1,2),[3,5)} +(1 row) + +SELECT nummultirange(numrange(1,2), numrange(4,5)) @- nummultirange(numrange(-2,0)); + ?column? +--------------- + {[1,2),[4,5)} +(1 row) + +SELECT nummultirange(numrange(1,2), numrange(4,5)) @- nummultirange(numrange(2,4)); + ?column? +--------------- + {[1,2),[4,5)} +(1 row) + +SELECT nummultirange(numrange(1,2), numrange(4,5)) @- nummultirange(numrange(3,5)); + ?column? +---------- + {[1,2)} +(1 row) + +SELECT nummultirange(numrange(1,2), numrange(4,5)) @- nummultirange(numrange(0,9)); + ?column? +---------- + {} +(1 row) + +SELECT nummultirange(numrange(1,3), numrange(4,5)) @- nummultirange(numrange(2,9)); + ?column? +---------- + {[1,2)} +(1 row) + +SELECT nummultirange(numrange(1,2), numrange(4,5)) @- nummultirange(numrange(8,9)); + ?column? +--------------- + {[1,2),[4,5)} +(1 row) + +SELECT nummultirange(numrange(1,2), numrange(4,5)) @- nummultirange(numrange(-2,0), numrange(8,9)); + ?column? +--------------- + {[1,2),[4,5)} +(1 row) + +-- intersection +SELECT 'empty'::numrange @* 'empty'::numrange; + ?column? +---------- + {} +(1 row) + +SELECT 'empty'::numrange @* nummultirange(); + ?column? +---------- + {} +(1 row) + +SELECT nummultirange() @* 'empty'::numrange; + ?column? +---------- + {} +(1 row) + +SELECT nummultirange() @* nummultirange(); + ?column? +---------- + {} +(1 row) + +SELECT 'empty'::numrange @* numrange(1,2); + ?column? +---------- + {} +(1 row) + +SELECT 'empty'::numrange @* nummultirange(numrange(1,2)); + ?column? +---------- + {} +(1 row) + +SELECT numrange(1,2) @* 'empty'::numrange; + ?column? +---------- + {} +(1 row) + +SELECT numrange(1,2) @* nummultirange(); + ?column? +---------- + {} +(1 row) + +SELECT nummultirange(numrange(1,2)) @* 'empty'::numrange; + ?column? +---------- + {} +(1 row) + +SELECT nummultirange() @* numrange(1,2); + ?column? +---------- + {} +(1 row) + +SELECT nummultirange() @* nummultirange(numrange(1,2)); + ?column? +---------- + {} +(1 row) + +SELECT nummultirange(numrange(1,2)) @* nummultirange(); + ?column? +---------- + {} +(1 row) + +SELECT numrange(1,3) @* numrange(1,3); + ?column? +---------- + {[1,3)} +(1 row) + +SELECT numrange(1,3) @* numrange(1,2); + ?column? +---------- + {[1,2)} +(1 row) + +SELECT numrange(1,3) @* numrange(1,5); + ?column? +---------- + {[1,3)} +(1 row) + +SELECT numrange(1,3) @* numrange(2,5); + ?column? +---------- + {[2,3)} +(1 row) + +SELECT numrange(1,5) @* numrange(2,3); + ?column? +---------- + {[2,3)} +(1 row) + +SELECT '{[1,3)}'::nummultirange @* numrange(1,3); + ?column? +---------- + {[1,3)} +(1 row) + +SELECT '{[1,3)}'::nummultirange @* numrange(1,2); + ?column? +---------- + {[1,2)} +(1 row) + +SELECT '{[1,3)}'::nummultirange @* numrange(1,5); + ?column? +---------- + {[1,3)} +(1 row) + +SELECT '{[1,3)}'::nummultirange @* numrange(2,5); + ?column? +---------- + {[2,3)} +(1 row) + +SELECT '{[1,5)}'::nummultirange @* numrange(2,3); + ?column? +---------- + {[2,3)} +(1 row) + +SELECT numrange(1,3) @* '{[1,3)}'::nummultirange; + ?column? +---------- + {[1,3)} +(1 row) + +SELECT numrange(1,2) @* '{[1,3)}'::nummultirange; + ?column? +---------- + {[1,2)} +(1 row) + +SELECT numrange(1,5) @* '{[1,3)}'::nummultirange; + ?column? +---------- + {[1,3)} +(1 row) + +SELECT numrange(2,5) @* '{[1,3)}'::nummultirange; + ?column? +---------- + {[2,3)} +(1 row) + +SELECT numrange(2,3) @* '{[1,5)}'::nummultirange; + ?column? +---------- + {[2,3)} +(1 row) + +SELECT '{[1,3)}'::nummultirange @* '{[1,5)}'::nummultirange; + ?column? +---------- + {[1,3)} +(1 row) + +SELECT '{[1,3)}'::nummultirange @* '{[0,5)}'::nummultirange; + ?column? +---------- + {[1,3)} +(1 row) + +SELECT '{[1,3)}'::nummultirange @* '{[0,2)}'::nummultirange; + ?column? +---------- + {[1,2)} +(1 row) + +SELECT '{[1,3)}'::nummultirange @* '{[2,5)}'::nummultirange; + ?column? +---------- + {[2,3)} +(1 row) + +SELECT '{[1,4)}'::nummultirange @* '{[2,3)}'::nummultirange; + ?column? +---------- + {[2,3)} +(1 row) + +SELECT '{[1,4)}'::nummultirange @* '{[0,2), [3,5)}'::nummultirange; + ?column? +--------------- + {[1,2),[3,4)} +(1 row) + +SELECT '{[1,4), [7,10)}'::nummultirange @* '{[0,8), [9,12)}'::nummultirange; + ?column? +---------------------- + {[1,4),[7,8),[9,10)} +(1 row) + +SELECT '{[1,4), [7,10)}'::nummultirange @* '{[9,12)}'::nummultirange; + ?column? +---------- + {[9,10)} +(1 row) + +SELECT '{[1,4), [7,10)}'::nummultirange @* '{[-5,-4), [5,6), [9,12)}'::nummultirange; + ?column? +---------- + {[9,10)} +(1 row) + +SELECT '{[1,4), [7,10)}'::nummultirange @* '{[0,2), [3,8), [9,12)}'::nummultirange; + ?column? +---------------------------- + {[1,2),[3,4),[7,8),[9,10)} +(1 row) + +SELECT '{[1,4), [7,10)}'::nummultirange @* '{[0,2), [3,8), [9,12)}'::nummultirange; + ?column? +---------------------------- + {[1,2),[3,4),[7,8),[9,10)} +(1 row) + +-- +-- range_agg function +-- +create table reservations ( room_id integer not null, booked_during daterange ); +insert into reservations values +-- 1: has a meets and a gap +(1, daterange('2018-07-01', '2018-07-07')), +(1, daterange('2018-07-07', '2018-07-14')), +(1, daterange('2018-07-20', '2018-07-22')), +-- 2: just a single row +(2, daterange('2018-07-01', '2018-07-03')), +-- 3: one null range +(3, NULL), +-- 4: two null ranges +(4, NULL), +(4, NULL), +-- 5: a null range and a non-null range +(5, NULL), +(5, daterange('2018-07-01', '2018-07-03')), +-- 6: has overlap +(6, daterange('2018-07-01', '2018-07-07')), +(6, daterange('2018-07-05', '2018-07-10')), +-- 7: two ranges that meet: no gap or overlap +(7, daterange('2018-07-01', '2018-07-07')), +(7, daterange('2018-07-07', '2018-07-14')), +-- 8: an empty range +(8, 'empty'::daterange) +; +SELECT room_id, range_agg(booked_during) +FROM reservations +GROUP BY room_id +ORDER BY room_id; + room_id | range_agg +---------+--------------------------------------------------- + 1 | {[07-01-2018,07-14-2018),[07-20-2018,07-22-2018)} + 2 | {[07-01-2018,07-03-2018)} + 3 | + 4 | + 5 | {[07-01-2018,07-03-2018)} + 6 | {[07-01-2018,07-10-2018)} + 7 | {[07-01-2018,07-14-2018)} + 8 | {} +(8 rows) + +-- range_agg on a custom range type too +SELECT range_agg(r) +FROM (VALUES + ('[a,c]'::textrange), + ('[b,b]'::textrange), + ('[c,f]'::textrange), + ('[g,h)'::textrange), + ('[h,j)'::textrange) + ) t(r); + range_agg +--------------- + {[a,f],[g,j)} +(1 row) + +select range_intersect_agg(nmr) from nummultirange_test; + range_intersect_agg +--------------------- + {} +(1 row) + +select range_intersect_agg(nmr) from nummultirange_test where false; + range_intersect_agg +--------------------- + +(1 row) + +-- test with just one input: +select range_intersect_agg(nmr) from (values ('{[1,2]}'::nummultirange)) t(nmr); + range_intersect_agg +--------------------- + {[1,2]} +(1 row) + +select range_intersect_agg(nmr) from nummultirange_test where nmr @> 4.0; + range_intersect_agg +--------------------- + {[3,5)} +(1 row) + +create table nummultirange_test2(nmr nummultirange); +create index nummultirange_test2_hash_idx on nummultirange_test2 using hash (nmr); +INSERT INTO nummultirange_test2 VALUES('{[, 5)}'); +INSERT INTO nummultirange_test2 VALUES(nummultirange(numrange(1.1, 2.2))); +INSERT INTO nummultirange_test2 VALUES(nummultirange(numrange(1.1, 2.2))); +INSERT INTO nummultirange_test2 VALUES(nummultirange(numrange(1.1, 2.2,'()'))); +INSERT INTO nummultirange_test2 VALUES('{}'); +select * from nummultirange_test2 where nmr = '{}'; + nmr +----- + {} +(1 row) + +select * from nummultirange_test2 where nmr = nummultirange(numrange(1.1, 2.2)); + nmr +------------- + {[1.1,2.2)} + {[1.1,2.2)} +(2 rows) + +select * from nummultirange_test2 where nmr = nummultirange(numrange(1.1, 2.3)); + nmr +----- +(0 rows) + +set enable_nestloop=t; +set enable_hashjoin=f; +set enable_mergejoin=f; +select * from nummultirange_test natural join nummultirange_test2 order by nmr; + nmr +------------- + {} + {} + {} + {} + {(,5)} + {[1.1,2.2)} + {[1.1,2.2)} +(7 rows) + +set enable_nestloop=f; +set enable_hashjoin=t; +set enable_mergejoin=f; +select * from nummultirange_test natural join nummultirange_test2 order by nmr; + nmr +------------- + {} + {} + {} + {} + {(,5)} + {[1.1,2.2)} + {[1.1,2.2)} +(7 rows) + +set enable_nestloop=f; +set enable_hashjoin=f; +set enable_mergejoin=t; +select * from nummultirange_test natural join nummultirange_test2 order by nmr; + nmr +------------- + {} + {} + {} + {} + {(,5)} + {[1.1,2.2)} + {[1.1,2.2)} +(7 rows) + +set enable_nestloop to default; +set enable_hashjoin to default; +set enable_mergejoin to default; +DROP TABLE nummultirange_test2; +-- +-- Test user-defined multirange of floats +-- +select '{[123.001, 5.e9)}'::float8multirange @> 888.882::float8; + ?column? +---------- + t +(1 row) + +create table float8multirange_test(f8mr float8multirange, i int); +insert into float8multirange_test values(float8multirange(float8range(-100.00007, '1.111113e9')), 42); +select * from float8multirange_test; + f8mr | i +---------------------------+---- + {[-100.00007,1111113000)} | 42 +(1 row) + +drop table float8multirange_test; +-- +-- Test multirange types over domains +-- +create domain mydomain as int4; +create type mydomainrange as range(subtype=mydomain); +select '{[4,50)}'::mydomainmultirange @> 7::mydomain; + ?column? +---------- + t +(1 row) + +drop domain mydomain cascade; +NOTICE: drop cascades to type mydomainrange +-- +-- Test domains over multirange types +-- +create domain restrictedmultirange as int4multirange check (upper(value) < 10); +select '{[4,5)}'::restrictedmultirange @> 7; + ?column? +---------- + f +(1 row) + +select '{[4,50)}'::restrictedmultirange @> 7; -- should fail +ERROR: value for domain restrictedmultirange violates check constraint "restrictedmultirange_check" +drop domain restrictedmultirange; +-- +-- Test multiple multirange types over the same subtype +-- +create type textrange1 as range(subtype=text, collation="C"); +create type textrange2 as range(subtype=text, collation="C"); +select textmultirange1(textrange2('a','Z')); -- should fail +ERROR: function textmultirange1(textrange2) does not exist +LINE 1: select textmultirange1(textrange2('a','Z')); + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +select textmultirange1(textrange1('a','Z')) @> 'b'::text; +ERROR: range lower bound must be less than or equal to range upper bound +select textmultirange2(textrange2('a','z')) @> 'b'::text; + ?column? +---------- + t +(1 row) + +drop type textrange1; +drop type textrange2; +-- +-- Test polymorphic type system +-- +create function anyarray_anymultirange_func(a anyarray, r anymultirange) + returns anyelement as 'select $1[1] + lower($2);' language sql; +select anyarray_anymultirange_func(ARRAY[1,2], int4multirange(int4range(10,20))); + anyarray_anymultirange_func +----------------------------- + 11 +(1 row) + +-- should fail +select anyarray_anymultirange_func(ARRAY[1,2], nummultirange(numrange(10,20))); +ERROR: function anyarray_anymultirange_func(integer[], nummultirange) does not exist +LINE 1: select anyarray_anymultirange_func(ARRAY[1,2], nummultirange... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +drop function anyarray_anymultirange_func(anyarray, anymultirange); +-- should fail +create function bogus_func(anyelement) + returns anymultirange as 'select int4multirange(int4range(1,10))' language sql; +ERROR: cannot determine result data type +DETAIL: A function returning "anyrange" or "anymultirange" must have at least one "anyrange" or "anymultirange" argument. +-- should fail +create function bogus_func(int) + returns anymultirange as 'select int4multirange(int4range(1,10))' language sql; +ERROR: cannot determine result data type +DETAIL: A function returning a polymorphic type must have at least one polymorphic argument. +create function range_add_bounds(anymultirange) + returns anyelement as 'select lower($1) + upper($1)' language sql; +select range_add_bounds(int4multirange(int4range(1, 17))); + range_add_bounds +------------------ + 18 +(1 row) + +select range_add_bounds(nummultirange(numrange(1.0001, 123.123))); + range_add_bounds +------------------ + 124.1231 +(1 row) + +create function multirangetypes_sql(q anymultirange, b anyarray, out c anyelement) + as $$ select upper($1) + $2[1] $$ + language sql; +select multirangetypes_sql(int4multirange(int4range(1,10)), ARRAY[2,20]); + multirangetypes_sql +--------------------- + 12 +(1 row) + +select multirangetypes_sql(nummultirange(numrange(1,10)), ARRAY[2,20]); -- match failure +ERROR: function multirangetypes_sql(nummultirange, integer[]) does not exist +LINE 1: select multirangetypes_sql(nummultirange(numrange(1,10)), AR... + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +-- +-- Arrays of multiranges +-- +select ARRAY[nummultirange(numrange(1.1, 1.2)), nummultirange(numrange(12.3, 155.5))]; + array +---------------------------------- + {"{[1.1,1.2)}","{[12.3,155.5)}"} +(1 row) + +create table i8mr_array (f1 int, f2 int8multirange[]); +insert into i8mr_array values (42, array[int8multirange(int8range(1,10)), int8multirange(int8range(2,20))]); +select * from i8mr_array; + f1 | f2 +----+------------------------- + 42 | {"{[1,10)}","{[2,20)}"} +(1 row) + +drop table i8mr_array; +-- +-- Multiranges of arrays +-- +select arraymultirange(arrayrange(ARRAY[1,2], ARRAY[2,1])); + arraymultirange +--------------------- + {["{1,2}","{2,1}")} +(1 row) + +select arraymultirange(arrayrange(ARRAY[2,1], ARRAY[1,2])); -- fail +ERROR: range lower bound must be less than or equal to range upper bound +select array[1,1] <@ arraymultirange(arrayrange(array[1,2], array[2,1])); + ?column? +---------- + f +(1 row) + +select array[1,3] <@ arraymultirange(arrayrange(array[1,2], array[2,1])); + ?column? +---------- + t +(1 row) + +-- +-- Ranges of composites +-- +create type two_ints as (a int, b int); +create type two_ints_range as range (subtype = two_ints); +-- with force_parallel_mode on, this exercises tqueue.c's range remapping +select *, row_to_json(upper(t)) as u from + (values (two_ints_multirange(two_ints_range(row(1,2), row(3,4)))), + (two_ints_multirange(two_ints_range(row(5,6), row(7,8))))) v(t); + t | u +---------------------+--------------- + {["(1,2)","(3,4)")} | {"a":3,"b":4} + {["(5,6)","(7,8)")} | {"a":7,"b":8} +(2 rows) + +drop type two_ints cascade; +NOTICE: drop cascades to type two_ints_range +-- +-- Check behavior when subtype lacks a hash function +-- +set enable_sort = off; -- try to make it pick a hash setop implementation +select '{(2,5)}'::cashmultirange except select '{(5,6)}'::cashmultirange; + cashmultirange +----------------- + {($2.00,$5.00)} +(1 row) + +reset enable_sort; +-- +-- OUT/INOUT/TABLE functions +-- +-- infer anymultirange from anymultirange +create function mr_outparam_succeed(i anymultirange, out r anymultirange, out t text) + as $$ select $1, 'foo'::text $$ language sql; +select * from mr_outparam_succeed(int4multirange(int4range(1,2))); + r | t +---------+----- + {[1,2)} | foo +(1 row) + +-- infer anyarray from anymultirange +create function mr_outparam_succeed2(i anymultirange, out r anyarray, out t text) + as $$ select ARRAY[upper($1)], 'foo'::text $$ language sql; +select * from mr_outparam_succeed2(int4multirange(int4range(1,2))); + r | t +-----+----- + {2} | foo +(1 row) + +-- infer anyrange from anymultirange +create function mr_outparam_succeed3(i anymultirange, out r anyrange, out t text) + as $$ select range_merge($1), 'foo'::text $$ language sql; +select * from mr_outparam_succeed3(int4multirange(int4range(1,2))); + r | t +-------+----- + [1,2) | foo +(1 row) + +-- infer anymultirange from anyrange +create function mr_outparam_succeed4(i anyrange, out r anymultirange, out t text) + as $$ select $1 @+ $1, 'foo'::text $$ language sql; +select * from mr_outparam_succeed4(int4range(1,2)); + r | t +---------+----- + {[1,2)} | foo +(1 row) + +-- infer anyelement from anymultirange +create function mr_inoutparam_succeed(out i anyelement, inout r anymultirange) + as $$ select upper($1), $1 $$ language sql; +select * from mr_inoutparam_succeed(int4multirange(int4range(1,2))); + i | r +---+--------- + 2 | {[1,2)} +(1 row) + +-- infer anyelement+anymultirange from anyelement+anymultirange +create function mr_table_succeed(i anyelement, r anymultirange) returns table(i anyelement, r anymultirange) + as $$ select $1, $2 $$ language sql; +select * from mr_table_succeed(123, int4multirange(int4range(1,11))); + i | r +-----+---------- + 123 | {[1,11)} +(1 row) + +-- should fail +create function mr_outparam_fail(i anyelement, out r anymultirange, out t text) + as $$ select '[1,10]', 'foo' $$ language sql; +ERROR: cannot determine result data type +DETAIL: A function returning "anyrange" or "anymultirange" must have at least one "anyrange" or "anymultirange" argument. +--should fail +create function mr_inoutparam_fail(inout i anyelement, out r anymultirange) + as $$ select $1, '[1,10]' $$ language sql; +ERROR: cannot determine result data type +DETAIL: A function returning "anyrange" or "anymultirange" must have at least one "anyrange" or "anymultirange" argument. +--should fail +create function mr_table_fail(i anyelement) returns table(i anyelement, r anymultirange) + as $$ select $1, '[1,10]' $$ language sql; +ERROR: cannot determine result data type +DETAIL: A function returning "anyrange" or "anymultirange" must have at least one "anyrange" or "anymultirange" argument. diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index 916e1016b5..e818eaea79 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -1101,13 +1101,15 @@ ORDER BY 1, 2; ?| | ?| ?|| | ?|| @ | ~ + @* | @* + @+ | @+ @@ | @@ @@@ | @@@ | | | ~<=~ | ~>=~ ~<~ | ~>~ ~= | ~= -(30 rows) +(32 rows) -- Likewise for negator pairs. SELECT DISTINCT o1.oprname AS op1, o2.oprname AS op2 diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out index 6fd16bddd1..1bfe426be6 100644 --- a/src/test/regress/expected/rangetypes.out +++ b/src/test/regress/expected/rangetypes.out @@ -518,6 +518,24 @@ select numrange(1.0, 2.0) * numrange(2.5, 3.0); empty (1 row) +select range_intersect_agg(nr) from numrange_test; + range_intersect_agg +--------------------- + empty +(1 row) + +select range_intersect_agg(nr) from numrange_test where false; + range_intersect_agg +--------------------- + +(1 row) + +select range_intersect_agg(nr) from numrange_test where nr @> 4.0; + range_intersect_agg +--------------------- + [3,5) +(1 row) + create table numrange_test2(nr numrange); create index numrange_test2_hash_idx on numrange_test2 using hash (nr); INSERT INTO numrange_test2 VALUES('[, 5)'); @@ -1284,7 +1302,7 @@ drop function anyarray_anyrange_func(anyarray, anyrange); create function bogus_func(anyelement) returns anyrange as 'select int4range(1,10)' language sql; ERROR: cannot determine result data type -DETAIL: A function returning "anyrange" must have at least one "anyrange" argument. +DETAIL: A function returning "anyrange" or "anymultirange" must have at least one "anyrange" or "anymultirange" argument. -- should fail create function bogus_func(int) returns anyrange as 'select int4range(1,10)' language sql; @@ -1392,6 +1410,7 @@ reset enable_sort; -- -- OUT/INOUT/TABLE functions -- +-- infer anyrange from anyrange create function outparam_succeed(i anyrange, out r anyrange, out t text) as $$ select $1, 'foo'::text $$ language sql; select * from outparam_succeed(int4range(1,2)); @@ -1400,6 +1419,16 @@ select * from outparam_succeed(int4range(1,2)); [1,2) | foo (1 row) +-- infer anyarray from anyrange +create function outparam_succeed2(i anyrange, out r anyarray, out t text) + as $$ select ARRAY[upper($1)], 'foo'::text $$ language sql; +select * from outparam_succeed2(int4range(int4range(1,2))); + r | t +-----+----- + {2} | foo +(1 row) + +-- infer anyelement from anyrange create function inoutparam_succeed(out i anyelement, inout r anyrange) as $$ select upper($1), $1 $$ language sql; select * from inoutparam_succeed(int4range(1,2)); @@ -1408,6 +1437,7 @@ select * from inoutparam_succeed(int4range(1,2)); 2 | [1,2) (1 row) +-- infer anyelement+anyrange from anyelement+anyrange create function table_succeed(i anyelement, r anyrange) returns table(i anyelement, r anyrange) as $$ select $1, $2 $$ language sql; select * from table_succeed(123, int4range(1,11)); @@ -1420,14 +1450,14 @@ select * from table_succeed(123, int4range(1,11)); create function outparam_fail(i anyelement, out r anyrange, out t text) as $$ select '[1,10]', 'foo' $$ language sql; ERROR: cannot determine result data type -DETAIL: A function returning "anyrange" must have at least one "anyrange" argument. +DETAIL: A function returning "anyrange" or "anymultirange" must have at least one "anyrange" or "anymultirange" argument. --should fail create function inoutparam_fail(inout i anyelement, out r anyrange) as $$ select $1, '[1,10]' $$ language sql; ERROR: cannot determine result data type -DETAIL: A function returning "anyrange" must have at least one "anyrange" argument. +DETAIL: A function returning "anyrange" or "anymultirange" must have at least one "anyrange" or "anymultirange" argument. --should fail create function table_fail(i anyelement) returns table(i anyelement, r anyrange) as $$ select $1, '[1,10]' $$ language sql; ERROR: cannot determine result data type -DETAIL: A function returning "anyrange" must have at least one "anyrange" argument. +DETAIL: A function returning "anyrange" or "anymultirange" must have at least one "anyrange" or "anymultirange" argument. diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out index 070de78e85..6f7fcf6326 100644 --- a/src/test/regress/expected/sanity_check.out +++ b/src/test/regress/expected/sanity_check.out @@ -92,6 +92,7 @@ num_exp_sqrt|t num_exp_sub|t num_input_test|f num_result|f +nummultirange_test|t onek|t onek2|t path_tbl|f @@ -172,6 +173,7 @@ quad_poly_tbl|t radix_text_tbl|t ramp|f real_city|f +reservations|f road|t shighway|t slow_emp4000|f diff --git a/src/test/regress/sql/multirangetypes.sql b/src/test/regress/sql/multirangetypes.sql index 8651ba3f3d..b3ede18f96 100644 --- a/src/test/regress/sql/multirangetypes.sql +++ b/src/test/regress/sql/multirangetypes.sql @@ -63,3 +63,610 @@ select textmultirange(); select textmultirange(textrange('a', 'c')); select textmultirange(textrange('a', 'c'), textrange('f', 'g')); select textmultirange(textrange('a', 'c'), textrange('b', 'd')); + +-- +-- create some test data and test the operators +-- + +CREATE TABLE nummultirange_test (nmr NUMMULTIRANGE); +CREATE INDEX nummultirange_test_btree ON nummultirange_test(nmr); + +INSERT INTO nummultirange_test VALUES('{}'); +INSERT INTO nummultirange_test VALUES('{[,)}'); +INSERT INTO nummultirange_test VALUES('{[3,]}'); +INSERT INTO nummultirange_test VALUES('{[,), [3,]}'); +INSERT INTO nummultirange_test VALUES('{[, 5)}'); +INSERT INTO nummultirange_test VALUES(nummultirange()); +INSERT INTO nummultirange_test VALUES(nummultirange(variadic '{}'::numrange[])); +INSERT INTO nummultirange_test VALUES(nummultirange(numrange(1.1, 2.2))); +INSERT INTO nummultirange_test VALUES('{empty}'); +INSERT INTO nummultirange_test VALUES(nummultirange(numrange(1.7, 1.7, '[]'), numrange(1.7, 1.9))); +INSERT INTO nummultirange_test VALUES(nummultirange(numrange(1.7, 1.7, '[]'), numrange(1.9, 2.1))); + +SELECT nmr, isempty(nmr), lower(nmr), upper(nmr) FROM nummultirange_test ORDER BY nmr; +SELECT nmr, lower_inc(nmr), lower_inf(nmr), upper_inc(nmr), upper_inf(nmr) FROM nummultirange_test ORDER BY nmr; + +SELECT * FROM nummultirange_test WHERE nmr = '{}'; +SELECT * FROM nummultirange_test WHERE nmr = '{(,5)}'; +SELECT * FROM nummultirange_test WHERE nmr = '{[3,)}'; +SELECT * FROM nummultirange_test WHERE nmr = '{[1.7,1.7]}'; +SELECT * FROM nummultirange_test WHERE nmr = '{[1.7,1.7],[1.9,2.1)}'; +SELECT * FROM nummultirange_test WHERE nmr < '{}'; +SELECT * FROM nummultirange_test WHERE nmr < '{[-1000.0, -1000.0]}'; +SELECT * FROM nummultirange_test WHERE nmr < '{[0.0, 1.0]}'; +SELECT * FROM nummultirange_test WHERE nmr < '{[1000.0, 1001.0]}'; +SELECT * FROM nummultirange_test WHERE nmr <= '{}'; +SELECT * FROM nummultirange_test WHERE nmr <= '{[3,)}'; +SELECT * FROM nummultirange_test WHERE nmr >= '{}'; +SELECT * FROM nummultirange_test WHERE nmr >= '{[3,)}'; +SELECT * FROM nummultirange_test WHERE nmr > '{}'; +SELECT * FROM nummultirange_test WHERE nmr > '{[-1000.0, -1000.0]}'; +SELECT * FROM nummultirange_test WHERE nmr > '{[0.0, 1.0]}'; +SELECT * FROM nummultirange_test WHERE nmr > '{[1000.0, 1001.0]}'; +SELECT * FROM nummultirange_test WHERE nmr <> '{}'; +SELECT * FROM nummultirange_test WHERE nmr <> '{(,5)}'; + +select nummultirange(numrange(2.0, 1.0)); +select nummultirange(numrange(5.0, 6.0), numrange(1.0, 2.0)); + +-- overlaps +SELECT * FROM nummultirange_test WHERE range_overlaps_multirange(numrange(4.0, 4.2), nmr); +SELECT * FROM nummultirange_test WHERE numrange(4.0, 4.2) && nmr; +SELECT * FROM nummultirange_test WHERE multirange_overlaps_range(nmr, numrange(4.0, 4.2)); +SELECT * FROM nummultirange_test WHERE nmr && numrange(4.0, 4.2); +SELECT * FROM nummultirange_test WHERE multirange_overlaps_multirange(nmr, nummultirange(numrange(4.0, 4.2), numrange(6.0, 7.0))); +SELECT * FROM nummultirange_test WHERE nmr && nummultirange(numrange(4.0, 4.2), numrange(6.0, 7.0)); +SELECT * FROM nummultirange_test WHERE nmr && nummultirange(numrange(6.0, 7.0)); +SELECT * FROM nummultirange_test WHERE nmr && nummultirange(numrange(6.0, 7.0), numrange(8.0, 9.0)); + +-- mr contains x +SELECT * FROM nummultirange_test WHERE multirange_contains_elem(nmr, 4.0); +SELECT * FROM nummultirange_test WHERE nmr @> 4.0; +SELECT * FROM nummultirange_test WHERE multirange_contains_range(nmr, numrange(4.0, 4.2)); +SELECT * FROM nummultirange_test WHERE nmr @> numrange(4.0, 4.2); +SELECT * FROM nummultirange_test WHERE multirange_contains_multirange(nmr, '{[4.0,4.2), [6.0, 8.0)}'); +SELECT * FROM nummultirange_test WHERE nmr @> '{[4.0,4.2), [6.0, 8.0)}'::nummultirange; + +-- x is contained by mr +SELECT * FROM nummultirange_test WHERE elem_contained_by_multirange(4.0, nmr); +SELECT * FROM nummultirange_test WHERE 4.0 <@ nmr; +SELECT * FROM nummultirange_test WHERE range_contained_by_multirange(numrange(4.0, 4.2), nmr); +SELECT * FROM nummultirange_test WHERE numrange(4.0, 4.2) <@ nmr; +SELECT * FROM nummultirange_test WHERE multirange_contained_by_multirange('{[4.0,4.2), [6.0, 8.0)}', nmr); +SELECT * FROM nummultirange_test WHERE '{[4.0,4.2), [6.0, 8.0)}'::nummultirange <@ nmr; + +-- overlaps +SELECT 'empty'::numrange && nummultirange(); +SELECT 'empty'::numrange && nummultirange(numrange(1,2)); +SELECT nummultirange() && 'empty'::numrange; +SELECT nummultirange(numrange(1,2)) && 'empty'::numrange; +SELECT nummultirange() && nummultirange(); +SELECT nummultirange() && nummultirange(numrange(1,2)); +SELECT nummultirange(numrange(1,2)) && nummultirange(); +SELECT nummultirange(numrange(3,4)) && nummultirange(numrange(1,2), numrange(7,8)); +SELECT nummultirange(numrange(1,2), numrange(7,8)) && nummultirange(numrange(3,4)); +SELECT nummultirange(numrange(3,4)) && nummultirange(numrange(1,2), numrange(3.5,8)); +SELECT nummultirange(numrange(1,2), numrange(3.5,8)) && numrange(3,4); +SELECT nummultirange(numrange(1,2), numrange(3.5,8)) && nummultirange(numrange(3,4)); + +-- contains +SELECT nummultirange() @> nummultirange(); +SELECT nummultirange() @> 'empty'::numrange; +SELECT nummultirange(numrange(null,null)) @> numrange(1,2); +SELECT nummultirange(numrange(null,null)) @> numrange(null,2); +SELECT nummultirange(numrange(null,null)) @> numrange(2,null); +SELECT nummultirange(numrange(null,5)) @> numrange(null,3); +SELECT nummultirange(numrange(null,5)) @> numrange(null,8); +SELECT nummultirange(numrange(5,null)) @> numrange(8,null); +SELECT nummultirange(numrange(5,null)) @> numrange(3,null); +SELECT nummultirange(numrange(1,5)) @> numrange(8,9); +SELECT nummultirange(numrange(1,5)) @> numrange(3,9); +SELECT nummultirange(numrange(1,5)) @> numrange(1,4); +SELECT nummultirange(numrange(1,5)) @> numrange(1,5); +SELECT nummultirange(numrange(-4,-2), numrange(1,5)) @> numrange(1,5); +SELECT nummultirange(numrange(1,5), numrange(8,9)) @> numrange(1,5); +SELECT nummultirange(numrange(1,5), numrange(8,9)) @> numrange(6,7); +SELECT nummultirange(numrange(1,5), numrange(6,9)) @> numrange(6,7); +SELECT '{[1,5)}'::nummultirange @> '{[1,5)}'; +SELECT '{[-4,-2), [1,5)}'::nummultirange @> '{[1,5)}'; +SELECT '{[1,5), [8,9)}'::nummultirange @> '{[1,5)}'; +SELECT '{[1,5), [8,9)}'::nummultirange @> '{[6,7)}'; +SELECT '{[1,5), [6,9)}'::nummultirange @> '{[6,7)}'; + +-- is contained by +SELECT nummultirange() <@ nummultirange(); +SELECT 'empty'::numrange <@ nummultirange(); +SELECT numrange(1,2) <@ nummultirange(numrange(null,null)); +SELECT numrange(null,2) <@ nummultirange(numrange(null,null)); +SELECT numrange(2,null) <@ nummultirange(numrange(null,null)); +SELECT numrange(null,3) <@ nummultirange(numrange(null,5)); +SELECT numrange(null,8) <@ nummultirange(numrange(null,5)); +SELECT numrange(8,null) <@ nummultirange(numrange(5,null)); +SELECT numrange(3,null) <@ nummultirange(numrange(5,null)); +SELECT numrange(8,9) <@ nummultirange(numrange(1,5)); +SELECT numrange(3,9) <@ nummultirange(numrange(1,5)); +SELECT numrange(1,4) <@ nummultirange(numrange(1,5)); +SELECT numrange(1,5) <@ nummultirange(numrange(1,5)); +SELECT numrange(1,5) <@ nummultirange(numrange(-4,-2), numrange(1,5)); +SELECT numrange(1,5) <@ nummultirange(numrange(1,5), numrange(8,9)); +SELECT numrange(6,7) <@ nummultirange(numrange(1,5), numrange(8,9)); +SELECT numrange(6,7) <@ nummultirange(numrange(1,5), numrange(6,9)); +SELECT '{[1,5)}' <@ '{[1,5)}'::nummultirange; +SELECT '{[1,5)}' <@ '{[-4,-2), [1,5)}'::nummultirange; +SELECT '{[1,5)}' <@ '{[1,5), [8,9)}'::nummultirange; +SELECT '{[6,7)}' <@ '{[1,5), [8,9)}'::nummultirange; +SELECT '{[6,7)}' <@ '{[1,5), [6,9)}'::nummultirange; + +-- overleft +SELECT 'empty'::numrange &< nummultirange(); +SELECT 'empty'::numrange &< nummultirange(numrange(1,2)); +SELECT nummultirange() &< 'empty'::numrange; +SELECT nummultirange(numrange(1,2)) &< 'empty'::numrange; +SELECT nummultirange() &< nummultirange(); +SELECT nummultirange(numrange(1,2)) &< nummultirange(); +SELECT nummultirange() &< nummultirange(numrange(1,2)); +SELECT numrange(6,7) &< nummultirange(numrange(3,4)); +SELECT numrange(1,2) &< nummultirange(numrange(3,4)); +SELECT numrange(1,4) &< nummultirange(numrange(3,4)); +SELECT numrange(1,6) &< nummultirange(numrange(3,4)); +SELECT numrange(3.5,6) &< nummultirange(numrange(3,4)); +SELECT nummultirange(numrange(6,7)) &< numrange(3,4); +SELECT nummultirange(numrange(1,2)) &< numrange(3,4); +SELECT nummultirange(numrange(1,4)) &< numrange(3,4); +SELECT nummultirange(numrange(1,6)) &< numrange(3,4); +SELECT nummultirange(numrange(3.5,6)) &< numrange(3,4); +SELECT nummultirange(numrange(6,7)) &< nummultirange(numrange(3,4)); +SELECT nummultirange(numrange(1,2)) &< nummultirange(numrange(3,4)); +SELECT nummultirange(numrange(1,4)) &< nummultirange(numrange(3,4)); +SELECT nummultirange(numrange(1,6)) &< nummultirange(numrange(3,4)); +SELECT nummultirange(numrange(3.5,6)) &< nummultirange(numrange(3,4)); + +-- overright +SELECT nummultirange() &> 'empty'::numrange; +SELECT nummultirange(numrange(1,2)) &> 'empty'::numrange; +SELECT 'empty'::numrange &> nummultirange(); +SELECT 'empty'::numrange &> nummultirange(numrange(1,2)); +SELECT nummultirange() &> nummultirange(); +SELECT nummultirange() &> nummultirange(numrange(1,2)); +SELECT nummultirange(numrange(1,2)) &> nummultirange(); +SELECT nummultirange(numrange(3,4)) &> numrange(6,7); +SELECT nummultirange(numrange(3,4)) &> numrange(1,2); +SELECT nummultirange(numrange(3,4)) &> numrange(1,4); +SELECT nummultirange(numrange(3,4)) &> numrange(1,6); +SELECT nummultirange(numrange(3,4)) &> numrange(3.5,6); +SELECT numrange(3,4) &> nummultirange(numrange(6,7)); +SELECT numrange(3,4) &> nummultirange(numrange(1,2)); +SELECT numrange(3,4) &> nummultirange(numrange(1,4)); +SELECT numrange(3,4) &> nummultirange(numrange(1,6)); +SELECT numrange(3,4) &> nummultirange(numrange(3.5,6)); +SELECT nummultirange(numrange(3,4)) &> nummultirange(numrange(6,7)); +SELECT nummultirange(numrange(3,4)) &> nummultirange(numrange(1,2)); +SELECT nummultirange(numrange(3,4)) &> nummultirange(numrange(1,4)); +SELECT nummultirange(numrange(3,4)) &> nummultirange(numrange(1,6)); +SELECT nummultirange(numrange(3,4)) &> nummultirange(numrange(3.5,6)); + +-- meets +SELECT 'empty'::numrange -|- nummultirange(); +SELECT 'empty'::numrange -|- nummultirange(numrange(1,2)); +SELECT nummultirange() -|- 'empty'::numrange; +SELECT nummultirange(numrange(1,2)) -|- 'empty'::numrange; +SELECT nummultirange() -|- nummultirange(); +SELECT nummultirange(numrange(1,2)) -|- nummultirange(); +SELECT nummultirange() -|- nummultirange(numrange(1,2)); +SELECT numrange(1,2) -|- nummultirange(numrange(2,4)); +SELECT numrange(1,2) -|- nummultirange(numrange(3,4)); +SELECT nummultirange(numrange(1,2)) -|- numrange(2,4); +SELECT nummultirange(numrange(1,2)) -|- numrange(3,4); +SELECT nummultirange(numrange(1,2)) -|- nummultirange(numrange(2,4)); +SELECT nummultirange(numrange(1,2)) -|- nummultirange(numrange(3,4)); +SELECT nummultirange(numrange(1,2), numrange(5,6)) -|- nummultirange(numrange(3,4)); +SELECT nummultirange(numrange(1,2), numrange(5,6)) -|- nummultirange(numrange(6,7)); +SELECT nummultirange(numrange(1,2), numrange(5,6)) -|- nummultirange(numrange(8,9)); +SELECT nummultirange(numrange(1,2)) -|- nummultirange(numrange(2,4), numrange(6,7)); + +-- strictly left +select 'empty'::numrange << nummultirange(); +select numrange(1,2) << nummultirange(); +select numrange(1,2) << nummultirange(numrange(3,4)); +select numrange(1,2) << nummultirange(numrange(0,4)); +select numrange(1,2) << nummultirange(numrange(0,4), numrange(7,8)); +select nummultirange() << 'empty'::numrange; +select nummultirange() << numrange(1,2); +select nummultirange(numrange(3,4)) << numrange(3,6); +select nummultirange(numrange(0,2)) << numrange(3,6); +select nummultirange(numrange(0,2), numrange(7,8)) << numrange(3,6); +select nummultirange(numrange(-4,-2), numrange(0,2)) << numrange(3,6); +select nummultirange() << nummultirange(); +select nummultirange() << nummultirange(numrange(1,2)); +select nummultirange(numrange(1,2)) << nummultirange(); +select nummultirange(numrange(1,2)) << nummultirange(numrange(1,2)); +select nummultirange(numrange(1,2)) << nummultirange(numrange(3,4)); +select nummultirange(numrange(1,2)) << nummultirange(numrange(3,4), numrange(7,8)); +select nummultirange(numrange(1,2), numrange(4,5)) << nummultirange(numrange(3,4), numrange(7,8)); + +-- strictly right +select nummultirange() >> 'empty'::numrange; +select nummultirange() >> numrange(1,2); +select nummultirange(numrange(3,4)) >> numrange(1,2); +select nummultirange(numrange(0,4)) >> numrange(1,2); +select nummultirange(numrange(0,4), numrange(7,8)) >> numrange(1,2); +select 'empty'::numrange >> nummultirange(); +select numrange(1,2) >> nummultirange(); +select numrange(3,6) >> nummultirange(numrange(3,4)); +select numrange(3,6) >> nummultirange(numrange(0,2)); +select numrange(3,6) >> nummultirange(numrange(0,2), numrange(7,8)); +select numrange(3,6) >> nummultirange(numrange(-4,-2), numrange(0,2)); +select nummultirange() >> nummultirange(); +select nummultirange(numrange(1,2)) >> nummultirange(); +select nummultirange() >> nummultirange(numrange(1,2)); +select nummultirange(numrange(1,2)) >> nummultirange(numrange(1,2)); +select nummultirange(numrange(3,4)) >> nummultirange(numrange(1,2)); +select nummultirange(numrange(3,4), numrange(7,8)) >> nummultirange(numrange(1,2)); +select nummultirange(numrange(3,4), numrange(7,8)) >> nummultirange(numrange(1,2), numrange(4,5)); + +-- union +SELECT 'empty'::numrange @+ 'empty'::numrange; +SELECT 'empty'::numrange @+ nummultirange(); +SELECT nummultirange() @+ 'empty'::numrange; +SELECT nummultirange() @+ nummultirange(); +SELECT 'empty'::numrange @+ numrange(1,2); +SELECT 'empty'::numrange @+ nummultirange(numrange(1,2)); +SELECT numrange(1,2) @+ nummultirange(); +SELECT nummultirange(numrange(1,2)) @+ 'empty'::numrange; +SELECT numrange(1,2) @+ 'empty'::numrange; +SELECT nummultirange() @+ numrange(1,2); +SELECT nummultirange() @+ nummultirange(numrange(1,2)); +SELECT nummultirange(numrange(1,2)) @+ nummultirange(); +SELECT numrange(1,2) @+ numrange(1,2); +SELECT numrange(1,2) @+ numrange(2,4); +SELECT numrange(1,2) @+ numrange(3,4); +SELECT nummultirange(numrange(1,2)) @+ nummultirange(numrange(1,2)); +SELECT nummultirange(numrange(1,2)) @+ nummultirange(numrange(2,4)); +SELECT nummultirange(numrange(1,2)) @+ nummultirange(numrange(3,4)); +SELECT nummultirange(numrange(1,2), numrange(4,5)) @+ nummultirange(numrange(2,4)); +SELECT nummultirange(numrange(1,2), numrange(4,5)) @+ nummultirange(numrange(3,4)); +SELECT nummultirange(numrange(1,2), numrange(4,5)) @+ nummultirange(numrange(0,9)); + +-- merge +SELECT range_merge(nummultirange()); +SELECT range_merge(nummultirange(numrange(1,2))); +SELECT range_merge(nummultirange(numrange(1,2), numrange(7,8))); + +-- minus +SELECT 'empty'::numrange @- 'empty'::numrange; +SELECT 'empty'::numrange @- nummultirange(); +SELECT nummultirange() @- 'empty'::numrange; +SELECT nummultirange() @- nummultirange(); +SELECT 'empty'::numrange @- numrange(1,2); +SELECT 'empty'::numrange @- nummultirange(numrange(1,2)); +SELECT numrange(1,2) @- nummultirange(); +SELECT nummultirange(numrange(1,2)) @- 'empty'::numrange; +SELECT 'empty'::numrange @- numrange(1,2); +SELECT numrange(1,2) @- 'empty'::numrange; +SELECT nummultirange() @- numrange(1,2); +SELECT nummultirange() @- nummultirange(numrange(1,2)); +SELECT nummultirange(numrange(1,2)) @- nummultirange(); +SELECT numrange(1,3) @- numrange(1,3); +SELECT numrange(1,3) @- numrange(1,2); +SELECT numrange(1,3) @- numrange(2,4); +SELECT numrange(1,3) @- numrange(3,4); +SELECT numrange(1,3) @- nummultirange(numrange(1,3)); +SELECT numrange(1,3) @- nummultirange(numrange(1,2)); +SELECT numrange(1,3) @- nummultirange(numrange(2,4)); +SELECT numrange(1,3) @- nummultirange(numrange(3,4)); +SELECT numrange(1,10) @- nummultirange(numrange(1,2), numrange(4,5)); +SELECT nummultirange(numrange(1,10)) @- numrange(0,2); +SELECT nummultirange(numrange(1,10)) @- numrange(1,2); +SELECT nummultirange(numrange(1,10)) @- numrange(3,4); +SELECT nummultirange(numrange(1,10)) @- numrange(13,18); +SELECT nummultirange(numrange(1,2), numrange(3,4)) @- nummultirange(); +SELECT nummultirange(numrange(1,2)) @- nummultirange(numrange(1,2)); +SELECT nummultirange(numrange(1,2)) @- nummultirange(numrange(2,4)); +SELECT nummultirange(numrange(1,2)) @- nummultirange(numrange(3,4)); +SELECT nummultirange(numrange(1,4)) @- nummultirange(numrange(1,2)); +SELECT nummultirange(numrange(1,4)) @- nummultirange(numrange(2,3)); +SELECT nummultirange(numrange(1,4)) @- nummultirange(numrange(0,8)); +SELECT nummultirange(numrange(1,4)) @- nummultirange(numrange(0,2)); +SELECT nummultirange(numrange(1,8)) @- nummultirange(numrange(0,2), numrange(3,4)); +SELECT nummultirange(numrange(1,8)) @- nummultirange(numrange(2,3), numrange(5,null)); +SELECT nummultirange(numrange(1,2), numrange(4,5)) @- nummultirange(numrange(-2,0)); +SELECT nummultirange(numrange(1,2), numrange(4,5)) @- nummultirange(numrange(2,4)); +SELECT nummultirange(numrange(1,2), numrange(4,5)) @- nummultirange(numrange(3,5)); +SELECT nummultirange(numrange(1,2), numrange(4,5)) @- nummultirange(numrange(0,9)); +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)); + +-- intersection +SELECT 'empty'::numrange @* 'empty'::numrange; +SELECT 'empty'::numrange @* nummultirange(); +SELECT nummultirange() @* 'empty'::numrange; +SELECT nummultirange() @* nummultirange(); +SELECT 'empty'::numrange @* numrange(1,2); +SELECT 'empty'::numrange @* nummultirange(numrange(1,2)); +SELECT numrange(1,2) @* 'empty'::numrange; +SELECT numrange(1,2) @* nummultirange(); +SELECT nummultirange(numrange(1,2)) @* 'empty'::numrange; +SELECT nummultirange() @* numrange(1,2); +SELECT nummultirange() @* nummultirange(numrange(1,2)); +SELECT nummultirange(numrange(1,2)) @* nummultirange(); +SELECT numrange(1,3) @* numrange(1,3); +SELECT numrange(1,3) @* numrange(1,2); +SELECT numrange(1,3) @* numrange(1,5); +SELECT numrange(1,3) @* numrange(2,5); +SELECT numrange(1,5) @* numrange(2,3); +SELECT '{[1,3)}'::nummultirange @* numrange(1,3); +SELECT '{[1,3)}'::nummultirange @* numrange(1,2); +SELECT '{[1,3)}'::nummultirange @* numrange(1,5); +SELECT '{[1,3)}'::nummultirange @* numrange(2,5); +SELECT '{[1,5)}'::nummultirange @* numrange(2,3); +SELECT numrange(1,3) @* '{[1,3)}'::nummultirange; +SELECT numrange(1,2) @* '{[1,3)}'::nummultirange; +SELECT numrange(1,5) @* '{[1,3)}'::nummultirange; +SELECT numrange(2,5) @* '{[1,3)}'::nummultirange; +SELECT numrange(2,3) @* '{[1,5)}'::nummultirange; +SELECT '{[1,3)}'::nummultirange @* '{[1,5)}'::nummultirange; +SELECT '{[1,3)}'::nummultirange @* '{[0,5)}'::nummultirange; +SELECT '{[1,3)}'::nummultirange @* '{[0,2)}'::nummultirange; +SELECT '{[1,3)}'::nummultirange @* '{[2,5)}'::nummultirange; +SELECT '{[1,4)}'::nummultirange @* '{[2,3)}'::nummultirange; +SELECT '{[1,4)}'::nummultirange @* '{[0,2), [3,5)}'::nummultirange; +SELECT '{[1,4), [7,10)}'::nummultirange @* '{[0,8), [9,12)}'::nummultirange; +SELECT '{[1,4), [7,10)}'::nummultirange @* '{[9,12)}'::nummultirange; +SELECT '{[1,4), [7,10)}'::nummultirange @* '{[-5,-4), [5,6), [9,12)}'::nummultirange; +SELECT '{[1,4), [7,10)}'::nummultirange @* '{[0,2), [3,8), [9,12)}'::nummultirange; +SELECT '{[1,4), [7,10)}'::nummultirange @* '{[0,2), [3,8), [9,12)}'::nummultirange; + +-- +-- range_agg function +-- +create table reservations ( room_id integer not null, booked_during daterange ); +insert into reservations values +-- 1: has a meets and a gap +(1, daterange('2018-07-01', '2018-07-07')), +(1, daterange('2018-07-07', '2018-07-14')), +(1, daterange('2018-07-20', '2018-07-22')), +-- 2: just a single row +(2, daterange('2018-07-01', '2018-07-03')), +-- 3: one null range +(3, NULL), +-- 4: two null ranges +(4, NULL), +(4, NULL), +-- 5: a null range and a non-null range +(5, NULL), +(5, daterange('2018-07-01', '2018-07-03')), +-- 6: has overlap +(6, daterange('2018-07-01', '2018-07-07')), +(6, daterange('2018-07-05', '2018-07-10')), +-- 7: two ranges that meet: no gap or overlap +(7, daterange('2018-07-01', '2018-07-07')), +(7, daterange('2018-07-07', '2018-07-14')), +-- 8: an empty range +(8, 'empty'::daterange) +; +SELECT room_id, range_agg(booked_during) +FROM reservations +GROUP BY room_id +ORDER BY room_id; + +-- range_agg on a custom range type too +SELECT range_agg(r) +FROM (VALUES + ('[a,c]'::textrange), + ('[b,b]'::textrange), + ('[c,f]'::textrange), + ('[g,h)'::textrange), + ('[h,j)'::textrange) + ) t(r); + +select range_intersect_agg(nmr) from nummultirange_test; +select range_intersect_agg(nmr) from nummultirange_test where false; +-- test with just one input: +select range_intersect_agg(nmr) from (values ('{[1,2]}'::nummultirange)) t(nmr); +select range_intersect_agg(nmr) from nummultirange_test where nmr @> 4.0; + +create table nummultirange_test2(nmr nummultirange); +create index nummultirange_test2_hash_idx on nummultirange_test2 using hash (nmr); + +INSERT INTO nummultirange_test2 VALUES('{[, 5)}'); +INSERT INTO nummultirange_test2 VALUES(nummultirange(numrange(1.1, 2.2))); +INSERT INTO nummultirange_test2 VALUES(nummultirange(numrange(1.1, 2.2))); +INSERT INTO nummultirange_test2 VALUES(nummultirange(numrange(1.1, 2.2,'()'))); +INSERT INTO nummultirange_test2 VALUES('{}'); + +select * from nummultirange_test2 where nmr = '{}'; +select * from nummultirange_test2 where nmr = nummultirange(numrange(1.1, 2.2)); +select * from nummultirange_test2 where nmr = nummultirange(numrange(1.1, 2.3)); + +set enable_nestloop=t; +set enable_hashjoin=f; +set enable_mergejoin=f; +select * from nummultirange_test natural join nummultirange_test2 order by nmr; +set enable_nestloop=f; +set enable_hashjoin=t; +set enable_mergejoin=f; +select * from nummultirange_test natural join nummultirange_test2 order by nmr; +set enable_nestloop=f; +set enable_hashjoin=f; +set enable_mergejoin=t; +select * from nummultirange_test natural join nummultirange_test2 order by nmr; + +set enable_nestloop to default; +set enable_hashjoin to default; +set enable_mergejoin to default; + +DROP TABLE nummultirange_test2; + +-- +-- Test user-defined multirange of floats +-- + +select '{[123.001, 5.e9)}'::float8multirange @> 888.882::float8; +create table float8multirange_test(f8mr float8multirange, i int); +insert into float8multirange_test values(float8multirange(float8range(-100.00007, '1.111113e9')), 42); +select * from float8multirange_test; +drop table float8multirange_test; + +-- +-- Test multirange types over domains +-- + +create domain mydomain as int4; +create type mydomainrange as range(subtype=mydomain); +select '{[4,50)}'::mydomainmultirange @> 7::mydomain; +drop domain mydomain cascade; + +-- +-- Test domains over multirange types +-- + +create domain restrictedmultirange as int4multirange check (upper(value) < 10); +select '{[4,5)}'::restrictedmultirange @> 7; +select '{[4,50)}'::restrictedmultirange @> 7; -- should fail +drop domain restrictedmultirange; + +-- +-- Test multiple multirange types over the same subtype +-- + +create type textrange1 as range(subtype=text, collation="C"); +create type textrange2 as range(subtype=text, collation="C"); + +select textmultirange1(textrange2('a','Z')); -- should fail +select textmultirange1(textrange1('a','Z')) @> 'b'::text; +select textmultirange2(textrange2('a','z')) @> 'b'::text; + +drop type textrange1; +drop type textrange2; + +-- +-- Test polymorphic type system +-- + +create function anyarray_anymultirange_func(a anyarray, r anymultirange) + returns anyelement as 'select $1[1] + lower($2);' language sql; + +select anyarray_anymultirange_func(ARRAY[1,2], int4multirange(int4range(10,20))); + +-- should fail +select anyarray_anymultirange_func(ARRAY[1,2], nummultirange(numrange(10,20))); + +drop function anyarray_anymultirange_func(anyarray, anymultirange); + +-- should fail +create function bogus_func(anyelement) + returns anymultirange as 'select int4multirange(int4range(1,10))' language sql; + +-- should fail +create function bogus_func(int) + returns anymultirange as 'select int4multirange(int4range(1,10))' language sql; + +create function range_add_bounds(anymultirange) + returns anyelement as 'select lower($1) + upper($1)' language sql; + +select range_add_bounds(int4multirange(int4range(1, 17))); +select range_add_bounds(nummultirange(numrange(1.0001, 123.123))); + +create function multirangetypes_sql(q anymultirange, b anyarray, out c anyelement) + as $$ select upper($1) + $2[1] $$ + language sql; + +select multirangetypes_sql(int4multirange(int4range(1,10)), ARRAY[2,20]); +select multirangetypes_sql(nummultirange(numrange(1,10)), ARRAY[2,20]); -- match failure + +-- +-- Arrays of multiranges +-- + +select ARRAY[nummultirange(numrange(1.1, 1.2)), nummultirange(numrange(12.3, 155.5))]; + +create table i8mr_array (f1 int, f2 int8multirange[]); +insert into i8mr_array values (42, array[int8multirange(int8range(1,10)), int8multirange(int8range(2,20))]); +select * from i8mr_array; +drop table i8mr_array; + +-- +-- Multiranges of arrays +-- + +select arraymultirange(arrayrange(ARRAY[1,2], ARRAY[2,1])); +select arraymultirange(arrayrange(ARRAY[2,1], ARRAY[1,2])); -- fail + +select array[1,1] <@ arraymultirange(arrayrange(array[1,2], array[2,1])); +select array[1,3] <@ arraymultirange(arrayrange(array[1,2], array[2,1])); + +-- +-- Ranges of composites +-- + +create type two_ints as (a int, b int); +create type two_ints_range as range (subtype = two_ints); + +-- with force_parallel_mode on, this exercises tqueue.c's range remapping +select *, row_to_json(upper(t)) as u from + (values (two_ints_multirange(two_ints_range(row(1,2), row(3,4)))), + (two_ints_multirange(two_ints_range(row(5,6), row(7,8))))) v(t); + +drop type two_ints cascade; + +-- +-- Check behavior when subtype lacks a hash function +-- + +set enable_sort = off; -- try to make it pick a hash setop implementation + +select '{(2,5)}'::cashmultirange except select '{(5,6)}'::cashmultirange; + +reset enable_sort; + +-- +-- OUT/INOUT/TABLE functions +-- + +-- infer anymultirange from anymultirange +create function mr_outparam_succeed(i anymultirange, out r anymultirange, out t text) + as $$ select $1, 'foo'::text $$ language sql; + +select * from mr_outparam_succeed(int4multirange(int4range(1,2))); + +-- infer anyarray from anymultirange +create function mr_outparam_succeed2(i anymultirange, out r anyarray, out t text) + as $$ select ARRAY[upper($1)], 'foo'::text $$ language sql; + +select * from mr_outparam_succeed2(int4multirange(int4range(1,2))); + +-- infer anyrange from anymultirange +create function mr_outparam_succeed3(i anymultirange, out r anyrange, out t text) + as $$ select range_merge($1), 'foo'::text $$ language sql; +select * from mr_outparam_succeed3(int4multirange(int4range(1,2))); + +-- infer anymultirange from anyrange +create function mr_outparam_succeed4(i anyrange, out r anymultirange, out t text) + as $$ select $1 @+ $1, 'foo'::text $$ language sql; + +select * from mr_outparam_succeed4(int4range(1,2)); + +-- infer anyelement from anymultirange +create function mr_inoutparam_succeed(out i anyelement, inout r anymultirange) + as $$ select upper($1), $1 $$ language sql; + +select * from mr_inoutparam_succeed(int4multirange(int4range(1,2))); + +-- infer anyelement+anymultirange from anyelement+anymultirange +create function mr_table_succeed(i anyelement, r anymultirange) returns table(i anyelement, r anymultirange) + as $$ select $1, $2 $$ language sql; + +select * from mr_table_succeed(123, int4multirange(int4range(1,11))); + +-- should fail +create function mr_outparam_fail(i anyelement, out r anymultirange, out t text) + as $$ select '[1,10]', 'foo' $$ language sql; + +--should fail +create function mr_inoutparam_fail(inout i anyelement, out r anymultirange) + as $$ select $1, '[1,10]' $$ language sql; + +--should fail +create function mr_table_fail(i anyelement) returns table(i anyelement, r anymultirange) + as $$ select $1, '[1,10]' $$ language sql; diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql index 8960add976..32e18661d0 100644 --- a/src/test/regress/sql/rangetypes.sql +++ b/src/test/regress/sql/rangetypes.sql @@ -118,6 +118,10 @@ select numrange(1.0, 2.0) * numrange(2.0, 3.0); select numrange(1.0, 2.0) * numrange(1.5, 3.0); select numrange(1.0, 2.0) * numrange(2.5, 3.0); +select range_intersect_agg(nr) from numrange_test; +select range_intersect_agg(nr) from numrange_test where false; +select range_intersect_agg(nr) from numrange_test where nr @> 4.0; + create table numrange_test2(nr numrange); create index numrange_test2_hash_idx on numrange_test2 using hash (nr); @@ -481,16 +485,25 @@ reset enable_sort; -- OUT/INOUT/TABLE functions -- +-- infer anyrange from anyrange create function outparam_succeed(i anyrange, out r anyrange, out t text) as $$ select $1, 'foo'::text $$ language sql; select * from outparam_succeed(int4range(1,2)); +-- infer anyarray from anyrange +create function outparam_succeed2(i anyrange, out r anyarray, out t text) + as $$ select ARRAY[upper($1)], 'foo'::text $$ language sql; + +select * from outparam_succeed2(int4range(int4range(1,2))); + +-- infer anyelement from anyrange create function inoutparam_succeed(out i anyelement, inout r anyrange) as $$ select upper($1), $1 $$ language sql; select * from inoutparam_succeed(int4range(1,2)); +-- infer anyelement+anyrange from anyelement+anyrange create function table_succeed(i anyelement, r anyrange) returns table(i anyelement, r anyrange) as $$ select $1, $2 $$ language sql; -- 2.20.1