serial autoincrement and related table

[Milos Prudek <prudek@bvx.cz>]

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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faqs/FAQ.html