Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita9e0839

Browse files
committed
Create crosstype comparison operators for date vs. timestamp and date
vs. timestamptz. This allows use of indexes for expressions like datecol >= date 'today' - interval '1 month'which were formerly not indexable without casting the righthand sidedown from timestamp to date.
1 parent2e5fe48 commita9e0839

File tree

9 files changed

+601
-100
lines changed

9 files changed

+601
-100
lines changed

‎src/backend/utils/adt/date.c

Lines changed: 417 additions & 68 deletions
Large diffs are not rendered by default.

‎src/backend/utils/adt/timestamp.c

Lines changed: 10 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.98 2003/12/25 03:36:23 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.99 2004/02/14 20:16:17 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1296,7 +1296,7 @@ SetEpochTimestamp(void)
12961296
*
12971297
*collate invalid timestamp at the end
12981298
*/
1299-
staticint
1299+
int
13001300
timestamp_cmp_internal(Timestampdt1,Timestampdt2)
13011301
{
13021302
#ifdefHAVE_INT64_TIMESTAMP
@@ -1703,7 +1703,7 @@ timestamp_mi(PG_FUNCTION_ARGS)
17031703
}
17041704

17051705

1706-
/*timestamp_pl_span()
1706+
/*timestamp_pl_interval()
17071707
* Add a interval to a timestamp data type.
17081708
* Note that interval has provisions for qualitative year/month
17091709
*units, so try to do the right thing with them.
@@ -1713,7 +1713,7 @@ timestamp_mi(PG_FUNCTION_ARGS)
17131713
* Lastly, add in the "quantitative time".
17141714
*/
17151715
Datum
1716-
timestamp_pl_span(PG_FUNCTION_ARGS)
1716+
timestamp_pl_interval(PG_FUNCTION_ARGS)
17171717
{
17181718
Timestamptimestamp=PG_GETARG_TIMESTAMP(0);
17191719
Interval*span=PG_GETARG_INTERVAL_P(1);
@@ -1764,7 +1764,7 @@ timestamp_pl_span(PG_FUNCTION_ARGS)
17641764
}
17651765

