dividing-money.diff

*** ./doc/src/sgml/datatype.sgml.orig	2010-05-31 14:51:02.000000000 -0700
--- ./doc/src/sgml/datatype.sgml	2010-05-31 14:54:02.000000000 -0700
***************
*** 843,863 ****
      floating-point literals, as well as typical
      currency formatting, such as <literal>'$1,000.00'</literal>.
      Output is generally in the latter form but depends on the locale.
!     Non-quoted numeric values can be converted to <type>money</type> by
!     casting the numeric value to <type>text</type> and then
!     <type>money</type>, for example:
! <programlisting>
! SELECT 1234::text::money;
! </programlisting>
!     There is no simple way of doing the reverse in a locale-independent
!     manner, namely casting a <type>money</type> value to a numeric type.
!     If you know the currency symbol and thousands separator you can use
!     <function>regexp_replace()</>:
  <programlisting>
! SELECT regexp_replace('52093.89'::money::text, '[$,]', '', 'g')::numeric;
  </programlisting>
  
     </para>
  
     <para>
      Since the output of this data type is locale-sensitive, it might not
--- 843,871 ----
      floating-point literals, as well as typical
      currency formatting, such as <literal>'$1,000.00'</literal>.
      Output is generally in the latter form but depends on the locale.
!    </para>
!    
!    <para>
!     Values of the <type>numeric</type> data type can be cast to <type>money</type>.
! 	Other numeric types can be converted to <type>money</type> by casting to
! 	<type>numeric</type> first, for example: 
! <programlisting>
! SELECT 1234::numeric::money;
! </programlisting>
! 	A <type>money</type> value can be cast to <type>numeric</type> without
! 	loss of precision. Conversion to other types could potentially lose precision,
! 	and it must be done in two stages:
  <programlisting>
! SELECT '52093.89'::money::numeric::float;
  </programlisting>
  
     </para>
+    
+    <para>
+     When a <type>money</type> value is divided by another <type>money</type> value,
+ 	the result is <type>double precision</type> (i.e. a pure number, not money);
+ 	the currency units cancel each other out in the division. 
+    </para>
  
     <para>
      Since the output of this data type is locale-sensitive, it might not
*** ./src/backend/utils/adt/cash.c.orig	2010-05-31 14:51:28.000000000 -0700
--- ./src/backend/utils/adt/cash.c	2010-05-31 14:54:02.000000000 -0700
***************
*** 27,32 ****
--- 27,33 ----
  #include "utils/builtins.h"
  #include "utils/cash.h"
  #include "utils/pg_locale.h"
+ #include "utils/numeric.h"
  
  #define CASH_BUFSZ		36
  
***************
*** 845,847 ****
--- 846,944 ----
  	/* return as text datum */
  	PG_RETURN_TEXT_P(cstring_to_text(buf));
  }
