Re: New version of money type

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/