NAME

DBD::Pg - PostgreSQL database driver for the DBI module

SYNOPSIS

use DBI;

$dbh = DBI->connect("dbi:Pg:$dbname", $user, $passwd);

# See the DBI module documentation for full details

DESCRIPTION

DBD::Pg is a Perl module which works with the DBI module to provide access to PostgreSQL databases.

CONNECTING TO POSTGRESQL

To connect to a database you can say:

$dbh = DBI->connect('dbi:Pg:DB', 'username', 'password');

The first parameter specifies the driver and the database. The second and third parameter specify the username and password. This returns a database handle which can be used for subsequent database interactions.

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:

$numrows = $sth->execute;

For statements which return data, $numrows is the number of selected 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:

$sth->bind_columns(undef, @list_of_refs_to_vars_to_bind);
while ($sth->fetch) {

}

When you have fetched as many rows as required, you close the statement handle using:

$sth->finish;

This frees the statement, but it does not free the related data structures. This is done when you destroy (undef) the statement handle:

undef $sth;

DISCONNECTING FROM A DATABASE

You can disconnect from the database:

$dbh->disconnect;

Note that this does not destroy the database handle. You need to do an explicit 'undef $dbh' to destroy the handle.

DYNAMIC ATTRIBUTES

The following attributes are supported:

$DBI::err	# error status

$DBI::errstr	# error message

$DBI::rows	# row count

STATEMENT HANDLE ATTRIBUTES

For statement handles of a select statement you can discover what the returned column names, types, sizes are:

@name = @{$sth->{'NAME'}};	# Column names
@type = @{$sth->{'TYPE'}};	# Data types
@size = @{$sth->{'SIZE'}};	# Numeric size

There is also support for two PostgreSQL-specific attributes:

$oid_status = $sth->{'pg_oid_status'};	# oid of last insert
$cmd_status = $sth->{'pg_cmd_status'};	# type of last command

TRANSACTIONS

PostgreSQL supports simple transactions. They can not be named and they can not be nested ! You start a transaction with:

$dbh->do('begin');

The transaction can be aborted or finished with:

$dbh->do('abort');
$dbh->do('end');

Note that the following functions can also be used:

$dbh->rollback;
$dbh->commit;

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.

• Currently PostgreSQL does not return the number of affected rows for non-select statements.

• Transactions can not be named and not be nested.

• 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 !

• $DBI::state is not supported.

• Some statement handle attributes are not supported.

SEE ALSO

DBI

AUTHORS

• DBI and DBD-Oracle by Tim Bunce (Tim.Bunce@ig.co.uk)

• DBD-Pg by Edmund Mergl (E.Mergl@bawue.de)

  Major parts of this package have been copied from DBD-Oracle.

COPYRIGHT

The DBD::Pg module is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

ACKNOWLEDGEMENTS

See also "ACKNOWLEDGEMENTS" in DBI.

cpanm DBD::Pg

perl -MCPAN -e shell
install DBD::Pg