sqlport - port Informix SQL to PostgreSQL

$ echo 'select first 1 hello from world' | sqlport
SELECT hello FROM world LIMIT 1;

$ sqlport -h
usage: sqlport [-h] [--outfile OUTFILE | --outdir OUTDIR | --replace]
               [--file-list [FILE]] [--quiet] [--verbose] [--debug]
               [--parse-tree] [--lex] [--informix]
               [INFILE [INFILE ...]]

Ports SQL code to another dialect.

positional arguments:
  INFILE

optional arguments:
  -h, --help            show this help message and exit
  --outfile OUTFILE, -o OUTFILE
			output file path pattern with place holders: "#" =
			input file path; "%" = input file path with last file
			extension removed; "%%" = input file path with last
			two file extensions removed; ...
  --outdir OUTDIR, -d OUTDIR
                        output base directory
  --replace, -r         replace input file
  --file-list [FILE], -f [FILE]
			read file list from file or stdin
  --quiet, -q           do not output anything
  --verbose, -v         verbose output
  --debug, -D           debugging output
  --parse-tree, -T      show parse tree
  --lex, -L             show lexer output
  --informix, -i        generate informix SQL

Implemented transformations

Data types

Informix	Postgres
lvarchar	varchar
varchar(x,y)	varchar(x)
byte	bytea
interval (1) year to month	interval

Literals

Informix	Postgres
current	current_timestamp
today	current_date
"some text"	'some text'

Misc

Informix	Postgres
SELECT FIRST 1 ...	SELECT ... LIMIT 1
SELECT UNIQUE ...	SELECT DISTINCT ...
SELECT ... INTO TEMP x	CREATE TEMP TABLE x AS SELECT ...
SELECT x, y, z FROM TABLE(some_function(a, b)) AS t (x, y, z)	SELECT x, y, z FROM some_function(a, b) AS t (x, y, z)
nvl(x, y)	coalesce(x, y)
ALTER TABLE ADD CONSTRAINT PRIMARY KEY ...	ALTER TABLE ADD PRIMARY KEY ...
UPDATE STATISTICS [FOR table_name]	ANALYZE [table_name]

Procedures

Informix	Postgres
CREATE PROCEDURE	CREATE FUNCTION
DROP PROCEDURE	DROP FUNCTION
RETURNING	RETURNS
no return value	RETURNS void
DEFINE x integer	x integer in DECLARE block
DEFINE x, y integer	converted to individual declarations
LET x = y	x := y
IF ... ELIF ... END IF	IF ... ELSIF ... END IF
WHILE x=y ... END WHILE	WHILE x=y LOOP ... END LOOP
EXIT WHILE, EXIT FOR, ...	EXIT
RAISE EXCEPTION -746, 0, "some text"	RAISE EXCEPTION "Error: %", 'some text'
semicolon optional after END IF, END FOR, ...	semicolon always required
EXECUTE PROCEDURE name(x,y), CALL name(x, y)	SELECT name(x, y), PERFORM name(x, y)

MERGE

• Informix:

  MERGE INTO x USING y ON y.y1 = x.x1
  WHEN MATCHED THEN UPDATE SET x.x2 = y.y2
  WHEN NOT MATCHED THEN INSERT (x1, x2) VALUES (y1, y2)
  

• Postgres:

  INSERT INTO x (x1, x2)
  SELECT y1, y2 FROM y
  ON CONFLICT (x1) DO UPDATE SET x1 = y1, x2 = y2
  

• MERGE without WHEN NOT MATCHES THEN INSERT is translated into UPDATE FROM syntax.

Keywords as names

• Informix: all, end, default, ...
• Postgres: not allowed
• append underscore, e.g. all_, end_, default_, ...

Constraint names

• Postgres: contraint name must differ from table name
• Prefix constraint name, e.g. with pk_

Limited transformations

SYSTEM

• Informix: SYSTEM "sleep 10"
• Postgres: PERFORM system('sleep 10')
• The system function has to be defined separately

ADD column BEFORE

• Informix: ALTER TABLE x ADD a int BEFORE c
• Postgres: BEFORE is not supported
• BEFORE c is dropped

FOREACH

• Informix:

  FOREACH SELECT a, b INTO x, y FROM ...
  ...
  END FOREACH
  

• Postgres:

  FOR record IN SELECT a AS x, b AS y
  FROM ... LOOP
  ...
  X := record x;
  y := record y;
  ...
  END LOOP;
  

• Using the record type directly could be cleaner.

OUTER(table)

• Informix: SELECT ... FROM a, OUTER(b)
• Postgres: not supported
• Supports limited translation to ANSI JOINs for simple cases.

Exception handlers and error codes

• Informix:

  ON EXCEPTION IN (-206, -958)
  ...
  END EXCEPTION
  

• Postgres:

  BEGIN
  ...
  EXCEPTION
  WHEN undefined_table OR duplicate_table THEN
  ...
  END
  

• Only a few error codes are mapped
• WITH RESUME is not supported
• ON EXCEPTION without error code is not supported

Unscaled decimal

• Informix: decimal(20)
• If you omit the scale in Informix it is not fixed.
• If you omit the scale in Postgres it defaults to zero.
• Currently these cases are translated to DECIMAL(30,10).

MATCHES

• Informix: MATCHES "*[a-z]?"
• Postgres: SIMILAR TO "%[a-z]_"
• This is converted for literal string patterns, but not if the pattern is a variable.

Slice

• Informix: text[2,4]
• Postgres: substring(text from 2 for 3)
• This is automatically converted. However this does not work if the slice is on the left side of a let statement (variable assignment).

Not Supported

Informix	Postgres
multiset(integer)
SET LOCK MODE
DEFINE GLOBAL
database[@server]:name
sys* tables
LET x, y = y, x
GRANT, REVOKE
multiple return values	use record type or OUT paramters
named return parameters

If something is not automatically translated a NOT_SUPPORTED message is included in the output.