[SQL] Retrieving info from the previous SQL insert
I have a SQL method which performs an INSERT in one table, followed by an INSERT into another table. The two INSERTs have to be in the same method because they need to be atomic. In the first table, there is an auto-incrementing field that the PostgreSQL DBMS assigns itself. I need the value of that field for the second INSERT and I don't see how to retrieve it inside the SQL method. Yes, I'll read more deeply PostgreSQL documentation and Zope documentation and <dtml-var my_postgresql_zope_adapter> documentation but, in the mean time, if someone has a ready-to-use solution?
On Tue, 11 Dec 2001, Stephane Bortzmeyer wrote:
I have a SQL method which performs an INSERT in one table, followed by an INSERT into another table. The two INSERTs have to be in the same method because they need to be atomic.
In the first table, there is an auto-incrementing field that the PostgreSQL DBMS assigns itself. I need the value of that field for the second INSERT and I don't see how to retrieve it inside the SQL method.
something along : SELECT curval('name_of_sequence'); if your fields is of type SERIAL then postgresql has automatically created a sequence for you, which name is probably something like : seq_id_tablename hth. Jerome Alet
If you need to know the inserted value, you would normally do a select currval('some_sequence'); where some_sequence is the name of the sequence attaced to the autoincrement column. So, you should be able to do the two inserts like this: insert into table1(col1, col2, etc) values(val1, val2, etc); insert into table2(col1, col2, etc) values(val1, currval('some_sequence'), etc); As long as you are in a transaction, I believe this should work. We do the same thing, but a little differently. I don't know your problem domain, but one of the really nice things about zope is that it can handle your transactions for you. So, anything that you do within one request is either all commited or all rolled back (ie everything in each transaction can be atomic). And this is of course also rippled to the database. This only works if you do not send your own commits and rollbacks of course. So most of the time, you don't really need to do the above. You can split the two inserts into separate statements if you want to and they are still treated as being part of the same transaction and are all either commited or rolled back at the end of the transaction. For example, I often want to pull the autoincremented value back into zope to do other stuff with it. So, I just created a ZSQL statement with select currval('some_sequence') and call that after the insert to get the inserted key value. Postgres keeps track of the last inserted value for each transaction and thread, so you always get the last incremented value that this particular thread inserted for a sequence. So, on a heavily updated database, you don't have to worry that you are getting the correct value. Kind of cool!! Hope that helps. -Chris On Tuesday 11 December 2001 03:57 am, Stephane Bortzmeyer wrote:
I have a SQL method which performs an INSERT in one table, followed by an INSERT into another table. The two INSERTs have to be in the same method because they need to be atomic.
In the first table, there is an auto-incrementing field that the PostgreSQL DBMS assigns itself. I need the value of that field for the second INSERT and I don't see how to retrieve it inside the SQL method.
Yes, I'll read more deeply PostgreSQL documentation and Zope documentation and <dtml-var my_postgresql_zope_adapter> documentation but, in the mean time, if someone has a ready-to-use solution?
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
-- Systems Analyst/Programmer VistaShare LLC www.vistashare.com
Stephane Bortzmeyer writes:
I have a SQL method which performs an INSERT in one table, followed by an INSERT into another table. The two INSERTs have to be in the same method because they need to be atomic. It is not necessary for both inserts to be in the same method! In Zope, all operations for one request are executed in a single transaction (if your DA does not set "auto-commit" mode). Thus, they form an atomic sequence even if executed in different methods.
Dieter
On Tue, Dec 11, 2001 at 09:57:12AM +0100, Stephane Bortzmeyer <bortzmeyer@netaktiv.com> wrote a message of 25 lines which said:
In the first table, there is an auto-incrementing field that the PostgreSQL DBMS assigns itself. I need the value of that field for the second INSERT and I don't see how to retrieve it inside the SQL method.
As several people indicated here, retrieving an auto-incremented PostgreSQL SERIAL value is done with the SQL function call currval (TABLENAME_COLUMNNAME_seq). I knew it (it's in PostgreSQL's FAQ) but I was unsure of the atomicity of the complete set of SQL instructions. Apparently, I missed that in Zope's documentation, every Zope request is atomic, even if it invokes several SQL methods. So, it should work fine in a straightforward way. Thanks to all.
participants (4)
-
Chris Kratz -
Dieter Maurer -
Jerome Alet -
Stephane Bortzmeyer