-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Merlin Moncur wrote:
> That is a shortcoming of the DBD:g 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 UNIQUE DEFAULT 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,{sequen ce=>'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=2529DF...9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFCAYgSvJuQZxSWSsgRAgg3AJ4id98pta0CQR2w3xgwkx nph7qW4wCeMAJH
g/eXhtcmvXei9mESDDXg/s8=
=QaUa
-----END PGP SIGNATURE-----



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

> 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.


This suffers from the same problems that currval does when using
connection pools tho.

I previously suggested a function similar to last_insert_id in behaviour,
and have attached it to this email for reference.

Even so, this also suffers from the same problems when using a connection pool.

The solution I proposed, namely having the tuple returned by
inserts/updates (perhaps even deletes?) would only mean changing the
client library to handle this, and as an example, libpg could easily
figure out the OID of said tuple and return that if it's present for
PQExec() (for backwards compatibility just as it does today,) and add a
separate PQExecSelect() that instead returns the tuple(s) as if they had
been SELECTed.

--
John Hansen <john@geeknet.com.au>
GeekNET


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)