Skip to content

Commit 7590ddb

Browse files
tglsfdctglsfdc
committed
Add support for dividing money by money (yielding a float8 result) and for
casting between money and numeric. Andy Balholm, reviewed by Kevin Grittner
1 parent e11cfa8 commit 7590ddb

File tree

7 files changed

+148
-34
lines changed

7 files changed

+148
-34
lines changed

‎doc/src/sgml/datatype.sgml

Lines changed: 31 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.250 2010/07/03 04:03:06 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.251 2010/07/16 02:15:53 tgl Exp $ -->
22

33
<chapter id="datatype">
44
<title>Data Types</title>
@@ -839,32 +839,11 @@ ALTER SEQUENCE <replaceable class="parameter">tablename</replaceable>_<replaceab
839839
fractional precision; see <xref
840840
linkend="datatype-money-table">. The fractional precision is
841841
determined by the database's <xref linkend="guc-lc-monetary"> setting.
842+
The range shown in the table assumes there are two fractional digits.
842843
Input is accepted in a variety of formats, including integer and
843844
floating-point literals, as well as typical
844845
currency formatting, such as <literal>'$1,000.00'</literal>.
845846
Output is generally in the latter form but depends on the locale.
846-
Non-quoted numeric values can be converted to <type>money</type> by
847-
casting the numeric value to <type>text</type> and then
848-
<type>money</type>, for example:
849-
<programlisting>
850-
SELECT 1234::text::money;
851-
</programlisting>
852-
There is no simple way of doing the reverse in a locale-independent
853-
manner, namely casting a <type>money</type> value to a numeric type.
854-
If you know the currency symbol and thousands separator you can use
855-
<function>regexp_replace()</>:
856-
<programlisting>
857-
SELECT regexp_replace('52093.89'::money::text, '[$,]', '', 'g')::numeric;
858-
</programlisting>
859-
860-
</para>
861-
862-
<para>
863-
Since the output of this data type is locale-sensitive, it might not
864-
work to load <type>money</> data into a database that has a different
865-
setting of <varname>lc_monetary</>. To avoid problems, before
866-
restoring a dump into a new database make sure <varname>lc_monetary</> has the same or
867-
equivalent value as in the database that was dumped.
868847
</para>
869848

870849
<table id="datatype-money-table">
@@ -888,6 +867,35 @@ SELECT regexp_replace('52093.89'::money::text, '[$,]', '', 'g')::numeric;
888867
</tbody>
889868
</tgroup>
890869
</table>
870+
871+
<para>
872+
Since the output of this data type is locale-sensitive, it might not
873+
work to load <type>money</> data into a database that has a different
874+
setting of <varname>lc_monetary</>. To avoid problems, before
875+
restoring a dump into a new database make sure <varname>lc_monetary</> has
876+
the same or equivalent value as in the database that was dumped.
877+
</para>
878+
879+
<para>
880+
Values of the <type>numeric</type> data type can be cast to
881+
<type>money</type>. Other numeric types can be converted to
882+
<type>money</type> by casting to <type>numeric</type> first, for example:
883+
<programlisting>
884+
SELECT 1234::numeric::money;
885+
</programlisting>
886+
A <type>money</type> value can be cast to <type>numeric</type> without
887+
loss of precision. Conversion to other types could potentially lose
888+
precision, and it must be done in two stages, for example:
889+
<programlisting>
890+
SELECT '52093.89'::money::numeric::float8;
891+
</programlisting>
892+
</para>
893+
894+
<para>
895+
When a <type>money</type> value is divided by another <type>money</type>
896+
value, the result is <type>double precision</type> (i.e., a pure number,
897+
not money); the currency units cancel each other out in the division.
898+
</para>
891899
</sect1>
892900

893901

‎src/backend/utils/adt/cash.c

Lines changed: 96 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
* this version handles 64 bit numbers and so can hold values up to
1414
* $92,233,720,368,547,758.07.
1515
*
16-
* $PostgreSQL: pgsql/src/backend/utils/adt/cash.c,v 1.82 2009/06/11 14:49:03 momjian Exp $
16+
* $PostgreSQL: pgsql/src/backend/utils/adt/cash.c,v 1.83 2010/07/16 02:15:53 tgl Exp $
1717
*/
1818

1919
#include "postgres.h"
@@ -26,6 +26,7 @@
2626
#include "libpq/pqformat.h"
2727
#include "utils/builtins.h"
2828
#include "utils/cash.h"
29+
#include "utils/numeric.h"
2930
#include "utils/pg_locale.h"
3031

