CONNECTING TO POSTGRESQL

The first parameter specifies the driver, the database and the optional host and port. The second and third parameter specify the username and password. Note that for these two parameters DBI distinguishes between empty and undefined. If these parameters are undefined DBI substitutes the values of the DBI_USER and DBI_PASS environment variables. The connect method returns a database handle which can be used for subsequent database interactions. Please refer to the pg_passwd man-page for the different types of authorization.

This module also supports the ping-method, which can be used to check the validity of a database-handle.

SIMPLE STATEMENTS

Given a database connection, you can execute an arbitrary statement using:

$dbh->do($stmt);

The statement must not be a SELECT statement (except SELECT...INTO TABLE).

PREPARING AND EXECUTING STATEMENTS

You can prepare a statement for multiple uses, and you can do this for SELECT statements which return data as well as for statements which return no data. You create a statement handle using:

$sth = $dbh->prepare($stmt);

Once the statement is prepared, you can execute it:

$rv = $sth->execute;

$rv is the number of selected / affected rows. You can retrieve the values in the following way:

while ($ary_ref = $sth->fetch) {
}

Another possibility is to bind the fields of a select statement to perl variables. Whenever a row is fetched from the database the corresponding perl variables will be automatically updated:

TRANSACTIONS

The transaction behavior is now controlled with the attribute AutoCommit. For a complete definition of AutoCommit please refer to the DBI documentation.

According to the DBI specification the default for AutoCommit is TRUE. In this mode, any change to the database becomes valid immediately. Any 'begin', 'commit' or 'rollback' statement will be rejected.

If AutoCommit is switched-off, immediately a transaction will be started by issuing a 'begin' statement. Any 'commit' or 'rollback' will start a new transaction. A disconnect will issue a 'rollback' statement.

In case your scripts do not use transactions, no changes are necessary. If your scripts make use of transactions, you have to adapt them to the AutoCommit feature. In most cases it is be sufficient, to remove the 'begin' statements and to switch-off the AutoCommit mode.

Meta-Information

The driver supports two simple methods to get meta-information about the available tables and their attributes:

$dbh->tables;
$dbh->DBD::Pg::db::attributes($table);

Because the second one is not (yet) supported by DBI you have to use the complete name including the package. The first method returns all tables which are owned by the current user. The second method returns for the given table a unique number, the name, the type, and the length of every attribute.

DATA TYPE bool

The current implementation of PostgreSQL returns 't' for true and 'f' for false. From the perl point of view a rather unfortunate choice. The DBD-Pg module translates the result for the data-type bool in a perl-ish like manner: 'f' -> '0' and 't' -> '1'. This way the application does not have to check the database-specific returned values for the data-type bool, because perl treats '0' as false and '1' as true. If you make use of the data-type bool you have to adapt your scripts !

Starting with version 6.3 PostgreSQL will consider 1 and '1' as input for the boolean data-type as true. In older versions everything except 't' is considerd as false.

BLOBS

Support for blob_read is provided. For further information and examples please read the files in the blobs subdirectory. In addition you can use the two registered built-in functions lo_import() and lo_export(). See the large_objects for further information and examples.

KNOWN RESTRICTIONS

PostgreSQL does not has the concept of preparing a statement. Here the prepare method just stores the statement.

Although PostgreSQL has a cursor concept, it has not been used in the current implementation. Cursors in PostgreSQL can only be used inside a transaction block. Because transactions in PostgreSQL can not be nested, this would have implied the restriction, not to use any nested SELECT statements. Hence the execute method fetches all data at once into data structures located in the frontend application. This has to be considered when selecting large amounts of data !