Re: dividing money by money - Mailing list 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 =3D PG_GETARG_CASH(0);
    Cash    divisor  =3D PG_GETARG_CASH(1);
    float8  quotient;
=20=20=20=20
    if (divisor =3D=3D 0)
        ereport(ERROR,
                (errcode(ERRCODE_DIVISION_BY_ZERO),
                 errmsg("division by zero")));
=20=20=20=20
    quotient =3D (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 =3D PG_GETARG_CASH(0);
    int     fpoint;
    int64   scale;
    int     i;
    Numeric result;
    Datum   amount;
    Datum   numeric_scale;
    Datum   one;
=20=20=20=20
    struct lconv *lconvert =3D PGLC_localeconv();
=20=20=20=20
    /*=20
     * Find the number of digits after the decimal point.
     * (These lines were copied from cash_in().)
     */
    fpoint =3D lconvert->frac_digits;
    if (fpoint < 0 || fpoint > 10)
        fpoint =3D 2;
    scale =3D 1;
    for (i =3D 0; i < fpoint; i++)=20
        scale *=3D 10;
=20=20=20=20
    amount        =3D DirectFunctionCall1(&int8_numeric, Int64GetDatum(mone=
y));
    one           =3D DirectFunctionCall1(&int8_numeric, Int64GetDatum(1));
    numeric_scale =3D DirectFunctionCall1(&int8_numeric, Int64GetDatum(scal=
e));
    numeric_scale =3D DirectFunctionCall2(&numeric_div, one, numeric_scale);
    result =3D DatumGetNumeric(DirectFunctionCall2(&numeric_mul, amount, nu=
meric_scale));
=20=20=20=20
    result->n_sign_dscale =3D NUMERIC_SIGN(result) | fpoint; /* Display the=
 right number of decimal digits. */
=20=20=20=20
    PG_RETURN_NUMERIC(result);
}

PG_FUNCTION_INFO_V1(numeric_cash);

/* numeric_cash()
 * Convert numeric to cash.
 */
Datum
numeric_cash(PG_FUNCTION_ARGS)
{
    Datum   amount =3D PG_GETARG_DATUM(0);
    Cash    result;
    int     fpoint;
    int64   scale;
    int     i;
    Datum   numeric_scale;
=20=20=20=20
    struct lconv *lconvert =3D PGLC_localeconv();
=20=20=20=20
    /*=20
     * Find the number of digits after the decimal point.
     */
    fpoint =3D lconvert->frac_digits;
    if (fpoint < 0 || fpoint > 10)
        fpoint =3D 2;
    scale =3D 1;
    for (i =3D 0; i < fpoint; i++)=20
        scale *=3D 10;
=20=20=20=20
    numeric_scale =3D DirectFunctionCall1(&int8_numeric, Int64GetDatum(scal=
e));
    amount =3D DirectFunctionCall2(&numeric_mul, amount, numeric_scale);
    amount =3D DirectFunctionCall1(&numeric_int8, amount);
=20=20=20=20
    result =3D 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) A=
S ASSIGNMENT;

CREATE OPERATOR / (
    PROCEDURE =3D cash_div_cash,
    LEFTARG =3D money,
    RIGHTARG =3D money
);