From a database (and client) point of view, you need to do a few things.
The first thing you need to consider is that you have to use integers for everything and have a number of implied decimals for each currency (eg: $2.00 is actually 200 with 2 implied decimals. The $2.00 part is a client formatting issue and a database maths issue.). Floating point never comes into it - you don't want to go near the mathematical implications involved in using floating point.
My advice: Have a currency
table with the currency_id
(PK), currency_name
and decimals
fields. Any other table then just needs a currency_id
and amount
column to relate to the original table. Formatting is then just a join between the two. Maths between any tables with the same currency is then just straight integers.
Maybe add a few more columns to the currency
table for leading and trailing symbols (eg: '$' for prefix, '.' for decimal spacer) for formatting.
It's an implementation limitation. It's theoretically possible, of course, but nobody's written the code to handle it yet.
To cope with column removals or type changes, PostgreSQL would have to scan every view that references the view being modified (using pg_catalog.pg_depend
) to see if any of them relied on the column. It'd also need to look for whole-row references and disallow changes in those cases.
It's less clear why adding a column is not permitted. Again, I suspect that's down to whole-row references. If pg_depend
was checked for whole-row references without finding any, and the new column appeared at the end, it'd be OK to add it.
However, views created with SELECT * FROM
wouldn't "inherit" the new column, because *
gets expanded into a column-list during view creation, though. So if you had view_A
that does a SELECT * FROM view_B
, and you added a column to view_B
, it wouldn't appear in view_A
. Yet if you dropped and re-created view_A
, the column would appear. Needless to say, that's not good. To cope with that, PostgreSQL would have to keep track of whether a given view's column list ("targetlist" in PostgreSQL internal terms) came from a *
wildcard. Which is more complicated than you'd think because you can write someview.*
too.
All in all - it's complicated, and nobody's wanted it enough to do the work to implement it.
Best Answer
Well,
Having said that, software should almost never do selects or inserts, without specifying the columns.