Skip to content

Commit e34082e

Browse files
tglsfdctglsfdc
committed
Add missing operators of the form interval-plus-datetime, as required for
better SQL compliance in this area, per recent discussion. Mark related operators as commutators where possible. (The system doesn't actually care about commutator marking for operators not returning boolean, at the moment, but this seems forward-thinking and besides it made it easier to verify that we hadn't missed any.) Also, remove interval-minus-time and interval-minus-timetz operators. I'm not sure how these got in, but they are nonstandard and had very obviously broken behavior. (minus is not commutative in anyone's book.) I doubt anyone had ever used 'em, because we'd surely have gotten a bug report about it if so.
1 parent 0b89d26 commit e34082e

File tree

8 files changed

+48
-93
lines changed

8 files changed

+48
-93
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 12 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.212 2004/07/02 18:59:20 joe Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.213 2004/07/02 22:49:45 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -4689,7 +4689,10 @@ substring('foobar' from 'o(.)b') <lineannotation>o</lineannotation>
46894689
All the functions and operators described below that take <type>time</type> or <type>timestamp</type>
46904690
inputs actually come in two variants: one that takes <type>time with time zone</type> or <type>timestamp
46914691
with time zone</type>, and one that takes <type>time without time zone</type> or <type>timestamp without time zone</type>.
4692-
For brevity, these variants are not shown separately.
4692+
For brevity, these variants are not shown separately. Also, the
4693+
<literal>+</> and <literal>*</> operators come in commutative pairs (for
4694+
example both date + integer and integer + date); we show only one of each
4695+
such pair.
46934696
</para>
46944697

46954698
<table id="operators-datetime-table">
@@ -4723,12 +4726,6 @@ substring('foobar' from 'o(.)b') <lineannotation>o</lineannotation>
47234726
<entry><literal>timestamp '2001-09-28 03:00'</literal></entry>
47244727
</row>
47254728

4726-
<row>
4727-
<entry> <literal>+</literal> </entry>
4728-
<entry><literal>time '03:00' + date '2001-09-28'</literal></entry>
4729-
<entry><literal>timestamp '2001-09-28 03:00'</literal></entry>
4730-
</row>
4731-
47324729
<row>
47334730
<entry> <literal>+</literal> </entry>
47344731
<entry><literal>interval '1 day' + interval '1 hour'</literal></entry>
@@ -4747,12 +4744,6 @@ substring('foobar' from 'o(.)b') <lineannotation>o</lineannotation>
47474744
<entry><literal>time '04:00'</literal></entry>
47484745
</row>
47494746

4750-
<row>
4751-
<entry> <literal>+</literal> </entry>
4752-
<entry><literal>interval '3 hours' + time '01:00'</literal></entry>
4753-
<entry><literal>time '04:00'</literal></entry>
4754-
</row>
4755-
47564747
<row>
47574748
<entry> <literal>-</literal> </entry>
47584749
<entry><literal>- interval '23 hours'</literal></entry>
@@ -4801,24 +4792,12 @@ substring('foobar' from 'o(.)b') <lineannotation>o</lineannotation>
48014792
<entry><literal>interval '23:00'</literal></entry>
48024793
</row>
48034794

4804-
<row>
4805-
<entry> <literal>-</literal> </entry>
4806-
<entry><literal>interval '2 hours' - time '05:00'</literal></entry>
4807-
<entry><literal>time '03:00'</literal></entry>
4808-
</row>
4809-
48104795
<row>
48114796
<entry> <literal>-</literal> </entry>
48124797
<entry><literal>timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'</literal></entry>
48134798
<entry><literal>interval '1 day 15:00'</literal></entry>
48144799
</row>
48154800

4816-
<row>
4817-
<entry> <literal>*</literal> </entry>
4818-
<entry><literal>double precision '3.5' * interval '1 hour'</literal></entry>
4819-
<entry><literal>interval '03:30'</literal></entry>
4820-
</row>
4821-
48224801
<row>
48234802
<entry> <literal>*</literal> </entry>
48244803
<entry><literal>interval '1 hour' * double precision '3.5'</literal></entry>
@@ -7332,7 +7311,7 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
73327311
<row>
73337312
<entry><literal><function>pg_tablespace_databases</function>(<parameter>tablespace_oid</parameter>)</literal></entry>
73347313
<entry><type>setof oid</type></entry>
7335-
<entry>get set of database oids that have objects in the tablespace</entry>
7314+
<entry>get set of database OIDs that have objects in the tablespace</entry>
73367315
</row>
73377316
</tbody>
73387317
</tgroup>
@@ -7373,12 +7352,13 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
73737352

