Re: [HACKERS] Enhancing PGSQL to be compatible with Informix SQL

Rod Chamberlin <rod@querix.com> writes:
> I am currently looking into the possibility of extending the current
> postgres SQL implementation to be compatible with Informix's SQL
> implementation.

Don Baccus already made the point that we are more interested in being
compatible with the standard than with specific commercial
implementations, so I won't repeat it.  I do have a couple of practical
suggestions though:

>     1/    Datetime type specifiers (should have no impact)
>     2/    Interval type specifiers (ditto)

We support enough datestyle variants already that it's hard to believe
there isn't one that will meet your needs.  But if not, I think adding
an "Informix" datestyle option might be considered reasonable.

>     5/    serial data type
>         o    Serial type must return inserted key value
>         o    Unfortunately (and this is the big bad hit)
>             informix's serial datatype does serial number
>             generation on a zero inserted valued.
>             The modification required to do this may have
>             impact on existing programs.

Breaking existing applications will not fly.  If you have lots of
code that depends on this behavior, you could easily emulate it
by adding a BEFORE INSERT trigger on each table that needs it.
Ignoring the boilerplate, the critical bit would look like:
if new.serialcolumn = 0 then    new.serialcolumn = nextval('sequenceobject');

However, if you need to know what value is being given to the
inserted tuple, much the cleanest solution is to select nextval
before inserting:
SELECT nextval('sequenceobject');INSERT INTO table VALUES(... , value-you-just-got, ...);

If you are always going to do that, then a trigger is a waste of cycles.
        regards, tom lane