Re: serial autoincrement and related table

Milos Prudek wrote:
> 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.

> 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;"

A simple "SELECT currval('members_idmember_seq');" will do it. The
sequence isn't part of the table.

>     c.execute(Cmd)
>     idmember = c.fetchone()[0]
>     Cmd = "INSERT INTO msg (idmember,txt) VALUES (%s,%s);"

Alternatively, you could rewrite this query:
"INSERT INO msg (idmember,txt) VALUES (currval('members_idmember_seq'),
%s);"

--
   Richard Huxton
   Archonet Ltd