dividing money by money

Andy Balholm wrote:

The ability to divide money by money would be useful for finding
what percent one money value is of another.

That certainly sounds useful and natural to me. I don't think it
rises to the level of a *bug*, but it's a reasonable request for
enhancement. If there are no objections I'll add it to the TODO
list.

-Kevin

On 2010-03-30, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:

Andy Balholm wrote:

The ability to divide money by money would be useful for finding
what percent one money value is of another.

That certainly sounds useful and natural to me. I don't think it
rises to the level of a *bug*, but it's a reasonable request for
enhancement. If there are no objections I'll add it to the TODO
list.

That'd also make it easy to convert money to numeric or double by dividing
by '1'::money.

Kevin Grittner �rta:

Andy Balholm wrote:

The ability to divide money by money would be useful for finding
what percent one money value is of another.

That certainly sounds useful and natural to me. I don't think it
rises to the level of a *bug*, but it's a reasonable request for
enhancement. If there are no objections I'll add it to the TODO
list.

How about improving the money type so it can store
values in different currencies?

=# create table money1 (x money);
CREATE TABLE
=# insert into money1 values ('1');
INSERT 0 1
=# select * from money1;
x
--------
Ft1,00
(1 sor)

=# insert into money1 values ('$1');
ERROR: invalid input syntax for type money: "$1"

pg_dump -t money1:

==================
Ft1,00
\.
==================

Loading this dump into another database that happens
to have a different locale than hu_HU fails.

But then any operator between two money values would
only work if both values have the same currency.

Best regards,
Zolt�n B�sz�rm�nyi

--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zolt�n B�sz�rm�nyi
Cybertec Sch�nig & Sch�nig GmbH
http://www.postgresql.at/

Boszormenyi Zoltan wrote:

Loading this dump into another database that happens
to have a different locale than hu_HU fails.

But then any operator between two money values would
only work if both values have the same currency.

indeed, its all a big tarpit. next, you'll want currency conversion
tables. and, are there still any currenccies like old style UK where
the subunits aren't 100ths? schillings or whatever

John R Pierce �rta:

Boszormenyi Zoltan wrote:

Loading this dump into another database that happens
to have a different locale than hu_HU fails.

But then any operator between two money values would
only work if both values have the same currency.

indeed, its all a big tarpit. next, you'll want currency conversion
tables. and, are there still any currenccies like old style UK where
the subunits aren't 100ths? schillings or whatever

Yeah, and we could also switch PG numeric type to base 12
to count in dozens or base 60 to deal with the mayan calendar... ;-)

--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zolt�n B�sz�rm�nyi
Cybertec Sch�nig & Sch�nig GmbH
http://www.postgresql.at/

Boszormenyi Zoltan wrote:

How about improving the money type so it can store
values in different currencies?

Have you seen "taggedtypes"?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

John R Pierce <pierce@hogranch.com> wrote:

Boszormenyi Zoltan wrote:

But then any operator between two money values would
only work if both values have the same currency.

That sounds like a sane limitation.

and, are there still any currenccies like old style UK where
the subunits aren't 100ths? schillings or whatever

I'm not sure if you're arguing for or against the database type
knowing how to divide those to get a percentage, versus putting the
onus on the application programmer. Where does it make the most
sense to you to put such logic?

-Kevin

Forgot to send to the list....

On Tue, Mar 30, 2010 at 8:25 AM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:

John R Pierce <pierce@hogranch.com> wrote:

Boszormenyi Zoltan wrote:

But then any operator between two money values would
only work if both values have the same currency.

That sounds like a sane limitation.

and, are there still any currenccies like old style UK where
the subunits aren't 100ths?   schillings or whatever

I'm not sure if you're arguing for or against the database type
knowing how to divide those to get a percentage, versus putting the
onus on the application programmer.  Where does it make the most
sense to you to put such logic?

With due respect, this sort of thing is rather difficult to get right
all at once.  I would suggest at some point having a modified MONEY or
maybe to avoid conflicts let's call it a CURRENCY datatype on
Pg-foundry where we can experiment and get these details right.  I am
thinking of doing a rough draft in SQL and PLPGSQL so that someone can
convert to C once everything works properly :-).

