Обсуждение: dividing money by money

When I divide a money value by another money value, I get an error message,=
 as follows:

***************************
psql (8.4.1)
Type "help" for help.

postgres=3D# select version();
                                                               version=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20
---------------------------------------------------------------------------=
-----------------------------------------------------------
 PostgreSQL 8.4.1 on i386-apple-darwin9.8.0, compiled by GCC i686-apple-dar=
win9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5490), 32-bit
(1 row)

postgres=3D# select '$2'::money / '$1'::money;
ERROR:  operator does not exist: money / money
LINE 1: select '$2'::money / '$1'::money;
                           ^
HINT:  No operator matches the given name and argument type(s). You might n=
eed to add explicit type casts.
***************************

I expected to get a result of 2 or 2.0 in some numeric type (maybe double p=
recision). The result should be a pure number because the units (dollars, i=
n this case) cancel out.

The ability to divide money by money would be useful for finding what perce=
nt one money value is of another. That is what I was wanting to use it for=
=97finding out what percentage of a customer's original balance has been pa=
id off.

It would also provide a better way to convert money into numeric types than=
 the regular expression in the documentation. You could just divide the mon=
ey amount by '1'::money.

Andy Balholm
(509) 276-2065
andy@balholm.com

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? =A0 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. =A0Where 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. =A0I 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. =A0I 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. =A0It 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? =A0(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.)=20=20

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

I think the function definition could be pasted directly into an appropriat=
e 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 fi=
t into postgres.bki.=20

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

---------------------------------------------------------------------------=
----------

And here is the SQL to load it (assuming it has been compiled as a dynamica=
lly 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';
=20=20=20=20
CREATE OPERATOR / (
    PROCEDURE =3D cash_div_cash,
    LEFTARG =3D money,
    RIGHTARG =3D 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.
>=20
> 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 res=
ult in an infinite loop.) So I went back to my float8 version and wrote a c=
ast 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 =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);
}

---------------------------------------------------------------------------=
---------

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 =3D cash_div_cash,
    LEFTARG =3D money,
    RIGHTARG =3D money
);

CREATE CAST (money AS numeric) WITH FUNCTION cash_numeric(money) AS ASSIGNM=
ENT;

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
moneyto 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 i=
t'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 ca=
st 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

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

Andy Balholm <andy@balholm.com> wrote:

> OK. Here is the whole thing in C

Cool!  I'll take it for a spin when I get a little time.

I guess there's not much point adding that TODO item now.  ;-)

-Kevin

Chris Travers <chris@metatrontech.com> writes:
> 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:

I'd be interested into participating into such a project, in particular
a multi-currencies support type coded in C would be useful for me.

> 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.

Sounds a good starting point, but it sounds like we'll have to think
about it to see how it survive a more detailed approach.

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

That's where it become interesting. Finding a nice way to solve the
problem of more than one currency in the same table, with dated
(timestamped?) conversion rates that are possibly unknown at INSERT
time=E2=80=A6

--=20
dim

On Fri, Apr 2, 2010 at 9:51 AM, Dimitri Fontaine <dfontaine@hi-media.com> w=
rote:

>> One could also then store monetary[] arrays for addressing specific
>> denomination storage. =A0I.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. =A0We could also store things like the cash
>> counted from a till at the end of the day by denomination. =A0One could
>> have easy monetary::numeric casts as well.
>
> Sounds a good starting point, but it sounds like we'll have to think
> about it to see how it survive a more detailed approach.

Sure.  See below.
>
>> Anyway, that's my basic thinking. =A0One could further add currency
>> conversion tables to an application if necessary.
>
> That's where it become interesting. Finding a nice way to solve the
> problem of more than one currency in the same table, with dated
> (timestamped?) conversion rates that are possibly unknown at INSERT
> time=85

Well, you have another problem (this may be wandering far afield from
the original question but here it goes):

Suppose I live in Canada and I have two checking accounts for my
business, one in CAD and one in USD.  In essence I have to account for
a floating balance of a foreign currency.  Consequently, I don't think
you can just suggest "convert at insert time" as a way to handle that.
In cases where you do (payments converted on deposit), that's a subset
of the more general problem, which is converting at an arbitrary point
in time.  However, even where you do (suppose instead I live in the US
and someone pays me in CAD), there is no guarantee that the conversion
rate when you enter the payment into your system as received and when
you convert it is the same.  The check could be deposited the next
day, for example and converted at that point.  OTOH, if it is an
incoming wire transfer in AUD, I would expect it to be converted on
receipt.

