Re: [PATCHES] thousands comma numeric formatting in psql

[Bruce Momjian]

Thanks, fix attached and applied

---

Eugen Nedelcu wrote:
> In function format_numericsep() you have:
> 
> new_str = pg_local_malloc(len_numericseps(my_str) + 1),
> 
> instead of:
> 
> new_str = pg_local_malloc(len_with_numericsep(my_str) + 1)
> 
> Another bug is in function len_numericseps(). This apear for querys 
> like:
> 
> select NULL::numeric; or
> select * from table_with_numeric_fields; where one of numeric fields
> have null values;
> 
> For such values, len_numericseps returns -1, instead of 0.
> 
> Instead of:
> 
> if (int_len % groupdigits != 0)
> sep_len = int_len / groupdigits;
> else
> sep_len = int_len / groupdigits - 1;
> 
> you must have:
> 
> if (int_len % groupdigits != 0 || int_len == 0)
> sep_len = int_len / groupdigits;
> else
> sep_len = int_len / groupdigits - 1;
> 
> Best Regards,
> Eugen
> 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073
Index: src/bin/psql/print.c
===
RCS file: /cvsroot/pgsql/src/bin/psql/print.c,v
retrieving revision 1.69
diff -c -c -r1.69 print.c
*** src/bin/psql/print.c14 Jul 2005 21:12:41 -  1.69
--- src/bin/psql/print.c18 Jul 2005 17:16:37 -
***
*** 68,77 
int int_len = integer_digits(my_str), sep_len;
int groupdigits = atoi(grouping);
  
!   if (int_len % groupdigits != 0)
!   sep_len = int_len / groupdigits;
else
!   sep_len = int_len / groupdigits - 1;/* no leading separator 
*/
  
return sep_len * strlen(thousands_sep) -
   strlen(".") + strlen(decimal_point);
--- 68,78 
int int_len = integer_digits(my_str), sep_len;
int groupdigits = atoi(grouping);
  
!   if (int_len == 0)
!   sep_len = 0;
else
!   /* Don't count a leading separator */
!   sep_len = int_len / groupdigits - (int_len % groupdigits == 0);
  
return sep_len * strlen(thousands_sep) -
   strlen(".") + strlen(decimal_point);
***
*** 93,99 
if (my_str[0] == '-')
my_str++;

!   new_str = pg_local_malloc(len_numericseps(my_str) + 1);
  
leading_digits = (int_len % groupdigits != 0) ?
 int_len % groupdigits : groupdigits;
--- 94,100 
if (my_str[0] == '-')
my_str++;

!   new_str = pg_local_malloc(len_with_numericsep(my_str) + 1);
  
leading_digits = (int_len % groupdigits != 0) ?
 int_len % groupdigits : groupdigits;

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PATCHES] thousands comma numeric formatting in psql

[Bruce Momjian]

Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian  writes:
> > > OK, I have applied the following patch to make numerisep a boolean,
> > 
> > "numericsep" is no longer even remotely reasonable as a name for the
> > parameter.  Something like "numeric_use_locale" would be appropriate
> > (but probably too wordy).
> 
> Basically, with the C locale, it changes 1000.00 to 1,000.00, so indeed
> is does add a numeric separator.  For a European locale, 1000.00 becomes
> 1.000,00. so in that case is changes both the separator and decimal
> marker.  We don't currently have a way to display 1000.00 as 1000,00.
> 
> I am thinking of calling it just numericlocale.

Done, and attached.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/ref/psql-ref.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.147
diff -c -c -r1.147 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml  14 Jul 2005 08:42:36 -  1.147
--- doc/src/sgml/ref/psql-ref.sgml  18 Jul 2005 19:38:56 -
***
*** 1493,1499 

  

!   numericsep


Toggles the display of a locale-aware character to separate groups
--- 1493,1499 

  

!   numericlocale


Toggles the display of a locale-aware character to separate groups
Index: src/bin/psql/command.c
===
RCS file: /cvsroot/pgsql/src/bin/psql/command.c,v
retrieving revision 1.149
diff -c -c -r1.149 command.c
*** src/bin/psql/command.c  14 Jul 2005 08:42:37 -  1.149
--- src/bin/psql/command.c  18 Jul 2005 19:39:02 -
***
*** 1420,1435 
   : _("Expanded display is off.\n"));
}
  
!   /* numeric separators */
!   else if (strcmp(param, "numericsep") == 0)
{
!   popt->topt.numericSep = !popt->topt.numericSep;
if (!quiet)
{
!   if (popt->topt.numericSep)
!   puts(_("Showing numeric separators."));
else
!   puts(_("Numeric separators are off."));
}
}
  
--- 1420,1435 
   : _("Expanded display is off.\n"));
}
  
