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.16.3 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 undefined
80 (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. Service names
84 can be defined in either a per-user service file or a system-wide file.
85 If the same service name exists in both the user and the system file,
86 the user file takes precedence. By default, the per-user service file
87 is named ~/.pg_service.conf. On Microsoft Windows, it is named
88 %APPDATA% \postgresql\.pg_service.conf (where %APPDATA% refers to the
89 Application Data subdirectory in the user's profile). A different file
90 name can be specified by setting the environment variable
91 PGSERVICEFILE. The system-wide file is named pg_service.conf. The
92 location of this file can be controlled by setting the PGSYSCONFDIR
93 environment variable. To use one of the named services within the file,
94 set the name by using either the service parameter or the environment
95 variable PGSERVICE. Note that when connecting this way, only the
96 minimum parameters should be used. For example, to connect to a service
97 named "zephyr", you could use:
98 $dbh = DBI->connect("dbi:Pg:service=zephyr", '', '');
100 You could also set $ENV{PGSERVICE} to "zephyr" and connect like this:
102 $dbh = DBI->connect("dbi:Pg:", '', '');
104 The format of the pg_service.conf file is simply a bracketed service
106 name, followed by one parameter per line in the format name=value. For
107 example:
108 [zephyr]
110 dbname=winds
111 user=wisp
112 password=W$2Hc00YSgP
113 port=6543
114 There are four valid arguments to the sslmode parameter, which controls
116 whether to use SSL to connect to the database:
117 • disable: SSL connections are never used
119 • allow: try non-SSL, then SSL
121 • prefer: try SSL, then non-SSL
123 • require: connect only with SSL
125 You can also connect using sockets in a specific directory. This may be
127 needed if the server you are connecting to has a different default
128 socket directory from the one used to compile DBD::Pg. Use the
129 complete path to the socket directory as the name of the host, like
130 this:
131 $dbh = DBI->connect('dbi:Pg:dbname=foo;host=/var/tmp/socket',
133 $username,
134 $password,
135 {AutoCommit => 0, RaiseError => 1});
136 The attribute hash can also contain a key named "dbd_verbose", which
138 simply calls "$dbh->trace('DBD')" after the handle is created. This
139 attribute is not recommended, as it is clearer to simply explicitly
140 call "trace" explicitly in your script.
141 connect_cached
143 $dbh = DBI->connect_cached("dbi:Pg:dbname=$dbname", $username, $password, \%options);
145 Implemented by DBI, no driver-specific impact.
147 data_sources
149 @data_sources = DBI->data_sources('Pg');
151 @data_sources = $dbh->data_sources();
152 Returns a list of available databases. Unless the environment variable
154 "DBI_DSN" is set, a connection will be attempted to the database
155 "template1". The normal connection environment variables also apply,
156 such as "PGHOST", "PGPORT", "DBI_USER", "DBI_PASS", and "PGSERVICE".
157 You can also pass in options to add to the connection string For
159 example, to specify an alternate port and host:
160 @data_sources = DBI->data_sources('Pg', 'port=5824;host=example.com');
162 or:
164 @data_sources = $dbh->data_sources('port=5824;host=example.com');
166 Methods Common To All Handles
168 For all of the methods below, $h can be either a database handle ($dbh)
169 or a statement handle ($sth). Note that $dbh and $sth can be replaced
170 with any variable name you choose: these are just the names most often
171 used. Another common variable used in this documentation is $rv, which
172 stands for "return value".
173 err
175 $rv = $h->err;
177 Returns the error code from the last method called. For the connect
179 method it returns "PQstatus", which is a number used by libpq (the
180 Postgres connection library). A value of 0 indicates no error
181 (CONNECTION_OK), while any other number indicates a failed connection.
182 The only other number commonly seen is 1 (CONNECTION_BAD). See the
183 libpq documentation for the complete list of return codes.
184 In all other non-connect methods "$h->err" returns the "PQresultStatus"
186 of the current handle. This is a number used by libpq and is one of:
187 0 Empty query string
189 1 A command that returns no data successfully completed.
190 2 A command that returns data successfully completed.
191 3 A COPY OUT command is still in progress.
192 4 A COPY IN command is still in progress.
193 5 A bad response was received from the backend.
194 6 A nonfatal error occurred (a notice or warning message)
195 7 A fatal error was returned: the last query failed.
196 errstr
198 $str = $h->errstr;
200 Returns the last error that was reported by Postgres. This message is
202 affected by the pg_errorlevel setting.
203 state
205 $str = $h->state;
207 Returns a five-character "SQLSTATE" code. Success is indicated by a
209 00000 code, which gets mapped to an empty string by DBI. A code of
210 "S8006" indicates a connection failure, usually because the connection
211 to the Postgres server has been lost.
212 While this method can be called as either "$sth->state" or
214 "$dbh->state", it is usually clearer to always use "$dbh->state".
215 The list of codes used by PostgreSQL can be found at:
217 <http://www.postgresql.org/docs/current/static/errcodes-appendix.html>
218 Note that these codes are part of the SQL standard and only a small
220 number of them will be used by PostgreSQL.
221 Common codes:
223 00000 Successful completion
225 25P01 No active SQL transaction
226 25P02 In failed SQL transaction
227 S8006 Connection failure
228 trace
230 $h->trace($trace_settings);
232 $h->trace($trace_settings, $trace_filename);
233 $trace_settings = $h->trace;
234 Changes the trace settings on a database or statement handle. The
236 optional second argument specifies a file to write the trace
237 information to. If no filename is given, the information is written to
238 STDERR. Note that tracing can be set globally as well by setting
239 "DBI->trace", or by using the environment variable DBI_TRACE.
240 The value is either a numeric level or a named flag. For the flags that
242 DBD::Pg uses, see parse_trace_flag.
243 trace_msg
245 $h->trace_msg($message_text);
247 $h->trace_msg($message_text, $min_level);
248 Writes a message to the current trace output (as set by the "trace"
250 method). If a second argument is given, the message is only written if
251 the current tracing level is equal to or greater than the $min_level.
252 parse_trace_flag and parse_trace_flags
254 $h->trace($h->parse_trace_flags('SQL|pglibpq'));
256 $h->trace($h->parse_trace_flags('1|pgstart'));
257 ## Simpler:
259 $h->trace('SQL|pglibpq');
260 $h->trace('1|pgstart');
261 my $value = DBD::Pg->parse_trace_flag('pglibpq');
263 DBI->trace($value);
264 The parse_trace_flags method is used to convert one or more named flags
266 to a number which can passed to the "trace" method. DBD::Pg currently
267 supports the DBI-specific flag, "SQL", as well as the ones listed
268 below.
269 Flags can be combined by using the parse_trace_flags method, which
271 simply calls "parse_trace_flag" on each item and combines them.
272 Sometimes you may wish to turn the tracing on before you connect to the
274 database. The second example above shows a way of doing this: the call
275 to "DBD::Pg->parse_trace_flags" provides a number than can be fed to
276 "DBI->trace" before you create a database handle.
277 DBD::Pg supports the following trace flags:
279 SQL Outputs all SQL statements. Note that the output provided will not
281 necessarily be in a form suitable to passing directly to Postgres,
282 as server-side prepared statements are used extensively by DBD::Pg.
283 For maximum portability of output (but with a potential performance
284 hit), use with "$dbh->{pg_server_prepare} = 0".
285 DBD Turns on all non-DBI flags, in other words, only the ones that are
287 specific to DBD::Pg (all those below which start with the letters
288 'pg').
289 pglibpq
291 Outputs the name of each libpq function (without arguments)
292 immediately before running it. This is a good way to trace the flow
293 of your program at a low level. This information is also output if
294 the trace level is set to 4 or greater.
295 pgstart
297 Outputs the name of each internal DBD::Pg function, and other
298 information such as the function arguments or important global
299 variables, as each function starts. This information is also output
300 if the trace level is set to 4 or greater.
301 pgend
303 Outputs a simple message at the very end of each internal DBD::Pg
304 function. This is also output if the trace level is set to 4 or
305 greater.
306 pgprefix
308 Forces each line of trace output to begin with the string "dbdpg:
309 ". This helps to differentiate it from the normal DBI trace output.
310 pglogin
312 Outputs a message showing the connection string right before a new
313 database connection is attempted, a message when the connection was
314 successful, and a message right after the database has been
315 disconnected. Also output if trace level is 5 or greater.
316 See the DBI section on TRACING for more information.
318 func
320 DBD::Pg uses the "func" method to support a variety of functions. Note
322 that the name of the function comes last, after the arguments.
323 table_attributes
325 $attrs = $dbh->func($table, 'table_attributes');
326 Use of the tables_attributes function is no longer recommended.
328 Instead, you can use the more portable "column_info" and
329 "primary_key" methods to access the same information.
330 The table_attributes method returns, for the given table argument,
332 a reference to an array of hashes, each of which contains the
333 following keys:
334 NAME attribute name
336 TYPE attribute type
337 SIZE attribute size (-1 for variable size)
338 NULLABLE flag nullable
339 DEFAULT default value
340 CONSTRAINT constraint
341 PRIMARY_KEY flag is_primary_key
342 REMARKS attribute description
343 pg_lo_creat
345 $lobjId = $dbh->pg_lo_creat($mode);
346 Creates a new large object and returns the object-id. $mode is a
348 bitmask describing read and write access to the new object. This
349 setting is ignored since Postgres version 8.1. For backwards
350 compatibility, however, you should set a valid mode anyway (see
351 "pg_lo_open" for a list of valid modes).
352 Upon failure it returns "undef". This function cannot be used if
354 AutoCommit is enabled.
355 The old way of calling large objects functions is deprecated:
357 $dbh->func(.., 'lo_);
358 pg_lo_open
360 $lobj_fd = $dbh->pg_lo_open($lobjId, $mode);
361 Opens an existing large object and returns an object-descriptor for
363 use in subsequent "pg_lo_*" calls. $mode is a bitmask describing
364 read and write access to the opened object. It may be one of:
365 $dbh->{pg_INV_READ}
367 $dbh->{pg_INV_WRITE}
368 $dbh->{pg_INV_READ} | $dbh->{pg_INV_WRITE}
369 "pg_INV_WRITE" and "pg_INV_WRITE | pg_INV_READ" modes are
371 identical; in both modes, the large object can be read from or
372 written to. Reading from the object will provide the object as
373 written in other committed transactions, along with any writes
374 performed by the current transaction. Objects opened with
375 "pg_INV_READ" cannot be written to. Reading from this object will
376 provide the stored data at the time of the transaction snapshot
377 which was active when "pg_lo_write" was called.
378 Returns "undef" upon failure. Note that 0 is a perfectly correct
380 (and common) object descriptor! This function cannot be used if
381 AutoCommit is enabled.
382 pg_lo_write
384 $nbytes = $dbh->pg_lo_write($lobj_fd, $buffer, $len);
385 Writes $len bytes of $buffer into the large object $lobj_fd.
387 Returns the number of bytes written and "undef" upon failure. This
388 function cannot be used if AutoCommit is enabled.
389 pg_lo_read
391 $nbytes = $dbh->pg_lo_read($lobj_fd, $buffer, $len);
392 Reads $len bytes into $buffer from large object $lobj_fd. Returns
394 the number of bytes read and "undef" upon failure. This function
395 cannot be used if AutoCommit is enabled.
396 pg_lo_lseek
398 $loc = $dbh->pg_lo_lseek($lobj_fd, $offset, $whence);
399 Changes the current read or write location on the large object
401 $obj_id. Currently $whence can only be 0 (which is L_SET). Returns
402 the current location and "undef" upon failure. This function cannot
403 be used if AutoCommit is enabled.
404 pg_lo_lseek64
406 Backwards compatible alias for "pg_lo_lseek". Since DBD::Pg 3.16,
407 that method handles 64-bit offsets if supported by the Perl and
408 PostgreSQL versions in use.
409 pg_lo_tell
411 $loc = $dbh->pg_lo_tell($lobj_fd);
412 Returns the current read or write location on the large object
414 $lobj_fd and "undef" upon failure. This function cannot be used if
415 AutoCommit is enabled.
416 pg_lo_tell64
418 Backwards compatible alias for "pg_lo_tell". Since DBD::Pg 3.16,
419 that method handles 64-bit offsets if supported by the Perl and
420 PostgreSQL versions in use.
421 pg_lo_truncate
423 $loc = $dbh->pg_lo_truncate($lobj_fd, $len);
424 Truncates the given large object to the new size. Returns "undef"
426 on failure, and 0 on success. This function cannot be used if
427 AutoCommit is enabled.
428 pg_lo_truncate64
430 Backwards compatible alias "for pg_lo_truncate". Since DBD::Pg
431 3.16, that method handles 64-bit offsets if supported by the Perl
432 and PostgreSQL versions in use.
433 pg_lo_close
435 $lobj_fd = $dbh->pg_lo_close($lobj_fd);
436 Closes an existing large object. Returns true upon success and
438 false upon failure. This function cannot be used if AutoCommit is
439 enabled.
440 pg_lo_unlink
442 $ret = $dbh->pg_lo_unlink($lobjId);
443 Deletes an existing large object. Returns true upon success and
445 false upon failure. This function cannot be used if AutoCommit is
446 enabled.
447 pg_lo_import
449 $lobjId = $dbh->pg_lo_import($filename);
450 Imports a Unix file as a large object and returns the object id of
452 the new object or "undef" upon failure.
453 pg_lo_import_with_oid
455 $lobjId = $dbh->pg_lo_import($filename, $OID);
456 Same as pg_lo_import, but attempts to use the supplied OID as the
458 large object number. If this number is 0, it falls back to the
459 behavior of pg_lo_import (which assigns the next available OID).
460 This is only available when DBD::Pg is compiled against a Postgres
462 server version 8.4 or later.
463 pg_lo_export
465 $ret = $dbh->pg_lo_export($lobjId, $filename);
466 Exports a large object into a Unix file. Returns false upon
468 failure, true otherwise.
469 getfd
471 $fd = $dbh->func('getfd');
472 Deprecated, use $dbh->{pg_socket} instead.
474 private_attribute_info
476 $hashref = $dbh->private_attribute_info();
478 $hashref = $sth->private_attribute_info();
479 Returns a hash of all private attributes used by DBD::Pg, for either a
481 database or a statement handle. Currently, all the hash values are
482 undef.
483
485 InactiveDestroy (boolean)
486 If set to true, then the "disconnect" method will not be automatically
488 called when the database handle goes out of scope. This is required if
489 you are forking, and even then you must tread carefully and ensure that
490 either the parent or the child (but not both!) handles all database
491 calls from that point forwards, so that messages from the Postgres
492 backend are only handled by one of the processes. If you don't set
493 things up properly, you will see messages such as "server closed the
494 connection unexpectedly", and "message type 0x32 arrived from server
495 while idle". The best solution is to either have the child process
496 reconnect to the database with a fresh database handle, or to rewrite
497 your application not to use forking. See the section on "Asynchronous
498 Queries" for a way to have your script continue to work while the
499 database is processing a request.
500 AutoInactiveDestroy (boolean)
502 The InactiveDestroy attribute, described above, needs to be explicitly
504 set in the child process after a fork. If the code that performs the
505 fork is in a third party module such as Sys::Syslog, this can present a
506 problem. Use AutoInactiveDestroy to get around this problem.
507 RaiseError (boolean, inherited)
509 Forces errors to always raise an exception. Although it defaults to
511 off, it is recommended that this be turned on, as the alternative is to
512 check the return value of every method (prepare, execute, fetch, etc.)
513 manually, which is easy to forget to do.
514 PrintError (boolean, inherited)
516 Forces database errors to also generate warnings, which can then be
518 filtered with methods such as locally redefining $SIG{__WARN__} or
519 using modules such as "CGI::Carp". This attribute is on by default.
520 ShowErrorStatement (boolean, inherited)
522 Appends information about the current statement to error messages. If
524 placeholder information is available, adds that as well. Defaults to
525 false.
526 Note that this will not work when using "do" without any arguments.
528 Warn (boolean, inherited)
530 Enables warnings. This is on by default, and should only be turned off
532 in a local block for a short a time only when absolutely needed.
533 Executed (boolean, read-only)
535 Indicates if a handle has been executed. For database handles, this
537 value is true after the "do" method has been called, or when one of the
538 child statement handles has issued an "execute". Issuing a "commit" or
539 "rollback" always resets the attribute to false for database handles.
540 For statement handles, any call to "execute" or its variants will flip
541 the value to true for the lifetime of the statement handle.
542 TraceLevel (integer, inherited)
544 Sets the trace level, similar to the "trace" method. See the sections
546 on "trace" and parse_trace_flag for more details.
547 Active (boolean, read-only)
549 Indicates if a handle is active or not. For database handles, this
551 indicates if the database has been disconnected or not. For statement
552 handles, it indicates if all the data has been fetched yet or not. Use
553 of this attribute is not encouraged.
554 Kids (integer, read-only)
556 Returns the number of child processes created for each handle type. For
558 a driver handle, indicates the number of database handles created. For
559 a database handle, indicates the number of statement handles created.
560 For statement handles, it always returns zero, because statement
561 handles do not create kids.
562 ActiveKids (integer, read-only)
564 Same as "Kids", but only returns those that are active.
566 CachedKids (hash ref)
568 Returns a hashref of handles. If called on a database handle, returns
570 all statement handles created by use of the "prepare_cached" method. If
571 called on a driver handle, returns all database handles created by the
572 "connect_cached" method.
573 ChildHandles (array ref)
575 Implemented by DBI, no driver-specific impact.
577 PrintWarn (boolean, inherited)
579 Implemented by DBI, no driver-specific impact.
581 HandleError (boolean, inherited)
583 Implemented by DBI, no driver-specific impact.
585 HandleSetErr (code ref, inherited)
587 Implemented by DBI, no driver-specific impact.
589 ErrCount (unsigned integer)
591 Implemented by DBI, no driver-specific impact.
593 FetchHashKeyName (string, inherited)
595 Implemented by DBI, no driver-specific impact.
597 ChopBlanks (boolean, inherited)
599 Supported by DBD::Pg as proposed by DBI. This method is similar to the
601 SQL function "RTRIM".
602 Taint (boolean, inherited)
604 Implemented by DBI, no driver-specific impact.
606 TaintIn (boolean, inherited)
608 Implemented by DBI, no driver-specific impact.
610 TaintOut (boolean, inherited)
612 Implemented by DBI, no driver-specific impact.
614 Profile (inherited)
616 Implemented by DBI, no driver-specific impact.
618 Type (scalar)
620 Returns "dr" for a driver handle, "db" for a database handle, and "st"
622 for a statement handle. Should be rarely needed.
623 LongReadLen
625 Not used by DBD::Pg
627 LongTruncOk
629 Not used by DBD::Pg
631 CompatMode
633 Not used by DBD::Pg
635
637 Database Handle Methods
638 selectall_arrayref
639 $ary_ref = $dbh->selectall_arrayref($sql);
641 $ary_ref = $dbh->selectall_arrayref($sql, \%attr);
642 $ary_ref = $dbh->selectall_arrayref($sql, \%attr, @bind_values);
643 Returns a reference to an array containing the rows returned by
645 preparing and executing the SQL string. See the DBI documentation for
646 full details.
647 selectall_hashref
649 $hash_ref = $dbh->selectall_hashref($sql, $key_field);
651 Returns a reference to a hash containing the rows returned by preparing
653 and executing the SQL string. See the DBI documentation for full
654 details.
655 selectcol_arrayref
657 $ary_ref = $dbh->selectcol_arrayref($sql, \%attr, @bind_values);
659 Returns a reference to an array containing the first column from each
661 rows returned by preparing and executing the SQL string. It is possible
662 to specify exactly which columns to return. See the DBI documentation
663 for full details.
664 prepare
666 $sth = $dbh->prepare($statement, \%attr);
668 WARNING: DBD::Pg now (as of version 1.40) uses true prepared statements
670 by sending them to the backend to be prepared by the Postgres server.
671 Statements that were legal before may no longer work. See below for
672 details.
673 The prepare method prepares a statement for later execution. PostgreSQL
675 supports prepared statements, which enables DBD::Pg to only send the
676 query once, and simply send the arguments for every subsequent call to
677 "execute". DBD::Pg can use these server-side prepared statements, or
678 it can just send the entire query to the server each time. The best way
679 is automatically chosen for each query. This will be sufficient for
680 most users: keep reading for a more detailed explanation and some
681 optional flags.
682 Queries that do not begin with the word "SELECT", "INSERT", "UPDATE",
684 or "DELETE" are never sent as server-side prepared statements.
685 Deciding whether or not to use prepared statements depends on many
687 factors, but you can force them to be used or not used by using the
688 pg_server_prepare attribute when calling "prepare". Setting this to
689 false means to never use prepared statements. Setting pg_server_prepare
690 to true means that prepared statements should be used whenever
691 possible. This is the default.
692 The pg_server_prepare attribute can also be set at connection time like
694 so:
695 $dbh = DBI->connect($DBNAME, $DBUSER, $DBPASS,
697 { AutoCommit => 0,
698 RaiseError => 1,
699 pg_server_prepare => 0,
700 });
701 or you may set it after your database handle is created:
703 $dbh->{pg_server_prepare} = 1;
705 To enable it for just one particular statement:
707 $sth = $dbh->prepare("SELECT id FROM mytable WHERE val = ?",
709 { pg_server_prepare => 1 });
710 You can even toggle between the two as you go:
712 $sth->{pg_server_prepare} = 1;
714 $sth->execute(22);
715 $sth->{pg_server_prepare} = 0;
716 $sth->execute(44);
717 $sth->{pg_server_prepare} = 1;
718 $sth->execute(66);
719 In the above example, the first execute will use the previously
721 prepared statement. The second execute will not, but will build the
722 query into a single string and send it to the server. The third one
723 will act like the first and only send the arguments. Even if you
724 toggle back and forth, a statement is only prepared once.
725 Using prepared statements is in theory quite a bit faster: not only
727 does the PostgreSQL backend only have to prepare the query only once,
728 but DBD::Pg no longer has to worry about quoting each value before
729 sending it to the server.
730 However, there are some drawbacks. The server cannot always choose the
732 ideal parse plan because it will not know the arguments before hand.
733 But for most situations in which you will be executing similar data
734 many times, the default plan will probably work out well. Programs such
735 as PgBouncer which cache connections at a low level should not use
736 prepared statements via DBD::Pg, or must take extra care in the
737 application to account for the fact that prepared statements are not
738 shared across database connections. Further discussion on this subject
739 is beyond the scope of this documentation: please consult the pgsql-
740 performance mailing list,
741 <http://archives.postgresql.org/pgsql-performance/>
742 Only certain commands will be sent to a server-side prepare: currently
744 these include "SELECT", "INSERT", "UPDATE", and "DELETE". DBD::Pg uses
745 a simple naming scheme for the prepared statements themselves:
746 dbdpg_XY_Z, where Y is the current PID, X is either 'p' or 'n'
747 (depending on if the PID is a positive or negative number), and Z is a
748 number that starts at 1 and increases each time a new statement is
749 prepared. This number is tracked at the database handle level, so
750 multiple statement handles will not collide.
751 You cannot send more than one command at a time in the same prepare
753 command (by separating them with semi-colons) when using server-side
754 prepares.
755 The actual "PREPARE" is usually not performed until the first execute
757 is called, due to the fact that information on the data types (provided
758 by "bind_param") may be provided after the prepare but before the
759 execute.
760 A server-side prepare may happen before the first "execute", but only
762 if the server can handle the server-side prepare, and the statement
763 contains no placeholders. It will also be prepared if the
764 pg_prepare_now attribute is passed in and set to a true value.
765 Similarly, the pg_prepare_now attribute can be set to 0 to ensure that
766 the statement is not prepared immediately, although the cases in which
767 you would want this are very rare. Finally, you can set the default
768 behavior of all prepare statements by setting the pg_prepare_now
769 attribute on the database handle:
770 $dbh->{pg_prepare_now} = 1;
772 The following two examples will be prepared right away:
774 $sth->prepare("SELECT 123"); ## no placeholders
776 $sth->prepare("SELECT 123, ?", {pg_prepare_now => 1});
778 The following two examples will NOT be prepared right away:
780 $sth->prepare("SELECT 123, ?"); ## has a placeholder
782 $sth->prepare("SELECT 123", {pg_prepare_now => 0});
784 There are times when you may want to prepare a statement yourself. To
786 do this, simply send the "PREPARE" statement directly to the server
787 (e.g. with the "do" method). Create a statement handle and set the
788 prepared name via the pg_prepare_name attribute. The statement handle
789 can be created with a dummy statement, as it will not be executed.
790 However, it should have the same number of placeholders as your
791 prepared statement. Example:
792 $dbh->do('PREPARE mystat AS SELECT COUNT(*) FROM pg_class WHERE reltuples < ?');
794 $sth = $dbh->prepare('SELECT ?');
795 $sth->bind_param(1, 1, SQL_INTEGER);
796 $sth->{pg_prepare_name} = 'mystat';
797 $sth->execute(123);
798 The above will run the equivalent of this query on the backend:
800 EXECUTE mystat(123);
802 which is the equivalent of:
804 SELECT COUNT(*) FROM pg_class WHERE reltuples < 123;
806 You can force DBD::Pg to send your query directly to the server by
808 adding the pg_direct attribute to your prepare call. This is not
809 recommended, but is added just in case you need it.
810 Placeholders
812 There are three types of placeholders that can be used in DBD::Pg. The
814 first is the "question mark" type, in which each placeholder is
815 represented by a single question mark character. This is the method
816 recommended by the DBI specs and is the most portable. Each question
817 mark is internally replaced by a "dollar sign number" in the order in
818 which they appear in the query (important when using "bind_param").
819 The second type of placeholder is "dollar sign numbers". This is the
821 method that Postgres uses internally and is overall probably the best
822 method to use if you do not need compatibility with other database
823 systems. DBD::Pg, like PostgreSQL, allows the same number to be used
824 more than once in the query. Numbers must start with "1" and increment
825 by one value (but can appear in any order within the query). If the
826 same number appears more than once in a query, it is treated as a
827 single parameter and all instances are replaced at once. Examples:
828 Not legal:
830 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $2'; # Does not start with 1
832 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $3'; # Missing 2
834 Legal:
836 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $1';
838 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $2';
840 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $2 AND $1'; # legal but confusing
842 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $2 AND reltuples > $1';
844 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $1 AND reltuples > $1';
846 In the final statement above, DBI thinks there is only one placeholder,
848 so this statement will replace both placeholders:
849 $sth->bind_param(1, 2045);
851 While a simple execute with no bind_param calls requires only a single
853 argument as well:
854 $sth->execute(2045);
856 The final placeholder type is "named parameters" in the format ":foo".
858 While this syntax is supported by DBD::Pg, its use is discouraged in
859 favor of dollar-sign numbers.
860 The different types of placeholders cannot be mixed within a statement,
862 but you may use different ones for each statement handle you have. This
863 is confusing at best, so stick to one style within your program.
864 If your queries use operators that contain question marks (e.g. some of
866 the native Postgres geometric operators and JSON operators) or array
867 slices (e.g. "data[100:300]"), there are methods to instruct DBD::Pg to
868 not treat some symbols as placeholders. First, you may simply add a
869 backslash before the start of a placeholder, and DBD::Pg will strip the
870 backslash and not treat the character as a placeholder.
871 You can also tell DBD::Pg to ignore any non-dollar sign placeholders by
873 setting the pg_placeholder_dollaronly attribute at either the database
874 handle or the statement handle level. Examples:
875 $dbh->{pg_placeholder_dollaronly} = 1;
877 $sth = $dbh->prepare(q{SELECT * FROM mytable WHERE lseg1 ?# lseg2 AND name = $1});
878 $sth->execute('segname');
879 Alternatively, you can set it at prepare time:
881 $sth = $dbh->prepare(q{SELECT * FROM mytable WHERE lseg1 ?-| lseg2 AND name = $1},
883 {pg_placeholder_dollaronly => 1});
884 $sth->execute('segname');
885 If your queries use array slices but you still want to use question
887 marks as placeholders, you can tell DBD::Pg to ignore just colon
888 placeholders by setting the "pg_placeholder_nocolons" attribute in the
889 same way. Examples:
890 $dbh->{pg_placeholder_nocolons} = 1;
892 $sth = $dbh->prepare(q{SELECT array[1:2] FROM mytable WHERE id = ?});
893 $sth->execute(1);
894 Again, you may set it at prepare time as well:
896 $sth = $dbh->prepare(q{SELECT array[1:2] FROM mytable WHERE id = ?},
898 {pg_placeholder_nocolons => 1});
899 $sth->execute(1);
900 It should be noted that placeholders only work when used outside of a
902 literal string context; i.e., the following examples will not
903 define/use any placeholders due to appearing inside strings within the
904 SQL:
905 $sth = $dbh->prepare(q{SELECT id FROM mytable WHERE text LIKE '%?'});
907 $dbh->do(q{DO LANGUAGE plpgsql $$ BEGIN RAISE NOTICE ?; END $$}, undef, $message);
908 See the DBI placeholder documentation for more details.
910 prepare_cached
912 $sth = $dbh->prepare_cached($statement, \%attr);
914 Implemented by DBI, no driver-specific impact. This method is most
916 useful when using a server that supports server-side prepares, and you
917 have asked the prepare to happen immediately via the pg_prepare_now
918 attribute.
919 do
921 $rv = $dbh->do($statement);
923 $rv = $dbh->do($statement, \%attr);
924 $rv = $dbh->do($statement, \%attr, @bind_values);
925 Prepare and execute a single statement. Returns the number of rows
927 affected if the query was successful, returns undef if an error
928 occurred, and returns -1 if the number of rows is unknown or not
929 available. Note that this method will return 0E0 instead of 0 for 'no
930 rows were affected', in order to always return a true value if no error
931 occurred.
932 If neither "\%attr" nor @bind_values is given, the query will be sent
934 directly to the server without the overhead of internally creating a
935 statement handle and running prepare and execute, for a measurable
936 speed increase.
937 Note that an empty statement (a string with no length) will not be
939 passed to the server; if you want a simple test, use "SELECT 123" or
940 the "ping" method.
941 last_insert_id
943 $rv = $dbh->last_insert_id(undef, $schema, $table, undef);
945 $rv = $dbh->last_insert_id(undef, $schema, $table, undef, {sequence => $seqname});
946 Attempts to return the id of the last value to be inserted into a
948 table. You can either provide a sequence name (preferred) or provide a
949 table name with optional schema, and DBD::Pg will attempt to find the
950 sequence itself. The current value of the sequence is returned by a
951 call to the CURRVAL() PostgreSQL function. This will fail if the
952 sequence has not yet been used in the current database connection.
953 If you do not know the name of the sequence, you can provide a table
955 name and DBD::Pg will attempt to return the correct value. To do this,
956 there must be at least one column in the table with a "NOT NULL"
957 constraint, that has a unique constraint, and which uses a sequence as
958 a default value (either manually, or via the "SERIAL" pseudotype or
959 "GENERATED ... AS IDENTITY"). If more than one column meets these
960 conditions, the primary key will be used. This involves some looking up
961 of things in the system table, so DBD::Pg will cache the sequence name
962 for subsequent calls. If you need to disable this caching for some
963 reason, (such as the sequence name changing), you can control it by
964 adding "pg_cache => 0" to the final (hashref) argument for
965 last_insert_id.
966 Please keep in mind that this method is far from foolproof, so make
968 your script use it properly. Specifically, make sure that it is called
969 immediately after the insert, and that the insert does not add a value
970 to the column that is using the sequence as a default value. However,
971 because we are using sequences, you can be sure that the value you got
972 back has not been used by any other process.
973 Some examples:
975 $dbh->do('CREATE SEQUENCE lii_seq START 1');
977 $dbh->do(q{CREATE TABLE lii (
978 foobar INTEGER NOT NULL UNIQUE DEFAULT nextval('lii_seq'),
979 baz VARCHAR)});
980 $SQL = 'INSERT INTO lii(baz) VALUES (?)';
981 $sth = $dbh->prepare($SQL);
982 for (qw(uno dos tres cuatro)) {
983 $sth->execute($_);
984 my $newid = $dbh->last_insert_id(undef,undef,undef,undef,{sequence=>'lii_seq'});
985 print "Last insert id was $newid\n";
986 }
987 If you did not want to worry about the sequence name:
989 $dbh->do('CREATE TABLE lii2 (
991 foobar SERIAL UNIQUE,
992 baz VARCHAR)');
993 $SQL = 'INSERT INTO lii2(baz) VALUES (?)';
994 $sth = $dbh->prepare($SQL);
995 for (qw(uno dos tres cuatro)) {
996 $sth->execute($_);
997 my $newid = $dbh->last_insert_id(undef,undef,"lii2",undef);
998 print "Last insert id was $newid\n";
999 }
1000 commit
1002 $rv = $dbh->commit;
1004 Issues a COMMIT to the server, indicating that the current transaction
1006 is finished and that all changes made will be visible to other
1007 processes. If AutoCommit is enabled, then a warning is given and no
1008 COMMIT is issued. Returns true on success, false on error. See also
1009 the section on "Transactions".
1010 rollback
1012 $rv = $dbh->rollback;
1014 Issues a ROLLBACK to the server, which discards any changes made in the
1016 current transaction. If AutoCommit is enabled, then a warning is given
1017 and no ROLLBACK is issued. Returns true on success, and false on error.
1018 See also the the section on "Transactions".
1019 begin_work
1021 This method turns on transactions until the next call to "commit" or
1023 "rollback", if AutoCommit is currently enabled. If it is not enabled,
1024 calling begin_work will issue an error. Note that the transaction will
1025 not actually begin until the first statement after begin_work is
1026 called. Example:
1027 $dbh->{AutoCommit} = 1;
1029 $dbh->do('INSERT INTO foo VALUES (123)'); ## Changes committed immediately
1030 $dbh->begin_work();
1031 ## Not in a transaction yet, but AutoCommit is set to 0
1032 $dbh->do("INSERT INTO foo VALUES (345)");
1034 ## DBD::PG actually issues two statements here:
1035 ## BEGIN;
1036 ## INSERT INTO foo VALUES (345)
1037 ## We are now in a transaction
1038 $dbh->commit();
1040 ## AutoCommit is now set to 1 again
1041 disconnect
1043 $rv = $dbh->disconnect;
1045 Disconnects from the Postgres database. Any uncommitted changes will be
1047 rolled back upon disconnection. It's good policy to always explicitly
1048 call commit or rollback at some point before disconnecting, rather than
1049 relying on the default rollback behavior.
1050 This method may give warnings about "disconnect invalidates X active
1052 statement handle(s)". This means that you called "$sth->execute()" but
1053 did not finish fetching all the rows from them. To avoid seeing this
1054 warning, either fetch all the rows or call "$sth->finish()" for each
1055 executed statement handle.
1056 If the script exits before disconnect is called (or, more precisely, if
1058 the database handle is no longer referenced by anything), then the
1059 database handle's DESTROY method will call the rollback() and
1060 disconnect() methods automatically. It is best to explicitly disconnect
1061 rather than rely on this behavior.
1062 quote
1064 $rv = $dbh->quote($value, $data_type);
1066 This module implements its own "quote" method. For simple string types,
1068 both backslashes and single quotes are doubled. You may also quote
1069 arrayrefs and receive a string suitable for passing into Postgres array
1070 columns.
1071 If the value contains backslashes, and the server is version 8.1 or
1073 higher, then the escaped string syntax will be used (which places a
1074 capital E before the first single quote). This syntax is always used
1075 when quoting bytea values on servers 8.1 and higher.
1076 The "data_type" argument is optional and should be one of the type
1078 constants exported by DBD::Pg (such as PG_BYTEA). In addition to
1079 string, bytea, char, bool, and other standard types, the following
1080 geometric types are supported: point, line, lseg, box, path, polygon,
1081 and circle (PG_POINT, PG_LINE, PG_LSEG, PG_BOX, PG_PATH, PG_POLYGON,
1082 and PG_CIRCLE respectively). To quote a Postgres-specific data type,
1083 you must use a 'hashref' argument like so:
1084 my $quotedval = $dbh->quote($value, { pg_type => PG_VARCHAR });
1086 NOTE: The undocumented (and invalid) support for the "SQL_BINARY" data
1088 type is officially deprecated. Use "PG_BYTEA" with bind_param()
1089 instead:
1090 $rv = $sth->bind_param($param_num, $bind_value,
1092 { pg_type => PG_BYTEA });
1093 quote_identifier
1095 $string = $dbh->quote_identifier( $name );
1097 $string = $dbh->quote_identifier( undef, $schema, $table);
1098 Returns a quoted version of the supplied string, which is commonly a
1100 schema, table, or column name. The three argument form will return the
1101 schema and the table together, separated by a dot. Examples:
1102 print $dbh->quote_identifier('grapefruit'); ## Prints: "grapefruit"
1104 print $dbh->quote_identifier('juicy fruit'); ## Prints: "juicy fruit"
1106 print $dbh->quote_identifier(undef, 'public', 'pg_proc');
1108 ## Prints: "public"."pg_proc"
1109 pg_notifies
1111 $ret = $dbh->pg_notifies;
1113 Looks for any asynchronous notifications received and returns either
1115 "undef" or a reference to a three-element array consisting of an event
1116 name, the PID of the backend that sent the NOTIFY command, and the
1117 optional payload string. Note that this does not check if the
1118 connection to the database is still valid first - for that, use the
1119 "ping" method. You may need to commit if not in autocommit mode - new
1120 notices will not be picked up while in the middle of a transaction. An
1121 example:
1122 $dbh->do("LISTEN abc");
1124 $dbh->do("LISTEN def");
1125 ## Hang around until we get the message we want
1127 LISTENLOOP: {
1128 while (my $notify = $dbh->pg_notifies) {
1129 my ($name, $pid, $payload) = @$notify;
1130 print qq{I received notice "$name" from PID $pid, payload was "$payload"\n};
1131 ## Do something based on the notice received
1132 }
1133 $dbh->ping() or die qq{Ping failed!};
1134 $dbh->commit();
1135 sleep(5);
1136 redo;
1137 }
1138 Payloads will always be an empty string unless you are connecting to a
1140 Postgres server version 9.0 or higher.
1141 ping
1143 $rv = $dbh->ping;
1145 The "ping" method determines if there is a working connection to an
1147 active database server. It does this by sending a small query to the
1148 server, currently 'DBD::Pg ping test v3.16.3'. It returns 0 (false) if
1149 the connection is not valid, otherwise it returns a positive number
1150 (true). The value returned indicates the current state:
1151 Value Meaning
1153 --------------------------------------------------
1154 1 Database is idle (not in a transaction)
1155 2 Database is active, there is a command in progress (usually seen after a COPY command)
1156 3 Database is idle within a transaction
1157 4 Database is idle, within a failed transaction
1158 Additional information on why a handle is not valid can be obtained by
1160 using the "pg_ping" method.
1161 pg_ping
1163 $rv = $dbh->pg_ping;
1165 This is a DBD::Pg-specific extension to the "ping" method. This will
1167 check the validity of a database handle in exactly the same way as
1168 "ping", but instead of returning a 0 for an invalid connection, it will
1169 return a negative number. So in addition to returning the positive
1170 numbers documented for "ping", it may also return the following:
1171 Value Meaning
1173 --------------------------------------------------
1174 -1 There is no connection to the database at all (e.g. after disconnect)
1175 -2 An unknown transaction status was returned (e.g. after forking)
1176 -3 The test query failed (PQexec returned null)
1177 -4 PQstatus returned a CONNECTION_BAD
1178 pg_error_field
1180 $value = $dbh->pg_error_field('context');
1182 The pg_error_field returns specific information about the last error
1184 that occurred. It needs to be called as soon as possible after an
1185 error occurs, as any other query sent to Postgres (via $dbh or $sth)
1186 will reset all the error information. Note that this is called at the
1187 database handle ($dbh) level, but can return errors that occurred via
1188 both database handles (e.g. $dbh->do) and statement handles (e.g.
1189 $sth->execute). It takes a single argument, indicating which field to
1190 return. The value returned will be undef if the previous command was
1191 not an error, or if the field is not applicable to the current error.
1192 The canonical list of field types can be found at:
1194 <https://www.postgresql.org/docs/current/libpq-exec.html#LIBPQ-PQRESULTERRORFIELD>
1196 The literal names on that page can be used (e.g.
1198 PG_DIAG_STATEMENT_HINT), but lowercase is accepted too, as well as the
1199 following abbreviated forms:
1200 severity
1202 severity_nonlocal (only for Postgres 10 and above)
1203 state
1204 primary
1205 detail (does not work well for Postgres < 9.2)
1206 hint
1207 statement_position
1208 internal_position
1209 internal_query
1210 context
1211 schema (only for Postgres 9.3 and above)
1212 table (only for Postgres 9.3 and above)
1213 column (only for Postgres 9.3 and above)
1214 type (only for Postgres 9.3 and above)
1215 constraint (only for Postgres 9.3 and above)
1216 source_file
1217 source_line
1218 source_function
1219 get_info
1221 $value = $dbh->get_info($info_type);
1223 Supports a very large set (> 250) of the information types, including
1225 the minimum recommended by DBI.
1226 Items of note:
1228 SQL_KEYWORDS
1230 This returns all items reserved by Postgres but NOT reserved by
1231 SQL:2011 standard. See:
1232 http://www.postgresql.org/docs/current/static/sql-keywords-appendix.htm
1234 table_info
1236 $sth = $dbh->table_info(undef, $schema, $table, $type);
1238 Returns all tables and views visible to the current user. The schema
1240 and table arguments will do a "LIKE" search if a percent sign ("%") or
1241 an underscore ("_") is detected in the argument. The $type argument
1242 accepts any comma-separated combination of "TABLE", "VIEW", "SYSTEM
1243 TABLE", "SYSTEM VIEW", "MATERIALIZED VIEW", "SYSTEM MATERIALIZED VIEW",
1244 "FOREIGN TABLE", "SYSTEM FOREIGN TABLE", or "LOCAL TEMPORARY". (Using
1245 all is the default action.)
1246 Note that a statement handle is returned, and not a direct list of
1248 tables. See the examples below for ways to handle this.
1249 The following fields are returned:
1251 TABLE_CAT: The name of the database that the table or view is in
1253 (always the current database).
1254 TABLE_SCHEM: The name of the schema that the table or view is in.
1256 TABLE_NAME: The name of the table or view.
1258 TABLE_TYPE: The type of object returned. Will be one of "TABLE",
1260 "VIEW", "MATERIALIZED VIEW", "SYSTEM VIEW", "SYSTEM MATERIALIZED VIEW",
1261 "SYSTEM TABLE", "FOREIGN TABLE", "SYSTEM FOREIGN TABLE", or "LOCAL
1262 TEMPORARY".
1263 The TABLE_SCHEM and TABLE_NAME will be quoted via quote_ident().
1265 Four additional fields specific to DBD::Pg are returned:
1267 pg_schema: the unquoted name of the schema
1269 pg_table: the unquoted name of the table
1271 pg_tablespace_name: the name of the tablespace the table is in
1273 pg_tablespace_location: the location of the tablespace the table is in
1275 Tables that have not been assigned to a particular tablespace (or
1277 views) will return NULL ("undef") for both of the above field.
1278 Rows are returned alphabetically, with all tables first, and then all
1280 views.
1281 Examples of use:
1283 ## Display all tables and views in the public schema:
1285 $sth = $dbh->table_info('', 'public', undef, undef);
1286 for my $rel (@{$sth->fetchall_arrayref({})}) {
1287 print "$rel->{TABLE_TYPE} name is $rel->{TABLE_NAME}\n";
1288 }
1289 # Display the schema of all tables named 'foo':
1292 $sth = $dbh->table_info('', undef, 'foo', 'TABLE');
1293 for my $rel (@{$sth->fetchall_arrayref({})}) {
1294 print "Table name is $rel->{TABLE_SCHEM}.$rel->{TABLE_NAME}\n";
1295 }
1296 column_info
1298 $sth = $dbh->column_info( undef, $schema, $table, $column );
1300 Supported by this driver as proposed by DBI with the follow exceptions.
1302 These fields are currently always returned with NULL ("undef") values:
1303 BUFFER_LENGTH
1305 DECIMAL_DIGITS
1306 NUM_PREC_RADIX
1307 SQL_DATA_TYPE
1308 SQL_DATETIME_SUB
1309 CHAR_OCTET_LENGTH
1310 Also, six additional non-standard fields are returned:
1312 pg_type: data type with additional info i.e. "character varying(20)"
1314 pg_constraint: holds column constraint definition
1316 pg_schema: the unquoted name of the schema
1318 pg_table: the unquoted name of the table
1320 pg_column: the unquoted name of the column
1322 pg_enum_values: an array reference of allowed values for an enum column
1324 Note that the TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME fields all
1326 return output wrapped in quote_ident(). If you need the unquoted
1327 version, use the pg_ fields above.
1328 primary_key_info
1330 $sth = $dbh->primary_key_info( undef, $schema, $table, \%attr );
1332 Supported by this driver as proposed by DBI. There are no search
1334 patterns allowed, but leaving the $schema argument blank will cause the
1335 first table found in the schema search path to be used. An additional
1336 field, "DATA_TYPE", is returned and shows the data type for each of the
1337 arguments in the "COLUMN_NAME" field.
1338 This method will also return tablespace information for servers that
1340 support tablespaces. See the "table_info" entry for more information.
1341 The five additional custom fields returned are:
1343 pg_tablespace_name: name of the tablespace, if any
1345 pg_tablespace_location: location of the tablespace
1347 pg_schema: the unquoted name of the schema
1349 pg_table: the unquoted name of the table
1351 pg_column: the unquoted name of the column
1353 In addition to the standard format of returning one row for each column
1355 found for the primary key, you can pass the "pg_onerow" attribute to
1356 force a single row to be used. If the primary key has multiple columns,
1357 the "KEY_SEQ", "COLUMN_NAME", and "DATA_TYPE" fields will return a
1358 comma-delimited string. If the "pg_onerow" attribute is set to "2", the
1359 fields will be returned as an arrayref, which can be useful when
1360 multiple columns are involved:
1361 $sth = $dbh->primary_key_info('', '', 'dbd_pg_test', {pg_onerow => 2});
1363 if (defined $sth) {
1364 my $pk = $sth->fetchall_arrayref()->[0];
1365 print "Table $pk->[2] has a primary key on these columns:\n";
1366 for (my $x=0; defined $pk->[3][$x]; $x++) {
1367 print "Column: $pk->[3][$x] (data type: $pk->[6][$x])\n";
1368 }
1369 }
1370 primary_key
1372 @key_column_names = $dbh->primary_key(undef, $schema, $table);
1374 Simple interface to the "primary_key_info" method. Returns a list of
1376 the column names that comprise the primary key of the specified table.
1377 The list is in primary key column sequence order. If there is no
1378 primary key then an empty list is returned.
1379 foreign_key_info
1381 $sth = $dbh->foreign_key_info( $pk_catalog, $pk_schema, $pk_table,
1383 $fk_catalog, $fk_schema, $fk_table );
1384 Supported by this driver as proposed by DBI, using the SQL/CLI variant.
1386 There are no search patterns allowed, but leaving the $schema argument
1387 blank will cause the first table found in the schema search path to be
1388 used. Two additional fields, "UK_DATA_TYPE" and "FK_DATA_TYPE", are
1389 returned to show the data type for the unique and foreign key columns.
1390 Foreign keys that have no named constraint (where the referenced column
1391 only has an unique index) will return "undef" for the "UK_NAME" field.
1392 statistics_info
1394 $sth = $dbh->statistics_info( undef, $schema, $table, $unique_only, $quick );
1396 Returns a statement handle that can be fetched from to give statistics
1398 information on a specific table and its indexes. The $table argument is
1399 mandatory. The $schema argument is optional but recommended. The
1400 $unique_only argument, if true, causes only information about unique
1401 indexes to be returned. The $quick argument is not used by DBD::Pg. For
1402 information on the format of the standard rows returned, please see the
1403 DBI documentation.
1404 DBI section on statistics_info
1406 In addition, the following Postgres specific columns are returned:
1408 pg_expression
1410 Postgres allows indexes on functions and scalar expressions based
1411 on one or more columns. This field will always be populated if an
1412 index, but the lack of an entry in the COLUMN_NAME should indicate
1413 that this is an index expression.
1414 pg_is_key_column
1416 Postgres (since version 11) allows including non-key columns in
1417 indexes so they can be retrieved by index-only scans. This field
1418 will be false for such columns, and true for normal index columns.
1419 pg_null_ordering
1421 In addition to "ASC" and "DESC", Postgres supports specifying
1422 "NULLS FIRST" or "NULLS LAST" for index columns. For columns of
1423 indexes that support ordering, this field will be "first" or
1424 "last", otherwise it will be "undef";
1425 tables
1427 @names = $dbh->tables( undef, $schema, $table, $type, \%attr );
1429 Supported by this driver as proposed by DBI. This method returns all
1431 tables and/or views (including foreign tables and materialized views)
1432 which are visible to the current user: see "table_info" for more
1433 information about the arguments. The name of the schema appears before
1434 the table or view name. This can be turned off by adding in the
1435 "pg_noprefix" attribute:
1436 my @tables = $dbh->tables( '', '', 'dbd_pg_test', '', {pg_noprefix => 1} );
1438 type_info_all
1440 $type_info_all = $dbh->type_info_all;
1442 Supported by this driver as proposed by DBI. Information is only
1444 provided for SQL datatypes and for frequently used datatypes. The
1445 mapping between the PostgreSQL typename and the SQL92 datatype (if
1446 possible) has been done according to the following table:
1447 +---------------+------------------------------------+
1449 | typname | SQL92 |
1450 |---------------+------------------------------------|
1451 | bool | BOOL |
1452 | text | / |
1453 | bpchar | CHAR(n) |
1454 | varchar | VARCHAR(n) |
1455 | int2 | SMALLINT |
1456 | int4 | INT |
1457 | int8 | BIGINT |
1458 | money | / |
1459 | float4 | FLOAT(p) p<7=float4, p<16=float8 |
1460 | float8 | REAL |
1461 | abstime | / |
1462 | reltime | / |
1463 | tinterval | / |
1464 | date | / |
1465 | time | / |
1466 | datetime | / |
1467 | timespan | TINTERVAL |
1468 | timestamp | TIMESTAMP |
1469 +---------------+------------------------------------+
1470 type_info
1472 @type_info = $dbh->type_info($data_type);
1474 Returns a list of hash references holding information about one or more
1476 variants of $data_type. See the DBI documentation for more details.
1477 pg_server_trace
1479 $dbh->pg_server_trace($filehandle);
1481 Writes debugging information from the PostgreSQL backend to a file.
1483 This is not related to the DBI "trace" method and you should not use
1484 this method unless you know what you are doing. If you do enable this,
1485 be aware that the file will grow very large, very quick. To stop
1486 logging to the file, use the "pg_server_untrace" method. The first
1487 argument must be a file handle, not a filename. Example:
1488 my $pid = $dbh->{pg_pid};
1490 my $file = "pgbackend.$pid.debug.log";
1491 open(my $fh, ">$file") or die qq{Could not open "$file": $!\n};
1492 $dbh->pg_server_trace($fh);
1493 ## Run code you want to trace here
1494 $dbh->pg_server_untrace;
1495 close($fh);
1496 pg_server_untrace
1498 $dbh->pg_server_untrace;
1500 Stop server logging to a previously opened file.
1502 selectrow_array
1504 @row_ary = $dbh->selectrow_array($sql);
1506 @row_ary = $dbh->selectrow_array($sql, \%attr);
1507 @row_ary = $dbh->selectrow_array($sql, \%attr, @bind_values);
1508 Returns an array of row information after preparing and executing the
1510 provided SQL string. The rows are returned by calling "fetchrow_array".
1511 The string can also be a statement handle generated by a previous
1512 prepare. Note that only the first row of data is returned. If called in
1513 a scalar context, only the first column of the first row is returned.
1514 Because this is not portable, it is not recommended that you use this
1515 method in that way.
1516 selectrow_arrayref
1518 $ary_ref = $dbh->selectrow_arrayref($statement);
1520 $ary_ref = $dbh->selectrow_arrayref($statement, \%attr);
1521 $ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @bind_values);
1522 Exactly the same as "selectrow_array", except that it returns a
1524 reference to an array, by internal use of the "fetchrow_arrayref"
1525 method.
1526 selectrow_hashref
1528 $hash_ref = $dbh->selectrow_hashref($sql);
1530 $hash_ref = $dbh->selectrow_hashref($sql, \%attr);
1531 $hash_ref = $dbh->selectrow_hashref($sql, \%attr, @bind_values);
1532 Exactly the same as "selectrow_array", except that it returns a
1534 reference to an hash, by internal use of the "fetchrow_hashref" method.
1535 clone
1537 $other_dbh = $dbh->clone();
1539 Creates a copy of the database handle by connecting with the same
1541 parameters as the original handle, then trying to merge the attributes.
1542 See the DBI documentation for complete usage.
1543 Database Handle Attributes
1545 AutoCommit (boolean)
1546 Supported by DBD::Pg as proposed by DBI. According to the
1548 classification of DBI, PostgreSQL is a database in which a transaction
1549 must be explicitly started. Without starting a transaction, every
1550 change to the database becomes immediately permanent. The default of
1551 AutoCommit is on, but this may change in the future, so it is highly
1552 recommended that you explicitly set it when calling "connect". For
1553 details see the notes about "Transactions" elsewhere in this document.
1554 ParamValues (hash ref, read-only)
1556 Ignored unless inside a "do" method call. There it is temporarily
1558 aliased to the "ParamValues" hash from the temporary statement handle
1559 inside an internal "prepare / execute / fetch" routine, invisible from
1560 outside, and is treated correspondingly (see "ParamValues" in
1561 "Statement Handle Attributes"). This allows for correct reporting of
1562 values bound to placeholders to the caller, should the query fail (see
1563 "ShowErrorStatement").
1564 pg_bool_tf (boolean)
1566 DBD::Pg specific attribute. If true, boolean values will be returned as
1568 the characters 't' and 'f' instead of '1' and '0'.
1569 ReadOnly (boolean)
1571 $dbh->{ReadOnly} = 1;
1573 Specifies if the current database connection should be in read-only
1575 mode or not. In this mode, changes that change the database are not
1576 allowed and will throw an error. Note: this method will not work if
1577 "AutoCommit" is true. The read-only effect is accomplished by sending a
1578 SET TRANSACTION READ ONLY after every begin. For more details, please
1579 see:
1580 http://www.postgresql.org/docs/current/interactive/sql-set-transaction.html
1582 Please not that this method is not foolproof: there are still ways to
1584 update the database. Consider this a safety net to catch applications
1585 that should not be issuing commands such as INSERT, UPDATE, or DELETE.
1586 This method requires DBI version 1.55 or better.
1588 pg_server_prepare (boolean)
1590 DBD::Pg specific attribute. Indicates if DBD::Pg should attempt to use
1592 server-side prepared statements. The default value, true, indicates
1593 that prepared statements should be used whenever possible. See the
1594 section on the "prepare" method for more information.
1595 pg_switch_prepared (integer)
1597 DBD::Pg specific attribute. Indicates when DBD::Pg will internally
1599 switch from using PQexecParams to PQexecPrepared. In other words, when
1600 it will start using server-side prepared statements (assuming all other
1601 requirements for them are met). The default value, 2, means that a
1602 prepared statement will be prepared and used the second and subsequent
1603 time execute is called. To always use PQexecPrepared instead of
1604 PQexecParams, set pg_switch_prepared to 1 (this was the default
1605 behavior in earlier versions). Setting pg_switch_prepared to 0 will
1606 force DBD::Pg to always use PQexecParams.
1607 pg_placeholder_dollaronly (boolean)
1609 DBD::Pg specific attribute. Defaults to false. When true, question
1611 marks inside of statements are not treated as placeholders. Useful for
1612 statements that contain unquoted question marks, such as geometric
1613 operators. Note that you may also simply escape question marks with a
1614 backslash to prevent them from being treated as placeholders.
1615 pg_placeholder_nocolons (boolean)
1617 DBD::Pg specific attribute. Defaults to false. When true, colons inside
1619 of statements are not treated as placeholders. Useful for statements
1620 that contain an array slice. You may also place a backslash directly
1621 before the colon to prevent it from being treated as a placeholder.
1622 pg_enable_utf8 (integer)
1624 DBD::Pg specific attribute. The behavior of DBD::Pg with regards to
1626 this flag has changed as of version 3.0.0. The default value for this
1627 attribute, -1, tells DBD::Pg to UTF8-decode all strings coming back
1628 from the database if the client_encoding is set to "UTF8". Use of this
1629 default is highly encouraged. If your code was previously using
1630 pg_enable_utf8, you can probably remove mention of it entirely.
1631 If this attribute is set to 0, then DBD::Pg will never UTF8-decode
1633 returned data, regardless of the current client_encoding.
1634 If this attribute is set to 1, then DBD::Pg will always UTF8-decode
1636 returned data, regardless of the current client_encoding (with the
1637 exception of bytea data).
1638 Note that the value of client_encoding is only checked on connection
1640 time. If you change the client_encoding to/from 'UTF8' after
1641 connecting, you can set pg_enable_utf8 to -1 to force DBD::Pg to read
1642 in the new client_encoding and act accordingly.
1643 pg_int8_as_string (integer)
1645 DBD::Pg specific attribute. Since version 3.0.0 the processing of
1647 SQL_INT8 has changed, before that 64 bit values were returned as
1648 strings, starting from version 3.0.0 64 bit values are returned as
1649 numbers. This flag makes it possible to return the old behavior. The
1650 old behavior is useful when encoding the results of a call in JSON
1651 format and passing it to JavaScript for processing, where integer
1652 values have a precision of no more than 53 bits.
1653 pg_errorlevel (integer)
1655 DBD::Pg specific attribute. Sets the amount of information returned by
1657 the server's error messages. Valid entries are 0, 1, and 2. Any other
1658 number will be forced to the default value of 1.
1659 A value of 0 ("TERSE") will show severity, primary text, and position
1661 only and will usually fit on a single line. A value of 1 ("DEFAULT")
1662 will also show any detail, hint, or context fields. A value of 2
1663 ("VERBOSE") will show all available information.
1664 pg_lib_version (integer, read-only)
1666 DBD::Pg specific attribute. Indicates which version of PostgreSQL that
1668 DBD::Pg was compiled against. In other words, which libraries were
1669 used. Returns a number with major, minor, and revision together;
1670 version 8.1.4 would be returned as 80104.
1671 pg_server_version (integer, read-only)
1673 DBD::Pg specific attribute. Indicates which version of PostgreSQL that
1675 the current database handle is connected to. Returns a number with
1676 major, minor, and revision together; version 8.0.1 would be 80001.
1677 Name (string, read-only)
1679 Returns the name of the current database. This is the same as the DSN,
1681 without the "dbi:Pg:" part. Before version 2.0.0, this only returned
1682 the bare database name (e.g. 'foo'). From version 2.0.0 onwards, it
1683 returns the more correct output (e.g. 'dbname=foo')
1684 Username (string, read-only)
1686 Returns the name of the user connected to the database.
1688 pg_db (string, read-only)
1690 DBD::Pg specific attribute. Returns the name of the current database.
1692 pg_user (string, read-only)
1694 DBD::Pg specific attribute. Returns the name of the user that connected
1696 to the server.
1697 pg_host (string, read-only)
1699 DBD::Pg specific attribute. Returns the host of the current server
1701 connection. Locally connected hosts will return an empty string.
1702 pg_port (integer, read-only)
1704 DBD::Pg specific attribute. Returns the port of the connection to the
1706 server.
1707 pg_socket (integer, read-only)
1709 DBD::Pg specific attribute. Returns the file description number of the
1711 connection socket to the server.
1712 pg_pass (string, read-only)
1714 DBD::Pg specific attribute. Returns the password used to connect to the
1716 server.
1717 pg_options (string, read-only)
1719 DBD::Pg specific attribute. Returns the command-line options passed to
1721 the server. May be an empty string.
1722 pg_default_port (integer, read-only)
1724 DBD::Pg specific attribute. Returns the default port used if none is
1726 specifically given.
1727 pg_pid (integer, read-only)
1729 DBD::Pg specific attribute. Returns the process id (PID) of the backend
1731 server process handling the connection.
1732 pg_prepare_now (boolean)
1734 DBD::Pg specific attribute. Default is off. If true, then the "prepare"
1736 method will immediately prepare commands, rather than waiting until the
1737 first execute.
1738 pg_expand_array (boolean)
1740 DBD::Pg specific attribute. Defaults to true. If false, arrays returned
1742 from the server will not be changed into a Perl arrayref, but remain as
1743 a string.
1744 pg_async_status (integer, read-only)
1746 DBD::Pg specific attribute. Returns the current status of an
1748 asynchronous command. 0 indicates no asynchronous command is in
1749 progress, 1 indicates that an asynchronous command has started and -1
1750 indicated that an asynchronous command has been cancelled.
1751 pg_standard_conforming_strings (boolean, read-only)
1753 DBD::Pg specific attribute. Returns true if the server is currently
1755 using standard conforming strings. Only available if the target server
1756 is version 8.2 or better.
1757 pg_INV_READ (integer, read-only)
1759 Constant to be used for the mode in "pg_lo_creat" and "pg_lo_open".
1761 pg_INV_WRITE (integer, read-only)
1763 Constant to be used for the mode in "pg_lo_creat" and "pg_lo_open".
1765 Driver (handle, read-only)
1767 Holds the handle of the parent driver. The only recommended use for
1769 this is to find the name of the driver using:
1770 $dbh->{Driver}->{Name}
1772 pg_protocol (integer, read-only)
1774 DBD::Pg specific attribute. Returns the version of the PostgreSQL
1776 server. If DBD::Pg is unable to figure out the version, it will return
1777 a "0". Otherwise, a "3" is returned.
1778 RowCacheSize
1780 Not used by DBD::Pg
1782
1784 Statement Handle Methods
1785 bind_param
1786 $rv = $sth->bind_param($param_num, $bind_value);
1788 $rv = $sth->bind_param($param_num, $bind_value, $bind_type);
1789 $rv = $sth->bind_param($param_num, $bind_value, \%attr);
1790 Allows the user to bind a value and/or a data type to a placeholder.
1792 This is especially important when using server-side prepares. See the
1793 "prepare" method for more information.
1794 The value of $param_num is a number if using the '?' or '$1' style
1796 placeholders. If using ":foo" style placeholders, the complete name
1797 (e.g. ":foo") must be given. For numeric values, you can either use a
1798 number or use a literal '$1'. See the examples below.
1799 The $bind_value argument is fairly self-explanatory. A value of "undef"
1801 will bind a "NULL" to the placeholder. Using "undef" is useful when you
1802 want to change just the type and will be overwriting the value later.
1803 (Any value is actually usable, but "undef" is easy and efficient).
1804 The "\%attr" hash is used to indicate the data type of the placeholder.
1806 The default value is "varchar". If you need something else, you must
1807 use one of the values provided by DBI or by DBD::Pg. To use a SQL
1808 value, modify your "use DBI" statement at the top of your script as
1809 follows:
1810 use DBI qw(:sql_types);
1812 This will import some constants into your script. You can plug those
1814 directly into the "bind_param" call. Some common ones that you will
1815 encounter are:
1816 SQL_INTEGER
1818 To use PostgreSQL data types, import the list of values like this:
1820 use DBD::Pg qw(:pg_types);
1822 You can then set the data types by setting the value of the "pg_type"
1824 key in the hash passed to "bind_param". The current list of Postgres
1825 data types exported is:
1826 PG_ACLITEM PG_ACLITEMARRAY PG_ANY PG_ANYARRAY PG_ANYCOMPATIBLE PG_ANYCOMPATIBLEARRAY
1828 PG_ANYCOMPATIBLEMULTIRANGE PG_ANYCOMPATIBLENONARRAY PG_ANYCOMPATIBLERANGE PG_ANYELEMENT PG_ANYENUM PG_ANYMULTIRANGE
1829 PG_ANYNONARRAY PG_ANYRANGE PG_BIT PG_BITARRAY PG_BOOL PG_BOOLARRAY
1830 PG_BOX PG_BOXARRAY PG_BPCHAR PG_BPCHARARRAY PG_BYTEA PG_BYTEAARRAY
1831 PG_CHAR PG_CHARARRAY PG_CID PG_CIDARRAY PG_CIDR PG_CIDRARRAY
1832 PG_CIRCLE PG_CIRCLEARRAY PG_CSTRING PG_CSTRINGARRAY PG_DATE PG_DATEARRAY
1833 PG_DATEMULTIRANGE PG_DATEMULTIRANGEARRAY PG_DATERANGE PG_DATERANGEARRAY PG_EVENT_TRIGGER PG_FDW_HANDLER
1834 PG_FLOAT4 PG_FLOAT4ARRAY PG_FLOAT8 PG_FLOAT8ARRAY PG_GTSVECTOR PG_GTSVECTORARRAY
1835 PG_INDEX_AM_HANDLER PG_INET PG_INETARRAY PG_INT2 PG_INT2ARRAY PG_INT2VECTOR
1836 PG_INT2VECTORARRAY PG_INT4 PG_INT4ARRAY PG_INT4MULTIRANGE PG_INT4MULTIRANGEARRAY PG_INT4RANGE
1837 PG_INT4RANGEARRAY PG_INT8 PG_INT8ARRAY PG_INT8MULTIRANGE PG_INT8MULTIRANGEARRAY PG_INT8RANGE
1838 PG_INT8RANGEARRAY PG_INTERNAL PG_INTERVAL PG_INTERVALARRAY PG_JSON PG_JSONARRAY
1839 PG_JSONB PG_JSONBARRAY PG_JSONPATH PG_JSONPATHARRAY PG_LANGUAGE_HANDLER PG_LINE
1840 PG_LINEARRAY PG_LSEG PG_LSEGARRAY PG_MACADDR PG_MACADDR8 PG_MACADDR8ARRAY
1841 PG_MACADDRARRAY PG_MONEY PG_MONEYARRAY PG_NAME PG_NAMEARRAY PG_NUMERIC
1842 PG_NUMERICARRAY PG_NUMMULTIRANGE PG_NUMMULTIRANGEARRAY PG_NUMRANGE PG_NUMRANGEARRAY PG_OID
1843 PG_OIDARRAY PG_OIDVECTOR PG_OIDVECTORARRAY PG_PATH PG_PATHARRAY PG_PG_ATTRIBUTE
1844 PG_PG_ATTRIBUTEARRAY PG_PG_BRIN_BLOOM_SUMMARY PG_PG_BRIN_MINMAX_MULTI_SUMMARY PG_PG_CLASS PG_PG_CLASSARRAY PG_PG_DDL_COMMAND
1845 PG_PG_DEPENDENCIES PG_PG_LSN PG_PG_LSNARRAY PG_PG_MCV_LIST PG_PG_NDISTINCT PG_PG_NODE_TREE
1846 PG_PG_PROC PG_PG_PROCARRAY PG_PG_SNAPSHOT PG_PG_SNAPSHOTARRAY PG_PG_TYPE PG_PG_TYPEARRAY
1847 PG_POINT PG_POINTARRAY PG_POLYGON PG_POLYGONARRAY PG_RECORD PG_RECORDARRAY
1848 PG_REFCURSOR PG_REFCURSORARRAY PG_REGCLASS PG_REGCLASSARRAY PG_REGCOLLATION PG_REGCOLLATIONARRAY
1849 PG_REGCONFIG PG_REGCONFIGARRAY PG_REGDICTIONARY PG_REGDICTIONARYARRAY PG_REGNAMESPACE PG_REGNAMESPACEARRAY
1850 PG_REGOPER PG_REGOPERARRAY PG_REGOPERATOR PG_REGOPERATORARRAY PG_REGPROC PG_REGPROCARRAY
1851 PG_REGPROCEDURE PG_REGPROCEDUREARRAY PG_REGROLE PG_REGROLEARRAY PG_REGTYPE PG_REGTYPEARRAY
1852 PG_TABLE_AM_HANDLER PG_TEXT PG_TEXTARRAY PG_TID PG_TIDARRAY PG_TIME
1853 PG_TIMEARRAY PG_TIMESTAMP PG_TIMESTAMPARRAY PG_TIMESTAMPTZ PG_TIMESTAMPTZARRAY PG_TIMETZ
1854 PG_TIMETZARRAY PG_TRIGGER PG_TSMULTIRANGE PG_TSMULTIRANGEARRAY PG_TSM_HANDLER PG_TSQUERY
1855 PG_TSQUERYARRAY PG_TSRANGE PG_TSRANGEARRAY PG_TSTZMULTIRANGE PG_TSTZMULTIRANGEARRAY PG_TSTZRANGE
1856 PG_TSTZRANGEARRAY PG_TSVECTOR PG_TSVECTORARRAY PG_TXID_SNAPSHOT PG_TXID_SNAPSHOTARRAY PG_UNKNOWN
1857 PG_UUID PG_UUIDARRAY PG_VARBIT PG_VARBITARRAY PG_VARCHAR PG_VARCHARARRAY
1858 PG_VOID PG_XID PG_XID8 PG_XID8ARRAY PG_XIDARRAY PG_XML
1859 PG_XMLARRAY
1860 Data types are "sticky," in that once a data type is set to a certain
1862 placeholder, it will remain for that placeholder, unless it is
1863 explicitly set to something else afterwards. If the statement has
1864 already been prepared, and you switch the data type to something else,
1865 DBD::Pg will re-prepare the statement for you before doing the next
1866 execute.
1867 Examples:
1869 use DBI qw(:sql_types);
1871 use DBD::Pg qw(:pg_types);
1872 $SQL = "SELECT id FROM ptable WHERE size > ? AND title = ?";
1874 $sth = $dbh->prepare($SQL);
1875 ## Both arguments below are bound to placeholders as "varchar"
1877 $sth->execute(123, "Merk");
1878 ## Reset the datatype for the first placeholder to an integer
1880 $sth->bind_param(1, undef, SQL_INTEGER);
1881 ## The "undef" bound above is not used, since we supply params to execute
1883 $sth->execute(123, "Merk");
1884 ## Set the first placeholder's value and data type
1886 $sth->bind_param(1, 234, { pg_type => PG_TIMESTAMP });
1887 ## Set the second placeholder's value and data type.
1889 ## We don't send a third argument, so the default "varchar" is used
1890 $sth->bind_param('$2', "Zool");
1891 ## We realize that the wrong data type was set above, so we change it:
1893 $sth->bind_param('$1', 234, { pg_type => SQL_INTEGER });
1894 ## We also got the wrong value, so we change that as well.
1896 ## Because the data type is sticky, we don't need to change it
1897 $sth->bind_param(1, 567);
1898 ## This executes the statement with 567 (integer) and "Zool" (varchar)
1900 $sth->execute();
1901 bind_param_inout
1903 $rv = $sth->bind_param_inout($param_num, \$scalar, 0);
1905 Experimental support for this feature is provided. The first argument
1907 to bind_param_inout should be a placeholder number. The second argument
1908 should be a reference to a scalar variable in your script. The third
1909 argument is not used and should simply be set to 0. Note that what this
1910 really does is assign a returned column to the variable, in the order
1911 in which the column appears. For example:
1912 my $foo = 123;
1914 $sth = $dbh->prepare("SELECT 1+?::int");
1915 $sth->bind_param_inout(1, \$foo, 0);
1916 $foo = 222;
1917 $sth->execute(444);
1918 $sth->fetch;
1919 The above will cause $foo to have a new value of "223" after the final
1921 fetch. Note that the variables bound in this manner are very sticky,
1922 and will trump any values passed in to execute. This is because the
1923 binding is done as late as possible, at the execute() stage, allowing
1924 the value to be changed between the time it was bound and the time the
1925 query is executed. Thus, the above execute is the same as:
1926 $sth->execute();
1928 bind_param_array
1930 $rv = $sth->bind_param_array($param_num, $array_ref_or_value)
1932 $rv = $sth->bind_param_array($param_num, $array_ref_or_value, $bind_type)
1933 $rv = $sth->bind_param_array($param_num, $array_ref_or_value, \%attr)
1934 Binds an array of values to a placeholder, so that each is used in turn
1936 by a call to the "execute_array" method.
1937 execute
1939 $rv = $sth->execute(@bind_values);
1941 Executes a previously prepared statement. In addition to "UPDATE",
1943 "DELETE", "INSERT" statements, for which it returns always the number
1944 of affected rows, the "execute" method can also be used for "SELECT ...
1945 INTO table" statements.
1946 The "prepare/bind/execute" process has changed significantly for
1948 PostgreSQL servers 7.4 and later: please see the prepare() and
1949 bind_param() entries for much more information.
1950 Setting one of the bind_values to "undef" is the equivalent of setting
1952 the value to NULL in the database. Setting the bind_value to
1953 $DBDPG_DEFAULT is equivalent to sending the literal string 'DEFAULT' to
1954 the backend. Note that using this option will force server-side
1955 prepares off until such time as PostgreSQL supports using DEFAULT in
1956 prepared statements.
1957 DBD::Pg also supports passing in arrays to execute: simply pass in an
1959 arrayref, and DBD::Pg will flatten it into a string suitable for input
1960 on the backend.
1961 If you are using Postgres version 8.2 or greater, you can also use any
1963 of the fetch methods to retrieve the values of a "RETURNING" clause
1964 after you execute an "UPDATE", "DELETE", or "INSERT". For example:
1965 $dbh->do(q{CREATE TABLE abc (id SERIAL, country TEXT)});
1967 $SQL = q{INSERT INTO abc (country) VALUES (?) RETURNING id};
1968 $sth = $dbh->prepare($SQL);
1969 $sth->execute('France');
1970 $countryid = $sth->fetch()->[0];
1971 $sth->execute('New Zealand');
1972 $countryid = $sth->fetch()->[0];
1973 execute_array
1975 $tuples = $sth->execute_array() or die $sth->errstr;
1977 $tuples = $sth->execute_array(\%attr) or die $sth->errstr;
1978 $tuples = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
1979 ($tuples, $rows) = $sth->execute_array(\%attr) or die $sth->errstr;
1981 ($tuples, $rows) = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
1982 Execute a prepared statement once for each item in a passed-in hashref,
1984 or items that were previously bound via the "bind_param_array" method.
1985 See the DBI documentation for more details.
1986 execute_for_fetch
1988 $tuples = $sth->execute_for_fetch($fetch_tuple_sub);
1990 $tuples = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
1991 ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub);
1993 ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
1994 Used internally by the "execute_array" method, and rarely used
1996 directly. See the DBI documentation for more details.
1997 fetchrow_arrayref
1999 $ary_ref = $sth->fetchrow_arrayref;
2001 Fetches the next row of data from the statement handle, and returns a
2003 reference to an array holding the column values. Any columns that are
2004 NULL are returned as undef within the array.
2005 If there are no more rows or if an error occurs, then this method
2007 return undef. You should check "$sth->err" afterwards (or use the
2008 RaiseError attribute) to discover if the undef returned was due to an
2009 error.
2010 Note that the same array reference is returned for each fetch, so don't
2012 store the reference and then use it after a later fetch. Also, the
2013 elements of the array are also reused for each row, so take care if you
2014 want to take a reference to an element. See also "bind_columns".
2015 fetchrow_array
2017 @ary = $sth->fetchrow_array;
2019 Similar to the "fetchrow_arrayref" method, but returns a list of column
2021 information rather than a reference to a list. Do not use this in a
2022 scalar context.
2023 fetchrow_hashref
2025 $hash_ref = $sth->fetchrow_hashref;
2027 $hash_ref = $sth->fetchrow_hashref($name);
2028 Fetches the next row of data and returns a hashref containing the name
2030 of the columns as the keys and the data itself as the values. Any NULL
2031 value is returned as an undef value.
2032 If there are no more rows or if an error occurs, then this method
2034 return undef. You should check "$sth->err" afterwards (or use the
2035 RaiseError attribute) to discover if the undef returned was due to an
2036 error.
2037 The optional $name argument should be either "NAME", "NAME_lc" or
2039 "NAME_uc", and indicates what sort of transformation to make to the
2040 keys in the hash.
2041 fetchall_arrayref
2043 $tbl_ary_ref = $sth->fetchall_arrayref();
2045 $tbl_ary_ref = $sth->fetchall_arrayref( $slice );
2046 $tbl_ary_ref = $sth->fetchall_arrayref( $slice, $max_rows );
2047 Returns a reference to an array of arrays that contains all the
2049 remaining rows to be fetched from the statement handle. If there are no
2050 more rows, an empty arrayref will be returned. If an error occurs, the
2051 data read in so far will be returned. Because of this, you should
2052 always check "$sth->err" after calling this method, unless RaiseError
2053 has been enabled.
2054 If $slice is an array reference, fetchall_arrayref uses the
2056 "fetchrow_arrayref" method to fetch each row as an array ref. If the
2057 $slice array is not empty then it is used as a slice to select
2058 individual columns by perl array index number (starting at 0, unlike
2059 column and parameter numbers which start at 1).
2060 With no parameters, or if $slice is undefined, fetchall_arrayref acts
2062 as if passed an empty array ref.
2063 If $slice is a hash reference, fetchall_arrayref uses
2065 "fetchrow_hashref" to fetch each row as a hash reference.
2066 See the DBI documentation for a complete discussion.
2068 fetchall_hashref
2070 $hash_ref = $sth->fetchall_hashref( $key_field );
2072 Returns a hashref containing all rows to be fetched from the statement
2074 handle. See the DBI documentation for a full discussion.
2075 finish
2077 $rv = $sth->finish;
2079 Indicates to DBI that you are finished with the statement handle and
2081 are not going to use it again. Only needed when you have not fetched
2082 all the possible rows.
2083 rows
2085 $rv = $sth->rows;
2087 Returns the number of rows returned by the last query. In contrast to
2089 many other DBD modules, the number of rows is available immediately
2090 after calling "$sth->execute". Note that the "execute" method itself
2091 returns the number of rows itself, which means that this method is
2092 rarely needed.
2093 bind_col
2095 $rv = $sth->bind_col($column_number, \$var_to_bind);
2097 $rv = $sth->bind_col($column_number, \$var_to_bind, \%attr );
2098 $rv = $sth->bind_col($column_number, \$var_to_bind, $bind_type );
2099 Binds a Perl variable and/or some attributes to an output column of a
2101 SELECT statement. Column numbers count up from 1. You do not need to
2102 bind output columns in order to fetch data.
2103 See the DBI documentation for a discussion of the optional parameters
2105 "\%attr" and $bind_type
2106 bind_columns
2108 $rv = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
2110 Calls the "bind_col" method for each column in the SELECT statement,
2112 using the supplied list.
2113 dump_results
2115 $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
2117 Fetches all the rows from the statement handle, calls "DBI::neat_list"
2119 for each row, and prints the results to $fh (which defaults to STDOUT).
2120 Rows are separated by $lsep (which defaults to a newline). Columns are
2121 separated by $fsep (which defaults to a comma). The $maxlen controls
2122 how wide the output can be, and defaults to 35.
2123 This method is designed as a handy utility for prototyping and testing
2125 queries. Since it uses "neat_list" to format and edit the string for
2126 reading by humans, it is not recommended for data transfer
2127 applications.
2128 blob_read
2130 $blob = $sth->blob_read($id, $offset, $len);
2132 Supported by DBD::Pg. This method is implemented by DBI but not
2134 currently documented by DBI, so this method might change.
2135 This method seems to be heavily influenced by the current
2137 implementation of blobs in Oracle. Nevertheless we try to be as
2138 compatible as possible. Whereas Oracle suffers from the limitation that
2139 blobs are related to tables and every table can have only one blob
2140 (datatype LONG), PostgreSQL handles its blobs independent of any table
2141 by using so-called object identifiers. This explains why the
2142 "blob_read" method is blessed into the STATEMENT package and not part
2143 of the DATABASE package. Here the field parameter has been used to
2144 handle this object identifier. The offset and len parameters may be set
2145 to zero, in which case the whole blob is fetched at once.
2146 See also the PostgreSQL-specific functions concerning blobs, which are
2148 available via the "func" interface.
2149 For further information and examples about blobs, please read the
2151 chapter about Large Objects in the PostgreSQL Programmer's Guide at
2152 <http://www.postgresql.org/docs/current/static/largeobjects.html>.
2153 pg_canonical_ids
2155 $data = $sth->pg_canonical_ids;
2157 DBD::Pg specific method. It returns Oid of table and position in table
2159 for every column in result set.
2160 Returns array of arrays with Table Oid and Column Position for every
2162 column in result set or undef if current column is not a simple
2163 reference.
2164 pg_canonical_names
2166 $data = $sth->pg_canonical_names;
2168 DBD::Pg specific method. It returns array of original (or canonical)
2170 names (from where this data is actually came from) of columns in
2171 Schema.Table.Column format or undef if current column is not a simple
2172 reference.
2173 Note that this method is quite slow because it need additional
2175 information from server for every column that is simple reference.
2176 Consider to use "pg_canonical_ids" instead.
2177 last_insert_id
2179 $rv = $sth->last_insert_id(undef, $schema, $table, undef);
2181 $rv = $sth->last_insert_id(undef, $schema, $table, undef, {sequence => $seqname});
2182 This is simply an alternative way to return the same information as
2184 "$dbh->last_insert_id".
2185 Statement Handle Attributes
2187 NUM_OF_FIELDS (integer, read-only)
2188 Returns the number of columns returned by the current statement. A
2190 number will only be returned for SELECT statements, for SHOW statements
2191 (which always return 1), and for INSERT, UPDATE, and DELETE statements
2192 which contain a RETURNING clause. This method returns undef if called
2193 before execute().
2194 NUM_OF_PARAMS (integer, read-only)
2196 Returns the number of placeholders in the current statement.
2198 NAME (arrayref, read-only)
2200 Returns an arrayref of column names for the current statement. This
2202 method will only work for SELECT statements, for SHOW statements, and
2203 for INSERT, UPDATE, and DELETE statements which contain a RETURNING
2204 clause. This method returns undef if called before execute().
2205 NAME_lc (arrayref, read-only)
2207 The same as the "NAME" attribute, except that all column names are
2209 forced to lower case.
2210 NAME_uc (arrayref, read-only)
2212 The same as the "NAME" attribute, except that all column names are
2214 forced to upper case.
2215 NAME_hash (hashref, read-only)
2217 Similar to the "NAME" attribute, but returns a hashref of column names
2219 instead of an arrayref. The names of the columns are the keys of the
2220 hash, and the values represent the order in which the columns are
2221 returned, starting at 0. This method returns undef if called before
2222 execute().
2223 NAME_lc_hash (hashref, read-only)
2225 The same as the "NAME_hash" attribute, except that all column names are
2227 forced to lower case.
2228 NAME_uc_hash (hashref, read-only)
2230 The same as the "NAME_hash" attribute, except that all column names are
2232 forced to lower case.
2233 TYPE (arrayref, read-only)
2235 Returns an arrayref indicating the data type for each column in the
2237 statement. This method returns undef if called before execute().
2238 PRECISION (arrayref, read-only)
2240 Returns an arrayref of integer values for each column returned by the
2242 statement. The number indicates the precision for "NUMERIC" columns,
2243 the size in number of characters for "CHAR" and "VARCHAR" columns, and
2244 for all other types of columns it returns the number of bytes. This
2245 method returns undef if called before execute().
2246 SCALE (arrayref, read-only)
2248 Returns an arrayref of integer values for each column returned by the
2250 statement. The number indicates the scale of the that column. The only
2251 type that will return a value is "NUMERIC". This method returns undef
2252 if called before execute().
2253 NULLABLE (arrayref, read-only)
2255 Returns an arrayref of integer values for each column returned by the
2257 statement. The number indicates if the column is nullable or not. 0 =
2258 not nullable, 1 = nullable, 2 = unknown. This method returns undef if
2259 called before execute().
2260 Database (dbh, read-only)
2262 Returns the database handle this statement handle was created from.
2264 ParamValues (hash ref, read-only)
2266 Returns a reference to a hash containing the values currently bound to
2268 placeholders. If the "named parameters" type of placeholders are being
2269 used (such as ":foo"), then the keys of the hash will be the names of
2270 the placeholders (without the colon). If the "dollar sign numbers" type
2271 of placeholders are being used, the keys of the hash will be the
2272 numbers, without the dollar signs. If the "question mark" type is used,
2273 integer numbers will be returned, starting at one and increasing for
2274 every placeholder.
2275 If this method is called before "execute", the literal values passed in
2277 are returned. If called after "execute", then the quoted versions of
2278 the values are returned.
2279 ParamTypes (hash ref, read-only)
2281 Returns a reference to a hash containing the type names currently bound
2283 to placeholders. The keys are the same as returned by the ParamValues
2284 method. The values are hashrefs containing a single key value pair, in
2285 which the key is either 'TYPE' if the type has a generic SQL
2286 equivalent, and 'pg_type' if the type can only be expressed by a
2287 Postgres type. The value is the internal number corresponding to the
2288 type originally passed in. (Placeholders that have not yet been bound
2289 will return undef as the value). This allows the output of ParamTypes
2290 to be passed back to the "bind_param" method.
2291 Statement (string, read-only)
2293 Returns the statement string passed to the most recent "prepare" method
2295 called in this database handle, even if that method failed. This is
2296 especially useful where "RaiseError" is enabled and the exception
2297 handler checks $@ and sees that a "prepare" method call failed.
2298 pg_current_row (integer, read-only)
2300 DBD::Pg specific attribute. Returns the number of the tuple (row) that
2302 was most recently fetched. Returns zero before and after fetching is
2303 performed.
2304 pg_numbound (integer, read-only)
2306 DBD::Pg specific attribute. Returns the number of placeholders that are
2308 currently bound (via bind_param).
2309 pg_bound (hashref, read-only)
2311 DBD::Pg specific attribute. Returns a hash of all named placeholders.
2313 The key is the name of the placeholder, and the value is a 0 or a 1,
2314 indicating if the placeholder has been bound yet (e.g. via bind_param)
2315 pg_size (arrayref, read-only)
2317 DBD::Pg specific attribute. It returns a reference to an array of
2319 integer values for each column. The integer shows the size of the
2320 column in bytes. Variable length columns are indicated by -1.
2321 pg_type (arrayref, read-only)
2323 DBD::Pg specific attribute. It returns a reference to an array of
2325 strings for each column. The string shows the name of the data_type.
2326 pg_segments (arrayref, read-only)
2328 DBD::Pg specific attribute. Returns an arrayref of the query split on
2330 the placeholders.
2331 pg_oid_status (integer, read-only)
2333 DBD::Pg specific attribute. It returns the OID of the last INSERT
2335 command.
2336 pg_cmd_status (integer, read-only)
2338 DBD::Pg specific attribute. It returns the type of the last command.
2340 Possible types are: "INSERT", "DELETE", "UPDATE", "SELECT".
2341 pg_direct (boolean)
2343 DBD::Pg specific attribute. Default is false. If true, the query is
2345 passed directly to the backend without parsing for placeholders.
2346 pg_prepare_now (boolean)
2348 DBD::Pg specific attribute. Default is off. If true, the query will be
2350 immediately prepared, rather than waiting for the "execute" call.
2351 pg_prepare_name (string)
2353 DBD::Pg specific attribute. Specifies the name of the prepared
2355 statement to use for this statement handle. Not normally needed, see
2356 the section on the "prepare" method for more information.
2357 pg_server_prepare (boolean)
2359 DBD::Pg specific attribute. Indicates if DBD::Pg should attempt to use
2361 server-side prepared statements for this statement handle. The default
2362 value, true, indicates that prepared statements should be used whenever
2363 possible. See the section on the "prepare" method for more information.
2364 pg_switch_prepared (integer)
2366 DBD::Pg specific attribute. Indicates when DBD::Pg will internally
2368 switch from using PQexecParams to PQexecPrepared. In other words, when
2369 it will start using server-side prepared statements (assuming all other
2370 requirements for them are met). The default value, 2, means that a
2371 prepared statement will be prepared and used the second and subsequent
2372 time execute is called. To always use PQexecPrepared instead of
2373 PQexecParams, set pg_switch_prepared to 1 (this was the default
2374 behavior in earlier versions). Setting pg_switch_prepared to 0 will
2375 force DBD::Pg to always use PQexecParams.
2376 pg_placeholder_dollaronly (boolean)
2378 DBD::Pg specific attribute. Defaults to false. When true, question
2380 marks inside of the query being prepared are not treated as
2381 placeholders. Useful for statements that contain unquoted question
2382 marks, such as geometric operators. Note that you may also simply
2383 escape question marks with a backslash to prevent them from being
2384 treated as placeholders.
2385 pg_placeholder_nocolons (boolean)
2387 DBD::Pg specific attribute. Defaults to false. When true, colons inside
2389 of statements are not treated as placeholders. Useful for statements
2390 that contain an array slice. You may also place a backslash directly
2391 before the colon to prevent it from being treated as a placeholder.
2392 pg_async (integer)
2394 DBD::Pg specific attribute. Indicates the current behavior for
2396 asynchronous queries. See the section on "Asynchronous Constants" for
2397 more information.
2398 pg_async_status (integer, read-only)
2400 DBD::Pg specific attribute. Returns the current status of an
2402 asynchronous command. 0 indicates no asynchronous command is in
2403 progress, 1 indicates that an asynchronous command has started and -1
2404 indicated that an asynchronous command has been cancelled.
2405 RowsInCache
2407 Not used by DBD::Pg
2409 RowCache
2411 Not used by DBD::Pg
2413 CursorName
2415 Not used by DBD::Pg. See the note about "Cursors" elsewhere in this
2417 document.
2418
2420 Encoding
2421 DBD::Pg has extensive support for a client_encoding of UTF-8, and most
2422 things like encoding and decoding should happen automatically. If you
2423 are using a different encoding, you will need do the encoding and
2424 decoding yourself. For this reason, it is highly recommended to always
2425 use a client_encoding of UTF-8. The server_encoding can be anything,
2426 and no recommendations are made there, other than avoid SQL_ASCII
2427 whenever possible.
2428 Transactions
2430 Transaction behavior is controlled via the "AutoCommit" attribute. For
2431 a complete definition of "AutoCommit" please refer to the DBI
2432 documentation.
2433 According to the DBI specification the default for "AutoCommit" is a
2435 true value. In this mode, any change to the database becomes valid
2436 immediately. Any "BEGIN", "COMMIT" or "ROLLBACK" statements will be
2437 rejected. Note that preparing a statement does not always contact the
2438 server, as the actual "PREPARE" is usually postponed until the first
2439 call to "execute".
2440 Savepoints
2442 PostgreSQL version 8.0 introduced the concept of savepoints, which
2443 allows transactions to be rolled back to a certain point without
2444 affecting the rest of the transaction. DBD::Pg encourages using the
2445 following methods to control savepoints:
2446 "pg_savepoint"
2448 Creates a savepoint. This will fail unless you are inside of a
2450 transaction. The only argument is the name of the savepoint. Note that
2451 PostgreSQL DOES allow multiple savepoints with the same name to exist.
2452 $dbh->pg_savepoint("mysavepoint");
2454 "pg_rollback_to"
2456 Rolls the database back to a named savepoint, discarding any work
2458 performed after that point. If more than one savepoint with that name
2459 exists, rolls back to the most recently created one.
2460 $dbh->pg_rollback_to("mysavepoint");
2462 "pg_release"
2464 Releases (or removes) a named savepoint. If more than one savepoint
2466 with that name exists, it will only destroy the most recently created
2467 one. Note that all savepoints created after the one being released are
2468 also destroyed.
2469 $dbh->pg_release("mysavepoint");
2471 Asynchronous Queries
2473 It is possible to send a query to the backend and have your script do
2474 other work while the query is running on the backend. Both queries sent
2475 by the "do" method, and by the "execute" method can be sent
2476 asynchronously. The basic usage is as follows:
2477 print "Async do() example:\n";
2479 $dbh->do("SELECT long_running_query()", {pg_async => PG_ASYNC});
2480 do_something_else();
2481 {
2482 if ($dbh->pg_ready()) {
2483 $res = $dbh->pg_result();
2484 print "Result of do(): $res\n";
2485 }
2486 print "Query is still running...\n";
2487 if (cancel_request_received) {
2488 $dbh->pg_cancel();
2489 }
2490 sleep 1;
2491 redo;
2492 }
2493 print "Async prepare/execute example:\n";
2495 $sth = $dbh->prepare("SELECT long_running_query(1)", {pg_async => PG_ASYNC});
2496 $sth->execute();
2497 ## Changed our mind, cancel and run again:
2499 $sth = $dbh->prepare("SELECT 678", {pg_async => PG_ASYNC + PG_OLDQUERY_CANCEL});
2500 $sth->execute();
2501 do_something_else();
2503 if (!$sth->pg_ready) {
2505 do_another_thing();
2506 }
2507 ## We wait until it is done, and get the result:
2509 $res = $dbh->pg_result();
2510 Asynchronous Constants
2512 There are currently three asynchronous constants automatically exported
2514 by DBD::Pg.
2515 PG_ASYNC
2517 This is a constant for the number 1. It is passed to either the
2518 "do" or the "prepare" method as a value to the pg_async key and
2519 indicates that the query should be sent asynchronously.
2520 PG_OLDQUERY_CANCEL
2522 This is a constant for the number 2. When passed to either the "do"
2523 or the "prepare" method, it causes any currently running
2524 asynchronous query to be cancelled and rolled back. It has no
2525 effect if no asynchronous query is currently running.
2526 PG_OLDQUERY_WAIT
2528 This is a constant for the number 4. When passed to either the "do"
2529 or the "prepare" method, it waits for any currently running
2530 asynchronous query to complete. It has no effect if there is no
2531 asynchronous query currently running.
2532 Asynchronous Methods
2534 pg_cancel
2536 This database-level method attempts to cancel any currently running
2537 asynchronous query. It returns true if the cancel succeeded, and
2538 false otherwise. Note that a query that has finished before this
2539 method is executed will also return false. WARNING: a successful
2540 cancellation may leave the database in an unusable state, so you
2541 may need to ROLLBACK or ROLLBACK TO a savepoint. As of version
2542 2.17.0 of DBD::Pg, rollbacks are not done automatically.
2543 $result = $dbh->pg_cancel();
2545 pg_ready
2547 This method can be called as a database handle method or (for
2548 convenience) as a statement handle method. Both simply see if a
2549 previously issued asynchronous query has completed yet. It returns
2550 true if the statement has finished, in which case you should then
2551 call the "pg_result" method. Calls to pg_ready() should only be
2552 used when you have other things to do while the query is running.
2553 If you simply want to wait until the query is done, do not call
2554 pg_ready() over and over, but simply call the pg_result() method.
2555 my $time = 0;
2557 while (!$dbh->pg_ready) {
2558 print "Query is still running. Seconds: $time\n";
2559 $time++;
2560 sleep 1;
2561 }
2562 $result = $dbh->pg_result;
2563 pg_result
2565 This database handle method returns the results of a previously
2566 issued asynchronous query. If the query is still running, this
2567 method will wait until it has finished. The result returned is the
2568 number of rows: the same thing that would have been returned by the
2569 asynchronous "do" or "execute" if it had been called without an
2570 asynchronous flag.
2571 $result = $dbh->pg_result;
2573 Asynchronous Examples
2575 Here are some working examples of asynchronous queries. Note that we'll
2577 use the pg_sleep function to emulate a long-running query.
2578 use strict;
2580 use warnings;
2581 use Time::HiRes 'sleep';
2582 use DBD::Pg ':async';
2583 my $dbh = DBI->connect('dbi:Pg:dbname=postgres', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
2585 ## Kick off a long running query on the first database:
2587 my $sth = $dbh->prepare("SELECT pg_sleep(?)", {pg_async => PG_ASYNC});
2588 $sth->execute(5);
2589 ## While that is running, do some other things
2591 print "Your query is processing. Thanks for waiting\n";
2592 check_on_the_kids(); ## Expensive sub, takes at least three seconds.
2593 while (!$dbh->pg_ready) {
2595 check_on_the_kids();
2596 ## If the above function returns quickly for some reason, we add a small sleep
2597 sleep 0.1;
2598 }
2599 print "The query has finished. Gathering results\n";
2601 my $result = $sth->pg_result;
2602 print "Result: $result\n";
2603 my $info = $sth->fetchall_arrayref();
2604 Without asynchronous queries, the above script would take about 8
2606 seconds to run: five seconds waiting for the execute to finish, then
2607 three for the check_on_the_kids() function to return. With asynchronous
2608 queries, the script takes about 6 seconds to run, and gets in two
2609 iterations of check_on_the_kids in the process.
2610 Here's an example showing the ability to cancel a long-running query.
2612 Imagine two replica databases in different geographic locations over a
2613 slow network. You need information as quickly as possible, so you query
2614 both at once. When you get an answer, you tell the other one to stop
2615 working on your query, as you don't need it anymore.
2616 use strict;
2618 use warnings;
2619 use Time::HiRes 'sleep';
2620 use DBD::Pg ':async';
2621 my $dbhrep1 = DBI->connect('dbi:Pg:dbname=postgres;host=replica1', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
2623 my $dbhrep2 = DBI->connect('dbi:Pg:dbname=postgres;host=replica2', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
2624 $SQL = "SELECT count(*) FROM largetable WHERE flavor='blueberry'";
2626 my $sth1 = $dbhrep1->prepare($SQL, {pg_async => PG_ASYNC});
2628 my $sth2 = $dbhrep2->prepare($SQL, {pg_async => PG_ASYNC});
2629 $sth1->execute();
2631 $sth2->execute();
2632 my $winner;
2634 while (!defined $winner) {
2635 if ($sth1->pg_ready) {
2636 $winner = 1;
2637 }
2638 elsif ($sth2->pg_ready) {
2639 $winner = 2;
2640 }
2641 Time::HiRes::sleep 0.05;
2642 }
2643 my $count;
2645 if ($winner == 1) {
2646 $sth2->pg_cancel();
2647 $sth1->pg_result();
2648 $count = $sth1->fetchall_arrayref()->[0][0];
2649 }
2650 else {
2651 $sth1->pg_cancel();
2652 $sth2->pg_result();
2653 $count = $sth2->fetchall_arrayref()->[0][0];
2654 }
2655 Array support
2657 DBD::Pg allows arrays (as arrayrefs) to be passed in to both the
2658 "quote" and the "execute" methods. In both cases, the array is
2659 flattened into a string representing a Postgres array.
2660 When fetching rows from a table that contains a column with an array
2662 type, the result will be passed back to your script as an arrayref.
2663 To turn off the automatic parsing of returned arrays into arrayrefs,
2665 you can set the attribute pg_expand_array, which is true by default.
2666 $dbh->{pg_expand_array} = 0;
2668 COPY support
2670 DBD::Pg allows for quick (bulk) reading and storing of data by using
2671 the COPY command. The basic process is to use "$dbh->do" to issue a
2672 COPY command, and then to either add rows using "pg_putcopydata", or to
2673 read them by using "pg_getcopydata".
2674 The first step is to put the server into "COPY" mode. This is done by
2676 sending a complete COPY command to the server, by using the "do"
2677 method. For example:
2678 $dbh->do("COPY foobar FROM STDIN");
2680 This would tell the server to enter a COPY IN mode (yes, that's
2682 confusing, but the mode is COPY IN because of the command COPY FROM).
2683 It is now ready to receive information via the "pg_putcopydata" method.
2684 The complete syntax of the COPY command is more complex and not
2685 documented here: the canonical PostgreSQL documentation for COPY can be
2686 found at:
2687 http://www.postgresql.org/docs/current/static/sql-copy.html
2689 Once a COPY command has been issued, no other SQL commands are allowed
2691 until "pg_putcopyend" has been issued (for COPY FROM), or the final
2692 "pg_getcopydata" has been called (for COPY TO).
2693 Note: All other COPY methods (pg_putline, pg_getline, etc.) are now
2695 heavily deprecated in favor of the pg_getcopydata, pg_putcopydata, and
2696 pg_putcopyend methods.
2697 pg_getcopydata
2699 Used to retrieve data from a table after the server has been put into a
2701 COPY OUT mode by calling "COPY tablename TO STDOUT". Data is always
2702 returned one data row at a time. Note that the server will add a
2703 newline to each returned row.
2704 The first argument to pg_getcopydata is the variable into which the
2706 data will be stored (this variable should not be undefined, or it may
2707 throw a warning, although it may be a reference). The pg_getcopydata
2708 method returns a number greater than 1 indicating the new size of the
2709 variable, or a -1 when the COPY has finished. Once a -1 has been
2710 returned, no other action is necessary, as COPY mode will have already
2711 terminated. Example:
2712 $dbh->do("COPY mytable TO STDOUT");
2714 my @data;
2715 my $x=0;
2716 1 while $dbh->pg_getcopydata($data[$x++]) >= 0;
2717 There is also a variation of this method called pg_getcopydata_async,
2719 which, as the name suggests, returns immediately. The only difference
2720 from the original method is that this version may return a 0,
2721 indicating that the row is not ready to be delivered yet. When this
2722 happens, the variable has not been changed, and you will need to call
2723 the method again until you get a non-zero result. (Data is still
2724 always returned one data row at a time.)
2725 pg_putcopydata
2727 Used to put data into a table after the server has been put into COPY
2729 IN mode by calling "COPY tablename FROM STDIN". The only argument is
2730 the data you want inserted. Issue a pg_putcopyend() when you have added
2731 all your rows.
2732 The default delimiter is a tab character, but this can be changed in
2734 the COPY statement. Returns a 1 on successful input. Examples:
2735 ## Simple example:
2737 $dbh->do("COPY mytable FROM STDIN");
2738 $dbh->pg_putcopydata("123\tPepperoni\t3\n");
2739 $dbh->pg_putcopydata("314\tMushroom\t8\n");
2740 $dbh->pg_putcopydata("6\tAnchovies\t100\n");
2741 $dbh->pg_putcopyend();
2742 ## This example uses explicit columns and a custom delimiter
2744 $dbh->do("COPY mytable(flavor, slices) FROM STDIN WITH DELIMITER '~'");
2745 $dbh->pg_putcopydata("Pepperoni~123\n");
2746 $dbh->pg_putcopydata("Mushroom~314\n");
2747 $dbh->pg_putcopydata("Anchovies~6\n");
2748 $dbh->pg_putcopyend();
2749 pg_putcopyend
2751 When you are finished with pg_putcopydata, call pg_putcopyend to let
2753 the server know that you are done, and it will return to a normal, non-
2754 COPY state. Returns a 1 on success. This method will fail if called
2755 when not in COPY IN mode.
2756 Postgres limits
2758 For convenience, DBD::Pg can export certain constants representing the
2759 limits of Postgres data types. To use them, just add ":pg_limits" when
2760 DBD::Pg is used:
2761 use DBD::Pg qw/:pg_limits/;
2763 The constants and their values are:
2765 PG_MIN_SMALLINT -32768
2767 PG_MAX_SMALLINT 32767
2768 PG_MIN_INTEGER -2147483648
2769 PG_MAX_INTEGER 2147483647
2770 PG_MIN_BIGINT -9223372036854775808
2771 PG_MAX_BIGINT 9223372036854775807
2772 PG_MIN_SMALLSERIAL 1
2773 PG_MAX_SMALLSERIAL 32767
2774 PG_MIN_SERIAL 1
2775 PG_MAX_SERIAL 2147483647
2776 PG_MIN_BIGSERIAL 1
2777 PG_MAX_BIGSERIAL 9223372036854775807
2778 Large Objects
2780 DBD::Pg supports all largeobject functions provided by libpq via the
2781 "$dbh->pg_lo*" methods. Please note that access to a large object, even
2782 read-only large objects, must be put into a transaction.
2783 If DBD::Pg is compiled against and connected to PostgreSQL 9.3 or
2785 newer, and your Perl has 64-bit integers, it will use the 64-bit
2786 variants of the seek, tell and truncate methods.
2787 Cursors
2789 Although PostgreSQL supports cursors, they have not been used in the
2790 current implementation. When DBD::Pg was created, cursors in PostgreSQL
2791 could only be used inside a transaction block. Because only one
2792 transaction block at a time is allowed, this would have implied the
2793 restriction not to use any nested "SELECT" statements. Therefore the
2794 "execute" method fetches all data at once into data structures located
2795 in the front-end application. This fact must to be considered when
2796 selecting large amounts of data!
2797 You can use cursors in your application, but you'll need to do a little
2799 work. First you must declare your cursor. Now you can issue queries
2800 against the cursor, then select against your queries. This typically
2801 results in a double loop, like this:
2802 # WITH HOLD is not needed if AutoCommit is off
2804 $dbh->do("DECLARE csr CURSOR WITH HOLD FOR $sql");
2805 while (1) {
2806 my $sth = $dbh->prepare("fetch 1000 from csr");
2807 $sth->execute;
2808 last if 0 == $sth->rows;
2809 while (my $row = $sth->fetchrow_hashref) {
2811 # Do something with the data.
2812 }
2813 }
2814 $dbh->do("CLOSE csr");
2815 Datatype bool
2817 The current implementation of PostgreSQL returns 't' for true and 'f'
2818 for false. From the Perl point of view, this is a rather unfortunate
2819 choice. DBD::Pg therefore translates the result for the "BOOL" data
2820 type in a Perlish manner: 'f' becomes the number 0 and 't' becomes the
2821 number 1. This way the application does not have to check the database-
2822 specific returned values for the data-type "BOOL" because Perl treats 0
2823 as false and 1 as true. You may set the pg_bool_tf attribute to a true
2824 value to change the values back to 't' and 'f' if you wish.
2825 Boolean values can be passed to PostgreSQL as TRUE, 't', 'true', 'y',
2827 'yes' or '1' for true and FALSE, 'f', 'false', 'n', 'no' or '0' for
2828 false.
2829 Schema support
2831 The PostgreSQL schema concept may differ from those of other databases.
2832 In a nutshell, a schema is a named collection of objects within a
2833 single database. Please refer to the PostgreSQL documentation for more
2834 details:
2835 <http://www.postgresql.org/docs/current/static/ddl-schemas.html>
2837 DBD::Pg does not provide explicit support for PostgreSQL schemas.
2839 However, schema functionality may be used without any restrictions by
2840 explicitly addressing schema objects, e.g.
2841 my $res = $dbh->selectall_arrayref("SELECT * FROM my_schema.my_table");
2843 or by manipulating the schema search path with "SET search_path", e.g.
2845 $dbh->do("SET search_path TO my_schema, public");
2847
2849 The DBI module
2850
2852 To report a bug, or view the current list of bugs, please visit
2853 https://github.com/bucardo/dbdpg/issues
2854
2856 Pull requests can be submitted to github. Detailed information on how
2857 to help out with this module can be found in the README.dev file. The
2858 latest development version can be obtained via: git clone
2859 git://github.com/bucardo/dbdpg.git
2860
2862 DBI by Tim Bunce <http://www.tim.bunce.name>
2863 The original DBD-Pg was by Edmund Mergl (E.Mergl@bawue.de) and Jeffrey
2865 W. Baker (jwbaker@acm.org). Major developers include David Wheeler
2866 <david@justatheory.com>, Jason Stewart <jason@openinformatics.com>,
2867 Bruce Momjian <pgman@candle.pha.pa.us>, and Greg Sabino Mullane
2868 <greg@turnstep.com>, with help from many others: see the Changes file
2869 (<http://search.cpan.org/dist/DBD-Pg/Changes>) for a complete list.
2870 Parts of this package were originally copied from DBI and DBD-Oracle.
2872 Mailing List
2874 The current maintainers may be reached through the 'dbd-pg' mailing
2876 list: <dbd-pg@perl.org>. Subscribe by sending an email to
2877 dbd-pg-subscribe@perl.org. Visit the archives at
2878 http://grokbase.com/g/perl/dbd-pg
2879
2881 Copyright (C) 1994-2023, Greg Sabino Mullane
2882 This module (DBD::Pg) is free software; you can redistribute it and/or
2884 modify it under the same terms as Perl 5.10.0. For more details, see
2885 the full text of the licenses in the directory LICENSES.
2886perl v5.36.0 2023-04-05 Pg(3)