So conversion between currencies is something which has to be done at
a specified point in time.  While some of this could be automated to
an extent, it would really be business-specific.

In essence, I think you would need a function like
convert_currency(source monetary, target curr, date) to do the
conversion.  Furthermore this would require currency tables, and would
be probably outside the core data type definition.

In essence, to handle exchange rates, I think you would need
additional tables and the like, and UDF's to do the actual
conversions.  For simplicity's sake, I think this would be broken off
into a separate module although I would be happy to collaborate on
that as well.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, Apr 02, 2010 at 10:18:24AM -0700, Chris Travers wrote:

> Suppose I live in Canada and I have two checking accounts for my
> business, one in CAD and one in USD.  In essence I have to account for
> a floating balance of a foreign currency [...]
>                          there is no guarantee that the conversion
> rate when you enter the payment into your system as received and when
> you convert it is the same.  The check could be deposited the next
> day, for example and converted at that point.

It isn't even clear that the exchange rate for a given point in time is
well-defined. Typically you get differing exchange rates depending on
where (and how much!) you try to realize the conversion.

> So conversion between currencies is something which has to be done at
> a specified point in time.  While some of this could be automated to
> an extent, it would really be business-specific.
>
> In essence, I think you would need a function like
> convert_currency(source monetary, target curr, date) to do the
> conversion.  Furthermore this would require currency tables, and would
> be probably outside the core data type definition.
>
> In essence, to handle exchange rates, I think you would need
> additional tables and the like, and UDF's to do the actual
> conversions.  For simplicity's sake, I think this would be broken off
> into a separate module although I would be happy to collaborate on
> that as well.

Hm. An expert would have to decide whether such a simplification is
useful (it could, e.g. help in estimating the money amount held in
different currencies at some point in time) -- but some exchange rate
seems to be well-defined only when you actually *do* the conversion.

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFLtkjNBcgs9XrR2kYRAr++AJwKkDt6/RBbfK/KOz8ZNIK4RjYy6QCcCPPK
S+gOzayBMJh+2n9sFgbTI64=
=oeqv
-----END PGP SIGNATURE-----

tomas@tuxteam.de writes:
> On Fri, Apr 02, 2010 at 10:18:24AM -0700, Chris Travers wrote:
>
>> Suppose I live in Canada and I have two checking accounts for my
>> business, one in CAD and one in USD.  In essence I have to account for
>> a floating balance of a foreign currency [...]
>>                          there is no guarantee that the conversion
>> rate when you enter the payment into your system as received and when
>> you convert it is the same.  The check could be deposited the next
>> day, for example and converted at that point.
>
> It isn't even clear that the exchange rate for a given point in time
> is well-defined. Typically you get differing exchange rates depending
> on where (and how much!) you try to realize the conversion.

Indeed.

You can only be certain of there actually being a conversion if the
transaction directly involved a conversion between currencies.

Thus...

 1. If I buy materials using $USD on the $USD checking account, it's not
    evident what conversion *ever* takes place for this transaction.

    Expressing that transaction in $CDN will *always* reflect an
    estimate, never a "reality."

 2. In contrast, a funds transfer from the $CDN account to the $USD
    account will indicate some kind of "spot rate" because there will be
    two specific amounts:

    - The amount of $CDN currency taken out of the one account, and
    - The amount of $USD currency put into the other account.

    You may or may not know both values immediately; as Chris Travers
    observes, there may be some time separation.

It seems like an awfully bad idea to try to model this as if you
immediately know the exchange rate at the time the transaction is
recorded.

To the contrary, it seems to me that rate conversion shouldn't be
treated as being at all tightly integrated into this.

I actually have a similar situation to this, albeit not for business; I
have a $USD denominated account that earns interest.

I am expected to report on interest earnings on an annual basis by the
tax authorities.  There tend to be three approaches considered readily
acceptable:

  1.  Use an annual average exchange rate (I presume it's a geometric
      mean, but am not sure) on a total amount.

      This is the easiest, and is what I do.

  2.  Use monthly average exchange rates, applying the appropriate
      one to each month's earnings.

  3.  Use spot rates as reported by an authority, applying them to each
      transaction.  (For a bank account, this is actually pretty nearly
      equivalent to #2, just with a different way of picking the
      exchange rate!)

