From 7590ddb3eba2e8bcbd531687789f56f9e0658277 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 16 Jul 2010 02:15:56 +0000 Subject: [PATCH] Add support for dividing money by money (yielding a float8 result) and for casting between money and numeric. Andy Balholm, reviewed by Kevin Grittner --- doc/src/sgml/datatype.sgml | 54 ++++++++++++--------- src/backend/utils/adt/cash.c | 99 +++++++++++++++++++++++++++++++++++++-- src/include/catalog/catversion.h | 4 +- src/include/catalog/pg_cast.h | 4 +- src/include/catalog/pg_operator.h | 3 +- src/include/catalog/pg_proc.h | 12 +++-- src/include/utils/cash.h | 6 ++- 7 files changed, 148 insertions(+), 34 deletions(-) diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 20bf2ea9fa..faf40befb1 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -1,4 +1,4 @@ - + Data Types @@ -839,32 +839,11 @@ ALTER SEQUENCE tablename_. The fractional precision is determined by the database's setting. + The range shown in the table assumes there are two fractional digits. Input is accepted in a variety of formats, including integer and floating-point literals, as well as typical currency formatting, such as '$1,000.00'. Output is generally in the latter form but depends on the locale. - Non-quoted numeric values can be converted to money by - casting the numeric value to text and then - money, for example: - -SELECT 1234::text::money; - - There is no simple way of doing the reverse in a locale-independent - manner, namely casting a money value to a numeric type. - If you know the currency symbol and thousands separator you can use - regexp_replace(): - -SELECT regexp_replace('52093.89'::money::text, '[$,]', '', 'g')::numeric; - - - - - - Since the output of this data type is locale-sensitive, it might not - work to load money data into a database that has a different - setting of lc_monetary. To avoid problems, before - restoring a dump into a new database make sure lc_monetary has the same or - equivalent value as in the database that was dumped. @@ -888,6 +867,35 @@ SELECT regexp_replace('52093.89'::money::text, '[$,]', '', 'g')::numeric;
+ + + Since the output of this data type is locale-sensitive, it might not + work to load money data into a database that has a different + setting of lc_monetary. To avoid problems, before + restoring a dump into a new database make sure lc_monetary has + the same or equivalent value as in the database that was dumped. + + + + Values of the numeric data type can be cast to + money. Other numeric types can be converted to + money by casting to numeric first, for example: + +SELECT 1234::numeric::money; + + A money value can be cast to numeric without + loss of precision. Conversion to other types could potentially lose + precision, and it must be done in two stages, for example: + +SELECT '52093.89'::money::numeric::float8; + + + + + When a money value is divided by another money + value, the result is double precision (i.e., a pure number, + not money); the currency units cancel each other out in the division. + diff --git a/src/backend/utils/adt/cash.c b/src/backend/utils/adt/cash.c index 784947e440..c33c7cdaae 100644 --- a/src/backend/utils/adt/cash.c +++ b/src/backend/utils/adt/cash.c @@ -13,7 +13,7 @@ * 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.82 2009/06/11 14:49:03 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/cash.c,v 1.83 2010/07/16 02:15:53 tgl Exp $ */ #include "postgres.h" @@ -26,6 +26,7 @@ #include "libpq/pqformat.h" #include "utils/builtins.h" #include "utils/cash.h" +#include "utils/numeric.h" #include "utils/pg_locale.h" #define CASH_BUFSZ 36 @@ -114,7 +115,6 @@ cash_in(PG_FUNCTION_ARGS) psymbol; const char *nsymbol, *csymbol; - struct lconv *lconvert = PGLC_localeconv(); /* @@ -263,7 +263,6 @@ cash_out(PG_FUNCTION_ARGS) *nsymbol; char dsymbol; char convention; - struct lconv *lconvert = PGLC_localeconv(); /* see comments about frac_digits in cash_in() */ @@ -478,6 +477,26 @@ cash_mi(PG_FUNCTION_ARGS) } +/* cash_div_cash() + * Divide cash by cash, returning float8. + */ +Datum +cash_div_cash(PG_FUNCTION_ARGS) +{ + Cash dividend = PG_GETARG_CASH(0); + Cash divisor = PG_GETARG_CASH(1); + float8 quotient; + + if (divisor == 0) + ereport(ERROR, + (errcode(ERRCODE_DIVISION_BY_ZERO), + errmsg("division by zero"))); + + quotient = (float8) dividend / (float8) divisor; + PG_RETURN_FLOAT8(quotient); +} + + /* cash_mul_flt8() * Multiply cash by float8. */ @@ -845,3 +864,77 @@ cash_words(PG_FUNCTION_ARGS) /* return as text datum */ PG_RETURN_TEXT_P(cstring_to_text(buf)); } + + +/* cash_numeric() + * Convert cash to numeric. + */ +Datum +cash_numeric(PG_FUNCTION_ARGS) +{ + Cash money = PG_GETARG_CASH(0); + Numeric result; + int fpoint; + int64 scale; + int i; + Datum amount; + Datum numeric_scale; + Datum quotient; + struct lconv *lconvert = PGLC_localeconv(); + + /* see comments about frac_digits in cash_in() */ + fpoint = lconvert->frac_digits; + if (fpoint < 0 || fpoint > 10) + fpoint = 2; + + /* compute required scale factor */ + scale = 1; + for (i = 0; i < fpoint; i++) + scale *= 10; + + /* form the result as money / scale */ + amount = DirectFunctionCall1(int8_numeric, Int64GetDatum(money)); + numeric_scale = DirectFunctionCall1(int8_numeric, Int64GetDatum(scale)); + quotient = DirectFunctionCall2(numeric_div, amount, numeric_scale); + + /* forcibly round to exactly the intended number of digits */ + result = DatumGetNumeric(DirectFunctionCall2(numeric_round, + quotient, + Int32GetDatum(fpoint))); + + PG_RETURN_NUMERIC(result); +} + +/* numeric_cash() + * Convert numeric to cash. + */ +Datum +numeric_cash(PG_FUNCTION_ARGS) +{ + Datum amount = PG_GETARG_DATUM(0); + Cash result; + int fpoint; + int64 scale; + int i; + Datum numeric_scale; + struct lconv *lconvert = PGLC_localeconv(); + + /* see comments about frac_digits in cash_in() */ + fpoint = lconvert->frac_digits; + if (fpoint < 0 || fpoint > 10) + fpoint = 2; + + /* compute required scale factor */ + scale = 1; + for (i = 0; i < fpoint; i++) + scale *= 10; + + /* multiply the input amount by scale factor */ + numeric_scale = DirectFunctionCall1(int8_numeric, Int64GetDatum(scale)); + amount = DirectFunctionCall2(numeric_mul, amount, numeric_scale); + + /* note that numeric_int8 will round to nearest integer for us */ + result = DatumGetInt64(DirectFunctionCall1(numeric_int8, amount)); + + PG_RETURN_CASH(result); +} diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index e901e8fe6e..ac52eb18bc 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.587 2010/04/26 14:22:37 momjian Exp $ + * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.588 2010/07/16 02:15:54 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201004261 +#define CATALOG_VERSION_NO 201007151 #endif diff --git a/src/include/catalog/pg_cast.h b/src/include/catalog/pg_cast.h index 3af28a1bb4..ca8b0b921b 100644 --- a/src/include/catalog/pg_cast.h +++ b/src/include/catalog/pg_cast.h @@ -10,7 +10,7 @@ * * Copyright (c) 2002-2010, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/include/catalog/pg_cast.h,v 1.44 2010/01/05 01:06:56 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_cast.h,v 1.45 2010/07/16 02:15:54 tgl Exp $ * * NOTES * the genbki.pl script reads this file and generates .bki @@ -124,6 +124,8 @@ DATA(insert ( 1700 21 1783 a f )); DATA(insert ( 1700 23 1744 a f )); DATA(insert ( 1700 700 1745 i f )); DATA(insert ( 1700 701 1746 i f )); +DATA(insert ( 790 1700 3823 a f )); +DATA(insert ( 1700 790 3824 a f )); /* Allow explicit coercions between int4 and bool */ DATA(insert ( 23 16 2557 e f )); diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h index 25429cf151..b6c4701c74 100644 --- a/src/include/catalog/pg_operator.h +++ b/src/include/catalog/pg_operator.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_operator.h,v 1.170 2010/01/14 16:31:09 teodor Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_operator.h,v 1.171 2010/07/16 02:15:54 tgl Exp $ * * NOTES * the genbki.pl script reads this file and generates .bki @@ -415,6 +415,7 @@ DATA(insert OID = 915 ( "/" PGNSP PGUID b f f 790 21 790 0 0 cash_div_ DATA(insert OID = 916 ( "*" PGNSP PGUID b f f 701 790 790 908 0 flt8_mul_cash - - )); DATA(insert OID = 917 ( "*" PGNSP PGUID b f f 23 790 790 912 0 int4_mul_cash - - )); DATA(insert OID = 918 ( "*" PGNSP PGUID b f f 21 790 790 914 0 int2_mul_cash - - )); +DATA(insert OID = 3825 ( "/" PGNSP PGUID b f f 790 790 701 0 0 cash_div_cash - - )); DATA(insert OID = 965 ( "^" PGNSP PGUID b f f 701 701 701 0 0 dpow - - )); DATA(insert OID = 966 ( "+" PGNSP PGUID b f f 1034 1033 1034 0 0 aclinsert - - )); diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 8360e317b1..8c498c3178 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.571 2010/05/27 16:20:11 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.572 2010/07/16 02:15:54 tgl Exp $ * * NOTES * The script catalog/genbki.pl reads this file and generates .bki @@ -1165,9 +1165,9 @@ DESCR("multiply"); DATA(insert OID = 867 ( cash_div_int2 PGNSP PGUID 12 1 0 0 f f f t f i 2 0 790 "790 21" _null_ _null_ _null_ _null_ cash_div_int2 _null_ _null_ _null_ )); DESCR("divide"); -DATA(insert OID = 886 ( cash_in PGNSP PGUID 12 1 0 0 f f f t f i 1 0 790 "2275" _null_ _null_ _null_ _null_ cash_in _null_ _null_ _null_ )); +DATA(insert OID = 886 ( cash_in PGNSP PGUID 12 1 0 0 f f f t f s 1 0 790 "2275" _null_ _null_ _null_ _null_ cash_in _null_ _null_ _null_ )); DESCR("I/O"); -DATA(insert OID = 887 ( cash_out PGNSP PGUID 12 1 0 0 f f f t f i 1 0 2275 "790" _null_ _null_ _null_ _null_ cash_out _null_ _null_ _null_ )); +DATA(insert OID = 887 ( cash_out PGNSP PGUID 12 1 0 0 f f f t f s 1 0 2275 "790" _null_ _null_ _null_ _null_ cash_out _null_ _null_ _null_ )); DESCR("I/O"); DATA(insert OID = 888 ( cash_eq PGNSP PGUID 12 1 0 0 f f f t f i 2 0 16 "790 790" _null_ _null_ _null_ _null_ cash_eq _null_ _null_ _null_ )); DESCR("equal"); @@ -1197,6 +1197,12 @@ DATA(insert OID = 919 ( flt8_mul_cash PGNSP PGUID 12 1 0 0 f f f t f i 2 0 DESCR("multiply"); DATA(insert OID = 935 ( cash_words PGNSP PGUID 12 1 0 0 f f f t f i 1 0 25 "790" _null_ _null_ _null_ _null_ cash_words _null_ _null_ _null_ )); DESCR("output amount as words"); +DATA(insert OID = 3822 ( cash_div_cash PGNSP PGUID 12 1 0 0 f f f t f i 2 0 701 "790 790" _null_ _null_ _null_ _null_ cash_div_cash _null_ _null_ _null_ )); +DESCR("divide"); +DATA(insert OID = 3823 ( numeric PGNSP PGUID 12 1 0 0 f f f t f s 1 0 1700 "790" _null_ _null_ _null_ _null_ cash_numeric _null_ _null_ _null_ )); +DESCR("(internal)"); +DATA(insert OID = 3824 ( money PGNSP PGUID 12 1 0 0 f f f t f s 1 0 790 "1700" _null_ _null_ _null_ _null_ numeric_cash _null_ _null_ _null_ )); +DESCR("(internal)"); /* OIDS 900 - 999 */ diff --git a/src/include/utils/cash.h b/src/include/utils/cash.h index 5f6fcdfc0d..af4448c8ac 100644 --- a/src/include/utils/cash.h +++ b/src/include/utils/cash.h @@ -1,5 +1,5 @@ /* - * $PostgreSQL: pgsql/src/include/utils/cash.h,v 1.27 2009/06/11 14:49:13 momjian Exp $ + * $PostgreSQL: pgsql/src/include/utils/cash.h,v 1.28 2010/07/16 02:15:56 tgl Exp $ * * * cash.h @@ -37,6 +37,7 @@ extern Datum cash_cmp(PG_FUNCTION_ARGS); extern Datum cash_pl(PG_FUNCTION_ARGS); extern Datum cash_mi(PG_FUNCTION_ARGS); +extern Datum cash_div_cash(PG_FUNCTION_ARGS); extern Datum cash_mul_flt8(PG_FUNCTION_ARGS); extern Datum flt8_mul_cash(PG_FUNCTION_ARGS); @@ -63,4 +64,7 @@ extern Datum cashsmaller(PG_FUNCTION_ARGS); extern Datum cash_words(PG_FUNCTION_ARGS); +extern Datum cash_numeric(PG_FUNCTION_ARGS); +extern Datum numeric_cash(PG_FUNCTION_ARGS); + #endif /* CASH_H */ -- 2.11.0