1Pg(3) User Contributed Perl Documentation Pg(3)
2
6 DBD::Pg - PostgreSQL database driver for the DBI module
7
9 use DBI;
10 $dbh = DBI->connect("dbi:Pg:dbname=$dbname", '', '', {AutoCommit => 0});
12 # The AutoCommit attribute should always be explicitly set
13 # For some advanced uses you may need PostgreSQL type values:
15 use DBD::Pg qw(:pg_types);
16 # For asynchronous calls, import the async constants:
18 use DBD::Pg qw(:async);
19 $dbh->do('INSERT INTO mytable(a) VALUES (1)');
21 $sth = $dbh->prepare('INSERT INTO mytable(a) VALUES (?)');
23 $sth->execute();
24
26 This documents version 3.7.4 of the DBD::Pg module
27
29 DBD::Pg is a Perl module that works with the DBI module to provide
30 access to PostgreSQL databases.
31
33 This documentation describes driver specific behavior and restrictions.
34 It is not supposed to be used as the only reference for the user. In
35 any case consult the DBI documentation first!
36
38 DBI Class Methods
39 connect
40 This method creates a database handle by connecting to a database, and
42 is the DBI equivalent of the "new" method. To connect to a Postgres
43 database with a minimum of parameters, use the following syntax:
44 $dbh = DBI->connect("dbi:Pg:dbname=$dbname", '', '', {AutoCommit => 0});
46 This connects to the database named in the $dbname variable on the
48 default port (usually 5432) without any user authentication.
49 The following connect statement shows almost all possible parameters:
51 $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host;port=$port;options=$options",
53 $username,
54 $password,
55 {AutoCommit => 0, RaiseError => 1, PrintError => 0}
56 );
57 Parameters containing unusual characters such as spaces can be wrapped
59 in single quotes around the value e.g. "dbi:Pg:dbname='spacey
60 name';host=$host"
61 If a parameter is not given, the connect() method will first look for
63 specific environment variables, and then fall back to hard-coded
64 defaults:
65 parameter environment variable hard coded default
67 ------------------------------------------------------
68 host PGHOST local domain socket
69 hostaddr PGHOSTADDR local domain socket
70 port PGPORT 5432
71 dbname* PGDATABASE current userid
72 username PGUSER current userid
73 password PGPASSWORD (none)
74 options PGOPTIONS (none)
75 service PGSERVICE (none)
76 sslmode PGSSLMODE (none)
77 * May also use the aliases "db" or "database"
79 If the username and password values passed via "connect()" are
81 undefined (as opposed to merely being empty strings), DBI will use the
82 environment variables DBI_USER and DBI_PASS if they exist.
83 You can also connect by using a service connection file, which is named
85 pg_service.conf. The location of this file can be controlled by setting
86 the PGSYSCONFDIR environment variable. To use one of the named services
87 within the file, set the name by using either the service parameter or
88 the environment variable PGSERVICE. Note that when connecting this way,
89 only the minimum parameters should be used. For example, to connect to
90 a service named "zephyr", you could use:
91 $dbh = DBI->connect("dbi:Pg:service=zephyr", '', '');
93 You could also set $ENV{PGSERVICE} to "zephyr" and connect like this:
95 $dbh = DBI->connect("dbi:Pg:", '', '');
97 The format of the pg_service.conf file is simply a bracketed service
99 name, followed by one parameter per line in the format name=value. For
100 example:
101 [zephyr]
103 dbname=winds
104 user=wisp
105 password=W$2Hc00YSgP
106 port=6543
107 There are four valid arguments to the sslmode parameter, which controls
109 whether to use SSL to connect to the database:
110 · disable: SSL connections are never used
112 · allow: try non-SSL, then SSL
114 · prefer: try SSL, then non-SSL
116 · require: connect only with SSL
118 You can also connect using sockets in a specific directory. This may be
120 needed if the server you are connecting to has a different default
121 socket directory from the one used to compile DBD::Pg. Use the
122 complete path to the socket directory as the name of the host, like
123 this:
124 $dbh = DBI->connect('dbi:Pg:dbname=foo;host=/var/tmp/socket',
126 $username,
127 $password,
128 {AutoCommit => 0, RaiseError => 1});
129 The attribute hash can also contain a key named "dbd_verbose", which
131 simply calls "$dbh->trace('DBD')" after the handle is created. This
132 attribute is not recommended, as it is clearer to simply explicitly
133 call "trace" explicitly in your script.
134 connect_cached
136 $dbh = DBI->connect_cached("dbi:Pg:dbname=$dbname", $username, $password, \%options);
138 Implemented by DBI, no driver-specific impact.
140 data_sources
142 @data_sources = DBI->data_sources('Pg');
144 @data_sources = $dbh->data_sources();
145 Returns a list of available databases. Unless the environment variable
147 "DBI_DSN" is set, a connection will be attempted to the database
148 "template1". The normal connection environment variables also apply,
149 such as "PGHOST", "PGPORT", "DBI_USER", "DBI_PASS", and "PGSERVICE".
150 You can also pass in options to add to the connection string For
152 example, to specify an alternate port and host:
153 @data_sources = DBI->data_sources('Pg', 'port=5824;host=example.com');
155 or:
157 @data_sources = $dbh->data_sources('port=5824;host=example.com');
159 Methods Common To All Handles
161 For all of the methods below, $h can be either a database handle ($dbh)
162 or a statement handle ($sth). Note that $dbh and $sth can be replaced
163 with any variable name you choose: these are just the names most often
164 used. Another common variable used in this documentation is $rv, which
165 stands for "return value".
166 err
168 $rv = $h->err;
170 Returns the error code from the last method called. For the connect
172 method it returns "PQstatus", which is a number used by libpq (the
173 Postgres connection library). A value of 0 indicates no error
174 (CONNECTION_OK), while any other number indicates a failed connection.
175 The only other number commonly seen is 1 (CONNECTION_BAD). See the
176 libpq documentation for the complete list of return codes.
177 In all other non-connect methods "$h->err" returns the "PQresultStatus"
179 of the current handle. This is a number used by libpq and is one of:
180 0 Empty query string
182 1 A command that returns no data successfully completed.
183 2 A command that returns data successfully completed.
184 3 A COPY OUT command is still in progress.
185 4 A COPY IN command is still in progress.
186 5 A bad response was received from the backend.
187 6 A nonfatal error occurred (a notice or warning message)
188 7 A fatal error was returned: the last query failed.
189 errstr
191 $str = $h->errstr;
193 Returns the last error that was reported by Postgres. This message is
195 affected by the pg_errorlevel setting.
196 state
198 $str = $h->state;
200 Returns a five-character "SQLSTATE" code. Success is indicated by a
202 00000 code, which gets mapped to an empty string by DBI. A code of
203 "S8006" indicates a connection failure, usually because the connection
204 to the Postgres server has been lost.
205 While this method can be called as either "$sth->state" or
207 "$dbh->state", it is usually clearer to always use "$dbh->state".
208 The list of codes used by PostgreSQL can be found at:
210 <http://www.postgresql.org/docs/current/static/errcodes-appendix.html>
211 Note that these codes are part of the SQL standard and only a small
213 number of them will be used by PostgreSQL.
214 Common codes:
216 00000 Successful completion
218 25P01 No active SQL transaction
219 25P02 In failed SQL transaction
220 S8006 Connection failure
221 trace
223 $h->trace($trace_settings);
225 $h->trace($trace_settings, $trace_filename);
226 $trace_settings = $h->trace;
227 Changes the trace settings on a database or statement handle. The
229 optional second argument specifies a file to write the trace
230 information to. If no filename is given, the information is written to
231 STDERR. Note that tracing can be set globally as well by setting
232 "DBI->trace", or by using the environment variable DBI_TRACE.
233 The value is either a numeric level or a named flag. For the flags that
235 DBD::Pg uses, see parse_trace_flag.
236 trace_msg
238 $h->trace_msg($message_text);
240 $h->trace_msg($message_text, $min_level);
241 Writes a message to the current trace output (as set by the "trace"
243 method). If a second argument is given, the message is only written if
244 the current tracing level is equal to or greater than the $min_level.
245 parse_trace_flag and parse_trace_flags
247 $h->trace($h->parse_trace_flags('SQL|pglibpq'));
249 $h->trace($h->parse_trace_flags('1|pgstart'));
250 ## Simpler:
252 $h->trace('SQL|pglibpq');
253 $h->trace('1|pgstart');
254 my $value = DBD::Pg->parse_trace_flag('pglibpq');
256 DBI->trace($value);
257 The parse_trace_flags method is used to convert one or more named flags
259 to a number which can passed to the "trace" method. DBD::Pg currently
260 supports the DBI-specific flag, "SQL", as well as the ones listed
261 below.
262 Flags can be combined by using the parse_trace_flags method, which
264 simply calls "parse_trace_flag" on each item and combines them.
265 Sometimes you may wish to turn the tracing on before you connect to the
267 database. The second example above shows a way of doing this: the call
268 to "DBD::Pg->parse_trace_flags" provides a number than can be fed to
269 "DBI->trace" before you create a database handle.
270 DBD::Pg supports the following trace flags:
272 SQL Outputs all SQL statements. Note that the output provided will not
274 necessarily be in a form suitable to passing directly to Postgres,
275 as server-side prepared statements are used extensively by DBD::Pg.
276 For maximum portability of output (but with a potential performance
277 hit), use with "$dbh->{pg_server_prepare} = 0".
278 DBD Turns on all non-DBI flags, in other words, only the ones that are
280 specific to DBD::Pg (all those below which start with the letters
281 'pg').
282 pglibpq
284 Outputs the name of each libpq function (without arguments)
285 immediately before running it. This is a good way to trace the flow
286 of your program at a low level. This information is also output if
287 the trace level is set to 4 or greater.
288 pgstart
290 Outputs the name of each internal DBD::Pg function, and other
291 information such as the function arguments or important global
292 variables, as each function starts. This information is also output
293 if the trace level is set to 4 or greater.
294 pgend
296 Outputs a simple message at the very end of each internal DBD::Pg
297 function. This is also output if the trace level is set to 4 or
298 greater.
299 pgprefix
301 Forces each line of trace output to begin with the string "dbdpg:
302 ". This helps to differentiate it from the normal DBI trace output.
303 pglogin
305 Outputs a message showing the connection string right before a new
306 database connection is attempted, a message when the connection was
307 successful, and a message right after the database has been
308 disconnected. Also output if trace level is 5 or greater.
309 func
311 DBD::Pg uses the "func" method to support a variety of functions. Note
313 that the name of the function comes last, after the arguments.
314 table_attributes
316 $attrs = $dbh->func($table, 'table_attributes');
317 Use of the tables_attributes function is no longer recommended.
319 Instead, you can use the more portable "column_info" and
320 "primary_key" methods to access the same information.
321 The table_attributes method returns, for the given table argument,
323 a reference to an array of hashes, each of which contains the
324 following keys:
325 NAME attribute name
327 TYPE attribute type
328 SIZE attribute size (-1 for variable size)
329 NULLABLE flag nullable
330 DEFAULT default value
331 CONSTRAINT constraint
332 PRIMARY_KEY flag is_primary_key
333 REMARKS attribute description
334 pg_lo_creat
336 $lobjId = $dbh->pg_lo_creat($mode);
337 Creates a new large object and returns the object-id. $mode is a
339 bitmask describing read and write access to the new object. This
340 setting is ignored since Postgres version 8.1. For backwards
341 compatibility, however, you should set a valid mode anyway (see
342 "pg_lo_open" for a list of valid modes).
343 Upon failure it returns "undef". This function cannot be used if
345 AutoCommit is enabled.
346 The old way of calling large objects functions is deprecated:
348 $dbh->func(.., 'lo_);
349 pg_lo_open
351 $lobj_fd = $dbh->pg_lo_open($lobjId, $mode);
352 Opens an existing large object and returns an object-descriptor for
354 use in subsequent "pg_lo_*" calls. $mode is a bitmask describing
355 read and write access to the opened object. It may be one of:
356 $dbh->{pg_INV_READ}
358 $dbh->{pg_INV_WRITE}
359 $dbh->{pg_INV_READ} | $dbh->{pg_INV_WRITE}
360 "pg_INV_WRITE" and "pg_INV_WRITE | pg_INV_READ" modes are
362 identical; in both modes, the large object can be read from or
363 written to. Reading from the object will provide the object as
364 written in other committed transactions, along with any writes
365 performed by the current transaction. Objects opened with
366 "pg_INV_READ" cannot be written to. Reading from this object will
367 provide the stored data at the time of the transaction snapshot
368 which was active when "pg_lo_write" was called.
369 Returns "undef" upon failure. Note that 0 is a perfectly correct
371 (and common) object descriptor! This function cannot be used if
372 AutoCommit is enabled.
373 pg_lo_write
375 $nbytes = $dbh->pg_lo_write($lobj_fd, $buffer, $len);
376 Writes $len bytes of c<$buffer> into the large object $lobj_fd.
378 Returns the number of bytes written and "undef" upon failure. This
379 function cannot be used if AutoCommit is enabled.
380 pg_lo_read
382 $nbytes = $dbh->pg_lo_read($lobj_fd, $buffer, $len);
383 Reads $len bytes into c<$buffer> from large object $lobj_fd.
385 Returns the number of bytes read and "undef" upon failure. This
386 function cannot be used if AutoCommit is enabled.
387 pg_lo_lseek
389 $loc = $dbh->pg_lo_lseek($lobj_fd, $offset, $whence);
390 Changes the current read or write location on the large object
392 $obj_id. Currently $whence can only be 0 (which is L_SET). Returns
393 the current location and "undef" upon failure. This function cannot
394 be used if AutoCommit is enabled.
395 pg_lo_tell
397 $loc = $dbh->pg_lo_tell($lobj_fd);
398 Returns the current read or write location on the large object
400 $lobj_fd and "undef" upon failure. This function cannot be used if
401 AutoCommit is enabled.
402 pg_lo_truncate
404 $loc = $dbh->pg_lo_truncate($lobj_fd, $len);
405 Truncates the given large object to the new size. Returns "undef"
407 on failure, and 0 on success. This function cannot be used if
408 AutoCommit is enabled.
409 pg_lo_close
411 $lobj_fd = $dbh->pg_lo_close($lobj_fd);
412 Closes an existing large object. Returns true upon success and
414 false upon failure. This function cannot be used if AutoCommit is
415 enabled.
416 pg_lo_unlink
418 $ret = $dbh->pg_lo_unlink($lobjId);
419 Deletes an existing large object. Returns true upon success and
421 false upon failure. This function cannot be used if AutoCommit is
422 enabled.
423 pg_lo_import
425 $lobjId = $dbh->pg_lo_import($filename);
426 Imports a Unix file as a large object and returns the object id of
428 the new object or "undef" upon failure.
429 pg_lo_import_with_oid
431 $lobjId = $dbh->pg_lo_import($filename, $OID);
432 Same as pg_lo_import, but attempts to use the supplied OID as the
434 large object number. If this number is 0, it falls back to the
435 behavior of pg_lo_import (which assigns the next available OID).
436 This is only available when DBD::Pg is compiled against a Postgres
438 server version 8.4 or later.
439 pg_lo_export
441 $ret = $dbh->pg_lo_export($lobjId, $filename);
442 Exports a large object into a Unix file. Returns false upon
444 failure, true otherwise.
445 getfd
447 $fd = $dbh->func('getfd');
448 Deprecated, use $dbh->{pg_socket} instead.
450 private_attribute_info
452 $hashref = $dbh->private_attribute_info();
454 $hashref = $sth->private_attribute_info();
455 Returns a hash of all private attributes used by DBD::Pg, for either a
457 database or a statement handle. Currently, all the hash values are
458 undef.
459
461 InactiveDestroy (boolean)
462 If set to true, then the "disconnect" method will not be automatically
464 called when the database handle goes out of scope. This is required if
465 you are forking, and even then you must tread carefully and ensure that
466 either the parent or the child (but not both!) handles all database
467 calls from that point forwards, so that messages from the Postgres
468 backend are only handled by one of the processes. If you don't set
469 things up properly, you will see messages such as "server closed the
470 connection unexpectedly", and "message type 0x32 arrived from server
471 while idle". The best solution is to either have the child process
472 reconnect to the database with a fresh database handle, or to rewrite
473 your application not to use forking. See the section on "Asynchronous
474 Queries" for a way to have your script continue to work while the
475 database is processing a request.
476 AutoInactiveDestroy (boolean)
478 The InactiveDestroy attribute, described above, needs to be explicitly
480 set in the child process after a fork. If the code that performs the
481 fork is in a third party module such as Sys::Syslog, this can present a
482 problem. Use AutoInactiveDestroy to get around this problem.
483 RaiseError (boolean, inherited)
485 Forces errors to always raise an exception. Although it defaults to
487 off, it is recommended that this be turned on, as the alternative is to
488 check the return value of every method (prepare, execute, fetch, etc.)
489 manually, which is easy to forget to do.
490 PrintError (boolean, inherited)
492 Forces database errors to also generate warnings, which can then be
494 filtered with methods such as locally redefining $SIG{__WARN__} or
495 using modules such as "CGI::Carp". This attribute is on by default.
496 ShowErrorStatement (boolean, inherited)
498 Appends information about the current statement to error messages. If
500 placeholder information is available, adds that as well. Defaults to
501 false.
502 Warn (boolean, inherited)
504 Enables warnings. This is on by default, and should only be turned off
506 in a local block for a short a time only when absolutely needed.
507 Executed (boolean, read-only)
509 Indicates if a handle has been executed. For database handles, this
511 value is true after the "do" method has been called, or when one of the
512 child statement handles has issued an "execute". Issuing a "commit" or
513 "rollback" always resets the attribute to false for database handles.
514 For statement handles, any call to "execute" or its variants will flip
515 the value to true for the lifetime of the statement handle.
516 TraceLevel (integer, inherited)
518 Sets the trace level, similar to the "trace" method. See the sections
520 on "trace" and parse_trace_flag for more details.
521 Active (boolean, read-only)
523 Indicates if a handle is active or not. For database handles, this
525 indicates if the database has been disconnected or not. For statement
526 handles, it indicates if all the data has been fetched yet or not. Use
527 of this attribute is not encouraged.
528 Kids (integer, read-only)
530 Returns the number of child processes created for each handle type. For
532 a driver handle, indicates the number of database handles created. For
533 a database handle, indicates the number of statement handles created.
534 For statement handles, it always returns zero, because statement
535 handles do not create kids.
536 ActiveKids (integer, read-only)
538 Same as "Kids", but only returns those that are active.
540 CachedKids (hash ref)
542 Returns a hashref of handles. If called on a database handle, returns
544 all statement handles created by use of the "prepare_cached" method. If
545 called on a driver handle, returns all database handles created by the
546 "connect_cached" method.
547 ChildHandles (array ref)
549 Implemented by DBI, no driver-specific impact.
551 PrintWarn (boolean, inherited)
553 Implemented by DBI, no driver-specific impact.
555 HandleError (boolean, inherited)
557 Implemented by DBI, no driver-specific impact.
559 HandleSetErr (code ref, inherited)
561 Implemented by DBI, no driver-specific impact.
563 ErrCount (unsigned integer)
565 Implemented by DBI, no driver-specific impact.
567 FetchHashKeyName (string, inherited)
569 Implemented by DBI, no driver-specific impact.
571 ChopBlanks (boolean, inherited)
573 Supported by DBD::Pg as proposed by DBI. This method is similar to the
575 SQL function "RTRIM".
576 Taint (boolean, inherited)
578 Implemented by DBI, no driver-specific impact.
580 TaintIn (boolean, inherited)
582 Implemented by DBI, no driver-specific impact.
584 TaintOut (boolean, inherited)
586 Implemented by DBI, no driver-specific impact.
588 Profile (inherited)
590 Implemented by DBI, no driver-specific impact.
592 Type (scalar)
594 Returns "dr" for a driver handle, "db" for a database handle, and "st"
596 for a statement handle. Should be rarely needed.
597 LongReadLen
599 Not used by DBD::Pg
601 LongTruncOk
603 Not used by DBD::Pg
605 CompatMode
607 Not used by DBD::Pg
609
611 Database Handle Methods
612 selectall_arrayref
613 $ary_ref = $dbh->selectall_arrayref($sql);
615 $ary_ref = $dbh->selectall_arrayref($sql, \%attr);
616 $ary_ref = $dbh->selectall_arrayref($sql, \%attr, @bind_values);
617 Returns a reference to an array containing the rows returned by
619 preparing and executing the SQL string. See the DBI documentation for
620 full details.
621 selectall_hashref
623 $hash_ref = $dbh->selectall_hashref($sql, $key_field);
625 Returns a reference to a hash containing the rows returned by preparing
627 and executing the SQL string. See the DBI documentation for full
628 details.
629 selectcol_arrayref
631 $ary_ref = $dbh->selectcol_arrayref($sql, \%attr, @bind_values);
633 Returns a reference to an array containing the first column from each
635 rows returned by preparing and executing the SQL string. It is possible
636 to specify exactly which columns to return. See the DBI documentation
637 for full details.
638 prepare
640 $sth = $dbh->prepare($statement, \%attr);
642 WARNING: DBD::Pg now (as of version 1.40) uses true prepared statements
644 by sending them to the backend to be prepared by the Postgres server.
645 Statements that were legal before may no longer work. See below for
646 details.
647 The prepare method prepares a statement for later execution. PostgreSQL
649 supports prepared statements, which enables DBD::Pg to only send the
650 query once, and simply send the arguments for every subsequent call to
651 "execute". DBD::Pg can use these server-side prepared statements, or
652 it can just send the entire query to the server each time. The best way
653 is automatically chosen for each query. This will be sufficient for
654 most users: keep reading for a more detailed explanation and some
655 optional flags.
656 Queries that do not begin with the word "SELECT", "INSERT", "UPDATE",
658 or "DELETE" are never sent as server-side prepared statements.
659 Deciding whether or not to use prepared statements depends on many
661 factors, but you can force them to be used or not used by using the
662 pg_server_prepare attribute when calling "prepare". Setting this to "0"
663 means to never use prepared statements. Setting pg_server_prepare to
664 "1" means that prepared statements should be used whenever possible.
665 This is the default when connected to Postgres servers version 8.0 or
666 higher. Servers that are version 7.4 get a special default value of
667 "2", because server-side statements were only partially supported in
668 that version. In this case, it only uses server-side prepares if all
669 parameters are specifically bound.
670 The pg_server_prepare attribute can also be set at connection time like
672 so:
673 $dbh = DBI->connect($DBNAME, $DBUSER, $DBPASS,
675 { AutoCommit => 0,
676 RaiseError => 1,
677 pg_server_prepare => 0,
678 });
679 or you may set it after your database handle is created:
681 $dbh->{pg_server_prepare} = 1;
683 To enable it for just one particular statement:
685 $sth = $dbh->prepare("SELECT id FROM mytable WHERE val = ?",
687 { pg_server_prepare => 1 });
688 You can even toggle between the two as you go:
690 $sth->{pg_server_prepare} = 1;
692 $sth->execute(22);
693 $sth->{pg_server_prepare} = 0;
694 $sth->execute(44);
695 $sth->{pg_server_prepare} = 1;
696 $sth->execute(66);
697 In the above example, the first execute will use the previously
699 prepared statement. The second execute will not, but will build the
700 query into a single string and send it to the server. The third one
701 will act like the first and only send the arguments. Even if you
702 toggle back and forth, a statement is only prepared once.
703 Using prepared statements is in theory quite a bit faster: not only
705 does the PostgreSQL backend only have to prepare the query only once,
706 but DBD::Pg no longer has to worry about quoting each value before
707 sending it to the server.
708 However, there are some drawbacks. The server cannot always choose the
710 ideal parse plan because it will not know the arguments before hand.
711 But for most situations in which you will be executing similar data
712 many times, the default plan will probably work out well. Programs such
713 as PgBouncer which cache connections at a low level should not use
714 prepared statements via DBD::Pg, or must take extra care in the
715 application to account for the fact that prepared statements are not
716 shared across database connections. Further discussion on this subject
717 is beyond the scope of this documentation: please consult the pgsql-
718 performance mailing list,
719 <http://archives.postgresql.org/pgsql-performance/>
720 Only certain commands will be sent to a server-side prepare: currently
722 these include "SELECT", "INSERT", "UPDATE", and "DELETE". DBD::Pg uses
723 a simple naming scheme for the prepared statements themselves:
724 dbdpg_XY_Z, where Y is the current PID, X is either 'p' or 'n'
725 (depending on if the PID is a positive or negative number), and Z is a
726 number that starts at 1 and increases each time a new statement is
727 prepared. This number is tracked at the database handle level, so
728 multiple statement handles will not collide.
729 You cannot send more than one command at a time in the same prepare
731 command (by separating them with semi-colons) when using server-side
732 prepares.
733 The actual "PREPARE" is usually not performed until the first execute
735 is called, due to the fact that information on the data types (provided
736 by "bind_param") may be provided after the prepare but before the
737 execute.
738 A server-side prepare may happen before the first "execute", but only
740 if the server can handle the server-side prepare, and the statement
741 contains no placeholders. It will also be prepared if the
742 pg_prepare_now attribute is passed in and set to a true value.
743 Similarly, the pg_prepare_now attribute can be set to 0 to ensure that
744 the statement is not prepared immediately, although the cases in which
745 you would want this are very rare. Finally, you can set the default
746 behavior of all prepare statements by setting the pg_prepare_now
747 attribute on the database handle:
748 $dbh->{pg_prepare_now} = 1;
750 The following two examples will be prepared right away:
752 $sth->prepare("SELECT 123"); ## no placeholders
754 $sth->prepare("SELECT 123, ?", {pg_prepare_now => 1});
756 The following two examples will NOT be prepared right away:
758 $sth->prepare("SELECT 123, ?"); ## has a placeholder
760 $sth->prepare("SELECT 123", {pg_prepare_now => 0});
762 There are times when you may want to prepare a statement yourself. To
764 do this, simply send the "PREPARE" statement directly to the server
765 (e.g. with the "do" method). Create a statement handle and set the
766 prepared name via the pg_prepare_name attribute. The statement handle
767 can be created with a dummy statement, as it will not be executed.
768 However, it should have the same number of placeholders as your
769 prepared statement. Example:
770 $dbh->do('PREPARE mystat AS SELECT COUNT(*) FROM pg_class WHERE reltuples < ?');
772 $sth = $dbh->prepare('SELECT ?');
773 $sth->bind_param(1, 1, SQL_INTEGER);
774 $sth->{pg_prepare_name} = 'mystat';
775 $sth->execute(123);
776 The above will run the equivalent of this query on the backend:
778 EXECUTE mystat(123);
780 which is the equivalent of:
782 SELECT COUNT(*) FROM pg_class WHERE reltuples < 123;
784 You can force DBD::Pg to send your query directly to the server by
786 adding the pg_direct attribute to your prepare call. This is not
787 recommended, but is added just in case you need it.
788 Placeholders
790 There are three types of placeholders that can be used in DBD::Pg. The
792 first is the "question mark" type, in which each placeholder is
793 represented by a single question mark character. This is the method
794 recommended by the DBI specs and is the most portable. Each question
795 mark is internally replaced by a "dollar sign number" in the order in
796 which they appear in the query (important when using "bind_param").
797 The second type of placeholder is "dollar sign numbers". This is the
799 method that Postgres uses internally and is overall probably the best
800 method to use if you do not need compatibility with other database
801 systems. DBD::Pg, like PostgreSQL, allows the same number to be used
802 more than once in the query. Numbers must start with "1" and increment
803 by one value (but can appear in any order within the query). If the
804 same number appears more than once in a query, it is treated as a
805 single parameter and all instances are replaced at once. Examples:
806 Not legal:
808 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $2'; # Does not start with 1
810 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $3'; # Missing 2
812 Legal:
814 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $1';
816 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $2';
818 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $2 AND $1'; # legal but confusing
820 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $2 AND reltuples > $1';
822 $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $1 AND reltuples > $1';
824 In the final statement above, DBI thinks there is only one placeholder,
826 so this statement will replace both placeholders:
827 $sth->bind_param(1, 2045);
829 While a simple execute with no bind_param calls requires only a single
831 argument as well:
832 $sth->execute(2045);
834 The final placeholder type is "named parameters" in the format ":foo".
836 While this syntax is supported by DBD::Pg, its use is discouraged in
837 favor of dollar-sign numbers.
838 The different types of placeholders cannot be mixed within a statement,
840 but you may use different ones for each statement handle you have. This
841 is confusing at best, so stick to one style within your program.
842 If your queries use operators that contain question marks (e.g. some of
844 the native Postgres geometric operators and JSON operators) or array
845 slices (e.g. "data[100:300]"), there are methods to instruct DBD::Pg to
846 not treat some symbols as placeholders. First, you may simply add a
847 backslash before the start of a placeholder, and DBD::Pg will strip the
848 backslash and not treat the character as a placeholder.
849 You can also tell DBD::Pg to ignore any non-dollar sign placeholders by
851 setting the pg_placeholder_dollaronly attribute at either the database
852 handle or the statement handle level. Examples:
853 $dbh->{pg_placeholder_dollaronly} = 1;
855 $sth = $dbh->prepare(q{SELECT * FROM mytable WHERE lseg1 ?# lseg2 AND name = $1});
856 $sth->execute('segname');
857 Alternatively, you can set it at prepare time:
859 $sth = $dbh->prepare(q{SELECT * FROM mytable WHERE lseg1 ?-| lseg2 AND name = $1},
861 {pg_placeholder_dollaronly => 1});
862 $sth->execute('segname');
863 If your queries use array slices but you still want to use question
865 marks as placeholders, you can tell DBD::Pg to ignore just colon
866 placeholders by setting the "pg_placeholder_nocolons" attribute in the
867 same way. Examples:
868 $dbh->{pg_placeholder_nocolons} = 1;
870 $sth = $dbh->prepare(q{SELECT array[1:2] FROM mytable WHERE id = ?});
871 $sth->execute(1);
872 Again, you may set it param time as well:
874 $sth = $dbh->prepare(q{SELECT array[1:2] FROM mytable WHERE id = ?}.
876 {pg_placeholder_nocolons => 1});
877 $sth->execute(1);
878 It should be noted that placeholders only work when used outside of a
880 literal string context; i.e., the following examples will not
881 define/use any placeholders due to appearing inside strings within the
882 SQL:
883 $sth = $dbh->prepare(q{SELECT id FROM mytable WHERE text LIKE '%?'});
885 $dbh->do(q{DO LANGUAGE plpgsql $$ BEGIN RAISE NOTICE ?; END $$}, undef, $message);
886 See the DBI placeholder documentation for more details.
888 prepare_cached
890 $sth = $dbh->prepare_cached($statement, \%attr);
892 Implemented by DBI, no driver-specific impact. This method is most
894 useful when using a server that supports server-side prepares, and you
895 have asked the prepare to happen immediately via the pg_prepare_now
896 attribute.
897 do
899 $rv = $dbh->do($statement);
901 $rv = $dbh->do($statement, \%attr);
902 $rv = $dbh->do($statement, \%attr, @bind_values);
903 Prepare and execute a single statement. Returns the number of rows
905 affected if the query was successful, returns undef if an error
906 occurred, and returns -1 if the number of rows is unknown or not
907 available. Note that this method will return 0E0 instead of 0 for 'no
908 rows were affected', in order to always return a true value if no error
909 occurred.
910 If neither "\%attr" nor @bind_values is given, the query will be sent
912 directly to the server without the overhead of internally creating a
913 statement handle and running prepare and execute, for a measurable
914 speed increase.
915 Note that an empty statement (a string with no length) will not be
917 passed to the server; if you want a simple test, use "SELECT 123" or
918 the "ping" method.
919 last_insert_id
921 $rv = $dbh->last_insert_id(undef, $schema, $table, undef);
923 $rv = $dbh->last_insert_id(undef, $schema, $table, undef, {sequence => $seqname});
924 Attempts to return the id of the last value to be inserted into a
926 table. You can either provide a sequence name (preferred) or provide a
927 table name with optional schema, and DBD::Pg will attempt to find the
928 sequence itself. The current value of the sequence is returned by a
929 call to the "CURRVAL()" PostgreSQL function. This will fail if the
930 sequence has not yet been used in the current database connection.
931 If you do not know the name of the sequence, you can provide a table
933 name and DBD::Pg will attempt to return the correct value. To do this,
934 there must be at least one column in the table with a "NOT NULL"
935 constraint, that has a unique constraint, and which uses a sequence as
936 a default value. If more than one column meets these conditions, the
937 primary key will be used. This involves some looking up of things in
938 the system table, so DBD::Pg will cache the sequence name for
939 subsequent calls. If you need to disable this caching for some reason,
940 (such as the sequence name changing), you can control it by adding
941 "pg_cache => 0" to the final (hashref) argument for last_insert_id.
942 Please keep in mind that this method is far from foolproof, so make
944 your script use it properly. Specifically, make sure that it is called
945 immediately after the insert, and that the insert does not add a value
946 to the column that is using the sequence as a default value. However,
947 because we are using sequences, you can be sure that the value you got
948 back has not been used by any other process.
949 Some examples:
951 $dbh->do('CREATE SEQUENCE lii_seq START 1');
953 $dbh->do(q{CREATE TABLE lii (
954 foobar INTEGER NOT NULL UNIQUE DEFAULT nextval('lii_seq'),
955 baz VARCHAR)});
956 $SQL = 'INSERT INTO lii(baz) VALUES (?)';
957 $sth = $dbh->prepare($SQL);
958 for (qw(uno dos tres cuatro)) {
959 $sth->execute($_);
960 my $newid = $dbh->last_insert_id(undef,undef,undef,undef,{sequence=>'lii_seq'});
961 print "Last insert id was $newid\n";
962 }
963 If you did not want to worry about the sequence name:
965 $dbh->do('CREATE TABLE lii2 (
967 foobar SERIAL UNIQUE,
968 baz VARCHAR)');
969 $SQL = 'INSERT INTO lii2(baz) VALUES (?)';
970 $sth = $dbh->prepare($SQL);
971 for (qw(uno dos tres cuatro)) {
972 $sth->execute($_);
973 my $newid = $dbh->last_insert_id(undef,undef,"lii2",undef);
974 print "Last insert id was $newid\n";
975 }
976 commit
978 $rv = $dbh->commit;
980 Issues a COMMIT to the server, indicating that the current transaction
982 is finished and that all changes made will be visible to other
983 processes. If AutoCommit is enabled, then a warning is given and no
984 COMMIT is issued. Returns true on success, false on error. See also
985 the section on "Transactions".
986 rollback
988 $rv = $dbh->rollback;
990 Issues a ROLLBACK to the server, which discards any changes made in the
992 current transaction. If AutoCommit is enabled, then a warning is given
993 and no ROLLBACK is issued. Returns true on success, and false on error.
994 See also the the section on "Transactions".
995 begin_work
997 This method turns on transactions until the next call to "commit" or
999 "rollback", if AutoCommit is currently enabled. If it is not enabled,
1000 calling begin_work will issue an error. Note that the transaction will
1001 not actually begin until the first statement after begin_work is
1002 called. Example:
1003 $dbh->{AutoCommit} = 1;
1005 $dbh->do('INSERT INTO foo VALUES (123)'); ## Changes committed immediately
1006 $dbh->begin_work();
1007 ## Not in a transaction yet, but AutoCommit is set to 0
1008 $dbh->do("INSERT INTO foo VALUES (345)");
1010 ## DBD::PG actually issues two statements here:
1011 ## BEGIN;
1012 ## INSERT INTO foo VALUES (345)
1013 ## We are now in a transaction
1014 $dbh->commit();
1016 ## AutoCommit is now set to 1 again
1017 disconnect
1019 $rv = $dbh->disconnect;
1021 Disconnects from the Postgres database. Any uncommitted changes will be
1023 rolled back upon disconnection. It's good policy to always explicitly
1024 call commit or rollback at some point before disconnecting, rather than
1025 relying on the default rollback behavior.
1026 This method may give warnings about "disconnect invalidates X active
1028 statement handle(s)". This means that you called "$sth->execute()" but
1029 did not finish fetching all the rows from them. To avoid seeing this
1030 warning, either fetch all the rows or call "$sth->finish()" for each
1031 executed statement handle.
1032 If the script exits before disconnect is called (or, more precisely, if
1034 the database handle is no longer referenced by anything), then the
1035 database handle's DESTROY method will call the rollback() and
1036 disconnect() methods automatically. It is best to explicitly disconnect
1037 rather than rely on this behavior.
1038 quote
1040 $rv = $dbh->quote($value, $data_type);
1042 This module implements its own "quote" method. For simple string types,
1044 both backslashes and single quotes are doubled. You may also quote
1045 arrayrefs and receive a string suitable for passing into Postgres array
1046 columns.
1047 If the value contains backslashes, and the server is version 8.1 or
1049 higher, then the escaped string syntax will be used (which places a
1050 capital E before the first single quote). This syntax is always used
1051 when quoting bytea values on servers 8.1 and higher.
1052 The "data_type" argument is optional and should be one of the type
1054 constants exported by DBD::Pg (such as PG_BYTEA). In addition to
1055 string, bytea, char, bool, and other standard types, the following
1056 geometric types are supported: point, line, lseg, box, path, polygon,
1057 and circle (PG_POINT, PG_LINE, PG_LSEG, PG_BOX, PG_PATH, PG_POLYGON,
1058 and PG_CIRCLE respectively). To quote a Postgres-specific data type,
1059 you must use a 'hashref' argument like so:
1060 my $quotedval = $dbh->quote($value, { pg_type => PG_VARCHAR });
1062 NOTE: The undocumented (and invalid) support for the "SQL_BINARY" data
1064 type is officially deprecated. Use "PG_BYTEA" with "bind_param()"
1065 instead:
1066 $rv = $sth->bind_param($param_num, $bind_value,
1068 { pg_type => PG_BYTEA });
1069 quote_identifier
1071 $string = $dbh->quote_identifier( $name );
1073 $string = $dbh->quote_identifier( undef, $schema, $table);
1074 Returns a quoted version of the supplied string, which is commonly a
1076 schema, table, or column name. The three argument form will return the
1077 schema and the table together, separated by a dot. Examples:
1078 print $dbh->quote_identifier('grapefruit'); ## Prints: "grapefruit"
1080 print $dbh->quote_identifier('juicy fruit'); ## Prints: "juicy fruit"
1082 print $dbh->quote_identifier(undef, 'public', 'pg_proc');
1084 ## Prints: "public"."pg_proc"
1085 pg_notifies
1087 $ret = $dbh->pg_notifies;
1089 Looks for any asynchronous notifications received and returns either
1091 "undef" or a reference to a three-element array consisting of an event
1092 name, the PID of the backend that sent the NOTIFY command, and the
1093 optional payload string. Note that this does not check if the
1094 connection to the database is still valid first - for that, use the
1095 c<ping> method. You may need to commit if not in autocommit mode - new
1096 notices will not be picked up while in the middle of a transaction. An
1097 example:
1098 $dbh->do("LISTEN abc");
1100 $dbh->do("LISTEN def");
1101 ## Hang around until we get the message we want
1103 LISTENLOOP: {
1104 while (my $notify = $dbh->pg_notifies) {
1105 my ($name, $pid, $payload) = @$notify;
1106 print qq{I received notice "$name" from PID $pid, payload was "$payload"\n};
1107 ## Do something based on the notice received
1108 }
1109 $dbh->ping() or die qq{Ping failed!};
1110 $dbh->commit();
1111 sleep(5);
1112 redo;
1113 }
1114 Payloads will always be an empty string unless you are connecting to a
1116 Postgres server version 9.0 or higher.
1117 ping
1119 $rv = $dbh->ping;
1121 The "ping" method determines if there is a working connection to an
1123 active database server. It does this by sending a small query to the
1124 server, currently 'DBD::Pg ping test v3.7.4'. It returns 0 (false) if
1125 the connection is not valid, otherwise it returns a positive number
1126 (true). The value returned indicates the current state:
1127 Value Meaning
1129 --------------------------------------------------
1130 1 Database is idle (not in a transaction)
1131 2 Database is active, there is a command in progress (usually seen after a COPY command)
1132 3 Database is idle within a transaction
1133 4 Database is idle, within a failed transaction
1134 Additional information on why a handle is not valid can be obtained by
1136 using the "pg_ping" method.
1137 pg_ping
1139 $rv = $dbh->pg_ping;
1141 This is a DBD::Pg-specific extension to the "ping" method. This will
1143 check the validity of a database handle in exactly the same way as
1144 "ping", but instead of returning a 0 for an invalid connection, it will
1145 return a negative number. So in addition to returning the positive
1146 numbers documented for "ping", it may also return the following:
1147 Value Meaning
1149 --------------------------------------------------
1150 -1 There is no connection to the database at all (e.g. after disconnect)
1151 -2 An unknown transaction status was returned (e.g. after forking)
1152 -3 The test query failed (PQexec returned null)
1153 -4 PQstatus returned a CONNECTION_BAD
1154 get_info
1156 $value = $dbh->get_info($info_type);
1158 Supports a very large set (> 250) of the information types, including
1160 the minimum recommended by DBI.
1161 table_info
1163 $sth = $dbh->table_info(undef, $schema, $table, $type);
1165 Returns all tables and views visible to the current user. The schema
1167 and table arguments will do a "LIKE" search if a percent sign ("%") or
1168 an underscore ("_") is detected in the argument. The $type argument
1169 accepts any comma-separated combination of "TABLE", "VIEW", "SYSTEM
1170 TABLE", "SYSTEM VIEW", "MATERIALIZED VIEW", "SYSTEM MATERIALIZED VIEW",
1171 or "LOCAL TEMPORARY". (Using all is the default action.)
1172 Note that a statement handle is returned, and not a direct list of
1174 tables. See the examples below for ways to handle this.
1175 The following fields are returned:
1177 TABLE_CAT: Always NULL, as Postgres does not have the concept of
1179 catalogs.
1180 TABLE_SCHEM: The name of the schema that the table or view is in.
1182 TABLE_NAME: The name of the table or view.
1184 TABLE_TYPE: The type of object returned. Will be one of "TABLE",
1186 "VIEW", "MATERIALIZED VIEW", "SYSTEM VIEW", "SYSTEM MATERIALIZED VIEW",
1187 "SYSTEM TABLE", or "LOCAL TEMPORARY".
1188 The TABLE_SCHEM and TABLE_NAME will be quoted via "quote_ident()".
1190 Two additional fields specific to DBD::Pg are returned:
1192 pg_schema: the unquoted name of the schema
1194 pg_table: the unquoted name of the table
1196 If your database supports tablespaces (version 8.0 or greater), two
1198 additional DBD::Pg specific fields are returned:
1199 pg_tablespace_name: the name of the tablespace the table is in
1201 pg_tablespace_location: the location of the tablespace the table is in
1203 Tables that have not been assigned to a particular tablespace (or
1205 views) will return NULL ("undef") for both of the above field.
1206 Rows are returned alphabetically, with all tables first, and then all
1208 views.
1209 Examples of use:
1211 ## Display all tables and views in the public schema:
1213 $sth = $dbh->table_info('', 'public', undef, undef);
1214 for my $rel (@{$sth->fetchall_arrayref({})}) {
1215 print "$rel->{TABLE_TYPE} name is $rel->{TABLE_NAME}\n";
1216 }
1217 # Display the schema of all tables named 'foo':
1220 $sth = $dbh->table_info('', undef, 'foo', 'TABLE');
1221 for my $rel (@{$sth->fetchall_arrayref({})}) {
1222 print "Table name is $rel->{TABLE_SCHEM}.$rel->{TABLE_NAME}\n";
1223 }
1224 column_info
1226 $sth = $dbh->column_info( undef, $schema, $table, $column );
1228 Supported by this driver as proposed by DBI with the follow exceptions.
1230 These fields are currently always returned with NULL ("undef") values:
1231 TABLE_CAT
1233 BUFFER_LENGTH
1234 DECIMAL_DIGITS
1235 NUM_PREC_RADIX
1236 SQL_DATA_TYPE
1237 SQL_DATETIME_SUB
1238 CHAR_OCTET_LENGTH
1239 Also, six additional non-standard fields are returned:
1241 pg_type: data type with additional info i.e. "character varying(20)"
1243 pg_constraint: holds column constraint definition
1245 pg_schema: the unquoted name of the schema
1247 pg_table: the unquoted name of the table
1249 pg_column: the unquoted name of the column
1251 pg_enum_values: an array reference of allowed values for an enum column
1253 Note that the TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME fields all
1255 return output wrapped in quote_ident(). If you need the unquoted
1256 version, use the pg_ fields above.
1257 primary_key_info
1259 $sth = $dbh->primary_key_info( undef, $schema, $table, \%attr );
1261 Supported by this driver as proposed by DBI. There are no search
1263 patterns allowed, but leaving the $schema argument blank will cause the
1264 first table found in the schema search path to be used. An additional
1265 field, "DATA_TYPE", is returned and shows the data type for each of the
1266 arguments in the "COLUMN_NAME" field.
1267 This method will also return tablespace information for servers that
1269 support tablespaces. See the "table_info" entry for more information.
1270 The five additional custom fields returned are:
1272 pg_tablespace_name: name of the tablespace, if any
1274 pg_tablespace_location: location of the tablespace
1276 pg_schema: the unquoted name of the schema
1278 pg_table: the unquoted name of the table
1280 pg_column: the unquoted name of the column
1282 In addition to the standard format of returning one row for each column
1284 found for the primary key, you can pass the "pg_onerow" attribute to
1285 force a single row to be used. If the primary key has multiple columns,
1286 the "KEY_SEQ", "COLUMN_NAME", and "DATA_TYPE" fields will return a
1287 comma-delimited string. If the "pg_onerow" attribute is set to "2", the
1288 fields will be returned as an arrayref, which can be useful when
1289 multiple columns are involved:
1290 $sth = $dbh->primary_key_info('', '', 'dbd_pg_test', {pg_onerow => 2});
1292 if (defined $sth) {
1293 my $pk = $sth->fetchall_arrayref()->[0];
1294 print "Table $pk->[2] has a primary key on these columns:\n";
1295 for (my $x=0; defined $pk->[3][$x]; $x++) {
1296 print "Column: $pk->[3][$x] (data type: $pk->[6][$x])\n";
1297 }
1298 }
1299 primary_key
1301 @key_column_names = $dbh->primary_key(undef, $schema, $table);
1303 Simple interface to the "primary_key_info" method. Returns a list of
1305 the column names that comprise the primary key of the specified table.
1306 The list is in primary key column sequence order. If there is no
1307 primary key then an empty list is returned.
1308 foreign_key_info
1310 $sth = $dbh->foreign_key_info( $pk_catalog, $pk_schema, $pk_table,
1312 $fk_catalog, $fk_schema, $fk_table );
1313 Supported by this driver as proposed by DBI, using the SQL/CLI variant.
1315 There are no search patterns allowed, but leaving the $schema argument
1316 blank will cause the first table found in the schema search path to be
1317 used. Two additional fields, "UK_DATA_TYPE" and "FK_DATA_TYPE", are
1318 returned to show the data type for the unique and foreign key columns.
1319 Foreign keys that have no named constraint (where the referenced column
1320 only has an unique index) will return "undef" for the "UK_NAME" field.
1321 statistics_info
1323 $sth = $dbh->statistics_info( undef, $schema, $table, $unique_only, $quick );
1325 Returns a statement handle that can be fetched from to give statistics
1327 information on a specific table and its indexes. The $table argument is
1328 mandatory. The $schema argument is optional but recommended. The
1329 $unique_only argument, if true, causes only information about unique
1330 indexes to be returned. The $quick argument is not used by DBD::Pg. For
1331 information on the format of the standard rows returned, please see the
1332 DBI documentation.
1333 In addition, the following Postgres specific columns are returned:
1335 pg_expression
1337 Postgres allows indexes on functions and scalar expressions based
1338 on one or more columns. This field will always be populated if an
1339 index, but the lack of an entry in the COLUMN_NAME should indicate
1340 that this is an index expression.
1341 tables
1343 @names = $dbh->tables( undef, $schema, $table, $type, \%attr );
1345 Supported by this driver as proposed by DBI. This method returns all
1347 tables and/or views (including materialized views) which are visible to
1348 the current user: see "table_info" for more information about the
1349 arguments. The name of the schema appears before the table or view
1350 name. This can be turned off by adding in the "pg_noprefix" attribute:
1351 my @tables = $dbh->tables( '', '', 'dbd_pg_test', '', {pg_noprefix => 1} );
1353 type_info_all
1355 $type_info_all = $dbh->type_info_all;
1357 Supported by this driver as proposed by DBI. Information is only
1359 provided for SQL datatypes and for frequently used datatypes. The
1360 mapping between the PostgreSQL typename and the SQL92 datatype (if
1361 possible) has been done according to the following table:
1362 +---------------+------------------------------------+
1364 | typname | SQL92 |
1365 |---------------+------------------------------------|
1366 | bool | BOOL |
1367 | text | / |
1368 | bpchar | CHAR(n) |
1369 | varchar | VARCHAR(n) |
1370 | int2 | SMALLINT |
1371 | int4 | INT |
1372 | int8 | BIGINT |
1373 | money | / |
1374 | float4 | FLOAT(p) p<7=float4, p<16=float8 |
1375 | float8 | REAL |
1376 | abstime | / |
1377 | reltime | / |
1378 | tinterval | / |
1379 | date | / |
1380 | time | / |
1381 | datetime | / |
1382 | timespan | TINTERVAL |
1383 | timestamp | TIMESTAMP |
1384 +---------------+------------------------------------+
1385 type_info
1387 @type_info = $dbh->type_info($data_type);
1389 Returns a list of hash references holding information about one or more
1391 variants of $data_type. See the DBI documentation for more details.
1392 pg_server_trace
1394 $dbh->pg_server_trace($filehandle);
1396 Writes debugging information from the PostgreSQL backend to a file.
1398 This is not related to the DBI "trace" method and you should not use
1399 this method unless you know what you are doing. If you do enable this,
1400 be aware that the file will grow very large, very quick. To stop
1401 logging to the file, use the "pg_server_untrace" method. The first
1402 argument must be a file handle, not a filename. Example:
1403 my $pid = $dbh->{pg_pid};
1405 my $file = "pgbackend.$pid.debug.log";
1406 open(my $fh, ">$file") or die qq{Could not open "$file": $!\n};
1407 $dbh->pg_server_trace($fh);
1408 ## Run code you want to trace here
1409 $dbh->pg_server_untrace;
1410 close($fh);
1411 pg_server_untrace
1413 $dbh->pg_server_untrace;
1415 Stop server logging to a previously opened file.
1417 selectrow_array
1419 @row_ary = $dbh->selectrow_array($sql);
1421 @row_ary = $dbh->selectrow_array($sql, \%attr);
1422 @row_ary = $dbh->selectrow_array($sql, \%attr, @bind_values);
1423 Returns an array of row information after preparing and executing the
1425 provided SQL string. The rows are returned by calling "fetchrow_array".
1426 The string can also be a statement handle generated by a previous
1427 prepare. Note that only the first row of data is returned. If called in
1428 a scalar context, only the first column of the first row is returned.
1429 Because this is not portable, it is not recommended that you use this
1430 method in that way.
1431 selectrow_arrayref
1433 $ary_ref = $dbh->selectrow_arrayref($statement);
1435 $ary_ref = $dbh->selectrow_arrayref($statement, \%attr);
1436 $ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @bind_values);
1437 Exactly the same as "selectrow_array", except that it returns a
1439 reference to an array, by internal use of the "fetchrow_arrayref"
1440 method.
1441 selectrow_hashref
1443 $hash_ref = $dbh->selectrow_hashref($sql);
1445 $hash_ref = $dbh->selectrow_hashref($sql, \%attr);
1446 $hash_ref = $dbh->selectrow_hashref($sql, \%attr, @bind_values);
1447 Exactly the same as "selectrow_array", except that it returns a
1449 reference to an hash, by internal use of the "fetchrow_hashref" method.
1450 clone
1452 $other_dbh = $dbh->clone();
1454 Creates a copy of the database handle by connecting with the same
1456 parameters as the original handle, then trying to merge the attributes.
1457 See the DBI documentation for complete usage.
1458 Database Handle Attributes
1460 AutoCommit (boolean)
1461 Supported by DBD::Pg as proposed by DBI. According to the
1463 classification of DBI, PostgreSQL is a database in which a transaction
1464 must be explicitly started. Without starting a transaction, every
1465 change to the database becomes immediately permanent. The default of
1466 AutoCommit is on, but this may change in the future, so it is highly
1467 recommended that you explicitly set it when calling "connect". For
1468 details see the notes about "Transactions" elsewhere in this document.
1469 pg_bool_tf (boolean)
1471 DBD::Pg specific attribute. If true, boolean values will be returned as
1473 the characters 't' and 'f' instead of '1' and '0'.
1474 ReadOnly (boolean)
1476 $dbh->{ReadOnly} = 1;
1478 Specifies if the current database connection should be in read-only
1480 mode or not. In this mode, changes that change the database are not
1481 allowed and will throw an error. Note: this method will not work if
1482 "AutoCommit" is true. The read-only effect is accomplished by sending a
1483 SET TRANSACTION READ ONLY after every begin. For more details, please
1484 see:
1485 http://www.postgresql.org/docs/current/interactive/sql-set-transaction.html
1487 Please not that this method is not foolproof: there are still ways to
1489 update the database. Consider this a safety net to catch applications
1490 that should not be issuing commands such as INSERT, UPDATE, or DELETE.
1491 This method requires DBI version 1.55 or better.
1493 pg_server_prepare (integer)
1495 DBD::Pg specific attribute. Indicates if DBD::Pg should attempt to use
1497 server-side prepared statements. The default value, 1, indicates that
1498 prepared statements should be used whenever possible. See the section
1499 on the "prepare" method for more information.
1500 pg_switch_prepared (integer)
1502 DBD::Pg specific attribute. Indicates when DBD::Pg will internally
1504 switch from using PQexecParams to PQexecPrepared. In other words, when
1505 it will start using server-side prepared statements (assuming all other
1506 requirements for them are met). The default value, 2, means that a
1507 prepared statement will be prepared and used the second and subsequent
1508 time execute is called. To always use PQexecPrepared instead of
1509 PQexecParams, set pg_switch_prepared to 1 (this was the default
1510 behavior in earlier versions). Setting pg_switch_prepared to 0 will
1511 force DBD::Pg to always use PQexecParams.
1512 pg_placeholder_dollaronly (boolean)
1514 DBD::Pg specific attribute. Defaults to false. When true, question
1516 marks inside of statements are not treated as placeholders. Useful for
1517 statements that contain unquoted question marks, such as geometric
1518 operators. Note that you may also simply escape question marks with a
1519 backslash to prevent them from being treated as placeholders.
1520 pg_placeholder_nocolons (boolean)
1522 DBD::Pg specific attribute. Defaults to false. When true, colons inside
1524 of statements are not treated as placeholders. Useful for statements
1525 that contain an array slice. You may also place a backslash directly
1526 before the colon to prevent it from being treated as a placeholder.
1527 pg_enable_utf8 (integer)
1529 DBD::Pg specific attribute. The behavior of DBD::Pg with regards to
1531 this flag has changed as of version 3.0.0. The default value for this
1532 attribute, -1, indicates that the internal Perl "utf8" flag will be
1533 turned on for all strings coming back from the database if the
1534 client_encoding is set to 'UTF8'. Use of this default is highly
1535 encouraged. If your code was previously using pg_enable_utf8, you can
1536 probably remove mention of it entirely.
1537 If this attribute is set to 0, then the internal "utf8" flag will
1539 *never* be turned on for returned data, regardless of the current
1540 client_encoding.
1541 If this attribute is set to 1, then the internal "utf8" flag will
1543 *always* be turned on for returned data, regardless of the current
1544 client_encoding (with the exception of bytea data).
1545 Note that the value of client_encoding is only checked on connection
1547 time. If you change the client_encoding to/from 'UTF8' after
1548 connecting, you can set pg_enable_utf8 to -1 to force DBD::Pg to read
1549 in the new client_encoding and act accordingly.
1550 pg_errorlevel (integer)
1552 DBD::Pg specific attribute. Sets the amount of information returned by
1554 the server's error messages. Valid entries are 0, 1, and 2. Any other
1555 number will be forced to the default value of 1.
1556 A value of 0 ("TERSE") will show severity, primary text, and position
1558 only and will usually fit on a single line. A value of 1 ("DEFAULT")
1559 will also show any detail, hint, or context fields. A value of 2
1560 ("VERBOSE") will show all available information.
1561 pg_lib_version (integer, read-only)
1563 DBD::Pg specific attribute. Indicates which version of PostgreSQL that
1565 DBD::Pg was compiled against. In other words, which libraries were
1566 used. Returns a number with major, minor, and revision together;
1567 version 8.1.4 would be returned as 80104.
1568 pg_server_version (integer, read-only)
1570 DBD::Pg specific attribute. Indicates which version of PostgreSQL that
1572 the current database handle is connected to. Returns a number with
1573 major, minor, and revision together; version 8.0.1 would be 80001.
1574 Name (string, read-only)
1576 Returns the name of the current database. This is the same as the DSN,
1578 without the "dbi:Pg:" part. Before version 2.0.0, this only returned
1579 the bare database name (e.g. 'foo'). From version 2.0.0 onwards, it
1580 returns the more correct output (e.g. 'dbname=foo')
1581 Username (string, read-only)
1583 Returns the name of the user connected to the database.
1585 pg_db (string, read-only)
1587 DBD::Pg specific attribute. Returns the name of the current database.
1589 pg_user (string, read-only)
1591 DBD::Pg specific attribute. Returns the name of the user that connected
1593 to the server.
1594 pg_host (string, read-only)
1596 DBD::Pg specific attribute. Returns the host of the current server
1598 connection. Locally connected hosts will return an empty string.
1599 pg_port (integer, read-only)
1601 DBD::Pg specific attribute. Returns the port of the connection to the
1603 server.
1604 pg_socket (integer, read-only)
1606 DBD::Pg specific attribute. Returns the file description number of the
1608 connection socket to the server.
1609 pg_pass (string, read-only)
1611 DBD::Pg specific attribute. Returns the password used to connect to the
1613 server.
1614 pg_options (string, read-only)
1616 DBD::Pg specific attribute. Returns the command-line options passed to
1618 the server. May be an empty string.
1619 pg_default_port (integer, read-only)
1621 DBD::Pg specific attribute. Returns the default port used if none is
1623 specifically given.
1624 pg_pid (integer, read-only)
1626 DBD::Pg specific attribute. Returns the process id (PID) of the backend
1628 server process handling the connection.
1629 pg_prepare_now (boolean)
1631 DBD::Pg specific attribute. Default is off. If true, then the "prepare"
1633 method will immediately prepare commands, rather than waiting until the
1634 first execute.
1635 pg_expand_array (boolean)
1637 DBD::Pg specific attribute. Defaults to true. If false, arrays returned
1639 from the server will not be changed into a Perl arrayref, but remain as
1640 a string.
1641 pg_async_status (integer, read-only)
1643 DBD::Pg specific attribute. Returns the current status of an
1645 asynchronous command. 0 indicates no asynchronous command is in
1646 progress, 1 indicates that an asynchronous command has started and -1
1647 indicated that an asynchronous command has been cancelled.
1648 pg_standard_conforming_strings (boolean, read-only)
1650 DBD::Pg specific attribute. Returns true if the server is currently
1652 using standard conforming strings. Only available if the target server
1653 is version 8.2 or better.
1654 pg_INV_READ (integer, read-only)
1656 Constant to be used for the mode in "pg_lo_creat" and "pg_lo_open".
1658 pg_INV_WRITE (integer, read-only)
1660 Constant to be used for the mode in "pg_lo_creat" and "pg_lo_open".
1662 Driver (handle, read-only)
1664 Holds the handle of the parent driver. The only recommended use for
1666 this is to find the name of the driver using:
1667 $dbh->{Driver}->{Name}
1669 pg_protocol (integer, read-only)
1671 DBD::Pg specific attribute. Returns the version of the PostgreSQL
1673 server. If DBD::Pg is unable to figure out the version, it will return
1674 a "0". Otherwise, a "3" is returned.
1675 RowCacheSize
1677 Not used by DBD::Pg
1679
1681 Statement Handle Methods
1682 bind_param
1683 $rv = $sth->bind_param($param_num, $bind_value);
1685 $rv = $sth->bind_param($param_num, $bind_value, $bind_type);
1686 $rv = $sth->bind_param($param_num, $bind_value, \%attr);
1687 Allows the user to bind a value and/or a data type to a placeholder.
1689 This is especially important when using server-side prepares. See the
1690 "prepare" method for more information.
1691 The value of $param_num is a number if using the '?' or '$1' style
1693 placeholders. If using ":foo" style placeholders, the complete name
1694 (e.g. ":foo") must be given. For numeric values, you can either use a
1695 number or use a literal '$1'. See the examples below.
1696 The $bind_value argument is fairly self-explanatory. A value of "undef"
1698 will bind a "NULL" to the placeholder. Using "undef" is useful when you
1699 want to change just the type and will be overwriting the value later.
1700 (Any value is actually usable, but "undef" is easy and efficient).
1701 The "\%attr" hash is used to indicate the data type of the placeholder.
1703 The default value is "varchar". If you need something else, you must
1704 use one of the values provided by DBI or by DBD::Pg. To use a SQL
1705 value, modify your "use DBI" statement at the top of your script as
1706 follows:
1707 use DBI qw(:sql_types);
1709 This will import some constants into your script. You can plug those
1711 directly into the "bind_param" call. Some common ones that you will
1712 encounter are:
1713 SQL_INTEGER
1715 To use PostgreSQL data types, import the list of values like this:
1717 use DBD::Pg qw(:pg_types);
1719 You can then set the data types by setting the value of the "pg_type"
1721 key in the hash passed to "bind_param". The current list of Postgres
1722 data types exported is:
1723 PG_ABSTIME PG_ABSTIMEARRAY PG_ACLITEM PG_ACLITEMARRAY PG_ANY PG_ANYARRAY
1725 PG_ANYELEMENT PG_ANYENUM PG_ANYNONARRAY PG_ANYRANGE PG_BIT PG_BITARRAY
1726 PG_BOOL PG_BOOLARRAY PG_BOX PG_BOXARRAY PG_BPCHAR PG_BPCHARARRAY
1727 PG_BYTEA PG_BYTEAARRAY PG_CHAR PG_CHARARRAY PG_CID PG_CIDARRAY
1728 PG_CIDR PG_CIDRARRAY PG_CIRCLE PG_CIRCLEARRAY PG_CSTRING PG_CSTRINGARRAY
1729 PG_DATE PG_DATEARRAY PG_DATERANGE PG_DATERANGEARRAY PG_EVENT_TRIGGER PG_FDW_HANDLER
1730 PG_FLOAT4 PG_FLOAT4ARRAY PG_FLOAT8 PG_FLOAT8ARRAY PG_GTSVECTOR PG_GTSVECTORARRAY
1731 PG_INDEX_AM_HANDLER PG_INET PG_INETARRAY PG_INT2 PG_INT2ARRAY PG_INT2VECTOR
1732 PG_INT2VECTORARRAY PG_INT4 PG_INT4ARRAY PG_INT4RANGE PG_INT4RANGEARRAY PG_INT8
1733 PG_INT8ARRAY PG_INT8RANGE PG_INT8RANGEARRAY PG_INTERNAL PG_INTERVAL PG_INTERVALARRAY
1734 PG_JSON PG_JSONARRAY PG_JSONB PG_JSONBARRAY PG_LANGUAGE_HANDLER PG_LINE
1735 PG_LINEARRAY PG_LSEG PG_LSEGARRAY PG_MACADDR PG_MACADDR8 PG_MACADDR8ARRAY
1736 PG_MACADDRARRAY PG_MONEY PG_MONEYARRAY PG_NAME PG_NAMEARRAY PG_NUMERIC
1737 PG_NUMERICARRAY PG_NUMRANGE PG_NUMRANGEARRAY PG_OID PG_OIDARRAY PG_OIDVECTOR
1738 PG_OIDVECTORARRAY PG_OPAQUE PG_PATH PG_PATHARRAY PG_PG_ATTRIBUTE PG_PG_CLASS
1739 PG_PG_DDL_COMMAND PG_PG_DEPENDENCIES PG_PG_LSN PG_PG_LSNARRAY PG_PG_NDISTINCT PG_PG_NODE_TREE
1740 PG_PG_PROC PG_PG_TYPE PG_POINT PG_POINTARRAY PG_POLYGON PG_POLYGONARRAY
1741 PG_RECORD PG_RECORDARRAY PG_REFCURSOR PG_REFCURSORARRAY PG_REGCLASS PG_REGCLASSARRAY
1742 PG_REGCONFIG PG_REGCONFIGARRAY PG_REGDICTIONARY PG_REGDICTIONARYARRAY PG_REGNAMESPACE PG_REGNAMESPACEARRAY
1743 PG_REGOPER PG_REGOPERARRAY PG_REGOPERATOR PG_REGOPERATORARRAY PG_REGPROC PG_REGPROCARRAY
1744 PG_REGPROCEDURE PG_REGPROCEDUREARRAY PG_REGROLE PG_REGROLEARRAY PG_REGTYPE PG_REGTYPEARRAY
1745 PG_RELTIME PG_RELTIMEARRAY PG_SMGR PG_TEXT PG_TEXTARRAY PG_TID
1746 PG_TIDARRAY PG_TIME PG_TIMEARRAY PG_TIMESTAMP PG_TIMESTAMPARRAY PG_TIMESTAMPTZ
1747 PG_TIMESTAMPTZARRAY PG_TIMETZ PG_TIMETZARRAY PG_TINTERVAL PG_TINTERVALARRAY PG_TRIGGER
1748 PG_TSM_HANDLER PG_TSQUERY PG_TSQUERYARRAY PG_TSRANGE PG_TSRANGEARRAY PG_TSTZRANGE
1749 PG_TSTZRANGEARRAY PG_TSVECTOR PG_TSVECTORARRAY PG_TXID_SNAPSHOT PG_TXID_SNAPSHOTARRAY PG_UNKNOWN
1750 PG_UUID PG_UUIDARRAY PG_VARBIT PG_VARBITARRAY PG_VARCHAR PG_VARCHARARRAY
1751 PG_VOID PG_XID PG_XIDARRAY PG_XML PG_XMLARRAY
1752 Data types are "sticky," in that once a data type is set to a certain
1754 placeholder, it will remain for that placeholder, unless it is
1755 explicitly set to something else afterwards. If the statement has
1756 already been prepared, and you switch the data type to something else,
1757 DBD::Pg will re-prepare the statement for you before doing the next
1758 execute.
1759 Examples:
1761 use DBI qw(:sql_types);
1763 use DBD::Pg qw(:pg_types);
1764 $SQL = "SELECT id FROM ptable WHERE size > ? AND title = ?";
1766 $sth = $dbh->prepare($SQL);
1767 ## Both arguments below are bound to placeholders as "varchar"
1769 $sth->execute(123, "Merk");
1770 ## Reset the datatype for the first placeholder to an integer
1772 $sth->bind_param(1, undef, SQL_INTEGER);
1773 ## The "undef" bound above is not used, since we supply params to execute
1775 $sth->execute(123, "Merk");
1776 ## Set the first placeholder's value and data type
1778 $sth->bind_param(1, 234, { pg_type => PG_TIMESTAMP });
1779 ## Set the second placeholder's value and data type.
1781 ## We don't send a third argument, so the default "varchar" is used
1782 $sth->bind_param('$2', "Zool");
1783 ## We realize that the wrong data type was set above, so we change it:
1785 $sth->bind_param('$1', 234, { pg_type => SQL_INTEGER });
1786 ## We also got the wrong value, so we change that as well.
1788 ## Because the data type is sticky, we don't need to change it
1789 $sth->bind_param(1, 567);
1790 ## This executes the statement with 567 (integer) and "Zool" (varchar)
1792 $sth->execute();
1793 bind_param_inout
1795 $rv = $sth->bind_param_inout($param_num, \$scalar, 0);
1797 Experimental support for this feature is provided. The first argument
1799 to bind_param_inout should be a placeholder number. The second argument
1800 should be a reference to a scalar variable in your script. The third
1801 argument is not used and should simply be set to 0. Note that what this
1802 really does is assign a returned column to the variable, in the order
1803 in which the column appears. For example:
1804 my $foo = 123;
1806 $sth = $dbh->prepare("SELECT 1+?::int");
1807 $sth->bind_param_inout(1, \$foo, 0);
1808 $foo = 222;
1809 $sth->execute(444);
1810 $sth->fetch;
1811 The above will cause $foo to have a new value of "223" after the final
1813 fetch. Note that the variables bound in this manner are very sticky,
1814 and will trump any values passed in to execute. This is because the
1815 binding is done as late as possible, at the execute() stage, allowing
1816 the value to be changed between the time it was bound and the time the
1817 query is executed. Thus, the above execute is the same as:
1818 $sth->execute();
1820 bind_param_array
1822 $rv = $sth->bind_param_array($param_num, $array_ref_or_value)
1824 $rv = $sth->bind_param_array($param_num, $array_ref_or_value, $bind_type)
1825 $rv = $sth->bind_param_array($param_num, $array_ref_or_value, \%attr)
1826 Binds an array of values to a placeholder, so that each is used in turn
1828 by a call to the "execute_array" method.
1829 execute
1831 $rv = $sth->execute(@bind_values);
1833 Executes a previously prepared statement. In addition to "UPDATE",
1835 "DELETE", "INSERT" statements, for which it returns always the number
1836 of affected rows, the "execute" method can also be used for "SELECT ...
1837 INTO table" statements.
1838 The "prepare/bind/execute" process has changed significantly for
1840 PostgreSQL servers 7.4 and later: please see the "prepare()" and
1841 "bind_param()" entries for much more information.
1842 Setting one of the bind_values to "undef" is the equivalent of setting
1844 the value to NULL in the database. Setting the bind_value to
1845 $DBDPG_DEFAULT is equivalent to sending the literal string 'DEFAULT' to
1846 the backend. Note that using this option will force server-side
1847 prepares off until such time as PostgreSQL supports using DEFAULT in
1848 prepared statements.
1849 DBD::Pg also supports passing in arrays to execute: simply pass in an
1851 arrayref, and DBD::Pg will flatten it into a string suitable for input
1852 on the backend.
1853 If you are using Postgres version 8.2 or greater, you can also use any
1855 of the fetch methods to retrieve the values of a "RETURNING" clause
1856 after you execute an "UPDATE", "DELETE", or "INSERT". For example:
1857 $dbh->do(q{CREATE TABLE abc (id SERIAL, country TEXT)});
1859 $SQL = q{INSERT INTO abc (country) VALUES (?) RETURNING id};
1860 $sth = $dbh->prepare($SQL);
1861 $sth->execute('France');
1862 $countryid = $sth->fetch()->[0];
1863 $sth->execute('New Zealand');
1864 $countryid = $sth->fetch()->[0];
1865 execute_array
1867 $tuples = $sth->execute_array() or die $sth->errstr;
1869 $tuples = $sth->execute_array(\%attr) or die $sth->errstr;
1870 $tuples = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
1871 ($tuples, $rows) = $sth->execute_array(\%attr) or die $sth->errstr;
1873 ($tuples, $rows) = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
1874 Execute a prepared statement once for each item in a passed-in hashref,
1876 or items that were previously bound via the "bind_param_array" method.
1877 See the DBI documentation for more details.
1878 execute_for_fetch
1880 $tuples = $sth->execute_for_fetch($fetch_tuple_sub);
1882 $tuples = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
1883 ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub);
1885 ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
1886 Used internally by the "execute_array" method, and rarely used
1888 directly. See the DBI documentation for more details.
1889 fetchrow_arrayref
1891 $ary_ref = $sth->fetchrow_arrayref;
1893 Fetches the next row of data from the statement handle, and returns a
1895 reference to an array holding the column values. Any columns that are
1896 NULL are returned as undef within the array.
1897 If there are no more rows or if an error occurs, the this method return
1899 undef. You should check "$sth->err" afterwards (or use the RaiseError
1900 attribute) to discover if the undef returned was due to an error.
1901 Note that the same array reference is returned for each fetch, so don't
1903 store the reference and then use it after a later fetch. Also, the
1904 elements of the array are also reused for each row, so take care if you
1905 want to take a reference to an element. See also "bind_columns".
1906 fetchrow_array
1908 @ary = $sth->fetchrow_array;
1910 Similar to the "fetchrow_arrayref" method, but returns a list of column
1912 information rather than a reference to a list. Do not use this in a
1913 scalar context.
1914 fetchrow_hashref
1916 $hash_ref = $sth->fetchrow_hashref;
1918 $hash_ref = $sth->fetchrow_hashref($name);
1919 Fetches the next row of data and returns a hashref containing the name
1921 of the columns as the keys and the data itself as the values. Any NULL
1922 value is returned as an undef value.
1923 If there are no more rows or if an error occurs, the this method return
1925 undef. You should check "$sth->err" afterwards (or use the RaiseError
1926 attribute) to discover if the undef returned was due to an error.
1927 The optional $name argument should be either "NAME", "NAME_lc" or
1929 "NAME_uc", and indicates what sort of transformation to make to the
1930 keys in the hash.
1931 fetchall_arrayref
1933 $tbl_ary_ref = $sth->fetchall_arrayref();
1935 $tbl_ary_ref = $sth->fetchall_arrayref( $slice );
1936 $tbl_ary_ref = $sth->fetchall_arrayref( $slice, $max_rows );
1937 Returns a reference to an array of arrays that contains all the
1939 remaining rows to be fetched from the statement handle. If there are no
1940 more rows, an empty arrayref will be returned. If an error occurs, the
1941 data read in so far will be returned. Because of this, you should
1942 always check "$sth->err" after calling this method, unless RaiseError
1943 has been enabled.
1944 If $slice is an array reference, fetchall_arrayref uses the
1946 "fetchrow_arrayref" method to fetch each row as an array ref. If the
1947 $slice array is not empty then it is used as a slice to select
1948 individual columns by perl array index number (starting at 0, unlike
1949 column and parameter numbers which start at 1).
1950 With no parameters, or if $slice is undefined, fetchall_arrayref acts
1952 as if passed an empty array ref.
1953 If $slice is a hash reference, fetchall_arrayref uses
1955 "fetchrow_hashref" to fetch each row as a hash reference.
1956 See the DBI documentation for a complete discussion.
1958 fetchall_hashref
1960 $hash_ref = $sth->fetchall_hashref( $key_field );
1962 Returns a hashref containing all rows to be fetched from the statement
1964 handle. See the DBI documentation for a full discussion.
1965 finish
1967 $rv = $sth->finish;
1969 Indicates to DBI that you are finished with the statement handle and
1971 are not going to use it again. Only needed when you have not fetched
1972 all the possible rows.
1973 rows
1975 $rv = $sth->rows;
1977 Returns the number of rows returned by the last query. In contrast to
1979 many other DBD modules, the number of rows is available immediately
1980 after calling "$sth->execute". Note that the "execute" method itself
1981 returns the number of rows itself, which means that this method is
1982 rarely needed.
1983 bind_col
1985 $rv = $sth->bind_col($column_number, \$var_to_bind);
1987 $rv = $sth->bind_col($column_number, \$var_to_bind, \%attr );
1988 $rv = $sth->bind_col($column_number, \$var_to_bind, $bind_type );
1989 Binds a Perl variable and/or some attributes to an output column of a
1991 SELECT statement. Column numbers count up from 1. You do not need to
1992 bind output columns in order to fetch data.
1993 See the DBI documentation for a discussion of the optional parameters
1995 "\%attr" and $bind_type
1996 bind_columns
1998 $rv = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
2000 Calls the "bind_col" method for each column in the SELECT statement,
2002 using the supplied list.
2003 dump_results
2005 $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
2007 Fetches all the rows from the statement handle, calls "DBI::neat_list"
2009 for each row, and prints the results to $fh (which defaults to STDOUT).
2010 Rows are separated by $lsep (which defaults to a newline). Columns are
2011 separated by $fsep (which defaults to a comma). The $maxlen controls
2012 how wide the output can be, and defaults to 35.
2013 This method is designed as a handy utility for prototyping and testing
2015 queries. Since it uses "neat_list" to format and edit the string for
2016 reading by humans, it is not recommended for data transfer
2017 applications.
2018 blob_read
2020 $blob = $sth->blob_read($id, $offset, $len);
2022 Supported by DBD::Pg. This method is implemented by DBI but not
2024 currently documented by DBI, so this method might change.
2025 This method seems to be heavily influenced by the current
2027 implementation of blobs in Oracle. Nevertheless we try to be as
2028 compatible as possible. Whereas Oracle suffers from the limitation that
2029 blobs are related to tables and every table can have only one blob
2030 (datatype LONG), PostgreSQL handles its blobs independent of any table
2031 by using so-called object identifiers. This explains why the
2032 "blob_read" method is blessed into the STATEMENT package and not part
2033 of the DATABASE package. Here the field parameter has been used to
2034 handle this object identifier. The offset and len parameters may be set
2035 to zero, in which case the whole blob is fetched at once.
2036 See also the PostgreSQL-specific functions concerning blobs, which are
2038 available via the "func" interface.
2039 For further information and examples about blobs, please read the
2041 chapter about Large Objects in the PostgreSQL Programmer's Guide at
2042 <http://www.postgresql.org/docs/current/static/largeobjects.html>.
2043 pg_canonical_ids
2045 $data = $sth->pg_canonical_ids;
2047 DBD::Pg specific method. It returns Oid of table and position in table
2049 for every column in result set.
2050 Returns array of arrays with Table Oid and Column Position for every
2052 column in result set or undef if current column is not a simple
2053 reference.
2054 pg_canonical_names
2056 $data = $sth->pg_canonical_names;
2058 DBD::Pg specific method. It returns array of original (or canonical)
2060 names (from where this data is actually came from) of columns in
2061 Schema.Table.Column format or undef if current column is not a simple
2062 reference.
2063 Note that this method is quite slow because it need additional
2065 information from server for every column that is simple reference.
2066 Consider to use "pg_canonical_ids" instead.
2067 Statement Handle Attributes
2069 NUM_OF_FIELDS (integer, read-only)
2070 Returns the number of columns returned by the current statement. A
2072 number will only be returned for SELECT statements, for SHOW statements
2073 (which always return 1), and for INSERT, UPDATE, and DELETE statements
2074 which contain a RETURNING clause. This method returns undef if called
2075 before "execute()".
2076 NUM_OF_PARAMS (integer, read-only)
2078 Returns the number of placeholders in the current statement.
2080 NAME (arrayref, read-only)
2082 Returns an arrayref of column names for the current statement. This
2084 method will only work for SELECT statements, for SHOW statements, and
2085 for INSERT, UPDATE, and DELETE statements which contain a RETURNING
2086 clause. This method returns undef if called before "execute()".
2087 NAME_lc (arrayref, read-only)
2089 The same as the "NAME" attribute, except that all column names are
2091 forced to lower case.
2092 NAME_uc (arrayref, read-only)
2094 The same as the "NAME" attribute, except that all column names are
2096 forced to upper case.
2097 NAME_hash (hashref, read-only)
2099 Similar to the "NAME" attribute, but returns a hashref of column names
2101 instead of an arrayref. The names of the columns are the keys of the
2102 hash, and the values represent the order in which the columns are
2103 returned, starting at 0. This method returns undef if called before
2104 "execute()".
2105 NAME_lc_hash (hashref, read-only)
2107 The same as the "NAME_hash" attribute, except that all column names are
2109 forced to lower case.
2110 NAME_uc_hash (hashref, read-only)
2112 The same as the "NAME_hash" attribute, except that all column names are
2114 forced to lower case.
2115 TYPE (arrayref, read-only)
2117 Returns an arrayref indicating the data type for each column in the
2119 statement. This method returns undef if called before "execute()".
2120 PRECISION (arrayref, read-only)
2122 Returns an arrayref of integer values for each column returned by the
2124 statement. The number indicates the precision for "NUMERIC" columns,
2125 the size in number of characters for "CHAR" and "VARCHAR" columns, and
2126 for all other types of columns it returns the number of bytes. This
2127 method returns undef if called before "execute()".
2128 SCALE (arrayref, read-only)
2130 Returns an arrayref of integer values for each column returned by the
2132 statement. The number indicates the scale of the that column. The only
2133 type that will return a value is "NUMERIC". This method returns undef
2134 if called before "execute()".
2135 NULLABLE (arrayref, read-only)
2137 Returns an arrayref of integer values for each column returned by the
2139 statement. The number indicates if the column is nullable or not. 0 =
2140 not nullable, 1 = nullable, 2 = unknown. This method returns undef if
2141 called before "execute()".
2142 Database (dbh, read-only)
2144 Returns the database handle this statement handle was created from.
2146 ParamValues (hash ref, read-only)
2148 Returns a reference to a hash containing the values currently bound to
2150 placeholders. If the "named parameters" type of placeholders are being
2151 used (such as ":foo"), then the keys of the hash will be the names of
2152 the placeholders (without the colon). If the "dollar sign numbers" type
2153 of placeholders are being used, the keys of the hash will be the
2154 numbers, without the dollar signs. If the "question mark" type is used,
2155 integer numbers will be returned, starting at one and increasing for
2156 every placeholder.
2157 If this method is called before "execute", the literal values passed in
2159 are returned. If called after "execute", then the quoted versions of
2160 the values are returned.
2161 ParamTypes (hash ref, read-only)
2163 Returns a reference to a hash containing the type names currently bound
2165 to placeholders. The keys are the same as returned by the ParamValues
2166 method. The values are hashrefs containing a single key value pair, in
2167 which the key is either 'TYPE' if the type has a generic SQL
2168 equivalent, and 'pg_type' if the type can only be expressed by a
2169 Postgres type. The value is the internal number corresponding to the
2170 type originally passed in. (Placeholders that have not yet been bound
2171 will return undef as the value). This allows the output of ParamTypes
2172 to be passed back to the "bind_param" method.
2173 Statement (string, read-only)
2175 Returns the statement string passed to the most recent "prepare" method
2177 called in this database handle, even if that method failed. This is
2178 especially useful where "RaiseError" is enabled and the exception
2179 handler checks $@ and sees that a "prepare" method call failed.
2180 pg_current_row (integer, read-only)
2182 DBD::Pg specific attribute. Returns the number of the tuple (row) that
2184 was most recently fetched. Returns zero before and after fetching is
2185 performed.
2186 pg_numbound (integer, read-only)
2188 DBD::Pg specific attribute. Returns the number of placeholders that are
2190 currently bound (via bind_param).
2191 pg_bound (hashref, read-only)
2193 DBD::Pg specific attribute. Returns a hash of all named placeholders.
2195 The key is the name of the placeholder, and the value is a 0 or a 1,
2196 indicating if the placeholder has been bound yet (e.g. via bind_param)
2197 pg_size (arrayref, read-only)
2199 DBD::Pg specific attribute. It returns a reference to an array of
2201 integer values for each column. The integer shows the size of the
2202 column in bytes. Variable length columns are indicated by -1.
2203 pg_type (arrayref, read-only)
2205 DBD::Pg specific attribute. It returns a reference to an array of
2207 strings for each column. The string shows the name of the data_type.
2208 pg_segments (arrayref, read-only)
2210 DBD::Pg specific attribute. Returns an arrayref of the query split on
2212 the placeholders.
2213 pg_oid_status (integer, read-only)
2215 DBD::Pg specific attribute. It returns the OID of the last INSERT
2217 command.
2218 pg_cmd_status (integer, read-only)
2220 DBD::Pg specific attribute. It returns the type of the last command.
2222 Possible types are: "INSERT", "DELETE", "UPDATE", "SELECT".
2223 pg_direct (boolean)
2225 DBD::Pg specific attribute. Default is false. If true, the query is
2227 passed directly to the backend without parsing for placeholders.
2228 pg_prepare_now (boolean)
2230 DBD::Pg specific attribute. Default is off. If true, the query will be
2232 immediately prepared, rather than waiting for the "execute" call.
2233 pg_prepare_name (string)
2235 DBD::Pg specific attribute. Specifies the name of the prepared
2237 statement to use for this statement handle. Not normally needed, see
2238 the section on the "prepare" method for more information.
2239 pg_server_prepare (integer)
2241 DBD::Pg specific attribute. Indicates if DBD::Pg should attempt to use
2243 server-side prepared statements for this statement handle. The default
2244 value, 1, indicates that prepared statements should be used whenever
2245 possible. See the section on the "prepare" method for more information.
2246 pg_switch_prepared (integer)
2248 DBD::Pg specific attribute. Indicates when DBD::Pg will internally
2250 switch from using PQexecParams to PQexecPrepared. In other words, when
2251 it will start using server-side prepared statements (assuming all other
2252 requirements for them are met). The default value, 2, means that a
2253 prepared statement will be prepared and used the second and subsequent
2254 time execute is called. To always use PQexecPrepared instead of
2255 PQexecParams, set pg_switch_prepared to 1 (this was the default
2256 behavior in earlier versions). Setting pg_switch_prepared to 0 will
2257 force DBD::Pg to always use PQexecParams.
2258 pg_placeholder_dollaronly (boolean)
2260 DBD::Pg specific attribute. Defaults to false. When true, question
2262 marks inside of the query being prepared are not treated as
2263 placeholders. Useful for statements that contain unquoted question
2264 marks, such as geometric operators. Note that you may also simply
2265 escape question marks with a backslash to prevent them from being
2266 treated as placeholders.
2267 pg_placeholder_nocolons (boolean)
2269 DBD::Pg specific attribute. Defaults to false. When true, colons inside
2271 of statements are not treated as placeholders. Useful for statements
2272 that contain an array slice. You may also place a backslash directly
2273 before the colon to prevent it from being treated as a placeholder.
2274 pg_async (integer)
2276 DBD::Pg specific attribute. Indicates the current behavior for
2278 asynchronous queries. See the section on "Asynchronous Constants" for
2279 more information.
2280 pg_async_status (integer, read-only)
2282 DBD::Pg specific attribute. Returns the current status of an
2284 asynchronous command. 0 indicates no asynchronous command is in
2285 progress, 1 indicates that an asynchronous command has started and -1
2286 indicated that an asynchronous command has been cancelled.
2287 RowsInCache
2289 Not used by DBD::Pg
2291 RowCache
2293 Not used by DBD::Pg
2295 CursorName
2297 Not used by DBD::Pg. See the note about "Cursors" elsewhere in this
2299 document.
2300
2302 Encoding
2303 DBD::Pg has extensive support for a client_encoding of UTF-8, and most
2304 things like encoding and decoding should happen automatically. If you
2305 are using a different encoding, you will need do the encoding and
2306 decoding yourself. For this reason, it is highly recommended to always
2307 use a client_encoding of UTF-8. The server_encoding can be anything,
2308 and no recommendations are made there, other than avoid SQL_ASCII
2309 whenever possible.
2310 Transactions
2312 Transaction behavior is controlled via the "AutoCommit" attribute. For
2313 a complete definition of "AutoCommit" please refer to the DBI
2314 documentation.
2315 According to the DBI specification the default for "AutoCommit" is a
2317 true value. In this mode, any change to the database becomes valid
2318 immediately. Any "BEGIN", "COMMIT" or "ROLLBACK" statements will be
2319 rejected. Note that preparing a statement does not always contact the
2320 server, as the actual "PREPARE" is usually postponed until the first
2321 call to "execute".
2322 Savepoints
2324 PostgreSQL version 8.0 introduced the concept of savepoints, which
2325 allows transactions to be rolled back to a certain point without
2326 affecting the rest of the transaction. DBD::Pg encourages using the
2327 following methods to control savepoints:
2328 "pg_savepoint"
2330 Creates a savepoint. This will fail unless you are inside of a
2332 transaction. The only argument is the name of the savepoint. Note that
2333 PostgreSQL DOES allow multiple savepoints with the same name to exist.
2334 $dbh->pg_savepoint("mysavepoint");
2336 "pg_rollback_to"
2338 Rolls the database back to a named savepoint, discarding any work
2340 performed after that point. If more than one savepoint with that name
2341 exists, rolls back to the most recently created one.
2342 $dbh->pg_rollback_to("mysavepoint");
2344 "pg_release"
2346 Releases (or removes) a named savepoint. If more than one savepoint
2348 with that name exists, it will only destroy the most recently created
2349 one. Note that all savepoints created after the one being released are
2350 also destroyed.
2351 $dbh->pg_release("mysavepoint");
2353 Asynchronous Queries
2355 It is possible to send a query to the backend and have your script do
2356 other work while the query is running on the backend. Both queries sent
2357 by the "do" method, and by the "execute" method can be sent
2358 asynchronously. (NOTE: This will only work if DBD::Pg has been compiled
2359 against Postgres libraries of version 8.0 or greater) The basic usage
2360 is as follows:
2361 use DBD::Pg ':async';
2363 print "Async do() example:\n";
2365 $dbh->do("SELECT long_running_query()", {pg_async => PG_ASYNC});
2366 do_something_else();
2367 {
2368 if ($dbh->pg_ready()) {
2369 $res = $dbh->pg_result();
2370 print "Result of do(): $res\n";
2371 }
2372 print "Query is still running...\n";
2373 if (cancel_request_received) {
2374 $dbh->pg_cancel();
2375 }
2376 sleep 1;
2377 redo;
2378 }
2379 print "Async prepare/execute example:\n";
2381 $sth = $dbh->prepare("SELECT long_running_query(1)", {pg_async => PG_ASYNC});
2382 $sth->execute();
2383 ## Changed our mind, cancel and run again:
2385 $sth = $dbh->prepare("SELECT 678", {pg_async => PG_ASYNC + PG_OLDQUERY_CANCEL});
2386 $sth->execute();
2387 do_something_else();
2389 if (!$sth->pg_ready) {
2391 do_another_thing();
2392 }
2393 ## We wait until it is done, and get the result:
2395 $res = $dbh->pg_result();
2396 Asynchronous Constants
2398 There are currently three asynchronous constants exported by DBD::Pg.
2400 You can import all of them by putting either of these at the top of
2401 your script:
2402 use DBD::Pg;
2404 use DBD::Pg ':async';
2406 You may also use the numbers instead of the constants, but using the
2408 constants is recommended as it makes your script more readable.
2409 PG_ASYNC
2411 This is a constant for the number 1. It is passed to either the
2412 "do" or the "prepare" method as a value to the pg_async key and
2413 indicates that the query should be sent asynchronously.
2414 PG_OLDQUERY_CANCEL
2416 This is a constant for the number 2. When passed to either the "do"
2417 or the "prepare" method, it causes any currently running
2418 asynchronous query to be cancelled and rolled back. It has no
2419 effect if no asynchronous query is currently running.
2420 PG_OLDQUERY_WAIT
2422 This is a constant for the number 4. When passed to either the "do"
2423 or the "prepare" method, it waits for any currently running
2424 asynchronous query to complete. It has no effect if there is no
2425 asynchronous query currently running.
2426 Asynchronous Methods
2428 pg_cancel
2430 This database-level method attempts to cancel any currently running
2431 asynchronous query. It returns true if the cancel succeeded, and
2432 false otherwise. Note that a query that has finished before this
2433 method is executed will also return false. WARNING: a successful
2434 cancellation may leave the database in an unusable state, so you
2435 may need to ROLLBACK or ROLLBACK TO a savepoint. As of version
2436 2.17.0 of DBD::Pg, rollbacks are not done automatically.
2437 $result = $dbh->pg_cancel();
2439 pg_ready
2441 This method can be called as a database handle method or (for
2442 convenience) as a statement handle method. Both simply see if a
2443 previously issued asynchronous query has completed yet. It returns
2444 true if the statement has finished, in which case you should then
2445 call the "pg_result" method. Calls to "pg_ready()" should only be
2446 used when you have other things to do while the query is running.
2447 If you simply want to wait until the query is done, do not call
2448 pg_ready() over and over, but simply call the pg_result() method.
2449 my $time = 0;
2451 while (!$dbh->pg_ready) {
2452 print "Query is still running. Seconds: $time\n";
2453 $time++;
2454 sleep 1;
2455 }
2456 $result = $dbh->pg_result;
2457 pg_result
2459 This database handle method returns the results of a previously
2460 issued asynchronous query. If the query is still running, this
2461 method will wait until it has finished. The result returned is the
2462 number of rows: the same thing that would have been returned by the
2463 asynchronous "do" or "execute" if it had been called without an
2464 asynchronous flag.
2465 $result = $dbh->pg_result;
2467 Asynchronous Examples
2469 Here are some working examples of asynchronous queries. Note that we'll
2471 use the pg_sleep function to emulate a long-running query.
2472 use strict;
2474 use warnings;
2475 use Time::HiRes 'sleep';
2476 use DBD::Pg ':async';
2477 my $dbh = DBI->connect('dbi:Pg:dbname=postgres', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
2479 ## Kick off a long running query on the first database:
2481 my $sth = $dbh->prepare("SELECT pg_sleep(?)", {pg_async => PG_ASYNC});
2482 $sth->execute(5);
2483 ## While that is running, do some other things
2485 print "Your query is processing. Thanks for waiting\n";
2486 check_on_the_kids(); ## Expensive sub, takes at least three seconds.
2487 while (!$dbh->pg_ready) {
2489 check_on_the_kids();
2490 ## If the above function returns quickly for some reason, we add a small sleep
2491 sleep 0.1;
2492 }
2493 print "The query has finished. Gathering results\n";
2495 my $result = $sth->pg_result;
2496 print "Result: $result\n";
2497 my $info = $sth->fetchall_arrayref();
2498 Without asynchronous queries, the above script would take about 8
2500 seconds to run: five seconds waiting for the execute to finish, then
2501 three for the check_on_the_kids() function to return. With asynchronous
2502 queries, the script takes about 6 seconds to run, and gets in two
2503 iterations of check_on_the_kids in the process.
2504 Here's an example showing the ability to cancel a long-running query.
2506 Imagine two slave databases in different geographic locations over a
2507 slow network. You need information as quickly as possible, so you query
2508 both at once. When you get an answer, you tell the other one to stop
2509 working on your query, as you don't need it anymore.
2510 use strict;
2512 use warnings;
2513 use Time::HiRes 'sleep';
2514 use DBD::Pg ':async';
2515 my $dbhslave1 = DBI->connect('dbi:Pg:dbname=postgres;host=slave1', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
2517 my $dbhslave2 = DBI->connect('dbi:Pg:dbname=postgres;host=slave2', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
2518 $SQL = "SELECT count(*) FROM largetable WHERE flavor='blueberry'";
2520 my $sth1 = $dbhslave1->prepare($SQL, {pg_async => PG_ASYNC});
2522 my $sth2 = $dbhslave2->prepare($SQL, {pg_async => PG_ASYNC});
2523 $sth1->execute();
2525 $sth2->execute();
2526 my $winner;
2528 while (!defined $winner) {
2529 if ($sth1->pg_ready) {
2530 $winner = 1;
2531 }
2532 elsif ($sth2->pg_ready) {
2533 $winner = 2;
2534 }
2535 Time::HiRes::sleep 0.05;
2536 }
2537 my $count;
2539 if ($winner == 1) {
2540 $sth2->pg_cancel();
2541 $sth1->pg_result();
2542 $count = $sth1->fetchall_arrayref()->[0][0];
2543 }
2544 else {
2545 $sth1->pg_cancel();
2546 $sth2->pg_result();
2547 $count = $sth2->fetchall_arrayref()->[0][0];
2548 }
2549 Array support
2551 DBD::Pg allows arrays (as arrayrefs) to be passed in to both the
2552 "quote" and the "execute" methods. In both cases, the array is
2553 flattened into a string representing a Postgres array.
2554 When fetching rows from a table that contains a column with an array
2556 type, the result will be passed back to your script as an arrayref.
2557 To turn off the automatic parsing of returned arrays into arrayrefs,
2559 you can set the attribute pg_expand_array, which is true by default.
2560 $dbh->{pg_expand_array} = 0;
2562 COPY support
2564 DBD::Pg allows for quick (bulk) reading and storing of data by using
2565 the COPY command. The basic process is to use "$dbh->do" to issue a
2566 COPY command, and then to either add rows using "pg_putcopydata", or to
2567 read them by using "pg_getcopydata".
2568 The first step is to put the server into "COPY" mode. This is done by
2570 sending a complete COPY command to the server, by using the "do"
2571 method. For example:
2572 $dbh->do("COPY foobar FROM STDIN");
2574 This would tell the server to enter a COPY IN mode (yes, that's
2576 confusing, but the mode is COPY IN because of the command COPY FROM).
2577 It is now ready to receive information via the "pg_putcopydata" method.
2578 The complete syntax of the COPY command is more complex and not
2579 documented here: the canonical PostgreSQL documentation for COPY can be
2580 found at:
2581 http://www.postgresql.org/docs/current/static/sql-copy.html
2583 Once a COPY command has been issued, no other SQL commands are allowed
2585 until "pg_putcopyend" has been issued (for COPY FROM), or the final
2586 "pg_getcopydata" has been called (for COPY TO).
2587 Note: All other COPY methods (pg_putline, pg_getline, etc.) are now
2589 heavily deprecated in favor of the pg_getcopydata, pg_putcopydata, and
2590 pg_putcopyend methods.
2591 pg_getcopydata
2593 Used to retrieve data from a table after the server has been put into a
2595 COPY OUT mode by calling "COPY tablename TO STDOUT". Data is always
2596 returned one data row at a time. The first argument to pg_getcopydata
2597 is the variable into which the data will be stored (this variable
2598 should not be undefined, or it may throw a warning, although it may be
2599 a reference). The pg_getcopydata method returns a number greater than 1
2600 indicating the new size of the variable, or a -1 when the COPY has
2601 finished. Once a -1 has been returned, no other action is necessary, as
2602 COPY mode will have already terminated. Example:
2603 $dbh->do("COPY mytable TO STDOUT");
2605 my @data;
2606 my $x=0;
2607 1 while $dbh->pg_getcopydata($data[$x++]) >= 0;
2608 There is also a variation of this method called pg_getcopydata_async,
2610 which, as the name suggests, returns immediately. The only difference
2611 from the original method is that this version may return a 0,
2612 indicating that the row is not ready to be delivered yet. When this
2613 happens, the variable has not been changed, and you will need to call
2614 the method again until you get a non-zero result. (Data is still
2615 always returned one data row at a time.)
2616 pg_putcopydata
2618 Used to put data into a table after the server has been put into COPY
2620 IN mode by calling "COPY tablename FROM STDIN". The only argument is
2621 the data you want inserted. Issue a pg_putcopyend() when you have added
2622 all your rows.
2623 The default delimiter is a tab character, but this can be changed in
2625 the COPY statement. Returns a 1 on successful input. Examples:
2626 ## Simple example:
2628 $dbh->do("COPY mytable FROM STDIN");
2629 $dbh->pg_putcopydata("123\tPepperoni\t3\n");
2630 $dbh->pg_putcopydata("314\tMushroom\t8\n");
2631 $dbh->pg_putcopydata("6\tAnchovies\t100\n");
2632 $dbh->pg_putcopyend();
2633 ## This example uses explicit columns and a custom delimiter
2635 $dbh->do("COPY mytable(flavor, slices) FROM STDIN WITH DELIMITER '~'");
2636 $dbh->pg_putcopydata("Pepperoni~123\n");
2637 $dbh->pg_putcopydata("Mushroom~314\n");
2638 $dbh->pg_putcopydata("Anchovies~6\n");
2639 $dbh->pg_putcopyend();
2640 pg_putcopyend
2642 When you are finished with pg_putcopydata, call pg_putcopyend to let
2644 the server know that you are done, and it will return to a normal, non-
2645 COPY state. Returns a 1 on success. This method will fail if called
2646 when not in COPY IN mode.
2647 Large Objects
2649 DBD::Pg supports all largeobject functions provided by libpq via the
2650 "$dbh->pg_lo*" methods. Please note that access to a large object, even
2651 read-only large objects, must be put into a transaction.
2652 Cursors
2654 Although PostgreSQL supports cursors, they have not been used in the
2655 current implementation. When DBD::Pg was created, cursors in PostgreSQL
2656 could only be used inside a transaction block. Because only one
2657 transaction block at a time is allowed, this would have implied the
2658 restriction not to use any nested "SELECT" statements. Therefore the
2659 "execute" method fetches all data at once into data structures located
2660 in the front-end application. This fact must to be considered when
2661 selecting large amounts of data!
2662 You can use cursors in your application, but you'll need to do a little
2664 work. First you must declare your cursor. Now you can issue queries
2665 against the cursor, then select against your queries. This typically
2666 results in a double loop, like this:
2667 # WITH HOLD is not needed if AutoCommit is off
2669 $dbh->do("DECLARE csr CURSOR WITH HOLD FOR $sql");
2670 while (1) {
2671 my $sth = $dbh->prepare("fetch 1000 from csr");
2672 $sth->execute;
2673 last if 0 == $sth->rows;
2674 while (my $row = $sth->fetchrow_hashref) {
2676 # Do something with the data.
2677 }
2678 }
2679 $dbh->do("CLOSE csr");
2680 Datatype bool
2682 The current implementation of PostgreSQL returns 't' for true and 'f'
2683 for false. From the Perl point of view, this is a rather unfortunate
2684 choice. DBD::Pg therefore translates the result for the "BOOL" data
2685 type in a Perlish manner: 'f' becomes the number 0 and 't' becomes the
2686 number 1. This way the application does not have to check the database-
2687 specific returned values for the data-type "BOOL" because Perl treats 0
2688 as false and 1 as true. You may set the pg_bool_tf attribute to a true
2689 value to change the values back to 't' and 'f' if you wish.
2690 Boolean values can be passed to PostgreSQL as TRUE, 't', 'true', 'y',
2692 'yes' or '1' for true and FALSE, 'f', 'false', 'n', 'no' or '0' for
2693 false.
2694 Schema support
2696 The PostgreSQL schema concept may differ from those of other databases.
2697 In a nutshell, a schema is a named collection of objects within a
2698 single database. Please refer to the PostgreSQL documentation for more
2699 details:
2700 <http://www.postgresql.org/docs/current/static/ddl-schemas.html>
2702 DBD::Pg does not provide explicit support for PostgreSQL schemas.
2704 However, schema functionality may be used without any restrictions by
2705 explicitly addressing schema objects, e.g.
2706 my $res = $dbh->selectall_arrayref("SELECT * FROM my_schema.my_table");
2708 or by manipulating the schema search path with "SET search_path", e.g.
2710 $dbh->do("SET search_path TO my_schema, public");
2712
2715 To report a bug, or view the current list of bugs, please visit
2716 http://rt.cpan.org/Public/Dist/Display.html?Name=DBD-Pg
2717
2719 Patches can be submitted to rt.cpan.org. Detailed information on how to
2720 help out with this module can be found in the README.dev file. The
2721 latest development version can be obtained via: git clone
2722 git://github.com/bucardo/dbdpg.git
2723
2725 DBI by Tim Bunce <http://www.tim.bunce.name>
2726 The original DBD-Pg was by Edmund Mergl (E.Mergl@bawue.de) and Jeffrey
2728 W. Baker (jwbaker@acm.org). Major developers include David Wheeler
2729 <david@justatheory.com>, Jason Stewart <jason@openinformatics.com>,
2730 Bruce Momjian <pgman@candle.pha.pa.us>, and Greg Sabino Mullane
2731 <greg@turnstep.com>, with help from many others: see the Changes file
2732 (<http://search.cpan.org/dist/DBD-Pg/Changes>) for a complete list.
2733 Parts of this package were originally copied from DBI and DBD-Oracle.
2735 Mailing List
2737 The current maintainers may be reached through the 'dbd-pg' mailing
2739 list: <dbd-pg@perl.org>. Subscribe by sending an email to
2740 dbd-pg-subscribe@perl.org. Visit the archives at
2741 http://grokbase.com/g/perl/dbd-pg
2742
2744 Copyright (C) 1994-2018, Greg Sabino Mullane
2745 This module (DBD::Pg) is free software; you can redistribute it and/or
2747 modify it under the same terms as Perl 5.10.0. For more details, see
2748 the full text of the licenses in the directory LICENSES.
2749perl v5.28.1 2018-02-13 Pg(3)