NAME

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

SYNOPSIS

use DBI;

$dbh = DBI->connect("dbi:PgSPI:internal", "", "");

DESCRIPTION

IF YOU ARE LOOKING FOR A WAY TO ACCESS POSTGRESQL DATABASE FROM A PERL SCRIPT RUNNING OUTSIDE OF YOUR DATABASE, LOOK AT DBD::Pg, YOU CANNOT USE THIS MODULE. THIS MODULE IS ONLY INTENDED FOR USE BY STORED PROCEDURES WRITTEN IN 'plperl' PROGRAMMING LANGUAGE RUNNING INSIDE POSTGRESQL.

DBD::PgSPI is a Perl module which works with the DBI module to provide access to PostgreSQL database from within pl/perl functions inside the database.

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, use the following syntax:

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

This is necessary to initialize SPI interface. You cannot specify any other parameters to connect(), and if you do, they'll be ignored..

data_sources

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

The driver supports this method, only returning 'dbi:PgSPI:internal', since there is only data source, the current database.

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.

func

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

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

(See DBD::Pg for further documentation on this)

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.

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.

selectall_arrayref

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

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;

Since the database is always 'up', this method always returns 1.

table_info =item tables =item type_info_all =item type_info See DBD::Pg documentation for details.

quote

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

Database Handle Attributes

AutoCommit (boolean)

Currently, since there are no nested transactions supported by PostgreSQL, you cannot turn off AutoCommit, and the database should be considered as 'transaction-unsupported'.

Name (string, read-only)

Always returns 'internal'.

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

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.

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.

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, with the restriction, that the types are PostgreSQL specific data-types which do not correspond to international standards.

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

Cursors

Cursors (portals in SPI parlance) are not currently being used in this implementation.

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

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)

• DBD-PgSPI by Alex Pilosov (alex@pilosoft.com)

  Major parts of this package have been copied from DBI, DBD::Oracle, DBD::Pg.

COPYRIGHT

The DBD::PgSPI 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.

ACKNOWLEDGMENTS

See also DBI/ACKNOWLEDGMENTS.

cpanm DBD::PgSPI

perl -MCPAN -e shell
install DBD::PgSPI