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 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);
