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@druid.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
foundin 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)#defineLAST_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)))#definePG_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 DatumCashGetDatum(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 */Datumcash_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 */Datumcash_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 */Datumcash_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 @@ Datumcash_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 */Datumcash_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 */Datumcash_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 @@ Datumcash_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
atINT_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        718DATA(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
moneynormally 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@druid.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.