From 70d8a2c29e809c90f7caaded2b4f47b1cf18d01d Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Tue, 16 Feb 2010 21:18:02 +0000 Subject: [PATCH] Honor to_char() "FM" specification in YYY, YY, and Y; it was already honored by YYYY. Also document Oracle "toggle" FM behavior. Per report from Guy Rouillier --- doc/src/sgml/func.sgml | 8 ++++++-- src/backend/utils/adt/formatting.c | 15 +++++++++------ src/test/regress/expected/timestamp.out | 8 ++++---- src/test/regress/expected/timestamptz.out | 8 ++++---- 4 files changed, 23 insertions(+), 16 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index a4ea1462a8..71952ee1fc 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,4 +1,4 @@ - + Functions and Operators @@ -5174,7 +5174,11 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); FM suppresses leading zeroes and trailing blanks that would otherwise be added to make the output of a pattern be - fixed-width. + fixed-width. In PostgreSQL, + FM modifies only the next specification, while in + Oracle FM affects all subsequent + specifications, and repeated FM modifiers + toggle fill mode on and off. diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c index 64f1a8aaa2..a3e7f0c76b 100644 --- a/src/backend/utils/adt/formatting.c +++ b/src/backend/utils/adt/formatting.c @@ -1,7 +1,7 @@ /* ----------------------------------------------------------------------- * formatting.c * - * $PostgreSQL: pgsql/src/backend/utils/adt/formatting.c,v 1.162 2010/01/02 16:57:53 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/formatting.c,v 1.163 2010/02/16 21:18:01 momjian Exp $ * * * Portions Copyright (c) 1999-2010, PostgreSQL Global Development Group @@ -515,6 +515,7 @@ do { \ #define S_th(_s) (((_s) & DCH_S_th) ? 1 : 0) #define S_TH_TYPE(_s) (((_s) & DCH_S_TH) ? TH_UPPER : TH_LOWER) +/* Oracle toggles FM behavior, we don't; see docs. */ #define S_FM(_s) (((_s) & DCH_S_FM) ? 1 : 0) #define S_SP(_s) (((_s) & DCH_S_SP) ? 1 : 0) #define S_TM(_s) (((_s) & DCH_S_TM) ? 1 : 0) @@ -2411,28 +2412,30 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out) break; case DCH_YYY: case DCH_IYY: - snprintf(buff, sizeof(buff), "%03d", + snprintf(buff, sizeof(buff), "%0*d", + S_FM(n->suffix) ? 0 : 3, n->key->id == DCH_YYY ? ADJUST_YEAR(tm->tm_year, is_interval) : ADJUST_YEAR(date2isoyear(tm->tm_year, tm->tm_mon, tm->tm_mday), is_interval)); i = strlen(buff); - strcpy(s, buff + (i - 3)); + strcpy(s, buff + (i > 3 ? i - 3 : 0)); if (S_THth(n->suffix)) str_numth(s, s, S_TH_TYPE(n->suffix)); s += strlen(s); break; case DCH_YY: case DCH_IY: - snprintf(buff, sizeof(buff), "%02d", + snprintf(buff, sizeof(buff), "%0*d", + S_FM(n->suffix) ? 0 : 2, n->key->id == DCH_YY ? ADJUST_YEAR(tm->tm_year, is_interval) : ADJUST_YEAR(date2isoyear(tm->tm_year, tm->tm_mon, tm->tm_mday), is_interval)); i = strlen(buff); - strcpy(s, buff + (i - 2)); + strcpy(s, buff + (i > 2 ? i - 2 : 0)); if (S_THth(n->suffix)) str_numth(s, s, S_TH_TYPE(n->suffix)); s += strlen(s); @@ -2446,7 +2449,7 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out) tm->tm_mon, tm->tm_mday), is_interval)); i = strlen(buff); - strcpy(s, buff + (i - 1)); + strcpy(s, buff + (i > 1 ? i - 1 : 0)); if (S_THth(n->suffix)) str_numth(s, s, S_TH_TYPE(n->suffix)); s += strlen(s); diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out index d99c110724..0062f8f380 100644 --- a/src/test/regress/expected/timestamp.out +++ b/src/test/regress/expected/timestamp.out @@ -1063,8 +1063,8 @@ SELECT '' AS to_char_4, to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM | 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494 | 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495 | 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496 - | 0,097 97 097 97 7 1 1 2 7 47 16 3 1686042 - | 0,097 97 097 97 7 1 1 2 7 47 16 7 1756536 + | 0,097 97 97 97 7 1 1 2 7 47 16 3 1686042 + | 0,097 97 97 97 7 1 1 2 7 47 16 7 1756536 | 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157 | 1,097 1097 097 97 7 11 1 2 7 47 16 3 2121778 | 1,697 1697 697 97 7 17 1 2 7 47 16 7 2340924 @@ -1561,8 +1561,8 @@ SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID') | 1997 997 97 7 7 47 5 | 1997 997 97 7 7 48 6 | 1997 997 97 7 7 49 7 - | 97 097 97 7 7 44 2 - | 97 097 97 7 7 48 6 + | 97 97 97 7 7 44 2 + | 97 97 97 7 7 48 6 | 597 597 97 7 7 46 4 | 1097 097 97 7 7 44 2 | 1697 697 97 7 7 48 6 diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out index f35bb14d35..1096f28e6b 100644 --- a/src/test/regress/expected/timestamptz.out +++ b/src/test/regress/expected/timestamptz.out @@ -1148,8 +1148,8 @@ SELECT '' AS to_char_4, to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM | 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494 | 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495 | 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496 - | 0,097 97 097 97 7 1 1 2 7 47 16 3 1686042 - | 0,097 97 097 97 7 1 1 2 7 47 16 7 1756536 + | 0,097 97 97 97 7 1 1 2 7 47 16 3 1686042 + | 0,097 97 97 97 7 1 1 2 7 47 16 7 1756536 | 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157 | 1,097 1097 097 97 7 11 1 2 7 47 16 3 2121778 | 1,697 1697 697 97 7 17 1 2 7 47 16 7 2340924 @@ -1655,8 +1655,8 @@ SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID') | 1997 997 97 7 7 47 5 | 1997 997 97 7 7 48 6 | 1997 997 97 7 7 49 7 - | 97 097 97 7 7 44 2 - | 97 097 97 7 7 48 6 + | 97 97 97 7 7 44 2 + | 97 97 97 7 7 48 6 | 597 597 97 7 7 46 4 | 1097 097 97 7 7 44 2 | 1697 697 97 7 7 48 6 -- 2.40.0