Search For :

in:

All Sections 1. General Commands 2. System Calls 3. Subroutines 4. Special Files 5. File Formats 6. Games and Demos 7. Macros and Conventions 8. Maintenence Commands

 

NAME

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

SYNOPSIS

  use DBI;

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

  # for some advanced uses you may need PostgreSQL type values:
  use DBD::Pg qw(:pg_types);

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

MODULE DOCUMENTATION

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!  

THE DBI CLASS

 

DBI Class Methods

connect

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

The options parameter specifies runtime options for the Postgres backend. Common usage is to increase the number of buffers with the "-B" option. Also important is the "-F" option, which disables automatic fsync() call after each transaction. For further details please refer to the postgres.

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.

available_drivers

  @driver_names = DBI->available_drivers;

Implemented by DBI, no driver-specific impact.

data_sources

  @data_sources = DBI->data_sources('Pg');

The driver supports this method. Note that the necessary database connection 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.

trace

  DBI->trace($trace_level, $trace_file)

Implemented by DBI, no driver-specific impact.

 

DBI Dynamic Attributes

See Common Methods.  

METHODS COMMON TO ALL HANDLES

err

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

errstr

  $str = $h->errstr;

Supported by the driver as proposed by DBI. It returns the PQerrorMessage related to the current handle.

state

  $str = $h->state;

This driver does not (yet) support the state method.

trace

  $h->trace($trace_level, $trace_filename);

Implemented by DBI, no driver-specific impact.

trace_msg

  $h->trace_msg($message_text);

Implemented by DBI, no driver-specific impact.

func

This driver supports a variety of driver specific functions accessible via the func interface:

  $attrs = $dbh->func($table, 'table_attributes');

The "table_attributes" function is no longer recommended. Instead, you can use the more portable "column_info" and "primary_key" functions to access all the same information.

This method returns for the given table a reference to an array of hashes:

  NAME        attribute name
  TYPE        attribute type
  SIZE        attribute size (-1 for variable size)
  NULLABLE    flag nullable
  DEFAULT     default value
  CONSTRAINT  constraint
  PRIMARY_KEY flag is_primary_key
  REMARKS     attribute description

  $lobjId = $dbh->func($mode, 'lo_creat');

Creates a new large object and returns the object-id. $mode is a bit-mask describing different attributes of the new object. Use the following constants:

  $dbh->{pg_INV_WRITE}
  $dbh->{pg_INV_READ}

Upon failure it returns undef.

  $lobj_fd = $dbh->func($lobjId, $mode, 'lo_open');

Opens an existing large object and returns an object-descriptor for use in subsequent lo_* calls. For the mode bits see lo_create. Returns undef upon failure. Note that 0 is a perfectly correct object descriptor!

  $nbytes = $dbh->func($lobj_fd, $buf, $len, 'lo_write');

Writes $len bytes of $buf into the large object $lobj_fd. Returns the number of bytes written and undef upon failure.

  $nbytes = $dbh->func($lobj_fd, $buf, $len, 'lo_read');

Reads $len bytes into $buf from large object $lobj_fd. Returns the number of bytes read and undef upon failure.

  $loc = $dbh->func($lobj_fd, $offset, $whence, 'lo_lseek');

Change the current read or write location on the large object $obj_id. Currently $whence can only be 0 (L_SET). Returns the current location and undef upon failure.

  $loc = $dbh->func($lobj_fd, 'lo_tell');

Returns the current read or write location on the large object $lobj_fd and undef upon failure.

  $lobj_fd = $dbh->func($lobj_fd, 'lo_close');

Closes an existing large object. Returns true upon success and false upon failure.

  $lobj_fd = $dbh->func($lobj_fd, 'lo_unlink');

Deletes an existing large object. Returns true upon success and false upon failure.

  $lobjId = $dbh->func($filename, 'lo_import');

Imports a Unix file as large object and returns the object id of the new object or undef upon failure.

  $ret = $dbh->func($lobjId, $filename, 'lo_export');

