From 7c243f83e82238b65c8ee08054be838fe723f704 Mon Sep 17 00:00:00 2001 From: "Thomas G. Lockhart" Date: Thu, 4 Sep 1997 18:43:59 +0000 Subject: [PATCH] Add comparision routines and catalog entries to support indices on datetime and timespan. --- src/backend/utils/adt/dt.c | 57 +++++++++++++- src/include/catalog/pg_amop.h | 128 ++++++++++++++++++------------- src/include/catalog/pg_amproc.h | 116 ++++++++++++++-------------- src/include/catalog/pg_opclass.h | 48 ++++++------ src/include/catalog/pg_proc.h | 4 +- 5 files changed, 219 insertions(+), 134 deletions(-) diff --git a/src/backend/utils/adt/dt.c b/src/backend/utils/adt/dt.c index 5001f32d08..a141fa042e 100644 --- a/src/backend/utils/adt/dt.c +++ b/src/backend/utils/adt/dt.c @@ -7,7 +7,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/utils/adt/Attic/dt.c,v 1.34 1997/09/01 06:13:21 thomas Exp $ + * $Header: /cvsroot/pgsql/src/backend/utils/adt/Attic/dt.c,v 1.35 1997/09/04 18:43:21 thomas Exp $ * *------------------------------------------------------------------------- */ @@ -473,6 +473,35 @@ datetime_ge(DateTime *datetime1, DateTime *datetime2) } /* datetime_ge() */ +/* datetime_cmp - 3-state comparison for datetime + * collate invalid datetime at the end + */ +int +datetime_cmp(DateTime *datetime1, DateTime *datetime2) +{ + DateTime dt1, dt2; + + if (!PointerIsValid(datetime1) || !PointerIsValid(datetime2)) + return 0; + + dt1 = *datetime1; + dt2 = *datetime2; + + if (DATETIME_IS_INVALID(dt1)) { + return( (DATETIME_IS_INVALID(dt2)? 0: 1)); + + } else if (DATETIME_IS_INVALID(dt2)) { + return( -1); + + } else { + if (DATETIME_IS_RELATIVE(dt1)) dt1 = SetDateTime(dt1); + if (DATETIME_IS_RELATIVE(dt2)) dt2 = SetDateTime(dt2); + }; + + return( ((dt1 < dt2)? -1: ((dt1 > dt2)? 1: 0))); +} /* datetime_cmp() */ + + /* timespan_relop - is timespan1 relop timespan2 */ bool @@ -578,6 +607,32 @@ timespan_ge(TimeSpan *timespan1, TimeSpan *timespan2) } /* timespan_ge() */ +/* timespan_cmp - 3-state comparison for timespan + */ +int +timespan_cmp(TimeSpan *timespan1, TimeSpan *timespan2) +{ + double span1, span2; + + if (!PointerIsValid(timespan1) || !PointerIsValid(timespan2)) + return 0; + + if (TIMESPAN_IS_INVALID(*timespan1)) { + return( TIMESPAN_IS_INVALID(*timespan2)? 0: 1); + + } else if (TIMESPAN_IS_INVALID(*timespan2)) { + return( -1); + }; + + span1 = timespan1->time; + if (timespan1->month != 0) span1 += (timespan1->month * (30.0*86400)); + span2 = timespan2->time; + if (timespan2->month != 0) span2 += (timespan2->month * (30.0*86400)); + + return( (span1 < span2)? -1: (span1 > span2)? 1: 0); +} /* timespan_cmp() */ + + /*---------------------------------------------------------- * "Arithmetic" operators on date/times. * datetime_foo returns foo as an object (pointer) that diff --git a/src/include/catalog/pg_amop.h b/src/include/catalog/pg_amop.h index 63ee8988a6..8385db0846 100644 --- a/src/include/catalog/pg_amop.h +++ b/src/include/catalog/pg_amop.h @@ -7,7 +7,7 @@ * * Copyright (c) 1994, Regents of the University of California * - * $Id: pg_amop.h,v 1.4 1996/11/13 20:50:50 scrappy Exp $ + * $Id: pg_amop.h,v 1.5 1997/09/04 18:43:57 thomas Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -103,6 +103,19 @@ DATA(insert OID = 0 ( 402 434 490 6 rtsel rtnpage )); DATA(insert OID = 0 ( 402 434 491 7 rtsel rtnpage )); DATA(insert OID = 0 ( 402 434 492 8 rtsel rtnpage )); +/* + * rtree circle_ops (supports circles) + */ + +DATA(insert OID = 0 ( 402 714 1506 1 rtsel rtnpage )); +DATA(insert OID = 0 ( 402 714 1507 2 rtsel rtnpage )); +DATA(insert OID = 0 ( 402 714 1508 3 rtsel rtnpage )); +DATA(insert OID = 0 ( 402 714 1509 4 rtsel rtnpage )); +DATA(insert OID = 0 ( 402 714 1510 5 rtsel rtnpage )); +DATA(insert OID = 0 ( 402 714 1511 6 rtsel rtnpage )); +DATA(insert OID = 0 ( 402 714 1512 7 rtsel rtnpage )); +DATA(insert OID = 0 ( 402 714 1513 8 rtsel rtnpage )); + /* * nbtree int2_ops */ @@ -324,6 +337,26 @@ DATA(insert OID = 0 ( 403 1115 1108 3 btreesel btreenpage )); DATA(insert OID = 0 ( 403 1115 1113 4 btreesel btreenpage )); DATA(insert OID = 0 ( 403 1115 1112 5 btreesel btreenpage )); +/* + * nbtree datetime_ops + */ + +DATA(insert OID = 0 ( 403 1312 1322 1 btreesel btreenpage )); +DATA(insert OID = 0 ( 403 1312 1323 2 btreesel btreenpage )); +DATA(insert OID = 0 ( 403 1312 1320 3 btreesel btreenpage )); +DATA(insert OID = 0 ( 403 1312 1325 4 btreesel btreenpage )); +DATA(insert OID = 0 ( 403 1312 1324 5 btreesel btreenpage )); + +/* + * nbtree timespan_ops + */ + +DATA(insert OID = 0 ( 403 1313 1332 1 btreesel btreenpage )); +DATA(insert OID = 0 ( 403 1313 1333 2 btreesel btreenpage )); +DATA(insert OID = 0 ( 403 1313 1330 3 btreesel btreenpage )); +DATA(insert OID = 0 ( 403 1313 1335 4 btreesel btreenpage )); +DATA(insert OID = 0 ( 403 1313 1334 5 btreesel btreenpage )); + BKI_BEGIN #ifdef NOBTREE BKI_END @@ -477,78 +510,69 @@ DATA(insert OID = 0 ( 404 432 560 3 btreesel btreenpage )); DATA(insert OID = 0 ( 404 432 565 4 btreesel btreenpage )); DATA(insert OID = 0 ( 404 432 563 5 btreesel btreenpage )); +/* + * nobtree datetime_ops + */ + +DATA(insert OID = 0 ( 404 1312 1322 1 btreesel btreenpage )); +DATA(insert OID = 0 ( 404 1312 1323 2 btreesel btreenpage )); +DATA(insert OID = 0 ( 404 1312 1320 3 btreesel btreenpage )); +DATA(insert OID = 0 ( 404 1312 1325 4 btreesel btreenpage )); +DATA(insert OID = 0 ( 404 1312 1324 5 btreesel btreenpage )); + +/* + * nobtree timespan_ops + */ + +DATA(insert OID = 0 ( 404 1313 1332 1 btreesel btreenpage )); +DATA(insert OID = 0 ( 404 1313 1333 2 btreesel btreenpage )); +DATA(insert OID = 0 ( 404 1313 1330 3 btreesel btreenpage )); +DATA(insert OID = 0 ( 404 1313 1335 4 btreesel btreenpage )); +DATA(insert OID = 0 ( 404 1313 1334 5 btreesel btreenpage )); + BKI_BEGIN #endif /* NOBTREE */ BKI_END /* - * hash table int2_ops + * hash table _ops */ + +/* hash table int2_ops */ DATA(insert OID = 0 ( 405 421 94 1 btreesel btreenpage )); -/* - * hash table float8_ops - */ +/* hash table float8_ops */ DATA(insert OID = 0 ( 405 423 670 1 btreesel btreenpage )); -/* - * hash table int4_ops - */ +/* hash table int4_ops */ DATA(insert OID = 0 ( 405 426 96 1 hashsel hashnpage )); -/* - * hash table oid_ops - */ +/* hash table oid_ops */ DATA(insert OID = 0 ( 405 427 607 1 hashsel hashnpage )); -/* - * hash table float4_ops - */ +/* hash table float4_ops */ DATA(insert OID = 0 ( 405 428 620 1 hashsel hashnpage )); -/* - * hash table char_ops - */ +/* hash table char_ops */ DATA(insert OID = 0 ( 405 429 92 1 hashsel hashnpage )); -/* - * hash table char2_ops - */ +/* hash table char2_ops */ DATA(insert OID = 0 ( 405 406 412 1 hashsel hashnpage )); -/* - * hash table char4_ops - */ +/* hash table char4_ops */ DATA(insert OID = 0 ( 405 407 413 1 hashsel hashnpage )); -/* - * hash table char8_ops - */ +/* hash table char8_ops */ DATA(insert OID = 0 ( 405 408 414 1 hashsel hashnpage )); -/* - * hash table char16_ops - */ +/* hash table char16_ops */ DATA(insert OID = 0 ( 405 430 1267 1 hashsel hashnpage )); -/* - * hash table name_ops - */ +/* hash table name_ops */ DATA(insert OID = 0 ( 405 1181 93 1 hashsel hashnpage )); -/* - * hash table text_ops - */ +/* hash table text_ops */ DATA(insert OID = 0 ( 405 431 98 1 hashsel hashnpage )); - -/* - * hash table bpchar_ops - */ +/* hash table bpchar_ops */ DATA(insert OID = 0 ( 405 1076 1054 1 hashsel hashnpage )); - -/* - * hash table varchar_ops - */ +/* hash table varchar_ops */ DATA(insert OID = 0 ( 405 1077 1062 1 hashsel hashnpage )); - -/* - * hash table date_ops - */ +/* hash table date_ops */ DATA(insert OID = 0 ( 405 1114 1093 1 hashsel hashnpage )); - -/* - * hash table time_ops - */ +/* hash table time_ops */ DATA(insert OID = 0 ( 405 1115 1108 1 hashsel hashnpage )); - +/* hash table datetime_ops */ +DATA(insert OID = 0 ( 405 1312 1320 1 hashsel hashnpage )); +/* hash table timespan_ops */ +DATA(insert OID = 0 ( 405 1313 1330 1 hashsel hashnpage )); #endif /* PG_AMOP_H */ diff --git a/src/include/catalog/pg_amproc.h b/src/include/catalog/pg_amproc.h index f76f9c7e85..e1353fbcd5 100644 --- a/src/include/catalog/pg_amproc.h +++ b/src/include/catalog/pg_amproc.h @@ -9,7 +9,7 @@ * * Copyright (c) 1994, Regents of the University of California * - * $Id: pg_amproc.h,v 1.3 1996/11/13 20:50:52 scrappy Exp $ + * $Id: pg_amproc.h,v 1.4 1997/09/04 18:43:57 thomas Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -61,75 +61,77 @@ typedef FormData_pg_amproc *Form_pg_amproc; * ---------------- */ -DATA(insert OID = 0 (402 422 193 1)); -DATA(insert OID = 0 (402 422 194 2)); -DATA(insert OID = 0 (402 422 195 3)); -DATA(insert OID = 0 (402 433 193 1)); -DATA(insert OID = 0 (402 433 194 2)); -DATA(insert OID = 0 (402 433 196 3)); -DATA(insert OID = 0 (402 434 197 1)); -DATA(insert OID = 0 (402 434 198 2)); -DATA(insert OID = 0 (402 434 199 3)); -DATA(insert OID = 0 (403 421 350 1)); -DATA(insert OID = 0 (403 423 355 1)); -DATA(insert OID = 0 (403 424 353 1)); -DATA(insert OID = 0 (403 425 352 1)); -DATA(insert OID = 0 (403 426 351 1)); -DATA(insert OID = 0 (403 427 356 1)); -DATA(insert OID = 0 (403 428 354 1)); -DATA(insert OID = 0 (403 429 358 1)); -DATA(insert OID = 0 (403 406 689 1)); -DATA(insert OID = 0 (403 407 690 1)); -DATA(insert OID = 0 (403 408 691 1)); -DATA(insert OID = 0 (403 1181 359 1)); -DATA(insert OID = 0 (403 430 1274 1)); -DATA(insert OID = 0 (403 431 360 1)); -DATA(insert OID = 0 (403 432 357 1)); -DATA(insert OID = 0 (403 435 928 1)); -DATA(insert OID = 0 (403 436 948 1)); -DATA(insert OID = 0 (403 437 828 1)); +DATA(insert OID = 0 (402 422 193 1)); +DATA(insert OID = 0 (402 422 194 2)); +DATA(insert OID = 0 (402 422 195 3)); +DATA(insert OID = 0 (402 433 193 1)); +DATA(insert OID = 0 (402 433 194 2)); +DATA(insert OID = 0 (402 433 196 3)); +DATA(insert OID = 0 (402 434 197 1)); +DATA(insert OID = 0 (402 434 198 2)); +DATA(insert OID = 0 (402 434 199 3)); +DATA(insert OID = 0 (403 421 350 1)); +DATA(insert OID = 0 (403 423 355 1)); +DATA(insert OID = 0 (403 424 353 1)); +DATA(insert OID = 0 (403 425 352 1)); +DATA(insert OID = 0 (403 426 351 1)); +DATA(insert OID = 0 (403 427 356 1)); +DATA(insert OID = 0 (403 428 354 1)); +DATA(insert OID = 0 (403 429 358 1)); +DATA(insert OID = 0 (403 406 689 1)); +DATA(insert OID = 0 (403 407 690 1)); +DATA(insert OID = 0 (403 408 691 1)); +DATA(insert OID = 0 (403 1181 359 1)); +DATA(insert OID = 0 (403 430 1274 1)); +DATA(insert OID = 0 (403 431 360 1)); +DATA(insert OID = 0 (403 432 357 1)); +DATA(insert OID = 0 (403 435 928 1)); +DATA(insert OID = 0 (403 436 948 1)); +DATA(insert OID = 0 (403 437 828 1)); DATA(insert OID = 0 (403 1076 1078 1)); DATA(insert OID = 0 (403 1077 1079 1)); DATA(insert OID = 0 (403 1114 1092 1)); DATA(insert OID = 0 (403 1115 1107 1)); +DATA(insert OID = 0 (403 1312 1314 1)); +DATA(insert OID = 0 (403 1313 1315 1)); BKI_BEGIN #ifdef NOBTREE BKI_END -DATA(insert OID = 0 (404 421 350 1)); -DATA(insert OID = 0 (404 423 355 1)); -DATA(insert OID = 0 (404 424 353 1)); -DATA(insert OID = 0 (404 425 352 1)); -DATA(insert OID = 0 (404 426 351 1)); -DATA(insert OID = 0 (404 427 356 1)); -DATA(insert OID = 0 (404 428 354 1)); -DATA(insert OID = 0 (404 429 358 1)); -DATA(insert OID = 0 (404 406 689 1)); -DATA(insert OID = 0 (404 407 690 1)); -DATA(insert OID = 0 (404 408 691 1)); -DATA(insert OID = 0 (404 1181 359 1)); -DATA(insert OID = 0 (404 430 1274 1)); -DATA(insert OID = 0 (404 431 360 1)); -DATA(insert OID = 0 (404 432 357 1)); +DATA(insert OID = 0 (404 421 350 1)); +DATA(insert OID = 0 (404 423 355 1)); +DATA(insert OID = 0 (404 424 353 1)); +DATA(insert OID = 0 (404 425 352 1)); +DATA(insert OID = 0 (404 426 351 1)); +DATA(insert OID = 0 (404 427 356 1)); +DATA(insert OID = 0 (404 428 354 1)); +DATA(insert OID = 0 (404 429 358 1)); +DATA(insert OID = 0 (404 406 689 1)); +DATA(insert OID = 0 (404 407 690 1)); +DATA(insert OID = 0 (404 408 691 1)); +DATA(insert OID = 0 (404 1181 359 1)); +DATA(insert OID = 0 (404 430 1274 1)); +DATA(insert OID = 0 (404 431 360 1)); +DATA(insert OID = 0 (404 432 357 1)); BKI_BEGIN #endif /* NOBTREE */ BKI_END -DATA(insert OID = 0 (405 421 449 1)); -DATA(insert OID = 0 (405 423 452 1)); -DATA(insert OID = 0 (405 426 450 1)); -DATA(insert OID = 0 (405 427 453 1)); -DATA(insert OID = 0 (405 428 451 1)); -DATA(insert OID = 0 (405 429 454 1)); -DATA(insert OID = 0 (405 406 692 1)); -DATA(insert OID = 0 (405 407 693 1)); -DATA(insert OID = 0 (405 408 694 1)); -DATA(insert OID = 0 (405 1181 455 1)); -DATA(insert OID = 0 (405 430 1281 1)); -DATA(insert OID = 0 (405 431 456 1)); +DATA(insert OID = 0 (405 421 449 1)); +DATA(insert OID = 0 (405 423 452 1)); +DATA(insert OID = 0 (405 426 450 1)); +DATA(insert OID = 0 (405 427 453 1)); +DATA(insert OID = 0 (405 428 451 1)); +DATA(insert OID = 0 (405 429 454 1)); +DATA(insert OID = 0 (405 406 692 1)); +DATA(insert OID = 0 (405 407 693 1)); +DATA(insert OID = 0 (405 408 694 1)); +DATA(insert OID = 0 (405 1181 455 1)); +DATA(insert OID = 0 (405 430 1281 1)); +DATA(insert OID = 0 (405 431 456 1)); DATA(insert OID = 0 (405 1076 1080 1)); DATA(insert OID = 0 (405 1077 1081 1)); -DATA(insert OID = 0 (405 1114 450 1)); -DATA(insert OID = 0 (405 1115 694 1)); +DATA(insert OID = 0 (405 1114 450 1)); +DATA(insert OID = 0 (405 1115 694 1)); #endif /* PG_AMPROC_H */ diff --git a/src/include/catalog/pg_opclass.h b/src/include/catalog/pg_opclass.h index ca761c47b2..ba267a10cc 100644 --- a/src/include/catalog/pg_opclass.h +++ b/src/include/catalog/pg_opclass.h @@ -7,7 +7,7 @@ * * Copyright (c) 1994, Regents of the University of California * - * $Id: pg_opclass.h,v 1.2 1996/10/31 09:47:48 scrappy Exp $ + * $Id: pg_opclass.h,v 1.3 1997/09/04 18:43:58 thomas Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -61,32 +61,34 @@ typedef FormData_pg_opclass *Form_pg_opclass; * (see the README in this directory), so just put zeros * in, which are invalid OID's anyway. --djm */ -DATA(insert OID = 406 ( char2_ops 409 )); -DATA(insert OID = 407 ( char4_ops 410 )); -DATA(insert OID = 408 ( char8_ops 411 )); -/* OID 409 is already used in table pg_type--this one should be unused */ -DATA(insert OID = 1181 ( name_ops 19 )); -DATA(insert OID = 421 ( int2_ops 21 )); -DATA(insert OID = 422 ( box_ops 603 )); -DATA(insert OID = 423 ( float8_ops 701 )); -DATA(insert OID = 424 ( int24_ops 0 )); -DATA(insert OID = 425 ( int42_ops 0 )); -DATA(insert OID = 426 ( int4_ops 23 )); +DATA(insert OID = 406 ( char2_ops 409 )); +DATA(insert OID = 407 ( char4_ops 410 )); +DATA(insert OID = 408 ( char8_ops 411 )); +DATA(insert OID = 1181 ( name_ops 19 )); +DATA(insert OID = 421 ( int2_ops 21 )); +DATA(insert OID = 422 ( box_ops 603 )); +DATA(insert OID = 423 ( float8_ops 701 )); +DATA(insert OID = 424 ( int24_ops 0 )); +DATA(insert OID = 425 ( int42_ops 0 )); +DATA(insert OID = 426 ( int4_ops 23 )); #define INT4_OPS_OID 426 -DATA(insert OID = 427 ( oid_ops 26 )); -DATA(insert OID = 428 ( float4_ops 700 )); -DATA(insert OID = 429 ( char_ops 18 )); -DATA(insert OID = 430 ( char16_ops 20 )); -DATA(insert OID = 431 ( text_ops 25 )); -DATA(insert OID = 432 ( abstime_ops 702 )); -DATA(insert OID = 433 ( bigbox_ops 0 )); -DATA(insert OID = 434 ( poly_ops 604 )); -DATA(insert OID = 435 ( oidint4_ops 910 )); -DATA(insert OID = 436 ( oidname_ops 911 )); -DATA(insert OID = 437 ( oidint2_ops 810 )); +DATA(insert OID = 427 ( oid_ops 26 )); +DATA(insert OID = 428 ( float4_ops 700 )); +DATA(insert OID = 429 ( char_ops 18 )); +DATA(insert OID = 430 ( char16_ops 20 )); +DATA(insert OID = 431 ( text_ops 25 )); +DATA(insert OID = 432 ( abstime_ops 702 )); +DATA(insert OID = 433 ( bigbox_ops 0 )); +DATA(insert OID = 434 ( poly_ops 604 )); +DATA(insert OID = 435 ( oidint4_ops 910 )); +DATA(insert OID = 436 ( oidname_ops 911 )); +DATA(insert OID = 437 ( oidint2_ops 810 )); +DATA(insert OID = 714 ( circle_ops 718 )); DATA(insert OID = 1076 ( bpchar_ops 1042 )); DATA(insert OID = 1077 ( varchar_ops 1043 )); DATA(insert OID = 1114 ( date_ops 1082 )); DATA(insert OID = 1115 ( time_ops 1083 )); +DATA(insert OID = 1312 ( datetime_ops 1184 )); +DATA(insert OID = 1313 ( timespan_ops 1186 )); #endif /* PG_OPCLASS_H */ diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 4cec072d2b..0663d78a5f 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -6,7 +6,7 @@ * * Copyright (c) 1994, Regents of the University of California * - * $Id: pg_proc.h,v 1.26 1997/09/01 05:46:09 thomas Exp $ + * $Id: pg_proc.h,v 1.27 1997/09/04 18:43:59 thomas Exp $ * * NOTES * The script catalog/genbki.sh reads this file and generates .bki @@ -888,6 +888,8 @@ DATA(insert OID = 1309 ( timestampgt PGUID 11 f t f 2 f 16 "1296 1296" 100 DATA(insert OID = 1310 ( timestample PGUID 11 f t f 2 f 16 "1296 1296" 100 0 0 100 foo bar )); DATA(insert OID = 1311 ( timestampge PGUID 11 f t f 2 f 16 "1296 1296" 100 0 0 100 foo bar )); /* reserve OIDs 1312-1319 for additional date/time conversion routines! tgl 97/04/01 */ +DATA(insert OID = 1314 ( datetime_cmp PGUID 11 f t f 2 f 23 "1184 1184" 100 0 0 100 foo bar )); +DATA(insert OID = 1315 ( timespan_cmp PGUID 11 f t f 2 f 23 "1186 1186" 100 0 0 100 foo bar )); DATA(insert OID = 1339 ( date_zone PGUID 14 f t f 2 f 25 "25 1184" 100 0 0 100 "select datetime_zone($1, $2)" - )); DATA(insert OID = 1340 ( text PGUID 14 f t f 1 f 25 "1184" 100 0 0 100 "select datetime_text($1)" - )); -- 2.40.0