Informix to PostgresSQL: ON EXCEPTION discarded

[strk]

ON EXCEPTION blocks in Informix functions are discarded when converting to PostgreSQL. Example:

CREATE FUNCTION test()
RETURNING integer;
  DEFINE sql_errno INTEGER;
  DEFINE isam_errno INTEGER;
  DEFINE sql_errtxt VARCHAR(255);
 ON EXCEPTION
     SET sql_errno, isam_errno, sql_errtxt
     RAISE EXCEPTION sql_errno, isam_errno, sql_errtxt;
 END EXCEPTION;
BEGIN
  DEFINE v1 INTEGER;
  RETURN 1;
END;
END FUNCTION; 

[strk]

NOTE: converting the above currently results in:

CREATE OR REPLACE FUNCTION test()
RETURNS integer AS $$

  DECLARE sql_errno INTEGER;
  isam_errno INTEGER;
  sql_errtxt VARCHAR(255);
BEGIN
  v1 INTEGER;
BEGIN
  RETURN 1;
END;
END;
$$ LANGUAGE plpgsql; 

Where the v1 INTEGER line is a syntax error in PostgreSQL. Note that moving the v1 under the DECLARE block fixes the syntax error, although it still discards the exception handling...

[strk]

A possible handling of the exception block could be:

CREATE OR REPLACE FUNCTION test()
RETURNS integer AS $$

  DECLARE sql_errno INTEGER;
  isam_errno INTEGER;
  sql_errtxt VARCHAR(255);
  v1 INTEGER;
BEGIN
BEGIN
  RETURN 1;
EXCEPTION WHEN OTHERS THEN
  sql_errno := SQLSTATE;
  isam_errno := SQLSTATE;
  sql_errtxt := SQLERRM;
  RAISE EXCEPTION '%, %, %', sql_errno, isam_errno, sql_errtxt;
END;
END;
$$ LANGUAGE plpgsql; 

I don't know how the SET sql_errno, isam_errno, sql_errtxt behaves in informix, would need some research about it. \cc @palmerj

[strk]

Informix specific parsing code does exist for ON EXCEPTION, and it is hit too (SqlParser::ParseOnExceptionStatement in sqlparser/statements.cpp) but for some reason fails to produce the intended output (EXCEPTION WHEN OTHERS is even mentioned in the code)