73747353
<para>
73757354
<function>pg_tablespace_databases</function> allows usage examination of a
7376-
tablespace. It will return a set of database oids, that have objects
7355+
tablespace. It will return a set of OIDs of databases that have objects
73777356
stored in the tablespace. If this function returns any row, the
7378-
tablespace is assumed not to be empty and cannot be dropped. To
7379-
display the actual objects populating the tablespace, you will need
7380-
to connect to the databases returned by
7381-
<function>pg_tablespace_databases</function> to query pg_class.
7357+
tablespace is not empty and cannot be dropped. To
7358+
display the specific objects populating the tablespace, you will need
7359+
to connect to the databases identified by
7360+
<function>pg_tablespace_databases</function> and query their
7361+
<structname>pg_class</> catalogs.
73827362
</para>
73837363

73847364
<indexterm zone="functions-misc">

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

Lines changed: 1 addition & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/date.c,v 1.99 2004/06/03 02:08:04 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/date.c,v 1.100 2004/07/02 22:49:46 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1531,18 +1531,6 @@ time_mi_interval(PG_FUNCTION_ARGS)
15311531
PG_RETURN_TIMEADT(result);
15321532
}
15331533

1534-
/* interval_pl_time()
1535-
* Add time to interval.
1536-
*/
1537-
Datum
1538-
interval_pl_time(PG_FUNCTION_ARGS)
1539-
{
1540-
Datum span = PG_GETARG_DATUM(0);
1541-
Datum time = PG_GETARG_DATUM(1);
1542-
1543-
return DirectFunctionCall2(time_pl_interval, time, span);
1544-
}
1545-
15461534

