Add support for dividing money by money (yielding a float8 result) and for

casting between money and numeric.

Andy Balholm, reviewed by Kevin Grittner

6 files changed, 117 insertions, 11 deletions

diff --git a/src/backend/utils/adt/cash.c b/src/backend/utils/adt/cash.c
index 784947e4405..c33c7cdaaea 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 e901e8fe6e1..ac52eb18bc7 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 3af28a1bb4f..ca8b0b921bf 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 25429cf1515..b6c4701c744 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 8360e317b1a..8c498c3178c 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 5f6fcdfc0d3..af4448c8ac5 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 */