If folks are interested, I might make a simple approximation of this
that would require 8.4 or higher.  It might come in handy for
LedgerSMB too.....

Best Wishes,
Chris Travers

Chris Travers <chris@metatrontech.com> wrote:

With due respect, this sort of thing is rather difficult to get
right all at once.

The existing type is fixed point and we know how to add and subtract
two of them. I don't think it's all that difficult to add division,
yielding some non-money numeric type (like perhaps float8).

Neither do I see it as a particularly slippery slope. If someone
has a list of other things they want to do for monetary types -- I
don't see that it has anything to do with this particular request,
unless some convincing argument can be made that adding this would
make the other features harder to implement.

Do we have to take a simple request for something useful and blow it
up into something grand and complicated? Well, I mean, *every*
time? ;-) The grander aspects of this thread would make more sense
as a separate thread for a longer-term effort, probably for a new
type (or set of types).

-Kevin

I wrote:

yielding some non-money numeric type (like perhaps float8).

Hmmm... Given that we've already had a couple posts on the idea
that dividing by '1'::money could convert money to something more
general, I guess it would be safer to stick to numeric.

-Kevin

On Tue, Mar 30, 2010 at 12:22 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:

[Did you mean to take this off-list, or was that accidental?]

Accidental.

Chris Travers <chris@metatrontech.com> wrote:

With due respect, this sort of thing is rather difficult to get
right all at once.

Division of two fixed-point numbers we already know how to add, or
the whole suite of all features one might possibly want in a
monetary data type?  (I get the feeling that some of the posts on
this thread involve a straw man going down a slippery slope.... ;-)

Ok. Here is my application: I write a multi-currency accounting
program backed by PostgreSQL. After 1.3 is released (2Q this year),
we expect to be doing a full redesign.

What I am thinking about is having a custom data type, something like:

CREATE DOMAIN curr VARCHAR(3);
CREATE TYPE monetary AS (amount NUMERIC, currency CURR, multiplier
NUMERIC); This reduces into two basic components: a value (amount *
multiplier) and a currency identifier (USD, etc).

One could also then store monetary[] arrays for addressing specific
denomination storage. I.e. "When closing the till we had 26 pennies,
53 nickles, 12 quarters, 25 $1 bills, 35 $5 bills, 15 $10 bills, and 5
$20 bills."

Then we can allow NUMERIC arithmetic on monetary amounts provided that
the CURR field is the same. We could also store things like the cash
counted from a till at the end of the day by denomination. One could
have easy monetary::numeric casts as well.

Anyway, that's my basic thinking. One could further add currency
conversion tables to an application if necessary.

Just thinking about the more general problem and how things could be
handled more gracefully...

Best Wishes,
Chris Travers

Chris Travers <chris@metatrontech.com> wrote:

Just thinking about the more general problem and how things could
be handled more gracefully...

Sure, but in the meantime, consider:

test=# select '12'::money * '2'::numeric;
?column?
----------
$24.00
(1 row)

test=# select '24'::money / '2'::numeric;
?column?
----------
$12.00
(1 row)

test=# select '24'::money / '12'::money;
ERROR: operator does not exist: money / money
LINE 1: select '24'::money / '12'::money;
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.

So we support:

a * b = c
c / b = a

but don't even *think* about c / a = b ???

The OP just wanted to add some symmetry to this, so that the
existing class could handle a not-uncommon use case more easily. As
far as I can see, the implementation of this operator could convert
two int64 values to numeric values and perform numeric division to
get the result. (I was going to mark the TODO as an easy one.) I
don't see how this change would affect what you want to do, one way
or the other.

-Kevin

On Mar 31, 2010, at 7:07 AM, Kevin Grittner wrote:

(I was going to mark the TODO as an easy one.)

I thought it would be pretty simple, too, so I decided to go ahead and write and test it as an external module.

