New version of money type

For years I have been promising that a 64 bit version of the money type
was on the way. Here it is. So far it compiles and I have done some
basic testing on it and it seems to work fine. Note that the currency
symbol is also dropped on output as well but it is accepted on input.

darcy=# select '$92,233,720,368,547,758.07'::money;
money
----------------------------
92,233,720,368,547,758.07
(1 row)

Index: src/backend/utils/adt/cash.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/cash.c,v
retrieving revision 1.68
diff -u -p -u -r1.68 cash.c
--- src/backend/utils/adt/cash.c 14 Jul 2006 14:52:23
-0000 1.68 +++ src/backend/utils/adt/cash.c 14 Sep 2006
14:28:30 -0000 @@ -1,13 +1,17 @@
/*
* cash.c
* Written by D'Arcy J.M. Cain
+ * darcy(at)druid(dot)net
+ * http://www.druid.net/darcy/
*
* Functions to allow input and output of money normally but store
- * and handle it as int4s
+ * and handle it as 64 bit ints
*
* A slightly modified version of this file and a discussion of the
* workings can be found in the book "Software Solutions in C" by
- * Dale Schumacher, Academic Press, ISBN: 0-12-632360-7.
+ * Dale Schumacher, Academic Press, ISBN: 0-12-632360-7 except that
+ * this version handles 64 bit numbers and so can hold values up to
+ * $92,233,720,368,547,758.07.
*
* $PostgreSQL: pgsql/src/backend/utils/adt/cash.c,v 1.68 2006/07/14
14:52:23 momjian Exp $ */
@@ -23,17 +27,12 @@
#include "utils/cash.h"
#include "utils/pg_locale.h"

-
-static const char *num_word(Cash value);
-
-/* when we go to 64 bit values we will have to modify this */
-#define CASH_BUFSZ 24
+#define CASH_BUFSZ 36

#define TERMINATOR (CASH_BUFSZ - 1)
#define LAST_PAREN (TERMINATOR - 1)
#define LAST_DIGIT (LAST_PAREN - 1)

-
/*
* Cash is a pass-by-ref SQL type, so we must pass and return pointers.
* These macros and support routine hide the pass-by-refness.
@@ -41,6 +40,65 @@ static const char *num_word(Cash value);
#define PG_GETARG_CASH(n) (* ((Cash *) PG_GETARG_POINTER(n)))
#define PG_RETURN_CASH(x) return CashGetDatum(x)

+
+
+/*************************************************************************
+ * Private routines
+
************************************************************************/
+ +static const char *
+num_word(Cash value)
+{
+ static char buf[128];
+ static const char *small[] = {
+ "zero", "one", "two", "three", "four", "five", "six",
"seven",
+ "eight", "nine", "ten", "eleven", "twelve",
"thirteen", "fourteen",
+ "fifteen", "sixteen", "seventeen", "eighteen",
"nineteen", "twenty",
+ "thirty", "forty", "fifty", "sixty", "seventy",
"eighty", "ninety"
+ };
+ const char **big = small + 18;
+ int tu = value % 100;
+
+ /* deal with the simple cases first */
+ if (value <= 20)
+ return small[value];
+
+ /* is it an even multiple of 100? */
+ if (!tu)
+ {
+ sprintf(buf, "%s hundred", small[value / 100]);
+ return buf;
+ }
+
+ /* more than 99? */
+ if (value > 99)
+ {
+ /* is it an even multiple of 10 other than 10? */
+ if (value % 10 == 0 && tu > 10)
+ sprintf(buf, "%s hundred %s",
+ small[value / 100], big[tu /
10]);
+ else if (tu < 20)
+ sprintf(buf, "%s hundred and %s",
+ small[value / 100], small[tu]);
+ else
+ sprintf(buf, "%s hundred %s %s",
+ small[value / 100], big[tu /
10], small[tu % 10]); +
+ }
+ else
+ {
+ /* is it an even multiple of 10 other than 10? */
+ if (value % 10 == 0 && tu > 10)
+ sprintf(buf, "%s", big[tu / 10]);
+ else if (tu < 20)
+ sprintf(buf, "%s", small[tu]);
+ else
+ sprintf(buf, "%s %s", big[tu / 10], small[tu %
10]);
+ }
+
+ return buf;
+} /* num_word() */
+
static Datum
CashGetDatum(Cash value)
{
@@ -56,12 +114,6 @@ CashGetDatum(Cash value)
* Format is [$]###[,]###[.##]
* Examples: 123.45 $123.45 $123,456.78
*
- * This is currently implemented as a 32-bit integer.
- * XXX HACK It looks as though some of the symbols for
- * monetary values returned by localeconv() can be multiple
- * bytes/characters. This code assumes one byte only. - tgl
97/04/14
- * XXX UNHACK Allow the currency symbol to be multibyte.
- * - thomas 1998-03-01
*/
Datum
cash_in(PG_FUNCTION_ARGS)
@@ -74,11 +126,11 @@ cash_in(PG_FUNCTION_ARGS)
int seen_dot = 0;
const char *s = str;
int fpoint;
- char *csymbol;
+ const char *csymbol,
+ *nsymbol;
char dsymbol,
ssymbol,
- psymbol,
- *nsymbol;
+ psymbol;

struct lconv *lconvert = PGLC_localeconv();

@@ -120,6 +172,7 @@ cash_in(PG_FUNCTION_ARGS)

/* a leading minus or paren signifies a negative number */
/* again, better heuristics needed */
+ /* XXX - doesn't properly check for balanced parens - djmc */
if (strncmp(s, nsymbol, strlen(nsymbol)) == 0)
{
sgn = -1;
@@ -152,7 +205,7 @@ cash_in(PG_FUNCTION_ARGS)

for (;; s++)
{
- /* we look for digits as int4 as we have less */
+ /* we look for digits as int8 as we have less */
/* than the required number of decimal places */
if (isdigit((unsigned char) *s) && dec < fpoint)
{
@@ -161,14 +214,14 @@ cash_in(PG_FUNCTION_ARGS)
if (seen_dot)
dec++;

- /* decimal point? then start counting
fractions... */ }
+ /* decimal point? then start counting fractions... */
else if (*s == dsymbol && !seen_dot)
{
seen_dot = 1;

- /* "thousands" separator? then skip... */
}
+ /* "thousands" separator? then skip... */
else if (*s == ssymbol)
{

@@ -187,7 +240,9 @@ cash_in(PG_FUNCTION_ARGS)
}
}

- while (isspace((unsigned char) *s) || *s == '0' || *s == ')')
+ /* should only be trailing digits followed by whitespace or
closing paren */
+ while (isdigit(*s)) s++;
+ while (isspace((unsigned char) *s) || *s == ')')
s++;

if (*s != '\0')
@@ -223,9 +278,8 @@ cash_out(PG_FUNCTION_ARGS)
int points,
mon_group;
char comma;
- char *csymbol,
- dsymbol,
- *nsymbol;
+ const char *nsymbol;
+ char dsymbol;
char convention;

struct lconv *lconvert = PGLC_localeconv();
@@ -246,7 +300,6 @@ cash_out(PG_FUNCTION_ARGS)
comma = ((*lconvert->mon_thousands_sep != '\0') ?
*lconvert->mon_thousands_sep : ','); convention = lconvert->n_sign_posn;
dsymbol = ((*lconvert->mon_decimal_point != '\0') ?
*lconvert->mon_decimal_point : '.');
- csymbol = ((*lconvert->currency_symbol != '\0') ?
lconvert->currency_symbol : "$"); nsymbol = ((*lconvert->negative_sign !
= '\0') ? lconvert->negative_sign : "-");
point_pos = LAST_DIGIT - points;
@@ -276,13 +329,10 @@ cash_out(PG_FUNCTION_ARGS)
else if (comma && count % (mon_group + 1) ==
comma_position) buf[count--] = comma;

- buf[count--] = ((unsigned int) value % 10) + '0';
- value = ((unsigned int) value) / 10;
+ buf[count--] = ((uint64) value % 10) + '0';
+ value = ((uint64) value) / 10;
}

- strncpy((buf + count - strlen(csymbol) + 1), csymbol, strlen
(csymbol));
- count -= strlen(csymbol) - 1;
-
if (buf[LAST_DIGIT] == ',')
buf[LAST_DIGIT] = buf[LAST_PAREN];

@@ -470,9 +520,6 @@ flt8_mul_cash(PG_FUNCTION_ARGS)

/* cash_div_flt8()
* Divide cash by float8.
- *
- * XXX Don't know if rounding or truncating is correct behavior.
- * Round for now. - tgl 97/04/15
*/
Datum
cash_div_flt8(PG_FUNCTION_ARGS)
@@ -490,6 +537,7 @@ cash_div_flt8(PG_FUNCTION_ARGS)
PG_RETURN_CASH(result);
}