The fact that there are multiple policies like this points to the
conclusion that it's inappropriate to try to capture exchange rates as
something tightly coupled inside each transactions.  It's something
you'd want to have the option to change later, because the reporting
policy could well change.

>> So conversion between currencies is something which has to be done at
>> a specified point in time.  While some of this could be automated to
>> an extent, it would really be business-specific.
>>
>> In essence, I think you would need a function like
>> convert_currency(source monetary, target curr, date) to do the
>> conversion.  Furthermore this would require currency tables, and
>> would be probably outside the core data type definition.
>>
>> In essence, to handle exchange rates, I think you would need
>> additional tables and the like, and UDF's to do the actual
>> conversions.  For simplicity's sake, I think this would be broken off
>> into a separate module although I would be happy to collaborate on
>> that as well.
>
> Hm. An expert would have to decide whether such a simplification is
> useful (it could, e.g. help in estimating the money amount held in
> different currencies at some point in time) -- but some exchange rate
> seems to be well-defined only when you actually *do* the conversion.

My very little bit of exchange rate conversion takes place the day when
I'm working on my tax return :-).

There's some use in having a convenient way to capture conversion rates,
to help with the analysis, but it isn't necessarily tied to the
transactions themselves.  And there's more information that likely needs
to get captured, such as whether the rate reflects:

 - Actual conversion that took place (e.g. - as happens when you
   transfer between your own accounts denominated in different
   currencies)

 - Spot rates published by financial institutions

 - Average rates (for some form of "average", for some period of time),
   as published.

There may be more kinds of conversion rates than those three, but I know
those three are of interest.
--
"The real  romance is   out   ahead and   yet to come.    The computer
revolution hasn't started yet. Don't be misled by the enormous flow of
money into bad defacto standards for unsophisticated buyers using poor
adaptations of incomplete ideas." -- Alan Kay

Hi Chris,

Many thanks for your comments.

On Mon, Apr 5, 2010 at 10:16 AM, Chris Browne <cbbrowne@acm.org> wrote:

> Indeed.
>
> You can only be certain of there actually being a conversion if the
> transaction directly involved a conversion between currencies.
>
> Thus...
>
> =A01. If I buy materials using $USD on the $USD checking account, it's not
> =A0 =A0evident what conversion *ever* takes place for this transaction.

Right.  That's a major problem with the way LSMB (and SQL-Ledger)
currently handle this.  There are plenty of other issues that come up
here as well....
>
> =A0 =A0Expressing that transaction in $CDN will *always* reflect an
> =A0 =A0estimate, never a "reality."

Furthermore, since there is no conversion, there isn't any realized fx
gain or loss.  In other words, any fx gain or loss is a mere estimate
necessary for accounting reports.  IMO, any estimated unrealized gains
or losses should be dynamically calculated anyway.
>
> =A02. In contrast, a funds transfer from the $CDN account to the $USD
> =A0 =A0account will indicate some kind of "spot rate" because there will =
be
> =A0 =A0two specific amounts:
>
> =A0 =A0- The amount of $CDN currency taken out of the one account, and
> =A0 =A0- The amount of $USD currency put into the other account.
>
> =A0 =A0You may or may not know both values immediately; as Chris Travers
> =A0 =A0observes, there may be some time separation.
>
> It seems like an awfully bad idea to try to model this as if you
> immediately know the exchange rate at the time the transaction is
> recorded.


Thinking through this further, I have concluded that at least two ways
are necessary:

1)  Conversion of currencies as an intrinsic process at a known rate.
I.e. if I transfer money from a USD to a CDN account, and I have the
numbers and the rates, I tell it the appropriate rate.

2)  Conversion of currencies as an extrinsic process at a discovered
rate.  I.e. if I am running an income statement for 2009, I look up
rates for converting my totals from USD to CDN at the end points and
calculate estimated, unrealized fx gains and losses on that basis.

My initial reasoning was different, namely that data types shouldn't
depend on database tables to be usable.  However, this then squarely
addresses the other concern.  So I suppose that's a good thing :-)

Obviously any extrinsic elements shouldn't be tightly coupled with the
intrinsic elements (meaning you have monetary types with intrinsic
operators and functions, and then you have a separate, optional
business logic module which can provide those extrinsic elements along
with tables to store the values).
>
> To the contrary, it seems to me that rate conversion shouldn't be
> treated as being at all tightly integrated into this.

