From 0de45c1c2753763c5ca7d4904075e2cae80bb353 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 22 Mar 2004 01:38:18 +0000 Subject: [PATCH] Add timestamp-versus-timestamptz cross-type comparison functions, flesh out the index operator classes to include these. In passing, fix erroneous volatility marking of ACL functions. --- src/backend/utils/adt/timestamp.c | 185 ++++++++++++++++++++++++++++++- src/include/catalog/catversion.h | 4 +- src/include/catalog/pg_amop.h | 14 ++- src/include/catalog/pg_amproc.h | 4 +- src/include/catalog/pg_operator.h | 26 ++++- src/include/catalog/pg_proc.h | 44 +++++++- src/include/utils/timestamp.h | 18 ++- src/test/regress/expected/opr_sanity.out | 44 ++++++++ src/test/regress/sql/opr_sanity.sql | 34 ++++++ 9 files changed, 355 insertions(+), 18 deletions(-) diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c index 582bc369d5..11a75cc2bd 100644 --- a/src/backend/utils/adt/timestamp.c +++ b/src/backend/utils/adt/timestamp.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.101 2004/03/15 03:29:22 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.102 2004/03/22 01:38:17 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -48,6 +48,7 @@ static int EncodeSpecialTimestamp(Timestamp dt, char *str); static Timestamp dt2local(Timestamp dt, int timezone); static void AdjustTimestampForTypmod(Timestamp *time, int32 typmod); static void AdjustIntervalForTypmod(Interval *interval, int32 typmod); +static TimestampTz timestamp2timestamptz(Timestamp timestamp); /***************************************************************************** @@ -1394,6 +1395,179 @@ timestamp_cmp(PG_FUNCTION_ARGS) /* + * Crosstype comparison functions for timestamp vs timestamptz + */ + +Datum +timestamp_eq_timestamptz(PG_FUNCTION_ARGS) +{ + Timestamp timestampVal = PG_GETARG_TIMESTAMP(0); + TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1); + TimestampTz dt1; + + dt1 = timestamp2timestamptz(timestampVal); + + PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) == 0); +} + +Datum +timestamp_ne_timestamptz(PG_FUNCTION_ARGS) +{ + Timestamp timestampVal = PG_GETARG_TIMESTAMP(0); + TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1); + TimestampTz dt1; + + dt1 = timestamp2timestamptz(timestampVal); + + PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) != 0); +} + +Datum +timestamp_lt_timestamptz(PG_FUNCTION_ARGS) +{ + Timestamp timestampVal = PG_GETARG_TIMESTAMP(0); + TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1); + TimestampTz dt1; + + dt1 = timestamp2timestamptz(timestampVal); + + PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) < 0); +} + +Datum +timestamp_gt_timestamptz(PG_FUNCTION_ARGS) +{ + Timestamp timestampVal = PG_GETARG_TIMESTAMP(0); + TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1); + TimestampTz dt1; + + dt1 = timestamp2timestamptz(timestampVal); + + PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) > 0); +} + +Datum +timestamp_le_timestamptz(PG_FUNCTION_ARGS) +{ + Timestamp timestampVal = PG_GETARG_TIMESTAMP(0); + TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1); + TimestampTz dt1; + + dt1 = timestamp2timestamptz(timestampVal); + + PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) <= 0); +} + +Datum +timestamp_ge_timestamptz(PG_FUNCTION_ARGS) +{ + Timestamp timestampVal = PG_GETARG_TIMESTAMP(0); + TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1); + TimestampTz dt1; + + dt1 = timestamp2timestamptz(timestampVal); + + PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) >= 0); +} + +Datum +timestamp_cmp_timestamptz(PG_FUNCTION_ARGS) +{ + Timestamp timestampVal = PG_GETARG_TIMESTAMP(0); + TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1); + TimestampTz dt1; + + dt1 = timestamp2timestamptz(timestampVal); + + PG_RETURN_INT32(timestamp_cmp_internal(dt1, dt2)); +} + +Datum +timestamptz_eq_timestamp(PG_FUNCTION_ARGS) +{ + TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0); + Timestamp timestampVal = PG_GETARG_TIMESTAMP(1); + TimestampTz dt2; + + dt2 = timestamp2timestamptz(timestampVal); + + PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) == 0); +} + +Datum +timestamptz_ne_timestamp(PG_FUNCTION_ARGS) +{ + TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0); + Timestamp timestampVal = PG_GETARG_TIMESTAMP(1); + TimestampTz dt2; + + dt2 = timestamp2timestamptz(timestampVal); + + PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) != 0); +} + +Datum +timestamptz_lt_timestamp(PG_FUNCTION_ARGS) +{ + TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0); + Timestamp timestampVal = PG_GETARG_TIMESTAMP(1); + TimestampTz dt2; + + dt2 = timestamp2timestamptz(timestampVal); + + PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) < 0); +} + +Datum +timestamptz_gt_timestamp(PG_FUNCTION_ARGS) +{ + TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0); + Timestamp timestampVal = PG_GETARG_TIMESTAMP(1); + TimestampTz dt2; + + dt2 = timestamp2timestamptz(timestampVal); + + PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) > 0); +} + +Datum +timestamptz_le_timestamp(PG_FUNCTION_ARGS) +{ + TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0); + Timestamp timestampVal = PG_GETARG_TIMESTAMP(1); + TimestampTz dt2; + + dt2 = timestamp2timestamptz(timestampVal); + + PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) <= 0); +} + +Datum +timestamptz_ge_timestamp(PG_FUNCTION_ARGS) +{ + TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0); + Timestamp timestampVal = PG_GETARG_TIMESTAMP(1); + TimestampTz dt2; + + dt2 = timestamp2timestamptz(timestampVal); + + PG_RETURN_BOOL(timestamp_cmp_internal(dt1, dt2) >= 0); +} + +Datum +timestamptz_cmp_timestamp(PG_FUNCTION_ARGS) +{ + TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0); + Timestamp timestampVal = PG_GETARG_TIMESTAMP(1); + TimestampTz dt2; + + dt2 = timestamp2timestamptz(timestampVal); + + PG_RETURN_INT32(timestamp_cmp_internal(dt1, dt2)); +} + + +/* * interval_relop - is interval1 relop interval2 * * collate invalid interval at the end @@ -3635,6 +3809,13 @@ Datum timestamp_timestamptz(PG_FUNCTION_ARGS) { Timestamp timestamp = PG_GETARG_TIMESTAMP(0); + + PG_RETURN_TIMESTAMPTZ(timestamp2timestamptz(timestamp)); +} + +static TimestampTz +timestamp2timestamptz(Timestamp timestamp) +{ TimestampTz result; struct tm tt, *tm = &tt; @@ -3658,7 +3839,7 @@ timestamp_timestamptz(PG_FUNCTION_ARGS) errmsg("timestamp out of range"))); } - PG_RETURN_TIMESTAMPTZ(result); + return result; } /* timestamptz_timestamp() diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 2d91191c49..5344d7afe8 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.221 2004/03/17 20:48:42 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.222 2004/03/22 01:38:17 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 200403171 +#define CATALOG_VERSION_NO 200403211 #endif diff --git a/src/include/catalog/pg_amop.h b/src/include/catalog/pg_amop.h index 8d7c59c5bf..f2385a8058 100644 --- a/src/include/catalog/pg_amop.h +++ b/src/include/catalog/pg_amop.h @@ -23,7 +23,7 @@ * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_amop.h,v 1.58 2004/02/14 20:16:17 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_amop.h,v 1.59 2004/03/22 01:38:17 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -349,6 +349,12 @@ DATA(insert ( 2039 1082 2 f 2372 )); DATA(insert ( 2039 1082 3 f 2373 )); DATA(insert ( 2039 1082 4 f 2374 )); DATA(insert ( 2039 1082 5 f 2375 )); +/* crosstype operators vs timestamptz */ +DATA(insert ( 2039 1184 1 f 2534 )); +DATA(insert ( 2039 1184 2 f 2535 )); +DATA(insert ( 2039 1184 3 f 2536 )); +DATA(insert ( 2039 1184 4 f 2537 )); +DATA(insert ( 2039 1184 5 f 2538 )); /* * btree timestamptz_ops @@ -365,6 +371,12 @@ DATA(insert ( 1998 1082 2 f 2385 )); DATA(insert ( 1998 1082 3 f 2386 )); DATA(insert ( 1998 1082 4 f 2387 )); DATA(insert ( 1998 1082 5 f 2388 )); +/* crosstype operators vs timestamp */ +DATA(insert ( 1998 1114 1 f 2540 )); +DATA(insert ( 1998 1114 2 f 2541 )); +DATA(insert ( 1998 1114 3 f 2542 )); +DATA(insert ( 1998 1114 4 f 2543 )); +DATA(insert ( 1998 1114 5 f 2544 )); /* * btree interval_ops diff --git a/src/include/catalog/pg_amproc.h b/src/include/catalog/pg_amproc.h index c8fbf62432..c23984d06c 100644 --- a/src/include/catalog/pg_amproc.h +++ b/src/include/catalog/pg_amproc.h @@ -19,7 +19,7 @@ * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_amproc.h,v 1.47 2004/02/14 20:16:17 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_amproc.h,v 1.48 2004/03/22 01:38:17 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -117,11 +117,13 @@ DATA(insert ( 1994 0 1 360 )); DATA(insert ( 1996 0 1 1107 )); DATA(insert ( 1998 0 1 1314 )); DATA(insert ( 1998 1082 1 2383 )); +DATA(insert ( 1998 1114 1 2533 )); DATA(insert ( 2000 0 1 1358 )); DATA(insert ( 2002 0 1 1672 )); DATA(insert ( 2003 0 1 360 )); DATA(insert ( 2039 0 1 2045 )); DATA(insert ( 2039 1082 1 2370 )); +DATA(insert ( 2039 1184 1 2526 )); DATA(insert ( 2095 0 1 2166 )); DATA(insert ( 2096 0 1 2166 )); DATA(insert ( 2097 0 1 2180 )); diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h index 7ee7b4d1e6..d0761b148e 100644 --- a/src/include/catalog/pg_operator.h +++ b/src/include/catalog/pg_operator.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_operator.h,v 1.124 2004/02/14 20:16:17 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_operator.h,v 1.125 2004/03/22 01:38:17 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -822,32 +822,48 @@ DATA(insert OID = 2337 ( "~<>~" PGNSP PGUID b f 19 19 16 2337 2334 0 0 0 0 name_ DATA(insert OID = 2345 ( "<" PGNSP PGUID b f 1082 1114 16 2375 2348 0 0 0 0 date_lt_timestamp scalarltsel scalarltjoinsel )); DATA(insert OID = 2346 ( "<=" PGNSP PGUID b f 1082 1114 16 2374 2349 0 0 0 0 date_le_timestamp scalarltsel scalarltjoinsel )); -DATA(insert OID = 2347 ( "=" PGNSP PGUID b f 1082 1114 16 2373 2350 0 0 0 0 date_eq_timestamp eqsel eqjoinsel )); +DATA(insert OID = 2347 ( "=" PGNSP PGUID b f 1082 1114 16 2373 2350 1095 2062 2345 2349 date_eq_timestamp eqsel eqjoinsel )); DATA(insert OID = 2348 ( ">=" PGNSP PGUID b f 1082 1114 16 2372 2345 0 0 0 0 date_ge_timestamp scalargtsel scalargtjoinsel )); DATA(insert OID = 2349 ( ">" PGNSP PGUID b f 1082 1114 16 2371 2346 0 0 0 0 date_gt_timestamp scalargtsel scalargtjoinsel )); DATA(insert OID = 2350 ( "<>" PGNSP PGUID b f 1082 1114 16 2376 2347 0 0 0 0 date_ne_timestamp neqsel neqjoinsel )); DATA(insert OID = 2358 ( "<" PGNSP PGUID b f 1082 1184 16 2388 2361 0 0 0 0 date_lt_timestamptz scalarltsel scalarltjoinsel )); DATA(insert OID = 2359 ( "<=" PGNSP PGUID b f 1082 1184 16 2387 2362 0 0 0 0 date_le_timestamptz scalarltsel scalarltjoinsel )); -DATA(insert OID = 2360 ( "=" PGNSP PGUID b f 1082 1184 16 2386 2363 0 0 0 0 date_eq_timestamptz eqsel eqjoinsel )); +DATA(insert OID = 2360 ( "=" PGNSP PGUID b f 1082 1184 16 2386 2363 1095 1322 2358 2362 date_eq_timestamptz eqsel eqjoinsel )); DATA(insert OID = 2361 ( ">=" PGNSP PGUID b f 1082 1184 16 2385 2358 0 0 0 0 date_ge_timestamptz scalargtsel scalargtjoinsel )); DATA(insert OID = 2362 ( ">" PGNSP PGUID b f 1082 1184 16 2384 2359 0 0 0 0 date_gt_timestamptz scalargtsel scalargtjoinsel )); DATA(insert OID = 2363 ( "<>" PGNSP PGUID b f 1082 1184 16 2389 2360 0 0 0 0 date_ne_timestamptz neqsel neqjoinsel )); DATA(insert OID = 2371 ( "<" PGNSP PGUID b f 1114 1082 16 2349 2374 0 0 0 0 timestamp_lt_date scalarltsel scalarltjoinsel )); DATA(insert OID = 2372 ( "<=" PGNSP PGUID b f 1114 1082 16 2348 2375 0 0 0 0 timestamp_le_date scalarltsel scalarltjoinsel )); -DATA(insert OID = 2373 ( "=" PGNSP PGUID b f 1114 1082 16 2347 2376 0 0 0 0 timestamp_eq_date eqsel eqjoinsel )); +DATA(insert OID = 2373 ( "=" PGNSP PGUID b f 1114 1082 16 2347 2376 2062 1095 2371 2375 timestamp_eq_date eqsel eqjoinsel )); DATA(insert OID = 2374 ( ">=" PGNSP PGUID b f 1114 1082 16 2346 2371 0 0 0 0 timestamp_ge_date scalargtsel scalargtjoinsel )); DATA(insert OID = 2375 ( ">" PGNSP PGUID b f 1114 1082 16 2345 2372 0 0 0 0 timestamp_gt_date scalargtsel scalargtjoinsel )); DATA(insert OID = 2376 ( "<>" PGNSP PGUID b f 1114 1082 16 2350 2373 0 0 0 0 timestamp_ne_date neqsel neqjoinsel )); DATA(insert OID = 2384 ( "<" PGNSP PGUID b f 1184 1082 16 2362 2387 0 0 0 0 timestamptz_lt_date scalarltsel scalarltjoinsel )); DATA(insert OID = 2385 ( "<=" PGNSP PGUID b f 1184 1082 16 2361 2388 0 0 0 0 timestamptz_le_date scalarltsel scalarltjoinsel )); -DATA(insert OID = 2386 ( "=" PGNSP PGUID b f 1184 1082 16 2360 2389 0 0 0 0 timestamptz_eq_date eqsel eqjoinsel )); +DATA(insert OID = 2386 ( "=" PGNSP PGUID b f 1184 1082 16 2360 2389 1322 1095 2384 2388 timestamptz_eq_date eqsel eqjoinsel )); DATA(insert OID = 2387 ( ">=" PGNSP PGUID b f 1184 1082 16 2359 2384 0 0 0 0 timestamptz_ge_date scalargtsel scalargtjoinsel )); DATA(insert OID = 2388 ( ">" PGNSP PGUID b f 1184 1082 16 2358 2385 0 0 0 0 timestamptz_gt_date scalargtsel scalargtjoinsel )); DATA(insert OID = 2389 ( "<>" PGNSP PGUID b f 1184 1082 16 2363 2386 0 0 0 0 timestamptz_ne_date neqsel neqjoinsel )); +/* crosstype operations for timestamp vs. timestamptz */ + +DATA(insert OID = 2534 ( "<" PGNSP PGUID b f 1114 1184 16 2544 2537 0 0 0 0 timestamp_lt_timestamptz scalarltsel scalarltjoinsel )); +DATA(insert OID = 2535 ( "<=" PGNSP PGUID b f 1114 1184 16 2543 2538 0 0 0 0 timestamp_le_timestamptz scalarltsel scalarltjoinsel )); +DATA(insert OID = 2536 ( "=" PGNSP PGUID b f 1114 1184 16 2542 2539 2062 1322 2534 2538 timestamp_eq_timestamptz eqsel eqjoinsel )); +DATA(insert OID = 2537 ( ">=" PGNSP PGUID b f 1114 1184 16 2541 2534 0 0 0 0 timestamp_ge_timestamptz scalargtsel scalargtjoinsel )); +DATA(insert OID = 2538 ( ">" PGNSP PGUID b f 1114 1184 16 2540 2535 0 0 0 0 timestamp_gt_timestamptz scalargtsel scalargtjoinsel )); +DATA(insert OID = 2539 ( "<>" PGNSP PGUID b f 1114 1184 16 2545 2536 0 0 0 0 timestamp_ne_timestamptz neqsel neqjoinsel )); + +DATA(insert OID = 2540 ( "<" PGNSP PGUID b f 1184 1114 16 2538 2543 0 0 0 0 timestamptz_lt_timestamp scalarltsel scalarltjoinsel )); +DATA(insert OID = 2541 ( "<=" PGNSP PGUID b f 1184 1114 16 2537 2544 0 0 0 0 timestamptz_le_timestamp scalarltsel scalarltjoinsel )); +DATA(insert OID = 2542 ( "=" PGNSP PGUID b f 1184 1114 16 2536 2545 1322 2062 2540 2544 timestamptz_eq_timestamp eqsel eqjoinsel )); +DATA(insert OID = 2543 ( ">=" PGNSP PGUID b f 1184 1114 16 2535 2540 0 0 0 0 timestamptz_ge_timestamp scalargtsel scalargtjoinsel )); +DATA(insert OID = 2544 ( ">" PGNSP PGUID b f 1184 1114 16 2534 2541 0 0 0 0 timestamptz_gt_timestamp scalargtsel scalargtjoinsel )); +DATA(insert OID = 2545 ( "<>" PGNSP PGUID b f 1184 1114 16 2539 2542 0 0 0 0 timestamptz_ne_timestamp neqsel neqjoinsel )); + /* * function prototypes diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 9112d9df9d..739e1b0da6 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.321 2004/03/21 22:29:11 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.322 2004/03/22 01:38:18 tgl Exp $ * * NOTES * The script catalog/genbki.sh reads this file and generates .bki @@ -1324,15 +1324,15 @@ DATA(insert OID = 1031 ( aclitemin PGNSP PGUID 12 f f t f s 1 1033 "2275" _ DESCR("I/O"); DATA(insert OID = 1032 ( aclitemout PGNSP PGUID 12 f f t f s 1 2275 "1033" _null_ aclitemout - _null_ )); DESCR("I/O"); -DATA(insert OID = 1035 ( aclinsert PGNSP PGUID 12 f f t f s 2 1034 "1034 1033" _null_ aclinsert - _null_ )); +DATA(insert OID = 1035 ( aclinsert PGNSP PGUID 12 f f t f i 2 1034 "1034 1033" _null_ aclinsert - _null_ )); DESCR("add/update ACL item"); -DATA(insert OID = 1036 ( aclremove PGNSP PGUID 12 f f t f s 2 1034 "1034 1033" _null_ aclremove - _null_ )); +DATA(insert OID = 1036 ( aclremove PGNSP PGUID 12 f f t f i 2 1034 "1034 1033" _null_ aclremove - _null_ )); DESCR("remove ACL item"); -DATA(insert OID = 1037 ( aclcontains PGNSP PGUID 12 f f t f s 2 16 "1034 1033" _null_ aclcontains - _null_ )); +DATA(insert OID = 1037 ( aclcontains PGNSP PGUID 12 f f t f i 2 16 "1034 1033" _null_ aclcontains - _null_ )); DESCR("does ACL contain item?"); -DATA(insert OID = 1062 ( aclitemeq PGNSP PGUID 12 f f t f s 2 16 "1033 1033" _null_ aclitem_eq - _null_ )); +DATA(insert OID = 1062 ( aclitemeq PGNSP PGUID 12 f f t f i 2 16 "1033 1033" _null_ aclitem_eq - _null_ )); DESCR("equality operator for ACL items"); -DATA(insert OID = 1365 ( makeaclitem PGNSP PGUID 12 f f t f s 5 1033 "23 23 23 25 16" _null_ makeaclitem - _null_ )); +DATA(insert OID = 1365 ( makeaclitem PGNSP PGUID 12 f f t f i 5 1033 "23 23 23 25 16" _null_ makeaclitem - _null_ )); DESCR("make ACL item"); DATA(insert OID = 1038 ( seteval PGNSP PGUID 12 f f t t v 1 23 "26" _null_ seteval - _null_ )); DESCR("internal function supporting PostQuel-style sets"); @@ -3261,6 +3261,38 @@ DESCR("not equal"); DATA(insert OID = 2383 ( timestamptz_cmp_date PGNSP PGUID 12 f f t f s 2 23 "1184 1082" _null_ timestamptz_cmp_date - _null_ )); DESCR("less-equal-greater"); +/* crosstype operations for timestamp vs. timestamptz */ +DATA(insert OID = 2520 ( timestamp_lt_timestamptz PGNSP PGUID 12 f f t f s 2 16 "1114 1184" _null_ timestamp_lt_timestamptz - _null_ )); +DESCR("less-than"); +DATA(insert OID = 2521 ( timestamp_le_timestamptz PGNSP PGUID 12 f f t f s 2 16 "1114 1184" _null_ timestamp_le_timestamptz - _null_ )); +DESCR("less-than-or-equal"); +DATA(insert OID = 2522 ( timestamp_eq_timestamptz PGNSP PGUID 12 f f t f s 2 16 "1114 1184" _null_ timestamp_eq_timestamptz - _null_ )); +DESCR("equal"); +DATA(insert OID = 2523 ( timestamp_gt_timestamptz PGNSP PGUID 12 f f t f s 2 16 "1114 1184" _null_ timestamp_gt_timestamptz - _null_ )); +DESCR("greater-than"); +DATA(insert OID = 2524 ( timestamp_ge_timestamptz PGNSP PGUID 12 f f t f s 2 16 "1114 1184" _null_ timestamp_ge_timestamptz - _null_ )); +DESCR("greater-than-or-equal"); +DATA(insert OID = 2525 ( timestamp_ne_timestamptz PGNSP PGUID 12 f f t f s 2 16 "1114 1184" _null_ timestamp_ne_timestamptz - _null_ )); +DESCR("not equal"); +DATA(insert OID = 2526 ( timestamp_cmp_timestamptz PGNSP PGUID 12 f f t f s 2 23 "1114 1184" _null_ timestamp_cmp_timestamptz - _null_ )); +DESCR("less-equal-greater"); + +DATA(insert OID = 2527 ( timestamptz_lt_timestamp PGNSP PGUID 12 f f t f s 2 16 "1184 1114" _null_ timestamptz_lt_timestamp - _null_ )); +DESCR("less-than"); +DATA(insert OID = 2528 ( timestamptz_le_timestamp PGNSP PGUID 12 f f t f s 2 16 "1184 1114" _null_ timestamptz_le_timestamp - _null_ )); +DESCR("less-than-or-equal"); +DATA(insert OID = 2529 ( timestamptz_eq_timestamp PGNSP PGUID 12 f f t f s 2 16 "1184 1114" _null_ timestamptz_eq_timestamp - _null_ )); +DESCR("equal"); +DATA(insert OID = 2530 ( timestamptz_gt_timestamp PGNSP PGUID 12 f f t f s 2 16 "1184 1114" _null_ timestamptz_gt_timestamp - _null_ )); +DESCR("greater-than"); +DATA(insert OID = 2531 ( timestamptz_ge_timestamp PGNSP PGUID 12 f f t f s 2 16 "1184 1114" _null_ timestamptz_ge_timestamp - _null_ )); +DESCR("greater-than-or-equal"); +DATA(insert OID = 2532 ( timestamptz_ne_timestamp PGNSP PGUID 12 f f t f s 2 16 "1184 1114" _null_ timestamptz_ne_timestamp - _null_ )); +DESCR("not equal"); +DATA(insert OID = 2533 ( timestamptz_cmp_timestamp PGNSP PGUID 12 f f t f s 2 23 "1184 1114" _null_ timestamptz_cmp_timestamp - _null_ )); +DESCR("less-equal-greater"); + + /* send/receive functions */ DATA(insert OID = 2400 ( array_recv PGNSP PGUID 12 f f t f s 2 2277 "2281 26" _null_ array_recv - _null_ )); DESCR("I/O"); diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h index aa395ba4f4..9840cbdd4b 100644 --- a/src/include/utils/timestamp.h +++ b/src/include/utils/timestamp.h @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/utils/timestamp.h,v 1.34 2004/02/14 20:16:18 tgl Exp $ + * $PostgreSQL: pgsql/src/include/utils/timestamp.h,v 1.35 2004/03/22 01:38:18 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -169,6 +169,22 @@ extern Datum timestamp_cmp(PG_FUNCTION_ARGS); extern Datum timestamp_smaller(PG_FUNCTION_ARGS); extern Datum timestamp_larger(PG_FUNCTION_ARGS); +extern Datum timestamp_eq_timestamptz(PG_FUNCTION_ARGS); +extern Datum timestamp_ne_timestamptz(PG_FUNCTION_ARGS); +extern Datum timestamp_lt_timestamptz(PG_FUNCTION_ARGS); +extern Datum timestamp_le_timestamptz(PG_FUNCTION_ARGS); +extern Datum timestamp_gt_timestamptz(PG_FUNCTION_ARGS); +extern Datum timestamp_ge_timestamptz(PG_FUNCTION_ARGS); +extern Datum timestamp_cmp_timestamptz(PG_FUNCTION_ARGS); + +extern Datum timestamptz_eq_timestamp(PG_FUNCTION_ARGS); +extern Datum timestamptz_ne_timestamp(PG_FUNCTION_ARGS); +extern Datum timestamptz_lt_timestamp(PG_FUNCTION_ARGS); +extern Datum timestamptz_le_timestamp(PG_FUNCTION_ARGS); +extern Datum timestamptz_gt_timestamp(PG_FUNCTION_ARGS); +extern Datum timestamptz_ge_timestamp(PG_FUNCTION_ARGS); +extern Datum timestamptz_cmp_timestamp(PG_FUNCTION_ARGS); + extern Datum interval_in(PG_FUNCTION_ARGS); extern Datum interval_out(PG_FUNCTION_ARGS); extern Datum interval_recv(PG_FUNCTION_ARGS); diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index b76cc695f4..bd343d6e0f 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -787,6 +787,28 @@ WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND -----------+---------+-----+---------+--------- (0 rows) +-- Operators that are primary members of opclasses must be immutable (else +-- it suggests that the index ordering isn't fixed). Operators that are +-- cross-type members need only be stable, since they are just shorthands +-- for index probe queries. +SELECT p1.amopclaid, p1.amopopr, p2.oprname, p3.prosrc +FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3 +WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND + p1.amopsubtype = 0 AND + p3.provolatile != 'i'; + amopclaid | amopopr | oprname | prosrc +-----------+---------+---------+-------- +(0 rows) + +SELECT p1.amopclaid, p1.amopopr, p2.oprname, p3.prosrc +FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3 +WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND + p1.amopsubtype != 0 AND + p3.provolatile = 'v'; + amopclaid | amopopr | oprname | prosrc +-----------+---------+---------+-------- +(0 rows) + -- **************** pg_amproc **************** -- Look for illegal values in pg_amproc fields SELECT p1.amopclaid, p1.amprocnum @@ -900,3 +922,25 @@ WHERE p3.opcamid = (SELECT oid FROM pg_am WHERE amname = 'hash') -----------+-----------+-----+---------+--------- (0 rows) +-- Support routines that are primary members of opclasses must be immutable +-- (else it suggests that the index ordering isn't fixed). But cross-type +-- members need only be stable, since they are just shorthands +-- for index probe queries. +SELECT p1.amopclaid, p1.amproc, p2.prosrc +FROM pg_amproc AS p1, pg_proc AS p2 +WHERE p1.amproc = p2.oid AND + p1.amprocsubtype = 0 AND + p2.provolatile != 'i'; + amopclaid | amproc | prosrc +-----------+--------+-------- +(0 rows) + +SELECT p1.amopclaid, p1.amproc, p2.prosrc +FROM pg_amproc AS p1, pg_proc AS p2 +WHERE p1.amproc = p2.oid AND + p1.amprocsubtype != 0 AND + p2.provolatile = 'v'; + amopclaid | amproc | prosrc +-----------+--------+-------- +(0 rows) + diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql index 82a294db6d..cf274dd7dc 100644 --- a/src/test/regress/sql/opr_sanity.sql +++ b/src/test/regress/sql/opr_sanity.sql @@ -640,6 +640,23 @@ WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND p1.amopsubtype != 0 AND p1.amopsubtype != p2.oprright; +-- Operators that are primary members of opclasses must be immutable (else +-- it suggests that the index ordering isn't fixed). Operators that are +-- cross-type members need only be stable, since they are just shorthands +-- for index probe queries. + +SELECT p1.amopclaid, p1.amopopr, p2.oprname, p3.prosrc +FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3 +WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND + p1.amopsubtype = 0 AND + p3.provolatile != 'i'; + +SELECT p1.amopclaid, p1.amopopr, p2.oprname, p3.prosrc +FROM pg_amop AS p1, pg_operator AS p2, pg_proc AS p3 +WHERE p1.amopopr = p2.oid AND p2.oprcode = p3.oid AND + p1.amopsubtype != 0 AND + p3.provolatile = 'v'; + -- **************** pg_amproc **************** -- Look for illegal values in pg_amproc fields @@ -738,3 +755,20 @@ WHERE p3.opcamid = (SELECT oid FROM pg_am WHERE amname = 'hash') OR pronargs != 1 -- OR NOT physically_coercible(opcintype, proargtypes[0]) ); + +-- Support routines that are primary members of opclasses must be immutable +-- (else it suggests that the index ordering isn't fixed). But cross-type +-- members need only be stable, since they are just shorthands +-- for index probe queries. + +SELECT p1.amopclaid, p1.amproc, p2.prosrc +FROM pg_amproc AS p1, pg_proc AS p2 +WHERE p1.amproc = p2.oid AND + p1.amprocsubtype = 0 AND + p2.provolatile != 'i'; + +SELECT p1.amopclaid, p1.amproc, p2.prosrc +FROM pg_amproc AS p1, pg_proc AS p2 +WHERE p1.amproc = p2.oid AND + p1.amprocsubtype != 0 AND + p2.provolatile = 'v'; -- 2.11.0