Re: Money casting too liberal?

On 30/03/13 04:08, Gavan Schneider wrote:
> Some thoughts.
>
> The current MONEY type might be considered akin to ASCII. Perfect for
> a base US centric accounting system where there are cents and dollars
> and no need to carry smaller fractions. As discussed, there are some
> details that could be refined.
>
> When it comes to this type being used in full blown money systems it
> lacks the ability to carry fractions of cents and keep track of
> currencies. It also needs to play nicer with other exact types such as
> numeric, i.e., no intermediate calculations as real.
>
> Therefore the discussion is really about the desired role for the
> MONEY type. Should it be refined in its current dallar and cents mode?
> or, be promoted to a more universal role (akin to a shift from ASCII
> to UTF)?
>
> If there is merit in making MONEY work for most situations involving
> financial transactions I think the following might apply:
>
> - keep integer as the underlying base type (for performance)
>
> - generalise the decimal multiplier of a MONRY column so a specific
> MONEY column can be what its creator wants (from partial cents to
> millions of dollars/Yen/Other, along with rounding/truncating rules as
> required by r the user of his/her external agencies)
>
> - define the currency for a given column and only allow this to change
> in defined ways, and specifically forbid implicit changes such as
> would arise from altering LOCALE information
>
> - ensure the MONEY type plays nice with other exact precision types,
> i.e., convert to REAL/FLOAT as a very last resort
>
>
> Personally I don't think it is appropriate for the MONEY type to have
> variable characteristics (such as different currencies) within a given
> column, rather the column variable should define the currency along
> with the desired decimal-multiplier and whatever else is required. The
> actual values within the column remain as simple integers. This is
> mostly based on performance issues. If the MONRY type is to be used it
> has to offer real performance benefits over bespoke NUMERIC applications.
>
> Regards
> Gavan Schneider
>
>
>
I agree 100%.

In the bad old days when I was a COBOL programmer we always stored money
in the COBOL equivalent of an integer (numeric without a fractional
part) to avoid round off, but we displayed with a decimal point to
digits to the left. So storing as an integer (actually bigint would be
required) is a good idea, with parameters to say how many effective
digits in the fractional part, and how many fractional digits to display
etc. - as you said.

Cheers,
Gavin