+
/* cash_mul_flt4()
* Multiply cash by float4.
*/
@@ -523,8 +571,6 @@ flt4_mul_cash(PG_FUNCTION_ARGS)
/* cash_div_flt4()
* Divide cash by float4.
*
- * XXX Don't know if rounding or truncating is correct behavior.
- * Round for now. - tgl 97/04/15
*/
Datum
cash_div_flt4(PG_FUNCTION_ARGS)
@@ -543,6 +589,56 @@ cash_div_flt4(PG_FUNCTION_ARGS)
}


+/* cash_mul_int8()
+ * Multiply cash by int8.
+ */
+Datum
+cash_mul_int8(PG_FUNCTION_ARGS)
+{
+ Cash c = PG_GETARG_CASH(0);
+ int64 i = PG_GETARG_INT64(1);
+ Cash result;
+
+ result = c * i;
+ PG_RETURN_CASH(result);
+}
+
+
+/* int8_mul_cash()
+ * Multiply int8 by cash.
+ */
+Datum
+int8_mul_cash(PG_FUNCTION_ARGS)
+{
+ int64 i = PG_GETARG_INT64(0);
+ Cash c = PG_GETARG_CASH(1);
+ Cash result;
+
+ result = i * c;
+ PG_RETURN_CASH(result);
+}
+
+/* cash_div_int8()
+ * Divide cash by 8-byte integer.
+ */
+Datum
+cash_div_int8(PG_FUNCTION_ARGS)
+{
+ Cash c = PG_GETARG_CASH(0);
+ int64 i = PG_GETARG_INT64(1);
+ Cash result;
+
+ if (i == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DIVISION_BY_ZERO),
+ errmsg("division by zero")));
+
+ result = rint(c / i);
+
+ PG_RETURN_CASH(result);
+}
+
+
/* cash_mul_int4()
* Multiply cash by int4.
*/
@@ -550,7 +646,7 @@ Datum
cash_mul_int4(PG_FUNCTION_ARGS)
{
Cash c = PG_GETARG_CASH(0);
- int32 i = PG_GETARG_INT32(1);
+ int64 i = PG_GETARG_INT32(1);
Cash result;

result = c * i;
@@ -576,14 +672,12 @@ int4_mul_cash(PG_FUNCTION_ARGS)
/* cash_div_int4()
* Divide cash by 4-byte integer.
*
- * XXX Don't know if rounding or truncating is correct behavior.
- * Round for now. - tgl 97/04/15
*/
Datum
cash_div_int4(PG_FUNCTION_ARGS)
{
Cash c = PG_GETARG_CASH(0);
- int32 i = PG_GETARG_INT32(1);
+ int64 i = PG_GETARG_INT32(1);
Cash result;

if (i == 0)
@@ -628,8 +722,6 @@ int2_mul_cash(PG_FUNCTION_ARGS)
/* cash_div_int2()
* Divide cash by int2.
*
- * XXX Don't know if rounding or truncating is correct behavior.
- * Round for now. - tgl 97/04/15
*/
Datum
cash_div_int2(PG_FUNCTION_ARGS)
@@ -677,7 +769,6 @@ cashsmaller(PG_FUNCTION_ARGS)
PG_RETURN_CASH(result);
}

-
/* cash_words()
* This converts a int4 as well but to a representation using words
* Obviously way North American centric - sorry
@@ -686,13 +777,16 @@ Datum
cash_words(PG_FUNCTION_ARGS)
{
Cash value = PG_GETARG_CASH(0);
- unsigned int val;
+ uint64 val;
char buf[256];
char *p = buf;
Cash m0;
Cash m1;
Cash m2;
Cash m3;
+ Cash m4;
+ Cash m5;
+ Cash m6;
text *result;

/* work with positive numbers */
@@ -706,12 +800,33 @@ cash_words(PG_FUNCTION_ARGS)
buf[0] = '\0';

/* Now treat as unsigned, to avoid trouble at INT_MIN */
- val = (unsigned int) value;
+ val = (uint64) value;
+
+ m0 = val % 100ll; /* cents */
+ m1 = (val / 100ll) % 1000; /* hundreds */
+ m2 = (val / 100000ll) % 1000; /* thousands */
+ m3 = val / 100000000ll % 1000; /* millions */
+ m4 = val / 100000000000ll % 1000; /* billions */
+ m5 = val / 100000000000000ll % 1000; /* trillions */
+ m6 = val / 100000000000000000ll % 1000; /* quadrillions
*/ +
+ if (m6)
+ {
+ strcat(buf, num_word(m6));
+ strcat(buf, " quadrillion ");
+ }

- m0 = val % 100; /* cents */
- m1 = (val / 100) % 1000; /* hundreds */
- m2 = (val / 100000) % 1000; /* thousands */
- m3 = val / 100000000 % 1000; /* millions */
+ if (m5)
+ {
+ strcat(buf, num_word(m5));
+ strcat(buf, " trillion ");
+ }
+
+ if (m4)
+ {
+ strcat(buf, num_word(m4));
+ strcat(buf, " billion ");
+ }

if (m3)
{
@@ -745,61 +860,3 @@ cash_words(PG_FUNCTION_ARGS)

PG_RETURN_TEXT_P(result);
}
-
-
-/*************************************************************************
- * Private routines
-
************************************************************************/
- -static const char *
-num_word(Cash value)
-{
- static char buf[128];
- static const char *small[] = {
- "zero", "one", "two", "three", "four", "five", "six",
"seven",
- "eight", "nine", "ten", "eleven", "twelve",
"thirteen", "fourteen",
- "fifteen", "sixteen", "seventeen", "eighteen",
"nineteen", "twenty",
- "thirty", "forty", "fifty", "sixty", "seventy",
"eighty", "ninety"
- };
- const char **big = small + 18;
- int tu = value % 100;
-
- /* deal with the simple cases first */
- if (value <= 20)
- return small[value];
-
- /* is it an even multiple of 100? */
- if (!tu)
- {
- sprintf(buf, "%s hundred", small[value / 100]);
- return buf;
- }
-
- /* more than 99? */
- if (value > 99)
- {
- /* is it an even multiple of 10 other than 10? */
- if (value % 10 == 0 && tu > 10)
- sprintf(buf, "%s hundred %s",
- small[value / 100], big[tu /
10]);
- else if (tu < 20)
- sprintf(buf, "%s hundred and %s",
- small[value / 100], small[tu]);
- else
- sprintf(buf, "%s hundred %s %s",
- small[value / 100], big[tu /
10], small[tu % 10]); -
- }
- else
- {
- /* is it an even multiple of 10 other than 10? */
- if (value % 10 == 0 && tu > 10)
- sprintf(buf, "%s", big[tu / 10]);
- else if (tu < 20)
- sprintf(buf, "%s", small[tu]);
- else
- sprintf(buf, "%s %s", big[tu / 10], small[tu %
10]);
- }
-
- return buf;
-} /* num_word() */
Index: src/include/catalog/pg_type.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_type.h,v
retrieving revision 1.171
diff -u -p -u -r1.171 pg_type.h
--- src/include/catalog/pg_type.h 5 Apr 2006 22:11:57
-0000 1.171 +++ src/include/catalog/pg_type.h 14 Sep 2006
14:28:31 -0000 @@ -376,7 +376,7 @@ DATA(insert OID = 718 ( circle
PGNSP DESCR("geometric circle '(center,radius)'");
#define CIRCLEOID 718
DATA(insert OID = 719 ( _circle PGNSP PGUID -1 f b t \054 0
718 array_in array_out array_recv array_send - d x f 0 -1 0 _null_
_null_ )); -DATA(insert OID = 790 ( money PGNSP
PGUID 4 f b t \054 0 0 cash_in cash_out cash_recv cash_send - i
p f 0 -1 0 _null_ _null_ )); +DATA(insert OID = 790 ( money
PGNSP PGUID 8 f b t \054 0 0 cash_in cash_out cash_recv
cash_send - i p f 0 -1 0 _null_ _null_ )); DESCR("monetary amounts,
$d,ddd.cc"); #define CASHOID 790 DATA(insert OID = 791 ( _money
PGNSP PGUID -1 f b t \054 0 790 array_in array_out array_recv
array_send - i x f 0 -1 0 _null_ _null_ )); Index:
src/include/utils/cash.h
=================================================================== RCS
file: /cvsroot/pgsql/src/include/utils/cash.h,v retrieving revision
1.23 diff -u -p -u -r1.23 cash.h --- src/include/utils/cash.h 13
Jul 2006 16:49:20 -0000 1.23 +++ src/include/utils/cash.h
14 Sep 2006 14:28:31 -0000 @@ -3,7 +3,7 @@
* Written by D'Arcy J.M. Cain
*
* Functions to allow input and output of money normally but store
- * and handle it as int4.
+ * and handle it as 64 bit integer.
*/

#ifndef CASH_H
@@ -11,8 +11,7 @@

#include "fmgr.h"

-/* if we store this as 4 bytes, we better make it int, not long, bjm */
-typedef int32 Cash;
+typedef int64 Cash;

extern Datum cash_in(PG_FUNCTION_ARGS);
extern Datum cash_out(PG_FUNCTION_ARGS);
@@ -31,16 +30,20 @@ extern Datum cash_pl(PG_FUNCTION_ARGS);
extern Datum cash_mi(PG_FUNCTION_ARGS);

extern Datum cash_mul_flt8(PG_FUNCTION_ARGS);
-extern Datum cash_div_flt8(PG_FUNCTION_ARGS);
extern Datum flt8_mul_cash(PG_FUNCTION_ARGS);
+extern Datum cash_div_flt8(PG_FUNCTION_ARGS);

extern Datum cash_mul_flt4(PG_FUNCTION_ARGS);
-extern Datum cash_div_flt4(PG_FUNCTION_ARGS);
extern Datum flt4_mul_cash(PG_FUNCTION_ARGS);
+extern Datum cash_div_flt4(PG_FUNCTION_ARGS);
+
+extern Datum cash_mul_int8(PG_FUNCTION_ARGS);
+extern Datum int8_mul_cash(PG_FUNCTION_ARGS);
+extern Datum cash_div_int8(PG_FUNCTION_ARGS);

extern Datum cash_mul_int4(PG_FUNCTION_ARGS);
-extern Datum cash_div_int4(PG_FUNCTION_ARGS);
extern Datum int4_mul_cash(PG_FUNCTION_ARGS);
+extern Datum cash_div_int4(PG_FUNCTION_ARGS);

extern Datum cash_mul_int2(PG_FUNCTION_ARGS);
extern Datum int2_mul_cash(PG_FUNCTION_ARGS);

--
D'Arcy J.M. Cain <darcy(at)druid(dot)net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

D'Arcy J.M. Cain wrote:
> For years I have been promising that a 64 bit version of the money type
> was on the way. Here it is. So far it compiles and I have done some
> basic testing on it and it seems to work fine. Note that the currency
> symbol is also dropped on output as well but it is accepted on input.

Not to come down on your hard work, but isn't the money type deprecated?

Joshua D. Drake

>
> darcy=# select '$92,233,720,368,547,758.07'::money;
> money
> ----------------------------
> 92,233,720,368,547,758.07
> (1 row)
>
>
> Index: src/backend/utils/adt/cash.c
> ===================================================================
> RCS file: /cvsroot/pgsql/src/backend/utils/adt/cash.c,v
> retrieving revision 1.68
> diff -u -p -u -r1.68 cash.c
> --- src/backend/utils/adt/cash.c 14 Jul 2006 14:52:23
> -0000 1.68 +++ src/backend/utils/adt/cash.c 14 Sep 2006
> 14:28:30 -0000 @@ -1,13 +1,17 @@
> /*
> * cash.c
> * Written by D'Arcy J.M. Cain
> + * darcy(at)druid(dot)net
> + * http://www.druid.net/darcy/
> *
> * Functions to allow input and output of money normally but store
> - * and handle it as int4s
> + * and handle it as 64 bit ints
> *
> * A slightly modified version of this file and a discussion of the
> * workings can be found in the book "Software Solutions in C" by
> - * Dale Schumacher, Academic Press, ISBN: 0-12-632360-7.
> + * Dale Schumacher, Academic Press, ISBN: 0-12-632360-7 except that
> + * this version handles 64 bit numbers and so can hold values up to
> + * $92,233,720,368,547,758.07.
> *
> * $PostgreSQL: pgsql/src/backend/utils/adt/cash.c,v 1.68 2006/07/14
> 14:52:23 momjian Exp $ */
> @@ -23,17 +27,12 @@
> #include "utils/cash.h"
> #include "utils/pg_locale.h"
>
> -
> -static const char *num_word(Cash value);
> -
> -/* when we go to 64 bit values we will have to modify this */
> -#define CASH_BUFSZ 24
> +#define CASH_BUFSZ 36
>
> #define TERMINATOR (CASH_BUFSZ - 1)
> #define LAST_PAREN (TERMINATOR - 1)
> #define LAST_DIGIT (LAST_PAREN - 1)
>
> -
> /*
> * Cash is a pass-by-ref SQL type, so we must pass and return pointers.
> * These macros and support routine hide the pass-by-refness.
> @@ -41,6 +40,65 @@ static const char *num_word(Cash value);
> #define PG_GETARG_CASH(n) (* ((Cash *) PG_GETARG_POINTER(n)))
> #define PG_RETURN_CASH(x) return CashGetDatum(x)
>
> +
> +
> +/*************************************************************************
> + * Private routines
> +
> ************************************************************************/
> + +static const char *
> +num_word(Cash value)
> +{
> + static char buf[128];
> + static const char *small[] = {
> + "zero", "one", "two", "three", "four", "five", "six",
> "seven",
> + "eight", "nine", "ten", "eleven", "twelve",
> "thirteen", "fourteen",
> + "fifteen", "sixteen", "seventeen", "eighteen",
> "nineteen", "twenty",
> + "thirty", "forty", "fifty", "sixty", "seventy",
> "eighty", "ninety"
> + };
> + const char **big = small + 18;
> + int tu = value % 100;
> +
> + /* deal with the simple cases first */
> + if (value <= 20)
> + return small[value];
> +
> + /* is it an even multiple of 100? */
> + if (!tu)
> + {
> + sprintf(buf, "%s hundred", small[value / 100]);
> + return buf;
> + }
> +
> + /* more than 99? */
> + if (value > 99)
> + {
> + /* is it an even multiple of 10 other than 10? */
> + if (value % 10 == 0 && tu > 10)
> + sprintf(buf, "%s hundred %s",
> + small[value / 100], big[tu /
> 10]);
> + else if (tu < 20)
> + sprintf(buf, "%s hundred and %s",
> + small[value / 100], small[tu]);
> + else
> + sprintf(buf, "%s hundred %s %s",
> + small[value / 100], big[tu /
> 10], small[tu % 10]); +
> + }
> + else
> + {
> + /* is it an even multiple of 10 other than 10? */
> + if (value % 10 == 0 && tu > 10)
> + sprintf(buf, "%s", big[tu / 10]);
> + else if (tu < 20)
> + sprintf(buf, "%s", small[tu]);
> + else
> + sprintf(buf, "%s %s", big[tu / 10], small[tu %
> 10]);
> + }
> +
> + return buf;
> +} /* num_word() */
> +
> static Datum
> CashGetDatum(Cash value)
> {
> @@ -56,12 +114,6 @@ CashGetDatum(Cash value)
> * Format is [$]###[,]###[.##]
> * Examples: 123.45 $123.45 $123,456.78
> *
> - * This is currently implemented as a 32-bit integer.
> - * XXX HACK It looks as though some of the symbols for
> - * monetary values returned by localeconv() can be multiple
> - * bytes/characters. This code assumes one byte only. - tgl
> 97/04/14
> - * XXX UNHACK Allow the currency symbol to be multibyte.
> - * - thomas 1998-03-01
> */
> Datum
> cash_in(PG_FUNCTION_ARGS)
> @@ -74,11 +126,11 @@ cash_in(PG_FUNCTION_ARGS)
> int seen_dot = 0;
> const char *s = str;
> int fpoint;
> - char *csymbol;
> + const char *csymbol,
> + *nsymbol;
> char dsymbol,
> ssymbol,
> - psymbol,
> - *nsymbol;
> + psymbol;
>
> struct lconv *lconvert = PGLC_localeconv();
>
> @@ -120,6 +172,7 @@ cash_in(PG_FUNCTION_ARGS)
>
> /* a leading minus or paren signifies a negative number */
> /* again, better heuristics needed */
> + /* XXX - doesn't properly check for balanced parens - djmc */
> if (strncmp(s, nsymbol, strlen(nsymbol)) == 0)
> {
> sgn = -1;
> @@ -152,7 +205,7 @@ cash_in(PG_FUNCTION_ARGS)
>
> for (;; s++)
> {
> - /* we look for digits as int4 as we have less */
> + /* we look for digits as int8 as we have less */
> /* than the required number of decimal places */
> if (isdigit((unsigned char) *s) && dec < fpoint)
> {
> @@ -161,14 +214,14 @@ cash_in(PG_FUNCTION_ARGS)
> if (seen_dot)
> dec++;
>
> - /* decimal point? then start counting
> fractions... */ }
> + /* decimal point? then start counting fractions... */
> else if (*s == dsymbol && !seen_dot)
> {
> seen_dot = 1;
>
> - /* "thousands" separator? then skip... */
> }
> + /* "thousands" separator? then skip... */
> else if (*s == ssymbol)
> {
>
> @@ -187,7 +240,9 @@ cash_in(PG_FUNCTION_ARGS)
> }
> }
>
> - while (isspace((unsigned char) *s) || *s == '0' || *s == ')')
> + /* should only be trailing digits followed by whitespace or
> closing paren */
> + while (isdigit(*s)) s++;
> + while (isspace((unsigned char) *s) || *s == ')')
> s++;
>
> if (*s != '\0')
> @@ -223,9 +278,8 @@ cash_out(PG_FUNCTION_ARGS)
> int points,
> mon_group;
> char comma;
> - char *csymbol,
> - dsymbol,
> - *nsymbol;
> + const char *nsymbol;
> + char dsymbol;
> char convention;
>
> struct lconv *lconvert = PGLC_localeconv();
> @@ -246,7 +300,6 @@ cash_out(PG_FUNCTION_ARGS)
> comma = ((*lconvert->mon_thousands_sep != '\0') ?
> *lconvert->mon_thousands_sep : ','); convention = lconvert->n_sign_posn;
> dsymbol = ((*lconvert->mon_decimal_point != '\0') ?
> *lconvert->mon_decimal_point : '.');
> - csymbol = ((*lconvert->currency_symbol != '\0') ?
> lconvert->currency_symbol : "$"); nsymbol = ((*lconvert->negative_sign !
> = '\0') ? lconvert->negative_sign : "-");
> point_pos = LAST_DIGIT - points;
> @@ -276,13 +329,10 @@ cash_out(PG_FUNCTION_ARGS)
> else if (comma && count % (mon_group + 1) ==
> comma_position) buf[count--] = comma;
>
> - buf[count--] = ((unsigned int) value % 10) + '0';
> - value = ((unsigned int) value) / 10;
> + buf[count--] = ((uint64) value % 10) + '0';
> + value = ((uint64) value) / 10;
> }
>
> - strncpy((buf + count - strlen(csymbol) + 1), csymbol, strlen
> (csymbol));
> - count -= strlen(csymbol) - 1;
> -
> if (buf[LAST_DIGIT] == ',')
> buf[LAST_DIGIT] = buf[LAST_PAREN];
>
> @@ -470,9 +520,6 @@ flt8_mul_cash(PG_FUNCTION_ARGS)
>
> /* cash_div_flt8()
> * Divide cash by float8.
> - *
> - * XXX Don't know if rounding or truncating is correct behavior.
> - * Round for now. - tgl 97/04/15
> */
> Datum
> cash_div_flt8(PG_FUNCTION_ARGS)
> @@ -490,6 +537,7 @@ cash_div_flt8(PG_FUNCTION_ARGS)
> PG_RETURN_CASH(result);
> }
>
> +
> /* cash_mul_flt4()
> * Multiply cash by float4.
> */
> @@ -523,8 +571,6 @@ flt4_mul_cash(PG_FUNCTION_ARGS)
> /* cash_div_flt4()
> * Divide cash by float4.
> *
> - * XXX Don't know if rounding or truncating is correct behavior.
> - * Round for now. - tgl 97/04/15
> */
> Datum
> cash_div_flt4(PG_FUNCTION_ARGS)
> @@ -543,6 +589,56 @@ cash_div_flt4(PG_FUNCTION_ARGS)
> }
>
>
> +/* cash_mul_int8()
> + * Multiply cash by int8.
> + */
> +Datum
> +cash_mul_int8(PG_FUNCTION_ARGS)
> +{
> + Cash c = PG_GETARG_CASH(0);
> + int64 i = PG_GETARG_INT64(1);
> + Cash result;
> +
> + result = c * i;
> + PG_RETURN_CASH(result);
> +}
> +
> +
> +/* int8_mul_cash()
> + * Multiply int8 by cash.
> + */
> +Datum
> +int8_mul_cash(PG_FUNCTION_ARGS)
> +{
> + int64 i = PG_GETARG_INT64(0);
> + Cash c = PG_GETARG_CASH(1);
> + Cash result;
> +
> + result = i * c;
> + PG_RETURN_CASH(result);
> +}
> +
> +/* cash_div_int8()
> + * Divide cash by 8-byte integer.
> + */
> +Datum
> +cash_div_int8(PG_FUNCTION_ARGS)
> +{
> + Cash c = PG_GETARG_CASH(0);
> + int64 i = PG_GETARG_INT64(1);
> + Cash result;
> +
> + if (i == 0)
> + ereport(ERROR,
> + (errcode(ERRCODE_DIVISION_BY_ZERO),
> + errmsg("division by zero")));
> +
> + result = rint(c / i);
> +
> + PG_RETURN_CASH(result);
> +}
> +
> +
> /* cash_mul_int4()
> * Multiply cash by int4.
> */
> @@ -550,7 +646,7 @@ Datum
> cash_mul_int4(PG_FUNCTION_ARGS)
> {
> Cash c = PG_GETARG_CASH(0);
> - int32 i = PG_GETARG_INT32(1);
> + int64 i = PG_GETARG_INT32(1);
> Cash result;
>
> result = c * i;
> @@ -576,14 +672,12 @@ int4_mul_cash(PG_FUNCTION_ARGS)
> /* cash_div_int4()
> * Divide cash by 4-byte integer.
> *
> - * XXX Don't know if rounding or truncating is correct behavior.
> - * Round for now. - tgl 97/04/15
> */
> Datum
> cash_div_int4(PG_FUNCTION_ARGS)
> {
> Cash c = PG_GETARG_CASH(0);
> - int32 i = PG_GETARG_INT32(1);
> + int64 i = PG_GETARG_INT32(1);
> Cash result;
>
> if (i == 0)
> @@ -628,8 +722,6 @@ int2_mul_cash(PG_FUNCTION_ARGS)
> /* cash_div_int2()
> * Divide cash by int2.
> *
> - * XXX Don't know if rounding or truncating is correct behavior.
> - * Round for now. - tgl 97/04/15
> */
> Datum
> cash_div_int2(PG_FUNCTION_ARGS)
> @@ -677,7 +769,6 @@ cashsmaller(PG_FUNCTION_ARGS)
> PG_RETURN_CASH(result);
> }
>
> -
> /* cash_words()
> * This converts a int4 as well but to a representation using words
> * Obviously way North American centric - sorry
> @@ -686,13 +777,16 @@ Datum
> cash_words(PG_FUNCTION_ARGS)
> {
> Cash value = PG_GETARG_CASH(0);
> - unsigned int val;
> + uint64 val;
> char buf[256];
> char *p = buf;
> Cash m0;
> Cash m1;
> Cash m2;
> Cash m3;
> + Cash m4;
> + Cash m5;
> + Cash m6;
> text *result;
>
> /* work with positive numbers */
> @@ -706,12 +800,33 @@ cash_words(PG_FUNCTION_ARGS)
> buf[0] = '\0';
>
> /* Now treat as unsigned, to avoid trouble at INT_MIN */
> - val = (unsigned int) value;
> + val = (uint64) value;
> +
> + m0 = val % 100ll; /* cents */
> + m1 = (val / 100ll) % 1000; /* hundreds */
> + m2 = (val / 100000ll) % 1000; /* thousands */
> + m3 = val / 100000000ll % 1000; /* millions */
> + m4 = val / 100000000000ll % 1000; /* billions */
> + m5 = val / 100000000000000ll % 1000; /* trillions */
> + m6 = val / 100000000000000000ll % 1000; /* quadrillions
> */ +
> + if (m6)
> + {
> + strcat(buf, num_word(m6));
> + strcat(buf, " quadrillion ");
> + }
>
> - m0 = val % 100; /* cents */
> - m1 = (val / 100) % 1000; /* hundreds */
> - m2 = (val / 100000) % 1000; /* thousands */
> - m3 = val / 100000000 % 1000; /* millions */
> + if (m5)
> + {
> + strcat(buf, num_word(m5));
> + strcat(buf, " trillion ");
> + }
> +
> + if (m4)
> + {
> + strcat(buf, num_word(m4));
> + strcat(buf, " billion ");
> + }
>
> if (m3)
> {
> @@ -745,61 +860,3 @@ cash_words(PG_FUNCTION_ARGS)
>
> PG_RETURN_TEXT_P(result);
> }
> -
> -
> -/*************************************************************************
> - * Private routines
> -
> ************************************************************************/
> - -static const char *
> -num_word(Cash value)
> -{
> - static char buf[128];
> - static const char *small[] = {
> - "zero", "one", "two", "three", "four", "five", "six",
> "seven",
> - "eight", "nine", "ten", "eleven", "twelve",
> "thirteen", "fourteen",
> - "fifteen", "sixteen", "seventeen", "eighteen",
> "nineteen", "twenty",
> - "thirty", "forty", "fifty", "sixty", "seventy",
> "eighty", "ninety"
> - };
> - const char **big = small + 18;
> - int tu = value % 100;
> -
> - /* deal with the simple cases first */
> - if (value <= 20)
> - return small[value];
> -
> - /* is it an even multiple of 100? */
> - if (!tu)
> - {
> - sprintf(buf, "%s hundred", small[value / 100]);
> - return buf;
> - }
> -
> - /* more than 99? */
> - if (value > 99)
> - {
> - /* is it an even multiple of 10 other than 10? */
> - if (value % 10 == 0 && tu > 10)
> - sprintf(buf, "%s hundred %s",
> - small[value / 100], big[tu /
> 10]);
> - else if (tu < 20)
> - sprintf(buf, "%s hundred and %s",
> - small[value / 100], small[tu]);
> - else
> - sprintf(buf, "%s hundred %s %s",
> - small[value / 100], big[tu /
> 10], small[tu % 10]); -
> - }
> - else
> - {
> - /* is it an even multiple of 10 other than 10? */
> - if (value % 10 == 0 && tu > 10)
> - sprintf(buf, "%s", big[tu / 10]);
> - else if (tu < 20)
> - sprintf(buf, "%s", small[tu]);
> - else
> - sprintf(buf, "%s %s", big[tu / 10], small[tu %
> 10]);
> - }
> -
> - return buf;
> -} /* num_word() */
> Index: src/include/catalog/pg_type.h
> ===================================================================
> RCS file: /cvsroot/pgsql/src/include/catalog/pg_type.h,v
> retrieving revision 1.171
> diff -u -p -u -r1.171 pg_type.h
> --- src/include/catalog/pg_type.h 5 Apr 2006 22:11:57
> -0000 1.171 +++ src/include/catalog/pg_type.h 14 Sep 2006
> 14:28:31 -0000 @@ -376,7 +376,7 @@ DATA(insert OID = 718 ( circle
> PGNSP DESCR("geometric circle '(center,radius)'");
> #define CIRCLEOID 718
> DATA(insert OID = 719 ( _circle PGNSP PGUID -1 f b t \054 0
> 718 array_in array_out array_recv array_send - d x f 0 -1 0 _null_
> _null_ )); -DATA(insert OID = 790 ( money PGNSP
> PGUID 4 f b t \054 0 0 cash_in cash_out cash_recv cash_send - i
> p f 0 -1 0 _null_ _null_ )); +DATA(insert OID = 790 ( money
> PGNSP PGUID 8 f b t \054 0 0 cash_in cash_out cash_recv
> cash_send - i p f 0 -1 0 _null_ _null_ )); DESCR("monetary amounts,
> $d,ddd.cc"); #define CASHOID 790 DATA(insert OID = 791 ( _money
> PGNSP PGUID -1 f b t \054 0 790 array_in array_out array_recv
> array_send - i x f 0 -1 0 _null_ _null_ )); Index:
> src/include/utils/cash.h
> =================================================================== RCS
> file: /cvsroot/pgsql/src/include/utils/cash.h,v retrieving revision
> 1.23 diff -u -p -u -r1.23 cash.h --- src/include/utils/cash.h 13
> Jul 2006 16:49:20 -0000 1.23 +++ src/include/utils/cash.h
> 14 Sep 2006 14:28:31 -0000 @@ -3,7 +3,7 @@
> * Written by D'Arcy J.M. Cain
> *
> * Functions to allow input and output of money normally but store
> - * and handle it as int4.
> + * and handle it as 64 bit integer.
> */
>
> #ifndef CASH_H
> @@ -11,8 +11,7 @@
>
> #include "fmgr.h"
>
> -/* if we store this as 4 bytes, we better make it int, not long, bjm */
> -typedef int32 Cash;
> +typedef int64 Cash;
>
> extern Datum cash_in(PG_FUNCTION_ARGS);
> extern Datum cash_out(PG_FUNCTION_ARGS);
> @@ -31,16 +30,20 @@ extern Datum cash_pl(PG_FUNCTION_ARGS);
> extern Datum cash_mi(PG_FUNCTION_ARGS);
>
> extern Datum cash_mul_flt8(PG_FUNCTION_ARGS);
> -extern Datum cash_div_flt8(PG_FUNCTION_ARGS);
> extern Datum flt8_mul_cash(PG_FUNCTION_ARGS);
> +extern Datum cash_div_flt8(PG_FUNCTION_ARGS);
>
> extern Datum cash_mul_flt4(PG_FUNCTION_ARGS);
> -extern Datum cash_div_flt4(PG_FUNCTION_ARGS);
> extern Datum flt4_mul_cash(PG_FUNCTION_ARGS);
> +extern Datum cash_div_flt4(PG_FUNCTION_ARGS);
> +
> +extern Datum cash_mul_int8(PG_FUNCTION_ARGS);
> +extern Datum int8_mul_cash(PG_FUNCTION_ARGS);
> +extern Datum cash_div_int8(PG_FUNCTION_ARGS);
>
> extern Datum cash_mul_int4(PG_FUNCTION_ARGS);
> -extern Datum cash_div_int4(PG_FUNCTION_ARGS);
> extern Datum int4_mul_cash(PG_FUNCTION_ARGS);
> +extern Datum cash_div_int4(PG_FUNCTION_ARGS);
>
> extern Datum cash_mul_int2(PG_FUNCTION_ARGS);
> extern Datum int2_mul_cash(PG_FUNCTION_ARGS);
>
>

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

On Thu, 14 Sep 2006 07:59:07 -0700
"Joshua D. Drake" <jd(at)commandprompt(dot)com> wrote:
> D'Arcy J.M. Cain wrote:
> > For years I have been promising that a 64 bit version of the money type
> > was on the way. Here it is. So far it compiles and I have done some
> > basic testing on it and it seems to work fine. Note that the currency
> > symbol is also dropped on output as well but it is accepted on input.
>
> Not to come down on your hard work, but isn't the money type deprecated?

Not by me. :-)

The biggest argument about the money type is that it has an unrealistic
limit. With this change we can go to almost one hundred thousand
trillion dollars. That should handle even the US federal budget for a
few more years.

The benefit of the money type is speed. Because internal operations
are done on integers they can generally be handled by single CPU ops.
My tests on the 64 bit version show 10% to 25% improvement over numeric
for many operations.

--
D'Arcy J.M. Cain <darcy(at)druid(dot)net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

D'Arcy J.M. Cain wrote:
> On Thu, 14 Sep 2006 07:59:07 -0700
> "Joshua D. Drake" <jd(at)commandprompt(dot)com> wrote:
>> D'Arcy J.M. Cain wrote:
>>> For years I have been promising that a 64 bit version of the money type
>>> was on the way. Here it is. So far it compiles and I have done some
>>> basic testing on it and it seems to work fine. Note that the currency
>>> symbol is also dropped on output as well but it is accepted on input.
>> Not to come down on your hard work, but isn't the money type deprecated?
>
> Not by me. :-)

Obviously ;), but it is deprecated by the project.