Exports a large object into a Unix file. Returns false upon failure, true otherwise.

  $ret = $dbh->func($line, 'putline');

Used together with the SQL-command 'COPY table FROM STDIN' to copy large amount of data into a table avoiding the overhead of using single insert commands. The application must explicitly send the two characters ``\.'' to indicate to the backend that it has finished sending its data.

  $ret = $dbh->func($buffer, length, 'getline');

Used together with the SQL-command 'COPY table TO STDOUT' to dump a complete table.

  $ret = $dbh->func('pg_notifies');

Returns either undef or a reference to two-element array [ $table, $backend_pid ] of asynchronous notifications received.

  $fd = $dbh->func('getfd');

Returns fd of the actual connection to server. Can be used with select() and func('pg_notifies').

 

ATTRIBUTES COMMON TO ALL HANDLES

Warn (boolean, inherited)

Implemented by DBI, no driver-specific impact.

Active (boolean, read-only)

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.

Kids (integer, read-only)

Implemented by DBI, no driver-specific impact.

ActiveKids (integer, read-only)

Implemented by DBI, no driver-specific impact.

CachedKids (hash ref)

Implemented by DBI, no driver-specific impact.

CompatMode (boolean, inherited)

Not used by this driver.

InactiveDestroy (boolean)

Implemented by DBI, no driver-specific impact.

PrintError (boolean, inherited)

Implemented by DBI, no driver-specific impact.

RaiseError (boolean, inherited)

Implemented by DBI, no driver-specific impact.

HandleError (boolean, inherited)

Implemented by DBI, no driver-specific impact.

ChopBlanks (boolean, inherited)

Supported by the driver as proposed by DBI. This method is similar to the SQL-function RTRIM.

LongReadLen (integer, inherited)

Implemented by DBI, not used by the driver.

LongTruncOk (boolean, inherited)

Implemented by DBI, not used by the driver.

Taint (boolean, inherited)

Implemented by DBI, no driver-specific impact.

private_*

Implemented by DBI, no driver-specific impact.

 

DBI DATABASE HANDLE OBJECTS

 

Database Handle Methods

selectrow_array

  @row_ary = $dbh->selectrow_array($statement, \%attr, @bind_values);

Implemented by DBI, no driver-specific impact.

selectrow_arrayref

  $ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @bind_values);

Implemented by DBI, no driver-specific impact.

selectrow_hashref

  $hash_ref = $dbh->selectrow_hashref($statement, \%attr, @bind_values);

Implemented by DBI, no driver-specific impact.

selectall_arrayref

  $ary_ref = $dbh->selectall_arrayref($statement, \%attr, @bind_values);

Implemented by DBI, no driver-specific impact.

selectall_hashref

  $hash_ref = $dbh->selectall_hashref($statement, $key_field);

Implemented by DBI, no driver-specific impact.

selectcol_arrayref

  $ary_ref = $dbh->selectcol_arrayref($statement, \%attr, @bind_values);

Implemented by DBI, no driver-specific impact.

prepare

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

prepare_cached

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

do

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

commit

  $rc  = $dbh->commit;

Supported by the driver as proposed by DBI. See also the notes about Transactions elsewhere in this document.

rollback

  $rc  = $dbh->rollback;

Supported by the driver as proposed by DBI. See also the notes about Transactions elsewhere in this document.

disconnect

  $rc  = $dbh->disconnect;

Supported by the driver as proposed by DBI.

ping

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

column_info

        $sth = $dbh->column_info( $catalog, $schema, $table, $column );

Supported by the driver as proposed by the DBI with the follow exceptions. These fields are currently always returned with NULL values:

   TABLE_CAT
   BUFFER_LENGTH
   DECIMAL_DIGITS
   NUM_PREC_RADIX
   SQL_DATA_TYPE
   SQL_DATETIME_SUB
   CHAR_OCTET_LENGTH

Also, four additional non-standard fields are returned:

  pg_type
  pg_type_only
  pg_attypmod
  pg_constraint - holds column constraint definition

