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