>
> The biggest argument about the money type is that it has an unrealistic
> limit. With this change we can go to almost one hundred thousand
> trillion dollars. That should handle even the US federal budget for a
> few more years.

Isn't that what numeric is for?

>
> The benefit of the money type is speed. Because internal operations
> are done on integers they can generally be handled by single CPU ops.
> My tests on the 64 bit version show 10% to 25% improvement over numeric
> for many operations.

Well that is certainly cool :) I will leave it to others to determine if
we should include it.

Sincerely,

Joshua D. Drake

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

On Thu, 14 Sep 2006 08:17:29 -0700
"Joshua D. Drake" <jd(at)commandprompt(dot)com> wrote:
> Obviously ;), but it is deprecated by the project.

I keep hearing that but no action is ever taken. I think that there
are too many people who still find it useful.

By the way, I removed the currency symbol from the output. Would
removing the commas also make sense? These are the sorts of things
that can be added by applications.

--
D'Arcy J.M. Cain <darcy(at)druid(dot)net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

D'Arcy J.M. Cain wrote:
> On Thu, 14 Sep 2006 08:17:29 -0700
> "Joshua D. Drake" <jd(at)commandprompt(dot)com> wrote:
>> Obviously ;), but it is deprecated by the project.
>
> I keep hearing that but no action is ever taken. I think that there
> are too many people who still find it useful.
>
> By the way, I removed the currency symbol from the output. Would
> removing the commas also make sense? These are the sorts of things
> that can be added by applications.

