Giuseppe Sacco wrote:
> Thanks for this suggestion. I have been evaluating the use of sequences
> in my application, but I was looking for a more portable code (this code
> have to run on postgresq 8.1, mysql 5, oracle 9.2 and sql-server 2005).
> As you may know postgresql nextval syntax isn't as required by the
> standard; and other vendors use different syntax too. Moreover it would
> make my code really complex since I would need a new sequence for any
> connected user.
A more portable way to use sequences is to define a default expression
on the column when you create the table, like this:
CREATE TABLE foo (
key int DEFAULT (nextval('foo_seq')) PRIMARY KEY
data ...
)
Now if you don't give key a value in your insert statement, it'll be
assigned the next value from the sequence automatically. For example:
INSERT INTO foo (data, ...) VALUES ('bar', ...);
The create table syntax isn't portable, AFAIK, but there's a similar
construct available on all common DBMSs. You could use the same SQL in
your application to insert rows, only the DDL to create tables would be
different on different DBMSs.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com