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 $dbh->do('INSERT INTO mytable(a) VALUES (1)');
18 $sth = $dbh->prepare('INSERT INTO mytable(a) VALUES (?)');
20 $sth->execute();
21
23 This documents version 3.15.0 of the DBD::Pg module
24
26 DBD::Pg is a Perl module that works with the DBI module to provide
27 access to PostgreSQL databases.
28
30 This documentation describes driver specific behavior and restrictions.
31 It is not supposed to be used as the only reference for the user. In
32 any case consult the DBI documentation first!
33 Latest DBI documentation.
35
37 DBI Class Methods
38 connect
39 This method creates a database handle by connecting to a database, and
41 is the DBI equivalent of the "new" method. To connect to a Postgres
42 database with a minimum of parameters, use the following syntax:
43 $dbh = DBI->connect("dbi:Pg:dbname=$dbname", '', '', {AutoCommit => 0});
45 This connects to the database named in the $dbname variable on the
47 default port (usually 5432) without any user authentication.
48 The following connect statement shows almost all possible parameters:
50 $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host;port=$port;options=$options",
52 $username,
53 $password,
54 {AutoCommit => 0, RaiseError => 1, PrintError => 0}
55 );
56 Parameters containing unusual characters such as spaces can be wrapped
58 in single quotes around the value e.g. "dbi:Pg:dbname='spacey
59 name';host=$host"
60 If a parameter is not given, the connect() method will first look for
62 specific environment variables, and then fall back to hard-coded
63 defaults:
64 parameter environment variable hard coded default
66 ------------------------------------------------------
67 host PGHOST local domain socket
68 hostaddr PGHOSTADDR local domain socket
69 port PGPORT 5432
70 dbname* PGDATABASE current userid
71 username PGUSER current userid
72 password PGPASSWORD (none)
73 options PGOPTIONS (none)
74 service PGSERVICE (none)
75 sslmode PGSSLMODE (none)
76 * May also use the aliases "db" or "database"
78 If the username and password values passed via "connect()" are
80 undefined (as opposed to merely being empty strings), DBI will use the
81 environment variables DBI_USER and DBI_PASS if they exist.
82 You can also connect by using a service connection file, which is named
84 pg_service.conf. The location of this file can be controlled by setting
85 the PGSYSCONFDIR environment variable. To use one of the named services
86 within the file, set the name by using either the service parameter or
87 the environment variable PGSERVICE. Note that when connecting this way,
88 only the minimum parameters should be used. For example, to connect to
89 a service named "zephyr", you could use:
90 $dbh = DBI->connect("dbi:Pg:service=zephyr", '', '');
92 You could also set $ENV{PGSERVICE} to "zephyr" and connect like this:
94 $dbh = DBI->connect("dbi:Pg:", '', '');
96 The format of the pg_service.conf file is simply a bracketed service
98 name, followed by one parameter per line in the format name=value. For
99 example:
100 [zephyr]
102 dbname=winds
103 user=wisp
104 password=W$2Hc00YSgP
105 port=6543
106 There are four valid arguments to the sslmode parameter, which controls
108 whether to use SSL to connect to the database:
109 • disable: SSL connections are never used
111 • allow: try non-SSL, then SSL
113 • prefer: try SSL, then non-SSL
115 • require: connect only with SSL
117 You can also connect using sockets in a specific directory. This may be
119 needed if the server you are connecting to has a different default
120 socket directory from the one used to compile DBD::Pg. Use the
121 complete path to the socket directory as the name of the host, like
122 this:
123 $dbh = DBI->connect('dbi:Pg:dbname=foo;host=/var/tmp/socket',
125 $username,
126 $password,
127 {AutoCommit => 0, RaiseError => 1});
128 The attribute hash can also contain a key named "dbd_verbose", which
130 simply calls "$dbh->trace('DBD')" after the handle is created. This
131 attribute is not recommended, as it is clearer to simply explicitly
132 call "trace" explicitly in your script.
133 connect_cached
135 $dbh = DBI->connect_cached("dbi:Pg:dbname=$dbname", $username, $password, \%options);
137 Implemented by DBI, no driver-specific impact.
139 data_sources
141 @data_sources = DBI->data_sources('Pg');
143 @data_sources = $dbh->data_sources();
144 Returns a list of available databases. Unless the environment variable
146 "DBI_DSN" is set, a connection will be attempted to the database
147 "template1". The normal connection environment variables also apply,
148 such as "PGHOST", "PGPORT", "DBI_USER", "DBI_PASS", and "PGSERVICE".
149 You can also pass in options to add to the connection string For
151 example, to specify an alternate port and host:
152 @data_sources = DBI->data_sources('Pg', 'port=5824;host=example.com');
154 or:
156 @data_sources = $dbh->data_sources('port=5824;host=example.com');
158 Methods Common To All Handles
160 For all of the methods below, $h can be either a database handle ($dbh)
161 or a statement handle ($sth). Note that $dbh and $sth can be replaced
162 with any variable name you choose: these are just the names most often
163 used. Another common variable used in this documentation is $rv, which
164 stands for "return value".
165 err
167 $rv = $h->err;
169 Returns the error code from the last method called. For the connect
171 method it returns "PQstatus", which is a number used by libpq (the
172 Postgres connection library). A value of 0 indicates no error
173 (CONNECTION_OK), while any other number indicates a failed connection.
174 The only other number commonly seen is 1 (CONNECTION_BAD). See the
175 libpq documentation for the complete list of return codes.
176 In all other non-connect methods "$h->err" returns the "PQresultStatus"
178 of the current handle. This is a number used by libpq and is one of:
179 0 Empty query string
181 1 A command that returns no data successfully completed.
182 2 A command that returns data successfully completed.
183 3 A COPY OUT command is still in progress.
184 4 A COPY IN command is still in progress.
185 5 A bad response was received from the backend.
186 6 A nonfatal error occurred (a notice or warning message)
187 7 A fatal error was returned: the last query failed.
188 errstr
190 $str = $h->errstr;
192 Returns the last error that was reported by Postgres. This message is
194 affected by the pg_errorlevel setting.
195 state
197 $str = $h->state;
199 Returns a five-character "SQLSTATE" code. Success is indicated by a
201 00000 code, which gets mapped to an empty string by DBI. A code of
202 "S8006" indicates a connection failure, usually because the connection
203 to the Postgres server has been lost.
204 While this method can be called as either "$sth->state" or
206 "$dbh->state", it is usually clearer to always use "$dbh->state".
207 The list of codes used by PostgreSQL can be found at:
209 <http://www.postgresql.org/docs/current/static/errcodes-appendix.html>
210 Note that these codes are part of the SQL standard and only a small
212 number of them will be used by PostgreSQL.
213 Common codes:
215 00000 Successful completion
217 25P01 No active SQL transaction
218 25P02 In failed SQL transaction
219 S8006 Connection failure
220 trace
222 $h->trace($trace_settings);
224 $h->trace($trace_settings, $trace_filename);
225 $trace_settings = $h->trace;
226 Changes the trace settings on a database or statement handle. The
228 optional second argument specifies a file to write the trace
229 information to. If no filename is given, the information is written to
230 STDERR. Note that tracing can be set globally as well by setting
231 "DBI->trace", or by using the environment variable DBI_TRACE.
232 The value is either a numeric level or a named flag. For the flags that
234 DBD::Pg uses, see parse_trace_flag.
235 trace_msg
237 $h->trace_msg($message_text);
239 $h->trace_msg($message_text, $min_level);
240 Writes a message to the current trace output (as set by the "trace"
242 method). If a second argument is given, the message is only written if
243 the current tracing level is equal to or greater than the $min_level.
244 parse_trace_flag and parse_trace_flags
246 $h->trace($h->parse_trace_flags('SQL|pglibpq'));
248 $h->trace($h->parse_trace_flags('1|pgstart'));
249 ## Simpler:
251 $h->trace('SQL|pglibpq');
252 $h->trace('1|pgstart');
253 my $value = DBD::Pg->parse_trace_flag('pglibpq');
255 DBI->trace($value);
256 The parse_trace_flags method is used to convert one or more named flags
258 to a number which can passed to the "trace" method. DBD::Pg currently
259 supports the DBI-specific flag, "SQL", as well as the ones listed
260 below.
261 Flags can be combined by using the parse_trace_flags method, which
263 simply calls "parse_trace_flag" on each item and combines them.
264 Sometimes you may wish to turn the tracing on before you connect to the
266 database. The second example above shows a way of doing this: the call
267 to "DBD::Pg->parse_trace_flags" provides a number than can be fed to
268 "DBI->trace" before you create a database handle.
269 DBD::Pg supports the following trace flags:
271 SQL Outputs all SQL statements. Note that the output provided will not
273 necessarily be in a form suitable to passing directly to Postgres,
274 as server-side prepared statements are used extensively by DBD::Pg.
275 For maximum portability of output (but with a potential performance
276 hit), use with "$dbh->{pg_server_prepare} = 0".
277 DBD Turns on all non-DBI flags, in other words, only the ones that are
279 specific to DBD::Pg (all those below which start with the letters
280 'pg').
281 pglibpq
283 Outputs the name of each libpq function (without arguments)
284 immediately before running it. This is a good way to trace the flow
285 of your program at a low level. This information is also output if
286 the trace level is set to 4 or greater.
287 pgstart
289 Outputs the name of each internal DBD::Pg function, and other
290 information such as the function arguments or important global
291 variables, as each function starts. This information is also output
292 if the trace level is set to 4 or greater.
293 pgend
295 Outputs a simple message at the very end of each internal DBD::Pg
296 function. This is also output if the trace level is set to 4 or
297 greater.
298 pgprefix
300 Forces each line of trace output to begin with the string "dbdpg:
301 ". This helps to differentiate it from the normal DBI trace output.
302 pglogin
304 Outputs a message showing the connection string right before a new
305 database connection is attempted, a message when the connection was
306 successful, and a message right after the database has been
307 disconnected. Also output if trace level is 5 or greater.
308 See the DBI section on TRACING for more information.
310 func
312 DBD::Pg uses the "func" method to support a variety of functions. Note
314 that the name of the function comes last, after the arguments.
315 table_attributes
317 $attrs = $dbh->func($table, 'table_attributes');
318 Use of the tables_attributes function is no longer recommended.
320 Instead, you can use the more portable "column_info" and
321 "primary_key" methods to access the same information.
322 The table_attributes method returns, for the given table argument,
324 a reference to an array of hashes, each of which contains the
325 following keys:
326 NAME attribute name
328 TYPE attribute type
329 SIZE attribute size (-1 for variable size)
330 NULLABLE flag nullable
331 DEFAULT default value
332 CONSTRAINT constraint
333 PRIMARY_KEY flag is_primary_key
334 REMARKS attribute description
335 pg_lo_creat
337 $lobjId = $dbh->pg_lo_creat($mode);
338 Creates a new large object and returns the object-id. $mode is a
340 bitmask describing read and write access to the new object. This
341 setting is ignored since Postgres version 8.1. For backwards
342 compatibility, however, you should set a valid mode anyway (see
343 "pg_lo_open" for a list of valid modes).
344 Upon failure it returns "undef". This function cannot be used if
346 AutoCommit is enabled.
347 The old way of calling large objects functions is deprecated:
349 $dbh->func(.., 'lo_);
350 pg_lo_open
352 $lobj_fd = $dbh->pg_lo_open($lobjId, $mode);
353 Opens an existing large object and returns an object-descriptor for
355 use in subsequent "pg_lo_*" calls. $mode is a bitmask describing
356 read and write access to the opened object. It may be one of:
357 $dbh->{pg_INV_READ}
359 $dbh->{pg_INV_WRITE}
360 $dbh->{pg_INV_READ} | $dbh->{pg_INV_WRITE}
361 "pg_INV_WRITE" and "pg_INV_WRITE | pg_INV_READ" modes are
363 identical; in both modes, the large object can be read from or
364 written to. Reading from the object will provide the object as
365 written in other committed transactions, along with any writes
366 performed by the current transaction. Objects opened with
367 "pg_INV_READ" cannot be written to. Reading from this object will
368 provide the stored data at the time of the transaction snapshot
369 which was active when "pg_lo_write" was called.
370 Returns "undef" upon failure. Note that 0 is a perfectly correct
372 (and common) object descriptor! This function cannot be used if
373 AutoCommit is enabled.
374 pg_lo_write
376 $nbytes = $dbh->pg_lo_write($lobj_fd, $buffer, $len);
377 Writes $len bytes of c<$buffer> into the large object $lobj_fd.
379 Returns the number of bytes written and "undef" upon failure. This
380 function cannot be used if AutoCommit is enabled.
381 pg_lo_read
383 $nbytes = $dbh->pg_lo_read($lobj_fd, $buffer, $len);
384 Reads $len bytes into c<$buffer> from large object $lobj_fd.
386 Returns the number of bytes read and "undef" upon failure. This
387 function cannot be used if AutoCommit is enabled.
388 pg_lo_lseek
390 $loc = $dbh->pg_lo_lseek($lobj_fd, $offset, $whence);
391 Changes the current read or write location on the large object
393 $obj_id. Currently $whence can only be 0 (which is L_SET). Returns
394 the current location and "undef" upon failure. This function cannot
395 be used if AutoCommit is enabled.
396 pg_lo_lseek64
398 $loc = $dbh->pg_lo_lseek64($lobj_fd, $offset, $whence);
399 Same as pg_lo_lseek, but can handle much larger offsets and
401 returned values. Requires Postgres 9.3 or greater.
402 pg_lo_tell
404 $loc = $dbh->pg_lo_tell($lobj_fd);
405 Returns the current read or write location on the large object
407 $lobj_fd and "undef" upon failure. This function cannot be used if
408 AutoCommit is enabled.
409 pg_lo_tell64
411 $loc = $dbh->pg_lo_tell64($lobj_fd);
412 Same as pg_lo_tell, but can return much larger values. Requires
414 Postgres 9.3 or greater.
415 pg_lo_truncate
417 $loc = $dbh->pg_lo_truncate($lobj_fd, $len);
418 Truncates the given large object to the new size. Returns "undef"
420 on failure, and 0 on success. This function cannot be used if
421 AutoCommit is enabled.
422 pg_lo_truncate64
424 $loc = $dbh->pg_lo_truncate64($lobj_fd, $len);
425 Same as pg_lo_truncate, but can handle much larger lengths.
427 Requires Postgres 9.3 or greater.
428 pg_lo_close
430 $lobj_fd = $dbh->pg_lo_close($lobj_fd);
431 Closes an existing large object. Returns true upon success and
433 false upon failure. This function cannot be used if AutoCommit is
434 enabled.
435 pg_lo_unlink
437 $ret = $dbh->pg_lo_unlink($lobjId);
438 Deletes an existing large object. Returns true upon success and
440 false upon failure. This function cannot be used if AutoCommit is
441 enabled.
442 pg_lo_import
444 $lobjId = $dbh->pg_lo_import($filename);
445 Imports a Unix file as a large object and returns the object id of
447 the new object or "undef" upon failure.
448 pg_lo_import_with_oid
450 $lobjId = $dbh->pg_lo_import($filename, $OID);
451 Same as pg_lo_import, but attempts to use the supplied OID as the
453 large object number. If this number is 0, it falls back to the
454 behavior of pg_lo_import (which assigns the next available OID).
455 This is only available when DBD::Pg is compiled against a Postgres
457 server version 8.4 or later.
458 pg_lo_export
460 $ret = $dbh->pg_lo_export($lobjId, $filename);
461 Exports a large object into a Unix file. Returns false upon
463 failure, true otherwise.
464 getfd
466 $fd = $dbh->func('getfd');
467 Deprecated, use $dbh->{pg_socket} instead.
469 private_attribute_info
471 $hashref = $dbh->private_attribute_info();
473 $hashref = $sth->private_attribute_info();
474 Returns a hash of all private attributes used by DBD::Pg, for either a
476 database or a statement handle. Currently, all the hash values are
477 undef.
478
480 InactiveDestroy (boolean)
481 If set to true, then the "disconnect" method will not be automatically
483 called when the database handle goes out of scope. This is required if
484 you are forking, and even then you must tread carefully and ensure that
485 either the parent or the child (but not both!) handles all database
486 calls from that point forwards, so that messages from the Postgres
487 backend are only handled by one of the processes. If you don't set
488 things up properly, you will see messages such as "server closed the
489 connection unexpectedly", and "message type 0x32 arrived from server
490 while idle". The best solution is to either have the child process
491 reconnect to the database with a fresh database handle, or to rewrite
492 your application not to use forking. See the section on "Asynchronous
493 Queries" for a way to have your script continue to work while the
494 database is processing a request.
495 AutoInactiveDestroy (boolean)
497 The InactiveDestroy attribute, described above, needs to be explicitly
499 set in the child process after a fork. If the code that performs the
500 fork is in a third party module such as Sys::Syslog, this can present a
501 problem. Use AutoInactiveDestroy to get around this problem.
502 RaiseError (boolean, inherited)
504 Forces errors to always raise an exception. Although it defaults to
506 off, it is recommended that this be turned on, as the alternative is to
507 check the return value of every method (prepare, execute, fetch, etc.)
508 manually, which is easy to forget to do.
509 PrintError (boolean, inherited)
511 Forces database errors to also generate warnings, which can then be
513 filtered with methods such as locally redefining $SIG{__WARN__} or
514 using modules such as "CGI::Carp". This attribute is on by default.
515 ShowErrorStatement (boolean, inherited)
517 Appends information about the current statement to error messages. If
519 placeholder information is available, adds that as well. Defaults to
520 false.
521 Note that this will not work when using "do" without any arguments.
523 Warn (boolean, inherited)
525 Enables warnings. This is on by default, and should only be turned off
527 in a local block for a short a time only when absolutely needed.
528 Executed (boolean, read-only)
530 Indicates if a handle has been executed. For database handles, this
532 value is true after the "do" method has been called, or when one of the
533 child statement handles has issued an "execute". Issuing a "commit" or
534 "rollback" always resets the attribute to false for database handles.
535 For statement handles, any call to "execute" or its variants will flip
536 the value to true for the lifetime of the statement handle.
537 TraceLevel (integer, inherited)
539 Sets the trace level, similar to the "trace" method. See the sections
541 on "trace" and parse_trace_flag for more details.
542 Active (boolean, read-only)
544 Indicates if a handle is active or not. For database handles, this
546 indicates if the database has been disconnected or not. For statement
547 handles, it indicates if all the data has been fetched yet or not. Use
548 of this attribute is not encouraged.
549 Kids (integer, read-only)
551 Returns the number of child processes created for each handle type. For
553 a driver handle, indicates the number of database handles created. For
554 a database handle, indicates the number of statement handles created.
555 For statement handles, it always returns zero, because statement
556 handles do not create kids.
557 ActiveKids (integer, read-only)
559 Same as "Kids", but only returns those that are active.
561 CachedKids (hash ref)
563 Returns a hashref of handles. If called on a database handle, returns
565 all statement handles created by use of the "prepare_cached" method. If
566 called on a driver handle, returns all database handles created by the
567 "connect_cached" method.
568 ChildHandles (array ref)
570 Implemented by DBI, no driver-specific impact.
572 PrintWarn (boolean, inherited)
574 Implemented by DBI, no driver-specific impact.
576 HandleError (boolean, inherited)
578 Implemented by DBI, no driver-specific impact.
580 HandleSetErr (code ref, inherited)
582 Implemented by DBI, no driver-specific impact.
584 ErrCount (unsigned integer)
586 Implemented by DBI, no driver-specific impact.
588 FetchHashKeyName (string, inherited)
590 Implemented by DBI, no driver-specific impact.
592 ChopBlanks (boolean, inherited)
594 Supported by DBD::Pg as proposed by DBI. This method is similar to the
596 SQL function "RTRIM".
597 Taint (boolean, inherited)
599 Implemented by DBI, no driver-specific impact.
601 TaintIn (boolean, inherited)
603 Implemented by DBI, no driver-specific impact.
605 TaintOut (boolean, inherited)
607 Implemented by DBI, no driver-specific impact.
609 Profile (inherited)
611 Implemented by DBI, no driver-specific impact.
613 Type (scalar)
615 Returns "dr" for a driver handle, "db" for a database handle, and "st"
617 for a statement handle. Should be rarely needed.
618 LongReadLen
620 Not used by DBD::Pg
622 LongTruncOk
624 Not used by DBD::Pg
626 CompatMode
628 Not used by DBD::Pg
630
632 Database Handle Methods
633 selectall_arrayref
634 $ary_ref = $dbh->selectall_arrayref($sql);
636 $ary_ref = $dbh->selectall_arrayref($sql, \%attr);
637 $ary_ref = $dbh->selectall_arrayref($sql, \%attr, @bind_values);
638 Returns a reference to an array containing the rows returned by
640 preparing and executing the SQL string. See the DBI documentation for
641 full details.
642 selectall_hashref
644 $hash_ref = $dbh->selectall_hashref($sql, $key_field);
646 Returns a reference to a hash containing the rows returned by preparing
648 and executing the SQL string. See the DBI documentation for full
649 details.
650 selectcol_arrayref
652 $ary_ref = $dbh->selectcol_arrayref($sql, \%attr, @bind_values);
654 Returns a reference to an array containing the first column from each
656 rows returned by preparing and executing the SQL string. It is possible
657 to specify exactly which columns to return. See the DBI documentation
658 for full details.
659 prepare
661 $sth = $dbh->prepare($statement, \%attr);
663 WARNING: DBD::Pg now (as of version 1.40) uses true prepared statements
665 by sending them to the backend to be prepared by the Postgres server.
666 Statements that were legal before may no longer work. See below for
667 details.
668 The prepare method prepares a statement for later execution. PostgreSQL
670 supports prepared statements, which enables DBD::Pg to only send the
671 query once, and simply send the arguments for every subsequent call to
672 "execute". DBD::Pg can use these server-side prepared statements, or
673 it can just send the entire query to the server each time. The best way
674 is automatically chosen for each query. This will be sufficient for
675 most users: keep reading for a more detailed explanation and some
676 optional flags.
677 Queries that do not begin with the word "SELECT", "INSERT", "UPDATE",
679 or "DELETE" are never sent as server-side prepared statements.
680 Deciding whether or not to use prepared statements depends on many
682 factors, but you can force them to be used or not used by using the
683 pg_server_prepare attribute when calling "prepare". Setting this to
684 false means to never use prepared statements. Setting pg_server_prepare
685 to true means that prepared statements should be used whenever
686 possible. This is the default.
687 The pg_server_prepare attribute can also be set at connection time like
689 so:
690 $dbh = DBI->connect($DBNAME, $DBUSER, $DBPASS,
692 { AutoCommit => 0,
693 RaiseError => 1,
694 pg_server_prepare => 0,
695 });
696 or you may set it after your database handle is created:
698 $dbh->{pg_server_prepare} = 1;
700 To enable it for just one particular statement:
702 $sth = $dbh->prepare("SELECT id FROM mytable WHERE val = ?",
704 { pg_server_prepare => 1 });
705 You can even toggle between the two as you go:
707 $sth->{pg_server_prepare} = 1;
709 $sth->execute(22);
710 $sth->{pg_server_prepare} = 0;
711 $sth->execute(44);
712 $sth->{pg_server_prepare} = 1;
713 $sth->execute(66);
714 In the above example, the first execute will use the previously
716 prepared statement. The second execute will not, but will build the
717 query into a single string and send it to the server. The third one
718 will act like the first and only send the arguments. Even if you
719 toggle back and forth, a statement is only prepared once.
720 Using prepared statements is in theory quite a bit faster: not only
722 does the PostgreSQL backend only have to prepare the query only once,
723 but DBD::Pg no longer has to worry about quoting each value before
724 sending it to the server.
725 However, there are some drawbacks. The server cannot always choose the
727 ideal parse plan because it will not know the arguments before hand.
728 But for most situations in which you will be executing similar data
729 many times, the default plan will probably work out well. Programs such
730 as PgBouncer which cache connections at a low level should not use
731 prepared statements via DBD::Pg, or must take extra care in the
732 application to account for the fact that prepared statements are not
733 shared across database connections. Further discussion on this subject
734 is beyond the scope of this documentation: please consult the pgsql-
735 performance mailing list,
736 <http://archives.postgresql.org/pgsql-performance/>
737 Only certain commands will be sent to a server-side prepare: currently
739 these include "SELECT", "INSERT", "UPDATE", and "DELETE". DBD::Pg uses
740 a simple naming scheme for the prepared statements themselves:
741 dbdpg_XY_Z, where Y is the current PID, X is either 'p' or 'n'
742 (depending on if the PID is a positive or negative number), and Z is a
743 number that starts at 1 and increases each time a new statement is
744 prepared. This number is tracked at the database handle level, so
745 multiple statement handles will not collide.
746 You cannot send more than one command at a time in the same prepare
748 command (by separating them with semi-colons) when using server-side
749 prepares.
750 The actual "PREPARE" is usually not performed until the first execute
752 is called, due to the fact that information on the data types (provided
753 by "bind_param") may be provided after the prepare but before the
754 execute.
755 A server-side prepare may happen before the first "execute", but only
757 if the server can handle the server-side prepare, and the statement
758 contains no placeholders. It will also be prepared if the
759 pg_prepare_now attribute is passed in and set to a true value.
760 Similarly, the pg_prepare_now attribute can be set to 0 to ensure that
761 the statement is not prepared immediately, although the cases in which
762 you would want this are very rare. Finally, you can set the default
763 behavior of all prepare statements by setting the pg_prepare_now
764 attribute on the database handle:
765 $dbh->{pg_prepare_now} = 1;
767 The following two examples will be prepared right away:
769 $sth->prepare("SELECT 123"); ## no placeholders
771 $sth->prepare("SELECT 123, ?", {pg_prepare_now => 1});
773 The following two examples will NOT be prepared right away:
775 $sth->prepare("SELECT 123, ?"); ## has a placeholder
777 $sth->prepare("SELECT 123", {pg_prepare_now => 0});
779 There are times when you may want to prepare a statement yourself. To
781 do this, simply send the "PREPARE" statement directly to the server
782 (e.g. with the "do" method). Create a statement handle and set the
783 prepared name via the pg_prepare_name attribute. The statement handle
784 can be created with a dummy statement, as it will not be executed.
785 However, it should have the same number of placeholders as your
786 prepared statement. Example:
787 $dbh->do('PREPARE mystat AS SELECT COUNT(*) FROM pg_class WHERE reltuples < ?');
789 $sth = $dbh->prepare('SELECT ?');
790 $sth->bind_param(1, 1, SQL_INTEGER);
791 $sth->{pg_prepare_name} = 'mystat';
792 $sth->execute(123);
793 The above will run the equivalent of this query on the backend:
795 EXECUTE mystat(123);
797 which is the equivalent of:
799 SELECT COUNT(*) FROM pg_class WHERE reltuples < 123;
801 You can force DBD::Pg to send your query directly to the server by
803 adding the pg_direct attribute to your prepare call. This is not
804 recommended, but is added just in case you need it.
805 Placeholders
807 There are three types of placeholders that can be used in DBD::Pg. The
809 first is the "question mark" type, in which each placeholder is
810 represented by a single question mark character. This is the method
811 recommended by the DBI specs and is the most portable. Each question
812 mark is internally replaced by a "dollar sign number" in the order in
813 which they appear in the query (important when using "bind_param").
814 The second type of placeholder is "dollar sign numbers". This is the
816 method that Postgres uses internally and is overall probably the best
817 method to use if you do not need compatibility with other database
818 systems. DBD::Pg, like PostgreSQL, allows the same number to be used
819 more than once in the query. Numbers must start with "1" and increment
820 by one value (but can appear in any order within the query). If the
821 same number appears more than once in a query, it is treated as a
822 single parameter and all instances are replaced at once. Examples:
823 Not legal:
825 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $2'; # Does not start with 1
827 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $3'; # Missing 2
829 Legal:
831 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $1';
833 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $2';
835 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $2 AND $1'; # legal but confusing
837 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $2 AND reltuples > $1';
839 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $1 AND reltuples > $1';
841 In the final statement above, DBI thinks there is only one placeholder,
843 so this statement will replace both placeholders:
844 $sth->bind_param(1, 2045);
846 While a simple execute with no bind_param calls requires only a single
848 argument as well:
849 $sth->execute(2045);
851 The final placeholder type is "named parameters" in the format ":foo".
853 While this syntax is supported by DBD::Pg, its use is discouraged in
854 favor of dollar-sign numbers.
855 The different types of placeholders cannot be mixed within a statement,
857 but you may use different ones for each statement handle you have. This
858 is confusing at best, so stick to one style within your program.
859 If your queries use operators that contain question marks (e.g. some of
861 the native Postgres geometric operators and JSON operators) or array
862 slices (e.g. "data[100:300]"), there are methods to instruct DBD::Pg to
863 not treat some symbols as placeholders. First, you may simply add a
864 backslash before the start of a placeholder, and DBD::Pg will strip the
865 backslash and not treat the character as a placeholder.
866 You can also tell DBD::Pg to ignore any non-dollar sign placeholders by
868 setting the pg_placeholder_dollaronly attribute at either the database
869 handle or the statement handle level. Examples:
870 $dbh->{pg_placeholder_dollaronly} = 1;
872 $sth = $dbh->prepare(q{SELECT * FROM mytable WHERE lseg1 ?# lseg2 AND name = $1});
873 $sth->execute('segname');
874 Alternatively, you can set it at prepare time:
876 $sth = $dbh->prepare(q{SELECT * FROM mytable WHERE lseg1 ?-| lseg2 AND name = $1},
878 {pg_placeholder_dollaronly => 1});
879 $sth->execute('segname');
880 If your queries use array slices but you still want to use question
882 marks as placeholders, you can tell DBD::Pg to ignore just colon
883 placeholders by setting the "pg_placeholder_nocolons" attribute in the
884 same way. Examples:
885 $dbh->{pg_placeholder_nocolons} = 1;
887 $sth = $dbh->prepare(q{SELECT array[1:2] FROM mytable WHERE id = ?});
888 $sth->execute(1);
889 Again, you may set it param time as well:
891 $sth = $dbh->prepare(q{SELECT array[1:2] FROM mytable WHERE id = ?},
893 {pg_placeholder_nocolons => 1});
894 $sth->execute(1);
895 It should be noted that placeholders only work when used outside of a
897 literal string context; i.e., the following examples will not
898 define/use any placeholders due to appearing inside strings within the
899 SQL:
900 $sth = $dbh->prepare(q{SELECT id FROM mytable WHERE text LIKE '%?'});
902 $dbh->do(q{DO LANGUAGE plpgsql $$ BEGIN RAISE NOTICE ?; END $$}, undef, $message);
903 See the DBI placeholder documentation for more details.
905 prepare_cached
907 $sth = $dbh->prepare_cached($statement, \%attr);
909 Implemented by DBI, no driver-specific impact. This method is most
911 useful when using a server that supports server-side prepares, and you
912 have asked the prepare to happen immediately via the pg_prepare_now
913 attribute.
914 do
916 $rv = $dbh->do($statement);
918 $rv = $dbh->do($statement, \%attr);
919 $rv = $dbh->do($statement, \%attr, @bind_values);
920 Prepare and execute a single statement. Returns the number of rows
922 affected if the query was successful, returns undef if an error
923 occurred, and returns -1 if the number of rows is unknown or not
924 available. Note that this method will return 0E0 instead of 0 for 'no
925 rows were affected', in order to always return a true value if no error
926 occurred.
927 If neither "\%attr" nor @bind_values is given, the query will be sent
929 directly to the server without the overhead of internally creating a
930 statement handle and running prepare and execute, for a measurable
931 speed increase.
932 Note that an empty statement (a string with no length) will not be
934 passed to the server; if you want a simple test, use "SELECT 123" or
935 the "ping" method.
936 last_insert_id
938 $rv = $dbh->last_insert_id(undef, $schema, $table, undef);
940 $rv = $dbh->last_insert_id(undef, $schema, $table, undef, {sequence => $seqname});
941 Attempts to return the id of the last value to be inserted into a
943 table. You can either provide a sequence name (preferred) or provide a
944 table name with optional schema, and DBD::Pg will attempt to find the
945 sequence itself. The current value of the sequence is returned by a
946 call to the "CURRVAL()" PostgreSQL function. This will fail if the
947 sequence has not yet been used in the current database connection.
948 If you do not know the name of the sequence, you can provide a table
950 name and DBD::Pg will attempt to return the correct value. To do this,
951 there must be at least one column in the table with a "NOT NULL"
952 constraint, that has a unique constraint, and which uses a sequence as
953 a default value (either manually, or via the "SERIAL" pseudotype or
954 "GENERATED ... AS IDENTITY"). If more than one column meets these
955 conditions, the primary key will be used. This involves some looking up
956 of things in the system table, so DBD::Pg will cache the sequence name
957 for subsequent calls. If you need to disable this caching for some
958 reason, (such as the sequence name changing), you can control it by
959 adding "pg_cache => 0" to the final (hashref) argument for
960 last_insert_id.
961 Please keep in mind that this method is far from foolproof, so make
963 your script use it properly. Specifically, make sure that it is called
964 immediately after the insert, and that the insert does not add a value
965 to the column that is using the sequence as a default value. However,
966 because we are using sequences, you can be sure that the value you got
967 back has not been used by any other process.
968 Some examples:
970 $dbh->do('CREATE SEQUENCE lii_seq START 1');
972 $dbh->do(q{CREATE TABLE lii (
973 foobar INTEGER NOT NULL UNIQUE DEFAULT nextval('lii_seq'),
974 baz VARCHAR)});
975 $SQL = 'INSERT INTO lii(baz) VALUES (?)';
976 $sth = $dbh->prepare($SQL);
977 for (qw(uno dos tres cuatro)) {
978 $sth->execute($_);
979 my $newid = $dbh->last_insert_id(undef,undef,undef,undef,{sequence=>'lii_seq'});
980 print "Last insert id was $newid\n";
981 }
982 If you did not want to worry about the sequence name:
984 $dbh->do('CREATE TABLE lii2 (
986 foobar SERIAL UNIQUE,
987 baz VARCHAR)');
988 $SQL = 'INSERT INTO lii2(baz) VALUES (?)';
989 $sth = $dbh->prepare($SQL);
990 for (qw(uno dos tres cuatro)) {
991 $sth->execute($_);
992 my $newid = $dbh->last_insert_id(undef,undef,"lii2",undef);
993 print "Last insert id was $newid\n";
994 }
995 commit
997 $rv = $dbh->commit;
999 Issues a COMMIT to the server, indicating that the current transaction
1001 is finished and that all changes made will be visible to other
1002 processes. If AutoCommit is enabled, then a warning is given and no
1003 COMMIT is issued. Returns true on success, false on error. See also
1004 the section on "Transactions".
1005 rollback
1007 $rv = $dbh->rollback;
1009 Issues a ROLLBACK to the server, which discards any changes made in the
1011 current transaction. If AutoCommit is enabled, then a warning is given
1012 and no ROLLBACK is issued. Returns true on success, and false on error.
1013 See also the the section on "Transactions".
1014 begin_work
1016 This method turns on transactions until the next call to "commit" or
1018 "rollback", if AutoCommit is currently enabled. If it is not enabled,
1019 calling begin_work will issue an error. Note that the transaction will
1020 not actually begin until the first statement after begin_work is
1021 called. Example:
1022 $dbh->{AutoCommit} = 1;
1024 $dbh->do('INSERT INTO foo VALUES (123)'); ## Changes committed immediately
1025 $dbh->begin_work();
1026 ## Not in a transaction yet, but AutoCommit is set to 0
1027 $dbh->do("INSERT INTO foo VALUES (345)");
1029 ## DBD::PG actually issues two statements here:
1030 ## BEGIN;
1031 ## INSERT INTO foo VALUES (345)
1032 ## We are now in a transaction
1033 $dbh->commit();
1035 ## AutoCommit is now set to 1 again
1036 disconnect
1038 $rv = $dbh->disconnect;
1040 Disconnects from the Postgres database. Any uncommitted changes will be
1042 rolled back upon disconnection. It's good policy to always explicitly
1043 call commit or rollback at some point before disconnecting, rather than
1044 relying on the default rollback behavior.
1045 This method may give warnings about "disconnect invalidates X active
1047 statement handle(s)". This means that you called "$sth->execute()" but
1048 did not finish fetching all the rows from them. To avoid seeing this
1049 warning, either fetch all the rows or call "$sth->finish()" for each
1050 executed statement handle.
1051 If the script exits before disconnect is called (or, more precisely, if
1053 the database handle is no longer referenced by anything), then the
1054 database handle's DESTROY method will call the rollback() and
1055 disconnect() methods automatically. It is best to explicitly disconnect
1056 rather than rely on this behavior.
1057 quote
1059 $rv = $dbh->quote($value, $data_type);
1061 This module implements its own "quote" method. For simple string types,
1063 both backslashes and single quotes are doubled. You may also quote
1064 arrayrefs and receive a string suitable for passing into Postgres array
1065 columns.
1066 If the value contains backslashes, and the server is version 8.1 or
1068 higher, then the escaped string syntax will be used (which places a
1069 capital E before the first single quote). This syntax is always used
1070 when quoting bytea values on servers 8.1 and higher.
1071 The "data_type" argument is optional and should be one of the type
1073 constants exported by DBD::Pg (such as PG_BYTEA). In addition to
1074 string, bytea, char, bool, and other standard types, the following
1075 geometric types are supported: point, line, lseg, box, path, polygon,
1076 and circle (PG_POINT, PG_LINE, PG_LSEG, PG_BOX, PG_PATH, PG_POLYGON,
1077 and PG_CIRCLE respectively). To quote a Postgres-specific data type,
1078 you must use a 'hashref' argument like so:
1079 my $quotedval = $dbh->quote($value, { pg_type => PG_VARCHAR });
1081 NOTE: The undocumented (and invalid) support for the "SQL_BINARY" data
1083 type is officially deprecated. Use "PG_BYTEA" with "bind_param()"
1084 instead:
1085 $rv = $sth->bind_param($param_num, $bind_value,
1087 { pg_type => PG_BYTEA });
1088 quote_identifier
1090 $string = $dbh->quote_identifier( $name );
1092 $string = $dbh->quote_identifier( undef, $schema, $table);
1093 Returns a quoted version of the supplied string, which is commonly a
1095 schema, table, or column name. The three argument form will return the
1096 schema and the table together, separated by a dot. Examples:
1097 print $dbh->quote_identifier('grapefruit'); ## Prints: "grapefruit"
1099 print $dbh->quote_identifier('juicy fruit'); ## Prints: "juicy fruit"
1101 print $dbh->quote_identifier(undef, 'public', 'pg_proc');
1103 ## Prints: "public"."pg_proc"
1104 pg_notifies
1106 $ret = $dbh->pg_notifies;
1108 Looks for any asynchronous notifications received and returns either
1110 "undef" or a reference to a three-element array consisting of an event
1111 name, the PID of the backend that sent the NOTIFY command, and the
1112 optional payload string. Note that this does not check if the
1113 connection to the database is still valid first - for that, use the
1114 c<ping> method. You may need to commit if not in autocommit mode - new
1115 notices will not be picked up while in the middle of a transaction. An
1116 example:
1117 $dbh->do("LISTEN abc");
1119 $dbh->do("LISTEN def");
1120 ## Hang around until we get the message we want
1122 LISTENLOOP: {
1123 while (my $notify = $dbh->pg_notifies) {
1124 my ($name, $pid, $payload) = @$notify;
1125 print qq{I received notice "$name" from PID $pid, payload was "$payload"\n};
1126 ## Do something based on the notice received
1127 }
1128 $dbh->ping() or die qq{Ping failed!};
1129 $dbh->commit();
1130 sleep(5);
1131 redo;
1132 }
1133 Payloads will always be an empty string unless you are connecting to a
1135 Postgres server version 9.0 or higher.
1136 ping
1138 $rv = $dbh->ping;
1140 The "ping" method determines if there is a working connection to an
1142 active database server. It does this by sending a small query to the
1143 server, currently 'DBD::Pg ping test v3.15.0'. It returns 0 (false) if
1144 the connection is not valid, otherwise it returns a positive number
1145 (true). The value returned indicates the current state:
1146 Value Meaning
1148 --------------------------------------------------
1149 1 Database is idle (not in a transaction)
1150 2 Database is active, there is a command in progress (usually seen after a COPY command)
1151 3 Database is idle within a transaction
1152 4 Database is idle, within a failed transaction
1153 Additional information on why a handle is not valid can be obtained by
1155 using the "pg_ping" method.
1156 pg_ping
1158 $rv = $dbh->pg_ping;
1160 This is a DBD::Pg-specific extension to the "ping" method. This will
1162 check the validity of a database handle in exactly the same way as
1163 "ping", but instead of returning a 0 for an invalid connection, it will
1164 return a negative number. So in addition to returning the positive
1165 numbers documented for "ping", it may also return the following:
1166 Value Meaning
1168 --------------------------------------------------
1169 -1 There is no connection to the database at all (e.g. after disconnect)
1170 -2 An unknown transaction status was returned (e.g. after forking)
1171 -3 The test query failed (PQexec returned null)
1172 -4 PQstatus returned a CONNECTION_BAD
1173 pg_error_field
1175 $value = $dbh->pg_error_field('context');
1177 The pg_error_field returns specific information about the last error
1179 that occurred. It needs to be called as soon as possible after an
1180 error occurs, as any other query sent to Postgres (via $dbh or $sth)
1181 will reset all the error information. Note that this is called at the
1182 database handle ($dbh) level, but can return errors that occurred via
1183 both database handles (e.g. $dbh->do) and statement handles (e.g.
1184 $sth->execute). It takes a single argument, indicating which field to
1185 return. The value returned will be undef if the previous command was
1186 not an error, or if the field is not applicable to the current error.
1187 The canonical list of field types can be found at:
1189 <https://www.postgresql.org/docs/current/libpq-exec.html#LIBPQ-PQRESULTERRORFIELD>
1191 The literal names on that page can be used (e.g.
1193 PG_DIAG_STATEMENT_HINT), but lowercase is accepted too, as well as the
1194 following abbreviated forms:
1195 severity
1197 severity_nonlocal (only for Postgres 10 and above)
1198 state
1199 primary
1200 detail (does not work well for Postgres < 9.2)
1201 hint
1202 statement_position
1203 internal_position
1204 internal_query
1205 context
1206 schema (only for Postgres 9.3 and above)
1207 table (only for Postgres 9.3 and above)
1208 column (only for Postgres 9.3 and above)
1209 type (only for Postgres 9.3 and above)
1210 constraint (only for Postgres 9.3 and above)
1211 source_file
1212 source_line
1213 source_function
1214 get_info
1216 $value = $dbh->get_info($info_type);
1218 Supports a very large set (> 250) of the information types, including
1220 the minimum recommended by DBI.
1221 Items of note:
1223 SQL_KEYWORDS
1225 This returns all items reserved by Postgres but NOT reserved by
1226 SQL:2011 standard. See:
1227 http://www.postgresql.org/docs/current/static/sql-keywords-appendix.htm
1229 table_info
1231 $sth = $dbh->table_info(undef, $schema, $table, $type);
1233 Returns all tables and views visible to the current user. The schema
1235 and table arguments will do a "LIKE" search if a percent sign ("%") or
1236 an underscore ("_") is detected in the argument. The $type argument
1237 accepts any comma-separated combination of "TABLE", "VIEW", "SYSTEM
1238 TABLE", "SYSTEM VIEW", "MATERIALIZED VIEW", "SYSTEM MATERIALIZED VIEW",
1239 "FOREIGN TABLE", "SYSTEM FOREIGN TABLE", or "LOCAL TEMPORARY". (Using
1240 all is the default action.)
1241 Note that a statement handle is returned, and not a direct list of
1243 tables. See the examples below for ways to handle this.
1244 The following fields are returned:
1246 TABLE_CAT: The name of the database that the table or view is in
1248 (always the current database).
1249 TABLE_SCHEM: The name of the schema that the table or view is in.
1251 TABLE_NAME: The name of the table or view.
1253 TABLE_TYPE: The type of object returned. Will be one of "TABLE",
1255 "VIEW", "MATERIALIZED VIEW", "SYSTEM VIEW", "SYSTEM MATERIALIZED VIEW",
1256 "SYSTEM TABLE", "FOREIGN TABLE", "SYSTEM FOREIGN TABLE", or "LOCAL
1257 TEMPORARY".
1258 The TABLE_SCHEM and TABLE_NAME will be quoted via "quote_ident()".
1260 Four additional fields specific to DBD::Pg are returned:
1262 pg_schema: the unquoted name of the schema
1264 pg_table: the unquoted name of the table
1266 pg_tablespace_name: the name of the tablespace the table is in
1268 pg_tablespace_location: the location of the tablespace the table is in
1270 Tables that have not been assigned to a particular tablespace (or
1272 views) will return NULL ("undef") for both of the above field.
1273 Rows are returned alphabetically, with all tables first, and then all
1275 views.
1276 Examples of use:
1278 ## Display all tables and views in the public schema:
1280 $sth = $dbh->table_info('', 'public', undef, undef);
1281 for my $rel (@{$sth->fetchall_arrayref({})}) {
1282 print "$rel->{TABLE_TYPE} name is $rel->{TABLE_NAME}\n";
1283 }
1284 # Display the schema of all tables named 'foo':
1287 $sth = $dbh->table_info('', undef, 'foo', 'TABLE');
1288 for my $rel (@{$sth->fetchall_arrayref({})}) {
1289 print "Table name is $rel->{TABLE_SCHEM}.$rel->{TABLE_NAME}\n";
1290 }
1291 column_info
1293 $sth = $dbh->column_info( undef, $schema, $table, $column );
1295 Supported by this driver as proposed by DBI with the follow exceptions.
1297 These fields are currently always returned with NULL ("undef") values:
1298 BUFFER_LENGTH
1300 DECIMAL_DIGITS
1301 NUM_PREC_RADIX
1302 SQL_DATA_TYPE
1303 SQL_DATETIME_SUB
1304 CHAR_OCTET_LENGTH
1305 Also, six additional non-standard fields are returned:
1307 pg_type: data type with additional info i.e. "character varying(20)"
1309 pg_constraint: holds column constraint definition
1311 pg_schema: the unquoted name of the schema
1313 pg_table: the unquoted name of the table
1315 pg_column: the unquoted name of the column
1317 pg_enum_values: an array reference of allowed values for an enum column
1319 Note that the TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME fields all
1321 return output wrapped in quote_ident(). If you need the unquoted
1322 version, use the pg_ fields above.
1323 primary_key_info
1325 $sth = $dbh->primary_key_info( undef, $schema, $table, \%attr );
1327 Supported by this driver as proposed by DBI. There are no search
1329 patterns allowed, but leaving the $schema argument blank will cause the
1330 first table found in the schema search path to be used. An additional
1331 field, "DATA_TYPE", is returned and shows the data type for each of the
1332 arguments in the "COLUMN_NAME" field.
1333 This method will also return tablespace information for servers that
1335 support tablespaces. See the "table_info" entry for more information.
1336 The five additional custom fields returned are:
1338 pg_tablespace_name: name of the tablespace, if any
1340 pg_tablespace_location: location of the tablespace
1342 pg_schema: the unquoted name of the schema
1344 pg_table: the unquoted name of the table
1346 pg_column: the unquoted name of the column
1348 In addition to the standard format of returning one row for each column
1350 found for the primary key, you can pass the "pg_onerow" attribute to
1351 force a single row to be used. If the primary key has multiple columns,
1352 the "KEY_SEQ", "COLUMN_NAME", and "DATA_TYPE" fields will return a
1353 comma-delimited string. If the "pg_onerow" attribute is set to "2", the
1354 fields will be returned as an arrayref, which can be useful when
1355 multiple columns are involved:
1356 $sth = $dbh->primary_key_info('', '', 'dbd_pg_test', {pg_onerow => 2});
1358 if (defined $sth) {
1359 my $pk = $sth->fetchall_arrayref()->[0];
1360 print "Table $pk->[2] has a primary key on these columns:\n";
1361 for (my $x=0; defined $pk->[3][$x]; $x++) {
1362 print "Column: $pk->[3][$x] (data type: $pk->[6][$x])\n";
1363 }
1364 }
1365 primary_key
1367 @key_column_names = $dbh->primary_key(undef, $schema, $table);
1369 Simple interface to the "primary_key_info" method. Returns a list of
1371 the column names that comprise the primary key of the specified table.
1372 The list is in primary key column sequence order. If there is no
1373 primary key then an empty list is returned.
1374 foreign_key_info
1376 $sth = $dbh->foreign_key_info( $pk_catalog, $pk_schema, $pk_table,
1378 $fk_catalog, $fk_schema, $fk_table );
1379 Supported by this driver as proposed by DBI, using the SQL/CLI variant.
1381 There are no search patterns allowed, but leaving the $schema argument
1382 blank will cause the first table found in the schema search path to be
1383 used. Two additional fields, "UK_DATA_TYPE" and "FK_DATA_TYPE", are
1384 returned to show the data type for the unique and foreign key columns.
1385 Foreign keys that have no named constraint (where the referenced column
1386 only has an unique index) will return "undef" for the "UK_NAME" field.
1387 statistics_info
1389 $sth = $dbh->statistics_info( undef, $schema, $table, $unique_only, $quick );
1391 Returns a statement handle that can be fetched from to give statistics
1393 information on a specific table and its indexes. The $table argument is
1394 mandatory. The $schema argument is optional but recommended. The
1395 $unique_only argument, if true, causes only information about unique
1396 indexes to be returned. The $quick argument is not used by DBD::Pg. For
1397 information on the format of the standard rows returned, please see the
1398 DBI documentation.
1399 DBI section on statistics_info
1401 In addition, the following Postgres specific columns are returned:
1403 pg_expression
1405 Postgres allows indexes on functions and scalar expressions based
1406 on one or more columns. This field will always be populated if an
1407 index, but the lack of an entry in the COLUMN_NAME should indicate
1408 that this is an index expression.
1409 pg_is_key_column
1411 Postgres (since version 11) allows including non-key columns in
1412 indexes so they can be retrieved by index-only scans. This field
1413 will be false for such columns, and true for normal index columns.
1414 pg_null_ordering
1416 In addition to "ASC" and "DESC", Postgres supports specifying
1417 "NULLS FIRST" or "NULLS LAST" for index columns. For columns of
1418 indexes that support ordering, this field will be "first" or
1419 "last", otherwise it will be "undef";
1420 tables
1422 @names = $dbh->tables( undef, $schema, $table, $type, \%attr );
1424 Supported by this driver as proposed by DBI. This method returns all
1426 tables and/or views (including foreign tables and materialized views)
1427 which are visible to the current user: see "table_info" for more
1428 information about the arguments. The name of the schema appears before
1429 the table or view name. This can be turned off by adding in the
1430 "pg_noprefix" attribute:
1431 my @tables = $dbh->tables( '', '', 'dbd_pg_test', '', {pg_noprefix => 1} );
1433 type_info_all
1435 $type_info_all = $dbh->type_info_all;
1437 Supported by this driver as proposed by DBI. Information is only
1439 provided for SQL datatypes and for frequently used datatypes. The
1440 mapping between the PostgreSQL typename and the SQL92 datatype (if
1441 possible) has been done according to the following table:
1442 +---------------+------------------------------------+
1444 | typname | SQL92 |
1445 |---------------+------------------------------------|
1446 | bool | BOOL |
1447 | text | / |
1448 | bpchar | CHAR(n) |
1449 | varchar | VARCHAR(n) |
1450 | int2 | SMALLINT |
1451 | int4 | INT |
1452 | int8 | BIGINT |
1453 | money | / |
1454 | float4 | FLOAT(p) p<7=float4, p<16=float8 |
1455 | float8 | REAL |
1456 | abstime | / |
1457 | reltime | / |
1458 | tinterval | / |
1459 | date | / |
1460 | time | / |
1461 | datetime | / |
1462 | timespan | TINTERVAL |
1463 | timestamp | TIMESTAMP |
1464 +---------------+------------------------------------+
1465 type_info
1467 @type_info = $dbh->type_info($data_type);
1469 Returns a list of hash references holding information about one or more
1471 variants of $data_type. See the DBI documentation for more details.
1472 pg_server_trace
1474 $dbh->pg_server_trace($filehandle);
1476 Writes debugging information from the PostgreSQL backend to a file.
1478 This is not related to the DBI "trace" method and you should not use
1479 this method unless you know what you are doing. If you do enable this,
1480 be aware that the file will grow very large, very quick. To stop
1481 logging to the file, use the "pg_server_untrace" method. The first
1482 argument must be a file handle, not a filename. Example:
1483 my $pid = $dbh->{pg_pid};
1485 my $file = "pgbackend.$pid.debug.log";
1486 open(my $fh, ">$file") or die qq{Could not open "$file": $!\n};
1487 $dbh->pg_server_trace($fh);
1488 ## Run code you want to trace here
1489 $dbh->pg_server_untrace;
1490 close($fh);
1491 pg_server_untrace
1493 $dbh->pg_server_untrace;
1495 Stop server logging to a previously opened file.
1497 selectrow_array
1499 @row_ary = $dbh->selectrow_array($sql);
1501 @row_ary = $dbh->selectrow_array($sql, \%attr);
1502 @row_ary = $dbh->selectrow_array($sql, \%attr, @bind_values);
1503 Returns an array of row information after preparing and executing the
1505 provided SQL string. The rows are returned by calling "fetchrow_array".
1506 The string can also be a statement handle generated by a previous
1507 prepare. Note that only the first row of data is returned. If called in
1508 a scalar context, only the first column of the first row is returned.
1509 Because this is not portable, it is not recommended that you use this
1510 method in that way.
1511 selectrow_arrayref
1513 $ary_ref = $dbh->selectrow_arrayref($statement);
1515 $ary_ref = $dbh->selectrow_arrayref($statement, \%attr);
1516 $ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @bind_values);
1517 Exactly the same as "selectrow_array", except that it returns a
1519 reference to an array, by internal use of the "fetchrow_arrayref"
1520 method.
1521 selectrow_hashref
1523 $hash_ref = $dbh->selectrow_hashref($sql);
1525 $hash_ref = $dbh->selectrow_hashref($sql, \%attr);
1526 $hash_ref = $dbh->selectrow_hashref($sql, \%attr, @bind_values);
1527 Exactly the same as "selectrow_array", except that it returns a
1529 reference to an hash, by internal use of the "fetchrow_hashref" method.
1530 clone
1532 $other_dbh = $dbh->clone();
1534 Creates a copy of the database handle by connecting with the same
1536 parameters as the original handle, then trying to merge the attributes.
1537 See the DBI documentation for complete usage.
1538 Database Handle Attributes
1540 AutoCommit (boolean)
1541 Supported by DBD::Pg as proposed by DBI. According to the
1543 classification of DBI, PostgreSQL is a database in which a transaction
1544 must be explicitly started. Without starting a transaction, every
1545 change to the database becomes immediately permanent. The default of
1546 AutoCommit is on, but this may change in the future, so it is highly
1547 recommended that you explicitly set it when calling "connect". For
1548 details see the notes about "Transactions" elsewhere in this document.
1549 ParamValues (hash ref, read-only)
1551 Ignored unless inside a "do" method call. There it is temporarily
1553 aliased to the "ParamValues" hash from the temporary statement handle
1554 inside an internal "prepare / execute / fetch" routine, invisible from
1555 outside, and is treated correspondingly (see "ParamValues" in
1556 "Statement Handle Attributes"). This allows for correct reporting of
1557 values bound to placeholders to the caller, should the query fail (see
1558 "ShowErrorStatement").
1559 pg_bool_tf (boolean)
1561 DBD::Pg specific attribute. If true, boolean values will be returned as
1563 the characters 't' and 'f' instead of '1' and '0'.
1564 ReadOnly (boolean)
1566 $dbh->{ReadOnly} = 1;
1568 Specifies if the current database connection should be in read-only
1570 mode or not. In this mode, changes that change the database are not
1571 allowed and will throw an error. Note: this method will not work if
1572 "AutoCommit" is true. The read-only effect is accomplished by sending a
1573 SET TRANSACTION READ ONLY after every begin. For more details, please
1574 see:
1575 http://www.postgresql.org/docs/current/interactive/sql-set-transaction.html
1577 Please not that this method is not foolproof: there are still ways to
1579 update the database. Consider this a safety net to catch applications
1580 that should not be issuing commands such as INSERT, UPDATE, or DELETE.
1581 This method requires DBI version 1.55 or better.
1583 pg_server_prepare (boolean)
1585 DBD::Pg specific attribute. Indicates if DBD::Pg should attempt to use
1587 server-side prepared statements. The default value, true, indicates
1588 that prepared statements should be used whenever possible. See the
1589 section on the "prepare" method for more information.
1590 pg_switch_prepared (integer)
1592 DBD::Pg specific attribute. Indicates when DBD::Pg will internally
1594 switch from using PQexecParams to PQexecPrepared. In other words, when
1595 it will start using server-side prepared statements (assuming all other
1596 requirements for them are met). The default value, 2, means that a
1597 prepared statement will be prepared and used the second and subsequent
1598 time execute is called. To always use PQexecPrepared instead of
1599 PQexecParams, set pg_switch_prepared to 1 (this was the default
1600 behavior in earlier versions). Setting pg_switch_prepared to 0 will
1601 force DBD::Pg to always use PQexecParams.
1602 pg_placeholder_dollaronly (boolean)
1604 DBD::Pg specific attribute. Defaults to false. When true, question
1606 marks inside of statements are not treated as placeholders. Useful for
1607 statements that contain unquoted question marks, such as geometric
1608 operators. Note that you may also simply escape question marks with a
1609 backslash to prevent them from being treated as placeholders.
1610 pg_placeholder_nocolons (boolean)
1612 DBD::Pg specific attribute. Defaults to false. When true, colons inside
1614 of statements are not treated as placeholders. Useful for statements
1615 that contain an array slice. You may also place a backslash directly
1616 before the colon to prevent it from being treated as a placeholder.
1617 pg_enable_utf8 (integer)
1619 DBD::Pg specific attribute. The behavior of DBD::Pg with regards to
1621 this flag has changed as of version 3.0.0. The default value for this
1622 attribute, -1, tells DBD::Pg to UTF8-decode all strings coming back
1623 from the database if the client_encoding is set to "UTF8". Use of this
1624 default is highly encouraged. If your code was previously using
1625 pg_enable_utf8, you can probably remove mention of it entirely.
1626 If this attribute is set to 0, then DBD::Pg will never UTF8-decode
1628 returned data, regardless of the current client_encoding.
1629 If this attribute is set to 1, then DBD::Pg will always UTF8-decode
1631 returned data, regardless of the current client_encoding (with the
1632 exception of bytea data).
1633 Note that the value of client_encoding is only checked on connection
1635 time. If you change the client_encoding to/from 'UTF8' after
1636 connecting, you can set pg_enable_utf8 to -1 to force DBD::Pg to read
1637 in the new client_encoding and act accordingly.
1638 pg_errorlevel (integer)
1640 DBD::Pg specific attribute. Sets the amount of information returned by
1642 the server's error messages. Valid entries are 0, 1, and 2. Any other
1643 number will be forced to the default value of 1.
1644 A value of 0 ("TERSE") will show severity, primary text, and position
1646 only and will usually fit on a single line. A value of 1 ("DEFAULT")
1647 will also show any detail, hint, or context fields. A value of 2
1648 ("VERBOSE") will show all available information.
1649 pg_lib_version (integer, read-only)
1651 DBD::Pg specific attribute. Indicates which version of PostgreSQL that
1653 DBD::Pg was compiled against. In other words, which libraries were
1654 used. Returns a number with major, minor, and revision together;
1655 version 8.1.4 would be returned as 80104.
1656 pg_server_version (integer, read-only)
1658 DBD::Pg specific attribute. Indicates which version of PostgreSQL that
1660 the current database handle is connected to. Returns a number with
1661 major, minor, and revision together; version 8.0.1 would be 80001.
1662 Name (string, read-only)
1664 Returns the name of the current database. This is the same as the DSN,
1666 without the "dbi:Pg:" part. Before version 2.0.0, this only returned
1667 the bare database name (e.g. 'foo'). From version 2.0.0 onwards, it
1668 returns the more correct output (e.g. 'dbname=foo')
1669 Username (string, read-only)
1671 Returns the name of the user connected to the database.
1673 pg_db (string, read-only)
1675 DBD::Pg specific attribute. Returns the name of the current database.
1677 pg_user (string, read-only)
1679 DBD::Pg specific attribute. Returns the name of the user that connected
1681 to the server.
1682 pg_host (string, read-only)
1684 DBD::Pg specific attribute. Returns the host of the current server
1686 connection. Locally connected hosts will return an empty string.
1687 pg_port (integer, read-only)
1689 DBD::Pg specific attribute. Returns the port of the connection to the
1691 server.
1692 pg_socket (integer, read-only)
1694 DBD::Pg specific attribute. Returns the file description number of the
1696 connection socket to the server.
1697 pg_pass (string, read-only)
1699 DBD::Pg specific attribute. Returns the password used to connect to the
1701 server.
1702 pg_options (string, read-only)
1704 DBD::Pg specific attribute. Returns the command-line options passed to
1706 the server. May be an empty string.
1707 pg_default_port (integer, read-only)
1709 DBD::Pg specific attribute. Returns the default port used if none is
1711 specifically given.
1712 pg_pid (integer, read-only)
1714 DBD::Pg specific attribute. Returns the process id (PID) of the backend
1716 server process handling the connection.
1717 pg_prepare_now (boolean)
1719 DBD::Pg specific attribute. Default is off. If true, then the "prepare"
1721 method will immediately prepare commands, rather than waiting until the
1722 first execute.
1723 pg_expand_array (boolean)
1725 DBD::Pg specific attribute. Defaults to true. If false, arrays returned
1727 from the server will not be changed into a Perl arrayref, but remain as
1728 a string.
1729 pg_async_status (integer, read-only)
1731 DBD::Pg specific attribute. Returns the current status of an
1733 asynchronous command. 0 indicates no asynchronous command is in
1734 progress, 1 indicates that an asynchronous command has started and -1
1735 indicated that an asynchronous command has been cancelled.
1736 pg_standard_conforming_strings (boolean, read-only)
1738 DBD::Pg specific attribute. Returns true if the server is currently
1740 using standard conforming strings. Only available if the target server
1741 is version 8.2 or better.
1742 pg_INV_READ (integer, read-only)
1744 Constant to be used for the mode in "pg_lo_creat" and "pg_lo_open".
1746 pg_INV_WRITE (integer, read-only)
1748 Constant to be used for the mode in "pg_lo_creat" and "pg_lo_open".
1750 Driver (handle, read-only)
1752 Holds the handle of the parent driver. The only recommended use for
1754 this is to find the name of the driver using:
1755 $dbh->{Driver}->{Name}
1757 pg_protocol (integer, read-only)
1759 DBD::Pg specific attribute. Returns the version of the PostgreSQL
1761 server. If DBD::Pg is unable to figure out the version, it will return
1762 a "0". Otherwise, a "3" is returned.
1763 RowCacheSize
1765 Not used by DBD::Pg
1767
1769 Statement Handle Methods
1770 bind_param
1771 $rv = $sth->bind_param($param_num, $bind_value);
1773 $rv = $sth->bind_param($param_num, $bind_value, $bind_type);
1774 $rv = $sth->bind_param($param_num, $bind_value, \%attr);
1775 Allows the user to bind a value and/or a data type to a placeholder.
1777 This is especially important when using server-side prepares. See the
1778 "prepare" method for more information.
1779 The value of $param_num is a number if using the '?' or '$1' style
1781 placeholders. If using ":foo" style placeholders, the complete name
1782 (e.g. ":foo") must be given. For numeric values, you can either use a
1783 number or use a literal '$1'. See the examples below.
1784 The $bind_value argument is fairly self-explanatory. A value of "undef"
1786 will bind a "NULL" to the placeholder. Using "undef" is useful when you
1787 want to change just the type and will be overwriting the value later.
1788 (Any value is actually usable, but "undef" is easy and efficient).
1789 The "\%attr" hash is used to indicate the data type of the placeholder.
1791 The default value is "varchar". If you need something else, you must
1792 use one of the values provided by DBI or by DBD::Pg. To use a SQL
1793 value, modify your "use DBI" statement at the top of your script as
1794 follows:
1795 use DBI qw(:sql_types);
1797 This will import some constants into your script. You can plug those
1799 directly into the "bind_param" call. Some common ones that you will
1800 encounter are:
1801 SQL_INTEGER
1803 To use PostgreSQL data types, import the list of values like this:
1805 use DBD::Pg qw(:pg_types);
1807 You can then set the data types by setting the value of the "pg_type"
1809 key in the hash passed to "bind_param". The current list of Postgres
1810 data types exported is:
1811 PG_ACLITEM PG_ACLITEMARRAY PG_ANY PG_ANYARRAY PG_ANYCOMPATIBLE PG_ANYCOMPATIBLEARRAY
1813 PG_ANYCOMPATIBLEMULTIRANGE PG_ANYCOMPATIBLENONARRAY PG_ANYCOMPATIBLERANGE PG_ANYELEMENT PG_ANYENUM PG_ANYMULTIRANGE
1814 PG_ANYNONARRAY PG_ANYRANGE PG_BIT PG_BITARRAY PG_BOOL PG_BOOLARRAY
1815 PG_BOX PG_BOXARRAY PG_BPCHAR PG_BPCHARARRAY PG_BYTEA PG_BYTEAARRAY
1816 PG_CHAR PG_CHARARRAY PG_CID PG_CIDARRAY PG_CIDR PG_CIDRARRAY
1817 PG_CIRCLE PG_CIRCLEARRAY PG_CSTRING PG_CSTRINGARRAY PG_DATE PG_DATEARRAY
1818 PG_DATEMULTIRANGE PG_DATEMULTIRANGEARRAY PG_DATERANGE PG_DATERANGEARRAY PG_EVENT_TRIGGER PG_FDW_HANDLER
1819 PG_FLOAT4 PG_FLOAT4ARRAY PG_FLOAT8 PG_FLOAT8ARRAY PG_GTSVECTOR PG_GTSVECTORARRAY
1820 PG_INDEX_AM_HANDLER PG_INET PG_INETARRAY PG_INT2 PG_INT2ARRAY PG_INT2VECTOR
1821 PG_INT2VECTORARRAY PG_INT4 PG_INT4ARRAY PG_INT4MULTIRANGE PG_INT4MULTIRANGEARRAY PG_INT4RANGE
1822 PG_INT4RANGEARRAY PG_INT8 PG_INT8ARRAY PG_INT8MULTIRANGE PG_INT8MULTIRANGEARRAY PG_INT8RANGE
1823 PG_INT8RANGEARRAY PG_INTERNAL PG_INTERVAL PG_INTERVALARRAY PG_JSON PG_JSONARRAY
1824 PG_JSONB PG_JSONBARRAY PG_JSONPATH PG_JSONPATHARRAY PG_LANGUAGE_HANDLER PG_LINE
1825 PG_LINEARRAY PG_LSEG PG_LSEGARRAY PG_MACADDR PG_MACADDR8 PG_MACADDR8ARRAY
1826 PG_MACADDRARRAY PG_MONEY PG_MONEYARRAY PG_NAME PG_NAMEARRAY PG_NUMERIC
1827 PG_NUMERICARRAY PG_NUMMULTIRANGE PG_NUMMULTIRANGEARRAY PG_NUMRANGE PG_NUMRANGEARRAY PG_OID
1828 PG_OIDARRAY PG_OIDVECTOR PG_OIDVECTORARRAY PG_PATH PG_PATHARRAY PG_PG_ATTRIBUTE
1829 PG_PG_ATTRIBUTEARRAY PG_PG_BRIN_BLOOM_SUMMARY PG_PG_BRIN_MINMAX_MULTI_SUMMARY PG_PG_CLASS PG_PG_CLASSARRAY PG_PG_DDL_COMMAND
1830 PG_PG_DEPENDENCIES PG_PG_LSN PG_PG_LSNARRAY PG_PG_MCV_LIST PG_PG_NDISTINCT PG_PG_NODE_TREE
1831 PG_PG_PROC PG_PG_PROCARRAY PG_PG_SNAPSHOT PG_PG_SNAPSHOTARRAY PG_PG_TYPE PG_PG_TYPEARRAY
1832 PG_POINT PG_POINTARRAY PG_POLYGON PG_POLYGONARRAY PG_RECORD PG_RECORDARRAY
1833 PG_REFCURSOR PG_REFCURSORARRAY PG_REGCLASS PG_REGCLASSARRAY PG_REGCOLLATION PG_REGCOLLATIONARRAY
1834 PG_REGCONFIG PG_REGCONFIGARRAY PG_REGDICTIONARY PG_REGDICTIONARYARRAY PG_REGNAMESPACE PG_REGNAMESPACEARRAY
1835 PG_REGOPER PG_REGOPERARRAY PG_REGOPERATOR PG_REGOPERATORARRAY PG_REGPROC PG_REGPROCARRAY
1836 PG_REGPROCEDURE PG_REGPROCEDUREARRAY PG_REGROLE PG_REGROLEARRAY PG_REGTYPE PG_REGTYPEARRAY
1837 PG_TABLE_AM_HANDLER PG_TEXT PG_TEXTARRAY PG_TID PG_TIDARRAY PG_TIME
1838 PG_TIMEARRAY PG_TIMESTAMP PG_TIMESTAMPARRAY PG_TIMESTAMPTZ PG_TIMESTAMPTZARRAY PG_TIMETZ
1839 PG_TIMETZARRAY PG_TRIGGER PG_TSMULTIRANGE PG_TSMULTIRANGEARRAY PG_TSM_HANDLER PG_TSQUERY
1840 PG_TSQUERYARRAY PG_TSRANGE PG_TSRANGEARRAY PG_TSTZMULTIRANGE PG_TSTZMULTIRANGEARRAY PG_TSTZRANGE
1841 PG_TSTZRANGEARRAY PG_TSVECTOR PG_TSVECTORARRAY PG_TXID_SNAPSHOT PG_TXID_SNAPSHOTARRAY PG_UNKNOWN
1842 PG_UUID PG_UUIDARRAY PG_VARBIT PG_VARBITARRAY PG_VARCHAR PG_VARCHARARRAY
1843 PG_VOID PG_XID PG_XID8 PG_XID8ARRAY PG_XIDARRAY PG_XML
1844 PG_XMLARRAY
1845 Data types are "sticky," in that once a data type is set to a certain
1847 placeholder, it will remain for that placeholder, unless it is
1848 explicitly set to something else afterwards. If the statement has
1849 already been prepared, and you switch the data type to something else,
1850 DBD::Pg will re-prepare the statement for you before doing the next
1851 execute.
1852 Examples:
1854 use DBI qw(:sql_types);
1856 use DBD::Pg qw(:pg_types);
1857 $SQL = "SELECT id FROM ptable WHERE size > ? AND title = ?";
1859 $sth = $dbh->prepare($SQL);
1860 ## Both arguments below are bound to placeholders as "varchar"
1862 $sth->execute(123, "Merk");
1863 ## Reset the datatype for the first placeholder to an integer
1865 $sth->bind_param(1, undef, SQL_INTEGER);
1866 ## The "undef" bound above is not used, since we supply params to execute
1868 $sth->execute(123, "Merk");
1869 ## Set the first placeholder's value and data type
1871 $sth->bind_param(1, 234, { pg_type => PG_TIMESTAMP });
1872 ## Set the second placeholder's value and data type.
1874 ## We don't send a third argument, so the default "varchar" is used
1875 $sth->bind_param('$2', "Zool");
1876 ## We realize that the wrong data type was set above, so we change it:
1878 $sth->bind_param('$1', 234, { pg_type => SQL_INTEGER });
1879 ## We also got the wrong value, so we change that as well.
1881 ## Because the data type is sticky, we don't need to change it
1882 $sth->bind_param(1, 567);
1883 ## This executes the statement with 567 (integer) and "Zool" (varchar)
1885 $sth->execute();
1886 bind_param_inout
1888 $rv = $sth->bind_param_inout($param_num, \$scalar, 0);
1890 Experimental support for this feature is provided. The first argument
1892 to bind_param_inout should be a placeholder number. The second argument
1893 should be a reference to a scalar variable in your script. The third
1894 argument is not used and should simply be set to 0. Note that what this
1895 really does is assign a returned column to the variable, in the order
1896 in which the column appears. For example:
1897 my $foo = 123;
1899 $sth = $dbh->prepare("SELECT 1+?::int");
1900 $sth->bind_param_inout(1, \$foo, 0);
1901 $foo = 222;
1902 $sth->execute(444);
1903 $sth->fetch;
1904 The above will cause $foo to have a new value of "223" after the final
1906 fetch. Note that the variables bound in this manner are very sticky,
1907 and will trump any values passed in to execute. This is because the
1908 binding is done as late as possible, at the execute() stage, allowing
1909 the value to be changed between the time it was bound and the time the
1910 query is executed. Thus, the above execute is the same as:
1911 $sth->execute();
1913 bind_param_array
1915 $rv = $sth->bind_param_array($param_num, $array_ref_or_value)
1917 $rv = $sth->bind_param_array($param_num, $array_ref_or_value, $bind_type)
1918 $rv = $sth->bind_param_array($param_num, $array_ref_or_value, \%attr)
1919 Binds an array of values to a placeholder, so that each is used in turn
1921 by a call to the "execute_array" method.
1922 execute
1924 $rv = $sth->execute(@bind_values);
1926 Executes a previously prepared statement. In addition to "UPDATE",
1928 "DELETE", "INSERT" statements, for which it returns always the number
1929 of affected rows, the "execute" method can also be used for "SELECT ...
1930 INTO table" statements.
1931 The "prepare/bind/execute" process has changed significantly for
1933 PostgreSQL servers 7.4 and later: please see the "prepare()" and
1934 "bind_param()" entries for much more information.
1935 Setting one of the bind_values to "undef" is the equivalent of setting
1937 the value to NULL in the database. Setting the bind_value to
1938 $DBDPG_DEFAULT is equivalent to sending the literal string 'DEFAULT' to
1939 the backend. Note that using this option will force server-side
1940 prepares off until such time as PostgreSQL supports using DEFAULT in
1941 prepared statements.
1942 DBD::Pg also supports passing in arrays to execute: simply pass in an
1944 arrayref, and DBD::Pg will flatten it into a string suitable for input
1945 on the backend.
1946 If you are using Postgres version 8.2 or greater, you can also use any
1948 of the fetch methods to retrieve the values of a "RETURNING" clause
1949 after you execute an "UPDATE", "DELETE", or "INSERT". For example:
1950 $dbh->do(q{CREATE TABLE abc (id SERIAL, country TEXT)});
1952 $SQL = q{INSERT INTO abc (country) VALUES (?) RETURNING id};
1953 $sth = $dbh->prepare($SQL);
1954 $sth->execute('France');
1955 $countryid = $sth->fetch()->[0];
1956 $sth->execute('New Zealand');
1957 $countryid = $sth->fetch()->[0];
1958 execute_array
1960 $tuples = $sth->execute_array() or die $sth->errstr;
1962 $tuples = $sth->execute_array(\%attr) or die $sth->errstr;
1963 $tuples = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
1964 ($tuples, $rows) = $sth->execute_array(\%attr) or die $sth->errstr;
1966 ($tuples, $rows) = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
1967 Execute a prepared statement once for each item in a passed-in hashref,
1969 or items that were previously bound via the "bind_param_array" method.
1970 See the DBI documentation for more details.
1971 execute_for_fetch
1973 $tuples = $sth->execute_for_fetch($fetch_tuple_sub);
1975 $tuples = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
1976 ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub);
1978 ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
1979 Used internally by the "execute_array" method, and rarely used
1981 directly. See the DBI documentation for more details.
1982 fetchrow_arrayref
1984 $ary_ref = $sth->fetchrow_arrayref;
1986 Fetches the next row of data from the statement handle, and returns a
1988 reference to an array holding the column values. Any columns that are
1989 NULL are returned as undef within the array.
1990 If there are no more rows or if an error occurs, then this method
1992 return undef. You should check "$sth->err" afterwards (or use the
1993 RaiseError attribute) to discover if the undef returned was due to an
1994 error.
1995 Note that the same array reference is returned for each fetch, so don't
1997 store the reference and then use it after a later fetch. Also, the
1998 elements of the array are also reused for each row, so take care if you
1999 want to take a reference to an element. See also "bind_columns".
2000 fetchrow_array
2002 @ary = $sth->fetchrow_array;
2004 Similar to the "fetchrow_arrayref" method, but returns a list of column
2006 information rather than a reference to a list. Do not use this in a
2007 scalar context.
2008 fetchrow_hashref
2010 $hash_ref = $sth->fetchrow_hashref;
2012 $hash_ref = $sth->fetchrow_hashref($name);
2013 Fetches the next row of data and returns a hashref containing the name
2015 of the columns as the keys and the data itself as the values. Any NULL
2016 value is returned as an undef value.
2017 If there are no more rows or if an error occurs, then this method
2019 return undef. You should check "$sth->err" afterwards (or use the
2020 RaiseError attribute) to discover if the undef returned was due to an
2021 error.
2022 The optional $name argument should be either "NAME", "NAME_lc" or
2024 "NAME_uc", and indicates what sort of transformation to make to the
2025 keys in the hash.
2026 fetchall_arrayref
2028 $tbl_ary_ref = $sth->fetchall_arrayref();
2030 $tbl_ary_ref = $sth->fetchall_arrayref( $slice );
2031 $tbl_ary_ref = $sth->fetchall_arrayref( $slice, $max_rows );
2032 Returns a reference to an array of arrays that contains all the
2034 remaining rows to be fetched from the statement handle. If there are no
2035 more rows, an empty arrayref will be returned. If an error occurs, the
2036 data read in so far will be returned. Because of this, you should
2037 always check "$sth->err" after calling this method, unless RaiseError
2038 has been enabled.
2039 If $slice is an array reference, fetchall_arrayref uses the
2041 "fetchrow_arrayref" method to fetch each row as an array ref. If the
2042 $slice array is not empty then it is used as a slice to select
2043 individual columns by perl array index number (starting at 0, unlike
2044 column and parameter numbers which start at 1).
2045 With no parameters, or if $slice is undefined, fetchall_arrayref acts
2047 as if passed an empty array ref.
2048 If $slice is a hash reference, fetchall_arrayref uses
2050 "fetchrow_hashref" to fetch each row as a hash reference.
2051 See the DBI documentation for a complete discussion.
2053 fetchall_hashref
2055 $hash_ref = $sth->fetchall_hashref( $key_field );
2057 Returns a hashref containing all rows to be fetched from the statement
2059 handle. See the DBI documentation for a full discussion.
2060 finish
2062 $rv = $sth->finish;
2064 Indicates to DBI that you are finished with the statement handle and
2066 are not going to use it again. Only needed when you have not fetched
2067 all the possible rows.
2068 rows
2070 $rv = $sth->rows;
2072 Returns the number of rows returned by the last query. In contrast to
2074 many other DBD modules, the number of rows is available immediately
2075 after calling "$sth->execute". Note that the "execute" method itself
2076 returns the number of rows itself, which means that this method is
2077 rarely needed.
2078 bind_col
2080 $rv = $sth->bind_col($column_number, \$var_to_bind);
2082 $rv = $sth->bind_col($column_number, \$var_to_bind, \%attr );
2083 $rv = $sth->bind_col($column_number, \$var_to_bind, $bind_type );
2084 Binds a Perl variable and/or some attributes to an output column of a
2086 SELECT statement. Column numbers count up from 1. You do not need to
2087 bind output columns in order to fetch data.
2088 See the DBI documentation for a discussion of the optional parameters
2090 "\%attr" and $bind_type
2091 bind_columns
2093 $rv = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
2095 Calls the "bind_col" method for each column in the SELECT statement,
2097 using the supplied list.
2098 dump_results
2100 $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
2102 Fetches all the rows from the statement handle, calls "DBI::neat_list"
2104 for each row, and prints the results to $fh (which defaults to STDOUT).
2105 Rows are separated by $lsep (which defaults to a newline). Columns are
2106 separated by $fsep (which defaults to a comma). The $maxlen controls
2107 how wide the output can be, and defaults to 35.
2108 This method is designed as a handy utility for prototyping and testing
2110 queries. Since it uses "neat_list" to format and edit the string for
2111 reading by humans, it is not recommended for data transfer
2112 applications.
2113 blob_read
2115 $blob = $sth->blob_read($id, $offset, $len);
2117 Supported by DBD::Pg. This method is implemented by DBI but not
2119 currently documented by DBI, so this method might change.
2120 This method seems to be heavily influenced by the current
2122 implementation of blobs in Oracle. Nevertheless we try to be as
2123 compatible as possible. Whereas Oracle suffers from the limitation that
2124 blobs are related to tables and every table can have only one blob
2125 (datatype LONG), PostgreSQL handles its blobs independent of any table
2126 by using so-called object identifiers. This explains why the
2127 "blob_read" method is blessed into the STATEMENT package and not part
2128 of the DATABASE package. Here the field parameter has been used to
2129 handle this object identifier. The offset and len parameters may be set
2130 to zero, in which case the whole blob is fetched at once.
2131 See also the PostgreSQL-specific functions concerning blobs, which are
2133 available via the "func" interface.
2134 For further information and examples about blobs, please read the
2136 chapter about Large Objects in the PostgreSQL Programmer's Guide at
2137 <http://www.postgresql.org/docs/current/static/largeobjects.html>.
2138 pg_canonical_ids
2140 $data = $sth->pg_canonical_ids;
2142 DBD::Pg specific method. It returns Oid of table and position in table
2144 for every column in result set.
2145 Returns array of arrays with Table Oid and Column Position for every
2147 column in result set or undef if current column is not a simple
2148 reference.
2149 pg_canonical_names
2151 $data = $sth->pg_canonical_names;
2153 DBD::Pg specific method. It returns array of original (or canonical)
2155 names (from where this data is actually came from) of columns in
2156 Schema.Table.Column format or undef if current column is not a simple
2157 reference.
2158 Note that this method is quite slow because it need additional
2160 information from server for every column that is simple reference.
2161 Consider to use "pg_canonical_ids" instead.
2162 last_insert_id
2164 $rv = $sth->last_insert_id(undef, $schema, $table, undef);
2166 $rv = $sth->last_insert_id(undef, $schema, $table, undef, {sequence => $seqname});
2167 This is simply an alternative way to return the same information as
2169 "$dbh->last_insert_id".
2170 Statement Handle Attributes
2172 NUM_OF_FIELDS (integer, read-only)
2173 Returns the number of columns returned by the current statement. A
2175 number will only be returned for SELECT statements, for SHOW statements
2176 (which always return 1), and for INSERT, UPDATE, and DELETE statements
2177 which contain a RETURNING clause. This method returns undef if called
2178 before "execute()".
2179 NUM_OF_PARAMS (integer, read-only)
2181 Returns the number of placeholders in the current statement.
2183 NAME (arrayref, read-only)
2185 Returns an arrayref of column names for the current statement. This
2187 method will only work for SELECT statements, for SHOW statements, and
2188 for INSERT, UPDATE, and DELETE statements which contain a RETURNING
2189 clause. This method returns undef if called before "execute()".
2190 NAME_lc (arrayref, read-only)
2192 The same as the "NAME" attribute, except that all column names are
2194 forced to lower case.
2195 NAME_uc (arrayref, read-only)
2197 The same as the "NAME" attribute, except that all column names are
2199 forced to upper case.
2200 NAME_hash (hashref, read-only)
2202 Similar to the "NAME" attribute, but returns a hashref of column names
2204 instead of an arrayref. The names of the columns are the keys of the
2205 hash, and the values represent the order in which the columns are
2206 returned, starting at 0. This method returns undef if called before
2207 "execute()".
2208 NAME_lc_hash (hashref, read-only)
2210 The same as the "NAME_hash" attribute, except that all column names are
2212 forced to lower case.
2213 NAME_uc_hash (hashref, read-only)
2215 The same as the "NAME_hash" attribute, except that all column names are
2217 forced to lower case.
2218 TYPE (arrayref, read-only)
2220 Returns an arrayref indicating the data type for each column in the
2222 statement. This method returns undef if called before "execute()".
2223 PRECISION (arrayref, read-only)
2225 Returns an arrayref of integer values for each column returned by the
2227 statement. The number indicates the precision for "NUMERIC" columns,
2228 the size in number of characters for "CHAR" and "VARCHAR" columns, and
2229 for all other types of columns it returns the number of bytes. This
2230 method returns undef if called before "execute()".
2231 SCALE (arrayref, read-only)
2233 Returns an arrayref of integer values for each column returned by the
2235 statement. The number indicates the scale of the that column. The only
2236 type that will return a value is "NUMERIC". This method returns undef
2237 if called before "execute()".
2238 NULLABLE (arrayref, read-only)
2240 Returns an arrayref of integer values for each column returned by the
2242 statement. The number indicates if the column is nullable or not. 0 =
2243 not nullable, 1 = nullable, 2 = unknown. This method returns undef if
2244 called before "execute()".
2245 Database (dbh, read-only)
2247 Returns the database handle this statement handle was created from.
2249 ParamValues (hash ref, read-only)
2251 Returns a reference to a hash containing the values currently bound to
2253 placeholders. If the "named parameters" type of placeholders are being
2254 used (such as ":foo"), then the keys of the hash will be the names of
2255 the placeholders (without the colon). If the "dollar sign numbers" type
2256 of placeholders are being used, the keys of the hash will be the
2257 numbers, without the dollar signs. If the "question mark" type is used,
2258 integer numbers will be returned, starting at one and increasing for
2259 every placeholder.
2260 If this method is called before "execute", the literal values passed in
2262 are returned. If called after "execute", then the quoted versions of
2263 the values are returned.
2264 ParamTypes (hash ref, read-only)
2266 Returns a reference to a hash containing the type names currently bound
2268 to placeholders. The keys are the same as returned by the ParamValues
2269 method. The values are hashrefs containing a single key value pair, in
2270 which the key is either 'TYPE' if the type has a generic SQL
2271 equivalent, and 'pg_type' if the type can only be expressed by a
2272 Postgres type. The value is the internal number corresponding to the
2273 type originally passed in. (Placeholders that have not yet been bound
2274 will return undef as the value). This allows the output of ParamTypes
2275 to be passed back to the "bind_param" method.
2276 Statement (string, read-only)
2278 Returns the statement string passed to the most recent "prepare" method
2280 called in this database handle, even if that method failed. This is
2281 especially useful where "RaiseError" is enabled and the exception
2282 handler checks $@ and sees that a "prepare" method call failed.
2283 pg_current_row (integer, read-only)
2285 DBD::Pg specific attribute. Returns the number of the tuple (row) that
2287 was most recently fetched. Returns zero before and after fetching is
2288 performed.
2289 pg_numbound (integer, read-only)
2291 DBD::Pg specific attribute. Returns the number of placeholders that are
2293 currently bound (via bind_param).
2294 pg_bound (hashref, read-only)
2296 DBD::Pg specific attribute. Returns a hash of all named placeholders.
2298 The key is the name of the placeholder, and the value is a 0 or a 1,
2299 indicating if the placeholder has been bound yet (e.g. via bind_param)
2300 pg_size (arrayref, read-only)
2302 DBD::Pg specific attribute. It returns a reference to an array of
2304 integer values for each column. The integer shows the size of the
2305 column in bytes. Variable length columns are indicated by -1.
2306 pg_type (arrayref, read-only)
2308 DBD::Pg specific attribute. It returns a reference to an array of
2310 strings for each column. The string shows the name of the data_type.
2311 pg_segments (arrayref, read-only)
2313 DBD::Pg specific attribute. Returns an arrayref of the query split on
2315 the placeholders.
2316 pg_oid_status (integer, read-only)
2318 DBD::Pg specific attribute. It returns the OID of the last INSERT
2320 command.
2321 pg_cmd_status (integer, read-only)
2323 DBD::Pg specific attribute. It returns the type of the last command.
2325 Possible types are: "INSERT", "DELETE", "UPDATE", "SELECT".
2326 pg_direct (boolean)
2328 DBD::Pg specific attribute. Default is false. If true, the query is
2330 passed directly to the backend without parsing for placeholders.
2331 pg_prepare_now (boolean)
2333 DBD::Pg specific attribute. Default is off. If true, the query will be
2335 immediately prepared, rather than waiting for the "execute" call.
2336 pg_prepare_name (string)
2338 DBD::Pg specific attribute. Specifies the name of the prepared
2340 statement to use for this statement handle. Not normally needed, see
2341 the section on the "prepare" method for more information.
2342 pg_server_prepare (boolean)
2344 DBD::Pg specific attribute. Indicates if DBD::Pg should attempt to use
2346 server-side prepared statements for this statement handle. The default
2347 value, true, indicates that prepared statements should be used whenever
2348 possible. See the section on the "prepare" method for more information.
2349 pg_switch_prepared (integer)
2351 DBD::Pg specific attribute. Indicates when DBD::Pg will internally
2353 switch from using PQexecParams to PQexecPrepared. In other words, when
2354 it will start using server-side prepared statements (assuming all other
2355 requirements for them are met). The default value, 2, means that a
2356 prepared statement will be prepared and used the second and subsequent
2357 time execute is called. To always use PQexecPrepared instead of
2358 PQexecParams, set pg_switch_prepared to 1 (this was the default
2359 behavior in earlier versions). Setting pg_switch_prepared to 0 will
2360 force DBD::Pg to always use PQexecParams.
2361 pg_placeholder_dollaronly (boolean)
2363 DBD::Pg specific attribute. Defaults to false. When true, question
2365 marks inside of the query being prepared are not treated as
2366 placeholders. Useful for statements that contain unquoted question
2367 marks, such as geometric operators. Note that you may also simply
2368 escape question marks with a backslash to prevent them from being
2369 treated as placeholders.
2370 pg_placeholder_nocolons (boolean)
2372 DBD::Pg specific attribute. Defaults to false. When true, colons inside
2374 of statements are not treated as placeholders. Useful for statements
2375 that contain an array slice. You may also place a backslash directly
2376 before the colon to prevent it from being treated as a placeholder.
2377 pg_async (integer)
2379 DBD::Pg specific attribute. Indicates the current behavior for
2381 asynchronous queries. See the section on "Asynchronous Constants" for
2382 more information.
2383 pg_async_status (integer, read-only)
2385 DBD::Pg specific attribute. Returns the current status of an
2387 asynchronous command. 0 indicates no asynchronous command is in
2388 progress, 1 indicates that an asynchronous command has started and -1
2389 indicated that an asynchronous command has been cancelled.
2390 RowsInCache
2392 Not used by DBD::Pg
2394 RowCache
2396 Not used by DBD::Pg
2398 CursorName
2400 Not used by DBD::Pg. See the note about "Cursors" elsewhere in this
2402 document.
2403
2405 Encoding
2406 DBD::Pg has extensive support for a client_encoding of UTF-8, and most
2407 things like encoding and decoding should happen automatically. If you
2408 are using a different encoding, you will need do the encoding and
2409 decoding yourself. For this reason, it is highly recommended to always
2410 use a client_encoding of UTF-8. The server_encoding can be anything,
2411 and no recommendations are made there, other than avoid SQL_ASCII
2412 whenever possible.
2413 Transactions
2415 Transaction behavior is controlled via the "AutoCommit" attribute. For
2416 a complete definition of "AutoCommit" please refer to the DBI
2417 documentation.
2418 According to the DBI specification the default for "AutoCommit" is a
2420 true value. In this mode, any change to the database becomes valid
2421 immediately. Any "BEGIN", "COMMIT" or "ROLLBACK" statements will be
2422 rejected. Note that preparing a statement does not always contact the
2423 server, as the actual "PREPARE" is usually postponed until the first
2424 call to "execute".
2425 Savepoints
2427 PostgreSQL version 8.0 introduced the concept of savepoints, which
2428 allows transactions to be rolled back to a certain point without
2429 affecting the rest of the transaction. DBD::Pg encourages using the
2430 following methods to control savepoints:
2431 "pg_savepoint"
2433 Creates a savepoint. This will fail unless you are inside of a
2435 transaction. The only argument is the name of the savepoint. Note that
2436 PostgreSQL DOES allow multiple savepoints with the same name to exist.
2437 $dbh->pg_savepoint("mysavepoint");
2439 "pg_rollback_to"
2441 Rolls the database back to a named savepoint, discarding any work
2443 performed after that point. If more than one savepoint with that name
2444 exists, rolls back to the most recently created one.
2445 $dbh->pg_rollback_to("mysavepoint");
2447 "pg_release"
2449 Releases (or removes) a named savepoint. If more than one savepoint
2451 with that name exists, it will only destroy the most recently created
2452 one. Note that all savepoints created after the one being released are
2453 also destroyed.
2454 $dbh->pg_release("mysavepoint");
2456 Asynchronous Queries
2458 It is possible to send a query to the backend and have your script do
2459 other work while the query is running on the backend. Both queries sent
2460 by the "do" method, and by the "execute" method can be sent
2461 asynchronously. The basic usage is as follows:
2462 print "Async do() example:\n";
2464 $dbh->do("SELECT long_running_query()", {pg_async => PG_ASYNC});
2465 do_something_else();
2466 {
2467 if ($dbh->pg_ready()) {
2468 $res = $dbh->pg_result();
2469 print "Result of do(): $res\n";
2470 }
2471 print "Query is still running...\n";
2472 if (cancel_request_received) {
2473 $dbh->pg_cancel();
2474 }
2475 sleep 1;
2476 redo;
2477 }
2478 print "Async prepare/execute example:\n";
2480 $sth = $dbh->prepare("SELECT long_running_query(1)", {pg_async => PG_ASYNC});
2481 $sth->execute();
2482 ## Changed our mind, cancel and run again:
2484 $sth = $dbh->prepare("SELECT 678", {pg_async => PG_ASYNC + PG_OLDQUERY_CANCEL});
2485 $sth->execute();
2486 do_something_else();
2488 if (!$sth->pg_ready) {
2490 do_another_thing();
2491 }
2492 ## We wait until it is done, and get the result:
2494 $res = $dbh->pg_result();
2495 Asynchronous Constants
2497 There are currently three asynchronous constants automatically exported
2499 by DBD::Pg.
2500 PG_ASYNC
2502 This is a constant for the number 1. It is passed to either the
2503 "do" or the "prepare" method as a value to the pg_async key and
2504 indicates that the query should be sent asynchronously.
2505 PG_OLDQUERY_CANCEL
2507 This is a constant for the number 2. When passed to either the "do"
2508 or the "prepare" method, it causes any currently running
2509 asynchronous query to be cancelled and rolled back. It has no
2510 effect if no asynchronous query is currently running.
2511 PG_OLDQUERY_WAIT
2513 This is a constant for the number 4. When passed to either the "do"
2514 or the "prepare" method, it waits for any currently running
2515 asynchronous query to complete. It has no effect if there is no
2516 asynchronous query currently running.
2517 Asynchronous Methods
2519 pg_cancel
2521 This database-level method attempts to cancel any currently running
2522 asynchronous query. It returns true if the cancel succeeded, and
2523 false otherwise. Note that a query that has finished before this
2524 method is executed will also return false. WARNING: a successful
2525 cancellation may leave the database in an unusable state, so you
2526 may need to ROLLBACK or ROLLBACK TO a savepoint. As of version
2527 2.17.0 of DBD::Pg, rollbacks are not done automatically.
2528 $result = $dbh->pg_cancel();
2530 pg_ready
2532 This method can be called as a database handle method or (for
2533 convenience) as a statement handle method. Both simply see if a
2534 previously issued asynchronous query has completed yet. It returns
2535 true if the statement has finished, in which case you should then
2536 call the "pg_result" method. Calls to "pg_ready()" should only be
2537 used when you have other things to do while the query is running.
2538 If you simply want to wait until the query is done, do not call
2539 pg_ready() over and over, but simply call the pg_result() method.
2540 my $time = 0;
2542 while (!$dbh->pg_ready) {
2543 print "Query is still running. Seconds: $time\n";
2544 $time++;
2545 sleep 1;
2546 }
2547 $result = $dbh->pg_result;
2548 pg_result
2550 This database handle method returns the results of a previously
2551 issued asynchronous query. If the query is still running, this
2552 method will wait until it has finished. The result returned is the
2553 number of rows: the same thing that would have been returned by the
2554 asynchronous "do" or "execute" if it had been called without an
2555 asynchronous flag.
2556 $result = $dbh->pg_result;
2558 Asynchronous Examples
2560 Here are some working examples of asynchronous queries. Note that we'll
2562 use the pg_sleep function to emulate a long-running query.
2563 use strict;
2565 use warnings;
2566 use Time::HiRes 'sleep';
2567 use DBD::Pg ':async';
2568 my $dbh = DBI->connect('dbi:Pg:dbname=postgres', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
2570 ## Kick off a long running query on the first database:
2572 my $sth = $dbh->prepare("SELECT pg_sleep(?)", {pg_async => PG_ASYNC});
2573 $sth->execute(5);
2574 ## While that is running, do some other things
2576 print "Your query is processing. Thanks for waiting\n";
2577 check_on_the_kids(); ## Expensive sub, takes at least three seconds.
2578 while (!$dbh->pg_ready) {
2580 check_on_the_kids();
2581 ## If the above function returns quickly for some reason, we add a small sleep
2582 sleep 0.1;
2583 }
2584 print "The query has finished. Gathering results\n";
2586 my $result = $sth->pg_result;
2587 print "Result: $result\n";
2588 my $info = $sth->fetchall_arrayref();
2589 Without asynchronous queries, the above script would take about 8
2591 seconds to run: five seconds waiting for the execute to finish, then
2592 three for the check_on_the_kids() function to return. With asynchronous
2593 queries, the script takes about 6 seconds to run, and gets in two
2594 iterations of check_on_the_kids in the process.
2595 Here's an example showing the ability to cancel a long-running query.
2597 Imagine two slave databases in different geographic locations over a
2598 slow network. You need information as quickly as possible, so you query
2599 both at once. When you get an answer, you tell the other one to stop
2600 working on your query, as you don't need it anymore.
2601 use strict;
2603 use warnings;
2604 use Time::HiRes 'sleep';
2605 use DBD::Pg ':async';
2606 my $dbhslave1 = DBI->connect('dbi:Pg:dbname=postgres;host=slave1', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
2608 my $dbhslave2 = DBI->connect('dbi:Pg:dbname=postgres;host=slave2', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
2609 $SQL = "SELECT count(*) FROM largetable WHERE flavor='blueberry'";
2611 my $sth1 = $dbhslave1->prepare($SQL, {pg_async => PG_ASYNC});
2613 my $sth2 = $dbhslave2->prepare($SQL, {pg_async => PG_ASYNC});
2614 $sth1->execute();
2616 $sth2->execute();
2617 my $winner;
2619 while (!defined $winner) {
2620 if ($sth1->pg_ready) {
2621 $winner = 1;
2622 }
2623 elsif ($sth2->pg_ready) {
2624 $winner = 2;
2625 }
2626 Time::HiRes::sleep 0.05;
2627 }
2628 my $count;
2630 if ($winner == 1) {
2631 $sth2->pg_cancel();
2632 $sth1->pg_result();
2633 $count = $sth1->fetchall_arrayref()->[0][0];
2634 }
2635 else {
2636 $sth1->pg_cancel();
2637 $sth2->pg_result();
2638 $count = $sth2->fetchall_arrayref()->[0][0];
2639 }
2640 Array support
2642 DBD::Pg allows arrays (as arrayrefs) to be passed in to both the
2643 "quote" and the "execute" methods. In both cases, the array is
2644 flattened into a string representing a Postgres array.
2645 When fetching rows from a table that contains a column with an array
2647 type, the result will be passed back to your script as an arrayref.
2648 To turn off the automatic parsing of returned arrays into arrayrefs,
2650 you can set the attribute pg_expand_array, which is true by default.
2651 $dbh->{pg_expand_array} = 0;
2653 COPY support
2655 DBD::Pg allows for quick (bulk) reading and storing of data by using
2656 the COPY command. The basic process is to use "$dbh->do" to issue a
2657 COPY command, and then to either add rows using "pg_putcopydata", or to
2658 read them by using "pg_getcopydata".
2659 The first step is to put the server into "COPY" mode. This is done by
2661 sending a complete COPY command to the server, by using the "do"
2662 method. For example:
2663 $dbh->do("COPY foobar FROM STDIN");
2665 This would tell the server to enter a COPY IN mode (yes, that's
2667 confusing, but the mode is COPY IN because of the command COPY FROM).
2668 It is now ready to receive information via the "pg_putcopydata" method.
2669 The complete syntax of the COPY command is more complex and not
2670 documented here: the canonical PostgreSQL documentation for COPY can be
2671 found at:
2672 http://www.postgresql.org/docs/current/static/sql-copy.html
2674 Once a COPY command has been issued, no other SQL commands are allowed
2676 until "pg_putcopyend" has been issued (for COPY FROM), or the final
2677 "pg_getcopydata" has been called (for COPY TO).
2678 Note: All other COPY methods (pg_putline, pg_getline, etc.) are now
2680 heavily deprecated in favor of the pg_getcopydata, pg_putcopydata, and
2681 pg_putcopyend methods.
2682 pg_getcopydata
2684 Used to retrieve data from a table after the server has been put into a
2686 COPY OUT mode by calling "COPY tablename TO STDOUT". Data is always
2687 returned one data row at a time. The first argument to pg_getcopydata
2688 is the variable into which the data will be stored (this variable
2689 should not be undefined, or it may throw a warning, although it may be
2690 a reference). The pg_getcopydata method returns a number greater than 1
2691 indicating the new size of the variable, or a -1 when the COPY has
2692 finished. Once a -1 has been returned, no other action is necessary, as
2693 COPY mode will have already terminated. Example:
2694 $dbh->do("COPY mytable TO STDOUT");
2696 my @data;
2697 my $x=0;
2698 1 while $dbh->pg_getcopydata($data[$x++]) >= 0;
2699 There is also a variation of this method called pg_getcopydata_async,
2701 which, as the name suggests, returns immediately. The only difference
2702 from the original method is that this version may return a 0,
2703 indicating that the row is not ready to be delivered yet. When this
2704 happens, the variable has not been changed, and you will need to call
2705 the method again until you get a non-zero result. (Data is still
2706 always returned one data row at a time.)
2707 pg_putcopydata
2709 Used to put data into a table after the server has been put into COPY
2711 IN mode by calling "COPY tablename FROM STDIN". The only argument is
2712 the data you want inserted. Issue a pg_putcopyend() when you have added
2713 all your rows.
2714 The default delimiter is a tab character, but this can be changed in
2716 the COPY statement. Returns a 1 on successful input. Examples:
2717 ## Simple example:
2719 $dbh->do("COPY mytable FROM STDIN");
2720 $dbh->pg_putcopydata("123\tPepperoni\t3\n");
2721 $dbh->pg_putcopydata("314\tMushroom\t8\n");
2722 $dbh->pg_putcopydata("6\tAnchovies\t100\n");
2723 $dbh->pg_putcopyend();
2724 ## This example uses explicit columns and a custom delimiter
2726 $dbh->do("COPY mytable(flavor, slices) FROM STDIN WITH DELIMITER '~'");
2727 $dbh->pg_putcopydata("Pepperoni~123\n");
2728 $dbh->pg_putcopydata("Mushroom~314\n");
2729 $dbh->pg_putcopydata("Anchovies~6\n");
2730 $dbh->pg_putcopyend();
2731 pg_putcopyend
2733 When you are finished with pg_putcopydata, call pg_putcopyend to let
2735 the server know that you are done, and it will return to a normal, non-
2736 COPY state. Returns a 1 on success. This method will fail if called
2737 when not in COPY IN mode.
2738 Postgres limits
2740 For convenience, DBD::Pg can export certain constants representing the
2741 limits of Postgres data types. To use them, just add ":pg_limits" when
2742 DBD::Pg is used:
2743 use DBD::Pg qw/:pg_limits/;
2745 The constants and their values are:
2747 PG_MIN_SMALLINT -32768
2749 PG_MAX_SMALLINT 32767
2750 PG_MIN_INTEGER -2147483648
2751 PG_MAX_INTEGER 2147483647
2752 PG_MIN_BIGINT -9223372036854775808
2753 PG_MAX_BIGINT 9223372036854775807
2754 PG_MIN_SMALLSERIAL 1
2755 PG_MAX_SMALLSERIAL 32767
2756 PG_MIN_SERIAL 1
2757 PG_MAX_SERIAL 2147483647
2758 PG_MIN_BIGSERIAL 1
2759 PG_MAX_BIGSERIAL 9223372036854775807
2760 Large Objects
2762 DBD::Pg supports all largeobject functions provided by libpq via the
2763 "$dbh->pg_lo*" methods. Please note that access to a large object, even
2764 read-only large objects, must be put into a transaction.
2765 Cursors
2767 Although PostgreSQL supports cursors, they have not been used in the
2768 current implementation. When DBD::Pg was created, cursors in PostgreSQL
2769 could only be used inside a transaction block. Because only one
2770 transaction block at a time is allowed, this would have implied the
2771 restriction not to use any nested "SELECT" statements. Therefore the
2772 "execute" method fetches all data at once into data structures located
2773 in the front-end application. This fact must to be considered when
2774 selecting large amounts of data!
2775 You can use cursors in your application, but you'll need to do a little
2777 work. First you must declare your cursor. Now you can issue queries
2778 against the cursor, then select against your queries. This typically
2779 results in a double loop, like this:
2780 # WITH HOLD is not needed if AutoCommit is off
2782 $dbh->do("DECLARE csr CURSOR WITH HOLD FOR $sql");
2783 while (1) {
2784 my $sth = $dbh->prepare("fetch 1000 from csr");
2785 $sth->execute;
2786 last if 0 == $sth->rows;
2787 while (my $row = $sth->fetchrow_hashref) {
2789 # Do something with the data.
2790 }
2791 }
2792 $dbh->do("CLOSE csr");
2793 Datatype bool
2795 The current implementation of PostgreSQL returns 't' for true and 'f'
2796 for false. From the Perl point of view, this is a rather unfortunate
2797 choice. DBD::Pg therefore translates the result for the "BOOL" data
2798 type in a Perlish manner: 'f' becomes the number 0 and 't' becomes the
2799 number 1. This way the application does not have to check the database-
2800 specific returned values for the data-type "BOOL" because Perl treats 0
2801 as false and 1 as true. You may set the pg_bool_tf attribute to a true
2802 value to change the values back to 't' and 'f' if you wish.
2803 Boolean values can be passed to PostgreSQL as TRUE, 't', 'true', 'y',
2805 'yes' or '1' for true and FALSE, 'f', 'false', 'n', 'no' or '0' for
2806 false.
2807 Schema support
2809 The PostgreSQL schema concept may differ from those of other databases.
2810 In a nutshell, a schema is a named collection of objects within a
2811 single database. Please refer to the PostgreSQL documentation for more
2812 details:
2813 <http://www.postgresql.org/docs/current/static/ddl-schemas.html>
2815 DBD::Pg does not provide explicit support for PostgreSQL schemas.
2817 However, schema functionality may be used without any restrictions by
2818 explicitly addressing schema objects, e.g.
2819 my $res = $dbh->selectall_arrayref("SELECT * FROM my_schema.my_table");
2821 or by manipulating the schema search path with "SET search_path", e.g.
2823 $dbh->do("SET search_path TO my_schema, public");
2825
2827 The DBI module
2828
2830 To report a bug, or view the current list of bugs, please visit
2831 https://github.com/bucardo/dbdpg/issues
2832
2834 Pull requests can be submitted to github. Detailed information on how
2835 to help out with this module can be found in the README.dev file. The
2836 latest development version can be obtained via: git clone
2837 git://github.com/bucardo/dbdpg.git
2838
2840 DBI by Tim Bunce <http://www.tim.bunce.name>
2841 The original DBD-Pg was by Edmund Mergl (E.Mergl@bawue.de) and Jeffrey
2843 W. Baker (jwbaker@acm.org). Major developers include David Wheeler
2844 <david@justatheory.com>, Jason Stewart <jason@openinformatics.com>,
2845 Bruce Momjian <pgman@candle.pha.pa.us>, and Greg Sabino Mullane
2846 <greg@turnstep.com>, with help from many others: see the Changes file
2847 (<http://search.cpan.org/dist/DBD-Pg/Changes>) for a complete list.
2848 Parts of this package were originally copied from DBI and DBD-Oracle.
2850 Mailing List
2852 The current maintainers may be reached through the 'dbd-pg' mailing
2854 list: <dbd-pg@perl.org>. Subscribe by sending an email to
2855 dbd-pg-subscribe@perl.org. Visit the archives at
2856 http://grokbase.com/g/perl/dbd-pg
2857
2859 Copyright (C) 1994-2021, Greg Sabino Mullane
2860 This module (DBD::Pg) is free software; you can redistribute it and/or
2862 modify it under the same terms as Perl 5.10.0. For more details, see
2863 the full text of the licenses in the directory LICENSES.
2864perl v5.34.0 2021-07-22 Pg(3)