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

Rod Chamberlin <rod(at)querix(dot)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