Pavel Stehule escribió:
> It was my mistake - I was confused from timestamp with time zone type,
> what has zero related to date and time.
>
> fixed to immutable,
> fixed duplicate oid
Thanks. I wasn't sure about the error message returned when times are
outside range; how about this instead? I'm not wedded to this approach
-- I can return to yours if this one isn't liked -- but I think the
more specific messages are better. I realize this is inconsistent with
the make_date case which always displays the full date instead of
specific fields, but I think it's more likely that someone is doing
arithmetic to enter time fields than date. (Anyway maybe this is not an
important enough issue to create more work for translators.)
+ if (tm_hour < 0 || tm_hour > HOURS_PER_DAY)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
+ errmsg("hours field in time value out of range: \"%02d\"",
+ tm_hour)));
+
+ if (tm_min < 0 || tm_min > MINS_PER_HOUR - 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
+ errmsg("minutes field in time value out of range: \"%02d\"",
+ tm_min)));
+
+ if (sec < 0.0 || sec > (float8) SECS_PER_MINUTE)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
+ errmsg("seconds field in time value out of range: \"%0*.*f\"",
+ MAX_TIME_PRECISION + 3,
+ MAX_TIME_PRECISION, fabs(sec))));
+
+ /* test for > 24:00:00 */
+ if ((tm_hour == HOURS_PER_DAY && (tm_min > 0 || sec > 0.0)))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
+ errmsg("time value out of range: \"%02d:%02d:%0*.*f\"",
+ tm_hour, tm_min,
+ MAX_TIME_PRECISION + 3,
+ MAX_TIME_PRECISION, fabs(sec))));
Other than that (and fixing regression tests as appropriate), I think
the attached, which has mild corrections over your v5, is ready to
commit. (You had one missing semicolon in the float timestamp case.)
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 6669,6674 **** SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
--- 6669,6716 ----
<row>
<entry>
<indexterm>
+ <primary>make_date</primary>
+ </indexterm>
+ <literal>
+ <function>
+ make_date(<parameter>year</parameter> <type>int</type>,
+ <parameter>month</parameter> <type>int</type>,
+ <parameter>day</parameter> <type>int</type>)
+ </function>
+ </literal>
+ </entry>
+ <entry><type>date</type></entry>
+ <entry>
+ Create date from year, month and day fields
+ </entry>
+ <entry><literal>make_date(2013, 7, 15)</literal></entry>
+ <entry><literal>2013-07-15</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>make_time</primary>
+ </indexterm>
+ <literal>
+ <function>
+ make_time(<parameter>hour</parameter> <type>int</type>,
+ <parameter>min</parameter> <type>int</type>,
+ <parameter>sec</parameter> <type>double precision</type>)
+ </function>
+ </literal>
+ </entry>
+ <entry><type>time</type></entry>
+ <entry>
+ Create time from hour, minutes and second fields
+ </entry>
+ <entry><literal>make_time(8, 15, 23.5)</literal></entry>
+ <entry><literal>08:15:23.5</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
<primary>now</primary>
</indexterm>
<literal><function>now()</function></literal>
*** a/src/backend/utils/adt/date.c
--- b/src/backend/utils/adt/date.c
***************
*** 2729,2731 **** timetz_izone(PG_FUNCTION_ARGS)
--- 2729,2815 ----
PG_RETURN_TIMETZADT_P(result);
}
+
+ /*
+ * make_date()
+ * date constructor
+ */
+ Datum
+ make_date(PG_FUNCTION_ARGS)
+ {
+ struct pg_tm tm;
+ DateADT date;
+ int dterr;
+
+ tm.tm_year = PG_GETARG_INT32(0);
+ tm.tm_mon = PG_GETARG_INT32(1);
+ tm.tm_mday = PG_GETARG_INT32(2);
+
+ dterr = ValidateDate(DTK_DATE_M, true, false, false, &tm);
+
+ if (dterr != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
+ errmsg("date field value out of range: \"%d-%d-%d\"",
+ tm.tm_year, tm.tm_mon, tm.tm_mday)));
+
+ if (!IS_VALID_JULIAN(tm.tm_year, tm.tm_mon, tm.tm_mday))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("date out of range: \"%d-%d-%d\"",
+ tm.tm_year, tm.tm_mon, tm.tm_mday)));
+
+ date = date2j(tm.tm_year, tm.tm_mon, tm.tm_mday) - POSTGRES_EPOCH_JDATE;
+
+ PG_RETURN_DATEADT(date);
+ }
+
+ /*
+ * make_time()
+ * time constructor
+ */
+ Datum
+ make_time(PG_FUNCTION_ARGS)
+ {
+ int tm_hour = PG_GETARG_INT32(0);
+ int tm_min = PG_GETARG_INT32(1);
+ float8 sec = PG_GETARG_FLOAT8(2);
+ TimeADT time;
+
+ if (tm_hour < 0 || tm_hour > HOURS_PER_DAY)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
+ errmsg("hours field in time value out of range: \"%02d\"",
+ tm_hour)));
+
+ if (tm_min < 0 || tm_min > MINS_PER_HOUR - 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
+ errmsg("minutes field in time value out of range: \"%02d\"",
+ tm_min)));
+
+ if (sec < 0.0 || sec > (float8) SECS_PER_MINUTE)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
+ errmsg("seconds field in time value out of range: \"%0*.*f\"",
+ MAX_TIME_PRECISION + 3,
+ MAX_TIME_PRECISION, fabs(sec))));
+
+ /* test for > 24:00:00 */
+ if ((tm_hour == HOURS_PER_DAY && (tm_min > 0 || sec > 0.0)))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_FIELD_OVERFLOW),
+ errmsg("time value out of range: \"%02d:%02d:%0*.*f\"",
+ tm_hour, tm_min,
+ MAX_TIME_PRECISION + 3,
+ MAX_TIME_PRECISION, fabs(sec))));
+
+ #ifdef HAVE_INT64_TIMESTAMP
+ time = ((tm_hour * MINS_PER_HOUR + tm_min) * SECS_PER_MINUTE) * USECS_PER_SEC
+ + rint(sec * USECS_PER_SEC);
+ #else
+ time = ((tm_hour * MINS_PER_HOUR + tm_min) * SECS_PER_MINUTE) + sec;
+ #endif
+
+ PG_RETURN_TIMEADT(time);
+ }
*** a/src/backend/utils/adt/datetime.c
--- b/src/backend/utils/adt/datetime.c
***************
*** 44,51 **** static int DecodeTimezone(char *str, int *tzp);
static const datetkn *datebsearch(const char *key, const datetkn *base, int nel);
static int DecodeDate(char *str, int fmask, int *tmask, bool *is2digits,
struct pg_tm * tm);
- static int ValidateDate(int fmask, bool isjulian, bool is2digits, bool bc,
- struct pg_tm * tm);
static void TrimTrailingZeros(char *str);
static void AppendSeconds(char *cp, int sec, fsec_t fsec,
int precision, bool fillzeros);
--- 44,49 ----
***************
*** 2266,2272 **** DecodeDate(char *str, int fmask, int *tmask, bool *is2digits,
* Check valid year/month/day values, handle BC and DOY cases
* Return 0 if okay, a DTERR code if not.
*/
! static int
ValidateDate(int fmask, bool isjulian, bool is2digits, bool bc,
struct pg_tm * tm)
{
--- 2264,2270 ----
* Check valid year/month/day values, handle BC and DOY cases
* Return 0 if okay, a DTERR code if not.
*/
! int
ValidateDate(int fmask, bool isjulian, bool is2digits, bool bc,
struct pg_tm * tm)
{
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 4662,4667 **** DESCR("int8range constructor");
--- 4662,4673 ----
DATA(insert OID = 3946 ( int8range PGNSP PGUID 12 1 0 0 0 f f f f f f i 3 0 3926 "20 20 25" _null_ _null_ _null_ _null_ range_constructor3 _null_ _null_ _null_ ));
DESCR("int8range constructor");
+ /* date, time constructors */
+ DATA(insert OID = 3969 ( make_date PGNSP PGUID 12 1 0 0 0 f f f f t f i 3 0 1082 "23 23 23" _null_ _null_ "{year,month,day}" _null_ make_date _null_ _null_ _null_ ));
+ DESCR("construct date");
+ DATA(insert OID = 3970 ( make_time PGNSP PGUID 12 1 0 0 0 f f f f t f i 3 0 1083 "23 23 701" _null_ _null_ "{hour,min,sec}" _null_ make_time _null_ _null_ _null_ ));
+ DESCR("construct time");
+
/* spgist support functions */
DATA(insert OID = 4001 ( spggettuple PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 16 "2281 2281" _null_ _null_ _null_ _null_ spggettuple _null_ _null_ _null_ ));
DESCR("spgist(internal)");
*** a/src/include/utils/date.h
--- b/src/include/utils/date.h
***************
*** 204,207 **** extern Datum timetz_izone(PG_FUNCTION_ARGS);
--- 204,210 ----
extern Datum timetz_pl_interval(PG_FUNCTION_ARGS);
extern Datum timetz_mi_interval(PG_FUNCTION_ARGS);
+ extern Datum make_date(PG_FUNCTION_ARGS);
+ extern Datum make_time(PG_FUNCTION_ARGS);
+
#endif /* DATE_H */
*** a/src/include/utils/datetime.h
--- b/src/include/utils/datetime.h
***************
*** 310,313 **** extern void InstallTimeZoneAbbrevs(TimeZoneAbbrevTable *tbl);
--- 310,316 ----
extern Datum pg_timezone_abbrevs(PG_FUNCTION_ARGS);
extern Datum pg_timezone_names(PG_FUNCTION_ARGS);
+ extern int ValidateDate(int fmask, bool isjulian, bool is2digits, bool bc, struct pg_tm *tm);
+
+
#endif /* DATETIME_H */
*** a/src/test/regress/expected/date.out
--- b/src/test/regress/expected/date.out
***************
*** 1184,1186 **** select isfinite('infinity'::date), isfinite('-infinity'::date), isfinite('today'
--- 1184,1208 ----
f | f | t
(1 row)
+ -- test constructors
+ select make_date(2013, 7, 15);
+ make_date
+ ------------
+ 07-15-2013
+ (1 row)
+
+ select make_time(8, 20, 0.0);
+ make_time
+ -----------
+ 08:20:00
+ (1 row)
+
+ -- should fail
+ select make_date(2013, 2, 30);
+ ERROR: date field value out of range: "2013-2-30"
+ select make_date(2013, 13, 1);
+ ERROR: date field value out of range: "2013-13-1"
+ select make_time(10, 55, 100.1);
+ ERROR: time field value out of range: "10:55:100.100000"
+ select make_time(24, 0, 2.1);
+ ERROR: time field value out of range: "24:00:02.100000"
*** a/src/test/regress/sql/date.sql
--- b/src/test/regress/sql/date.sql
***************
*** 276,278 **** select 'infinity'::date, '-infinity'::date;
--- 276,289 ----
select 'infinity'::date > 'today'::date as t;
select '-infinity'::date < 'today'::date as t;
select isfinite('infinity'::date), isfinite('-infinity'::date), isfinite('today'::date);
+
+
+ -- test constructors
+ select make_date(2013, 7, 15);
+ select make_time(8, 20, 0.0);
+
+ -- should fail
+ select make_date(2013, 2, 30);
+ select make_date(2013, 13, 1);
+ select make_time(10, 55, 100.1);
+ select make_time(24, 0, 2.1);
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