15471535
/* time_text()
15481536
* Convert time to text data type.

‎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.242 2004/07/02 18:59:24 joe Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.243 2004/07/02 22:49:48 tgl Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 200407021
56+
#define CATALOG_VERSION_NO 200407022
5757

5858
#endif

‎src/include/catalog/pg_operator.h

Lines changed: 21 additions & 16 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.125 2004/03/22 01:38:17 tgl Exp $
11+
* $PostgreSQL: pgsql/src/include/catalog/pg_operator.h,v 1.126 2004/07/02 22:49:48 tgl Exp $
1212
*
1313
* NOTES
1414
* the genbki.sh script reads this file and generates .bki
@@ -451,7 +451,7 @@ DATA(insert OID = 1074 ( "<=" PGNSP PGUID b f 2277 2277 16 1075 1073 0 0 0
451451
DATA(insert OID = 1075 ( ">=" PGNSP PGUID b f 2277 2277 16 1074 1072 0 0 0 0 array_ge scalargtsel scalargtjoinsel ));
452452

453453
/* date operators */
454-
DATA(insert OID = 1076 ( "+" PGNSP PGUID b f 1082 1186 1114 0 0 0 0 0 0 date_pl_interval - - ));
454+
DATA(insert OID = 1076 ( "+" PGNSP PGUID b f 1082 1186 1114 2551 0 0 0 0 0 date_pl_interval - - ));
455455
DATA(insert OID = 1077 ( "-" PGNSP PGUID b f 1082 1186 1114 0 0 0 0 0 0 date_mi_interval - - ));
456456
DATA(insert OID = 1093 ( "=" PGNSP PGUID b t 1082 1082 16 1093 1094 1095 1095 1095 1097 date_eq eqsel eqjoinsel ));
457457
DATA(insert OID = 1094 ( "<>" PGNSP PGUID b f 1082 1082 16 1094 1093 0 0 0 0 date_ne neqsel neqjoinsel ));
@@ -460,7 +460,7 @@ DATA(insert OID = 1096 ( "<=" PGNSP PGUID b f 1082 1082 16 1098 1097 0 0 0
460460
DATA(insert OID = 1097 ( ">" PGNSP PGUID b f 1082 1082 16 1095 1096 0 0 0 0 date_gt scalargtsel scalargtjoinsel ));
461461
DATA(insert OID = 1098 ( ">=" PGNSP PGUID b f 1082 1082 16 1096 1095 0 0 0 0 date_ge scalargtsel scalargtjoinsel ));
462462
DATA(insert OID = 1099 ( "-" PGNSP PGUID b f 1082 1082 23 0 0 0 0 0 0 date_mi - - ));
463-
DATA(insert OID = 1100 ( "+" PGNSP PGUID b f 1082 23 1082 0 0 0 0 0 0 date_pli - - ));
463+
DATA(insert OID = 1100 ( "+" PGNSP PGUID b f 1082 23 1082 2555 0 0 0 0 0 date_pli - - ));
464464
DATA(insert OID = 1101 ( "-" PGNSP PGUID b f 1082 23 1082 0 0 0 0 0 0 date_mii - - ));
465465

466466
/* time operators */
@@ -470,10 +470,8 @@ DATA(insert OID = 1110 ( "<" PGNSP PGUID b f 1083 1083 16 1112 1113 0 0 0
470470
DATA(insert OID = 1111 ( "<=" PGNSP PGUID b f 1083 1083 16 1113 1112 0 0 0 0 time_le scalarltsel scalarltjoinsel ));
471471
DATA(insert OID = 1112 ( ">" PGNSP PGUID b f 1083 1083 16 1110 1111 0 0 0 0 time_gt scalargtsel scalargtjoinsel ));
472472
DATA(insert OID = 1113 ( ">=" PGNSP PGUID b f 1083 1083 16 1111 1110 0 0 0 0 time_ge scalargtsel scalargtjoinsel ));
473-
DATA(insert OID = 1269 ( "-" PGNSP PGUID b f 1186 1083 1083 0 0 0 0 0 0 interval_mi_time - - ));
474473

475474
/* timetz operators */
476-
DATA(insert OID = 1295 ( "-" PGNSP PGUID b f 1186 1266 1266 0 0 0 0 0 0 interval_mi_timetz - - ));
477475
DATA(insert OID = 1550 ( "=" PGNSP PGUID b t 1266 1266 16 1550 1551 1552 1552 1552 1554 timetz_eq eqsel eqjoinsel ));
478476
DATA(insert OID = 1551 ( "<>" PGNSP PGUID b f 1266 1266 16 1551 1550 0 0 0 0 timetz_ne neqsel neqjoinsel ));
479477
DATA(insert OID = 1552 ( "<" PGNSP PGUID b f 1266 1266 16 1554 1555 0 0 0 0 timetz_lt scalarltsel scalarltjoinsel ));
@@ -535,7 +533,7 @@ DATA(insert OID = 1322 ( "<" PGNSP PGUID b f 1184 1184 16 1324 1325 0 0 0 0
535533
DATA(insert OID = 1323 ( "<=" PGNSP PGUID b f 1184 1184 16 1325 1324 0 0 0 0 timestamptz_le scalarltsel scalarltjoinsel ));
536534
DATA(insert OID = 1324 ( ">" PGNSP PGUID b f 1184 1184 16 1322 1323 0 0 0 0 timestamptz_gt scalargtsel scalargtjoinsel ));
537535
DATA(insert OID = 1325 ( ">=" PGNSP PGUID b f 1184 1184 16 1323 1322 0 0 0 0 timestamptz_ge scalargtsel scalargtjoinsel ));
538-
DATA(insert OID = 1327 ( "+" PGNSP PGUID b f 1184 1186 1184 0 0 0 0 0 0 timestamptz_pl_interval - - ));
536+
DATA(insert OID = 1327 ( "+" PGNSP PGUID b f 1184 1186 1184 2554 0 0 0 0 0 timestamptz_pl_interval - - ));
539537
DATA(insert OID = 1328 ( "-" PGNSP PGUID b f 1184 1184 1186 0 0 0 0 0 0 timestamptz_mi - - ));
540538
DATA(insert OID = 1329 ( "-" PGNSP PGUID b f 1184 1186 1184 0 0 0 0 0 0 timestamptz_mi_interval - - ));
541539

@@ -551,10 +549,10 @@ DATA(insert OID = 1336 ( "-" PGNSP PGUID l f 0 1186 1186 0 0 0 0 0 0 inte
551549
DATA(insert OID = 1337 ( "+" PGNSP PGUID b f 1186 1186 1186 1337 0 0 0 0 0 interval_pl - - ));
552550
DATA(insert OID = 1338 ( "-" PGNSP PGUID b f 1186 1186 1186 0 0 0 0 0 0 interval_mi - - ));
553551

554-
DATA(insert OID = 1360 ( "+" PGNSP PGUID b f 1082 1083 1114 0 0 0 0 0 0 datetime_pl - - ));
555-
DATA(insert OID = 1361 ( "+" PGNSP PGUID b f 1082 1266 1184 0 0 0 0 0 0 datetimetz_pl - - ));
556-
DATA(insert OID = 1363 ( "+" PGNSP PGUID b f 1083 1082 1114 0 0 0 0 0 0 timedate_pl - - ));
557-
DATA(insert OID = 1366 ( "+" PGNSP PGUID b f 1266 1082 1184 0 0 0 0 0 0 timetzdate_pl - - ));
552+
DATA(insert OID = 1360 ( "+" PGNSP PGUID b f 1082 1083 1114 1363 0 0 0 0 0 datetime_pl - - ));
553+
DATA(insert OID = 1361 ( "+" PGNSP PGUID b f 1082 1266 1184 1366 0 0 0 0 0 datetimetz_pl - - ));
554+
DATA(insert OID = 1363 ( "+" PGNSP PGUID b f 1083 1082 1114 1360 0 0 0 0 0 timedate_pl - - ));
555+
DATA(insert OID = 1366 ( "+" PGNSP PGUID b f 1266 1082 1184 1361 0 0 0 0 0 timetzdate_pl - - ));
558556

559557
DATA(insert OID = 1399 ( "-" PGNSP PGUID b f 1083 1083 1186 0 0 0 0 0 0 time_mi_time - - ));
560558

@@ -616,8 +614,8 @@ DATA(insert OID = 1567 ( "##" PGNSP PGUID b f 601 603 600 0 0 0 0 0 0 cl
616614
DATA(insert OID = 1568 ( "##" PGNSP PGUID b f 628 603 600 0 0 0 0 0 0 close_lb - - ));
617615
DATA(insert OID = 1577 ( "##" PGNSP PGUID b f 628 601 600 0 0 0 0 0 0 close_ls - - ));
618616
DATA(insert OID = 1578 ( "##" PGNSP PGUID b f 601 601 600 0 0 0 0 0 0 close_lseg - - ));
619-
DATA(insert OID = 1583 ( "*" PGNSP PGUID b f 1186 701 1186 0 0 0 0 0 0 interval_mul - - ));
620-
DATA(insert OID = 1584 ( "*" PGNSP PGUID b f 701 1186 1186 0 0 0 0 0 0 mul_d_interval - - ));
617+
DATA(insert OID = 1583 ( "*" PGNSP PGUID b f 1186 701 1186 1584 0 0 0 0 0 interval_mul - - ));
618+
DATA(insert OID = 1584 ( "*" PGNSP PGUID b f 701 1186 1186 1583 0 0 0 0 0 mul_d_interval - - ));
621619
DATA(insert OID = 1585 ( "/" PGNSP PGUID b f 1186 701 1186 0 0 0 0 0 0 interval_div - - ));
622620

623621
DATA(insert OID = 1586 ( "<>" PGNSP PGUID b f 601 601 16 1586 1535 0 0 0 0 lseg_ne neqsel neqjoinsel ));
@@ -716,9 +714,9 @@ DATA(insert OID = 1795 ( "<<" PGNSP PGUID b f 1560 23 1560 0 0 0 0 0
716714
DATA(insert OID = 1796 ( ">>" PGNSP PGUID b f 1560 23 1560 0 0 0 0 0 0 bitshiftright - - ));
717715
DATA(insert OID = 1797 ( "||" PGNSP PGUID b f 1560 1560 1560 0 0 0 0 0 0 bitcat - - ));
718716

719-
DATA(insert OID = 1800 ( "+" PGNSP PGUID b f 1083 1186 1083 0 0 0 0 0 0 time_pl_interval - - ));
717+
DATA(insert OID = 1800 ( "+" PGNSP PGUID b f 1083 1186 1083 1849 0 0 0 0 0 time_pl_interval - - ));
720718
DATA(insert OID = 1801 ( "-" PGNSP PGUID b f 1083 1186 1083 0 0 0 0 0 0 time_mi_interval - - ));
721-
DATA(insert OID = 1802 ( "+" PGNSP PGUID b f 1266 1186 1266 0 0 0 0 0 0 timetz_pl_interval - - ));
719+
DATA(insert OID = 1802 ( "+" PGNSP PGUID b f 1266 1186 1266 2552 0 0 0 0 0 timetz_pl_interval - - ));
722720
DATA(insert OID = 1803 ( "-" PGNSP PGUID b f 1266 1186 1266 0 0 0 0 0 0 timetz_mi_interval - - ));
723721

724722
DATA(insert OID = 1804 ( "=" PGNSP PGUID b f 1562 1562 16 1804 1805 1806 1806 1806 1807 varbiteq eqsel eqjoinsel ));
@@ -728,7 +726,7 @@ DATA(insert OID = 1807 ( ">" PGNSP PGUID b f 1562 1562 16 1806 1808 0 0 0
728726
DATA(insert OID = 1808 ( "<=" PGNSP PGUID b f 1562 1562 16 1809 1807 0 0 0 0 varbitle scalarltsel scalarltjoinsel ));
729727
DATA(insert OID = 1809 ( ">=" PGNSP PGUID b f 1562 1562 16 1808 1806 0 0 0 0 varbitge scalargtsel scalargtjoinsel ));
730728

731-
DATA(insert OID = 1849 ( "+" PGNSP PGUID b f 1186 1083 1083 0 0 0 0 0 0 interval_pl_time - - ));
729+
DATA(insert OID = 1849 ( "+" PGNSP PGUID b f 1186 1083 1083 1800 0 0 0 0 0 interval_pl_time - - ));
732730

733731
DATA(insert OID = 1862 ( "=" PGNSP PGUID b f 21 20 16 1868 1863 95 412 1864 1865 int28eq eqsel eqjoinsel ));
734732
DATA(insert OID = 1863 ( "<>" PGNSP PGUID b f 21 20 16 1869 1862 0 0 0 0 int28ne neqsel neqjoinsel ));
@@ -791,7 +789,7 @@ DATA(insert OID = 2062 ( "<" PGNSP PGUID b f 1114 1114 16 2064 2065 0 0 0 0
791789
DATA(insert OID = 2063 ( "<=" PGNSP PGUID b f 1114 1114 16 2065 2064 0 0 0 0 timestamp_le scalarltsel scalarltjoinsel ));
792790
DATA(insert OID = 2064 ( ">" PGNSP PGUID b f 1114 1114 16 2062 2063 0 0 0 0 timestamp_gt scalargtsel scalargtjoinsel ));
793791
DATA(insert OID = 2065 ( ">=" PGNSP PGUID b f 1114 1114 16 2063 2062 0 0 0 0 timestamp_ge scalargtsel scalargtjoinsel ));
794-
DATA(insert OID = 2066 ( "+" PGNSP PGUID b f 1114 1186 1114 0 0 0 0 0 0 timestamp_pl_interval - - ));
792+
DATA(insert OID = 2066 ( "+" PGNSP PGUID b f 1114 1186 1114 2553 0 0 0 0 0 timestamp_pl_interval - - ));
795793
DATA(insert OID = 2067 ( "-" PGNSP PGUID b f 1114 1114 1186 0 0 0 0 0 0 timestamp_mi - - ));
796794
DATA(insert OID = 2068 ( "-" PGNSP PGUID b f 1114 1186 1114 0 0 0 0 0 0 timestamp_mi_interval - - ));
797795

@@ -864,6 +862,13 @@ DATA(insert OID = 2543 ( ">=" PGNSP PGUID b f 1184 1114 16 2535 2540 0 0 0
864862
DATA(insert OID = 2544 ( ">" PGNSP PGUID b f 1184 1114 16 2534 2541 0 0 0 0 timestamptz_gt_timestamp scalargtsel scalargtjoinsel ));
865863
DATA(insert OID = 2545 ( "<>" PGNSP PGUID b f 1184 1114 16 2539 2542 0 0 0 0 timestamptz_ne_timestamp neqsel neqjoinsel ));
866864

865+
/* formerly-missing interval + datetime operators */
866+
DATA(insert OID = 2551 ( "+" PGNSP PGUID b f 1186 1082 1114 1076 0 0 0 0 0 interval_pl_date - - ));
867+
DATA(insert OID = 2552 ( "+" PGNSP PGUID b f 1186 1266 1266 1802 0 0 0 0 0 interval_pl_timetz - - ));
868+
DATA(insert OID = 2553 ( "+" PGNSP PGUID b f 1186 1114 1114 2066 0 0 0 0 0 interval_pl_timestamp - - ));
869+
DATA(insert OID = 2554 ( "+" PGNSP PGUID b f 1186 1184 1184 1327 0 0 0 0 0 interval_pl_timestamptz - - ));
870+
DATA(insert OID = 2555 ( "+" PGNSP PGUID b f 23 1082 1082 1100 0 0 0 0 0 integer_pl_date - - ));
871+
867872

868873
/*
869874
* function prototypes

0 commit comments

Comments
 (0)