← Index
NYTProf Performance Profile   « block view • line view • sub view »
For ddd2.pl
  Run on Tue May 25 16:52:24 2010
Reported on Tue May 25 16:57:05 2010

File /project/perl/lib/DBD/Pg.pm
Statements Executed 192
Statement Execution Time 123ms
Subroutines — ordered by exclusive time
Calls P F Exclusive
Time		 Inclusive
Time		 Subroutine
11273.9ms73.9msDBD::Pg::db::::_login DBD::Pg::db::_login (xsub)
71230.8ms30.8msDBD::Pg::db::::_ping DBD::Pg::db::_ping (xsub)
1111.01ms6.27msDBD::Pg::::driver DBD::Pg::driver
112757µs757µsDBD::Pg::::bootstrap DBD::Pg::bootstrap (xsub)
1212223µs223µsDBD::Pg::st::::_prepare DBD::Pg::st::_prepare (xsub)
11134µs34µsDBD::Pg::DefaultValue::::newDBD::Pg::DefaultValue::new
21219µs19µsDBD::Pg::dr::::CORE:match DBD::Pg::dr::CORE:match (opcode)
11217µs17µsDBD::Pg::dr::::CORE:subst DBD::Pg::dr::CORE:subst (opcode)
0000s0sDBD::Pg::::BEGIN DBD::Pg::BEGIN
0000s0sDBD::Pg::::CLONE DBD::Pg::CLONE
0000s0sDBD::Pg::::_pg_use_catalog DBD::Pg::_pg_use_catalog
0000s0sDBD::Pg::db::::BEGIN DBD::Pg::db::BEGIN
0000s0sDBD::Pg::db::::__ANON__[:386] DBD::Pg::db::__ANON__[:386]
0000s0sDBD::Pg::db::::__ANON__[:393] DBD::Pg::db::__ANON__[:393]
0000s0sDBD::Pg::db::::__ANON__[:399] DBD::Pg::db::__ANON__[:399]
0000s0sDBD::Pg::db::::_calc_col_size DBD::Pg::db::_calc_col_size
0000s0sDBD::Pg::db::::_prepare_from_data DBD::Pg::db::_prepare_from_data
0000s0sDBD::Pg::db::::column_info DBD::Pg::db::column_info
0000s0sDBD::Pg::db::::foreign_key_info DBD::Pg::db::foreign_key_info
0000s0sDBD::Pg::db::::get_info DBD::Pg::db::get_info
0000s0sDBD::Pg::db::::last_insert_id DBD::Pg::db::last_insert_id
0000s0sDBD::Pg::db::::parse_trace_flag DBD::Pg::db::parse_trace_flag
0000s0sDBD::Pg::db::::pg_ping DBD::Pg::db::pg_ping
0000s0sDBD::Pg::db::::pg_type_info DBD::Pg::db::pg_type_info
0000s0sDBD::Pg::db::::ping DBD::Pg::db::ping
0000s0sDBD::Pg::db::::prepare DBD::Pg::db::prepare
0000s0sDBD::Pg::db::::primary_key DBD::Pg::db::primary_key
0000s0sDBD::Pg::db::::primary_key_info DBD::Pg::db::primary_key_info
0000s0sDBD::Pg::db::::private_attribute_info DBD::Pg::db::private_attribute_info
0000s0sDBD::Pg::db::::statistics_info DBD::Pg::db::statistics_info
0000s0sDBD::Pg::db::::table_attributes DBD::Pg::db::table_attributes
0000s0sDBD::Pg::db::::table_info DBD::Pg::db::table_info
0000s0sDBD::Pg::db::::tables DBD::Pg::db::tables
0000s0sDBD::Pg::db::::type_info_all DBD::Pg::db::type_info_all
0000s0sDBD::Pg::dr::::BEGIN DBD::Pg::dr::BEGIN
0000s0sDBD::Pg::dr::::connect DBD::Pg::dr::connect
0000s0sDBD::Pg::dr::::data_sources DBD::Pg::dr::data_sources
0000s0sDBD::Pg::dr::::private_attribute_info DBD::Pg::dr::private_attribute_info
0000s0sDBD::Pg::::parse_trace_flag DBD::Pg::parse_trace_flag
0000s0sDBD::Pg::::parse_trace_flags DBD::Pg::parse_trace_flags
0000s0sDBD::Pg::st::::bind_param_array DBD::Pg::st::bind_param_array
0000s0sDBD::Pg::st::::parse_trace_flag DBD::Pg::st::parse_trace_flag
0000s0sDBD::Pg::st::::private_attribute_info DBD::Pg::st::private_attribute_info
0000s0sDBI::_firesafe::::BEGIN DBI::_firesafe::BEGIN
Call graph for these subroutines as a Graphviz dot language file.
Line State
ments		 Time
on line		 Calls Time
in subs		 Code
1# -*-cperl-*-
2# $Id: Pg.pm 13672 2009-12-17 17:04:11Z turnstep $
3#
4# Copyright (c) 2002-2009 Greg Sabino Mullane and others: see the Changes file
5# Portions Copyright (c) 2002 Jeffrey W. Baker
6# Portions Copyright (c) 1997-2001 Edmund Mergl
7# Portions Copyright (c) 1994-1997 Tim Bunce
8#
9# You may distribute under the terms of either the GNU General Public
10# License or the Artistic License, as specified in the Perl README file.
11
12
13386µs123µsuse strict;
# spent 23µs making 1 call to strict::import
14380µs1118µsuse warnings;
# spent 118µs making 1 call to warnings::import
15364µsuse 5.006001;
16
17{
1819µs package DBD::Pg;
19
204161µs2301µs use version; our $VERSION = qv('2.16.0');
# spent 226µs making 1 call to version::__ANON__[version.pm:32] # spent 75µs making 1 call to version::import
21
22351µs use DBI ();
23345µs use DynaLoader ();
24363µs use Exporter ();
2531.32ms1742µs use vars qw(@ISA %EXPORT_TAGS $err $errstr $sqlstate $drh $dbh $DBDPG_DEFAULT @EXPORT);
# spent 742µs making 1 call to vars::import
26110µs @ISA = qw(DynaLoader Exporter);
27
28
29183µs %EXPORT_TAGS =
30 (
31 async => [qw(PG_ASYNC PG_OLDQUERY_CANCEL PG_OLDQUERY_WAIT)],
32 pg_types => [qw(
33 PG_ABSTIME PG_ABSTIMEARRAY PG_ACLITEM PG_ACLITEMARRAY PG_ANY
34 PG_ANYARRAY PG_ANYELEMENT PG_ANYENUM PG_ANYNONARRAY PG_BIT
35 PG_BITARRAY PG_BOOL PG_BOOLARRAY PG_BOX PG_BOXARRAY
36 PG_BPCHAR PG_BPCHARARRAY PG_BYTEA PG_BYTEAARRAY PG_CHAR
37 PG_CHARARRAY PG_CID PG_CIDARRAY PG_CIDR PG_CIDRARRAY
38 PG_CIRCLE PG_CIRCLEARRAY PG_CSTRING PG_CSTRINGARRAY PG_DATE
39 PG_DATEARRAY PG_FLOAT4 PG_FLOAT4ARRAY PG_FLOAT8 PG_FLOAT8ARRAY
40 PG_GTSVECTOR PG_GTSVECTORARRAY PG_INET PG_INETARRAY PG_INT2
41 PG_INT2ARRAY PG_INT2VECTOR PG_INT2VECTORARRAY PG_INT4 PG_INT4ARRAY
42 PG_INT8 PG_INT8ARRAY PG_INTERNAL PG_INTERVAL PG_INTERVALARRAY
43 PG_LANGUAGE_HANDLER PG_LINE PG_LINEARRAY PG_LSEG PG_LSEGARRAY
44 PG_MACADDR PG_MACADDRARRAY PG_MONEY PG_MONEYARRAY PG_NAME
45 PG_NAMEARRAY PG_NUMERIC PG_NUMERICARRAY PG_OID PG_OIDARRAY
46 PG_OIDVECTOR PG_OIDVECTORARRAY PG_OPAQUE PG_PATH PG_PATHARRAY
47 PG_PG_ATTRIBUTE PG_PG_CLASS PG_PG_PROC PG_PG_TYPE PG_POINT
48 PG_POINTARRAY PG_POLYGON PG_POLYGONARRAY PG_RECORD PG_RECORDARRAY
49 PG_REFCURSOR PG_REFCURSORARRAY PG_REGCLASS PG_REGCLASSARRAY PG_REGCONFIG
50 PG_REGCONFIGARRAY PG_REGDICTIONARY PG_REGDICTIONARYARRAY PG_REGOPER PG_REGOPERARRAY
51 PG_REGOPERATOR PG_REGOPERATORARRAY PG_REGPROC PG_REGPROCARRAY PG_REGPROCEDURE
52 PG_REGPROCEDUREARRAY PG_REGTYPE PG_REGTYPEARRAY PG_RELTIME PG_RELTIMEARRAY
53 PG_SMGR PG_TEXT PG_TEXTARRAY PG_TID PG_TIDARRAY
54 PG_TIME PG_TIMEARRAY PG_TIMESTAMP PG_TIMESTAMPARRAY PG_TIMESTAMPTZ
55 PG_TIMESTAMPTZARRAY PG_TIMETZ PG_TIMETZARRAY PG_TINTERVAL PG_TINTERVALARRAY
56 PG_TRIGGER PG_TSQUERY PG_TSQUERYARRAY PG_TSVECTOR PG_TSVECTORARRAY
57 PG_TXID_SNAPSHOT PG_TXID_SNAPSHOTARRAY PG_UNKNOWN PG_UUID PG_UUIDARRAY
58 PG_VARBIT PG_VARBITARRAY PG_VARCHAR PG_VARCHARARRAY PG_VOID
59 PG_XID PG_XIDARRAY PG_XML PG_XMLARRAY
60 )]
61 );
62
63 {
6419µs package DBD::Pg::DefaultValue;
65239µs
# spent 34µs within DBD::Pg::DefaultValue::new which was called # once (34µs+0s) by DBI::install_driver at line 67
sub new { my $self = {}; return bless $self, shift; }
66 }
67125µs134µs $DBDPG_DEFAULT = DBD::Pg::DefaultValue->new();
# spent 34µs making 1 call to DBD::Pg::DefaultValue::new
68118µs1104µs Exporter::export_ok_tags('pg_types', 'async');
# spent 104µs making 1 call to Exporter::export_ok_tags
6919µs @EXPORT = qw($DBDPG_DEFAULT PG_ASYNC PG_OLDQUERY_CANCEL PG_OLDQUERY_WAIT PG_BYTEA);
70
71132µs193µs require_version DBI 1.52;
# spent 93µs making 1 call to Exporter::require_version
72
73142µs11.84ms bootstrap DBD::Pg $VERSION;
# spent 1.84ms making 1 call to DynaLoader::bootstrap
74
7515µs $err = 0; # holds error code for DBI::err
7616µs $errstr = ''; # holds error string for DBI::errstr
7716µs $sqlstate = ''; # holds five character SQLSTATE code
7815µs $drh = undef; # holds driver handle once initialized
79
80 ## These two methods are here to allow calling before connect()
81 sub parse_trace_flag {
82 my ($class, $flag) = @_;
83 return (0x7FFFFF00 - 0x08000000) if $flag eq 'DBD'; ## all but the prefix
84 return 0x01000000 if $flag eq 'pglibpq';
85 return 0x02000000 if $flag eq 'pgstart';
86 return 0x04000000 if $flag eq 'pgend';
87 return 0x08000000 if $flag eq 'pgprefix';
88 return 0x10000000 if $flag eq 'pglogin';
89 return 0x20000000 if $flag eq 'pgquote';
90 return DBI::parse_trace_flag($class, $flag);
91 }
92 sub parse_trace_flags {
93 my ($class, $flags) = @_;
94 return DBI::parse_trace_flags($class, $flags);
95 }
96
97 sub CLONE {
98 $drh = undef;
99 return;
100 }
101
102 ## Deprecated
103 sub _pg_use_catalog {
104 return 'pg_catalog.';
105 }
106
107
# spent 6.27ms (1.01+5.26) within DBD::Pg::driver which was called # once (1.01ms+5.26ms) by DBI::install_driver at line 775 of DBI.pm
sub driver {
10815µs return $drh if defined $drh;
10918µs my($class, $attr) = @_;
110
11116µs $class .= '::dr';
112
113148µs1271µs $drh = DBI::_new_drh($class, {
# spent 271µs making 1 call to DBI::_new_drh
114 'Name' => 'Pg',
115 'Version' => $VERSION,
116 'Err' => \$DBD::Pg::err,
117 'Errstr' => \$DBD::Pg::errstr,
118 'State' => \$DBD::Pg::sqlstate,
119 'Attribution' => "DBD::Pg $VERSION by Greg Sabino Mullane and others",
120 });
121
122
123140µs1175µs DBD::Pg::db->install_method('pg_cancel');
# spent 175µs making 1 call to DBD::_::common::install_method
124124µs1147µs DBD::Pg::db->install_method('pg_endcopy');
# spent 147µs making 1 call to DBD::_::common::install_method
125125µs1126µs DBD::Pg::db->install_method('pg_getline');
# spent 126µs making 1 call to DBD::_::common::install_method
126122µs1130µs DBD::Pg::db->install_method('pg_getcopydata');
# spent 130µs making 1 call to DBD::_::common::install_method
127123µs1126µs DBD::Pg::db->install_method('pg_getcopydata_async');
# spent 126µs making 1 call to DBD::_::common::install_method
128123µs1124µs DBD::Pg::db->install_method('pg_notifies');
# spent 124µs making 1 call to DBD::_::common::install_method
129123µs1123µs DBD::Pg::db->install_method('pg_putcopydata');
# spent 123µs making 1 call to DBD::_::common::install_method
130123µs1125µs DBD::Pg::db->install_method('pg_putcopyend');
# spent 125µs making 1 call to DBD::_::common::install_method
131122µs1120µs DBD::Pg::db->install_method('pg_ping');
# spent 120µs making 1 call to DBD::_::common::install_method
132122µs1125µs DBD::Pg::db->install_method('pg_putline');
# spent 125µs making 1 call to DBD::_::common::install_method
133124µs1125µs DBD::Pg::db->install_method('pg_ready');
# spent 125µs making 1 call to DBD::_::common::install_method
134124µs1147µs DBD::Pg::db->install_method('pg_release');
# spent 147µs making 1 call to DBD::_::common::install_method
135122µs1117µs DBD::Pg::db->install_method('pg_result');
# spent 117µs making 1 call to DBD::_::common::install_method
136124µs1130µs DBD::Pg::db->install_method('pg_rollback_to');
# spent 130µs making 1 call to DBD::_::common::install_method
137123µs1123µs DBD::Pg::db->install_method('pg_savepoint');
# spent 123µs making 1 call to DBD::_::common::install_method
138124µs1124µs DBD::Pg::db->install_method('pg_server_trace');
# spent 124µs making 1 call to DBD::_::common::install_method
139143µs1134µs DBD::Pg::db->install_method('pg_server_untrace');
# spent 134µs making 1 call to DBD::_::common::install_method
140122µs1123µs DBD::Pg::db->install_method('pg_type_info');
# spent 123µs making 1 call to DBD::_::common::install_method
141
142131µs1125µs DBD::Pg::st->install_method('pg_cancel');
# spent 125µs making 1 call to DBD::_::common::install_method
143122µs1122µs DBD::Pg::st->install_method('pg_result');
# spent 122µs making 1 call to DBD::_::common::install_method
144125µs1144µs DBD::Pg::st->install_method('pg_ready');
# spent 144µs making 1 call to DBD::_::common::install_method
145
146123µs1127µs DBD::Pg::db->install_method('pg_lo_creat');
# spent 127µs making 1 call to DBD::_::common::install_method
147123µs1136µs DBD::Pg::db->install_method('pg_lo_open');
# spent 136µs making 1 call to DBD::_::common::install_method
148123µs1124µs DBD::Pg::db->install_method('pg_lo_write');
# spent 124µs making 1 call to DBD::_::common::install_method
149123µs1140µs DBD::Pg::db->install_method('pg_lo_read');
# spent 140µs making 1 call to DBD::_::common::install_method
150123µs1139µs DBD::Pg::db->install_method('pg_lo_lseek');
# spent 139µs making 1 call to DBD::_::common::install_method
151126µs11.04ms DBD::Pg::db->install_method('pg_lo_tell');
# spent 1.04ms making 1 call to DBD::_::common::install_method
152131µs1148µs DBD::Pg::db->install_method('pg_lo_close');
# spent 148µs making 1 call to DBD::_::common::install_method
153127µs1123µs DBD::Pg::db->install_method('pg_lo_unlink');
# spent 123µs making 1 call to DBD::_::common::install_method
154123µs1132µs DBD::Pg::db->install_method('pg_lo_import');
# spent 132µs making 1 call to DBD::_::common::install_method
155123µs1144µs DBD::Pg::db->install_method('pg_lo_export');
# spent 144µs making 1 call to DBD::_::common::install_method
156
157121µs return $drh;
158
159 } ## end of driver
160
161
16216µs 1;
163
164} ## end of package DBD::Pg
165
166
167{
168110µs package DBD::Pg::dr;
169
17031.00ms123µs use strict;
# spent 23µs making 1 call to strict::import
171
172 ## Returns an array of formatted database names from the pg_database table
173 sub data_sources {
174
175 my $drh = shift;
176 my $attr = shift || '';
177 ## Future: connect to "postgres" when the minimum version we support is 8.0
178 my $connstring = 'dbname=template1';
179 if ($ENV{DBI_DSN}) {
180 ($connstring = $ENV{DBI_DSN}) =~ s/dbi:Pg://;
181 }
182 if (length $attr) {
183 $connstring .= ";$attr";
184 }
185
186 my $dbh = DBD::Pg::dr::connect($drh, $connstring) or return undef;
187 $dbh->{AutoCommit}=1;
188 my $SQL = 'SELECT pg_catalog.quote_ident(datname) FROM pg_catalog.pg_database ORDER BY 1';
189 my $sth = $dbh->prepare($SQL);
190 $sth->execute() or die $DBI::errstr;
191 $attr and $attr = ";$attr";
192 my @sources = map { "dbi:Pg:dbname=$_->[0]$attr" } @{$sth->fetchall_arrayref()};
193 $dbh->disconnect;
194 return @sources;
195 }
196
197
198 sub connect { ## no critic (ProhibitBuiltinHomonyms)
19917µs my ($drh, $dbname, $user, $pass, $attr) = @_;
200
201 ## Allow "db" and "database" as synonyms for "dbname"
202140µs117µs $dbname =~ s/\b(?:db|database)\s*=/dbname=/;
# spent 17µs making 1 call to DBD::Pg::dr::CORE:subst
203
20415µs my $name = $dbname;
205149µs219µs if ($dbname =~ m{dbname\s*=\s*[\"\']([^\"\']+)}) {
# spent 19µs making 2 calls to DBD::Pg::dr::CORE:match, avg 10µs/call
206 $name = "'$1'";
207 $dbname =~ s/\"/\'/g;
208 }
209 elsif ($dbname =~ m{dbname\s*=\s*([^;]+)}) {
210 $name = $1;
211 }
212
21314µs $user = defined($user) ? $user : defined $ENV{DBI_USER} ? $ENV{DBI_USER} : '';
21415µs $pass = defined($pass) ? $pass : defined $ENV{DBI_PASS} ? $ENV{DBI_PASS} : '';
215
216144µs1278µs my ($dbh) = DBI::_new_dbh($drh, {
# spent 278µs making 1 call to DBI::_new_dbh
217 'Name' => $dbname,
218 'Username' => $user,
219 'CURRENT_USER' => $user,
220 });
221
222 # Connect to the database..
223173.9ms173.9ms DBD::Pg::db::_login($dbh, $dbname, $user, $pass) or return undef;
# spent 73.9ms making 1 call to DBD::Pg::db::_login
224
225183µs134µs my $version = $dbh->{pg_server_version};
# spent 34µs making 1 call to DBI::common::FETCH
226185µs237µs $dbh->{private_dbdpg}{version} = $version;
# spent 28µs making 1 call to DBI::common::STORE # spent 9µs making 1 call to DBI::common::FETCH
227
22816µs if ($attr) {
229 if ($attr->{dbd_verbose}) {
230 $dbh->trace('DBD');
231 }
232 }
233
23418µs return $dbh;
235 }
236
237 sub private_attribute_info {
238 return {
239 };
240 }
241
242} ## end of package DBD::Pg::dr
243
244
245{
246110µs package DBD::Pg::db;
247
2483108µs14.53ms use DBI qw(:sql_types);
# spent 4.53ms making 1 call to Exporter::import
249
250312.3ms124µs use strict;
# spent 24µs making 1 call to strict::import
251
252 sub parse_trace_flag {
253 my ($h, $flag) = @_;
254 return DBD::Pg->parse_trace_flag($flag);
255 }
256
257 sub prepare {
2581296µs my($dbh, $statement, @attribs) = @_;
259
2601261µs return undef if ! defined $statement;
261
262 # Create a 'blank' statement handle:
26312260µs122.53ms my $sth = DBI::_new_sth($dbh, {
# spent 2.53ms making 12 calls to DBI::_new_sth, avg 211µs/call
264 'Statement' => $statement,
265 });
266
26712403µs12223µs DBD::Pg::st::_prepare($sth, $statement, @attribs) || 0;
# spent 223µs making 12 calls to DBD::Pg::st::_prepare, avg 19µs/call
268
2691276µs return $sth;
270 }
271
272 sub last_insert_id {
273
274 my ($dbh, $catalog, $schema, $table, $col, $attr) = @_;
275
276 ## Our ultimate goal is to get a sequence
277 my ($sth, $count, $SQL, $sequence);
278
279 ## Cache all of our table lookups? Default is yes
280 my $cache = 1;
281
282 ## Catalog and col are not used
283 $schema = '' if ! defined $schema;
284 $table = '' if ! defined $table;
285 my $cachename = "lii$table$schema";
286
287 if (defined $attr and length $attr) {
288 ## If not a hash, assume it is a sequence name
289 if (! ref $attr) {
290 $attr = {sequence => $attr};
291 }
292 elsif (ref $attr ne 'HASH') {
293 $dbh->set_err(1, 'last_insert_id must be passed a hashref as the final argument');
294 return undef;
295 }
296 ## Named sequence overrides any table or schema settings
297 if (exists $attr->{sequence} and length $attr->{sequence}) {
298 $sequence = $attr->{sequence};
299 }
300 if (exists $attr->{pg_cache}) {
301 $cache = $attr->{pg_cache};
302 }
303 }
304
305 if (! defined $sequence and exists $dbh->{private_dbdpg}{$cachename} and $cache) {
306 $sequence = $dbh->{private_dbdpg}{$cachename};
307 }
308 elsif (! defined $sequence) {
309 ## At this point, we must have a valid table name
310 if (! length $table) {
311 $dbh->set_err(1, 'last_insert_id needs at least a sequence or table name');
312 return undef;
313 }
314 my @args = ($table);
315 ## Make sure the table in question exists and grab its oid
316 my ($schemajoin,$schemawhere) = ('','');
317 if (length $schema) {
318 $schemajoin = "\n JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)";
319 $schemawhere = "\n AND n.nspname = ?";
320 push @args, $schema;
321 }
322 $SQL = "SELECT c.oid FROM pg_catalog.pg_class c $schemajoin\n WHERE relname = ?$schemawhere";
323 if (! length $schema) {
324 $SQL .= ' AND pg_catalog.pg_table_is_visible(c.oid)';
325 }
326 $sth = $dbh->prepare_cached($SQL);
327 $count = $sth->execute(@args);
328 if (!defined $count or $count eq '0E0') {
329 $sth->finish();
330 my $message = qq{Could not find the table "$table"};
331 length $schema and $message .= qq{ in the schema "$schema"};
332 $dbh->set_err(1, $message);
333 return undef;
334 }
335 my $oid = $sth->fetchall_arrayref()->[0][0];
336 $oid =~ /(\d+)/ or die qq{OID was not numeric?!?\n};
337 $oid = $1;
338 ## This table has a primary key. Is there a sequence associated with it via a unique, indexed column?
339 $SQL = "SELECT a.attname, i.indisprimary, pg_catalog.pg_get_expr(adbin,adrelid)\n".
340 "FROM pg_catalog.pg_index i, pg_catalog.pg_attribute a, pg_catalog.pg_attrdef d\n ".
341 "WHERE i.indrelid = $oid AND d.adrelid=a.attrelid AND d.adnum=a.attnum\n".
342 " AND a.attrelid = $oid AND i.indisunique IS TRUE\n".
343 " AND a.atthasdef IS TRUE AND i.indkey[0]=a.attnum\n".
344 q{ AND d.adsrc ~ '^nextval'};
345 $sth = $dbh->prepare($SQL);
346 $count = $sth->execute();
347 if (!defined $count or $count eq '0E0') {
348 $sth->finish();
349 $dbh->set_err(1, qq{No suitable column found for last_insert_id of table "$table"});
350 return undef;
351 }
352 my $info = $sth->fetchall_arrayref();
353
354 ## We have at least one with a default value. See if we can determine sequences
355 my @def;
356 for (@$info) {
357 next unless $_->[2] =~ /^nextval\(+'([^']+)'::/o;
358 push @$_, $1;
359 push @def, $_;
360 }
361 if (!@def) {
362 $dbh->set_err(1, qq{No suitable column found for last_insert_id of table "$table"\n});
363 }
364 ## Tiebreaker goes to the primary keys
365 if (@def > 1) {
366 my @pri = grep { $_->[1] } @def;
367 if (1 != @pri) {
368 $dbh->set_err(1, qq{No suitable column found for last_insert_id of table "$table"\n});
369 }
370 @def = @pri;
371 }
372 $sequence = $def[0]->[3];
373 ## Cache this information for subsequent calls
374 $dbh->{private_dbdpg}{$cachename} = $sequence;
375 }
376
377 $sth = $dbh->prepare_cached('SELECT currval(?)');
378 $count = $sth->execute($sequence);
379 return undef if ! defined $count;
380 return $sth->fetchall_arrayref()->[0][0];
381
382 } ## end of last_insert_id
383
384 sub ping {
385739µs my $dbh = shift;
3867164µs768µs local $SIG{__WARN__} = sub { } if $dbh->FETCH('PrintError');
# spent 68µs making 7 calls to DBI::common::FETCH, avg 10µs/call
387731.0ms730.8ms my $ret = DBD::Pg::db::_ping($dbh);
# spent 30.8ms making 7 calls to DBD::Pg::db::_ping, avg 4.40ms/call
388776µs return $ret < 1 ? 0 : $ret;
389 }
390
391 sub pg_ping {
392 my $dbh = shift;
393 local $SIG{__WARN__} = sub { } if $dbh->FETCH('PrintError');
394 return DBD::Pg::db::_ping($dbh);
395 }
396
397 sub pg_type_info {
398 my($dbh,$pg_type) = @_;
399 local $SIG{__WARN__} = sub { } if $dbh->FETCH('PrintError');
400 my $ret = DBD::Pg::db::_pg_type_info($pg_type);
401 return $ret;
402 }
403
404 # Column expected in statement handle returned.
405 # table_cat, table_schem, table_name, column_name, data_type, type_name,
406 # column_size, buffer_length, DECIMAL_DIGITS, NUM_PREC_RADIX, NULLABLE,
407 # REMARKS, COLUMN_DEF, SQL_DATA_TYPE, SQL_DATETIME_SUB, CHAR_OCTET_LENGTH,
408 # ORDINAL_POSITION, IS_NULLABLE
409 # The result set is ordered by TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.
410
411 sub column_info {
412 my $dbh = shift;
413 my ($catalog, $schema, $table, $column) = @_;
414
415 my @search;
416 ## If the schema or table has an underscore or a %, use a LIKE comparison
417 if (defined $schema and length $schema) {
418 push @search, 'n.nspname ' . ($schema =~ /[_%]/ ? 'LIKE ' : '= ') .
419 $dbh->quote($schema);
420 }
421 if (defined $table and length $table) {
422 push @search, 'c.relname ' . ($table =~ /[_%]/ ? 'LIKE ' : '= ') .
423 $dbh->quote($table);
424 }
425 if (defined $column and length $column) {
426 push @search, 'a.attname ' . ($column =~ /[_%]/ ? 'LIKE ' : '= ') .
427 $dbh->quote($column);
428 }
429
430 my $whereclause = join "\n\t\t\t\tAND ", '', @search;
431
432 my $schemajoin = 'JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)';
433
434 my $remarks = 'pg_catalog.col_description(a.attrelid, a.attnum)';
435
436 my $column_def = $dbh->{private_dbdpg}{version} >= 80000
437 ? 'pg_catalog.pg_get_expr(af.adbin, af.adrelid)'
438 : 'af.adsrc';
439
440 my $col_info_sql = qq!
441 SELECT
442 NULL::text AS "TABLE_CAT"
443 , quote_ident(n.nspname) AS "TABLE_SCHEM"
444 , quote_ident(c.relname) AS "TABLE_NAME"
445 , quote_ident(a.attname) AS "COLUMN_NAME"
446 , a.atttypid AS "DATA_TYPE"
447 , pg_catalog.format_type(a.atttypid, NULL) AS "TYPE_NAME"
448 , a.attlen AS "COLUMN_SIZE"
449 , NULL::text AS "BUFFER_LENGTH"
450 , NULL::text AS "DECIMAL_DIGITS"
451 , NULL::text AS "NUM_PREC_RADIX"
452 , CASE a.attnotnull WHEN 't' THEN 0 ELSE 1 END AS "NULLABLE"
453 , $remarks AS "REMARKS"
454 , $column_def AS "COLUMN_DEF"
455 , NULL::text AS "SQL_DATA_TYPE"
456 , NULL::text AS "SQL_DATETIME_SUB"
457 , NULL::text AS "CHAR_OCTET_LENGTH"
458 , a.attnum AS "ORDINAL_POSITION"
459 , CASE a.attnotnull WHEN 't' THEN 'NO' ELSE 'YES' END AS "IS_NULLABLE"
460 , pg_catalog.format_type(a.atttypid, a.atttypmod) AS "pg_type"
461 , '?' AS "pg_constraint"
462 , n.nspname AS "pg_schema"
463 , c.relname AS "pg_table"
464 , a.attname AS "pg_column"
465 , a.attrelid AS "pg_attrelid"
466 , a.attnum AS "pg_attnum"
467 , a.atttypmod AS "pg_atttypmod"
468 , t.typtype AS "_pg_type_typtype"
469 , t.oid AS "_pg_type_oid"
470 FROM
471 pg_catalog.pg_type t
472 JOIN pg_catalog.pg_attribute a ON (t.oid = a.atttypid)
473 JOIN pg_catalog.pg_class c ON (a.attrelid = c.oid)
474 LEFT JOIN pg_catalog.pg_attrdef af ON (a.attnum = af.adnum AND a.attrelid = af.adrelid)
475 $schemajoin
476 WHERE
477 a.attnum >= 0
478 AND c.relkind IN ('r','v')
479 $whereclause
480 ORDER BY "TABLE_SCHEM", "TABLE_NAME", "ORDINAL_POSITION"
481 !;
482
483 my $data = $dbh->selectall_arrayref($col_info_sql) or return undef;
484
485 # To turn the data back into a statement handle, we need
486 # to fetch the data as an array of arrays, and also have a
487 # a matching array of all the column names
488 my %col_map = (qw/
489 TABLE_CAT 0
490 TABLE_SCHEM 1
491 TABLE_NAME 2
492 COLUMN_NAME 3
493 DATA_TYPE 4
494 TYPE_NAME 5
495 COLUMN_SIZE 6
496 BUFFER_LENGTH 7
497 DECIMAL_DIGITS 8
498 NUM_PREC_RADIX 9
499 NULLABLE 10
500 REMARKS 11
501 COLUMN_DEF 12
502 SQL_DATA_TYPE 13
503 SQL_DATETIME_SUB 14
504 CHAR_OCTET_LENGTH 15
505 ORDINAL_POSITION 16
506 IS_NULLABLE 17
507 pg_type 18
508 pg_constraint 19
509 pg_schema 20
510 pg_table 21
511 pg_column 22
512 pg_enum_values 23
513 /);
514
515 for my $row (@$data) {
516 my $typoid = pop @$row;
517 my $typtype = pop @$row;
518 my $typmod = pop @$row;
519 my $attnum = pop @$row;
520 my $aid = pop @$row;
521
522 $row->[$col_map{COLUMN_SIZE}] =
523 _calc_col_size($typmod,$row->[$col_map{COLUMN_SIZE}]);
524
525 # Replace the Pg type with the SQL_ type
526 $row->[$col_map{DATA_TYPE}] = DBD::Pg::db::pg_type_info($dbh,$row->[$col_map{DATA_TYPE}]);
527
528 # Add pg_constraint
529 my $SQL = q{SELECT consrc FROM pg_catalog.pg_constraint WHERE contype = 'c' AND }.
530 qq{conrelid = $aid AND conkey = '{$attnum}'};
531 my $info = $dbh->selectall_arrayref($SQL);
532 if (@$info) {
533 $row->[19] = $info->[0][0];
534 }
535 else {
536 $row->[19] = undef;
537 }
538
539 if ( $typtype eq 'e' ) {
540 $SQL = "SELECT enumlabel FROM pg_catalog.pg_enum WHERE enumtypid = $typoid ORDER BY oid";
541 $row->[23] = $dbh->selectcol_arrayref($SQL);
542 }
543 else {
544 $row->[23] = undef;
545 }
546 }
547
548 # Since we've processed the data in Perl, we have to jump through a hoop
549 # To turn it back into a statement handle
550 #
551 return _prepare_from_data
552 (
553 'column_info',
554 $data,
555 [ sort { $col_map{$a} <=> $col_map{$b} } keys %col_map]
556 );
557 }
558
559 sub _prepare_from_data {
560 my ($statement, $data, $names, %attr) = @_;
561 my $sponge = DBI->connect('dbi:Sponge:', '', '', { RaiseError => 1 });
562 my $sth = $sponge->prepare($statement, { rows=>$data, NAME=>$names, %attr });
563 return $sth;
564 }
565
566 sub statistics_info {
567
568 my $dbh = shift;
569 my ($catalog, $schema, $table, $unique_only, $quick, $attr) = @_;
570
571 ## Catalog is ignored, but table is mandatory
572 return undef unless defined $table and length $table;
573
574 my $schema_where = '';
575 my @exe_args = ($table);
576
577 my $input_schema = (defined $schema and length $schema) ? 1 : 0;
578
579 if ($input_schema) {
580 $schema_where = 'AND n.nspname = ? AND n.oid = d.relnamespace';
581 push(@exe_args, $schema);
582 }
583 else {
584 $schema_where = 'AND n.oid = d.relnamespace';
585 }
586
587 my $table_stats_sql = qq{
588 SELECT d.relpages, d.reltuples, n.nspname
589 FROM pg_catalog.pg_class d, pg_catalog.pg_namespace n
590 WHERE d.relname = ? $schema_where
591 };
592
593 my $colnames_sql = qq{
594 SELECT
595 a.attnum, a.attname
596 FROM
597 pg_catalog.pg_attribute a, pg_catalog.pg_class d, pg_catalog.pg_namespace n
598 WHERE
599 a.attrelid = d.oid AND d.relname = ? $schema_where
600 };
601
602 my $stats_sql = qq{
603 SELECT
604 c.relname, i.indkey, i.indisunique, i.indisclustered, a.amname,
605 n.nspname, c.relpages, c.reltuples, i.indexprs,
606 pg_get_expr(i.indpred,i.indrelid) as predicate
607 FROM
608 pg_catalog.pg_index i, pg_catalog.pg_class c,
609 pg_catalog.pg_class d, pg_catalog.pg_am a,
610 pg_catalog.pg_namespace n
611 WHERE
612 d.relname = ? $schema_where AND d.oid = i.indrelid
613 AND i.indexrelid = c.oid AND c.relam = a.oid
614 ORDER BY
615 i.indisunique desc, a.amname, c.relname
616 };
617
618 my @output_rows;
619
620 # Table-level stats
621 if (!$unique_only) {
622 my $table_stats_sth = $dbh->prepare($table_stats_sql);
623 $table_stats_sth->execute(@exe_args) or return undef;
624 my $tst = $table_stats_sth->fetchrow_hashref or return undef;
625 push(@output_rows, [
626 undef, # TABLE_CAT
627 $tst->{nspname}, # TABLE_SCHEM
628 $table, # TABLE_NAME
629 undef, # NON_UNIQUE
630 undef, # INDEX_QUALIFIER
631 undef, # INDEX_NAME
632 'table', # TYPE
633 undef, # ORDINAL_POSITION
634 undef, # COLUMN_NAME
635 undef, # ASC_OR_DESC
636 $tst->{reltuples},# CARDINALITY
637 $tst->{relpages}, # PAGES
638 undef, # FILTER_CONDITION
639 ]);
640 }
641
642 # Fetch the column names for later use
643 my $colnames_sth = $dbh->prepare($colnames_sql);
644 $colnames_sth->execute(@exe_args) or return undef;
645 my $colnames = $colnames_sth->fetchall_hashref('attnum');
646
647 # Fetch the index definitions
648 my $sth = $dbh->prepare($stats_sql);
649 $sth->execute(@exe_args) or return undef;
650
651 STAT_ROW:
652 #use Data::Dumper;
653 #warn Dumper $stats_sql;
654 while (my $row = $sth->fetchrow_hashref) {
655 #warn Dumper $row;
656 next if $row->{indexprs}; # We can't return these accurately via this interface ...
657 next if $unique_only and !$row->{indisunique};
658
659 my $indtype = $row->{indisclustered}
660 ? 'clustered'
661 : ( $row->{amname} eq 'btree' )
662 ? 'btree'
663 : ($row->{amname} eq 'hash' )
664 ? 'hashed' : 'other';
665
666 my $nonunique = $row->{indisunique} ? 0 : 1;
667
668 my @index_row = (
669 undef, # TABLE_CAT
670 $row->{nspname}, # TABLE_SCHEM
671 $table, # TABLE_NAME
672 $nonunique, # NON_UNIQUE
673 undef, # INDEX_QUALIFIER
674 $row->{relname}, # INDEX_NAME
675 $indtype, # TYPE
676 undef, # ORDINAL_POSITION
677 undef, # COLUMN_NAME
678 'A', # ASC_OR_DESC
679 $row->{reltuples}, # CARDINALITY
680 $row->{relpages}, # PAGES
681 $row->{predicate}, # FILTER_CONDITION
682 );
683
684 my $col_nums = $row->{indkey};
685 $col_nums =~ s/^\s+//;
686 my @col_nums = split(/\s+/, $col_nums);
687
688 my $ord_pos = 1;
689 for my $col_num (@col_nums) {
690 my @copy = @index_row;
691 $copy[7] = $ord_pos++; # ORDINAL_POSITION
692 $copy[8] = $colnames->{$col_num}->{attname}; # COLUMN_NAME
693 push(@output_rows, \@copy);
694 }
695 }
696
697 my @output_colnames = qw/ TABLE_CAT TABLE_SCHEM TABLE_NAME NON_UNIQUE INDEX_QUALIFIER
698 INDEX_NAME TYPE ORDINAL_POSITION COLUMN_NAME ASC_OR_DESC
699 CARDINALITY PAGES FILTER_CONDITION /;
700
701 return _prepare_from_data('statistics_info', \@output_rows, \@output_colnames);
702 }
703
704 sub primary_key_info {
705
706 my $dbh = shift;
707 my ($catalog, $schema, $table, $attr) = @_;
708
709 ## Catalog is ignored, but table is mandatory
710 return undef unless defined $table and length $table;
711
712 my $whereclause = 'AND c.relname = ' . $dbh->quote($table);
713
714 if (defined $schema and length $schema) {
715 $whereclause .= "\n\t\t\tAND n.nspname = " . $dbh->quote($schema);
716 }
717
718 my $TSJOIN = 'pg_catalog.pg_tablespace t ON (t.oid = c.reltablespace)';
719 if ($dbh->{private_dbdpg}{version} < 80000) {
720 $TSJOIN = '(SELECT 0 AS oid, 0 AS spcname, 0 AS spclocation LIMIT 0) AS t ON (t.oid=1)';
721 }
722
723 my $pri_key_sql = qq{
724 SELECT
725 c.oid
726 , quote_ident(n.nspname)
727 , quote_ident(c.relname)
728 , quote_ident(c2.relname)
729 , i.indkey, quote_ident(t.spcname), quote_ident(t.spclocation)
730 , n.nspname, c.relname, c2.relname
731 FROM
732 pg_catalog.pg_class c
733 JOIN pg_catalog.pg_index i ON (i.indrelid = c.oid)
734 JOIN pg_catalog.pg_class c2 ON (c2.oid = i.indexrelid)
735 LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
736 LEFT JOIN $TSJOIN
737 WHERE
738 i.indisprimary IS TRUE
739 $whereclause
740 };
741
742 my $sth = $dbh->prepare($pri_key_sql) or return undef;
743 $sth->execute();
744 my $info = $sth->fetchall_arrayref()->[0];
745 return undef if ! defined $info;
746
747 # Get the attribute information
748 my $indkey = join ',', split /\s+/, $info->[4];
749 my $sql = qq{
750 SELECT a.attnum, pg_catalog.quote_ident(a.attname) AS colname,
751 pg_catalog.quote_ident(t.typname) AS typename
752 FROM pg_catalog.pg_attribute a, pg_catalog.pg_type t
753 WHERE a.attrelid = '$info->[0]'
754 AND a.atttypid = t.oid
755 AND attnum IN ($indkey);
756 };
757 $sth = $dbh->prepare($sql) or return undef;
758 $sth->execute();
759 my $attribs = $sth->fetchall_hashref('attnum');
760
761 my $pkinfo = [];
762
763 ## Normal way: complete "row" per column in the primary key
764 if (!exists $attr->{'pg_onerow'}) {
765 my $x=0;
766 my @key_seq = split/\s+/, $info->[4];
767 for (@key_seq) {
768 # TABLE_CAT
769 $pkinfo->[$x][0] = undef;
770 # SCHEMA_NAME
771 $pkinfo->[$x][1] = $info->[1];
772 # TABLE_NAME
773 $pkinfo->[$x][2] = $info->[2];
774 # COLUMN_NAME
775 $pkinfo->[$x][3] = $attribs->{$_}{colname};
776 # KEY_SEQ
777 $pkinfo->[$x][4] = $_;
778 # PK_NAME
779 $pkinfo->[$x][5] = $info->[3];
780 # DATA_TYPE
781 $pkinfo->[$x][6] = $attribs->{$_}{typename};
782 $pkinfo->[$x][7] = $info->[5];
783 $pkinfo->[$x][8] = $info->[6];
784 $pkinfo->[$x][9] = $info->[7];
785 $pkinfo->[$x][10] = $info->[8];
786 $pkinfo->[$x][11] = $info->[9];
787 $x++;
788 }
789 }
790 else { ## Nicer way: return only one row
791
792 # TABLE_CAT
793 $info->[0] = undef;
794 # TABLESPACES
795 $info->[7] = $info->[5];
796 $info->[8] = $info->[6];
797 # Unquoted names
798 $info->[9] = $info->[7];
799 $info->[10] = $info->[8];
800 $info->[11] = $info->[9];
801 # PK_NAME
802 $info->[5] = $info->[3];
803 # COLUMN_NAME
804 $info->[3] = 2==$attr->{'pg_onerow'} ?
805 [ map { $attribs->{$_}{colname} } split /\s+/, $info->[4] ] :
806 join ', ', map { $attribs->{$_}{colname} } split /\s+/, $info->[4];
807 # DATA_TYPE
808 $info->[6] = 2==$attr->{'pg_onerow'} ?
809 [ map { $attribs->{$_}{typename} } split /\s+/, $info->[4] ] :
810 join ', ', map { $attribs->{$_}{typename} } split /\s+/, $info->[4];
811 # KEY_SEQ
812 $info->[4] = 2==$attr->{'pg_onerow'} ?
813 [ split /\s+/, $info->[4] ] :
814 join ', ', split /\s+/, $info->[4];
815
816 $pkinfo = [$info];
817 }
818
819 my @cols = (qw(TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME
820 KEY_SEQ PK_NAME DATA_TYPE));
821 push @cols, 'pg_tablespace_name', 'pg_tablespace_location';
822 push @cols, 'pg_schema', 'pg_table', 'pg_column';
823
824 return _prepare_from_data('primary_key_info', $pkinfo, \@cols);
825
826 }
827
828 sub primary_key {
829 my $sth = primary_key_info(@_[0..3], {pg_onerow => 2});
830 return defined $sth ? @{$sth->fetchall_arrayref()->[0][3]} : ();
831 }
832
833
834 sub foreign_key_info {
835
836 my $dbh = shift;
837
838 ## PK: catalog, schema, table, FK: catalog, schema, table, attr
839
840 my $oldname = $dbh->{FetchHashKeyName};
841
842 local $dbh->{FetchHashKeyName} = 'NAME_lc';
843
844 ## Each of these may be undef or empty
845 my $pschema = $_[1] || '';
846 my $ptable = $_[2] || '';
847 my $fschema = $_[4] || '';
848 my $ftable = $_[5] || '';
849 my $args = $_[6];
850
851 ## No way to currently specify it, but we are ready when there is
852 my $odbc = 0;
853
854 ## Must have at least one named table
855 return undef if !$ptable and !$ftable;
856
857 ## If only the primary table is given, we return only those columns
858 ## that are used as foreign keys, even if that means that we return
859 ## unique keys but not primary one. We also return all the foreign
860 ## tables/columns that are referencing them, of course.
861
862 ## The first step is to find the oid of each specific table in the args:
863 ## Return undef if no matching relation found
864 my %oid;
865 for ([$ptable, $pschema, 'P'], [$ftable, $fschema, 'F']) {
866 if (length $_->[0]) {
867 my $SQL = "SELECT c.oid AS schema FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n\n".
868 'WHERE c.relnamespace = n.oid AND c.relname = ' . $dbh->quote($_->[0]);
869 if (length $_->[1]) {
870 $SQL .= ' AND n.nspname = ' . $dbh->quote($_->[1]);
871 }
872 my $info = $dbh->selectall_arrayref($SQL);
873 return undef if ! @$info;
874 $oid{$_->[2]} = $info->[0][0];
875 }
876 }
877
878 ## We now need information about each constraint we care about.
879 ## Foreign table: only 'f' / Primary table: only 'p' or 'u'
880 my $WHERE = $odbc ? q{((contype = 'p'} : q{((contype IN ('p','u')};
881 if (length $ptable) {
882 $WHERE .= " AND conrelid=$oid{'P'}::oid";
883 }
884 else {
885 $WHERE .= " AND conrelid IN (SELECT DISTINCT confrelid FROM pg_catalog.pg_constraint WHERE conrelid=$oid{'F'}::oid)";
886 if (length $pschema) {
887 $WHERE .= ' AND n2.nspname = ' . $dbh->quote($pschema);
888 }
889 }
890
891 $WHERE .= ")\n \t\t\t\tOR \n \t\t\t\t(contype = 'f'";
892 if (length $ftable) {
893 $WHERE .= " AND conrelid=$oid{'F'}::oid";
894 if (length $ptable) {
895 $WHERE .= " AND confrelid=$oid{'P'}::oid";
896 }
897 }
898 else {
899 $WHERE .= " AND confrelid = $oid{'P'}::oid";
900 if (length $fschema) {
901 $WHERE .= ' AND n2.nspname = ' . $dbh->quote($fschema);
902 }
903 }
904 $WHERE .= '))';
905
906 ## Grab everything except specific column names:
907 my $fk_sql = qq{
908 SELECT conrelid, confrelid, contype, conkey, confkey,
909 pg_catalog.quote_ident(c.relname) AS t_name, pg_catalog.quote_ident(n2.nspname) AS t_schema,
910 pg_catalog.quote_ident(n.nspname) AS c_schema, pg_catalog.quote_ident(conname) AS c_name,
911 CASE
912 WHEN confupdtype = 'c' THEN 0
913 WHEN confupdtype = 'r' THEN 1
914 WHEN confupdtype = 'n' THEN 2
915 WHEN confupdtype = 'a' THEN 3
916 WHEN confupdtype = 'd' THEN 4
917 ELSE -1
918 END AS update,
919 CASE
920 WHEN confdeltype = 'c' THEN 0
921 WHEN confdeltype = 'r' THEN 1
922 WHEN confdeltype = 'n' THEN 2
923 WHEN confdeltype = 'a' THEN 3
924 WHEN confdeltype = 'd' THEN 4
925 ELSE -1
926 END AS delete,
927 CASE
928 WHEN condeferrable = 'f' THEN 7
929 WHEN condeferred = 't' THEN 6
930 WHEN condeferred = 'f' THEN 5
931 ELSE -1
932 END AS defer
933 FROM pg_catalog.pg_constraint k, pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_catalog.pg_namespace n2
934 WHERE $WHERE
935 AND k.connamespace = n.oid
936 AND k.conrelid = c.oid
937 AND c.relnamespace = n2.oid
938 ORDER BY conrelid ASC
939 };
940
941 my $sth = $dbh->prepare($fk_sql);
942 $sth->execute();
943 my $info = $sth->fetchall_arrayref({});
944 return undef if ! defined $info or ! @$info;
945
946 ## Return undef if just ptable given but no fk found
947 return undef if ! length $ftable and ! grep { $_->{'contype'} eq 'f'} @$info;
948
949 ## Figure out which columns we need information about
950 my %colnum;
951 for my $row (@$info) {
952 for (@{$row->{'conkey'}}) {
953 $colnum{$row->{'conrelid'}}{$_}++;
954 }
955 if ($row->{'contype'} eq 'f') {
956 for (@{$row->{'confkey'}}) {
957 $colnum{$row->{'confrelid'}}{$_}++;
958 }
959 }
960 }
961 ## Get the information about the columns computed above
962 my $SQL = qq{
963 SELECT a.attrelid, a.attnum, pg_catalog.quote_ident(a.attname) AS colname,
964 pg_catalog.quote_ident(t.typname) AS typename
965 FROM pg_catalog.pg_attribute a, pg_catalog.pg_type t
966 WHERE a.atttypid = t.oid
967 AND (\n};
968
969 $SQL .= join "\n\t\t\t\tOR\n" => map {
970 my $cols = join ',' => keys %{$colnum{$_}};
971 "\t\t\t\t( a.attrelid = '$_' AND a.attnum IN ($cols) )"
972 } sort keys %colnum;
973
974 $sth = $dbh->prepare(qq{$SQL \)});
975 $sth->execute();
976 my $attribs = $sth->fetchall_arrayref({});
977
978 ## Make a lookup hash
979 my %attinfo;
980 for (@$attribs) {
981 $attinfo{"$_->{'attrelid'}"}{"$_->{'attnum'}"} = $_;
982 }
983
984 ## This is an array in case we have identical oid/column combos. Lowest oid wins
985 my %ukey;
986 for my $c (grep { $_->{'contype'} ne 'f' } @$info) {
987 ## Munge multi-column keys into sequential order
988 my $multi = join ' ' => sort @{$c->{'conkey'}};
989 push @{$ukey{$c->{'conrelid'}}{$multi}}, $c;
990 }
991
992 ## Finally, return as a SQL/CLI structure:
993 my $fkinfo = [];
994 my $x=0;
995 for my $t (sort { $a->{'c_name'} cmp $b->{'c_name'} } grep { $_->{'contype'} eq 'f' } @$info) {
996 ## We need to find which constraint row (if any) matches our confrelid-confkey combo
997 ## by checking out ukey hash. We sort for proper matching of { 1 2 } vs. { 2 1 }
998 ## No match means we have a pure index constraint
999 my $u;
1000 my $multi = join ' ' => sort @{$t->{'confkey'}};
1001 if (exists $ukey{$t->{'confrelid'}}{$multi}) {
1002 $u = $ukey{$t->{'confrelid'}}{$multi}->[0];
1003 }
1004 else {
1005 ## Mark this as an index so we can fudge things later on
1006 $multi = 'index';
1007 ## Grab the first one found, modify later on as needed
1008 $u = ((values %{$ukey{$t->{'confrelid'}}})[0]||[])->[0];
1009 ## Bail in case there was no match
1010 next if ! ref $u;
1011 }
1012
1013 ## ODBC is primary keys only
1014 next if $odbc and ($u->{'contype'} ne 'p' or $multi eq 'index');
1015
1016 my $conkey = $t->{'conkey'};
1017 my $confkey = $t->{'confkey'};
1018 for (my $y=0; $conkey->[$y]; $y++) {
1019 # UK_TABLE_CAT
1020 $fkinfo->[$x][0] = undef;
1021 # UK_TABLE_SCHEM
1022 $fkinfo->[$x][1] = $u->{'t_schema'};
1023 # UK_TABLE_NAME
1024 $fkinfo->[$x][2] = $u->{'t_name'};
1025 # UK_COLUMN_NAME
1026 $fkinfo->[$x][3] = $attinfo{$t->{'confrelid'}}{$confkey->[$y]}{'colname'};
1027 # FK_TABLE_CAT
1028 $fkinfo->[$x][4] = undef;
1029 # FK_TABLE_SCHEM
1030 $fkinfo->[$x][5] = $t->{'t_schema'};
1031 # FK_TABLE_NAME
1032 $fkinfo->[$x][6] = $t->{'t_name'};
1033 # FK_COLUMN_NAME
1034 $fkinfo->[$x][7] = $attinfo{$t->{'conrelid'}}{$conkey->[$y]}{'colname'};
1035 # ORDINAL_POSITION
1036 $fkinfo->[$x][8] = $conkey->[$y];
1037 # UPDATE_RULE
1038 $fkinfo->[$x][9] = "$t->{'update'}";
1039 # DELETE_RULE
1040 $fkinfo->[$x][10] = "$t->{'delete'}";
1041 # FK_NAME
1042 $fkinfo->[$x][11] = $t->{'c_name'};
1043 # UK_NAME (may be undef if an index with no named constraint)
1044 $fkinfo->[$x][12] = $multi eq 'index' ? undef : $u->{'c_name'};
1045 # DEFERRABILITY
1046 $fkinfo->[$x][13] = "$t->{'defer'}";
1047 # UNIQUE_OR_PRIMARY
1048 $fkinfo->[$x][14] = ($u->{'contype'} eq 'p' and $multi ne 'index') ? 'PRIMARY' : 'UNIQUE';
1049 # UK_DATA_TYPE
1050 $fkinfo->[$x][15] = $attinfo{$t->{'confrelid'}}{$confkey->[$y]}{'typename'};
1051 # FK_DATA_TYPE
1052 $fkinfo->[$x][16] = $attinfo{$t->{'conrelid'}}{$conkey->[$y]}{'typename'};
1053 $x++;
1054 } ## End each column in this foreign key
1055 } ## End each foreign key
1056
1057 my @CLI_cols = (qw(
1058 UK_TABLE_CAT UK_TABLE_SCHEM UK_TABLE_NAME UK_COLUMN_NAME
1059 FK_TABLE_CAT FK_TABLE_SCHEM FK_TABLE_NAME FK_COLUMN_NAME
1060 ORDINAL_POSITION UPDATE_RULE DELETE_RULE FK_NAME UK_NAME
1061 DEFERABILITY UNIQUE_OR_PRIMARY UK_DATA_TYPE FK_DATA_TYPE
1062 ));
1063
1064 my @ODBC_cols = (qw(
1065 PKTABLE_CAT PKTABLE_SCHEM PKTABLE_NAME PKCOLUMN_NAME
1066 FKTABLE_CAT FKTABLE_SCHEM FKTABLE_NAME FKCOLUMN_NAME
1067 KEY_SEQ UPDATE_RULE DELETE_RULE FK_NAME PK_NAME
1068 DEFERABILITY UNIQUE_OR_PRIMARY PK_DATA_TYPE FKDATA_TYPE
1069 ));
1070
1071 if ($oldname eq 'NAME_lc') {
1072 if ($odbc) {
1073 for my $col (@ODBC_cols) {
1074 $col = lc $col;
1075 }
1076 }
1077 else {
1078 for my $col (@CLI_cols) {
1079 $col = lc $col;
1080 }
1081 }
1082 }
1083
1084 return _prepare_from_data('foreign_key_info', $fkinfo, $odbc ? \@ODBC_cols : \@CLI_cols);
1085
1086 }
1087
1088
1089 sub table_info {
1090
1091 my $dbh = shift;
1092 my ($catalog, $schema, $table, $type) = @_;
1093
1094 my $tbl_sql = ();
1095
1096 my $extracols = q{,NULL::text AS pg_schema, NULL::text AS pg_table};
1097 if ( # Rule 19a
1098 (defined $catalog and $catalog eq '%')
1099 and (defined $schema and $schema eq '')
1100 and (defined $table and $table eq '')
1101 ) {
1102 $tbl_sql = qq{
1103 SELECT
1104 NULL::text AS "TABLE_CAT"
1105 , NULL::text AS "TABLE_SCHEM"
1106 , NULL::text AS "TABLE_NAME"
1107 , NULL::text AS "TABLE_TYPE"
1108 , NULL::text AS "REMARKS" $extracols
1109 };
1110 }
1111 elsif (# Rule 19b
1112 (defined $catalog and $catalog eq '')
1113 and (defined $schema and $schema eq '%')
1114 and (defined $table and $table eq '')
1115 ) {
1116 $extracols = q{,n.nspname AS pg_schema, NULL::text AS pg_table};
1117 $tbl_sql = qq{SELECT
1118 NULL::text AS "TABLE_CAT"
1119 , quote_ident(n.nspname) AS "TABLE_SCHEM"
1120 , NULL::text AS "TABLE_NAME"
1121 , NULL::text AS "TABLE_TYPE"
1122 , CASE WHEN n.nspname ~ '^pg_' THEN 'system schema' ELSE 'owned by ' || pg_get_userbyid(n.nspowner) END AS "REMARKS" $extracols
1123 FROM pg_catalog.pg_namespace n
1124 ORDER BY "TABLE_SCHEM"
1125 };
1126 }
1127 elsif (# Rule 19c
1128 (defined $catalog and $catalog eq '')
1129 and (defined $schema and $schema eq '')
1130 and (defined $table and $table eq '')
1131 and (defined $type and $type eq '%')
1132 ) {
1133 $tbl_sql = qq{
1134 SELECT
1135 NULL::text AS "TABLE_CAT"
1136 , NULL::text AS "TABLE_SCHEM"
1137 , NULL::text AS "TABLE_NAME"
1138 , 'TABLE' AS "TABLE_TYPE"
1139 , 'relkind: r' AS "REMARKS" $extracols
1140 UNION
1141 SELECT
1142 NULL::text AS "TABLE_CAT"
1143 , NULL::text AS "TABLE_SCHEM"
1144 , NULL::text AS "TABLE_NAME"
1145 , 'VIEW' AS "TABLE_TYPE"
1146 , 'relkind: v' AS "REMARKS" $extracols
1147 };
1148 }
1149 else {
1150 # Default SQL
1151 $extracols = q{,n.nspname AS pg_schema, c.relname AS pg_table};
1152 my @search;
1153 my $showtablespace = ', quote_ident(t.spcname) AS "pg_tablespace_name", quote_ident(t.spclocation) AS "pg_tablespace_location"';
1154
1155 ## If the schema or table has an underscore or a %, use a LIKE comparison
1156 if (defined $schema and length $schema) {
1157 push @search, 'n.nspname ' . ($schema =~ /[_%]/ ? 'LIKE ' : '= ') . $dbh->quote($schema);
1158 }
1159 if (defined $table and length $table) {
1160 push @search, 'c.relname ' . ($table =~ /[_%]/ ? 'LIKE ' : '= ') . $dbh->quote($table);
1161 }
1162 ## All we can see is "table" or "view". Default is both
1163 my $typesearch = q{IN ('r','v')};
1164 if (defined $type and length $type) {
1165 if ($type =~ /\btable\b/i and $type !~ /\bview\b/i) {
1166 $typesearch = q{= 'r'};
1167 }
1168 elsif ($type =~ /\bview\b/i and $type !~ /\btable\b/i) {
1169 $typesearch = q{= 'v'};
1170 }
1171 }
1172 push @search, "c.relkind $typesearch";
1173
1174 my $TSJOIN = 'pg_catalog.pg_tablespace t ON (t.oid = c.reltablespace)';
1175 if ($dbh->{private_dbdpg}{version} < 80000) {
1176 $TSJOIN = '(SELECT 0 AS oid, 0 AS spcname, 0 AS spclocation LIMIT 0) AS t ON (t.oid=1)';
1177 }
1178 my $whereclause = join "\n\t\t\t\t\t AND " => @search;
1179 $tbl_sql = qq{
1180 SELECT NULL::text AS "TABLE_CAT"
1181 , quote_ident(n.nspname) AS "TABLE_SCHEM"
1182 , quote_ident(c.relname) AS "TABLE_NAME"
1183 , CASE
1184 WHEN c.relkind = 'v' THEN
1185 CASE WHEN quote_ident(n.nspname) ~ '^pg_' THEN 'SYSTEM VIEW' ELSE 'VIEW' END
1186 ELSE
1187 CASE WHEN quote_ident(n.nspname) ~ '^pg_' THEN 'SYSTEM TABLE' ELSE 'TABLE' END
1188 END AS "TABLE_TYPE"
1189 , d.description AS "REMARKS" $showtablespace $extracols
1190 FROM pg_catalog.pg_class AS c
1191 LEFT JOIN pg_catalog.pg_description AS d
1192 ON (c.oid = d.objoid AND c.tableoid = d.classoid AND d.objsubid = 0)
1193 LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
1194 LEFT JOIN $TSJOIN
1195 WHERE $whereclause
1196 ORDER BY "TABLE_TYPE", "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME"
1197 };
1198 }
1199 my $sth = $dbh->prepare( $tbl_sql ) or return undef;
1200 $sth->execute();
1201
1202 return $sth;
1203 }
1204
1205 sub tables {
1206 my ($dbh, @args) = @_;
1207 my $attr = $args[4];
1208 my $sth = $dbh->table_info(@args) or return;
1209 my $tables = $sth->fetchall_arrayref() or return;
1210 my @tables = map { (! (ref $attr eq 'HASH' and $attr->{pg_noprefix})) ?
1211 "$_->[1].$_->[2]" : $_->[2] } @$tables;
1212 return @tables;
1213 }
1214
1215 sub table_attributes {
1216 my ($dbh, $table) = @_;
1217
1218 my $sth = $dbh->column_info(undef,undef,$table,undef);
1219
1220 my %convert = (
1221 COLUMN_NAME => 'NAME',
1222 DATA_TYPE => 'TYPE',
1223 COLUMN_SIZE => 'SIZE',
1224 NULLABLE => 'NOTNULL',
1225 REMARKS => 'REMARKS',
1226 COLUMN_DEF => 'DEFAULT',
1227 pg_constraint => 'CONSTRAINT',
1228 );
1229
1230 my $attrs = $sth->fetchall_arrayref(\%convert);
1231
1232 for my $row (@$attrs) {
1233 # switch the column names
1234 for my $name (keys %$row) {
1235 $row->{ $convert{$name} } = $row->{$name};
1236
1237 ## Keep some original columns
1238 delete $row->{$name} unless ($name eq 'REMARKS' or $name eq 'NULLABLE');
1239
1240 }
1241 # Moved check outside of loop as it was inverting the NOTNULL value for
1242 # attribute.
1243 # NOTNULL inverts the sense of NULLABLE
1244 $row->{NOTNULL} = ($row->{NOTNULL} ? 0 : 1);
1245
1246 my @pri_keys = ();
1247 @pri_keys = $dbh->primary_key( undef, undef, $table );
1248 $row->{PRIMARY_KEY} = scalar(grep { /^$row->{NAME}$/i } @pri_keys) ? 1 : 0;
1249 }
1250
1251 return $attrs;
1252
1253 }
1254
1255 sub _calc_col_size {
1256
1257 my $mod = shift;
1258 my $size = shift;
1259
1260
1261 if ((defined $size) and ($size > 0)) {
1262 return $size;
1263 } elsif ($mod > 0xffff) {
1264 my $prec = ($mod & 0xffff) - 4;
1265 $mod >>= 16;
1266 my $dig = $mod;
1267 return "$prec,$dig";
1268 } elsif ($mod >= 4) {
1269 return $mod - 4;
1270 } # else {
1271 # $rtn = $mod;
1272 # $rtn = undef;
1273 # }
1274
1275 return;
1276 }
1277
1278
1279 sub type_info_all {
1280 my ($dbh) = @_;
1281
1282 my $names =
1283 {
1284 TYPE_NAME => 0,
1285 DATA_TYPE => 1,
1286 COLUMN_SIZE => 2,
1287 LITERAL_PREFIX => 3,
1288 LITERAL_SUFFIX => 4,
1289 CREATE_PARAMS => 5,
1290 NULLABLE => 6,
1291 CASE_SENSITIVE => 7,
1292 SEARCHABLE => 8,
1293 UNSIGNED_ATTRIBUTE => 9,
1294 FIXED_PREC_SCALE => 10,
1295 AUTO_UNIQUE_VALUE => 11,
1296 LOCAL_TYPE_NAME => 12,
1297 MINIMUM_SCALE => 13,
1298 MAXIMUM_SCALE => 14,
1299 SQL_DATA_TYPE => 15,
1300 SQL_DATETIME_SUB => 16,
1301 NUM_PREC_RADIX => 17,
1302 INTERVAL_PRECISION => 18,
1303 };
1304
1305 ## This list is derived from dbi_sql.h in DBI, from types.c and types.h, and from the PG docs
1306
1307 ## Aids to make the list more readable:
1308 my $GIG = 1073741824;
1309 my $PS = 'precision/scale';
1310 my $LEN = 'length';
1311 my $UN = undef;
1312 my $ti =
1313 [
1314 $names,
1315# name sql_type size pfx/sfx crt n/c/s +-/P/I local min max sub rdx itvl
1316
1317['unknown', SQL_UNKNOWN_TYPE, 0, $UN,$UN, $UN, 1,0,0, $UN,0,0, 'UNKNOWN', $UN,$UN,
1318 SQL_UNKNOWN_TYPE, $UN, $UN, $UN ],
1319['bytea', SQL_VARBINARY, $GIG, q{'},q{'}, $UN, 1,0,3, $UN,0,0, 'BYTEA', $UN,$UN,
1320 SQL_VARBINARY, $UN, $UN, $UN ],
1321['bpchar', SQL_CHAR, $GIG, q{'},q{'}, $LEN, 1,1,3, $UN,0,0, 'CHARACTER', $UN,$UN,
1322 SQL_CHAR, $UN, $UN, $UN ],
1323['numeric', SQL_DECIMAL, 1000, $UN,$UN, $PS, 1,0,2, 0,0,0, 'FLOAT', 0,1000,
1324 SQL_DECIMAL, $UN, $UN, $UN ],
1325['numeric', SQL_NUMERIC, 1000, $UN,$UN, $PS, 1,0,2, 0,0,0, 'FLOAT', 0,1000,
1326 SQL_NUMERIC, $UN, $UN, $UN ],
1327['int4', SQL_INTEGER, 10, $UN,$UN, $UN, 1,0,2, 0,0,0, 'INTEGER', 0,0,
1328 SQL_INTEGER, $UN, $UN, $UN ],
1329['int2', SQL_SMALLINT, 5, $UN,$UN, $UN, 1,0,2, 0,0,0, 'SMALLINT', 0,0,
1330 SQL_SMALLINT, $UN, $UN, $UN ],
1331['float4', SQL_FLOAT, 6, $UN,$UN, $PS, 1,0,2, 0,0,0, 'FLOAT', 0,6,
1332 SQL_FLOAT, $UN, $UN, $UN ],
1333['float8', SQL_REAL, 15, $UN,$UN, $PS, 1,0,2, 0,0,0, 'REAL', 0,15,
1334 SQL_REAL, $UN, $UN, $UN ],
1335['int8', SQL_DOUBLE, 20, $UN,$UN, $UN, 1,0,2, 0,0,0, 'LONGINT', 0,0,
1336 SQL_DOUBLE, $UN, $UN, $UN ],
1337['date', SQL_DATE, 10, q{'},q{'}, $UN, 1,0,2, $UN,0,0, 'DATE', 0,0,
1338 SQL_DATE, $UN, $UN, $UN ],
1339['tinterval',SQL_TIME, 18, q{'},q{'}, $UN, 1,0,2, $UN,0,0, 'TINTERVAL', 0,6,
1340 SQL_TIME, $UN, $UN, $UN ],
1341['timestamp',SQL_TIMESTAMP, 29, q{'},q{'}, $UN, 1,0,2, $UN,0,0, 'TIMESTAMP', 0,6,
1342 SQL_TIMESTAMP, $UN, $UN, $UN ],
1343['text', SQL_VARCHAR, $GIG, q{'},q{'}, $LEN, 1,1,3, $UN,0,0, 'TEXT', $UN,$UN,
1344 SQL_VARCHAR, $UN, $UN, $UN ],
1345['bool', SQL_BOOLEAN, 1, q{'},q{'}, $UN, 1,0,2, $UN,0,0, 'BOOLEAN', $UN,$UN,
1346 SQL_BOOLEAN, $UN, $UN, $UN ],
1347['array', SQL_ARRAY, 1, q{'},q{'}, $UN, 1,0,2, $UN,0,0, 'ARRAY', $UN,$UN,
1348 SQL_ARRAY, $UN, $UN, $UN ],
1349['date', SQL_TYPE_DATE, 10, q{'},q{'}, $UN, 1,0,2, $UN,0,0, 'DATE', 0,0,
1350 SQL_TYPE_DATE, $UN, $UN, $UN ],
1351['time', SQL_TYPE_TIME, 18, q{'},q{'}, $UN, 1,0,2, $UN,0,0, 'TIME', 0,6,
1352 SQL_TYPE_TIME, $UN, $UN, $UN ],
1353['timestamp',SQL_TYPE_TIMESTAMP,29, q{'},q{'}, $UN, 1,0,2, $UN,0,0, 'TIMESTAMP', 0,6,
1354 SQL_TYPE_TIMESTAMP, $UN, $UN, $UN ],
1355['timetz', SQL_TYPE_TIME_WITH_TIMEZONE,
1356 29, q{'},q{'}, $UN, 1,0,2, $UN,0,0, 'TIMETZ', 0,6,
1357 SQL_TYPE_TIME_WITH_TIMEZONE, $UN, $UN, $UN ],
1358['timestamptz',SQL_TYPE_TIMESTAMP_WITH_TIMEZONE,
1359 29, q{'},q{'}, $UN, 1,0,2, $UN,0,0, 'TIMESTAMPTZ',0,6,
1360 SQL_TYPE_TIMESTAMP_WITH_TIMEZONE, $UN, $UN, $UN ],
1361 #
1362 # intentionally omitted: char, all geometric types, internal types
1363 ];
1364 return $ti;
1365 }
1366
1367
1368 # Characters that need to be escaped by quote().
1369112µs my %esc = (
1370 q{'} => '\\047', # '\\' . sprintf("%03o", ord("'")), # ISO SQL 2
1371 '\\' => '\\134', # '\\' . sprintf("%03o", ord("\\")),
1372 );
1373
1374 # Set up lookup for SQL types we don't want to escape.
13751164µs742µs my %no_escape = map { $_ => 1 }
# spent 7µs making 1 call to DBI::SQL_INTEGER # spent 6µs making 1 call to DBI::SQL_DECIMAL # spent 6µs making 1 call to DBI::SQL_DOUBLE # spent 6µs making 1 call to DBI::SQL_FLOAT # spent 6µs making 1 call to DBI::SQL_REAL # spent 6µs making 1 call to DBI::SQL_SMALLINT # spent 5µs making 1 call to DBI::SQL_NUMERIC
1376 DBI::SQL_INTEGER, DBI::SQL_SMALLINT, DBI::SQL_DECIMAL,
1377 DBI::SQL_FLOAT, DBI::SQL_REAL, DBI::SQL_DOUBLE, DBI::SQL_NUMERIC;
1378
1379 sub get_info {
1380
1381 my ($dbh,$type) = @_;
1382
1383 return undef unless defined $type and length $type;
1384
1385 my %type = (
1386
1387## Driver information:
1388
1389 116 => ['SQL_ACTIVE_ENVIRONMENTS', 0 ], ## unlimited
1390 10021 => ['SQL_ASYNC_MODE', 2 ], ## SQL_AM_STATEMENT
1391 120 => ['SQL_BATCH_ROW_COUNT', 2 ], ## SQL_BRC_EXPLICIT
1392 121 => ['SQL_BATCH_SUPPORT', 3 ], ## 12 SELECT_PROC + ROW_COUNT_PROC
1393 2 => ['SQL_DATA_SOURCE_NAME', "dbi:Pg:$dbh->{Name}" ],
1394 3 => ['SQL_DRIVER_HDBC', 0 ], ## not applicable
1395 135 => ['SQL_DRIVER_HDESC', 0 ], ## not applicable
1396 4 => ['SQL_DRIVER_HENV', 0 ], ## not applicable
1397 76 => ['SQL_DRIVER_HLIB', 0 ], ## not applicable
1398 5 => ['SQL_DRIVER_HSTMT', 0 ], ## not applicable
1399 ## Not clear what should go here. Some things suggest 'Pg', others 'Pg.pm'. We'll use DBD::Pg for now
1400 6 => ['SQL_DRIVER_NAME', 'DBD::Pg' ],
1401 77 => ['SQL_DRIVER_ODBC_VERSION', '03.00' ],
1402 7 => ['SQL_DRIVER_VER', 'DBDVERSION' ], ## magic word
1403 144 => ['SQL_DYNAMIC_CURSOR_ATTRIBUTES1', 0 ], ## we can FETCH, but not via methods
1404 145 => ['SQL_DYNAMIC_CURSOR_ATTRIBUTES2', 0 ], ## same as above
1405 84 => ['SQL_FILE_USAGE', 0 ], ## SQL_FILE_NOT_SUPPORTED (this is good)
1406 146 => ['SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES1', 519 ], ## not clear what this refers to in DBD context
1407 147 => ['SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES2', 5209 ], ## see above
1408 81 => ['SQL_GETDATA_EXTENSIONS', 15 ], ## 1+2+4+8
1409 149 => ['SQL_INFO_SCHEMA_VIEWS', 3932149 ], ## not: assert, charset, collat, trans
1410 150 => ['SQL_KEYSET_CURSOR_ATTRIBUTES1', 0 ], ## applies to us?
1411 151 => ['SQL_KEYSET_CURSOR_ATTRIBUTES2', 0 ], ## see above
1412 10022 => ['SQL_MAX_ASYNC_CONCURRENT_STATEMENTS', 0 ], ## unlimited, probably
1413 0 => ['SQL_MAX_DRIVER_CONNECTIONS', 'MAXCONNECTIONS' ], ## magic word
1414 152 => ['SQL_ODBC_INTERFACE_CONFORMANCE', 1 ], ## SQL_OIC_LEVEL_1
1415 10 => ['SQL_ODBC_VER', '03.00.0000' ],
1416 153 => ['SQL_PARAM_ARRAY_ROW_COUNTS', 2 ], ## correct?
1417 154 => ['SQL_PARAM_ARRAY_SELECTS', 3 ], ## PAS_NO_SELECT
1418 11 => ['SQL_ROW_UPDATES', 'N' ],
1419 14 => ['SQL_SEARCH_PATTERN_ESCAPE', '\\' ],
1420 13 => ['SQL_SERVER_NAME', 'CURRENTDB' ], ## magic word
1421 166 => ['SQL_STANDARD_CLI_CONFORMANCE', 2 ], ## ??
1422 167 => ['SQL_STATIC_CURSOR_ATTRIBUTES1', 519 ], ## ??
1423 168 => ['SQL_STATIC_CURSOR_ATTRIBUTES2', 5209 ], ## ??
1424
1425## DBMS Information
1426
1427 16 => ['SQL_DATABASE_NAME', 'CURRENTDB' ], ## magic word
1428 17 => ['SQL_DBMS_NAME', 'PostgreSQL' ],
1429 18 => ['SQL_DBMS_VERSION', 'ODBCVERSION' ], ## magic word
1430
1431## Data source information
1432
1433 20 => ['SQL_ACCESSIBLE_PROCEDURES', 'Y' ], ## is this really true?
1434 19 => ['SQL_ACCESSIBLE_TABLES', 'Y' ], ## is this really true?
1435 82 => ['SQL_BOOKMARK_PERSISTENCE', 0 ],
1436 42 => ['SQL_CATALOG_TERM', '' ], ## empty = catalogs are not supported
1437 10004 => ['SQL_COLLATION_SEQ', 'ENCODING' ], ## magic word
1438 22 => ['SQL_CONCAT_NULL_BEHAVIOR', 0 ], ## SQL_CB_NULL
1439 23 => ['SQL_CURSOR_COMMIT_BEHAVIOR', 1 ], ## SQL_CB_CLOSE
1440 24 => ['SQL_CURSOR_ROLLBACK_BEHAVIOR', 1 ], ## SQL_CB_CLOSE
1441 10001 => ['SQL_CURSOR_SENSITIVITY', 1 ], ## SQL_INSENSITIVE
1442 25 => ['SQL_DATA_SOURCE_READ_ONLY', 'READONLY' ], ## magic word
1443 26 => ['SQL_DEFAULT_TXN_ISOLATION', 'DEFAULTTXN' ], ## magic word (2 or 8)
1444 10002 => ['SQL_DESCRIBE_PARAMETER', 'Y' ],
1445 36 => ['SQL_MULT_RESULT_SETS', 'Y' ],
1446 37 => ['SQL_MULTIPLE_ACTIVE_TXN', 'Y' ],
1447 111 => ['SQL_NEED_LONG_DATA_LEN', 'N' ],
1448 85 => ['SQL_NULL_COLLATION', 0 ], ## SQL_NC_HIGH
1449 40 => ['SQL_PROCEDURE_TERM', 'function' ], ## for now
1450 39 => ['SQL_SCHEMA_TERM', 'schema' ],
1451 44 => ['SQL_SCROLL_OPTIONS', 8 ], ## not really for DBD?
1452 45 => ['SQL_TABLE_TERM', 'table' ],
1453 46 => ['SQL_TXN_CAPABLE', 2 ], ## SQL_TC_ALL
1454 72 => ['SQL_TXN_ISOLATION_OPTION', 10 ], ## 2+8
1455 47 => ['SQL_USER_NAME', $dbh->{CURRENT_USER} ],
1456
1457## Supported SQL
1458
1459 169 => ['SQL_AGGREGATE_FUNCTIONS', 127 ], ## all of 'em
1460 117 => ['SQL_ALTER_DOMAIN', 31 ], ## all but deferred
1461 86 => ['SQL_ALTER_TABLE', 32639 ], ## no collate
1462 114 => ['SQL_CATALOG_LOCATION', 0 ],
1463 10003 => ['SQL_CATALOG_NAME', 'N' ],
1464 41 => ['SQL_CATALOG_NAME_SEPARATOR', '' ],
1465 92 => ['SQL_CATALOG_USAGE', 0 ],
1466 87 => ['SQL_COLUMN_ALIAS', 'Y' ],
1467 74 => ['SQL_CORRELATION_NAME', 2 ], ## SQL_CN_ANY
1468 127 => ['SQL_CREATE_ASSERTION', 0 ],
1469 128 => ['SQL_CREATE_CHARACTER_SET', 0 ],
1470 129 => ['SQL_CREATE_COLLATION', 0 ],
1471 130 => ['SQL_CREATE_DOMAIN', 23 ], ## no collation, no defer
1472 131 => ['SQL_CREATE_SCHEMA', 3 ], ## 1+2 schema + authorize
1473 132 => ['SQL_CREATE_TABLE', 13845 ], ## no collation
1474 133 => ['SQL_CREATE_TRANSLATION', 0 ],
1475 134 => ['SQL_CREATE_VIEW', 9 ], ## local + create?
1476 119 => ['SQL_DATETIME_LITERALS', 65535 ], ## all?
1477 170 => ['SQL_DDL_INDEX', 3 ], ## create + drop
1478 136 => ['SQL_DROP_ASSERTION', 0 ],
1479 137 => ['SQL_DROP_CHARACTER_SET', 0 ],
1480 138 => ['SQL_DROP_COLLATION', 0 ],
1481 139 => ['SQL_DROP_DOMAIN', 7 ],
1482 140 => ['SQL_DROP_SCHEMA', 7 ],
1483 141 => ['SQL_DROP_TABLE', 7 ],
1484 142 => ['SQL_DROP_TRANSLATION', 0 ],
1485 143 => ['SQL_DROP_VIEW', 7 ],
1486 27 => ['SQL_EXPRESSIONS_IN_ORDERBY', 'Y' ],
1487 88 => ['SQL_GROUP_BY', 2 ], ## GROUP_BY_CONTAINS_SELECT
1488 28 => ['SQL_IDENTIFIER_CASE', 2 ], ## SQL_IC_LOWER
1489 29 => ['SQL_IDENTIFIER_QUOTE_CHAR', q{"} ],
1490 148 => ['SQL_INDEX_KEYWORDS', 0 ], ## not needed for Pg
1491 172 => ['SQL_INSERT_STATEMENT', 7 ], ## 1+2+4 = all
1492 73 => ['SQL_INTEGRITY', 'Y' ], ## e.g. ON DELETE CASCADE?
1493 89 => ['SQL_KEYWORDS', 'KEYWORDS' ], ## magic word
1494 113 => ['SQL_LIKE_ESCAPE_CLAUSE', 'Y' ],
1495 75 => ['SQL_NON_NULLABLE_COLUMNS', 1 ], ## NNC_NOT_NULL
1496 115 => ['SQL_OJ_CAPABILITIES', 127 ], ## all
1497 90 => ['SQL_ORDER_BY_COLUMNS_IN_SELECT', 'N' ],
1498 38 => ['SQL_OUTER_JOINS', 'Y' ],
1499 21 => ['SQL_PROCEDURES', 'Y' ],
1500 93 => ['SQL_QUOTED_IDENTIFIER_CASE', 3 ], ## SQL_IC_SENSITIVE
1501 91 => ['SQL_SCHEMA_USAGE', 31 ], ## all
1502 94 => ['SQL_SPECIAL_CHARACTERS', '$' ], ## there are actually many more...
1503 118 => ['SQL_SQL_CONFORMANCE', 4 ], ## SQL92_INTERMEDIATE ??
1504 95 => ['SQL_SUBQUERIES', 31 ], ## all
1505 96 => ['SQL_UNION', 3 ], ## 1+2 = all
1506
1507## SQL limits
1508
1509 112 => ['SQL_MAX_BINARY_LITERAL_LEN', 0 ],
1510 34 => ['SQL_MAX_CATALOG_NAME_LEN', 0 ],
1511 108 => ['SQL_MAX_CHAR_LITERAL_LEN', 0 ],
1512 30 => ['SQL_MAX_COLUMN_NAME_LEN', 'NAMEDATALEN' ], ## magic word
1513 97 => ['SQL_MAX_COLUMNS_IN_GROUP_BY', 0 ],
1514 98 => ['SQL_MAX_COLUMNS_IN_INDEX', 0 ],
1515 99 => ['SQL_MAX_COLUMNS_IN_ORDER_BY', 0 ],
1516 100 => ['SQL_MAX_COLUMNS_IN_SELECT', 0 ],
1517 101 => ['SQL_MAX_COLUMNS_IN_TABLE', 250 ], ## 250-1600 (depends on column types)
1518 31 => ['SQL_MAX_CURSOR_NAME_LEN', 'NAMEDATALEN' ], ## magic word
1519 10005 => ['SQL_MAX_IDENTIFIER_LEN', 'NAMEDATALEN' ], ## magic word
1520 102 => ['SQL_MAX_INDEX_SIZE', 0 ],
1521 102 => ['SQL_MAX_PROCEDURE_NAME_LEN', 'NAMEDATALEN' ], ## magic word
1522 104 => ['SQL_MAX_ROW_SIZE', 0 ], ## actually 1.6 TB, but too big to represent here
1523 103 => ['SQL_MAX_ROW_SIZE_INCLUDES_LONG', 'Y' ],
1524 32 => ['SQL_MAX_SCHEMA_NAME_LEN', 'NAMEDATALEN' ], ## magic word
1525 105 => ['SQL_MAX_STATEMENT_LEN', 0 ],
1526 35 => ['SQL_MAX_TABLE_NAME_LEN', 'NAMEDATALEN' ], ## magic word
1527 106 => ['SQL_MAX_TABLES_IN_SELECT', 0 ],
1528 107 => ['SQL_MAX_USER_NAME_LEN', 'NAMEDATALEN' ], ## magic word
1529
1530## Scalar function information
1531
1532 48 => ['SQL_CONVERT_FUNCTIONS', 2 ], ## CVT_CAST only?
1533 49 => ['SQL_NUMERIC_FUNCTIONS', 16777215 ], ## ?? all but some naming clashes: rand(om), trunc(ate), log10=ln, etc.
1534 50 => ['SQL_STRING_FUNCTIONS', 16280984 ], ## ??
1535 51 => ['SQL_SYSTEM_FUNCTIONS', 0 ], ## ??
1536 109 => ['SQL_TIMEDATE_ADD_INTERVALS', 0 ], ## ?? no explicit timestampadd?
1537 110 => ['SQL_TIMEDATE_DIFF_INTERVALS', 0 ], ## ??
1538 52 => ['SQL_TIMEDATE_FUNCTIONS', 1966083 ],
1539
1540## Conversion information - all but BIT, LONGVARBINARY, and LONGVARCHAR
1541
1542 53 => ['SQL_CONVERT_BIGINT', 1830399 ],
1543 54 => ['SQL_CONVERT_BINARY', 1830399 ],
1544 55 => ['SQL_CONVERT_BIT', 0 ],
1545 56 => ['SQL_CONVERT_CHAR', 1830399 ],
1546 57 => ['SQL_CONVERT_DATE', 1830399 ],
1547 58 => ['SQL_CONVERT_DECIMAL', 1830399 ],
1548 59 => ['SQL_CONVERT_DOUBLE', 1830399 ],
1549 60 => ['SQL_CONVERT_FLOAT', 1830399 ],
1550 61 => ['SQL_CONVERT_INTEGER', 1830399 ],
1551 123 => ['SQL_CONVERT_INTERVAL_DAY_TIME', 1830399 ],
1552 124 => ['SQL_CONVERT_INTERVAL_YEAR_MONTH', 1830399 ],
1553 71 => ['SQL_CONVERT_LONGVARBINARY', 0 ],
1554 62 => ['SQL_CONVERT_LONGVARCHAR', 0 ],
1555 63 => ['SQL_CONVERT_NUMERIC', 1830399 ],
1556 64 => ['SQL_CONVERT_REAL', 1830399 ],
1557 65 => ['SQL_CONVERT_SMALLINT', 1830399 ],
1558 66 => ['SQL_CONVERT_TIME', 1830399 ],
1559 67 => ['SQL_CONVERT_TIMESTAMP', 1830399 ],
1560 68 => ['SQL_CONVERT_TINYINT', 1830399 ],
1561 69 => ['SQL_CONVERT_VARBINARY', 0 ],
1562 70 => ['SQL_CONVERT_VARCHAR', 1830399 ],
1563 122 => ['SQL_CONVERT_WCHAR', 0 ],
1564 125 => ['SQL_CONVERT_WLONGVARCHAR', 0 ],
1565 126 => ['SQL_CONVERT_WVARCHAR', 0 ],
1566
1567 ); ## end of %type
1568
1569 ## Put both numbers and names into a hash
1570 my %t;
1571 for (keys %type) {
1572 $t{$_} = $type{$_}->[1];
1573 $t{$type{$_}->[0]} = $type{$_}->[1];
1574 }
1575
1576 return undef unless exists $t{$type};
1577
1578 my $ans = $t{$type};
1579
1580 if ($ans eq 'NAMEDATALEN') {
1581 return $dbh->selectall_arrayref('SHOW max_identifier_length')->[0][0];
1582 }
1583 elsif ($ans eq 'ODBCVERSION') {
1584 my $version = $dbh->{private_dbdpg}{version};
1585 return '00.00.0000' unless $version =~ /^(\d\d?)(\d\d)(\d\d)$/o;
1586 return sprintf '%02d.%02d.%.2d00', $1,$2,$3;
1587 }
1588 elsif ($ans eq 'DBDVERSION') {
1589 my $simpleversion = $DBD::Pg::VERSION;
1590 $simpleversion =~ s/_/./g;
1591 return sprintf '%02d.%02d.%1d%1d%1d%1d', split (/\./, "$simpleversion.0.0.0.0.0.0");
1592 }
1593 elsif ($ans eq 'MAXCONNECTIONS') {
1594 return $dbh->selectall_arrayref('SHOW max_connections')->[0][0];
1595 }
1596 elsif ($ans eq 'ENCODING') {
1597 return $dbh->selectall_arrayref('SHOW server_encoding')->[0][0];
1598 }
1599 elsif ($ans eq 'KEYWORDS') {
1600 ## http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html
1601 ## Basically, we want ones that are 'reserved' for PostgreSQL but not 'reserved' in SQL:2003
1602 ##
1603 return join ',' => (qw(ANALYSE ANALYZE ASC DEFERRABLE DESC DO FREEZE ILIKE INITIALLY ISNULL LIMIT NOTNULL OFF OFFSET PLACING RETURNING VERBOSE));
1604 }
1605 elsif ($ans eq 'CURRENTDB') {
1606 return $dbh->selectall_arrayref('SELECT pg_catalog.current_database()')->[0][0];
1607 }
1608 elsif ($ans eq 'READONLY') {
1609 my $SQL = q{SELECT CASE WHEN setting = 'on' THEN 'Y' ELSE 'N' END FROM pg_settings WHERE name = 'transaction_read_only'};
1610 my $info = $dbh->selectall_arrayref($SQL);
1611 return defined $info->[0] ? $info->[0][0] : 'N';
1612 }
1613 elsif ($ans eq 'DEFAULTTXN') {
1614 my $SQL = q{SELECT CASE WHEN setting = 'read committed' THEN 2 ELSE 8 END FROM pg_settings WHERE name = 'default_transaction_isolation'};
1615 my $info = $dbh->selectall_arrayref($SQL);
1616 return defined $info->[0] ? $info->[0][0] : 2;
1617 }
1618
1619 return $ans;
1620 } # end of get_info
1621
1622 sub private_attribute_info {
1623 return {
1624 pg_async_status => undef,
1625 pg_bool_tf => undef,
1626 pg_db => undef,
1627 pg_default_port => undef,
1628 pg_enable_utf8 => undef,
1629 pg_errorlevel => undef,
1630 pg_expand_array => undef,
1631 pg_host => undef,
1632 pg_INV_READ => undef,
1633 pg_INV_WRITE => undef,
1634 pg_lib_version => undef,
1635 pg_options => undef,
1636 pg_pass => undef,
1637 pg_pid => undef,
1638 pg_placeholder_dollaronly => undef,
1639 pg_port => undef,
1640 pg_prepare_now => undef,
1641 pg_protocol => undef,
1642 pg_server_prepare => undef,
1643 pg_server_version => undef,
1644 pg_socket => undef,
1645 pg_standard_conforming_strings => undef,
1646 pg_user => undef,
1647 };
1648 }
1649}
1650
1651
1652{
165318µs package DBD::Pg::st;
1654
1655 sub parse_trace_flag {
1656 my ($h, $flag) = @_;
1657 return DBD::Pg->parse_trace_flag($flag);
1658 }
1659
1660 sub bind_param_array {
1661
1662 ## Binds an array of data to a specific placeholder in a statement
1663 ## The DBI version is broken, so we implement a near-copy here
1664
1665 my $sth = shift;
1666 my ($p_id, $value_array, $attr) = @_;
1667
1668 ## Bail if the second arg is not undef or an an arrayref
1669 return $sth->set_err(1, "Value for parameter $p_id must be a scalar or an arrayref, not a ".ref($value_array))
1670 if defined $value_array and ref $value_array and ref $value_array ne 'ARRAY';
1671
1672 ## Bail if the first arg is not a number
1673 return $sth->set_err(1, q{Can't use named placeholders for non-driver supported bind_param_array})
1674 unless DBI::looks_like_number($p_id); # because we rely on execute(@ary) here
1675
1676 ## Store the list of items in the hash (will be undef or an arayref)
1677 $sth->{ParamArrays}{$p_id} = $value_array;
1678
1679 ## If any attribs were passed in, we need to call bind_param
1680 return $sth->bind_param($p_id, '', $attr) if $attr; ## This is the big change so -w does not complain
1681
1682 return 1;
1683 } ## end bind_param_array
1684
1685 sub private_attribute_info {
1686 return {
1687 pg_async => undef,
1688 pg_bound => undef,
1689 pg_current_row => undef,
1690 pg_direct => undef,
1691 pg_numbound => undef,
1692 pg_cmd_status => undef,
1693 pg_oid_status => undef,
1694 pg_placeholder_dollaronly => undef,
1695 pg_prepare_name => undef,
1696 pg_prepare_now => undef,
1697 pg_segments => undef,
1698 pg_server_prepare => undef,
1699 pg_size => undef,
1700 pg_type => undef,
1701 };
1702 }
1703
1704} ## end st section
1705
1706166µs1;
1707
1708__END__
1709
1710=head1 NAME
1711
1712DBD::Pg - PostgreSQL database driver for the DBI module
1713
1714=head1 SYNOPSIS
1715
1716 use DBI;
1717
1718 $dbh = DBI->connect("dbi:Pg:dbname=$dbname", '', '', {AutoCommit => 0});
1719 # The AutoCommit attribute should always be explicitly set
1720
1721 # For some advanced uses you may need PostgreSQL type values:
1722 use DBD::Pg qw(:pg_types);
1723
1724 # For asynchronous calls, import the async constants:
1725 use DBD::Pg qw(:async);
1726
1727 $dbh->do('INSERT INTO mytable(a) VALUES (1)');
1728
1729 $sth = $dbh->prepare('INSERT INTO mytable(a) VALUES (?)');
1730 $sth->execute();
1731
1732=head1 VERSION
1733
1734This documents version 2.16.0 of the DBD::Pg module
1735
1736=head1 DESCRIPTION
1737
1738DBD::Pg is a Perl module that works with the DBI module to provide access to
1739PostgreSQL databases.
1740
1741=head1 MODULE DOCUMENTATION
1742
1743This documentation describes driver specific behavior and restrictions. It is
1744not supposed to be used as the only reference for the user. In any case
1745consult the B<DBI> documentation first!
1746
1747=for html <a href="http://search.cpan.org/~timb/DBI/DBI.pm">Latest DBI docmentation.</a>
1748
1749=head1 THE DBI CLASS
1750
1751=head2 DBI Class Methods
1752
1753=head3 B<connect>
1754
1755This method creates a database handle by connecting to a database, and is the DBI
1756equivalent of the "new" method. To connect to a Postgres database with a minimum of parameters,
1757use the following syntax:
1758
1759 $dbh = DBI->connect("dbi:Pg:dbname=$dbname", '', '', {AutoCommit => 0});
1760
1761This connects to the database named in the C<$dbname> variable on the default port (usually 5432)
1762without any user authentication.
1763
1764The following connect statement shows almost all possible parameters:
1765
1766 $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host;port=$port;options=$options",
1767 $username,
1768 $password,
1769 {AutoCommit => 0, RaiseError => 1, PrintError => 0}
1770 );
1771
1772If a parameter is not given, the connect() method will first look for
1773specific environment variables, and then fall back to hard-coded defaults:
1774
1775 parameter environment variable hard coded default
1776 ------------------------------------------------------
1777 host PGHOST local domain socket
1778 hostaddr PGHOSTADDR local domain socket
1779 port PGPORT 5432
1780 dbname* PGDATABASE current userid
1781 username PGUSER current userid
1782 password PGPASSWORD (none)
1783 options PGOPTIONS (none)
1784 service PGSERVICE (none)
1785 sslmode PGSSLMODE (none)
1786
1787* May also use the aliases C<db> or C<database>
1788
1789If the username and password values passed via C<connect()> are undefined (as opposed
1790to merely being empty strings), DBI will use the environment variables I<DBI_USER>
1791and I<DBI_PASS> if they exist.
1792
1793You can also connect by using a service connection file, which is named
1794F<pg_service.conf>. The location of this file can be controlled by
1795setting the I<PGSYSCONFDIR> environment variable. To use one of the named
1796services within the file, set the name by using either the I<service> parameter
1797or the environment variable I<PGSERVICE>. Note that when connecting this way,
1798only the minimum parameters should be used. For example, to connect to a
1799service named "zephyr", you could use:
1800
1801 $dbh = DBI->connect("dbi:Pg:service=zephyr", '', '');
1802
1803You could also set C<$ENV{PGSERVICE}> to "zephyr" and connect like this:
1804
1805 $dbh = DBI->connect("dbi:Pg:", '', '');
1806
1807The format of the F<pg_service.conf> file is simply a bracketed service
1808name, followed by one parameter per line in the format name=value.
1809For example:
1810
1811 [zephyr]
1812 dbname=winds
1813 user=wisp
1814 password=W$2Hc00YSgP
1815 port=6543
1816
1817There are four valid arguments to the I<sslmode> parameter, which controls
1818whether to use SSL to connect to the database:
1819
1820=over 4
1821
1822=item * disable: SSL connections are never used
1823
1824=item * allow: try non-SSL, then SSL
1825
1826=item * prefer: try SSL, then non-SSL
1827
1828=item * require: connect only with SSL
1829
1830=back
1831
1832You can also connect using sockets in a specific directory. This
1833may be needed if the server you are connecting to has a different
1834default socket directory from the one used to compile DBD::Pg.
1835Use the complete path to the socket directory as the name of the
1836host, like this:
1837
1838 $dbh = DBI->connect('dbi:Pg:dbname=foo;host=/var/tmp/socket',
1839 $username,
1840 $password,
1841 {AutoCommit => 0, RaiseError => 1});
1842
1843The attribute hash can also contain a key named C<dbd_verbose>, which
1844simply calls C<< $dbh->trace('DBD') >> after the handle is created. This attribute
1845is not recommended, as it is clearer to simply explicitly call C<trace> explicitly
1846in your script.
1847
1848=head3 B<connect_cached>
1849
1850 $dbh = DBI->connect_cached("dbi:Pg:dbname=$dbname", $username, $password, \%options);
1851
1852Implemented by DBI, no driver-specific impact.
1853
1854=head3 B<data_sources>
1855
1856 @data_sources = DBI->data_sources('Pg');
1857 @data_sources = $dbh->data_sources();
1858
1859Returns a list of available databases. Unless the environment variable C<DBI_DSN> is set,
1860a connection will be attempted to the database C<template1>. The normal connection
1861environment variables also apply, such as C<PGHOST>, C<PGPORT>, C<DBI_USER>,
1862C<DBI_PASS>, and C<PGSERVICE>.
1863
1864You can also pass in options to add to the connection string For example, to specify
1865an alternate port and host:
1866
1867 @data_sources = DBI->data_sources('Pg', 'port=5824;host=example.com');
1868
1869 or:
1870
1871 @data_sources = $dbh->data_sources('port=5824;host=example.com');
1872
1873
1874=head2 Methods Common To All Handles
1875
1876For all of the methods below, B<$h> can be either a database handle (B<$dbh>)
1877or a statement handle (B<$sth>). Note that I<$dbh> and I<$sth> can be replaced with
1878any variable name you choose: these are just the names most often used. Another
1879common variable used in this documentation is $I<rv>, which stands for "return value".
1880
1881=head3 B<err>
1882
1883 $rv = $h->err;
1884
1885Returns the error code from the last method called. For the connect method it returns
1886C<PQstatus>, which is a number used by I<libpq> (the Postgres connection library). A value of 0
1887indicates no error (CONNECTION_OK), while any other number indicates a failed connection. The
1888only other number commonly seen is 1 (CONNECTION_BAD). See the libpq documentation for the
1889complete list of return codes.
1890
1891In all other non-connect methods C<< $h->err >> returns the C<PQresultStatus> of the current
1892handle. This is a number used by libpq and is one of:
1893
1894 0 Empty query string
1895 1 A command that returns no data successfully completed.
1896 2 A command that returns data sucessfully completed.
1897 3 A COPY OUT command is still in progress.
1898 4 A COPY IN command is still in progress.
1899 5 A bad response was received from the backend.
1900 6 A nonfatal error occurred (a notice or warning message)
1901 7 A fatal error was returned: the last query failed.
1902
1903=head3 B<errstr>
1904
1905 $str = $h->errstr;
1906
1907Returns the last error that was reported by Postgres. This message is affected
1908by the L</pg_errorlevel> setting.
1909
1910=head3 B<state>
1911
1912 $str = $h->state;
1913
1914Returns a five-character "SQLSTATE" code. Success is indicated by a C<00000> code, which
1915gets mapped to an empty string by DBI. A code of C<S8006> indicates a connection failure,
1916usually because the connection to the Postgres server has been lost.
1917
1918While this method can be called as either C<< $sth->state >> or C<< $dbh->state >>, it
1919is usually clearer to always use C<< $dbh->state >>.
1920
1921The list of codes used by PostgreSQL can be found at:
1922L<http://www.postgresql.org/docs/current/static/errcodes-appendix.html>
1923
1924Note that these codes are part of the SQL standard and only a small number
1925of them will be used by PostgreSQL.
1926
1927Common codes:
1928
1929 00000 Successful completion
1930 25P01 No active SQL transaction
1931 25P02 In failed SQL transaction
1932 S8006 Connection failure
1933
1934=head3 B<trace>
1935
1936 $h->trace($trace_settings);
1937 $h->trace($trace_settings, $trace_filename);
1938 $trace_settings = $h->trace;
1939
1940Changes the trace settings on a database or statement handle.
1941The optional second argument specifies a file to write the
1942trace information to. If no filename is given, the information
1943is written to F<STDERR>. Note that tracing can be set globally as
1944well by setting C<< DBI->trace >>, or by using the environment
1945variable I<DBI_TRACE>.
1946
1947The value is either a numeric level or a named flag. For the
1948flags that DBD::Pg uses, see L<parse_trace_flag|/parse_trace_flag and parse_trace_flags>.
1949
1950=head3 B<trace_msg>
1951
1952 $h->trace_msg($message_text);
1953 $h->trace_msg($message_text, $min_level);
1954
1955Writes a message to the current trace output (as set by the L</trace> method). If a second argument
1956is given, the message is only written if the current tracing level is equal to or greater than
1957the C<$min_level>.
1958
1959=head3 B<parse_trace_flag> and B<parse_trace_flags>
1960
1961 $h->trace($h->parse_trace_flags('SQL|pglibpq'));
1962 $h->trace($h->parse_trace_flags('1|pgstart'));
1963
1964 ## Simpler:
1965 $h->trace('SQL|pglibpq');
1966 $h->trace('1|pgstart');
1967
1968 my $value = DBD::Pg->parse_trace_flag('pglibpq');
1969 DBI->trace($value);
1970
1971The parse_trace_flags method is used to convert one or more named
1972flags to a number which can passed to the L</trace> method.
1973DBD::Pg currently supports the DBI-specific flag, C<SQL>,
1974as well as the ones listed below.
1975
1976Flags can be combined by using the parse_trace_flags method,
1977which simply calls C<parse_trace_flag> on each item and
1978combines them.
1979
1980Sometimes you may wish to turn the tracing on before you connect
1981to the database. The second example above shows a way of doing this:
1982the call to C<< DBD::Pg->parse_trace_flags >> provides a number than can
1983be fed to C<< DBI->trace >> before you create a database handle.
1984
1985DBD::Pg supports the following trace flags:
1986
1987=over 4
1988
1989=item SQL
1990
1991Outputs all SQL statements. Note that the output provided will not
1992necessarily be in a form suitable to passing directly to Postgres,
1993as server-side prepared statements are used extensively by DBD::Pg.
1994For maximum portability of output (but with a potential performance
1995hit), use with C<< $dbh->{pg_server_prepare} = 0 >>.
1996
1997=item DBD
1998
1999Turns on all non-DBI flags, in other words, only the ones that are specific
2000to DBD::Pg (all those below which start with the letters 'pg').
2001
2002=item pglibpq
2003
2004Outputs the name of each libpq function (without arguments) immediately
2005before running it. This is a good way to trace the flow of your program
2006at a low level. This information is also output if the trace level
2007is set to 4 or greater.
2008
2009=item pgstart
2010
2011Outputs the name of each internal DBD::Pg function, and other information such as
2012the function arguments or important global variables, as each function starts. This
2013information is also output if the trace level is set to 4 or greater.
2014
2015=item pgend
2016
2017Outputs a simple message at the very end of each internal DBD::Pg function. This is also
2018output if the trace level is set to 4 or greater.
2019
2020=item pgprefix
2021
2022Forces each line of trace output to begin with the string B<C<dbdpg: >>. This helps to
2023differentiate it from the normal DBI trace output.
2024
2025=item pglogin
2026
2027Outputs a message showing the connection string right before a new database connection
2028is attempted, a message when the connection was successful, and a message right after
2029the database has been disconnected. Also output if trace level is 5 or greater.
2030
2031=back
2032
2033=for text See the DBI section on TRACING for more information.
2034
2035=for html See the <a href="http://search.cpan.org/~timb/DBI/DBI.pm#TRACING">DBI section on TRACING</a> for more information.<br />
2036
2037=head3 B<func>
2038
2039DBD::Pg uses the C<func> method to support a variety of functions.
2040Note that the name of the function comes I<last>, after the arguments.
2041
2042=over
2043
2044=item table_attributes
2045
2046 $attrs = $dbh->func($table, 'table_attributes');
2047
2048Use of the tables_attributes function is no longer recommended. Instead,
2049you can use the more portable C<column_info> and C<primary_key> methods
2050to access the same information.
2051
2052The table_attributes method returns, for the given table argument, a
2053reference to an array of hashes, each of which contains the following keys:
2054
2055 NAME attribute name
2056 TYPE attribute type
2057 SIZE attribute size (-1 for variable size)
2058 NULLABLE flag nullable
2059 DEFAULT default value
2060 CONSTRAINT constraint
2061 PRIMARY_KEY flag is_primary_key
2062 REMARKS attribute description
2063
2064=item pg_lo_creat
2065
2066 $lobjId = $dbh->pg_lo_creat($mode);
2067
2068Creates a new large object and returns the object-id. C<$mode> is a bitmask
2069describing read and write access to the new object. This setting is ignored
2070since Postgres version 8.1. For backwards compatibility, however, you should
2071set a valid mode anyway (see L</pg_lo_open> for a list of valid modes).
2072
2073Upon failure it returns C<undef>. This function cannot be used if AutoCommit is enabled.
2074
2075The old way of calling large objects functions is deprecated: $dbh->func(.., 'lo_);
2076
2077=item lo_open
2078
2079 $lobj_fd = $dbh->pg_lo_open($lobjId, $mode);
2080
2081Opens an existing large object and returns an object-descriptor for use in
2082subsequent C<lo_*> calls. C<$mode> is a bitmask describing read and write
2083access to the opened object. It may be one of:
2084
2085 $dbh->{pg_INV_READ}
2086 $dbh->{pg_INV_WRITE}
2087 $dbh->{pg_INV_READ} | $dbh->{pg_INV_WRITE}
2088
2089C<pg_INV_WRITE> and C<pg_INV_WRITE | pg_INV_READ> modes are identical; in
2090both modes, the large object can be read from or written to.
2091Reading from the object will provide the object as written in other committed
2092transactions, along with any writes performed by the current transaction.
2093Objects opened with C<pg_INV_READ> cannot be written to. Reading from this
2094object will provide the stored data at the time of the transaction snapshot
2095which was active when C<lo_write> was called.
2096
2097Returns C<undef> upon failure. Note that 0 is a perfectly correct (and common)
2098object descriptor! This function cannot be used if AutoCommit is enabled.
2099
2100=item lo_write
2101
2102 $nbytes = $dbh->pg_lo_write($lobj_fd, $buffer, $len);
2103
2104Writes C<$len> bytes of c<$buffer> into the large object C<$lobj_fd>. Returns the number
2105of bytes written and C<undef> upon failure. This function cannot be used if AutoCommit is enabled.
2106
2107=item lo_read
2108
2109 $nbytes = $dbh->pg_lo_read($lobj_fd, $buffer, $len);
2110
2111Reads C<$len> bytes into c<$buffer> from large object C<$lobj_fd>. Returns the number of
2112bytes read and C<undef> upon failure. This function cannot be used if AutoCommit is enabled.
2113
2114=item lo_lseek
2115
2116 $loc = $dbh->pg_lo_lseek($lobj_fd, $offset, $whence);
2117
2118Changes the current read or write location on the large object
2119C<$obj_id>. Currently C<$whence> can only be 0 (which is L_SET). Returns the current
2120location and C<undef> upon failure. This function cannot be used if AutoCommit is enabled.
2121
2122=item lo_tell
2123
2124 $loc = $dbh->pg_lo_tell($lobj_fd);
2125
2126Returns the current read or write location on the large object C<$lobj_fd> and C<undef> upon failure.
2127This function cannot be used if AutoCommit is enabled.
2128
2129=item lo_close
2130
2131 $lobj_fd = $dbh->pg_lo_close($lobj_fd);
2132
2133Closes an existing large object. Returns true upon success and false upon failure.
2134This function cannot be used if AutoCommit is enabled.
2135
2136=item lo_unlink
2137
2138 $ret = $dbh->pg_lo_unlink($lobjId);
2139
2140Deletes an existing large object. Returns true upon success and false upon failure.
2141This function cannot be used if AutoCommit is enabled.
2142
2143=item lo_import
2144
2145
2146 $lobjId = $dbh->pg_lo_import($filename);
2147
2148Imports a Unix file as a large object and returns the object id of the new
2149object or C<undef> upon failure.
2150
2151=item lo_export
2152
2153 $ret = $dbh->pg_lo_export($lobjId, $filename);
2154
2155Exports a large object into a Unix file. Returns false upon failure, true otherwise.
2156
2157=item getfd
2158
2159 $fd = $dbh->func('getfd');
2160
2161Deprecated, use L<< $dbh->{pg_socket}|/pg_socket >> instead.
2162
2163=back
2164
2165=head3 B<private_attribute_info>
2166
2167 $hashref = $dbh->private_attribute_info();
2168 $hashref = $sth->private_attribute_info();
2169
2170Returns a hash of all private attributes used by DBD::Pg, for either
2171a database or a statement handle. Currently, all the hash values are undef.
2172
2173=head1 ATTRIBUTES COMMON TO ALL HANDLES
2174
2175=head3 B<InactiveDestroy> (boolean)
2176
2177If set to true, then the L</disconnect> method will not be automatically called when
2178the database handle goes out of scope. This is required if you are forking, and even
2179then you must tread carefully and ensure that either the parent or the child (but not
2180both!) handles all database calls from that point forwards, so that messages from the
2181Postgres backend are only handled by one of the processes. If you don't set things up
2182properly, you will see messages such as "I<server closed the connection unexpectedly>",
2183and "I<message type 0x32 arrived from server while idle>". The best solution is to either
2184have the child process reconnect to the database with a fresh database handle, or to
2185rewrite your application not to use use forking. See the section on L</Asynchronous Queries>
2186for a way to have your script continue to work while the database is processing a request.
2187
2188=head3 B<RaiseError> (boolean, inherited)
2189
2190Forces errors to always raise an exception. Although it defaults to off, it is recommended that this
2191be turned on, as the alternative is to check the return value of every method (prepare, execute, fetch, etc.)
2192manually, which is easy to forget to do.
2193
2194=head3 B<PrintError> (boolean, inherited)
2195
2196Forces database errors to also generate warnings, which can then be filtered with methods such as
2197locally redefining I<$SIG{__WARN__}> or using modules such as C<CGI::Carp>. This attribute is on
2198by default.
2199
2200=head3 B<ShowErrorStatement> (boolean, inherited)
2201
2202Appends information about the current statement to error messages. If placeholder information
2203is available, adds that as well. Defaults to false.
2204
2205=head3 B<Warn> (boolean, inherited)
2206
2207Enables warnings. This is on by default, and should only be turned off in a local block
2208for a short a time only when absolutely needed.
2209
2210=head3 B<Executed> (boolean, read-only)
2211
2212Indicates if a handle has been executed. For database handles, this value is true after the L</do> method has been called, or
2213when one of the child statement handles has issued an L</execute>. Issuing a L</commit> or L</rollback> always resets the
2214attribute to false for database handles. For statement handles, any call to L</execute> or its variants will flip the value to
2215true for the lifetime of the statement handle.
2216
2217=head3 B<TraceLevel> (integer, inherited)
2218
2219Sets the trace level, similar to the L</trace> method. See the sections on
2220L</trace> and L</parse_trace_flag> for more details.
2221
2222=head3 B<Active> (boolean, read-only)
2223
2224Indicates if a handle is active or not. For database handles, this indicates if the database has
2225been disconnected or not. For statement handles, it indicates if all the data has been fetched yet
2226or not. Use of this attribute is not encouraged.
2227
2228=head3 B<Kids> (integer, read-only)
2229
2230Returns the number of child processes created for each handle type. For a driver handle, indicates the number
2231of database handles created. For a database handle, indicates the number of statement handles created. For
2232statement handles, it always returns zero, because statement handles do not create kids.
2233
2234=head3 B<ActiveKids> (integer, read-only)
2235
2236Same as C<Kids>, but only returns those that are active.
2237
2238=head3 B<CachedKids> (hash ref)
2239
2240Returns a hashref of handles. If called on a database handle, returns all statement handles created by use of the
2241C<prepare_cached> method. If called on a driver handle, returns all database handles created by the L</connect_cached>
2242method.
2243
2244=head3 B<ChildHandles> (array ref)
2245
2246Implemented by DBI, no driver-specific impact.
2247
2248=head3 B<PrintWarn> (boolean, inherited)
2249
2250Implemented by DBI, no driver-specific impact.
2251
2252=head3 B<HandleError> (boolean, inherited)
2253
2254Implemented by DBI, no driver-specific impact.
2255
2256=head3 B<HandleSetErr> (code ref, inherited)
2257
2258Implemented by DBI, no driver-specific impact.
2259
2260=head3 B<ErrCount> (unsigned integer)
2261
2262Implemented by DBI, no driver-specific impact.
2263
2264=head3 B<FetchHashKeyName> (string, inherited)
2265
2266Implemented by DBI, no driver-specific impact.
2267
2268=head3 B<ChopBlanks> (boolean, inherited)
2269
2270Supported by DBD::Pg as proposed by DBI. This method is similar to the
2271SQL function C<RTRIM>.
2272
2273=head3 B<Taint> (boolean, inherited)
2274
2275Implemented by DBI, no driver-specific impact.
2276
2277=head3 B<TaintIn> (boolean, inherited)
2278
2279Implemented by DBI, no driver-specific impact.
2280
2281=head3 B<TaintOut> (boolean, inherited)
2282
2283Implemented by DBI, no driver-specific impact.
2284
2285=head3 B<Profile> (inherited)
2286
2287Implemented by DBI, no driver-specific impact.
2288
2289=head3 B<Type> (scalar)
2290
2291Returns C<dr> for a driver handle, C<db> for a database handle, and C<st> for a statement handle.
2292Should be rarely needed.
2293
2294=head3 B<LongReadLen>
2295
2296Not used by DBD::Pg
2297
2298=head3 B<LongTruncOk>
2299
2300Not used by DBD::Pg
2301
2302=head3 B<CompatMode>
2303
2304Not used by DBD::Pg
2305
2306=head1 DBI DATABASE HANDLE OBJECTS
2307
2308=head2 Database Handle Methods
2309
2310=head3 B<selectall_arrayref>
2311
2312 $ary_ref = $dbh->selectall_arrayref($sql);
2313 $ary_ref = $dbh->selectall_arrayref($sql, \%attr);
2314 $ary_ref = $dbh->selectall_arrayref($sql, \%attr, @bind_values);
2315
2316Returns a reference to an array containing the rows returned by preparing and executing the SQL string.
2317See the DBI documentation for full details.
2318
2319=head3 B<selectall_hashref>
2320
2321 $hash_ref = $dbh->selectall_hashref($sql, $key_field);
2322
2323Returns a reference to a hash containing the rows returned by preparing and executing the SQL string.
2324See the DBI documentation for full details.
2325
2326=head3 B<selectcol_arrayref>
2327
2328 $ary_ref = $dbh->selectcol_arrayref($sql, \%attr, @bind_values);
2329
2330Returns a reference to an array containing the first column
2331from each rows returned by preparing and executing the SQL string. It is possible to specify exactly
2332which columns to return. See the DBI documentation for full details.
2333
2334=head3 B<prepare>
2335
2336 $sth = $dbh->prepare($statement, \%attr);
2337
2338WARNING: DBD::Pg now (as of version 1.40) uses true prepared statements by sending them
2339to the backend to be prepared by the Postgres server. Statements
2340that were legal before may no longer work. See below for details.
2341
2342The prepare method prepares a statement for later execution. PostgreSQL supports
2343prepared statements, which enables DBD::Pg to only send the query once, and
2344simply send the arguments for every subsequent call to L</execute>.
2345DBD::Pg can use these server-side prepared statements, or it can
2346just send the entire query to the server each time. The best way
2347is automatically chosen for each query. This will be sufficient for
2348most users: keep reading for a more detailed explanation and some
2349optional flags.
2350
2351Queries that do not begin with the word "SELECT", "INSERT",
2352"UPDATE", or "DELETE" are never sent as server-side prepared statements.
2353
2354Deciding whether or not to use prepared statements depends on many factors,
2355but you can force them to be used or not used by using the
2356L</pg_server_prepare> attribute when calling L</prepare>. Setting this to "0" means to never use
2357prepared statements. Setting L</pg_server_prepare> to "1" means that prepared
2358statements should be used whenever possible. This is the default when connected
2359to Postgres servers version 8.0 or higher. Servers that are version 7.4 get a special
2360default value of "2", because server-side statements were only partially supported
2361in that version. In this case, it only uses server-side prepares if all
2362parameters are specifically bound.
2363
2364The L</pg_server_prepare> attribute can also be set at connection time like so:
2365
2366 $dbh = DBI->connect($DBNAME, $DBUSER, $DBPASS,
2367 { AutoCommit => 0,
2368 RaiseError => 1,
2369 pg_server_prepare => 0,
2370 });
2371
2372or you may set it after your database handle is created:
2373
2374 $dbh->{pg_server_prepare} = 1;
2375
2376To enable it for just one particular statement:
2377
2378 $sth = $dbh->prepare("SELECT id FROM mytable WHERE val = ?",
2379 { pg_server_prepare => 1 });
2380
2381You can even toggle between the two as you go:
2382
2383 $sth->{pg_server_prepare} = 1;
2384 $sth->execute(22);
2385 $sth->{pg_server_prepare} = 0;
2386 $sth->execute(44);
2387 $sth->{pg_server_prepare} = 1;
2388 $sth->execute(66);
2389
2390In the above example, the first execute will use the previously prepared statement.
2391The second execute will not, but will build the query into a single string and send
2392it to the server. The third one will act like the first and only send the arguments.
2393Even if you toggle back and forth, a statement is only prepared once.
2394
2395Using prepared statements is in theory quite a bit faster: not only does the
2396PostgreSQL backend only have to prepare the query only once, but DBD::Pg no
2397longer has to worry about quoting each value before sending it to the server.
2398
2399However, there are some drawbacks. The server cannot always choose the ideal
2400parse plan because it will not know the arguments before hand. But for most
2401situations in which you will be executing similar data many times, the default
2402plan will probably work out well. Programs such as PgBouncer which cache connections
2403at a low level should not use prepared statements via DBD::Pg, or must take
2404extra care in the application to account for the fact that prepared statements
2405are not shared across database connections. Further discussion on this subject is beyond
2406the scope of this documentation: please consult the pgsql-performance mailing
2407list, L<http://archives.postgresql.org/pgsql-performance/>
2408
2409Only certain commands will be sent to a server-side prepare: currently these
2410include C<SELECT>, C<INSERT>, C<UPDATE>, and C<DELETE>. DBD::Pg uses a simple
2411naming scheme for the prepared statements themselves: B<dbdpg_XY_Z>, where B<Y> is the current
2412PID, B<X> is either 'p' or 'n' (depending on if the PID is a positive or negative
2413number), and B<Z> is a number that starts at 1 and increases each time a new statement
2414is prepared. This number is tracked at the database handle level, so multiple
2415statement handles will not collide.
2416
2417You cannot send more than one command at a time in the same prepare command
2418(by separating them with semi-colons) when using server-side prepares.
2419
2420The actual C<PREPARE> is usually not performed until the first execute is called, due
2421to the fact that information on the data types (provided by L</bind_param>) may
2422be provided after the prepare but before the execute.
2423
2424A server-side prepare may happen before the first L</execute>, but only if the server can
2425handle the server-side prepare, and the statement contains no placeholders. It will
2426also be prepared if the L</pg_prepare_now> attribute is passed in and set to a true
2427value. Similarly, the L</pg_prepare_now> attribute can be set to 0 to ensure that
2428the statement is B<not> prepared immediately, although the cases in which you would
2429want this are very rare. Finally, you can set the default behavior of all prepare
2430statements by setting the L</pg_prepare_now> attribute on the database handle:
2431
2432 $dbh->{pg_prepare_now} = 1;
2433
2434The following two examples will be prepared right away:
2435
2436 $sth->prepare("SELECT 123"); ## no placeholders
2437
2438 $sth->prepare("SELECT 123, ?", {pg_prepare_now => 1});
2439
2440The following two examples will NOT be prepared right away:
2441
2442 $sth->prepare("SELECT 123, ?"); ## has a placeholder
2443
2444 $sth->prepare("SELECT 123", {pg_prepare_now => 0});
2445
2446There are times when you may want to prepare a statement yourself. To do this,
2447simply send the C<PREPARE> statement directly to the server (e.g. with
2448the L</do> method). Create a statement handle and set the prepared name via
2449the L</pg_prepare_name> attribute. The statement handle can be created with a dummy
2450statement, as it will not be executed. However, it should have the same
2451number of placeholders as your prepared statement. Example:
2452
2453 $dbh->do('PREPARE mystat AS SELECT COUNT(*) FROM pg_class WHERE reltuples < ?');
2454 $sth = $dbh->prepare('SELECT ?');
2455 $sth->bind_param(1, 1, SQL_INTEGER);
2456 $sth->{pg_prepare_name} = 'mystat';
2457 $sth->execute(123);
2458
2459The above will run the equivalent of this query on the backend:
2460
2461 EXECUTE mystat(123);
2462
2463which is the equivalent of:
2464
2465 SELECT COUNT(*) FROM pg_class WHERE reltuples < 123;
2466
2467You can force DBD::Pg to send your query directly to the server by adding
2468the L</pg_direct> attribute to your prepare call. This is not recommended,
2469but is added just in case you need it.
2470
2471=head4 B<Placeholders>
2472
2473There are three types of placeholders that can be used in DBD::Pg. The first is
2474the "question mark" type, in which each placeholder is represented by a single
2475question mark character. This is the method recommended by the DBI specs and is the most
2476portable. Each question mark is internally replaced by a "dollar sign number" in the order
2477in which they appear in the query (important when using L</bind_param>).
2478
2479The method second type of placeholder is "dollar sign numbers". This is the method
2480that Postgres uses internally and is overall probably the best method to use
2481if you do not need compatibility with other database systems. DBD::Pg, like
2482PostgreSQL, allows the same number to be used more than once in the query.
2483Numbers must start with "1" and increment by one value (but can appear in any order
2484within the query). If the same number appears more than once in a query, it is treated as a
2485single parameter and all instances are replaced at once. Examples:
2486
2487Not legal:
2488
2489 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $2'; # Does not start with 1
2490
2491 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $3'; # Missing 2
2492
2493Legal:
2494
2495 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $1';
2496
2497 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $2';
2498
2499 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $2 AND $1'; # legal but confusing
2500
2501 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $2 AND reltuples > $1';
2502
2503 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $1 AND reltuples > $1';
2504
2505In the final statement above, DBI thinks there is only one placeholder, so this
2506statement will replace both placeholders:
2507
2508 $sth->bind_param(1, 2045);
2509
2510While a simple execute with no bind_param calls requires only a single argument as well:
2511
2512 $sth->execute(2045);
2513
2514The final placeholder type is "named parameters" in the format ":foo". While this
2515syntax is supported by DBD::Pg, its use is discouraged in favor of
2516dollar-sign numbers.
2517
2518The different types of placeholders cannot be mixed within a statement, but you may
2519use different ones for each statement handle you have. This is confusing at best, so
2520stick to one style within your program.
2521
2522If your queries use operators that contain question marks (e.g. some of the native
2523Postgres geometric operators) or array slices (e.g. C<data[100:300]>), you can tell
2524DBD::Pg to ignore any non-dollar sign placeholders by setting the
2525L</pg_placeholder_dollaronly> attribute at either the database handle or the statement
2526handle level. Examples:
2527
2528 $dbh->{pg_placeholder_dollaronly} = 1;
2529 $sth = $dbh->prepare(q{SELECT * FROM mytable WHERE lseg1 ?# lseg2 AND name = $1});
2530 $sth->execute('segname');
2531
2532Alternatively, you can set it at prepare time:
2533
2534 $sth = $dbh->prepare(q{SELECT * FROM mytable WHERE lseg1 ?-| lseg2 AND name = $1},
2535 {pg_placeholder_dollaronly = 1});
2536 $sth->execute('segname');
2537
2538=head3 B<prepare_cached>
2539
2540 $sth = $dbh->prepare_cached($statement, \%attr);
2541
2542Implemented by DBI, no driver-specific impact. This method is most useful
2543when using a server that supports server-side prepares, and you have asked
2544the prepare to happen immediately via the L</pg_prepare_now> attribute.
2545
2546=head3 B<do>
2547
2548 $rv = $dbh->do($statement);
2549 $rv = $dbh->do($statement, \%attr);
2550 $rv = $dbh->do($statement, \%attr, @bind_values);
2551
2552Prepare and execute a single statement. Returns the number of rows affected if the
2553query was successful, returns undef if an error occurred, and returns -1 if the
2554number of rows is unknown or not available. Note that this method will return B<0E0> instead
2555of 0 for 'no rows were affected', in order to always return a true value if no error occurred.
2556
2557If neither C<\%attr> nor C<@bind_values> is given, the query will be sent directly
2558to the server without the overhead of internally creating a statement handle and
2559running prepare and execute, for a measurable speed increase.
2560
2561Note that an empty statement (a string with no length) will not be passed to
2562the server; if you want a simple test, use "SELECT 123" or the L</ping> method.
2563
2564=head3 B<last_insert_id>
2565
2566 $rv = $dbh->last_insert_id(undef, $schema, $table, undef);
2567 $rv = $dbh->last_insert_id(undef, $schema, $table, undef, {sequence => $seqname});
2568
2569Attempts to return the id of the last value to be inserted into a table.
2570You can either provide a sequence name (preferred) or provide a table
2571name with optional schema, and DBD::Pg will attempt to find the sequence itself.
2572The current value of the sequence is returned by a call to the C<CURRVAL()>
2573PostgreSQL function. This will fail if the sequence has not yet been used in the
2574current database connection.
2575
2576If you do not know the name of the sequence, you can provide a table name and
2577DBD::Pg will attempt to return the correct value. To do this, there must be at
2578least one column in the table with a C<NOT NULL> constraint, that has a unique
2579constraint, and which uses a sequence as a default value. If more than one column
2580meets these conditions, the primary key will be used. This involves some
2581looking up of things in the system table, so DBD::Pg will cache the sequence
2582name for subsequent calls. If you need to disable this caching for some reason,
2583(such as the sequence name changing), you can control it by adding C<< pg_cache => 0 >>
2584to the final (hashref) argument for last_insert_id.
2585
2586Please keep in mind that this method is far from foolproof, so make your
2587script use it properly. Specifically, make sure that it is called
2588immediately after the insert, and that the insert does not add a value
2589to the column that is using the sequence as a default value. However, because
2590we are using sequences, you can be sure that the value you got back has not
2591been used by any other process.
2592
2593Some examples:
2594
2595 $dbh->do('CREATE SEQUENCE lii_seq START 1');
2596 $dbh->do(q{CREATE TABLE lii (
2597 foobar INTEGER NOT NULL UNIQUE DEFAULT nextval('lii_seq'),
2598 baz VARCHAR)});
2599 $SQL = 'INSERT INTO lii(baz) VALUES (?)';
2600 $sth = $dbh->prepare($SQL);
2601 for (qw(uno dos tres cuatro)) {
2602 $sth->execute($_);
2603 my $newid = $dbh->last_insert_id(undef,undef,undef,undef,{sequence=>'lii_seq'});
2604 print "Last insert id was $newid\n";
2605 }
2606
2607If you did not want to worry about the sequence name:
2608
2609 $dbh->do('CREATE TABLE lii2 (
2610 foobar SERIAL UNIQUE,
2611 baz VARCHAR)');
2612 $SQL = 'INSERT INTO lii2(baz) VALUES (?)';
2613 $sth = $dbh->prepare($SQL);
2614 for (qw(uno dos tres cuatro)) {
2615 $sth->execute($_);
2616 my $newid = $dbh->last_insert_id(undef,undef,"lii2",undef);
2617 print "Last insert id was $newid\n";
2618 }
2619
2620=head3 B<commit>
2621
2622 $rv = $dbh->commit;
2623
2624Issues a COMMIT to the server, indicating that the current transaction is finished and that
2625all changes made will be visible to other processes. If AutoCommit is enabled, then
2626a warning is given and no COMMIT is issued. Returns true on success, false on error.
2627See also the the section on L</Transactions>.
2628
2629=head3 B<rollback>
2630
2631 $rv = $dbh->rollback;
2632
2633Issues a ROLLBACK to the server, which discards any changes made in the current transaction. If AutoCommit
2634is enabled, then a warning is given and no ROLLBACK is issued. Returns true on success, and
2635false on error. See also the the section on L</Transactions>.
2636
2637=head3 B<begin_work>
2638
2639This method turns on transactions until the next call to L</commit> or L</rollback>, if L</AutoCommit> is
2640currently enabled. If it is not enabled, calling begin_work will issue an error. Note that the
2641transaction will not actually begin until the first statement after begin_work is called.
2642Example:
2643
2644 $dbh->{AutoCommit} = 1;
2645 $dbh->do('INSERT INTO foo VALUES (123)'); ## Changes committed immediately
2646 $dbh->begin_work();
2647 ## Not in a transaction yet, but AutoCommit is set to 0
2648
2649 $dbh->do("INSERT INTO foo VALUES (345)");
2650 ## DBD::PG actually issues two statements here:
2651 ## BEGIN;
2652 ## INSERT INTO foo VALUES (345)
2653 ## We are now in a transaction
2654
2655 $dbh->commit();
2656 ## AutoCommit is now set to 1 again
2657
2658=head3 B<disconnect>
2659
2660 $rv = $dbh->disconnect;
2661
2662Disconnects from the Postgres database. Any uncommitted changes will be rolled back upon disconnection. It's
2663good policy to always explicitly call commit or rollback at some point before disconnecting, rather than
2664relying on the default rollback behavior.
2665
2666This method may give warnings about "disconnect invalidates X active statement handle(s)". This means that
2667you called C<< $sth->execute() >> but did not finish fetching all the rows from them. To avoid seeing this
2668warning, either fetch all the rows or call C<< $sth->finish() >> for each executed statement handle.
2669
2670If the script exits before disconnect is called (or, more precisely, if the database handle is no longer
2671referenced by anything), then the database handle's DESTROY method will call the rollback() and disconnect()
2672methods automatically. It is best to explicitly disconnect rather than rely on this behavior.
2673
2674=head3 B<quote>
2675
2676 $rv = $dbh->quote($value, $data_type);
2677
2678This module implements its own C<quote> method. For simple string types, both backslashes
2679and single quotes are doubled. You may also quote arrayrefs and receive a string
2680suitable for passing into Postgres array columns.
2681
2682If the value contains backslashes, and the server is version 8.1 or higher,
2683then the escaped string syntax will be used (which places a capital E before
2684the first single quote). This syntax is always used when quoting bytea values
2685on servers 8.1 and higher.
2686
2687The C<data_type> argument is optional and should be one of the type constants
2688exported by DBD::Pg (such as PG_BYTEA). In addition to string, bytea, char, bool,
2689and other standard types, the following geometric types are supported: point, line,
2690lseg, box, path, polygon, and circle (PG_POINT, PG_LINE, PG_LSEG, PG_BOX,
2691PG_PATH, PG_POLYGON, and PG_CIRCLE respectively). To quote a Postgres-specific
2692data type, you must use a 'hashref' argument like so:
2693
2694 my $quotedval = $dbh->quote($value, { pg_type => PG_VARCHAR });
2695
2696B<NOTE:> The undocumented (and invalid) support for the C<SQL_BINARY> data
2697type is officially deprecated. Use C<PG_BYTEA> with C<bind_param()> instead:
2698
2699 $rv = $sth->bind_param($param_num, $bind_value,
2700 { pg_type => PG_BYTEA });
2701
2702=head3 B<quote_identifier>
2703
2704 $string = $dbh->quote_identifier( $name );
2705 $string = $dbh->quote_identifier( undef, $schema, $table);
2706
2707Returns a quoted version of the supplied string, which is commonly a schema,
2708table, or column name. The three argument form will return the schema and
2709the table together, separated by a dot. Examples:
2710
2711 print $dbh->quote_identifier('grapefruit'); ## Prints: "grapefruit"
2712
2713 print $dbh->quote_identifier('juicy fruit'); ## Prints: "juicy fruit"
2714
2715 print $dbh->quote_identifier(undef, 'public', 'pg_proc');
2716 ## Prints: "public"."pg_proc"
2717
2718=head3 B<pg_notifies>
2719
2720 $ret = $dbh->pg_notifies;
2721
2722Looks for any asynchronous notifications received and returns either C<undef>
2723or a reference to a three-element array consisting of an event name, the PID
2724of the backend that sent the NOTIFY command, and the optional payload string.
2725Note that this does not check if the connection to the database is still valid first -
2726for that, use the c<ping> method. You may need to commit if not in autocommit mode -
2727new notices will not be picked up while in the middle of a transaction. An example:
2728
2729 $dbh->do("LISTEN abc");
2730 $dbh->do("LISTEN def");
2731
2732 ## Hang around until we get the message we want
2733 LISTENLOOP: {
2734 while (my $notify = $dbh->pg_notifies) {
2735 my ($name, $pid, $payload) = @$notify;
2736 print qq{I received notice "$name" from PID $pid, payload was "$payload"\n};
2737 ## Do something based on the notice received
2738 }
2739 $dbh->ping() or die qq{Ping failed!};
2740 $dbh->commit();
2741 sleep(5);
2742 redo;
2743 }
2744
2745Payloads will always be an empty string unless you are connecting to a Postgres
2746server version 8.5 or higher.
2747
2748=head3 B<ping>
2749
2750 $rv = $dbh->ping;
2751
2752This C<ping> method is used to check the validity of a database handle. The value returned is
2753either 0, indicating that the connection is no longer valid, or a positive integer, indicating
2754the following:
2755
2756 Value Meaning
2757 --------------------------------------------------
2758 1 Database is idle (not in a transaction)
2759 2 Database is active, there is a command in progress (usually seen after a COPY command)
2760 3 Database is idle within a transaction
2761 4 Database is idle, within a failed transaction
2762
2763Additional information on why a handle is not valid can be obtained by using the
2764L</pg_ping> method.
2765
2766=head3 B<pg_ping>
2767
2768 $rv = $dbh->pg_ping;
2769
2770This is a DBD::Pg-specific extension to the L</ping> method. This will check the
2771validity of a database handle in exactly the same way as C<ping>, but instead of
2772returning a 0 for an invalid connection, it will return a negative number. So in
2773addition to returning the positive numbers documented for C<ping>, it may also
2774return the following:
2775
2776 Value Meaning
2777 --------------------------------------------------
2778 -1 There is no connection to the database at all (e.g. after C<disconnect>)
2779 -2 An unknown transaction status was returned (e.g. after forking)
2780 -3 The handle exists, but no data was returned from a test query.
2781
2782In practice, you should only ever see -1 and -2.
2783
2784=head3 B<get_info>
2785
2786 $value = $dbh->get_info($info_type);
2787
2788Supports a very large set (> 250) of the information types, including the minimum
2789recommended by DBI.
2790
2791=head3 B<table_info>
2792
2793 $sth = $dbh->table_info(undef, $schema, $table, $type);
2794
2795Returns all tables and views visible to the current user.
2796The schema and table arguments will do a C<LIKE> search if a percent sign (C<%>) or an
2797underscore (C<_>) is detected in the argument. The C<$type> argument accepts a value of either
2798"TABLE" or "VIEW" (using both is the default action). Note that a statement handle is returned,
2799and not a direct list of tables. See the examples below for ways to handle this.
2800
2801The following fields are returned:
2802
2803B<TABLE_CAT>: Always NULL, as Postgres does not have the concept of catalogs.
2804
2805B<TABLE_SCHEM>: The name of the schema that the table or view is in.
2806
2807B<TABLE_NAME>: The name of the table or view.
2808
2809B<TABLE_TYPE>: The type of object returned. Will be one of "TABLE", "VIEW",
2810or "SYSTEM TABLE".
2811
2812The TABLE_SCHEM and TABLE_NAME will be quoted via C<quote_ident()>.
2813
2814Two additional fields specific to DBD::Pg are returned:
2815
2816B<pg_schema>: the unquoted name of the schema
2817
2818B<pg_table>: the unquoted name of the table
2819
2820If your database supports tablespaces (version 8.0 or greater), two additional
2821DBD::Pg specific fields are returned:
2822
2823B<pg_tablespace_name>: the name of the tablespace the table is in
2824
2825B<pg_tablespace_location>: the location of the tablespace the table is in
2826
2827Tables that have not been assigned to a particular tablespace (or views)
2828will return NULL (C<undef>) for both of the above field.
2829
2830Rows are returned alphabetically, with all tables first, and then all views.
2831
2832Examples of use:
2833
2834 ## Display all tables and views in the public schema:
2835 $sth = $dbh->table_info('', 'public', undef, undef);
2836 for my $rel (@{$sth->fetchall_arrayref({})}) {
2837 print "$rel->{TABLE_TYPE} name is $rel->{TABLE_NAME}\n";
2838 }
2839
2840
2841 # Display the schema of all tables named 'foo':
2842 $sth = $dbh->table_info('', undef, 'foo', 'TABLE');
2843 for my $rel (@{$sth->fetchall_arrayref({})}) {
2844 print "Table name is $rel->{TABLE_SCHEM}.$rel->{TABLE_NAME}\n";
2845 }
2846
2847=head3 B<column_info>
2848
2849 $sth = $dbh->column_info( undef, $schema, $table, $column );
2850
2851Supported by this driver as proposed by DBI with the follow exceptions.
2852These fields are currently always returned with NULL (C<undef>) values:
2853
2854 TABLE_CAT
2855 BUFFER_LENGTH
2856 DECIMAL_DIGITS
2857 NUM_PREC_RADIX
2858 SQL_DATA_TYPE
2859 SQL_DATETIME_SUB
2860 CHAR_OCTET_LENGTH
2861
2862Also, six additional non-standard fields are returned:
2863
2864B<pg_type>: data type with additional info i.e. "character varying(20)"
2865
2866B<pg_constraint>: holds column constraint definition
2867
2868B<pg_schema>: the unquoted name of the schema
2869
2870B<pg_table>: the unquoted name of the table
2871
2872B<pg_column>: the unquoted name of the column
2873
2874B<pg_enum_values>: an array reference of allowed values for an enum column
2875
2876Note that the TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME fields all return
2877output wrapped in quote_ident(). If you need the unquoted version, use
2878the pg_ fields above.
2879
2880=head3 B<primary_key_info>
2881
2882 $sth = $dbh->primary_key_info( undef, $schema, $table, \%attr );
2883
2884Supported by this driver as proposed by DBI. There are no search patterns allowed, but leaving the
2885$schema argument blank will cause the first table found in the schema
2886search path to be used. An additional field, "DATA_TYPE", is returned and
2887shows the data type for each of the arguments in the "COLUMN_NAME" field.
2888
2889This method will also return tablespace information for servers that support
2890tablespaces. See the L</table_info> entry for more information.
2891
2892The five additional custom fields returned are:
2893
2894B<pg_tablespace_name>: name of the tablespace, if any
2895
2896B<pg_tablespace_location>: location of the tablespace
2897
2898B<pg_schema>: the unquoted name of the schema
2899
2900B<pg_table>: the unquoted name of the table
2901
2902B<pg_column>: the unquoted name of the column
2903
2904In addition to the standard format of returning one row for each column
2905found for the primary key, you can pass the C<pg_onerow> attribute to force
2906a single row to be used. If the primary key has multiple columns, the
2907"KEY_SEQ", "COLUMN_NAME", and "DATA_TYPE" fields will return a comma-delimited
2908string. If the C<pg_onerow> attribute is set to "2", the fields will be
2909returned as an arrayref, which can be useful when multiple columns are
2910involved:
2911
2912 $sth = $dbh->primary_key_info('', '', 'dbd_pg_test', {pg_onerow => 2});
2913 if (defined $sth) {
2914 my $pk = $sth->fetchall_arrayref()->[0];
2915 print "Table $pk->[2] has a primary key on these columns:\n";
2916 for (my $x=0; defined $pk->[3][$x]; $x++) {
2917 print "Column: $pk->[3][$x] (data type: $pk->[6][$x])\n";
2918 }
2919 }
2920
2921=head3 B<primary_key>
2922
2923 @key_column_names = $dbh->primary_key(undef, $schema, $table);
2924
2925Simple interface to the L</primary_key_info> method. Returns a list of the column names that
2926comprise the primary key of the specified table. The list is in primary key column sequence
2927order. If there is no primary key then an empty list is returned.
2928
2929=head3 B<foreign_key_info>
2930
2931 $sth = $dbh->foreign_key_info( $pk_catalog, $pk_schema, $pk_table,
2932 $fk_catalog, $fk_schema, $fk_table );
2933
2934Supported by this driver as proposed by DBI, using the SQL/CLI variant.
2935There are no search patterns allowed, but leaving the C<$schema> argument
2936blank will cause the first table found in the schema search path to be
2937used. Two additional fields, "UK_DATA_TYPE" and "FK_DATA_TYPE", are returned
2938to show the data type for the unique and foreign key columns. Foreign
2939keys that have no named constraint (where the referenced column only has
2940an unique index) will return C<undef> for the "UK_NAME" field.
2941
2942=head3 B<statistics_info>
2943
2944 $sth = $dbh->statistics_info( undef, $schema, $table, $unique_only, $quick );
2945
2946Returns a statement handle that can be fetched from to give statistics information
2947on a specific table and its indexes. The C<$table> argument is mandatory. The
2948C<$schema> argument is optional but recommended. The C<$unique_only> argument, if true,
2949causes only information about unique indexes to be returned. The C<$quick> argument is
2950not used by DBD::Pg. For information on the format of the rows returned, please see the DBI
2951documentation.
2952
2953=for html <a href="http://search.cpan.org/~timb/DBI/DBI.pm#statistics_info">DBI section on statistics_info</a>
2954
2955=head3 B<tables>
2956
2957 @names = $dbh->tables( undef, $schema, $table, $type, \%attr );
2958
2959Supported by this driver as proposed by DBI. This method returns all tables
2960and/or views which are visible to the current user: see L</table_info>
2961for more information about the arguments. The name of the schema appears
2962before the table or view name. This can be turned off by adding in the
2963C<pg_noprefix> attribute:
2964
2965 my @tables = $dbh->tables( '', '', 'dbd_pg_test', '', {pg_noprefix => 1} );
2966
2967=head3 B<type_info_all>
2968
2969 $type_info_all = $dbh->type_info_all;
2970
2971Supported by this driver as proposed by DBI. Information is only provided for
2972SQL datatypes and for frequently used datatypes. The mapping between the
2973PostgreSQL typename and the SQL92 datatype (if possible) has been done
2974according to the following table:
2975
2976 +---------------+------------------------------------+
2977 | typname | SQL92 |
2978 |---------------+------------------------------------|
2979 | bool | BOOL |
2980 | text | / |
2981 | bpchar | CHAR(n) |
2982 | varchar | VARCHAR(n) |
2983 | int2 | SMALLINT |
2984 | int4 | INT |
2985 | int8 | / |
2986 | money | / |
2987 | float4 | FLOAT(p) p<7=float4, p<16=float8 |
2988 | float8 | REAL |
2989 | abstime | / |
2990 | reltime | / |
2991 | tinterval | / |
2992 | date | / |
2993 | time | / |
2994 | datetime | / |
2995 | timespan | TINTERVAL |
2996 | timestamp | TIMESTAMP |
2997 +---------------+------------------------------------+
2998
2999=head3 B<type_info>
3000
3001 @type_info = $dbh->type_info($data_type);
3002
3003Returns a list of hash references holding information about one or more variants of $data_type.
3004See the DBI documentation for more details.
3005
3006=head3 B<pg_server_trace>
3007
3008 $dbh->pg_server_trace($filehandle);
3009
3010Writes debugging information from the PostgreSQL backend to a file. This is
3011not related to the DBI L</trace> method and you should not use this method unless
3012you know what you are doing. If you do enable this, be aware that the file
3013will grow very large, very quick. To stop logging to the file, use the
3014L</pg_server_untrace> method. The first argument must be a file handle, not
3015a filename. Example:
3016
3017 my $pid = $dbh->{pg_pid};
3018 my $file = "pgbackend.$pid.debug.log";
3019 open(my $fh, ">$file") or die qq{Could not open "$file": $!\n};
3020 $dbh->pg_server_trace($fh);
3021 ## Run code you want to trace here
3022 $dbh->pg_server_untrace;
3023 close($fh);
3024
3025=head3 B<pg_server_untrace>
3026
3027 $dbh->pg_server_untrace;
3028
3029Stop server logging to a previously opened file.
3030
3031=head3 B<selectrow_array>
3032
3033 @row_ary = $dbh->selectrow_array($sql);
3034 @row_ary = $dbh->selectrow_array($sql, \%attr);
3035 @row_ary = $dbh->selectrow_array($sql, \%attr, @bind_values);
3036
3037Returns an array of row information after preparing and executing the provided SQL string. The rows are returned
3038by calling L</fetchrow_array>. The string can also be a statement handle generated by a previous prepare. Note that
3039only the first row of data is returned. If called in a scalar context, only the first column of the first row is
3040returned. Because this is not portable, it is not recommended that you use this method in that way.
3041
3042=head3 B<selectrow_arrayref>
3043
3044 $ary_ref = $dbh->selectrow_arrayref($statement);
3045 $ary_ref = $dbh->selectrow_arrayref($statement, \%attr);
3046 $ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @bind_values);
3047
3048Exactly the same as L</selectrow_array>, except that it returns a reference to an array, by internal use of
3049the L</fetchrow_arrayref> method.
3050
3051=head3 B<selectrow_hashref>
3052
3053 $hash_ref = $dbh->selectrow_hashref($sql);
3054 $hash_ref = $dbh->selectrow_hashref($sql, \%attr);
3055 $hash_ref = $dbh->selectrow_hashref($sql, \%attr, @bind_values);
3056
3057Exactly the same as L</selectrow_array>, except that it returns a reference to an hash, by internal use of
3058the L</fetchrow_hashref> method.
3059
3060=head3 B<clone>
3061
3062 $other_dbh = $dbh->clone();
3063
3064Creates a copy of the database handle by connecting with the same parameters as the original
3065handle, then trying to merge the attributes. See the DBI documentation for complete usage.
3066
3067=head2 Database Handle Attributes
3068
3069=head3 B<AutoCommit> (boolean)
3070
3071Supported by DBD::Pg as proposed by DBI. According to the classification of
3072DBI, PostgreSQL is a database in which a transaction must be explicitly
3073started. Without starting a transaction, every change to the database becomes
3074immediately permanent. The default of AutoCommit is on, but this may change
3075in the future, so it is highly recommended that you explicitly set it when
3076calling L</connect>. For details see the notes about L</Transactions>
3077elsewhere in this document.
3078
3079=head3 B<pg_bool_tf> (boolean)
3080
3081DBD::Pg specific attribute. If true, boolean values will be returned
3082as the characters 't' and 'f' instead of '1' and '0'.
3083
3084=head3 B<ReadOnly> (boolean)
3085
3086$dbh->{ReadOnly} = 1;
3087
3088Specifies if the current database connection should be in read-only mode or not.
3089In this mode, changes that change the database are not allowed and will throw
3090an error. Note: this method will B<not> work if L</AutoCommit> is true. The
3091read-only effect is accomplished by sending a S<SET TRANSACTION READ ONLY> after
3092every begin. For more details, please see:
3093
3094http://www.postgresql.org/docs/current/interactive/sql-set-transaction.html
3095
3096Please not that this method is not foolproof: there are still ways to update the
3097database. Consider this a safety net to catch applications that should not be
3098issuing commands such as INSERT, UPDATE, or DELETE.
3099
3100This method method requires DBI version 1.55 or better.
3101
3102=head3 B<pg_server_prepare> (integer)
3103
3104DBD::Pg specific attribute. Indicates if DBD::Pg should attempt to use server-side
3105prepared statements. The default value, 1, indicates that prepared statements should
3106be used whenever possible. See the section on the L</prepare> method for more information.
3107
3108=head3 B<pg_placeholder_dollaronly> (boolean)
3109
3110DBD::Pg specific attribute. Defaults to false. When true, question marks inside of statements
3111are not treated as L<placeholders|/Placeholders>. Useful for statements that contain unquoted question
3112marks, such as geometric operators.
3113
3114=head3 B<pg_enable_utf8> (boolean)
3115
3116DBD::Pg specific attribute. If true, then the C<utf8> flag will be turned on
3117for returned character data (if the data is valid UTF-8). For details about
3118the C<utf8> flag, see the C<Encode> module. This attribute is only relevant under
3119perl 5.8 and later.
3120
3121=head3 B<pg_errorlevel> (integer)
3122
3123DBD::Pg specific attribute. Sets the amount of information returned by the server's
3124error messages. Valid entries are 0, 1, and 2. Any other number will be forced to the
3125default value of 1.
3126
3127A value of 0 ("TERSE") will show severity, primary text, and position only
3128and will usually fit on a single line. A value of 1 ("DEFAULT") will also
3129show any detail, hint, or context fields. A value of 2 ("VERBOSE") will
3130show all available information.
3131
3132=head3 B<pg_lib_version> (integer, read-only)
3133
3134DBD::Pg specific attribute. Indicates which version of PostgreSQL that
3135DBD::Pg was compiled against. In other words, which libraries were used.
3136Returns a number with major, minor, and revision together; version 8.1.4
3137would be returned as C<80104>.
3138
3139=head3 B<pg_server_version> (integer, read-only)
3140
3141DBD::Pg specific attribute. Indicates which version of PostgreSQL that
3142the current database handle is connected to. Returns a number with major,
3143minor, and revision together; version 8.0.1 would be C<80001>.
3144
3145=head3 B<Name> (string, read-only)
3146
3147Returns the name of the current database. This is the same as the DSN, without the
3148"dbi:Pg:" part. Before version 2.0.0, this only returned the bare database name
3149(e.g. 'foo'). From version 2.0.0 onwards, it returns the more correct
3150output (e.g. 'dbname=foo')
3151
3152=head3 B<Username> (string, read-only)
3153
3154Returns the name of the user connected to the database.
3155
3156=head3 B<pg_db> (string, read-only)
3157
3158DBD::Pg specific attribute. Returns the name of the current database.
3159
3160=head3 B<pg_user> (string, read-only)
3161
3162DBD::Pg specific attribute. Returns the name of the user that
3163connected to the server.
3164
3165=head3 B<pg_host> (string, read-only)
3166
3167DBD::Pg specific attribute. Returns the host of the current
3168server connection. Locally connected hosts will return an empty
3169string.
3170
3171=head3 B<pg_port> (integer, read-only)
3172
3173DBD::Pg specific attribute. Returns the port of the connection to
3174the server.
3175
3176=head3 B<pg_socket> (integer, read-only)
3177
3178DBD::Pg specific attribute. Returns the file description number of
3179the connection socket to the server.
3180
3181=head3 B<pg_pass> (string, read-only)
3182
3183DBD::Pg specific attribute. Returns the password used to connect
3184to the server.
3185
3186=head3 B<pg_options> (string, read-only)
3187
3188DBD::Pg specific attribute. Returns the command-line options passed
3189to the server. May be an empty string.
3190
3191=head3 B<pg_default_port> (integer, read-only)
3192
3193DBD::Pg specific attribute. Returns the default port used if none is
3194specifically given.
3195
3196=head3 B<pg_pid> (integer, read-only)
3197
3198DBD::Pg specific attribute. Returns the process id (PID) of the
3199backend server process handling the connection.
3200
3201=head3 B<pg_prepare_now> (boolean)
3202
3203DBD::Pg specific attribute. Default is off. If true, then the L</prepare> method will
3204immediately prepare commands, rather than waiting until the first execute.
3205
3206=head3 B<pg_expand_array> (boolean)
3207
3208DBD::Pg specific attribute. Defaults to true. If false, arrays returned from the server will
3209not be changed into a Perl arrayref, but remain as a string.
3210
3211=head3 B<pg_async_status> (integer, read-only)
3212
3213DBD::Pg specific attribute. Returns the current status of an L<asynchronous|/Asynchronous Queries>
3214command. 0 indicates no asynchronous command is in progress, 1 indicates that
3215an asynchronous command has started and -1 indicated that an asynchronous command
3216has been cancelled.
3217
3218=head3 B<pg_standard_conforming_strings> (boolean, read-only)
3219
3220DBD::Pg specific attribute. Returns true if the server is currently using
3221standard conforming strings. Only available if the target
3222server is version 8.2 or better.
3223
3224=head3 B<pg_INV_READ> (integer, read-only)
3225
3226Constant to be used for the mode in L</lo_creat> and L</lo_open>.
3227
3228=head3 B<pg_INV_WRITE> (integer, read-only)
3229
3230Constant to be used for the mode in L</lo_creat> and L</lo_open>.
3231
3232=head3 B<Driver> (handle, read-only)
3233
3234Holds the handle of the parent driver. The only recommended use for this is to find the name
3235of the driver using:
3236
3237 $dbh->{Driver}->{Name}
3238
3239=head3 B<pg_protocol> (integer, read-only)
3240
3241DBD::Pg specific attribute. Returns the version of the PostgreSQL server.
3242If DBD::Pg is unable to figure out the version, it will return a "0". Otherwise,
3243a "3" is returned.
3244
3245=head3 B<RowCacheSize>
3246
3247Not used by DBD::Pg
3248
3249=head1 DBI STATEMENT HANDLE OBJECTS
3250
3251=head2 Statement Handle Methods
3252
3253=head3 B<bind_param>
3254
3255 $rv = $sth->bind_param($param_num, $bind_value);
3256 $rv = $sth->bind_param($param_num, $bind_value, $bind_type);
3257 $rv = $sth->bind_param($param_num, $bind_value, \%attr);
3258
3259Allows the user to bind a value and/or a data type to a placeholder. This is
3260especially important when using server-side prepares. See the
3261L</prepare> method for more information.
3262
3263The value of C<$param_num> is a number if using the '?' or '$1' style
3264placeholders. If using ":foo" style placeholders, the complete name
3265(e.g. ":foo") must be given. For numeric values, you can either use a
3266number or use a literal '$1'. See the examples below.
3267
3268The C<$bind_value> argument is fairly self-explanatory. A value of C<undef> will
3269bind a C<NULL> to the placeholder. Using C<undef> is useful when you want
3270to change just the type and will be overwriting the value later.
3271(Any value is actually usable, but C<undef> is easy and efficient).
3272
3273The C<\%attr> hash is used to indicate the data type of the placeholder.
3274The default value is "varchar". If you need something else, you must
3275use one of the values provided by DBI or by DBD::Pg. To use a SQL value,
3276modify your "use DBI" statement at the top of your script as follows:
3277
3278 use DBI qw(:sql_types);
3279
3280This will import some constants into your script. You can plug those
3281directly into the L</bind_param> call. Some common ones that you will
3282encounter are:
3283
3284 SQL_INTEGER
3285
3286To use PostgreSQL data types, import the list of values like this:
3287
3288 use DBD::Pg qw(:pg_types);
3289
3290You can then set the data types by setting the value of the C<pg_type>
3291key in the hash passed to L</bind_param>.
3292The current list of Postgres data types exported is:
3293
3294 PG_ABSTIME PG_ABSTIMEARRAY PG_ACLITEM PG_ACLITEMARRAY PG_ANY PG_ANYARRAY
3295 PG_ANYELEMENT PG_ANYENUM PG_ANYNONARRAY PG_BIT PG_BITARRAY PG_BOOL
3296 PG_BOOLARRAY PG_BOX PG_BOXARRAY PG_BPCHAR PG_BPCHARARRAY PG_BYTEA
3297 PG_BYTEAARRAY PG_CHAR PG_CHARARRAY PG_CID PG_CIDARRAY PG_CIDR
3298 PG_CIDRARRAY PG_CIRCLE PG_CIRCLEARRAY PG_CSTRING PG_CSTRINGARRAY PG_DATE
3299 PG_DATEARRAY PG_FLOAT4 PG_FLOAT4ARRAY PG_FLOAT8 PG_FLOAT8ARRAY PG_GTSVECTOR
3300 PG_GTSVECTORARRAY PG_INET PG_INETARRAY PG_INT2 PG_INT2ARRAY PG_INT2VECTOR
3301 PG_INT2VECTORARRAY PG_INT4 PG_INT4ARRAY PG_INT8 PG_INT8ARRAY PG_INTERNAL
3302 PG_INTERVAL PG_INTERVALARRAY PG_LANGUAGE_HANDLER PG_LINE PG_LINEARRAY PG_LSEG
3303 PG_LSEGARRAY PG_MACADDR PG_MACADDRARRAY PG_MONEY PG_MONEYARRAY PG_NAME
3304 PG_NAMEARRAY PG_NUMERIC PG_NUMERICARRAY PG_OID PG_OIDARRAY PG_OIDVECTOR
3305 PG_OIDVECTORARRAY PG_OPAQUE PG_PATH PG_PATHARRAY PG_PG_ATTRIBUTE PG_PG_CLASS
3306 PG_PG_PROC PG_PG_TYPE PG_POINT PG_POINTARRAY PG_POLYGON PG_POLYGONARRAY
3307 PG_RECORD PG_RECORDARRAY PG_REFCURSOR PG_REFCURSORARRAY PG_REGCLASS PG_REGCLASSARRAY
3308 PG_REGCONFIG PG_REGCONFIGARRAY PG_REGDICTIONARY PG_REGDICTIONARYARRAY PG_REGOPER PG_REGOPERARRAY
3309 PG_REGOPERATOR PG_REGOPERATORARRAY PG_REGPROC PG_REGPROCARRAY PG_REGPROCEDURE PG_REGPROCEDUREARRAY
3310 PG_REGTYPE PG_REGTYPEARRAY PG_RELTIME PG_RELTIMEARRAY PG_SMGR PG_TEXT
3311 PG_TEXTARRAY PG_TID PG_TIDARRAY PG_TIME PG_TIMEARRAY PG_TIMESTAMP
3312 PG_TIMESTAMPARRAY PG_TIMESTAMPTZ PG_TIMESTAMPTZARRAY PG_TIMETZ PG_TIMETZARRAY PG_TINTERVAL
3313 PG_TINTERVALARRAY PG_TRIGGER PG_TSQUERY PG_TSQUERYARRAY PG_TSVECTOR PG_TSVECTORARRAY
3314 PG_TXID_SNAPSHOT PG_TXID_SNAPSHOTARRAY PG_UNKNOWN PG_UUID PG_UUIDARRAY PG_VARBIT
3315 PG_VARBITARRAY PG_VARCHAR PG_VARCHARARRAY PG_VOID PG_XID PG_XIDARRAY
3316 PG_XML PG_XMLARRAY
3317
3318Data types are "sticky," in that once a data type is set to a certain placeholder,
3319it will remain for that placeholder, unless it is explicitly set to something
3320else afterwards. If the statement has already been prepared, and you switch the
3321data type to something else, DBD::Pg will re-prepare the statement for you before
3322doing the next execute.
3323
3324Examples:
3325
3326 use DBI qw(:sql_types);
3327 use DBD::Pg qw(:pg_types);
3328
3329 $SQL = "SELECT id FROM ptable WHERE size > ? AND title = ?";
3330 $sth = $dbh->prepare($SQL);
3331
3332 ## Both arguments below are bound to placeholders as "varchar"
3333 $sth->execute(123, "Merk");
3334
3335 ## Reset the datatype for the first placeholder to an integer
3336 $sth->bind_param(1, undef, SQL_INTEGER);
3337
3338 ## The "undef" bound above is not used, since we supply params to execute
3339 $sth->execute(123, "Merk");
3340
3341 ## Set the first placeholder's value and data type
3342 $sth->bind_param(1, 234, { pg_type => PG_TIMESTAMP });
3343
3344 ## Set the second placeholder's value and data type.
3345 ## We don't send a third argument, so the default "varchar" is used
3346 $sth->bind_param('$2', "Zool");
3347
3348 ## We realize that the wrong data type was set above, so we change it:
3349 $sth->bind_param('$1', 234, { pg_type => SQL_INTEGER });
3350
3351 ## We also got the wrong value, so we change that as well.
3352 ## Because the data type is sticky, we don't need to change it
3353 $sth->bind_param(1, 567);
3354
3355 ## This executes the statement with 567 (integer) and "Zool" (varchar)
3356 $sth->execute();
3357
3358=head3 B<bind_param_inout>
3359
3360 $rv = $sth->bind_param_inout($param_num, \$scalar, 0);
3361
3362
3363Experimental support for this feature is provided. The first argument to
3364bind_param_inout should be a placeholder number. The second argument
3365should be a reference to a scalar variable in your script. The third argument
3366is not used and should simply be set to 0. Note that what this really does is
3367assign a returned column to the variable, in the order in which the column
3368appears. For example:
3369
3370 my $foo = 123;
3371 $sth = $dbh->prepare("SELECT 1+?::int");
3372 $sth->bind_param_inout(1, \$foo, 0);
3373 $foo = 222;
3374 $sth->execute(444);
3375 $sth->fetch;
3376
3377The above will cause $foo to have a new value of "223" after the final fetch.
3378Note that the variables bound in this manner are very sticky, and will trump any
3379values passed in to execute. This is because the binding is done as late as possible,
3380at the execute() stage, allowing the value to be changed between the time it was bound
3381and the time the query is executed. Thus, the above execute is the same as:
3382
3383 $sth->execute();
3384
3385=head3 B<bind_param_array>
3386
3387
3388 $rv = $sth->bind_param_array($param_num, $array_ref_or_value)
3389 $rv = $sth->bind_param_array($param_num, $array_ref_or_value, $bind_type)
3390 $rv = $sth->bind_param_array($param_num, $array_ref_or_value, \%attr)
3391
3392Binds an array of values to a placeholder, so that each is used in turn by a call
3393to the L</execute_array> method.
3394
3395=head3 B<execute>
3396
3397 $rv = $sth->execute(@bind_values);
3398
3399Executes a previously prepared statement. In addition to C<UPDATE>, C<DELETE>,
3400C<INSERT> statements, for which it returns always the number of affected rows,
3401the C<execute> method can also be used for C<SELECT ... INTO table> statements.
3402
3403The "prepare/bind/execute" process has changed significantly for PostgreSQL
3404servers 7.4 and later: please see the C<prepare()> and C<bind_param()> entries for
3405much more information.
3406
3407Setting one of the bind_values to "undef" is the equivalent of setting the value
3408to NULL in the database. Setting the bind_value to $DBDPG_DEFAULT is equivalent
3409to sending the literal string 'DEFAULT' to the backend. Note that using this
3410option will force server-side prepares off until such time as PostgreSQL
3411supports using DEFAULT in prepared statements.
3412
3413DBD::Pg also supports passing in arrays to execute: simply pass in an arrayref,
3414and DBD::Pg will flatten it into a string suitable for input on the backend.
3415
3416If you are using Postgres version 8.2 or greater, you can also use any of the
3417fetch methods to retrieve the values of a C<RETURNING> clause after you execute
3418an C<UPDATE>, C<DELETE>, or C<INSERT>. For example:
3419
3420 $dbh->do(q{CREATE TABLE abc (id SERIAL, country TEXT)});
3421 $SQL = q{INSERT INTO abc (country) VALUES (?) RETURNING id};
3422 $sth = $dbh->prepare($SQL);
3423 $sth->execute('France');
3424 $countryid = $sth->fetch()->[0];
3425 $sth->execute('New Zealand');
3426 $countryid = $sth->fetch()->[0];
3427
3428=head3 B<execute_array>
3429
3430 $tuples = $sth->execute_array() or die $sth->errstr;
3431 $tuples = $sth->execute_array(\%attr) or die $sth->errstr;
3432 $tuples = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
3433
3434 ($tuples, $rows) = $sth->execute_array(\%attr) or die $sth->errstr;
3435 ($tuples, $rows) = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
3436
3437Execute a prepared statement once for each item in a passed-in hashref, or items that
3438were previously bound via the L</bind_param_array> method. See the DBI documentation
3439for more details.
3440
3441=head3 B<execute_for_fetch>
3442
3443 $tuples = $sth->execute_for_fetch($fetch_tuple_sub);
3444 $tuples = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
3445
3446 ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub);
3447 ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
3448
3449Used internally by the L</execute_array> method, and rarely used directly. See the
3450DBI documentation for more details.
3451
3452=head3 B<fetchrow_arrayref>
3453
3454 $ary_ref = $sth->fetchrow_arrayref;
3455
3456Fetches the next row of data from the statement handle, and returns a reference to an array
3457holding the column values. Any columns that are NULL are returned as undef within the array.
3458
3459If there are no more rows or if an error occurs, the this method return undef. You should
3460check C<< $sth->err >> afterwards (or use the L</RaiseError> attribute) to discover if the undef returned
3461was due to an error.
3462
3463Note that the same array reference is returned for each fetch, so don't store the reference and
3464then use it after a later fetch. Also, the elements of the array are also reused for each row,
3465so take care if you want to take a reference to an element. See also L</bind_columns>.
3466
3467=head3 B<fetchrow_array>
3468
3469 @ary = $sth->fetchrow_array;
3470
3471Similar to the L</fetchrow_arrayref> method, but returns a list of column information rather than
3472a reference to a list. Do not use this in a scalar context.
3473
3474=head3 B<fetchrow_hashref>
3475
3476 $hash_ref = $sth->fetchrow_hashref;
3477 $hash_ref = $sth->fetchrow_hashref($name);
3478
3479Fetches the next row of data and returns a hashref containing the name of the columns as the keys
3480and the data itself as the values. Any NULL value is returned as as undef value.
3481
3482If there are no more rows or if an error occurs, the this method return undef. You should
3483check C<< $sth->err >> afterwards (or use the L</RaiseError> attribute) to discover if the undef returned
3484was due to an error.
3485
3486The optional C<$name> argument should be either C<NAME>, C<NAME_lc> or C<NAME_uc>, and indicates
3487what sort of transformation to make to the keys in the hash.
3488
3489=head3 B<fetchall_arrayref>
3490
3491 $tbl_ary_ref = $sth->fetchall_arrayref();
3492 $tbl_ary_ref = $sth->fetchall_arrayref( $slice );
3493 $tbl_ary_ref = $sth->fetchall_arrayref( $slice, $max_rows );
3494
3495Returns a reference to an array of arrays that contains all the remaining rows to be fetched from the
3496statement handle. If there are no more rows, an empty arrayref will be returned. If an error occurs,
3497the data read in so far will be returned. Because of this, you should always check C<< $sth->err >> after
3498calling this method, unless L</RaiseError> has been enabled.
3499
3500If C<$slice> is an array reference, fetchall_arrayref uses the L</fetchrow_arrayref> method to fetch each
3501row as an array ref. If the C<$slice> array is not empty then it is used as a slice to select individual
3502columns by perl array index number (starting at 0, unlike column and parameter numbers which start at 1).
3503
3504With no parameters, or if $slice is undefined, fetchall_arrayref acts as if passed an empty array ref.
3505
3506If C<$slice> is a hash reference, fetchall_arrayref uses L</fetchrow_hashref> to fetch each row as a hash reference.
3507
3508See the DBI documentation for a complete discussion.
3509
3510=head3 B<fetchall_hashref>
3511
3512 $hash_ref = $sth->fetchall_hashref( $key_field );
3513
3514Returns a hashref containing all rows to be fetched from the statement handle. See the DBI documentation for
3515a full discussion.
3516
3517=head3 B<finish>
3518
3519 $rv = $sth->finish;
3520
3521Indicates to DBI that you are finished with the statement handle and are not going to use it again. Only needed
3522when you have not fetched all the possible rows.
3523
3524=head3 B<rows>
3525
3526 $rv = $sth->rows;
3527
3528Returns the number of rows returned by the last query. In contrast to many other DBD modules,
3529the number of rows is available immediately after calling C<< $sth->execute >>. Note that
3530the L</execute> method itself returns the number of rows itself, which means that this
3531method is rarely needed.
3532
3533=head3 B<bind_col>
3534
3535 $rv = $sth->bind_col($column_number, \$var_to_bind);
3536 $rv = $sth->bind_col($column_number, \$var_to_bind, \%attr );
3537 $rv = $sth->bind_col($column_number, \$var_to_bind, $bind_type );
3538
3539Binds a Perl variable and/or some attributes to an output column of a SELECT statement.
3540Column numbers count up from 1. You do not need to bind output columns in order to fetch data.
3541
3542See the DBI documentation for a discussion of the optional parameters C<\%attr> and C<$bind_type>
3543
3544=head3 B<bind_columns>
3545
3546 $rv = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
3547
3548Calls the L</bind_col> method for each column in the SELECT statement, using the supplied list.
3549
3550=head3 B<dump_results>
3551
3552 $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
3553
3554Fetches all the rows from the statement handle, calls C<DBI::neat_list> for each row, and
3555prints the results to C<$fh> (which defaults to F<STDOUT>). Rows are separated by C<$lsep> (which defaults
3556to a newline). Columns are separated by C<$fsep> (which defaults to a comma). The C<$maxlen> controls
3557how wide the output can be, and defaults to 35.
3558
3559This method is designed as a handy utility for prototyping and testing queries. Since it uses
3560"neat_list" to format and edit the string for reading by humans, it is not recommended
3561for data transfer applications.
3562
3563=head3 B<blob_read>
3564
3565 $blob = $sth->blob_read($id, $offset, $len);
3566
3567Supported by DBD::Pg. This method is implemented by DBI but not
3568currently documented by DBI, so this method might change.
3569
3570This method seems to be heavily influenced by the current implementation of
3571blobs in Oracle. Nevertheless we try to be as compatible as possible. Whereas
3572Oracle suffers from the limitation that blobs are related to tables and every
3573table can have only one blob (datatype LONG), PostgreSQL handles its blobs
3574independent of any table by using so-called object identifiers. This explains
3575why the C<blob_read> method is blessed into the STATEMENT package and not part of
3576the DATABASE package. Here the field parameter has been used to handle this
3577object identifier. The offset and len parameters may be set to zero, in which
3578case the whole blob is fetched at once.
3579
3580See also the PostgreSQL-specific functions concerning blobs, which are
3581available via the C<func> interface.
3582
3583For further information and examples about blobs, please read the chapter
3584about Large Objects in the PostgreSQL Programmer's Guide at
3585L<http://www.postgresql.org/docs/current/static/largeobjects.html>.
3586
3587=head2 Statement Handle Attributes
3588
3589=head3 B<NUM_OF_FIELDS> (integer, read-only)
3590
3591Returns the number of columns returned by the current statement. A number will only be returned for
3592SELECT statements, for SHOW statements (which always return C<1>), and for INSERT,
3593UPDATE, and DELETE statements which contain a RETURNING clause.
3594This method returns undef if called before C<execute()>.
3595
3596=head3 B<NUM_OF_PARAMS> (integer, read-only)
3597
3598Returns the number of placeholders in the current statement.
3599
3600=head3 B<NAME> (arrayref, read-only)
3601
3602Returns an arrayref of column names for the current statement. This
3603method will only work for SELECT statements, for SHOW statements, and for
3604INSERT, UPDATE, and DELETE statements which contain a RETURNING clause.
3605This method returns undef if called before C<execute()>.
3606
3607=head3 B<NAME_lc> (arrayref, read-only)
3608
3609The same as the C<NAME> attribute, except that all column names are forced to lower case.
3610
3611=head3 B<NAME_uc> (arrayref, read-only)
3612
3613The same as the C<NAME> attribute, except that all column names are forced to upper case.
3614
3615=head3 B<NAME_hash> (hashref, read-only)
3616
3617Similar to the C<NAME> attribute, but returns a hashref of column names instead of an arrayref. The names of the columns
3618are the keys of the hash, and the values represent the order in which the columns are returned, starting at 0.
3619This method returns undef if called before C<execute()>.
3620
3621=head3 B<NAME_lc_hash> (hashref, read-only)
3622
3623The same as the C<NAME_hash> attribute, except that all column names are forced to lower case.
3624
3625=head3 B<NAME_uc_hash> (hashref, read-only)
3626
3627The same as the C<NAME_hash> attribute, except that all column names are forced to lower case.
3628
3629=head3 B<TYPE> (arrayref, read-only)
3630
3631Returns an arrayref indicating the data type for each column in the statement.
3632This method returns undef if called before C<execute()>.
3633
3634=head3 B<PRECISION> (arrayref, read-only)
3635
3636Returns an arrayref of integer values for each column returned by the statement.
3637The number indicates the precision for C<NUMERIC> columns, the size in number of
3638characters for C<CHAR> and C<VARCHAR> columns, and for all other types of columns
3639it returns the number of I<bytes>.
3640This method returns undef if called before C<execute()>.
3641
3642=head3 B<SCALE> (arrayref, read-only)
3643
3644Returns an arrayref of integer values for each column returned by the statement. The number
3645indicates the scale of the that column. The only type that will return a value is C<NUMERIC>.
3646This method returns undef if called before C<execute()>.
3647
3648=head3 B<NULLABLE> (arrayref, read-only)
3649
3650Returns an arrayref of integer values for each column returned by the statement. The number
3651indicates if the column is nullable or not. 0 = not nullable, 1 = nullable, 2 = unknown.
3652This method returns undef if called before C<execute()>.
3653
3654=head3 B<Database> (dbh, read-only)
3655
3656Returns the database handle this statement handle was created from.
3657
3658=head3 B<ParamValues> (hash ref, read-only)
3659
3660Returns a reference to a hash containing the values currently bound to placeholders. If the "named parameters"
3661type of placeholders are being used (such as ":foo"), then the keys of the hash will be the names of the
3662placeholders (without the colon). If the "dollar sign numbers" type of placeholders are being used, the keys of the hash will
3663be the numbers, without the dollar signs. If the "question mark" type is used, integer numbers will be returned,
3664starting at one and increasing for every placeholder.
3665
3666If this method is called before L</execute>, the literal values passed in are returned. If called after
3667L</execute>, then the quoted versions of the values are returned.
3668
3669=head3 B<ParamTypes> (hash ref, read-only)
3670
3671Returns a reference to a hash containing the type names currently bound to placeholders. The keys
3672are the same as returned by the ParamValues method. The values are hashrefs containing a single key value
3673pair, in which the key is either 'TYPE' if the type has a generic SQL equivalent, and 'pg_type' if the type can
3674only be expressed by a Postgres type. The value is the internal number corresponding to the type originally
3675passed in. (Placeholders that have not yet been bound will return undef as the value). This allows the output of
3676ParamTypes to be passed back to the L</bind_param> method.
3677
3678=head3 B<Statement> (string, read-only)
3679
3680Returns the statement string passed to the most recent "prepare" method called in this database handle, even if that method
3681failed. This is especially useful where "RaiseError" is enabled and the exception handler checks $@ and sees that a C<prepare>
3682method call failed.
3683
3684=head3 B<pg_current_row> (integer, read-only)
3685
3686DBD::Pg specific attribute. Returns the number of the tuple (row) that was
3687most recently fetched. Returns zero before and after fetching is performed.
3688
3689=head3 B<pg_numbound> (integer, read-only)
3690
3691DBD::Pg specific attribute. Returns the number of placeholders
3692that are currently bound (via bind_param).
3693
3694=head3 B<pg_bound> (hashref, read-only)
3695
3696DBD::Pg specific attribute. Returns a hash of all named placeholders. The
3697key is the name of the placeholder, and the value is a 0 or a 1, indicating if
3698the placeholder has been bound yet (e.g. via bind_param)
3699
3700=head3 B<pg_size> (arrayref, read-only)
3701
3702DBD::Pg specific attribute. It returns a reference to an array of integer
3703values for each column. The integer shows the size of the column in
3704bytes. Variable length columns are indicated by -1.
3705
3706=head3 B<pg_type> (arrayref, read-only)
3707
3708DBD::Pg specific attribute. It returns a reference to an array of strings
3709for each column. The string shows the name of the data_type.
3710
3711=head3 B<pg_segments> (arrayref, read-only)
3712
3713DBD::Pg specific attribute. Returns an arrayref of the query split on the
3714placeholders.
3715
3716=head3 B<pg_oid_status> (integer, read-only)
3717
3718DBD::Pg specific attribute. It returns the OID of the last INSERT command.
3719
3720=head3 B<pg_cmd_status> (integer, read-only)
3721
3722DBD::Pg specific attribute. It returns the type of the last
3723command. Possible types are: "INSERT", "DELETE", "UPDATE", "SELECT".
3724
3725=head3 B<pg_direct> (boolean)
3726
3727DBD::Pg specific attribute. Default is false. If true, the query is passed
3728directly to the backend without parsing for placeholders.
3729
3730=head3 B<pg_prepare_now> (boolean)
3731
3732DBD::Pg specific attribute. Default is off. If true, the query will be immediately
3733prepared, rather than waiting for the L</execute> call.
3734
3735=head3 B<pg_prepare_name> (string)
3736
3737DBD::Pg specific attribute. Specifies the name of the prepared statement to use for this
3738statement handle. Not normally needed, see the section on the L</prepare> method for
3739more information.
3740
3741=head3 B<pg_server_prepare> (integer)
3742
3743DBD::Pg specific attribute. Indicates if DBD::Pg should attempt to use server-side
3744prepared statements for this statement handle. The default value, 1, indicates that prepared
3745statements should be used whenever possible. See the section on the L</prepare> method for
3746more information.
3747
3748=head3 B<pg_placeholder_dollaronly> (boolean)
3749
3750DBD::Pg specific attribute. Defaults to off. When true, question marks inside of the query
3751being prepared are not treated as placeholders. Useful for statements that contain unquoted question
3752marks, such as geometric operators.
3753
3754=head3 B<pg_async> (integer)
3755
3756DBD::Pg specific attribute. Indicates the current behavior for asynchronous queries. See the section
3757on L</Asynchronous Constants> for more information.
3758
3759=head3 B<RowsInCache>
3760
3761Not used by DBD::Pg
3762
3763=head3 B<RowCache>
3764
3765Not used by DBD::Pg
3766
3767=head3 B<CursorName>
3768
3769Not used by DBD::Pg. See the note about L</Cursors> elsewhere in this document.
3770
3771=head1 FURTHER INFORMATION
3772
3773=head2 Transactions
3774
3775Transaction behavior is controlled via the L</AutoCommit> attribute. For a
3776complete definition of C<AutoCommit> please refer to the DBI documentation.
3777
3778According to the DBI specification the default for C<AutoCommit> is a true
3779value. In this mode, any change to the database becomes valid immediately. Any
3780C<BEGIN>, C<COMMIT> or C<ROLLBACK> statements will be rejected. DBD::Pg
3781implements C<AutoCommit> by issuing a C<BEGIN> statement immediately before
3782executing a statement, and a C<COMMIT> afterwards. Note that preparing a
3783statement is not always enough to trigger the first C<BEGIN>, as the actual
3784C<PREPARE> is usually postponed until the first call to L</execute>.
3785
3786=head2 Savepoints
3787
3788PostgreSQL version 8.0 introduced the concept of savepoints, which allows
3789transactions to be rolled back to a certain point without affecting the
3790rest of the transaction. DBD::Pg encourages using the following methods to
3791control savepoints:
3792
3793=head3 C<pg_savepoint>
3794
3795Creates a savepoint. This will fail unless you are inside of a transaction. The
3796only argument is the name of the savepoint. Note that PostgreSQL DOES allow
3797multiple savepoints with the same name to exist.
3798
3799 $dbh->pg_savepoint("mysavepoint");
3800
3801=head3 C<pg_rollback_to>
3802
3803Rolls the database back to a named savepoint, discarding any work performed after
3804that point. If more than one savepoint with that name exists, rolls back to the
3805most recently created one.
3806
3807 $dbh->pg_rollback_to("mysavepoint");
3808
3809=head3 C<pg_release>
3810
3811Releases (or removes) a named savepoint. If more than one savepoint with that name
3812exists, it will only destroy the most recently created one. Note that all savepoints
3813created after the one being released are also destroyed.
3814
3815 $dbh->pg_release("mysavepoint");
3816
3817=head2 Asynchronous Queries
3818
3819It is possible to send a query to the backend and have your script do other work while the query is
3820running on the backend. Both queries sent by the L</do> method, and by the L</execute> method can be
3821sent asynchronously. (NOTE: This will only work if DBD::Pg has been compiled against Postgres libraries
3822of version 8.0 or greater) The basic usage is as follows:
3823
3824 use DBD::Pg ':async';
3825
3826 print "Async do() example:\n";
3827 $dbh->do("SELECT long_running_query()", {pg_async => PG_ASYNC});
3828 do_something_else();
3829 {
3830 if ($dbh->pg_ready()) {
3831 $res = $pg_result();
3832 print "Result of do(): $res\n";
3833 }
3834 print "Query is still running...\n";
3835 if (cancel_request_received) {
3836 $dbh->pg_cancel();
3837 }
3838 sleep 1;
3839 redo;
3840 }
3841
3842 print "Async prepare/execute example:\n";
3843 $sth = $dbh->prepare("SELECT long_running_query(1)", {pg_async => PG_ASYNC});
3844 $sth->execute();
3845
3846 ## Changed our mind, cancel and run again:
3847 $sth = $dbh->prepare("SELECT 678", {pg_async => PG_ASYNC + PG_OLDQUERY_CANCEL});
3848 $sth->execute();
3849
3850 do_something_else();
3851
3852 if (!$sth->pg_ready) {
3853 do_another_thing();
3854 }
3855
3856 ## We wait until it is done, and get the result:
3857 $res = $dbh->pg_result();
3858
3859=head3 Asynchronous Constants
3860
3861There are currently three asynchronous constants exported by DBD::Pg. You can import all of them by putting
3862either of these at the top of your script:
3863
3864 use DBD::Pg;
3865
3866 use DBD::Pg ':async';
3867
3868You may also use the numbers instead of the constants, but using the constants is recommended as it
3869makes your script more readable.
3870
3871=over 4
3872
3873=item PG_ASYNC
3874
3875This is a constant for the number 1. It is passed to either the L</do> or the L</prepare> method as a value
3876to the pg_async key and indicates that the query should be sent asynchronously.
3877
3878=item PG_OLDQUERY_CANCEL
3879
3880This is a constant for the number 2. When passed to either the L</do> or the L</prepare> method, it causes any
3881currently running asynchronous query to be cancelled and rolled back. It has no effect if no asynchronous
3882query is currently running.
3883
3884=item PG_OLDQUERY_WAIT
3885
3886This is a constant for the number 4. When passed to either the L</do> or the L</prepare> method, it waits for any
3887currently running asynchronous query to complete. It has no effect if there is no asynchronous query currently running.
3888
3889=back
3890
3891=head3 Asynchronous Methods
3892
3893=over 4
3894
3895=item B<pg_cancel>
3896
3897This database-level method attempts to cancel any currently running asynchronous query. It returns true if
3898the cancel succeeded, and false otherwise. Note that a query that has finished before this method is executed
3899will also return false. B<WARNING>: a successful cancellation will leave the database in an unusable state,
3900so DBD::Pg will automatically clear out the error message and issue a ROLLBACK.
3901
3902 $result = $dbh->pg_cancel();
3903
3904=item B<pg_ready>
3905
3906This method can be called as a database handle method or (for convenience) as a statement handle method. Both simply
3907see if a previously issued asynchronous query has completed yet. It returns true if the statement has finished, in which
3908case you should then call the L</pg_result> method. Calls to C<pg_ready()> should only be used when you have other
3909things to do while the query is running. If you simply want to wait until the query is done, do not call pg_ready()
3910over and over, but simply call the pg_result() method.
3911
3912 my $time = 0;
3913 while (!$dbh->pg_ready) {
3914 print "Query is still running. Seconds: $time\n";
3915 $time++;
3916 sleep 1;
3917 }
3918 $result = $dbh->pg_result;
3919
3920=item B<pg_result>
3921
3922This database handle method returns the results of a previously issued asynchronous query. If the query is still
3923running, this method will wait until it has finished. The result returned is the number of rows: the same thing
3924that would have been returned by the asynchronous L</do> or L</execute> if it had been called without an asynchronous flag.
3925
3926 $result = $dbh->pg_result;
3927
3928=back
3929
3930=head3 Asynchronous Examples
3931
3932Here are some working examples of asynchronous queries. Note that we'll use the B<pg_sleep> function to emulate a
3933long-running query.
3934
3935 use strict;
3936 use warnings;
3937 use Time::HiRes 'sleep';
3938 use DBD::Pg ':async';
3939
3940 my $dbh = DBI->connect('dbi:Pg:dbname=postgres', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
3941
3942 ## Kick off a long running query on the first database:
3943 my $sth = $dbh->prepare("SELECT pg_sleep(?)", {pg_async => PG_ASYNC});
3944 $sth->execute(5);
3945
3946 ## While that is running, do some other things
3947 print "Your query is processing. Thanks for waiting\n";
3948 check_on_the_kids(); ## Expensive sub, takes at least three seconds.
3949
3950 while (!$dbh->pg_ready) {
3951 check_on_the_kids();
3952 ## If the above function returns quickly for some reason, we add a small sleep
3953 sleep 0.1;
3954 }
3955
3956 print "The query has finished. Gathering results\n";
3957 my $result = $sth->pg_result;
3958 print "Result: $result\n";
3959 my $info = $sth->fetchall_arrayref();
3960
3961Without asynchronous queries, the above script would take about 8 seconds to run: five seconds waiting
3962for the execute to finish, then three for the check_on_the_kids() function to return. With asynchronous
3963queries, the script takes about 6 seconds to run, and gets in two iterations of check_on_the_kids in
3964the process.
3965
3966Here's an example showing the ability to cancel a long-running query. Imagine two slave databases in
3967different geographic locations over a slow network. You need information as quickly as possible, so
3968you query both at once. When you get an answer, you tell the other one to stop working on your query,
3969as you don't need it anymore.
3970
3971 use strict;
3972 use warnings;
3973 use Time::HiRes 'sleep';
3974 use DBD::Pg ':async';
3975
3976 my $dbhslave1 = DBI->connect('dbi:Pg:dbname=postgres;host=slave1', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
3977 my $dbhslave2 = DBI->connect('dbi:Pg:dbname=postgres;host=slave2', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
3978
3979 $SQL = "SELECT count(*) FROM largetable WHERE flavor='blueberry'";
3980
3981 my $sth1 = $dbhslave1->prepare($SQL, {pg_async => PG_ASYNC});
3982 my $sth2 = $dbhslave2->prepare($SQL, {pg_async => PG_ASYNC});
3983
3984 $sth1->execute();
3985 $sth2->execute();
3986
3987 my $winner;
3988 while (!defined $winner) {
3989 if ($sth1->pg_ready) {
3990 $winner = 1;
3991 }
3992 elsif ($sth2->pg_ready) {
3993 $winner = 2;
3994 }
3995 Time::HiRes::sleep 0.05;
3996 }
3997
3998 my $count;
3999 if ($winner == 1) {
4000 $sth2->pg_cancel();
4001 $sth1->pg_result();
4002 $count = $sth1->fetchall_arrayref()->[0][0];
4003 }
4004 else {
4005 $sth1->pg_cancel();
4006 $sth2->pg_result();
4007 $count = $sth2->fetchall_arrayref()->[0][0];
4008 }
4009
4010=head2 Array support
4011
4012DBD::Pg allows arrays (as arrayrefs) to be passed in to both
4013the L</quote> and the L</execute> methods. In both cases, the array is
4014flattened into a string representing a Postgres array.
4015
4016When fetching rows from a table that contains a column with an
4017array type, the result will be passed back to your script as an arrayref.
4018
4019To turn off the automatic parsing of returned arrays into arrayrefs,
4020you can set the attribute L<pg_expand_array|/pg_expand_array_(boolean)>, which is true by default.
4021
4022 $dbh->{pg_expand_array} = 0;
4023
4024
4025=head2 COPY support
4026
4027DBD::Pg allows for quick (bulk) reading and storing of data by using
4028the B<COPY> command. The basic process is to use C<< $dbh->do >> to issue a
4029COPY command, and then to either add rows using L</pg_putcopydata>, or to
4030read them by using L</pg_getcopydata>.
4031
4032The first step is to put the server into "COPY" mode. This is done by
4033sending a complete COPY command to the server, by using the L</do> method.
4034For example:
4035
4036 $dbh->do("COPY foobar FROM STDIN");
4037
4038This would tell the server to enter a COPY IN mode (yes, that's confusing, but
4039the I<mode> is COPY IN because of the I<command> COPY FROM). It is now ready to
4040receive information via the L</pg_putcopydata> method. The complete syntax of the
4041COPY command is more complex and not documented here: the canonical
4042PostgreSQL documentation for COPY can be found at:
4043
4044http://www.postgresql.org/docs/current/static/sql-copy.html
4045
4046Once a COPY command has been issued, no other SQL commands are allowed
4047until L</pg_putcopyend> has been issued (for COPY FROM), or the final
4048L</pg_getcopydata> has been called (for COPY TO).
4049
4050Note: All other COPY methods (pg_putline, pg_getline, etc.) are now
4051heavily deprecated in favor of the pg_getcopydata, pg_putcopydata, and
4052pg_putcopyend methods.
4053
4054=head3 B<pg_getcopydata>
4055
4056Used to retrieve data from a table after the server has been put into a
4057COPY OUT mode by calling "COPY tablename TO STDOUT". Data is always returned
4058one data row at a time. The first argument to pg_getcopydata
4059is the variable into which the data will be stored (this variable should not
4060be undefined, or it may throw a warning, although it may be a reference). The
4061pg_gecopydata method returns a number greater than 1 indicating the new size of
4062the variable, or a -1 when the COPY has finished. Once a -1 has been returned, no
4063other action is necessary, as COPY mode will have already terminated. Example:
4064
4065 $dbh->do("COPY mytable TO STDOUT");
4066 my @data;
4067 my $x=0;
4068 1 while $dbh->pg_getcopydata($data[$x++]) >= 0;
4069
4070There is also a variation of this method called B<pg_getcopydata_async>, which,
4071as the name suggests, returns immediately. The only difference from the original
4072method is that this version may return a 0, indicating that the row is not
4073ready to be delivered yet. When this happens, the variable has not been changed,
4074and you will need to call the method again until you get a non-zero result.
4075(Data is still always returned one data row at a time.)
4076
4077=head3 B<pg_putcopydata>
4078
4079Used to put data into a table after the server has been put into COPY IN mode
4080by calling "COPY tablename FROM STDIN". The only argument is the data you want
4081inserted. Issue a pg_putcopyend() when you have added all your rows.
4082
4083The default delimiter is a tab character, but this can be changed in
4084the COPY statement. Returns a 1 on successful input. Examples:
4085
4086 ## Simple example:
4087 $dbh->do("COPY mytable FROM STDIN");
4088 $dbh->pg_putcopydata("123\tPepperoni\t3\n");
4089 $dbh->pg_putcopydata("314\tMushroom\t8\n");
4090 $dbh->pg_putcopydata("6\tAnchovies\t100\n");
4091 $dbh->pg_putcopyend();
4092
4093 ## This example uses explicit columns and a custom delimiter
4094 $dbh->do("COPY mytable(flavor, slices) FROM STDIN WITH DELIMITER '~'");
4095 $dbh->pg_putcopydata("Pepperoni~123\n");
4096 $dbh->pg_putcopydata("Mushroom~314\n");
4097 $dbh->pg_putcopydata("Anchovies~6\n");
4098 $dbh->pg_putcopyend();
4099
4100=head3 B<pg_putcopyend>
4101
4102When you are finished with pg_putcopydata, call pg_putcopyend to let the server know
4103that you are done, and it will return to a normal, non-COPY state. Returns a 1 on
4104success. This method will fail if called when not in COPY IN mode.
4105
4106=head2 Large Objects
4107
4108DBD::Pg supports all largeobject functions provided by libpq via the
4109C<func> method. Please note that access to a large object, even read-only
4110large objects, must be put into a transaction.
4111
4112=head2 Cursors
4113
4114Although PostgreSQL supports cursors, they have not been used in the current
4115implementation. When DBD::Pg was created, cursors in PostgreSQL could only be
4116used inside a transaction block. Because only one transaction block at a time
4117is allowed, this would have implied the restriction not to use any nested
4118C<SELECT> statements. Therefore the L</execute> method fetches all data at
4119once into data structures located in the front-end application. This fact
4120must to be considered when selecting large amounts of data!
4121
4122You can use cursors in your application, but you'll need to do a little
4123work. First you must declare your cursor. Now you can issue queries against
4124the cursor, then select against your queries. This typically results in a
4125double loop, like this:
4126
4127 # WITH HOLD is not needed if AutoCommit is off
4128 $dbh->do("DECLARE csr CURSOR WITH HOLD FOR $sql");
4129 while (1) {
4130 my $sth = $dbh->prepare("fetch 1000 from csr");
4131 $sth->execute;
4132 last if 0 == $sth->rows;
4133
4134 while (my $row = $sth->fetchrow_hashref) {
4135 # Do something with the data.
4136 }
4137 }
4138 $dbh->do("CLOSE csr");
4139
4140=head2 Datatype bool
4141
4142The current implementation of PostgreSQL returns 't' for true and 'f' for
4143false. From the Perl point of view, this is a rather unfortunate
4144choice. DBD::Pg therefore translates the result for the C<BOOL> data type in a
4145Perlish manner: 'f' becomes the number C<0> and 't' becomes the number C<1>. This way
4146the application does not have to check the database-specific returned values for
4147the data-type C<BOOL> because Perl treats C<0> as false and C<1> as true. You may
4148set the L<pg_bool_tf|/pg_bool_tf_(boolean)> attribute to a true value to change the values back to 't' and
4149'f' if you wish.
4150
4151Boolean values can be passed to PostgreSQL as TRUE, 't', 'true', 'y', 'yes' or
4152'1' for true and FALSE, 'f', 'false', 'n', 'no' or '0' for false.
4153
4154=head2 Schema support
4155
4156The PostgreSQL schema concept may differ from those of other databases. In a nutshell,
4157a schema is a named collection of objects within a single database. Please refer to the
4158PostgreSQL documentation for more details:
4159
4160L<http://www.postgresql.org/docs/current/static/ddl-schemas.html>
4161
4162DBD::Pg does not provide explicit support for PostgreSQL schemas.
4163However, schema functionality may be used without any restrictions by
4164explicitly addressing schema objects, e.g.
4165
4166 my $res = $dbh->selectall_arrayref("SELECT * FROM my_schema.my_table");
4167
4168or by manipulating the schema search path with C<SET search_path>, e.g.
4169
4170 $dbh->do("SET search_path TO my_schema, public");
4171
4172=head1 SEE ALSO
4173
4174=for text The B<DBI> module.
4175
4176=for html <a href="http://search.cpan.org/~timb/DBI/DBI.pm">The DBI module</a>
4177
4178=head1 BUGS
4179
4180To report a bug, or view the current list of bugs, please visit
4181http://rt.cpan.org/Public/Dist/Display.html?Name=DBD-Pg
4182
4183=head1 AUTHORS
4184
4185DBI by Tim Bunce L<http://www.tim.bunce.name>
4186
4187The original DBD-Pg was by Edmund Mergl (E.Mergl@bawue.de) and Jeffrey W. Baker
4188(jwbaker@acm.org). Major developers include David Wheeler <david@justatheory.com>, Jason
4189Stewart <jason@openinformatics.com>, Bruce Momjian <pgman@candle.pha.pa.us>, and
4190Greg Sabino Mullane <greg@turnstep.com>, with help from many others: see the F<Changes>
4191file for a complete list.
4192
4193Parts of this package were originally copied from DBI and DBD-Oracle.
4194
4195B<Mailing List>
4196
4197The current maintainers may be reached through the 'dbd-pg' mailing list:
4198<dbd-pg@perl.org>
4199
4200=head1 COPYRIGHT AND LICENSE
4201
4202Copyright (C) 1994-2009, Greg Sabino Mullane
4203
4204This module (DBD::Pg) is free software; you can redistribute it and/or modify it
4205under the same terms as Perl 5.10.0. For more details, see the full text of the
4206licenses in the directory LICENSES.
4207
4208=cut
# spent 757µs within DBD::Pg::bootstrap which was called # once (757µs+0s) by DynaLoader::bootstrap at line 253 of DynaLoader.pm
sub DBD::Pg::bootstrap; # xsub
# spent 73.9ms within DBD::Pg::db::_login which was called # once (73.9ms+0s) by DBI::dr::connect at line 223 of DBD/Pg.pm
sub DBD::Pg::db::_login; # xsub
# spent 30.8ms within DBD::Pg::db::_ping which was called 7 times, avg 4.40ms/call: # 7 times (30.8ms+0s) by DBI::db::ping at line 387 of DBD/Pg.pm, avg 4.40ms/call
sub DBD::Pg::db::_ping; # xsub
# spent 19µs within DBD::Pg::dr::CORE:match which was called 2 times, avg 10µs/call: # 2 times (19µs+0s) by DBI::dr::connect at line 205 of DBD/Pg.pm, avg 10µs/call
sub DBD::Pg::dr::CORE:match; # xsub
# spent 17µs within DBD::Pg::dr::CORE:subst which was called # once (17µs+0s) by DBI::dr::connect at line 202 of DBD/Pg.pm
sub DBD::Pg::dr::CORE:subst; # xsub
# spent 223µs within DBD::Pg::st::_prepare which was called 12 times, avg 19µs/call: # 12 times (223µs+0s) by DBI::db::prepare at line 267 of DBD/Pg.pm, avg 19µs/call
sub DBD::Pg::st::_prepare; # xsub
Report produced by the NYTProf 3.11 Perl profiler, developed by Tim Bunce and Adam Kaplan.