CREATE TYPE example needed 
Author Message
 CREATE TYPE example needed

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

---------------------------(end of broadcast)---------------------------



Mon, 04 Jul 2005 03:30:52 GMT
 CREATE TYPE example needed

Quote:

> 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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Mon, 04 Jul 2005 05:20:48 GMT
 CREATE TYPE example needed
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

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Mon, 04 Jul 2005 07:50:43 GMT
 CREATE TYPE example needed

Quote:

> 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Mon, 04 Jul 2005 07:58:35 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. any example of CREATE TYPE BODY?

2. Need an example of an Assesment(survey type) database

3. NEED VB SQL ADO IMAGE DATA TYPE EXAMPLE

4. Need Create Table with Defaults example

5. Need Create Java Function example, Please

6. Oracle8: CREATE TYPE BODY syntax: Need help

7. How to use the type of CREATE TYPE?

8. How to Create SQL Server Resource Type, SQL Server Agent Resource Type

9. contrib/tablefunc fixes (GRANTs to PUBLIC, use CREATE TYPE vice CREATE

10. contrib/tablefunc fixes (GRANTs to PUBLIC, use CREATE TYPE vice CREATE VIEW)

11. Examples of structured data types

12. Example of stored procedure with OUTPUT parameter of type TEXT


 
Powered by phpBB® Forum Software