!   /* locale-aware numeric output */
!   else if (strcmp(param, "numericlocale") == 0)
{
!   popt->topt.numericLocale = !popt->topt.numericLocale;
if (!quiet)
{
!   if (popt->topt.numericLocale)
!   puts(_("Showing locale-adjusted numeric 
output."));
else
!   puts(_("Locale-adjusted numeric output is 
off."));
}
}
  
Index: src/bin/psql/help.c
===
RCS file: /cvsroot/pgsql/src/bin/psql/help.c,v
retrieving revision 1.104
diff -c -c -r1.104 help.c
*** src/bin/psql/help.c 10 Jul 2005 03:46:13 -  1.104
--- src/bin/psql/help.c 18 Jul 2005 19:39:02 -
***
*** 239,245 
fprintf(output, _("  \\pset NAME [VALUE]\n"
  " set table output 
option\n"
  " (NAME := 
{format|border|expanded|fieldsep|footer|null|\n"
!   " 
numericsep|recordsep|tuples_only|title|tableattr|pager})\n"));
fprintf(output, _("  \\t show only rows (currently %s)\n"),
ON(pset.popt.topt.tuples_only));
fprintf(output, _("  \\T [STRING]set HTML  tag attributes, 
or unset if none\n"));
--- 239,245 
fprintf(output, _("  \\pset NAME [VALUE]\n"
  " set table output 
option\n"
  " (NAME := 
{format|border|expanded|fieldsep|footer|null|\n"
!   " 
numericlocale|recordsep|tuples_only|title|tableattr|pager})\n"));
fprintf(output, _("  \\t show only rows (currently %s)\n"),
ON(pset.popt.topt.tuples_only));
fprintf(output, _("  \\T [STRING]set HTML  tag attributes, 
or unset if none\n"));
Index: src/bin/psql/print.c
===
RCS file: /cvsroot/pgsql/src/bin/psql/print.c,v
retrieving revision 1.71
diff -c -c -r1.71 print.c
*** src/bin/psql/print.c18 Jul 2005 19:27:37 -  1.71
--- 

Re: [PATCHES] thousands comma numeric formatting in psql

[Eugen Nedelcu]

In function format_numericsep() you have:

new_str = pg_local_malloc(len_numericseps(my_str) + 1),

instead of:

new_str = pg_local_malloc(len_with_numericsep(my_str) + 1)

Another bug is in function len_numericseps(). This apear for querys 
like:

select NULL::numeric; or
select * from table_with_numeric_fields; where one of numeric fields
have null values;

For such values, len_numericseps returns -1, instead of 0.

Instead of:

if (int_len % groupdigits != 0)
sep_len = int_len / groupdigits;
else
sep_len = int_len / groupdigits - 1;

you must have:

if (int_len % groupdigits != 0 || int_len == 0)
sep_len = int_len / groupdigits;
else
sep_len = int_len / groupdigits - 1;

Best Regards,
Eugen

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PATCHES] thousands comma numeric formatting in psql

[Bruce Momjian]

Eugen Nedelcu wrote:
> The new code is broken. Please test it with resonably large tables.
> Do not test it with querys like: select -132323435.34343;
> 
> If I use a query like:
> 
> select * from my_table limit 100;
> 
> I can't see anything on my screen until I hit CTRL^C
> 
> If I use a pager (\pset pager less) strange things happened:
> I can't see anything on my screen, hit CTRL^C, then quit psql,
> reset xterm, then less is still running and eat lots of memory and
> cpu.

I did:

SELECT random() * 10 INTO test 
FROM generate_series(1,10);
\pset numericsep
SELECT * FROM test;

and it seemed to work fine.  Did you do a complete rebuild/install?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PATCHES] thousands comma numeric formatting in psql

[Eugen Nedelcu]

The new code is broken. Please test it with resonably large tables.
Do not test it with querys like: select -132323435.34343;

If I use a query like:

select * from my_table limit 100;

I can't see anything on my screen until I hit CTRL^C

If I use a pager (\pset pager less) strange things happened:
I can't see anything on my screen, hit CTRL^C, then quit psql,
reset xterm, then less is still running and eat lots of memory and
cpu.

Best Regards,
Eugen

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PATCHES] thousands comma numeric formatting in psql

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian  writes:
> > OK, I have applied the following patch to make numerisep a boolean,
> 
> "numericsep" is no longer even remotely reasonable as a name for the
> parameter.  Something like "numeric_use_locale" would be appropriate
> (but probably too wordy).

Basically, with the C locale, it changes 1000.00 to 1,000.00, so indeed
is does add a numeric separator.  For a European locale, 1000.00 becomes
1.000,00. so in that case is changes both the separator and decimal
marker.  We don't currently have a way to display 1000.00 as 1000,00.

I am thinking of calling it just numericlocale.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PATCHES] thousands comma numeric formatting in psql

[Bruce Momjian]

Tom Lane wrote:
> > The only question I have is whether those locale values are single-byte
> > strings in all locals, or could they be multi-byte or multi-character,
> > in which case I have to treat them as strings.
> 
> I think you have to assume they could be strings.

OK, the following applied patch enables multi-byte locale strings for
numericsep.  I also reorganized the code a little.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073
Index: src/bin/psql/print.c
===
RCS file: /cvsroot/pgsql/src/bin/psql/print.c,v
retrieving revision 1.68
diff -c -c -r1.68 print.c
*** src/bin/psql/print.c14 Jul 2005 15:54:21 -  1.68
--- src/bin/psql/print.c14 Jul 2005 20:54:05 -
***
*** 50,125 
  }
  
  static int
! num_numericseps(const char *my_str)
  {
!   int old_len, dec_len, int_len;
!   int groupdigits = atoi(grouping);
  
if (my_str[0] == '-')
my_str++;
  
!   old_len = strlen(my_str);
!   dec_len = strchr(my_str, '.') ? strlen(strchr(my_str, '.')) : 0;
  
-   int_len = old_len - dec_len;
if (int_len % groupdigits != 0)
!   return int_len / groupdigits;
else
!   return int_len / groupdigits - 1;   /* no leading separator 
*/
  }
  
  static int
  len_with_numericsep(const char *my_str)
  {
!   return strlen(my_str) + num_numericseps(my_str);
  }
  
  static void 
  format_numericsep(char *my_str)
  {
!   int i, j, digits_before_sep, old_len, new_len, dec_len, int_len;
!   char *new_str;
!   char *dec_value;
int groupdigits = atoi(grouping);
  
if (my_str[0] == '-')
my_str++;
! 
!   old_len = strlen(my_str);
!   dec_len = strchr(my_str, '.') ? strlen(strchr(my_str, '.')) : 0;
!   int_len = old_len - dec_len;
!   digits_before_sep = int_len % groupdigits;
! 
!   new_len = int_len + int_len / groupdigits + dec_len;
!   if (digits_before_sep == 0)
!   new_len--;  /* no leading separator */
  
!   new_str = pg_local_malloc(new_len + 1);
  
for (i=0, j=0; ; i++, j++)
{
!   /* hit decimal point */
if (my_str[i] == '.')
{
!   new_str[j] = *decimal_point;
!   new_str[j+1] = '\0';
!   dec_value = strchr(my_str, '.');
!   strcat(new_str, ++dec_value);
break;
}
  
!   /* end of string */
if (my_str[i] == '\0')
{
new_str[j] = '\0';
break;
}
  
!   /* add separator? */
!   if (i != 0 &&
!   (i - (digits_before_sep ? digits_before_sep : 
groupdigits))
!   % groupdigits == 0)
!   new_str[j++] = *thousands_sep;
  
new_str[j] = my_str[i];
}
--- 50,128 
  }
  
  static int
! integer_digits(const char *my_str)
  {
!   int frac_len;
  
if (my_str[0] == '-')
my_str++;
  
!   frac_len = strchr(my_str, '.') ? strlen(strchr(my_str, '.')) : 0;
! 
!   return strlen(my_str) - frac_len;
! }
! 
! static int
! len_numericseps(const char *my_str)
! {
!   int int_len = integer_digits(my_str), sep_len;
!   int groupdigits = atoi(grouping);
  
if (int_len % groupdigits != 0)
!   sep_len = int_len / groupdigits;
else
!   sep_len = int_len / groupdigits - 1;/* no leading separator 
*/
! 
!   return sep_len * strlen(thousands_sep) -
!  strlen(".") + strlen(decimal_point);
  }
  
  static int
  len_with_numericsep(const char *my_str)
  {
!   return strlen(my_str) + len_numericseps(my_str);
  }
  
  static void 
  format_numericsep(char *my_str)
  {
!   int i, j, int_len = integer_digits(my_str), leading_digits;
int groupdigits = atoi(grouping);
+   char *new_str;
  
if (my_str[0] == '-')
my_str++;
!   
!   new_str = pg_local_malloc(len_numericseps(my_str) + 1);
  
!   leading_digits = (int_len % groupdigits != 0) ?
!int_len % groupdigits : groupdigits;
  
for (i=0, j=0; ; i++, j++)
{
!   /* Hit decimal point? */
if (my_str[i] == '.')
{
!   strcpy(&new_str[j], decimal_point);
!   j += strlen(decimal_point);
!   /* add fractional part */
!   strcpy(&new_str[j], &my_str[i] + 1);

Re: [PATCHES] thousands comma numeric formatting in psql

[Tom Lane]

Bruce Momjian  writes:
> OK, I have applied the following patch to make numerisep a boolean,

"numericsep" is no longer even remotely reasonable as a name for the
parameter.  Something like "numeric_use_locale" would be appropriate
(but probably too wordy).

> The only question I have is whether those locale values are single-byte
> strings in all locals, or could they be multi-byte or multi-character,
> in which case I have to treat them as strings.

I think you have to assume they could be strings.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PATCHES] thousands comma numeric formatting in psql

[Peter Eisentraut]

Am Donnerstag, 14. Juli 2005 10:48 schrieb Bruce Momjian:
> OK, I have applied the following patch to make numerisep a boolean, made
> it locale-aware, set defaults if the locale doesn't return meaningful
> values, added code to handle locale-reported groupings, and updated the
> documentation.

Then the name "numericsep" doesn't seem to make much sense anymore.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PATCHES] thousands comma numeric formatting in psql

[Bruce Momjian]

Eugen Nedelcu wrote:
> Regarding locale aproach, it is trivial to replace langinfo with
> localeconv:
> 
> struct lconv *l = localeconv();
> char *dec_point = l->decimal_point;
> 
> instead of:
> 
> #include langinfo.h
> char *dec_point = nl_langinfo(__DECIMAL_POINT);
> 
> I used langinfo because in linux libc it is considered
> "The Elegant and Fast Way" of using locale and conforms with
> X/Open portability guide that every modern Unix follows
> (Solaris, Linux, latest FreeBSD).
> 
> Regarding locale vs. \pset numericsep, for me it doesn't matter
> which one is used. I consider the patch very usefull, and I think 
> that other guys that use psql daily for working with financial data
> will appreciate it too.
> 
> With a quick setting like \pset numericsep ',' all my numeric fields
> will appear in easy readable form. I must underline that to_char is
> a backend function and we talk here about a little psql feature which
> makes life a little easier (for me at least).

OK, I have applied the following patch to make numerisep a boolean, made
it locale-aware, set defaults if the locale doesn't return meaningful
values, added code to handle locale-reported groupings, and updated the
documentation.  

The only question I have is whether those locale values are single-byte
strings in all locals, or could they be multi-byte or multi-character,
in which case I have to treat them as strings.  For now, I added a code
comment that we treat them as single-byte strings.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/ref/psql-ref.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.146
retrieving revision 1.147
diff -c -r1.146 -r1.147
*** doc/src/sgml/ref/psql-ref.sgml  10 Jul 2005 03:46:12 -  1.146
--- doc/src/sgml/ref/psql-ref.sgml  14 Jul 2005 08:42:36 -  1.147
***
*** 1,5 
  
  
--- 1,5 
  
  
***
*** 1496,1505 
numericsep


!   Specifies the character separator between groups of three digits
!   to the left of the decimal marker.  The default is ''
!   (none).  Setting this to a period also changes the decimal marker 
!   to a comma.



--- 1496,1504 
numericsep


!   Toggles the display of a locale-aware character to separate groups
!   of digits to the left of the decimal marker.  It also enables
!   a locale-aware decimal marker.



Index: src/bin/psql/command.c
===
RCS file: /cvsroot/pgsql/src/bin/psql/command.c,v
retrieving revision 1.148
retrieving revision 1.149
diff -c -r1.148 -r1.149
*** src/bin/psql/command.c  14 Jul 2005 06:49:58 -  1.148
--- src/bin/psql/command.c  14 Jul 2005 08:42:37 -  1.149
***
*** 3,9 
   *
   * Copyright (c) 2000-2005, PostgreSQL Global Development Group
   *
!  * $PostgreSQL: pgsql/src/bin/psql/command.c,v 1.148 2005/07/14 06:49:58 
momjian Exp $
   */
  #include "postgres_fe.h"
  #include "command.h"
--- 3,9 
   *
   * Copyright (c) 2000-2005, PostgreSQL Global Development Group
   *
!  * $PostgreSQL: pgsql/src/bin/psql/command.c,v 1.149 2005/07/14 08:42:37 
momjian Exp $
   */
  #include "postgres_fe.h"
  #include "command.h"
***
*** 1420,1434 
   : _("Expanded display is off.\n"));
}
  
else if (strcmp(param, "numericsep") == 0)
{
!   if (value)
{
!   free(popt->topt.numericSep);
!   popt->topt.numericSep = pg_strdup(value);
}
-   if (!quiet)
-   printf(_("Numeric separator is \"%s\".\n"), 
popt->topt.numericSep);
}
  
/* null display */
--- 1420,1436 
   : _("Expanded display is off.\n"));
}
  
+   /* numeric separators */
else if (strcmp(param, "numericsep") == 0)
{
!   popt->topt.numericSep = !popt->topt.numericSep;
!   if (!quiet)
{
!   if (popt->topt.numericSep)
!   puts(_("Showing numeric separators."));
!   else
!   puts(_("Numeric separators are off."));
}
}
  
/* null display */
Index: src/bin/psql/print.c
===
RCS file: /cvsroot/pgsql/src/bin/psql/print.c,v
retrieving revision 1.66

Re: [PATCHES] thousands comma numeric formatting in psql

[Bruce Momjian]

Thanks, fixed, and applied.  I also centralized the malloc into a
function.  We could use pg_malloc, but that doesn't export out to
/scripts, where this is shared.  We will fix that some day, but not
right now.

---

Eugen Nedelcu wrote:
> On Mon, Jul 11, 2005 at 11:37:17PM -0400, Bruce Momjian wrote:
> > 
> > Did you do a 'make clean' before testing?  I can't reproduce any failure
> > here.  Can you supply a number that is failing, like this:
> > 
> > SELECT 100;
> > 
> > ---
> > 
> 
> The bug is in the following code:
> 
> new_str = malloc(new_len) and every one of
> char *my_cell = malloc()
> 
> Ring a bell?
> 
> The corect form is:
> 
> new_str = malloc(new_len + 1),
> char *my_cell = malloc( + 1)
> 
> because of the null character that must terminate the string (\0)
> 
> The error apears of course randomly (more probably for a query which 
> returns many rows)
> 
> Regarding locale aproach, it is trivial to replace langinfo with
> localeconv:
> 
> struct lconv *l = localeconv();
> char *dec_point = l->decimal_point;
> 
> instead of:
> 
> #include langinfo.h
> char *dec_point = nl_langinfo(__DECIMAL_POINT);
> 
> I used langinfo because in linux libc it is considered
> "The Elegant and Fast Way" of using locale and conforms with
> X/Open portability guide that every modern Unix follows
> (Solaris, Linux, latest FreeBSD).
> 
> Regarding locale vs. \pset numericsep, for me it doesn't matter
> which one is used. I consider the patch very usefull, and I think 
> that other guys that use psql daily for working with financial data
> will appreciate it too.
> 
> With a quick setting like \pset numericsep ',' all my numeric fields
> will appear in easy readable form. I must underline that to_char is
> a backend function and we talk here about a little psql feature which
> makes life a little easier (for me at least).
> 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073
Index: src/bin/psql/print.c
===
RCS file: /cvsroot/pgsql/src/bin/psql/print.c,v
retrieving revision 1.63
diff -c -c -r1.63 print.c
*** src/bin/psql/print.c10 Jul 2005 15:53:42 -  1.63
--- src/bin/psql/print.c14 Jul 2005 06:44:10 -
***
*** 29,34 
--- 29,48 
  
  #include "mbprint.h"
  
+ static void *
+ pg_local_malloc(size_t size)
+ {
+   void   *tmp;
+ 
+   tmp = malloc(size);
+   if (!tmp)
+   {
+   psql_error("out of memory\n");
+   exit(EXIT_FAILURE);
+   }
+   return tmp;
+ }
+ 
  static int
  num_numericseps(const char *my_str)
  {
***
*** 46,51 
--- 60,66 
else
return int_len / 3 - 1; /* no leading separator */
  }
+ 
  static int
  len_with_numericsep(const char *my_str)
  {
***
*** 77,88 
if (digits_before_sep == 0)
new_len--;  /* no leading separator */
  
!   new_str = malloc(new_len);
!   if (!new_str)
!   {
!   fprintf(stderr, _("out of memory\n"));
!   exit(EXIT_FAILURE);
!   }
  
for (i=0, j=0; ; i++, j++)
{
--- 92,98 
if (digits_before_sep == 0)
new_len--;  /* no leading separator */
  
!   new_str = pg_local_malloc(new_len + 1);
  
for (i=0, j=0; ; i++, j++)
{
***
*** 167,179 
if ((opt_align[i % col_count] == 'r') && strlen(*ptr) > 0 &&
opt_numericsep != NULL && strlen(opt_numericsep) > 0)
{
!   char *my_cell = malloc(len_with_numericsep(*ptr));

-   if (!my_cell)
-   {
-   fprintf(stderr, _("out of memory\n"));
-   exit(EXIT_FAILURE);
-   }
strcpy(my_cell, *ptr);
format_numericsep(my_cell, opt_numericsep);
fputs(my_cell, fout);
--- 177,184 
if ((opt_align[i % col_count] == 'r') && strlen(*ptr) > 0 &&
opt_numericsep != NULL && strlen(opt_numericsep) > 0)
{
!   char *my_cell = 
pg_local_malloc(len_with_numericsep(*ptr) + 1);

strcpy(my_cell, *ptr);
format_numericsep(my_cell, opt_numericsep);
fputs(my_cell, fout);
***
*** 249,261 
if ((opt_align[i % col_count] == 'r') && strlen(*ptr) != 0 &&
opt_numeri

Re: [PATCHES] thousands comma numeric formatting in psql

[Bruce Momjian]

Peter Eisentraut wrote:
> Bruce Momjian wrote:
> > I have heavily modified your patch and have applied it.  Instead of
> > using langinfo, I used a \pset variable numericsep.
> 
> Why?

Because I don't have langinfo on my system, so I can't test it, nor add
configure code for it.  It also prevents us from using space as the
separator, which is the international standard.

If you think we should use langinfo, we can discuss it, but at this
point, it is not used.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [PATCHES] thousands comma numeric formatting in psql

[Peter Eisentraut]

Bruce Momjian wrote:
> I have heavily modified your patch and have applied it.  Instead of
> using langinfo, I used a \pset variable numericsep.

Why?

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PATCHES] thousands comma numeric formatting in psql

[Bruce Momjian]

Eugen Nedelcu wrote:
> Hello,
> 
> I have included my patch attached to this mail.
> 
> I have made the changes to deal with locale settings from client
> environment. So now you can start psql like this:
> 
> (export LC_ALL=ro_RO; psql -U user db)
> 
> and have numeric formatting with '.' as thousands separator and
> ',' as decimal point, or
> 
> (export LC_ALL=en_US; psql -U user db)
> 
> and have numeric formatting with ',' as thousands separator and
> '.' as decimal point. This formatting is default when locale is 'C'
> 
> You can set any locale and numeric formatting code will take it in
> consideration.
> 
> This patch is for version 7.3.2. The steps for install is:
> 
> 1) cp thousands_comma.diff $POSTGRES_DIR/src/bin/psql
> 2) cd $POSTGRES_DIR/src/bin/psql
> 3) patch -p0 < thousands_comma.diff
> 4) ../../../configure && make

I have heavily modified your patch and have applied it.  Instead of
using langinfo, I used a \pset variable numericsep. (We can talk about
adding langinfo detection later.)  By default, it is off, ''.  If you
set it to '.', the decimal marker will be ','.  This also allows
separators like ' ' too so numebers can appear as 100 000.

I have also added documentation.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/ref/psql-ref.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.145
diff -c -c -r1.145 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml  14 Jun 2005 02:57:38 -  1.145
--- doc/src/sgml/ref/psql-ref.sgml  10 Jul 2005 03:24:16 -
***
*** 1493,1498 
--- 1493,1510 

  

+   numericsep
+   
+   
+   Specifies the character separator between groups of three digits
+   to the left of the decimal marker.  The default is ''
+   (none).  Setting this to a period also changes the decimal marker 
+   to a comma.
+   
+   
+   
+ 
+   
recordsep


Index: src/bin/psql/command.c
===
RCS file: /cvsroot/pgsql/src/bin/psql/command.c,v
retrieving revision 1.146
diff -c -c -r1.146 command.c
*** src/bin/psql/command.c  13 Jun 2005 06:36:22 -  1.146
--- src/bin/psql/command.c  10 Jul 2005 03:24:17 -
***
*** 838,844 
else if (strcmp(cmd, "x") == 0)
success = do_pset("expanded", NULL, &pset.popt, quiet);
  
- 
/* \z -- list table rights (equivalent to \dp) */
else if (strcmp(cmd, "z") == 0)
{
--- 838,843 
***
*** 1421,1426 
--- 1420,1436 
   : _("Expanded display is off.\n"));
}
  
+   else if (strcmp(param, "numericsep") == 0)
+   {
+   if (value)
+   {
+   free(popt->topt.numericSep);
+   popt->topt.numericSep = pg_strdup(value);
+   }
+   if (!quiet)
+   printf(_("Numeric separator is \"%s\".\n"), 
popt->topt.numericSep);
+   }
+ 
/* null display */
else if (strcmp(param, "null") == 0)
{
Index: src/bin/psql/help.c
===
RCS file: /cvsroot/pgsql/src/bin/psql/help.c,v
retrieving revision 1.103
diff -c -c -r1.103 help.c
*** src/bin/psql/help.c 6 Jul 2005 03:14:48 -   1.103
--- src/bin/psql/help.c 10 Jul 2005 03:24:18 -
***
*** 239,245 
fprintf(output, _("  \\pset NAME [VALUE]\n"
  " set table output 
option\n"
  " (NAME := 
{format|border|expanded|fieldsep|footer|null|\n"
!   " recordsep|tuples_only|title|tableattr|pager})\n"));
fprintf(output, _("  \\t show only rows (currently %s)\n"),
ON(pset.popt.topt.tuples_only));
fprintf(output, _("  \\T [STRING]set HTML  tag attributes, 
or unset if none\n"));
--- 239,245 
fprintf(output, _("  \\pset NAME [VALUE]\n"
  " set table output 
option\n"
  " (NAME := 
{format|border|expanded|fieldsep|footer|null|\n"
!   " 
numericsep|recordsep|tuples_only|title|tableattr|pager})\n"));
fprintf(output, _("  \\t show only rows (currently %s)\n"),
ON(pset.popt.topt.tuples_only));
fprintf(output, _("  \\T [STRING]set HTML  ta

Re: [PATCHES] thousands comma numeric formatting in psql

[Eugen Nedelcu]

Hello,

I have included my patch attached to this mail.

I have made the changes to deal with locale settings from client
environment. So now you can start psql like this:

(export LC_ALL=ro_RO; psql -U user db)

and have numeric formatting with '.' as thousands separator and
',' as decimal point, or

(export LC_ALL=en_US; psql -U user db)

and have numeric formatting with ',' as thousands separator and
'.' as decimal point. This formatting is default when locale is 'C'

You can set any locale and numeric formatting code will take it in
consideration.

This patch is for version 7.3.2. The steps for install is:

1) cp thousands_comma.diff $POSTGRES_DIR/src/bin/psql
2) cd $POSTGRES_DIR/src/bin/psql
3) patch -p0 < thousands_comma.diff
4) ../../../configure && make


Best Regards,
Eugen*** ./command.c.origFri Jan 24 07:23:55 2003
--- ./command.c Fri Jun 17 12:10:12 2005
***
*** 817,822 
--- 817,827 
else if (strcmp(cmd, "x") == 0)
success = do_pset("expanded", NULL, &pset.popt, quiet);
  
+   /* thousands comma patch */
+   /* \n -- toggle numeric formating */
+   else if (strcmp(cmd, "n") == 0)
+   success = do_pset("numeric", NULL, &pset.popt, quiet);
+   /* thousands comma patch */
  
/* \z -- list table rights (equivalent to \dp) */
else if (strcmp(cmd, "z") == 0)
***
*** 1780,1785 
--- 1785,1802 
   : gettext("Expanded display is off.\n"));
}
  
+
+   /* thousands comma patch */
+   else if ((strcmp(param, "n") == 0) || (strcmp(param, "numeric") == 0))
+   {
+   popt->topt.numeric = !popt->topt.numeric;
+   if (!quiet)
+   printf(popt->topt.numeric
+  ? gettext("Numeric formating is on.\n")
+  : gettext("Numeric formating is off.\n"));
+   }
+   /* thousands comma patch */
+ 
/* null display */
else if (strcmp(param, "null") == 0)
{
*** ./help.c.orig   Thu Oct 24 04:33:50 2002
--- ./help.cFri Jun 17 12:42:41 2005
***
*** 224,229 
--- 224,231 
ON(pset.popt.topt.expanded));
fprintf(output, _(" \\z [PATTERN]   list table access privileges (same 
as \\dp)\n"));
fprintf(output, _(" \\! [COMMAND]   execute command in shell or start 
interactive shell\n"));
+   fprintf(output, _(" \\n toggle numeric formating (currently 
%s)\n"),
+   ON(pset.popt.topt.numeric));
  
if (output != stdout)
{
*** ./print.c.orig  Fri Nov  1 17:12:19 2002
--- ./print.c   Wed Jun 22 10:10:08 2005
***
*** 19,24 
--- 19,114 
  
  #include "mbprint.h"
  
+ /* thousands comma patch */
+ # include "langinfo.h"
+ 
+ int get_nr_delim (char *);
+ int get_new_l (char *);
+ 
+ int get_nr_delim (char *my_str)
+ {
+ int old_l,dec_l,int_l,k,nr_delim;
+ old_l = strlen(my_str);
+ dec_l = (strstr(my_str, ".") ? strlen(strstr(my_str, ".")) : 0);
+ if (my_str[0] == '-')
+   int_l = old_l - dec_l - 1;
+ else
+   int_l = old_l - dec_l;
+   
+ k = int_l % 3;
+ 
+ if (k)
+   nr_delim = (int_l / 3);
+ else
+   nr_delim = (int_l / 3) - 1;
+ 
+ return nr_delim;
+ }
+ 
+ int get_new_l (char *my_str)
+ {
+ return (strlen(my_str) + get_nr_delim(my_str));
+ }
+ 
+ static void 
+ thousands_comma(char *my_str)
+ {
+ int i,j,k,old_l,new_l,dec_l,int_l;
+ 
+ char *dec_point = nl_langinfo(__DECIMAL_POINT);
+ char *thou_sep;
+ 
+ if (!strcmp(dec_point, "")) dec_point = ".";
+ if (!strcmp(dec_point, "."))
+   thou_sep = ",";
+ else
+   thou_sep = ".";
+ 
+ if (my_str[0] == '-') my_str++;
+ 
+ old_l = strlen(my_str);
+ dec_l = (strstr(my_str, ".") ? strlen(strstr(my_str, ".")) : 0);
+ int_l = old_l - dec_l;
+ k = int_l % 3;
+ 
+ if (k)
+   new_l = int_l + (int_l / 3) + dec_l;
+ else
+   new_l = int_l + (int_l / 3) - 1 + dec_l;
+ 
+ char new_str[new_l];
+ 
+ for (i=0,j=0;;i++,j++)
+ {
+   if (my_str[i] == '.')
+   {
+   new_str[j] = *dec_point;
+   new_str[j+1] = '\0';
+   char *dec_value = strstr(my_str, ".");
+   strcat(new_str, ++dec_value);
+   break;
+   }
+ 
+   if (my_str[i] == '\0')
+   {
+   new_str[j] = '\0';
+   break;
+   }
+ 
+   if ((j - (k ? k : 3)) % 4)
+   new_str[j] = my_str[i];
+   else
+   {
+   new_str[j] = *thou_sep;
+   i--;
+   }
+ }
+ 
+ strcpy(my_str, new_str);
+ }
+ 
+ /* thousands comma patch */
+ 
  /*/
  /* Unaligned text  */
  /*/
***
*** 26,33 
  
  static void
  print_unaligned_text(const char *title, const char *const * headers,
!  

Re: [PATCHES] thousands comma numeric formatting in psql

[Tom Lane]

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> On Tue, Jun 21, 2005 at 04:03:43PM +0300, Eugen Nedelcu wrote:
>> This is a patch for psql client and not for the backend.

> I think it would be much nicer if it was a backend setting.

Doing this as a backend setting has been proposed and rejected before.
The risk of breaking client code seems to outweigh any possible value.

As a psql setting, though, it seems relatively harmless --- certainly
no more dangerous than \x or the other pset formatting parameters.

> If it depended on the locale setting (LC_NUMERIC, I think)

Yes, I wanted to ask that too --- if the patch can cope with locales that
exchange the roles of comma and period, that would make a lot of people
very happy.

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [PATCHES] thousands comma numeric formatting in psql

[Alvaro Herrera]

On Tue, Jun 21, 2005 at 04:03:43PM +0300, Eugen Nedelcu wrote:

> This is a patch for psql client and not for the backend. It's role
> is to output numbers to screen in easy readable form (2,345,675,454,543
> is much easier to read then 2345675454543.456). I think graphical 
> clients like pgAdmin or phppgadmin have a way to do this. I don't know 
> this for sure but I will investigate it.

I think it would be much nicer if it was a backend setting.  If it
depended on the locale setting (LC_NUMERIC, I think) and worked for both
input and output, it would be very good.

-- 
Alvaro Herrera ()
"Uno puede defenderse de los ataques; contra los elogios se esta indefenso"

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [PATCHES] thousands comma numeric formatting in psql

[Bruno Wolff III]

On Tue, Jun 21, 2005 at 16:03:43 +0300,
  Eugen Nedelcu <[EMAIL PROTECTED]> wrote:
> 
> This is a patch for psql client and not for the backend. It's role
> is to output numbers to screen in easy readable form (2,345,675,454,543
> is much easier to read then 2345675454543.456). I think graphical 
> clients like pgAdmin or phppgadmin have a way to do this. I don't know 
> this for sure but I will investigate it.

I did miss that this was a psql only feature. I still don't see a great
need, but it isn't going to cause a problem for copy or pg_dump that way.

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [PATCHES] thousands comma numeric formatting in psql

[Eugen Nedelcu]

On Tue, Jun 21, 2005 at 06:59:38AM -0500, Bruno Wolff III wrote:
> On Tue, Jun 21, 2005 at 08:42:16 +0300,
>   Eugen Nedelcu <[EMAIL PROTECTED]> wrote:
> > 
> > One solution to deal with this is to use to_char function, but for
> > complex selects against multiple tables it's not a good option.
> 
> Why not? You only have to apply it to the output expressions that
> need it.

I think this:

select * from table_with_text_and_numeric_fields;

is much,much easier than:

select text_field1,text_field2,to_char(numeric_field1, '99G999G999'),
   to_char(numeric_field2, '9G999G999G999'), text_field3, 
   text_field4, to_char(numeric_field3, 'MI90G999D99') from
   table_with_text_and_numeric_fields;

> 
> Note that if you output numbers like this, you also need to be able to
> read them back in. I don't think adding complexity for doing that is
> worth not having to add a few to_char calls in your select queries.
> 

I don't know what 'read them back in' means to you. 
This formatting is only done when the number is output to the screen.

Something like: 
fputs(thousands_comma_number, fout)
instead of:
fputs(original_number, fout)

If you want to output to some file for reading back later, you could
turn the feature off with the backslash switch '\n'.

This is a patch for psql client and not for the backend. It's role
is to output numbers to screen in easy readable form (2,345,675,454,543
is much easier to read then 2345675454543.456). I think graphical 
clients like pgAdmin or phppgadmin have a way to do this. I don't know 
this for sure but I will investigate it.

Best Regards,
Eugen

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PATCHES] thousands comma numeric formatting in psql

[Bruno Wolff III]

On Tue, Jun 21, 2005 at 08:42:16 +0300,
  Eugen Nedelcu <[EMAIL PROTECTED]> wrote:
> 
> One solution to deal with this is to use to_char function, but for
> complex selects against multiple tables it's not a good option.

Why not? You only have to apply it to the output expressions that
need it.

Note that if you output numbers like this, you also need to be able to
read them back in. I don't think adding complexity for doing that is
worth not having to add a few to_char calls in your select queries.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[PATCHES] thousands comma numeric formatting in psql

[Eugen Nedelcu]

Hello,

This is my first post to this list.
Sorry if my english it's not so good. It's not my native language.

I'm interrested to now if someone think that having a feature like
'thousands comma delimited numeric formatting' in psql it's a 
usefull thing.

I've made a patch for psql that adds this feature, so issuing a
select like this:

my_database=> select 1234567.89;

results in:

   ?column?
--
 1,234,567.89
 
This feature is toggle on/off with a backslash command ('\n'):

my_database=> \n
Numeric formatting is off.

my_database=> select 1234567.89;

   ?column?
--
 1234567.89
 
For me, psql it's still the best client for postgres, faster and 
flexible than graphic ones. And having tables with many numeric
columns witch contain huge numbers make difficult to read this
numbers.

One solution to deal with this is to use to_char function, but for
complex selects against multiple tables it's not a good option.

Another one is to make a custom function that works like this:

select my_function(... complex subselect ...);

but this seems ugly to me.

By adding the '\n' switch to psql I can make any complex select and
have all numeric fields in result formatted in easy readable form.

I'm not an expert in postgresql, so if someone thinks there is an
easier way to deal with numeric fields, please share.

If my idea is considered usefull I can post the patch to this list.

Best regards,
Eugen.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org