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

On Thu, 6 Jan 2000, Bruce Momjian wrote:

> > 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.
> 
> He can do:
> 
>      INSERT INTO table VALUES(... , nextval('sequenceobject'), ...);
> 
> and currval() will get him the previous nextval() value.
> 

The problem is unfortunately much more generic than this.  I would like
able to take an informix/4GL program an run it without modification on a
postgres backend.  The difficulty here is that the database interface
*does not know* the datatypes in the insert statement.  The problem
actually becomes more tricky because the catalog tables don't even know
that the original datatype was a serial, so the interface layer cannot
take any special steps to pre-process the data.

The only other alternative is to write a secondary parser in the interface
layer which does the SQL conversion.  This strikes me as an exceptionally
complex solution given the relative similarity between Informix/SQL and
Postgress SQL.

.............................Rod

+-----------------------------------------------------------------------------+
| Rod Chamberlin              |  rod@querix.com   Tel +44 1703 232345         |
| Software Engineer           |                   Mob +44 7803 295406         |
| QueriX                      |                   Fax +44 1703 399685         |
+-----------------------------------------------------------------------------+
| The views expressed in this document do not necessarily represent those of  |
|                    the management of QueriX (UK) Ltd.                       |
+-----------------------------------------------------------------------------+