+ 
+ /*
+  * The functions cash_div_cash(), cash_numeric(), and numeric_cash()
+  * were written by Andy Balholm <andy@balholm.com>.
+  */
+ 
+ /* 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_numeric()
+  * Convert cash to numeric.
+  */
+ Datum
+ cash_numeric(PG_FUNCTION_ARGS)
+ {
+     Cash    money = PG_GETARG_CASH(0);
+     int     fpoint;
+     int64   scale;
+     int     i;
+     Numeric result;
+     Datum   amount;
+     Datum   numeric_scale;
+     Datum   one;
+     
+     struct lconv *lconvert = PGLC_localeconv();
+     
+     /* 
+      * Find the number of digits after the decimal point.
+      * (These lines were copied from cash_in().)
+      */
+     fpoint = lconvert->frac_digits;
+ 	if (fpoint < 0 || fpoint > 10)
+ 		fpoint = 2;
+     scale = 1;
+     for (i = 0; i < fpoint; i++) 
+         scale *= 10;
+     
+     amount        = DirectFunctionCall1(&int8_numeric, Int64GetDatum(money));
+     one           = DirectFunctionCall1(&int8_numeric, Int64GetDatum(1));
+     numeric_scale = DirectFunctionCall1(&int8_numeric, Int64GetDatum(scale));
+     numeric_scale = DirectFunctionCall2(&numeric_div, one, numeric_scale);
+     result = DatumGetNumeric(DirectFunctionCall2(&numeric_mul, amount, numeric_scale));
+     
+     result->n_sign_dscale = NUMERIC_SIGN(result) | fpoint; /* Display the right number of decimal digits. */
+     
+     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();
+     
+     /* 
+      * Find the number of digits after the decimal point.
+      */
+     fpoint = lconvert->frac_digits;
+ 	if (fpoint < 0 || fpoint > 10)
+ 		fpoint = 2;
+     scale = 1;
+     for (i = 0; i < fpoint; i++) 
+         scale *= 10;
+     
+     numeric_scale = DirectFunctionCall1(&int8_numeric, Int64GetDatum(scale));
+     amount = DirectFunctionCall2(&numeric_mul, amount, numeric_scale);
+     amount = DirectFunctionCall1(&numeric_int8, amount);
+     
+     result = DatumGetInt64(amount);
+     PG_RETURN_CASH(result);
+ }
*** ./src/include/catalog/pg_cast.h.orig	2010-05-31 14:52:30.000000000 -0700
--- ./src/include/catalog/pg_cast.h	2010-05-31 14:54:02.000000000 -0700
***************
*** 124,129 ****
--- 124,131 ----
  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 ));
*** ./src/include/catalog/pg_operator.h.orig	2010-05-31 14:52:30.000000000 -0700
--- ./src/include/catalog/pg_operator.h	2010-05-31 14:54:02.000000000 -0700
***************
*** 943,948 ****
--- 943,951 ----
  DATA(insert OID = 2992 (  "<="	   PGNSP PGUID b f f 2249 2249 16 2993 2991 record_le scalarltsel scalarltjoinsel ));
  DATA(insert OID = 2993 (  ">="	   PGNSP PGUID b f f 2249 2249 16 2992 2990 record_ge scalargtsel scalargtjoinsel ));
  
+ /* enhancement to money type */
+ DATA(insert OID = 3825 (  "/"	   PGNSP PGUID b f f 790  790	701   0   0 cash_div_cash - - ));
+ 
  
  /*
   * function prototypes
*** ./src/include/catalog/pg_proc.h.orig	2010-05-31 14:52:30.000000000 -0700
--- ./src/include/catalog/pg_proc.h	2010-05-31 14:54:02.000000000 -0700
***************
*** 4778,4783 ****
--- 4778,4791 ----
  DATA(insert OID = 3114 (  nth_value		PGNSP PGUID 12 1 0 0 f t f t f i 2 0 2283 "2283 23" _null_ _null_ _null_ _null_ window_nth_value _null_ _null_ _null_ ));
  DESCR("fetch the Nth row value");
  
+ /* enhancements to money type */
+ 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 i 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 i 1 0 790 "1700" _null_ _null_ _null_ _null_ numeric_cash _null_ _null_ _null_ ));
+ DESCR("(internal)");
+ 
  
  /*
   * Symbolic values for provolatile column: these indicate whether the result
*** ./src/include/utils/cash.h.orig	2010-05-31 14:52:44.000000000 -0700
--- ./src/include/utils/cash.h	2010-05-31 14:54:02.000000000 -0700
***************
*** 63,66 ****
--- 63,70 ----
  
  extern Datum cash_words(PG_FUNCTION_ARGS);
  
+ extern Datum cash_div_cash(PG_FUNCTION_ARGS);
+ extern Datum cash_numeric(PG_FUNCTION_ARGS);
+ extern Datum numeric_cash(PG_FUNCTION_ARGS);
+ 
  #endif   /* CASH_H */