I don't think that we should be providing *any* presentation beyond the
actual representation of the data. What if it is not US dollars? :)

Joshua D. Drake

>

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

On Thu, 14 Sep 2006 10:33:19 -0700
"Joshua D. Drake" <jd(at)commandprompt(dot)com> wrote:
> > By the way, I removed the currency symbol from the output. Would
> > removing the commas also make sense? These are the sorts of things
> > that can be added by applications.
>
> I don't think that we should be providing *any* presentation beyond the
> actual representation of the data. What if it is not US dollars? :)

That's what locale is for. It looks at that to determine that sort of
stuff including currency symbol before I removed it.

--
D'Arcy J.M. Cain <darcy(at)druid(dot)net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

On Sep 14, 2006, at 14:04 , D'Arcy J.M. Cain wrote:

> On Thu, 14 Sep 2006 10:33:19 -0700
> "Joshua D. Drake" <jd(at)commandprompt(dot)com> wrote:
>>> By the way, I removed the currency symbol from the output. Would
>>> removing the commas also make sense? These are the sorts of things
>>> that can be added by applications.
>>
>> I don't think that we should be providing *any* presentation
>> beyond the
>> actual representation of the data. What if it is not US dollars? :)
>
> That's what locale is for. It looks at that to determine that sort of
> stuff including currency symbol before I removed it.

