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

Informix Cheat Sheet

Shared Memory stats
$ipcs -m
$onstat -g seg

To quickly get Informix version number:
$onstat -s

Use oninit with -s option before using the onmode -m
This will allow you to check log files and other config params before users can logon onstat -lRdt

Use onmode with -yuk option. This terminates user sessions, rolls back transaction and then goes offline allowing for a quicker startup
$onmode -yuk

The default page size with onspaces is 2K. You can use onmonitor or -k option to change the page size.
$onstat -bB
$oncheck -pr\grep -i 'page size'
$getconf PAGESIZE

# view the number of daemon servers and Flush page processes
$onstat -u
$ps -efgrep oninit

rollbacks, commits, cache, reads/writes etc.
$oncheck -cc
$oncheck -pc
$oncheck -pr

After going fully online, you can run the following to check VPs, Users and Sessions:
$onstat -g glo
$onstat -ug ses session_id

Importing/Exporting database:
Note qhen using dbimport Informix Engine will drop the database and create it new with the output of dbexport.

Other useful dbaccess SQL commands
info tables
info columns for table
info status for table
info access for table

PostgreSQL Cheat Sheet

Starting/Stopping Server:
$set PGDATA=/postgres/data
$pg_ctl -w start/restart -D /var/lib/pgsql/data -l log -o --port=5432
$pg_ctl -D /var/lib/pgsql/data stop -m immediate/smart/fast
$pg_ctl -s
$pg_ctl reload

Server Administration (psql)
Some useful commands :
=>select VERSION()
=>select CURRENT_DATABASE()
=>select CURRENT_SCHEMA()
=>select USER
=>select INET_SERVER_PORT()
=>select INET_SERVER_ADDR()
=>vacum analyze verbose tablename
=>explain sql

#TCP/IP settings in var/lib/pgsql/data/pg_hba.conf
host all 127.0.0.1 255.255.255.0 password
To allow remote connections: modify /var/lib/pgsql/data/postgresql.conf
#change listen_addresses = 'localhost'
# to listen_addresses = '*'

Forgot root/postgres password?
#Modify pg_hba.conf to all trust connections
$pg_ctl reload
$psql -U postgres
=>alter user postgres with password 'newpassword' ;
=>\q
#Set pg_hba.conf back to orginal settings
$pg_ctl reload

$pg_dump -U user db > tmp/db_dump
Create a database:
$createdb test
or
$psql -U postgres -c "CREATE DATABASE test" -d template1
$psql -U postgres -d test
=>\r ; clear buffer
=>\e ; edit buffer
=>\w ; write buffer
=> \! ; run sub-shell
=>\g or ; ; rerun contents of buffer
=>copy table to file
=>copy table from file
=>set ; to view DBNAME, user, host and port
=>\connect or \c dbname ; no arguments gives current database name
=>\i file.sql ; execute script
=>\e ; to change encoding
=>\o filename ;
send query results to a file
=>\o
=>\d table or \d for all tables
=>\dt or \di or \dv or \dv or \dC
=>\dg ; list of groups
=>\dn ; schemas
=>\dn+ schema
=>\c db username

To view a stored procedure in psql:
=>select prosrc from pg_proc where procname='procedure_name' ;
Some other useful system tables
pg_trigger (triggers)
pg_class (tables)
=>\d tablename ;describes the table
=>select relname from pg_class ; lists all the user tables

To view the a schema with all its tables, try the following:
\o dbschema.txt
=>echo \d `select relname from pg_class`
\o

error messages
undefined symbol: pg_valid_server_encoding_id is often the result of incompatible libraries (usally when upgrading PostgreSQL) usually the libpq.so
$ldd -d path to psql
$export LD_LIBRARY_PATH=path to correct libpq.so
$sudo ldconfig