I think the function definition could be pasted directly into an appropriate place in src/backend/utils/adt/cash.c, if someone wants to add it to the main code base. The SQL to load it would need to be modified somewhat to fit into postgres.bki.

Here is the C source:

#include <postgres.h>
#include <fmgr.h>
#include <utils/cash.h>

PG_MODULE_MAGIC;

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);
}

-------------------------------------------------------------------------------------

And here is the SQL to load it (assuming it has been compiled as a dynamically loadable module named divide_money and placed in the library directory on the server):

CREATE FUNCTION cash_div_cash(money, money) RETURNS double precision
LANGUAGE c IMMUTABLE
AS '$libdir/divide_money', 'cash_div_cash';

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

Andy Balholm <andy@balholm.com> wrote:

quotient = (float8)dividend / (float8)divisor;
PG_RETURN_FLOAT8(quotient);

That was my first inclination, but the fact that two different
people talked about using division by '1'::money as a way to convert
money to another type has me nervous about using an approximate
type. Any chance you could rework it using numeric? I know it's
less trivial that way, but unless we provide a cast to numeric, I'm
afraid people will use the above trick, assign it to a numeric
variable or column, and then wonder why they've lost precision.

Or I guess we could leave this as you've written it and add support
for a cast from money to numeric.

-Kevin

On Mar 31, 2010, at 11:01 AM, Kevin Grittner wrote:

That was my first inclination, but the fact that two different
people talked about using division by '1'::money as a way to convert
money to another type has me nervous about using an approximate
type. Any chance you could rework it using numeric? I know it's
less trivial that way, but unless we provide a cast to numeric, I'm
afraid people will use the above trick, assign it to a numeric
variable or column, and then wonder why they've lost precision.

Or I guess we could leave this as you've written it and add support
for a cast from money to numeric.

It probably is wiser to rewrite it with the numeric type. A cast from money to numeric is theoretically ambiguous (the result could be either dollars or cents), although most people would expect dollars.

I'll see if I can rewrite it with a return type of numeric.

Or I guess we could leave this as you've written it and add support
for a cast from money to numeric.

I tried rewriting my function to use numeric, but I discovered that numeric division is not exact. (Otherwise SELECT 1::numeric / 3::numeric would result in an infinite loop.) So I went back to my float8 version and wrote a cast from money to numeric.

Here is my C source code now:

#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);

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);
}

------------------------------------------------------------------------------------

And here is the SQL it takes 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 OPERATOR / (
PROCEDURE = cash_div_cash,
LEFTARG = money,
RIGHTARG = money
);

CREATE CAST (money AS numeric) WITH FUNCTION cash_numeric(money) AS ASSIGNMENT;

Andy Balholm <andy@balholm.com> writes:

I tried rewriting my function to use numeric, but I discovered that numeric division is not exact. (Otherwise SELECT 1::numeric / 3::numeric would result in an infinite loop.) So I went back to my float8 version and wrote a cast from money to numeric.

That's hardly an improvement if you're concerned about lack of
exactness.

regards, tom lane

That's hardly an improvement if you're concerned about lack of
exactness.

I know; I lose a couple of digits by using float8 instead of numeric, but it's much simpler and faster, and if it returned numeric people would _think_ it was exact.

And if we have a cast to numeric, people who want those extra digits can cast to numeric before dividing.

But I do still have the numeric code that I tried, so if that's how people want to do it, I can provide it.

Andy Balholm <andy@balholm.com> wrote:

That's hardly an improvement if you're concerned about lack of
exactness.

I know; I lose a couple of digits by using float8 instead of
numeric, but it's much simpler and faster

It also has the advantage of being symmetrical with the other
operators.

and if it returned numeric people would _think_ it was exact.

Well, I don't know how many people would expect an *exact* decimal
representation of dividing a number by three. The case which had me
concerned was specifically division by one as a "back door" cast.
With numeric we could guarantee *that* was exact.

And if we have a cast to numeric, people who want those extra
digits can cast to numeric before dividing.

And nobody has much reason to do the divide-by-one trick.

But I do still have the numeric code that I tried, so if that's
how people want to do it, I can provide it.

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.

-Kevin