Postgres Pro
Downloads
Home   >   mailing lists

Thread: CREATE TYPE example needed

CREATE TYPE example needed

From
"Jim Buttafuoco"
Date:
Hi all,

I am looking for a way to create a VARCHAR2 (oracle) type so I can use the same SQL to create my tables in either
Oracle
or Postgresql.  Does anyone have a working example?

I have tried the following with no luck

create type varchar2 (input=varcharin,output=varcharout,INTERNALLENGTH=VARIABLE);
ERROR:  TypeCreate: function 'varcharin(opaque)' does not exist

Thanks

Jim

Re: CREATE TYPE example needed

From
Tom Lane
Date:
"Jim Buttafuoco" <jim@spectrumtelecorp.com> writes:
> I am looking for a way to create a VARCHAR2 (oracle) type so I can use the same SQL to create my tables in either
Oracle
> or Postgresql.  Does anyone have a working example?

In 7.3, it's easy to use a "domain" as a type alias.

    CREATE DOMAIN varchar2 AS varchar;

This has the advantage that all the operations on varchar will work for
varchar2.  If you make a real type, you need to provide all those
operations.

Note however that this doesn't give you any special syntax for varchar2.
If you're expecting to be able to write "varchar2(N)" you're still out
of luck...

            regards, tom lane

Re: CREATE TYPE example needed

From
"Jim Buttafuoco"
Date:
Thanks for the answer Tom,  I did want to write varchar2(n).  Is this something special in the code?  Could I somehow
muck with the system catalogs and copy varchar.  The object of the execise is to have one set of SQL statements for
both
Oracle and Postgres.  I think it would help other people converting if we had the ability to handle Oracle table create
statements.

Thanks
Jim


> "Jim Buttafuoco" <jim@spectrumtelecorp.com> writes:
> > I am looking for a way to create a VARCHAR2 (oracle) type so I can use the same SQL to create my tables in either
Oracle
> > or Postgresql.  Does anyone have a working example?
>
> In 7.3, it's easy to use a "domain" as a type alias.
>
>     CREATE DOMAIN varchar2 AS varchar;
>
> This has the advantage that all the operations on varchar will work for
> varchar2.  If you make a real type, you need to provide all those
> operations.
>
> Note however that this doesn't give you any special syntax for varchar2.
> If you're expecting to be able to write "varchar2(N)" you're still out
> of luck...
>
>             regards, tom lane

Re: CREATE TYPE example needed

From
Tom Lane
Date:
"Jim Buttafuoco" <jim@spectrumtelecorp.com> writes:
> Thanks for the answer Tom, I did want to write varchar2(n).  Is this
> something special in the code?  Could I somehow muck with the system
> catalogs and copy varchar.

Nope, won't help you.  varchar(n) is hard-wired in the grammar.  Since
there are so few datatypes that have specialized syntax, there's no
provision for extensibility of that syntax.

Short of a lot of cutting-and-pasting in the parser, I don't think
you're going to get it to work.

            regards, tom lane