If you force the locale into the money type, then the entire column
must be of the same currency. That seems like an unnecessary
limitation. Does your type support banker's rounding?

-M

* D'Arcy J.M. Cain (darcy(at)druid(dot)net) wrote:
> The benefit of the money type is speed. Because internal operations
> are done on integers they can generally be handled by single CPU ops.
> My tests on the 64 bit version show 10% to 25% improvement over numeric
> for many operations.

Erm, the numeric is doing integer ops too mostly, no? Perhaps I'm
missing something here.. What *exactly* makes it faster than numeric,
and why couldn't numeric use that improvement? The one thing I can
think of right off would be having a 64bit-base numeric type instead of
the current 32bit-base (which limits us to base-10,000 while 64bit would
give us base-1,000,000,000, which means more done in one operation and
so less work overall- *if* you can do fast 64bit integer operations,
which isn't necessairly the case on all architectures...).

If that's where the improvment is then let's add a 'numeric64' type.

Thanks,

Stephen

Darcy,

> The biggest argument about the money type is that it has an unrealistic
> limit.

Funny, I thought it was the lack of operators, conversions and any clear plan
on how to have a money type that supports multiple currencies.

Or are you working on those? That would be keen ...

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

On Thu, Sep 14, 2006 at 01:56:16PM -0700, Josh Berkus wrote:
> Darcy,
>
> > The biggest argument about the money type is that it has an unrealistic
> > limit.
>
> Funny, I thought it was the lack of operators, conversions and any clear plan
> on how to have a money type that supports multiple currencies.

Indeed, the multiple currencies is what I thought was the real killer.
The taggedtypes module provides a way to handle the multiple currencies
part, I don't think there have been any other real contenders.

Ofcorse, if this is a faster numeric type, you could use the
taggedtypes module to turn it into a generic money type. Win win.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

On 2006-09-15, Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:
> Ofcorse, if this is a faster numeric type,

Presumably the same speed as bigint, which is to say that while it is
faster than numeric for calculation, it is (much) slower for input/output.
(The difference in speed between bigint output and numeric output is
measured in multiples, not in percentages.)

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

On Fri, 15 Sep 2006 10:17:55 -0000
Andrew - Supernews <andrew+nonews(at)supernews(dot)com> wrote:
> Presumably the same speed as bigint, which is to say that while it is
> faster than numeric for calculation, it is (much) slower for input/output.
> (The difference in speed between bigint output and numeric output is
> measured in multiples, not in percentages.)

I/O for money seems at least as compareable to numeric if not slightly
better. Other than that it has all the speed advantages as bigint for
basically the same reasons. It's basically bigint with modified input
and output functions.

--
D'Arcy J.M. Cain <darcy(at)druid(dot)net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

On Thu, 14 Sep 2006 14:12:30 -0400
AgentM <agentm(at)themactionfaction(dot)com> wrote:
> If you force the locale into the money type, then the entire column
> must be of the same currency. That seems like an unnecessary
> limitation. Does your type support banker's rounding?

The whole point of money is to have a high speed type suitable for
accounting apps. I had an application that used money that we had to
switch to numeric due to the size limitation. When we did we saw a
dramatic degredation in performance. The app was a gift card system
that tracked card balances. A card might have hundreds of transactions
and one client might have millions of cards. We had to sum all of
those transactions grouped by card. It would have been great to have
been able to keep the original money type but total sales broke the
limit.

We use rint(), same as the previous version. I know that that isn't
precisely banker's rounding. I think that those special rules would
have to be handled in code. In that environment you would probably
want to do that for auditing (code and otherwise) purposes.

--
D'Arcy J.M. Cain <darcy(at)druid(dot)net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

On 2006-09-15, "D'Arcy J.M. Cain" <darcy(at)druid(dot)net> wrote:
> On Fri, 15 Sep 2006 10:17:55 -0000
> Andrew - Supernews <andrew+nonews(at)supernews(dot)com> wrote:
>> Presumably the same speed as bigint, which is to say that while it is
>> faster than numeric for calculation, it is (much) slower for input/output.
>> (The difference in speed between bigint output and numeric output is
>> measured in multiples, not in percentages.)
>
> I/O for money seems at least as compareable to numeric if not slightly
> better.

Seems? Have you benchmarked it?

> Other than that it has all the speed advantages as bigint for
> basically the same reasons. It's basically bigint with modified input
> and output functions.

The point is that bigint is _not_ faster than numeric for I/O (in fact
even integer is not faster than numeric for output).

Numbers from an actual benchmark:

int4out(0) - 0.42us/call
numeric_out(0) - 0.32us/call

int4out(1000000000) - 0.67us/call
numeric_out(1000000000) - 0.42us/call

For numbers at the top end of bigint's range, the speed difference is on
the order of 4x (albeit on my 32-bit machine)

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

On Fri, 15 Sep 2006 15:14:10 -0000
Andrew - Supernews <andrew+nonews(at)supernews(dot)com> wrote:
> On 2006-09-15, "D'Arcy J.M. Cain" <darcy(at)druid(dot)net> wrote:
> > On Fri, 15 Sep 2006 10:17:55 -0000
> > Andrew - Supernews <andrew+nonews(at)supernews(dot)com> wrote:
> >> Presumably the same speed as bigint, which is to say that while it is
> >> faster than numeric for calculation, it is (much) slower for input/output.
> >> (The difference in speed between bigint output and numeric output is
> >> measured in multiples, not in percentages.)
> >
> > I/O for money seems at least as compareable to numeric if not slightly
> > better.
>
> Seems? Have you benchmarked it?

Not rigourously but a few "ANALYZE EXPLAIN" statements bear out this
observation.

> The point is that bigint is _not_ faster than numeric for I/O (in fact
> even integer is not faster than numeric for output).
>
> Numbers from an actual benchmark:
>
> int4out(0) - 0.42us/call
> numeric_out(0) - 0.32us/call
>
> int4out(1000000000) - 0.67us/call
> numeric_out(1000000000) - 0.42us/call

Whay benchmark is this? Perhaps I can modify it to include my new
implementation.

--
D'Arcy J.M. Cain <darcy(at)druid(dot)net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

Andrew - Supernews wrote:
> Numbers from an actual benchmark:
>
> int4out(0) - 0.42us/call
> numeric_out(0) - 0.32us/call
>
> int4out(1000000000) - 0.67us/call
> numeric_out(1000000000) - 0.42us/call

Is this really int4out, or is it int8out?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 2006-09-15, "D'Arcy J.M. Cain" <darcy(at)druid(dot)net> wrote:
>> Seems? Have you benchmarked it?
>
> Not rigourously but a few "ANALYZE EXPLAIN" statements bear out this
> observation.

The overhead of EXPLAIN ANALYZE is so large that it completely swamps any
real difference.

>> The point is that bigint is _not_ faster than numeric for I/O (in fact
>> even integer is not faster than numeric for output).
>>
>> Numbers from an actual benchmark:
>>
>> int4out(0) - 0.42us/call
>> numeric_out(0) - 0.32us/call
>>
>> int4out(1000000000) - 0.67us/call
>> numeric_out(1000000000) - 0.42us/call
>
> Whay benchmark is this?

Simple queries output to /dev/null. Use \timing in psql to get times.

First measure the benchmark overhead:

select null::integer from generate_series(1,1000) s1,
generate_series(1,1000) s2;

Since output functions are strict, this does not call int4out at all, so
this measures the time taken to generate the million rows, output and discard
them.

Then do the real tests:

select 0::integer from generate_series(1,1000) s1,
generate_series(1,1000) s2;

This calls int4out(0) a million times. (the input function is only called
once since it is a constant, and therefore handled during planning)

select 0::numeric from generate_series(1,1000) s1,
generate_series(1,1000) s2;

This calls numeric_out(0) a million times. And so on.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

On 2006-09-15, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> Andrew - Supernews wrote:
>> Numbers from an actual benchmark:
>>
>> int4out(0) - 0.42us/call
>> numeric_out(0) - 0.32us/call
>>
>> int4out(1000000000) - 0.67us/call
>> numeric_out(1000000000) - 0.42us/call
>
> Is this really int4out, or is it int8out?

int4out. int8out is slower.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

On Fri, 15 Sep 2006 16:15:24 -0000
Andrew - Supernews <andrew+nonews(at)supernews(dot)com> wrote:
> On 2006-09-15, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> > Andrew - Supernews wrote:
> >> Numbers from an actual benchmark:
> >>
> >> int4out(0) - 0.42us/call
> >> numeric_out(0) - 0.32us/call
> >>
> >> int4out(1000000000) - 0.67us/call
> >> numeric_out(1000000000) - 0.42us/call
> >
> > Is this really int4out, or is it int8out?
>
> int4out. int8out is slower.

int8out is probably a better comparison since it is the same range.

--
D'Arcy J.M. Cain <darcy(at)druid(dot)net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

On Fri, 15 Sep 2006 16:15:04 -0000
Andrew - Supernews <andrew+nonews(at)supernews(dot)com> wrote:
> On 2006-09-15, "D'Arcy J.M. Cain" <darcy(at)druid(dot)net> wrote:
> >> Seems? Have you benchmarked it?
> >
> > Not rigourously but a few "ANALYZE EXPLAIN" statements bear out this
> > observation.
>
> The overhead of EXPLAIN ANALYZE is so large that it completely swamps any
> real difference.

Hence my "not rigourously" comment.

> First measure the benchmark overhead:
>
> select null::integer from generate_series(1,1000) s1,
> generate_series(1,1000) s2;

Time: 870.531 ms

> Since output functions are strict, this does not call int4out at all, so
> this measures the time taken to generate the million rows, output and discard
> them.
>
> Then do the real tests:
>
> select 0::integer from generate_series(1,1000) s1,
> generate_series(1,1000) s2;

Time: 1410.690 ms

> This calls int4out(0) a million times. (the input function is only called
> once since it is a constant, and therefore handled during planning)
>
> select 0::numeric from generate_series(1,1000) s1,
> generate_series(1,1000) s2;

Time: 1256.539 ms

Selecting "'0'::money" gives:

Time: 1487.757 ms

Bigint gives:

Time: 1450.405 ms

The extra processing over int and bigint is probably due to locale
formatting. That's partially why I was wondering if the basic type
should be doing that as opposed to doing it in app code. Also, I
wonder if some of the techniques in numeric could be applied here. I
haven't looked carefully at the numeric output code yet.

In any case, I/O speed is probably not that important with this type.
Internal calculations, in my experience, are much more critical.

--
D'Arcy J.M. Cain <darcy(at)druid(dot)net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

On Thu, Sep 14, 2006 at 11:12:14AM -0400, D'Arcy J.M. Cain wrote:
> The benefit of the money type is speed. Because internal operations
> are done on integers they can generally be handled by single CPU ops.
> My tests on the 64 bit version show 10% to 25% improvement over numeric
> for many operations.

Has anyone looked at changing numeric so that for numbers with less than
9 digits it stores/uses an int, and for between 10 and 18 digits it uses
a bigint? Perhaps that would net every numeric user a speed improvement.
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

On Sep 16, 2006, at 5:27 PM, Jim C. Nasby wrote:

> On Thu, Sep 14, 2006 at 11:12:14AM -0400, D'Arcy J.M. Cain wrote:
>> The benefit of the money type is speed. Because internal operations
>> are done on integers they can generally be handled by single CPU ops.
>> My tests on the 64 bit version show 10% to 25% improvement over
>> numeric
>> for many operations.
>
> Has anyone looked at changing numeric so that for numbers with less
> than
> 9 digits it stores/uses an int, and for between 10 and 18 digits it
> uses
> a bigint? Perhaps that would net every numeric user a speed
> improvement.

Would that pose indexing issues? It would also mean that when
joining two tables you'd have to handle some interesting type
conversion issues (at times). We had someone accidentally create a
largish table with userid as "numeric" and other tables are "bigint",
it was disastrous for performance (joining). I'd imagine that if the
above wasn't done cleverly, that performance problem would be repeated.

// Theo Schlossnagle
// CTO -- http://www.omniti.com/~jesus/
// OmniTI Computer Consulting, Inc. -- http://www.omniti.com/

* Theo Schlossnagle (jesus(at)omniti(dot)com) wrote:
> Would that pose indexing issues? It would also mean that when
> joining two tables you'd have to handle some interesting type
> conversion issues (at times). We had someone accidentally create a
> largish table with userid as "numeric" and other tables are "bigint",
> it was disastrous for performance (joining). I'd imagine that if the
> above wasn't done cleverly, that performance problem would be repeated.

The performance issue you ran into with joins was more likely because
there's no hash function for numeric than the way numerics are stored.
I'm not really sure how I feel about this idea... If it's handled
completely inside numeric then it might be reasonable to do (there
wouldn't *be* any real 'type conversion', numeric would just be modified
to support both sizes and would handle an upgrading/downgrading, I don't
think the code would be all *that* complex, honestly...).

I don't think the indexing would be an issue either as you can provide
the appropriate operations regardless of the size.. It might make
writing the hash function a bit more interesting, but probably not...

We might want to have a compile-time option for this tho, as not all
architectures handle 64bit integer ops very well.

Thanks,

Stephen

Theo Schlossnagle <jesus(at)omniti(dot)com> writes:

> Would that pose indexing issues? It would also mean that when joining two
> tables you'd have to handle some interesting type conversion issues (at
> times). We had someone accidentally create a largish table with userid as
> "numeric" and other tables are "bigint", it was disastrous for performance
> (joining). I'd imagine that if the above wasn't done cleverly, that
> performance problem would be repeated.

