I have been hacking around for a while trying to create some example Oracle
compatibility types. Canonical examples: varchar2 and number. With the new
features in 8.3 and 8.4, such as user-definable typmods and type categories,
it appears to be actually possible to create a type equivalent to numeric or
varchar entirely in user space. Cool.
Actually doing this, however, appears to be shockingly complicated. You need
to redefine all the input/output/send/receive functions and all the cast
functions and casts and then tie them all together. I don't expect that this
is something a user would succeed in, and not even an experienced developer
would want to type all that in. I actually had to write a script to generate
all that code.
So while thinking about how to make this simpler I remembered the "distinct
type" feature of SQL, which works quite similarly, namely the new type has
the same structure as the old type, but is a separate entity. It looks like
CREATE TYPE newtype AS oldtype;
This feature by itself could be quite useful, and then we could simply add
something like
CREATE TYPE newtype AS oldtype WITH CASTS;
to copy all the casts as well, so the new type can be used in contexts where
the old type could be used.
There is also another possible way one might want to create a compatibility
type. Instead of creating a new type, create an alias for an existing type,
much like we currently have built-in mappings for int -> int4, bigint ->
int8, etc. The difference here is that the type you put in is not the same
as the one you get dumped out. So depending on taste and requirements, a
user might want to choose the distinct type or the alias route.
What do you think about adding this kind of support to PostgreSQL? Obviously,
some details need to be worked out, but most of this is actually
straightforward catalog manipulation.
