Обсуждение: Compatibility types, type aliases, and distinct types

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.

* Peter Eisentraut (peter_e@gmx.net) wrote:
> 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.

The alias route gets me thinking about Oracle synonyms..  That'd be nice
to have in PG for a number of object types.  Most recently I was wishing
I could create a schema synonym, though being able to do tables/views
would have worked as well in that case, just a bit more work.

> 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.

I like the concept.  Not sure how much I'd end up using it, personally.
Thanks,
    Stephen

<div dir="ltr">In my experience synonyms as well as rules are hacks and should be avoided althou there are cases where
theycan save some work for dba's during transitions from one situation to better one.<br /><br />> There is also
anotherpossible way one might want to create a compatibility<br /> > type.  Instead of creating a new type, create
analias for an existing type,<br /> > much like we currently have built-in mappings for int -> int4, bigint
-><br/> > int8, etc.  The difference here is that the type you put in is not the same<br /> > as the one you
getdumped out.  So depending on taste and requirements, a<br /> > user might want to choose the distinct type or the
aliasroute.<br /><br />Example or two would be helpful here where you expect this kind of functionality be useful.
Couldyou use it for defining Oracle compatibel varchar2 and how would it work then?<br /><br /><div
class="gmail_quote">OnMon, Aug 18, 2008 at 3:33 PM, Stephen Frost <span dir="ltr"><<a
href="mailto:sfrost@snowman.net">sfrost@snowman.net</a>></span>wrote:<br /><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="Ih2E3d">*
PeterEisentraut (<a href="mailto:peter_e@gmx.net">peter_e@gmx.net</a>) wrote:<br /> > There is also another possible
wayone might want to create a compatibility<br /> > type.  Instead of creating a new type, create an alias for an
existingtype,<br /> > much like we currently have built-in mappings for int -> int4, bigint -><br /> >
int8,etc.  The difference here is that the type you put in is not the same<br /> > as the one you get dumped out.
 Sodepending on taste and requirements, a<br /> > user might want to choose the distinct type or the alias route.<br
/><br/></div>The alias route gets me thinking about Oracle synonyms..  That'd be nice<br /> to have in PG for a number
ofobject types.  Most recently I was wishing<br /> I could create a schema synonym, though being able to do
tables/views<br/> would have worked as well in that case, just a bit more work.<br /><div class="Ih2E3d"><br /> >
Whatdo you think about adding this kind of support to PostgreSQL?  Obviously,<br /> > some details need to be worked
out,but most of this is actually<br /> > straightforward catalog manipulation.<br /><br /></div>I like the concept.
 Notsure how much I'd end up using it, personally.<br /><br />        Thanks,<br /><font color="#888888"><br />        
      Stephen<br /></font><br />-----BEGIN PGP SIGNATURE-----<br /> Version: GnuPG v1.4.9 (GNU/Linux)<br /><br />
iEYEARECAAYFAkipbCgACgkQrzgMPqB3kiinmwCfROrhdu8YDpzsJvOtvpSW147O<br/> SOQAn3y/4MGadFz9VqDsmcm8fiKuxsn5<br /> =gdfU<br
/>-----END PGP SIGNATURE-----<br /><br /></blockquote></div><br /></div> 

Peter Eisentraut <peter_e@gmx.net> writes:
> 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;

This seems like a great way to get lost in "ambiguous function" hell ...
        regards, tom lane

On Monday 18 August 2008 17:26:16 Tom Lane wrote:
> > This feature by itself could be quite useful, and then we could simply
> > add something like
> >     CREATE TYPE newtype AS oldtype WITH CASTS;
>
> This seems like a great way to get lost in "ambiguous function" hell ...

I don't understand this point.  No new overloaded functions are being defined.

Peter Eisentraut <peter_e@gmx.net> writes:
> On Monday 18 August 2008 17:26:16 Tom Lane wrote:
> This feature by itself could be quite useful, and then we could simply
> add something like
> CREATE TYPE newtype AS oldtype WITH CASTS;
>> 
>> This seems like a great way to get lost in "ambiguous function" hell ...

> I don't understand this point.  No new overloaded functions are being defined.

If the type has no functions of its own, then the only way to make it
easily usable is to throw in implicit conversions *in both directions*
between it and the type it's an alias for.  You're going to find that
that's a problem.
        regards, tom lane

Am Monday, 18. August 2008 schrieb Tom Lane:
> If the type has no functions of its own, then the only way to make it
> easily usable is to throw in implicit conversions *in both directions*
> between it and the type it's an alias for.  You're going to find that
> that's a problem.

I'm not finding that that's a problem.  We have several cases of that in the 
standard catalogs already.  What kind of problem are you foreseeing?

One direction of the cast could be AS ASSIGNMENT, btw., but that is another 
decision that would have to be worked out.

Peter Eisentraut <peter_e@gmx.net> writes:
> One direction of the cast could be AS ASSIGNMENT, btw., but that is another 
> decision that would have to be worked out.

Making the back-cast be AS ASSIGNMENT would reduce the risks of
ambiguities, for sure.
        regards, tom lane