Agreed.  If you'd be interested in seeing the first draft of the spec
I came up with, I would be happy to forward it along.  A few things in
it will need to be changed due to what you have noted regarding looked
up exchange rates, but the basic type definitions and base functions
seem solid.
>
> I actually have a similar situation to this, albeit not for business; I
> have a $USD denominated account that earns interest.
>
> I am expected to report on interest earnings on an annual basis by the
> tax authorities. =A0There tend to be three approaches considered readily
> acceptable:
>
> =A01. =A0Use an annual average exchange rate (I presume it's a geometric
> =A0 =A0 =A0mean, but am not sure) on a total amount.
>
> =A0 =A0 =A0This is the easiest, and is what I do.

This is a published rate by the tax authorities?
>
> =A02. =A0Use monthly average exchange rates, applying the appropriate
> =A0 =A0 =A0one to each month's earnings.

Are these published as well by tax authorities?
>
> =A03. =A0Use spot rates as reported by an authority, applying them to each
> =A0 =A0 =A0transaction. =A0(For a bank account, this is actually pretty n=
early
> =A0 =A0 =A0equivalent to #2, just with a different way of picking the
> =A0 =A0 =A0exchange rate!)
>
> The fact that there are multiple policies like this points to the
> conclusion that it's inappropriate to try to capture exchange rates as
> something tightly coupled inside each transactions. =A0It's something
> you'd want to have the option to change later, because the reporting
> policy could well change.

Right.  There's also the following issue:
1)  I send an invoice in CDN
2)  1 month later that invoice is paid in CDN.
3)  I have to report relavant fx gains and losses.

The only way I can see of doing this is to look up an accepted rate as
of date of invoice, convert that, and then use the spot rate on the
conversion of the payment.  So one has (in this case) a realized gain
or loss which is in part estimated (and extrinsic to the "conversion"
which isn't "real) and in part known (and intrinsic to a real
conversion).  If conversions at specified rates are seen as a part of
the core module, then any lookup logic can be neatly uncoupled :-).

>> Hm. An expert would have to decide whether such a simplification is
>> useful (it could, e.g. help in estimating the money amount held in
>> different currencies at some point in time) -- but some exchange rate
>> seems to be well-defined only when you actually *do* the conversion.
>
> My very little bit of exchange rate conversion takes place the day when
> I'm working on my tax return :-).
>
> There's some use in having a convenient way to capture conversion rates,
> to help with the analysis, but it isn't necessarily tied to the
> transactions themselves. =A0And there's more information that likely needs
> to get captured, such as whether the rate reflects:
>
> =A0- Actual conversion that took place (e.g. - as happens when you
> =A0 transfer between your own accounts denominated in different
> =A0 currencies)
>
> =A0- Spot rates published by financial institutions
>
> =A0- Average rates (for some form of "average", for some period of time),
> =A0 as published.
>
> There may be more kinds of conversion rates than those three, but I know
> those three are of interest.

Hmm... Back to the drawing board on that helper module :-).

Best Wishes,
Chris Travers

Chris Travers wrote:
> Hmm... Back to the drawing board on that helper module :-).
>

like I said, its a big tarpit.

John R Pierce <pierce@hogranch.com> wrote:
> Chris Travers wrote:
>> Hmm... Back to the drawing board on that helper module :-).
>
> like I said, its a big tarpit.

If you start up on this again, you might want to start a new thread
with a more descriptive subject.  Those who weren't interested in
Andy's issue might not be following your discussion.  The proposed
new types and conversion capabilities  really have nothing to do
with the original topic, which makes it kinda confusing.

-Kevin

On Mon, Apr 5, 2010 at 4:37 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> John R Pierce <pierce@hogranch.com> wrote:
>> Chris Travers wrote:
>>> Hmm... Back to the drawing board on that helper module :-).
>>
>> like I said, its a big tarpit.
>
> If you start up on this again, you might want to start a new thread
> with a more descriptive subject. =A0Those who weren't interested in
> Andy's issue might not be following your discussion. =A0The proposed
> new types and conversion capabilities =A0really have nothing to do
> with the original topic, which makes it kinda confusing.

Yeah - it should probably be on -hackers, too, not here.

...Robert