Convert Informix SPL to PostgreSQL PL/pgSQL with a mapping table and the six behaviors that change results

CREATE PROCEDURE post_batch(b INT)
  DEFINE id INT;
  ON EXCEPTION IN (-239)
  END EXCEPTION WITH RESUME;
  FOREACH SELECT order_num INTO id
    FROM staging WHERE batch = b
    INSERT INTO ledger
      SELECT * FROM staging
      WHERE order_num = id;
  END FOREACH;
END PROCEDURE;

CREATE PROCEDURE post_batch(b int)
LANGUAGE plpgsql AS $$
DECLARE id int;
BEGIN
  FOR id IN SELECT order_num
    FROM staging WHERE batch = b LOOP
    BEGIN
      INSERT INTO ledger SELECT *
        FROM staging WHERE order_num = id;
    EXCEPTION WHEN unique_violation
      THEN NULL;
    END;
  END LOOP;
END $$;

How do I convert Informix stored procedures to PostgreSQL?

Inventory first, convert second, review third. Pull the routine source from sysprocbody, count procedures, functions and triggers, and note which functions use RETURN WITH RESUME, which handlers use WITH RESUME and which routines call SYSTEM. Those three lists are where the manual work sits. Then run the code through a converter. Ispirer sells a toolkit for Informix to PostgreSQL with optional services, SQLines publishes its SPL conversion rules and sells the converter, and credativ announced a migrator in June 2025 that it says converts 80 to 90% of Informix SPL to PL/pgSQL. AWS is not on the list: neither AWS SCT nor DMS Schema Conversion supports Informix as a source.

A converter gets the syntax right. What it cannot know is what your application relied on. The mapping table below is the mechanical part, and the six behaviors after it need a person who knows the code. If the data side of the project is still open, every tool for the schema and the load is compared on our Informix to PostgreSQL migration tools page.

Informix SPL to PostgreSQL PL/pgSQL mapping table

Eighteen constructs that appear in almost every Informix code base, with the PL/pgSQL each one becomes and the note a reviewer should check.

Six SPL behaviors that convert cleanly and return a different answer

Each of these produces valid PL/pgSQL. None raises an error in testing unless the test data happens to hit it. They are listed in the order they tend to surface after go-live.

ON EXCEPTION WITH RESUME. In SPL, a handler declared WITH RESUME lets the routine continue with the statement after the one that failed. Batch loaders use it to skip a duplicate key and move on to the next row. PL/pgSQL has nothing like it. An EXCEPTION clause belongs to a block, and when it fires, PostgreSQL rolls back the changes made inside that block and continues after its END. A converter that moves the handler to the end of the procedure turns "skip the bad row" into "stop at the first bad row". The figure above shows the fix: a small BEGIN ... EXCEPTION block around each statement that is allowed to fail, inside the loop. Each of those blocks costs a subtransaction, so keep them around the risky statement only.

DATETIME variables. IBM describes DATETIME as "an instant in time expressed as a calendar date and time of day", and SPL code is full of variables declared DATETIME YEAR TO MINUTE. SQLines' published map sends YEAR TO MINUTE and YEAR TO HOUR to DATE. On a column that drops the time on load, and in a routine it drops it on every assignment, so a variable that held a dispatch time of 4:45 PM holds midnight in PostgreSQL. Arithmetic like CURRENT + 30 UNITS MINUTE still compiles once rewritten with an interval, and its result is then truncated back to a date. Declare these variables timestamp(0).

DECIMAL(p) variables. IBM documents that in a database that is not ANSI-compliant, a DECIMAL with fewer than two parameters is a floating-point decimal. PostgreSQL documents that NUMERIC(precision) selects a scale of zero. So DEFINE amt DECIMAL(8); copied to amt numeric(8); rounds 12.75 to 13 in the middle of a calculation. The error compounds across a loop and shows up as totals that miss by a few dollars. Declare numeric with no precision, or the precision and scale the values really need.

MATCHES patterns. MATCHES uses * for any string and ? for one character, with square brackets for character sets. LIKE uses % and _ and has no brackets. A converter that swaps the keyword and leaves the pattern alone produces a query that looks for a literal asterisk and returns nothing. Rewrite the wildcards, escape any literal % or _ that the old pattern contained, and use a regular expression with ~ where the pattern used brackets.

DEFINE GLOBAL. An SPL global variable keeps its value across routine calls for the rest of the session, which old code uses for counters, flags and a cached user ID. PL/pgSQL has no global variables. A converter that turns them into local declarations resets them on every call, and the counter starts at its default each time. Keep session state with set_config('app.batch_id', ..., false) and current_setting(), or in a table if it must survive a reconnect.

RETURN WITH RESUME. An SPL iterator function hands back one row per call and resumes where it left off, and callers read it through FOREACH EXECUTE FUNCTION. The PL/pgSQL equivalent is a set-returning function with RETURN NEXT or RETURN QUERY, but PostgreSQL's manual notes that the current implementation builds the entire result set before returning from the function. An iterator that streamed millions of rows now holds them all first and can spill to disk. Every caller also changes, from FOREACH EXECUTE FUNCTION to SELECT * FROM f(). For very large sets, return a cursor instead.

What replaces the SYSTEM statement in PostgreSQL?

Nothing inside the database, and that is usually for the best. SPL's SYSTEM runs an operating system command from inside a routine, and older Informix shops use it to send files, kick off reports or call scripts. PL/pgSQL cannot do this without an untrusted language extension that most managed PostgreSQL services do not allow. Move the work into the application or a scheduled job that reads a queue table the routine writes to. Once that job runs outside the database, nothing tells you when it stops, so put uptime checks on the job runner's API and ports before the first nightly run after cutover.

How do I convert Informix outer joins to PostgreSQL?

Rewrite Informix-style FROM a, OUTER b as a LEFT JOIN b ON ..., and move every condition on b out of WHERE and into the ON clause. A condition on the optional table left in WHERE discards the rows where b had no match, so the LEFT JOIN quietly behaves like an inner join and the report loses its customers with no orders. SQLines lists this rewrite in its Informix rules, and it is worth checking by hand on every query that used OUTER.

Can I convert Informix SPL to PostgreSQL automatically?

Mostly. Ispirer, SQLines and credativ-pg-migrator all translate SPL to PL/pgSQL, and credativ puts its own success rate at 80 to 90% depending on the writing style of the original code. The remainder is concentrated in the six behaviors above plus SYSTEM calls, and it is exactly the part a converter cannot judge, because the right rewrite depends on what the application expected.

How do I verify an Informix to PostgreSQL conversion?

Run the same inputs through both and compare outputs, not row counts. For each converted routine, capture a set of real calls on Informix, replay them on PostgreSQL against the same data, and diff the returned rows and the rows changed. Include a batch with a deliberate duplicate key, so the WITH RESUME rewrite is exercised, and compare every timestamp to the minute.

If Informix stays live for months while the code is rewritten, PostgreSQL has to be kept current and checked against it the whole time. That is a data sync, not a code conversion, and it is what our plans do: declare the columns once with TIMESTAMP for DATETIME and NUMERIC for MONEY, then sync incrementally with per-record logs. The closest relatives of this guide are converting Sybase T-SQL to PostgreSQL and converting Oracle PL/SQL to PostgreSQL, and the capture options for a long cutover are compared in change data capture tools.