The latest version of DBD::Pg, the PostgreSQL DBI driver, has been released and provides some useful new functionality, including support for server-side prepares, SQLSTATE code retrieval, and a last_insert_id() function. See below for a brief overview.

$dbh->last_insert_id()

This makes it easier to retrieve the id of the last successful INSERT statement, providing the table contains a sequence column (typically, but not always, the primary key). This is effectively a shortcut for SELECT CURRVAL('name_of_sequence').

Using this example table:

CREATE TABLE insert_test (id SERIAL, val TEXT, PRIMARY KEY (id))

the latest value of id can be obtained with

$id = $dbh->last_insert_id(undef,undef,undef,undef,
                          { sequence => 'insert_test_id_seq'}
                          );

As an added bonus it is not necessary to provide the name of the sequence, just the table name, providing the sequence is defined on a NOT NULL column with a unique constraint:

$id = $dbh->last_insert_id(undef,undef,'insert_test',undef);

$sth->state()

This method provided access to the standard SQLSTATE numeric error codes, which applications can interpret more easily than textual error messages. Example:

$sth = $dbh->prepare('SELECT * FROM insert_test LIMIT 1');
$sth->execute();
$SQLSTATE = $sth->state;

Assuming the operation is successful, $SQLSTATE will contain the scalar value 000000.

The PostgreSQL SQLSTATE codes are listed in the documentation at Appendix A. PostgreSQL Error Codes. Note that some error codes are PostgreSQL extensions to the standard; some of these may be common to other databases.

Server-side prepares

DBD::Pg now supports PostgreSQL's server-side PREPARE functionality (introduect in PostgreSQL 7.4) automatically when using the $dbh->prepare() method.

Usage of server-side prepares can be controlled at driver level or for indivdual statements.

Links

• News release
• GBort project page
• CPAN