Re: [GENERAL] FM format modifier does not remove leading zero from year - Mailing list pgsql-hackers

Guy Rouillier wrote:
> On 1/6/2010 3:29 PM, Tom Lane wrote:
> > Guy Rouillier<guyr-ml1@burntmail.com>  writes:
> >> Oracle states clearly in the SQL Reference manual:
> >
> >> "A modifier can appear in a format model more than once. In such a case,
> >> each subsequent occurrence toggles the effects of the modifier."
> >
> > *Toggles* the effect of the modifier?  Egad, what drunken idiot chose
> > that specification?
>
> Eh, tomato, tomahto.  If you assume that someone will strip leading
> zeroes consistently, the Oracle approach makes sense.  That would be a
> reasonable assumption to make; why would I strip the zero off the month
> but leave it on the day?  So, in the unusual case that you want to do
> such a thing, you are asked to use a second occurrence of FM to turn
> zero suppression back off.

I have developed the attached patch which implements FM control of YYY,
YY, and Y specifications.  I also documented that we do not match
Oracle's toggle behavior.  There are a few effects on regression test
output which are part of this patch.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.500
diff -c -c -r1.500 func.sgml
*** doc/src/sgml/func.sgml    1 Feb 2010 15:38:21 -0000    1.500
--- doc/src/sgml/func.sgml    6 Feb 2010 21:14:41 -0000
***************
*** 5174,5180 ****
        <para>
         <literal>FM</literal> suppresses leading zeroes and trailing blanks
         that would otherwise be added to make the output of a pattern be
!        fixed-width.
        </para>
       </listitem>

--- 5174,5184 ----
        <para>
         <literal>FM</literal> suppresses leading zeroes and trailing blanks
         that would otherwise be added to make the output of a pattern be
!        fixed-width.  In <productname>PostgreSQL</productname>,
!        <literal>FM</literal> modifies only the next specification, while in
!        Oracle <literal>FM</literal> affects all subsequent
!        specifications, and repeated <literal>FM</literal> modifiers
!        toggle fill mode on and off.
        </para>
       </listitem>

Index: src/backend/utils/adt/formatting.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v
retrieving revision 1.162
diff -c -c -r1.162 formatting.c
*** src/backend/utils/adt/formatting.c    2 Jan 2010 16:57:53 -0000    1.162
--- src/backend/utils/adt/formatting.c    6 Feb 2010 21:14:42 -0000
***************
*** 515,520 ****
--- 515,521 ----
  #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,2438 ****
                  break;
              case DCH_YYY:
              case DCH_IYY:
!                 snprintf(buff, sizeof(buff), "%03d",
                           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));
                  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",
                           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));
                  if (S_THth(n->suffix))
                      str_numth(s, s, S_TH_TYPE(n->suffix));
                  s += strlen(s);
--- 2412,2441 ----
                  break;
              case DCH_YYY:
              case DCH_IYY:
!                 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 ? 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), "%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 ? i - 2 : 0));
                  if (S_THth(n->suffix))
                      str_numth(s, s, S_TH_TYPE(n->suffix));
                  s += strlen(s);
***************
*** 2446,2452 ****
                                                    tm->tm_mon, tm->tm_mday),
                                       is_interval));
                  i = strlen(buff);
!                 strcpy(s, buff + (i - 1));
                  if (S_THth(n->suffix))
                      str_numth(s, s, S_TH_TYPE(n->suffix));
                  s += strlen(s);
--- 2449,2455 ----
                                                    tm->tm_mon, tm->tm_mday),
                                       is_interval));
                  i = strlen(buff);
!                 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);
Index: src/test/regress/expected/timestamp.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/timestamp.out,v
retrieving revision 1.40
diff -c -c -r1.40 timestamp.out
*** src/test/regress/expected/timestamp.out    22 Mar 2009 01:12:32 -0000    1.40
--- src/test/regress/expected/timestamp.out    6 Feb 2010 21:14:44 -0000
***************
*** 1063,1070 ****
             | 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,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
--- 1063,1070 ----
             | 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 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,1568 ****
              | 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
              | 597 597 97 7 7 46 4
              | 1097 097 97 7 7 44 2
              | 1697 697 97 7 7 48 6
--- 1561,1568 ----
              | 1997 997 97 7 7 47 5
              | 1997 997 97 7 7 48 6
              | 1997 997 97 7 7 49 7
!             | 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
Index: src/test/regress/expected/timestamptz.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/timestamptz.out,v
retrieving revision 1.34
diff -c -c -r1.34 timestamptz.out
*** src/test/regress/expected/timestamptz.out    22 Mar 2009 01:12:32 -0000    1.34
--- src/test/regress/expected/timestamptz.out    6 Feb 2010 21:14:44 -0000
***************
*** 1148,1155 ****
             | 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,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
--- 1148,1155 ----
             | 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 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,1662 ****
              | 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
              | 597 597 97 7 7 46 4
              | 1097 097 97 7 7 44 2
              | 1697 697 97 7 7 48 6
--- 1655,1662 ----
              | 1997 997 97 7 7 47 5
              | 1997 997 97 7 7 48 6
              | 1997 997 97 7 7 49 7
!             | 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