table_info

  $sth = $dbh->table_info( $catalog, $schema, $table, $type );

Supported by the driver as proposed by DBI. This method returns all tables and views visible to the current user. The $catalog argument is currently unused. The schema and table arguments will do a 'LIKE' search if a percent sign (%) or an underscore (_) are detected in the argument. The $type argument accepts a value of wither ``TABLE'' or ``VIEW'' (using both is the default action).

foreign_key_info

  $sth = $dbh->foreign_key_info( $pk_catalog, $pk_schema, $pk_table,
                                 $fk_catalog, $fk_schema, $fk_table );

Supported by the driver as proposed by DBI. Unimplemented for Postgres servers before 7.3 (returns undef). Currently only returns information about first column of any multiple-column keys.

tables

  @names = $dbh->tables( $catalog, $schema, $table, $type );

Supported by the driver as proposed by DBI. This method returns all tables and views which are visible to the current user. If the database is version 7.3 or higher, the name of the schema appears before the table or view name.

type_info_all

  $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

  @type_info = $dbh->type_info($data_type);

Implemented by DBI, no driver-specific impact.

quote

  $sql = $dbh->quote($value, $data_type);

This module implements its own quote method. In addition to the DBI method it also doubles the backslash, because PostgreSQL treats a backslash as an escape character.

NOTE: The undocumented (and invalid) support for the "SQL_BINARY" data type is officially deprecated. Use "PG_BYTEA" with "bind_param()" instead:

  $rv = $sth->bind_param($param_num, $bind_value,
                         { pg_type => DBD::Pg::PG_BYTEA });

 

Database Handle Attributes

AutoCommit (boolean)

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.

Driver (handle)

Implemented by DBI, no driver-specific impact.

Name (string, read-only)

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.

RowCacheSize (integer)

Implemented by DBI, not used by the driver.

pg_auto_escape (boolean)

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

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.

pg_enable_utf8 (boolean)

PostgreSQL specific attribute. If true, then the utf8 flag will be turned for returned character data (if the data is valid utf8). For details about the utf8 flag, see Encode. This is only relevant under perl 5.8 and higher.

NB: This attribute is experimental and may be subject to change.

pg_INV_READ (integer, read-only)

Constant to be used for the mode in lo_creat and lo_open.

pg_INV_WRITE (integer, read-only)

Constant to be used for the mode in lo_creat and lo_open.

 

DBI STATEMENT HANDLE OBJECTS

 

Statement Handle Methods

bind_param

  $rv = $sth->bind_param($param_num, $bind_value, \%attr);

Supported by the driver as proposed by DBI.

NOTE: The undocumented (and invalid) support for the "SQL_BINARY" SQL type is officially deprecated. Use "PG_BYTEA" instead:

  $rv = $sth->bind_param($param_num, $bind_value,
                         { pg_type => DBD::Pg::PG_BYTEA });

bind_param_inout

Not supported by this driver.

execute

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

fetchrow_arrayref

  $ary_ref = $sth->fetchrow_arrayref;

Supported by the driver as proposed by DBI.

fetchrow_array

  @ary = $sth->fetchrow_array;

Supported by the driver as proposed by DBI.

fetchrow_hashref

  $hash_ref = $sth->fetchrow_hashref;

Supported by the driver as proposed by DBI.

fetchall_arrayref

  $tbl_ary_ref = $sth->fetchall_arrayref;

Implemented by DBI, no driver-specific impact.

finish

  $rc = $sth->finish;

Supported by the driver as proposed by DBI.

rows

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

bind_col

  $rc = $sth->bind_col($column_number, \$var_to_bind, \%attr);

Supported by the driver as proposed by DBI.

bind_columns

  $rc = $sth->bind_columns(\%attr, @list_of_refs_to_vars_to_bind);

Supported by the driver as proposed by DBI.

dump_results

  $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);

Implemented by DBI, no driver-specific impact.

blob_read

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

Starting with PostgreSQL-6.5 every access to a blob has to be put into a transaction. This holds even for a read-only access.

