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