diff --git a/src/backend/utils/adt/cash.c b/src/backend/utils/adt/cash.c index ac8f74f..debe1b3 100644 --- a/src/backend/utils/adt/cash.c +++ b/src/backend/utils/adt/cash.c @@ -30,6 +30,7 @@ #include "utils/numeric.h" #include "utils/pg_locale.h" +#define SAMESIGN(a,b) (((a) < 0) == ((b) < 0)) /************************************************************************* * Private routines @@ -1157,3 +1158,23 @@ int8_cash(PG_FUNCTION_ARGS) PG_RETURN_CASH(result); } + +Datum +cash_dist(PG_FUNCTION_ARGS) +{ + Cash a = PG_GETARG_CASH(0); + Cash b = PG_GETARG_CASH(1); + Cash r; + Cash ra; + + r = a - b; + ra = Abs(r); + + /* Overflow check. */ + if (ra < 0 || (!SAMESIGN(a, b) && !SAMESIGN(r, a))) + ereport(ERROR, + (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE), + errmsg("money out of range"))); + + PG_RETURN_CASH(ra); +} diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c index 0a100a3..88010d5 100644 --- a/src/backend/utils/adt/date.c +++ b/src/backend/utils/adt/date.c @@ -565,6 +565,17 @@ date_mii(PG_FUNCTION_ARGS) PG_RETURN_DATEADT(result); } +Datum +date_dist(PG_FUNCTION_ARGS) +{ + /* we assume the difference can't overflow */ + Datum diff = DirectFunctionCall2(date_mi, + PG_GETARG_DATUM(0), + PG_GETARG_DATUM(1)); + + PG_RETURN_INT32(Abs(DatumGetInt32(diff))); +} + /* * Internal routines for promoting date to timestamp and timestamp with * time zone @@ -2065,6 +2076,16 @@ time_part(PG_FUNCTION_ARGS) PG_RETURN_FLOAT8(result); } +Datum +time_dist(PG_FUNCTION_ARGS) +{ + Datum diff = DirectFunctionCall2(time_mi_time, + PG_GETARG_DATUM(0), + PG_GETARG_DATUM(1)); + + PG_RETURN_INTERVAL_P(abs_interval(DatumGetIntervalP(diff))); +} + /***************************************************************************** * Time With Time Zone ADT diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c index 894f026..906e70a 100644 --- a/src/backend/utils/adt/float.c +++ b/src/backend/utils/adt/float.c @@ -3585,6 +3585,32 @@ width_bucket_float8(PG_FUNCTION_ARGS) PG_RETURN_INT32(result); } +Datum +float4_dist(PG_FUNCTION_ARGS) +{ + float4 a = PG_GETARG_FLOAT4(0); + float4 b = PG_GETARG_FLOAT4(1); + float4 r; + + r = a - b; + CHECKFLOATVAL(r, isinf(a) || isinf(b), true); + + PG_RETURN_FLOAT4(Abs(r)); +} + +Datum +float8_dist(PG_FUNCTION_ARGS) +{ + float8 a = PG_GETARG_FLOAT8(0); + float8 b = PG_GETARG_FLOAT8(1); + float8 r; + + r = a - b; + CHECKFLOATVAL(r, isinf(a) || isinf(b), true); + + PG_RETURN_FLOAT8(Abs(r)); +} + /* ========== PRIVATE ROUTINES ========== */ #ifndef HAVE_CBRT diff --git a/src/backend/utils/adt/int.c b/src/backend/utils/adt/int.c index bd4422e..43dcf40 100644 --- a/src/backend/utils/adt/int.c +++ b/src/backend/utils/adt/int.c @@ -1395,3 +1395,43 @@ generate_series_step_int4(PG_FUNCTION_ARGS) /* do when there is no more left */ SRF_RETURN_DONE(funcctx); } + +Datum +int2_dist(PG_FUNCTION_ARGS) +{ + int16 a = PG_GETARG_INT16(0); + int16 b = PG_GETARG_INT16(1); + int16 r; + int16 ra; + + r = a - b; + ra = Abs(r); + + /* Overflow check. */ + if (ra < 0 || (!SAMESIGN(a, b) && !SAMESIGN(r, a))) + ereport(ERROR, + (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE), + errmsg("smallint out of range"))); + + PG_RETURN_INT16(ra); +} + +Datum +int4_dist(PG_FUNCTION_ARGS) +{ + int32 a = PG_GETARG_INT32(0); + int32 b = PG_GETARG_INT32(1); + int32 r; + int32 ra; + + r = a - b; + ra = Abs(r); + + /* Overflow check. */ + if (ra < 0 || (!SAMESIGN(a, b) && !SAMESIGN(r, a))) + ereport(ERROR, + (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE), + errmsg("integer out of range"))); + + PG_RETURN_INT32(ra); +} diff --git a/src/backend/utils/adt/int8.c b/src/backend/utils/adt/int8.c index 0c6a412..77938e0 100644 --- a/src/backend/utils/adt/int8.c +++ b/src/backend/utils/adt/int8.c @@ -1508,3 +1508,23 @@ generate_series_step_int8(PG_FUNCTION_ARGS) /* do when there is no more left */ SRF_RETURN_DONE(funcctx); } + +Datum +int8_dist(PG_FUNCTION_ARGS) +{ + int64 a = PG_GETARG_INT64(0); + int64 b = PG_GETARG_INT64(1); + int64 r; + int64 ra; + + r = a - b; + ra = Abs(r); + + /* Overflow check. */ + if (ra < 0 || (!SAMESIGN(a, b) && !SAMESIGN(r, a))) + ereport(ERROR, + (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE), + errmsg("bigint out of range"))); + + PG_RETURN_INT64(ra); +} diff --git a/src/backend/utils/adt/oid.c b/src/backend/utils/adt/oid.c index 12ef783..bab7ae7 100644 --- a/src/backend/utils/adt/oid.c +++ b/src/backend/utils/adt/oid.c @@ -455,3 +455,17 @@ oidvectorgt(PG_FUNCTION_ARGS) PG_RETURN_BOOL(cmp > 0); } + +Datum +oid_dist(PG_FUNCTION_ARGS) +{ + Oid a = PG_GETARG_OID(0); + Oid b = PG_GETARG_OID(1); + Oid res; + + if (a < b) + res = b - a; + else + res = a - b; + PG_RETURN_OID(res); +} diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c index f2784da..f665177 100644 --- a/src/backend/utils/adt/timestamp.c +++ b/src/backend/utils/adt/timestamp.c @@ -2922,6 +2922,62 @@ timestamp_mi(PG_FUNCTION_ARGS) PG_RETURN_INTERVAL_P(result); } +Datum +ts_dist(PG_FUNCTION_ARGS) +{ + Timestamp a = PG_GETARG_TIMESTAMP(0); + Timestamp b = PG_GETARG_TIMESTAMP(1); + Interval *r; + + if (TIMESTAMP_NOT_FINITE(a) || TIMESTAMP_NOT_FINITE(b)) + { + Interval *p = palloc(sizeof(Interval)); + + p->day = INT_MAX; + p->month = INT_MAX; +#ifdef HAVE_INT64_TIMESTAMP + p->time = PG_INT64_MAX; +#else + p->time = DBL_MAX; +#endif + PG_RETURN_INTERVAL_P(p); + } + else + r = DatumGetIntervalP(DirectFunctionCall2(timestamp_mi, + PG_GETARG_DATUM(0), + PG_GETARG_DATUM(1))); + PG_RETURN_INTERVAL_P(abs_interval(r)); +} + + +Datum +tstz_dist(PG_FUNCTION_ARGS) +{ + TimestampTz a = PG_GETARG_TIMESTAMPTZ(0); + TimestampTz b = PG_GETARG_TIMESTAMPTZ(1); + Interval *r; + + if (TIMESTAMP_NOT_FINITE(a) || TIMESTAMP_NOT_FINITE(b)) + { + Interval *p = palloc(sizeof(Interval)); + + p->day = INT_MAX; + p->month = INT_MAX; +#ifdef HAVE_INT64_TIMESTAMP + p->time = PG_INT64_MAX; +#else + p->time = DBL_MAX; +#endif + PG_RETURN_INTERVAL_P(p); + } + + r = DatumGetIntervalP(DirectFunctionCall2(timestamp_mi, + PG_GETARG_DATUM(0), + PG_GETARG_DATUM(1))); + PG_RETURN_INTERVAL_P(abs_interval(r)); +} + + /* * interval_justify_interval() * @@ -3725,6 +3781,29 @@ interval_avg(PG_FUNCTION_ARGS) Float8GetDatum((double) N.time)); } +Interval * +abs_interval(Interval *a) +{ + static Interval zero = {0, 0, 0}; + + if (DatumGetBool(DirectFunctionCall2(interval_lt, + IntervalPGetDatum(a), + IntervalPGetDatum(&zero)))) + a = DatumGetIntervalP(DirectFunctionCall1(interval_um, + IntervalPGetDatum(a))); + + return a; +} + +Datum +interval_dist(PG_FUNCTION_ARGS) +{ + Datum diff = DirectFunctionCall2(interval_mi, + PG_GETARG_DATUM(0), + PG_GETARG_DATUM(1)); + + PG_RETURN_INTERVAL_P(abs_interval(DatumGetIntervalP(diff))); +} /* timestamp_age() * Calculate time difference while retaining year/month fields. diff --git a/src/include/catalog/pg_amop.h b/src/include/catalog/pg_amop.h index 0251664..44e2caf 100644 --- a/src/include/catalog/pg_amop.h +++ b/src/include/catalog/pg_amop.h @@ -105,6 +105,7 @@ DATA(insert ( 1976 21 21 2 s 522 403 0 )); DATA(insert ( 1976 21 21 3 s 94 403 0 )); DATA(insert ( 1976 21 21 4 s 524 403 0 )); DATA(insert ( 1976 21 21 5 s 520 403 0 )); +DATA(insert ( 1976 21 21 6 o 3365 403 1976 )); /* crosstype operators int24 */ DATA(insert ( 1976 21 23 1 s 534 403 0 )); DATA(insert ( 1976 21 23 2 s 540 403 0 )); @@ -123,6 +124,7 @@ DATA(insert ( 1976 23 23 2 s 523 403 0 )); DATA(insert ( 1976 23 23 3 s 96 403 0 )); DATA(insert ( 1976 23 23 4 s 525 403 0 )); DATA(insert ( 1976 23 23 5 s 521 403 0 )); +DATA(insert ( 1976 23 23 6 o 3366 403 1976 )); /* crosstype operators int42 */ DATA(insert ( 1976 23 21 1 s 535 403 0 )); DATA(insert ( 1976 23 21 2 s 541 403 0 )); @@ -141,6 +143,7 @@ DATA(insert ( 1976 20 20 2 s 414 403 0 )); DATA(insert ( 1976 20 20 3 s 410 403 0 )); DATA(insert ( 1976 20 20 4 s 415 403 0 )); DATA(insert ( 1976 20 20 5 s 413 403 0 )); +DATA(insert ( 1976 20 20 6 o 3367 403 1976 )); /* crosstype operators int82 */ DATA(insert ( 1976 20 21 1 s 1870 403 0 )); DATA(insert ( 1976 20 21 2 s 1872 403 0 )); @@ -163,6 +166,7 @@ DATA(insert ( 1989 26 26 2 s 611 403 0 )); DATA(insert ( 1989 26 26 3 s 607 403 0 )); DATA(insert ( 1989 26 26 4 s 612 403 0 )); DATA(insert ( 1989 26 26 5 s 610 403 0 )); +DATA(insert ( 1989 26 26 6 o 3368 403 1989 )); /* * btree tid_ops @@ -194,6 +198,7 @@ DATA(insert ( 1970 700 700 2 s 624 403 0 )); DATA(insert ( 1970 700 700 3 s 620 403 0 )); DATA(insert ( 1970 700 700 4 s 625 403 0 )); DATA(insert ( 1970 700 700 5 s 623 403 0 )); +DATA(insert ( 1970 700 700 6 o 3369 403 1970 )); /* crosstype operators float48 */ DATA(insert ( 1970 700 701 1 s 1122 403 0 )); DATA(insert ( 1970 700 701 2 s 1124 403 0 )); @@ -206,6 +211,7 @@ DATA(insert ( 1970 701 701 2 s 673 403 0 )); DATA(insert ( 1970 701 701 3 s 670 403 0 )); DATA(insert ( 1970 701 701 4 s 675 403 0 )); DATA(insert ( 1970 701 701 5 s 674 403 0 )); +DATA(insert ( 1970 701 701 6 o 3370 403 1970 )); /* crosstype operators float84 */ DATA(insert ( 1970 701 700 1 s 1132 403 0 )); DATA(insert ( 1970 701 700 2 s 1134 403 0 )); @@ -283,6 +289,7 @@ DATA(insert ( 434 1082 1082 2 s 1096 403 0 )); DATA(insert ( 434 1082 1082 3 s 1093 403 0 )); DATA(insert ( 434 1082 1082 4 s 1098 403 0 )); DATA(insert ( 434 1082 1082 5 s 1097 403 0 )); +DATA(insert ( 434 1082 1082 6 o 3372 403 1976 )); /* crosstype operators vs timestamp */ DATA(insert ( 434 1082 1114 1 s 2345 403 0 )); DATA(insert ( 434 1082 1114 2 s 2346 403 0 )); @@ -301,6 +308,7 @@ DATA(insert ( 434 1114 1114 2 s 2063 403 0 )); DATA(insert ( 434 1114 1114 3 s 2060 403 0 )); DATA(insert ( 434 1114 1114 4 s 2065 403 0 )); DATA(insert ( 434 1114 1114 5 s 2064 403 0 )); +DATA(insert ( 434 1114 1114 6 o 3374 403 1982 )); /* crosstype operators vs date */ DATA(insert ( 434 1114 1082 1 s 2371 403 0 )); DATA(insert ( 434 1114 1082 2 s 2372 403 0 )); @@ -319,6 +327,7 @@ DATA(insert ( 434 1184 1184 2 s 1323 403 0 )); DATA(insert ( 434 1184 1184 3 s 1320 403 0 )); DATA(insert ( 434 1184 1184 4 s 1325 403 0 )); DATA(insert ( 434 1184 1184 5 s 1324 403 0 )); +DATA(insert ( 434 1184 1184 6 o 3375 403 1982 )); /* crosstype operators vs date */ DATA(insert ( 434 1184 1082 1 s 2384 403 0 )); DATA(insert ( 434 1184 1082 2 s 2385 403 0 )); @@ -341,6 +350,7 @@ DATA(insert ( 1996 1083 1083 2 s 1111 403 0 )); DATA(insert ( 1996 1083 1083 3 s 1108 403 0 )); DATA(insert ( 1996 1083 1083 4 s 1113 403 0 )); DATA(insert ( 1996 1083 1083 5 s 1112 403 0 )); +DATA(insert ( 1996 1083 1083 6 o 3373 403 1982 )); /* * btree timetz_ops @@ -361,6 +371,7 @@ DATA(insert ( 1982 1186 1186 2 s 1333 403 0 )); DATA(insert ( 1982 1186 1186 3 s 1330 403 0 )); DATA(insert ( 1982 1186 1186 4 s 1335 403 0 )); DATA(insert ( 1982 1186 1186 5 s 1334 403 0 )); +DATA(insert ( 1982 1186 1186 6 o 3376 403 1982 )); /* * btree macaddr @@ -451,6 +462,7 @@ DATA(insert ( 2099 790 790 2 s 904 403 0 )); DATA(insert ( 2099 790 790 3 s 900 403 0 )); DATA(insert ( 2099 790 790 4 s 905 403 0 )); DATA(insert ( 2099 790 790 5 s 903 403 0 )); +DATA(insert ( 2099 790 790 6 o 3371 403 2099 )); /* * btree reltime_ops diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h index 45feb69..dca1706 100644 --- a/src/include/catalog/pg_operator.h +++ b/src/include/catalog/pg_operator.h @@ -1624,6 +1624,32 @@ DESCR("greater than or equal"); DATA(insert OID = 3228 ( "-" PGNSP PGUID b f f 3220 3220 1700 0 0 pg_lsn_mi - - )); DESCR("minus"); +/* distance operators */ +DATA(insert OID = 3365 ( "<->" PGNSP PGUID b f f 21 21 21 3365 0 int2_dist - - )); +DESCR("distance between"); +DATA(insert OID = 3366 ( "<->" PGNSP PGUID b f f 23 23 23 3366 0 int4_dist - - )); +DESCR("distance between"); +DATA(insert OID = 3367 ( "<->" PGNSP PGUID b f f 20 20 20 3367 0 int8_dist - - )); +DESCR("distance between"); +DATA(insert OID = 3368 ( "<->" PGNSP PGUID b f f 26 26 26 3368 0 oid_dist - - )); +DESCR("distance between"); +DATA(insert OID = 3369 ( "<->" PGNSP PGUID b f f 700 700 700 3369 0 float4_dist - - )); +DESCR("distance between"); +DATA(insert OID = 3370 ( "<->" PGNSP PGUID b f f 701 701 701 3370 0 float8_dist - - )); +DESCR("distance between"); +DATA(insert OID = 3371 ( "<->" PGNSP PGUID b f f 790 790 790 3371 0 cash_dist - - )); +DESCR("distance between"); +DATA(insert OID = 3372 ( "<->" PGNSP PGUID b f f 1082 1082 23 3372 0 date_dist - - )); +DESCR("distance between"); +DATA(insert OID = 3373 ( "<->" PGNSP PGUID b f f 1083 1083 1186 3373 0 time_dist - - )); +DESCR("distance between"); +DATA(insert OID = 3374 ( "<->" PGNSP PGUID b f f 1114 1114 1186 3374 0 ts_dist - - )); +DESCR("distance between"); +DATA(insert OID = 3375 ( "<->" PGNSP PGUID b f f 1184 1184 1186 3375 0 tstz_dist - - )); +DESCR("distance between"); +DATA(insert OID = 3376 ( "<->" PGNSP PGUID b f f 1186 1186 1186 3376 0 interval_dist - - )); +DESCR("distance between"); + /* enum operators */ DATA(insert OID = 3516 ( "=" PGNSP PGUID b t t 3500 3500 16 3516 3517 enum_eq eqsel eqjoinsel )); DESCR("equal"); diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 05652e8..ca418fe 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -5361,6 +5361,21 @@ DESCR("pg_controldata init state information as a function"); DATA(insert OID = 3445 ( pg_import_system_collations PGNSP PGUID 12 100 0 0 0 f f f f t f v r 2 0 2278 "16 4089" _null_ _null_ "{if_not_exists,schema}" _null_ _null_ pg_import_system_collations _null_ _null_ _null_ )); DESCR("import collations from operating system"); +/* distance functions */ +DATA(insert OID = 3353 ( int2_dist PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 21 "21 21" _null_ _null_ _null_ _null_ _null_ int2_dist _null_ _null_ _null_ )); +DATA(insert OID = 3354 ( int4_dist PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 23 "23 23" _null_ _null_ _null_ _null_ _null_ int4_dist _null_ _null_ _null_ )); +DATA(insert OID = 3355 ( int8_dist PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 20 "20 20" _null_ _null_ _null_ _null_ _null_ int8_dist _null_ _null_ _null_ )); +DATA(insert OID = 3356 ( oid_dist PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 26 "26 26" _null_ _null_ _null_ _null_ _null_ oid_dist _null_ _null_ _null_ )); +DATA(insert OID = 3357 ( float4_dist PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 700 "700 700" _null_ _null_ _null_ _null_ _null_ float4_dist _null_ _null_ _null_ )); +DATA(insert OID = 3358 ( float8_dist PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 701 "701 701" _null_ _null_ _null_ _null_ _null_ float8_dist _null_ _null_ _null_ )); +DATA(insert OID = 3359 ( cash_dist PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 790 "790 790" _null_ _null_ _null_ _null_ _null_ cash_dist _null_ _null_ _null_ )); +DATA(insert OID = 3360 ( date_dist PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 23 "1082 1082" _null_ _null_ _null_ _null_ _null_ date_dist _null_ _null_ _null_ )); +DATA(insert OID = 3361 ( time_dist PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 1186 "1083 1083" _null_ _null_ _null_ _null_ _null_ time_dist _null_ _null_ _null_ )); +DATA(insert OID = 3362 ( ts_dist PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 1186 "1114 1114" _null_ _null_ _null_ _null_ _null_ ts_dist _null_ _null_ _null_ )); +DATA(insert OID = 3363 ( tstz_dist PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 1186 "1184 1184" _null_ _null_ _null_ _null_ _null_ tstz_dist _null_ _null_ _null_ )); +DATA(insert OID = 3364 ( interval_dist PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 1186 "1186 1186" _null_ _null_ _null_ _null_ _null_ interval_dist _null_ _null_ _null_ )); + + /* * Symbolic values for provolatile column: these indicate whether the result * of a function is dependent *only* on the values of its explicit arguments, diff --git a/src/include/utils/datetime.h b/src/include/utils/datetime.h index f0e7798..4b4d02d 100644 --- a/src/include/utils/datetime.h +++ b/src/include/utils/datetime.h @@ -346,4 +346,6 @@ extern TimeZoneAbbrevTable *ConvertTimeZoneAbbrevs(struct tzEntry *abbrevs, int n); extern void InstallTimeZoneAbbrevs(TimeZoneAbbrevTable *tbl); +extern Interval *abs_interval(Interval *a); + #endif /* DATETIME_H */ diff --git a/src/test/regress/expected/amutils.out b/src/test/regress/expected/amutils.out index 74f7c9f..55296b7 100644 --- a/src/test/regress/expected/amutils.out +++ b/src/test/regress/expected/amutils.out @@ -24,7 +24,7 @@ select prop, nulls_first | | | f nulls_last | | | t orderable | | | t - distance_orderable | | | f + distance_orderable | | | t returnable | | | t search_array | | | t search_nulls | | | t @@ -97,7 +97,7 @@ select prop, nulls_first | f | f | f | f | f | f nulls_last | t | f | f | f | f | f orderable | t | f | f | f | f | f - distance_orderable | f | f | t | f | f | f + distance_orderable | t | f | t | f | f | f returnable | t | f | f | t | f | f search_array | t | f | f | f | f | f search_nulls | t | f | t | t | f | t diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index 0bcec13..fb44ae8 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -1721,6 +1721,7 @@ ORDER BY 1, 2, 3; 403 | 5 | *> 403 | 5 | > 403 | 5 | ~>~ + 403 | 6 | <-> 405 | 1 | = 783 | 1 | << 783 | 1 | @@ @@ -1828,7 +1829,7 @@ ORDER BY 1, 2, 3; 4000 | 25 | <<= 4000 | 26 | >> 4000 | 27 | >>= -(121 rows) +(122 rows) -- Check that all opclass search operators have selectivity estimators. -- This is not absolutely required, but it seems a reasonable thing