PostgreSQL PL/pgSQL to Informix SPL migration

PL/pgSQL coding can be quite a different experience coding with Informix SPL. However almost all the procedural functionality is converted in SPL.

1. Variables: In SPL use DEFINE instead of plpgsqls' DECLARE
Most in-build PL/pgSQL variables are supported.
For %ROWTYPE however one has to create a row type datatype
In pgplsql for: DECLARE var tbl.colname%TYPE
in SPL use: DEFINE var like tbl.colname
Assigning variables SPL requires the LET keyword
LET var1 = var2+5 ;

2. User defined functions returning values
SPL uses RETURNING keyword whereas in plpgsql use RETURN

3. Date/Time
I couldn't find a with or without timezone clause in SPL. However if TZ environment variable is set,
then one can call select DBINFO('get_tz') from tablename
plpgsql TIMESTAMP is equivalent to SPL datetime
The data types Interval, Date and Time are the same in both language
To get epoch time in SPL:
select DBINFO('utc_to_datetime',colname) from tablename;
select DBINFO('utc_current') returns the current date as epoch
To convert datetime to epoch is more interesting:
Some developers have blogged to_epoch user defined functions that manually converted datetime to an integer.


4. EXCEPTION Handling
SPL provides the SQLCODE=100 for a successful query but with no rows returned.
This is similar to plpgsqls' IF NOT FOUND clause
I found I had to use a cursor to retrieve the SQLCODE
create function fn()
returning integer;
DEFINE qry varchar(100) ;
LET qry= "select col from table where col='somevalue'" ;
PREPARE stmt from qry ;
DECLARE cursor1 cursor for stmt ;
OPEN cursor1 ;
FETCH cursor1 into found ;
if (SQLCODE = 100) then
raise exception -746,0,"no matching row found";
end if
return SQLCODE ;
end function ;
The -746 is a custom code provided by Informix allowing a developer to return their own created exception

If someone doesn't want to use cursors, another option is after running the select statement, use:
select DBINFO(sqlca.sqlerrd2') into rows_affected from systables where tabid=1 ;
or
LET retcode = DBINFO('sqlca.sqlerrd2') ;
If no rows were returned this will return 0 into rows_affected

6. SEQUENCES
ANSI SQL does not support sequences as DEFAULT col values in CREATE TABLE DDL, although PL/pgSQL allows it.
Once you create a sequence, the next value can be retrieved into an SPL variable and then used in an insert stmt.

7. SQL/RETURNING statement
PL/pgSQL provides a RETURNING clause after an SQL select, insert or update stmt (not ANSI compliant).
In SPL I wrote an SQL select stmt after insert/update SQL to get the value.

8. Testing an SPL routine
One can use dbaccess to create a routine and save it
From query-language->New option then
execute function function_name() ;

The returned values/error messages are displayed