Craig Longman <craigl@begeek.com> writes:
> so, i presume then that this is a problem in the database because no-one
> has sat down and made sure that all the possible combinations of
> built-in datatypes are handled for all the various built-in
> functions/operators?
No, that's not it, and that would be quite the wrong way to go about it.
We have, hmm, six different built-in numeric datatypes (int2, int4,
int8, float4, float8, numeric; not to count quasi-numerics like OID,
XID, "char", etc). Does it make sense to field thirty-six variants of
"plus", thirty-six of "minus", etc? How many would you need to add to
support even one additional user-defined numeric type ("complex", say)?
Nope, it just doesn't scale.
What we need is some logic that decides on a common datatype to promote
the two inputs to and then apply a single-data-type operator. The
mechanics are there to do this, what we haven't got is the rule that
allows a unique choice to be made when there are several possibilities.
For example, in your float8 * numeric case, the system hasn't got a way
to decide between using float8 multiply or numeric multiply, although it
can do either one if you coax it by supplying a cast. Interestingly,
it can do all the other cases, such as int4 * float8, just fine --- the
problem is that both float8 and numeric are marked as "preferred types",
leaving the poor thing with no way to make a choice. The real issue
here is that the "preferred type" heuristic doesn't encode enough
knowledge to deal with all the numeric datatypes. We need a more
general approach.
You can find more about this in the pghackers archives, eg thread
"type conversion discussion" around 5/14/2000.
regards, tom lane