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