Dear friends,
I would like to port Compiere CRM from Oracle to PostgreSQL (and release it
for free).
At first I would like to convert the data schema. This is not difficult as
Compiere is written using portable types like NUMBER (i,d) which can be
replaced by NUMERIC (i,d), etc... A series of Search/Replace is sufficiant.
There are other solutions in Contrib to connect to Oracle and export the data
(Bruce). Don't blame me to search in another (silly) direction...
The point here is that I would like to use the CREATE TYPE or CREATE DOMAIN
syntax to map Oracle types to PostgreSQL types. Therefore I can say "Guys,
Oracle is now mostly compatible with PostreSQL".
In PostgreSQL, I used CREATE TYPE syntax to map
Oracle nvarchar2 -> PostgreSQL varchar (see code #1).
The code seems to be the equivalent of "CREATE DOMAIN nvarchar2 as varchar;"
Now I can create tables with nvarchar2 but not nvarchar2(lenght).
Is there a way to map Oracle nvarchar2(lenght) to PostgreSQL varchar(lenght)
in PostgreSQL 7.3? Are there plans to allow such mapping in the future using
the CREATE DOMAIN syntax?
Best regards,
Jean-Michel Pouré
**********************************************************************
Code #1
--DROP TYPE nvarchar2 CASCADE;
CREATE OR REPLACE FUNCTION oracle_nvarchar2in(cstring, oid, int4)
RETURNS nvarchar2 AS
'varcharin'
LANGUAGE 'internal' IMMUTABLE STRICT;
COMMENT ON FUNCTION oracle_nvarchar2in(cstring, oid, int4) IS '(internal)';
CREATE OR REPLACE FUNCTION oracle_nvarchar2out(nvarchar2)
RETURNS cstring AS
'varcharout'
LANGUAGE 'internal' IMMUTABLE STRICT;
CREATE TYPE nvarchar2
(INPUT=oracle_nvarchar2in, OUTPUT=oracle_nvarchar2out, DEFAULT='',
INTERNALLENGTH=-1, ALIGNMENT=int4, STORAGE=EXTENDED);
COMMENT ON TYPE nvarchar2 IS 'Oracle type nvarchar2(length) mapped to
PostgreSQL type varchar(lenght)';