3132
#define CASH_BUFSZ 36
@@ -114,7 +115,6 @@ cash_in(PG_FUNCTION_ARGS)
114115
psymbol;
115116
const char *nsymbol,
116117
*csymbol;
117-
118118
struct lconv *lconvert = PGLC_localeconv();
119119

120120
/*
@@ -263,7 +263,6 @@ cash_out(PG_FUNCTION_ARGS)
263263
*nsymbol;
264264
char dsymbol;
265265
char convention;
266-
267266
struct lconv *lconvert = PGLC_localeconv();
268267

269268
/* see comments about frac_digits in cash_in() */
@@ -478,6 +477,26 @@ cash_mi(PG_FUNCTION_ARGS)
478477
}
479478

480479

480+
/* cash_div_cash()
481+
* Divide cash by cash, returning float8.
482+
*/
483+
Datum
484+
cash_div_cash(PG_FUNCTION_ARGS)
485+
{
486+
Cash dividend = PG_GETARG_CASH(0);
487+
Cash divisor = PG_GETARG_CASH(1);
488+
float8 quotient;
489+
490+
if (divisor == 0)
491+
ereport(ERROR,
492+
(errcode(ERRCODE_DIVISION_BY_ZERO),
493+
errmsg("division by zero")));
494+
495+
quotient = (float8) dividend / (float8) divisor;
496+
PG_RETURN_FLOAT8(quotient);
497+
}
498+
499+
481500
/* cash_mul_flt8()
482501
* Multiply cash by float8.
483502
*/
@@ -845,3 +864,77 @@ cash_words(PG_FUNCTION_ARGS)
845864
/* return as text datum */
846865
PG_RETURN_TEXT_P(cstring_to_text(buf));
847866
}
867+
868+
869+
/* cash_numeric()
870+
* Convert cash to numeric.
871+
*/
872+
Datum
873+
cash_numeric(PG_FUNCTION_ARGS)
874+
{
875+
Cash money = PG_GETARG_CASH(0);
876+
Numeric result;
877+
int fpoint;
878+
int64 scale;
879+
int i;
880+
Datum amount;
881+
Datum numeric_scale;
882+
Datum quotient;
883+
struct lconv *lconvert = PGLC_localeconv();
884+
885+
/* see comments about frac_digits in cash_in() */
886+
fpoint = lconvert->frac_digits;
887+
if (fpoint < 0 || fpoint > 10)
888+
fpoint = 2;
889+
890+
/* compute required scale factor */
891+
scale = 1;
892+
for (i = 0; i < fpoint; i++)
893+
scale *= 10;
894+
895+
/* form the result as money / scale */
896+
amount = DirectFunctionCall1(int8_numeric, Int64GetDatum(money));
897+
numeric_scale = DirectFunctionCall1(int8_numeric, Int64GetDatum(scale));
898+
quotient = DirectFunctionCall2(numeric_div, amount, numeric_scale);
899+
900+
/* forcibly round to exactly the intended number of digits */
901+
result = DatumGetNumeric(DirectFunctionCall2(numeric_round,
902+
quotient,
903+
Int32GetDatum(fpoint)));
904+
905+
PG_RETURN_NUMERIC(result);
906+
}
907+
908+
/* numeric_cash()
909+
* Convert numeric to cash.
910+
*/
911+
Datum
912+
numeric_cash(PG_FUNCTION_ARGS)
913+
{
914+
Datum amount = PG_GETARG_DATUM(0);
915+
Cash result;
916+
int fpoint;
917+
int64 scale;
918+
int i;
919+
Datum numeric_scale;
920+
struct lconv *lconvert = PGLC_localeconv();
921+
922+
/* see comments about frac_digits in cash_in() */
923+
fpoint = lconvert->frac_digits;
924+
if (fpoint < 0 || fpoint > 10)
925+
fpoint = 2;
926+
927+
/* compute required scale factor */
928+
scale = 1;
929+
for (i = 0; i < fpoint; i++)
930+
scale *= 10;
931+
932+
/* multiply the input amount by scale factor */
933+
numeric_scale = DirectFunctionCall1(int8_numeric, Int64GetDatum(scale));
934+
amount = DirectFunctionCall2(numeric_mul, amount, numeric_scale);
935+
936+
/* note that numeric_int8 will round to nearest integer for us */
937+
result = DatumGetInt64(DirectFunctionCall1(numeric_int8, amount));
938+
939+
PG_RETURN_CASH(result);
940+
}

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.587 2010/04/26 14:22:37 momjian Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.588 2010/07/16 02:15:54 tgl Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201004261
56+
#define CATALOG_VERSION_NO 201007151
5757

5858
#endif

