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.15.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 Note that these codes are part of the SQL standard and only a small
209 number of them will be used by PostgreSQL.
210 Common codes:
212 00000 Successful completion
214 25P01 No active SQL transaction
215 25P02 In failed SQL transaction
216 S8006 Connection failure
217 trace
219 $h->trace($trace_settings);
221 $h->trace($trace_settings, $trace_filename);
222 $trace_settings = $h->trace;
223 Changes the trace settings on a database or statement handle. The
225 optional second argument specifies a file to write the trace
226 information to. If no filename is given, the information is written to
227 STDERR. Note that tracing can be set globally as well by setting
228 "DBI->trace", or by using the environment variable DBI_TRACE.
229 The value is either a numeric level or a named flag. For the flags that
231 DBD::Pg uses, see parse_trace_flag.
232 trace_msg
234 $h->trace_msg($message_text);
236 $h->trace_msg($message_text, $min_level);
237 Writes a message to the current trace output (as set by the "trace"
239 method). If a second argument is given, the message is only written if
240 the current tracing level is equal to or greater than the $min_level.
241 parse_trace_flag and parse_trace_flags
243 $h->trace($h->parse_trace_flags('SQL|pglibpq'));
245 $h->trace($h->parse_trace_flags('1|pgstart'));
246 ## Simpler:
248 $h->trace('SQL|pglibpq');
249 $h->trace('1|pgstart');
250 my $value = DBD::Pg->parse_trace_flag('pglibpq');
252 DBI->trace($value);
253 The parse_trace_flags method is used to convert one or more named flags
255 to a number which can passed to the "trace" method. DBD::Pg currently
256 supports the DBI-specific flag, "SQL", as well as the ones listed
257 below.
258 Flags can be combined by using the parse_trace_flags method, which
260 simply calls "parse_trace_flag" on each item and combines them.
261 Sometimes you may wish to turn the tracing on before you connect to the
263 database. The second example above shows a way of doing this: the call
264 to "DBD::Pg->parse_trace_flags" provides a number than can be fed to
265 "DBI->trace" before you create a database handle.
266 DBD::Pg supports the following trace flags:
268 SQL Outputs all SQL statements. Note that the output provided will not
270 necessarily be in a form suitable to passing directly to Postgres,
271 as server-side prepared statements are used extensively by DBD::Pg.
272 For maximum portability of output (but with a potential performance
273 hit), use with "$dbh-"{pg_server_prepare} = 0>
274 DBD Turns on all non-DBI flags, in other words, only the ones that are
276 specific to DBD::Pg (all those below which start with the letters
277 'pg').
278 pglibpq
280 Outputs the name of each libpq function (without arguments)
281 immediately before running it. This is a good way to trace the flow
282 of your program at a low level. This information is also output if
283 the trace level is set to 4 or greater.
284 pgstart
286 Outputs the name of each internal DBD::Pg function, and other
287 information such as the function arguments or important global
288 variables, as each function starts. This information is also output
289 if the trace level is set to 4 or greater.
290 pgend
292 Outputs a simple message at the very end of each internal DBD::Pg
293 function. This is also output if the trace level is set to 4 or
294 greater.
295 pgprefix
297 Forces each line of trace output to begin with the string "dbdpg:
298 ". This helps to differentiate it from the normal DBI trace output.
299 pglogin
301 Outputs a message showing the connection string right before a new
302 database connection is attempted, a message when the connection was
303 successful, and a message right after the database has been
304 disconnected. Also output if trace level is 5 or greater.
305 func
307 DBD::Pg uses the "func" method to support a variety of functions. Note
309 that the name of the function comes last, after the arguments.
310 table_attributes
312 $attrs = $dbh->func($table, 'table_attributes');
313 Use of the tables_attributes function is no longer recommended.
315 Instead, you can use the more portable "column_info" and
316 "primary_key" methods to access the same information.
317 The table_attributes method returns, for the given table argument,
319 a reference to an array of hashes, each of which contains the
320 following keys:
321 NAME attribute name
323 TYPE attribute type
324 SIZE attribute size (-1 for variable size)
325 NULLABLE flag nullable
326 DEFAULT default value
327 CONSTRAINT constraint
328 PRIMARY_KEY flag is_primary_key
329 REMARKS attribute description
330 pg_lo_creat
332 $lobjId = $dbh->pg_lo_creat($mode);
333 Creates a new large object and returns the object-id. $mode is a
335 bitmask describing read and write access to the new object. This
336 setting is ignored since Postgres version 8.1. For backwards
337 compatibility, however, you should set a valid mode anyway (see
338 "pg_lo_open" for a list of valid modes).
339 Upon failure it returns "undef". This function cannot be used if
341 AutoCommit is enabled.
342 The old way of calling large objects functions is deprecated:
344 $dbh->func(.., 'lo_);
345 lo_open
347 $lobj_fd = $dbh->pg_lo_open($lobjId, $mode);
348 Opens an existing large object and returns an object-descriptor for
350 use in subsequent "lo_*" calls. $mode is a bitmask describing read
351 and write access to the opened object. It may be one of:
352 $dbh->{pg_INV_READ}
354 $dbh->{pg_INV_WRITE}
355 $dbh->{pg_INV_READ} | $dbh->{pg_INV_WRITE}
356 "pg_INV_WRITE" and "pg_INV_WRITE | pg_INV_READ" modes are
358 identical; in both modes, the large object can be read from or
359 written to. Reading from the object will provide the object as
360 written in other committed transactions, along with any writes
361 performed by the current transaction. Objects opened with
362 "pg_INV_READ" cannot be written to. Reading from this object will
363 provide the stored data at the time of the transaction snapshot
364 which was active when "lo_write" was called.
365 Returns "undef" upon failure. Note that 0 is a perfectly correct
367 (and common) object descriptor! This function cannot be used if
368 AutoCommit is enabled.
369 lo_write
371 $nbytes = $dbh->pg_lo_write($lobj_fd, $buffer, $len);
372 Writes $len bytes of c<$buffer> into the large object $lobj_fd.
374 Returns the number of bytes written and "undef" upon failure. This
375 function cannot be used if AutoCommit is enabled.
376 lo_read
378 $nbytes = $dbh->pg_lo_read($lobj_fd, $buffer, $len);
379 Reads $len bytes into c<$buffer> from large object $lobj_fd.
381 Returns the number of bytes read and "undef" upon failure. This
382 function cannot be used if AutoCommit is enabled.
383 lo_lseek
385 $loc = $dbh->pg_lo_lseek($lobj_fd, $offset, $whence);
386 Changes the current read or write location on the large object
388 $obj_id. Currently $whence can only be 0 (which is L_SET). Returns
389 the current location and "undef" upon failure. This function cannot
390 be used if AutoCommit is enabled.
391 lo_tell
393 $loc = $dbh->pg_lo_tell($lobj_fd);
394 Returns the current read or write location on the large object
396 $lobj_fd and "undef" upon failure. This function cannot be used if
397 AutoCommit is enabled.
398 lo_close
400 $lobj_fd = $dbh->pg_lo_close($lobj_fd);
401 Closes an existing large object. Returns true upon success and
403 false upon failure. This function cannot be used if AutoCommit is
404 enabled.
405 lo_unlink
407 $ret = $dbh->pg_lo_unlink($lobjId);
408 Deletes an existing large object. Returns true upon success and
410 false upon failure. This function cannot be used if AutoCommit is
411 enabled.
412 lo_import
414 $lobjId = $dbh->pg_lo_import($filename);
415 Imports a Unix file as a large object and returns the object id of
417 the new object or "undef" upon failure.
418 lo_export
420 $ret = $dbh->pg_lo_export($lobjId, $filename);
421 Exports a large object into a Unix file. Returns false upon
423 failure, true otherwise.
424 getfd
426 $fd = $dbh->func('getfd');
427 Deprecated, use $dbh->{pg_socket} instead.
429 private_attribute_info
431 $hashref = $dbh->private_attribute_info();
433 $hashref = $sth->private_attribute_info();
434 Returns a hash of all private attributes used by DBD::Pg, for either a
436 database or a statement handle. Currently, all the hash values are
437 undef.
438
440 InactiveDestroy (boolean)
441 If set to true, then the "disconnect" method will not be automatically
443 called when the database handle goes out of scope. This is required if
444 you are forking, and even then you must tread carefully and ensure that
445 either the parent or the child (but not both!) handles all database
446 calls from that point forwards, so that messages from the Postgres
447 backend are only handled by one of the processes. If you don't set
448 things up properly, you will see messages such as "server closed the
449 connection unexpectedly", and "message type 0x32 arrived from server
450 while idle". The best solution is to either have the child process
451 reconnect to the database with a fresh database handle, or to rewrite
452 your application not to use use forking. See the section on
453 "Asynchronous Queries" for a way to have your script continue to work
454 while the database is processing a request.
455 RaiseError (boolean, inherited)
457 Forces errors to always raise an exception. Although it defaults to
459 off, it is recommended that this be turned on, as the alternative is to
460 check the return value of every method (prepare, execute, fetch, etc.)
461 manually, which is easy to forget to do.
462 PrintError (boolean, inherited)
464 Forces database errors to also generate warnings, which can then be
466 filtered with methods such as locally redefining $SIG{__WARN__} or
467 using modules such as "CGI::Carp". This attribute is on by default.
468 ShowErrorStatement (boolean, inherited)
470 Appends information about the current statement to error messages. If
472 placeholder information is available, adds that as well. Defaults to
473 false.
474 Warn (boolean, inherited)
476 Enables warnings. This is on by default, and should only be turned off
478 in a local block for a short a time only when absolutely needed.
479 Executed (boolean, read-only)
481 Indicates if a handle has been executed. For database handles, this
483 value is true after the "do" method has been called, or when one of the
484 child statement handles has issued an "execute". Issuing a "commit" or
485 "rollback" always resets the attribute to false for database handles.
486 For statement handles, any call to "execute" or its variants will flip
487 the value to true for the lifetime of the statement handle.
488 TraceLevel (integer, inherited)
490 Sets the trace level, similar to the "trace" method. See the sections
492 on "trace" and "parse_trace_flag" for more details.
493 Active (boolean, read-only)
495 Indicates if a handle is active or not. For database handles, this
497 indicates if the database has been disconnected or not. For statement
498 handles, it indicates if all the data has been fetched yet or not. Use
499 of this attribute is not encouraged.
500 Kids (integer, read-only)
502 Returns the number of child processes created for each handle type. For
504 a driver handle, indicates the number of database handles created. For
505 a database handle, indicates the number of statement handles created.
506 For statement handles, it always returns zero, because statement
507 handles do not create kids.
508 ActiveKids (integer, read-only)
510 Same as "Kids", but only returns those that are active.
512 CachedKids (hash ref)
514 Returns a hashref of handles. If called on a database handle, returns
516 all statement handles created by use of the "prepare_cached" method. If
517 called on a driver handle, returns all database handles created by the
518 "connect_cached" method.
519 ChildHandles (array ref)
521 Implemented by DBI, no driver-specific impact.
523 PrintWarn (boolean, inherited)
525 Implemented by DBI, no driver-specific impact.
527 HandleError (boolean, inherited)
529 Implemented by DBI, no driver-specific impact.
531 HandleSetErr (code ref, inherited)
533 Implemented by DBI, no driver-specific impact.
535 ErrCount (unsigned integer)
537 Implemented by DBI, no driver-specific impact.
539 FetchHashKeyName (string, inherited)
541 Implemented by DBI, no driver-specific impact.
543 ChopBlanks (boolean, inherited)
545 Supported by DBD::Pg as proposed by DBI. This method is similar to the
547 SQL function "RTRIM".
548 Taint (boolean, inherited)
550 Implemented by DBI, no driver-specific impact.
552 TaintIn (boolean, inherited)
554 Implemented by DBI, no driver-specific impact.
556 TaintOut (boolean, inherited)
558 Implemented by DBI, no driver-specific impact.
560 Profile (inherited)
562 Implemented by DBI, no driver-specific impact.
564 Type (scalar)
566 Returns "dr" for a driver handle, "db" for a database handle, and "st"
568 for a statement handle. Should be rarely needed.
569 LongReadLen
571 Not used by DBD::Pg
573 LongTruncOk
575 Not used by DBD::Pg
577 CompatMode
579 Not used by DBD::Pg
581
583 Database Handle Methods
584 selectall_arrayref
585 $ary_ref = $dbh->selectall_arrayref($sql);
587 $ary_ref = $dbh->selectall_arrayref($sql, \%attr);
588 $ary_ref = $dbh->selectall_arrayref($sql, \%attr, @bind_values);
589 Returns a reference to an array containing the rows returned by
591 preparing and executing the SQL string. See the DBI documentation for
592 full details.
593 selectall_hashref
595 $hash_ref = $dbh->selectall_hashref($sql, $key_field);
597 Returns a reference to a hash containing the rows returned by preparing
599 and executing the SQL string. See the DBI documentation for full
600 details.
601 selectcol_arrayref
603 $ary_ref = $dbh->selectcol_arrayref($sql, \%attr, @bind_values);
605 Returns a reference to an array containing the first column from each
607 rows returned by preparing and executing the SQL string. It is possible
608 to specify exactly which columns to return. See the DBI documentation
609 for full details.
610 prepare
612 $sth = $dbh->prepare($statement, \%attr);
614 WARNING: DBD::Pg now (as of version 1.40) uses true prepared statements
616 by sending them to the backend to be prepared by the Postgres server.
617 Statements that were legal before may no longer work. See below for
618 details.
619 The prepare method prepares a statement for later execution. PostgreSQL
621 supports prepared statements, which enables DBD::Pg to only send the
622 query once, and simply send the arguments for every subsequent call to
623 "execute". DBD::Pg can use these server-side prepared statements, or
624 it can just send the entire query to the server each time. The best way
625 is automatically chosen for each query. This will be sufficient for
626 most users: keep reading for a more detailed explanation and some
627 optional flags.
628 Queries that do not begin with the word "SELECT", "INSERT", "UPDATE",
630 or "DELETE" are never sent as server-side prepared statements.
631 Deciding whether or not to use prepared statements depends on many
633 factors, but you can force them to be used or not used by using the
634 "pg_server_prepare" attribute when calling "prepare". Setting this to
635 "0" means to never use prepared statements. Setting "pg_server_prepare"
636 to "1" means that prepared statements should be used whenever possible.
637 This is the default when connected to Postgres servers version 8.0 or
638 higher. Servers that are version 7.4 get a special default value of
639 "2", because server-side statements were only partially supported in
640 that version. In this case, it only uses server-side prepares if all
641 parameters are specifically bound.
642 The "pg_server_prepare" attribute can also be set at connection time
644 like so:
645 $dbh = DBI->connect($DBNAME, $DBUSER, $DBPASS,
647 { AutoCommit => 0,
648 RaiseError => 1,
649 pg_server_prepare => 0,
650 });
651 or you may set it after your database handle is created:
653 $dbh->{pg_server_prepare} = 1;
655 To enable it for just one particular statement:
657 $sth = $dbh->prepare("SELECT id FROM mytable WHERE val = ?",
659 { pg_server_prepare => 1 });
660 You can even toggle between the two as you go:
662 $sth->{pg_server_prepare} = 1;
664 $sth->execute(22);
665 $sth->{pg_server_prepare} = 0;
666 $sth->execute(44);
667 $sth->{pg_server_prepare} = 1;
668 $sth->execute(66);
669 In the above example, the first execute will use the previously
671 prepared statement. The second execute will not, but will build the
672 query into a single string and send it to the server. The third one
673 will act like the first and only send the arguments. Even if you
674 toggle back and forth, a statement is only prepared once.
675 Using prepared statements is in theory quite a bit faster: not only
677 does the PostgreSQL backend only have to prepare the query only once,
678 but DBD::Pg no longer has to worry about quoting each value before
679 sending it to the server.
680 However, there are some drawbacks. The server cannot always choose the
682 ideal parse plan because it will not know the arguments before hand.
683 But for most situations in which you will be executing similar data
684 many times, the default plan will probably work out well. Programs such
685 as PgBouncer which cache connections at a low level should not use
686 prepared statements via DBD::Pg, or must take extra care in the
687 application to account for the fact that prepared statements are not
688 shared across database connections. Further discussion on this subject
689 is beyond the scope of this documentation: please consult the pgsql-
690 performance mailing list,
691 <http://archives.postgresql.org/pgsql-performance/>
692 Only certain commands will be sent to a server-side prepare: currently
694 these include "SELECT", "INSERT", "UPDATE", and "DELETE". DBD::Pg uses
695 a simple naming scheme for the prepared statements themselves:
696 dbdpg_XY_Z, where Y is the current PID, X is either 'p' or 'n'
697 (depending on if the PID is a positive or negative number), and Z is a
698 number that starts at 1 and increases each time a new statement is
699 prepared. This number is tracked at the database handle level, so
700 multiple statement handles will not collide.
701 You cannot send more than one command at a time in the same prepare
703 command (by separating them with semi-colons) when using server-side
704 prepares.
705 The actual "PREPARE" is usually not performed until the first execute
707 is called, due to the fact that information on the data types (provided
708 by "bind_param") may be provided after the prepare but before the
709 execute.
710 A server-side prepare may happen before the first "execute", but only
712 if the server can handle the server-side prepare, and the statement
713 contains no placeholders. It will also be prepared if the
714 "pg_prepare_now" attribute is passed in and set to a true value.
715 Similarly, the "pg_prepare_now" attribute can be set to 0 to ensure
716 that the statement is not prepared immediately, although the cases in
717 which you would want this are very rare. Finally, you can set the
718 default behavior of all prepare statements by setting the
719 "pg_prepare_now" attribute on the database handle:
720 $dbh->{pg_prepare_now} = 1;
722 The following two examples will be prepared right away:
724 $sth->prepare("SELECT 123"); ## no placeholders
726 $sth->prepare("SELECT 123, ?", {pg_prepare_now => 1});
728 The following two examples will NOT be prepared right away:
730 $sth->prepare("SELECT 123, ?"); ## has a placeholder
732 $sth->prepare("SELECT 123", {pg_prepare_now => 0});
734 There are times when you may want to prepare a statement yourself. To
736 do this, simply send the "PREPARE" statement directly to the server
737 (e.g. with the "do" method). Create a statement handle and set the
738 prepared name via the "pg_prepare_name" attribute. The statement handle
739 can be created with a dummy statement, as it will not be executed.
740 However, it should have the same number of placeholders as your
741 prepared statement. Example:
742 $dbh->do('PREPARE mystat AS SELECT COUNT(*) FROM pg_class WHERE reltuples < ?');
744 $sth = $dbh->prepare('SELECT ?');
745 $sth->bind_param(1, 1, SQL_INTEGER);
746 $sth->{pg_prepare_name} = 'mystat';
747 $sth->execute(123);
748 The above will run the equivalent of this query on the backend:
750 EXECUTE mystat(123);
752 which is the equivalent of:
754 SELECT COUNT(*) FROM pg_class WHERE reltuples < 123;
756 You can force DBD::Pg to send your query directly to the server by
758 adding the "pg_direct" attribute to your prepare call. This is not
759 recommended, but is added just in case you need it.
760 Placeholders
762 There are three types of placeholders that can be used in DBD::Pg. The
764 first is the "question mark" type, in which each placeholder is
765 represented by a single question mark character. This is the method
766 recommended by the DBI specs and is the most portable. Each question
767 mark is internally replaced by a "dollar sign number" in the order in
768 which they appear in the query (important when using "bind_param").
769 The method second type of placeholder is "dollar sign numbers". This is
771 the method that Postgres uses internally and is overall probably the
772 best method to use if you do not need compatibility with other database
773 systems. DBD::Pg, like PostgreSQL, allows the same number to be used
774 more than once in the query. Numbers must start with "1" and increment
775 by one value (but can appear in any order within the query). If the
776 same number appears more than once in a query, it is treated as a
777 single parameter and all instances are replaced at once. Examples:
778 Not legal:
780 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $2'; # Does not start with 1
782 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $3'; # Missing 2
784 Legal:
786 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $1';
788 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $2';
790 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $2 AND $1'; # legal but confusing
792 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $2 AND reltuples > $1';
794 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $1 AND reltuples > $1';
796 In the final statement above, DBI thinks there is only one placeholder,
798 so this statement will replace both placeholders:
799 $sth->bind_param(1, 2045);
801 While a simple execute with no bind_param calls requires only a single
803 argument as well:
804 $sth->execute(2045);
806 The final placeholder type is "named parameters" in the format ":foo".
808 While this syntax is supported by DBD::Pg, its use is discouraged in
809 favor of dollar-sign numbers.
810 The different types of placeholders cannot be mixed within a statement,
812 but you may use different ones for each statement handle you have. This
813 is confusing at best, so stick to one style within your program.
814 If your queries use operators that contain question marks (e.g. some of
816 the native Postgres geometric operators) or array slices (e.g.
817 "data[100:300]"), you can tell DBD::Pg to ignore any non-dollar sign
818 placeholders by setting the "pg_placeholder_dollaronly" attribute at
819 either the database handle or the statement handle level. Examples:
820 $dbh->{pg_placeholder_dollaronly} = 1;
822 $sth = $dbh->prepare(q{SELECT * FROM mytable WHERE lseg1 ?# lseg2 AND name = $1});
823 $sth->execute('segname');
824 Alternatively, you can set it at prepare time:
826 $sth = $dbh->prepare(q{SELECT * FROM mytable WHERE lseg1 ?-| lseg2 AND name = $1},
828 {pg_placeholder_dollaronly = 1});
829 $sth->execute('segname');
830 prepare_cached
832 $sth = $dbh->prepare_cached($statement, \%attr);
834 Implemented by DBI, no driver-specific impact. This method is most
836 useful when using a server that supports server-side prepares, and you
837 have asked the prepare to happen immediately via the "pg_prepare_now"
838 attribute.
839 do
841 $rv = $dbh->do($statement);
843 $rv = $dbh->do($statement, \%attr);
844 $rv = $dbh->do($statement, \%attr, @bind_values);
845 Prepare and execute a single statement. Returns the number of rows
847 affected if the query was successful, returns undef if an error
848 occurred, and returns -1 if the number of rows is unknown or not
849 available. Note that this method will return 0E0 instead of 0 for 'no
850 rows were affected', in order to always return a true value if no error
851 occurred.
852 If neither "\%attr" nor @bind_values is given, the query will be sent
854 directly to the server without the overhead of internally creating a
855 statement handle and running prepare and execute, for a measurable
856 speed increase.
857 Note that an empty statement (a string with no length) will not be
859 passed to the server; if you want a simple test, use "SELECT 123" or
860 the "ping" method.
861 last_insert_id
863 $rv = $dbh->last_insert_id(undef, $schema, $table, undef);
865 $rv = $dbh->last_insert_id(undef, $schema, $table, undef, {sequence => $seqname});
866 Attempts to return the id of the last value to be inserted into a
868 table. You can either provide a sequence name (preferred) or provide a
869 table name with optional schema, and DBD::Pg will attempt to find the
870 sequence itself. The current value of the sequence is returned by a
871 call to the "CURRVAL()" PostgreSQL function. This will fail if the
872 sequence has not yet been used in the current database connection.
873 If you do not know the name of the sequence, you can provide a table
875 name and DBD::Pg will attempt to return the correct value. To do this,
876 there must be at least one column in the table with a "NOT NULL"
877 constraint, that has a unique constraint, and which uses a sequence as
878 a default value. If more than one column meets these conditions, the
879 primary key will be used. This involves some looking up of things in
880 the system table, so DBD::Pg will cache the sequence name for
881 subsequent calls. If you need to disable this caching for some reason,
882 (such as the sequence name changing), you can control it by adding
883 "pg_cache =" 0> to the final (hashref) argument for last_insert_id.
884 Please keep in mind that this method is far from foolproof, so make
886 your script use it properly. Specifically, make sure that it is called
887 immediately after the insert, and that the insert does not add a value
888 to the column that is using the sequence as a default value. However,
889 because we are using sequences, you can be sure that the value you got
890 back has not been used by any other process.
891 Some examples:
893 $dbh->do('CREATE SEQUENCE lii_seq START 1');
895 $dbh->do(q{CREATE TABLE lii (
896 foobar INTEGER NOT NULL UNIQUE DEFAULT nextval('lii_seq'),
897 baz VARCHAR)});
898 $SQL = 'INSERT INTO lii(baz) VALUES (?)';
899 $sth = $dbh->prepare($SQL);
900 for (qw(uno dos tres cuatro)) {
901 $sth->execute($_);
902 my $newid = $dbh->last_insert_id(undef,undef,undef,undef,{sequence=>'lii_seq'});
903 print "Last insert id was $newid\n";
904 }
905 If you did not want to worry about the sequence name:
907 $dbh->do('CREATE TABLE lii2 (
909 foobar SERIAL UNIQUE,
910 baz VARCHAR)');
911 $SQL = 'INSERT INTO lii2(baz) VALUES (?)';
912 $sth = $dbh->prepare($SQL);
913 for (qw(uno dos tres cuatro)) {
914 $sth->execute($_);
915 my $newid = $dbh->last_insert_id(undef,undef,"lii2",undef);
916 print "Last insert id was $newid\n";
917 }
918 commit
920 $rv = $dbh->commit;
922 Issues a COMMIT to the server, indicating that the current transaction
924 is finished and that all changes made will be visible to other
925 processes. If AutoCommit is enabled, then a warning is given and no
926 COMMIT is issued. Returns true on success, false on error. See also
927 the the section on "Transactions".
928 rollback
930 $rv = $dbh->rollback;
932 Issues a ROLLBACK to the server, which discards any changes made in the
934 current transaction. If AutoCommit is enabled, then a warning is given
935 and no ROLLBACK is issued. Returns true on success, and false on error.
936 See also the the section on "Transactions".
937 begin_work
939 This method turns on transactions until the next call to "commit" or
941 "rollback", if "AutoCommit" is currently enabled. If it is not enabled,
942 calling begin_work will issue an error. Note that the transaction will
943 not actually begin until the first statement after begin_work is
944 called. Example:
945 $dbh->{AutoCommit} = 1;
947 $dbh->do('INSERT INTO foo VALUES (123)'); ## Changes committed immediately
948 $dbh->begin_work();
949 ## Not in a transaction yet, but AutoCommit is set to 0
950 $dbh->do("INSERT INTO foo VALUES (345)");
952 ## DBD::PG actually issues two statements here:
953 ## BEGIN;
954 ## INSERT INTO foo VALUES (345)
955 ## We are now in a transaction
956 $dbh->commit();
958 ## AutoCommit is now set to 1 again
959 disconnect
961 $rv = $dbh->disconnect;
963 Disconnects from the Postgres database. Any uncommitted changes will be
965 rolled back upon disconnection. It's good policy to always explicitly
966 call commit or rollback at some point before disconnecting, rather than
967 relying on the default rollback behavior.
968 This method may give warnings about "disconnect invalidates X active
970 statement handle(s)". This means that you called "$sth->execute()" but
971 did not finish fetching all the rows from them. To avoid seeing this
972 warning, either fetch all the rows or call "$sth->finish()" for each
973 executed statement handle.
974 If the script exits before disconnect is called (or, more precisely, if
976 the database handle is no longer referenced by anything), then the
977 database handle's DESTROY method will call the rollback() and
978 disconnect() methods automatically. It is best to explicitly disconnect
979 rather than rely on this behavior.
980 quote
982 $rv = $dbh->quote($value, $data_type);
984 This module implements its own "quote" method. For simple string types,
986 both backslashes and single quotes are doubled. You may also quote
987 arrayrefs and receive a string suitable for passing into Postgres array
988 columns.
989 If the value contains backslashes, and the server is version 8.1 or
991 higher, then the escaped string syntax will be used (which places a
992 capital E before the first single quote). This syntax is always used
993 when quoting bytea values on servers 8.1 and higher.
994 The "data_type" argument is optional and should be one of the type
996 constants exported by DBD::Pg (such as PG_BYTEA). In addition to
997 string, bytea, char, bool, and other standard types, the following
998 geometric types are supported: point, line, lseg, box, path, polygon,
999 and circle (PG_POINT, PG_LINE, PG_LSEG, PG_BOX, PG_PATH, PG_POLYGON,
1000 and PG_CIRCLE respectively).
1001 NOTE: The undocumented (and invalid) support for the "SQL_BINARY" data
1003 type is officially deprecated. Use "PG_BYTEA" with "bind_param()"
1004 instead:
1005 $rv = $sth->bind_param($param_num, $bind_value,
1007 { pg_type => PG_BYTEA });
1008 quote_identifier
1010 $string = $dbh->quote_identifier( $name );
1012 $string = $dbh->quote_identifier( undef, $schema, $table);
1013 Returns a quoted version of the supplied string, which is commonly a
1015 schema, table, or column name. The three argument form will return the
1016 schema and the table together, separated by a dot. Examples:
1017 print $dbh->quote_identifier('grapefruit'); ## Prints: "grapefruit"
1019 print $dbh->quote_identifier('juicy fruit'); ## Prints: "juicy fruit"
1021 print $dbh->quote_identifier(undef, 'public', 'pg_proc');
1023 ## Prints: "public"."pg_proc"
1024 pg_notifies
1026 $ret = $dbh->pg_notifies;
1028 Looks for any asynchronous notifications received and returns either
1030 "undef" or a reference to a three-element array consisting of an event
1031 name, the PID of the backend that sent the NOTIFY command, and the
1032 optional payload string. Note that this does not check if the
1033 connection to the database is still valid first - for that, use the
1034 c<ping> method. You may need to commit if not in autocommit mode - new
1035 notices will not be picked up while in the middle of a transaction. An
1036 example:
1037 $dbh->do("LISTEN abc");
1039 $dbh->do("LISTEN def");
1040 ## Hang around until we get the message we want
1042 LISTENLOOP: {
1043 while (my $notify = $dbh->pg_notifies) {
1044 my ($name, $pid, $payload) = @$notify;
1045 print qq{I received notice "$name" from PID $pid, payload was "$payload"\n};
1046 ## Do something based on the notice received
1047 }
1048 $dbh->ping() or die qq{Ping failed!};
1049 $dbh->commit();
1050 sleep(5);
1051 redo;
1052 }
1053 Payloads will always be an empty string unless you are connecting to a
1055 Postgres server version 8.5 or higher.
1056 ping
1058 $rv = $dbh->ping;
1060 This "ping" method is used to check the validity of a database handle.
1062 The value returned is either 0, indicating that the connection is no
1063 longer valid, or a positive integer, indicating the following:
1064 Value Meaning
1066 --------------------------------------------------
1067 1 Database is idle (not in a transaction)
1068 2 Database is active, there is a command in progress (usually seen after a COPY command)
1069 3 Database is idle within a transaction
1070 4 Database is idle, within a failed transaction
1071 Additional information on why a handle is not valid can be obtained by
1073 using the "pg_ping" method.
1074 pg_ping
1076 $rv = $dbh->pg_ping;
1078 This is a DBD::Pg-specific extension to the "ping" method. This will
1080 check the validity of a database handle in exactly the same way as
1081 "ping", but instead of returning a 0 for an invalid connection, it will
1082 return a negative number. So in addition to returning the positive
1083 numbers documented for "ping", it may also return the following:
1084 Value Meaning
1086 --------------------------------------------------
1087 -1 There is no connection to the database at all (e.g. after C<disconnect>)
1088 -2 An unknown transaction status was returned (e.g. after forking)
1089 -3 The handle exists, but no data was returned from a test query.
1090 In practice, you should only ever see -1 and -2.
1092 get_info
1094 $value = $dbh->get_info($info_type);
1096 Supports a very large set (> 250) of the information types, including
1098 the minimum recommended by DBI.
1099 table_info
1101 $sth = $dbh->table_info(undef, $schema, $table, $type);
1103 Returns all tables and views visible to the current user. The schema
1105 and table arguments will do a "LIKE" search if a percent sign ("%") or
1106 an underscore ("_") is detected in the argument. The $type argument
1107 accepts a value of either "TABLE" or "VIEW" (using both is the default
1108 action). Note that a statement handle is returned, and not a direct
1109 list of tables. See the examples below for ways to handle this.
1110 The following fields are returned:
1112 TABLE_CAT: Always NULL, as Postgres does not have the concept of
1114 catalogs.
1115 TABLE_SCHEM: The name of the schema that the table or view is in.
1117 TABLE_NAME: The name of the table or view.
1119 TABLE_TYPE: The type of object returned. Will be one of "TABLE",
1121 "VIEW", or "SYSTEM TABLE".
1122 The TABLE_SCHEM and TABLE_NAME will be quoted via "quote_ident()".
1124 Two additional fields specific to DBD::Pg are returned:
1126 pg_schema: the unquoted name of the schema
1128 pg_table: the unquoted name of the table
1130 If your database supports tablespaces (version 8.0 or greater), two
1132 additional DBD::Pg specific fields are returned:
1133 pg_tablespace_name: the name of the tablespace the table is in
1135 pg_tablespace_location: the location of the tablespace the table is in
1137 Tables that have not been assigned to a particular tablespace (or
1139 views) will return NULL ("undef") for both of the above field.
1140 Rows are returned alphabetically, with all tables first, and then all
1142 views.
1143 Examples of use:
1145 ## Display all tables and views in the public schema:
1147 $sth = $dbh->table_info('', 'public', undef, undef);
1148 for my $rel (@{$sth->fetchall_arrayref({})}) {
1149 print "$rel->{TABLE_TYPE} name is $rel->{TABLE_NAME}\n";
1150 }
1151 # Display the schema of all tables named 'foo':
1154 $sth = $dbh->table_info('', undef, 'foo', 'TABLE');
1155 for my $rel (@{$sth->fetchall_arrayref({})}) {
1156 print "Table name is $rel->{TABLE_SCHEM}.$rel->{TABLE_NAME}\n";
1157 }
1158 column_info
1160 $sth = $dbh->column_info( undef, $schema, $table, $column );
1162 Supported by this driver as proposed by DBI with the follow exceptions.
1164 These fields are currently always returned with NULL ("undef") values:
1165 TABLE_CAT
1167 BUFFER_LENGTH
1168 DECIMAL_DIGITS
1169 NUM_PREC_RADIX
1170 SQL_DATA_TYPE
1171 SQL_DATETIME_SUB
1172 CHAR_OCTET_LENGTH
1173 Also, six additional non-standard fields are returned:
1175 pg_type: data type with additional info i.e. "character varying(20)"
1177 pg_constraint: holds column constraint definition
1179 pg_schema: the unquoted name of the schema
1181 pg_table: the unquoted name of the table
1183 pg_column: the unquoted name of the column
1185 pg_enum_values: an array reference of allowed values for an enum column
1187 Note that the TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME fields all
1189 return output wrapped in quote_ident(). If you need the unquoted
1190 version, use the pg_ fields above.
1191 primary_key_info
1193 $sth = $dbh->primary_key_info( undef, $schema, $table, \%attr );
1195 Supported by this driver as proposed by DBI. There are no search
1197 patterns allowed, but leaving the $schema argument blank will cause the
1198 first table found in the schema search path to be used. An additional
1199 field, "DATA_TYPE", is returned and shows the data type for each of the
1200 arguments in the "COLUMN_NAME" field.
1201 This method will also return tablespace information for servers that
1203 support tablespaces. See the "table_info" entry for more information.
1204 The five additional custom fields returned are:
1206 pg_tablespace_name: name of the tablespace, if any
1208 pg_tablespace_location: location of the tablespace
1210 pg_schema: the unquoted name of the schema
1212 pg_table: the unquoted name of the table
1214 pg_column: the unquoted name of the column
1216 In addition to the standard format of returning one row for each column
1218 found for the primary key, you can pass the "pg_onerow" attribute to
1219 force a single row to be used. If the primary key has multiple columns,
1220 the "KEY_SEQ", "COLUMN_NAME", and "DATA_TYPE" fields will return a
1221 comma-delimited string. If the "pg_onerow" attribute is set to "2", the
1222 fields will be returned as an arrayref, which can be useful when
1223 multiple columns are involved:
1224 $sth = $dbh->primary_key_info('', '', 'dbd_pg_test', {pg_onerow => 2});
1226 if (defined $sth) {
1227 my $pk = $sth->fetchall_arrayref()->[0];
1228 print "Table $pk->[2] has a primary key on these columns:\n";
1229 for (my $x=0; defined $pk->[3][$x]; $x++) {
1230 print "Column: $pk->[3][$x] (data type: $pk->[6][$x])\n";
1231 }
1232 }
1233 primary_key
1235 @key_column_names = $dbh->primary_key(undef, $schema, $table);
1237 Simple interface to the "primary_key_info" method. Returns a list of
1239 the column names that comprise the primary key of the specified table.
1240 The list is in primary key column sequence order. If there is no
1241 primary key then an empty list is returned.
1242 foreign_key_info
1244 $sth = $dbh->foreign_key_info( $pk_catalog, $pk_schema, $pk_table,
1246 $fk_catalog, $fk_schema, $fk_table );
1247 Supported by this driver as proposed by DBI, using the SQL/CLI variant.
1249 There are no search patterns allowed, but leaving the $schema argument
1250 blank will cause the first table found in the schema search path to be
1251 used. Two additional fields, "UK_DATA_TYPE" and "FK_DATA_TYPE", are
1252 returned to show the data type for the unique and foreign key columns.
1253 Foreign keys that have no named constraint (where the referenced column
1254 only has an unique index) will return "undef" for the "UK_NAME" field.
1255 statistics_info
1257 $sth = $dbh->statistics_info( undef, $schema, $table, $unique_only, $quick );
1259 Returns a statement handle that can be fetched from to give statistics
1261 information on a specific table and its indexes. The $table argument is
1262 mandatory. The $schema argument is optional but recommended. The
1263 $unique_only argument, if true, causes only information about unique
1264 indexes to be returned. The $quick argument is not used by DBD::Pg. For
1265 information on the format of the rows returned, please see the DBI
1266 documentation.
1267 tables
1269 @names = $dbh->tables( undef, $schema, $table, $type, \%attr );
1271 Supported by this driver as proposed by DBI. This method returns all
1273 tables and/or views which are visible to the current user: see
1274 "table_info" for more information about the arguments. The name of the
1275 schema appears before the table or view name. This can be turned off by
1276 adding in the "pg_noprefix" attribute:
1277 my @tables = $dbh->tables( '', '', 'dbd_pg_test', '', {pg_noprefix => 1} );
1279 type_info_all
1281 $type_info_all = $dbh->type_info_all;
1283 Supported by this driver as proposed by DBI. Information is only
1285 provided for SQL datatypes and for frequently used datatypes. The
1286 mapping between the PostgreSQL typename and the SQL92 datatype (if
1287 possible) has been done according to the following table:
1288 +---------------+------------------------------------+
1290 | typname | SQL92 |
1291 |---------------+------------------------------------|
1292 | bool | BOOL |
1293 | text | / |
1294 | bpchar | CHAR(n) |
1295 | varchar | VARCHAR(n) |
1296 | int2 | SMALLINT |
1297 | int4 | INT |
1298 | int8 | / |
1299 | money | / |
1300 | float4 | FLOAT(p) p<7=float4, p<16=float8 |
1301 | float8 | REAL |
1302 | abstime | / |
1303 | reltime | / |
1304 | tinterval | / |
1305 | date | / |
1306 | time | / |
1307 | datetime | / |
1308 | timespan | TINTERVAL |
1309 | timestamp | TIMESTAMP |
1310 +---------------+------------------------------------+
1311 type_info
1313 @type_info = $dbh->type_info($data_type);
1315 Returns a list of hash references holding information about one or more
1317 variants of $data_type. See the DBI documentation for more details.
1318 pg_server_trace
1320 $dbh->pg_server_trace($filehandle);
1322 Writes debugging information from the PostgreSQL backend to a file.
1324 This is not related to the DBI "trace" method and you should not use
1325 this method unless you know what you are doing. If you do enable this,
1326 be aware that the file will grow very large, very quick. To stop
1327 logging to the file, use the "pg_server_untrace" method. The first
1328 argument must be a file handle, not a filename. Example:
1329 my $pid = $dbh->{pg_pid};
1331 my $file = "pgbackend.$pid.debug.log";
1332 open(my $fh, ">$file") or die qq{Could not open "$file": $!\n};
1333 $dbh->pg_server_trace($fh);
1334 ## Run code you want to trace here
1335 $dbh->pg_server_untrace;
1336 close($fh);
1337 pg_server_untrace
1339 $dbh->pg_server_untrace;
1341 Stop server logging to a previously opened file.
1343 selectrow_array
1345 @row_ary = $dbh->selectrow_array($sql);
1347 @row_ary = $dbh->selectrow_array($sql, \%attr);
1348 @row_ary = $dbh->selectrow_array($sql, \%attr, @bind_values);
1349 Returns an array of row information after preparing and executing the
1351 provided SQL string. The rows are returned by calling "fetchrow_array".
1352 The string can also be a statement handle generated by a previous
1353 prepare. Note that only the first row of data is returned. If called in
1354 a scalar context, only the first column of the first row is returned.
1355 Because this is not portable, it is not recommended that you use this
1356 method in that way.
1357 selectrow_arrayref
1359 $ary_ref = $dbh->selectrow_arrayref($statement);
1361 $ary_ref = $dbh->selectrow_arrayref($statement, \%attr);
1362 $ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @bind_values);
1363 Exactly the same as "selectrow_array", except that it returns a
1365 reference to an array, by internal use of the "fetchrow_arrayref"
1366 method.
1367 selectrow_hashref
1369 $hash_ref = $dbh->selectrow_hashref($sql);
1371 $hash_ref = $dbh->selectrow_hashref($sql, \%attr);
1372 $hash_ref = $dbh->selectrow_hashref($sql, \%attr, @bind_values);
1373 Exactly the same as "selectrow_array", except that it returns a
1375 reference to an hash, by internal use of the "fetchrow_hashref" method.
1376 clone
1378 $other_dbh = $dbh->clone();
1380 Creates a copy of the database handle by connecting with the same
1382 parameters as the original handle, then trying to merge the attributes.
1383 See the DBI documentation for complete usage.
1384 Database Handle Attributes
1386 AutoCommit (boolean)
1387 Supported by DBD::Pg as proposed by DBI. According to the
1389 classification of DBI, PostgreSQL is a database in which a transaction
1390 must be explicitly started. Without starting a transaction, every
1391 change to the database becomes immediately permanent. The default of
1392 AutoCommit is on, but this may change in the future, so it is highly
1393 recommended that you explicitly set it when calling "connect". For
1394 details see the notes about "Transactions" elsewhere in this document.
1395 pg_bool_tf (boolean)
1397 DBD::Pg specific attribute. If true, boolean values will be returned as
1399 the characters 't' and 'f' instead of '1' and '0'.
1400 ReadOnly (boolean)
1402 $dbh->{ReadOnly} = 1;
1404 Specifies if the current database connection should be in read-only
1406 mode or not. In this mode, changes that change the database are not
1407 allowed and will throw an error. Note: this method will not work if
1408 "AutoCommit" is true. The read-only effect is accomplished by sending a
1409 SET TRANSACTION READ ONLY after every begin. For more details, please
1410 see:
1411 http://www.postgresql.org/docs/current/interactive/sql-set-transaction.html
1413 Please not that this method is not foolproof: there are still ways to
1415 update the database. Consider this a safety net to catch applications
1416 that should not be issuing commands such as INSERT, UPDATE, or DELETE.
1417 This method method requires DBI version 1.55 or better.
1419 pg_server_prepare (integer)
1421 DBD::Pg specific attribute. Indicates if DBD::Pg should attempt to use
1423 server-side prepared statements. The default value, 1, indicates that
1424 prepared statements should be used whenever possible. See the section
1425 on the "prepare" method for more information.
1426 pg_placeholder_dollaronly (boolean)
1428 DBD::Pg specific attribute. Defaults to false. When true, question
1430 marks inside of statements are not treated as placeholders. Useful for
1431 statements that contain unquoted question marks, such as geometric
1432 operators.
1433 pg_enable_utf8 (boolean)
1435 DBD::Pg specific attribute. If true, then the "utf8" flag will be
1437 turned on for returned character data (if the data is valid UTF-8). For
1438 details about the "utf8" flag, see the "Encode" module. This attribute
1439 is only relevant under perl 5.8 and later.
1440 pg_errorlevel (integer)
1442 DBD::Pg specific attribute. Sets the amount of information returned by
1444 the server's error messages. Valid entries are 0, 1, and 2. Any other
1445 number will be forced to the default value of 1.
1446 A value of 0 ("TERSE") will show severity, primary text, and position
1448 only and will usually fit on a single line. A value of 1 ("DEFAULT")
1449 will also show any detail, hint, or context fields. A value of 2
1450 ("VERBOSE") will show all available information.
1451 pg_lib_version (integer, read-only)
1453 DBD::Pg specific attribute. Indicates which version of PostgreSQL that
1455 DBD::Pg was compiled against. In other words, which libraries were
1456 used. Returns a number with major, minor, and revision together;
1457 version 8.1.4 would be returned as 80104.
1458 pg_server_version (integer, read-only)
1460 DBD::Pg specific attribute. Indicates which version of PostgreSQL that
1462 the current database handle is connected to. Returns a number with
1463 major, minor, and revision together; version 8.0.1 would be 80001.
1464 Name (string, read-only)
1466 Returns the name of the current database. This is the same as the DSN,
1468 without the "dbi:Pg:" part. Before version 2.0.0, this only returned
1469 the bare database name (e.g. 'foo'). From version 2.0.0 onwards, it
1470 returns the more correct output (e.g. 'dbname=foo')
1471 Username (string, read-only)
1473 Returns the name of the user connected to the database.
1475 pg_db (string, read-only)
1477 DBD::Pg specific attribute. Returns the name of the current database.
1479 pg_user (string, read-only)
1481 DBD::Pg specific attribute. Returns the name of the user that connected
1483 to the server.
1484 pg_host (string, read-only)
1486 DBD::Pg specific attribute. Returns the host of the current server
1488 connection. Locally connected hosts will return an empty string.
1489 pg_port (integer, read-only)
1491 DBD::Pg specific attribute. Returns the port of the connection to the
1493 server.
1494 pg_socket (integer, read-only)
1496 DBD::Pg specific attribute. Returns the file description number of the
1498 connection socket to the server.
1499 pg_pass (string, read-only)
1501 DBD::Pg specific attribute. Returns the password used to connect to the
1503 server.
1504 pg_options (string, read-only)
1506 DBD::Pg specific attribute. Returns the command-line options passed to
1508 the server. May be an empty string.
1509 pg_default_port (integer, read-only)
1511 DBD::Pg specific attribute. Returns the default port used if none is
1513 specifically given.
1514 pg_pid (integer, read-only)
1516 DBD::Pg specific attribute. Returns the process id (PID) of the backend
1518 server process handling the connection.
1519 pg_prepare_now (boolean)
1521 DBD::Pg specific attribute. Default is off. If true, then the "prepare"
1523 method will immediately prepare commands, rather than waiting until the
1524 first execute.
1525 pg_expand_array (boolean)
1527 DBD::Pg specific attribute. Defaults to true. If false, arrays returned
1529 from the server will not be changed into a Perl arrayref, but remain as
1530 a string.
1531 pg_async_status (integer, read-only)
1533 DBD::Pg specific attribute. Returns the current status of an
1535 asynchronous command. 0 indicates no asynchronous command is in
1536 progress, 1 indicates that an asynchronous command has started and -1
1537 indicated that an asynchronous command has been cancelled.
1538 pg_standard_conforming_strings (boolean, read-only)
1540 DBD::Pg specific attribute. Returns true if the server is currently
1542 using standard conforming strings. Only available if the target server
1543 is version 8.2 or better.
1544 pg_INV_READ (integer, read-only)
1546 Constant to be used for the mode in "lo_creat" and "lo_open".
1548 pg_INV_WRITE (integer, read-only)
1550 Constant to be used for the mode in "lo_creat" and "lo_open".
1552 Driver (handle, read-only)
1554 Holds the handle of the parent driver. The only recommended use for
1556 this is to find the name of the driver using:
1557 $dbh->{Driver}->{Name}
1559 pg_protocol (integer, read-only)
1561 DBD::Pg specific attribute. Returns the version of the PostgreSQL
1563 server. If DBD::Pg is unable to figure out the version, it will return
1564 a "0". Otherwise, a "3" is returned.
1565 RowCacheSize
1567 Not used by DBD::Pg
1569
1571 Statement Handle Methods
1572 bind_param
1573 $rv = $sth->bind_param($param_num, $bind_value);
1575 $rv = $sth->bind_param($param_num, $bind_value, $bind_type);
1576 $rv = $sth->bind_param($param_num, $bind_value, \%attr);
1577 Allows the user to bind a value and/or a data type to a placeholder.
1579 This is especially important when using server-side prepares. See the
1580 "prepare" method for more information.
1581 The value of $param_num is a number if using the '?' or '$1' style
1583 placeholders. If using ":foo" style placeholders, the complete name
1584 (e.g. ":foo") must be given. For numeric values, you can either use a
1585 number or use a literal '$1'. See the examples below.
1586 The $bind_value argument is fairly self-explanatory. A value of "undef"
1588 will bind a "NULL" to the placeholder. Using "undef" is useful when you
1589 want to change just the type and will be overwriting the value later.
1590 (Any value is actually usable, but "undef" is easy and efficient).
1591 The "\%attr" hash is used to indicate the data type of the placeholder.
1593 The default value is "varchar". If you need something else, you must
1594 use one of the values provided by DBI or by DBD::Pg. To use a SQL
1595 value, modify your "use DBI" statement at the top of your script as
1596 follows:
1597 use DBI qw(:sql_types);
1599 This will import some constants into your script. You can plug those
1601 directly into the "bind_param" call. Some common ones that you will
1602 encounter are:
1603 SQL_INTEGER
1605 To use PostgreSQL data types, import the list of values like this:
1607 use DBD::Pg qw(:pg_types);
1609 You can then set the data types by setting the value of the "pg_type"
1611 key in the hash passed to "bind_param". The current list of Postgres
1612 data types exported is:
1613 PG_ABSTIME PG_ABSTIMEARRAY PG_ACLITEM PG_ACLITEMARRAY PG_ANY PG_ANYARRAY
1615 PG_ANYELEMENT PG_ANYENUM PG_ANYNONARRAY PG_BIT PG_BITARRAY PG_BOOL
1616 PG_BOOLARRAY PG_BOX PG_BOXARRAY PG_BPCHAR PG_BPCHARARRAY PG_BYTEA
1617 PG_BYTEAARRAY PG_CHAR PG_CHARARRAY PG_CID PG_CIDARRAY PG_CIDR
1618 PG_CIDRARRAY PG_CIRCLE PG_CIRCLEARRAY PG_CSTRING PG_CSTRINGARRAY PG_DATE
1619 PG_DATEARRAY PG_FLOAT4 PG_FLOAT4ARRAY PG_FLOAT8 PG_FLOAT8ARRAY PG_GTSVECTOR
1620 PG_GTSVECTORARRAY PG_INET PG_INETARRAY PG_INT2 PG_INT2ARRAY PG_INT2VECTOR
1621 PG_INT2VECTORARRAY PG_INT4 PG_INT4ARRAY PG_INT8 PG_INT8ARRAY PG_INTERNAL
1622 PG_INTERVAL PG_INTERVALARRAY PG_LANGUAGE_HANDLER PG_LINE PG_LINEARRAY PG_LSEG
1623 PG_LSEGARRAY PG_MACADDR PG_MACADDRARRAY PG_MONEY PG_MONEYARRAY PG_NAME
1624 PG_NAMEARRAY PG_NUMERIC PG_NUMERICARRAY PG_OID PG_OIDARRAY PG_OIDVECTOR
1625 PG_OIDVECTORARRAY PG_OPAQUE PG_PATH PG_PATHARRAY PG_PG_ATTRIBUTE PG_PG_CLASS
1626 PG_PG_PROC PG_PG_TYPE PG_POINT PG_POINTARRAY PG_POLYGON PG_POLYGONARRAY
1627 PG_RECORD PG_RECORDARRAY PG_REFCURSOR PG_REFCURSORARRAY PG_REGCLASS PG_REGCLASSARRAY
1628 PG_REGCONFIG PG_REGCONFIGARRAY PG_REGDICTIONARY PG_REGDICTIONARYARRAY PG_REGOPER PG_REGOPERARRAY
1629 PG_REGOPERATOR PG_REGOPERATORARRAY PG_REGPROC PG_REGPROCARRAY PG_REGPROCEDURE PG_REGPROCEDUREARRAY
1630 PG_REGTYPE PG_REGTYPEARRAY PG_RELTIME PG_RELTIMEARRAY PG_SMGR PG_TEXT
1631 PG_TEXTARRAY PG_TID PG_TIDARRAY PG_TIME PG_TIMEARRAY PG_TIMESTAMP
1632 PG_TIMESTAMPARRAY PG_TIMESTAMPTZ PG_TIMESTAMPTZARRAY PG_TIMETZ PG_TIMETZARRAY PG_TINTERVAL
1633 PG_TINTERVALARRAY PG_TRIGGER PG_TSQUERY PG_TSQUERYARRAY PG_TSVECTOR PG_TSVECTORARRAY
1634 PG_TXID_SNAPSHOT PG_TXID_SNAPSHOTARRAY PG_UNKNOWN PG_UUID PG_UUIDARRAY PG_VARBIT
1635 PG_VARBITARRAY PG_VARCHAR PG_VARCHARARRAY PG_VOID PG_XID PG_XIDARRAY
1636 PG_XML PG_XMLARRAY
1637 Data types are "sticky," in that once a data type is set to a certain
1639 placeholder, it will remain for that placeholder, unless it is
1640 explicitly set to something else afterwards. If the statement has
1641 already been prepared, and you switch the data type to something else,
1642 DBD::Pg will re-prepare the statement for you before doing the next
1643 execute.
1644 Examples:
1646 use DBI qw(:sql_types);
1648 use DBD::Pg qw(:pg_types);
1649 $SQL = "SELECT id FROM ptable WHERE size > ? AND title = ?";
1651 $sth = $dbh->prepare($SQL);
1652 ## Both arguments below are bound to placeholders as "varchar"
1654 $sth->execute(123, "Merk");
1655 ## Reset the datatype for the first placeholder to an integer
1657 $sth->bind_param(1, undef, SQL_INTEGER);
1658 ## The "undef" bound above is not used, since we supply params to execute
1660 $sth->execute(123, "Merk");
1661 ## Set the first placeholder's value and data type
1663 $sth->bind_param(1, 234, { pg_type => PG_TIMESTAMP });
1664 ## Set the second placeholder's value and data type.
1666 ## We don't send a third argument, so the default "varchar" is used
1667 $sth->bind_param('$2', "Zool");
1668 ## We realize that the wrong data type was set above, so we change it:
1670 $sth->bind_param('$1', 234, { pg_type => SQL_INTEGER });
1671 ## We also got the wrong value, so we change that as well.
1673 ## Because the data type is sticky, we don't need to change it
1674 $sth->bind_param(1, 567);
1675 ## This executes the statement with 567 (integer) and "Zool" (varchar)
1677 $sth->execute();
1678 bind_param_inout
1680 $rv = $sth->bind_param_inout($param_num, \$scalar, 0);
1682 Experimental support for this feature is provided. The first argument
1684 to bind_param_inout should be a placeholder number. The second argument
1685 should be a reference to a scalar variable in your script. The third
1686 argument is not used and should simply be set to 0. Note that what this
1687 really does is assign a returned column to the variable, in the order
1688 in which the column appears. For example:
1689 my $foo = 123;
1691 $sth = $dbh->prepare("SELECT 1+?::int");
1692 $sth->bind_param_inout(1, \$foo, 0);
1693 $foo = 222;
1694 $sth->execute(444);
1695 $sth->fetch;
1696 The above will cause $foo to have a new value of "223" after the final
1698 fetch. Note that the variables bound in this manner are very sticky,
1699 and will trump any values passed in to execute. This is because the
1700 binding is done as late as possible, at the execute() stage, allowing
1701 the value to be changed between the time it was bound and the time the
1702 query is executed. Thus, the above execute is the same as:
1703 $sth->execute();
1705 bind_param_array
1707 $rv = $sth->bind_param_array($param_num, $array_ref_or_value)
1709 $rv = $sth->bind_param_array($param_num, $array_ref_or_value, $bind_type)
1710 $rv = $sth->bind_param_array($param_num, $array_ref_or_value, \%attr)
1711 Binds an array of values to a placeholder, so that each is used in turn
1713 by a call to the "execute_array" method.
1714 execute
1716 $rv = $sth->execute(@bind_values);
1718 Executes a previously prepared statement. In addition to "UPDATE",
1720 "DELETE", "INSERT" statements, for which it returns always the number
1721 of affected rows, the "execute" method can also be used for "SELECT ...
1722 INTO table" statements.
1723 The "prepare/bind/execute" process has changed significantly for
1725 PostgreSQL servers 7.4 and later: please see the "prepare()" and
1726 "bind_param()" entries for much more information.
1727 Setting one of the bind_values to "undef" is the equivalent of setting
1729 the value to NULL in the database. Setting the bind_value to
1730 $DBDPG_DEFAULT is equivalent to sending the literal string 'DEFAULT' to
1731 the backend. Note that using this option will force server-side
1732 prepares off until such time as PostgreSQL supports using DEFAULT in
1733 prepared statements.
1734 DBD::Pg also supports passing in arrays to execute: simply pass in an
1736 arrayref, and DBD::Pg will flatten it into a string suitable for input
1737 on the backend.
1738 If you are using Postgres version 8.2 or greater, you can also use any
1740 of the fetch methods to retrieve the values of a "RETURNING" clause
1741 after you execute an "UPDATE", "DELETE", or "INSERT". For example:
1742 $dbh->do(q{CREATE TABLE abc (id SERIAL, country TEXT)});
1744 $SQL = q{INSERT INTO abc (country) VALUES (?) RETURNING id};
1745 $sth = $dbh->prepare($SQL);
1746 $sth->execute('France');
1747 $countryid = $sth->fetch()->[0];
1748 $sth->execute('New Zealand');
1749 $countryid = $sth->fetch()->[0];
1750 execute_array
1752 $tuples = $sth->execute_array() or die $sth->errstr;
1754 $tuples = $sth->execute_array(\%attr) or die $sth->errstr;
1755 $tuples = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
1756 ($tuples, $rows) = $sth->execute_array(\%attr) or die $sth->errstr;
1758 ($tuples, $rows) = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
1759 Execute a prepared statement once for each item in a passed-in hashref,
1761 or items that were previously bound via the "bind_param_array" method.
1762 See the DBI documentation for more details.
1763 execute_for_fetch
1765 $tuples = $sth->execute_for_fetch($fetch_tuple_sub);
1767 $tuples = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
1768 ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub);
1770 ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
1771 Used internally by the "execute_array" method, and rarely used
1773 directly. See the DBI documentation for more details.
1774 fetchrow_arrayref
1776 $ary_ref = $sth->fetchrow_arrayref;
1778 Fetches the next row of data from the statement handle, and returns a
1780 reference to an array holding the column values. Any columns that are
1781 NULL are returned as undef within the array.
1782 If there are no more rows or if an error occurs, the this method return
1784 undef. You should check "$sth->err" afterwards (or use the "RaiseError"
1785 attribute) to discover if the undef returned was due to an error.
1786 Note that the same array reference is returned for each fetch, so don't
1788 store the reference and then use it after a later fetch. Also, the
1789 elements of the array are also reused for each row, so take care if you
1790 want to take a reference to an element. See also "bind_columns".
1791 fetchrow_array
1793 @ary = $sth->fetchrow_array;
1795 Similar to the "fetchrow_arrayref" method, but returns a list of column
1797 information rather than a reference to a list. Do not use this in a
1798 scalar context.
1799 fetchrow_hashref
1801 $hash_ref = $sth->fetchrow_hashref;
1803 $hash_ref = $sth->fetchrow_hashref($name);
1804 Fetches the next row of data and returns a hashref containing the name
1806 of the columns as the keys and the data itself as the values. Any NULL
1807 value is returned as as undef value.
1808 If there are no more rows or if an error occurs, the this method return
1810 undef. You should check "$sth->err" afterwards (or use the "RaiseError"
1811 attribute) to discover if the undef returned was due to an error.
1812 The optional $name argument should be either "NAME", "NAME_lc" or
1814 "NAME_uc", and indicates what sort of transformation to make to the
1815 keys in the hash.
1816 fetchall_arrayref
1818 $tbl_ary_ref = $sth->fetchall_arrayref();
1820 $tbl_ary_ref = $sth->fetchall_arrayref( $slice );
1821 $tbl_ary_ref = $sth->fetchall_arrayref( $slice, $max_rows );
1822 Returns a reference to an array of arrays that contains all the
1824 remaining rows to be fetched from the statement handle. If there are no
1825 more rows, an empty arrayref will be returned. If an error occurs, the
1826 data read in so far will be returned. Because of this, you should
1827 always check "$sth->err" after calling this method, unless "RaiseError"
1828 has been enabled.
1829 If $slice is an array reference, fetchall_arrayref uses the
1831 "fetchrow_arrayref" method to fetch each row as an array ref. If the
1832 $slice array is not empty then it is used as a slice to select
1833 individual columns by perl array index number (starting at 0, unlike
1834 column and parameter numbers which start at 1).
1835 With no parameters, or if $slice is undefined, fetchall_arrayref acts
1837 as if passed an empty array ref.
1838 If $slice is a hash reference, fetchall_arrayref uses
1840 "fetchrow_hashref" to fetch each row as a hash reference.
1841 See the DBI documentation for a complete discussion.
1843 fetchall_hashref
1845 $hash_ref = $sth->fetchall_hashref( $key_field );
1847 Returns a hashref containing all rows to be fetched from the statement
1849 handle. See the DBI documentation for a full discussion.
1850 finish
1852 $rv = $sth->finish;
1854 Indicates to DBI that you are finished with the statement handle and
1856 are not going to use it again. Only needed when you have not fetched
1857 all the possible rows.
1858 rows
1860 $rv = $sth->rows;
1862 Returns the number of rows returned by the last query. In contrast to
1864 many other DBD modules, the number of rows is available immediately
1865 after calling "$sth->execute". Note that the "execute" method itself
1866 returns the number of rows itself, which means that this method is
1867 rarely needed.
1868 bind_col
1870 $rv = $sth->bind_col($column_number, \$var_to_bind);
1872 $rv = $sth->bind_col($column_number, \$var_to_bind, \%attr );
1873 $rv = $sth->bind_col($column_number, \$var_to_bind, $bind_type );
1874 Binds a Perl variable and/or some attributes to an output column of a
1876 SELECT statement. Column numbers count up from 1. You do not need to
1877 bind output columns in order to fetch data.
1878 See the DBI documentation for a discussion of the optional parameters
1880 "\%attr" and $bind_type
1881 bind_columns
1883 $rv = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
1885 Calls the "bind_col" method for each column in the SELECT statement,
1887 using the supplied list.
1888 dump_results
1890 $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
1892 Fetches all the rows from the statement handle, calls "DBI::neat_list"
1894 for each row, and prints the results to $fh (which defaults to STDOUT).
1895 Rows are separated by $lsep (which defaults to a newline). Columns are
1896 separated by $fsep (which defaults to a comma). The $maxlen controls
1897 how wide the output can be, and defaults to 35.
1898 This method is designed as a handy utility for prototyping and testing
1900 queries. Since it uses "neat_list" to format and edit the string for
1901 reading by humans, it is not recommended for data transfer
1902 applications.
1903 blob_read
1905 $blob = $sth->blob_read($id, $offset, $len);
1907 Supported by DBD::Pg. This method is implemented by DBI but not
1909 currently documented by DBI, so this method might change.
1910 This method seems to be heavily influenced by the current
1912 implementation of blobs in Oracle. Nevertheless we try to be as
1913 compatible as possible. Whereas Oracle suffers from the limitation that
1914 blobs are related to tables and every table can have only one blob
1915 (datatype LONG), PostgreSQL handles its blobs independent of any table
1916 by using so-called object identifiers. This explains why the
1917 "blob_read" method is blessed into the STATEMENT package and not part
1918 of the DATABASE package. Here the field parameter has been used to
1919 handle this object identifier. The offset and len parameters may be set
1920 to zero, in which case the whole blob is fetched at once.
1921 See also the PostgreSQL-specific functions concerning blobs, which are
1923 available via the "func" interface.
1924 For further information and examples about blobs, please read the
1926 chapter about Large Objects in the PostgreSQL Programmer's Guide at
1927 <http://www.postgresql.org/docs/current/static/largeobjects.html>.
1928 Statement Handle Attributes
1930 NUM_OF_FIELDS (integer, read-only)
1931 Returns the number of columns returned by the current statement. A
1933 number will only be returned for SELECT statements, for SHOW statements
1934 (which always return 1), and for INSERT, UPDATE, and DELETE statements
1935 which contain a RETURNING clause. This method returns undef if called
1936 before "execute()".
1937 NUM_OF_PARAMS (integer, read-only)
1939 Returns the number of placeholders in the current statement.
1941 NAME (arrayref, read-only)
1943 Returns an arrayref of column names for the current statement. This
1945 method will only work for SELECT statements, for SHOW statements, and
1946 for INSERT, UPDATE, and DELETE statements which contain a RETURNING
1947 clause. This method returns undef if called before "execute()".
1948 NAME_lc (arrayref, read-only)
1950 The same as the "NAME" attribute, except that all column names are
1952 forced to lower case.
1953 NAME_uc (arrayref, read-only)
1955 The same as the "NAME" attribute, except that all column names are
1957 forced to upper case.
1958 NAME_hash (hashref, read-only)
1960 Similar to the "NAME" attribute, but returns a hashref of column names
1962 instead of an arrayref. The names of the columns are the keys of the
1963 hash, and the values represent the order in which the columns are
1964 returned, starting at 0. This method returns undef if called before
1965 "execute()".
1966 NAME_lc_hash (hashref, read-only)
1968 The same as the "NAME_hash" attribute, except that all column names are
1970 forced to lower case.
1971 NAME_uc_hash (hashref, read-only)
1973 The same as the "NAME_hash" attribute, except that all column names are
1975 forced to lower case.
1976 TYPE (arrayref, read-only)
1978 Returns an arrayref indicating the data type for each column in the
1980 statement. This method returns undef if called before "execute()".
1981 PRECISION (arrayref, read-only)
1983 Returns an arrayref of integer values for each column returned by the
1985 statement. The number indicates the precision for "NUMERIC" columns,
1986 the size in number of characters for "CHAR" and "VARCHAR" columns, and
1987 for all other types of columns it returns the number of bytes. This
1988 method returns undef if called before "execute()".
1989 SCALE (arrayref, read-only)
1991 Returns an arrayref of integer values for each column returned by the
1993 statement. The number indicates the scale of the that column. The only
1994 type that will return a value is "NUMERIC". This method returns undef
1995 if called before "execute()".
1996 NULLABLE (arrayref, read-only)
1998 Returns an arrayref of integer values for each column returned by the
2000 statement. The number indicates if the column is nullable or not. 0 =
2001 not nullable, 1 = nullable, 2 = unknown. This method returns undef if
2002 called before "execute()".
2003 Database (dbh, read-only)
2005 Returns the database handle this statement handle was created from.
2007 ParamValues (hash ref, read-only)
2009 Returns a reference to a hash containing the values currently bound to
2011 placeholders. If the "named parameters" type of placeholders are being
2012 used (such as ":foo"), then the keys of the hash will be the names of
2013 the placeholders (without the colon). If the "dollar sign numbers" type
2014 of placeholders are being used, the keys of the hash will be the
2015 numbers, without the dollar signs. If the "question mark" type is used,
2016 integer numbers will be returned, starting at one and increasing for
2017 every placeholder.
2018 If this method is called before "execute", the literal values passed in
2020 are returned. If called after "execute", then the quoted versions of
2021 the values are returned.
2022 ParamTypes (hash ref, read-only)
2024 Returns a reference to a hash containing the type names currently bound
2026 to placeholders. The keys are the same as returned by the ParamValues
2027 method. The values are hashrefs containing a single key value pair, in
2028 which the key is either 'TYPE' if the type has a generic SQL
2029 equivalent, and 'pg_type' if the type can only be expressed by a
2030 Postgres type. The value is the internal number corresponding to the
2031 type originally passed in. (Placeholders that have not yet been bound
2032 will return undef as the value). This allows the output of ParamTypes
2033 to be passed back to the "bind_param" method.
2034 Statement (string, read-only)
2036 Returns the statement string passed to the most recent "prepare" method
2038 called in this database handle, even if that method failed. This is
2039 especially useful where "RaiseError" is enabled and the exception
2040 handler checks $@ and sees that a "prepare" method call failed.
2041 pg_current_row (integer, read-only)
2043 DBD::Pg specific attribute. Returns the number of the tuple (row) that
2045 was most recently fetched. Returns zero before and after fetching is
2046 performed.
2047 pg_numbound (integer, read-only)
2049 DBD::Pg specific attribute. Returns the number of placeholders that are
2051 currently bound (via bind_param).
2052 pg_bound (hashref, read-only)
2054 DBD::Pg specific attribute. Returns a hash of all named placeholders.
2056 The key is the name of the placeholder, and the value is a 0 or a 1,
2057 indicating if the placeholder has been bound yet (e.g. via bind_param)
2058 pg_size (arrayref, read-only)
2060 DBD::Pg specific attribute. It returns a reference to an array of
2062 integer values for each column. The integer shows the size of the
2063 column in bytes. Variable length columns are indicated by -1.
2064 pg_type (arrayref, read-only)
2066 DBD::Pg specific attribute. It returns a reference to an array of
2068 strings for each column. The string shows the name of the data_type.
2069 pg_segments (arrayref, read-only)
2071 DBD::Pg specific attribute. Returns an arrayref of the query split on
2073 the placeholders.
2074 pg_oid_status (integer, read-only)
2076 DBD::Pg specific attribute. It returns the OID of the last INSERT
2078 command.
2079 pg_cmd_status (integer, read-only)
2081 DBD::Pg specific attribute. It returns the type of the last command.
2083 Possible types are: "INSERT", "DELETE", "UPDATE", "SELECT".
2084 pg_direct (boolean)
2086 DBD::Pg specific attribute. Default is false. If true, the query is
2088 passed directly to the backend without parsing for placeholders.
2089 pg_prepare_now (boolean)
2091 DBD::Pg specific attribute. Default is off. If true, the query will be
2093 immediately prepared, rather than waiting for the "execute" call.
2094 pg_prepare_name (string)
2096 DBD::Pg specific attribute. Specifies the name of the prepared
2098 statement to use for this statement handle. Not normally needed, see
2099 the section on the "prepare" method for more information.
2100 pg_server_prepare (integer)
2102 DBD::Pg specific attribute. Indicates if DBD::Pg should attempt to use
2104 server-side prepared statements for this statement handle. The default
2105 value, 1, indicates that prepared statements should be used whenever
2106 possible. See the section on the "prepare" method for more information.
2107 pg_placeholder_dollaronly (boolean)
2109 DBD::Pg specific attribute. Defaults to off. When true, question marks
2111 inside of the query being prepared are not treated as placeholders.
2112 Useful for statements that contain unquoted question marks, such as
2113 geometric operators.
2114 pg_async (integer)
2116 DBD::Pg specific attribute. Indicates the current behavior for
2118 asynchronous queries. See the section on "Asynchronous Constants" for
2119 more information.
2120 RowsInCache
2122 Not used by DBD::Pg
2124 RowCache
2126 Not used by DBD::Pg
2128 CursorName
2130 Not used by DBD::Pg. See the note about "Cursors" elsewhere in this
2132 document.
2133
2135 Transactions
2136 Transaction behavior is controlled via the "AutoCommit" attribute. For
2137 a complete definition of "AutoCommit" please refer to the DBI
2138 documentation.
2139 According to the DBI specification the default for "AutoCommit" is a
2141 true value. In this mode, any change to the database becomes valid
2142 immediately. Any "BEGIN", "COMMIT" or "ROLLBACK" statements will be
2143 rejected. DBD::Pg implements "AutoCommit" by issuing a "BEGIN"
2144 statement immediately before executing a statement, and a "COMMIT"
2145 afterwards. Note that preparing a statement is not always enough to
2146 trigger the first "BEGIN", as the actual "PREPARE" is usually postponed
2147 until the first call to "execute".
2148 Savepoints
2150 PostgreSQL version 8.0 introduced the concept of savepoints, which
2151 allows transactions to be rolled back to a certain point without
2152 affecting the rest of the transaction. DBD::Pg encourages using the
2153 following methods to control savepoints:
2154 "pg_savepoint"
2156 Creates a savepoint. This will fail unless you are inside of a
2158 transaction. The only argument is the name of the savepoint. Note that
2159 PostgreSQL DOES allow multiple savepoints with the same name to exist.
2160 $dbh->pg_savepoint("mysavepoint");
2162 "pg_rollback_to"
2164 Rolls the database back to a named savepoint, discarding any work
2166 performed after that point. If more than one savepoint with that name
2167 exists, rolls back to the most recently created one.
2168 $dbh->pg_rollback_to("mysavepoint");
2170 "pg_release"
2172 Releases (or removes) a named savepoint. If more than one savepoint
2174 with that name exists, it will only destroy the most recently created
2175 one. Note that all savepoints created after the one being released are
2176 also destroyed.
2177 $dbh->pg_release("mysavepoint");
2179 Asynchronous Queries
2181 It is possible to send a query to the backend and have your script do
2182 other work while the query is running on the backend. Both queries sent
2183 by the "do" method, and by the "execute" method can be sent
2184 asynchronously. (NOTE: This will only work if DBD::Pg has been compiled
2185 against Postgres libraries of version 8.0 or greater) The basic usage
2186 is as follows:
2187 use DBD::Pg ':async';
2189 print "Async do() example:\n";
2191 $dbh->do("SELECT long_running_query()", {pg_async => PG_ASYNC});
2192 do_something_else();
2193 {
2194 if ($dbh->pg_ready()) {
2195 $res = $pg_result();
2196 print "Result of do(): $res\n";
2197 }
2198 print "Query is still running...\n";
2199 if (cancel_request_received) {
2200 $dbh->pg_cancel();
2201 }
2202 sleep 1;
2203 redo;
2204 }
2205 print "Async prepare/execute example:\n";
2207 $sth = $dbh->prepare("SELECT long_running_query(1)", {pg_async => PG_ASYNC});
2208 $sth->execute();
2209 ## Changed our mind, cancel and run again:
2211 $sth = $dbh->prepare("SELECT 678", {pg_async => PG_ASYNC + PG_OLDQUERY_CANCEL});
2212 $sth->execute();
2213 do_something_else();
2215 if (!$sth->pg_ready) {
2217 do_another_thing();
2218 }
2219 ## We wait until it is done, and get the result:
2221 $res = $dbh->pg_result();
2222 Asynchronous Constants
2224 There are currently three asynchronous constants exported by DBD::Pg.
2226 You can import all of them by putting either of these at the top of
2227 your script:
2228 use DBD::Pg;
2230 use DBD::Pg ':async';
2232 You may also use the numbers instead of the constants, but using the
2234 constants is recommended as it makes your script more readable.
2235 PG_ASYNC
2237 This is a constant for the number 1. It is passed to either the
2238 "do" or the "prepare" method as a value to the pg_async key and
2239 indicates that the query should be sent asynchronously.
2240 PG_OLDQUERY_CANCEL
2242 This is a constant for the number 2. When passed to either the "do"
2243 or the "prepare" method, it causes any currently running
2244 asynchronous query to be cancelled and rolled back. It has no
2245 effect if no asynchronous query is currently running.
2246 PG_OLDQUERY_WAIT
2248 This is a constant for the number 4. When passed to either the "do"
2249 or the "prepare" method, it waits for any currently running
2250 asynchronous query to complete. It has no effect if there is no
2251 asynchronous query currently running.
2252 Asynchronous Methods
2254 pg_cancel
2256 This database-level method attempts to cancel any currently running
2257 asynchronous query. It returns true if the cancel succeeded, and
2258 false otherwise. Note that a query that has finished before this
2259 method is executed will also return false. WARNING: a successful
2260 cancellation will leave the database in an unusable state, so
2261 DBD::Pg will automatically clear out the error message and issue a
2262 ROLLBACK.
2263 $result = $dbh->pg_cancel();
2265 pg_ready
2267 This method can be called as a database handle method or (for
2268 convenience) as a statement handle method. Both simply see if a
2269 previously issued asynchronous query has completed yet. It returns
2270 true if the statement has finished, in which case you should then
2271 call the "pg_result" method. Calls to "pg_ready()" should only be
2272 used when you have other things to do while the query is running.
2273 If you simply want to wait until the query is done, do not call
2274 pg_ready() over and over, but simply call the pg_result() method.
2275 my $time = 0;
2277 while (!$dbh->pg_ready) {
2278 print "Query is still running. Seconds: $time\n";
2279 $time++;
2280 sleep 1;
2281 }
2282 $result = $dbh->pg_result;
2283 pg_result
2285 This database handle method returns the results of a previously
2286 issued asynchronous query. If the query is still running, this
2287 method will wait until it has finished. The result returned is the
2288 number of rows: the same thing that would have been returned by the
2289 asynchronous "do" or "execute" if it had been called without an
2290 asynchronous flag.
2291 $result = $dbh->pg_result;
2293 Asynchronous Examples
2295 Here are some working examples of asynchronous queries. Note that we'll
2297 use the pg_sleep function to emulate a long-running query.
2298 use strict;
2300 use warnings;
2301 use Time::HiRes 'sleep';
2302 use DBD::Pg ':async';
2303 my $dbh = DBI->connect('dbi:Pg:dbname=postgres', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
2305 ## Kick off a long running query on the first database:
2307 my $sth = $dbh->prepare("SELECT pg_sleep(?)", {pg_async => PG_ASYNC});
2308 $sth->execute(5);
2309 ## While that is running, do some other things
2311 print "Your query is processing. Thanks for waiting\n";
2312 check_on_the_kids(); ## Expensive sub, takes at least three seconds.
2313 while (!$dbh->pg_ready) {
2315 check_on_the_kids();
2316 ## If the above function returns quickly for some reason, we add a small sleep
2317 sleep 0.1;
2318 }
2319 print "The query has finished. Gathering results\n";
2321 my $result = $sth->pg_result;
2322 print "Result: $result\n";
2323 my $info = $sth->fetchall_arrayref();
2324 Without asynchronous queries, the above script would take about 8
2326 seconds to run: five seconds waiting for the execute to finish, then
2327 three for the check_on_the_kids() function to return. With asynchronous
2328 queries, the script takes about 6 seconds to run, and gets in two
2329 iterations of check_on_the_kids in the process.
2330 Here's an example showing the ability to cancel a long-running query.
2332 Imagine two slave databases in different geographic locations over a
2333 slow network. You need information as quickly as possible, so you query
2334 both at once. When you get an answer, you tell the other one to stop
2335 working on your query, as you don't need it anymore.
2336 use strict;
2338 use warnings;
2339 use Time::HiRes 'sleep';
2340 use DBD::Pg ':async';
2341 my $dbhslave1 = DBI->connect('dbi:Pg:dbname=postgres;host=slave1', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
2343 my $dbhslave2 = DBI->connect('dbi:Pg:dbname=postgres;host=slave2', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
2344 $SQL = "SELECT count(*) FROM largetable WHERE flavor='blueberry'";
2346 my $sth1 = $dbhslave1->prepare($SQL, {pg_async => PG_ASYNC});
2348 my $sth2 = $dbhslave2->prepare($SQL, {pg_async => PG_ASYNC});
2349 $sth1->execute();
2351 $sth2->execute();
2352 my $winner;
2354 while (!defined $winner) {
2355 if ($sth1->pg_ready) {
2356 $winner = 1;
2357 }
2358 elsif ($sth2->pg_ready) {
2359 $winner = 2;
2360 }
2361 Time::HiRes::sleep 0.05;
2362 }
2363 my $count;
2365 if ($winner == 1) {
2366 $sth2->pg_cancel();
2367 $sth1->pg_result();
2368 $count = $sth1->fetchall_arrayref()->[0][0];
2369 }
2370 else {
2371 $sth1->pg_cancel();
2372 $sth2->pg_result();
2373 $count = $sth2->fetchall_arrayref()->[0][0];
2374 }
2375 Array support
2377 DBD::Pg allows arrays (as arrayrefs) to be passed in to both the
2378 "quote" and the "execute" methods. In both cases, the array is
2379 flattened into a string representing a Postgres array.
2380 When fetching rows from a table that contains a column with an array
2382 type, the result will be passed back to your script as an arrayref.
2383 To turn off the automatic parsing of returned arrays into arrayrefs,
2385 you can set the attribute pg_expand_array, which is true by default.
2386 $dbh->{pg_expand_array} = 0;
2388 COPY support
2390 DBD::Pg allows for quick (bulk) reading and storing of data by using
2391 the COPY command. The basic process is to use "$dbh->do" to issue a
2392 COPY command, and then to either add rows using "pg_putcopydata", or to
2393 read them by using "pg_getcopydata".
2394 The first step is to put the server into "COPY" mode. This is done by
2396 sending a complete COPY command to the server, by using the "do"
2397 method. For example:
2398 $dbh->do("COPY foobar FROM STDIN");
2400 This would tell the server to enter a COPY IN mode (yes, that's
2402 confusing, but the mode is COPY IN because of the command COPY FROM).
2403 It is now ready to receive information via the "pg_putcopydata" method.
2404 The complete syntax of the COPY command is more complex and not
2405 documented here: the canonical PostgreSQL documentation for COPY can be
2406 found at:
2407 http://www.postgresql.org/docs/current/static/sql-copy.html
2409 Once a COPY command has been issued, no other SQL commands are allowed
2411 until "pg_putcopyend" has been issued (for COPY FROM), or the final
2412 "pg_getcopydata" has been called (for COPY TO).
2413 Note: All other COPY methods (pg_putline, pg_getline, etc.) are now
2415 heavily deprecated in favor of the pg_getcopydata, pg_putcopydata, and
2416 pg_putcopyend methods.
2417 pg_getcopydata
2419 Used to retrieve data from a table after the server has been put into a
2421 COPY OUT mode by calling "COPY tablename TO STDOUT". Data is always
2422 returned one data row at a time. The first argument to pg_getcopydata
2423 is the variable into which the data will be stored (this variable
2424 should not be undefined, or it may throw a warning, although it may be
2425 a reference). The pg_gecopydata method returns a number greater than 1
2426 indicating the new size of the variable, or a -1 when the COPY has
2427 finished. Once a -1 has been returned, no other action is necessary, as
2428 COPY mode will have already terminated. Example:
2429 $dbh->do("COPY mytable TO STDOUT");
2431 my @data;
2432 my $x=0;
2433 1 while $dbh->pg_getcopydata($data[$x++]) >= 0;
2434 There is also a variation of this method called pg_getcopydata_async,
2436 which, as the name suggests, returns immediately. The only difference
2437 from the original method is that this version may return a 0,
2438 indicating that the row is not ready to be delivered yet. When this
2439 happens, the variable has not been changed, and you will need to call
2440 the method again until you get a non-zero result. (Data is still
2441 always returned one data row at a time.)
2442 pg_putcopydata
2444 Used to put data into a table after the server has been put into COPY
2446 IN mode by calling "COPY tablename FROM STDIN". The only argument is
2447 the data you want inserted. Issue a pg_putcopyend() when you have added
2448 all your rows.
2449 The default delimiter is a tab character, but this can be changed in
2451 the COPY statement. Returns a 1 on successful input. Examples:
2452 ## Simple example:
2454 $dbh->do("COPY mytable FROM STDIN");
2455 $dbh->pg_putcopydata("123\tPepperoni\t3\n");
2456 $dbh->pg_putcopydata("314\tMushroom\t8\n");
2457 $dbh->pg_putcopydata("6\tAnchovies\t100\n");
2458 $dbh->pg_putcopyend();
2459 ## This example uses explicit columns and a custom delimiter
2461 $dbh->do("COPY mytable(flavor, slices) FROM STDIN WITH DELIMITER '~'");
2462 $dbh->pg_putcopydata("Pepperoni~123\n");
2463 $dbh->pg_putcopydata("Mushroom~314\n");
2464 $dbh->pg_putcopydata("Anchovies~6\n");
2465 $dbh->pg_putcopyend();
2466 pg_putcopyend
2468 When you are finished with pg_putcopydata, call pg_putcopyend to let
2470 the server know that you are done, and it will return to a normal, non-
2471 COPY state. Returns a 1 on success. This method will fail if called
2472 when not in COPY IN mode.
2473 Large Objects
2475 DBD::Pg supports all largeobject functions provided by libpq via the
2476 "func" method. Please note that access to a large object, even read-
2477 only large objects, must be put into a transaction.
2478 Cursors
2480 Although PostgreSQL supports cursors, they have not been used in the
2481 current implementation. When DBD::Pg was created, cursors in PostgreSQL
2482 could only be used inside a transaction block. Because only one
2483 transaction block at a time is allowed, this would have implied the
2484 restriction not to use any nested "SELECT" statements. Therefore the
2485 "execute" method fetches all data at once into data structures located
2486 in the front-end application. This fact must to be considered when
2487 selecting large amounts of data!
2488 You can use cursors in your application, but you'll need to do a little
2490 work. First you must declare your cursor. Now you can issue queries
2491 against the cursor, then select against your queries. This typically
2492 results in a double loop, like this:
2493 # WITH HOLD is not needed if AutoCommit is off
2495 $dbh->do("DECLARE csr CURSOR WITH HOLD FOR $sql");
2496 while (1) {
2497 my $sth = $dbh->prepare("fetch 1000 from csr");
2498 $sth->execute;
2499 last if 0 == $sth->rows;
2500 while (my $row = $sth->fetchrow_hashref) {
2502 # Do something with the data.
2503 }
2504 }
2505 $dbh->do("CLOSE csr");
2506 Datatype bool
2508 The current implementation of PostgreSQL returns 't' for true and 'f'
2509 for false. From the Perl point of view, this is a rather unfortunate
2510 choice. DBD::Pg therefore translates the result for the "BOOL" data
2511 type in a Perlish manner: 'f' becomes the number 0 and 't' becomes the
2512 number 1. This way the application does not have to check the database-
2513 specific returned values for the data-type "BOOL" because Perl treats 0
2514 as false and 1 as true. You may set the pg_bool_tf attribute to a true
2515 value to change the values back to 't' and 'f' if you wish.
2516 Boolean values can be passed to PostgreSQL as TRUE, 't', 'true', 'y',
2518 'yes' or '1' for true and FALSE, 'f', 'false', 'n', 'no' or '0' for
2519 false.
2520 Schema support
2522 The PostgreSQL schema concept may differ from those of other databases.
2523 In a nutshell, a schema is a named collection of objects within a
2524 single database. Please refer to the PostgreSQL documentation for more
2525 details:
2526 <http://www.postgresql.org/docs/current/static/ddl-schemas.html>
2528 DBD::Pg does not provide explicit support for PostgreSQL schemas.
2530 However, schema functionality may be used without any restrictions by
2531 explicitly addressing schema objects, e.g.
2532 my $res = $dbh->selectall_arrayref("SELECT * FROM my_schema.my_table");
2534 or by manipulating the schema search path with "SET search_path", e.g.
2536 $dbh->do("SET search_path TO my_schema, public");
2538
2541 To report a bug, or view the current list of bugs, please visit
2542 http://rt.cpan.org/Public/Dist/Display.html?Name=DBD-Pg
2543
2545 DBI by Tim Bunce <http://www.tim.bunce.name>
2546 The original DBD-Pg was by Edmund Mergl (E.Mergl@bawue.de) and Jeffrey
2548 W. Baker (jwbaker@acm.org). Major developers include David Wheeler
2549 <david@justatheory.com>, Jason Stewart <jason@openinformatics.com>,
2550 Bruce Momjian <pgman@candle.pha.pa.us>, and Greg Sabino Mullane
2551 <greg@turnstep.com>, with help from many others: see the Changes file
2552 for a complete list.
2553 Parts of this package were originally copied from DBI and DBD-Oracle.
2555 Mailing List
2557 The current maintainers may be reached through the 'dbd-pg' mailing
2559 list: <dbd-pg@perl.org>
2560
2562 The DBD::Pg module is free software. You may distribute under the terms
2563 of either the GNU General Public License or the Artistic License, as
2564 specified in the Perl README file.
2565perl v5.10.1 2009-08-07 Pg(3)