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. |
+-----------------------------------------------------------------------------+