/usr/man/cat.3/DBD__Pg.3.Z(/usr/man/cat.3/DBD__Pg.3.Z)
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 fol-
lowing 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 unde-
fined. 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, PGPORT, 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 PQer-
rorMessage 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 acces-
sible 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" func-
tions 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
The REMARKS field will be returned as NULL for Postgres versions
7.1.x and older.
$lobjId = $dbh->func($mode, 'lo_creat');
Creates a new large object and returns the object-id. $mode is a
bitmask 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 cur-
rent 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.
$ret = $dbh->func($lobjId, '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 fail-
ure, 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 fin-
ished 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.
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 data-
base 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
The REMARKS field will be returned as NULL for Postgres versions
7.1.x and older.
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).
primary_key_info
$sth = $dbh->primary_key_info( $catalog, $schema, $table, \%attr );
Supported by the driver as proposed by DBI. The $catalog argument
is curently unused, and the $schema argument has no effect against
servers running version 7.2 or less. There are no search patterns
allowed, but leaving the $schema argument blank will cause the
first table found in the schema search path to be used. An addi-
tional field, DATA_TYPE, is returned and shows the data type for
each of the arguments in the COLUMN_NAME field.
In addition to the standard format of returning one row for each
column found for the primary key, you can pass the argument
"pg_onerow" to force a single row to be used. If the primary key
has multiple columns, the KEY_SEQ, COLUMN_NAME, and DATA_TYPE
fields will return a comma-delimited string. If "pg_onerow" is set
to "2", the fields will be returned as an arrayref, which can be
useful when multiple columns are involved:
$sth = $dbh->primary_key_info('', '', 'dbd_pg_test', {pg_onerow => 2});
if (defined $sth) {
my $pk = $sth->fetchall_arrayref()->[0];
print "Table $pk->[2] has a primary key on these columns:\n";
for (my $x=0; defined $pk->[3][$x]; $x++) {
print "Column: $pk->[3][$x] (data type: $pk->[6][$x])\n";
}
}
primary_key
Supported by the driver as proposed by DBI.
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, using the SQL/CLI vari-
ant. This function returns undef for PostgreSQL servers earlier
than version 7.3. There are no search patterns allowed, but leaving
the $schema argument blank will cause the first table found in the
schema search path to be used. Two additional fields, UK_DATA_TYPE
and FK_DATA_TYPE, are returned which show the data type for the
unique and foreign key columns. Foreign keys which have no named
constraint (where the referenced column only has an unique index)
will return undef for the UK_NAME field.
tables
@names = $dbh->tables( $catalog, $schema, $table, $type, \%attr );
Supported by the driver as proposed by DBI. This method returns all
tables and/or views which are visible to the current user: see the
table_info() for more information about the arguments. If the data-
base is version 7.3 or higher, the name of the schema appears
before the table or view name. This can be turned off by adding in
the "noprefix" attribute:
my @tables = $dbh->tables( '', '', 'dbd_pg_test', '', {noprefix => 1} );
type_info_all
$type_info_all = $dbh->type_info_all;
Supported by the driver as proposed by DBI. Information is only
provided for SQL datatypes and for frequently used datatypes. The
mapping between the PostgreSQL typename and the SQL92 datatype (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 datatypes
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 clas-
sification 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.
pg_bool_tf (boolean)
PostgreSQL specific attribute. If true, boolean values will be
returned as the characters 't' and 'f' instead of '1' and '0'.
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 implemen-
tation 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
(datatype 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 (array-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 com-
mand. Possible types are: INSERT, DELETE, UPDATE, SELECT.
FURTHER INFORMATION
Transactions
The transaction behavior is now controlled with the attribute AutoCom-
mit. 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' state-
ment.
Large Objects
The driver supports all large-objects related functions provided by
libpq via the func-interface. Please note, that starting with Post-
greSQL 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!
Datatype 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. You
may set the pg_bool_tf attribute to change the values back to 't' and
'f' if you wish.
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 Post-
greSQL 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-gen-
eral/>.
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.
perl v5.8.6 2004-02-25 Pg(3)
Man(1) output converted with
man2html