Re: Re: [GENERAL] FM format modifier does not remove leading zero from year

Applied.

---------------------------------------------------------------------------

Bruce Momjian wrote:
> Guy Rouillier wrote:
> > On 1/6/2010 3:29 PM, Tom Lane wrote:
> > > Guy Rouillier<guyr-ml1(at)burntmail(dot)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(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + If your life is a hard drive, Christ can be your backup. +

[ text/x-diff is unsupported, treating like TEXT/PLAIN ]

> 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

>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +