From: | Andy Balholm <andy(at)balholm(dot)com> |
---|---|
To: | Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> |
Cc: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: dividing money by money |
Date: | 2010-04-01 16:34:27 |
Message-ID: | A4F106D4-5D4D-49B1-B8D3-8102B62158A5@balholm.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Apr 1, 2010, at 7:57 AM, Kevin Grittner wrote:
> I'm inclined to think it's better to have an explicit cast from
> money to numeric, as long as it is exact, and leave the division of
> money by money as float8. It does sort of beg the question of
> whether we should support a cast back in the other direction,
> though. I think that would wrap this all up in a tidy package.
OK. Here is the whole thing in C:
#include <postgres.h>
#include <fmgr.h>
#include <utils/cash.h>
#include <utils/numeric.h>
#include <utils/pg_locale.h>
PG_MODULE_MAGIC;
extern Datum int8_numeric(PG_FUNCTION_ARGS);
extern Datum numeric_div(PG_FUNCTION_ARGS);
extern Datum numeric_mul(PG_FUNCTION_ARGS);
extern Datum numeric_int8(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(cash_div_cash);
/* 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);
}
PG_FUNCTION_INFO_V1(cash_numeric);
/* 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);
}
PG_FUNCTION_INFO_V1(numeric_cash);
/* 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);
}
--------------------------------------------------------------------------------------
And the SQL to load it:
CREATE FUNCTION cash_div_cash(money, money) RETURNS double precision
LANGUAGE c IMMUTABLE STRICT
AS '$libdir/divide_money', 'cash_div_cash';
CREATE FUNCTION cash_numeric(money) RETURNS numeric
LANGUAGE c IMMUTABLE STRICT
AS '$libdir/divide_money', 'cash_numeric';
CREATE FUNCTION numeric_cash(numeric) RETURNS money
LANGUAGE c IMMUTABLE STRICT
AS '$libdir/divide_money', 'numeric_cash';
CREATE CAST (money AS numeric) WITH FUNCTION public.cash_numeric(money) AS ASSIGNMENT;
CREATE CAST (numeric AS money) WITH FUNCTION public.numeric_cash(numeric) AS ASSIGNMENT;
CREATE OPERATOR / (
PROCEDURE = cash_div_cash,
LEFTARG = money,
RIGHTARG = money
);
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2010-04-01 17:08:36 | Re: dividing money by money |
Previous Message | Kevin Grittner | 2010-04-01 14:57:40 | Re: dividing money by money |