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.8.0 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 Latest DBI documentation.
38
40 DBI Class Methods
41 connect
42 This method creates a database handle by connecting to a database, and
44 is the DBI equivalent of the "new" method. To connect to a Postgres
45 database with a minimum of parameters, use the following syntax:
46 $dbh = DBI->connect("dbi:Pg:dbname=$dbname", '', '', {AutoCommit => 0});
48 This connects to the database named in the $dbname variable on the
50 default port (usually 5432) without any user authentication.
51 The following connect statement shows almost all possible parameters:
53 $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host;port=$port;options=$options",
55 $username,
56 $password,
57 {AutoCommit => 0, RaiseError => 1, PrintError => 0}
58 );
59 Parameters containing unusual characters such as spaces can be wrapped
61 in single quotes around the value e.g. "dbi:Pg:dbname='spacey
62 name';host=$host"
63 If a parameter is not given, the connect() method will first look for
65 specific environment variables, and then fall back to hard-coded
66 defaults:
67 parameter environment variable hard coded default
69 ------------------------------------------------------
70 host PGHOST local domain socket
71 hostaddr PGHOSTADDR local domain socket
72 port PGPORT 5432
73 dbname* PGDATABASE current userid
74 username PGUSER current userid
75 password PGPASSWORD (none)
76 options PGOPTIONS (none)
77 service PGSERVICE (none)
78 sslmode PGSSLMODE (none)
79 * May also use the aliases "db" or "database"
81 If the username and password values passed via "connect()" are
83 undefined (as opposed to merely being empty strings), DBI will use the
84 environment variables DBI_USER and DBI_PASS if they exist.
85 You can also connect by using a service connection file, which is named
87 pg_service.conf. The location of this file can be controlled by setting
88 the PGSYSCONFDIR environment variable. To use one of the named services
89 within the file, set the name by using either the service parameter or
90 the environment variable PGSERVICE. Note that when connecting this way,
91 only the minimum parameters should be used. For example, to connect to
92 a service named "zephyr", you could use:
93 $dbh = DBI->connect("dbi:Pg:service=zephyr", '', '');
95 You could also set $ENV{PGSERVICE} to "zephyr" and connect like this:
97 $dbh = DBI->connect("dbi:Pg:", '', '');
99 The format of the pg_service.conf file is simply a bracketed service
101 name, followed by one parameter per line in the format name=value. For
102 example:
103 [zephyr]
105 dbname=winds
106 user=wisp
107 password=W$2Hc00YSgP
108 port=6543
109 There are four valid arguments to the sslmode parameter, which controls
111 whether to use SSL to connect to the database:
112 · disable: SSL connections are never used
114 · allow: try non-SSL, then SSL
116 · prefer: try SSL, then non-SSL
118 · require: connect only with SSL
120 You can also connect using sockets in a specific directory. This may be
122 needed if the server you are connecting to has a different default
123 socket directory from the one used to compile DBD::Pg. Use the
124 complete path to the socket directory as the name of the host, like
125 this:
126 $dbh = DBI->connect('dbi:Pg:dbname=foo;host=/var/tmp/socket',
128 $username,
129 $password,
130 {AutoCommit => 0, RaiseError => 1});
131 The attribute hash can also contain a key named "dbd_verbose", which
133 simply calls "$dbh->trace('DBD')" after the handle is created. This
134 attribute is not recommended, as it is clearer to simply explicitly
135 call "trace" explicitly in your script.
136 connect_cached
138 $dbh = DBI->connect_cached("dbi:Pg:dbname=$dbname", $username, $password, \%options);
140 Implemented by DBI, no driver-specific impact.
142 data_sources
144 @data_sources = DBI->data_sources('Pg');
146 @data_sources = $dbh->data_sources();
147 Returns a list of available databases. Unless the environment variable
149 "DBI_DSN" is set, a connection will be attempted to the database
150 "template1". The normal connection environment variables also apply,
151 such as "PGHOST", "PGPORT", "DBI_USER", "DBI_PASS", and "PGSERVICE".
152 You can also pass in options to add to the connection string For
154 example, to specify an alternate port and host:
155 @data_sources = DBI->data_sources('Pg', 'port=5824;host=example.com');
157 or:
159 @data_sources = $dbh->data_sources('port=5824;host=example.com');
161 Methods Common To All Handles
163 For all of the methods below, $h can be either a database handle ($dbh)
164 or a statement handle ($sth). Note that $dbh and $sth can be replaced
165 with any variable name you choose: these are just the names most often
166 used. Another common variable used in this documentation is $rv, which
167 stands for "return value".
168 err
170 $rv = $h->err;
172 Returns the error code from the last method called. For the connect
174 method it returns "PQstatus", which is a number used by libpq (the
175 Postgres connection library). A value of 0 indicates no error
176 (CONNECTION_OK), while any other number indicates a failed connection.
177 The only other number commonly seen is 1 (CONNECTION_BAD). See the
178 libpq documentation for the complete list of return codes.
179 In all other non-connect methods "$h->err" returns the "PQresultStatus"
181 of the current handle. This is a number used by libpq and is one of:
182 0 Empty query string
184 1 A command that returns no data successfully completed.
185 2 A command that returns data successfully completed.
186 3 A COPY OUT command is still in progress.
187 4 A COPY IN command is still in progress.
188 5 A bad response was received from the backend.
189 6 A nonfatal error occurred (a notice or warning message)
190 7 A fatal error was returned: the last query failed.
191 errstr
193 $str = $h->errstr;
195 Returns the last error that was reported by Postgres. This message is
197 affected by the pg_errorlevel setting.
198 state
200 $str = $h->state;
202 Returns a five-character "SQLSTATE" code. Success is indicated by a
204 00000 code, which gets mapped to an empty string by DBI. A code of
205 "S8006" indicates a connection failure, usually because the connection
206 to the Postgres server has been lost.
207 While this method can be called as either "$sth->state" or
209 "$dbh->state", it is usually clearer to always use "$dbh->state".
210 The list of codes used by PostgreSQL can be found at:
212 <http://www.postgresql.org/docs/current/static/errcodes-appendix.html>
213 Note that these codes are part of the SQL standard and only a small
215 number of them will be used by PostgreSQL.
216 Common codes:
218 00000 Successful completion
220 25P01 No active SQL transaction
221 25P02 In failed SQL transaction
222 S8006 Connection failure
223 trace
225 $h->trace($trace_settings);
227 $h->trace($trace_settings, $trace_filename);
228 $trace_settings = $h->trace;
229 Changes the trace settings on a database or statement handle. The
231 optional second argument specifies a file to write the trace
232 information to. If no filename is given, the information is written to
233 STDERR. Note that tracing can be set globally as well by setting
234 "DBI->trace", or by using the environment variable DBI_TRACE.
235 The value is either a numeric level or a named flag. For the flags that
237 DBD::Pg uses, see parse_trace_flag.
238 trace_msg
240 $h->trace_msg($message_text);
242 $h->trace_msg($message_text, $min_level);
243 Writes a message to the current trace output (as set by the "trace"
245 method). If a second argument is given, the message is only written if
246 the current tracing level is equal to or greater than the $min_level.
247 parse_trace_flag and parse_trace_flags
249 $h->trace($h->parse_trace_flags('SQL|pglibpq'));
251 $h->trace($h->parse_trace_flags('1|pgstart'));
252 ## Simpler:
254 $h->trace('SQL|pglibpq');
255 $h->trace('1|pgstart');
256 my $value = DBD::Pg->parse_trace_flag('pglibpq');
258 DBI->trace($value);
259 The parse_trace_flags method is used to convert one or more named flags
261 to a number which can passed to the "trace" method. DBD::Pg currently
262 supports the DBI-specific flag, "SQL", as well as the ones listed
263 below.
264 Flags can be combined by using the parse_trace_flags method, which
266 simply calls "parse_trace_flag" on each item and combines them.
267 Sometimes you may wish to turn the tracing on before you connect to the
269 database. The second example above shows a way of doing this: the call
270 to "DBD::Pg->parse_trace_flags" provides a number than can be fed to
271 "DBI->trace" before you create a database handle.
272 DBD::Pg supports the following trace flags:
274 SQL Outputs all SQL statements. Note that the output provided will not
276 necessarily be in a form suitable to passing directly to Postgres,
277 as server-side prepared statements are used extensively by DBD::Pg.
278 For maximum portability of output (but with a potential performance
279 hit), use with "$dbh->{pg_server_prepare} = 0".
280 DBD Turns on all non-DBI flags, in other words, only the ones that are
282 specific to DBD::Pg (all those below which start with the letters
283 'pg').
284 pglibpq
286 Outputs the name of each libpq function (without arguments)
287 immediately before running it. This is a good way to trace the flow
288 of your program at a low level. This information is also output if
289 the trace level is set to 4 or greater.
290 pgstart
292 Outputs the name of each internal DBD::Pg function, and other
293 information such as the function arguments or important global
294 variables, as each function starts. This information is also output
295 if the trace level is set to 4 or greater.
296 pgend
298 Outputs a simple message at the very end of each internal DBD::Pg
299 function. This is also output if the trace level is set to 4 or
300 greater.
301 pgprefix
303 Forces each line of trace output to begin with the string "dbdpg:
304 ". This helps to differentiate it from the normal DBI trace output.
305 pglogin
307 Outputs a message showing the connection string right before a new
308 database connection is attempted, a message when the connection was
309 successful, and a message right after the database has been
310 disconnected. Also output if trace level is 5 or greater.
311 See the DBI section on TRACING for more information.
313 func
315 DBD::Pg uses the "func" method to support a variety of functions. Note
317 that the name of the function comes last, after the arguments.
318 table_attributes
320 $attrs = $dbh->func($table, 'table_attributes');
321 Use of the tables_attributes function is no longer recommended.
323 Instead, you can use the more portable "column_info" and
324 "primary_key" methods to access the same information.
325 The table_attributes method returns, for the given table argument,
327 a reference to an array of hashes, each of which contains the
328 following keys:
329 NAME attribute name
331 TYPE attribute type
332 SIZE attribute size (-1 for variable size)
333 NULLABLE flag nullable
334 DEFAULT default value
335 CONSTRAINT constraint
336 PRIMARY_KEY flag is_primary_key
337 REMARKS attribute description
338 pg_lo_creat
340 $lobjId = $dbh->pg_lo_creat($mode);
341 Creates a new large object and returns the object-id. $mode is a
343 bitmask describing read and write access to the new object. This
344 setting is ignored since Postgres version 8.1. For backwards
345 compatibility, however, you should set a valid mode anyway (see
346 "pg_lo_open" for a list of valid modes).
347 Upon failure it returns "undef". This function cannot be used if
349 AutoCommit is enabled.
350 The old way of calling large objects functions is deprecated:
352 $dbh->func(.., 'lo_);
353 pg_lo_open
355 $lobj_fd = $dbh->pg_lo_open($lobjId, $mode);
356 Opens an existing large object and returns an object-descriptor for
358 use in subsequent "pg_lo_*" calls. $mode is a bitmask describing
359 read and write access to the opened object. It may be one of:
360 $dbh->{pg_INV_READ}
362 $dbh->{pg_INV_WRITE}
363 $dbh->{pg_INV_READ} | $dbh->{pg_INV_WRITE}
364 "pg_INV_WRITE" and "pg_INV_WRITE | pg_INV_READ" modes are
366 identical; in both modes, the large object can be read from or
367 written to. Reading from the object will provide the object as
368 written in other committed transactions, along with any writes
369 performed by the current transaction. Objects opened with
370 "pg_INV_READ" cannot be written to. Reading from this object will
371 provide the stored data at the time of the transaction snapshot
372 which was active when "pg_lo_write" was called.
373 Returns "undef" upon failure. Note that 0 is a perfectly correct
375 (and common) object descriptor! This function cannot be used if
376 AutoCommit is enabled.
377 pg_lo_write
379 $nbytes = $dbh->pg_lo_write($lobj_fd, $buffer, $len);
380 Writes $len bytes of c<$buffer> into the large object $lobj_fd.
382 Returns the number of bytes written and "undef" upon failure. This
383 function cannot be used if AutoCommit is enabled.
384 pg_lo_read
386 $nbytes = $dbh->pg_lo_read($lobj_fd, $buffer, $len);
387 Reads $len bytes into c<$buffer> from large object $lobj_fd.
389 Returns the number of bytes read and "undef" upon failure. This
390 function cannot be used if AutoCommit is enabled.
391 pg_lo_lseek
393 $loc = $dbh->pg_lo_lseek($lobj_fd, $offset, $whence);
394 Changes the current read or write location on the large object
396 $obj_id. Currently $whence can only be 0 (which is L_SET). Returns
397 the current location and "undef" upon failure. This function cannot
398 be used if AutoCommit is enabled.
399 pg_lo_tell
401 $loc = $dbh->pg_lo_tell($lobj_fd);
402 Returns the current read or write location on the large object
404 $lobj_fd and "undef" upon failure. This function cannot be used if
405 AutoCommit is enabled.
406 pg_lo_truncate
408 $loc = $dbh->pg_lo_truncate($lobj_fd, $len);
409 Truncates the given large object to the new size. Returns "undef"
411 on failure, and 0 on success. This function cannot be used if
412 AutoCommit is enabled.
413 pg_lo_close
415 $lobj_fd = $dbh->pg_lo_close($lobj_fd);
416 Closes an existing large object. Returns true upon success and
418 false upon failure. This function cannot be used if AutoCommit is
419 enabled.
420 pg_lo_unlink
422 $ret = $dbh->pg_lo_unlink($lobjId);
423 Deletes an existing large object. Returns true upon success and
425 false upon failure. This function cannot be used if AutoCommit is
426 enabled.
427 pg_lo_import
429 $lobjId = $dbh->pg_lo_import($filename);
430 Imports a Unix file as a large object and returns the object id of
432 the new object or "undef" upon failure.
433 pg_lo_import_with_oid
435 $lobjId = $dbh->pg_lo_import($filename, $OID);
436 Same as pg_lo_import, but attempts to use the supplied OID as the
438 large object number. If this number is 0, it falls back to the
439 behavior of pg_lo_import (which assigns the next available OID).
440 This is only available when DBD::Pg is compiled against a Postgres
442 server version 8.4 or later.
443 pg_lo_export
445 $ret = $dbh->pg_lo_export($lobjId, $filename);
446 Exports a large object into a Unix file. Returns false upon
448 failure, true otherwise.
449 getfd
451 $fd = $dbh->func('getfd');
452 Deprecated, use $dbh->{pg_socket} instead.
454 private_attribute_info
456 $hashref = $dbh->private_attribute_info();
458 $hashref = $sth->private_attribute_info();
459 Returns a hash of all private attributes used by DBD::Pg, for either a
461 database or a statement handle. Currently, all the hash values are
462 undef.
463
465 InactiveDestroy (boolean)
466 If set to true, then the "disconnect" method will not be automatically
468 called when the database handle goes out of scope. This is required if
469 you are forking, and even then you must tread carefully and ensure that
470 either the parent or the child (but not both!) handles all database
471 calls from that point forwards, so that messages from the Postgres
472 backend are only handled by one of the processes. If you don't set
473 things up properly, you will see messages such as "server closed the
474 connection unexpectedly", and "message type 0x32 arrived from server
475 while idle". The best solution is to either have the child process
476 reconnect to the database with a fresh database handle, or to rewrite
477 your application not to use forking. See the section on "Asynchronous
478 Queries" for a way to have your script continue to work while the
479 database is processing a request.
480 AutoInactiveDestroy (boolean)
482 The InactiveDestroy attribute, described above, needs to be explicitly
484 set in the child process after a fork. If the code that performs the
485 fork is in a third party module such as Sys::Syslog, this can present a
486 problem. Use AutoInactiveDestroy to get around this problem.
487 RaiseError (boolean, inherited)
489 Forces errors to always raise an exception. Although it defaults to
491 off, it is recommended that this be turned on, as the alternative is to
492 check the return value of every method (prepare, execute, fetch, etc.)
493 manually, which is easy to forget to do.
494 PrintError (boolean, inherited)
496 Forces database errors to also generate warnings, which can then be
498 filtered with methods such as locally redefining $SIG{__WARN__} or
499 using modules such as "CGI::Carp". This attribute is on by default.
500 ShowErrorStatement (boolean, inherited)
502 Appends information about the current statement to error messages. If
504 placeholder information is available, adds that as well. Defaults to
505 false.
506 Warn (boolean, inherited)
508 Enables warnings. This is on by default, and should only be turned off
510 in a local block for a short a time only when absolutely needed.
511 Executed (boolean, read-only)
513 Indicates if a handle has been executed. For database handles, this
515 value is true after the "do" method has been called, or when one of the
516 child statement handles has issued an "execute". Issuing a "commit" or
517 "rollback" always resets the attribute to false for database handles.
518 For statement handles, any call to "execute" or its variants will flip
519 the value to true for the lifetime of the statement handle.
520 TraceLevel (integer, inherited)
522 Sets the trace level, similar to the "trace" method. See the sections
524 on "trace" and parse_trace_flag for more details.
525 Active (boolean, read-only)
527 Indicates if a handle is active or not. For database handles, this
529 indicates if the database has been disconnected or not. For statement
530 handles, it indicates if all the data has been fetched yet or not. Use
531 of this attribute is not encouraged.
532 Kids (integer, read-only)
534 Returns the number of child processes created for each handle type. For
536 a driver handle, indicates the number of database handles created. For
537 a database handle, indicates the number of statement handles created.
538 For statement handles, it always returns zero, because statement
539 handles do not create kids.
540 ActiveKids (integer, read-only)
542 Same as "Kids", but only returns those that are active.
544 CachedKids (hash ref)
546 Returns a hashref of handles. If called on a database handle, returns
548 all statement handles created by use of the "prepare_cached" method. If
549 called on a driver handle, returns all database handles created by the
550 "connect_cached" method.
551 ChildHandles (array ref)
553 Implemented by DBI, no driver-specific impact.
555 PrintWarn (boolean, inherited)
557 Implemented by DBI, no driver-specific impact.
559 HandleError (boolean, inherited)
561 Implemented by DBI, no driver-specific impact.
563 HandleSetErr (code ref, inherited)
565 Implemented by DBI, no driver-specific impact.
567 ErrCount (unsigned integer)
569 Implemented by DBI, no driver-specific impact.
571 FetchHashKeyName (string, inherited)
573 Implemented by DBI, no driver-specific impact.
575 ChopBlanks (boolean, inherited)
577 Supported by DBD::Pg as proposed by DBI. This method is similar to the
579 SQL function "RTRIM".
580 Taint (boolean, inherited)
582 Implemented by DBI, no driver-specific impact.
584 TaintIn (boolean, inherited)
586 Implemented by DBI, no driver-specific impact.
588 TaintOut (boolean, inherited)
590 Implemented by DBI, no driver-specific impact.
592 Profile (inherited)
594 Implemented by DBI, no driver-specific impact.
596 Type (scalar)
598 Returns "dr" for a driver handle, "db" for a database handle, and "st"
600 for a statement handle. Should be rarely needed.
601 LongReadLen
603 Not used by DBD::Pg
605 LongTruncOk
607 Not used by DBD::Pg
609 CompatMode
611 Not used by DBD::Pg
613
615 Database Handle Methods
616 selectall_arrayref
617 $ary_ref = $dbh->selectall_arrayref($sql);
619 $ary_ref = $dbh->selectall_arrayref($sql, \%attr);
620 $ary_ref = $dbh->selectall_arrayref($sql, \%attr, @bind_values);
621 Returns a reference to an array containing the rows returned by
623 preparing and executing the SQL string. See the DBI documentation for
624 full details.
625 selectall_hashref
627 $hash_ref = $dbh->selectall_hashref($sql, $key_field);
629 Returns a reference to a hash containing the rows returned by preparing
631 and executing the SQL string. See the DBI documentation for full
632 details.
633 selectcol_arrayref
635 $ary_ref = $dbh->selectcol_arrayref($sql, \%attr, @bind_values);
637 Returns a reference to an array containing the first column from each
639 rows returned by preparing and executing the SQL string. It is possible
640 to specify exactly which columns to return. See the DBI documentation
641 for full details.
642 prepare
644 $sth = $dbh->prepare($statement, \%attr);
646 WARNING: DBD::Pg now (as of version 1.40) uses true prepared statements
648 by sending them to the backend to be prepared by the Postgres server.
649 Statements that were legal before may no longer work. See below for
650 details.
651 The prepare method prepares a statement for later execution. PostgreSQL
653 supports prepared statements, which enables DBD::Pg to only send the
654 query once, and simply send the arguments for every subsequent call to
655 "execute". DBD::Pg can use these server-side prepared statements, or
656 it can just send the entire query to the server each time. The best way
657 is automatically chosen for each query. This will be sufficient for
658 most users: keep reading for a more detailed explanation and some
659 optional flags.
660 Queries that do not begin with the word "SELECT", "INSERT", "UPDATE",
662 or "DELETE" are never sent as server-side prepared statements.
663 Deciding whether or not to use prepared statements depends on many
665 factors, but you can force them to be used or not used by using the
666 pg_server_prepare attribute when calling "prepare". Setting this to
667 false means to never use prepared statements. Setting pg_server_prepare
668 to true means that prepared statements should be used whenever
669 possible. This is the default.
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 (either manually, or via the "SERIAL" pseudotype or
937 "GENERATED ... AS IDENTITY"). If more than one column meets these
938 conditions, the primary key will be used. This involves some looking up
939 of things in the system table, so DBD::Pg will cache the sequence name
940 for subsequent calls. If you need to disable this caching for some
941 reason, (such as the sequence name changing), you can control it by
942 adding "pg_cache => 0" to the final (hashref) argument for
943 last_insert_id.
944 Please keep in mind that this method is far from foolproof, so make
946 your script use it properly. Specifically, make sure that it is called
947 immediately after the insert, and that the insert does not add a value
948 to the column that is using the sequence as a default value. However,
949 because we are using sequences, you can be sure that the value you got
950 back has not been used by any other process.
951 Some examples:
953 $dbh->do('CREATE SEQUENCE lii_seq START 1');
955 $dbh->do(q{CREATE TABLE lii (
956 foobar INTEGER NOT NULL UNIQUE DEFAULT nextval('lii_seq'),
957 baz VARCHAR)});
958 $SQL = 'INSERT INTO lii(baz) VALUES (?)';
959 $sth = $dbh->prepare($SQL);
960 for (qw(uno dos tres cuatro)) {
961 $sth->execute($_);
962 my $newid = $dbh->last_insert_id(undef,undef,undef,undef,{sequence=>'lii_seq'});
963 print "Last insert id was $newid\n";
964 }
965 If you did not want to worry about the sequence name:
967 $dbh->do('CREATE TABLE lii2 (
969 foobar SERIAL UNIQUE,
970 baz VARCHAR)');
971 $SQL = 'INSERT INTO lii2(baz) VALUES (?)';
972 $sth = $dbh->prepare($SQL);
973 for (qw(uno dos tres cuatro)) {
974 $sth->execute($_);
975 my $newid = $dbh->last_insert_id(undef,undef,"lii2",undef);
976 print "Last insert id was $newid\n";
977 }
978 commit
980 $rv = $dbh->commit;
982 Issues a COMMIT to the server, indicating that the current transaction
984 is finished and that all changes made will be visible to other
985 processes. If AutoCommit is enabled, then a warning is given and no
986 COMMIT is issued. Returns true on success, false on error. See also
987 the section on "Transactions".
988 rollback
990 $rv = $dbh->rollback;
992 Issues a ROLLBACK to the server, which discards any changes made in the
994 current transaction. If AutoCommit is enabled, then a warning is given
995 and no ROLLBACK is issued. Returns true on success, and false on error.
996 See also the the section on "Transactions".
997 begin_work
999 This method turns on transactions until the next call to "commit" or
1001 "rollback", if AutoCommit is currently enabled. If it is not enabled,
1002 calling begin_work will issue an error. Note that the transaction will
1003 not actually begin until the first statement after begin_work is
1004 called. Example:
1005 $dbh->{AutoCommit} = 1;
1007 $dbh->do('INSERT INTO foo VALUES (123)'); ## Changes committed immediately
1008 $dbh->begin_work();
1009 ## Not in a transaction yet, but AutoCommit is set to 0
1010 $dbh->do("INSERT INTO foo VALUES (345)");
1012 ## DBD::PG actually issues two statements here:
1013 ## BEGIN;
1014 ## INSERT INTO foo VALUES (345)
1015 ## We are now in a transaction
1016 $dbh->commit();
1018 ## AutoCommit is now set to 1 again
1019 disconnect
1021 $rv = $dbh->disconnect;
1023 Disconnects from the Postgres database. Any uncommitted changes will be
1025 rolled back upon disconnection. It's good policy to always explicitly
1026 call commit or rollback at some point before disconnecting, rather than
1027 relying on the default rollback behavior.
1028 This method may give warnings about "disconnect invalidates X active
1030 statement handle(s)". This means that you called "$sth->execute()" but
1031 did not finish fetching all the rows from them. To avoid seeing this
1032 warning, either fetch all the rows or call "$sth->finish()" for each
1033 executed statement handle.
1034 If the script exits before disconnect is called (or, more precisely, if
1036 the database handle is no longer referenced by anything), then the
1037 database handle's DESTROY method will call the rollback() and
1038 disconnect() methods automatically. It is best to explicitly disconnect
1039 rather than rely on this behavior.
1040 quote
1042 $rv = $dbh->quote($value, $data_type);
1044 This module implements its own "quote" method. For simple string types,
1046 both backslashes and single quotes are doubled. You may also quote
1047 arrayrefs and receive a string suitable for passing into Postgres array
1048 columns.
1049 If the value contains backslashes, and the server is version 8.1 or
1051 higher, then the escaped string syntax will be used (which places a
1052 capital E before the first single quote). This syntax is always used
1053 when quoting bytea values on servers 8.1 and higher.
1054 The "data_type" argument is optional and should be one of the type
1056 constants exported by DBD::Pg (such as PG_BYTEA). In addition to
1057 string, bytea, char, bool, and other standard types, the following
1058 geometric types are supported: point, line, lseg, box, path, polygon,
1059 and circle (PG_POINT, PG_LINE, PG_LSEG, PG_BOX, PG_PATH, PG_POLYGON,
1060 and PG_CIRCLE respectively). To quote a Postgres-specific data type,
1061 you must use a 'hashref' argument like so:
1062 my $quotedval = $dbh->quote($value, { pg_type => PG_VARCHAR });
1064 NOTE: The undocumented (and invalid) support for the "SQL_BINARY" data
1066 type is officially deprecated. Use "PG_BYTEA" with "bind_param()"
1067 instead:
1068 $rv = $sth->bind_param($param_num, $bind_value,
1070 { pg_type => PG_BYTEA });
1071 quote_identifier
1073 $string = $dbh->quote_identifier( $name );
1075 $string = $dbh->quote_identifier( undef, $schema, $table);
1076 Returns a quoted version of the supplied string, which is commonly a
1078 schema, table, or column name. The three argument form will return the
1079 schema and the table together, separated by a dot. Examples:
1080 print $dbh->quote_identifier('grapefruit'); ## Prints: "grapefruit"
1082 print $dbh->quote_identifier('juicy fruit'); ## Prints: "juicy fruit"
1084 print $dbh->quote_identifier(undef, 'public', 'pg_proc');
1086 ## Prints: "public"."pg_proc"
1087 pg_notifies
1089 $ret = $dbh->pg_notifies;
1091 Looks for any asynchronous notifications received and returns either
1093 "undef" or a reference to a three-element array consisting of an event
1094 name, the PID of the backend that sent the NOTIFY command, and the
1095 optional payload string. Note that this does not check if the
1096 connection to the database is still valid first - for that, use the
1097 c<ping> method. You may need to commit if not in autocommit mode - new
1098 notices will not be picked up while in the middle of a transaction. An
1099 example:
1100 $dbh->do("LISTEN abc");
1102 $dbh->do("LISTEN def");
1103 ## Hang around until we get the message we want
1105 LISTENLOOP: {
1106 while (my $notify = $dbh->pg_notifies) {
1107 my ($name, $pid, $payload) = @$notify;
1108 print qq{I received notice "$name" from PID $pid, payload was "$payload"\n};
1109 ## Do something based on the notice received
1110 }
1111 $dbh->ping() or die qq{Ping failed!};
1112 $dbh->commit();
1113 sleep(5);
1114 redo;
1115 }
1116 Payloads will always be an empty string unless you are connecting to a
1118 Postgres server version 9.0 or higher.
1119 ping
1121 $rv = $dbh->ping;
1123 The "ping" method determines if there is a working connection to an
1125 active database server. It does this by sending a small query to the
1126 server, currently 'DBD::Pg ping test v3.8.0'. It returns 0 (false) if
1127 the connection is not valid, otherwise it returns a positive number
1128 (true). The value returned indicates the current state:
1129 Value Meaning
1131 --------------------------------------------------
1132 1 Database is idle (not in a transaction)
1133 2 Database is active, there is a command in progress (usually seen after a COPY command)
1134 3 Database is idle within a transaction
1135 4 Database is idle, within a failed transaction
1136 Additional information on why a handle is not valid can be obtained by
1138 using the "pg_ping" method.
1139 pg_ping
1141 $rv = $dbh->pg_ping;
1143 This is a DBD::Pg-specific extension to the "ping" method. This will
1145 check the validity of a database handle in exactly the same way as
1146 "ping", but instead of returning a 0 for an invalid connection, it will
1147 return a negative number. So in addition to returning the positive
1148 numbers documented for "ping", it may also return the following:
1149 Value Meaning
1151 --------------------------------------------------
1152 -1 There is no connection to the database at all (e.g. after disconnect)
1153 -2 An unknown transaction status was returned (e.g. after forking)
1154 -3 The test query failed (PQexec returned null)
1155 -4 PQstatus returned a CONNECTION_BAD
1156 get_info
1158 $value = $dbh->get_info($info_type);
1160 Supports a very large set (> 250) of the information types, including
1162 the minimum recommended by DBI.
1163 table_info
1165 $sth = $dbh->table_info(undef, $schema, $table, $type);
1167 Returns all tables and views visible to the current user. The schema
1169 and table arguments will do a "LIKE" search if a percent sign ("%") or
1170 an underscore ("_") is detected in the argument. The $type argument
1171 accepts any comma-separated combination of "TABLE", "VIEW", "SYSTEM
1172 TABLE", "SYSTEM VIEW", "MATERIALIZED VIEW", "SYSTEM MATERIALIZED VIEW",
1173 "FOREIGN TABLE", "SYSTEM FOREIGN TABLE", or "LOCAL TEMPORARY". (Using
1174 all is the default action.)
1175 Note that a statement handle is returned, and not a direct list of
1177 tables. See the examples below for ways to handle this.
1178 The following fields are returned:
1180 TABLE_CAT: The name of the database that the table or view is in
1182 (always the current database).
1183 TABLE_SCHEM: The name of the schema that the table or view is in.
1185 TABLE_NAME: The name of the table or view.
1187 TABLE_TYPE: The type of object returned. Will be one of "TABLE",
1189 "VIEW", "MATERIALIZED VIEW", "SYSTEM VIEW", "SYSTEM MATERIALIZED VIEW",
1190 "SYSTEM TABLE", "FOREIGN TABLE", "SYSTEM FOREIGN TABLE", or "LOCAL
1191 TEMPORARY".
1192 The TABLE_SCHEM and TABLE_NAME will be quoted via "quote_ident()".
1194 Four additional fields specific to DBD::Pg are returned:
1196 pg_schema: the unquoted name of the schema
1198 pg_table: the unquoted name of the table
1200 pg_tablespace_name: the name of the tablespace the table is in
1202 pg_tablespace_location: the location of the tablespace the table is in
1204 Tables that have not been assigned to a particular tablespace (or
1206 views) will return NULL ("undef") for both of the above field.
1207 Rows are returned alphabetically, with all tables first, and then all
1209 views.
1210 Examples of use:
1212 ## Display all tables and views in the public schema:
1214 $sth = $dbh->table_info('', 'public', undef, undef);
1215 for my $rel (@{$sth->fetchall_arrayref({})}) {
1216 print "$rel->{TABLE_TYPE} name is $rel->{TABLE_NAME}\n";
1217 }
1218 # Display the schema of all tables named 'foo':
1221 $sth = $dbh->table_info('', undef, 'foo', 'TABLE');
1222 for my $rel (@{$sth->fetchall_arrayref({})}) {
1223 print "Table name is $rel->{TABLE_SCHEM}.$rel->{TABLE_NAME}\n";
1224 }
1225 column_info
1227 $sth = $dbh->column_info( undef, $schema, $table, $column );
1229 Supported by this driver as proposed by DBI with the follow exceptions.
1231 These fields are currently always returned with NULL ("undef") values:
1232 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 DBI section on statistics_info
1335 In addition, the following Postgres specific columns are returned:
1337 pg_expression
1339 Postgres allows indexes on functions and scalar expressions based
1340 on one or more columns. This field will always be populated if an
1341 index, but the lack of an entry in the COLUMN_NAME should indicate
1342 that this is an index expression.
1343 tables
1345 @names = $dbh->tables( undef, $schema, $table, $type, \%attr );
1347 Supported by this driver as proposed by DBI. This method returns all
1349 tables and/or views (including foreign tables and materialized views)
1350 which are visible to the current user: see "table_info" for more
1351 information about the arguments. The name of the schema appears before
1352 the table or view name. This can be turned off by adding in the
1353 "pg_noprefix" attribute:
1354 my @tables = $dbh->tables( '', '', 'dbd_pg_test', '', {pg_noprefix => 1} );
1356 type_info_all
1358 $type_info_all = $dbh->type_info_all;
1360 Supported by this driver as proposed by DBI. Information is only
1362 provided for SQL datatypes and for frequently used datatypes. The
1363 mapping between the PostgreSQL typename and the SQL92 datatype (if
1364 possible) has been done according to the following table:
1365 +---------------+------------------------------------+
1367 | typname | SQL92 |
1368 |---------------+------------------------------------|
1369 | bool | BOOL |
1370 | text | / |
1371 | bpchar | CHAR(n) |
1372 | varchar | VARCHAR(n) |
1373 | int2 | SMALLINT |
1374 | int4 | INT |
1375 | int8 | BIGINT |
1376 | money | / |
1377 | float4 | FLOAT(p) p<7=float4, p<16=float8 |
1378 | float8 | REAL |
1379 | abstime | / |
1380 | reltime | / |
1381 | tinterval | / |
1382 | date | / |
1383 | time | / |
1384 | datetime | / |
1385 | timespan | TINTERVAL |
1386 | timestamp | TIMESTAMP |
1387 +---------------+------------------------------------+
1388 type_info
1390 @type_info = $dbh->type_info($data_type);
1392 Returns a list of hash references holding information about one or more
1394 variants of $data_type. See the DBI documentation for more details.
1395 pg_server_trace
1397 $dbh->pg_server_trace($filehandle);
1399 Writes debugging information from the PostgreSQL backend to a file.
1401 This is not related to the DBI "trace" method and you should not use
1402 this method unless you know what you are doing. If you do enable this,
1403 be aware that the file will grow very large, very quick. To stop
1404 logging to the file, use the "pg_server_untrace" method. The first
1405 argument must be a file handle, not a filename. Example:
1406 my $pid = $dbh->{pg_pid};
1408 my $file = "pgbackend.$pid.debug.log";
1409 open(my $fh, ">$file") or die qq{Could not open "$file": $!\n};
1410 $dbh->pg_server_trace($fh);
1411 ## Run code you want to trace here
1412 $dbh->pg_server_untrace;
1413 close($fh);
1414 pg_server_untrace
1416 $dbh->pg_server_untrace;
1418 Stop server logging to a previously opened file.
1420 selectrow_array
1422 @row_ary = $dbh->selectrow_array($sql);
1424 @row_ary = $dbh->selectrow_array($sql, \%attr);
1425 @row_ary = $dbh->selectrow_array($sql, \%attr, @bind_values);
1426 Returns an array of row information after preparing and executing the
1428 provided SQL string. The rows are returned by calling "fetchrow_array".
1429 The string can also be a statement handle generated by a previous
1430 prepare. Note that only the first row of data is returned. If called in
1431 a scalar context, only the first column of the first row is returned.
1432 Because this is not portable, it is not recommended that you use this
1433 method in that way.
1434 selectrow_arrayref
1436 $ary_ref = $dbh->selectrow_arrayref($statement);
1438 $ary_ref = $dbh->selectrow_arrayref($statement, \%attr);
1439 $ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @bind_values);
1440 Exactly the same as "selectrow_array", except that it returns a
1442 reference to an array, by internal use of the "fetchrow_arrayref"
1443 method.
1444 selectrow_hashref
1446 $hash_ref = $dbh->selectrow_hashref($sql);
1448 $hash_ref = $dbh->selectrow_hashref($sql, \%attr);
1449 $hash_ref = $dbh->selectrow_hashref($sql, \%attr, @bind_values);
1450 Exactly the same as "selectrow_array", except that it returns a
1452 reference to an hash, by internal use of the "fetchrow_hashref" method.
1453 clone
1455 $other_dbh = $dbh->clone();
1457 Creates a copy of the database handle by connecting with the same
1459 parameters as the original handle, then trying to merge the attributes.
1460 See the DBI documentation for complete usage.
1461 Database Handle Attributes
1463 AutoCommit (boolean)
1464 Supported by DBD::Pg as proposed by DBI. According to the
1466 classification of DBI, PostgreSQL is a database in which a transaction
1467 must be explicitly started. Without starting a transaction, every
1468 change to the database becomes immediately permanent. The default of
1469 AutoCommit is on, but this may change in the future, so it is highly
1470 recommended that you explicitly set it when calling "connect". For
1471 details see the notes about "Transactions" elsewhere in this document.
1472 pg_bool_tf (boolean)
1474 DBD::Pg specific attribute. If true, boolean values will be returned as
1476 the characters 't' and 'f' instead of '1' and '0'.
1477 ReadOnly (boolean)
1479 $dbh->{ReadOnly} = 1;
1481 Specifies if the current database connection should be in read-only
1483 mode or not. In this mode, changes that change the database are not
1484 allowed and will throw an error. Note: this method will not work if
1485 "AutoCommit" is true. The read-only effect is accomplished by sending a
1486 SET TRANSACTION READ ONLY after every begin. For more details, please
1487 see:
1488 http://www.postgresql.org/docs/current/interactive/sql-set-transaction.html
1490 Please not that this method is not foolproof: there are still ways to
1492 update the database. Consider this a safety net to catch applications
1493 that should not be issuing commands such as INSERT, UPDATE, or DELETE.
1494 This method requires DBI version 1.55 or better.
1496 pg_server_prepare (boolean)
1498 DBD::Pg specific attribute. Indicates if DBD::Pg should attempt to use
1500 server-side prepared statements. The default value, true, indicates
1501 that prepared statements should be used whenever possible. See the
1502 section on the "prepare" method for more information.
1503 pg_switch_prepared (integer)
1505 DBD::Pg specific attribute. Indicates when DBD::Pg will internally
1507 switch from using PQexecParams to PQexecPrepared. In other words, when
1508 it will start using server-side prepared statements (assuming all other
1509 requirements for them are met). The default value, 2, means that a
1510 prepared statement will be prepared and used the second and subsequent
1511 time execute is called. To always use PQexecPrepared instead of
1512 PQexecParams, set pg_switch_prepared to 1 (this was the default
1513 behavior in earlier versions). Setting pg_switch_prepared to 0 will
1514 force DBD::Pg to always use PQexecParams.
1515 pg_placeholder_dollaronly (boolean)
1517 DBD::Pg specific attribute. Defaults to false. When true, question
1519 marks inside of statements are not treated as placeholders. Useful for
1520 statements that contain unquoted question marks, such as geometric
1521 operators. Note that you may also simply escape question marks with a
1522 backslash to prevent them from being treated as placeholders.
1523 pg_placeholder_nocolons (boolean)
1525 DBD::Pg specific attribute. Defaults to false. When true, colons inside
1527 of statements are not treated as placeholders. Useful for statements
1528 that contain an array slice. You may also place a backslash directly
1529 before the colon to prevent it from being treated as a placeholder.
1530 pg_enable_utf8 (integer)
1532 DBD::Pg specific attribute. The behavior of DBD::Pg with regards to
1534 this flag has changed as of version 3.0.0. The default value for this
1535 attribute, -1, indicates that the internal Perl "utf8" flag will be
1536 turned on for all strings coming back from the database if the
1537 client_encoding is set to 'UTF8'. Use of this default is highly
1538 encouraged. If your code was previously using pg_enable_utf8, you can
1539 probably remove mention of it entirely.
1540 If this attribute is set to 0, then the internal "utf8" flag will
1542 *never* be turned on for returned data, regardless of the current
1543 client_encoding.
1544 If this attribute is set to 1, then the internal "utf8" flag will
1546 *always* be turned on for returned data, regardless of the current
1547 client_encoding (with the exception of bytea data).
1548 Note that the value of client_encoding is only checked on connection
1550 time. If you change the client_encoding to/from 'UTF8' after
1551 connecting, you can set pg_enable_utf8 to -1 to force DBD::Pg to read
1552 in the new client_encoding and act accordingly.
1553 pg_errorlevel (integer)
1555 DBD::Pg specific attribute. Sets the amount of information returned by
1557 the server's error messages. Valid entries are 0, 1, and 2. Any other
1558 number will be forced to the default value of 1.
1559 A value of 0 ("TERSE") will show severity, primary text, and position
1561 only and will usually fit on a single line. A value of 1 ("DEFAULT")
1562 will also show any detail, hint, or context fields. A value of 2
1563 ("VERBOSE") will show all available information.
1564 pg_lib_version (integer, read-only)
1566 DBD::Pg specific attribute. Indicates which version of PostgreSQL that
1568 DBD::Pg was compiled against. In other words, which libraries were
1569 used. Returns a number with major, minor, and revision together;
1570 version 8.1.4 would be returned as 80104.
1571 pg_server_version (integer, read-only)
1573 DBD::Pg specific attribute. Indicates which version of PostgreSQL that
1575 the current database handle is connected to. Returns a number with
1576 major, minor, and revision together; version 8.0.1 would be 80001.
1577 Name (string, read-only)
1579 Returns the name of the current database. This is the same as the DSN,
1581 without the "dbi:Pg:" part. Before version 2.0.0, this only returned
1582 the bare database name (e.g. 'foo'). From version 2.0.0 onwards, it
1583 returns the more correct output (e.g. 'dbname=foo')
1584 Username (string, read-only)
1586 Returns the name of the user connected to the database.
1588 pg_db (string, read-only)
1590 DBD::Pg specific attribute. Returns the name of the current database.
1592 pg_user (string, read-only)
1594 DBD::Pg specific attribute. Returns the name of the user that connected
1596 to the server.
1597 pg_host (string, read-only)
1599 DBD::Pg specific attribute. Returns the host of the current server
1601 connection. Locally connected hosts will return an empty string.
1602 pg_port (integer, read-only)
1604 DBD::Pg specific attribute. Returns the port of the connection to the
1606 server.
1607 pg_socket (integer, read-only)
1609 DBD::Pg specific attribute. Returns the file description number of the
1611 connection socket to the server.
1612 pg_pass (string, read-only)
1614 DBD::Pg specific attribute. Returns the password used to connect to the
1616 server.
1617 pg_options (string, read-only)
1619 DBD::Pg specific attribute. Returns the command-line options passed to
1621 the server. May be an empty string.
1622 pg_default_port (integer, read-only)
1624 DBD::Pg specific attribute. Returns the default port used if none is
1626 specifically given.
1627 pg_pid (integer, read-only)
1629 DBD::Pg specific attribute. Returns the process id (PID) of the backend
1631 server process handling the connection.
1632 pg_prepare_now (boolean)
1634 DBD::Pg specific attribute. Default is off. If true, then the "prepare"
1636 method will immediately prepare commands, rather than waiting until the
1637 first execute.
1638 pg_expand_array (boolean)
1640 DBD::Pg specific attribute. Defaults to true. If false, arrays returned
1642 from the server will not be changed into a Perl arrayref, but remain as
1643 a string.
1644 pg_async_status (integer, read-only)
1646 DBD::Pg specific attribute. Returns the current status of an
1648 asynchronous command. 0 indicates no asynchronous command is in
1649 progress, 1 indicates that an asynchronous command has started and -1
1650 indicated that an asynchronous command has been cancelled.
1651 pg_standard_conforming_strings (boolean, read-only)
1653 DBD::Pg specific attribute. Returns true if the server is currently
1655 using standard conforming strings. Only available if the target server
1656 is version 8.2 or better.
1657 pg_INV_READ (integer, read-only)
1659 Constant to be used for the mode in "pg_lo_creat" and "pg_lo_open".
1661 pg_INV_WRITE (integer, read-only)
1663 Constant to be used for the mode in "pg_lo_creat" and "pg_lo_open".
1665 Driver (handle, read-only)
1667 Holds the handle of the parent driver. The only recommended use for
1669 this is to find the name of the driver using:
1670 $dbh->{Driver}->{Name}
1672 pg_protocol (integer, read-only)
1674 DBD::Pg specific attribute. Returns the version of the PostgreSQL
1676 server. If DBD::Pg is unable to figure out the version, it will return
1677 a "0". Otherwise, a "3" is returned.
1678 RowCacheSize
1680 Not used by DBD::Pg
1682
1684 Statement Handle Methods
1685 bind_param
1686 $rv = $sth->bind_param($param_num, $bind_value);
1688 $rv = $sth->bind_param($param_num, $bind_value, $bind_type);
1689 $rv = $sth->bind_param($param_num, $bind_value, \%attr);
1690 Allows the user to bind a value and/or a data type to a placeholder.
1692 This is especially important when using server-side prepares. See the
1693 "prepare" method for more information.
1694 The value of $param_num is a number if using the '?' or '$1' style
1696 placeholders. If using ":foo" style placeholders, the complete name
1697 (e.g. ":foo") must be given. For numeric values, you can either use a
1698 number or use a literal '$1'. See the examples below.
1699 The $bind_value argument is fairly self-explanatory. A value of "undef"
1701 will bind a "NULL" to the placeholder. Using "undef" is useful when you
1702 want to change just the type and will be overwriting the value later.
1703 (Any value is actually usable, but "undef" is easy and efficient).
1704 The "\%attr" hash is used to indicate the data type of the placeholder.
1706 The default value is "varchar". If you need something else, you must
1707 use one of the values provided by DBI or by DBD::Pg. To use a SQL
1708 value, modify your "use DBI" statement at the top of your script as
1709 follows:
1710 use DBI qw(:sql_types);
1712 This will import some constants into your script. You can plug those
1714 directly into the "bind_param" call. Some common ones that you will
1715 encounter are:
1716 SQL_INTEGER
1718 To use PostgreSQL data types, import the list of values like this:
1720 use DBD::Pg qw(:pg_types);
1722 You can then set the data types by setting the value of the "pg_type"
1724 key in the hash passed to "bind_param". The current list of Postgres
1725 data types exported is:
1726 PG_ACLITEM PG_ACLITEMARRAY PG_ANY PG_ANYARRAY PG_ANYELEMENT PG_ANYENUM
1728 PG_ANYNONARRAY PG_ANYRANGE PG_BIT PG_BITARRAY PG_BOOL PG_BOOLARRAY
1729 PG_BOX PG_BOXARRAY PG_BPCHAR PG_BPCHARARRAY PG_BYTEA PG_BYTEAARRAY
1730 PG_CHAR PG_CHARARRAY PG_CID PG_CIDARRAY PG_CIDR PG_CIDRARRAY
1731 PG_CIRCLE PG_CIRCLEARRAY PG_CSTRING PG_CSTRINGARRAY PG_DATE PG_DATEARRAY
1732 PG_DATERANGE PG_DATERANGEARRAY PG_EVENT_TRIGGER PG_FDW_HANDLER PG_FLOAT4 PG_FLOAT4ARRAY
1733 PG_FLOAT8 PG_FLOAT8ARRAY PG_GTSVECTOR PG_GTSVECTORARRAY PG_INDEX_AM_HANDLER PG_INET
1734 PG_INETARRAY PG_INT2 PG_INT2ARRAY PG_INT2VECTOR PG_INT2VECTORARRAY PG_INT4
1735 PG_INT4ARRAY PG_INT4RANGE PG_INT4RANGEARRAY PG_INT8 PG_INT8ARRAY PG_INT8RANGE
1736 PG_INT8RANGEARRAY PG_INTERNAL PG_INTERVAL PG_INTERVALARRAY PG_JSON PG_JSONARRAY
1737 PG_JSONB PG_JSONBARRAY PG_JSONPATH PG_JSONPATHARRAY PG_LANGUAGE_HANDLER PG_LINE
1738 PG_LINEARRAY PG_LSEG PG_LSEGARRAY PG_MACADDR PG_MACADDR8 PG_MACADDR8ARRAY
1739 PG_MACADDRARRAY PG_MONEY PG_MONEYARRAY PG_NAME PG_NAMEARRAY PG_NUMERIC
1740 PG_NUMERICARRAY PG_NUMRANGE PG_NUMRANGEARRAY PG_OID PG_OIDARRAY PG_OIDVECTOR
1741 PG_OIDVECTORARRAY PG_OPAQUE PG_PATH PG_PATHARRAY PG_PG_ATTRIBUTE PG_PG_CLASS
1742 PG_PG_DDL_COMMAND PG_PG_DEPENDENCIES PG_PG_LSN PG_PG_LSNARRAY PG_PG_MCV_LIST PG_PG_NDISTINCT
1743 PG_PG_NODE_TREE PG_PG_PROC PG_PG_TYPE PG_POINT PG_POINTARRAY PG_POLYGON
1744 PG_POLYGONARRAY PG_RECORD PG_RECORDARRAY PG_REFCURSOR PG_REFCURSORARRAY PG_REGCLASS
1745 PG_REGCLASSARRAY PG_REGCONFIG PG_REGCONFIGARRAY PG_REGDICTIONARY PG_REGDICTIONARYARRAY PG_REGNAMESPACE
1746 PG_REGNAMESPACEARRAY PG_REGOPER PG_REGOPERARRAY PG_REGOPERATOR PG_REGOPERATORARRAY PG_REGPROC
1747 PG_REGPROCARRAY PG_REGPROCEDURE PG_REGPROCEDUREARRAY PG_REGROLE PG_REGROLEARRAY PG_REGTYPE
1748 PG_REGTYPEARRAY PG_TABLE_AM_HANDLER PG_TEXT PG_TEXTARRAY PG_TID PG_TIDARRAY
1749 PG_TIME PG_TIMEARRAY PG_TIMESTAMP PG_TIMESTAMPARRAY PG_TIMESTAMPTZ PG_TIMESTAMPTZARRAY
1750 PG_TIMETZ PG_TIMETZARRAY PG_TRIGGER PG_TSM_HANDLER PG_TSQUERY PG_TSQUERYARRAY
1751 PG_TSRANGE PG_TSRANGEARRAY PG_TSTZRANGE PG_TSTZRANGEARRAY PG_TSVECTOR PG_TSVECTORARRAY
1752 PG_TXID_SNAPSHOT PG_TXID_SNAPSHOTARRAY PG_UNKNOWN PG_UUID PG_UUIDARRAY PG_VARBIT
1753 PG_VARBITARRAY PG_VARCHAR PG_VARCHARARRAY PG_VOID PG_XID PG_XIDARRAY
1754 PG_XML PG_XMLARRAY
1755 Data types are "sticky," in that once a data type is set to a certain
1757 placeholder, it will remain for that placeholder, unless it is
1758 explicitly set to something else afterwards. If the statement has
1759 already been prepared, and you switch the data type to something else,
1760 DBD::Pg will re-prepare the statement for you before doing the next
1761 execute.
1762 Examples:
1764 use DBI qw(:sql_types);
1766 use DBD::Pg qw(:pg_types);
1767 $SQL = "SELECT id FROM ptable WHERE size > ? AND title = ?";
1769 $sth = $dbh->prepare($SQL);
1770 ## Both arguments below are bound to placeholders as "varchar"
1772 $sth->execute(123, "Merk");
1773 ## Reset the datatype for the first placeholder to an integer
1775 $sth->bind_param(1, undef, SQL_INTEGER);
1776 ## The "undef" bound above is not used, since we supply params to execute
1778 $sth->execute(123, "Merk");
1779 ## Set the first placeholder's value and data type
1781 $sth->bind_param(1, 234, { pg_type => PG_TIMESTAMP });
1782 ## Set the second placeholder's value and data type.
1784 ## We don't send a third argument, so the default "varchar" is used
1785 $sth->bind_param('$2', "Zool");
1786 ## We realize that the wrong data type was set above, so we change it:
1788 $sth->bind_param('$1', 234, { pg_type => SQL_INTEGER });
1789 ## We also got the wrong value, so we change that as well.
1791 ## Because the data type is sticky, we don't need to change it
1792 $sth->bind_param(1, 567);
1793 ## This executes the statement with 567 (integer) and "Zool" (varchar)
1795 $sth->execute();
1796 bind_param_inout
1798 $rv = $sth->bind_param_inout($param_num, \$scalar, 0);
1800 Experimental support for this feature is provided. The first argument
1802 to bind_param_inout should be a placeholder number. The second argument
1803 should be a reference to a scalar variable in your script. The third
1804 argument is not used and should simply be set to 0. Note that what this
1805 really does is assign a returned column to the variable, in the order
1806 in which the column appears. For example:
1807 my $foo = 123;
1809 $sth = $dbh->prepare("SELECT 1+?::int");
1810 $sth->bind_param_inout(1, \$foo, 0);
1811 $foo = 222;
1812 $sth->execute(444);
1813 $sth->fetch;
1814 The above will cause $foo to have a new value of "223" after the final
1816 fetch. Note that the variables bound in this manner are very sticky,
1817 and will trump any values passed in to execute. This is because the
1818 binding is done as late as possible, at the execute() stage, allowing
1819 the value to be changed between the time it was bound and the time the
1820 query is executed. Thus, the above execute is the same as:
1821 $sth->execute();
1823 bind_param_array
1825 $rv = $sth->bind_param_array($param_num, $array_ref_or_value)
1827 $rv = $sth->bind_param_array($param_num, $array_ref_or_value, $bind_type)
1828 $rv = $sth->bind_param_array($param_num, $array_ref_or_value, \%attr)
1829 Binds an array of values to a placeholder, so that each is used in turn
1831 by a call to the "execute_array" method.
1832 execute
1834 $rv = $sth->execute(@bind_values);
1836 Executes a previously prepared statement. In addition to "UPDATE",
1838 "DELETE", "INSERT" statements, for which it returns always the number
1839 of affected rows, the "execute" method can also be used for "SELECT ...
1840 INTO table" statements.
1841 The "prepare/bind/execute" process has changed significantly for
1843 PostgreSQL servers 7.4 and later: please see the "prepare()" and
1844 "bind_param()" entries for much more information.
1845 Setting one of the bind_values to "undef" is the equivalent of setting
1847 the value to NULL in the database. Setting the bind_value to
1848 $DBDPG_DEFAULT is equivalent to sending the literal string 'DEFAULT' to
1849 the backend. Note that using this option will force server-side
1850 prepares off until such time as PostgreSQL supports using DEFAULT in
1851 prepared statements.
1852 DBD::Pg also supports passing in arrays to execute: simply pass in an
1854 arrayref, and DBD::Pg will flatten it into a string suitable for input
1855 on the backend.
1856 If you are using Postgres version 8.2 or greater, you can also use any
1858 of the fetch methods to retrieve the values of a "RETURNING" clause
1859 after you execute an "UPDATE", "DELETE", or "INSERT". For example:
1860 $dbh->do(q{CREATE TABLE abc (id SERIAL, country TEXT)});
1862 $SQL = q{INSERT INTO abc (country) VALUES (?) RETURNING id};
1863 $sth = $dbh->prepare($SQL);
1864 $sth->execute('France');
1865 $countryid = $sth->fetch()->[0];
1866 $sth->execute('New Zealand');
1867 $countryid = $sth->fetch()->[0];
1868 execute_array
1870 $tuples = $sth->execute_array() or die $sth->errstr;
1872 $tuples = $sth->execute_array(\%attr) or die $sth->errstr;
1873 $tuples = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
1874 ($tuples, $rows) = $sth->execute_array(\%attr) or die $sth->errstr;
1876 ($tuples, $rows) = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
1877 Execute a prepared statement once for each item in a passed-in hashref,
1879 or items that were previously bound via the "bind_param_array" method.
1880 See the DBI documentation for more details.
1881 execute_for_fetch
1883 $tuples = $sth->execute_for_fetch($fetch_tuple_sub);
1885 $tuples = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
1886 ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub);
1888 ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
1889 Used internally by the "execute_array" method, and rarely used
1891 directly. See the DBI documentation for more details.
1892 fetchrow_arrayref
1894 $ary_ref = $sth->fetchrow_arrayref;
1896 Fetches the next row of data from the statement handle, and returns a
1898 reference to an array holding the column values. Any columns that are
1899 NULL are returned as undef within the array.
1900 If there are no more rows or if an error occurs, then this method
1902 return undef. You should check "$sth->err" afterwards (or use the
1903 RaiseError attribute) to discover if the undef returned was due to an
1904 error.
1905 Note that the same array reference is returned for each fetch, so don't
1907 store the reference and then use it after a later fetch. Also, the
1908 elements of the array are also reused for each row, so take care if you
1909 want to take a reference to an element. See also "bind_columns".
1910 fetchrow_array
1912 @ary = $sth->fetchrow_array;
1914 Similar to the "fetchrow_arrayref" method, but returns a list of column
1916 information rather than a reference to a list. Do not use this in a
1917 scalar context.
1918 fetchrow_hashref
1920 $hash_ref = $sth->fetchrow_hashref;
1922 $hash_ref = $sth->fetchrow_hashref($name);
1923 Fetches the next row of data and returns a hashref containing the name
1925 of the columns as the keys and the data itself as the values. Any NULL
1926 value is returned as an undef value.
1927 If there are no more rows or if an error occurs, then this method
1929 return undef. You should check "$sth->err" afterwards (or use the
1930 RaiseError attribute) to discover if the undef returned was due to an
1931 error.
1932 The optional $name argument should be either "NAME", "NAME_lc" or
1934 "NAME_uc", and indicates what sort of transformation to make to the
1935 keys in the hash.
1936 fetchall_arrayref
1938 $tbl_ary_ref = $sth->fetchall_arrayref();
1940 $tbl_ary_ref = $sth->fetchall_arrayref( $slice );
1941 $tbl_ary_ref = $sth->fetchall_arrayref( $slice, $max_rows );
1942 Returns a reference to an array of arrays that contains all the
1944 remaining rows to be fetched from the statement handle. If there are no
1945 more rows, an empty arrayref will be returned. If an error occurs, the
1946 data read in so far will be returned. Because of this, you should
1947 always check "$sth->err" after calling this method, unless RaiseError
1948 has been enabled.
1949 If $slice is an array reference, fetchall_arrayref uses the
1951 "fetchrow_arrayref" method to fetch each row as an array ref. If the
1952 $slice array is not empty then it is used as a slice to select
1953 individual columns by perl array index number (starting at 0, unlike
1954 column and parameter numbers which start at 1).
1955 With no parameters, or if $slice is undefined, fetchall_arrayref acts
1957 as if passed an empty array ref.
1958 If $slice is a hash reference, fetchall_arrayref uses
1960 "fetchrow_hashref" to fetch each row as a hash reference.
1961 See the DBI documentation for a complete discussion.
1963 fetchall_hashref
1965 $hash_ref = $sth->fetchall_hashref( $key_field );
1967 Returns a hashref containing all rows to be fetched from the statement
1969 handle. See the DBI documentation for a full discussion.
1970 finish
1972 $rv = $sth->finish;
1974 Indicates to DBI that you are finished with the statement handle and
1976 are not going to use it again. Only needed when you have not fetched
1977 all the possible rows.
1978 rows
1980 $rv = $sth->rows;
1982 Returns the number of rows returned by the last query. In contrast to
1984 many other DBD modules, the number of rows is available immediately
1985 after calling "$sth->execute". Note that the "execute" method itself
1986 returns the number of rows itself, which means that this method is
1987 rarely needed.
1988 bind_col
1990 $rv = $sth->bind_col($column_number, \$var_to_bind);
1992 $rv = $sth->bind_col($column_number, \$var_to_bind, \%attr );
1993 $rv = $sth->bind_col($column_number, \$var_to_bind, $bind_type );
1994 Binds a Perl variable and/or some attributes to an output column of a
1996 SELECT statement. Column numbers count up from 1. You do not need to
1997 bind output columns in order to fetch data.
1998 See the DBI documentation for a discussion of the optional parameters
2000 "\%attr" and $bind_type
2001 bind_columns
2003 $rv = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
2005 Calls the "bind_col" method for each column in the SELECT statement,
2007 using the supplied list.
2008 dump_results
2010 $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
2012 Fetches all the rows from the statement handle, calls "DBI::neat_list"
2014 for each row, and prints the results to $fh (which defaults to STDOUT).
2015 Rows are separated by $lsep (which defaults to a newline). Columns are
2016 separated by $fsep (which defaults to a comma). The $maxlen controls
2017 how wide the output can be, and defaults to 35.
2018 This method is designed as a handy utility for prototyping and testing
2020 queries. Since it uses "neat_list" to format and edit the string for
2021 reading by humans, it is not recommended for data transfer
2022 applications.
2023 blob_read
2025 $blob = $sth->blob_read($id, $offset, $len);
2027 Supported by DBD::Pg. This method is implemented by DBI but not
2029 currently documented by DBI, so this method might change.
2030 This method seems to be heavily influenced by the current
2032 implementation of blobs in Oracle. Nevertheless we try to be as
2033 compatible as possible. Whereas Oracle suffers from the limitation that
2034 blobs are related to tables and every table can have only one blob
2035 (datatype LONG), PostgreSQL handles its blobs independent of any table
2036 by using so-called object identifiers. This explains why the
2037 "blob_read" method is blessed into the STATEMENT package and not part
2038 of the DATABASE package. Here the field parameter has been used to
2039 handle this object identifier. The offset and len parameters may be set
2040 to zero, in which case the whole blob is fetched at once.
2041 See also the PostgreSQL-specific functions concerning blobs, which are
2043 available via the "func" interface.
2044 For further information and examples about blobs, please read the
2046 chapter about Large Objects in the PostgreSQL Programmer's Guide at
2047 <http://www.postgresql.org/docs/current/static/largeobjects.html>.
2048 pg_canonical_ids
2050 $data = $sth->pg_canonical_ids;
2052 DBD::Pg specific method. It returns Oid of table and position in table
2054 for every column in result set.
2055 Returns array of arrays with Table Oid and Column Position for every
2057 column in result set or undef if current column is not a simple
2058 reference.
2059 pg_canonical_names
2061 $data = $sth->pg_canonical_names;
2063 DBD::Pg specific method. It returns array of original (or canonical)
2065 names (from where this data is actually came from) of columns in
2066 Schema.Table.Column format or undef if current column is not a simple
2067 reference.
2068 Note that this method is quite slow because it need additional
2070 information from server for every column that is simple reference.
2071 Consider to use "pg_canonical_ids" instead.
2072 Statement Handle Attributes
2074 NUM_OF_FIELDS (integer, read-only)
2075 Returns the number of columns returned by the current statement. A
2077 number will only be returned for SELECT statements, for SHOW statements
2078 (which always return 1), and for INSERT, UPDATE, and DELETE statements
2079 which contain a RETURNING clause. This method returns undef if called
2080 before "execute()".
2081 NUM_OF_PARAMS (integer, read-only)
2083 Returns the number of placeholders in the current statement.
2085 NAME (arrayref, read-only)
2087 Returns an arrayref of column names for the current statement. This
2089 method will only work for SELECT statements, for SHOW statements, and
2090 for INSERT, UPDATE, and DELETE statements which contain a RETURNING
2091 clause. This method returns undef if called before "execute()".
2092 NAME_lc (arrayref, read-only)
2094 The same as the "NAME" attribute, except that all column names are
2096 forced to lower case.
2097 NAME_uc (arrayref, read-only)
2099 The same as the "NAME" attribute, except that all column names are
2101 forced to upper case.
2102 NAME_hash (hashref, read-only)
2104 Similar to the "NAME" attribute, but returns a hashref of column names
2106 instead of an arrayref. The names of the columns are the keys of the
2107 hash, and the values represent the order in which the columns are
2108 returned, starting at 0. This method returns undef if called before
2109 "execute()".
2110 NAME_lc_hash (hashref, read-only)
2112 The same as the "NAME_hash" attribute, except that all column names are
2114 forced to lower case.
2115 NAME_uc_hash (hashref, read-only)
2117 The same as the "NAME_hash" attribute, except that all column names are
2119 forced to lower case.
2120 TYPE (arrayref, read-only)
2122 Returns an arrayref indicating the data type for each column in the
2124 statement. This method returns undef if called before "execute()".
2125 PRECISION (arrayref, read-only)
2127 Returns an arrayref of integer values for each column returned by the
2129 statement. The number indicates the precision for "NUMERIC" columns,
2130 the size in number of characters for "CHAR" and "VARCHAR" columns, and
2131 for all other types of columns it returns the number of bytes. This
2132 method returns undef if called before "execute()".
2133 SCALE (arrayref, read-only)
2135 Returns an arrayref of integer values for each column returned by the
2137 statement. The number indicates the scale of the that column. The only
2138 type that will return a value is "NUMERIC". This method returns undef
2139 if called before "execute()".
2140 NULLABLE (arrayref, read-only)
2142 Returns an arrayref of integer values for each column returned by the
2144 statement. The number indicates if the column is nullable or not. 0 =
2145 not nullable, 1 = nullable, 2 = unknown. This method returns undef if
2146 called before "execute()".
2147 Database (dbh, read-only)
2149 Returns the database handle this statement handle was created from.
2151 ParamValues (hash ref, read-only)
2153 Returns a reference to a hash containing the values currently bound to
2155 placeholders. If the "named parameters" type of placeholders are being
2156 used (such as ":foo"), then the keys of the hash will be the names of
2157 the placeholders (without the colon). If the "dollar sign numbers" type
2158 of placeholders are being used, the keys of the hash will be the
2159 numbers, without the dollar signs. If the "question mark" type is used,
2160 integer numbers will be returned, starting at one and increasing for
2161 every placeholder.
2162 If this method is called before "execute", the literal values passed in
2164 are returned. If called after "execute", then the quoted versions of
2165 the values are returned.
2166 ParamTypes (hash ref, read-only)
2168 Returns a reference to a hash containing the type names currently bound
2170 to placeholders. The keys are the same as returned by the ParamValues
2171 method. The values are hashrefs containing a single key value pair, in
2172 which the key is either 'TYPE' if the type has a generic SQL
2173 equivalent, and 'pg_type' if the type can only be expressed by a
2174 Postgres type. The value is the internal number corresponding to the
2175 type originally passed in. (Placeholders that have not yet been bound
2176 will return undef as the value). This allows the output of ParamTypes
2177 to be passed back to the "bind_param" method.
2178 Statement (string, read-only)
2180 Returns the statement string passed to the most recent "prepare" method
2182 called in this database handle, even if that method failed. This is
2183 especially useful where "RaiseError" is enabled and the exception
2184 handler checks $@ and sees that a "prepare" method call failed.
2185 pg_current_row (integer, read-only)
2187 DBD::Pg specific attribute. Returns the number of the tuple (row) that
2189 was most recently fetched. Returns zero before and after fetching is
2190 performed.
2191 pg_numbound (integer, read-only)
2193 DBD::Pg specific attribute. Returns the number of placeholders that are
2195 currently bound (via bind_param).
2196 pg_bound (hashref, read-only)
2198 DBD::Pg specific attribute. Returns a hash of all named placeholders.
2200 The key is the name of the placeholder, and the value is a 0 or a 1,
2201 indicating if the placeholder has been bound yet (e.g. via bind_param)
2202 pg_size (arrayref, read-only)
2204 DBD::Pg specific attribute. It returns a reference to an array of
2206 integer values for each column. The integer shows the size of the
2207 column in bytes. Variable length columns are indicated by -1.
2208 pg_type (arrayref, read-only)
2210 DBD::Pg specific attribute. It returns a reference to an array of
2212 strings for each column. The string shows the name of the data_type.
2213 pg_segments (arrayref, read-only)
2215 DBD::Pg specific attribute. Returns an arrayref of the query split on
2217 the placeholders.
2218 pg_oid_status (integer, read-only)
2220 DBD::Pg specific attribute. It returns the OID of the last INSERT
2222 command.
2223 pg_cmd_status (integer, read-only)
2225 DBD::Pg specific attribute. It returns the type of the last command.
2227 Possible types are: "INSERT", "DELETE", "UPDATE", "SELECT".
2228 pg_direct (boolean)
2230 DBD::Pg specific attribute. Default is false. If true, the query is
2232 passed directly to the backend without parsing for placeholders.
2233 pg_prepare_now (boolean)
2235 DBD::Pg specific attribute. Default is off. If true, the query will be
2237 immediately prepared, rather than waiting for the "execute" call.
2238 pg_prepare_name (string)
2240 DBD::Pg specific attribute. Specifies the name of the prepared
2242 statement to use for this statement handle. Not normally needed, see
2243 the section on the "prepare" method for more information.
2244 pg_server_prepare (boolean)
2246 DBD::Pg specific attribute. Indicates if DBD::Pg should attempt to use
2248 server-side prepared statements for this statement handle. The default
2249 value, true, indicates that prepared statements should be used whenever
2250 possible. See the section on the "prepare" method for more information.
2251 pg_switch_prepared (integer)
2253 DBD::Pg specific attribute. Indicates when DBD::Pg will internally
2255 switch from using PQexecParams to PQexecPrepared. In other words, when
2256 it will start using server-side prepared statements (assuming all other
2257 requirements for them are met). The default value, 2, means that a
2258 prepared statement will be prepared and used the second and subsequent
2259 time execute is called. To always use PQexecPrepared instead of
2260 PQexecParams, set pg_switch_prepared to 1 (this was the default
2261 behavior in earlier versions). Setting pg_switch_prepared to 0 will
2262 force DBD::Pg to always use PQexecParams.
2263 pg_placeholder_dollaronly (boolean)
2265 DBD::Pg specific attribute. Defaults to false. When true, question
2267 marks inside of the query being prepared are not treated as
2268 placeholders. Useful for statements that contain unquoted question
2269 marks, such as geometric operators. Note that you may also simply
2270 escape question marks with a backslash to prevent them from being
2271 treated as placeholders.
2272 pg_placeholder_nocolons (boolean)
2274 DBD::Pg specific attribute. Defaults to false. When true, colons inside
2276 of statements are not treated as placeholders. Useful for statements
2277 that contain an array slice. You may also place a backslash directly
2278 before the colon to prevent it from being treated as a placeholder.
2279 pg_async (integer)
2281 DBD::Pg specific attribute. Indicates the current behavior for
2283 asynchronous queries. See the section on "Asynchronous Constants" for
2284 more information.
2285 pg_async_status (integer, read-only)
2287 DBD::Pg specific attribute. Returns the current status of an
2289 asynchronous command. 0 indicates no asynchronous command is in
2290 progress, 1 indicates that an asynchronous command has started and -1
2291 indicated that an asynchronous command has been cancelled.
2292 RowsInCache
2294 Not used by DBD::Pg
2296 RowCache
2298 Not used by DBD::Pg
2300 CursorName
2302 Not used by DBD::Pg. See the note about "Cursors" elsewhere in this
2304 document.
2305
2307 Encoding
2308 DBD::Pg has extensive support for a client_encoding of UTF-8, and most
2309 things like encoding and decoding should happen automatically. If you
2310 are using a different encoding, you will need do the encoding and
2311 decoding yourself. For this reason, it is highly recommended to always
2312 use a client_encoding of UTF-8. The server_encoding can be anything,
2313 and no recommendations are made there, other than avoid SQL_ASCII
2314 whenever possible.
2315 Transactions
2317 Transaction behavior is controlled via the "AutoCommit" attribute. For
2318 a complete definition of "AutoCommit" please refer to the DBI
2319 documentation.
2320 According to the DBI specification the default for "AutoCommit" is a
2322 true value. In this mode, any change to the database becomes valid
2323 immediately. Any "BEGIN", "COMMIT" or "ROLLBACK" statements will be
2324 rejected. Note that preparing a statement does not always contact the
2325 server, as the actual "PREPARE" is usually postponed until the first
2326 call to "execute".
2327 Savepoints
2329 PostgreSQL version 8.0 introduced the concept of savepoints, which
2330 allows transactions to be rolled back to a certain point without
2331 affecting the rest of the transaction. DBD::Pg encourages using the
2332 following methods to control savepoints:
2333 "pg_savepoint"
2335 Creates a savepoint. This will fail unless you are inside of a
2337 transaction. The only argument is the name of the savepoint. Note that
2338 PostgreSQL DOES allow multiple savepoints with the same name to exist.
2339 $dbh->pg_savepoint("mysavepoint");
2341 "pg_rollback_to"
2343 Rolls the database back to a named savepoint, discarding any work
2345 performed after that point. If more than one savepoint with that name
2346 exists, rolls back to the most recently created one.
2347 $dbh->pg_rollback_to("mysavepoint");
2349 "pg_release"
2351 Releases (or removes) a named savepoint. If more than one savepoint
2353 with that name exists, it will only destroy the most recently created
2354 one. Note that all savepoints created after the one being released are
2355 also destroyed.
2356 $dbh->pg_release("mysavepoint");
2358 Asynchronous Queries
2360 It is possible to send a query to the backend and have your script do
2361 other work while the query is running on the backend. Both queries sent
2362 by the "do" method, and by the "execute" method can be sent
2363 asynchronously. The basic usage is as follows:
2364 use DBD::Pg ':async';
2366 print "Async do() example:\n";
2368 $dbh->do("SELECT long_running_query()", {pg_async => PG_ASYNC});
2369 do_something_else();
2370 {
2371 if ($dbh->pg_ready()) {
2372 $res = $dbh->pg_result();
2373 print "Result of do(): $res\n";
2374 }
2375 print "Query is still running...\n";
2376 if (cancel_request_received) {
2377 $dbh->pg_cancel();
2378 }
2379 sleep 1;
2380 redo;
2381 }
2382 print "Async prepare/execute example:\n";
2384 $sth = $dbh->prepare("SELECT long_running_query(1)", {pg_async => PG_ASYNC});
2385 $sth->execute();
2386 ## Changed our mind, cancel and run again:
2388 $sth = $dbh->prepare("SELECT 678", {pg_async => PG_ASYNC + PG_OLDQUERY_CANCEL});
2389 $sth->execute();
2390 do_something_else();
2392 if (!$sth->pg_ready) {
2394 do_another_thing();
2395 }
2396 ## We wait until it is done, and get the result:
2398 $res = $dbh->pg_result();
2399 Asynchronous Constants
2401 There are currently three asynchronous constants exported by DBD::Pg.
2403 You can import all of them by putting either of these at the top of
2404 your script:
2405 use DBD::Pg;
2407 use DBD::Pg ':async';
2409 You may also use the numbers instead of the constants, but using the
2411 constants is recommended as it makes your script more readable.
2412 PG_ASYNC
2414 This is a constant for the number 1. It is passed to either the
2415 "do" or the "prepare" method as a value to the pg_async key and
2416 indicates that the query should be sent asynchronously.
2417 PG_OLDQUERY_CANCEL
2419 This is a constant for the number 2. When passed to either the "do"
2420 or the "prepare" method, it causes any currently running
2421 asynchronous query to be cancelled and rolled back. It has no
2422 effect if no asynchronous query is currently running.
2423 PG_OLDQUERY_WAIT
2425 This is a constant for the number 4. When passed to either the "do"
2426 or the "prepare" method, it waits for any currently running
2427 asynchronous query to complete. It has no effect if there is no
2428 asynchronous query currently running.
2429 Asynchronous Methods
2431 pg_cancel
2433 This database-level method attempts to cancel any currently running
2434 asynchronous query. It returns true if the cancel succeeded, and
2435 false otherwise. Note that a query that has finished before this
2436 method is executed will also return false. WARNING: a successful
2437 cancellation may leave the database in an unusable state, so you
2438 may need to ROLLBACK or ROLLBACK TO a savepoint. As of version
2439 2.17.0 of DBD::Pg, rollbacks are not done automatically.
2440 $result = $dbh->pg_cancel();
2442 pg_ready
2444 This method can be called as a database handle method or (for
2445 convenience) as a statement handle method. Both simply see if a
2446 previously issued asynchronous query has completed yet. It returns
2447 true if the statement has finished, in which case you should then
2448 call the "pg_result" method. Calls to "pg_ready()" should only be
2449 used when you have other things to do while the query is running.
2450 If you simply want to wait until the query is done, do not call
2451 pg_ready() over and over, but simply call the pg_result() method.
2452 my $time = 0;
2454 while (!$dbh->pg_ready) {
2455 print "Query is still running. Seconds: $time\n";
2456 $time++;
2457 sleep 1;
2458 }
2459 $result = $dbh->pg_result;
2460 pg_result
2462 This database handle method returns the results of a previously
2463 issued asynchronous query. If the query is still running, this
2464 method will wait until it has finished. The result returned is the
2465 number of rows: the same thing that would have been returned by the
2466 asynchronous "do" or "execute" if it had been called without an
2467 asynchronous flag.
2468 $result = $dbh->pg_result;
2470 Asynchronous Examples
2472 Here are some working examples of asynchronous queries. Note that we'll
2474 use the pg_sleep function to emulate a long-running query.
2475 use strict;
2477 use warnings;
2478 use Time::HiRes 'sleep';
2479 use DBD::Pg ':async';
2480 my $dbh = DBI->connect('dbi:Pg:dbname=postgres', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
2482 ## Kick off a long running query on the first database:
2484 my $sth = $dbh->prepare("SELECT pg_sleep(?)", {pg_async => PG_ASYNC});
2485 $sth->execute(5);
2486 ## While that is running, do some other things
2488 print "Your query is processing. Thanks for waiting\n";
2489 check_on_the_kids(); ## Expensive sub, takes at least three seconds.
2490 while (!$dbh->pg_ready) {
2492 check_on_the_kids();
2493 ## If the above function returns quickly for some reason, we add a small sleep
2494 sleep 0.1;
2495 }
2496 print "The query has finished. Gathering results\n";
2498 my $result = $sth->pg_result;
2499 print "Result: $result\n";
2500 my $info = $sth->fetchall_arrayref();
2501 Without asynchronous queries, the above script would take about 8
2503 seconds to run: five seconds waiting for the execute to finish, then
2504 three for the check_on_the_kids() function to return. With asynchronous
2505 queries, the script takes about 6 seconds to run, and gets in two
2506 iterations of check_on_the_kids in the process.
2507 Here's an example showing the ability to cancel a long-running query.
2509 Imagine two slave databases in different geographic locations over a
2510 slow network. You need information as quickly as possible, so you query
2511 both at once. When you get an answer, you tell the other one to stop
2512 working on your query, as you don't need it anymore.
2513 use strict;
2515 use warnings;
2516 use Time::HiRes 'sleep';
2517 use DBD::Pg ':async';
2518 my $dbhslave1 = DBI->connect('dbi:Pg:dbname=postgres;host=slave1', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
2520 my $dbhslave2 = DBI->connect('dbi:Pg:dbname=postgres;host=slave2', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
2521 $SQL = "SELECT count(*) FROM largetable WHERE flavor='blueberry'";
2523 my $sth1 = $dbhslave1->prepare($SQL, {pg_async => PG_ASYNC});
2525 my $sth2 = $dbhslave2->prepare($SQL, {pg_async => PG_ASYNC});
2526 $sth1->execute();
2528 $sth2->execute();
2529 my $winner;
2531 while (!defined $winner) {
2532 if ($sth1->pg_ready) {
2533 $winner = 1;
2534 }
2535 elsif ($sth2->pg_ready) {
2536 $winner = 2;
2537 }
2538 Time::HiRes::sleep 0.05;
2539 }
2540 my $count;
2542 if ($winner == 1) {
2543 $sth2->pg_cancel();
2544 $sth1->pg_result();
2545 $count = $sth1->fetchall_arrayref()->[0][0];
2546 }
2547 else {
2548 $sth1->pg_cancel();
2549 $sth2->pg_result();
2550 $count = $sth2->fetchall_arrayref()->[0][0];
2551 }
2552 Array support
2554 DBD::Pg allows arrays (as arrayrefs) to be passed in to both the
2555 "quote" and the "execute" methods. In both cases, the array is
2556 flattened into a string representing a Postgres array.
2557 When fetching rows from a table that contains a column with an array
2559 type, the result will be passed back to your script as an arrayref.
2560 To turn off the automatic parsing of returned arrays into arrayrefs,
2562 you can set the attribute pg_expand_array, which is true by default.
2563 $dbh->{pg_expand_array} = 0;
2565 COPY support
2567 DBD::Pg allows for quick (bulk) reading and storing of data by using
2568 the COPY command. The basic process is to use "$dbh->do" to issue a
2569 COPY command, and then to either add rows using "pg_putcopydata", or to
2570 read them by using "pg_getcopydata".
2571 The first step is to put the server into "COPY" mode. This is done by
2573 sending a complete COPY command to the server, by using the "do"
2574 method. For example:
2575 $dbh->do("COPY foobar FROM STDIN");
2577 This would tell the server to enter a COPY IN mode (yes, that's
2579 confusing, but the mode is COPY IN because of the command COPY FROM).
2580 It is now ready to receive information via the "pg_putcopydata" method.
2581 The complete syntax of the COPY command is more complex and not
2582 documented here: the canonical PostgreSQL documentation for COPY can be
2583 found at:
2584 http://www.postgresql.org/docs/current/static/sql-copy.html
2586 Once a COPY command has been issued, no other SQL commands are allowed
2588 until "pg_putcopyend" has been issued (for COPY FROM), or the final
2589 "pg_getcopydata" has been called (for COPY TO).
2590 Note: All other COPY methods (pg_putline, pg_getline, etc.) are now
2592 heavily deprecated in favor of the pg_getcopydata, pg_putcopydata, and
2593 pg_putcopyend methods.
2594 pg_getcopydata
2596 Used to retrieve data from a table after the server has been put into a
2598 COPY OUT mode by calling "COPY tablename TO STDOUT". Data is always
2599 returned one data row at a time. The first argument to pg_getcopydata
2600 is the variable into which the data will be stored (this variable
2601 should not be undefined, or it may throw a warning, although it may be
2602 a reference). The pg_getcopydata method returns a number greater than 1
2603 indicating the new size of the variable, or a -1 when the COPY has
2604 finished. Once a -1 has been returned, no other action is necessary, as
2605 COPY mode will have already terminated. Example:
2606 $dbh->do("COPY mytable TO STDOUT");
2608 my @data;
2609 my $x=0;
2610 1 while $dbh->pg_getcopydata($data[$x++]) >= 0;
2611 There is also a variation of this method called pg_getcopydata_async,
2613 which, as the name suggests, returns immediately. The only difference
2614 from the original method is that this version may return a 0,
2615 indicating that the row is not ready to be delivered yet. When this
2616 happens, the variable has not been changed, and you will need to call
2617 the method again until you get a non-zero result. (Data is still
2618 always returned one data row at a time.)
2619 pg_putcopydata
2621 Used to put data into a table after the server has been put into COPY
2623 IN mode by calling "COPY tablename FROM STDIN". The only argument is
2624 the data you want inserted. Issue a pg_putcopyend() when you have added
2625 all your rows.
2626 The default delimiter is a tab character, but this can be changed in
2628 the COPY statement. Returns a 1 on successful input. Examples:
2629 ## Simple example:
2631 $dbh->do("COPY mytable FROM STDIN");
2632 $dbh->pg_putcopydata("123\tPepperoni\t3\n");
2633 $dbh->pg_putcopydata("314\tMushroom\t8\n");
2634 $dbh->pg_putcopydata("6\tAnchovies\t100\n");
2635 $dbh->pg_putcopyend();
2636 ## This example uses explicit columns and a custom delimiter
2638 $dbh->do("COPY mytable(flavor, slices) FROM STDIN WITH DELIMITER '~'");
2639 $dbh->pg_putcopydata("Pepperoni~123\n");
2640 $dbh->pg_putcopydata("Mushroom~314\n");
2641 $dbh->pg_putcopydata("Anchovies~6\n");
2642 $dbh->pg_putcopyend();
2643 pg_putcopyend
2645 When you are finished with pg_putcopydata, call pg_putcopyend to let
2647 the server know that you are done, and it will return to a normal, non-
2648 COPY state. Returns a 1 on success. This method will fail if called
2649 when not in COPY IN mode.
2650 Large Objects
2652 DBD::Pg supports all largeobject functions provided by libpq via the
2653 "$dbh->pg_lo*" methods. Please note that access to a large object, even
2654 read-only large objects, must be put into a transaction.
2655 Cursors
2657 Although PostgreSQL supports cursors, they have not been used in the
2658 current implementation. When DBD::Pg was created, cursors in PostgreSQL
2659 could only be used inside a transaction block. Because only one
2660 transaction block at a time is allowed, this would have implied the
2661 restriction not to use any nested "SELECT" statements. Therefore the
2662 "execute" method fetches all data at once into data structures located
2663 in the front-end application. This fact must to be considered when
2664 selecting large amounts of data!
2665 You can use cursors in your application, but you'll need to do a little
2667 work. First you must declare your cursor. Now you can issue queries
2668 against the cursor, then select against your queries. This typically
2669 results in a double loop, like this:
2670 # WITH HOLD is not needed if AutoCommit is off
2672 $dbh->do("DECLARE csr CURSOR WITH HOLD FOR $sql");
2673 while (1) {
2674 my $sth = $dbh->prepare("fetch 1000 from csr");
2675 $sth->execute;
2676 last if 0 == $sth->rows;
2677 while (my $row = $sth->fetchrow_hashref) {
2679 # Do something with the data.
2680 }
2681 }
2682 $dbh->do("CLOSE csr");
2683 Datatype bool
2685 The current implementation of PostgreSQL returns 't' for true and 'f'
2686 for false. From the Perl point of view, this is a rather unfortunate
2687 choice. DBD::Pg therefore translates the result for the "BOOL" data
2688 type in a Perlish manner: 'f' becomes the number 0 and 't' becomes the
2689 number 1. This way the application does not have to check the database-
2690 specific returned values for the data-type "BOOL" because Perl treats 0
2691 as false and 1 as true. You may set the pg_bool_tf attribute to a true
2692 value to change the values back to 't' and 'f' if you wish.
2693 Boolean values can be passed to PostgreSQL as TRUE, 't', 'true', 'y',
2695 'yes' or '1' for true and FALSE, 'f', 'false', 'n', 'no' or '0' for
2696 false.
2697 Schema support
2699 The PostgreSQL schema concept may differ from those of other databases.
2700 In a nutshell, a schema is a named collection of objects within a
2701 single database. Please refer to the PostgreSQL documentation for more
2702 details:
2703 <http://www.postgresql.org/docs/current/static/ddl-schemas.html>
2705 DBD::Pg does not provide explicit support for PostgreSQL schemas.
2707 However, schema functionality may be used without any restrictions by
2708 explicitly addressing schema objects, e.g.
2709 my $res = $dbh->selectall_arrayref("SELECT * FROM my_schema.my_table");
2711 or by manipulating the schema search path with "SET search_path", e.g.
2713 $dbh->do("SET search_path TO my_schema, public");
2715
2717 The DBI module
2718
2720 To report a bug, or view the current list of bugs, please visit
2721 http://rt.cpan.org/Public/Dist/Display.html?Name=DBD-Pg
2722
2724 Patches can be submitted to rt.cpan.org. Detailed information on how to
2725 help out with this module can be found in the README.dev file. The
2726 latest development version can be obtained via: git clone
2727 git://github.com/bucardo/dbdpg.git
2728
2730 DBI by Tim Bunce <http://www.tim.bunce.name>
2731 The original DBD-Pg was by Edmund Mergl (E.Mergl@bawue.de) and Jeffrey
2733 W. Baker (jwbaker@acm.org). Major developers include David Wheeler
2734 <david@justatheory.com>, Jason Stewart <jason@openinformatics.com>,
2735 Bruce Momjian <pgman@candle.pha.pa.us>, and Greg Sabino Mullane
2736 <greg@turnstep.com>, with help from many others: see the Changes file
2737 (<http://search.cpan.org/dist/DBD-Pg/Changes>) for a complete list.
2738 Parts of this package were originally copied from DBI and DBD-Oracle.
2740 Mailing List
2742 The current maintainers may be reached through the 'dbd-pg' mailing
2744 list: <dbd-pg@perl.org>. Subscribe by sending an email to
2745 dbd-pg-subscribe@perl.org. Visit the archives at
2746 http://grokbase.com/g/perl/dbd-pg
2747
2749 Copyright (C) 1994-2019, Greg Sabino Mullane
2750 This module (DBD::Pg) is free software; you can redistribute it and/or
2752 modify it under the same terms as Perl 5.10.0. For more details, see
2753 the full text of the licenses in the directory LICENSES.
2754perl v5.28.1 2019-04-25 Pg(3)