Re: Finding value of primary key (autoincrement) from last-inserted row with DBIx::Abstract and postgresql

ajkessel:

If nothing else, you could always add a select max(columnname) from tablename in the same transaction as the insert to get the value back. Not necessarily pretty. I'm sure that there's a better way, but I'm not familiar with PostgreSQL.

Update: I just did a quick google, and found http://lists.initd.org/pipermail/psycopg/2003-January/001656.html in which Colin Fox describes a better way:

The Postgres 'serial' type is really a shorthand for creating a sequence, and setting the default value of the field to the NEXTVAL() of the sequence.

You can use the CURVAL() function to determine the current value of a sequence, as long as you know the name of the sequence. Postgres follows a simple naming convention for the serial numbers: tablename_fieldname_seq, so in your case the sequence would be called test_id_seq:

select CURVAL('test_id_seq');
[download]

--roboticus

Comment on Re: Finding value of primary key (autoincrement) from last-inserted row with DBIx::Abstract and postgresql Select or Download Code

Replies are listed 'Best First'.
Re^2: Finding value of primary key (autoincrement) from last-inserted row with DBIx::Abstract and postgresql by ajkessel (Acolyte) on Jun 19, 2006 at 02:43 UTC
Thanks for the quick reply. Hopefully I'm not being too dense here, but how would I implement the CURVAL() SELECT in the same transaction as the INSERT, using DBIx::Abstract? If I do SELECT curval() immediately after the INSERT (in the next line of perl), there is of course a small chance that another instance of the script will have done an INSERT in the interim. (Scaled up enough, presumably that is sure to happen). Or do I need to do send an "unmediated" SQL statement to the DB handle that just does the INSERT and then the SELECT? Even if that case, isn't there chance that another INSERT from another instance gets in before the SELECT?	[reply]
Re^3: Finding value of primary key (autoincrement) from last-inserted row with DBIx::Abstract and postgresql by roboticus (Chancellor) on Jun 19, 2006 at 03:03 UTC
I just looked at the package, and I'm not so sure. I see the commit and rollback functions, but I can't seem to find a begin transaction function. Perhaps the query method would let you put the insert and select in the same statement. I use the DBI package, rather than DBIx, so I'm just guessing. --roboticus	[reply]