Re: [NOVICE] Last ID Problem - Mailing list pgsql-hackers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Merlin Moncur wrote:
> That is a shortcoming of the DBD::pg driver which really should be
> returning a key (comprised of columns, some or none of which may be
> defaulted by the server).
Actually, the spec comes from DBI, not DBD::Pg, and is inspired by
MySQL's last_insert_id function. It is a poorly-speced function,
but we've done our best in the upcoming version of DBD::Pg,
which will support it.
Greg Stark wrote:
> For postgres it looks like currently it requires you to pass in
> the table and field might even need a "driver-specific hint" telling
> it the sequence name.
For the record, the only required field for DBD::Pg will be the table
name, although the name of the sequence is highly encouraged. Here's
the docs for the next version, the first which supports lii:
last_insert_id $rv = $dbh->last_insert_id($catalog, $schema, $table, $field); $rv = $dbh->last_insert_id($catalog,
$schema,$table, $field, \%attr);
 
Attempts to return the id of the last value to be inserted into a table.
You can either provide a sequence name (preferred) or provide a table
name with optional schema. The $catalog and $field arguments are always ignored.
The current value of the sequence is returned by a call to the
'currval' PostgreSQL function. This will fail if the sequence has not yet
been used in the current database connection.
If you do not know the name of the sequence, you can provide a table name and
DBD::Pg will attempt to return the correct value. To do this, there must be at
least one column in the table with a C<NOT NULL> constraint, that has a unique
constraint, and which uses a sequence as a default value. If more than one column
meets these conditions, the primary key will be used. This involves some
looking up of things in the system table, so DBD::Pg will cache the sequence
name for susequent calls. If you need to disable this caching for some reason,
you can control it via the 'pg_cache' attribute.
Please keep in mind that this method is far from foolproof, so make your
script use it properly. Specifically, make sure that it is called
immediately after the insert, and that the insert does not add a value
to the column that is using the sequence as a default value.
Some examples: $dbh->do("CREATE SEQUENCE lii_seq START 1"); $dbh->do("CREATE TABLE lii (   foobar INTEGER NOT NULL
UNIQUEDEFAULT nextval('lii_seq'),   baz VARCHAR)"); $SQL = "INSERT INTO lii(baz) VALUES (?)"; $sth =
$dbh->prepare($SQL);for (qw(uno dos tres quattro)) {   $sth->execute($_);   my $newid =
$dbh->last_insert_id(C<undef>,undef,undef,undef,{sequence=>'lii_seq'});  print "Last insert id was $newid\n"; }
 
If you did not want to worry about the sequence name: $dbh->do("CREATE TABLE lii2 (   foobar SERIAL UNIQUE,   baz
VARCHAR)");$SQL = "INSERT INTO lii2(baz) VALUES (?)"; $sth = $dbh->prepare($SQL); for (qw(uno dos tres quattro)) {
$sth->execute($_);  my $newid = $dbh->last_insert_id(undef,undef,"lii2",undef);   print "Last insert id was $newid\n";
}
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200502022110
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFCAYgSvJuQZxSWSsgRAgg3AJ4id98pta0CQR2w3xgwkxnph7qW4wCeMAJH
g/eXhtcmvXei9mESDDXg/s8=
=QaUa
-----END PGP SIGNATURE-----