serial autoincrement and related table

Поиск
Список
Период
Сортировка
От Milos Prudek
Тема serial autoincrement and related table
Дата
Msg-id 40A892E6.5000807@bvx.cz
обсуждение исходный текст
Ответы Re: serial autoincrement and related table  (Paul Thomas <paul@tmsl.demon.co.uk>)
Re: serial autoincrement and related table  (Richard Huxton <dev@archonet.com>)
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: Ulrich Wisser
Дата:
Сообщение: type conversion date <-> timestamp
Следующее
От: Paul Thomas
Дата:
Сообщение: Re: serial autoincrement and related table