That used to be a problem but Tom solved it a little while back. Not a perfect
solution in that it requires lots of cross-data-type operators as the number
of data types grows but it works.

In any case I think Jim was suggesting this be handled internally to the
numeric data type which wouldn't cause this problem. However I'm not sure
anything has to be done. A numeric is an array of 16 bit integers, so anything
under 64k *is* stored just as an integer.

Well, just an integer plus a useless exponent. I think it would be a neat
trick to normalize the exponent to the end of the last element of the mantissa
rather than the first digit so that integers don't need an exponent.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

* Gregory Stark (stark(at)enterprisedb(dot)com) wrote:
> In any case I think Jim was suggesting this be handled internally to the
> numeric data type which wouldn't cause this problem. However I'm not sure
> anything has to be done. A numeric is an array of 16 bit integers, so anything
> under 64k *is* stored just as an integer.

Right, which is fine, but for >64k (Actually, isn't it 10,000?),
operations could be done in 1 step using 64bit ints instead of in
multiple steps. On systems with fast 64bit integer ops (quite a few of
them out there these days...) this seems likely to be an improvement in
performance.

Of course, there's the question of how much of an improvement, how
complicated it makes the code, backwards-compatibility issues, and what
to do about the binary in/out operations.

Thanks,

Stephen

On Sep 16, 2006, at 7:31 PM, Gregory Stark wrote:
>> Would that pose indexing issues? It would also mean that when
>> joining two
>> tables you'd have to handle some interesting type conversion
>> issues (at
>> times). We had someone accidentally create a largish table with
>> userid as
>> "numeric" and other tables are "bigint", it was disastrous for
>> performance
>> (joining). I'd imagine that if the above wasn't done cleverly, that
>> performance problem would be repeated.
>
> That used to be a problem but Tom solved it a little while back.
> Not a perfect
> solution in that it requires lots of cross-data-type operators as
> the number
> of data types grows but it works.
>
> In any case I think Jim was suggesting this be handled internally
> to the
> numeric data type which wouldn't cause this problem. However I'm
> not sure
> anything has to be done. A numeric is an array of 16 bit integers,
> so anything
> under 64k *is* stored just as an integer.

Yes, I definitely meant for this to be internal-only... end users
shouldn't notice any difference (except hopefully improved performance).

If all the math is done in 64k chunks then this might not be as big a
help. Numbers between 2^16 and 2^64 (or 2^32 on some platforms) would
probably be faster, but how much faster isn't clear. Perhaps the OP
could do some testing if someone came up with a quick and dirty
prototype patch.

> Well, just an integer plus a useless exponent. I think it would be
> a neat
> trick to normalize the exponent to the end of the last element of
> the mantissa
> rather than the first digit so that integers don't need an exponent.

How would that help? If I'm understanding correctly you're just
talking about storing how many places after the decimal instead of
how many in front of it?
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

On Thu, 14 Sep 2006 10:35:03 -0400
"D'Arcy J.M. Cain" <darcy(at)druid(dot)net> wrote:
> For years I have been promising that a 64 bit version of the money type
> was on the way. Here it is. So far it compiles and I have done some
> basic testing on it and it seems to work fine. Note that the currency
> symbol is also dropped on output as well but it is accepted on input.
>
> darcy=# select '$92,233,720,368,547,758.07'::money;
> money
> ----------------------------
> 92,233,720,368,547,758.07

There has been plenty of discussion back and forth but still no ruling
from core. Is money out in the next release in which case I can
convert this to a contrib module or will this improvement be accepted
for the next release.

--
D'Arcy J.M. Cain <darcy(at)druid(dot)net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.