Oninit® SQLMorph — PostgreSQL
SPL converts to PostgreSQL PL/pgSQL. Control flow (IF / WHILE / FOR rec IN SELECT … LOOP), exception handling (EXCEPTION WHEN OTHERS THEN), and cursors all map cleanly — PL/pgSQL was Ada-derived alongside Oracle PL/SQL so the procedural surface is close. Each input procedure produces a single <name>.sql file containing the PostgreSQL install script.
What gets emitted
| File | Contents |
|---|---|
| <name>.sql | CREATE OR REPLACE FUNCTION … LANGUAGE plpgsql AS $$ DECLARE … BEGIN … END $$;. Install with psql -d <db> -f <name>.sql or via the Makefile postgres-install target. |
| REPORT.md | Only emitted when constructs were flagged. Each entry carries file:line, the construct, and a one-line note explaining what to review. |
Type mapping
| SPL type | PostgreSQL type |
|---|---|
| SMALLINT | SMALLINT |
| INT / INTEGER / SERIAL | INTEGER / SERIAL |
| INT8 / BIGINT / SERIAL8 / BIGSERIAL | BIGINT / BIGSERIAL |
| FLOAT / DOUBLE PRECISION | DOUBLE PRECISION |
| SMALLFLOAT / REAL | REAL |
| DECIMAL / NUMERIC / MONEY | NUMERIC(p,s) |
| CHAR(n) / VARCHAR(n) | CHAR(n) / VARCHAR(n) |
| LVARCHAR | VARCHAR(n) when sized, otherwise TEXT |
| DATE | DATE |
| DATETIME | TIMESTAMP |
| INTERVAL | INTERVAL YEAR TO MONTH or INTERVAL DAY TO SECOND |
| BOOLEAN | BOOLEAN |
| TEXT / CLOB | TEXT |
| BYTE / BLOB | BYTEA |
| table.col%TYPE | table.col%TYPE (PG native) |
Procedural mapping
| SPL construct | PostgreSQL |
|---|---|
| DEFINE name TYPE | DECLARE name TYPE; |
| LET name = expr | name := expr; |
| IF / ELIF / ELSE / END IF | IF / ELSIF / ELSE / END IF |
| WHILE cond / END WHILE | WHILE cond LOOP … END LOOP |
| FOREACH (cur AS) SELECT … / END FOREACH | FOR rec IN SELECT … LOOP … END LOOP |
| EXIT / CONTINUE | EXIT / CONTINUE |
| ON EXCEPTION … END EXCEPTION | EXCEPTION WHEN OTHERS THEN … |
| RAISE EXCEPTION code, msg | RAISE EXCEPTION '%', msg …; |
| RETURN expr | RETURN expr; |
| OUT parameters | Native — PG functions support OUT and INOUT param modes; no wrapper type needed. |
Mappings that are no-ops on PostgreSQL
A few SPL conventions emit unchanged because PostgreSQL natively supports them, no rewrite needed:
- || string concat — PostgreSQL native operator.
- <expr>::<TYPE> cast — PostgreSQL native syntax (matches the SPL form verbatim).
- NULL handling via COALESCE — PostgreSQL native.
- %TYPE column-type reference — PostgreSQL native.
Other Informix-isms are rewritten: TODAY → CURRENT_DATE, CURRENT / SYSDATE → CURRENT_TIMESTAMP, USER → CURRENT_USER.