Add a new GUC variable called "IntervalStyle" that decouples interval output

from DateStyle, and create a new interval style that produces output matching
the SQL standard (at least for interval values that fall within the standard's
restrictions).  IntervalStyle is also used to resolve the conflict between the
standard and traditional Postgres rules for interpreting negative interval
input.

Ron Mayer

3 files changed, 65 insertions, 1 deletions

diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out
index 82705163888..e8fee7a38e9 100644
--- a/src/test/regress/expected/interval.out
+++ b/src/test/regress/expected/interval.out
@@ -2,6 +2,7 @@
 -- INTERVAL
 --
 SET DATESTYLE = 'ISO';
+SET IntervalStyle to postgres;
 -- check acceptance of "time zone style"
 SELECT INTERVAL '01:00' AS "One hour";
  One hour 
@@ -273,6 +274,7 @@ FROM INTERVAL_MULDIV_TBL;
 
 DROP TABLE INTERVAL_MULDIV_TBL;
 SET DATESTYLE = 'postgres';
+SET IntervalStyle to postgres_verbose;
 SELECT '' AS ten, * FROM INTERVAL_TBL;
  ten |              f1               
 -----+-------------------------------
@@ -326,6 +328,7 @@ SELECT justify_interval(interval '1 month -1 hour') as "1 month -1 hour";
 
 -- test fractional second input, and detection of duplicate units
 SET DATESTYLE = 'ISO';
+SET IntervalStyle TO postgres;
 SELECT '1 millisecond'::interval, '1 microsecond'::interval,
        '500 seconds 99 milliseconds 51 microseconds'::interval;
    interval   |    interval     |    interval     
@@ -609,3 +612,37 @@ SELECT interval '1 2:03:04.5678' minute to second(2);
  00:03:04.57
 (1 row)
 
+-- test inputting and outputting SQL standard interval literals
+SET IntervalStyle TO sql_standard;
+SELECT  interval '0'                       AS "zero",
+        interval '1-2' year to month       AS "year-month",
+        interval '1 2:03:04' day to second AS "day-time",
+        - interval '1-2'                   AS "negative year-month",
+        - interval '1 2:03:04'             AS "negative day-time";
+ zero | year-month | day-time  | negative year-month | negative day-time 
+------+------------+-----------+---------------------+-------------------
+ 0    | 1-2        | 1 2:03:04 | -1-2                | -1 2:03:04
+(1 row)
+
+-- test input of some not-quite-standard interval values in the sql style
+SET IntervalStyle TO postgres;
+SELECT  interval '+1 -1:00:00',
+        interval '-1 +1:00:00',
+        interval '+1-2 -3 +4:05:06.789',
+        interval '-1-2 +3 -4:05:06.789';
+    interval     |     interval      |              interval               |                interval                
+-----------------+-------------------+-------------------------------------+----------------------------------------
+ 1 day -01:00:00 | -1 days +01:00:00 | 1 year 2 mons -3 days +04:05:06.789 | -1 years -2 mons +3 days -04:05:06.789
+(1 row)
+
+-- test output of couple non-standard interval values in the sql style
+SET IntervalStyle TO sql_standard;
+SELECT  interval '1 day -1 hours',
+        interval '-1 days +1 hours',
+        interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds',
+        - interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds';
+     interval     |     interval     |       interval       |       ?column?       
+------------------+------------------+----------------------+----------------------
+ +0-0 +1 -1:00:00 | +0-0 -1 +1:00:00 | +1-2 -3 +4:05:06.789 | -1-2 +3 -4:05:06.789
+(1 row)
+
diff --git a/src/test/regress/pg_regress.c b/src/test/regress/pg_regress.c
index 93f99eba713..8d4c3dda339 100644
--- a/src/test/regress/pg_regress.c
+++ b/src/test/regress/pg_regress.c
@@ -11,7 +11,7 @@
  * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/test/regress/pg_regress.c,v 1.48 2008/10/01 22:38:57 petere Exp $
+ * $PostgreSQL: pgsql/src/test/regress/pg_regress.c,v 1.49 2008/11/09 00:28:35 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -708,6 +708,7 @@ initialize_environment(void)
 	 */
 	putenv("PGTZ=PST8PDT");
 	putenv("PGDATESTYLE=Postgres, MDY");
+	putenv("PGINTERVALSTYLE=postgres_verbose");
 
 	if (temp_install)
 	{
diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql
index 732ca026f9f..9b32dd6f3b3 100644
--- a/src/test/regress/sql/interval.sql
+++ b/src/test/regress/sql/interval.sql
@@ -3,6 +3,7 @@
 --
 
 SET DATESTYLE = 'ISO';
+SET IntervalStyle to postgres;
 
 -- check acceptance of "time zone style"
 SELECT INTERVAL '01:00' AS "One hour";
@@ -94,6 +95,7 @@ FROM INTERVAL_MULDIV_TBL;
 DROP TABLE INTERVAL_MULDIV_TBL;
 
 SET DATESTYLE = 'postgres';
+SET IntervalStyle to postgres_verbose;
 
 SELECT '' AS ten, * FROM INTERVAL_TBL;
 
@@ -118,6 +120,8 @@ SELECT justify_interval(interval '1 month -1 hour') as "1 month -1 hour";
 
 -- test fractional second input, and detection of duplicate units
 SET DATESTYLE = 'ISO';
+SET IntervalStyle TO postgres;
+
 SELECT '1 millisecond'::interval, '1 microsecond'::interval,
        '500 seconds 99 milliseconds 51 microseconds'::interval;
 SELECT '3 days 5 milliseconds'::interval;
@@ -174,3 +178,25 @@ SELECT interval '1 2:03:04.5678' hour to second(2);
 SELECT interval '1 2.3456' minute to second(2);
 SELECT interval '1 2:03.5678' minute to second(2);
 SELECT interval '1 2:03:04.5678' minute to second(2);
+
+-- test inputting and outputting SQL standard interval literals
+SET IntervalStyle TO sql_standard;
+SELECT  interval '0'                       AS "zero",
+        interval '1-2' year to month       AS "year-month",
+        interval '1 2:03:04' day to second AS "day-time",
+        - interval '1-2'                   AS "negative year-month",
+        - interval '1 2:03:04'             AS "negative day-time";
+
+-- test input of some not-quite-standard interval values in the sql style
+SET IntervalStyle TO postgres;
+SELECT  interval '+1 -1:00:00',
+        interval '-1 +1:00:00',
+        interval '+1-2 -3 +4:05:06.789',
+        interval '-1-2 +3 -4:05:06.789';
+
+-- test output of couple non-standard interval values in the sql style
+SET IntervalStyle TO sql_standard;
+SELECT  interval '1 day -1 hours',
+        interval '-1 days +1 hours',
+        interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds',
+        - interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds';