1Pg(3)                 User Contributed Perl Documentation                Pg(3)
2
3
4

NAME

6       DBD::Pg - PostgreSQL database driver for the DBI module
7

SYNOPSIS

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

VERSION

23       This documents version 3.15.1 of the DBD::Pg module
24

DESCRIPTION

26       DBD::Pg is a Perl module that works with the DBI module to provide
27       access to PostgreSQL databases.
28

MODULE DOCUMENTATION

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

THE DBI CLASS

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             $loc = $dbh->pg_lo_lseek64($lobj_fd, $offset, $whence);
399
400           Same as pg_lo_lseek, but can handle much larger offsets and
401           returned values. Requires Postgres 9.3 or greater.
402
403       pg_lo_tell
404             $loc = $dbh->pg_lo_tell($lobj_fd);
405
406           Returns the current read or write location on the large object
407           $lobj_fd and "undef" upon failure.  This function cannot be used if
408           AutoCommit is enabled.
409
410       pg_lo_tell64
411             $loc = $dbh->pg_lo_tell64($lobj_fd);
412
413           Same as pg_lo_tell, but can return much larger values. Requires
414           Postgres 9.3 or greater.
415
416       pg_lo_truncate
417             $loc = $dbh->pg_lo_truncate($lobj_fd, $len);
418
419           Truncates the given large object to the new size. Returns "undef"
420           on failure, and 0 on success.  This function cannot be used if
421           AutoCommit is enabled.
422
423       pg_lo_truncate64
424             $loc = $dbh->pg_lo_truncate64($lobj_fd, $len);
425
426           Same as pg_lo_truncate, but can handle much larger lengths.
427           Requires Postgres 9.3 or greater.
428
429       pg_lo_close
430             $lobj_fd = $dbh->pg_lo_close($lobj_fd);
431
432           Closes an existing large object. Returns true upon success and
433           false upon failure.  This function cannot be used if AutoCommit is
434           enabled.
435
436       pg_lo_unlink
437             $ret = $dbh->pg_lo_unlink($lobjId);
438
439           Deletes an existing large object. Returns true upon success and
440           false upon failure.  This function cannot be used if AutoCommit is
441           enabled.
442
443       pg_lo_import
444             $lobjId = $dbh->pg_lo_import($filename);
445
446           Imports a Unix file as a large object and returns the object id of
447           the new object or "undef" upon failure.
448
449       pg_lo_import_with_oid
450             $lobjId = $dbh->pg_lo_import($filename, $OID);
451
452           Same as pg_lo_import, but attempts to use the supplied OID as the
453           large object number. If this number is 0, it falls back to the
454           behavior of pg_lo_import (which assigns the next available OID).
455
456           This is only available when DBD::Pg is compiled against a Postgres
457           server version 8.4 or later.
458
459       pg_lo_export
460             $ret = $dbh->pg_lo_export($lobjId, $filename);
461
462           Exports a large object into a Unix file. Returns false upon
463           failure, true otherwise.
464
465       getfd
466             $fd = $dbh->func('getfd');
467
468           Deprecated, use $dbh->{pg_socket} instead.
469
470       private_attribute_info
471
472         $hashref = $dbh->private_attribute_info();
473         $hashref = $sth->private_attribute_info();
474
475       Returns a hash of all private attributes used by DBD::Pg, for either a
476       database or a statement handle. Currently, all the hash values are
477       undef.
478

ATTRIBUTES COMMON TO ALL HANDLES

480       InactiveDestroy (boolean)
481
482       If set to true, then the "disconnect" method will not be automatically
483       called when the database handle goes out of scope. This is required if
484       you are forking, and even then you must tread carefully and ensure that
485       either the parent or the child (but not both!) handles all database
486       calls from that point forwards, so that messages from the Postgres
487       backend are only handled by one of the processes. If you don't set
488       things up properly, you will see messages such as "server closed the
489       connection unexpectedly", and "message type 0x32 arrived from server
490       while idle". The best solution is to either have the child process
491       reconnect to the database with a fresh database handle, or to rewrite
492       your application not to use forking. See the section on "Asynchronous
493       Queries" for a way to have your script continue to work while the
494       database is processing a request.
495
496       AutoInactiveDestroy (boolean)
497
498       The InactiveDestroy attribute, described above, needs to be explicitly
499       set in the child process after a fork. If the code that performs the
500       fork is in a third party module such as Sys::Syslog, this can present a
501       problem. Use AutoInactiveDestroy to get around this problem.
502
503       RaiseError (boolean, inherited)
504
505       Forces errors to always raise an exception. Although it defaults to
506       off, it is recommended that this be turned on, as the alternative is to
507       check the return value of every method (prepare, execute, fetch, etc.)
508       manually, which is easy to forget to do.
509
510       PrintError (boolean, inherited)
511
512       Forces database errors to also generate warnings, which can then be
513       filtered with methods such as locally redefining $SIG{__WARN__} or
514       using modules such as "CGI::Carp". This attribute is on by default.
515
516       ShowErrorStatement (boolean, inherited)
517
518       Appends information about the current statement to error messages. If
519       placeholder information is available, adds that as well. Defaults to
520       false.
521
522       Note that this will not work when using "do" without any arguments.
523
524       Warn (boolean, inherited)
525
526       Enables warnings. This is on by default, and should only be turned off
527       in a local block for a short a time only when absolutely needed.
528
529       Executed (boolean, read-only)
530
531       Indicates if a handle has been executed. For database handles, this
532       value is true after the "do" method has been called, or when one of the
533       child statement handles has issued an "execute". Issuing a "commit" or
534       "rollback" always resets the attribute to false for database handles.
535       For statement handles, any call to "execute" or its variants will flip
536       the value to true for the lifetime of the statement handle.
537
538       TraceLevel (integer, inherited)
539
540       Sets the trace level, similar to the "trace" method. See the sections
541       on "trace" and parse_trace_flag for more details.
542
543       Active (boolean, read-only)
544
545       Indicates if a handle is active or not. For database handles, this
546       indicates if the database has been disconnected or not. For statement
547       handles, it indicates if all the data has been fetched yet or not. Use
548       of this attribute is not encouraged.
549
550       Kids (integer, read-only)
551
552       Returns the number of child processes created for each handle type. For
553       a driver handle, indicates the number of database handles created. For
554       a database handle, indicates the number of statement handles created.
555       For statement handles, it always returns zero, because statement
556       handles do not create kids.
557
558       ActiveKids (integer, read-only)
559
560       Same as "Kids", but only returns those that are active.
561
562       CachedKids (hash ref)
563
564       Returns a hashref of handles. If called on a database handle, returns
565       all statement handles created by use of the "prepare_cached" method. If
566       called on a driver handle, returns all database handles created by the
567       "connect_cached" method.
568
569       ChildHandles (array ref)
570
571       Implemented by DBI, no driver-specific impact.
572
573       PrintWarn (boolean, inherited)
574
575       Implemented by DBI, no driver-specific impact.
576
577       HandleError (boolean, inherited)
578
579       Implemented by DBI, no driver-specific impact.
580
581       HandleSetErr (code ref, inherited)
582
583       Implemented by DBI, no driver-specific impact.
584
585       ErrCount (unsigned integer)
586
587       Implemented by DBI, no driver-specific impact.
588
589       FetchHashKeyName (string, inherited)
590
591       Implemented by DBI, no driver-specific impact.
592
593       ChopBlanks (boolean, inherited)
594
595       Supported by DBD::Pg as proposed by DBI. This method is similar to the
596       SQL function "RTRIM".
597
598       Taint (boolean, inherited)
599
600       Implemented by DBI, no driver-specific impact.
601
602       TaintIn (boolean, inherited)
603
604       Implemented by DBI, no driver-specific impact.
605
606       TaintOut (boolean, inherited)
607
608       Implemented by DBI, no driver-specific impact.
609
610       Profile (inherited)
611
612       Implemented by DBI, no driver-specific impact.
613
614       Type (scalar)
615
616       Returns "dr" for a driver handle, "db" for a database handle, and "st"
617       for a statement handle.  Should be rarely needed.
618
619       LongReadLen
620
621       Not used by DBD::Pg
622
623       LongTruncOk
624
625       Not used by DBD::Pg
626
627       CompatMode
628
629       Not used by DBD::Pg
630

DBI DATABASE HANDLE OBJECTS

632   Database Handle Methods
633       selectall_arrayref
634
635         $ary_ref = $dbh->selectall_arrayref($sql);
636         $ary_ref = $dbh->selectall_arrayref($sql, \%attr);
637         $ary_ref = $dbh->selectall_arrayref($sql, \%attr, @bind_values);
638
639       Returns a reference to an array containing the rows returned by
640       preparing and executing the SQL string.  See the DBI documentation for
641       full details.
642
643       selectall_hashref
644
645         $hash_ref = $dbh->selectall_hashref($sql, $key_field);
646
647       Returns a reference to a hash containing the rows returned by preparing
648       and executing the SQL string.  See the DBI documentation for full
649       details.
650
651       selectcol_arrayref
652
653         $ary_ref = $dbh->selectcol_arrayref($sql, \%attr, @bind_values);
654
655       Returns a reference to an array containing the first column from each
656       rows returned by preparing and executing the SQL string. It is possible
657       to specify exactly which columns to return. See the DBI documentation
658       for full details.
659
660       prepare
661
662         $sth = $dbh->prepare($statement, \%attr);
663
664       WARNING: DBD::Pg now (as of version 1.40) uses true prepared statements
665       by sending them to the backend to be prepared by the Postgres server.
666       Statements that were legal before may no longer work. See below for
667       details.
668
669       The prepare method prepares a statement for later execution. PostgreSQL
670       supports prepared statements, which enables DBD::Pg to only send the
671       query once, and simply send the arguments for every subsequent call to
672       "execute".  DBD::Pg can use these server-side prepared statements, or
673       it can just send the entire query to the server each time. The best way
674       is automatically chosen for each query. This will be sufficient for
675       most users: keep reading for a more detailed explanation and some
676       optional flags.
677
678       Queries that do not begin with the word "SELECT", "INSERT", "UPDATE",
679       or "DELETE" are never sent as server-side prepared statements.
680
681       Deciding whether or not to use prepared statements depends on many
682       factors, but you can force them to be used or not used by using the
683       pg_server_prepare attribute when calling "prepare".  Setting this to
684       false means to never use prepared statements. Setting pg_server_prepare
685       to true means that prepared statements should be used whenever
686       possible. This is the default.
687
688       The pg_server_prepare attribute can also be set at connection time like
689       so:
690
691         $dbh = DBI->connect($DBNAME, $DBUSER, $DBPASS,
692                             { AutoCommit => 0,
693                               RaiseError => 1,
694                               pg_server_prepare => 0,
695                             });
696
697       or you may set it after your database handle is created:
698
699         $dbh->{pg_server_prepare} = 1;
700
701       To enable it for just one particular statement:
702
703         $sth = $dbh->prepare("SELECT id FROM mytable WHERE val = ?",
704                              { pg_server_prepare => 1 });
705
706       You can even toggle between the two as you go:
707
708         $sth->{pg_server_prepare} = 1;
709         $sth->execute(22);
710         $sth->{pg_server_prepare} = 0;
711         $sth->execute(44);
712         $sth->{pg_server_prepare} = 1;
713         $sth->execute(66);
714
715       In the above example, the first execute will use the previously
716       prepared statement.  The second execute will not, but will build the
717       query into a single string and send it to the server. The third one
718       will act like the first and only send the arguments.  Even if you
719       toggle back and forth, a statement is only prepared once.
720
721       Using prepared statements is in theory quite a bit faster: not only
722       does the PostgreSQL backend only have to prepare the query only once,
723       but DBD::Pg no longer has to worry about quoting each value before
724       sending it to the server.
725
726       However, there are some drawbacks. The server cannot always choose the
727       ideal parse plan because it will not know the arguments before hand.
728       But for most situations in which you will be executing similar data
729       many times, the default plan will probably work out well. Programs such
730       as PgBouncer which cache connections at a low level should not use
731       prepared statements via DBD::Pg, or must take extra care in the
732       application to account for the fact that prepared statements are not
733       shared across database connections. Further discussion on this subject
734       is beyond the scope of this documentation: please consult the pgsql-
735       performance mailing list,
736       <http://archives.postgresql.org/pgsql-performance/>
737
738       Only certain commands will be sent to a server-side prepare: currently
739       these include "SELECT", "INSERT", "UPDATE", and "DELETE". DBD::Pg uses
740       a simple naming scheme for the prepared statements themselves:
741       dbdpg_XY_Z, where Y is the current PID, X is either 'p' or 'n'
742       (depending on if the PID is a positive or negative number), and Z is a
743       number that starts at 1 and increases each time a new statement is
744       prepared. This number is tracked at the database handle level, so
745       multiple statement handles will not collide.
746
747       You cannot send more than one command at a time in the same prepare
748       command (by separating them with semi-colons) when using server-side
749       prepares.
750
751       The actual "PREPARE" is usually not performed until the first execute
752       is called, due to the fact that information on the data types (provided
753       by "bind_param") may be provided after the prepare but before the
754       execute.
755
756       A server-side prepare may happen before the first "execute", but only
757       if the server can handle the server-side prepare, and the statement
758       contains no placeholders. It will also be prepared if the
759       pg_prepare_now attribute is passed in and set to a true value.
760       Similarly, the pg_prepare_now attribute can be set to 0 to ensure that
761       the statement is not prepared immediately, although the cases in which
762       you would want this are very rare. Finally, you can set the default
763       behavior of all prepare statements by setting the pg_prepare_now
764       attribute on the database handle:
765
766         $dbh->{pg_prepare_now} = 1;
767
768       The following two examples will be prepared right away:
769
770         $sth->prepare("SELECT 123"); ## no placeholders
771
772         $sth->prepare("SELECT 123, ?", {pg_prepare_now => 1});
773
774       The following two examples will NOT be prepared right away:
775
776         $sth->prepare("SELECT 123, ?"); ## has a placeholder
777
778         $sth->prepare("SELECT 123", {pg_prepare_now => 0});
779
780       There are times when you may want to prepare a statement yourself. To
781       do this, simply send the "PREPARE" statement directly to the server
782       (e.g. with the "do" method). Create a statement handle and set the
783       prepared name via the pg_prepare_name attribute. The statement handle
784       can be created with a dummy statement, as it will not be executed.
785       However, it should have the same number of placeholders as your
786       prepared statement. Example:
787
788         $dbh->do('PREPARE mystat AS SELECT COUNT(*) FROM pg_class WHERE reltuples < ?');
789         $sth = $dbh->prepare('SELECT ?');
790         $sth->bind_param(1, 1, SQL_INTEGER);
791         $sth->{pg_prepare_name} = 'mystat';
792         $sth->execute(123);
793
794       The above will run the equivalent of this query on the backend:
795
796         EXECUTE mystat(123);
797
798       which is the equivalent of:
799
800         SELECT COUNT(*) FROM pg_class WHERE reltuples < 123;
801
802       You can force DBD::Pg to send your query directly to the server by
803       adding the pg_direct attribute to your prepare call. This is not
804       recommended, but is added just in case you need it.
805
806       Placeholders
807
808       There are three types of placeholders that can be used in DBD::Pg. The
809       first is the "question mark" type, in which each placeholder is
810       represented by a single question mark character. This is the method
811       recommended by the DBI specs and is the most portable. Each question
812       mark is internally replaced by a "dollar sign number" in the order in
813       which they appear in the query (important when using "bind_param").
814
815       The second type of placeholder is "dollar sign numbers". This is the
816       method that Postgres uses internally and is overall probably the best
817       method to use if you do not need compatibility with other database
818       systems. DBD::Pg, like PostgreSQL, allows the same number to be used
819       more than once in the query.  Numbers must start with "1" and increment
820       by one value (but can appear in any order within the query). If the
821       same number appears more than once in a query, it is treated as a
822       single parameter and all instances are replaced at once. Examples:
823
824       Not legal:
825
826         $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $2'; # Does not start with 1
827
828         $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $3'; # Missing 2
829
830       Legal:
831
832         $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $1';
833
834         $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $2';
835
836         $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $2 AND $1'; # legal but confusing
837
838         $SQL = 'SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $2 AND reltuples > $1';
839
840         $SQL = 'SELECT count(*) FROM pg_class WHERE relpages > $1 AND reltuples > $1';
841
842       In the final statement above, DBI thinks there is only one placeholder,
843       so this statement will replace both placeholders:
844
845         $sth->bind_param(1, 2045);
846
847       While a simple execute with no bind_param calls requires only a single
848       argument as well:
849
850         $sth->execute(2045);
851
852       The final placeholder type is "named parameters" in the format ":foo".
853       While this syntax is supported by DBD::Pg, its use is discouraged in
854       favor of dollar-sign numbers.
855
856       The different types of placeholders cannot be mixed within a statement,
857       but you may use different ones for each statement handle you have. This
858       is confusing at best, so stick to one style within your program.
859
860       If your queries use operators that contain question marks (e.g. some of
861       the native Postgres geometric operators and JSON operators) or array
862       slices (e.g. "data[100:300]"), there are methods to instruct DBD::Pg to
863       not treat some symbols as placeholders. First, you may simply add a
864       backslash before the start of a placeholder, and DBD::Pg will strip the
865       backslash and not treat the character as a placeholder.
866
867       You can also tell DBD::Pg to ignore any non-dollar sign placeholders by
868       setting the pg_placeholder_dollaronly attribute at either the database
869       handle or the statement handle level. Examples:
870
871         $dbh->{pg_placeholder_dollaronly} = 1;
872         $sth = $dbh->prepare(q{SELECT * FROM mytable WHERE lseg1 ?# lseg2 AND name = $1});
873         $sth->execute('segname');
874
875       Alternatively, you can set it at prepare time:
876
877         $sth = $dbh->prepare(q{SELECT * FROM mytable WHERE lseg1 ?-| lseg2 AND name = $1},
878           {pg_placeholder_dollaronly => 1});
879         $sth->execute('segname');
880
881       If your queries use array slices but you still want to use question
882       marks as placeholders, you can tell DBD::Pg to ignore just colon
883       placeholders by setting the "pg_placeholder_nocolons" attribute in the
884       same way. Examples:
885
886         $dbh->{pg_placeholder_nocolons} = 1;
887         $sth = $dbh->prepare(q{SELECT array[1:2] FROM mytable WHERE id = ?});
888         $sth->execute(1);
889
890       Again, you may set it at prepare time as well:
891
892         $sth = $dbh->prepare(q{SELECT array[1:2] FROM mytable WHERE id = ?},
893           {pg_placeholder_nocolons => 1});
894         $sth->execute(1);
895
896       It should be noted that placeholders only work when used outside of a
897       literal string context; i.e., the following examples will not
898       define/use any placeholders due to appearing inside strings within the
899       SQL:
900
901         $sth = $dbh->prepare(q{SELECT id FROM mytable WHERE text LIKE '%?'});
902         $dbh->do(q{DO LANGUAGE plpgsql $$ BEGIN RAISE NOTICE ?; END $$}, undef, $message);
903
904       See the DBI placeholder documentation for more details.
905
906       prepare_cached
907
908         $sth = $dbh->prepare_cached($statement, \%attr);
909
910       Implemented by DBI, no driver-specific impact. This method is most
911       useful when using a server that supports server-side prepares, and you
912       have asked the prepare to happen immediately via the pg_prepare_now
913       attribute.
914
915       do
916
917         $rv = $dbh->do($statement);
918         $rv = $dbh->do($statement, \%attr);
919         $rv = $dbh->do($statement, \%attr, @bind_values);
920
921       Prepare and execute a single statement. Returns the number of rows
922       affected if the query was successful, returns undef if an error
923       occurred, and returns -1 if the number of rows is unknown or not
924       available. Note that this method will return 0E0 instead of 0 for 'no
925       rows were affected', in order to always return a true value if no error
926       occurred.
927
928       If neither "\%attr" nor @bind_values is given, the query will be sent
929       directly to the server without the overhead of internally creating a
930       statement handle and running prepare and execute, for a measurable
931       speed increase.
932
933       Note that an empty statement (a string with no length) will not be
934       passed to the server; if you want a simple test, use "SELECT 123" or
935       the "ping" method.
936
937       last_insert_id
938
939         $rv = $dbh->last_insert_id(undef, $schema, $table, undef);
940         $rv = $dbh->last_insert_id(undef, $schema, $table, undef, {sequence => $seqname});
941
942       Attempts to return the id of the last value to be inserted into a
943       table.  You can either provide a sequence name (preferred) or provide a
944       table name with optional schema, and DBD::Pg will attempt to find the
945       sequence itself.  The current value of the sequence is returned by a
946       call to the "CURRVAL()" PostgreSQL function. This will fail if the
947       sequence has not yet been used in the current database connection.
948
949       If you do not know the name of the sequence, you can provide a table
950       name and DBD::Pg will attempt to return the correct value. To do this,
951       there must be at least one column in the table with a "NOT NULL"
952       constraint, that has a unique constraint, and which uses a sequence as
953       a default value (either manually, or via the "SERIAL" pseudotype or
954       "GENERATED ... AS IDENTITY"). If more than one column meets these
955       conditions, the primary key will be used. This involves some looking up
956       of things in the system table, so DBD::Pg will cache the sequence name
957       for subsequent calls. If you need to disable this caching for some
958       reason, (such as the sequence name changing), you can control it by
959       adding "pg_cache => 0" to the final (hashref) argument for
960       last_insert_id.
961
962       Please keep in mind that this method is far from foolproof, so make
963       your script use it properly. Specifically, make sure that it is called
964       immediately after the insert, and that the insert does not add a value
965       to the column that is using the sequence as a default value. However,
966       because we are using sequences, you can be sure that the value you got
967       back has not been used by any other process.
968
969       Some examples:
970
971         $dbh->do('CREATE SEQUENCE lii_seq START 1');
972         $dbh->do(q{CREATE TABLE lii (
973           foobar INTEGER NOT NULL UNIQUE DEFAULT nextval('lii_seq'),
974           baz VARCHAR)});
975         $SQL = 'INSERT INTO lii(baz) VALUES (?)';
976         $sth = $dbh->prepare($SQL);
977         for (qw(uno dos tres cuatro)) {
978           $sth->execute($_);
979           my $newid = $dbh->last_insert_id(undef,undef,undef,undef,{sequence=>'lii_seq'});
980           print "Last insert id was $newid\n";
981         }
982
983       If you did not want to worry about the sequence name:
984
985         $dbh->do('CREATE TABLE lii2 (
986           foobar SERIAL UNIQUE,
987           baz VARCHAR)');
988         $SQL = 'INSERT INTO lii2(baz) VALUES (?)';
989         $sth = $dbh->prepare($SQL);
990         for (qw(uno dos tres cuatro)) {
991           $sth->execute($_);
992           my $newid = $dbh->last_insert_id(undef,undef,"lii2",undef);
993           print "Last insert id was $newid\n";
994         }
995
996       commit
997
998         $rv = $dbh->commit;
999
1000       Issues a COMMIT to the server, indicating that the current transaction
1001       is finished and that all changes made will be visible to other
1002       processes. If AutoCommit is enabled, then a warning is given and no
1003       COMMIT is issued. Returns true on success, false on error.  See also
1004       the section on "Transactions".
1005
1006       rollback
1007
1008         $rv = $dbh->rollback;
1009
1010       Issues a ROLLBACK to the server, which discards any changes made in the
1011       current transaction. If AutoCommit is enabled, then a warning is given
1012       and no ROLLBACK is issued. Returns true on success, and false on error.
1013       See also the the section on "Transactions".
1014
1015       begin_work
1016
1017       This method turns on transactions until the next call to "commit" or
1018       "rollback", if AutoCommit is currently enabled. If it is not enabled,
1019       calling begin_work will issue an error. Note that the transaction will
1020       not actually begin until the first statement after begin_work is
1021       called.  Example:
1022
1023         $dbh->{AutoCommit} = 1;
1024         $dbh->do('INSERT INTO foo VALUES (123)'); ## Changes committed immediately
1025         $dbh->begin_work();
1026         ## Not in a transaction yet, but AutoCommit is set to 0
1027
1028         $dbh->do("INSERT INTO foo VALUES (345)");
1029         ## DBD::PG actually issues two statements here:
1030         ## BEGIN;
1031         ## INSERT INTO foo VALUES (345)
1032         ## We are now in a transaction
1033
1034         $dbh->commit();
1035         ## AutoCommit is now set to 1 again
1036
1037       disconnect
1038
1039         $rv = $dbh->disconnect;
1040
1041       Disconnects from the Postgres database. Any uncommitted changes will be
1042       rolled back upon disconnection. It's good policy to always explicitly
1043       call commit or rollback at some point before disconnecting, rather than
1044       relying on the default rollback behavior.
1045
1046       This method may give warnings about "disconnect invalidates X active
1047       statement handle(s)". This means that you called "$sth->execute()" but
1048       did not finish fetching all the rows from them. To avoid seeing this
1049       warning, either fetch all the rows or call "$sth->finish()" for each
1050       executed statement handle.
1051
1052       If the script exits before disconnect is called (or, more precisely, if
1053       the database handle is no longer referenced by anything), then the
1054       database handle's DESTROY method will call the rollback() and
1055       disconnect() methods automatically. It is best to explicitly disconnect
1056       rather than rely on this behavior.
1057
1058       quote
1059
1060         $rv = $dbh->quote($value, $data_type);
1061
1062       This module implements its own "quote" method. For simple string types,
1063       both backslashes and single quotes are doubled. You may also quote
1064       arrayrefs and receive a string suitable for passing into Postgres array
1065       columns.
1066
1067       If the value contains backslashes, and the server is version 8.1 or
1068       higher, then the escaped string syntax will be used (which places a
1069       capital E before the first single quote). This syntax is always used
1070       when quoting bytea values on servers 8.1 and higher.
1071
1072       The "data_type" argument is optional and should be one of the type
1073       constants exported by DBD::Pg (such as PG_BYTEA). In addition to
1074       string, bytea, char, bool, and other standard types, the following
1075       geometric types are supported: point, line, lseg, box, path, polygon,
1076       and circle (PG_POINT, PG_LINE, PG_LSEG, PG_BOX, PG_PATH, PG_POLYGON,
1077       and PG_CIRCLE respectively). To quote a Postgres-specific data type,
1078       you must use a 'hashref' argument like so:
1079
1080         my $quotedval = $dbh->quote($value, { pg_type => PG_VARCHAR });
1081
1082       NOTE: The undocumented (and invalid) support for the "SQL_BINARY" data
1083       type is officially deprecated. Use "PG_BYTEA" with "bind_param()"
1084       instead:
1085
1086         $rv = $sth->bind_param($param_num, $bind_value,
1087                                { pg_type => PG_BYTEA });
1088
1089       quote_identifier
1090
1091         $string = $dbh->quote_identifier( $name );
1092         $string = $dbh->quote_identifier( undef, $schema, $table);
1093
1094       Returns a quoted version of the supplied string, which is commonly a
1095       schema, table, or column name. The three argument form will return the
1096       schema and the table together, separated by a dot. Examples:
1097
1098         print $dbh->quote_identifier('grapefruit'); ## Prints: "grapefruit"
1099
1100         print $dbh->quote_identifier('juicy fruit'); ## Prints: "juicy fruit"
1101
1102         print $dbh->quote_identifier(undef, 'public', 'pg_proc');
1103         ## Prints: "public"."pg_proc"
1104
1105       pg_notifies
1106
1107         $ret = $dbh->pg_notifies;
1108
1109       Looks for any asynchronous notifications received and returns either
1110       "undef" or a reference to a three-element array consisting of an event
1111       name, the PID of the backend that sent the NOTIFY command, and the
1112       optional payload string.  Note that this does not check if the
1113       connection to the database is still valid first - for that, use the
1114       "ping" method. You may need to commit if not in autocommit mode - new
1115       notices will not be picked up while in the middle of a transaction. An
1116       example:
1117
1118         $dbh->do("LISTEN abc");
1119         $dbh->do("LISTEN def");
1120
1121         ## Hang around until we get the message we want
1122         LISTENLOOP: {
1123           while (my $notify = $dbh->pg_notifies) {
1124             my ($name, $pid, $payload) = @$notify;
1125             print qq{I received notice "$name" from PID $pid, payload was "$payload"\n};
1126             ## Do something based on the notice received
1127           }
1128           $dbh->ping() or die qq{Ping failed!};
1129           $dbh->commit();
1130           sleep(5);
1131           redo;
1132         }
1133
1134       Payloads will always be an empty string unless you are connecting to a
1135       Postgres server version 9.0 or higher.
1136
1137       ping
1138
1139         $rv = $dbh->ping;
1140
1141       The "ping" method determines if there is a working connection to an
1142       active database server. It does this by sending a small query to the
1143       server, currently 'DBD::Pg ping test v3.15.1'. It returns 0 (false) if
1144       the connection is not valid, otherwise it returns a positive number
1145       (true). The value returned indicates the current state:
1146
1147         Value    Meaning
1148         --------------------------------------------------
1149           1      Database is idle (not in a transaction)
1150           2      Database is active, there is a command in progress (usually seen after a COPY command)
1151           3      Database is idle within a transaction
1152           4      Database is idle, within a failed transaction
1153
1154       Additional information on why a handle is not valid can be obtained by
1155       using the "pg_ping" method.
1156
1157       pg_ping
1158
1159         $rv = $dbh->pg_ping;
1160
1161       This is a DBD::Pg-specific extension to the "ping" method. This will
1162       check the validity of a database handle in exactly the same way as
1163       "ping", but instead of returning a 0 for an invalid connection, it will
1164       return a negative number. So in addition to returning the positive
1165       numbers documented for "ping", it may also return the following:
1166
1167         Value    Meaning
1168         --------------------------------------------------
1169          -1      There is no connection to the database at all (e.g. after disconnect)
1170          -2      An unknown transaction status was returned (e.g. after forking)
1171          -3      The test query failed (PQexec returned null)
1172          -4      PQstatus returned a CONNECTION_BAD
1173
1174       pg_error_field
1175
1176         $value = $dbh->pg_error_field('context');
1177
1178       The pg_error_field returns specific information about the last error
1179       that occurred.  It needs to be called as soon as possible after an
1180       error occurs, as any other query sent to Postgres (via $dbh or $sth)
1181       will reset all the error information. Note that this is called at the
1182       database handle ($dbh) level, but can return errors that occurred via
1183       both database handles (e.g. $dbh->do) and statement handles (e.g.
1184       $sth->execute).  It takes a single argument, indicating which field to
1185       return. The value returned will be undef if the previous command was
1186       not an error, or if the field is not applicable to the current error.
1187
1188       The canonical list of field types can be found at:
1189
1190       <https://www.postgresql.org/docs/current/libpq-exec.html#LIBPQ-PQRESULTERRORFIELD>
1191
1192       The literal names on that page can be used (e.g.
1193       PG_DIAG_STATEMENT_HINT), but lowercase is accepted too, as well as the
1194       following abbreviated forms:
1195
1196       severity
1197       severity_nonlocal (only for Postgres 10 and above)
1198       state
1199       primary
1200       detail (does not work well for Postgres < 9.2)
1201       hint
1202       statement_position
1203       internal_position
1204       internal_query
1205       context
1206       schema (only for Postgres 9.3 and above)
1207       table (only for Postgres 9.3 and above)
1208       column (only for Postgres 9.3 and above)
1209       type (only for Postgres 9.3 and above)
1210       constraint (only for Postgres 9.3 and above)
1211       source_file
1212       source_line
1213       source_function
1214
1215       get_info
1216
1217         $value = $dbh->get_info($info_type);
1218
1219       Supports a very large set (> 250) of the information types, including
1220       the minimum recommended by DBI.
1221
1222       Items of note:
1223
1224       SQL_KEYWORDS
1225           This returns all items reserved by Postgres but NOT reserved by
1226           SQL:2011 standard. See:
1227
1228           http://www.postgresql.org/docs/current/static/sql-keywords-appendix.htm
1229
1230       table_info
1231
1232         $sth = $dbh->table_info(undef, $schema, $table, $type);
1233
1234       Returns all tables and views visible to the current user.  The schema
1235       and table arguments will do a "LIKE" search if a percent sign ("%") or
1236       an underscore ("_") is detected in the argument. The $type argument
1237       accepts any comma-separated combination of "TABLE", "VIEW", "SYSTEM
1238       TABLE", "SYSTEM VIEW", "MATERIALIZED VIEW", "SYSTEM MATERIALIZED VIEW",
1239       "FOREIGN TABLE", "SYSTEM FOREIGN TABLE", or "LOCAL TEMPORARY".  (Using
1240       all is the default action.)
1241
1242       Note that a statement handle is returned, and not a direct list of
1243       tables. See the examples below for ways to handle this.
1244
1245       The following fields are returned:
1246
1247       TABLE_CAT: The name of the database that the table or view is in
1248       (always the current database).
1249
1250       TABLE_SCHEM: The name of the schema that the table or view is in.
1251
1252       TABLE_NAME: The name of the table or view.
1253
1254       TABLE_TYPE: The type of object returned. Will be one of "TABLE",
1255       "VIEW", "MATERIALIZED VIEW", "SYSTEM VIEW", "SYSTEM MATERIALIZED VIEW",
1256       "SYSTEM TABLE", "FOREIGN TABLE", "SYSTEM FOREIGN TABLE", or "LOCAL
1257       TEMPORARY".
1258
1259       The TABLE_SCHEM and TABLE_NAME will be quoted via "quote_ident()".
1260
1261       Four additional fields specific to DBD::Pg are returned:
1262
1263       pg_schema: the unquoted name of the schema
1264
1265       pg_table: the unquoted name of the table
1266
1267       pg_tablespace_name: the name of the tablespace the table is in
1268
1269       pg_tablespace_location: the location of the tablespace the table is in
1270
1271       Tables that have not been assigned to a particular tablespace (or
1272       views) will return NULL ("undef") for both of the above field.
1273
1274       Rows are returned alphabetically, with all tables first, and then all
1275       views.
1276
1277       Examples of use:
1278
1279         ## Display all tables and views in the public schema:
1280         $sth = $dbh->table_info('', 'public', undef, undef);
1281         for my $rel (@{$sth->fetchall_arrayref({})}) {
1282           print "$rel->{TABLE_TYPE} name is $rel->{TABLE_NAME}\n";
1283         }
1284
1285
1286         # Display the schema of all tables named 'foo':
1287         $sth = $dbh->table_info('', undef, 'foo', 'TABLE');
1288         for my $rel (@{$sth->fetchall_arrayref({})}) {
1289           print "Table name is $rel->{TABLE_SCHEM}.$rel->{TABLE_NAME}\n";
1290         }
1291
1292       column_info
1293
1294         $sth = $dbh->column_info( undef, $schema, $table, $column );
1295
1296       Supported by this driver as proposed by DBI with the follow exceptions.
1297       These fields are currently always returned with NULL ("undef") values:
1298
1299          BUFFER_LENGTH
1300          DECIMAL_DIGITS
1301          NUM_PREC_RADIX
1302          SQL_DATA_TYPE
1303          SQL_DATETIME_SUB
1304          CHAR_OCTET_LENGTH
1305
1306       Also, six additional non-standard fields are returned:
1307
1308       pg_type: data type with additional info i.e. "character varying(20)"
1309
1310       pg_constraint: holds column constraint definition
1311
1312       pg_schema: the unquoted name of the schema
1313
1314       pg_table: the unquoted name of the table
1315
1316       pg_column: the unquoted name of the column
1317
1318       pg_enum_values: an array reference of allowed values for an enum column
1319
1320       Note that the TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME fields all
1321       return output wrapped in quote_ident(). If you need the unquoted
1322       version, use the pg_ fields above.
1323
1324       primary_key_info
1325
1326         $sth = $dbh->primary_key_info( undef, $schema, $table, \%attr );
1327
1328       Supported by this driver as proposed by DBI. There are no search
1329       patterns allowed, but leaving the $schema argument blank will cause the
1330       first table found in the schema search path to be used. An additional
1331       field, "DATA_TYPE", is returned and shows the data type for each of the
1332       arguments in the "COLUMN_NAME" field.
1333
1334       This method will also return tablespace information for servers that
1335       support tablespaces. See the "table_info" entry for more information.
1336
1337       The five additional custom fields returned are:
1338
1339       pg_tablespace_name: name of the tablespace, if any
1340
1341       pg_tablespace_location: location of the tablespace
1342
1343       pg_schema: the unquoted name of the schema
1344
1345       pg_table: the unquoted name of the table
1346
1347       pg_column: the unquoted name of the column
1348
1349       In addition to the standard format of returning one row for each column
1350       found for the primary key, you can pass the "pg_onerow" attribute to
1351       force a single row to be used. If the primary key has multiple columns,
1352       the "KEY_SEQ", "COLUMN_NAME", and "DATA_TYPE" fields will return a
1353       comma-delimited string. If the "pg_onerow" attribute is set to "2", the
1354       fields will be returned as an arrayref, which can be useful when
1355       multiple columns are involved:
1356
1357         $sth = $dbh->primary_key_info('', '', 'dbd_pg_test', {pg_onerow => 2});
1358         if (defined $sth) {
1359           my $pk = $sth->fetchall_arrayref()->[0];
1360           print "Table $pk->[2] has a primary key on these columns:\n";
1361           for (my $x=0; defined $pk->[3][$x]; $x++) {
1362             print "Column: $pk->[3][$x]  (data type: $pk->[6][$x])\n";
1363           }
1364         }
1365
1366       primary_key
1367
1368         @key_column_names = $dbh->primary_key(undef, $schema, $table);
1369
1370       Simple interface to the "primary_key_info" method. Returns a list of
1371       the column names that comprise the primary key of the specified table.
1372       The list is in primary key column sequence order. If there is no
1373       primary key then an empty list is returned.
1374
1375       foreign_key_info
1376
1377         $sth = $dbh->foreign_key_info( $pk_catalog, $pk_schema, $pk_table,
1378                                        $fk_catalog, $fk_schema, $fk_table );
1379
1380       Supported by this driver as proposed by DBI, using the SQL/CLI variant.
1381       There are no search patterns allowed, but leaving the $schema argument
1382       blank will cause the first table found in the schema search path to be
1383       used. Two additional fields, "UK_DATA_TYPE" and "FK_DATA_TYPE", are
1384       returned to show the data type for the unique and foreign key columns.
1385       Foreign keys that have no named constraint (where the referenced column
1386       only has an unique index) will return "undef" for the "UK_NAME" field.
1387
1388       statistics_info
1389
1390         $sth = $dbh->statistics_info( undef, $schema, $table, $unique_only, $quick );
1391
1392       Returns a statement handle that can be fetched from to give statistics
1393       information on a specific table and its indexes. The $table argument is
1394       mandatory. The $schema argument is optional but recommended. The
1395       $unique_only argument, if true, causes only information about unique
1396       indexes to be returned. The $quick argument is not used by DBD::Pg. For
1397       information on the format of the standard rows returned, please see the
1398       DBI documentation.
1399
1400       DBI section on statistics_info
1401
1402       In addition, the following Postgres specific columns are returned:
1403
1404       pg_expression
1405           Postgres allows indexes on functions and scalar expressions based
1406           on one or more columns. This field will always be populated if an
1407           index, but the lack of an entry in the COLUMN_NAME should indicate
1408           that this is an index expression.
1409
1410       pg_is_key_column
1411           Postgres (since version 11) allows including non-key columns in
1412           indexes so they can be retrieved by index-only scans.  This field
1413           will be false for such columns, and true for normal index columns.
1414
1415       pg_null_ordering
1416           In addition to "ASC" and "DESC", Postgres supports specifying
1417           "NULLS FIRST" or "NULLS LAST" for index columns.  For columns of
1418           indexes that support ordering, this field will be "first" or
1419           "last", otherwise it will be "undef";
1420
1421       tables
1422
1423         @names = $dbh->tables( undef, $schema, $table, $type, \%attr );
1424
1425       Supported by this driver as proposed by DBI. This method returns all
1426       tables and/or views (including foreign tables and materialized views)
1427       which are visible to the current user: see "table_info" for more
1428       information about the arguments. The name of the schema appears before
1429       the table or view name. This can be turned off by adding in the
1430       "pg_noprefix" attribute:
1431
1432         my @tables = $dbh->tables( '', '', 'dbd_pg_test', '', {pg_noprefix => 1} );
1433
1434       type_info_all
1435
1436         $type_info_all = $dbh->type_info_all;
1437
1438       Supported by this driver as proposed by DBI. Information is only
1439       provided for SQL datatypes and for frequently used datatypes. The
1440       mapping between the PostgreSQL typename and the SQL92 datatype (if
1441       possible) has been done according to the following table:
1442
1443         +---------------+------------------------------------+
1444         | typname       | SQL92                              |
1445         |---------------+------------------------------------|
1446         | bool          | BOOL                               |
1447         | text          | /                                  |
1448         | bpchar        | CHAR(n)                            |
1449         | varchar       | VARCHAR(n)                         |
1450         | int2          | SMALLINT                           |
1451         | int4          | INT                                |
1452         | int8          | BIGINT                             |
1453         | money         | /                                  |
1454         | float4        | FLOAT(p)   p<7=float4, p<16=float8 |
1455         | float8        | REAL                               |
1456         | abstime       | /                                  |
1457         | reltime       | /                                  |
1458         | tinterval     | /                                  |
1459         | date          | /                                  |
1460         | time          | /                                  |
1461         | datetime      | /                                  |
1462         | timespan      | TINTERVAL                          |
1463         | timestamp     | TIMESTAMP                          |
1464         +---------------+------------------------------------+
1465
1466       type_info
1467
1468         @type_info = $dbh->type_info($data_type);
1469
1470       Returns a list of hash references holding information about one or more
1471       variants of $data_type.  See the DBI documentation for more details.
1472
1473       pg_server_trace
1474
1475         $dbh->pg_server_trace($filehandle);
1476
1477       Writes debugging information from the PostgreSQL backend to a file.
1478       This is not related to the DBI "trace" method and you should not use
1479       this method unless you know what you are doing. If you do enable this,
1480       be aware that the file will grow very large, very quick. To stop
1481       logging to the file, use the "pg_server_untrace" method. The first
1482       argument must be a file handle, not a filename. Example:
1483
1484         my $pid = $dbh->{pg_pid};
1485         my $file = "pgbackend.$pid.debug.log";
1486         open(my $fh, ">$file") or die qq{Could not open "$file": $!\n};
1487         $dbh->pg_server_trace($fh);
1488         ## Run code you want to trace here
1489         $dbh->pg_server_untrace;
1490         close($fh);
1491
1492       pg_server_untrace
1493
1494         $dbh->pg_server_untrace;
1495
1496       Stop server logging to a previously opened file.
1497
1498       selectrow_array
1499
1500         @row_ary = $dbh->selectrow_array($sql);
1501         @row_ary = $dbh->selectrow_array($sql, \%attr);
1502         @row_ary = $dbh->selectrow_array($sql, \%attr, @bind_values);
1503
1504       Returns an array of row information after preparing and executing the
1505       provided SQL string. The rows are returned by calling "fetchrow_array".
1506       The string can also be a statement handle generated by a previous
1507       prepare. Note that only the first row of data is returned. If called in
1508       a scalar context, only the first column of the first row is returned.
1509       Because this is not portable, it is not recommended that you use this
1510       method in that way.
1511
1512       selectrow_arrayref
1513
1514         $ary_ref = $dbh->selectrow_arrayref($statement);
1515         $ary_ref = $dbh->selectrow_arrayref($statement, \%attr);
1516         $ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @bind_values);
1517
1518       Exactly the same as "selectrow_array", except that it returns a
1519       reference to an array, by internal use of the "fetchrow_arrayref"
1520       method.
1521
1522       selectrow_hashref
1523
1524         $hash_ref = $dbh->selectrow_hashref($sql);
1525         $hash_ref = $dbh->selectrow_hashref($sql, \%attr);
1526         $hash_ref = $dbh->selectrow_hashref($sql, \%attr, @bind_values);
1527
1528       Exactly the same as "selectrow_array", except that it returns a
1529       reference to an hash, by internal use of the "fetchrow_hashref" method.
1530
1531       clone
1532
1533         $other_dbh = $dbh->clone();
1534
1535       Creates a copy of the database handle by connecting with the same
1536       parameters as the original handle, then trying to merge the attributes.
1537       See the DBI documentation for complete usage.
1538
1539   Database Handle Attributes
1540       AutoCommit (boolean)
1541
1542       Supported by DBD::Pg as proposed by DBI. According to the
1543       classification of DBI, PostgreSQL is a database in which a transaction
1544       must be explicitly started. Without starting a transaction, every
1545       change to the database becomes immediately permanent. The default of
1546       AutoCommit is on, but this may change in the future, so it is highly
1547       recommended that you explicitly set it when calling "connect". For
1548       details see the notes about "Transactions" elsewhere in this document.
1549
1550       ParamValues (hash ref, read-only)
1551
1552       Ignored unless inside a "do" method call. There it is temporarily
1553       aliased to the "ParamValues" hash from the temporary statement handle
1554       inside an internal "prepare / execute / fetch" routine, invisible from
1555       outside, and is treated correspondingly (see "ParamValues" in
1556       "Statement Handle Attributes"). This allows for correct reporting of
1557       values bound to placeholders to the caller, should the query fail (see
1558       "ShowErrorStatement").
1559
1560       pg_bool_tf (boolean)
1561
1562       DBD::Pg specific attribute. If true, boolean values will be returned as
1563       the characters 't' and 'f' instead of '1' and '0'.
1564
1565       ReadOnly (boolean)
1566
1567       $dbh->{ReadOnly} = 1;
1568
1569       Specifies if the current database connection should be in read-only
1570       mode or not.  In this mode, changes that change the database are not
1571       allowed and will throw an error. Note: this method will not work if
1572       "AutoCommit" is true. The read-only effect is accomplished by sending a
1573       SET TRANSACTION READ ONLY after every begin. For more details, please
1574       see:
1575
1576       http://www.postgresql.org/docs/current/interactive/sql-set-transaction.html
1577
1578       Please not that this method is not foolproof: there are still ways to
1579       update the database. Consider this a safety net to catch applications
1580       that should not be issuing commands such as INSERT, UPDATE, or DELETE.
1581
1582       This method requires DBI version 1.55 or better.
1583
1584       pg_server_prepare (boolean)
1585
1586       DBD::Pg specific attribute. Indicates if DBD::Pg should attempt to use
1587       server-side prepared statements. The default value, true, indicates
1588       that prepared statements should be used whenever possible. See the
1589       section on the "prepare" method for more information.
1590
1591       pg_switch_prepared (integer)
1592
1593       DBD::Pg specific attribute. Indicates when DBD::Pg will internally
1594       switch from using PQexecParams to PQexecPrepared. In other words, when
1595       it will start using server-side prepared statements (assuming all other
1596       requirements for them are met). The default value, 2, means that a
1597       prepared statement will be prepared and used the second and subsequent
1598       time execute is called. To always use PQexecPrepared instead of
1599       PQexecParams, set pg_switch_prepared to 1 (this was the default
1600       behavior in earlier versions).  Setting pg_switch_prepared to 0 will
1601       force DBD::Pg to always use PQexecParams.
1602
1603       pg_placeholder_dollaronly (boolean)
1604
1605       DBD::Pg specific attribute. Defaults to false. When true, question
1606       marks inside of statements are not treated as placeholders. Useful for
1607       statements that contain unquoted question marks, such as geometric
1608       operators. Note that you may also simply escape question marks with a
1609       backslash to prevent them from being treated as placeholders.
1610
1611       pg_placeholder_nocolons (boolean)
1612
1613       DBD::Pg specific attribute. Defaults to false. When true, colons inside
1614       of statements are not treated as placeholders. Useful for statements
1615       that contain an array slice. You may also place a backslash directly
1616       before the colon to prevent it from being treated as a placeholder.
1617
1618       pg_enable_utf8 (integer)
1619
1620       DBD::Pg specific attribute. The behavior of DBD::Pg with regards to
1621       this flag has changed as of version 3.0.0. The default value for this
1622       attribute, -1, tells DBD::Pg to UTF8-decode all strings coming back
1623       from the database if the client_encoding is set to "UTF8". Use of this
1624       default is highly encouraged. If your code was previously using
1625       pg_enable_utf8, you can probably remove mention of it entirely.
1626
1627       If this attribute is set to 0, then DBD::Pg will never UTF8-decode
1628       returned data, regardless of the current client_encoding.
1629
1630       If this attribute is set to 1, then DBD::Pg will always UTF8-decode
1631       returned data, regardless of the current client_encoding (with the
1632       exception of bytea data).
1633
1634       Note that the value of client_encoding is only checked on connection
1635       time. If you change the client_encoding to/from 'UTF8' after
1636       connecting, you can set pg_enable_utf8 to -1 to force DBD::Pg to read
1637       in the new client_encoding and act accordingly.
1638
1639       pg_errorlevel (integer)
1640
1641       DBD::Pg specific attribute. Sets the amount of information returned by
1642       the server's error messages. Valid entries are 0, 1, and 2. Any other
1643       number will be forced to the default value of 1.
1644
1645       A value of 0 ("TERSE") will show severity, primary text, and position
1646       only and will usually fit on a single line. A value of 1 ("DEFAULT")
1647       will also show any detail, hint, or context fields. A value of 2
1648       ("VERBOSE") will show all available information.
1649
1650       pg_lib_version (integer, read-only)
1651
1652       DBD::Pg specific attribute. Indicates which version of PostgreSQL that
1653       DBD::Pg was compiled against. In other words, which libraries were
1654       used.  Returns a number with major, minor, and revision together;
1655       version 8.1.4 would be returned as 80104.
1656
1657       pg_server_version (integer, read-only)
1658
1659       DBD::Pg specific attribute. Indicates which version of PostgreSQL that
1660       the current database handle is connected to. Returns a number with
1661       major, minor, and revision together; version 8.0.1 would be 80001.
1662
1663       Name (string, read-only)
1664
1665       Returns the name of the current database. This is the same as the DSN,
1666       without the "dbi:Pg:" part. Before version 2.0.0, this only returned
1667       the bare database name (e.g. 'foo'). From version 2.0.0 onwards, it
1668       returns the more correct output (e.g. 'dbname=foo')
1669
1670       Username (string, read-only)
1671
1672       Returns the name of the user connected to the database.
1673
1674       pg_db (string, read-only)
1675
1676       DBD::Pg specific attribute. Returns the name of the current database.
1677
1678       pg_user (string, read-only)
1679
1680       DBD::Pg specific attribute. Returns the name of the user that connected
1681       to the server.
1682
1683       pg_host (string, read-only)
1684
1685       DBD::Pg specific attribute. Returns the host of the current server
1686       connection. Locally connected hosts will return an empty string.
1687
1688       pg_port (integer, read-only)
1689
1690       DBD::Pg specific attribute. Returns the port of the connection to the
1691       server.
1692
1693       pg_socket (integer, read-only)
1694
1695       DBD::Pg specific attribute. Returns the file description number of the
1696       connection socket to the server.
1697
1698       pg_pass (string, read-only)
1699
1700       DBD::Pg specific attribute. Returns the password used to connect to the
1701       server.
1702
1703       pg_options (string, read-only)
1704
1705       DBD::Pg specific attribute. Returns the command-line options passed to
1706       the server. May be an empty string.
1707
1708       pg_default_port (integer, read-only)
1709
1710       DBD::Pg specific attribute. Returns the default port used if none is
1711       specifically given.
1712
1713       pg_pid (integer, read-only)
1714
1715       DBD::Pg specific attribute. Returns the process id (PID) of the backend
1716       server process handling the connection.
1717
1718       pg_prepare_now (boolean)
1719
1720       DBD::Pg specific attribute. Default is off. If true, then the "prepare"
1721       method will immediately prepare commands, rather than waiting until the
1722       first execute.
1723
1724       pg_expand_array (boolean)
1725
1726       DBD::Pg specific attribute. Defaults to true. If false, arrays returned
1727       from the server will not be changed into a Perl arrayref, but remain as
1728       a string.
1729
1730       pg_async_status (integer, read-only)
1731
1732       DBD::Pg specific attribute. Returns the current status of an
1733       asynchronous command. 0 indicates no asynchronous command is in
1734       progress, 1 indicates that an asynchronous command has started and -1
1735       indicated that an asynchronous command has been cancelled.
1736
1737       pg_standard_conforming_strings (boolean, read-only)
1738
1739       DBD::Pg specific attribute. Returns true if the server is currently
1740       using standard conforming strings. Only available if the target server
1741       is version 8.2 or better.
1742
1743       pg_INV_READ (integer, read-only)
1744
1745       Constant to be used for the mode in "pg_lo_creat" and "pg_lo_open".
1746
1747       pg_INV_WRITE (integer, read-only)
1748
1749       Constant to be used for the mode in "pg_lo_creat" and "pg_lo_open".
1750
1751       Driver (handle, read-only)
1752
1753       Holds the handle of the parent driver. The only recommended use for
1754       this is to find the name of the driver using:
1755
1756         $dbh->{Driver}->{Name}
1757
1758       pg_protocol (integer, read-only)
1759
1760       DBD::Pg specific attribute. Returns the version of the PostgreSQL
1761       server.  If DBD::Pg is unable to figure out the version, it will return
1762       a "0". Otherwise, a "3" is returned.
1763
1764       RowCacheSize
1765
1766       Not used by DBD::Pg
1767

DBI STATEMENT HANDLE OBJECTS

1769   Statement Handle Methods
1770       bind_param
1771
1772         $rv = $sth->bind_param($param_num, $bind_value);
1773         $rv = $sth->bind_param($param_num, $bind_value, $bind_type);
1774         $rv = $sth->bind_param($param_num, $bind_value, \%attr);
1775
1776       Allows the user to bind a value and/or a data type to a placeholder.
1777       This is especially important when using server-side prepares. See the
1778       "prepare" method for more information.
1779
1780       The value of $param_num is a number if using the '?' or '$1' style
1781       placeholders. If using ":foo" style placeholders, the complete name
1782       (e.g. ":foo") must be given. For numeric values, you can either use a
1783       number or use a literal '$1'. See the examples below.
1784
1785       The $bind_value argument is fairly self-explanatory. A value of "undef"
1786       will bind a "NULL" to the placeholder. Using "undef" is useful when you
1787       want to change just the type and will be overwriting the value later.
1788       (Any value is actually usable, but "undef" is easy and efficient).
1789
1790       The "\%attr" hash is used to indicate the data type of the placeholder.
1791       The default value is "varchar". If you need something else, you must
1792       use one of the values provided by DBI or by DBD::Pg. To use a SQL
1793       value, modify your "use DBI" statement at the top of your script as
1794       follows:
1795
1796         use DBI qw(:sql_types);
1797
1798       This will import some constants into your script. You can plug those
1799       directly into the "bind_param" call. Some common ones that you will
1800       encounter are:
1801
1802         SQL_INTEGER
1803
1804       To use PostgreSQL data types, import the list of values like this:
1805
1806         use DBD::Pg qw(:pg_types);
1807
1808       You can then set the data types by setting the value of the "pg_type"
1809       key in the hash passed to "bind_param".  The current list of Postgres
1810       data types exported is:
1811
1812        PG_ACLITEM PG_ACLITEMARRAY PG_ANY PG_ANYARRAY PG_ANYCOMPATIBLE PG_ANYCOMPATIBLEARRAY
1813        PG_ANYCOMPATIBLEMULTIRANGE PG_ANYCOMPATIBLENONARRAY PG_ANYCOMPATIBLERANGE PG_ANYELEMENT PG_ANYENUM PG_ANYMULTIRANGE
1814        PG_ANYNONARRAY PG_ANYRANGE PG_BIT PG_BITARRAY PG_BOOL PG_BOOLARRAY
1815        PG_BOX PG_BOXARRAY PG_BPCHAR PG_BPCHARARRAY PG_BYTEA PG_BYTEAARRAY
1816        PG_CHAR PG_CHARARRAY PG_CID PG_CIDARRAY PG_CIDR PG_CIDRARRAY
1817        PG_CIRCLE PG_CIRCLEARRAY PG_CSTRING PG_CSTRINGARRAY PG_DATE PG_DATEARRAY
1818        PG_DATEMULTIRANGE PG_DATEMULTIRANGEARRAY PG_DATERANGE PG_DATERANGEARRAY PG_EVENT_TRIGGER PG_FDW_HANDLER
1819        PG_FLOAT4 PG_FLOAT4ARRAY PG_FLOAT8 PG_FLOAT8ARRAY PG_GTSVECTOR PG_GTSVECTORARRAY
1820        PG_INDEX_AM_HANDLER PG_INET PG_INETARRAY PG_INT2 PG_INT2ARRAY PG_INT2VECTOR
1821        PG_INT2VECTORARRAY PG_INT4 PG_INT4ARRAY PG_INT4MULTIRANGE PG_INT4MULTIRANGEARRAY PG_INT4RANGE
1822        PG_INT4RANGEARRAY PG_INT8 PG_INT8ARRAY PG_INT8MULTIRANGE PG_INT8MULTIRANGEARRAY PG_INT8RANGE
1823        PG_INT8RANGEARRAY PG_INTERNAL PG_INTERVAL PG_INTERVALARRAY PG_JSON PG_JSONARRAY
1824        PG_JSONB PG_JSONBARRAY PG_JSONPATH PG_JSONPATHARRAY PG_LANGUAGE_HANDLER PG_LINE
1825        PG_LINEARRAY PG_LSEG PG_LSEGARRAY PG_MACADDR PG_MACADDR8 PG_MACADDR8ARRAY
1826        PG_MACADDRARRAY PG_MONEY PG_MONEYARRAY PG_NAME PG_NAMEARRAY PG_NUMERIC
1827        PG_NUMERICARRAY PG_NUMMULTIRANGE PG_NUMMULTIRANGEARRAY PG_NUMRANGE PG_NUMRANGEARRAY PG_OID
1828        PG_OIDARRAY PG_OIDVECTOR PG_OIDVECTORARRAY PG_PATH PG_PATHARRAY PG_PG_ATTRIBUTE
1829        PG_PG_ATTRIBUTEARRAY PG_PG_BRIN_BLOOM_SUMMARY PG_PG_BRIN_MINMAX_MULTI_SUMMARY PG_PG_CLASS PG_PG_CLASSARRAY PG_PG_DDL_COMMAND
1830        PG_PG_DEPENDENCIES PG_PG_LSN PG_PG_LSNARRAY PG_PG_MCV_LIST PG_PG_NDISTINCT PG_PG_NODE_TREE
1831        PG_PG_PROC PG_PG_PROCARRAY PG_PG_SNAPSHOT PG_PG_SNAPSHOTARRAY PG_PG_TYPE PG_PG_TYPEARRAY
1832        PG_POINT PG_POINTARRAY PG_POLYGON PG_POLYGONARRAY PG_RECORD PG_RECORDARRAY
1833        PG_REFCURSOR PG_REFCURSORARRAY PG_REGCLASS PG_REGCLASSARRAY PG_REGCOLLATION PG_REGCOLLATIONARRAY
1834        PG_REGCONFIG PG_REGCONFIGARRAY PG_REGDICTIONARY PG_REGDICTIONARYARRAY PG_REGNAMESPACE PG_REGNAMESPACEARRAY
1835        PG_REGOPER PG_REGOPERARRAY PG_REGOPERATOR PG_REGOPERATORARRAY PG_REGPROC PG_REGPROCARRAY
1836        PG_REGPROCEDURE PG_REGPROCEDUREARRAY PG_REGROLE PG_REGROLEARRAY PG_REGTYPE PG_REGTYPEARRAY
1837        PG_TABLE_AM_HANDLER PG_TEXT PG_TEXTARRAY PG_TID PG_TIDARRAY PG_TIME
1838        PG_TIMEARRAY PG_TIMESTAMP PG_TIMESTAMPARRAY PG_TIMESTAMPTZ PG_TIMESTAMPTZARRAY PG_TIMETZ
1839        PG_TIMETZARRAY PG_TRIGGER PG_TSMULTIRANGE PG_TSMULTIRANGEARRAY PG_TSM_HANDLER PG_TSQUERY
1840        PG_TSQUERYARRAY PG_TSRANGE PG_TSRANGEARRAY PG_TSTZMULTIRANGE PG_TSTZMULTIRANGEARRAY PG_TSTZRANGE
1841        PG_TSTZRANGEARRAY PG_TSVECTOR PG_TSVECTORARRAY PG_TXID_SNAPSHOT PG_TXID_SNAPSHOTARRAY PG_UNKNOWN
1842        PG_UUID PG_UUIDARRAY PG_VARBIT PG_VARBITARRAY PG_VARCHAR PG_VARCHARARRAY
1843        PG_VOID PG_XID PG_XID8 PG_XID8ARRAY PG_XIDARRAY PG_XML
1844        PG_XMLARRAY
1845
1846       Data types are "sticky," in that once a data type is set to a certain
1847       placeholder, it will remain for that placeholder, unless it is
1848       explicitly set to something else afterwards. If the statement has
1849       already been prepared, and you switch the data type to something else,
1850       DBD::Pg will re-prepare the statement for you before doing the next
1851       execute.
1852
1853       Examples:
1854
1855         use DBI qw(:sql_types);
1856         use DBD::Pg qw(:pg_types);
1857
1858         $SQL = "SELECT id FROM ptable WHERE size > ? AND title = ?";
1859         $sth = $dbh->prepare($SQL);
1860
1861         ## Both arguments below are bound to placeholders as "varchar"
1862         $sth->execute(123, "Merk");
1863
1864         ## Reset the datatype for the first placeholder to an integer
1865         $sth->bind_param(1, undef, SQL_INTEGER);
1866
1867         ## The "undef" bound above is not used, since we supply params to execute
1868         $sth->execute(123, "Merk");
1869
1870         ## Set the first placeholder's value and data type
1871         $sth->bind_param(1, 234, { pg_type => PG_TIMESTAMP });
1872
1873         ## Set the second placeholder's value and data type.
1874         ## We don't send a third argument, so the default "varchar" is used
1875         $sth->bind_param('$2', "Zool");
1876
1877         ## We realize that the wrong data type was set above, so we change it:
1878         $sth->bind_param('$1', 234, { pg_type => SQL_INTEGER });
1879
1880         ## We also got the wrong value, so we change that as well.
1881         ## Because the data type is sticky, we don't need to change it
1882         $sth->bind_param(1, 567);
1883
1884         ## This executes the statement with 567 (integer) and "Zool" (varchar)
1885         $sth->execute();
1886
1887       bind_param_inout
1888
1889         $rv = $sth->bind_param_inout($param_num, \$scalar, 0);
1890
1891       Experimental support for this feature is provided. The first argument
1892       to bind_param_inout should be a placeholder number. The second argument
1893       should be a reference to a scalar variable in your script. The third
1894       argument is not used and should simply be set to 0. Note that what this
1895       really does is assign a returned column to the variable, in the order
1896       in which the column appears. For example:
1897
1898         my $foo = 123;
1899         $sth = $dbh->prepare("SELECT 1+?::int");
1900         $sth->bind_param_inout(1, \$foo, 0);
1901         $foo = 222;
1902         $sth->execute(444);
1903         $sth->fetch;
1904
1905       The above will cause $foo to have a new value of "223" after the final
1906       fetch.  Note that the variables bound in this manner are very sticky,
1907       and will trump any values passed in to execute. This is because the
1908       binding is done as late as possible, at the execute() stage, allowing
1909       the value to be changed between the time it was bound and the time the
1910       query is executed. Thus, the above execute is the same as:
1911
1912         $sth->execute();
1913
1914       bind_param_array
1915
1916         $rv = $sth->bind_param_array($param_num, $array_ref_or_value)
1917         $rv = $sth->bind_param_array($param_num, $array_ref_or_value, $bind_type)
1918         $rv = $sth->bind_param_array($param_num, $array_ref_or_value, \%attr)
1919
1920       Binds an array of values to a placeholder, so that each is used in turn
1921       by a call to the "execute_array" method.
1922
1923       execute
1924
1925         $rv = $sth->execute(@bind_values);
1926
1927       Executes a previously prepared statement. In addition to "UPDATE",
1928       "DELETE", "INSERT" statements, for which it returns always the number
1929       of affected rows, the "execute" method can also be used for "SELECT ...
1930       INTO table" statements.
1931
1932       The "prepare/bind/execute" process has changed significantly for
1933       PostgreSQL servers 7.4 and later: please see the "prepare()" and
1934       "bind_param()" entries for much more information.
1935
1936       Setting one of the bind_values to "undef" is the equivalent of setting
1937       the value to NULL in the database. Setting the bind_value to
1938       $DBDPG_DEFAULT is equivalent to sending the literal string 'DEFAULT' to
1939       the backend. Note that using this option will force server-side
1940       prepares off until such time as PostgreSQL supports using DEFAULT in
1941       prepared statements.
1942
1943       DBD::Pg also supports passing in arrays to execute: simply pass in an
1944       arrayref, and DBD::Pg will flatten it into a string suitable for input
1945       on the backend.
1946
1947       If you are using Postgres version 8.2 or greater, you can also use any
1948       of the fetch methods to retrieve the values of a "RETURNING" clause
1949       after you execute an "UPDATE", "DELETE", or "INSERT". For example:
1950
1951         $dbh->do(q{CREATE TABLE abc (id SERIAL, country TEXT)});
1952         $SQL = q{INSERT INTO abc (country) VALUES (?) RETURNING id};
1953         $sth = $dbh->prepare($SQL);
1954         $sth->execute('France');
1955         $countryid = $sth->fetch()->[0];
1956         $sth->execute('New Zealand');
1957         $countryid = $sth->fetch()->[0];
1958
1959       execute_array
1960
1961         $tuples = $sth->execute_array() or die $sth->errstr;
1962         $tuples = $sth->execute_array(\%attr) or die $sth->errstr;
1963         $tuples = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
1964
1965         ($tuples, $rows) = $sth->execute_array(\%attr) or die $sth->errstr;
1966         ($tuples, $rows) = $sth->execute_array(\%attr, @bind_values) or die $sth->errstr;
1967
1968       Execute a prepared statement once for each item in a passed-in hashref,
1969       or items that were previously bound via the "bind_param_array" method.
1970       See the DBI documentation for more details.
1971
1972       execute_for_fetch
1973
1974         $tuples = $sth->execute_for_fetch($fetch_tuple_sub);
1975         $tuples = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
1976
1977         ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub);
1978         ($tuples, $rows) = $sth->execute_for_fetch($fetch_tuple_sub, \@tuple_status);
1979
1980       Used internally by the "execute_array" method, and rarely used
1981       directly. See the DBI documentation for more details.
1982
1983       fetchrow_arrayref
1984
1985         $ary_ref = $sth->fetchrow_arrayref;
1986
1987       Fetches the next row of data from the statement handle, and returns a
1988       reference to an array holding the column values. Any columns that are
1989       NULL are returned as undef within the array.
1990
1991       If there are no more rows or if an error occurs, then this method
1992       return undef. You should check "$sth->err" afterwards (or use the
1993       RaiseError attribute) to discover if the undef returned was due to an
1994       error.
1995
1996       Note that the same array reference is returned for each fetch, so don't
1997       store the reference and then use it after a later fetch. Also, the
1998       elements of the array are also reused for each row, so take care if you
1999       want to take a reference to an element. See also "bind_columns".
2000
2001       fetchrow_array
2002
2003         @ary = $sth->fetchrow_array;
2004
2005       Similar to the "fetchrow_arrayref" method, but returns a list of column
2006       information rather than a reference to a list. Do not use this in a
2007       scalar context.
2008
2009       fetchrow_hashref
2010
2011         $hash_ref = $sth->fetchrow_hashref;
2012         $hash_ref = $sth->fetchrow_hashref($name);
2013
2014       Fetches the next row of data and returns a hashref containing the name
2015       of the columns as the keys and the data itself as the values. Any NULL
2016       value is returned as an undef value.
2017
2018       If there are no more rows or if an error occurs, then this method
2019       return undef. You should check "$sth->err" afterwards (or use the
2020       RaiseError attribute) to discover if the undef returned was due to an
2021       error.
2022
2023       The optional $name argument should be either "NAME", "NAME_lc" or
2024       "NAME_uc", and indicates what sort of transformation to make to the
2025       keys in the hash.
2026
2027       fetchall_arrayref
2028
2029         $tbl_ary_ref = $sth->fetchall_arrayref();
2030         $tbl_ary_ref = $sth->fetchall_arrayref( $slice );
2031         $tbl_ary_ref = $sth->fetchall_arrayref( $slice, $max_rows );
2032
2033       Returns a reference to an array of arrays that contains all the
2034       remaining rows to be fetched from the statement handle. If there are no
2035       more rows, an empty arrayref will be returned. If an error occurs, the
2036       data read in so far will be returned. Because of this, you should
2037       always check "$sth->err" after calling this method, unless RaiseError
2038       has been enabled.
2039
2040       If $slice is an array reference, fetchall_arrayref uses the
2041       "fetchrow_arrayref" method to fetch each row as an array ref. If the
2042       $slice array is not empty then it is used as a slice to select
2043       individual columns by perl array index number (starting at 0, unlike
2044       column and parameter numbers which start at 1).
2045
2046       With no parameters, or if $slice is undefined, fetchall_arrayref acts
2047       as if passed an empty array ref.
2048
2049       If $slice is a hash reference, fetchall_arrayref uses
2050       "fetchrow_hashref" to fetch each row as a hash reference.
2051
2052       See the DBI documentation for a complete discussion.
2053
2054       fetchall_hashref
2055
2056         $hash_ref = $sth->fetchall_hashref( $key_field );
2057
2058       Returns a hashref containing all rows to be fetched from the statement
2059       handle. See the DBI documentation for a full discussion.
2060
2061       finish
2062
2063         $rv = $sth->finish;
2064
2065       Indicates to DBI that you are finished with the statement handle and
2066       are not going to use it again. Only needed when you have not fetched
2067       all the possible rows.
2068
2069       rows
2070
2071         $rv = $sth->rows;
2072
2073       Returns the number of rows returned by the last query. In contrast to
2074       many other DBD modules, the number of rows is available immediately
2075       after calling "$sth->execute". Note that the "execute" method itself
2076       returns the number of rows itself, which means that this method is
2077       rarely needed.
2078
2079       bind_col
2080
2081         $rv = $sth->bind_col($column_number, \$var_to_bind);
2082         $rv = $sth->bind_col($column_number, \$var_to_bind, \%attr );
2083         $rv = $sth->bind_col($column_number, \$var_to_bind, $bind_type );
2084
2085       Binds a Perl variable and/or some attributes to an output column of a
2086       SELECT statement.  Column numbers count up from 1. You do not need to
2087       bind output columns in order to fetch data.
2088
2089       See the DBI documentation for a discussion of the optional parameters
2090       "\%attr" and $bind_type
2091
2092       bind_columns
2093
2094         $rv = $sth->bind_columns(@list_of_refs_to_vars_to_bind);
2095
2096       Calls the "bind_col" method for each column in the SELECT statement,
2097       using the supplied list.
2098
2099       dump_results
2100
2101         $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
2102
2103       Fetches all the rows from the statement handle, calls "DBI::neat_list"
2104       for each row, and prints the results to $fh (which defaults to STDOUT).
2105       Rows are separated by $lsep (which defaults to a newline). Columns are
2106       separated by $fsep (which defaults to a comma). The $maxlen controls
2107       how wide the output can be, and defaults to 35.
2108
2109       This method is designed as a handy utility for prototyping and testing
2110       queries. Since it uses "neat_list" to format and edit the string for
2111       reading by humans, it is not recommended for data transfer
2112       applications.
2113
2114       blob_read
2115
2116         $blob = $sth->blob_read($id, $offset, $len);
2117
2118       Supported by DBD::Pg. This method is implemented by DBI but not
2119       currently documented by DBI, so this method might change.
2120
2121       This method seems to be heavily influenced by the current
2122       implementation of blobs in Oracle. Nevertheless we try to be as
2123       compatible as possible. Whereas Oracle suffers from the limitation that
2124       blobs are related to tables and every table can have only one blob
2125       (datatype LONG), PostgreSQL handles its blobs independent of any table
2126       by using so-called object identifiers. This explains why the
2127       "blob_read" method is blessed into the STATEMENT package and not part
2128       of the DATABASE package. Here the field parameter has been used to
2129       handle this object identifier. The offset and len parameters may be set
2130       to zero, in which case the whole blob is fetched at once.
2131
2132       See also the PostgreSQL-specific functions concerning blobs, which are
2133       available via the "func" interface.
2134
2135       For further information and examples about blobs, please read the
2136       chapter about Large Objects in the PostgreSQL Programmer's Guide at
2137       <http://www.postgresql.org/docs/current/static/largeobjects.html>.
2138
2139       pg_canonical_ids
2140
2141         $data = $sth->pg_canonical_ids;
2142
2143       DBD::Pg specific method. It returns Oid of table and position in table
2144       for every column in result set.
2145
2146       Returns array of arrays with Table Oid and Column Position for every
2147       column in result set or undef if current column is not a simple
2148       reference.
2149
2150       pg_canonical_names
2151
2152         $data = $sth->pg_canonical_names;
2153
2154       DBD::Pg specific method. It returns array of original (or canonical)
2155       names (from where this data is actually came from) of columns in
2156       Schema.Table.Column format or undef if current column is not a simple
2157       reference.
2158
2159       Note that this method is quite slow because it need additional
2160       information from server for every column that is simple reference.
2161       Consider to use "pg_canonical_ids" instead.
2162
2163       last_insert_id
2164
2165         $rv = $sth->last_insert_id(undef, $schema, $table, undef);
2166         $rv = $sth->last_insert_id(undef, $schema, $table, undef, {sequence => $seqname});
2167
2168       This is simply an alternative way to return the same information as
2169       "$dbh->last_insert_id".
2170
2171   Statement Handle Attributes
2172       NUM_OF_FIELDS (integer, read-only)
2173
2174       Returns the number of columns returned by the current statement. A
2175       number will only be returned for SELECT statements, for SHOW statements
2176       (which always return 1), and for INSERT, UPDATE, and DELETE statements
2177       which contain a RETURNING clause.  This method returns undef if called
2178       before "execute()".
2179
2180       NUM_OF_PARAMS (integer, read-only)
2181
2182       Returns the number of placeholders in the current statement.
2183
2184       NAME (arrayref, read-only)
2185
2186       Returns an arrayref of column names for the current statement. This
2187       method will only work for SELECT statements, for SHOW statements, and
2188       for INSERT, UPDATE, and DELETE statements which contain a RETURNING
2189       clause.  This method returns undef if called before "execute()".
2190
2191       NAME_lc (arrayref, read-only)
2192
2193       The same as the "NAME" attribute, except that all column names are
2194       forced to lower case.
2195
2196       NAME_uc  (arrayref, read-only)
2197
2198       The same as the "NAME" attribute, except that all column names are
2199       forced to upper case.
2200
2201       NAME_hash (hashref, read-only)
2202
2203       Similar to the "NAME" attribute, but returns a hashref of column names
2204       instead of an arrayref. The names of the columns are the keys of the
2205       hash, and the values represent the order in which the columns are
2206       returned, starting at 0.  This method returns undef if called before
2207       "execute()".
2208
2209       NAME_lc_hash (hashref, read-only)
2210
2211       The same as the "NAME_hash" attribute, except that all column names are
2212       forced to lower case.
2213
2214       NAME_uc_hash (hashref, read-only)
2215
2216       The same as the "NAME_hash" attribute, except that all column names are
2217       forced to lower case.
2218
2219       TYPE (arrayref, read-only)
2220
2221       Returns an arrayref indicating the data type for each column in the
2222       statement.  This method returns undef if called before "execute()".
2223
2224       PRECISION (arrayref, read-only)
2225
2226       Returns an arrayref of integer values for each column returned by the
2227       statement.  The number indicates the precision for "NUMERIC" columns,
2228       the size in number of characters for "CHAR" and "VARCHAR" columns, and
2229       for all other types of columns it returns the number of bytes.  This
2230       method returns undef if called before "execute()".
2231
2232       SCALE (arrayref, read-only)
2233
2234       Returns an arrayref of integer values for each column returned by the
2235       statement. The number indicates the scale of the that column. The only
2236       type that will return a value is "NUMERIC".  This method returns undef
2237       if called before "execute()".
2238
2239       NULLABLE (arrayref, read-only)
2240
2241       Returns an arrayref of integer values for each column returned by the
2242       statement. The number indicates if the column is nullable or not. 0 =
2243       not nullable, 1 = nullable, 2 = unknown.  This method returns undef if
2244       called before "execute()".
2245
2246       Database (dbh, read-only)
2247
2248       Returns the database handle this statement handle was created from.
2249
2250       ParamValues (hash ref, read-only)
2251
2252       Returns a reference to a hash containing the values currently bound to
2253       placeholders. If the "named parameters" type of placeholders are being
2254       used (such as ":foo"), then the keys of the hash will be the names of
2255       the placeholders (without the colon). If the "dollar sign numbers" type
2256       of placeholders are being used, the keys of the hash will be the
2257       numbers, without the dollar signs. If the "question mark" type is used,
2258       integer numbers will be returned, starting at one and increasing for
2259       every placeholder.
2260
2261       If this method is called before "execute", the literal values passed in
2262       are returned. If called after "execute", then the quoted versions of
2263       the values are returned.
2264
2265       ParamTypes (hash ref, read-only)
2266
2267       Returns a reference to a hash containing the type names currently bound
2268       to placeholders. The keys are the same as returned by the ParamValues
2269       method. The values are hashrefs containing a single key value pair, in
2270       which the key is either 'TYPE' if the type has a generic SQL
2271       equivalent, and 'pg_type' if the type can only be expressed by a
2272       Postgres type. The value is the internal number corresponding to the
2273       type originally passed in. (Placeholders that have not yet been bound
2274       will return undef as the value). This allows the output of ParamTypes
2275       to be passed back to the "bind_param" method.
2276
2277       Statement (string, read-only)
2278
2279       Returns the statement string passed to the most recent "prepare" method
2280       called in this database handle, even if that method failed. This is
2281       especially useful where "RaiseError" is enabled and the exception
2282       handler checks $@ and sees that a "prepare" method call failed.
2283
2284       pg_current_row (integer, read-only)
2285
2286       DBD::Pg specific attribute. Returns the number of the tuple (row) that
2287       was most recently fetched. Returns zero before and after fetching is
2288       performed.
2289
2290       pg_numbound (integer, read-only)
2291
2292       DBD::Pg specific attribute. Returns the number of placeholders that are
2293       currently bound (via bind_param).
2294
2295       pg_bound (hashref, read-only)
2296
2297       DBD::Pg specific attribute. Returns a hash of all named placeholders.
2298       The key is the name of the placeholder, and the value is a 0 or a 1,
2299       indicating if the placeholder has been bound yet (e.g. via bind_param)
2300
2301       pg_size (arrayref, read-only)
2302
2303       DBD::Pg specific attribute. It returns a reference to an array of
2304       integer values for each column. The integer shows the size of the
2305       column in bytes. Variable length columns are indicated by -1.
2306
2307       pg_type (arrayref, read-only)
2308
2309       DBD::Pg specific attribute. It returns a reference to an array of
2310       strings for each column. The string shows the name of the data_type.
2311
2312       pg_segments (arrayref, read-only)
2313
2314       DBD::Pg specific attribute. Returns an arrayref of the query split on
2315       the placeholders.
2316
2317       pg_oid_status (integer, read-only)
2318
2319       DBD::Pg specific attribute. It returns the OID of the last INSERT
2320       command.
2321
2322       pg_cmd_status (integer, read-only)
2323
2324       DBD::Pg specific attribute. It returns the type of the last command.
2325       Possible types are: "INSERT", "DELETE", "UPDATE", "SELECT".
2326
2327       pg_direct (boolean)
2328
2329       DBD::Pg specific attribute. Default is false. If true, the query is
2330       passed directly to the backend without parsing for placeholders.
2331
2332       pg_prepare_now (boolean)
2333
2334       DBD::Pg specific attribute. Default is off. If true, the query will be
2335       immediately prepared, rather than waiting for the "execute" call.
2336
2337       pg_prepare_name (string)
2338
2339       DBD::Pg specific attribute. Specifies the name of the prepared
2340       statement to use for this statement handle. Not normally needed, see
2341       the section on the "prepare" method for more information.
2342
2343       pg_server_prepare (boolean)
2344
2345       DBD::Pg specific attribute. Indicates if DBD::Pg should attempt to use
2346       server-side prepared statements for this statement handle. The default
2347       value, true, indicates that prepared statements should be used whenever
2348       possible. See the section on the "prepare" method for more information.
2349
2350       pg_switch_prepared (integer)
2351
2352       DBD::Pg specific attribute. Indicates when DBD::Pg will internally
2353       switch from using PQexecParams to PQexecPrepared. In other words, when
2354       it will start using server-side prepared statements (assuming all other
2355       requirements for them are met). The default value, 2, means that a
2356       prepared statement will be prepared and used the second and subsequent
2357       time execute is called. To always use PQexecPrepared instead of
2358       PQexecParams, set pg_switch_prepared to 1 (this was the default
2359       behavior in earlier versions).  Setting pg_switch_prepared to 0 will
2360       force DBD::Pg to always use PQexecParams.
2361
2362       pg_placeholder_dollaronly (boolean)
2363
2364       DBD::Pg specific attribute. Defaults to false. When true, question
2365       marks inside of the query being prepared are not treated as
2366       placeholders. Useful for statements that contain unquoted question
2367       marks, such as geometric operators. Note that you may also simply
2368       escape question marks with a backslash to prevent them from being
2369       treated as placeholders.
2370
2371       pg_placeholder_nocolons (boolean)
2372
2373       DBD::Pg specific attribute. Defaults to false. When true, colons inside
2374       of statements are not treated as placeholders. Useful for statements
2375       that contain an array slice. You may also place a backslash directly
2376       before the colon to prevent it from being treated as a placeholder.
2377
2378       pg_async (integer)
2379
2380       DBD::Pg specific attribute. Indicates the current behavior for
2381       asynchronous queries. See the section on "Asynchronous Constants" for
2382       more information.
2383
2384       pg_async_status (integer, read-only)
2385
2386       DBD::Pg specific attribute. Returns the current status of an
2387       asynchronous command. 0 indicates no asynchronous command is in
2388       progress, 1 indicates that an asynchronous command has started and -1
2389       indicated that an asynchronous command has been cancelled.
2390
2391       RowsInCache
2392
2393       Not used by DBD::Pg
2394
2395       RowCache
2396
2397       Not used by DBD::Pg
2398
2399       CursorName
2400
2401       Not used by DBD::Pg. See the note about "Cursors" elsewhere in this
2402       document.
2403

FURTHER INFORMATION

2405   Encoding
2406       DBD::Pg has extensive support for a client_encoding of UTF-8, and most
2407       things like encoding and decoding should happen automatically. If you
2408       are using a different encoding, you will need do the encoding and
2409       decoding yourself. For this reason, it is highly recommended to always
2410       use a client_encoding of UTF-8. The server_encoding can be anything,
2411       and no recommendations are made there, other than avoid SQL_ASCII
2412       whenever possible.
2413
2414   Transactions
2415       Transaction behavior is controlled via the "AutoCommit" attribute. For
2416       a complete definition of "AutoCommit" please refer to the DBI
2417       documentation.
2418
2419       According to the DBI specification the default for "AutoCommit" is a
2420       true value. In this mode, any change to the database becomes valid
2421       immediately. Any "BEGIN", "COMMIT" or "ROLLBACK" statements will be
2422       rejected. Note that preparing a statement does not always contact the
2423       server, as the actual "PREPARE" is usually postponed until the first
2424       call to "execute".
2425
2426   Savepoints
2427       PostgreSQL version 8.0 introduced the concept of savepoints, which
2428       allows transactions to be rolled back to a certain point without
2429       affecting the rest of the transaction. DBD::Pg encourages using the
2430       following methods to control savepoints:
2431
2432       "pg_savepoint"
2433
2434       Creates a savepoint. This will fail unless you are inside of a
2435       transaction. The only argument is the name of the savepoint. Note that
2436       PostgreSQL DOES allow multiple savepoints with the same name to exist.
2437
2438         $dbh->pg_savepoint("mysavepoint");
2439
2440       "pg_rollback_to"
2441
2442       Rolls the database back to a named savepoint, discarding any work
2443       performed after that point. If more than one savepoint with that name
2444       exists, rolls back to the most recently created one.
2445
2446         $dbh->pg_rollback_to("mysavepoint");
2447
2448       "pg_release"
2449
2450       Releases (or removes) a named savepoint. If more than one savepoint
2451       with that name exists, it will only destroy the most recently created
2452       one. Note that all savepoints created after the one being released are
2453       also destroyed.
2454
2455         $dbh->pg_release("mysavepoint");
2456
2457   Asynchronous Queries
2458       It is possible to send a query to the backend and have your script do
2459       other work while the query is running on the backend. Both queries sent
2460       by the "do" method, and by the "execute" method can be sent
2461       asynchronously. The basic usage is as follows:
2462
2463         print "Async do() example:\n";
2464         $dbh->do("SELECT long_running_query()", {pg_async => PG_ASYNC});
2465         do_something_else();
2466         {
2467           if ($dbh->pg_ready()) {
2468             $res = $dbh->pg_result();
2469             print "Result of do(): $res\n";
2470           }
2471           print "Query is still running...\n";
2472           if (cancel_request_received) {
2473             $dbh->pg_cancel();
2474           }
2475           sleep 1;
2476           redo;
2477         }
2478
2479         print "Async prepare/execute example:\n";
2480         $sth = $dbh->prepare("SELECT long_running_query(1)", {pg_async => PG_ASYNC});
2481         $sth->execute();
2482
2483         ## Changed our mind, cancel and run again:
2484         $sth = $dbh->prepare("SELECT 678", {pg_async => PG_ASYNC + PG_OLDQUERY_CANCEL});
2485         $sth->execute();
2486
2487         do_something_else();
2488
2489         if (!$sth->pg_ready) {
2490           do_another_thing();
2491         }
2492
2493         ## We wait until it is done, and get the result:
2494         $res = $dbh->pg_result();
2495
2496       Asynchronous Constants
2497
2498       There are currently three asynchronous constants automatically exported
2499       by DBD::Pg.
2500
2501       PG_ASYNC
2502           This is a constant for the number 1. It is passed to either the
2503           "do" or the "prepare" method as a value to the pg_async key and
2504           indicates that the query should be sent asynchronously.
2505
2506       PG_OLDQUERY_CANCEL
2507           This is a constant for the number 2. When passed to either the "do"
2508           or the "prepare" method, it causes any currently running
2509           asynchronous query to be cancelled and rolled back. It has no
2510           effect if no asynchronous query is currently running.
2511
2512       PG_OLDQUERY_WAIT
2513           This is a constant for the number 4. When passed to either the "do"
2514           or the "prepare" method, it waits for any currently running
2515           asynchronous query to complete. It has no effect if there is no
2516           asynchronous query currently running.
2517
2518       Asynchronous Methods
2519
2520       pg_cancel
2521           This database-level method attempts to cancel any currently running
2522           asynchronous query. It returns true if the cancel succeeded, and
2523           false otherwise. Note that a query that has finished before this
2524           method is executed will also return false. WARNING: a successful
2525           cancellation may leave the database in an unusable state, so you
2526           may need to ROLLBACK or ROLLBACK TO a savepoint. As of version
2527           2.17.0 of DBD::Pg, rollbacks are not done automatically.
2528
2529             $result = $dbh->pg_cancel();
2530
2531       pg_ready
2532           This method can be called as a database handle method or (for
2533           convenience) as a statement handle method. Both simply see if a
2534           previously issued asynchronous query has completed yet. It returns
2535           true if the statement has finished, in which case you should then
2536           call the "pg_result" method. Calls to "pg_ready()" should only be
2537           used when you have other things to do while the query is running.
2538           If you simply want to wait until the query is done, do not call
2539           pg_ready() over and over, but simply call the pg_result() method.
2540
2541             my $time = 0;
2542             while (!$dbh->pg_ready) {
2543               print "Query is still running. Seconds: $time\n";
2544               $time++;
2545               sleep 1;
2546             }
2547             $result = $dbh->pg_result;
2548
2549       pg_result
2550           This database handle method returns the results of a previously
2551           issued asynchronous query. If the query is still running, this
2552           method will wait until it has finished. The result returned is the
2553           number of rows: the same thing that would have been returned by the
2554           asynchronous "do" or "execute" if it had been called without an
2555           asynchronous flag.
2556
2557             $result = $dbh->pg_result;
2558
2559       Asynchronous Examples
2560
2561       Here are some working examples of asynchronous queries. Note that we'll
2562       use the pg_sleep function to emulate a long-running query.
2563
2564         use strict;
2565         use warnings;
2566         use Time::HiRes 'sleep';
2567         use DBD::Pg ':async';
2568
2569         my $dbh = DBI->connect('dbi:Pg:dbname=postgres', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
2570
2571         ## Kick off a long running query on the first database:
2572         my $sth = $dbh->prepare("SELECT pg_sleep(?)", {pg_async => PG_ASYNC});
2573         $sth->execute(5);
2574
2575         ## While that is running, do some other things
2576         print "Your query is processing. Thanks for waiting\n";
2577         check_on_the_kids(); ## Expensive sub, takes at least three seconds.
2578
2579         while (!$dbh->pg_ready) {
2580           check_on_the_kids();
2581           ## If the above function returns quickly for some reason, we add a small sleep
2582           sleep 0.1;
2583         }
2584
2585         print "The query has finished. Gathering results\n";
2586         my $result = $sth->pg_result;
2587         print "Result: $result\n";
2588         my $info = $sth->fetchall_arrayref();
2589
2590       Without asynchronous queries, the above script would take about 8
2591       seconds to run: five seconds waiting for the execute to finish, then
2592       three for the check_on_the_kids() function to return. With asynchronous
2593       queries, the script takes about 6 seconds to run, and gets in two
2594       iterations of check_on_the_kids in the process.
2595
2596       Here's an example showing the ability to cancel a long-running query.
2597       Imagine two slave databases in different geographic locations over a
2598       slow network. You need information as quickly as possible, so you query
2599       both at once. When you get an answer, you tell the other one to stop
2600       working on your query, as you don't need it anymore.
2601
2602         use strict;
2603         use warnings;
2604         use Time::HiRes 'sleep';
2605         use DBD::Pg ':async';
2606
2607         my $dbhslave1 = DBI->connect('dbi:Pg:dbname=postgres;host=slave1', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
2608         my $dbhslave2 = DBI->connect('dbi:Pg:dbname=postgres;host=slave2', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
2609
2610         $SQL = "SELECT count(*) FROM largetable WHERE flavor='blueberry'";
2611
2612         my $sth1 = $dbhslave1->prepare($SQL, {pg_async => PG_ASYNC});
2613         my $sth2 = $dbhslave2->prepare($SQL, {pg_async => PG_ASYNC});
2614
2615         $sth1->execute();
2616         $sth2->execute();
2617
2618         my $winner;
2619         while (!defined $winner) {
2620           if ($sth1->pg_ready) {
2621             $winner = 1;
2622           }
2623           elsif ($sth2->pg_ready) {
2624             $winner = 2;
2625           }
2626           Time::HiRes::sleep 0.05;
2627         }
2628
2629         my $count;
2630         if ($winner == 1) {
2631           $sth2->pg_cancel();
2632           $sth1->pg_result();
2633           $count = $sth1->fetchall_arrayref()->[0][0];
2634         }
2635         else {
2636           $sth1->pg_cancel();
2637           $sth2->pg_result();
2638           $count = $sth2->fetchall_arrayref()->[0][0];
2639         }
2640
2641   Array support
2642       DBD::Pg allows arrays (as arrayrefs) to be passed in to both the
2643       "quote" and the "execute" methods. In both cases, the array is
2644       flattened into a string representing a Postgres array.
2645
2646       When fetching rows from a table that contains a column with an array
2647       type, the result will be passed back to your script as an arrayref.
2648
2649       To turn off the automatic parsing of returned arrays into arrayrefs,
2650       you can set the attribute pg_expand_array, which is true by default.
2651
2652         $dbh->{pg_expand_array} = 0;
2653
2654   COPY support
2655       DBD::Pg allows for quick (bulk) reading and storing of data by using
2656       the COPY command. The basic process is to use "$dbh->do" to issue a
2657       COPY command, and then to either add rows using "pg_putcopydata", or to
2658       read them by using "pg_getcopydata".
2659
2660       The first step is to put the server into "COPY" mode. This is done by
2661       sending a complete COPY command to the server, by using the "do"
2662       method.  For example:
2663
2664         $dbh->do("COPY foobar FROM STDIN");
2665
2666       This would tell the server to enter a COPY IN mode (yes, that's
2667       confusing, but the mode is COPY IN because of the command COPY FROM).
2668       It is now ready to receive information via the "pg_putcopydata" method.
2669       The complete syntax of the COPY command is more complex and not
2670       documented here: the canonical PostgreSQL documentation for COPY can be
2671       found at:
2672
2673       http://www.postgresql.org/docs/current/static/sql-copy.html
2674
2675       Once a COPY command has been issued, no other SQL commands are allowed
2676       until "pg_putcopyend" has been issued (for COPY FROM), or the final
2677       "pg_getcopydata" has been called (for COPY TO).
2678
2679       Note: All other COPY methods (pg_putline, pg_getline, etc.) are now
2680       heavily deprecated in favor of the pg_getcopydata, pg_putcopydata, and
2681       pg_putcopyend methods.
2682
2683       pg_getcopydata
2684
2685       Used to retrieve data from a table after the server has been put into a
2686       COPY OUT mode by calling "COPY tablename TO STDOUT". Data is always
2687       returned one data row at a time. The first argument to pg_getcopydata
2688       is the variable into which the data will be stored (this variable
2689       should not be undefined, or it may throw a warning, although it may be
2690       a reference). The pg_getcopydata method returns a number greater than 1
2691       indicating the new size of the variable, or a -1 when the COPY has
2692       finished. Once a -1 has been returned, no other action is necessary, as
2693       COPY mode will have already terminated. Example:
2694
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   Cursors
2767       Although PostgreSQL supports cursors, they have not been used in the
2768       current implementation. When DBD::Pg was created, cursors in PostgreSQL
2769       could only be used inside a transaction block. Because only one
2770       transaction block at a time is allowed, this would have implied the
2771       restriction not to use any nested "SELECT" statements. Therefore the
2772       "execute" method fetches all data at once into data structures located
2773       in the front-end application. This fact must to be considered when
2774       selecting large amounts of data!
2775
2776       You can use cursors in your application, but you'll need to do a little
2777       work. First you must declare your cursor. Now you can issue queries
2778       against the cursor, then select against your queries. This typically
2779       results in a double loop, like this:
2780
2781         # WITH HOLD is not needed if AutoCommit is off
2782         $dbh->do("DECLARE csr CURSOR WITH HOLD FOR $sql");
2783         while (1) {
2784           my $sth = $dbh->prepare("fetch 1000 from csr");
2785           $sth->execute;
2786           last if 0 == $sth->rows;
2787
2788           while (my $row = $sth->fetchrow_hashref) {
2789             # Do something with the data.
2790           }
2791         }
2792         $dbh->do("CLOSE csr");
2793
2794   Datatype bool
2795       The current implementation of PostgreSQL returns 't' for true and 'f'
2796       for false. From the Perl point of view, this is a rather unfortunate
2797       choice. DBD::Pg therefore translates the result for the "BOOL" data
2798       type in a Perlish manner: 'f' becomes the number 0 and 't' becomes the
2799       number 1. This way the application does not have to check the database-
2800       specific returned values for the data-type "BOOL" because Perl treats 0
2801       as false and 1 as true. You may set the pg_bool_tf attribute to a true
2802       value to change the values back to 't' and 'f' if you wish.
2803
2804       Boolean values can be passed to PostgreSQL as TRUE, 't', 'true', 'y',
2805       'yes' or '1' for true and FALSE, 'f', 'false', 'n', 'no' or '0' for
2806       false.
2807
2808   Schema support
2809       The PostgreSQL schema concept may differ from those of other databases.
2810       In a nutshell, a schema is a named collection of objects within a
2811       single database. Please refer to the PostgreSQL documentation for more
2812       details:
2813
2814       <http://www.postgresql.org/docs/current/static/ddl-schemas.html>
2815
2816       DBD::Pg does not provide explicit support for PostgreSQL schemas.
2817       However, schema functionality may be used without any restrictions by
2818       explicitly addressing schema objects, e.g.
2819
2820         my $res = $dbh->selectall_arrayref("SELECT * FROM my_schema.my_table");
2821
2822       or by manipulating the schema search path with "SET search_path", e.g.
2823
2824         $dbh->do("SET search_path TO my_schema, public");
2825

SEE ALSO

2827       The DBI module
2828

BUGS

2830       To report a bug, or view the current list of bugs, please visit
2831       https://github.com/bucardo/dbdpg/issues
2832

DEVELOPMENT

2834       Pull requests can be submitted to github. Detailed information on how
2835       to help out with this module can be found in the README.dev file. The
2836       latest development version can be obtained via: git clone
2837       git://github.com/bucardo/dbdpg.git
2838

AUTHORS

2840       DBI by Tim Bunce <http://www.tim.bunce.name>
2841
2842       The original DBD-Pg was by Edmund Mergl (E.Mergl@bawue.de) and Jeffrey
2843       W. Baker (jwbaker@acm.org). Major developers include David Wheeler
2844       <david@justatheory.com>, Jason Stewart <jason@openinformatics.com>,
2845       Bruce Momjian <pgman@candle.pha.pa.us>, and Greg Sabino Mullane
2846       <greg@turnstep.com>, with help from many others: see the Changes file
2847       (<http://search.cpan.org/dist/DBD-Pg/Changes>) for a complete list.
2848
2849       Parts of this package were originally copied from DBI and DBD-Oracle.
2850
2851       Mailing List
2852
2853       The current maintainers may be reached through the 'dbd-pg' mailing
2854       list: <dbd-pg@perl.org>. Subscribe by sending an email to
2855       dbd-pg-subscribe@perl.org.  Visit the archives at
2856       http://grokbase.com/g/perl/dbd-pg
2857
2859       Copyright (C) 1994-2022, Greg Sabino Mullane
2860
2861       This module (DBD::Pg) is free software; you can redistribute it and/or
2862       modify it under the same terms as Perl 5.10.0. For more details, see
2863       the full text of the licenses in the directory LICENSES.
2864
2865
2866
2867perl v5.34.0                      2022-02-21                             Pg(3)
Impressum