Re: [HACKERS] 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 Rouillierguyr-...@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 br...@momjian.ushttp://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.sgml1 Feb 2010 15:38:21 - 1.500 --- doc/src/sgml/func.sgml6 Feb 2010 21:14:41 - *** *** 5174,5180 para literalFM/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 literalFM/literal suppresses leading zeroes and trailing blanks that would otherwise be added to make the output of a pattern be !fixed-width. In productnamePostgreSQL/productname, !literalFM/literal modifies only the next specification, while in !Oracle literalFM/literal affects all subsequent !specifications, and repeated literalFM/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.c2 Jan 2010 16:57:53 - 1.162 --- src/backend/utils/adt/formatting.c6 Feb 2010 21:14:42 - *** *** 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); !
Re: [HACKERS] Re: [GENERAL] FM format modifier does not remove leading zero from year
Bruce Momjian wrote: Guy Rouillier wrote: On 1/6/2010 3:29 PM, Tom Lane wrote: Guy Rouillierguyr-...@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. What's the point of not following Oracle here, since this is solely an Oracle compatibility function? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [GENERAL] FM format modifier does not remove leading zero from year
Alvaro Herrera alvhe...@commandprompt.com writes: What's the point of not following Oracle here, since this is solely an Oracle compatibility function? Changing FM's behavior like that will break approximately every user of to_char() ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [GENERAL] FM format modifier does not remove leading zero from year
Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: What's the point of not following Oracle here, since this is solely an Oracle compatibility function? Changing FM's behavior like that will break approximately every user of to_char() ... I would love to know why we are finding out about this incompatibility only in 2010, years after we implemented this. Is no one porting multi-specification FM to_char() strings from Oracle to PostgreSQL? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [GENERAL] FM format modifier does not remove leading zero from year
Guy Rouillier wrote:
On 1/6/2010 3:29 PM, Tom Lane wrote:
Guy Rouillierguyr-...@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 br...@momjian.ushttp://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 - 1.500
--- doc/src/sgml/func.sgml 6 Feb 2010 21:14:41 -
***
*** 5174,5180
para
literalFM/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
literalFM/literal suppresses leading zeroes and trailing blanks
that would otherwise be added to make the output of a pattern be
!fixed-width. In productnamePostgreSQL/productname,
!literalFM/literal modifies only the next specification, while in
!Oracle literalFM/literal affects all subsequent
!specifications, and repeated literalFM/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 - 1.162
--- src/backend/utils/adt/formatting.c 6 Feb 2010 21:14:42 -
***
*** 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),
