Re: dividing money by money

Chris Travers <chris(at)metatrontech(dot)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…

--
dim