I have a serial autoincrement column called "idmember" in my main table
(members). This serial column is a key to a second table. A row in
"members" table corresponds to many rows in the second table.
What is the best way to discover current "idmember" value if I create a
few rows in the second table immediately after creating a row in the
first table?
I know about "select currval('members_idmember_seq') from members limit
1;" and I know that it works without requiring a commit. So it should be
safe when another user does the same operation - the autoincremented
'idmember' should not be assigned incorrectly.
My question is: is this the best practice?
Here's an example in Python:
conn=psycopg.connect(dbconnstr)
c=conn.cursor()
# LOOP BEGINS HERE...
Cmd = "INSERT INTO members ... VALUES (...);"
c.execute(Cmd, Data)
Cmd = "SELECT currval('members_idmember_seq') FROM members LIMIT 1;"
c.execute(Cmd)
idmember = c.fetchone()[0]
Cmd = "INSERT INTO msg (idmember,txt) VALUES (%s,%s);"
c.execute(Cmd,(idmember,TxtData)
conn.commit()
# LOOP ENDS HERE
c.close()
conn.commit()
conn.close()
--
Milos Prudek
