DBD::Pg - PostgreSQL database driver for the DBI module
use DBI; $dbh = DBI->connect("dbi:Pg:dbname=$dbname"); # See the DBI module documentation for full details
DBD::Pg is a Perl module which works with the DBI module to provide access to PostgreSQL databases.
This documentation describes driver specific behavior and restrictions. It is not supposed to be used as the only reference for the user. In any case consult the DBI documentation first !
To connect to a database with a minimum of parameters, use the following syntax:
$dbh = DBI->connect("dbi:Pg:dbname=$dbname");
This connects to the database $dbname at localhost without any user authentication. This is sufficient for the defaults of PostgreSQL.
The following connect statement shows all possible parameters:
$dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host;port=$port;options=$options;tty=$tty", "$username", "$password");
If a parameter is undefined PostgreSQL first looks for specific environment variables and then it uses hard coded defaults:
parameter environment variable hard coded default -------------------------------------------------- dbname PGDATABASE current userid host PGHOST localhost port PGPORT 5432 options PGOPTIONS "" tty PGTTY "" username PGUSER current userid password PGPASSWORD ""
If a host is specified, the postmaster on this host needs to be started with the -i option (TCP/IP sockets).
-i
The options parameter specifies runtime options for the Postgres backend. Common usage is to increase the number of buffers with the -B option. For further details please refer to the postgres.
-B
For authentication with username and password appropriate entries have to be made in pg_hba.conf. Please refer to the pg_hba.conf and the pg_passwd for the different types of authentication. Note that for these two parameters DBI distinguishes between empty and undefined. If these parameters are undefined DBI substitutes the values of the environment variables DBI_USER and DBI_PASS if present.
@driver_names = DBI->available_drivers;
Implemented by DBI, no driver-specific impact.
@data_sources = DBI->data_sources('Pg');
The driver supports this method. Note, that the necessary database connect to the database template1 will be done on the localhost without any user-authentication. Other preferences can only be set with the environment variables PGHOST, DBI_USER and DBI_PASS.
DBI->trace($trace_level, $trace_file)
See Common Methods.
$rv = $h->err;
Supported by the driver as proposed by DBI. For the connect method it returns PQstatus. In all other cases it returns PQresultStatus of the current handle.
$str = $h->errstr;
Supported by the driver as proposed by DBI. It returns the PQerrorMessage related to the current handle.
$str = $h->state;
This driver does not (yet) support the state method.
$h->trace($trace_level, $trace_filename);
$h->trace_msg($message_text);
Supported by the driver as proposed by DBI. A database handle is active while it is connected and statement handle is active until it is finished.
Not used by this driver.
Supported by the driver as proposed by DBI. This method is similar to the SQL-function RTRIM.
Implemented by DBI, not used by the driver.
@row_ary = $dbh->selectrow_array($statement, \%attr, @bind_values);
$ary_ref = $dbh->selectall_arrayref($statement, \%attr, @bind_values);
$sth = $dbh->prepare($statement, \%attr);
PostgreSQL does not have the concept of preparing a statement. Hence the prepare method just stores the statement after checking for place-holders. No information about the statement is available after preparing it.
$sth = $dbh->prepare_cached($statement, \%attr);
Implemented by DBI, no driver-specific impact. This method is not useful for this driver, because preparing a statement has no database interaction.
$rv = $dbh->do($statement, \%attr, @bind_values);
Implemented by DBI, no driver-specific impact. See the notes for the execute method elsewhere in this document.
$rc = $dbh->commit;
Supported by the driver as proposed by DBI. See also the notes about Transactions elsewhere in this document.
$rc = $dbh->rollback;
$rc = $dbh->disconnect;
Supported by the driver as proposed by DBI.
$rc = $dbh->ping;
This driver supports the ping-method, which can be used to check the validity of a database-handle. The ping method issues an empty query and checks the result status.
$sth = $dbh->table_info;
Supported by the driver as proposed by DBI. This method returns all tables and views which are owned by the current user. It does not select any indices and sequences. Also System tables are not selected. As TABLE_QUALIFIER the reltype attribute is returned and the REMARKS are undefined.
@names = $dbh->tables;
Supported by the driver as proposed by DBI. This method returns all tables and views which are owned by the current user. It does not select any indices and sequences. Also system tables are not selected.
$sth = $dbh->DBD::Pg::db::attributes($table);
This method is PostgreSQL specific and returns for the given table the following data:
unique number attribute name attribute type attribute length (-1 for variable length attributes)
Because this method is not supported by DBI you have to use the complete name including the package.
$type_info_all = $dbh->type_info_all;
Supported by the driver as proposed by DBI. Only for SQL data-types and for frequently used data-types information is provided. The mapping between the PostgreSQL typename and the SQL92 data-type (if possible) has been done according to the following table:
+---------------+------------------------------------+ | typname | SQL92 | |---------------+------------------------------------| | bool | BOOL | | text | / | | bpchar | CHAR(n) | | varchar | VARCHAR(n) | | int2 | SMALLINT | | int4 | INT | | int8 | / | | money | / | | float4 | FLOAT(p) p<7=float4, p<16=float8 | | float8 | REAL | | abstime | / | | reltime | / | | tinterval | / | | date | / | | time | / | | datetime | / | | timespan | TINTERVAL | | timestamp | TIMESTAMP | +---------------+------------------------------------+
For further details concerning the PostgreSQL specific data-types please read the pgbuiltin.
@type_info = $dbh->type_info($data_type);
$sql = $dbh->quote($value, $data_type);
This module implements it's own quote method. In addition to the DBI method it doubles also the backslash, because PostgreSQL treats a backslash as an escape character. The optional data_type parameter is not used.
Supported by the driver as proposed by DBI. According to the classification of DBI, PostgreSQL is a database, in which a transaction must be explicitly started. Without starting a transaction, every change to the database becomes immediately permanent. The default of AutoCommit is on, which corresponds to the default behavior of PostgreSQL. When setting AutoCommit to off, a transaction will be started and every commit or rollback will automatically start a new transaction. For details see the notes about Transactions elsewhere in this document.
The default method of DBI is overridden by a driver specific method, which returns only the database name. Anything else from the connection string is stripped off. Note, that here the method is read-only in contrast to the DBI specs.
PostgreSQL specific attribute. If true, then quotes and backslashes in all parameters will be escaped in the following way:
escape quote with a quote (SQL) escape backslash with a backslash except for octal presentation
The default is on. Note, that PostgreSQL also accepts quotes, which are escaped by a backslash. Any other ASCII character can be used directly in a string constant.
$rv = $sth->bind_param($param_num, $bind_value, \%attr);
Not supported by this driver.
$rv = $sth->execute(@bind_values);
Supported by the driver as proposed by DBI. In addition to 'UPDATE', 'DELETE', 'INSERT' statements, for which it returns always the number of affected rows, the execute method can also be used for 'SELECT ... INTO table' statements.
$ary_ref = $sth->fetchrow_arrayref;
@ary = $sth->fetchrow_array;
$hash_ref = $sth->fetchrow_hashref;
$tbl_ary_ref = $sth->fetchall_arrayref;
$rc = $sth->finish;
$rv = $sth->rows;
Supported by the driver as proposed by DBI. In contrast to many other drivers the number of rows is available immediately after executing the statement.
$rc = $sth->bind_col($column_number, \$var_to_bind, \%attr);
$rc = $sth->bind_columns(\%attr, @list_of_refs_to_vars_to_bind);
$rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
$blob = $sth->blob_read($id, $offset, $len);
Supported by this driver as proposed by DBI. Implemented by DBI but not documented, so this method might change.
This method seems to be heavily influenced by the current implementation of blobs in Oracle. Nevertheless we try to be as compatible as possible. Whereas Oracle suffers from the limitation that blobs are related to tables and every table can have only one blob (data-type LONG), PostgreSQL handles its blobs independent of any table by using so called object identifiers. This explains why the blob_read method is blessed into the STATEMENT package and not part of the DATABASE package. Here the field parameter has been used to handle this object identifier.
The offset and len parameter may be set to zero, in which case the driver fetches the whole blob at once. See also the blob_copy_to_file method, which also supported by DBI, but not documented.
For further information and examples about blobs, specifically about the two built-in functions lo_import() and lo_export(), please read the large_objects.
Supported by the driver as proposed by DBI, with the restriction, that the types are PostgreSQL specific data-types which do not correspond to international standards.
Not supported by the driver.
Not supported by the driver. See the note about Cursors elsewhere in this document.
PostgreSQL specific attribute. It returns a reference to an array of integer values for each column. The integer shows the size of the column in bytes. Variable length columns are indicated by -1.
PostgreSQL specific attribute. It returns a reference to an array of strings for each column. The string shows the name of the data_type.
PostgreSQL specific attribute. It returns the OID of the last INSERT command.
PostgreSQL specific attribute. It returns the type of the last command. Possible types are: INSERT, DELETE, UPDATE, SELECT.
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.
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 only one transaction block at a time is allowed, 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 !
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.
PostgreSQL Version 6.2 considers the input 't' as true and anything else as false. PostgreSQL Version 6.3 considers the input 't', '1' and 1 as true and anything else as false. PostgreSQL Version 6.4 considers the input 't', '1' and 'y' as true and any other character as false.
DBI
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 DBI and DBD-Oracle.
The DBD::Pg module is free software. You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file, with the exception that it cannot be placed on a CD-ROM or similar media for commercial distribution without the prior approval of the author.
See also DBI/ACKNOWLEDGMENTS.
2 POD Errors
The following errors were encountered while parsing the POD:
'=item' outside of any '=over'
You forgot a '=back' before '=head1'
To install DBD::Pg, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBD::Pg
CPAN shell
perl -MCPAN -e shell install DBD::Pg
For more information on module installation, please visit the detailed CPAN module installation guide.