1Pg(3) User Contributed Perl Documentation Pg(3)
2
6 DBD::Pg - PostgreSQL database driver for the DBI module
7
9 use DBI;
10 $dbh = DBI->connect("dbi:Pg:dbname=$dbname", '', '', {AutoCommit => 0});
12 # The AutoCommit attribute should always be explicitly set
13 # For some advanced uses you may need PostgreSQL type values:
15 use DBD::Pg qw(:pg_types);
16 # For asynchronous calls, import the async constants:
18 use DBD::Pg qw(:async);
19 $dbh->do('INSERT INTO mytable(a) VALUES (1)');
21 $sth = $dbh->prepare('INSERT INTO mytable(a) VALUES (?)');
23 $sth->execute();
24
26 This documents version 2.17.1 of the DBD::Pg module
27
29 DBD::Pg is a Perl module that works with the DBI module to provide
30 access to PostgreSQL databases.
31
33 This documentation describes driver specific behavior and restrictions.
34 It is not supposed to be used as the only reference for the user. In
35 any case consult the DBI documentation first!
36
38 DBI Class Methods
39 connect
40 This method creates a database handle by connecting to a database, and
42 is the DBI equivalent of the "new" method. To connect to a Postgres
43 database with a minimum of parameters, use the following syntax:
44 $dbh = DBI->connect("dbi:Pg:dbname=$dbname", '', '', {AutoCommit => 0});
46 This connects to the database named in the $dbname variable on the
48 default port (usually 5432) without any user authentication.
49 The following connect statement shows almost all possible parameters:
51 $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host;port=$port;options=$options",
53 $username,
54 $password,
55 {AutoCommit => 0, RaiseError => 1, PrintError => 0}
56 );
57 If a parameter is not given, the connect() method will first look for
59 specific environment variables, and then fall back to hard-coded
60 defaults:
61 parameter environment variable hard coded default
63 ------------------------------------------------------
64 host PGHOST local domain socket
65 hostaddr PGHOSTADDR local domain socket
66 port PGPORT 5432
67 dbname* PGDATABASE current userid
68 username PGUSER current userid
69 password PGPASSWORD (none)
70 options PGOPTIONS (none)
71 service PGSERVICE (none)
72 sslmode PGSSLMODE (none)
73 * May also use the aliases "db" or "database"
75 If the username and password values passed via "connect()" are
77 undefined (as opposed to merely being empty strings), DBI will use the
78 environment variables DBI_USER and DBI_PASS if they exist.
79 You can also connect by using a service connection file, which is named
81 pg_service.conf. The location of this file can be controlled by setting
82 the PGSYSCONFDIR environment variable. To use one of the named services
83 within the file, set the name by using either the service parameter or
84 the environment variable PGSERVICE. Note that when connecting this way,
85 only the minimum parameters should be used. For example, to connect to
86 a service named "zephyr", you could use:
87 $dbh = DBI->connect("dbi:Pg:service=zephyr", '', '');
89 You could also set $ENV{PGSERVICE} to "zephyr" and connect like this:
91 $dbh = DBI->connect("dbi:Pg:", '', '');
93 The format of the pg_service.conf file is simply a bracketed service
95 name, followed by one parameter per line in the format name=value. For
96 example:
97 [zephyr]
99 dbname=winds
100 user=wisp
101 password=W$2Hc00YSgP
102 port=6543
103 There are four valid arguments to the sslmode parameter, which controls
105 whether to use SSL to connect to the database:
106 · disable: SSL connections are never used
108 · allow: try non-SSL, then SSL
110 · prefer: try SSL, then non-SSL
112 · require: connect only with SSL
114 You can also connect using sockets in a specific directory. This may be
116 needed if the server you are connecting to has a different default
117 socket directory from the one used to compile DBD::Pg. Use the
118 complete path to the socket directory as the name of the host, like
119 this:
120 $dbh = DBI->connect('dbi:Pg:dbname=foo;host=/var/tmp/socket',
122 $username,
123 $password,
124 {AutoCommit => 0, RaiseError => 1});
125 The attribute hash can also contain a key named "dbd_verbose", which
127 simply calls "$dbh->trace('DBD')" after the handle is created. This
128 attribute is not recommended, as it is clearer to simply explicitly
129 call "trace" explicitly in your script.
130 connect_cached
132 $dbh = DBI->connect_cached("dbi:Pg:dbname=$dbname", $username, $password, \%options);
134 Implemented by DBI, no driver-specific impact.
136 data_sources
138 @data_sources = DBI->data_sources('Pg');
140 @data_sources = $dbh->data_sources();
141 Returns a list of available databases. Unless the environment variable
143 "DBI_DSN" is set, a connection will be attempted to the database
144 "template1". The normal connection environment variables also apply,
145 such as "PGHOST", "PGPORT", "DBI_USER", "DBI_PASS", and "PGSERVICE".
146 You can also pass in options to add to the connection string For
148 example, to specify an alternate port and host:
149 @data_sources = DBI->data_sources('Pg', 'port=5824;host=example.com');
151 or:
153 @data_sources = $dbh->data_sources('port=5824;host=example.com');
155 Methods Common To All Handles
157 For all of the methods below, $h can be either a database handle ($dbh)
158 or a statement handle ($sth). Note that $dbh and $sth can be replaced
159 with any variable name you choose: these are just the names most often
160 used. Another common variable used in this documentation is $rv, which
161 stands for "return value".
162 err
164 $rv = $h->err;
166 Returns the error code from the last method called. For the connect
168 method it returns "PQstatus", which is a number used by libpq (the
169 Postgres connection library). A value of 0 indicates no error
170 (CONNECTION_OK), while any other number indicates a failed connection.
171 The only other number commonly seen is 1 (CONNECTION_BAD). See the
172 libpq documentation for the complete list of return codes.
173 In all other non-connect methods "$h->err" returns the "PQresultStatus"
175 of the current handle. This is a number used by libpq and is one of:
176 0 Empty query string
178 1 A command that returns no data successfully completed.
179 2 A command that returns data sucessfully completed.
180 3 A COPY OUT command is still in progress.
181 4 A COPY IN command is still in progress.
182 5 A bad response was received from the backend.
183 6 A nonfatal error occurred (a notice or warning message)
184 7 A fatal error was returned: the last query failed.
185 errstr
187 $str = $h->errstr;
189 Returns the last error that was reported by Postgres. This message is
191 affected by the "pg_errorlevel" setting.
192 state
194 $str = $h->state;
196 Returns a five-character "SQLSTATE" code. Success is indicated by a
198 00000 code, which gets mapped to an empty string by DBI. A code of
199 "S8006" indicates a connection failure, usually because the connection
200 to the Postgres server has been lost.
201 While this method can be called as either "$sth->state" or
203 "$dbh->state", it is usually clearer to always use "$dbh->state".
204 The list of codes used by PostgreSQL can be found at:
206 http://www.postgresql.org/docs/current/static/errcodes-appendix.html
207 <http://www.postgresql.org/docs/current/static/errcodes-appendix.html>
208 Note that these codes are part of the SQL standard and only a small
210 number of them will be used by PostgreSQL.
211 Common codes:
213 00000 Successful completion
215 25P01 No active SQL transaction
216 25P02 In failed SQL transaction
217 S8006 Connection failure
218 trace
220 $h->trace($trace_settings);
222 $h->trace($trace_settings, $trace_filename);
223 $trace_settings = $h->trace;
224 Changes the trace settings on a database or statement handle. The
226 optional second argument specifies a file to write the trace
227 information to. If no filename is given, the information is written to
228 STDERR. Note that tracing can be set globally as well by setting
229 "DBI->trace", or by using the environment variable DBI_TRACE.
230 The value is either a numeric level or a named flag. For the flags that
232 DBD::Pg uses, see parse_trace_flag.
233 trace_msg
235 $h->trace_msg($message_text);
237 $h->trace_msg($message_text, $min_level);
238 Writes a message to the current trace output (as set by the "trace"
240 method). If a second argument is given, the message is only written if
241 the current tracing level is equal to or greater than the $min_level.
242 parse_trace_flag and parse_trace_flags
244 $h->trace($h->parse_trace_flags('SQL|pglibpq'));
246 $h->trace($h->parse_trace_flags('1|pgstart'));
247 ## Simpler:
249 $h->trace('SQL|pglibpq');
250 $h->trace('1|pgstart');
251 my $value = DBD::Pg->parse_trace_flag('pglibpq');
253 DBI->trace($value);
254 The parse_trace_flags method is used to convert one or more named flags
256 to a number which can passed to the "trace" method. DBD::Pg currently
257 supports the DBI-specific flag, "SQL", as well as the ones listed
258 below.
259 Flags can be combined by using the parse_trace_flags method, which
261 simply calls "parse_trace_flag" on each item and combines them.
262 Sometimes you may wish to turn the tracing on before you connect to the
264 database. The second example above shows a way of doing this: the call
265 to "DBD::Pg->parse_trace_flags" provides a number than can be fed to
266 "DBI->trace" before you create a database handle.
267 DBD::Pg supports the following trace flags:
269 SQL Outputs all SQL statements. Note that the output provided will not
271 necessarily be in a form suitable to passing directly to Postgres,
272 as server-side prepared statements are used extensively by DBD::Pg.
273 For maximum portability of output (but with a potential performance
274 hit), use with "$dbh->{pg_server_prepare} = 0".
275 DBD Turns on all non-DBI flags, in other words, only the ones that are
277 specific to DBD::Pg (all those below which start with the letters
278 'pg').
279 pglibpq
281 Outputs the name of each libpq function (without arguments)
282 immediately before running it. This is a good way to trace the flow
283 of your program at a low level. This information is also output if
284 the trace level is set to 4 or greater.
285 pgstart
287 Outputs the name of each internal DBD::Pg function, and other
288 information such as the function arguments or important global
289 variables, as each function starts. This information is also output
290 if the trace level is set to 4 or greater.
291 pgend
293 Outputs a simple message at the very end of each internal DBD::Pg
294 function. This is also output if the trace level is set to 4 or
295 greater.
296 pgprefix
298 Forces each line of trace output to begin with the string "dbdpg:
299 ". This helps to differentiate it from the normal DBI trace output.
300 pglogin
302 Outputs a message showing the connection string right before a new
303 database connection is attempted, a message when the connection was
304 successful, and a message right after the database has been
305 disconnected. Also output if trace level is 5 or greater.
306 func
308 DBD::Pg uses the "func" method to support a variety of functions. Note
310 that the name of the function comes last, after the arguments.
311 table_attributes
313 $attrs = $dbh->func($table, 'table_attributes');
314 Use of the tables_attributes function is no longer recommended.
316 Instead, you can use the more portable "column_info" and
317 "primary_key" methods to access the same information.
318 The table_attributes method returns, for the given table argument,
320 a reference to an array of hashes, each of which contains the
321 following keys:
322 NAME attribute name
324 TYPE attribute type
325 SIZE attribute size (-1 for variable size)
326 NULLABLE flag nullable
327 DEFAULT default value
328 CONSTRAINT constraint
329 PRIMARY_KEY flag is_primary_key
330 REMARKS attribute description
331 pg_lo_creat
333 $lobjId = $dbh->pg_lo_creat($mode);
334 Creates a new large object and returns the object-id. $mode is a
336 bitmask describing read and write access to the new object. This
337 setting is ignored since Postgres version 8.1. For backwards
338 compatibility, however, you should set a valid mode anyway (see
339 "pg_lo_open" for a list of valid modes).
340 Upon failure it returns "undef". This function cannot be used if
342 AutoCommit is enabled.
343 The old way of calling large objects functions is deprecated:
345 $dbh->func(.., 'lo_);
346 lo_open
348 $lobj_fd = $dbh->pg_lo_open($lobjId, $mode);
349 Opens an existing large object and returns an object-descriptor for
351 use in subsequent "lo_*" calls. $mode is a bitmask describing read
352 and write access to the opened object. It may be one of:
353 $dbh->{pg_INV_READ}
355 $dbh->{pg_INV_WRITE}
356 $dbh->{pg_INV_READ} | $dbh->{pg_INV_WRITE}
357 "pg_INV_WRITE" and "pg_INV_WRITE | pg_INV_READ" modes are
359 identical; in both modes, the large object can be read from or
360 written to. Reading from the object will provide the object as
361 written in other committed transactions, along with any writes
362 performed by the current transaction. Objects opened with
363 "pg_INV_READ" cannot be written to. Reading from this object will
364 provide the stored data at the time of the transaction snapshot
365 which was active when "lo_write" was called.
366 Returns "undef" upon failure. Note that 0 is a perfectly correct
368 (and common) object descriptor! This function cannot be used if
369 AutoCommit is enabled.
370 lo_write
372 $nbytes = $dbh->pg_lo_write($lobj_fd, $buffer, $len);
373 Writes $len bytes of c<$buffer> into the large object $lobj_fd.
375 Returns the number of bytes written and "undef" upon failure. This
376 function cannot be used if AutoCommit is enabled.
377 lo_read
379 $nbytes = $dbh->pg_lo_read($lobj_fd, $buffer, $len);
380 Reads $len bytes into c<$buffer> from large object $lobj_fd.
382 Returns the number of bytes read and "undef" upon failure. This
383 function cannot be used if AutoCommit is enabled.
384 lo_lseek
386 $loc = $dbh->pg_lo_lseek($lobj_fd, $offset, $whence);
387 Changes the current read or write location on the large object
389 $obj_id. Currently $whence can only be 0 (which is L_SET). Returns
390 the current location and "undef" upon failure. This function cannot
391 be used if AutoCommit is enabled.
392 lo_tell
394 $loc = $dbh->pg_lo_tell($lobj_fd);
395 Returns the current read or write location on the large object
397 $lobj_fd and "undef" upon failure. This function cannot be used if
398 AutoCommit is enabled.
399 lo_close
401 $lobj_fd = $dbh->pg_lo_close($lobj_fd);
402 Closes an existing large object. Returns true upon success and
404 false upon failure. This function cannot be used if AutoCommit is
405 enabled.
406 lo_unlink
408 $ret = $dbh->pg_lo_unlink($lobjId);
409 Deletes an existing large object. Returns true upon success and
411 false upon failure. This function cannot be used if AutoCommit is
412 enabled.
413 lo_import
415 $lobjId = $dbh->pg_lo_import($filename);
416 Imports a Unix file as a large object and returns the object id of
418 the new object or "undef" upon failure.
419 lo_import_with_oid
421 $lobjId = $dbh->pg_lo_import($filename, $OID);
422 Same as lo_import, but attempts to use the supplied OID as the
424 large object number. If this number is 0, it falls back to the
425 behavior of lo_import (which assigns the next available OID).
426 This is only available when DBD::Pg is compiled against a Postgres
428 server version 8.4 or later.
429 lo_export
431 $ret = $dbh->pg_lo_export($lobjId, $filename);
432 Exports a large object into a Unix file. Returns false upon
434 failure, true otherwise.
435 getfd
437 $fd = $dbh->func('getfd');
438 Deprecated, use $dbh->{pg_socket} instead.
440 private_attribute_info
442 $hashref = $dbh->private_attribute_info();
444 $hashref = $sth->private_attribute_info();
445 Returns a hash of all private attributes used by DBD::Pg, for either a
447 database or a statement handle. Currently, all the hash values are
448 undef.
449
451 InactiveDestroy (boolean)
452 If set to true, then the "disconnect" method will not be automatically
454 called when the database handle goes out of scope. This is required if
455 you are forking, and even then you must tread carefully and ensure that
456 either the parent or the child (but not both!) handles all database
457 calls from that point forwards, so that messages from the Postgres
458 backend are only handled by one of the processes. If you don't set
459 things up properly, you will see messages such as "server closed the
460 connection unexpectedly", and "message type 0x32 arrived from server
461 while idle". The best solution is to either have the child process
462 reconnect to the database with a fresh database handle, or to rewrite
463 your application not to use use forking. See the section on
464 "Asynchronous Queries" for a way to have your script continue to work
465 while the database is processing a request.
466 RaiseError (boolean, inherited)
468 Forces errors to always raise an exception. Although it defaults to
470 off, it is recommended that this be turned on, as the alternative is to
471 check the return value of every method (prepare, execute, fetch, etc.)
472 manually, which is easy to forget to do.
473 PrintError (boolean, inherited)
475 Forces database errors to also generate warnings, which can then be
477 filtered with methods such as locally redefining $SIG{__WARN__} or
478 using modules such as "CGI::Carp". This attribute is on by default.
479 ShowErrorStatement (boolean, inherited)
481 Appends information about the current statement to error messages. If
483 placeholder information is available, adds that as well. Defaults to
484 false.
485 Warn (boolean, inherited)
487 Enables warnings. This is on by default, and should only be turned off
489 in a local block for a short a time only when absolutely needed.
490 Executed (boolean, read-only)
492 Indicates if a handle has been executed. For database handles, this
494 value is true after the "do" method has been called, or when one of the
495 child statement handles has issued an "execute". Issuing a "commit" or
496 "rollback" always resets the attribute to false for database handles.
497 For statement handles, any call to "execute" or its variants will flip
498 the value to true for the lifetime of the statement handle.
499 TraceLevel (integer, inherited)
501 Sets the trace level, similar to the "trace" method. See the sections
503 on "trace" and "parse_trace_flag" for more details.
504 Active (boolean, read-only)
506 Indicates if a handle is active or not. For database handles, this
508 indicates if the database has been disconnected or not. For statement
509 handles, it indicates if all the data has been fetched yet or not. Use
510 of this attribute is not encouraged.
511 Kids (integer, read-only)
513 Returns the number of child processes created for each handle type. For
515 a driver handle, indicates the number of database handles created. For
516 a database handle, indicates the number of statement handles created.
517 For statement handles, it always returns zero, because statement
518 handles do not create kids.
519 ActiveKids (integer, read-only)
521 Same as "Kids", but only returns those that are active.
523 CachedKids (hash ref)
525 Returns a hashref of handles. If called on a database handle, returns
527 all statement handles created by use of the "prepare_cached" method. If
528 called on a driver handle, returns all database handles created by the
529 "connect_cached" method.
530 ChildHandles (array ref)
532 Implemented by DBI, no driver-specific impact.
534 PrintWarn (boolean, inherited)
536 Implemented by DBI, no driver-specific impact.
538 HandleError (boolean, inherited)
540 Implemented by DBI, no driver-specific impact.
542 HandleSetErr (code ref, inherited)
544 Implemented by DBI, no driver-specific impact.
546 ErrCount (unsigned integer)
548 Implemented by DBI, no driver-specific impact.
550 FetchHashKeyName (string, inherited)
552 Implemented by DBI, no driver-specific impact.
554 ChopBlanks (boolean, inherited)
556 Supported by DBD::Pg as proposed by DBI. This method is similar to the
558 SQL function "RTRIM".
559 Taint (boolean, inherited)
561 Implemented by DBI, no driver-specific impact.
563 TaintIn (boolean, inherited)
565 Implemented by DBI, no driver-specific impact.
567 TaintOut (boolean, inherited)
569 Implemented by DBI, no driver-specific impact.
571 Profile (inherited)
573 Implemented by DBI, no driver-specific impact.
575 Type (scalar)
577 Returns "dr" for a driver handle, "db" for a database handle, and "st"
579 for a statement handle. Should be rarely needed.
580 LongReadLen
582 Not used by DBD::Pg
584 LongTruncOk
586 Not used by DBD::Pg
588 CompatMode
590 Not used by DBD::Pg
592
594 Database Handle Methods
595 selectall_arrayref
596 $ary_ref = $dbh->selectall_arrayref($sql);
598 $ary_ref = $dbh->selectall_arrayref($sql, \%attr);
599 $ary_ref = $dbh->selectall_arrayref($sql, \%attr, @bind_values);
600 Returns a reference to an array containing the rows returned by
602 preparing and executing the SQL string. See the DBI documentation for
603 full details.
604 selectall_hashref
606 $hash_ref = $dbh->selectall_hashref($sql, $key_field);
608 Returns a reference to a hash containing the rows returned by preparing
610 and executing the SQL string. See the DBI documentation for full
611 details.
612 selectcol_arrayref
614 $ary_ref = $dbh->selectcol_arrayref($sql, \%attr, @bind_values);
616 Returns a reference to an array containing the first column from each
618 rows returned by preparing and executing the SQL string. It is possible
619 to specify exactly which columns to return. See the DBI documentation
620 for full details.
621 prepare
623 $sth = $dbh->prepare($statement, \%attr);
625 WARNING: DBD::Pg now (as of version 1.40) uses true prepared statements
627 by sending them to the backend to be prepared by the Postgres server.
628 Statements that were legal before may no longer work. See below for
629 details.
630 The prepare method prepares a statement for later execution. PostgreSQL
632 supports prepared statements, which enables DBD::Pg to only send the
633 query once, and simply send the arguments for every subsequent call to
634 "execute". DBD::Pg can use these server-side prepared statements, or
635 it can just send the entire query to the server each time. The best way
636 is automatically chosen for each query. This will be sufficient for
637 most users: keep reading for a more detailed explanation and some
638 optional flags.
639 Queries that do not begin with the word "SELECT", "INSERT", "UPDATE",
641 or "DELETE" are never sent as server-side prepared statements.
642 Deciding whether or not to use prepared statements depends on many
644 factors, but you can force them to be used or not used by using the
645 "pg_server_prepare" attribute when calling "prepare". Setting this to
646 "0" means to never use prepared statements. Setting "pg_server_prepare"
647 to "1" means that prepared statements should be used whenever possible.
648 This is the default when connected to Postgres servers version 8.0 or
649 higher. Servers that are version 7.4 get a special default value of
650 "2", because server-side statements were only partially supported in
651 that version. In this case, it only uses server-side prepares if all
652 parameters are specifically bound.
653 The "pg_server_prepare" attribute can also be set at connection time
655 like so:
656 $dbh = DBI->connect($DBNAME, $DBUSER, $DBPASS,
658 { AutoCommit => 0,
659 RaiseError => 1,
660 pg_server_prepare => 0,
661 });
662 or you may set it after your database handle is created:
664 $dbh->{pg_server_prepare} = 1;
666 To enable it for just one particular statement:
668 $sth = $dbh->prepare("SELECT id FROM mytable WHERE val = ?",
670 { pg_server_prepare => 1 });
671 You can even toggle between the two as you go:
673 $sth->{pg_server_prepare} = 1;
675 $sth->execute(22);
676 $sth->{pg_server_prepare} = 0;
677 $sth->execute(44);
678 $sth->{pg_server_prepare} = 1;
679 $sth->execute(66);
680 In the above example, the first execute will use the previously
682 prepared statement. The second execute will not, but will build the
683 query into a single string and send it to the server. The third one
684 will act like the first and only send the arguments. Even if you
685 toggle back and forth, a statement is only prepared once.
686 Using prepared statements is in theory quite a bit faster: not only
688 does the PostgreSQL backend only have to prepare the query only once,
689 but DBD::Pg no longer has to worry about quoting each value before
690 sending it to the server.
691 However, there are some drawbacks. The server cannot always choose the
693 ideal parse plan because it will not know the arguments before hand.
694 But for most situations in which you will be executing similar data
695 many times, the default plan will probably work out well. Programs such
696 as PgBouncer which cache connections at a low level should not use
697 prepared statements via DBD::Pg, or must take extra care in the
698 application to account for the fact that prepared statements are not
699 shared across database connections. Further discussion on this subject
700 is beyond the scope of this documentation: please consult the pgsql-
701 performance mailing list,
702 http://archives.postgresql.org/pgsql-performance/
703 <http://archives.postgresql.org/pgsql-performance/>
704 Only certain commands will be sent to a server-side prepare: currently
706 these include "SELECT", "INSERT", "UPDATE", and "DELETE". DBD::Pg uses
707 a simple naming scheme for the prepared statements themselves:
708 dbdpg_XY_Z, where Y is the current PID, X is either 'p' or 'n'
709 (depending on if the PID is a positive or negative number), and Z is a
710 number that starts at 1 and increases each time a new statement is
711 prepared. This number is tracked at the database handle level, so
712 multiple statement handles will not collide.
713 You cannot send more than one command at a time in the same prepare
715 command (by separating them with semi-colons) when using server-side
716 prepares.
717 The actual "PREPARE" is usually not performed until the first execute
719 is called, due to the fact that information on the data types (provided
720 by "bind_param") may be provided after the prepare but before the
721 execute.
722 A server-side prepare may happen before the first "execute", but only
724 if the server can handle the server-side prepare, and the statement
725 contains no placeholders. It will also be prepared if the
726 "pg_prepare_now" attribute is passed in and set to a true value.
727 Similarly, the "pg_prepare_now" attribute can be set to 0 to ensure
728 that the statement is not prepared immediately, although the cases in
729 which you would want this are very rare. Finally, you can set the
730 default behavior of all prepare statements by setting the
731 "pg_prepare_now" attribute on the database handle:
732 $dbh->{pg_prepare_now} = 1;
734 The following two examples will be prepared right away:
736 $sth->prepare("SELECT 123"); ## no placeholders
738 $sth->prepare("SELECT 123, ?", {pg_prepare_now => 1});
740 The following two examples will NOT be prepared right away:
742 $sth->prepare("SELECT 123, ?"); ## has a placeholder
744 $sth->prepare("SELECT 123", {pg_prepare_now => 0});
746 There are times when you may want to prepare a statement yourself. To
748 do this, simply send the "PREPARE" statement directly to the server
749 (e.g. with the "do" method). Create a statement handle and set the
750 prepared name via the "pg_prepare_name" attribute. The statement handle
751 can be created with a dummy statement, as it will not be executed.
752 However, it should have the same number of placeholders as your
753 prepared statement. Example:
754 $dbh->do('PREPARE mystat AS SELECT COUNT(*) FROM pg_class WHERE reltuples < ?');
756 $sth = $dbh->prepare('SELECT ?');
757 $sth->bind_param(1, 1, SQL_INTEGER);
758 $sth->{pg_prepare_name} = 'mystat';
759 $sth->execute(123);
760 The above will run the equivalent of this query on the backend:
762 EXECUTE mystat(123);
764 which is the equivalent of:
766 SELECT COUNT(*) FROM pg_class WHERE reltuples < 123;
768 You can force DBD::Pg to send your query directly to the server by
770 adding the "pg_direct" attribute to your prepare call. This is not
771 recommended, but is added just in case you need it.
772 Placeholders
774 There are three types of placeholders that can be used in DBD::Pg. The
776 first is the "question mark" type, in which each placeholder is
777 represented by a single question mark character. This is the method
778 recommended by the DBI specs and is the most portable. Each question
779 mark is internally replaced by a "dollar sign number" in the order in
780 which they appear in the query (important when using "bind_param").
781 The method second type of placeholder is "dollar sign numbers". This is
783 the method that Postgres uses internally and is overall probably the
784 best method to use if you do not need compatibility with other database
785 systems. DBD::Pg, like PostgreSQL, allows the same number to be used
786 more than once in the query. Numbers must start with "1" and increment
787 by one value (but can appear in any order within the query). If the
788 same number appears more than once in a query, it is treated as a
789 single parameter and all instances are replaced at once. Examples:
790 Not legal:
792 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $2'; # Does not start with 1
794 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $3'; # Missing 2
796 Legal:
798 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $1';
800 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $2';
802 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $2 AND $1'; # legal but confusing
804 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $2 AND reltuples > $1';
806 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $1 AND reltuples > $1';
808 In the final statement above, DBI thinks there is only one placeholder,
810 so this statement will replace both placeholders:
811 $sth->bind_param(1, 2045);
813 While a simple execute with no bind_param calls requires only a single
815 argument as well:
816 $sth->execute(2045);
818 The final placeholder type is "named parameters" in the format ":foo".
820 While this syntax is supported by DBD::Pg, its use is discouraged in
821 favor of dollar-sign numbers.
822 The different types of placeholders cannot be mixed within a statement,
824 but you may use different ones for each statement handle you have. This
825 is confusing at best, so stick to one style within your program.
826 If your queries use operators that contain question marks (e.g. some of
828 the native Postgres geometric operators) or array slices (e.g.
829 "data[100:300]"), you can tell DBD::Pg to ignore any non-dollar sign
830 placeholders by setting the "pg_placeholder_dollaronly" attribute at
831 either the database handle or the statement handle level. Examples:
832 $dbh->{pg_placeholder_dollaronly} = 1;
834 $sth = $dbh->prepare(q{SELECT * FROM mytable WHERE lseg1 ?# lseg2 AND name = $1});
835 $sth->execute('segname');
836 Alternatively, you can set it at prepare time:
838 $sth = $dbh->prepare(q{SELECT * FROM mytable WHERE lseg1 ?-| lseg2 AND name = $1},
840 {pg_placeholder_dollaronly = 1});
841 $sth->execute('segname');
842 prepare_cached
844 $sth = $dbh->prepare_cached($statement, \%attr);
846 Implemented by DBI, no driver-specific impact. This method is most
848 useful when using a server that supports server-side prepares, and you
849 have asked the prepare to happen immediately via the "pg_prepare_now"
850 attribute.
851 do
853 $rv = $dbh->do($statement);
855 $rv = $dbh->do($statement, \%attr);
856 $rv = $dbh->do($statement, \%attr, @bind_values);
857 Prepare and execute a single statement. Returns the number of rows
859 affected if the query was successful, returns undef if an error
860 occurred, and returns -1 if the number of rows is unknown or not
861 available. Note that this method will return 0E0 instead of 0 for 'no
862 rows were affected', in order to always return a true value if no error
863 occurred.
864 If neither "\%attr" nor @bind_values is given, the query will be sent
866 directly to the server without the overhead of internally creating a
867 statement handle and running prepare and execute, for a measurable
868 speed increase.
869 Note that an empty statement (a string with no length) will not be
871 passed to the server; if you want a simple test, use "SELECT 123" or
872 the "ping" method.
873 last_insert_id
875 $rv = $dbh->last_insert_id(undef, $schema, $table, undef);
877 $rv = $dbh->last_insert_id(undef, $schema, $table, undef, {sequence => $seqname});
878 Attempts to return the id of the last value to be inserted into a
880 table. You can either provide a sequence name (preferred) or provide a
881 table name with optional schema, and DBD::Pg will attempt to find the
882 sequence itself. The current value of the sequence is returned by a
883 call to the "CURRVAL()" PostgreSQL function. This will fail if the
884 sequence has not yet been used in the current database connection.
885 If you do not know the name of the sequence, you can provide a table
887 name and DBD::Pg will attempt to return the correct value. To do this,
888 there must be at least one column in the table with a "NOT NULL"
889 constraint, that has a unique constraint, and which uses a sequence as
890 a default value. If more than one column meets these conditions, the
891 primary key will be used. This involves some looking up of things in
892 the system table, so DBD::Pg will cache the sequence name for
893 subsequent calls. If you need to disable this caching for some reason,
894 (such as the sequence name changing), you can control it by adding
895 "pg_cache => 0" to the final (hashref) argument for last_insert_id.
896 Please keep in mind that this method is far from foolproof, so make
898 your script use it properly. Specifically, make sure that it is called
899 immediately after the insert, and that the insert does not add a value
900 to the column that is using the sequence as a default value. However,
901 because we are using sequences, you can be sure that the value you got
902 back has not been used by any other process.
903 Some examples:
905 $dbh->do('CREATE SEQUENCE lii_seq START 1');
907 $dbh->do(q{CREATE TABLE lii (
908 foobar INTEGER NOT NULL UNIQUE DEFAULT nextval('lii_seq'),
909 baz VARCHAR)});
910 $SQL = 'INSERT INTO lii(baz) VALUES (?)';
911 $sth = $dbh->prepare($SQL);
912 for (qw(uno dos tres cuatro)) {
913 $sth->execute($_);
914 my $newid = $dbh->last_insert_id(undef,undef,undef,undef,{sequence=>'lii_seq'});
915 print "Last insert id was $newid\n";
916 }
917 If you did not want to worry about the sequence name:
919 $dbh->do('CREATE TABLE lii2 (
921 foobar SERIAL UNIQUE,
922 baz VARCHAR)');
923 $SQL = 'INSERT INTO lii2(baz) VALUES (?)';
924 $sth = $dbh->prepare($SQL);
925 for (qw(uno dos tres cuatro)) {
926 $sth->execute($_);
927 my $newid = $dbh->last_insert_id(undef,undef,"lii2",undef);
928 print "Last insert id was $newid\n";
929 }
930 commit
932 $rv = $dbh->commit;
934 Issues a COMMIT to the server, indicating that the current transaction
936 is finished and that all changes made will be visible to other
937 processes. If AutoCommit is enabled, then a warning is given and no
938 COMMIT is issued. Returns true on success, false on error. See also
939 the the section on "Transactions".
940 rollback
942 $rv = $dbh->rollback;
944 Issues a ROLLBACK to the server, which discards any changes made in the
946 current transaction. If AutoCommit is enabled, then a warning is given
947 and no ROLLBACK is issued. Returns true on success, and false on error.
948 See also the the section on "Transactions".
949 begin_work
951 This method turns on transactions until the next call to "commit" or
953 "rollback", if "AutoCommit" is currently enabled. If it is not enabled,
954 calling begin_work will issue an error. Note that the transaction will
955 not actually begin until the first statement after begin_work is
956 called. Example:
957 $dbh->{AutoCommit} = 1;
959 $dbh->do('INSERT INTO foo VALUES (123)'); ## Changes committed immediately
960 $dbh->begin_work();
961 ## Not in a transaction yet, but AutoCommit is set to 0
962 $dbh->do("INSERT INTO foo VALUES (345)");
964 ## DBD::PG actually issues two statements here:
965 ## BEGIN;
966 ## INSERT INTO foo VALUES (345)
967 ## We are now in a transaction
968 $dbh->commit();
970 ## AutoCommit is now set to 1 again
971 disconnect
973 $rv = $dbh->disconnect;
975 Disconnects from the Postgres database. Any uncommitted changes will be
977 rolled back upon disconnection. It's good policy to always explicitly
978 call commit or rollback at some point before disconnecting, rather than
979 relying on the default rollback behavior.
980 This method may give warnings about "disconnect invalidates X active
982 statement handle(s)". This means that you called "$sth->execute()" but
983 did not finish fetching all the rows from them. To avoid seeing this
984 warning, either fetch all the rows or call "$sth->finish()" for each
985 executed statement handle.
986 If the script exits before disconnect is called (or, more precisely, if
988 the database handle is no longer referenced by anything), then the
989 database handle's DESTROY method will call the rollback() and
990 disconnect() methods automatically. It is best to explicitly disconnect
991 rather than rely on this behavior.
992 quote
994 $rv = $dbh->quote($value, $data_type);
996 This module implements its own "quote" method. For simple string types,
998 both backslashes and single quotes are doubled. You may also quote
999 arrayrefs and receive a string suitable for passing into Postgres array
1000 columns.
1001 If the value contains backslashes, and the server is version 8.1 or
1003 higher, then the escaped string syntax will be used (which places a
1004 capital E before the first single quote). This syntax is always used
1005 when quoting bytea values on servers 8.1 and higher.
1006 The "data_type" argument is optional and should be one of the type
1008 constants exported by DBD::Pg (such as PG_BYTEA). In addition to
1009 string, bytea, char, bool, and other standard types, the following
1010 geometric types are supported: point, line, lseg, box, path, polygon,
1011 and circle (PG_POINT, PG_LINE, PG_LSEG, PG_BOX, PG_PATH, PG_POLYGON,
1012 and PG_CIRCLE respectively). To quote a Postgres-specific data type,
1013 you must use a 'hashref' argument like so:
1014 my $quotedval = $dbh->quote($value, { pg_type => PG_VARCHAR });
1016 NOTE: The undocumented (and invalid) support for the "SQL_BINARY" data
1018 type is officially deprecated. Use "PG_BYTEA" with "bind_param()"
1019 instead:
1020 $rv = $sth->bind_param($param_num, $bind_value,
1022 { pg_type => PG_BYTEA });
1023 quote_identifier
1025 $string = $dbh->quote_identifier( $name );
1027 $string = $dbh->quote_identifier( undef, $schema, $table);
1028 Returns a quoted version of the supplied string, which is commonly a
1030 schema, table, or column name. The three argument form will return the
1031 schema and the table together, separated by a dot. Examples:
1032 print $dbh->quote_identifier('grapefruit'); ## Prints: "grapefruit"
1034 print $dbh->quote_identifier('juicy fruit'); ## Prints: "juicy fruit"
1036 print $dbh->quote_identifier(undef, 'public', 'pg_proc');
1038 ## Prints: "public"."pg_proc"
1039 pg_notifies
1041 $ret = $dbh->pg_notifies;
1043 Looks for any asynchronous notifications received and returns either
1045 "undef" or a reference to a three-element array consisting of an event
1046 name, the PID of the backend that sent the NOTIFY command, and the
1047 optional payload string. Note that this does not check if the
1048 connection to the database is still valid first - for that, use the
1049 c<ping> method. You may need to commit if not in autocommit mode - new
1050 notices will not be picked up while in the middle of a transaction. An
1051 example:
1052 $dbh->do("LISTEN abc");
1054 $dbh->do("LISTEN def");
1055 ## Hang around until we get the message we want
1057 LISTENLOOP: {
1058 while (my $notify = $dbh->pg_notifies) {
1059 my ($name, $pid, $payload) = @$notify;
1060 print qq{I received notice "$name" from PID $pid, payload was "$payload"\n};
1061 ## Do something based on the notice received
1062 }
1063 $dbh->ping() or die qq{Ping failed!};
1064 $dbh->commit();
1065 sleep(5);
1066 redo;
1067 }
1068 Payloads will always be an empty string unless you are connecting to a
1070 Postgres server version 9.0 or higher.
1071 ping
1073 $rv = $dbh->ping;
1075 This "ping" method is used to check the validity of a database handle.
1077 The value returned is either 0, indicating that the connection is no
1078 longer valid, or a positive integer, indicating the following:
1079 Value Meaning
1081 --------------------------------------------------
1082 1 Database is idle (not in a transaction)
1083 2 Database is active, there is a command in progress (usually seen after a COPY command)
1084 3 Database is idle within a transaction
1085 4 Database is idle, within a failed transaction
1086 Additional information on why a handle is not valid can be obtained by
1088 using the "pg_ping" method.
1089 pg_ping
1091 $rv = $dbh->pg_ping;
1093 This is a DBD::Pg-specific extension to the "ping" method. This will
1095 check the validity of a database handle in exactly the same way as
1096 "ping", but instead of returning a 0 for an invalid connection, it will
1097 return a negative number. So in addition to returning the positive
1098 numbers documented for "ping", it may also return the following:
1099 Value Meaning
1101 --------------------------------------------------
1102 -1 There is no connection to the database at all (e.g. after C<disconnect>)
1103 -2 An unknown transaction status was returned (e.g. after forking)
1104 -3 The handle exists, but no data was returned from a test query.
1105 In practice, you should only ever see -1 and -2.
1107 get_info
1109 $value = $dbh->get_info($info_type);
1111 Supports a very large set (> 250) of the information types, including
1113 the minimum recommended by DBI.
1114 table_info
1116 $sth = $dbh->table_info(undef, $schema, $table, $type);
1118 Returns all tables and views visible to the current user. The schema
1120 and table arguments will do a "LIKE" search if a percent sign ("%") or
1121 an underscore ("_") is detected in the argument. The $type argument
1122 accepts a value of either "TABLE" or "VIEW" (using both is the default
1123 action). Note that a statement handle is returned, and not a direct
1124 list of tables. See the examples below for ways to handle this.
1125 The following fields are returned:
1127 TABLE_CAT: Always NULL, as Postgres does not have the concept of
1129 catalogs.
1130 TABLE_SCHEM: The name of the schema that the table or view is in.
1132 TABLE_NAME: The name of the table or view.
1134 TABLE_TYPE: The type of object returned. Will be one of "TABLE",
1136 "VIEW", or "SYSTEM TABLE".
1137 The TABLE_SCHEM and TABLE_NAME will be quoted via "quote_ident()".
1139 Two additional fields specific to DBD::Pg are returned:
1141 pg_schema: the unquoted name of the schema
1143 pg_table: the unquoted name of the table
1145 If your database supports tablespaces (version 8.0 or greater), two
1147 additional DBD::Pg specific fields are returned:
1148 pg_tablespace_name: the name of the tablespace the table is in
1150 pg_tablespace_location: the location of the tablespace the table is in
1152 Tables that have not been assigned to a particular tablespace (or
1154 views) will return NULL ("undef") for both of the above field.
1155 Rows are returned alphabetically, with all tables first, and then all
1157 views.
1158 Examples of use:
1160 ## Display all tables and views in the public schema:
1162 $sth = $dbh->table_info('', 'public', undef, undef);
1163 for my $rel (@{$sth->fetchall_arrayref({})}) {
1164 print "$rel->{TABLE_TYPE} name is $rel->{TABLE_NAME}\n";
1165 }
1166 # Display the schema of all tables named 'foo':
1169 $sth = $dbh->table_info('', undef, 'foo', 'TABLE');
1170 for my $rel (@{$sth->fetchall_arrayref({})}) {
1171 print "Table name is $rel->{TABLE_SCHEM}.$rel->{TABLE_NAME}\n";
1172 }
1173 column_info
1175 $sth = $dbh->column_info( undef, $schema, $table, $column );
1177 Supported by this driver as proposed by DBI with the follow exceptions.
1179 These fields are currently always returned with NULL ("undef") values:
1180 TABLE_CAT
1182 BUFFER_LENGTH
1183 DECIMAL_DIGITS
1184 NUM_PREC_RADIX
1185 SQL_DATA_TYPE
1186 SQL_DATETIME_SUB
1187 CHAR_OCTET_LENGTH
1188 Also, six additional non-standard fields are returned:
1190 pg_type: data type with additional info i.e. "character varying(20)"
1192 pg_constraint: holds column constraint definition
1194 pg_schema: the unquoted name of the schema
1196 pg_table: the unquoted name of the table
1198 pg_column: the unquoted name of the column
1200 pg_enum_values: an array reference of allowed values for an enum column
1202 Note that the TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME fields all
1204 return output wrapped in quote_ident(). If you need the unquoted
1205 version, use the pg_ fields above.
1206 primary_key_info
1208 $sth = $dbh->primary_key_info( undef, $schema, $table, \%attr );
1210 Supported by this driver as proposed by DBI. There are no search
1212 patterns allowed, but leaving the $schema argument blank will cause the
1213 first table found in the schema search path to be used. An additional
1214 field, "DATA_TYPE", is returned and shows the data type for each of the
1215 arguments in the "COLUMN_NAME" field.
1216 This method will also return tablespace information for servers that
1218 support tablespaces. See the "table_info" entry for more information.
1219 The five additional custom fields returned are:
1221 pg_tablespace_name: name of the tablespace, if any
1223 pg_tablespace_location: location of the tablespace
1225 pg_schema: the unquoted name of the schema
1227 pg_table: the unquoted name of the table
1229 pg_column: the unquoted name of the column
1231 In addition to the standard format of returning one row for each column
1233 found for the primary key, you can pass the "pg_onerow" attribute to
1234 force a single row to be used. If the primary key has multiple columns,
1235 the "KEY_SEQ", "COLUMN_NAME", and "DATA_TYPE" fields will return a
1236 comma-delimited string. If the "pg_onerow" attribute is set to "2", the
1237 fields will be returned as an arrayref, which can be useful when
1238 multiple columns are involved:
1239 $sth = $dbh->primary_key_info('', '', 'dbd_pg_test', {pg_onerow => 2});
1241 if (defined $sth) {
1242 my $pk = $sth->fetchall_arrayref()->[0];
1243 print "Table $pk->[2] has a primary key on these columns:\n";
1244 for (my $x=0; defined $pk->[3][$x]; $x++) {
1245 print "Column: $pk->[3][$x] (data type: $pk->[6][$x])\n";
1246 }
1247 }
1248 primary_key
1250 @key_column_names = $dbh->primary_key(undef, $schema, $table);
1252 Simple interface to the "primary_key_info" method. Returns a list of
1254 the column names that comprise the primary key of the specified table.
1255 The list is in primary key column sequence order. If there is no
1256 primary key then an empty list is returned.
1257 foreign_key_info
1259 $sth = $dbh->foreign_key_info( $pk_catalog, $pk_schema, $pk_table,
1261 $fk_catalog, $fk_schema, $fk_table );
1262 Supported by this driver as proposed by DBI, using the SQL/CLI variant.
1264 There are no search patterns allowed, but leaving the $schema argument
1265 blank will cause the first table found in the schema search path to be
1266 used. Two additional fields, "UK_DATA_TYPE" and "FK_DATA_TYPE", are
1267 returned to show the data type for the unique and foreign key columns.
1268 Foreign keys that have no named constraint (where the referenced column
1269 only has an unique index) will return "undef" for the "UK_NAME" field.
1270 statistics_info
1272 $sth = $dbh->statistics_info( undef, $schema, $table, $unique_only, $quick );
1274 Returns a statement handle that can be fetched from to give statistics
1276 information on a specific table and its indexes. The $table argument is
1277 mandatory. The $schema argument is optional but recommended. The
1278 $unique_only argument, if true, causes only information about unique
1279 indexes to be returned. The $quick argument is not used by DBD::Pg. For
1280 information on the format of the rows returned, please see the DBI
1281 documentation.
1282 tables
1284 @names = $dbh->tables( undef, $schema, $table, $type, \%attr );
1286 Supported by this driver as proposed by DBI. This method returns all
1288 tables and/or views which are visible to the current user: see
1289 "table_info" for more information about the arguments. The name of the
1290 schema appears before the table or view name. This can be turned off by
1291 adding in the "pg_noprefix" attribute:
1292 my @tables = $dbh->tables( '', '', 'dbd_pg_test', '', {pg_noprefix => 1} );
1294 type_info_all
1296 $type_info_all = $dbh->type_info_all;
1298 Supported by this driver as proposed by DBI. Information is only
1300 provided for SQL datatypes and for frequently used datatypes. The
1301 mapping between the PostgreSQL typename and the SQL92 datatype (if
1302 possible) has been done according to the following table:
1303 +---------------+------------------------------------+
1305 | typname | SQL92 |
1306 |---------------+------------------------------------|
1307 | bool | BOOL |
1308 | text | / |
1309 | bpchar | CHAR(n) |
1310 | varchar | VARCHAR(n) |
1311 | int2 | SMALLINT |
1312 | int4 | INT |
1313 | int8 | / |
1314 | money | / |
1315 | float4 | FLOAT(p) p<7=float4, p<16=float8 |
1316 | float8 | REAL |
1317 | abstime | / |
1318 | reltime | / |
1319 | tinterval | / |
1320 | date | / |
1321 | time | / |
1322 | datetime | / |
1323 | timespan | TINTERVAL |
1324 | timestamp | TIMESTAMP |
1325 +---------------+------------------------------------+
1326 type_info
1328 @type_info = $dbh->type_info($data_type);
1330 Returns a list of hash references holding information about one or more
1332 variants of $data_type. See the DBI documentation for more details.
1333 pg_server_trace
1335 $dbh->pg_server_trace($filehandle);
1337 Writes debugging information from the PostgreSQL backend to a file.
1339 This is not related to the DBI "trace" method and you should not use
1340 this method unless you know what you are doing. If you do enable this,
1341 be aware that the file will grow very large, very quick. To stop
1342 logging to the file, use the "pg_server_untrace" method. The first
1343 argument must be a file handle, not a filename. Example:
1344 my $pid = $dbh->{pg_pid};
1346 my $file = "pgbackend.$pid.debug.log";
1347 open(my $fh, ">$file") or die qq{Could not open "$file": $!\n};
1348 $dbh->pg_server_trace($fh);
1349 ## Run code you want to trace here
1350 $dbh->pg_server_untrace;
1351 close($fh);
1352 pg_server_untrace
1354 $dbh->pg_server_untrace;
1356 Stop server logging to a previously opened file.
1358 selectrow_array
1360 @row_ary = $dbh->selectrow_array($sql);
1362 @row_ary = $dbh->selectrow_array($sql, \%attr);
1363 @row_ary = $dbh->selectrow_array($sql, \%attr, @bind_values);
1364 Returns an array of row information after preparing and executing the
1366 provided SQL string. The rows are returned by calling "fetchrow_array".
1367 The string can also be a statement handle generated by a previous
1368 prepare. Note that only the first row of data is returned. If called in
1369 a scalar context, only the first column of the first row is returned.
1370 Because this is not portable, it is not recommended that you use this
1371 method in that way.
1372 selectrow_arrayref
1374 $ary_ref = $dbh->selectrow_arrayref($statement);
1376 $ary_ref = $dbh->selectrow_arrayref($statement, \%attr);
1377 $ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @bind_values);
1378 Exactly the same as "selectrow_array", except that it returns a
1380 reference to an array, by internal use of the "fetchrow_arrayref"
1381 method.
1382 selectrow_hashref
1384 $hash_ref = $dbh->selectrow_hashref($sql);
1386 $hash_ref = $dbh->selectrow_hashref($sql, \%attr);
1387 $hash_ref = $dbh->selectrow_hashref($sql, \%attr, @bind_values);
1388 Exactly the same as "selectrow_array", except that it returns a
1390 reference to an hash, by internal use of the "fetchrow_hashref" method.
1391 clone
1393 $other_dbh = $dbh->clone();
1395 Creates a copy of the database handle by connecting with the same
1397 parameters as the original handle, then trying to merge the attributes.
1398 See the DBI documentation for complete usage.
1399 Database Handle Attributes
1401 AutoCommit (boolean)
1402 Supported by DBD::Pg as proposed by DBI. According to the
1404 classification of DBI, PostgreSQL is a database in which a transaction
1405 must be explicitly started. Without starting a transaction, every
1406 change to the database becomes immediately permanent. The default of
1407 AutoCommit is on, but this may change in the future, so it is highly
1408 recommended that you explicitly set it when calling "connect". For
1409 details see the notes about "Transactions" elsewhere in this document.
1410 pg_bool_tf (boolean)
1412 DBD::Pg specific attribute. If true, boolean values will be returned as
1414 the characters 't' and 'f' instead of '1' and '0'.
1415 ReadOnly (boolean)
1417 $dbh->{ReadOnly} = 1;
1419 Specifies if the current database connection should be in read-only
1421 mode or not. In this mode, changes that change the database are not
1422 allowed and will throw an error. Note: this method will not work if
1423 "AutoCommit" is true. The read-only effect is accomplished by sending a
1424 SET TRANSACTION READ ONLY after every begin. For more details, please
1425 see:
1426 http://www.postgresql.org/docs/current/interactive/sql-set-transaction.html
1428 Please not that this method is not foolproof: there are still ways to
1430 update the database. Consider this a safety net to catch applications
1431 that should not be issuing commands such as INSERT, UPDATE, or DELETE.
1432 This method method requires DBI version 1.55 or better.
1434 pg_server_prepare (integer)
1436 DBD::Pg specific attribute. Indicates if DBD::Pg should attempt to use
1438 server-side prepared statements. The default value, 1, indicates that
1439 prepared statements should be used whenever possible. See the section
1440 on the "prepare" method for more information.
1441 pg_placeholder_dollaronly (boolean)
1443 DBD::Pg specific attribute. Defaults to false. When true, question
1445 marks inside of statements are not treated as placeholders. Useful for
1446 statements that contain unquoted question marks, such as geometric
1447 operators.
1448 pg_enable_utf8 (boolean)
1450 DBD::Pg specific attribute. If true, then the "utf8" flag will be
1452 turned on for returned character data (if the data is valid UTF-8). For
1453 details about the "utf8" flag, see the "Encode" module. This attribute
1454 is only relevant under perl 5.8 and later.
1455 pg_errorlevel (integer)
1457 DBD::Pg specific attribute. Sets the amount of information returned by
1459 the server's error messages. Valid entries are 0, 1, and 2. Any other
1460 number will be forced to the default value of 1.
1461 A value of 0 ("TERSE") will show severity, primary text, and position
1463 only and will usually fit on a single line. A value of 1 ("DEFAULT")
1464 will also show any detail, hint, or context fields. A value of 2
1465 ("VERBOSE") will show all available information.
1466 pg_lib_version (integer, read-only)
1468 DBD::Pg specific attribute. Indicates which version of PostgreSQL that
1470 DBD::Pg was compiled against. In other words, which libraries were
1471 used. Returns a number with major, minor, and revision together;
1472 version 8.1.4 would be returned as 80104.
1473 pg_server_version (integer, read-only)
1475 DBD::Pg specific attribute. Indicates which version of PostgreSQL that
1477 the current database handle is connected to. Returns a number with
1478 major, minor, and revision together; version 8.0.1 would be 80001.
1479 Name (string, read-only)
1481 Returns the name of the current database. This is the same as the DSN,
1483 without the "dbi:Pg:" part. Before version 2.0.0, this only returned
1484 the bare database name (e.g. 'foo'). From version 2.0.0 onwards, it
1485 returns the more correct output (e.g. 'dbname=foo')
1486 Username (string, read-only)
1488 Returns the name of the user connected to the database.
1490 pg_db (string, read-only)
1492 DBD::Pg specific attribute. Returns the name of the current database.
1494 pg_user (string, read-only)
1496 DBD::Pg specific attribute. Returns the name of the user that connected
1498 to the server.
1499 pg_host (string, read-only)
1501 DBD::Pg specific attribute. Returns the host of the current server
1503 connection. Locally connected hosts will return an empty string.
1504 pg_port (integer, read-only)
1506 DBD::Pg specific attribute. Returns the port of the connection to the
1508 server.
1509 pg_socket (integer, read-only)
1511 DBD::Pg specific attribute. Returns the file description number of the
1513 connection socket to the server.
1514 pg_pass (string, read-only)
1516 DBD::Pg specific attribute. Returns the password used to connect to the
1518 server.
1519 pg_options (string, read-only)
1521 DBD::Pg specific attribute. Returns the command-line options passed to
1523 the server. May be an empty string.
1524 pg_default_port (integer, read-only)
1526 DBD::Pg specific attribute. Returns the default port used if none is
1528 specifically given.
1529 pg_pid (integer, read-only)
1531 DBD::Pg specific attribute. Returns the process id (PID) of the backend
1533 server process handling the connection.
1534 pg_prepare_now (boolean)
1536 DBD::Pg specific attribute. Default is off. If true, then the "prepare"
1538 method will immediately prepare commands, rather than waiting until the
1539 first execute.
1540 pg_expand_array (boolean)
1542 DBD::Pg specific attribute. Defaults to true. If false, arrays returned
1544 from the server will not be changed into a Perl arrayref, but remain as
1545 a string.
1546 pg_async_status (integer, read-only)
1548 DBD::Pg specific attribute. Returns the current status of an
1550 asynchronous command. 0 indicates no asynchronous command is in
1551 progress, 1 indicates that an asynchronous command has started and -1
1552 indicated that an asynchronous command has been cancelled.
1553 pg_standard_conforming_strings (boolean, read-only)
1555 DBD::Pg specific attribute. Returns true if the server is currently
1557 using standard conforming strings. Only available if the target server
1558 is version 8.2 or better.
1559 pg_INV_READ (integer, read-only)
1561 Constant to be used for the mode in "lo_creat" and "lo_open".
1563 pg_INV_WRITE (integer, read-only)
1565 Constant to be used for the mode in "lo_creat" and "lo_open".
1567 Driver (handle, read-only)
1569 Holds the handle of the parent driver. The only recommended use for
1571 this is to find the name of the driver using:
1572 $dbh->{Driver}->{Name}
1574 pg_protocol (integer, read-only)
1576 DBD::Pg specific attribute. Returns the version of the PostgreSQL
1578 server. If DBD::Pg is unable to figure out the version, it will return
1579 a "0". Otherwise, a "3" is returned.
1580 RowCacheSize
1582 Not used by DBD::Pg
1584
1586 Statement Handle Methods
1587 bind_param
1588 $rv = $sth->bind_param($param_num, $bind_value);
1590 $rv = $sth->bind_param($param_num, $bind_value, $bind_type);
1591 $rv = $sth->bind_param($param_num, $bind_value, \%attr);
1592 Allows the user to bind a value and/or a data type to a placeholder.
1594 This is especially important when using server-side prepares. See the
1595 "prepare" method for more information.
1596 The value of $param_num is a number if using the '?' or '$1' style
1598 placeholders. If using ":foo" style placeholders, the complete name
1599 (e.g. ":foo") must be given. For numeric values, you can either use a
1600 number or use a literal '$1'. See the examples below.
1601 The $bind_value argument is fairly self-explanatory. A value of "undef"
1603 will bind a "NULL" to the placeholder. Using "undef" is useful when you
1604 want to change just the type and will be overwriting the value later.
1605 (Any value is actually usable, but "undef" is easy and efficient).
1606 The "\%attr" hash is used to indicate the data type of the placeholder.
1608 The default value is "varchar". If you need something else, you must
1609 use one of the values provided by DBI or by DBD::Pg. To use a SQL
1610 value, modify your "use DBI" statement at the top of your script as
1611 follows:
1612 use DBI qw(:sql_types);
1614 This will import some constants into your script. You can plug those
1616 directly into the "bind_param" call. Some common ones that you will
1617 encounter are:
1618 SQL_INTEGER
1620 To use PostgreSQL data types, import the list of values like this:
1622 use DBD::Pg qw(:pg_types);
1624 You can then set the data types by setting the value of the "pg_type"
1626 key in the hash passed to "bind_param". The current list of Postgres
1627 data types exported is:
1628 PG_ABSTIME PG_ABSTIMEARRAY PG_ACLITEM PG_ACLITEMARRAY PG_ANY PG_ANYARRAY
1630 PG_ANYELEMENT PG_ANYENUM PG_ANYNONARRAY PG_BIT PG_BITARRAY PG_BOOL
1631 PG_BOOLARRAY PG_BOX PG_BOXARRAY PG_BPCHAR PG_BPCHARARRAY PG_BYTEA
1632 PG_BYTEAARRAY PG_CHAR PG_CHARARRAY PG_CID PG_CIDARRAY PG_CIDR
1633 PG_CIDRARRAY PG_CIRCLE PG_CIRCLEARRAY PG_CSTRING PG_CSTRINGARRAY PG_DATE
1634 PG_DATEARRAY PG_FLOAT4 PG_FLOAT4ARRAY PG_FLOAT8 PG_FLOAT8ARRAY PG_GTSVECTOR
1635 PG_GTSVECTORARRAY PG_INET PG_INETARRAY PG_INT2 PG_INT2ARRAY PG_INT2VECTOR
1636 PG_INT2VECTORARRAY PG_INT4 PG_INT4ARRAY PG_INT8 PG_INT8ARRAY PG_INTERNAL
1637 PG_INTERVAL PG_INTERVALARRAY PG_LANGUAGE_HANDLER PG_LINE PG_LINEARRAY PG_LSEG
1638 PG_LSEGARRAY PG_MACADDR PG_MACADDRARRAY PG_MONEY PG_MONEYARRAY PG_NAME
1639 PG_NAMEARRAY PG_NUMERIC PG_NUMERICARRAY PG_OID PG_OIDARRAY PG_OIDVECTOR
1640 PG_OIDVECTORARRAY PG_OPAQUE PG_PATH PG_PATHARRAY PG_PG_ATTRIBUTE PG_PG_CLASS
1641 PG_PG_PROC PG_PG_TYPE PG_POINT PG_POINTARRAY PG_POLYGON PG_POLYGONARRAY
1642 PG_RECORD PG_RECORDARRAY PG_REFCURSOR PG_REFCURSORARRAY PG_REGCLASS PG_REGCLASSARRAY
1643 PG_REGCONFIG PG_REGCONFIGARRAY PG_REGDICTIONARY PG_REGDICTIONARYARRAY PG_REGOPER PG_REGOPERARRAY
1644 PG_REGOPERATOR PG_REGOPERATORARRAY PG_REGPROC PG_REGPROCARRAY PG_REGPROCEDURE PG_REGPROCEDUREARRAY
1645 PG_REGTYPE PG_REGTYPEARRAY PG_RELTIME PG_RELTIMEARRAY PG_SMGR PG_TEXT
1646 PG_TEXTARRAY PG_TID PG_TIDARRAY PG_TIME PG_TIMEARRAY PG_TIMESTAMP
1647 PG_TIMESTAMPARRAY PG_TIMESTAMPTZ PG_TIMESTAMPTZARRAY PG_TIMETZ PG_TIMETZARRAY PG_TINTERVAL
1648 PG_TINTERVALARRAY PG_TRIGGER PG_TSQUERY PG_TSQUERYARRAY PG_TSVECTOR PG_TSVECTORARRAY
1649 PG_TXID_SNAPSHOT PG_TXID_SNAPSHOTARRAY PG_UNKNOWN PG_UUID PG_UUIDARRAY PG_VARBIT
1650 PG_VARBITARRAY PG_VARCHAR PG_VARCHARARRAY PG_VOID PG_XID PG_XIDARRAY
1651 PG_XML PG_XMLARRAY
1652 Data types are "sticky," in that once a data type is set to a certain
1654 placeholder, it will remain for that placeholder, unless it is
1655 explicitly set to something else afterwards. If the statement has
1656 already been prepared, and you switch the data type to something else,
1657 DBD::Pg will re-prepare the statement for you before doing the next
1658 execute.
1659 Examples:
1661 use DBI qw(:sql_types);
1663 use DBD::Pg qw(:pg_types);
1664 $SQL = "SELECT id FROM ptable WHERE size > ? AND title = ?";
1666 $sth = $dbh->prepare($SQL);
1667 ## Both arguments below are bound to placeholders as "varchar"
1669 $sth->execute(123, "Merk");
1670 ## Reset the datatype for the first placeholder to an integer
1672 $sth->bind_param(1, undef, SQL_INTEGER);
1673 ## The "undef" bound above is not used, since we supply params to execute
1675 $sth->execute(123, "Merk");
1676 ## Set the first placeholder's value and data type
1678 $sth->bind_param(1, 234, { pg_type => PG_TIMESTAMP });
1679 ## Set the second placeholder's value and data type.
1681 ## We don't send a third argument, so the default "varchar" is used
1682 $sth->bind_param('$2', "Zool");
1683 ## We realize that the wrong data type was set above, so we change it:
1685 $sth->bind_param('$1', 234, { pg_type => SQL_INTEGER });
1686 ## We also got the wrong value, so we change that as well.
1688 ## Because the data type is sticky, we don't need to change it
1689 $sth->bind_param(1, 567);
1690 ## This executes the statement with 567 (integer) and "Zool" (varchar)
1692 $sth->execute();
1693 bind_param_inout
1695 $rv = $sth->bind_param_inout($param_num, \$scalar, 0);
1697 Experimental support for this feature is provided. The first argument
1699 to bind_param_inout should be a placeholder number. The second argument
1700 should be a reference to a scalar variable in your script. The third
1701 argument is not used and should simply be set to 0. Note that what this
1702 really does is assign a returned column to the variable, in the order
1703 in which the column appears. For example:
1704 my $foo = 123;
1706 $sth = $dbh->prepare("SELECT 1+?::int");
1707 $sth->bind_param_inout(1, \$foo, 0);
1708 $foo = 222;
1709 $sth->execute(444);
1710 $sth->fetch;
1711 The above will cause $foo to have a new value of "223" after the final
1713 fetch. Note that the variables bound in this manner are very sticky,
1714 and will trump any values passed in to execute. This is because the
1715 binding is done as late as possible, at the execute() stage, allowing
1716 the value to be changed between the time it was bound and the time the
1717 query is executed. Thus, the above execute is the same as:
1718 $sth->execute();
1720 bind_param_array
1722 $rv = $sth->bind_param_array($param_num, $array_ref_or_value)
1724 $rv = $sth->bind_param_array($param_num, $array_ref_or_value, $bind_type)
1725 $rv = $sth->bind_param_array($param_num, $array_ref_or_value, \%attr)
1726 Binds an array of values to a placeholder, so that each is used in turn
1728 by a call to the "execute_array" method.
1729 execute
1731 $rv = $sth->execute(@bind_values);
1733 Executes a previously prepared statement. In addition to "UPDATE",
1735 "DELETE", "INSERT" statements, for which it returns always the number
1736 of affected rows, the "execute" method can also be used for "SELECT ...
1737 INTO table" statements.
1738 The "prepare/bind/execute" process has changed significantly for
1740 PostgreSQL servers 7.4 and later: please see the "prepare()" and
1741 "bind_param()" entries for much more information.
1742 Setting one of the bind_values to "undef" is the equivalent of setting
1744 the value to NULL in the database. Setting the bind_value to
1745 $DBDPG_DEFAULT is equivalent to sending the literal string 'DEFAULT' to
1746 the backend. Note that using this option will force server-side
1747 prepares off until such time as PostgreSQL supports using DEFAULT in
1748 prepared statements.
1749 DBD::Pg also supports passing in arrays to execute: simply pass in an
1751 arrayref, and DBD::Pg will flatten it into a string suitable for input
1752 on the backend.
1753 If you are using Postgres version 8.2 or greater, you can also use any
1755 of the fetch methods to retrieve the values of a "RETURNING" clause
1756 after you execute an "UPDATE", "DELETE", or "INSERT". For example:
1757 $dbh->do(q{CREATE TABLE abc (id SERIAL, country TEXT)});
1759 $SQL = q{INSERT INTO abc (country) VALUES (?) RETURNING id};
1760 $sth = $dbh->prepare($SQL);
1761 $sth->execute('France');
1762 $countryid = $sth->fetch()->[0];
1763 $sth->execute('New Zealand');
1764 $countryid = $sth->fetch()->[0];
1765 execute_array
1767 $tuples = $sth->execute_array() or die $sth->errstr;
1769 $tuples = $sth->execute_array(\%attr) or die $sth->errstr;
1770 $tuples = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
1771 ($tuples, $rows) = $sth->execute_array(\%attr) or die $sth->errstr;
1773 ($tuples, $rows) = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
1774 Execute a prepared statement once for each item in a passed-in hashref,
1776 or items that were previously bound via the "bind_param_array" method.
1777 See the DBI documentation for more details.
1778 execute_for_fetch
1780 $tuples = $sth->execute_for_fetch($fetch_tuple_sub);
1782 $tuples = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
1783 ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub);
1785 ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
1786 Used internally by the "execute_array" method, and rarely used
1788 directly. See the DBI documentation for more details.
1789 fetchrow_arrayref
1791 $ary_ref = $sth->fetchrow_arrayref;
1793 Fetches the next row of data from the statement handle, and returns a
1795 reference to an array holding the column values. Any columns that are
1796 NULL are returned as undef within the array.
1797 If there are no more rows or if an error occurs, the this method return
1799 undef. You should check "$sth->err" afterwards (or use the "RaiseError"
1800 attribute) to discover if the undef returned was due to an error.
1801 Note that the same array reference is returned for each fetch, so don't
1803 store the reference and then use it after a later fetch. Also, the
1804 elements of the array are also reused for each row, so take care if you
1805 want to take a reference to an element. See also "bind_columns".
1806 fetchrow_array
1808 @ary = $sth->fetchrow_array;
1810 Similar to the "fetchrow_arrayref" method, but returns a list of column
1812 information rather than a reference to a list. Do not use this in a
1813 scalar context.
1814 fetchrow_hashref
1816 $hash_ref = $sth->fetchrow_hashref;
1818 $hash_ref = $sth->fetchrow_hashref($name);
1819 Fetches the next row of data and returns a hashref containing the name
1821 of the columns as the keys and the data itself as the values. Any NULL
1822 value is returned as as undef value.
1823 If there are no more rows or if an error occurs, the this method return
1825 undef. You should check "$sth->err" afterwards (or use the "RaiseError"
1826 attribute) to discover if the undef returned was due to an error.
1827 The optional $name argument should be either "NAME", "NAME_lc" or
1829 "NAME_uc", and indicates what sort of transformation to make to the
1830 keys in the hash.
1831 fetchall_arrayref
1833 $tbl_ary_ref = $sth->fetchall_arrayref();
1835 $tbl_ary_ref = $sth->fetchall_arrayref( $slice );
1836 $tbl_ary_ref = $sth->fetchall_arrayref( $slice, $max_rows );
1837 Returns a reference to an array of arrays that contains all the
1839 remaining rows to be fetched from the statement handle. If there are no
1840 more rows, an empty arrayref will be returned. If an error occurs, the
1841 data read in so far will be returned. Because of this, you should
1842 always check "$sth->err" after calling this method, unless "RaiseError"
1843 has been enabled.
1844 If $slice is an array reference, fetchall_arrayref uses the
1846 "fetchrow_arrayref" method to fetch each row as an array ref. If the
1847 $slice array is not empty then it is used as a slice to select
1848 individual columns by perl array index number (starting at 0, unlike
1849 column and parameter numbers which start at 1).
1850 With no parameters, or if $slice is undefined, fetchall_arrayref acts
1852 as if passed an empty array ref.
1853 If $slice is a hash reference, fetchall_arrayref uses
1855 "fetchrow_hashref" to fetch each row as a hash reference.
1856 See the DBI documentation for a complete discussion.
1858 fetchall_hashref
1860 $hash_ref = $sth->fetchall_hashref( $key_field );
1862 Returns a hashref containing all rows to be fetched from the statement
1864 handle. See the DBI documentation for a full discussion.
1865 finish
1867 $rv = $sth->finish;
1869 Indicates to DBI that you are finished with the statement handle and
1871 are not going to use it again. Only needed when you have not fetched
1872 all the possible rows.
1873 rows
1875 $rv = $sth->rows;
1877 Returns the number of rows returned by the last query. In contrast to
1879 many other DBD modules, the number of rows is available immediately
1880 after calling "$sth->execute". Note that the "execute" method itself
1881 returns the number of rows itself, which means that this method is
1882 rarely needed.
1883 bind_col
1885 $rv = $sth->bind_col($column_number, \$var_to_bind);
1887 $rv = $sth->bind_col($column_number, \$var_to_bind, \%attr );
1888 $rv = $sth->bind_col($column_number, \$var_to_bind, $bind_type );
1889 Binds a Perl variable and/or some attributes to an output column of a
1891 SELECT statement. Column numbers count up from 1. You do not need to
1892 bind output columns in order to fetch data.
1893 See the DBI documentation for a discussion of the optional parameters
1895 "\%attr" and $bind_type
1896 bind_columns
1898 $rv = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
1900 Calls the "bind_col" method for each column in the SELECT statement,
1902 using the supplied list.
1903 dump_results
1905 $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
1907 Fetches all the rows from the statement handle, calls "DBI::neat_list"
1909 for each row, and prints the results to $fh (which defaults to STDOUT).
1910 Rows are separated by $lsep (which defaults to a newline). Columns are
1911 separated by $fsep (which defaults to a comma). The $maxlen controls
1912 how wide the output can be, and defaults to 35.
1913 This method is designed as a handy utility for prototyping and testing
1915 queries. Since it uses "neat_list" to format and edit the string for
1916 reading by humans, it is not recommended for data transfer
1917 applications.
1918 blob_read
1920 $blob = $sth->blob_read($id, $offset, $len);
1922 Supported by DBD::Pg. This method is implemented by DBI but not
1924 currently documented by DBI, so this method might change.
1925 This method seems to be heavily influenced by the current
1927 implementation of blobs in Oracle. Nevertheless we try to be as
1928 compatible as possible. Whereas Oracle suffers from the limitation that
1929 blobs are related to tables and every table can have only one blob
1930 (datatype LONG), PostgreSQL handles its blobs independent of any table
1931 by using so-called object identifiers. This explains why the
1932 "blob_read" method is blessed into the STATEMENT package and not part
1933 of the DATABASE package. Here the field parameter has been used to
1934 handle this object identifier. The offset and len parameters may be set
1935 to zero, in which case the whole blob is fetched at once.
1936 See also the PostgreSQL-specific functions concerning blobs, which are
1938 available via the "func" interface.
1939 For further information and examples about blobs, please read the
1941 chapter about Large Objects in the PostgreSQL Programmer's Guide at
1942 <http://www.postgresql.org/docs/current/static/largeobjects.html>.
1943 Statement Handle Attributes
1945 NUM_OF_FIELDS (integer, read-only)
1946 Returns the number of columns returned by the current statement. A
1948 number will only be returned for SELECT statements, for SHOW statements
1949 (which always return 1), and for INSERT, UPDATE, and DELETE statements
1950 which contain a RETURNING clause. This method returns undef if called
1951 before "execute()".
1952 NUM_OF_PARAMS (integer, read-only)
1954 Returns the number of placeholders in the current statement.
1956 NAME (arrayref, read-only)
1958 Returns an arrayref of column names for the current statement. This
1960 method will only work for SELECT statements, for SHOW statements, and
1961 for INSERT, UPDATE, and DELETE statements which contain a RETURNING
1962 clause. This method returns undef if called before "execute()".
1963 NAME_lc (arrayref, read-only)
1965 The same as the "NAME" attribute, except that all column names are
1967 forced to lower case.
1968 NAME_uc (arrayref, read-only)
1970 The same as the "NAME" attribute, except that all column names are
1972 forced to upper case.
1973 NAME_hash (hashref, read-only)
1975 Similar to the "NAME" attribute, but returns a hashref of column names
1977 instead of an arrayref. The names of the columns are the keys of the
1978 hash, and the values represent the order in which the columns are
1979 returned, starting at 0. This method returns undef if called before
1980 "execute()".
1981 NAME_lc_hash (hashref, read-only)
1983 The same as the "NAME_hash" attribute, except that all column names are
1985 forced to lower case.
1986 NAME_uc_hash (hashref, read-only)
1988 The same as the "NAME_hash" attribute, except that all column names are
1990 forced to lower case.
1991 TYPE (arrayref, read-only)
1993 Returns an arrayref indicating the data type for each column in the
1995 statement. This method returns undef if called before "execute()".
1996 PRECISION (arrayref, read-only)
1998 Returns an arrayref of integer values for each column returned by the
2000 statement. The number indicates the precision for "NUMERIC" columns,
2001 the size in number of characters for "CHAR" and "VARCHAR" columns, and
2002 for all other types of columns it returns the number of bytes. This
2003 method returns undef if called before "execute()".
2004 SCALE (arrayref, read-only)
2006 Returns an arrayref of integer values for each column returned by the
2008 statement. The number indicates the scale of the that column. The only
2009 type that will return a value is "NUMERIC". This method returns undef
2010 if called before "execute()".
2011 NULLABLE (arrayref, read-only)
2013 Returns an arrayref of integer values for each column returned by the
2015 statement. The number indicates if the column is nullable or not. 0 =
2016 not nullable, 1 = nullable, 2 = unknown. This method returns undef if
2017 called before "execute()".
2018 Database (dbh, read-only)
2020 Returns the database handle this statement handle was created from.
2022 ParamValues (hash ref, read-only)
2024 Returns a reference to a hash containing the values currently bound to
2026 placeholders. If the "named parameters" type of placeholders are being
2027 used (such as ":foo"), then the keys of the hash will be the names of
2028 the placeholders (without the colon). If the "dollar sign numbers" type
2029 of placeholders are being used, the keys of the hash will be the
2030 numbers, without the dollar signs. If the "question mark" type is used,
2031 integer numbers will be returned, starting at one and increasing for
2032 every placeholder.
2033 If this method is called before "execute", the literal values passed in
2035 are returned. If called after "execute", then the quoted versions of
2036 the values are returned.
2037 ParamTypes (hash ref, read-only)
2039 Returns a reference to a hash containing the type names currently bound
2041 to placeholders. The keys are the same as returned by the ParamValues
2042 method. The values are hashrefs containing a single key value pair, in
2043 which the key is either 'TYPE' if the type has a generic SQL
2044 equivalent, and 'pg_type' if the type can only be expressed by a
2045 Postgres type. The value is the internal number corresponding to the
2046 type originally passed in. (Placeholders that have not yet been bound
2047 will return undef as the value). This allows the output of ParamTypes
2048 to be passed back to the "bind_param" method.
2049 Statement (string, read-only)
2051 Returns the statement string passed to the most recent "prepare" method
2053 called in this database handle, even if that method failed. This is
2054 especially useful where "RaiseError" is enabled and the exception
2055 handler checks $@ and sees that a "prepare" method call failed.
2056 pg_current_row (integer, read-only)
2058 DBD::Pg specific attribute. Returns the number of the tuple (row) that
2060 was most recently fetched. Returns zero before and after fetching is
2061 performed.
2062 pg_numbound (integer, read-only)
2064 DBD::Pg specific attribute. Returns the number of placeholders that are
2066 currently bound (via bind_param).
2067 pg_bound (hashref, read-only)
2069 DBD::Pg specific attribute. Returns a hash of all named placeholders.
2071 The key is the name of the placeholder, and the value is a 0 or a 1,
2072 indicating if the placeholder has been bound yet (e.g. via bind_param)
2073 pg_size (arrayref, read-only)
2075 DBD::Pg specific attribute. It returns a reference to an array of
2077 integer values for each column. The integer shows the size of the
2078 column in bytes. Variable length columns are indicated by -1.
2079 pg_type (arrayref, read-only)
2081 DBD::Pg specific attribute. It returns a reference to an array of
2083 strings for each column. The string shows the name of the data_type.
2084 pg_segments (arrayref, read-only)
2086 DBD::Pg specific attribute. Returns an arrayref of the query split on
2088 the placeholders.
2089 pg_oid_status (integer, read-only)
2091 DBD::Pg specific attribute. It returns the OID of the last INSERT
2093 command.
2094 pg_cmd_status (integer, read-only)
2096 DBD::Pg specific attribute. It returns the type of the last command.
2098 Possible types are: "INSERT", "DELETE", "UPDATE", "SELECT".
2099 pg_direct (boolean)
2101 DBD::Pg specific attribute. Default is false. If true, the query is
2103 passed directly to the backend without parsing for placeholders.
2104 pg_prepare_now (boolean)
2106 DBD::Pg specific attribute. Default is off. If true, the query will be
2108 immediately prepared, rather than waiting for the "execute" call.
2109 pg_prepare_name (string)
2111 DBD::Pg specific attribute. Specifies the name of the prepared
2113 statement to use for this statement handle. Not normally needed, see
2114 the section on the "prepare" method for more information.
2115 pg_server_prepare (integer)
2117 DBD::Pg specific attribute. Indicates if DBD::Pg should attempt to use
2119 server-side prepared statements for this statement handle. The default
2120 value, 1, indicates that prepared statements should be used whenever
2121 possible. See the section on the "prepare" method for more information.
2122 pg_placeholder_dollaronly (boolean)
2124 DBD::Pg specific attribute. Defaults to off. When true, question marks
2126 inside of the query being prepared are not treated as placeholders.
2127 Useful for statements that contain unquoted question marks, such as
2128 geometric operators.
2129 pg_async (integer)
2131 DBD::Pg specific attribute. Indicates the current behavior for
2133 asynchronous queries. See the section on "Asynchronous Constants" for
2134 more information.
2135 RowsInCache
2137 Not used by DBD::Pg
2139 RowCache
2141 Not used by DBD::Pg
2143 CursorName
2145 Not used by DBD::Pg. See the note about "Cursors" elsewhere in this
2147 document.
2148
2150 Transactions
2151 Transaction behavior is controlled via the "AutoCommit" attribute. For
2152 a complete definition of "AutoCommit" please refer to the DBI
2153 documentation.
2154 According to the DBI specification the default for "AutoCommit" is a
2156 true value. In this mode, any change to the database becomes valid
2157 immediately. Any "BEGIN", "COMMIT" or "ROLLBACK" statements will be
2158 rejected. DBD::Pg implements "AutoCommit" by issuing a "BEGIN"
2159 statement immediately before executing a statement, and a "COMMIT"
2160 afterwards. Note that preparing a statement is not always enough to
2161 trigger the first "BEGIN", as the actual "PREPARE" is usually postponed
2162 until the first call to "execute".
2163 Savepoints
2165 PostgreSQL version 8.0 introduced the concept of savepoints, which
2166 allows transactions to be rolled back to a certain point without
2167 affecting the rest of the transaction. DBD::Pg encourages using the
2168 following methods to control savepoints:
2169 "pg_savepoint"
2171 Creates a savepoint. This will fail unless you are inside of a
2173 transaction. The only argument is the name of the savepoint. Note that
2174 PostgreSQL DOES allow multiple savepoints with the same name to exist.
2175 $dbh->pg_savepoint("mysavepoint");
2177 "pg_rollback_to"
2179 Rolls the database back to a named savepoint, discarding any work
2181 performed after that point. If more than one savepoint with that name
2182 exists, rolls back to the most recently created one.
2183 $dbh->pg_rollback_to("mysavepoint");
2185 "pg_release"
2187 Releases (or removes) a named savepoint. If more than one savepoint
2189 with that name exists, it will only destroy the most recently created
2190 one. Note that all savepoints created after the one being released are
2191 also destroyed.
2192 $dbh->pg_release("mysavepoint");
2194 Asynchronous Queries
2196 It is possible to send a query to the backend and have your script do
2197 other work while the query is running on the backend. Both queries sent
2198 by the "do" method, and by the "execute" method can be sent
2199 asynchronously. (NOTE: This will only work if DBD::Pg has been compiled
2200 against Postgres libraries of version 8.0 or greater) The basic usage
2201 is as follows:
2202 use DBD::Pg ':async';
2204 print "Async do() example:\n";
2206 $dbh->do("SELECT long_running_query()", {pg_async => PG_ASYNC});
2207 do_something_else();
2208 {
2209 if ($dbh->pg_ready()) {
2210 $res = $pg_result();
2211 print "Result of do(): $res\n";
2212 }
2213 print "Query is still running...\n";
2214 if (cancel_request_received) {
2215 $dbh->pg_cancel();
2216 }
2217 sleep 1;
2218 redo;
2219 }
2220 print "Async prepare/execute example:\n";
2222 $sth = $dbh->prepare("SELECT long_running_query(1)", {pg_async => PG_ASYNC});
2223 $sth->execute();
2224 ## Changed our mind, cancel and run again:
2226 $sth = $dbh->prepare("SELECT 678", {pg_async => PG_ASYNC + PG_OLDQUERY_CANCEL});
2227 $sth->execute();
2228 do_something_else();
2230 if (!$sth->pg_ready) {
2232 do_another_thing();
2233 }
2234 ## We wait until it is done, and get the result:
2236 $res = $dbh->pg_result();
2237 Asynchronous Constants
2239 There are currently three asynchronous constants exported by DBD::Pg.
2241 You can import all of them by putting either of these at the top of
2242 your script:
2243 use DBD::Pg;
2245 use DBD::Pg ':async';
2247 You may also use the numbers instead of the constants, but using the
2249 constants is recommended as it makes your script more readable.
2250 PG_ASYNC
2252 This is a constant for the number 1. It is passed to either the
2253 "do" or the "prepare" method as a value to the pg_async key and
2254 indicates that the query should be sent asynchronously.
2255 PG_OLDQUERY_CANCEL
2257 This is a constant for the number 2. When passed to either the "do"
2258 or the "prepare" method, it causes any currently running
2259 asynchronous query to be cancelled and rolled back. It has no
2260 effect if no asynchronous query is currently running.
2261 PG_OLDQUERY_WAIT
2263 This is a constant for the number 4. When passed to either the "do"
2264 or the "prepare" method, it waits for any currently running
2265 asynchronous query to complete. It has no effect if there is no
2266 asynchronous query currently running.
2267 Asynchronous Methods
2269 pg_cancel
2271 This database-level method attempts to cancel any currently running
2272 asynchronous query. It returns true if the cancel succeeded, and
2273 false otherwise. Note that a query that has finished before this
2274 method is executed will also return false. WARNING: a successful
2275 cancellation may leave the database in an unusable state, so you
2276 may need to ROLLBACK or ROLLBACK TO a savepoint. As of version
2277 2.17.0 of DBD::Pg, rollbacks are not done automatically.
2278 $result = $dbh->pg_cancel();
2280 pg_ready
2282 This method can be called as a database handle method or (for
2283 convenience) as a statement handle method. Both simply see if a
2284 previously issued asynchronous query has completed yet. It returns
2285 true if the statement has finished, in which case you should then
2286 call the "pg_result" method. Calls to "pg_ready()" should only be
2287 used when you have other things to do while the query is running.
2288 If you simply want to wait until the query is done, do not call
2289 pg_ready() over and over, but simply call the pg_result() method.
2290 my $time = 0;
2292 while (!$dbh->pg_ready) {
2293 print "Query is still running. Seconds: $time\n";
2294 $time++;
2295 sleep 1;
2296 }
2297 $result = $dbh->pg_result;
2298 pg_result
2300 This database handle method returns the results of a previously
2301 issued asynchronous query. If the query is still running, this
2302 method will wait until it has finished. The result returned is the
2303 number of rows: the same thing that would have been returned by the
2304 asynchronous "do" or "execute" if it had been called without an
2305 asynchronous flag.
2306 $result = $dbh->pg_result;
2308 Asynchronous Examples
2310 Here are some working examples of asynchronous queries. Note that we'll
2312 use the pg_sleep function to emulate a long-running query.
2313 use strict;
2315 use warnings;
2316 use Time::HiRes 'sleep';
2317 use DBD::Pg ':async';
2318 my $dbh = DBI->connect('dbi:Pg:dbname=postgres', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
2320 ## Kick off a long running query on the first database:
2322 my $sth = $dbh->prepare("SELECT pg_sleep(?)", {pg_async => PG_ASYNC});
2323 $sth->execute(5);
2324 ## While that is running, do some other things
2326 print "Your query is processing. Thanks for waiting\n";
2327 check_on_the_kids(); ## Expensive sub, takes at least three seconds.
2328 while (!$dbh->pg_ready) {
2330 check_on_the_kids();
2331 ## If the above function returns quickly for some reason, we add a small sleep
2332 sleep 0.1;
2333 }
2334 print "The query has finished. Gathering results\n";
2336 my $result = $sth->pg_result;
2337 print "Result: $result\n";
2338 my $info = $sth->fetchall_arrayref();
2339 Without asynchronous queries, the above script would take about 8
2341 seconds to run: five seconds waiting for the execute to finish, then
2342 three for the check_on_the_kids() function to return. With asynchronous
2343 queries, the script takes about 6 seconds to run, and gets in two
2344 iterations of check_on_the_kids in the process.
2345 Here's an example showing the ability to cancel a long-running query.
2347 Imagine two slave databases in different geographic locations over a
2348 slow network. You need information as quickly as possible, so you query
2349 both at once. When you get an answer, you tell the other one to stop
2350 working on your query, as you don't need it anymore.
2351 use strict;
2353 use warnings;
2354 use Time::HiRes 'sleep';
2355 use DBD::Pg ':async';
2356 my $dbhslave1 = DBI->connect('dbi:Pg:dbname=postgres;host=slave1', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
2358 my $dbhslave2 = DBI->connect('dbi:Pg:dbname=postgres;host=slave2', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
2359 $SQL = "SELECT count(*) FROM largetable WHERE flavor='blueberry'";
2361 my $sth1 = $dbhslave1->prepare($SQL, {pg_async => PG_ASYNC});
2363 my $sth2 = $dbhslave2->prepare($SQL, {pg_async => PG_ASYNC});
2364 $sth1->execute();
2366 $sth2->execute();
2367 my $winner;
2369 while (!defined $winner) {
2370 if ($sth1->pg_ready) {
2371 $winner = 1;
2372 }
2373 elsif ($sth2->pg_ready) {
2374 $winner = 2;
2375 }
2376 Time::HiRes::sleep 0.05;
2377 }
2378 my $count;
2380 if ($winner == 1) {
2381 $sth2->pg_cancel();
2382 $sth1->pg_result();
2383 $count = $sth1->fetchall_arrayref()->[0][0];
2384 }
2385 else {
2386 $sth1->pg_cancel();
2387 $sth2->pg_result();
2388 $count = $sth2->fetchall_arrayref()->[0][0];
2389 }
2390 Array support
2392 DBD::Pg allows arrays (as arrayrefs) to be passed in to both the
2393 "quote" and the "execute" methods. In both cases, the array is
2394 flattened into a string representing a Postgres array.
2395 When fetching rows from a table that contains a column with an array
2397 type, the result will be passed back to your script as an arrayref.
2398 To turn off the automatic parsing of returned arrays into arrayrefs,
2400 you can set the attribute pg_expand_array, which is true by default.
2401 $dbh->{pg_expand_array} = 0;
2403 COPY support
2405 DBD::Pg allows for quick (bulk) reading and storing of data by using
2406 the COPY command. The basic process is to use "$dbh->do" to issue a
2407 COPY command, and then to either add rows using "pg_putcopydata", or to
2408 read them by using "pg_getcopydata".
2409 The first step is to put the server into "COPY" mode. This is done by
2411 sending a complete COPY command to the server, by using the "do"
2412 method. For example:
2413 $dbh->do("COPY foobar FROM STDIN");
2415 This would tell the server to enter a COPY IN mode (yes, that's
2417 confusing, but the mode is COPY IN because of the command COPY FROM).
2418 It is now ready to receive information via the "pg_putcopydata" method.
2419 The complete syntax of the COPY command is more complex and not
2420 documented here: the canonical PostgreSQL documentation for COPY can be
2421 found at:
2422 http://www.postgresql.org/docs/current/static/sql-copy.html
2424 Once a COPY command has been issued, no other SQL commands are allowed
2426 until "pg_putcopyend" has been issued (for COPY FROM), or the final
2427 "pg_getcopydata" has been called (for COPY TO).
2428 Note: All other COPY methods (pg_putline, pg_getline, etc.) are now
2430 heavily deprecated in favor of the pg_getcopydata, pg_putcopydata, and
2431 pg_putcopyend methods.
2432 pg_getcopydata
2434 Used to retrieve data from a table after the server has been put into a
2436 COPY OUT mode by calling "COPY tablename TO STDOUT". Data is always
2437 returned one data row at a time. The first argument to pg_getcopydata
2438 is the variable into which the data will be stored (this variable
2439 should not be undefined, or it may throw a warning, although it may be
2440 a reference). The pg_gecopydata method returns a number greater than 1
2441 indicating the new size of the variable, or a -1 when the COPY has
2442 finished. Once a -1 has been returned, no other action is necessary, as
2443 COPY mode will have already terminated. Example:
2444 $dbh->do("COPY mytable TO STDOUT");
2446 my @data;
2447 my $x=0;
2448 1 while $dbh->pg_getcopydata($data[$x++]) >= 0;
2449 There is also a variation of this method called pg_getcopydata_async,
2451 which, as the name suggests, returns immediately. The only difference
2452 from the original method is that this version may return a 0,
2453 indicating that the row is not ready to be delivered yet. When this
2454 happens, the variable has not been changed, and you will need to call
2455 the method again until you get a non-zero result. (Data is still
2456 always returned one data row at a time.)
2457 pg_putcopydata
2459 Used to put data into a table after the server has been put into COPY
2461 IN mode by calling "COPY tablename FROM STDIN". The only argument is
2462 the data you want inserted. Issue a pg_putcopyend() when you have added
2463 all your rows.
2464 The default delimiter is a tab character, but this can be changed in
2466 the COPY statement. Returns a 1 on successful input. Examples:
2467 ## Simple example:
2469 $dbh->do("COPY mytable FROM STDIN");
2470 $dbh->pg_putcopydata("123\tPepperoni\t3\n");
2471 $dbh->pg_putcopydata("314\tMushroom\t8\n");
2472 $dbh->pg_putcopydata("6\tAnchovies\t100\n");
2473 $dbh->pg_putcopyend();
2474 ## This example uses explicit columns and a custom delimiter
2476 $dbh->do("COPY mytable(flavor, slices) FROM STDIN WITH DELIMITER '~'");
2477 $dbh->pg_putcopydata("Pepperoni~123\n");
2478 $dbh->pg_putcopydata("Mushroom~314\n");
2479 $dbh->pg_putcopydata("Anchovies~6\n");
2480 $dbh->pg_putcopyend();
2481 pg_putcopyend
2483 When you are finished with pg_putcopydata, call pg_putcopyend to let
2485 the server know that you are done, and it will return to a normal, non-
2486 COPY state. Returns a 1 on success. This method will fail if called
2487 when not in COPY IN mode.
2488 Large Objects
2490 DBD::Pg supports all largeobject functions provided by libpq via the
2491 "$dbh->pg_lo*" methods. Please note that access to a large object, even
2492 read-only large objects, must be put into a transaction.
2493 Cursors
2495 Although PostgreSQL supports cursors, they have not been used in the
2496 current implementation. When DBD::Pg was created, cursors in PostgreSQL
2497 could only be used inside a transaction block. Because only one
2498 transaction block at a time is allowed, this would have implied the
2499 restriction not to use any nested "SELECT" statements. Therefore the
2500 "execute" method fetches all data at once into data structures located
2501 in the front-end application. This fact must to be considered when
2502 selecting large amounts of data!
2503 You can use cursors in your application, but you'll need to do a little
2505 work. First you must declare your cursor. Now you can issue queries
2506 against the cursor, then select against your queries. This typically
2507 results in a double loop, like this:
2508 # WITH HOLD is not needed if AutoCommit is off
2510 $dbh->do("DECLARE csr CURSOR WITH HOLD FOR $sql");
2511 while (1) {
2512 my $sth = $dbh->prepare("fetch 1000 from csr");
2513 $sth->execute;
2514 last if 0 == $sth->rows;
2515 while (my $row = $sth->fetchrow_hashref) {
2517 # Do something with the data.
2518 }
2519 }
2520 $dbh->do("CLOSE csr");
2521 Datatype bool
2523 The current implementation of PostgreSQL returns 't' for true and 'f'
2524 for false. From the Perl point of view, this is a rather unfortunate
2525 choice. DBD::Pg therefore translates the result for the "BOOL" data
2526 type in a Perlish manner: 'f' becomes the number 0 and 't' becomes the
2527 number 1. This way the application does not have to check the database-
2528 specific returned values for the data-type "BOOL" because Perl treats 0
2529 as false and 1 as true. You may set the pg_bool_tf attribute to a true
2530 value to change the values back to 't' and 'f' if you wish.
2531 Boolean values can be passed to PostgreSQL as TRUE, 't', 'true', 'y',
2533 'yes' or '1' for true and FALSE, 'f', 'false', 'n', 'no' or '0' for
2534 false.
2535 Schema support
2537 The PostgreSQL schema concept may differ from those of other databases.
2538 In a nutshell, a schema is a named collection of objects within a
2539 single database. Please refer to the PostgreSQL documentation for more
2540 details:
2541 http://www.postgresql.org/docs/current/static/ddl-schemas.html
2543 <http://www.postgresql.org/docs/current/static/ddl-schemas.html>
2544 DBD::Pg does not provide explicit support for PostgreSQL schemas.
2546 However, schema functionality may be used without any restrictions by
2547 explicitly addressing schema objects, e.g.
2548 my $res = $dbh->selectall_arrayref("SELECT * FROM my_schema.my_table");
2550 or by manipulating the schema search path with "SET search_path", e.g.
2552 $dbh->do("SET search_path TO my_schema, public");
2554
2557 To report a bug, or view the current list of bugs, please visit
2558 http://rt.cpan.org/Public/Dist/Display.html?Name=DBD-Pg
2559
2561 DBI by Tim Bunce <http://www.tim.bunce.name>
2562 The original DBD-Pg was by Edmund Mergl (E.Mergl@bawue.de) and Jeffrey
2564 W. Baker (jwbaker@acm.org). Major developers include David Wheeler
2565 <david@justatheory.com>, Jason Stewart <jason@openinformatics.com>,
2566 Bruce Momjian <pgman@candle.pha.pa.us>, and Greg Sabino Mullane
2567 <greg@turnstep.com>, with help from many others: see the Changes file
2568 for a complete list.
2569 Parts of this package were originally copied from DBI and DBD-Oracle.
2571 Mailing List
2573 The current maintainers may be reached through the 'dbd-pg' mailing
2575 list: <dbd-pg@perl.org>
2576
2578 Copyright (C) 1994-2010, Greg Sabino Mullane
2579 This module (DBD::Pg) is free software; you can redistribute it and/or
2581 modify it under the same terms as Perl 5.10.0. For more details, see
2582 the full text of the licenses in the directory LICENSES.
2583perl v5.12.2 2010-04-07 Pg(3)