See also the PostgreSQL-specific functions concerning blobs which are available via the func-interface.

For further information and examples about blobs, please read the chapter about Large Objects in the PostgreSQL Programmer's Guide.

 

Statement Handle Attributes

NUM_OF_FIELDS (integer, read-only)

Implemented by DBI, no driver-specific impact.

NUM_OF_PARAMS (integer, read-only)

Implemented by DBI, no driver-specific impact.

NAME (array-ref, read-only)

Supported by the driver as proposed by DBI.

NAME_lc (array-ref, read-only)

Implemented by DBI, no driver-specific impact.

NAME_uc (array-ref, read-only)

Implemented by DBI, no driver-specific impact.

TYPE (array-ref, read-only)

Supported by the driver as proposed by DBI

PRECISION (array-ref, read-only)

Not supported by the driver.

SCALE (array-ref, read-only)

Not supported by the driver.

NULLABLE (array-ref, read-only)

Not supported by the driver.

CursorName (string, read-only)

Not supported by the driver. See the note about Cursors elsewhere in this document.

Statement (string, read-only)

Supported by the driver as proposed by DBI.

RowCache (integer, read-only)

Not supported by the driver.

pg_size (array-ref, read-only)

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.

pg_type (hash-ref, read-only)

PostgreSQL specific attribute. It returns a reference to an array of strings for each column. The string shows the name of the data_type.

pg_oid_status (integer, read-only)

PostgreSQL specific attribute. It returns the OID of the last INSERT command.

pg_cmd_status (integer, read-only)

PostgreSQL specific attribute. It returns the type of the last command. Possible types are: INSERT, DELETE, UPDATE, SELECT.

 

FURTHER INFORMATION

 

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.  

Large Objects

The driver supports all large-objects related functions provided by libpq via the func-interface. Please note, that starting with PostgreSQL 6.5 any access to a large object - even read-only - has to be put into a transaction!  

Cursors

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!  

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.

Boolean values can be passed to PostgreSQL as TRUE, 't', 'true', 'y', 'yes' or '1' for true and FALSE, 'f', 'false', 'n', 'no' or '0' for false.  

Schema support

PostgreSQL version 7.3 introduced schema support. Note that the PostgreSQL schema concept may differ to that of other databases. Please refer to the PostgreSQL documentation for more details.

Currently DBD::Pg does not provide explicit support for PostgreSQL schemas. However, schema functionality may be used without any restrictions by explicitly addressing schema objects, e.g.

  my $res = $dbh->selectall_arrayref("SELECT * FROM my_schema.my_table");

or by manipulating the schema search path with SET search_path, e.g.

  $dbh->do("SET search_path TO my_schema, public");

 

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) and Jeffrey W. Baker (jwbaker@acm.org). By David Wheeler <david@wheeler.net>, Jason Stewart <jason@openinformatics.com> and Bruce Momjian <pgman@candle.pha.pa.us> and others after v1.13.

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

Mailing List

The current maintainers may be reached through the 'dbdpg-general' mailing list: http://gborg.postgresql.org/mailman/listinfo/dbdpg-general

This list is available through Gmane (http://www.gmane.org) as a newsgroup with the name: "gmane.comp.db.postgresql.dbdpg"  

COPYRIGHT

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.  

ACKNOWLEDGMENTS

See also DBI/ACKNOWLEDGMENTS.

---

 

Index

NAME

SYNOPSIS

DESCRIPTION

MODULE DOCUMENTATION

THE DBI CLASS

DBI Class Methods

DBI Dynamic Attributes

METHODS COMMON TO ALL HANDLES

ATTRIBUTES COMMON TO ALL HANDLES

DBI DATABASE HANDLE OBJECTS

Database Handle Methods

Database Handle Attributes

DBI STATEMENT HANDLE OBJECTS

Statement Handle Methods

Statement Handle Attributes

FURTHER INFORMATION

Transactions

Large Objects

Cursors

Data-Type bool

Schema support

SEE ALSO

AUTHORS

COPYRIGHT

ACKNOWLEDGMENTS