[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [HACKERS] dividing money by money
From: Andy Balholm <andy () balholm ! com>
Date: 2010-05-31 21:59:06
Message-ID: 78267BAD-AA50-4792-A414-8E78BE5F5589 () balholm ! com
[Download RAW message or body]
On May 30, 2010, at 6:53 AM, Kevin Grittner wrote:
> You would then generate a diff in context format and post to the
> -hackers list with that file as an attachment.
Here it is:
["dividing-money.diff" (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 */
> Don't forget to add
> it to the "CommitFest" page:
>
> https://commitfest.postgresql.org/action/commitfest_view/open
I can't add it to the CommitFest page, since I don't have web access, just e-mail. \
Could you please take care of that part? (What is the CommitFest page, anyway?)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic