summaryrefslogtreecommitdiff
path: root/Master/tlpkg/tlperl/lib/DBD/Pg.pm
diff options
context:
space:
mode:
authorNorbert Preining <preining@logic.at>2010-05-12 16:54:37 +0000
committerNorbert Preining <preining@logic.at>2010-05-12 16:54:37 +0000
commit661c41a09e39a182865e0b51e34cc995a0dc96e8 (patch)
tree2f79bb1406e22fdcb2587be8ffda6c0c609d7932 /Master/tlpkg/tlperl/lib/DBD/Pg.pm
parentb645030efc22e13c2498a1522083634ab91b2de1 (diff)
move tlperl.straw to tlperl
git-svn-id: svn://tug.org/texlive/trunk@18210 c570f23f-e606-0410-a88d-b1316a301751
Diffstat (limited to 'Master/tlpkg/tlperl/lib/DBD/Pg.pm')
-rwxr-xr-xMaster/tlpkg/tlperl/lib/DBD/Pg.pm4208
1 files changed, 4208 insertions, 0 deletions
diff --git a/Master/tlpkg/tlperl/lib/DBD/Pg.pm b/Master/tlpkg/tlperl/lib/DBD/Pg.pm
new file mode 100755
index 00000000000..0218e222a07
--- /dev/null
+++ b/Master/tlpkg/tlperl/lib/DBD/Pg.pm
@@ -0,0 +1,4208 @@
+# -*-cperl-*-
+# $Id: Pg.pm 13752 2010-01-20 19:19:06Z turnstep $
+#
+# Copyright (c) 2002-2010 Greg Sabino Mullane and others: see the Changes file
+# Portions Copyright (c) 2002 Jeffrey W. Baker
+# Portions Copyright (c) 1997-2001 Edmund Mergl
+# Portions Copyright (c) 1994-1997 Tim Bunce
+#
+# You may distribute under the terms of either the GNU General Public
+# License or the Artistic License, as specified in the Perl README file.
+
+
+use strict;
+use warnings;
+use 5.006001;
+
+{
+ package DBD::Pg;
+
+ use version; our $VERSION = qv('2.16.1');
+
+ use DBI ();
+ use DynaLoader ();
+ use Exporter ();
+ use vars qw(@ISA %EXPORT_TAGS $err $errstr $sqlstate $drh $dbh $DBDPG_DEFAULT @EXPORT);
+ @ISA = qw(DynaLoader Exporter);
+
+
+ %EXPORT_TAGS =
+ (
+ async => [qw(PG_ASYNC PG_OLDQUERY_CANCEL PG_OLDQUERY_WAIT)],
+ pg_types => [qw(
+ PG_ABSTIME PG_ABSTIMEARRAY PG_ACLITEM PG_ACLITEMARRAY PG_ANY
+ PG_ANYARRAY PG_ANYELEMENT PG_ANYENUM PG_ANYNONARRAY PG_BIT
+ PG_BITARRAY PG_BOOL PG_BOOLARRAY PG_BOX PG_BOXARRAY
+ PG_BPCHAR PG_BPCHARARRAY PG_BYTEA PG_BYTEAARRAY PG_CHAR
+ PG_CHARARRAY PG_CID PG_CIDARRAY PG_CIDR PG_CIDRARRAY
+ PG_CIRCLE PG_CIRCLEARRAY PG_CSTRING PG_CSTRINGARRAY PG_DATE
+ PG_DATEARRAY PG_FLOAT4 PG_FLOAT4ARRAY PG_FLOAT8 PG_FLOAT8ARRAY
+ PG_GTSVECTOR PG_GTSVECTORARRAY PG_INET PG_INETARRAY PG_INT2
+ PG_INT2ARRAY PG_INT2VECTOR PG_INT2VECTORARRAY PG_INT4 PG_INT4ARRAY
+ PG_INT8 PG_INT8ARRAY PG_INTERNAL PG_INTERVAL PG_INTERVALARRAY
+ PG_LANGUAGE_HANDLER PG_LINE PG_LINEARRAY PG_LSEG PG_LSEGARRAY
+ PG_MACADDR PG_MACADDRARRAY PG_MONEY PG_MONEYARRAY PG_NAME
+ PG_NAMEARRAY PG_NUMERIC PG_NUMERICARRAY PG_OID PG_OIDARRAY
+ PG_OIDVECTOR PG_OIDVECTORARRAY PG_OPAQUE PG_PATH PG_PATHARRAY
+ PG_PG_ATTRIBUTE PG_PG_CLASS PG_PG_PROC PG_PG_TYPE PG_POINT
+ PG_POINTARRAY PG_POLYGON PG_POLYGONARRAY PG_RECORD PG_RECORDARRAY
+ PG_REFCURSOR PG_REFCURSORARRAY PG_REGCLASS PG_REGCLASSARRAY PG_REGCONFIG
+ PG_REGCONFIGARRAY PG_REGDICTIONARY PG_REGDICTIONARYARRAY PG_REGOPER PG_REGOPERARRAY
+ PG_REGOPERATOR PG_REGOPERATORARRAY PG_REGPROC PG_REGPROCARRAY PG_REGPROCEDURE
+ PG_REGPROCEDUREARRAY PG_REGTYPE PG_REGTYPEARRAY PG_RELTIME PG_RELTIMEARRAY
+ PG_SMGR PG_TEXT PG_TEXTARRAY PG_TID PG_TIDARRAY
+ PG_TIME PG_TIMEARRAY PG_TIMESTAMP PG_TIMESTAMPARRAY PG_TIMESTAMPTZ
+ PG_TIMESTAMPTZARRAY PG_TIMETZ PG_TIMETZARRAY PG_TINTERVAL PG_TINTERVALARRAY
+ PG_TRIGGER PG_TSQUERY PG_TSQUERYARRAY PG_TSVECTOR PG_TSVECTORARRAY
+ PG_TXID_SNAPSHOT PG_TXID_SNAPSHOTARRAY PG_UNKNOWN PG_UUID PG_UUIDARRAY
+ PG_VARBIT PG_VARBITARRAY PG_VARCHAR PG_VARCHARARRAY PG_VOID
+ PG_XID PG_XIDARRAY PG_XML PG_XMLARRAY
+ )]
+ );
+
+ {
+ package DBD::Pg::DefaultValue;
+ sub new { my $self = {}; return bless $self, shift; }
+ }
+ $DBDPG_DEFAULT = DBD::Pg::DefaultValue->new();
+ Exporter::export_ok_tags('pg_types', 'async');
+ @EXPORT = qw($DBDPG_DEFAULT PG_ASYNC PG_OLDQUERY_CANCEL PG_OLDQUERY_WAIT PG_BYTEA);
+
+ require_version DBI 1.52;
+
+ bootstrap DBD::Pg $VERSION;
+
+ $err = 0; # holds error code for DBI::err
+ $errstr = ''; # holds error string for DBI::errstr
+ $sqlstate = ''; # holds five character SQLSTATE code
+ $drh = undef; # holds driver handle once initialized
+
+ ## These two methods are here to allow calling before connect()
+ sub parse_trace_flag {
+ my ($class, $flag) = @_;
+ return (0x7FFFFF00 - 0x08000000) if $flag eq 'DBD'; ## all but the prefix
+ return 0x01000000 if $flag eq 'pglibpq';
+ return 0x02000000 if $flag eq 'pgstart';
+ return 0x04000000 if $flag eq 'pgend';
+ return 0x08000000 if $flag eq 'pgprefix';
+ return 0x10000000 if $flag eq 'pglogin';
+ return 0x20000000 if $flag eq 'pgquote';
+ return DBI::parse_trace_flag($class, $flag);
+ }
+ sub parse_trace_flags {
+ my ($class, $flags) = @_;
+ return DBI::parse_trace_flags($class, $flags);
+ }
+
+ sub CLONE {
+ $drh = undef;
+ return;
+ }
+
+ ## Deprecated
+ sub _pg_use_catalog {
+ return 'pg_catalog.';
+ }
+
+ sub driver {
+ return $drh if defined $drh;
+ my($class, $attr) = @_;
+
+ $class .= '::dr';
+
+ $drh = DBI::_new_drh($class, {
+ 'Name' => 'Pg',
+ 'Version' => $VERSION,
+ 'Err' => \$DBD::Pg::err,
+ 'Errstr' => \$DBD::Pg::errstr,
+ 'State' => \$DBD::Pg::sqlstate,
+ 'Attribution' => "DBD::Pg $VERSION by Greg Sabino Mullane and others",
+ });
+
+
+ DBD::Pg::db->install_method('pg_cancel');
+ DBD::Pg::db->install_method('pg_endcopy');
+ DBD::Pg::db->install_method('pg_getline');
+ DBD::Pg::db->install_method('pg_getcopydata');
+ DBD::Pg::db->install_method('pg_getcopydata_async');
+ DBD::Pg::db->install_method('pg_notifies');
+ DBD::Pg::db->install_method('pg_putcopydata');
+ DBD::Pg::db->install_method('pg_putcopyend');
+ DBD::Pg::db->install_method('pg_ping');
+ DBD::Pg::db->install_method('pg_putline');
+ DBD::Pg::db->install_method('pg_ready');
+ DBD::Pg::db->install_method('pg_release');
+ DBD::Pg::db->install_method('pg_result');
+ DBD::Pg::db->install_method('pg_rollback_to');
+ DBD::Pg::db->install_method('pg_savepoint');
+ DBD::Pg::db->install_method('pg_server_trace');
+ DBD::Pg::db->install_method('pg_server_untrace');
+ DBD::Pg::db->install_method('pg_type_info');
+
+ DBD::Pg::st->install_method('pg_cancel');
+ DBD::Pg::st->install_method('pg_result');
+ DBD::Pg::st->install_method('pg_ready');
+
+ DBD::Pg::db->install_method('pg_lo_creat');
+ DBD::Pg::db->install_method('pg_lo_open');
+ DBD::Pg::db->install_method('pg_lo_write');
+ DBD::Pg::db->install_method('pg_lo_read');
+ DBD::Pg::db->install_method('pg_lo_lseek');
+ DBD::Pg::db->install_method('pg_lo_tell');
+ DBD::Pg::db->install_method('pg_lo_close');
+ DBD::Pg::db->install_method('pg_lo_unlink');
+ DBD::Pg::db->install_method('pg_lo_import');
+ DBD::Pg::db->install_method('pg_lo_export');
+
+ return $drh;
+
+ } ## end of driver
+
+
+ 1;
+
+} ## end of package DBD::Pg
+
+
+{
+ package DBD::Pg::dr;
+
+ use strict;
+
+ ## Returns an array of formatted database names from the pg_database table
+ sub data_sources {
+
+ my $drh = shift;
+ my $attr = shift || '';
+ ## Future: connect to "postgres" when the minimum version we support is 8.0
+ my $connstring = 'dbname=template1';
+ if ($ENV{DBI_DSN}) {
+ ($connstring = $ENV{DBI_DSN}) =~ s/dbi:Pg://;
+ }
+ if (length $attr) {
+ $connstring .= ";$attr";
+ }
+
+ my $dbh = DBD::Pg::dr::connect($drh, $connstring) or return undef;
+ $dbh->{AutoCommit}=1;
+ my $SQL = 'SELECT pg_catalog.quote_ident(datname) FROM pg_catalog.pg_database ORDER BY 1';
+ my $sth = $dbh->prepare($SQL);
+ $sth->execute() or die $DBI::errstr;
+ $attr and $attr = ";$attr";
+ my @sources = map { "dbi:Pg:dbname=$_->[0]$attr" } @{$sth->fetchall_arrayref()};
+ $dbh->disconnect;
+ return @sources;
+ }
+
+
+ sub connect { ## no critic (ProhibitBuiltinHomonyms)
+ my ($drh, $dbname, $user, $pass, $attr) = @_;
+
+ ## Allow "db" and "database" as synonyms for "dbname"
+ $dbname =~ s/\b(?:db|database)\s*=/dbname=/;
+
+ my $name = $dbname;
+ if ($dbname =~ m{dbname\s*=\s*[\"\']([^\"\']+)}) {
+ $name = "'$1'";
+ $dbname =~ s/\"/\'/g;
+ }
+ elsif ($dbname =~ m{dbname\s*=\s*([^;]+)}) {
+ $name = $1;
+ }
+
+ $user = defined($user) ? $user : defined $ENV{DBI_USER} ? $ENV{DBI_USER} : '';
+ $pass = defined($pass) ? $pass : defined $ENV{DBI_PASS} ? $ENV{DBI_PASS} : '';
+
+ my ($dbh) = DBI::_new_dbh($drh, {
+ 'Name' => $dbname,
+ 'Username' => $user,
+ 'CURRENT_USER' => $user,
+ });
+
+ # Connect to the database..
+ DBD::Pg::db::_login($dbh, $dbname, $user, $pass) or return undef;
+
+ my $version = $dbh->{pg_server_version};
+ $dbh->{private_dbdpg}{version} = $version;
+
+ if ($attr) {
+ if ($attr->{dbd_verbose}) {
+ $dbh->trace('DBD');
+ }
+ }
+
+ return $dbh;
+ }
+
+ sub private_attribute_info {
+ return {
+ };
+ }
+
+} ## end of package DBD::Pg::dr
+
+
+{
+ package DBD::Pg::db;
+
+ use DBI qw(:sql_types);
+
+ use strict;
+
+ sub parse_trace_flag {
+ my ($h, $flag) = @_;
+ return DBD::Pg->parse_trace_flag($flag);
+ }
+
+ sub prepare {
+ my($dbh, $statement, @attribs) = @_;
+
+ return undef if ! defined $statement;
+
+ # Create a 'blank' statement handle:
+ my $sth = DBI::_new_sth($dbh, {
+ 'Statement' => $statement,
+ });
+
+ DBD::Pg::st::_prepare($sth, $statement, @attribs) || 0;
+
+ return $sth;
+ }
+
+ sub last_insert_id {
+
+ my ($dbh, $catalog, $schema, $table, $col, $attr) = @_;
+
+ ## Our ultimate goal is to get a sequence
+ my ($sth, $count, $SQL, $sequence);
+
+ ## Cache all of our table lookups? Default is yes
+ my $cache = 1;
+
+ ## Catalog and col are not used
+ $schema = '' if ! defined $schema;
+ $table = '' if ! defined $table;
+ my $cachename = "lii$table$schema";
+
+ if (defined $attr and length $attr) {
+ ## If not a hash, assume it is a sequence name
+ if (! ref $attr) {
+ $attr = {sequence => $attr};
+ }
+ elsif (ref $attr ne 'HASH') {
+ $dbh->set_err(1, 'last_insert_id must be passed a hashref as the final argument');
+ return undef;
+ }
+ ## Named sequence overrides any table or schema settings
+ if (exists $attr->{sequence} and length $attr->{sequence}) {
+ $sequence = $attr->{sequence};
+ }
+ if (exists $attr->{pg_cache}) {
+ $cache = $attr->{pg_cache};
+ }
+ }
+
+ if (! defined $sequence and exists $dbh->{private_dbdpg}{$cachename} and $cache) {
+ $sequence = $dbh->{private_dbdpg}{$cachename};
+ }
+ elsif (! defined $sequence) {
+ ## At this point, we must have a valid table name
+ if (! length $table) {
+ $dbh->set_err(1, 'last_insert_id needs at least a sequence or table name');
+ return undef;
+ }
+ my @args = ($table);
+ ## Make sure the table in question exists and grab its oid
+ my ($schemajoin,$schemawhere) = ('','');
+ if (length $schema) {
+ $schemajoin = "\n JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)";
+ $schemawhere = "\n AND n.nspname = ?";
+ push @args, $schema;
+ }
+ $SQL = "SELECT c.oid FROM pg_catalog.pg_class c $schemajoin\n WHERE relname = ?$schemawhere";
+ if (! length $schema) {
+ $SQL .= ' AND pg_catalog.pg_table_is_visible(c.oid)';
+ }
+ $sth = $dbh->prepare_cached($SQL);
+ $count = $sth->execute(@args);
+ if (!defined $count or $count eq '0E0') {
+ $sth->finish();
+ my $message = qq{Could not find the table "$table"};
+ length $schema and $message .= qq{ in the schema "$schema"};
+ $dbh->set_err(1, $message);
+ return undef;
+ }
+ my $oid = $sth->fetchall_arrayref()->[0][0];
+ $oid =~ /(\d+)/ or die qq{OID was not numeric?!?\n};
+ $oid = $1;
+ ## This table has a primary key. Is there a sequence associated with it via a unique, indexed column?
+ $SQL = "SELECT a.attname, i.indisprimary, pg_catalog.pg_get_expr(adbin,adrelid)\n".
+ "FROM pg_catalog.pg_index i, pg_catalog.pg_attribute a, pg_catalog.pg_attrdef d\n ".
+ "WHERE i.indrelid = $oid AND d.adrelid=a.attrelid AND d.adnum=a.attnum\n".
+ " AND a.attrelid = $oid AND i.indisunique IS TRUE\n".
+ " AND a.atthasdef IS TRUE AND i.indkey[0]=a.attnum\n".
+ q{ AND d.adsrc ~ '^nextval'};
+ $sth = $dbh->prepare($SQL);
+ $count = $sth->execute();
+ if (!defined $count or $count eq '0E0') {
+ $sth->finish();
+ $dbh->set_err(1, qq{No suitable column found for last_insert_id of table "$table"});
+ return undef;
+ }
+ my $info = $sth->fetchall_arrayref();
+
+ ## We have at least one with a default value. See if we can determine sequences
+ my @def;
+ for (@$info) {
+ next unless $_->[2] =~ /^nextval\(+'([^']+)'::/o;
+ push @$_, $1;
+ push @def, $_;
+ }
+ if (!@def) {
+ $dbh->set_err(1, qq{No suitable column found for last_insert_id of table "$table"\n});
+ }
+ ## Tiebreaker goes to the primary keys
+ if (@def > 1) {
+ my @pri = grep { $_->[1] } @def;
+ if (1 != @pri) {
+ $dbh->set_err(1, qq{No suitable column found for last_insert_id of table "$table"\n});
+ }
+ @def = @pri;
+ }
+ $sequence = $def[0]->[3];
+ ## Cache this information for subsequent calls
+ $dbh->{private_dbdpg}{$cachename} = $sequence;
+ }
+
+ $sth = $dbh->prepare_cached('SELECT currval(?)');
+ $count = $sth->execute($sequence);
+ return undef if ! defined $count;
+ return $sth->fetchall_arrayref()->[0][0];
+
+ } ## end of last_insert_id
+
+ sub ping {
+ my $dbh = shift;
+ local $SIG{__WARN__} = sub { } if $dbh->FETCH('PrintError');
+ my $ret = DBD::Pg::db::_ping($dbh);
+ return $ret < 1 ? 0 : $ret;
+ }
+
+ sub pg_ping {
+ my $dbh = shift;
+ local $SIG{__WARN__} = sub { } if $dbh->FETCH('PrintError');
+ return DBD::Pg::db::_ping($dbh);
+ }
+
+ sub pg_type_info {
+ my($dbh,$pg_type) = @_;
+ local $SIG{__WARN__} = sub { } if $dbh->FETCH('PrintError');
+ my $ret = DBD::Pg::db::_pg_type_info($pg_type);
+ return $ret;
+ }
+
+ # Column expected in statement handle returned.
+ # table_cat, table_schem, table_name, column_name, data_type, type_name,
+ # column_size, buffer_length, DECIMAL_DIGITS, NUM_PREC_RADIX, NULLABLE,
+ # REMARKS, COLUMN_DEF, SQL_DATA_TYPE, SQL_DATETIME_SUB, CHAR_OCTET_LENGTH,
+ # ORDINAL_POSITION, IS_NULLABLE
+ # The result set is ordered by TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.
+
+ sub column_info {
+ my $dbh = shift;
+ my ($catalog, $schema, $table, $column) = @_;
+
+ my @search;
+ ## If the schema or table has an underscore or a %, use a LIKE comparison
+ if (defined $schema and length $schema) {
+ push @search, 'n.nspname ' . ($schema =~ /[_%]/ ? 'LIKE ' : '= ') .
+ $dbh->quote($schema);
+ }
+ if (defined $table and length $table) {
+ push @search, 'c.relname ' . ($table =~ /[_%]/ ? 'LIKE ' : '= ') .
+ $dbh->quote($table);
+ }
+ if (defined $column and length $column) {
+ push @search, 'a.attname ' . ($column =~ /[_%]/ ? 'LIKE ' : '= ') .
+ $dbh->quote($column);
+ }
+
+ my $whereclause = join "\n\t\t\t\tAND ", '', @search;
+
+ my $schemajoin = 'JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)';
+
+ my $remarks = 'pg_catalog.col_description(a.attrelid, a.attnum)';
+
+ my $column_def = $dbh->{private_dbdpg}{version} >= 80000
+ ? 'pg_catalog.pg_get_expr(af.adbin, af.adrelid)'
+ : 'af.adsrc';
+
+ my $col_info_sql = qq!
+ SELECT
+ NULL::text AS "TABLE_CAT"
+ , quote_ident(n.nspname) AS "TABLE_SCHEM"
+ , quote_ident(c.relname) AS "TABLE_NAME"
+ , quote_ident(a.attname) AS "COLUMN_NAME"
+ , a.atttypid AS "DATA_TYPE"
+ , pg_catalog.format_type(a.atttypid, NULL) AS "TYPE_NAME"
+ , a.attlen AS "COLUMN_SIZE"
+ , NULL::text AS "BUFFER_LENGTH"
+ , NULL::text AS "DECIMAL_DIGITS"
+ , NULL::text AS "NUM_PREC_RADIX"
+ , CASE a.attnotnull WHEN 't' THEN 0 ELSE 1 END AS "NULLABLE"
+ , $remarks AS "REMARKS"
+ , $column_def AS "COLUMN_DEF"
+ , NULL::text AS "SQL_DATA_TYPE"
+ , NULL::text AS "SQL_DATETIME_SUB"
+ , NULL::text AS "CHAR_OCTET_LENGTH"
+ , a.attnum AS "ORDINAL_POSITION"
+ , CASE a.attnotnull WHEN 't' THEN 'NO' ELSE 'YES' END AS "IS_NULLABLE"
+ , pg_catalog.format_type(a.atttypid, a.atttypmod) AS "pg_type"
+ , '?' AS "pg_constraint"
+ , n.nspname AS "pg_schema"
+ , c.relname AS "pg_table"
+ , a.attname AS "pg_column"
+ , a.attrelid AS "pg_attrelid"
+ , a.attnum AS "pg_attnum"
+ , a.atttypmod AS "pg_atttypmod"
+ , t.typtype AS "_pg_type_typtype"
+ , t.oid AS "_pg_type_oid"
+ FROM
+ pg_catalog.pg_type t
+ JOIN pg_catalog.pg_attribute a ON (t.oid = a.atttypid)
+ JOIN pg_catalog.pg_class c ON (a.attrelid = c.oid)
+ LEFT JOIN pg_catalog.pg_attrdef af ON (a.attnum = af.adnum AND a.attrelid = af.adrelid)
+ $schemajoin
+ WHERE
+ a.attnum >= 0
+ AND c.relkind IN ('r','v')
+ $whereclause
+ ORDER BY "TABLE_SCHEM", "TABLE_NAME", "ORDINAL_POSITION"
+ !;
+
+ my $data = $dbh->selectall_arrayref($col_info_sql) or return undef;
+
+ # To turn the data back into a statement handle, we need
+ # to fetch the data as an array of arrays, and also have a
+ # a matching array of all the column names
+ my %col_map = (qw/
+ TABLE_CAT 0
+ TABLE_SCHEM 1
+ TABLE_NAME 2
+ COLUMN_NAME 3
+ DATA_TYPE 4
+ TYPE_NAME 5
+ COLUMN_SIZE 6
+ BUFFER_LENGTH 7
+ DECIMAL_DIGITS 8
+ NUM_PREC_RADIX 9
+ NULLABLE 10
+ REMARKS 11
+ COLUMN_DEF 12
+ SQL_DATA_TYPE 13
+ SQL_DATETIME_SUB 14
+ CHAR_OCTET_LENGTH 15
+ ORDINAL_POSITION 16
+ IS_NULLABLE 17
+ pg_type 18
+ pg_constraint 19
+ pg_schema 20
+ pg_table 21
+ pg_column 22
+ pg_enum_values 23
+ /);
+
+ for my $row (@$data) {
+ my $typoid = pop @$row;
+ my $typtype = pop @$row;
+ my $typmod = pop @$row;
+ my $attnum = pop @$row;
+ my $aid = pop @$row;
+
+ $row->[$col_map{COLUMN_SIZE}] =
+ _calc_col_size($typmod,$row->[$col_map{COLUMN_SIZE}]);
+
+ # Replace the Pg type with the SQL_ type
+ $row->[$col_map{DATA_TYPE}] = DBD::Pg::db::pg_type_info($dbh,$row->[$col_map{DATA_TYPE}]);
+
+ # Add pg_constraint
+ my $SQL = q{SELECT consrc FROM pg_catalog.pg_constraint WHERE contype = 'c' AND }.
+ qq{conrelid = $aid AND conkey = '{$attnum}'};
+ my $info = $dbh->selectall_arrayref($SQL);
+ if (@$info) {
+ $row->[19] = $info->[0][0];
+ }
+ else {
+ $row->[19] = undef;
+ }
+
+ if ( $typtype eq 'e' ) {
+ $SQL = "SELECT enumlabel FROM pg_catalog.pg_enum WHERE enumtypid = $typoid ORDER BY oid";
+ $row->[23] = $dbh->selectcol_arrayref($SQL);
+ }
+ else {
+ $row->[23] = undef;
+ }
+ }
+
+ # Since we've processed the data in Perl, we have to jump through a hoop
+ # To turn it back into a statement handle
+ #
+ return _prepare_from_data
+ (
+ 'column_info',
+ $data,
+ [ sort { $col_map{$a} <=> $col_map{$b} } keys %col_map]
+ );
+ }
+
+ sub _prepare_from_data {
+ my ($statement, $data, $names, %attr) = @_;
+ my $sponge = DBI->connect('dbi:Sponge:', '', '', { RaiseError => 1 });
+ my $sth = $sponge->prepare($statement, { rows=>$data, NAME=>$names, %attr });
+ return $sth;
+ }
+
+ sub statistics_info {
+
+ my $dbh = shift;
+ my ($catalog, $schema, $table, $unique_only, $quick, $attr) = @_;
+
+ ## Catalog is ignored, but table is mandatory
+ return undef unless defined $table and length $table;
+
+ my $schema_where = '';
+ my @exe_args = ($table);
+
+ my $input_schema = (defined $schema and length $schema) ? 1 : 0;
+
+ if ($input_schema) {
+ $schema_where = 'AND n.nspname = ? AND n.oid = d.relnamespace';
+ push(@exe_args, $schema);
+ }
+ else {
+ $schema_where = 'AND n.oid = d.relnamespace';
+ }
+
+ my $table_stats_sql = qq{
+ SELECT d.relpages, d.reltuples, n.nspname
+ FROM pg_catalog.pg_class d, pg_catalog.pg_namespace n
+ WHERE d.relname = ? $schema_where
+ };
+
+ my $colnames_sql = qq{
+ SELECT
+ a.attnum, a.attname
+ FROM
+ pg_catalog.pg_attribute a, pg_catalog.pg_class d, pg_catalog.pg_namespace n
+ WHERE
+ a.attrelid = d.oid AND d.relname = ? $schema_where
+ };
+
+ my $stats_sql = qq{
+ SELECT
+ c.relname, i.indkey, i.indisunique, i.indisclustered, a.amname,
+ n.nspname, c.relpages, c.reltuples, i.indexprs,
+ pg_get_expr(i.indpred,i.indrelid) as predicate
+ FROM
+ pg_catalog.pg_index i, pg_catalog.pg_class c,
+ pg_catalog.pg_class d, pg_catalog.pg_am a,
+ pg_catalog.pg_namespace n
+ WHERE
+ d.relname = ? $schema_where AND d.oid = i.indrelid
+ AND i.indexrelid = c.oid AND c.relam = a.oid
+ ORDER BY
+ i.indisunique desc, a.amname, c.relname
+ };
+
+ my @output_rows;
+
+ # Table-level stats
+ if (!$unique_only) {
+ my $table_stats_sth = $dbh->prepare($table_stats_sql);
+ $table_stats_sth->execute(@exe_args) or return undef;
+ my $tst = $table_stats_sth->fetchrow_hashref or return undef;
+ push(@output_rows, [
+ undef, # TABLE_CAT
+ $tst->{nspname}, # TABLE_SCHEM
+ $table, # TABLE_NAME
+ undef, # NON_UNIQUE
+ undef, # INDEX_QUALIFIER
+ undef, # INDEX_NAME
+ 'table', # TYPE
+ undef, # ORDINAL_POSITION
+ undef, # COLUMN_NAME
+ undef, # ASC_OR_DESC
+ $tst->{reltuples},# CARDINALITY
+ $tst->{relpages}, # PAGES
+ undef, # FILTER_CONDITION
+ ]);
+ }
+
+ # Fetch the column names for later use
+ my $colnames_sth = $dbh->prepare($colnames_sql);
+ $colnames_sth->execute(@exe_args) or return undef;
+ my $colnames = $colnames_sth->fetchall_hashref('attnum');
+
+ # Fetch the index definitions
+ my $sth = $dbh->prepare($stats_sql);
+ $sth->execute(@exe_args) or return undef;
+
+ STAT_ROW:
+ #use Data::Dumper;
+ #warn Dumper $stats_sql;
+ while (my $row = $sth->fetchrow_hashref) {
+ #warn Dumper $row;
+ next if $row->{indexprs}; # We can't return these accurately via this interface ...
+ next if $unique_only and !$row->{indisunique};
+
+ my $indtype = $row->{indisclustered}
+ ? 'clustered'
+ : ( $row->{amname} eq 'btree' )
+ ? 'btree'
+ : ($row->{amname} eq 'hash' )
+ ? 'hashed' : 'other';
+
+ my $nonunique = $row->{indisunique} ? 0 : 1;
+
+ my @index_row = (
+ undef, # TABLE_CAT
+ $row->{nspname}, # TABLE_SCHEM
+ $table, # TABLE_NAME
+ $nonunique, # NON_UNIQUE
+ undef, # INDEX_QUALIFIER
+ $row->{relname}, # INDEX_NAME
+ $indtype, # TYPE
+ undef, # ORDINAL_POSITION
+ undef, # COLUMN_NAME
+ 'A', # ASC_OR_DESC
+ $row->{reltuples}, # CARDINALITY
+ $row->{relpages}, # PAGES
+ $row->{predicate}, # FILTER_CONDITION
+ );
+
+ my $col_nums = $row->{indkey};
+ $col_nums =~ s/^\s+//;
+ my @col_nums = split(/\s+/, $col_nums);
+
+ my $ord_pos = 1;
+ for my $col_num (@col_nums) {
+ my @copy = @index_row;
+ $copy[7] = $ord_pos++; # ORDINAL_POSITION
+ $copy[8] = $colnames->{$col_num}->{attname}; # COLUMN_NAME
+ push(@output_rows, \@copy);
+ }
+ }
+
+ my @output_colnames = qw/ TABLE_CAT TABLE_SCHEM TABLE_NAME NON_UNIQUE INDEX_QUALIFIER
+ INDEX_NAME TYPE ORDINAL_POSITION COLUMN_NAME ASC_OR_DESC
+ CARDINALITY PAGES FILTER_CONDITION /;
+
+ return _prepare_from_data('statistics_info', \@output_rows, \@output_colnames);
+ }
+
+ sub primary_key_info {
+
+ my $dbh = shift;
+ my ($catalog, $schema, $table, $attr) = @_;
+
+ ## Catalog is ignored, but table is mandatory
+ return undef unless defined $table and length $table;
+
+ my $whereclause = 'AND c.relname = ' . $dbh->quote($table);
+
+ if (defined $schema and length $schema) {
+ $whereclause .= "\n\t\t\tAND n.nspname = " . $dbh->quote($schema);
+ }
+
+ my $TSJOIN = 'pg_catalog.pg_tablespace t ON (t.oid = c.reltablespace)';
+ if ($dbh->{private_dbdpg}{version} < 80000) {
+ $TSJOIN = '(SELECT 0 AS oid, 0 AS spcname, 0 AS spclocation LIMIT 0) AS t ON (t.oid=1)';
+ }
+
+ my $pri_key_sql = qq{
+ SELECT
+ c.oid
+ , quote_ident(n.nspname)
+ , quote_ident(c.relname)
+ , quote_ident(c2.relname)
+ , i.indkey, quote_ident(t.spcname), quote_ident(t.spclocation)
+ , n.nspname, c.relname, c2.relname
+ FROM
+ pg_catalog.pg_class c
+ JOIN pg_catalog.pg_index i ON (i.indrelid = c.oid)
+ JOIN pg_catalog.pg_class c2 ON (c2.oid = i.indexrelid)
+ LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
+ LEFT JOIN $TSJOIN
+ WHERE
+ i.indisprimary IS TRUE
+ $whereclause
+ };
+
+ my $sth = $dbh->prepare($pri_key_sql) or return undef;
+ $sth->execute();
+ my $info = $sth->fetchall_arrayref()->[0];
+ return undef if ! defined $info;
+
+ # Get the attribute information
+ my $indkey = join ',', split /\s+/, $info->[4];
+ my $sql = qq{
+ SELECT a.attnum, pg_catalog.quote_ident(a.attname) AS colname,
+ pg_catalog.quote_ident(t.typname) AS typename
+ FROM pg_catalog.pg_attribute a, pg_catalog.pg_type t
+ WHERE a.attrelid = '$info->[0]'
+ AND a.atttypid = t.oid
+ AND attnum IN ($indkey);
+ };
+ $sth = $dbh->prepare($sql) or return undef;
+ $sth->execute();
+ my $attribs = $sth->fetchall_hashref('attnum');
+
+ my $pkinfo = [];
+
+ ## Normal way: complete "row" per column in the primary key
+ if (!exists $attr->{'pg_onerow'}) {
+ my $x=0;
+ my @key_seq = split/\s+/, $info->[4];
+ for (@key_seq) {
+ # TABLE_CAT
+ $pkinfo->[$x][0] = undef;
+ # SCHEMA_NAME
+ $pkinfo->[$x][1] = $info->[1];
+ # TABLE_NAME
+ $pkinfo->[$x][2] = $info->[2];
+ # COLUMN_NAME
+ $pkinfo->[$x][3] = $attribs->{$_}{colname};
+ # KEY_SEQ
+ $pkinfo->[$x][4] = $_;
+ # PK_NAME
+ $pkinfo->[$x][5] = $info->[3];
+ # DATA_TYPE
+ $pkinfo->[$x][6] = $attribs->{$_}{typename};
+ $pkinfo->[$x][7] = $info->[5];
+ $pkinfo->[$x][8] = $info->[6];
+ $pkinfo->[$x][9] = $info->[7];
+ $pkinfo->[$x][10] = $info->[8];
+ $pkinfo->[$x][11] = $info->[9];
+ $x++;
+ }
+ }
+ else { ## Nicer way: return only one row
+
+ # TABLE_CAT
+ $info->[0] = undef;
+ # TABLESPACES
+ $info->[7] = $info->[5];
+ $info->[8] = $info->[6];
+ # Unquoted names
+ $info->[9] = $info->[7];
+ $info->[10] = $info->[8];
+ $info->[11] = $info->[9];
+ # PK_NAME
+ $info->[5] = $info->[3];
+ # COLUMN_NAME
+ $info->[3] = 2==$attr->{'pg_onerow'} ?
+ [ map { $attribs->{$_}{colname} } split /\s+/, $info->[4] ] :
+ join ', ', map { $attribs->{$_}{colname} } split /\s+/, $info->[4];
+ # DATA_TYPE
+ $info->[6] = 2==$attr->{'pg_onerow'} ?
+ [ map { $attribs->{$_}{typename} } split /\s+/, $info->[4] ] :
+ join ', ', map { $attribs->{$_}{typename} } split /\s+/, $info->[4];
+ # KEY_SEQ
+ $info->[4] = 2==$attr->{'pg_onerow'} ?
+ [ split /\s+/, $info->[4] ] :
+ join ', ', split /\s+/, $info->[4];
+
+ $pkinfo = [$info];
+ }
+
+ my @cols = (qw(TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME
+ KEY_SEQ PK_NAME DATA_TYPE));
+ push @cols, 'pg_tablespace_name', 'pg_tablespace_location';
+ push @cols, 'pg_schema', 'pg_table', 'pg_column';
+
+ return _prepare_from_data('primary_key_info', $pkinfo, \@cols);
+
+ }
+
+ sub primary_key {
+ my $sth = primary_key_info(@_[0..3], {pg_onerow => 2});
+ return defined $sth ? @{$sth->fetchall_arrayref()->[0][3]} : ();
+ }
+
+
+ sub foreign_key_info {
+
+ my $dbh = shift;
+
+ ## PK: catalog, schema, table, FK: catalog, schema, table, attr
+
+ my $oldname = $dbh->{FetchHashKeyName};
+
+ local $dbh->{FetchHashKeyName} = 'NAME_lc';
+
+ ## Each of these may be undef or empty
+ my $pschema = $_[1] || '';
+ my $ptable = $_[2] || '';
+ my $fschema = $_[4] || '';
+ my $ftable = $_[5] || '';
+ my $args = $_[6];
+
+ ## No way to currently specify it, but we are ready when there is
+ my $odbc = 0;
+
+ ## Must have at least one named table
+ return undef if !$ptable and !$ftable;
+
+ ## If only the primary table is given, we return only those columns
+ ## that are used as foreign keys, even if that means that we return
+ ## unique keys but not primary one. We also return all the foreign
+ ## tables/columns that are referencing them, of course.
+
+ ## The first step is to find the oid of each specific table in the args:
+ ## Return undef if no matching relation found
+ my %oid;
+ for ([$ptable, $pschema, 'P'], [$ftable, $fschema, 'F']) {
+ if (length $_->[0]) {
+ my $SQL = "SELECT c.oid AS schema FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n\n".
+ 'WHERE c.relnamespace = n.oid AND c.relname = ' . $dbh->quote($_->[0]);
+ if (length $_->[1]) {
+ $SQL .= ' AND n.nspname = ' . $dbh->quote($_->[1]);
+ }
+ my $info = $dbh->selectall_arrayref($SQL);
+ return undef if ! @$info;
+ $oid{$_->[2]} = $info->[0][0];
+ }
+ }
+
+ ## We now need information about each constraint we care about.
+ ## Foreign table: only 'f' / Primary table: only 'p' or 'u'
+ my $WHERE = $odbc ? q{((contype = 'p'} : q{((contype IN ('p','u')};
+ if (length $ptable) {
+ $WHERE .= " AND conrelid=$oid{'P'}::oid";
+ }
+ else {
+ $WHERE .= " AND conrelid IN (SELECT DISTINCT confrelid FROM pg_catalog.pg_constraint WHERE conrelid=$oid{'F'}::oid)";
+ if (length $pschema) {
+ $WHERE .= ' AND n2.nspname = ' . $dbh->quote($pschema);
+ }
+ }
+
+ $WHERE .= ")\n \t\t\t\tOR \n \t\t\t\t(contype = 'f'";
+ if (length $ftable) {
+ $WHERE .= " AND conrelid=$oid{'F'}::oid";
+ if (length $ptable) {
+ $WHERE .= " AND confrelid=$oid{'P'}::oid";
+ }
+ }
+ else {
+ $WHERE .= " AND confrelid = $oid{'P'}::oid";
+ if (length $fschema) {
+ $WHERE .= ' AND n2.nspname = ' . $dbh->quote($fschema);
+ }
+ }
+ $WHERE .= '))';
+
+ ## Grab everything except specific column names:
+ my $fk_sql = qq{
+ SELECT conrelid, confrelid, contype, conkey, confkey,
+ pg_catalog.quote_ident(c.relname) AS t_name, pg_catalog.quote_ident(n2.nspname) AS t_schema,
+ pg_catalog.quote_ident(n.nspname) AS c_schema, pg_catalog.quote_ident(conname) AS c_name,
+ CASE
+ WHEN confupdtype = 'c' THEN 0
+ WHEN confupdtype = 'r' THEN 1
+ WHEN confupdtype = 'n' THEN 2
+ WHEN confupdtype = 'a' THEN 3
+ WHEN confupdtype = 'd' THEN 4
+ ELSE -1
+ END AS update,
+ CASE
+ WHEN confdeltype = 'c' THEN 0
+ WHEN confdeltype = 'r' THEN 1
+ WHEN confdeltype = 'n' THEN 2
+ WHEN confdeltype = 'a' THEN 3
+ WHEN confdeltype = 'd' THEN 4
+ ELSE -1
+ END AS delete,
+ CASE
+ WHEN condeferrable = 'f' THEN 7
+ WHEN condeferred = 't' THEN 6
+ WHEN condeferred = 'f' THEN 5
+ ELSE -1
+ END AS defer
+ FROM pg_catalog.pg_constraint k, pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_catalog.pg_namespace n2
+ WHERE $WHERE
+ AND k.connamespace = n.oid
+ AND k.conrelid = c.oid
+ AND c.relnamespace = n2.oid
+ ORDER BY conrelid ASC
+ };
+
+ my $sth = $dbh->prepare($fk_sql);
+ $sth->execute();
+ my $info = $sth->fetchall_arrayref({});
+ return undef if ! defined $info or ! @$info;
+
+ ## Return undef if just ptable given but no fk found
+ return undef if ! length $ftable and ! grep { $_->{'contype'} eq 'f'} @$info;
+
+ ## Figure out which columns we need information about
+ my %colnum;
+ for my $row (@$info) {
+ for (@{$row->{'conkey'}}) {
+ $colnum{$row->{'conrelid'}}{$_}++;
+ }
+ if ($row->{'contype'} eq 'f') {
+ for (@{$row->{'confkey'}}) {
+ $colnum{$row->{'confrelid'}}{$_}++;
+ }
+ }
+ }
+ ## Get the information about the columns computed above
+ my $SQL = qq{
+ SELECT a.attrelid, a.attnum, pg_catalog.quote_ident(a.attname) AS colname,
+ pg_catalog.quote_ident(t.typname) AS typename
+ FROM pg_catalog.pg_attribute a, pg_catalog.pg_type t
+ WHERE a.atttypid = t.oid
+ AND (\n};
+
+ $SQL .= join "\n\t\t\t\tOR\n" => map {
+ my $cols = join ',' => keys %{$colnum{$_}};
+ "\t\t\t\t( a.attrelid = '$_' AND a.attnum IN ($cols) )"
+ } sort keys %colnum;
+
+ $sth = $dbh->prepare(qq{$SQL \)});
+ $sth->execute();
+ my $attribs = $sth->fetchall_arrayref({});
+
+ ## Make a lookup hash
+ my %attinfo;
+ for (@$attribs) {
+ $attinfo{"$_->{'attrelid'}"}{"$_->{'attnum'}"} = $_;
+ }
+
+ ## This is an array in case we have identical oid/column combos. Lowest oid wins
+ my %ukey;
+ for my $c (grep { $_->{'contype'} ne 'f' } @$info) {
+ ## Munge multi-column keys into sequential order
+ my $multi = join ' ' => sort @{$c->{'conkey'}};
+ push @{$ukey{$c->{'conrelid'}}{$multi}}, $c;
+ }
+
+ ## Finally, return as a SQL/CLI structure:
+ my $fkinfo = [];
+ my $x=0;
+ for my $t (sort { $a->{'c_name'} cmp $b->{'c_name'} } grep { $_->{'contype'} eq 'f' } @$info) {
+ ## We need to find which constraint row (if any) matches our confrelid-confkey combo
+ ## by checking out ukey hash. We sort for proper matching of { 1 2 } vs. { 2 1 }
+ ## No match means we have a pure index constraint
+ my $u;
+ my $multi = join ' ' => sort @{$t->{'confkey'}};
+ if (exists $ukey{$t->{'confrelid'}}{$multi}) {
+ $u = $ukey{$t->{'confrelid'}}{$multi}->[0];
+ }
+ else {
+ ## Mark this as an index so we can fudge things later on
+ $multi = 'index';
+ ## Grab the first one found, modify later on as needed
+ $u = ((values %{$ukey{$t->{'confrelid'}}})[0]||[])->[0];
+ ## Bail in case there was no match
+ next if ! ref $u;
+ }
+
+ ## ODBC is primary keys only
+ next if $odbc and ($u->{'contype'} ne 'p' or $multi eq 'index');
+
+ my $conkey = $t->{'conkey'};
+ my $confkey = $t->{'confkey'};
+ for (my $y=0; $conkey->[$y]; $y++) {
+ # UK_TABLE_CAT
+ $fkinfo->[$x][0] = undef;
+ # UK_TABLE_SCHEM
+ $fkinfo->[$x][1] = $u->{'t_schema'};
+ # UK_TABLE_NAME
+ $fkinfo->[$x][2] = $u->{'t_name'};
+ # UK_COLUMN_NAME
+ $fkinfo->[$x][3] = $attinfo{$t->{'confrelid'}}{$confkey->[$y]}{'colname'};
+ # FK_TABLE_CAT
+ $fkinfo->[$x][4] = undef;
+ # FK_TABLE_SCHEM
+ $fkinfo->[$x][5] = $t->{'t_schema'};
+ # FK_TABLE_NAME
+ $fkinfo->[$x][6] = $t->{'t_name'};
+ # FK_COLUMN_NAME
+ $fkinfo->[$x][7] = $attinfo{$t->{'conrelid'}}{$conkey->[$y]}{'colname'};
+ # ORDINAL_POSITION
+ $fkinfo->[$x][8] = $conkey->[$y];
+ # UPDATE_RULE
+ $fkinfo->[$x][9] = "$t->{'update'}";
+ # DELETE_RULE
+ $fkinfo->[$x][10] = "$t->{'delete'}";
+ # FK_NAME
+ $fkinfo->[$x][11] = $t->{'c_name'};
+ # UK_NAME (may be undef if an index with no named constraint)
+ $fkinfo->[$x][12] = $multi eq 'index' ? undef : $u->{'c_name'};
+ # DEFERRABILITY
+ $fkinfo->[$x][13] = "$t->{'defer'}";
+ # UNIQUE_OR_PRIMARY
+ $fkinfo->[$x][14] = ($u->{'contype'} eq 'p' and $multi ne 'index') ? 'PRIMARY' : 'UNIQUE';
+ # UK_DATA_TYPE
+ $fkinfo->[$x][15] = $attinfo{$t->{'confrelid'}}{$confkey->[$y]}{'typename'};
+ # FK_DATA_TYPE
+ $fkinfo->[$x][16] = $attinfo{$t->{'conrelid'}}{$conkey->[$y]}{'typename'};
+ $x++;
+ } ## End each column in this foreign key
+ } ## End each foreign key
+
+ my @CLI_cols = (qw(
+ UK_TABLE_CAT UK_TABLE_SCHEM UK_TABLE_NAME UK_COLUMN_NAME
+ FK_TABLE_CAT FK_TABLE_SCHEM FK_TABLE_NAME FK_COLUMN_NAME
+ ORDINAL_POSITION UPDATE_RULE DELETE_RULE FK_NAME UK_NAME
+ DEFERABILITY UNIQUE_OR_PRIMARY UK_DATA_TYPE FK_DATA_TYPE
+ ));
+
+ my @ODBC_cols = (qw(
+ PKTABLE_CAT PKTABLE_SCHEM PKTABLE_NAME PKCOLUMN_NAME
+ FKTABLE_CAT FKTABLE_SCHEM FKTABLE_NAME FKCOLUMN_NAME
+ KEY_SEQ UPDATE_RULE DELETE_RULE FK_NAME PK_NAME
+ DEFERABILITY UNIQUE_OR_PRIMARY PK_DATA_TYPE FKDATA_TYPE
+ ));
+
+ if ($oldname eq 'NAME_lc') {
+ if ($odbc) {
+ for my $col (@ODBC_cols) {
+ $col = lc $col;
+ }
+ }
+ else {
+ for my $col (@CLI_cols) {
+ $col = lc $col;
+ }
+ }
+ }
+
+ return _prepare_from_data('foreign_key_info', $fkinfo, $odbc ? \@ODBC_cols : \@CLI_cols);
+
+ }
+
+
+ sub table_info {
+
+ my $dbh = shift;
+ my ($catalog, $schema, $table, $type) = @_;
+
+ my $tbl_sql = ();
+
+ my $extracols = q{,NULL::text AS pg_schema, NULL::text AS pg_table};
+ if ( # Rule 19a
+ (defined $catalog and $catalog eq '%')
+ and (defined $schema and $schema eq '')
+ and (defined $table and $table eq '')
+ ) {
+ $tbl_sql = qq{
+ SELECT
+ NULL::text AS "TABLE_CAT"
+ , NULL::text AS "TABLE_SCHEM"
+ , NULL::text AS "TABLE_NAME"
+ , NULL::text AS "TABLE_TYPE"
+ , NULL::text AS "REMARKS" $extracols
+ };
+ }
+ elsif (# Rule 19b
+ (defined $catalog and $catalog eq '')
+ and (defined $schema and $schema eq '%')
+ and (defined $table and $table eq '')
+ ) {
+ $extracols = q{,n.nspname AS pg_schema, NULL::text AS pg_table};
+ $tbl_sql = qq{SELECT
+ NULL::text AS "TABLE_CAT"
+ , quote_ident(n.nspname) AS "TABLE_SCHEM"
+ , NULL::text AS "TABLE_NAME"
+ , NULL::text AS "TABLE_TYPE"
+ , CASE WHEN n.nspname ~ '^pg_' THEN 'system schema' ELSE 'owned by ' || pg_get_userbyid(n.nspowner) END AS "REMARKS" $extracols
+ FROM pg_catalog.pg_namespace n
+ ORDER BY "TABLE_SCHEM"
+ };
+ }
+ elsif (# Rule 19c
+ (defined $catalog and $catalog eq '')
+ and (defined $schema and $schema eq '')
+ and (defined $table and $table eq '')
+ and (defined $type and $type eq '%')
+ ) {
+ $tbl_sql = qq{
+ SELECT
+ NULL::text AS "TABLE_CAT"
+ , NULL::text AS "TABLE_SCHEM"
+ , NULL::text AS "TABLE_NAME"
+ , 'TABLE' AS "TABLE_TYPE"
+ , 'relkind: r' AS "REMARKS" $extracols
+ UNION
+ SELECT
+ NULL::text AS "TABLE_CAT"
+ , NULL::text AS "TABLE_SCHEM"
+ , NULL::text AS "TABLE_NAME"
+ , 'VIEW' AS "TABLE_TYPE"
+ , 'relkind: v' AS "REMARKS" $extracols
+ };
+ }
+ else {
+ # Default SQL
+ $extracols = q{,n.nspname AS pg_schema, c.relname AS pg_table};
+ my @search;
+ my $showtablespace = ', quote_ident(t.spcname) AS "pg_tablespace_name", quote_ident(t.spclocation) AS "pg_tablespace_location"';
+
+ ## If the schema or table has an underscore or a %, use a LIKE comparison
+ if (defined $schema and length $schema) {
+ push @search, 'n.nspname ' . ($schema =~ /[_%]/ ? 'LIKE ' : '= ') . $dbh->quote($schema);
+ }
+ if (defined $table and length $table) {
+ push @search, 'c.relname ' . ($table =~ /[_%]/ ? 'LIKE ' : '= ') . $dbh->quote($table);
+ }
+ ## All we can see is "table" or "view". Default is both
+ my $typesearch = q{IN ('r','v')};
+ if (defined $type and length $type) {
+ if ($type =~ /\btable\b/i and $type !~ /\bview\b/i) {
+ $typesearch = q{= 'r'};
+ }
+ elsif ($type =~ /\bview\b/i and $type !~ /\btable\b/i) {
+ $typesearch = q{= 'v'};
+ }
+ }
+ push @search, "c.relkind $typesearch";
+
+ my $TSJOIN = 'pg_catalog.pg_tablespace t ON (t.oid = c.reltablespace)';
+ if ($dbh->{private_dbdpg}{version} < 80000) {
+ $TSJOIN = '(SELECT 0 AS oid, 0 AS spcname, 0 AS spclocation LIMIT 0) AS t ON (t.oid=1)';
+ }
+ my $whereclause = join "\n\t\t\t\t\t AND " => @search;
+ $tbl_sql = qq{
+ SELECT NULL::text AS "TABLE_CAT"
+ , quote_ident(n.nspname) AS "TABLE_SCHEM"
+ , quote_ident(c.relname) AS "TABLE_NAME"
+ , CASE
+ WHEN c.relkind = 'v' THEN
+ CASE WHEN quote_ident(n.nspname) ~ '^pg_' THEN 'SYSTEM VIEW' ELSE 'VIEW' END
+ ELSE
+ CASE WHEN quote_ident(n.nspname) ~ '^pg_' THEN 'SYSTEM TABLE' ELSE 'TABLE' END
+ END AS "TABLE_TYPE"
+ , d.description AS "REMARKS" $showtablespace $extracols
+ FROM pg_catalog.pg_class AS c
+ LEFT JOIN pg_catalog.pg_description AS d
+ ON (c.oid = d.objoid AND c.tableoid = d.classoid AND d.objsubid = 0)
+ LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
+ LEFT JOIN $TSJOIN
+ WHERE $whereclause
+ ORDER BY "TABLE_TYPE", "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME"
+ };
+ }
+ my $sth = $dbh->prepare( $tbl_sql ) or return undef;
+ $sth->execute();
+
+ return $sth;
+ }
+
+ sub tables {
+ my ($dbh, @args) = @_;
+ my $attr = $args[4];
+ my $sth = $dbh->table_info(@args) or return;
+ my $tables = $sth->fetchall_arrayref() or return;
+ my @tables = map { (! (ref $attr eq 'HASH' and $attr->{pg_noprefix})) ?
+ "$_->[1].$_->[2]" : $_->[2] } @$tables;
+ return @tables;
+ }
+
+ sub table_attributes {
+ my ($dbh, $table) = @_;
+
+ my $sth = $dbh->column_info(undef,undef,$table,undef);
+
+ my %convert = (
+ COLUMN_NAME => 'NAME',
+ DATA_TYPE => 'TYPE',
+ COLUMN_SIZE => 'SIZE',
+ NULLABLE => 'NOTNULL',
+ REMARKS => 'REMARKS',
+ COLUMN_DEF => 'DEFAULT',
+ pg_constraint => 'CONSTRAINT',
+ );
+
+ my $attrs = $sth->fetchall_arrayref(\%convert);
+
+ for my $row (@$attrs) {
+ # switch the column names
+ for my $name (keys %$row) {
+ $row->{ $convert{$name} } = $row->{$name};
+
+ ## Keep some original columns
+ delete $row->{$name} unless ($name eq 'REMARKS' or $name eq 'NULLABLE');
+
+ }
+ # Moved check outside of loop as it was inverting the NOTNULL value for
+ # attribute.
+ # NOTNULL inverts the sense of NULLABLE
+ $row->{NOTNULL} = ($row->{NOTNULL} ? 0 : 1);
+
+ my @pri_keys = ();
+ @pri_keys = $dbh->primary_key( undef, undef, $table );
+ $row->{PRIMARY_KEY} = scalar(grep { /^$row->{NAME}$/i } @pri_keys) ? 1 : 0;
+ }
+
+ return $attrs;
+
+ }
+
+ sub _calc_col_size {
+
+ my $mod = shift;
+ my $size = shift;
+
+
+ if ((defined $size) and ($size > 0)) {
+ return $size;
+ } elsif ($mod > 0xffff) {
+ my $prec = ($mod & 0xffff) - 4;
+ $mod >>= 16;
+ my $dig = $mod;
+ return "$prec,$dig";
+ } elsif ($mod >= 4) {
+ return $mod - 4;
+ } # else {
+ # $rtn = $mod;
+ # $rtn = undef;
+ # }
+
+ return;
+ }
+
+
+ sub type_info_all {
+ my ($dbh) = @_;
+
+ my $names =
+ {
+ TYPE_NAME => 0,
+ DATA_TYPE => 1,
+ COLUMN_SIZE => 2,
+ LITERAL_PREFIX => 3,
+ LITERAL_SUFFIX => 4,
+ CREATE_PARAMS => 5,
+ NULLABLE => 6,
+ CASE_SENSITIVE => 7,
+ SEARCHABLE => 8,
+ UNSIGNED_ATTRIBUTE => 9,
+ FIXED_PREC_SCALE => 10,
+ AUTO_UNIQUE_VALUE => 11,
+ LOCAL_TYPE_NAME => 12,
+ MINIMUM_SCALE => 13,
+ MAXIMUM_SCALE => 14,
+ SQL_DATA_TYPE => 15,
+ SQL_DATETIME_SUB => 16,
+ NUM_PREC_RADIX => 17,
+ INTERVAL_PRECISION => 18,
+ };
+
+ ## This list is derived from dbi_sql.h in DBI, from types.c and types.h, and from the PG docs
+
+ ## Aids to make the list more readable:
+ my $GIG = 1073741824;
+ my $PS = 'precision/scale';
+ my $LEN = 'length';
+ my $UN = undef;
+ my $ti =
+ [
+ $names,
+# name sql_type size pfx/sfx crt n/c/s +-/P/I local min max sub rdx itvl
+
+['unknown', SQL_UNKNOWN_TYPE, 0, $UN,$UN, $UN, 1,0,0, $UN,0,0, 'UNKNOWN', $UN,$UN,
+ SQL_UNKNOWN_TYPE, $UN, $UN, $UN ],
+['bytea', SQL_VARBINARY, $GIG, q{'},q{'}, $UN, 1,0,3, $UN,0,0, 'BYTEA', $UN,$UN,
+ SQL_VARBINARY, $UN, $UN, $UN ],
+['bpchar', SQL_CHAR, $GIG, q{'},q{'}, $LEN, 1,1,3, $UN,0,0, 'CHARACTER', $UN,$UN,
+ SQL_CHAR, $UN, $UN, $UN ],
+['numeric', SQL_DECIMAL, 1000, $UN,$UN, $PS, 1,0,2, 0,0,0, 'FLOAT', 0,1000,
+ SQL_DECIMAL, $UN, $UN, $UN ],
+['numeric', SQL_NUMERIC, 1000, $UN,$UN, $PS, 1,0,2, 0,0,0, 'FLOAT', 0,1000,
+ SQL_NUMERIC, $UN, $UN, $UN ],
+['int4', SQL_INTEGER, 10, $UN,$UN, $UN, 1,0,2, 0,0,0, 'INTEGER', 0,0,
+ SQL_INTEGER, $UN, $UN, $UN ],
+['int2', SQL_SMALLINT, 5, $UN,$UN, $UN, 1,0,2, 0,0,0, 'SMALLINT', 0,0,
+ SQL_SMALLINT, $UN, $UN, $UN ],
+['float4', SQL_FLOAT, 6, $UN,$UN, $PS, 1,0,2, 0,0,0, 'FLOAT', 0,6,
+ SQL_FLOAT, $UN, $UN, $UN ],
+['float8', SQL_REAL, 15, $UN,$UN, $PS, 1,0,2, 0,0,0, 'REAL', 0,15,
+ SQL_REAL, $UN, $UN, $UN ],
+['int8', SQL_DOUBLE, 20, $UN,$UN, $UN, 1,0,2, 0,0,0, 'LONGINT', 0,0,
+ SQL_DOUBLE, $UN, $UN, $UN ],
+['date', SQL_DATE, 10, q{'},q{'}, $UN, 1,0,2, $UN,0,0, 'DATE', 0,0,
+ SQL_DATE, $UN, $UN, $UN ],
+['tinterval',SQL_TIME, 18, q{'},q{'}, $UN, 1,0,2, $UN,0,0, 'TINTERVAL', 0,6,
+ SQL_TIME, $UN, $UN, $UN ],
+['timestamp',SQL_TIMESTAMP, 29, q{'},q{'}, $UN, 1,0,2, $UN,0,0, 'TIMESTAMP', 0,6,
+ SQL_TIMESTAMP, $UN, $UN, $UN ],
+['text', SQL_VARCHAR, $GIG, q{'},q{'}, $LEN, 1,1,3, $UN,0,0, 'TEXT', $UN,$UN,
+ SQL_VARCHAR, $UN, $UN, $UN ],
+['bool', SQL_BOOLEAN, 1, q{'},q{'}, $UN, 1,0,2, $UN,0,0, 'BOOLEAN', $UN,$UN,
+ SQL_BOOLEAN, $UN, $UN, $UN ],
+['array', SQL_ARRAY, 1, q{'},q{'}, $UN, 1,0,2, $UN,0,0, 'ARRAY', $UN,$UN,
+ SQL_ARRAY, $UN, $UN, $UN ],
+['date', SQL_TYPE_DATE, 10, q{'},q{'}, $UN, 1,0,2, $UN,0,0, 'DATE', 0,0,
+ SQL_TYPE_DATE, $UN, $UN, $UN ],
+['time', SQL_TYPE_TIME, 18, q{'},q{'}, $UN, 1,0,2, $UN,0,0, 'TIME', 0,6,
+ SQL_TYPE_TIME, $UN, $UN, $UN ],
+['timestamp',SQL_TYPE_TIMESTAMP,29, q{'},q{'}, $UN, 1,0,2, $UN,0,0, 'TIMESTAMP', 0,6,
+ SQL_TYPE_TIMESTAMP, $UN, $UN, $UN ],
+['timetz', SQL_TYPE_TIME_WITH_TIMEZONE,
+ 29, q{'},q{'}, $UN, 1,0,2, $UN,0,0, 'TIMETZ', 0,6,
+ SQL_TYPE_TIME_WITH_TIMEZONE, $UN, $UN, $UN ],
+['timestamptz',SQL_TYPE_TIMESTAMP_WITH_TIMEZONE,
+ 29, q{'},q{'}, $UN, 1,0,2, $UN,0,0, 'TIMESTAMPTZ',0,6,
+ SQL_TYPE_TIMESTAMP_WITH_TIMEZONE, $UN, $UN, $UN ],
+ #
+ # intentionally omitted: char, all geometric types, internal types
+ ];
+ return $ti;
+ }
+
+
+ # Characters that need to be escaped by quote().
+ my %esc = (
+ q{'} => '\\047', # '\\' . sprintf("%03o", ord("'")), # ISO SQL 2
+ '\\' => '\\134', # '\\' . sprintf("%03o", ord("\\")),
+ );
+
+ # Set up lookup for SQL types we don't want to escape.
+ my %no_escape = map { $_ => 1 }
+ DBI::SQL_INTEGER, DBI::SQL_SMALLINT, DBI::SQL_DECIMAL,
+ DBI::SQL_FLOAT, DBI::SQL_REAL, DBI::SQL_DOUBLE, DBI::SQL_NUMERIC;
+
+ sub get_info {
+
+ my ($dbh,$type) = @_;
+
+ return undef unless defined $type and length $type;
+
+ my %type = (
+
+## Driver information:
+
+ 116 => ['SQL_ACTIVE_ENVIRONMENTS', 0 ], ## unlimited
+ 10021 => ['SQL_ASYNC_MODE', 2 ], ## SQL_AM_STATEMENT
+ 120 => ['SQL_BATCH_ROW_COUNT', 2 ], ## SQL_BRC_EXPLICIT
+ 121 => ['SQL_BATCH_SUPPORT', 3 ], ## 12 SELECT_PROC + ROW_COUNT_PROC
+ 2 => ['SQL_DATA_SOURCE_NAME', "dbi:Pg:$dbh->{Name}" ],
+ 3 => ['SQL_DRIVER_HDBC', 0 ], ## not applicable
+ 135 => ['SQL_DRIVER_HDESC', 0 ], ## not applicable
+ 4 => ['SQL_DRIVER_HENV', 0 ], ## not applicable
+ 76 => ['SQL_DRIVER_HLIB', 0 ], ## not applicable
+ 5 => ['SQL_DRIVER_HSTMT', 0 ], ## not applicable
+ ## Not clear what should go here. Some things suggest 'Pg', others 'Pg.pm'. We'll use DBD::Pg for now
+ 6 => ['SQL_DRIVER_NAME', 'DBD::Pg' ],
+ 77 => ['SQL_DRIVER_ODBC_VERSION', '03.00' ],
+ 7 => ['SQL_DRIVER_VER', 'DBDVERSION' ], ## magic word
+ 144 => ['SQL_DYNAMIC_CURSOR_ATTRIBUTES1', 0 ], ## we can FETCH, but not via methods
+ 145 => ['SQL_DYNAMIC_CURSOR_ATTRIBUTES2', 0 ], ## same as above
+ 84 => ['SQL_FILE_USAGE', 0 ], ## SQL_FILE_NOT_SUPPORTED (this is good)
+ 146 => ['SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES1', 519 ], ## not clear what this refers to in DBD context
+ 147 => ['SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES2', 5209 ], ## see above
+ 81 => ['SQL_GETDATA_EXTENSIONS', 15 ], ## 1+2+4+8
+ 149 => ['SQL_INFO_SCHEMA_VIEWS', 3932149 ], ## not: assert, charset, collat, trans
+ 150 => ['SQL_KEYSET_CURSOR_ATTRIBUTES1', 0 ], ## applies to us?
+ 151 => ['SQL_KEYSET_CURSOR_ATTRIBUTES2', 0 ], ## see above
+ 10022 => ['SQL_MAX_ASYNC_CONCURRENT_STATEMENTS', 0 ], ## unlimited, probably
+ 0 => ['SQL_MAX_DRIVER_CONNECTIONS', 'MAXCONNECTIONS' ], ## magic word
+ 152 => ['SQL_ODBC_INTERFACE_CONFORMANCE', 1 ], ## SQL_OIC_LEVEL_1
+ 10 => ['SQL_ODBC_VER', '03.00.0000' ],
+ 153 => ['SQL_PARAM_ARRAY_ROW_COUNTS', 2 ], ## correct?
+ 154 => ['SQL_PARAM_ARRAY_SELECTS', 3 ], ## PAS_NO_SELECT
+ 11 => ['SQL_ROW_UPDATES', 'N' ],
+ 14 => ['SQL_SEARCH_PATTERN_ESCAPE', '\\' ],
+ 13 => ['SQL_SERVER_NAME', 'CURRENTDB' ], ## magic word
+ 166 => ['SQL_STANDARD_CLI_CONFORMANCE', 2 ], ## ??
+ 167 => ['SQL_STATIC_CURSOR_ATTRIBUTES1', 519 ], ## ??
+ 168 => ['SQL_STATIC_CURSOR_ATTRIBUTES2', 5209 ], ## ??
+
+## DBMS Information
+
+ 16 => ['SQL_DATABASE_NAME', 'CURRENTDB' ], ## magic word
+ 17 => ['SQL_DBMS_NAME', 'PostgreSQL' ],
+ 18 => ['SQL_DBMS_VERSION', 'ODBCVERSION' ], ## magic word
+
+## Data source information
+
+ 20 => ['SQL_ACCESSIBLE_PROCEDURES', 'Y' ], ## is this really true?
+ 19 => ['SQL_ACCESSIBLE_TABLES', 'Y' ], ## is this really true?
+ 82 => ['SQL_BOOKMARK_PERSISTENCE', 0 ],
+ 42 => ['SQL_CATALOG_TERM', '' ], ## empty = catalogs are not supported
+ 10004 => ['SQL_COLLATION_SEQ', 'ENCODING' ], ## magic word
+ 22 => ['SQL_CONCAT_NULL_BEHAVIOR', 0 ], ## SQL_CB_NULL
+ 23 => ['SQL_CURSOR_COMMIT_BEHAVIOR', 1 ], ## SQL_CB_CLOSE
+ 24 => ['SQL_CURSOR_ROLLBACK_BEHAVIOR', 1 ], ## SQL_CB_CLOSE
+ 10001 => ['SQL_CURSOR_SENSITIVITY', 1 ], ## SQL_INSENSITIVE
+ 25 => ['SQL_DATA_SOURCE_READ_ONLY', 'READONLY' ], ## magic word
+ 26 => ['SQL_DEFAULT_TXN_ISOLATION', 'DEFAULTTXN' ], ## magic word (2 or 8)
+ 10002 => ['SQL_DESCRIBE_PARAMETER', 'Y' ],
+ 36 => ['SQL_MULT_RESULT_SETS', 'Y' ],
+ 37 => ['SQL_MULTIPLE_ACTIVE_TXN', 'Y' ],
+ 111 => ['SQL_NEED_LONG_DATA_LEN', 'N' ],
+ 85 => ['SQL_NULL_COLLATION', 0 ], ## SQL_NC_HIGH
+ 40 => ['SQL_PROCEDURE_TERM', 'function' ], ## for now
+ 39 => ['SQL_SCHEMA_TERM', 'schema' ],
+ 44 => ['SQL_SCROLL_OPTIONS', 8 ], ## not really for DBD?
+ 45 => ['SQL_TABLE_TERM', 'table' ],
+ 46 => ['SQL_TXN_CAPABLE', 2 ], ## SQL_TC_ALL
+ 72 => ['SQL_TXN_ISOLATION_OPTION', 10 ], ## 2+8
+ 47 => ['SQL_USER_NAME', $dbh->{CURRENT_USER} ],
+
+## Supported SQL
+
+ 169 => ['SQL_AGGREGATE_FUNCTIONS', 127 ], ## all of 'em
+ 117 => ['SQL_ALTER_DOMAIN', 31 ], ## all but deferred
+ 86 => ['SQL_ALTER_TABLE', 32639 ], ## no collate
+ 114 => ['SQL_CATALOG_LOCATION', 0 ],
+ 10003 => ['SQL_CATALOG_NAME', 'N' ],
+ 41 => ['SQL_CATALOG_NAME_SEPARATOR', '' ],
+ 92 => ['SQL_CATALOG_USAGE', 0 ],
+ 87 => ['SQL_COLUMN_ALIAS', 'Y' ],
+ 74 => ['SQL_CORRELATION_NAME', 2 ], ## SQL_CN_ANY
+ 127 => ['SQL_CREATE_ASSERTION', 0 ],
+ 128 => ['SQL_CREATE_CHARACTER_SET', 0 ],
+ 129 => ['SQL_CREATE_COLLATION', 0 ],
+ 130 => ['SQL_CREATE_DOMAIN', 23 ], ## no collation, no defer
+ 131 => ['SQL_CREATE_SCHEMA', 3 ], ## 1+2 schema + authorize
+ 132 => ['SQL_CREATE_TABLE', 13845 ], ## no collation
+ 133 => ['SQL_CREATE_TRANSLATION', 0 ],
+ 134 => ['SQL_CREATE_VIEW', 9 ], ## local + create?
+ 119 => ['SQL_DATETIME_LITERALS', 65535 ], ## all?
+ 170 => ['SQL_DDL_INDEX', 3 ], ## create + drop
+ 136 => ['SQL_DROP_ASSERTION', 0 ],
+ 137 => ['SQL_DROP_CHARACTER_SET', 0 ],
+ 138 => ['SQL_DROP_COLLATION', 0 ],
+ 139 => ['SQL_DROP_DOMAIN', 7 ],
+ 140 => ['SQL_DROP_SCHEMA', 7 ],
+ 141 => ['SQL_DROP_TABLE', 7 ],
+ 142 => ['SQL_DROP_TRANSLATION', 0 ],
+ 143 => ['SQL_DROP_VIEW', 7 ],
+ 27 => ['SQL_EXPRESSIONS_IN_ORDERBY', 'Y' ],
+ 88 => ['SQL_GROUP_BY', 2 ], ## GROUP_BY_CONTAINS_SELECT
+ 28 => ['SQL_IDENTIFIER_CASE', 2 ], ## SQL_IC_LOWER
+ 29 => ['SQL_IDENTIFIER_QUOTE_CHAR', q{"} ],
+ 148 => ['SQL_INDEX_KEYWORDS', 0 ], ## not needed for Pg
+ 172 => ['SQL_INSERT_STATEMENT', 7 ], ## 1+2+4 = all
+ 73 => ['SQL_INTEGRITY', 'Y' ], ## e.g. ON DELETE CASCADE?
+ 89 => ['SQL_KEYWORDS', 'KEYWORDS' ], ## magic word
+ 113 => ['SQL_LIKE_ESCAPE_CLAUSE', 'Y' ],
+ 75 => ['SQL_NON_NULLABLE_COLUMNS', 1 ], ## NNC_NOT_NULL
+ 115 => ['SQL_OJ_CAPABILITIES', 127 ], ## all
+ 90 => ['SQL_ORDER_BY_COLUMNS_IN_SELECT', 'N' ],
+ 38 => ['SQL_OUTER_JOINS', 'Y' ],
+ 21 => ['SQL_PROCEDURES', 'Y' ],
+ 93 => ['SQL_QUOTED_IDENTIFIER_CASE', 3 ], ## SQL_IC_SENSITIVE
+ 91 => ['SQL_SCHEMA_USAGE', 31 ], ## all
+ 94 => ['SQL_SPECIAL_CHARACTERS', '$' ], ## there are actually many more...
+ 118 => ['SQL_SQL_CONFORMANCE', 4 ], ## SQL92_INTERMEDIATE ??
+ 95 => ['SQL_SUBQUERIES', 31 ], ## all
+ 96 => ['SQL_UNION', 3 ], ## 1+2 = all
+
+## SQL limits
+
+ 112 => ['SQL_MAX_BINARY_LITERAL_LEN', 0 ],
+ 34 => ['SQL_MAX_CATALOG_NAME_LEN', 0 ],
+ 108 => ['SQL_MAX_CHAR_LITERAL_LEN', 0 ],
+ 30 => ['SQL_MAX_COLUMN_NAME_LEN', 'NAMEDATALEN' ], ## magic word
+ 97 => ['SQL_MAX_COLUMNS_IN_GROUP_BY', 0 ],
+ 98 => ['SQL_MAX_COLUMNS_IN_INDEX', 0 ],
+ 99 => ['SQL_MAX_COLUMNS_IN_ORDER_BY', 0 ],
+ 100 => ['SQL_MAX_COLUMNS_IN_SELECT', 0 ],
+ 101 => ['SQL_MAX_COLUMNS_IN_TABLE', 250 ], ## 250-1600 (depends on column types)
+ 31 => ['SQL_MAX_CURSOR_NAME_LEN', 'NAMEDATALEN' ], ## magic word
+ 10005 => ['SQL_MAX_IDENTIFIER_LEN', 'NAMEDATALEN' ], ## magic word
+ 102 => ['SQL_MAX_INDEX_SIZE', 0 ],
+ 102 => ['SQL_MAX_PROCEDURE_NAME_LEN', 'NAMEDATALEN' ], ## magic word
+ 104 => ['SQL_MAX_ROW_SIZE', 0 ], ## actually 1.6 TB, but too big to represent here
+ 103 => ['SQL_MAX_ROW_SIZE_INCLUDES_LONG', 'Y' ],
+ 32 => ['SQL_MAX_SCHEMA_NAME_LEN', 'NAMEDATALEN' ], ## magic word
+ 105 => ['SQL_MAX_STATEMENT_LEN', 0 ],
+ 35 => ['SQL_MAX_TABLE_NAME_LEN', 'NAMEDATALEN' ], ## magic word
+ 106 => ['SQL_MAX_TABLES_IN_SELECT', 0 ],
+ 107 => ['SQL_MAX_USER_NAME_LEN', 'NAMEDATALEN' ], ## magic word
+
+## Scalar function information
+
+ 48 => ['SQL_CONVERT_FUNCTIONS', 2 ], ## CVT_CAST only?
+ 49 => ['SQL_NUMERIC_FUNCTIONS', 16777215 ], ## ?? all but some naming clashes: rand(om), trunc(ate), log10=ln, etc.
+ 50 => ['SQL_STRING_FUNCTIONS', 16280984 ], ## ??
+ 51 => ['SQL_SYSTEM_FUNCTIONS', 0 ], ## ??
+ 109 => ['SQL_TIMEDATE_ADD_INTERVALS', 0 ], ## ?? no explicit timestampadd?
+ 110 => ['SQL_TIMEDATE_DIFF_INTERVALS', 0 ], ## ??
+ 52 => ['SQL_TIMEDATE_FUNCTIONS', 1966083 ],
+
+## Conversion information - all but BIT, LONGVARBINARY, and LONGVARCHAR
+
+ 53 => ['SQL_CONVERT_BIGINT', 1830399 ],
+ 54 => ['SQL_CONVERT_BINARY', 1830399 ],
+ 55 => ['SQL_CONVERT_BIT', 0 ],
+ 56 => ['SQL_CONVERT_CHAR', 1830399 ],
+ 57 => ['SQL_CONVERT_DATE', 1830399 ],
+ 58 => ['SQL_CONVERT_DECIMAL', 1830399 ],
+ 59 => ['SQL_CONVERT_DOUBLE', 1830399 ],
+ 60 => ['SQL_CONVERT_FLOAT', 1830399 ],
+ 61 => ['SQL_CONVERT_INTEGER', 1830399 ],
+ 123 => ['SQL_CONVERT_INTERVAL_DAY_TIME', 1830399 ],
+ 124 => ['SQL_CONVERT_INTERVAL_YEAR_MONTH', 1830399 ],
+ 71 => ['SQL_CONVERT_LONGVARBINARY', 0 ],
+ 62 => ['SQL_CONVERT_LONGVARCHAR', 0 ],
+ 63 => ['SQL_CONVERT_NUMERIC', 1830399 ],
+ 64 => ['SQL_CONVERT_REAL', 1830399 ],
+ 65 => ['SQL_CONVERT_SMALLINT', 1830399 ],
+ 66 => ['SQL_CONVERT_TIME', 1830399 ],
+ 67 => ['SQL_CONVERT_TIMESTAMP', 1830399 ],
+ 68 => ['SQL_CONVERT_TINYINT', 1830399 ],
+ 69 => ['SQL_CONVERT_VARBINARY', 0 ],
+ 70 => ['SQL_CONVERT_VARCHAR', 1830399 ],
+ 122 => ['SQL_CONVERT_WCHAR', 0 ],
+ 125 => ['SQL_CONVERT_WLONGVARCHAR', 0 ],
+ 126 => ['SQL_CONVERT_WVARCHAR', 0 ],
+
+ ); ## end of %type
+
+ ## Put both numbers and names into a hash
+ my %t;
+ for (keys %type) {
+ $t{$_} = $type{$_}->[1];
+ $t{$type{$_}->[0]} = $type{$_}->[1];
+ }
+
+ return undef unless exists $t{$type};
+
+ my $ans = $t{$type};
+
+ if ($ans eq 'NAMEDATALEN') {
+ return $dbh->selectall_arrayref('SHOW max_identifier_length')->[0][0];
+ }
+ elsif ($ans eq 'ODBCVERSION') {
+ my $version = $dbh->{private_dbdpg}{version};
+ return '00.00.0000' unless $version =~ /^(\d\d?)(\d\d)(\d\d)$/o;
+ return sprintf '%02d.%02d.%.2d00', $1,$2,$3;
+ }
+ elsif ($ans eq 'DBDVERSION') {
+ my $simpleversion = $DBD::Pg::VERSION;
+ $simpleversion =~ s/_/./g;
+ return sprintf '%02d.%02d.%1d%1d%1d%1d', split (/\./, "$simpleversion.0.0.0.0.0.0");
+ }
+ elsif ($ans eq 'MAXCONNECTIONS') {
+ return $dbh->selectall_arrayref('SHOW max_connections')->[0][0];
+ }
+ elsif ($ans eq 'ENCODING') {
+ return $dbh->selectall_arrayref('SHOW server_encoding')->[0][0];
+ }
+ elsif ($ans eq 'KEYWORDS') {
+ ## http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html
+ ## Basically, we want ones that are 'reserved' for PostgreSQL but not 'reserved' in SQL:2003
+ ##
+ return join ',' => (qw(ANALYSE ANALYZE ASC DEFERRABLE DESC DO FREEZE ILIKE INITIALLY ISNULL LIMIT NOTNULL OFF OFFSET PLACING RETURNING VERBOSE));
+ }
+ elsif ($ans eq 'CURRENTDB') {
+ return $dbh->selectall_arrayref('SELECT pg_catalog.current_database()')->[0][0];
+ }
+ elsif ($ans eq 'READONLY') {
+ my $SQL = q{SELECT CASE WHEN setting = 'on' THEN 'Y' ELSE 'N' END FROM pg_settings WHERE name = 'transaction_read_only'};
+ my $info = $dbh->selectall_arrayref($SQL);
+ return defined $info->[0] ? $info->[0][0] : 'N';
+ }
+ elsif ($ans eq 'DEFAULTTXN') {
+ my $SQL = q{SELECT CASE WHEN setting = 'read committed' THEN 2 ELSE 8 END FROM pg_settings WHERE name = 'default_transaction_isolation'};
+ my $info = $dbh->selectall_arrayref($SQL);
+ return defined $info->[0] ? $info->[0][0] : 2;
+ }
+
+ return $ans;
+ } # end of get_info
+
+ sub private_attribute_info {
+ return {
+ pg_async_status => undef,
+ pg_bool_tf => undef,
+ pg_db => undef,
+ pg_default_port => undef,
+ pg_enable_utf8 => undef,
+ pg_errorlevel => undef,
+ pg_expand_array => undef,
+ pg_host => undef,
+ pg_INV_READ => undef,
+ pg_INV_WRITE => undef,
+ pg_lib_version => undef,
+ pg_options => undef,
+ pg_pass => undef,
+ pg_pid => undef,
+ pg_placeholder_dollaronly => undef,
+ pg_port => undef,
+ pg_prepare_now => undef,
+ pg_protocol => undef,
+ pg_server_prepare => undef,
+ pg_server_version => undef,
+ pg_socket => undef,
+ pg_standard_conforming_strings => undef,
+ pg_user => undef,
+ };
+ }
+}
+
+
+{
+ package DBD::Pg::st;
+
+ sub parse_trace_flag {
+ my ($h, $flag) = @_;
+ return DBD::Pg->parse_trace_flag($flag);
+ }
+
+ sub bind_param_array {
+
+ ## Binds an array of data to a specific placeholder in a statement
+ ## The DBI version is broken, so we implement a near-copy here
+
+ my $sth = shift;
+ my ($p_id, $value_array, $attr) = @_;
+
+ ## Bail if the second arg is not undef or an an arrayref
+ return $sth->set_err(1, "Value for parameter $p_id must be a scalar or an arrayref, not a ".ref($value_array))
+ if defined $value_array and ref $value_array and ref $value_array ne 'ARRAY';
+
+ ## Bail if the first arg is not a number
+ return $sth->set_err(1, q{Can't use named placeholders for non-driver supported bind_param_array})
+ unless DBI::looks_like_number($p_id); # because we rely on execute(@ary) here
+
+ ## Store the list of items in the hash (will be undef or an arayref)
+ $sth->{ParamArrays}{$p_id} = $value_array;
+
+ ## If any attribs were passed in, we need to call bind_param
+ return $sth->bind_param($p_id, '', $attr) if $attr; ## This is the big change so -w does not complain
+
+ return 1;
+ } ## end bind_param_array
+
+ sub private_attribute_info {
+ return {
+ pg_async => undef,
+ pg_bound => undef,
+ pg_current_row => undef,
+ pg_direct => undef,
+ pg_numbound => undef,
+ pg_cmd_status => undef,
+ pg_oid_status => undef,
+ pg_placeholder_dollaronly => undef,
+ pg_prepare_name => undef,
+ pg_prepare_now => undef,
+ pg_segments => undef,
+ pg_server_prepare => undef,
+ pg_size => undef,
+ pg_type => undef,
+ };
+ }
+
+} ## end st section
+
+1;
+
+__END__
+
+=head1 NAME
+
+DBD::Pg - PostgreSQL database driver for the DBI module
+
+=head1 SYNOPSIS
+
+ use DBI;
+
+ $dbh = DBI->connect("dbi:Pg:dbname=$dbname", '', '', {AutoCommit => 0});
+ # The AutoCommit attribute should always be explicitly set
+
+ # For some advanced uses you may need PostgreSQL type values:
+ use DBD::Pg qw(:pg_types);
+
+ # For asynchronous calls, import the async constants:
+ use DBD::Pg qw(:async);
+
+ $dbh->do('INSERT INTO mytable(a) VALUES (1)');
+
+ $sth = $dbh->prepare('INSERT INTO mytable(a) VALUES (?)');
+ $sth->execute();
+
+=head1 VERSION
+
+This documents version 2.16.1 of the DBD::Pg module
+
+=head1 DESCRIPTION
+
+DBD::Pg is a Perl module that works with the DBI module to provide access to
+PostgreSQL databases.
+
+=head1 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 B<DBI> documentation first!
+
+=for html <a href="http://search.cpan.org/~timb/DBI/DBI.pm">Latest DBI docmentation.</a>
+
+=head1 THE DBI CLASS
+
+=head2 DBI Class Methods
+
+=head3 B<connect>
+
+This method creates a database handle by connecting to a database, and is the DBI
+equivalent of the "new" method. To connect to a Postgres database with a minimum of parameters,
+use the following syntax:
+
+ $dbh = DBI->connect("dbi:Pg:dbname=$dbname", '', '', {AutoCommit => 0});
+
+This connects to the database named in the C<$dbname> variable on the default port (usually 5432)
+without any user authentication.
+
+The following connect statement shows almost all possible parameters:
+
+ $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host;port=$port;options=$options",
+ $username,
+ $password,
+ {AutoCommit => 0, RaiseError => 1, PrintError => 0}
+ );
+
+If a parameter is not given, the connect() method will first look for
+specific environment variables, and then fall back to hard-coded defaults:
+
+ parameter environment variable hard coded default
+ ------------------------------------------------------
+ host PGHOST local domain socket
+ hostaddr PGHOSTADDR local domain socket
+ port PGPORT 5432
+ dbname* PGDATABASE current userid
+ username PGUSER current userid
+ password PGPASSWORD (none)
+ options PGOPTIONS (none)
+ service PGSERVICE (none)
+ sslmode PGSSLMODE (none)
+
+* May also use the aliases C<db> or C<database>
+
+If the username and password values passed via C<connect()> are undefined (as opposed
+to merely being empty strings), DBI will use the environment variables I<DBI_USER>
+and I<DBI_PASS> if they exist.
+
+You can also connect by using a service connection file, which is named
+F<pg_service.conf>. The location of this file can be controlled by
+setting the I<PGSYSCONFDIR> environment variable. To use one of the named
+services within the file, set the name by using either the I<service> parameter
+or the environment variable I<PGSERVICE>. Note that when connecting this way,
+only the minimum parameters should be used. For example, to connect to a
+service named "zephyr", you could use:
+
+ $dbh = DBI->connect("dbi:Pg:service=zephyr", '', '');
+
+You could also set C<$ENV{PGSERVICE}> to "zephyr" and connect like this:
+
+ $dbh = DBI->connect("dbi:Pg:", '', '');
+
+The format of the F<pg_service.conf> file is simply a bracketed service
+name, followed by one parameter per line in the format name=value.
+For example:
+
+ [zephyr]
+ dbname=winds
+ user=wisp
+ password=W$2Hc00YSgP
+ port=6543
+
+There are four valid arguments to the I<sslmode> parameter, which controls
+whether to use SSL to connect to the database:
+
+=over 4
+
+=item * disable: SSL connections are never used
+
+=item * allow: try non-SSL, then SSL
+
+=item * prefer: try SSL, then non-SSL
+
+=item * require: connect only with SSL
+
+=back
+
+You can also connect using sockets in a specific directory. This
+may be needed if the server you are connecting to has a different
+default socket directory from the one used to compile DBD::Pg.
+Use the complete path to the socket directory as the name of the
+host, like this:
+
+ $dbh = DBI->connect('dbi:Pg:dbname=foo;host=/var/tmp/socket',
+ $username,
+ $password,
+ {AutoCommit => 0, RaiseError => 1});
+
+The attribute hash can also contain a key named C<dbd_verbose>, which
+simply calls C<< $dbh->trace('DBD') >> after the handle is created. This attribute
+is not recommended, as it is clearer to simply explicitly call C<trace> explicitly
+in your script.
+
+=head3 B<connect_cached>
+
+ $dbh = DBI->connect_cached("dbi:Pg:dbname=$dbname", $username, $password, \%options);
+
+Implemented by DBI, no driver-specific impact.
+
+=head3 B<data_sources>
+
+ @data_sources = DBI->data_sources('Pg');
+ @data_sources = $dbh->data_sources();
+
+Returns a list of available databases. Unless the environment variable C<DBI_DSN> is set,
+a connection will be attempted to the database C<template1>. The normal connection
+environment variables also apply, such as C<PGHOST>, C<PGPORT>, C<DBI_USER>,
+C<DBI_PASS>, and C<PGSERVICE>.
+
+You can also pass in options to add to the connection string For example, to specify
+an alternate port and host:
+
+ @data_sources = DBI->data_sources('Pg', 'port=5824;host=example.com');
+
+ or:
+
+ @data_sources = $dbh->data_sources('port=5824;host=example.com');
+
+
+=head2 Methods Common To All Handles
+
+For all of the methods below, B<$h> can be either a database handle (B<$dbh>)
+or a statement handle (B<$sth>). Note that I<$dbh> and I<$sth> can be replaced with
+any variable name you choose: these are just the names most often used. Another
+common variable used in this documentation is $I<rv>, which stands for "return value".
+
+=head3 B<err>
+
+ $rv = $h->err;
+
+Returns the error code from the last method called. For the connect method it returns
+C<PQstatus>, which is a number used by I<libpq> (the Postgres connection library). A value of 0
+indicates no error (CONNECTION_OK), while any other number indicates a failed connection. The
+only other number commonly seen is 1 (CONNECTION_BAD). See the libpq documentation for the
+complete list of return codes.
+
+In all other non-connect methods C<< $h->err >> returns the C<PQresultStatus> of the current
+handle. This is a number used by libpq and is one of:
+
+ 0 Empty query string
+ 1 A command that returns no data successfully completed.
+ 2 A command that returns data sucessfully completed.
+ 3 A COPY OUT command is still in progress.
+ 4 A COPY IN command is still in progress.
+ 5 A bad response was received from the backend.
+ 6 A nonfatal error occurred (a notice or warning message)
+ 7 A fatal error was returned: the last query failed.
+
+=head3 B<errstr>
+
+ $str = $h->errstr;
+
+Returns the last error that was reported by Postgres. This message is affected
+by the L</pg_errorlevel> setting.
+
+=head3 B<state>
+
+ $str = $h->state;
+
+Returns a five-character "SQLSTATE" code. Success is indicated by a C<00000> code, which
+gets mapped to an empty string by DBI. A code of C<S8006> indicates a connection failure,
+usually because the connection to the Postgres server has been lost.
+
+While this method can be called as either C<< $sth->state >> or C<< $dbh->state >>, it
+is usually clearer to always use C<< $dbh->state >>.
+
+The list of codes used by PostgreSQL can be found at:
+L<http://www.postgresql.org/docs/current/static/errcodes-appendix.html>
+
+Note that these codes are part of the SQL standard and only a small number
+of them will be used by PostgreSQL.
+
+Common codes:
+
+ 00000 Successful completion
+ 25P01 No active SQL transaction
+ 25P02 In failed SQL transaction
+ S8006 Connection failure
+
+=head3 B<trace>
+
+ $h->trace($trace_settings);
+ $h->trace($trace_settings, $trace_filename);
+ $trace_settings = $h->trace;
+
+Changes the trace settings on a database or statement handle.
+The optional second argument specifies a file to write the
+trace information to. If no filename is given, the information
+is written to F<STDERR>. Note that tracing can be set globally as
+well by setting C<< DBI->trace >>, or by using the environment
+variable I<DBI_TRACE>.
+
+The value is either a numeric level or a named flag. For the
+flags that DBD::Pg uses, see L<parse_trace_flag|/parse_trace_flag and parse_trace_flags>.
+
+=head3 B<trace_msg>
+
+ $h->trace_msg($message_text);
+ $h->trace_msg($message_text, $min_level);
+
+Writes a message to the current trace output (as set by the L</trace> method). If a second argument
+is given, the message is only written if the current tracing level is equal to or greater than
+the C<$min_level>.
+
+=head3 B<parse_trace_flag> and B<parse_trace_flags>
+
+ $h->trace($h->parse_trace_flags('SQL|pglibpq'));
+ $h->trace($h->parse_trace_flags('1|pgstart'));
+
+ ## Simpler:
+ $h->trace('SQL|pglibpq');
+ $h->trace('1|pgstart');
+
+ my $value = DBD::Pg->parse_trace_flag('pglibpq');
+ DBI->trace($value);
+
+The parse_trace_flags method is used to convert one or more named
+flags to a number which can passed to the L</trace> method.
+DBD::Pg currently supports the DBI-specific flag, C<SQL>,
+as well as the ones listed below.
+
+Flags can be combined by using the parse_trace_flags method,
+which simply calls C<parse_trace_flag> on each item and
+combines them.
+
+Sometimes you may wish to turn the tracing on before you connect
+to the database. The second example above shows a way of doing this:
+the call to C<< DBD::Pg->parse_trace_flags >> provides a number than can
+be fed to C<< DBI->trace >> before you create a database handle.
+
+DBD::Pg supports the following trace flags:
+
+=over 4
+
+=item SQL
+
+Outputs all SQL statements. Note that the output provided will not
+necessarily be in a form suitable to passing directly to Postgres,
+as server-side prepared statements are used extensively by DBD::Pg.
+For maximum portability of output (but with a potential performance
+hit), use with C<< $dbh->{pg_server_prepare} = 0 >>.
+
+=item DBD
+
+Turns on all non-DBI flags, in other words, only the ones that are specific
+to DBD::Pg (all those below which start with the letters 'pg').
+
+=item pglibpq
+
+Outputs the name of each libpq function (without arguments) immediately
+before running it. This is a good way to trace the flow of your program
+at a low level. This information is also output if the trace level
+is set to 4 or greater.
+
+=item pgstart
+
+Outputs the name of each internal DBD::Pg function, and other information such as
+the function arguments or important global variables, as each function starts. This
+information is also output if the trace level is set to 4 or greater.
+
+=item pgend
+
+Outputs a simple message at the very end of each internal DBD::Pg function. This is also
+output if the trace level is set to 4 or greater.
+
+=item pgprefix
+
+Forces each line of trace output to begin with the string B<C<dbdpg: >>. This helps to
+differentiate it from the normal DBI trace output.
+
+=item pglogin
+
+Outputs a message showing the connection string right before a new database connection
+is attempted, a message when the connection was successful, and a message right after
+the database has been disconnected. Also output if trace level is 5 or greater.
+
+=back
+
+=for text See the DBI section on TRACING for more information.
+
+=for html See the <a href="http://search.cpan.org/~timb/DBI/DBI.pm#TRACING">DBI section on TRACING</a> for more information.<br />
+
+=head3 B<func>
+
+DBD::Pg uses the C<func> method to support a variety of functions.
+Note that the name of the function comes I<last>, after the arguments.
+
+=over
+
+=item table_attributes
+
+ $attrs = $dbh->func($table, 'table_attributes');
+
+Use of the tables_attributes function is no longer recommended. Instead,
+you can use the more portable C<column_info> and C<primary_key> methods
+to access the same information.
+
+The table_attributes method returns, for the given table argument, a
+reference to an array of hashes, each of which contains the following keys:
+
+ 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
+
+=item pg_lo_creat
+
+ $lobjId = $dbh->pg_lo_creat($mode);
+
+Creates a new large object and returns the object-id. C<$mode> is a bitmask
+describing read and write access to the new object. This setting is ignored
+since Postgres version 8.1. For backwards compatibility, however, you should
+set a valid mode anyway (see L</pg_lo_open> for a list of valid modes).
+
+Upon failure it returns C<undef>. This function cannot be used if AutoCommit is enabled.
+
+The old way of calling large objects functions is deprecated: $dbh->func(.., 'lo_);
+
+=item lo_open
+
+ $lobj_fd = $dbh->pg_lo_open($lobjId, $mode);
+
+Opens an existing large object and returns an object-descriptor for use in
+subsequent C<lo_*> calls. C<$mode> is a bitmask describing read and write
+access to the opened object. It may be one of:
+
+ $dbh->{pg_INV_READ}
+ $dbh->{pg_INV_WRITE}
+ $dbh->{pg_INV_READ} | $dbh->{pg_INV_WRITE}
+
+C<pg_INV_WRITE> and C<pg_INV_WRITE | pg_INV_READ> modes are identical; in
+both modes, the large object can be read from or written to.
+Reading from the object will provide the object as written in other committed
+transactions, along with any writes performed by the current transaction.
+Objects opened with C<pg_INV_READ> cannot be written to. Reading from this
+object will provide the stored data at the time of the transaction snapshot
+which was active when C<lo_write> was called.
+
+Returns C<undef> upon failure. Note that 0 is a perfectly correct (and common)
+object descriptor! This function cannot be used if AutoCommit is enabled.
+
+=item lo_write
+
+ $nbytes = $dbh->pg_lo_write($lobj_fd, $buffer, $len);
+
+Writes C<$len> bytes of c<$buffer> into the large object C<$lobj_fd>. Returns the number
+of bytes written and C<undef> upon failure. This function cannot be used if AutoCommit is enabled.
+
+=item lo_read
+
+ $nbytes = $dbh->pg_lo_read($lobj_fd, $buffer, $len);
+
+Reads C<$len> bytes into c<$buffer> from large object C<$lobj_fd>. Returns the number of
+bytes read and C<undef> upon failure. This function cannot be used if AutoCommit is enabled.
+
+=item lo_lseek
+
+ $loc = $dbh->pg_lo_lseek($lobj_fd, $offset, $whence);
+
+Changes the current read or write location on the large object
+C<$obj_id>. Currently C<$whence> can only be 0 (which is L_SET). Returns the current
+location and C<undef> upon failure. This function cannot be used if AutoCommit is enabled.
+
+=item lo_tell
+
+ $loc = $dbh->pg_lo_tell($lobj_fd);
+
+Returns the current read or write location on the large object C<$lobj_fd> and C<undef> upon failure.
+This function cannot be used if AutoCommit is enabled.
+
+=item lo_close
+
+ $lobj_fd = $dbh->pg_lo_close($lobj_fd);
+
+Closes an existing large object. Returns true upon success and false upon failure.
+This function cannot be used if AutoCommit is enabled.
+
+=item lo_unlink
+
+ $ret = $dbh->pg_lo_unlink($lobjId);
+
+Deletes an existing large object. Returns true upon success and false upon failure.
+This function cannot be used if AutoCommit is enabled.
+
+=item lo_import
+
+
+ $lobjId = $dbh->pg_lo_import($filename);
+
+Imports a Unix file as a large object and returns the object id of the new
+object or C<undef> upon failure.
+
+=item lo_export
+
+ $ret = $dbh->pg_lo_export($lobjId, $filename);
+
+Exports a large object into a Unix file. Returns false upon failure, true otherwise.
+
+=item getfd
+
+ $fd = $dbh->func('getfd');
+
+Deprecated, use L<< $dbh->{pg_socket}|/pg_socket >> instead.
+
+=back
+
+=head3 B<private_attribute_info>
+
+ $hashref = $dbh->private_attribute_info();
+ $hashref = $sth->private_attribute_info();
+
+Returns a hash of all private attributes used by DBD::Pg, for either
+a database or a statement handle. Currently, all the hash values are undef.
+
+=head1 ATTRIBUTES COMMON TO ALL HANDLES
+
+=head3 B<InactiveDestroy> (boolean)
+
+If set to true, then the L</disconnect> method will not be automatically called when
+the database handle goes out of scope. This is required if you are forking, and even
+then you must tread carefully and ensure that either the parent or the child (but not
+both!) handles all database calls from that point forwards, so that messages from the
+Postgres backend are only handled by one of the processes. If you don't set things up
+properly, you will see messages such as "I<server closed the connection unexpectedly>",
+and "I<message type 0x32 arrived from server while idle>". The best solution is to either
+have the child process reconnect to the database with a fresh database handle, or to
+rewrite your application not to use use forking. See the section on L</Asynchronous Queries>
+for a way to have your script continue to work while the database is processing a request.
+
+=head3 B<RaiseError> (boolean, inherited)
+
+Forces errors to always raise an exception. Although it defaults to off, it is recommended that this
+be turned on, as the alternative is to check the return value of every method (prepare, execute, fetch, etc.)
+manually, which is easy to forget to do.
+
+=head3 B<PrintError> (boolean, inherited)
+
+Forces database errors to also generate warnings, which can then be filtered with methods such as
+locally redefining I<$SIG{__WARN__}> or using modules such as C<CGI::Carp>. This attribute is on
+by default.
+
+=head3 B<ShowErrorStatement> (boolean, inherited)
+
+Appends information about the current statement to error messages. If placeholder information
+is available, adds that as well. Defaults to false.
+
+=head3 B<Warn> (boolean, inherited)
+
+Enables warnings. This is on by default, and should only be turned off in a local block
+for a short a time only when absolutely needed.
+
+=head3 B<Executed> (boolean, read-only)
+
+Indicates if a handle has been executed. For database handles, this value is true after the L</do> method has been called, or
+when one of the child statement handles has issued an L</execute>. Issuing a L</commit> or L</rollback> always resets the
+attribute to false for database handles. For statement handles, any call to L</execute> or its variants will flip the value to
+true for the lifetime of the statement handle.
+
+=head3 B<TraceLevel> (integer, inherited)
+
+Sets the trace level, similar to the L</trace> method. See the sections on
+L</trace> and L</parse_trace_flag> for more details.
+
+=head3 B<Active> (boolean, read-only)
+
+Indicates if a handle is active or not. For database handles, this indicates if the database has
+been disconnected or not. For statement handles, it indicates if all the data has been fetched yet
+or not. Use of this attribute is not encouraged.
+
+=head3 B<Kids> (integer, read-only)
+
+Returns the number of child processes created for each handle type. For a driver handle, indicates the number
+of database handles created. For a database handle, indicates the number of statement handles created. For
+statement handles, it always returns zero, because statement handles do not create kids.
+
+=head3 B<ActiveKids> (integer, read-only)
+
+Same as C<Kids>, but only returns those that are active.
+
+=head3 B<CachedKids> (hash ref)
+
+Returns a hashref of handles. If called on a database handle, returns all statement handles created by use of the
+C<prepare_cached> method. If called on a driver handle, returns all database handles created by the L</connect_cached>
+method.
+
+=head3 B<ChildHandles> (array ref)
+
+Implemented by DBI, no driver-specific impact.
+
+=head3 B<PrintWarn> (boolean, inherited)
+
+Implemented by DBI, no driver-specific impact.
+
+=head3 B<HandleError> (boolean, inherited)
+
+Implemented by DBI, no driver-specific impact.
+
+=head3 B<HandleSetErr> (code ref, inherited)
+
+Implemented by DBI, no driver-specific impact.
+
+=head3 B<ErrCount> (unsigned integer)
+
+Implemented by DBI, no driver-specific impact.
+
+=head3 B<FetchHashKeyName> (string, inherited)
+
+Implemented by DBI, no driver-specific impact.
+
+=head3 B<ChopBlanks> (boolean, inherited)
+
+Supported by DBD::Pg as proposed by DBI. This method is similar to the
+SQL function C<RTRIM>.
+
+=head3 B<Taint> (boolean, inherited)
+
+Implemented by DBI, no driver-specific impact.
+
+=head3 B<TaintIn> (boolean, inherited)
+
+Implemented by DBI, no driver-specific impact.
+
+=head3 B<TaintOut> (boolean, inherited)
+
+Implemented by DBI, no driver-specific impact.
+
+=head3 B<Profile> (inherited)
+
+Implemented by DBI, no driver-specific impact.
+
+=head3 B<Type> (scalar)
+
+Returns C<dr> for a driver handle, C<db> for a database handle, and C<st> for a statement handle.
+Should be rarely needed.
+
+=head3 B<LongReadLen>
+
+Not used by DBD::Pg
+
+=head3 B<LongTruncOk>
+
+Not used by DBD::Pg
+
+=head3 B<CompatMode>
+
+Not used by DBD::Pg
+
+=head1 DBI DATABASE HANDLE OBJECTS
+
+=head2 Database Handle Methods
+
+=head3 B<selectall_arrayref>
+
+ $ary_ref = $dbh->selectall_arrayref($sql);
+ $ary_ref = $dbh->selectall_arrayref($sql, \%attr);
+ $ary_ref = $dbh->selectall_arrayref($sql, \%attr, @bind_values);
+
+Returns a reference to an array containing the rows returned by preparing and executing the SQL string.
+See the DBI documentation for full details.
+
+=head3 B<selectall_hashref>
+
+ $hash_ref = $dbh->selectall_hashref($sql, $key_field);
+
+Returns a reference to a hash containing the rows returned by preparing and executing the SQL string.
+See the DBI documentation for full details.
+
+=head3 B<selectcol_arrayref>
+
+ $ary_ref = $dbh->selectcol_arrayref($sql, \%attr, @bind_values);
+
+Returns a reference to an array containing the first column
+from each rows returned by preparing and executing the SQL string. It is possible to specify exactly
+which columns to return. See the DBI documentation for full details.
+
+=head3 B<prepare>
+
+ $sth = $dbh->prepare($statement, \%attr);
+
+WARNING: DBD::Pg now (as of version 1.40) uses true prepared statements by sending them
+to the backend to be prepared by the Postgres server. Statements
+that were legal before may no longer work. See below for details.
+
+The prepare method prepares a statement for later execution. PostgreSQL supports
+prepared statements, which enables DBD::Pg to only send the query once, and
+simply send the arguments for every subsequent call to L</execute>.
+DBD::Pg can use these server-side prepared statements, or it can
+just send the entire query to the server each time. The best way
+is automatically chosen for each query. This will be sufficient for
+most users: keep reading for a more detailed explanation and some
+optional flags.
+
+Queries that do not begin with the word "SELECT", "INSERT",
+"UPDATE", or "DELETE" are never sent as server-side prepared statements.
+
+Deciding whether or not to use prepared statements depends on many factors,
+but you can force them to be used or not used by using the
+L</pg_server_prepare> attribute when calling L</prepare>. Setting this to "0" means to never use
+prepared statements. Setting L</pg_server_prepare> to "1" means that prepared
+statements should be used whenever possible. This is the default when connected
+to Postgres servers version 8.0 or higher. Servers that are version 7.4 get a special
+default value of "2", because server-side statements were only partially supported
+in that version. In this case, it only uses server-side prepares if all
+parameters are specifically bound.
+
+The L</pg_server_prepare> attribute can also be set at connection time like so:
+
+ $dbh = DBI->connect($DBNAME, $DBUSER, $DBPASS,
+ { AutoCommit => 0,
+ RaiseError => 1,
+ pg_server_prepare => 0,
+ });
+
+or you may set it after your database handle is created:
+
+ $dbh->{pg_server_prepare} = 1;
+
+To enable it for just one particular statement:
+
+ $sth = $dbh->prepare("SELECT id FROM mytable WHERE val = ?",
+ { pg_server_prepare => 1 });
+
+You can even toggle between the two as you go:
+
+ $sth->{pg_server_prepare} = 1;
+ $sth->execute(22);
+ $sth->{pg_server_prepare} = 0;
+ $sth->execute(44);
+ $sth->{pg_server_prepare} = 1;
+ $sth->execute(66);
+
+In the above example, the first execute will use the previously prepared statement.
+The second execute will not, but will build the query into a single string and send
+it to the server. The third one will act like the first and only send the arguments.
+Even if you toggle back and forth, a statement is only prepared once.
+
+Using prepared statements is in theory quite a bit faster: not only does the
+PostgreSQL backend only have to prepare the query only once, but DBD::Pg no
+longer has to worry about quoting each value before sending it to the server.
+
+However, there are some drawbacks. The server cannot always choose the ideal
+parse plan because it will not know the arguments before hand. But for most
+situations in which you will be executing similar data many times, the default
+plan will probably work out well. Programs such as PgBouncer which cache connections
+at a low level should not use prepared statements via DBD::Pg, or must take
+extra care in the application to account for the fact that prepared statements
+are not shared across database connections. Further discussion on this subject is beyond
+the scope of this documentation: please consult the pgsql-performance mailing
+list, L<http://archives.postgresql.org/pgsql-performance/>
+
+Only certain commands will be sent to a server-side prepare: currently these
+include C<SELECT>, C<INSERT>, C<UPDATE>, and C<DELETE>. DBD::Pg uses a simple
+naming scheme for the prepared statements themselves: B<dbdpg_XY_Z>, where B<Y> is the current
+PID, B<X> is either 'p' or 'n' (depending on if the PID is a positive or negative
+number), and B<Z> is a number that starts at 1 and increases each time a new statement
+is prepared. This number is tracked at the database handle level, so multiple
+statement handles will not collide.
+
+You cannot send more than one command at a time in the same prepare command
+(by separating them with semi-colons) when using server-side prepares.
+
+The actual C<PREPARE> is usually not performed until the first execute is called, due
+to the fact that information on the data types (provided by L</bind_param>) may
+be provided after the prepare but before the execute.
+
+A server-side prepare may happen before the first L</execute>, but only if the server can
+handle the server-side prepare, and the statement contains no placeholders. It will
+also be prepared if the L</pg_prepare_now> attribute is passed in and set to a true
+value. Similarly, the L</pg_prepare_now> attribute can be set to 0 to ensure that
+the statement is B<not> prepared immediately, although the cases in which you would
+want this are very rare. Finally, you can set the default behavior of all prepare
+statements by setting the L</pg_prepare_now> attribute on the database handle:
+
+ $dbh->{pg_prepare_now} = 1;
+
+The following two examples will be prepared right away:
+
+ $sth->prepare("SELECT 123"); ## no placeholders
+
+ $sth->prepare("SELECT 123, ?", {pg_prepare_now => 1});
+
+The following two examples will NOT be prepared right away:
+
+ $sth->prepare("SELECT 123, ?"); ## has a placeholder
+
+ $sth->prepare("SELECT 123", {pg_prepare_now => 0});
+
+There are times when you may want to prepare a statement yourself. To do this,
+simply send the C<PREPARE> statement directly to the server (e.g. with
+the L</do> method). Create a statement handle and set the prepared name via
+the L</pg_prepare_name> attribute. The statement handle can be created with a dummy
+statement, as it will not be executed. However, it should have the same
+number of placeholders as your prepared statement. Example:
+
+ $dbh->do('PREPARE mystat AS SELECT COUNT(*) FROM pg_class WHERE reltuples < ?');
+ $sth = $dbh->prepare('SELECT ?');
+ $sth->bind_param(1, 1, SQL_INTEGER);
+ $sth->{pg_prepare_name} = 'mystat';
+ $sth->execute(123);
+
+The above will run the equivalent of this query on the backend:
+
+ EXECUTE mystat(123);
+
+which is the equivalent of:
+
+ SELECT COUNT(*) FROM pg_class WHERE reltuples < 123;
+
+You can force DBD::Pg to send your query directly to the server by adding
+the L</pg_direct> attribute to your prepare call. This is not recommended,
+but is added just in case you need it.
+
+=head4 B<Placeholders>
+
+There are three types of placeholders that can be used in DBD::Pg. The first is
+the "question mark" type, in which each placeholder is represented by a single
+question mark character. This is the method recommended by the DBI specs and is the most
+portable. Each question mark is internally replaced by a "dollar sign number" in the order
+in which they appear in the query (important when using L</bind_param>).
+
+The method second type of placeholder is "dollar sign numbers". This is the method
+that Postgres uses internally and is overall probably the best method to use
+if you do not need compatibility with other database systems. DBD::Pg, like
+PostgreSQL, allows the same number to be used more than once in the query.
+Numbers must start with "1" and increment by one value (but can appear in any order
+within the query). If the same number appears more than once in a query, it is treated as a
+single parameter and all instances are replaced at once. Examples:
+
+Not legal:
+
+ $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $2'; # Does not start with 1
+
+ $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $3'; # Missing 2
+
+Legal:
+
+ $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $1';
+
+ $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $2';
+
+ $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $2 AND $1'; # legal but confusing
+
+ $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $2 AND reltuples > $1';
+
+ $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $1 AND reltuples > $1';
+
+In the final statement above, DBI thinks there is only one placeholder, so this
+statement will replace both placeholders:
+
+ $sth->bind_param(1, 2045);
+
+While a simple execute with no bind_param calls requires only a single argument as well:
+
+ $sth->execute(2045);
+
+The final placeholder type is "named parameters" in the format ":foo". While this
+syntax is supported by DBD::Pg, its use is discouraged in favor of
+dollar-sign numbers.
+
+The different types of placeholders cannot be mixed within a statement, but you may
+use different ones for each statement handle you have. This is confusing at best, so
+stick to one style within your program.
+
+If your queries use operators that contain question marks (e.g. some of the native
+Postgres geometric operators) or array slices (e.g. C<data[100:300]>), you can tell
+DBD::Pg to ignore any non-dollar sign placeholders by setting the
+L</pg_placeholder_dollaronly> attribute at either the database handle or the statement
+handle level. Examples:
+
+ $dbh->{pg_placeholder_dollaronly} = 1;
+ $sth = $dbh->prepare(q{SELECT * FROM mytable WHERE lseg1 ?# lseg2 AND name = $1});
+ $sth->execute('segname');
+
+Alternatively, you can set it at prepare time:
+
+ $sth = $dbh->prepare(q{SELECT * FROM mytable WHERE lseg1 ?-| lseg2 AND name = $1},
+ {pg_placeholder_dollaronly = 1});
+ $sth->execute('segname');
+
+=head3 B<prepare_cached>
+
+ $sth = $dbh->prepare_cached($statement, \%attr);
+
+Implemented by DBI, no driver-specific impact. This method is most useful
+when using a server that supports server-side prepares, and you have asked
+the prepare to happen immediately via the L</pg_prepare_now> attribute.
+
+=head3 B<do>
+
+ $rv = $dbh->do($statement);
+ $rv = $dbh->do($statement, \%attr);
+ $rv = $dbh->do($statement, \%attr, @bind_values);
+
+Prepare and execute a single statement. Returns the number of rows affected if the
+query was successful, returns undef if an error occurred, and returns -1 if the
+number of rows is unknown or not available. Note that this method will return B<0E0> instead
+of 0 for 'no rows were affected', in order to always return a true value if no error occurred.
+
+If neither C<\%attr> nor C<@bind_values> is given, the query will be sent directly
+to the server without the overhead of internally creating a statement handle and
+running prepare and execute, for a measurable speed increase.
+
+Note that an empty statement (a string with no length) will not be passed to
+the server; if you want a simple test, use "SELECT 123" or the L</ping> method.
+
+=head3 B<last_insert_id>
+
+ $rv = $dbh->last_insert_id(undef, $schema, $table, undef);
+ $rv = $dbh->last_insert_id(undef, $schema, $table, undef, {sequence => $seqname});
+
+Attempts to return the id of the last value to be inserted into a table.
+You can either provide a sequence name (preferred) or provide a table
+name with optional schema, and DBD::Pg will attempt to find the sequence itself.
+The current value of the sequence is returned by a call to the C<CURRVAL()>
+PostgreSQL function. This will fail if the sequence has not yet been used in the
+current database connection.
+
+If you do not know the name of the sequence, you can provide a table name and
+DBD::Pg will attempt to return the correct value. To do this, there must be at
+least one column in the table with a C<NOT NULL> constraint, that has a unique
+constraint, and which uses a sequence as a default value. If more than one column
+meets these conditions, the primary key will be used. This involves some
+looking up of things in the system table, so DBD::Pg will cache the sequence
+name for subsequent calls. If you need to disable this caching for some reason,
+(such as the sequence name changing), you can control it by adding C<< pg_cache => 0 >>
+to the final (hashref) argument for last_insert_id.
+
+Please keep in mind that this method is far from foolproof, so make your
+script use it properly. Specifically, make sure that it is called
+immediately after the insert, and that the insert does not add a value
+to the column that is using the sequence as a default value. However, because
+we are using sequences, you can be sure that the value you got back has not
+been used by any other process.
+
+Some examples:
+
+ $dbh->do('CREATE SEQUENCE lii_seq START 1');
+ $dbh->do(q{CREATE TABLE lii (
+ foobar INTEGER NOT NULL UNIQUE DEFAULT nextval('lii_seq'),
+ baz VARCHAR)});
+ $SQL = 'INSERT INTO lii(baz) VALUES (?)';
+ $sth = $dbh->prepare($SQL);
+ for (qw(uno dos tres cuatro)) {
+ $sth->execute($_);
+ my $newid = $dbh->last_insert_id(undef,undef,undef,undef,{sequence=>'lii_seq'});
+ print "Last insert id was $newid\n";
+ }
+
+If you did not want to worry about the sequence name:
+
+ $dbh->do('CREATE TABLE lii2 (
+ foobar SERIAL UNIQUE,
+ baz VARCHAR)');
+ $SQL = 'INSERT INTO lii2(baz) VALUES (?)';
+ $sth = $dbh->prepare($SQL);
+ for (qw(uno dos tres cuatro)) {
+ $sth->execute($_);
+ my $newid = $dbh->last_insert_id(undef,undef,"lii2",undef);
+ print "Last insert id was $newid\n";
+ }
+
+=head3 B<commit>
+
+ $rv = $dbh->commit;
+
+Issues a COMMIT to the server, indicating that the current transaction is finished and that
+all changes made will be visible to other processes. If AutoCommit is enabled, then
+a warning is given and no COMMIT is issued. Returns true on success, false on error.
+See also the the section on L</Transactions>.
+
+=head3 B<rollback>
+
+ $rv = $dbh->rollback;
+
+Issues a ROLLBACK to the server, which discards any changes made in the current transaction. If AutoCommit
+is enabled, then a warning is given and no ROLLBACK is issued. Returns true on success, and
+false on error. See also the the section on L</Transactions>.
+
+=head3 B<begin_work>
+
+This method turns on transactions until the next call to L</commit> or L</rollback>, if L</AutoCommit> is
+currently enabled. If it is not enabled, calling begin_work will issue an error. Note that the
+transaction will not actually begin until the first statement after begin_work is called.
+Example:
+
+ $dbh->{AutoCommit} = 1;
+ $dbh->do('INSERT INTO foo VALUES (123)'); ## Changes committed immediately
+ $dbh->begin_work();
+ ## Not in a transaction yet, but AutoCommit is set to 0
+
+ $dbh->do("INSERT INTO foo VALUES (345)");
+ ## DBD::PG actually issues two statements here:
+ ## BEGIN;
+ ## INSERT INTO foo VALUES (345)
+ ## We are now in a transaction
+
+ $dbh->commit();
+ ## AutoCommit is now set to 1 again
+
+=head3 B<disconnect>
+
+ $rv = $dbh->disconnect;
+
+Disconnects from the Postgres database. Any uncommitted changes will be rolled back upon disconnection. It's
+good policy to always explicitly call commit or rollback at some point before disconnecting, rather than
+relying on the default rollback behavior.
+
+This method may give warnings about "disconnect invalidates X active statement handle(s)". This means that
+you called C<< $sth->execute() >> but did not finish fetching all the rows from them. To avoid seeing this
+warning, either fetch all the rows or call C<< $sth->finish() >> for each executed statement handle.
+
+If the script exits before disconnect is called (or, more precisely, if the database handle is no longer
+referenced by anything), then the database handle's DESTROY method will call the rollback() and disconnect()
+methods automatically. It is best to explicitly disconnect rather than rely on this behavior.
+
+=head3 B<quote>
+
+ $rv = $dbh->quote($value, $data_type);
+
+This module implements its own C<quote> method. For simple string types, both backslashes
+and single quotes are doubled. You may also quote arrayrefs and receive a string
+suitable for passing into Postgres array columns.
+
+If the value contains backslashes, and the server is version 8.1 or higher,
+then the escaped string syntax will be used (which places a capital E before
+the first single quote). This syntax is always used when quoting bytea values
+on servers 8.1 and higher.
+
+The C<data_type> argument is optional and should be one of the type constants
+exported by DBD::Pg (such as PG_BYTEA). In addition to string, bytea, char, bool,
+and other standard types, the following geometric types are supported: point, line,
+lseg, box, path, polygon, and circle (PG_POINT, PG_LINE, PG_LSEG, PG_BOX,
+PG_PATH, PG_POLYGON, and PG_CIRCLE respectively). To quote a Postgres-specific
+data type, you must use a 'hashref' argument like so:
+
+ my $quotedval = $dbh->quote($value, { pg_type => PG_VARCHAR });
+
+B<NOTE:> The undocumented (and invalid) support for the C<SQL_BINARY> data
+type is officially deprecated. Use C<PG_BYTEA> with C<bind_param()> instead:
+
+ $rv = $sth->bind_param($param_num, $bind_value,
+ { pg_type => PG_BYTEA });
+
+=head3 B<quote_identifier>
+
+ $string = $dbh->quote_identifier( $name );
+ $string = $dbh->quote_identifier( undef, $schema, $table);
+
+Returns a quoted version of the supplied string, which is commonly a schema,
+table, or column name. The three argument form will return the schema and
+the table together, separated by a dot. Examples:
+
+ print $dbh->quote_identifier('grapefruit'); ## Prints: "grapefruit"
+
+ print $dbh->quote_identifier('juicy fruit'); ## Prints: "juicy fruit"
+
+ print $dbh->quote_identifier(undef, 'public', 'pg_proc');
+ ## Prints: "public"."pg_proc"
+
+=head3 B<pg_notifies>
+
+ $ret = $dbh->pg_notifies;
+
+Looks for any asynchronous notifications received and returns either C<undef>
+or a reference to a three-element array consisting of an event name, the PID
+of the backend that sent the NOTIFY command, and the optional payload string.
+Note that this does not check if the connection to the database is still valid first -
+for that, use the c<ping> method. You may need to commit if not in autocommit mode -
+new notices will not be picked up while in the middle of a transaction. An example:
+
+ $dbh->do("LISTEN abc");
+ $dbh->do("LISTEN def");
+
+ ## Hang around until we get the message we want
+ LISTENLOOP: {
+ while (my $notify = $dbh->pg_notifies) {
+ my ($name, $pid, $payload) = @$notify;
+ print qq{I received notice "$name" from PID $pid, payload was "$payload"\n};
+ ## Do something based on the notice received
+ }
+ $dbh->ping() or die qq{Ping failed!};
+ $dbh->commit();
+ sleep(5);
+ redo;
+ }
+
+Payloads will always be an empty string unless you are connecting to a Postgres
+server version 8.5 or higher.
+
+=head3 B<ping>
+
+ $rv = $dbh->ping;
+
+This C<ping> method is used to check the validity of a database handle. The value returned is
+either 0, indicating that the connection is no longer valid, or a positive integer, indicating
+the following:
+
+ Value Meaning
+ --------------------------------------------------
+ 1 Database is idle (not in a transaction)
+ 2 Database is active, there is a command in progress (usually seen after a COPY command)
+ 3 Database is idle within a transaction
+ 4 Database is idle, within a failed transaction
+
+Additional information on why a handle is not valid can be obtained by using the
+L</pg_ping> method.
+
+=head3 B<pg_ping>
+
+ $rv = $dbh->pg_ping;
+
+This is a DBD::Pg-specific extension to the L</ping> method. This will check the
+validity of a database handle in exactly the same way as C<ping>, but instead of
+returning a 0 for an invalid connection, it will return a negative number. So in
+addition to returning the positive numbers documented for C<ping>, it may also
+return the following:
+
+ Value Meaning
+ --------------------------------------------------
+ -1 There is no connection to the database at all (e.g. after C<disconnect>)
+ -2 An unknown transaction status was returned (e.g. after forking)
+ -3 The handle exists, but no data was returned from a test query.
+
+In practice, you should only ever see -1 and -2.
+
+=head3 B<get_info>
+
+ $value = $dbh->get_info($info_type);
+
+Supports a very large set (> 250) of the information types, including the minimum
+recommended by DBI.
+
+=head3 B<table_info>
+
+ $sth = $dbh->table_info(undef, $schema, $table, $type);
+
+Returns all tables and views visible to the current user.
+The schema and table arguments will do a C<LIKE> search if a percent sign (C<%>) or an
+underscore (C<_>) is detected in the argument. The C<$type> argument accepts a value of either
+"TABLE" or "VIEW" (using both is the default action). Note that a statement handle is returned,
+and not a direct list of tables. See the examples below for ways to handle this.
+
+The following fields are returned:
+
+B<TABLE_CAT>: Always NULL, as Postgres does not have the concept of catalogs.
+
+B<TABLE_SCHEM>: The name of the schema that the table or view is in.
+
+B<TABLE_NAME>: The name of the table or view.
+
+B<TABLE_TYPE>: The type of object returned. Will be one of "TABLE", "VIEW",
+or "SYSTEM TABLE".
+
+The TABLE_SCHEM and TABLE_NAME will be quoted via C<quote_ident()>.
+
+Two additional fields specific to DBD::Pg are returned:
+
+B<pg_schema>: the unquoted name of the schema
+
+B<pg_table>: the unquoted name of the table
+
+If your database supports tablespaces (version 8.0 or greater), two additional
+DBD::Pg specific fields are returned:
+
+B<pg_tablespace_name>: the name of the tablespace the table is in
+
+B<pg_tablespace_location>: the location of the tablespace the table is in
+
+Tables that have not been assigned to a particular tablespace (or views)
+will return NULL (C<undef>) for both of the above field.
+
+Rows are returned alphabetically, with all tables first, and then all views.
+
+Examples of use:
+
+ ## Display all tables and views in the public schema:
+ $sth = $dbh->table_info('', 'public', undef, undef);
+ for my $rel (@{$sth->fetchall_arrayref({})}) {
+ print "$rel->{TABLE_TYPE} name is $rel->{TABLE_NAME}\n";
+ }
+
+
+ # Display the schema of all tables named 'foo':
+ $sth = $dbh->table_info('', undef, 'foo', 'TABLE');
+ for my $rel (@{$sth->fetchall_arrayref({})}) {
+ print "Table name is $rel->{TABLE_SCHEM}.$rel->{TABLE_NAME}\n";
+ }
+
+=head3 B<column_info>
+
+ $sth = $dbh->column_info( undef, $schema, $table, $column );
+
+Supported by this driver as proposed by DBI with the follow exceptions.
+These fields are currently always returned with NULL (C<undef>) values:
+
+ TABLE_CAT
+ BUFFER_LENGTH
+ DECIMAL_DIGITS
+ NUM_PREC_RADIX
+ SQL_DATA_TYPE
+ SQL_DATETIME_SUB
+ CHAR_OCTET_LENGTH
+
+Also, six additional non-standard fields are returned:
+
+B<pg_type>: data type with additional info i.e. "character varying(20)"
+
+B<pg_constraint>: holds column constraint definition
+
+B<pg_schema>: the unquoted name of the schema
+
+B<pg_table>: the unquoted name of the table
+
+B<pg_column>: the unquoted name of the column
+
+B<pg_enum_values>: an array reference of allowed values for an enum column
+
+Note that the TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME fields all return
+output wrapped in quote_ident(). If you need the unquoted version, use
+the pg_ fields above.
+
+=head3 B<primary_key_info>
+
+ $sth = $dbh->primary_key_info( undef, $schema, $table, \%attr );
+
+Supported by this driver as proposed by DBI. 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 additional field, "DATA_TYPE", is returned and
+shows the data type for each of the arguments in the "COLUMN_NAME" field.
+
+This method will also return tablespace information for servers that support
+tablespaces. See the L</table_info> entry for more information.
+
+The five additional custom fields returned are:
+
+B<pg_tablespace_name>: name of the tablespace, if any
+
+B<pg_tablespace_location>: location of the tablespace
+
+B<pg_schema>: the unquoted name of the schema
+
+B<pg_table>: the unquoted name of the table
+
+B<pg_column>: the unquoted name of the column
+
+In addition to the standard format of returning one row for each column
+found for the primary key, you can pass the C<pg_onerow> attribute 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 the C<pg_onerow> attribute 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";
+ }
+ }
+
+=head3 B<primary_key>
+
+ @key_column_names = $dbh->primary_key(undef, $schema, $table);
+
+Simple interface to the L</primary_key_info> method. Returns a list of the column names that
+comprise the primary key of the specified table. The list is in primary key column sequence
+order. If there is no primary key then an empty list is returned.
+
+=head3 B<foreign_key_info>
+
+ $sth = $dbh->foreign_key_info( $pk_catalog, $pk_schema, $pk_table,
+ $fk_catalog, $fk_schema, $fk_table );
+
+Supported by this driver as proposed by DBI, using the SQL/CLI variant.
+There are no search patterns allowed, but leaving the C<$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
+to show the data type for the unique and foreign key columns. Foreign
+keys that have no named constraint (where the referenced column only has
+an unique index) will return C<undef> for the "UK_NAME" field.
+
+=head3 B<statistics_info>
+
+ $sth = $dbh->statistics_info( undef, $schema, $table, $unique_only, $quick );
+
+Returns a statement handle that can be fetched from to give statistics information
+on a specific table and its indexes. The C<$table> argument is mandatory. The
+C<$schema> argument is optional but recommended. The C<$unique_only> argument, if true,
+causes only information about unique indexes to be returned. The C<$quick> argument is
+not used by DBD::Pg. For information on the format of the rows returned, please see the DBI
+documentation.
+
+=for html <a href="http://search.cpan.org/~timb/DBI/DBI.pm#statistics_info">DBI section on statistics_info</a>
+
+=head3 B<tables>
+
+ @names = $dbh->tables( undef, $schema, $table, $type, \%attr );
+
+Supported by this driver as proposed by DBI. This method returns all tables
+and/or views which are visible to the current user: see L</table_info>
+for more information about the arguments. The name of the schema appears
+before the table or view name. This can be turned off by adding in the
+C<pg_noprefix> attribute:
+
+ my @tables = $dbh->tables( '', '', 'dbd_pg_test', '', {pg_noprefix => 1} );
+
+=head3 B<type_info_all>
+
+ $type_info_all = $dbh->type_info_all;
+
+Supported by this 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 |
+ +---------------+------------------------------------+
+
+=head3 B<type_info>
+
+ @type_info = $dbh->type_info($data_type);
+
+Returns a list of hash references holding information about one or more variants of $data_type.
+See the DBI documentation for more details.
+
+=head3 B<pg_server_trace>
+
+ $dbh->pg_server_trace($filehandle);
+
+Writes debugging information from the PostgreSQL backend to a file. This is
+not related to the DBI L</trace> method and you should not use this method unless
+you know what you are doing. If you do enable this, be aware that the file
+will grow very large, very quick. To stop logging to the file, use the
+L</pg_server_untrace> method. The first argument must be a file handle, not
+a filename. Example:
+
+ my $pid = $dbh->{pg_pid};
+ my $file = "pgbackend.$pid.debug.log";
+ open(my $fh, ">$file") or die qq{Could not open "$file": $!\n};
+ $dbh->pg_server_trace($fh);
+ ## Run code you want to trace here
+ $dbh->pg_server_untrace;
+ close($fh);
+
+=head3 B<pg_server_untrace>
+
+ $dbh->pg_server_untrace;
+
+Stop server logging to a previously opened file.
+
+=head3 B<selectrow_array>
+
+ @row_ary = $dbh->selectrow_array($sql);
+ @row_ary = $dbh->selectrow_array($sql, \%attr);
+ @row_ary = $dbh->selectrow_array($sql, \%attr, @bind_values);
+
+Returns an array of row information after preparing and executing the provided SQL string. The rows are returned
+by calling L</fetchrow_array>. The string can also be a statement handle generated by a previous prepare. Note that
+only the first row of data is returned. If called in a scalar context, only the first column of the first row is
+returned. Because this is not portable, it is not recommended that you use this method in that way.
+
+=head3 B<selectrow_arrayref>
+
+ $ary_ref = $dbh->selectrow_arrayref($statement);
+ $ary_ref = $dbh->selectrow_arrayref($statement, \%attr);
+ $ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @bind_values);
+
+Exactly the same as L</selectrow_array>, except that it returns a reference to an array, by internal use of
+the L</fetchrow_arrayref> method.
+
+=head3 B<selectrow_hashref>
+
+ $hash_ref = $dbh->selectrow_hashref($sql);
+ $hash_ref = $dbh->selectrow_hashref($sql, \%attr);
+ $hash_ref = $dbh->selectrow_hashref($sql, \%attr, @bind_values);
+
+Exactly the same as L</selectrow_array>, except that it returns a reference to an hash, by internal use of
+the L</fetchrow_hashref> method.
+
+=head3 B<clone>
+
+ $other_dbh = $dbh->clone();
+
+Creates a copy of the database handle by connecting with the same parameters as the original
+handle, then trying to merge the attributes. See the DBI documentation for complete usage.
+
+=head2 Database Handle Attributes
+
+=head3 B<AutoCommit> (boolean)
+
+Supported by DBD::Pg 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, but this may change
+in the future, so it is highly recommended that you explicitly set it when
+calling L</connect>. For details see the notes about L</Transactions>
+elsewhere in this document.
+
+=head3 B<pg_bool_tf> (boolean)
+
+DBD::Pg specific attribute. If true, boolean values will be returned
+as the characters 't' and 'f' instead of '1' and '0'.
+
+=head3 B<ReadOnly> (boolean)
+
+$dbh->{ReadOnly} = 1;
+
+Specifies if the current database connection should be in read-only mode or not.
+In this mode, changes that change the database are not allowed and will throw
+an error. Note: this method will B<not> work if L</AutoCommit> is true. The
+read-only effect is accomplished by sending a S<SET TRANSACTION READ ONLY> after
+every begin. For more details, please see:
+
+http://www.postgresql.org/docs/current/interactive/sql-set-transaction.html
+
+Please not that this method is not foolproof: there are still ways to update the
+database. Consider this a safety net to catch applications that should not be
+issuing commands such as INSERT, UPDATE, or DELETE.
+
+This method method requires DBI version 1.55 or better.
+
+=head3 B<pg_server_prepare> (integer)
+
+DBD::Pg specific attribute. Indicates if DBD::Pg should attempt to use server-side
+prepared statements. The default value, 1, indicates that prepared statements should
+be used whenever possible. See the section on the L</prepare> method for more information.
+
+=head3 B<pg_placeholder_dollaronly> (boolean)
+
+DBD::Pg specific attribute. Defaults to false. When true, question marks inside of statements
+are not treated as L<placeholders|/Placeholders>. Useful for statements that contain unquoted question
+marks, such as geometric operators.
+
+=head3 B<pg_enable_utf8> (boolean)
+
+DBD::Pg specific attribute. If true, then the C<utf8> flag will be turned on
+for returned character data (if the data is valid UTF-8). For details about
+the C<utf8> flag, see the C<Encode> module. This attribute is only relevant under
+perl 5.8 and later.
+
+=head3 B<pg_errorlevel> (integer)
+
+DBD::Pg specific attribute. Sets the amount of information returned by the server's
+error messages. Valid entries are 0, 1, and 2. Any other number will be forced to the
+default value of 1.
+
+A value of 0 ("TERSE") will show severity, primary text, and position only
+and will usually fit on a single line. A value of 1 ("DEFAULT") will also
+show any detail, hint, or context fields. A value of 2 ("VERBOSE") will
+show all available information.
+
+=head3 B<pg_lib_version> (integer, read-only)
+
+DBD::Pg specific attribute. Indicates which version of PostgreSQL that
+DBD::Pg was compiled against. In other words, which libraries were used.
+Returns a number with major, minor, and revision together; version 8.1.4
+would be returned as C<80104>.
+
+=head3 B<pg_server_version> (integer, read-only)
+
+DBD::Pg specific attribute. Indicates which version of PostgreSQL that
+the current database handle is connected to. Returns a number with major,
+minor, and revision together; version 8.0.1 would be C<80001>.
+
+=head3 B<Name> (string, read-only)
+
+Returns the name of the current database. This is the same as the DSN, without the
+"dbi:Pg:" part. Before version 2.0.0, this only returned the bare database name
+(e.g. 'foo'). From version 2.0.0 onwards, it returns the more correct
+output (e.g. 'dbname=foo')
+
+=head3 B<Username> (string, read-only)
+
+Returns the name of the user connected to the database.
+
+=head3 B<pg_db> (string, read-only)
+
+DBD::Pg specific attribute. Returns the name of the current database.
+
+=head3 B<pg_user> (string, read-only)
+
+DBD::Pg specific attribute. Returns the name of the user that
+connected to the server.
+
+=head3 B<pg_host> (string, read-only)
+
+DBD::Pg specific attribute. Returns the host of the current
+server connection. Locally connected hosts will return an empty
+string.
+
+=head3 B<pg_port> (integer, read-only)
+
+DBD::Pg specific attribute. Returns the port of the connection to
+the server.
+
+=head3 B<pg_socket> (integer, read-only)
+
+DBD::Pg specific attribute. Returns the file description number of
+the connection socket to the server.
+
+=head3 B<pg_pass> (string, read-only)
+
+DBD::Pg specific attribute. Returns the password used to connect
+to the server.
+
+=head3 B<pg_options> (string, read-only)
+
+DBD::Pg specific attribute. Returns the command-line options passed
+to the server. May be an empty string.
+
+=head3 B<pg_default_port> (integer, read-only)
+
+DBD::Pg specific attribute. Returns the default port used if none is
+specifically given.
+
+=head3 B<pg_pid> (integer, read-only)
+
+DBD::Pg specific attribute. Returns the process id (PID) of the
+backend server process handling the connection.
+
+=head3 B<pg_prepare_now> (boolean)
+
+DBD::Pg specific attribute. Default is off. If true, then the L</prepare> method will
+immediately prepare commands, rather than waiting until the first execute.
+
+=head3 B<pg_expand_array> (boolean)
+
+DBD::Pg specific attribute. Defaults to true. If false, arrays returned from the server will
+not be changed into a Perl arrayref, but remain as a string.
+
+=head3 B<pg_async_status> (integer, read-only)
+
+DBD::Pg specific attribute. Returns the current status of an L<asynchronous|/Asynchronous Queries>
+command. 0 indicates no asynchronous command is in progress, 1 indicates that
+an asynchronous command has started and -1 indicated that an asynchronous command
+has been cancelled.
+
+=head3 B<pg_standard_conforming_strings> (boolean, read-only)
+
+DBD::Pg specific attribute. Returns true if the server is currently using
+standard conforming strings. Only available if the target
+server is version 8.2 or better.
+
+=head3 B<pg_INV_READ> (integer, read-only)
+
+Constant to be used for the mode in L</lo_creat> and L</lo_open>.
+
+=head3 B<pg_INV_WRITE> (integer, read-only)
+
+Constant to be used for the mode in L</lo_creat> and L</lo_open>.
+
+=head3 B<Driver> (handle, read-only)
+
+Holds the handle of the parent driver. The only recommended use for this is to find the name
+of the driver using:
+
+ $dbh->{Driver}->{Name}
+
+=head3 B<pg_protocol> (integer, read-only)
+
+DBD::Pg specific attribute. Returns the version of the PostgreSQL server.
+If DBD::Pg is unable to figure out the version, it will return a "0". Otherwise,
+a "3" is returned.
+
+=head3 B<RowCacheSize>
+
+Not used by DBD::Pg
+
+=head1 DBI STATEMENT HANDLE OBJECTS
+
+=head2 Statement Handle Methods
+
+=head3 B<bind_param>
+
+ $rv = $sth->bind_param($param_num, $bind_value);
+ $rv = $sth->bind_param($param_num, $bind_value, $bind_type);
+ $rv = $sth->bind_param($param_num, $bind_value, \%attr);
+
+Allows the user to bind a value and/or a data type to a placeholder. This is
+especially important when using server-side prepares. See the
+L</prepare> method for more information.
+
+The value of C<$param_num> is a number if using the '?' or '$1' style
+placeholders. If using ":foo" style placeholders, the complete name
+(e.g. ":foo") must be given. For numeric values, you can either use a
+number or use a literal '$1'. See the examples below.
+
+The C<$bind_value> argument is fairly self-explanatory. A value of C<undef> will
+bind a C<NULL> to the placeholder. Using C<undef> is useful when you want
+to change just the type and will be overwriting the value later.
+(Any value is actually usable, but C<undef> is easy and efficient).
+
+The C<\%attr> hash is used to indicate the data type of the placeholder.
+The default value is "varchar". If you need something else, you must
+use one of the values provided by DBI or by DBD::Pg. To use a SQL value,
+modify your "use DBI" statement at the top of your script as follows:
+
+ use DBI qw(:sql_types);
+
+This will import some constants into your script. You can plug those
+directly into the L</bind_param> call. Some common ones that you will
+encounter are:
+
+ SQL_INTEGER
+
+To use PostgreSQL data types, import the list of values like this:
+
+ use DBD::Pg qw(:pg_types);
+
+You can then set the data types by setting the value of the C<pg_type>
+key in the hash passed to L</bind_param>.
+The current list of Postgres data types exported is:
+
+ PG_ABSTIME PG_ABSTIMEARRAY PG_ACLITEM PG_ACLITEMARRAY PG_ANY PG_ANYARRAY
+ PG_ANYELEMENT PG_ANYENUM PG_ANYNONARRAY PG_BIT PG_BITARRAY PG_BOOL
+ PG_BOOLARRAY PG_BOX PG_BOXARRAY PG_BPCHAR PG_BPCHARARRAY PG_BYTEA
+ PG_BYTEAARRAY PG_CHAR PG_CHARARRAY PG_CID PG_CIDARRAY PG_CIDR
+ PG_CIDRARRAY PG_CIRCLE PG_CIRCLEARRAY PG_CSTRING PG_CSTRINGARRAY PG_DATE
+ PG_DATEARRAY PG_FLOAT4 PG_FLOAT4ARRAY PG_FLOAT8 PG_FLOAT8ARRAY PG_GTSVECTOR
+ PG_GTSVECTORARRAY PG_INET PG_INETARRAY PG_INT2 PG_INT2ARRAY PG_INT2VECTOR
+ PG_INT2VECTORARRAY PG_INT4 PG_INT4ARRAY PG_INT8 PG_INT8ARRAY PG_INTERNAL
+ PG_INTERVAL PG_INTERVALARRAY PG_LANGUAGE_HANDLER PG_LINE PG_LINEARRAY PG_LSEG
+ PG_LSEGARRAY PG_MACADDR PG_MACADDRARRAY PG_MONEY PG_MONEYARRAY PG_NAME
+ PG_NAMEARRAY PG_NUMERIC PG_NUMERICARRAY PG_OID PG_OIDARRAY PG_OIDVECTOR
+ PG_OIDVECTORARRAY PG_OPAQUE PG_PATH PG_PATHARRAY PG_PG_ATTRIBUTE PG_PG_CLASS
+ PG_PG_PROC PG_PG_TYPE PG_POINT PG_POINTARRAY PG_POLYGON PG_POLYGONARRAY
+ PG_RECORD PG_RECORDARRAY PG_REFCURSOR PG_REFCURSORARRAY PG_REGCLASS PG_REGCLASSARRAY
+ PG_REGCONFIG PG_REGCONFIGARRAY PG_REGDICTIONARY PG_REGDICTIONARYARRAY PG_REGOPER PG_REGOPERARRAY
+ PG_REGOPERATOR PG_REGOPERATORARRAY PG_REGPROC PG_REGPROCARRAY PG_REGPROCEDURE PG_REGPROCEDUREARRAY
+ PG_REGTYPE PG_REGTYPEARRAY PG_RELTIME PG_RELTIMEARRAY PG_SMGR PG_TEXT
+ PG_TEXTARRAY PG_TID PG_TIDARRAY PG_TIME PG_TIMEARRAY PG_TIMESTAMP
+ PG_TIMESTAMPARRAY PG_TIMESTAMPTZ PG_TIMESTAMPTZARRAY PG_TIMETZ PG_TIMETZARRAY PG_TINTERVAL
+ PG_TINTERVALARRAY PG_TRIGGER PG_TSQUERY PG_TSQUERYARRAY PG_TSVECTOR PG_TSVECTORARRAY
+ PG_TXID_SNAPSHOT PG_TXID_SNAPSHOTARRAY PG_UNKNOWN PG_UUID PG_UUIDARRAY PG_VARBIT
+ PG_VARBITARRAY PG_VARCHAR PG_VARCHARARRAY PG_VOID PG_XID PG_XIDARRAY
+ PG_XML PG_XMLARRAY
+
+Data types are "sticky," in that once a data type is set to a certain placeholder,
+it will remain for that placeholder, unless it is explicitly set to something
+else afterwards. If the statement has already been prepared, and you switch the
+data type to something else, DBD::Pg will re-prepare the statement for you before
+doing the next execute.
+
+Examples:
+
+ use DBI qw(:sql_types);
+ use DBD::Pg qw(:pg_types);
+
+ $SQL = "SELECT id FROM ptable WHERE size > ? AND title = ?";
+ $sth = $dbh->prepare($SQL);
+
+ ## Both arguments below are bound to placeholders as "varchar"
+ $sth->execute(123, "Merk");
+
+ ## Reset the datatype for the first placeholder to an integer
+ $sth->bind_param(1, undef, SQL_INTEGER);
+
+ ## The "undef" bound above is not used, since we supply params to execute
+ $sth->execute(123, "Merk");
+
+ ## Set the first placeholder's value and data type
+ $sth->bind_param(1, 234, { pg_type => PG_TIMESTAMP });
+
+ ## Set the second placeholder's value and data type.
+ ## We don't send a third argument, so the default "varchar" is used
+ $sth->bind_param('$2', "Zool");
+
+ ## We realize that the wrong data type was set above, so we change it:
+ $sth->bind_param('$1', 234, { pg_type => SQL_INTEGER });
+
+ ## We also got the wrong value, so we change that as well.
+ ## Because the data type is sticky, we don't need to change it
+ $sth->bind_param(1, 567);
+
+ ## This executes the statement with 567 (integer) and "Zool" (varchar)
+ $sth->execute();
+
+=head3 B<bind_param_inout>
+
+ $rv = $sth->bind_param_inout($param_num, \$scalar, 0);
+
+
+Experimental support for this feature is provided. The first argument to
+bind_param_inout should be a placeholder number. The second argument
+should be a reference to a scalar variable in your script. The third argument
+is not used and should simply be set to 0. Note that what this really does is
+assign a returned column to the variable, in the order in which the column
+appears. For example:
+
+ my $foo = 123;
+ $sth = $dbh->prepare("SELECT 1+?::int");
+ $sth->bind_param_inout(1, \$foo, 0);
+ $foo = 222;
+ $sth->execute(444);
+ $sth->fetch;
+
+The above will cause $foo to have a new value of "223" after the final fetch.
+Note that the variables bound in this manner are very sticky, and will trump any
+values passed in to execute. This is because the binding is done as late as possible,
+at the execute() stage, allowing the value to be changed between the time it was bound
+and the time the query is executed. Thus, the above execute is the same as:
+
+ $sth->execute();
+
+=head3 B<bind_param_array>
+
+
+ $rv = $sth->bind_param_array($param_num, $array_ref_or_value)
+ $rv = $sth->bind_param_array($param_num, $array_ref_or_value, $bind_type)
+ $rv = $sth->bind_param_array($param_num, $array_ref_or_value, \%attr)
+
+Binds an array of values to a placeholder, so that each is used in turn by a call
+to the L</execute_array> method.
+
+=head3 B<execute>
+
+ $rv = $sth->execute(@bind_values);
+
+Executes a previously prepared statement. In addition to C<UPDATE>, C<DELETE>,
+C<INSERT> statements, for which it returns always the number of affected rows,
+the C<execute> method can also be used for C<SELECT ... INTO table> statements.
+
+The "prepare/bind/execute" process has changed significantly for PostgreSQL
+servers 7.4 and later: please see the C<prepare()> and C<bind_param()> entries for
+much more information.
+
+Setting one of the bind_values to "undef" is the equivalent of setting the value
+to NULL in the database. Setting the bind_value to $DBDPG_DEFAULT is equivalent
+to sending the literal string 'DEFAULT' to the backend. Note that using this
+option will force server-side prepares off until such time as PostgreSQL
+supports using DEFAULT in prepared statements.
+
+DBD::Pg also supports passing in arrays to execute: simply pass in an arrayref,
+and DBD::Pg will flatten it into a string suitable for input on the backend.
+
+If you are using Postgres version 8.2 or greater, you can also use any of the
+fetch methods to retrieve the values of a C<RETURNING> clause after you execute
+an C<UPDATE>, C<DELETE>, or C<INSERT>. For example:
+
+ $dbh->do(q{CREATE TABLE abc (id SERIAL, country TEXT)});
+ $SQL = q{INSERT INTO abc (country) VALUES (?) RETURNING id};
+ $sth = $dbh->prepare($SQL);
+ $sth->execute('France');
+ $countryid = $sth->fetch()->[0];
+ $sth->execute('New Zealand');
+ $countryid = $sth->fetch()->[0];
+
+=head3 B<execute_array>
+
+ $tuples = $sth->execute_array() or die $sth->errstr;
+ $tuples = $sth->execute_array(\%attr) or die $sth->errstr;
+ $tuples = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
+
+ ($tuples, $rows) = $sth->execute_array(\%attr) or die $sth->errstr;
+ ($tuples, $rows) = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
+
+Execute a prepared statement once for each item in a passed-in hashref, or items that
+were previously bound via the L</bind_param_array> method. See the DBI documentation
+for more details.
+
+=head3 B<execute_for_fetch>
+
+ $tuples = $sth->execute_for_fetch($fetch_tuple_sub);
+ $tuples = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
+
+ ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub);
+ ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
+
+Used internally by the L</execute_array> method, and rarely used directly. See the
+DBI documentation for more details.
+
+=head3 B<fetchrow_arrayref>
+
+ $ary_ref = $sth->fetchrow_arrayref;
+
+Fetches the next row of data from the statement handle, and returns a reference to an array
+holding the column values. Any columns that are NULL are returned as undef within the array.
+
+If there are no more rows or if an error occurs, the this method return undef. You should
+check C<< $sth->err >> afterwards (or use the L</RaiseError> attribute) to discover if the undef returned
+was due to an error.
+
+Note that the same array reference is returned for each fetch, so don't store the reference and
+then use it after a later fetch. Also, the elements of the array are also reused for each row,
+so take care if you want to take a reference to an element. See also L</bind_columns>.
+
+=head3 B<fetchrow_array>
+
+ @ary = $sth->fetchrow_array;
+
+Similar to the L</fetchrow_arrayref> method, but returns a list of column information rather than
+a reference to a list. Do not use this in a scalar context.
+
+=head3 B<fetchrow_hashref>
+
+ $hash_ref = $sth->fetchrow_hashref;
+ $hash_ref = $sth->fetchrow_hashref($name);
+
+Fetches the next row of data and returns a hashref containing the name of the columns as the keys
+and the data itself as the values. Any NULL value is returned as as undef value.
+
+If there are no more rows or if an error occurs, the this method return undef. You should
+check C<< $sth->err >> afterwards (or use the L</RaiseError> attribute) to discover if the undef returned
+was due to an error.
+
+The optional C<$name> argument should be either C<NAME>, C<NAME_lc> or C<NAME_uc>, and indicates
+what sort of transformation to make to the keys in the hash.
+
+=head3 B<fetchall_arrayref>
+
+ $tbl_ary_ref = $sth->fetchall_arrayref();
+ $tbl_ary_ref = $sth->fetchall_arrayref( $slice );
+ $tbl_ary_ref = $sth->fetchall_arrayref( $slice, $max_rows );
+
+Returns a reference to an array of arrays that contains all the remaining rows to be fetched from the
+statement handle. If there are no more rows, an empty arrayref will be returned. If an error occurs,
+the data read in so far will be returned. Because of this, you should always check C<< $sth->err >> after
+calling this method, unless L</RaiseError> has been enabled.
+
+If C<$slice> is an array reference, fetchall_arrayref uses the L</fetchrow_arrayref> method to fetch each
+row as an array ref. If the C<$slice> array is not empty then it is used as a slice to select individual
+columns by perl array index number (starting at 0, unlike column and parameter numbers which start at 1).
+
+With no parameters, or if $slice is undefined, fetchall_arrayref acts as if passed an empty array ref.
+
+If C<$slice> is a hash reference, fetchall_arrayref uses L</fetchrow_hashref> to fetch each row as a hash reference.
+
+See the DBI documentation for a complete discussion.
+
+=head3 B<fetchall_hashref>
+
+ $hash_ref = $sth->fetchall_hashref( $key_field );
+
+Returns a hashref containing all rows to be fetched from the statement handle. See the DBI documentation for
+a full discussion.
+
+=head3 B<finish>
+
+ $rv = $sth->finish;
+
+Indicates to DBI that you are finished with the statement handle and are not going to use it again. Only needed
+when you have not fetched all the possible rows.
+
+=head3 B<rows>
+
+ $rv = $sth->rows;
+
+Returns the number of rows returned by the last query. In contrast to many other DBD modules,
+the number of rows is available immediately after calling C<< $sth->execute >>. Note that
+the L</execute> method itself returns the number of rows itself, which means that this
+method is rarely needed.
+
+=head3 B<bind_col>
+
+ $rv = $sth->bind_col($column_number, \$var_to_bind);
+ $rv = $sth->bind_col($column_number, \$var_to_bind, \%attr );
+ $rv = $sth->bind_col($column_number, \$var_to_bind, $bind_type );
+
+Binds a Perl variable and/or some attributes to an output column of a SELECT statement.
+Column numbers count up from 1. You do not need to bind output columns in order to fetch data.
+
+See the DBI documentation for a discussion of the optional parameters C<\%attr> and C<$bind_type>
+
+=head3 B<bind_columns>
+
+ $rv = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
+
+Calls the L</bind_col> method for each column in the SELECT statement, using the supplied list.
+
+=head3 B<dump_results>
+
+ $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
+
+Fetches all the rows from the statement handle, calls C<DBI::neat_list> for each row, and
+prints the results to C<$fh> (which defaults to F<STDOUT>). Rows are separated by C<$lsep> (which defaults
+to a newline). Columns are separated by C<$fsep> (which defaults to a comma). The C<$maxlen> controls
+how wide the output can be, and defaults to 35.
+
+This method is designed as a handy utility for prototyping and testing queries. Since it uses
+"neat_list" to format and edit the string for reading by humans, it is not recommended
+for data transfer applications.
+
+=head3 B<blob_read>
+
+ $blob = $sth->blob_read($id, $offset, $len);
+
+Supported by DBD::Pg. This method is implemented by DBI but not
+currently documented by DBI, 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 (datatype LONG), PostgreSQL handles its blobs
+independent of any table by using so-called object identifiers. This explains
+why the C<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 parameters may be set to zero, in which
+case the whole blob is fetched at once.
+
+See also the PostgreSQL-specific functions concerning blobs, which are
+available via the C<func> interface.
+
+For further information and examples about blobs, please read the chapter
+about Large Objects in the PostgreSQL Programmer's Guide at
+L<http://www.postgresql.org/docs/current/static/largeobjects.html>.
+
+=head2 Statement Handle Attributes
+
+=head3 B<NUM_OF_FIELDS> (integer, read-only)
+
+Returns the number of columns returned by the current statement. A number will only be returned for
+SELECT statements, for SHOW statements (which always return C<1>), and for INSERT,
+UPDATE, and DELETE statements which contain a RETURNING clause.
+This method returns undef if called before C<execute()>.
+
+=head3 B<NUM_OF_PARAMS> (integer, read-only)
+
+Returns the number of placeholders in the current statement.
+
+=head3 B<NAME> (arrayref, read-only)
+
+Returns an arrayref of column names for the current statement. This
+method will only work for SELECT statements, for SHOW statements, and for
+INSERT, UPDATE, and DELETE statements which contain a RETURNING clause.
+This method returns undef if called before C<execute()>.
+
+=head3 B<NAME_lc> (arrayref, read-only)
+
+The same as the C<NAME> attribute, except that all column names are forced to lower case.
+
+=head3 B<NAME_uc> (arrayref, read-only)
+
+The same as the C<NAME> attribute, except that all column names are forced to upper case.
+
+=head3 B<NAME_hash> (hashref, read-only)
+
+Similar to the C<NAME> attribute, but returns a hashref of column names instead of an arrayref. The names of the columns
+are the keys of the hash, and the values represent the order in which the columns are returned, starting at 0.
+This method returns undef if called before C<execute()>.
+
+=head3 B<NAME_lc_hash> (hashref, read-only)
+
+The same as the C<NAME_hash> attribute, except that all column names are forced to lower case.
+
+=head3 B<NAME_uc_hash> (hashref, read-only)
+
+The same as the C<NAME_hash> attribute, except that all column names are forced to lower case.
+
+=head3 B<TYPE> (arrayref, read-only)
+
+Returns an arrayref indicating the data type for each column in the statement.
+This method returns undef if called before C<execute()>.
+
+=head3 B<PRECISION> (arrayref, read-only)
+
+Returns an arrayref of integer values for each column returned by the statement.
+The number indicates the precision for C<NUMERIC> columns, the size in number of
+characters for C<CHAR> and C<VARCHAR> columns, and for all other types of columns
+it returns the number of I<bytes>.
+This method returns undef if called before C<execute()>.
+
+=head3 B<SCALE> (arrayref, read-only)
+
+Returns an arrayref of integer values for each column returned by the statement. The number
+indicates the scale of the that column. The only type that will return a value is C<NUMERIC>.
+This method returns undef if called before C<execute()>.
+
+=head3 B<NULLABLE> (arrayref, read-only)
+
+Returns an arrayref of integer values for each column returned by the statement. The number
+indicates if the column is nullable or not. 0 = not nullable, 1 = nullable, 2 = unknown.
+This method returns undef if called before C<execute()>.
+
+=head3 B<Database> (dbh, read-only)
+
+Returns the database handle this statement handle was created from.
+
+=head3 B<ParamValues> (hash ref, read-only)
+
+Returns a reference to a hash containing the values currently bound to placeholders. If the "named parameters"
+type of placeholders are being used (such as ":foo"), then the keys of the hash will be the names of the
+placeholders (without the colon). If the "dollar sign numbers" type of placeholders are being used, the keys of the hash will
+be the numbers, without the dollar signs. If the "question mark" type is used, integer numbers will be returned,
+starting at one and increasing for every placeholder.
+
+If this method is called before L</execute>, the literal values passed in are returned. If called after
+L</execute>, then the quoted versions of the values are returned.
+
+=head3 B<ParamTypes> (hash ref, read-only)
+
+Returns a reference to a hash containing the type names currently bound to placeholders. The keys
+are the same as returned by the ParamValues method. The values are hashrefs containing a single key value
+pair, in which the key is either 'TYPE' if the type has a generic SQL equivalent, and 'pg_type' if the type can
+only be expressed by a Postgres type. The value is the internal number corresponding to the type originally
+passed in. (Placeholders that have not yet been bound will return undef as the value). This allows the output of
+ParamTypes to be passed back to the L</bind_param> method.
+
+=head3 B<Statement> (string, read-only)
+
+Returns the statement string passed to the most recent "prepare" method called in this database handle, even if that method
+failed. This is especially useful where "RaiseError" is enabled and the exception handler checks $@ and sees that a C<prepare>
+method call failed.
+
+=head3 B<pg_current_row> (integer, read-only)
+
+DBD::Pg specific attribute. Returns the number of the tuple (row) that was
+most recently fetched. Returns zero before and after fetching is performed.
+
+=head3 B<pg_numbound> (integer, read-only)
+
+DBD::Pg specific attribute. Returns the number of placeholders
+that are currently bound (via bind_param).
+
+=head3 B<pg_bound> (hashref, read-only)
+
+DBD::Pg specific attribute. Returns a hash of all named placeholders. The
+key is the name of the placeholder, and the value is a 0 or a 1, indicating if
+the placeholder has been bound yet (e.g. via bind_param)
+
+=head3 B<pg_size> (arrayref, read-only)
+
+DBD::Pg 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.
+
+=head3 B<pg_type> (arrayref, read-only)
+
+DBD::Pg specific attribute. It returns a reference to an array of strings
+for each column. The string shows the name of the data_type.
+
+=head3 B<pg_segments> (arrayref, read-only)
+
+DBD::Pg specific attribute. Returns an arrayref of the query split on the
+placeholders.
+
+=head3 B<pg_oid_status> (integer, read-only)
+
+DBD::Pg specific attribute. It returns the OID of the last INSERT command.
+
+=head3 B<pg_cmd_status> (integer, read-only)
+
+DBD::Pg specific attribute. It returns the type of the last
+command. Possible types are: "INSERT", "DELETE", "UPDATE", "SELECT".
+
+=head3 B<pg_direct> (boolean)
+
+DBD::Pg specific attribute. Default is false. If true, the query is passed
+directly to the backend without parsing for placeholders.
+
+=head3 B<pg_prepare_now> (boolean)
+
+DBD::Pg specific attribute. Default is off. If true, the query will be immediately
+prepared, rather than waiting for the L</execute> call.
+
+=head3 B<pg_prepare_name> (string)
+
+DBD::Pg specific attribute. Specifies the name of the prepared statement to use for this
+statement handle. Not normally needed, see the section on the L</prepare> method for
+more information.
+
+=head3 B<pg_server_prepare> (integer)
+
+DBD::Pg specific attribute. Indicates if DBD::Pg should attempt to use server-side
+prepared statements for this statement handle. The default value, 1, indicates that prepared
+statements should be used whenever possible. See the section on the L</prepare> method for
+more information.
+
+=head3 B<pg_placeholder_dollaronly> (boolean)
+
+DBD::Pg specific attribute. Defaults to off. When true, question marks inside of the query
+being prepared are not treated as placeholders. Useful for statements that contain unquoted question
+marks, such as geometric operators.
+
+=head3 B<pg_async> (integer)
+
+DBD::Pg specific attribute. Indicates the current behavior for asynchronous queries. See the section
+on L</Asynchronous Constants> for more information.
+
+=head3 B<RowsInCache>
+
+Not used by DBD::Pg
+
+=head3 B<RowCache>
+
+Not used by DBD::Pg
+
+=head3 B<CursorName>
+
+Not used by DBD::Pg. See the note about L</Cursors> elsewhere in this document.
+
+=head1 FURTHER INFORMATION
+
+=head2 Transactions
+
+Transaction behavior is controlled via the L</AutoCommit> attribute. For a
+complete definition of C<AutoCommit> please refer to the DBI documentation.
+
+According to the DBI specification the default for C<AutoCommit> is a true
+value. In this mode, any change to the database becomes valid immediately. Any
+C<BEGIN>, C<COMMIT> or C<ROLLBACK> statements will be rejected. DBD::Pg
+implements C<AutoCommit> by issuing a C<BEGIN> statement immediately before
+executing a statement, and a C<COMMIT> afterwards. Note that preparing a
+statement is not always enough to trigger the first C<BEGIN>, as the actual
+C<PREPARE> is usually postponed until the first call to L</execute>.
+
+=head2 Savepoints
+
+PostgreSQL version 8.0 introduced the concept of savepoints, which allows
+transactions to be rolled back to a certain point without affecting the
+rest of the transaction. DBD::Pg encourages using the following methods to
+control savepoints:
+
+=head3 C<pg_savepoint>
+
+Creates a savepoint. This will fail unless you are inside of a transaction. The
+only argument is the name of the savepoint. Note that PostgreSQL DOES allow
+multiple savepoints with the same name to exist.
+
+ $dbh->pg_savepoint("mysavepoint");
+
+=head3 C<pg_rollback_to>
+
+Rolls the database back to a named savepoint, discarding any work performed after
+that point. If more than one savepoint with that name exists, rolls back to the
+most recently created one.
+
+ $dbh->pg_rollback_to("mysavepoint");
+
+=head3 C<pg_release>
+
+Releases (or removes) a named savepoint. If more than one savepoint with that name
+exists, it will only destroy the most recently created one. Note that all savepoints
+created after the one being released are also destroyed.
+
+ $dbh->pg_release("mysavepoint");
+
+=head2 Asynchronous Queries
+
+It is possible to send a query to the backend and have your script do other work while the query is
+running on the backend. Both queries sent by the L</do> method, and by the L</execute> method can be
+sent asynchronously. (NOTE: This will only work if DBD::Pg has been compiled against Postgres libraries
+of version 8.0 or greater) The basic usage is as follows:
+
+ use DBD::Pg ':async';
+
+ print "Async do() example:\n";
+ $dbh->do("SELECT long_running_query()", {pg_async => PG_ASYNC});
+ do_something_else();
+ {
+ if ($dbh->pg_ready()) {
+ $res = $pg_result();
+ print "Result of do(): $res\n";
+ }
+ print "Query is still running...\n";
+ if (cancel_request_received) {
+ $dbh->pg_cancel();
+ }
+ sleep 1;
+ redo;
+ }
+
+ print "Async prepare/execute example:\n";
+ $sth = $dbh->prepare("SELECT long_running_query(1)", {pg_async => PG_ASYNC});
+ $sth->execute();
+
+ ## Changed our mind, cancel and run again:
+ $sth = $dbh->prepare("SELECT 678", {pg_async => PG_ASYNC + PG_OLDQUERY_CANCEL});
+ $sth->execute();
+
+ do_something_else();
+
+ if (!$sth->pg_ready) {
+ do_another_thing();
+ }
+
+ ## We wait until it is done, and get the result:
+ $res = $dbh->pg_result();
+
+=head3 Asynchronous Constants
+
+There are currently three asynchronous constants exported by DBD::Pg. You can import all of them by putting
+either of these at the top of your script:
+
+ use DBD::Pg;
+
+ use DBD::Pg ':async';
+
+You may also use the numbers instead of the constants, but using the constants is recommended as it
+makes your script more readable.
+
+=over 4
+
+=item PG_ASYNC
+
+This is a constant for the number 1. It is passed to either the L</do> or the L</prepare> method as a value
+to the pg_async key and indicates that the query should be sent asynchronously.
+
+=item PG_OLDQUERY_CANCEL
+
+This is a constant for the number 2. When passed to either the L</do> or the L</prepare> method, it causes any
+currently running asynchronous query to be cancelled and rolled back. It has no effect if no asynchronous
+query is currently running.
+
+=item PG_OLDQUERY_WAIT
+
+This is a constant for the number 4. When passed to either the L</do> or the L</prepare> method, it waits for any
+currently running asynchronous query to complete. It has no effect if there is no asynchronous query currently running.
+
+=back
+
+=head3 Asynchronous Methods
+
+=over 4
+
+=item B<pg_cancel>
+
+This database-level method attempts to cancel any currently running asynchronous query. It returns true if
+the cancel succeeded, and false otherwise. Note that a query that has finished before this method is executed
+will also return false. B<WARNING>: a successful cancellation will leave the database in an unusable state,
+so DBD::Pg will automatically clear out the error message and issue a ROLLBACK.
+
+ $result = $dbh->pg_cancel();
+
+=item B<pg_ready>
+
+This method can be called as a database handle method or (for convenience) as a statement handle method. Both simply
+see if a previously issued asynchronous query has completed yet. It returns true if the statement has finished, in which
+case you should then call the L</pg_result> method. Calls to C<pg_ready()> should only be used when you have other
+things to do while the query is running. If you simply want to wait until the query is done, do not call pg_ready()
+over and over, but simply call the pg_result() method.
+
+ my $time = 0;
+ while (!$dbh->pg_ready) {
+ print "Query is still running. Seconds: $time\n";
+ $time++;
+ sleep 1;
+ }
+ $result = $dbh->pg_result;
+
+=item B<pg_result>
+
+This database handle method returns the results of a previously issued asynchronous query. If the query is still
+running, this method will wait until it has finished. The result returned is the number of rows: the same thing
+that would have been returned by the asynchronous L</do> or L</execute> if it had been called without an asynchronous flag.
+
+ $result = $dbh->pg_result;
+
+=back
+
+=head3 Asynchronous Examples
+
+Here are some working examples of asynchronous queries. Note that we'll use the B<pg_sleep> function to emulate a
+long-running query.
+
+ use strict;
+ use warnings;
+ use Time::HiRes 'sleep';
+ use DBD::Pg ':async';
+
+ my $dbh = DBI->connect('dbi:Pg:dbname=postgres', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
+
+ ## Kick off a long running query on the first database:
+ my $sth = $dbh->prepare("SELECT pg_sleep(?)", {pg_async => PG_ASYNC});
+ $sth->execute(5);
+
+ ## While that is running, do some other things
+ print "Your query is processing. Thanks for waiting\n";
+ check_on_the_kids(); ## Expensive sub, takes at least three seconds.
+
+ while (!$dbh->pg_ready) {
+ check_on_the_kids();
+ ## If the above function returns quickly for some reason, we add a small sleep
+ sleep 0.1;
+ }
+
+ print "The query has finished. Gathering results\n";
+ my $result = $sth->pg_result;
+ print "Result: $result\n";
+ my $info = $sth->fetchall_arrayref();
+
+Without asynchronous queries, the above script would take about 8 seconds to run: five seconds waiting
+for the execute to finish, then three for the check_on_the_kids() function to return. With asynchronous
+queries, the script takes about 6 seconds to run, and gets in two iterations of check_on_the_kids in
+the process.
+
+Here's an example showing the ability to cancel a long-running query. Imagine two slave databases in
+different geographic locations over a slow network. You need information as quickly as possible, so
+you query both at once. When you get an answer, you tell the other one to stop working on your query,
+as you don't need it anymore.
+
+ use strict;
+ use warnings;
+ use Time::HiRes 'sleep';
+ use DBD::Pg ':async';
+
+ my $dbhslave1 = DBI->connect('dbi:Pg:dbname=postgres;host=slave1', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
+ my $dbhslave2 = DBI->connect('dbi:Pg:dbname=postgres;host=slave2', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
+
+ $SQL = "SELECT count(*) FROM largetable WHERE flavor='blueberry'";
+
+ my $sth1 = $dbhslave1->prepare($SQL, {pg_async => PG_ASYNC});
+ my $sth2 = $dbhslave2->prepare($SQL, {pg_async => PG_ASYNC});
+
+ $sth1->execute();
+ $sth2->execute();
+
+ my $winner;
+ while (!defined $winner) {
+ if ($sth1->pg_ready) {
+ $winner = 1;
+ }
+ elsif ($sth2->pg_ready) {
+ $winner = 2;
+ }
+ Time::HiRes::sleep 0.05;
+ }
+
+ my $count;
+ if ($winner == 1) {
+ $sth2->pg_cancel();
+ $sth1->pg_result();
+ $count = $sth1->fetchall_arrayref()->[0][0];
+ }
+ else {
+ $sth1->pg_cancel();
+ $sth2->pg_result();
+ $count = $sth2->fetchall_arrayref()->[0][0];
+ }
+
+=head2 Array support
+
+DBD::Pg allows arrays (as arrayrefs) to be passed in to both
+the L</quote> and the L</execute> methods. In both cases, the array is
+flattened into a string representing a Postgres array.
+
+When fetching rows from a table that contains a column with an
+array type, the result will be passed back to your script as an arrayref.
+
+To turn off the automatic parsing of returned arrays into arrayrefs,
+you can set the attribute L<pg_expand_array|/pg_expand_array_(boolean)>, which is true by default.
+
+ $dbh->{pg_expand_array} = 0;
+
+
+=head2 COPY support
+
+DBD::Pg allows for quick (bulk) reading and storing of data by using
+the B<COPY> command. The basic process is to use C<< $dbh->do >> to issue a
+COPY command, and then to either add rows using L</pg_putcopydata>, or to
+read them by using L</pg_getcopydata>.
+
+The first step is to put the server into "COPY" mode. This is done by
+sending a complete COPY command to the server, by using the L</do> method.
+For example:
+
+ $dbh->do("COPY foobar FROM STDIN");
+
+This would tell the server to enter a COPY IN mode (yes, that's confusing, but
+the I<mode> is COPY IN because of the I<command> COPY FROM). It is now ready to
+receive information via the L</pg_putcopydata> method. The complete syntax of the
+COPY command is more complex and not documented here: the canonical
+PostgreSQL documentation for COPY can be found at:
+
+http://www.postgresql.org/docs/current/static/sql-copy.html
+
+Once a COPY command has been issued, no other SQL commands are allowed
+until L</pg_putcopyend> has been issued (for COPY FROM), or the final
+L</pg_getcopydata> has been called (for COPY TO).
+
+Note: All other COPY methods (pg_putline, pg_getline, etc.) are now
+heavily deprecated in favor of the pg_getcopydata, pg_putcopydata, and
+pg_putcopyend methods.
+
+=head3 B<pg_getcopydata>
+
+Used to retrieve data from a table after the server has been put into a
+COPY OUT mode by calling "COPY tablename TO STDOUT". Data is always returned
+one data row at a time. The first argument to pg_getcopydata
+is the variable into which the data will be stored (this variable should not
+be undefined, or it may throw a warning, although it may be a reference). The
+pg_gecopydata method returns a number greater than 1 indicating the new size of
+the variable, or a -1 when the COPY has finished. Once a -1 has been returned, no
+other action is necessary, as COPY mode will have already terminated. Example:
+
+ $dbh->do("COPY mytable TO STDOUT");
+ my @data;
+ my $x=0;
+ 1 while $dbh->pg_getcopydata($data[$x++]) >= 0;
+
+There is also a variation of this method called B<pg_getcopydata_async>, which,
+as the name suggests, returns immediately. The only difference from the original
+method is that this version may return a 0, indicating that the row is not
+ready to be delivered yet. When this happens, the variable has not been changed,
+and you will need to call the method again until you get a non-zero result.
+(Data is still always returned one data row at a time.)
+
+=head3 B<pg_putcopydata>
+
+Used to put data into a table after the server has been put into COPY IN mode
+by calling "COPY tablename FROM STDIN". The only argument is the data you want
+inserted. Issue a pg_putcopyend() when you have added all your rows.
+
+The default delimiter is a tab character, but this can be changed in
+the COPY statement. Returns a 1 on successful input. Examples:
+
+ ## Simple example:
+ $dbh->do("COPY mytable FROM STDIN");
+ $dbh->pg_putcopydata("123\tPepperoni\t3\n");
+ $dbh->pg_putcopydata("314\tMushroom\t8\n");
+ $dbh->pg_putcopydata("6\tAnchovies\t100\n");
+ $dbh->pg_putcopyend();
+
+ ## This example uses explicit columns and a custom delimiter
+ $dbh->do("COPY mytable(flavor, slices) FROM STDIN WITH DELIMITER '~'");
+ $dbh->pg_putcopydata("Pepperoni~123\n");
+ $dbh->pg_putcopydata("Mushroom~314\n");
+ $dbh->pg_putcopydata("Anchovies~6\n");
+ $dbh->pg_putcopyend();
+
+=head3 B<pg_putcopyend>
+
+When you are finished with pg_putcopydata, call pg_putcopyend to let the server know
+that you are done, and it will return to a normal, non-COPY state. Returns a 1 on
+success. This method will fail if called when not in COPY IN mode.
+
+=head2 Large Objects
+
+DBD::Pg supports all largeobject functions provided by libpq via the
+C<func> method. Please note that access to a large object, even read-only
+large objects, must be put into a transaction.
+
+=head2 Cursors
+
+Although PostgreSQL supports cursors, they have not been used in the current
+implementation. When DBD::Pg was created, cursors in PostgreSQL could 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
+C<SELECT> statements. Therefore the L</execute> method fetches all data at
+once into data structures located in the front-end application. This fact
+must to be considered when selecting large amounts of data!
+
+You can use cursors in your application, but you'll need to do a little
+work. First you must declare your cursor. Now you can issue queries against
+the cursor, then select against your queries. This typically results in a
+double loop, like this:
+
+ # WITH HOLD is not needed if AutoCommit is off
+ $dbh->do("DECLARE csr CURSOR WITH HOLD FOR $sql");
+ while (1) {
+ my $sth = $dbh->prepare("fetch 1000 from csr");
+ $sth->execute;
+ last if 0 == $sth->rows;
+
+ while (my $row = $sth->fetchrow_hashref) {
+ # Do something with the data.
+ }
+ }
+ $dbh->do("CLOSE csr");
+
+=head2 Datatype bool
+
+The current implementation of PostgreSQL returns 't' for true and 'f' for
+false. From the Perl point of view, this is a rather unfortunate
+choice. DBD::Pg therefore translates the result for the C<BOOL> data type in a
+Perlish manner: 'f' becomes the number C<0> and 't' becomes the number C<1>. This way
+the application does not have to check the database-specific returned values for
+the data-type C<BOOL> because Perl treats C<0> as false and C<1> as true. You may
+set the L<pg_bool_tf|/pg_bool_tf_(boolean)> attribute to a true value 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.
+
+=head2 Schema support
+
+The PostgreSQL schema concept may differ from those of other databases. In a nutshell,
+a schema is a named collection of objects within a single database. Please refer to the
+PostgreSQL documentation for more details:
+
+L<http://www.postgresql.org/docs/current/static/ddl-schemas.html>
+
+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 C<SET search_path>, e.g.
+
+ $dbh->do("SET search_path TO my_schema, public");
+
+=head1 SEE ALSO
+
+=for text The B<DBI> module.
+
+=for html <a href="http://search.cpan.org/~timb/DBI/DBI.pm">The DBI module</a>
+
+=head1 BUGS
+
+To report a bug, or view the current list of bugs, please visit
+http://rt.cpan.org/Public/Dist/Display.html?Name=DBD-Pg
+
+=head1 AUTHORS
+
+DBI by Tim Bunce L<http://www.tim.bunce.name>
+
+The original DBD-Pg was by Edmund Mergl (E.Mergl@bawue.de) and Jeffrey W. Baker
+(jwbaker@acm.org). Major developers include David Wheeler <david@justatheory.com>, Jason
+Stewart <jason@openinformatics.com>, Bruce Momjian <pgman@candle.pha.pa.us>, and
+Greg Sabino Mullane <greg@turnstep.com>, with help from many others: see the F<Changes>
+file for a complete list.
+
+Parts of this package were originally copied from DBI and DBD-Oracle.
+
+B<Mailing List>
+
+The current maintainers may be reached through the 'dbd-pg' mailing list:
+<dbd-pg@perl.org>
+
+=head1 COPYRIGHT AND LICENSE
+
+Copyright (C) 1994-2010, Greg Sabino Mullane
+
+This module (DBD::Pg) is free software; you can redistribute it and/or modify it
+under the same terms as Perl 5.10.0. For more details, see the full text of the
+licenses in the directory LICENSES.
+
+=cut