17661766
Datum
1767-
timestamp_mi_span(PG_FUNCTION_ARGS)
1767+
timestamp_mi_interval(PG_FUNCTION_ARGS)
17681768
{
17691769
Timestamptimestamp=PG_GETARG_TIMESTAMP(0);
17701770
Interval*span=PG_GETARG_INTERVAL_P(1);
@@ -1773,13 +1773,13 @@ timestamp_mi_span(PG_FUNCTION_ARGS)
17731773
tspan.month=-span->month;
17741774
tspan.time=-span->time;
17751775

1776-
returnDirectFunctionCall2(timestamp_pl_span,
1776+
returnDirectFunctionCall2(timestamp_pl_interval,
17771777
TimestampGetDatum(timestamp),
17781778
PointerGetDatum(&tspan));
17791779
}
17801780

17811781

1782-
/*timestamptz_pl_span()
1782+
/*timestamptz_pl_interval()
17831783
* Add a interval to a timestamp with time zone data type.
17841784
* Note that interval has provisions for qualitative year/month
17851785
*units, so try to do the right thing with them.
@@ -1789,7 +1789,7 @@ timestamp_mi_span(PG_FUNCTION_ARGS)
17891789
* Lastly, add in the "quantitative time".
17901790
*/
17911791
Datum
1792-
timestamptz_pl_span(PG_FUNCTION_ARGS)
1792+
timestamptz_pl_interval(PG_FUNCTION_ARGS)
17931793
{
17941794
TimestampTztimestamp=PG_GETARG_TIMESTAMPTZ(0);
17951795
Interval*span=PG_GETARG_INTERVAL_P(1);
@@ -1844,7 +1844,7 @@ timestamptz_pl_span(PG_FUNCTION_ARGS)
18441844
}
18451845

18461846
Datum
1847-
timestamptz_mi_span(PG_FUNCTION_ARGS)
1847+
timestamptz_mi_interval(PG_FUNCTION_ARGS)
18481848
{
18491849
TimestampTztimestamp=PG_GETARG_TIMESTAMPTZ(0);
18501850
Interval*span=PG_GETARG_INTERVAL_P(1);
@@ -1853,7 +1853,7 @@ timestamptz_mi_span(PG_FUNCTION_ARGS)
18531853
tspan.month=-span->month;
18541854
tspan.time=-span->time;
18551855

1856-
returnDirectFunctionCall2(timestamptz_pl_span,
1856+
returnDirectFunctionCall2(timestamptz_pl_interval,
18571857
TimestampGetDatum(timestamp),
18581858
PointerGetDatum(&tspan));
18591859
}

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.218 2004/02/12 23:41:03 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.219 2004/02/14 20:16:17 tgl Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO200402121
56+
#defineCATALOG_VERSION_NO200402141
5757

5858
#endif

‎src/include/catalog/pg_amop.h

Lines changed: 25 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -23,7 +23,7 @@
2323
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
2424
* Portions Copyright (c) 1994, Regents of the University of California
2525
*
26-
* $PostgreSQL: pgsql/src/include/catalog/pg_amop.h,v 1.57 2003/11/29 22:40:58 pgsql Exp $
26+
* $PostgreSQL: pgsql/src/include/catalog/pg_amop.h,v 1.58 2004/02/14 20:16:17 tgl Exp $
2727
*
2828
* NOTES
2929
* the genbki.sh script reads this file and generates .bki
@@ -301,6 +301,18 @@ DATA(insert ( 434 0 2 f 1096 ));
301301
DATA(insert (43403f1093 ));
302302
DATA(insert (43404f1098 ));
303303
DATA(insert (43405f1097 ));
304+
/* crosstype operators vs timestamp */
305+
DATA(insert (43411141f2345 ));
306+
DATA(insert (43411142f2346 ));
307+
DATA(insert (43411143f2347 ));
308+
DATA(insert (43411144f2348 ));
309+
DATA(insert (43411145f2349 ));
310+
/* crosstype operators vs timestamptz */
311+
DATA(insert (43411841f2358 ));
312+
DATA(insert (43411842f2359 ));
313+
DATA(insert (43411843f2360 ));
314+
DATA(insert (43411844f2361 ));
315+
DATA(insert (43411845f2362 ));
304316

305317
/*
306318
*btree time_ops
@@ -331,6 +343,12 @@ DATA(insert (2039 0 2 f 2063 ));
331343
DATA(insert (203903f2060 ));
332344
DATA(insert (203904f2065 ));
333345
DATA(insert (203905f2064 ));
346+
/* crosstype operators vs date */
347+
DATA(insert (203910821f2371 ));
348+
DATA(insert (203910822f2372 ));
349+
DATA(insert (203910823f2373 ));
350+
DATA(insert (203910824f2374 ));
351+
DATA(insert (203910825f2375 ));
334352

335353
/*
336354
*btree timestamptz_ops
@@ -341,6 +359,12 @@ DATA(insert (1998 0 2 f 1323 ));
341359
DATA(insert (199803f1320 ));
342360
DATA(insert (199804f1325 ));
343361
DATA(insert (199805f1324 ));
362+
/* crosstype operators vs date */
363+
DATA(insert (199810821f2384 ));
364+
DATA(insert (199810822f2385 ));
365+
DATA(insert (199810823f2386 ));
366+
DATA(insert (199810824f2387 ));
367+
DATA(insert (199810825f2388 ));
344368

345369
/*
346370
*btree interval_ops

‎src/include/catalog/pg_amproc.h

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -19,7 +19,7 @@
1919
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
2020
* Portions Copyright (c) 1994, Regents of the University of California
2121
*
22-
* $PostgreSQL: pgsql/src/include/catalog/pg_amproc.h,v 1.46 2003/11/29 22:40:58 pgsql Exp $
22+
* $PostgreSQL: pgsql/src/include/catalog/pg_amproc.h,v 1.47 2004/02/14 20:16:17 tgl Exp $
2323
*
2424
* NOTES
2525
* the genbki.sh script reads this file and generates .bki
@@ -91,6 +91,8 @@ DATA(insert ( 428 0 1 1954 ));
9191
DATA(insert (42901358 ));
9292
DATA(insert (43201926 ));
9393
DATA(insert (434011092 ));
94+
DATA(insert (434111412344 ));
95+
DATA(insert (434118412357 ));
9496
DATA(insert (197001354 ));
9597
DATA(insert (197070112194 ));
9698
DATA(insert (197201355 ));
@@ -114,10 +116,12 @@ DATA(insert (1991 0 1404 ));
114116
DATA(insert (199401360 ));
115117
DATA(insert (1996011107 ));
116118
DATA(insert (1998011314 ));
119+
DATA(insert (1998108212383 ));
117120
DATA(insert (2000011358 ));
118121
DATA(insert (2002011672 ));
119122
DATA(insert (200301360 ));
120123
DATA(insert (2039012045 ));
124+
DATA(insert (2039108212370 ));
121125
DATA(insert (2095012166 ));
122126
DATA(insert (2096012166 ));
123127
DATA(insert (2097012180 ));

‎src/include/catalog/pg_operator.h

Lines changed: 34 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
99
* Portions Copyright (c) 1994, Regents of the University of California
1010
*
11-
* $PostgreSQL: pgsql/src/include/catalog/pg_operator.h,v 1.123 2003/12/01 21:52:37 momjian Exp $
11+
* $PostgreSQL: pgsql/src/include/catalog/pg_operator.h,v 1.124 2004/02/14 20:16:17 tgl Exp $
1212
*
1313
* NOTES
1414
* the genbki.sh script reads this file and generates .bki
@@ -535,9 +535,9 @@ DATA(insert OID = 1322 ( "<" PGNSP PGUID b f 1184 1184 16 1324 1325 0 0 0 0
535535
DATA(insertOID=1323 ("<="PGNSPPGUIDbf1184118416132513240000timestamptz_lescalarltselscalarltjoinsel ));
536536
DATA(insertOID=1324 (">"PGNSPPGUIDbf1184118416132213230000timestamptz_gtscalargtselscalargtjoinsel ));
537537
DATA(insertOID=1325 (">="PGNSPPGUIDbf1184118416132313220000timestamptz_gescalargtselscalargtjoinsel ));
538-
DATA(insertOID=1327 ("+"PGNSPPGUIDbf118411861184000000timestamptz_pl_span-- ));
538+
DATA(insertOID=1327 ("+"PGNSPPGUIDbf118411861184000000timestamptz_pl_interval-- ));
539539
DATA(insertOID=1328 ("-"PGNSPPGUIDbf118411841186000000timestamptz_mi-- ));
540-
DATA(insertOID=1329 ("-"PGNSPPGUIDbf118411861184000000timestamptz_mi_span-- ));
540+
DATA(insertOID=1329 ("-"PGNSPPGUIDbf118411861184000000timestamptz_mi_interval-- ));
541541

542542
/* interval operators */
543543
DATA(insertOID=1330 ("="PGNSPPGUIDbt1186118616133013311332133213321334interval_eqeqseleqjoinsel ));
@@ -791,9 +791,9 @@ DATA(insert OID = 2062 ( "<" PGNSP PGUID b f 1114 1114 16 2064 2065 0 0 0 0
791791
DATA(insertOID=2063 ("<="PGNSPPGUIDbf1114111416206520640000timestamp_lescalarltselscalarltjoinsel ));
792792
DATA(insertOID=2064 (">"PGNSPPGUIDbf1114111416206220630000timestamp_gtscalargtselscalargtjoinsel ));
793793
DATA(insertOID=2065 (">="PGNSPPGUIDbf1114111416206320620000timestamp_gescalargtselscalargtjoinsel ));
794-
DATA(insertOID=2066 ("+"PGNSPPGUIDbf111411861114000000timestamp_pl_span-- ));
794+
DATA(insertOID=2066 ("+"PGNSPPGUIDbf111411861114000000timestamp_pl_interval-- ));
795795
DATA(insertOID=2067 ("-"PGNSPPGUIDbf111411141186000000timestamp_mi-- ));
796-
DATA(insertOID=2068 ("-"PGNSPPGUIDbf111411861114000000timestamp_mi_span-- ));
796+
DATA(insertOID=2068 ("-"PGNSPPGUIDbf111411861114000000timestamp_mi_interval-- ));
797797

798798
/* character-by-character (not collation order) comparison operators for character types */
799799

@@ -818,6 +818,35 @@ DATA(insert OID = 2335 ( "~>=~" PGNSP PGUID b f 19 19 16 2333 2332 0 0 0 0 name_
818818
DATA(insertOID=2336 ("~>~"PGNSPPGUIDbf191916233223330000name_pattern_gtscalargtselscalargtjoinsel ));
819819
DATA(insertOID=2337 ("~<>~"PGNSPPGUIDbf191916233723340000name_pattern_neneqselneqjoinsel ));
820820

821+
/* crosstype operations for date vs. timestamp and timestamptz */
822+
823+
DATA(insertOID=2345 ("<"PGNSPPGUIDbf1082111416237523480000date_lt_timestampscalarltselscalarltjoinsel ));
824+
DATA(insertOID=2346 ("<="PGNSPPGUIDbf1082111416237423490000date_le_timestampscalarltselscalarltjoinsel ));
825+
DATA(insertOID=2347 ("="PGNSPPGUIDbf1082111416237323500000date_eq_timestampeqseleqjoinsel ));
826+
DATA(insertOID=2348 (">="PGNSPPGUIDbf1082111416237223450000date_ge_timestampscalargtselscalargtjoinsel ));
827+
DATA(insertOID=2349 (">"PGNSPPGUIDbf1082111416237123460000date_gt_timestampscalargtselscalargtjoinsel ));
828+
DATA(insertOID=2350 ("<>"PGNSPPGUIDbf1082111416237623470000date_ne_timestampneqselneqjoinsel ));
829+
830+
DATA(insertOID=2358 ("<"PGNSPPGUIDbf1082118416238823610000date_lt_timestamptzscalarltselscalarltjoinsel ));
831+
DATA(insertOID=2359 ("<="PGNSPPGUIDbf1082118416238723620000date_le_timestamptzscalarltselscalarltjoinsel ));
832+
DATA(insertOID=2360 ("="PGNSPPGUIDbf1082118416238623630000date_eq_timestamptzeqseleqjoinsel ));
833+
DATA(insertOID=2361 (">="PGNSPPGUIDbf1082118416238523580000date_ge_timestamptzscalargtselscalargtjoinsel ));
834+
DATA(insertOID=2362 (">"PGNSPPGUIDbf1082118416238423590000date_gt_timestamptzscalargtselscalargtjoinsel ));
835+
DATA(insertOID=2363 ("<>"PGNSPPGUIDbf1082118416238923600000date_ne_timestamptzneqselneqjoinsel ));
836+
837+
DATA(insertOID=2371 ("<"PGNSPPGUIDbf1114108216234923740000timestamp_lt_datescalarltselscalarltjoinsel ));
838+
DATA(insertOID=2372 ("<="PGNSPPGUIDbf1114108216234823750000timestamp_le_datescalarltselscalarltjoinsel ));
839+
DATA(insertOID=2373 ("="PGNSPPGUIDbf1114108216234723760000timestamp_eq_dateeqseleqjoinsel ));
840+
DATA(insertOID=2374 (">="PGNSPPGUIDbf1114108216234623710000timestamp_ge_datescalargtselscalargtjoinsel ));
841+
DATA(insertOID=2375 (">"PGNSPPGUIDbf1114108216234523720000timestamp_gt_datescalargtselscalargtjoinsel ));
842+
DATA(insertOID=2376 ("<>"PGNSPPGUIDbf1114108216235023730000timestamp_ne_dateneqselneqjoinsel ));
843+
844+
DATA(insertOID=2384 ("<"PGNSPPGUIDbf1184108216236223870000timestamptz_lt_datescalarltselscalarltjoinsel ));
845+
DATA(insertOID=2385 ("<="PGNSPPGUIDbf1184108216236123880000timestamptz_le_datescalarltselscalarltjoinsel ));
846+
DATA(insertOID=2386 ("="PGNSPPGUIDbf1184108216236023890000timestamptz_eq_dateeqseleqjoinsel ));
847+
DATA(insertOID=2387 (">="PGNSPPGUIDbf1184108216235923840000timestamptz_ge_datescalargtselscalargtjoinsel ));
848+
DATA(insertOID=2388 (">"PGNSPPGUIDbf1184108216235823850000timestamptz_gt_datescalargtselscalargtjoinsel ));
849+
DATA(insertOID=2389 ("<>"PGNSPPGUIDbf1184108216236323860000timestamptz_ne_dateneqselneqjoinsel ));
821850

822851

823852
/*

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp