Re: [GENERAL] A real currency type

Martijn van Oosterhout wrote:
> On Tue, Mar 21, 2006 at 03:59:31PM -0500, Andrew Dunstan wrote:
>>> I think such types would be better implemented as some sort of
>>> structured type, possibly with constructors and methods and all the
>>> other stuff that SQL talks about. We don't have all of that yet --
>>> maybe we don't need all of it immediately -- but before we start
>>> endorsing many of these types I'd like to see some consideration given
>>> to this issue.
>> Yeah, looks interesting but let's find the wrinkles. I was wondering if
>> it might have relevance to what I wanted to do with enumeration types,
>> i.e. we would tag each one with its particular enumeration id.
>
> I have considered making enumeration types with this. It would probably
> work but kind of overkill. The main problem being that this code is
> seriously aimed at creating new types that you intend to do operations
> on, create operators for. An enumeration is just that, an enumeration.
>
>> I also don't like the idea of it being done with user tables - if this
>> is core material then the tags should go in the catalog, ISTM.
>
> But the user is providing the list of tags when creating the type.
> Unless you are proposing copying the tag table to the catalog when the
> type is created? You can't store all the tags for all tagged types in
> one tag table either. At least, that wasn't what I was envisiging. See
> the currency type where the tag-table also stores the format string for
> output. No other tagged type is going to need that information.
>
> SQL types with constructors and methods might provide this also, I
> don't know. My purpose with this was allowing people to simply create
> new types with specific behaviour without writing functions in C. Right
> now type input/output functions cannot be written in anything other
> than C. Here are a few C functions that handle these new types in a
> generic way and user can write little pl/pgsql wrappers to customise
> the output.
>
> Have a ncie day,
>
Martin,

This would be extremely useful to have.

For example, if I store currencies tagged properly, I could do a select
and multiply the currency tag by the factor associated with that tag.
This would allow easily quoting financials in a standard currency, but
protect against type mismatches presumably at the database level (USD +
AUD fails).

Either way, it should be easy to add tags (ie, INSERT or similar) or get
a list of them (SELECT * from somewhere where type = "currency"). I'm
sure this later will be the case system catalog or not.

- August