SQL-92 compatible scripts

For installing and running SQL-92 scripts that can run on both Informix and PostgreSQL:

Two sources for SQL-92 syntax are:
HOWTO - SQL92 website
http://www.ocelot.ca/commands.htm
I found an excellent reference from Apress for PostgreSQL:
Beginning PHP and PostgreSQL 8 by Gilmore and Treat


IF EXISTS in statement
remove any "if exists" statements from the sql 'drop' command
ex. drop view if exists view1 should be changed to:
drop view view1
drop index if exists indx1 should be changed to:
drop index indx1
drop sequence if exists seq1 should be changed to:
drop sequence seq1
drop table if exists tab1 should be changed to:
drop table indx1


PostgreSQL scripts allow:
alter table.. owner ..

The owner clause in 'alter table' is not SQL-92 compatible.

When using and SQL DDL command such as create table..
note that setting a DEFAULT value that references a sequence is not allowed (see Sequences below)


BOOLEAN DATA
boolean data should be 't' for true and 'f' for false

SEQUENCES
Sequences are not defined in SQL-92 and are database specific.

IN PostgreSQL, to create a sequence:
create sequence seq1
start with 1
increment by 1
NO MAXVALUE
NO MINVALUE
cache cache_size

is rewritten for Informix as:
create sequence 'seq1'
start with value
increment by 1
nominvalue
nomaxvalue
cache cache_size

Informix does not allow use of NEXTVAL or CURVAL except in insert, select, delete and update statements;
NEXTVAL cannot be used in DDL.
In PostgreSQL, the setval function is used for setting a sequence value :
select pg_data.setval('seq1', value, true) = select pg_data.setval('seq',value)
sets the sequence to the next value when using NEXTVAL
This is PostgreSQL specific code.

VIEWS
PostgreSQL 8.3 easily integrates 'order by' into views.
In PostgreSQL
create view view1 as
select ..
from..
order by..

For Informix rewrite
create view view1 as
select (..)
from
(select ..
order by.. )

According to sql.org website 'or replace' option with 'create view' command is not sql-92 and Informix does not accept the clause. So instead use:
drop view view ;
CREATE VIEW view [ column [, ...] ] AS SELECT expression [ AS colname ] [, ...]
FROM table [ WHERE condition ]
[ WITH [ CASCADE LOCAL ] CHECK OPTION ]


CONVERTING EPOCH TIME:
To convert a unix timestamp to informix:
select DBINFO('utc_to_datetime', unix_epoch_time)
from..

CONSTRAINTS
The syntax for adding contraints is:
ALTER TABLE tbl ADD CONSTRAINT
PRIMARY/FOREIGN KEY (col1,col2..)
CONSTRAINT constraint_name ;

PostgreSQL OID::bpchar ::regclass
This specification is not required for informix and not SQL-92