wrong thousand/group separator with to_char on certain locales...

Hey all,...
I've faced a "bogus" situation in postgreSQL while using "to_char" function
with locale resources that was described before at
http://archives.postgresql.org/pgsql-bugs/2003-05/msg00065.php, and I was
able to identify what the real problem is...

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Ranulfo Netto
Your email address : rcnetto (at) yahoo (dot) com

System Configuration
---------------------
Architecture : Intel Pentium
Operating System : Suse (9.1) Linux 2.6.5-7.108-default
PostgreSQL version : PostgreSQL-7.4.5
Compiler used : gcc version 3.3.3 (SuSE Linux)

Please enter a FULL description of your problem:
------------------------------------------------
When using the function "to_char", with locale support, to format a given
number to a string, the wrong thousand (group) separator is used by
postgreSQL.
It happens only for certain locales which don't have a defined thousand
separator for numeric values into their settings.

Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
- The initialized database must have locale support.
- Try the following:
example #1:
testdb=# set lc_numeric TO 'de_DE';
SET
testdb=# select to_char(10975.23,'999G999G999G990D00');
to_char
-----------------------
10.975,23
(1 row)
- This first example does the right thing because 'de_DE' locale has . (dot)
as its thousand separator for numeric values, so the correct value was
produced.

example #2:
testdb=# set lc_numeric TO 'pt_BR';
SET
testdb=# select to_char(10975.23,'999G999G999G990D00');
to_char
-----------------------
10,975,23
(1 row)
- Now, as brazilian portuguese (pt_BR) locale does not have thousand
separator for numeric values, postgresql uses (at its own) the comma as
separator.
The same happens to pt_PT, de_AT and many others.

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
Browsing the source code, I was able to identify the code that defines that
behavior.

Annotation of pgsql/src/backend/utils/adt/formatting.c, revision 1.81:
(...)
1.35 momjian 3549: /*
1.1 momjian 3550: * Number thousands separator
1.8 momjian 3551: */
1.1 momjian 3552: if (lconv->thousands_sep &&
*lconv->thousands_sep)
3553: Np->L_thousands_sep =
lconv->thousands_sep;
1.8 momjian 3554: else
1.1 momjian 3555: Np->L_thousands_sep = ",";
(...)

Well, this code asks for a thousand separator. If it does not exists, then
the comma is given.
I believe this behavior should be different, cause there are several locales
without thousand separator, and that should be respected.
I mean, the arbitrary values should be given only if any kind of locale info
were found. It can't just mix the locale info with default values... What a
mess, isn't it? :)
Note that this "bogus" can occur to any lconv property when it's not
defined, since the others proterties are tested the same way.

There's another issue to be discussed about that, even I guess this is not
the right place to do so... In Oracle, it's possible to define what are the
numeric characters we want to_char function use when formatting
(NLS_NUMERIC_CHARACTERS = '.,') and since the comments at formatting.c file,
reforce the connection between postgreSQL and Oracle ("inspired by the
Oracle TO_CHAR() / TO_DATE() / TO_NUMBER() routines."), maybe postgreSQL
could have an option such like this to override locale info.

Contact me for more info about this hole thing...
That's it...
Netto