Daniele Varrazzo wrote:
> I would say no, "returning" is the best option, "select
> currval(seq_name)" a second -vastly inferior- one.
>
I can see the logic in that. "Returning" happens in one statement, but
select currval followed by an insert means you have to go to the
database twice.
> Looks like you are hoping to write "portable SQL queries". I don't
> believe this approach will bring you very far: if you want to write
> portable _queries_ you need a tool that generates the _SQL_ for you,
> or you will have problems in any query but the simplest ones. Hard?
> Yes, it is. Take a look at SQLAlchemy if you want portable queries, or
> be prepared to work at a level higher than the SQL.
>
I'm aware of the risk, but I've had reasonable enough success so far for
routine queries that I don't want to get into the extra cost of using an
sql generator. Most of what I do with a database is look things up in
tables, and it is pretty easy to write even fairly complicated select
statements that don't get into database-specifics. This auto-increment
field is the most unusual thing I do in my current application, so
working around the database differences there is still less work than
converting to an sql generator.
I appreciate the advice, though. In other circumstances, I would look
into it.
Mark S.