Add comparision routines and catalog entries to support indices on

 datetime and timespan.

5 files changed, 219 insertions, 134 deletions

diff --git a/src/backend/utils/adt/dt.c b/src/backend/utils/adt/dt.c
index 5001f32d080..a141fa042ec 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 63ee8988a61..8385db08462 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
@@ -104,6 +104,19 @@ 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 f76f9c7e858..e1353fbcd5d 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 ca761c47b2f..ba267a10ccc 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 4cec072d2b7..0663d78a5ff 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)" - ));