‎src/include/catalog/pg_cast.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
*
1111
* Copyright (c) 2002-2010, PostgreSQL Global Development Group
1212
*
13-
* $PostgreSQL: pgsql/src/include/catalog/pg_cast.h,v 1.44 2010/01/05 01:06:56 tgl Exp $
13+
* $PostgreSQL: pgsql/src/include/catalog/pg_cast.h,v 1.45 2010/07/16 02:15:54 tgl Exp $
1414
*
1515
* NOTES
1616
* the genbki.pl script reads this file and generates .bki
@@ -124,6 +124,8 @@ DATA(insert ( 1700 21 1783 a f ));
124124
DATA(insert ( 1700 23 1744 a f ));
125125
DATA(insert ( 1700 700 1745 i f ));
126126
DATA(insert ( 1700 701 1746 i f ));
127+
DATA(insert ( 790 1700 3823 a f ));
128+
DATA(insert ( 1700 790 3824 a f ));
127129

128130
/* Allow explicit coercions between int4 and bool */
129131
DATA(insert ( 23 16 2557 e f ));

‎src/include/catalog/pg_operator.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
99
* Portions Copyright (c) 1994, Regents of the University of California
1010
*
11-
* $PostgreSQL: pgsql/src/include/catalog/pg_operator.h,v 1.170 2010/01/14 16:31:09 teodor Exp $
11+
* $PostgreSQL: pgsql/src/include/catalog/pg_operator.h,v 1.171 2010/07/16 02:15:54 tgl Exp $
1212
*
1313
* NOTES
1414
* 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_
415415
DATA(insert OID = 916 ( "*" PGNSP PGUID b f f 701 790 790 908 0 flt8_mul_cash - - ));
416416
DATA(insert OID = 917 ( "*" PGNSP PGUID b f f 23 790 790 912 0 int4_mul_cash - - ));
417417
DATA(insert OID = 918 ( "*" PGNSP PGUID b f f 21 790 790 914 0 int2_mul_cash - - ));
418+
DATA(insert OID = 3825 ( "/" PGNSP PGUID b f f 790 790 701 0 0 cash_div_cash - - ));
418419

419420
DATA(insert OID = 965 ( "^" PGNSP PGUID b f f 701 701 701 0 0 dpow - - ));
420421
DATA(insert OID = 966 ( "+" PGNSP PGUID b f f 1034 1033 1034 0 0 aclinsert - - ));

‎src/include/catalog/pg_proc.h

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.571 2010/05/27 16:20:11 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.572 2010/07/16 02:15:54 tgl Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.pl reads this file and generates .bki
@@ -1165,9 +1165,9 @@ DESCR("multiply");
11651165
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_ ));
11661166
DESCR("divide");
11671167

1168-
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_ ));
1168+
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_ ));
11691169
DESCR("I/O");
1170-
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_ ));
1170+
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_ ));
11711171
DESCR("I/O");
11721172
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_ ));
11731173
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
11971197
DESCR("multiply");
11981198
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_ ));
11991199
DESCR("output amount as words");
1200+
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_ ));
1201+
DESCR("divide");
1202+
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_ ));
1203+
DESCR("(internal)");
1204+
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_ ));
1205+
DESCR("(internal)");
12001206

12011207
/* OIDS 900 - 999 */
12021208

‎src/include/utils/cash.h

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
/*
2-
* $PostgreSQL: pgsql/src/include/utils/cash.h,v 1.27 2009/06/11 14:49:13 momjian Exp $
2+
* $PostgreSQL: pgsql/src/include/utils/cash.h,v 1.28 2010/07/16 02:15:56 tgl Exp $
33
*
44
*
55
* cash.h
@@ -37,6 +37,7 @@ extern Datum cash_cmp(PG_FUNCTION_ARGS);
3737

3838
extern Datum cash_pl(PG_FUNCTION_ARGS);
3939
extern Datum cash_mi(PG_FUNCTION_ARGS);
40+
extern Datum cash_div_cash(PG_FUNCTION_ARGS);
4041

4142
extern Datum cash_mul_flt8(PG_FUNCTION_ARGS);
4243
extern Datum flt8_mul_cash(PG_FUNCTION_ARGS);
@@ -63,4 +64,7 @@ extern Datum cashsmaller(PG_FUNCTION_ARGS);
6364

6465
extern Datum cash_words(PG_FUNCTION_ARGS);
6566

67+
extern Datum cash_numeric(PG_FUNCTION_ARGS);
68+
extern Datum numeric_cash(PG_FUNCTION_ARGS);
69+
6670
#endif /* CASH_H */

0 commit comments

Comments
 (0)