1Pg(3) User Contributed Perl Documentation Pg(3)
2
6 DBD::Pg - PostgreSQL database driver for the DBI module
7
9 This documents version 1.49 of the DBD::Pg module
10
12 use DBI;
13 $dbh = DBI->connect("dbi:Pg:dbname=$dbname", "", "", {AutoCommit => 0});
15 # For some advanced uses you may need PostgreSQL type values:
17 use DBD::Pg qw(:pg_types);
18 # See the DBI module documentation for full details
20
22 DBD::Pg is a Perl module that works with the DBI module to provide
23 access to PostgreSQL databases.
24
26 This documentation describes driver specific behavior and restrictions.
27 It is not supposed to be used as the only reference for the user. In
28 any case consult the DBI documentation first!
29
31 DBI Class Methods
32 connect
34 To connect to a database with a minimum of parameters, use the fol‐
35 lowing syntax:
36 $dbh = DBI->connect("dbi:Pg:dbname=$dbname", "", "");
38 This connects to the database $dbname on the default port (usually
40 5432) without any user authentication.
41 The following connect statement shows almost all possible parame‐
43 ters:
44 $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host;port=$port;" .
46 "options=$options", "$username", "$password",
47 {AutoCommit => 0});
48 If a parameter is not given, the PostgreSQL server will first look
50 for specific environment variables, and then use hard-coded
51 defaults:
52 parameter environment variable hard coded default
54 --------------------------------------------------
55 host PGHOST local domain socket
56 hostaddr* PGHOSTADDR local domain socket
57 port PGPORT 5432
58 dbname** PGDATABASE current userid
59 username PGUSER current userid
60 password PGPASSWORD (none)
61 options PGOPTIONS (none)
62 service* PGSERVICE (none)
63 sslmode* PGSSLMODE (none)
64 * Only for servers running version 7.4 or greater
66 ** Can also use "db" or "database"
68 The options parameter specifies runtime options for the Postgres
70 backend. Common usage is to increase the number of buffers with the
71 "-B" option. Also important is the "-F" option, which disables
72 automatic fsync() call after each transaction. For further details
73 please refer to the PostgreSQL documentation at <http://www.post‐
74 gresql.org/docs/>.
75 For authentication with username and password, appropriate entries
77 have to be made in pg_hba.conf. Please refer to the comments in the
78 pg_hba.conf and the pg_passwd files for the different types of
79 authentication. Note that for these two parameters DBI distin‐
80 guishes between empty and undefined. If these parameters are unde‐
81 fined DBI substitutes the values of the environment variables
82 "DBI_USER" and "DBI_PASS" if present.
83 You can also conenct by using a service connection file, which is
85 named "pg_service.conf." The location of this file can be con‐
86 trolled by setting the "PGSYSCONFDIR" environment variable. To use
87 one of the named services within the file, set the name by using
88 either the "service" parameter or the environment variable "PGSER‐
89 VICE". Note that when connecting this way, only the minimum parame‐
90 ters should be used. For example, to connect to a service named
91 "zephyr", you could use:
92 $dbh = DBI->connect("dbi:Pg:service=zephyr", "", "");
94 You could also set $ENV{PGSERVICE} to "zephyr" and connect like
96 this:
97 $dbh = DBI->connect("dbi:Pg:", "", "");
99 The format of the pg_service.conf file is simply a bracketed ser‐
101 vice name, followed by one parameter per line in the format
102 name=value. For example:
103 [zephyr]
105 dbname=winds
106 user=wisp
107 password=W$2Hc00YSgP
108 port=6543
109 There are four valid arguments to the "sslmode" parameter, which
111 controls whether to use SSL to connect to the database:
112 disable - SSL connections are never used =item allow - try non-SSL,
114 then SSL =item prefer - try SSL, then non-SSL =item require - con‐
115 nect only with SSL
116 connect_cached
117 Implemented by DBI, no driver-specific impact.
118 installed_drivers
120 Implemented by DBI, no driver-specific impact.
121 installed_versions
123 Implemented by DBI, no driver-specific impact.
124 available_drivers
126 @driver_names = DBI->available_drivers;
127 Implemented by DBI, no driver-specific impact.
129 data_sources
131 @data_sources = DBI->data_sources('Pg');
132 This driver supports this method. Note that the necessary database
134 connection to the database "template1" will be made on the local‐
135 host without any user authentication. Other preferences can only be
136 set with the environment variables "PGHOST", "PGPORT", "DBI_USER",
137 "DBI_PASS", and "PGSERVICE".
138
140 err
141 $rv = $h->err;
142 Supported by this driver as proposed by DBI. For the connect method
144 it returns "PQstatus". In all other cases it returns "PQresultSta‐
145 tus" of the current handle.
146 errstr
148 $str = $h->errstr;
149 Supported by this driver as proposed by DBI. It returns the "PQer‐
151 rorMessage" related to the current handle.
152 state
154 $str = $h->state;
155 Supported by this driver. Returns a five-character "SQLSTATE" code.
157 Success is indicated by a "00000" code, which gets mapped to an
158 empty string by DBI. A code of S8006 indicates a connection fail‐
159 ure, usually because the connection to the PostgreSQL server has
160 been lost.
161 Note that state can be called as either $sth->state or $dbh->state.
163 PostgreSQL servers version less than 7.4 will return a small subset
165 of the available codes, and should not be relied upon.
166 The list of codes used by PostgreSQL can be found at:
168 <http://www.postgresql.org/docs/current/static/errcodes-appen‐
169 dix.html>
170 trace
172 $h->trace($trace_level);
173 $h->trace($trace_level, $trace_filename);
174 Implemented by DBI, no driver-specific impact.
176 trace_msg
178 $h->trace_msg($message_text);
179 $h->trace_msg($message_text, $min_level);
180 Implemented by DBI, no driver-specific impact.
182 func
184 This driver supports a variety of driver specific functions acces‐
185 sible via the "func" method. Note that the name of the function
186 comes last, after the arguments.
187 table_attributes
189 $attrs = $dbh->func($table, 'table_attributes');
190 The "table_attributes" function is no longer recommended.
192 Instead, you can use the more portable "column_info" and "pri‐
193 mary_key" methods to access the same information.
194 The "table_attributes" method returns, for the given table
196 argument, a reference to an array of hashes, each of which con‐
197 tains the following keys:
198 NAME attribute name
200 TYPE attribute type
201 SIZE attribute size (-1 for variable size)
202 NULLABLE flag nullable
203 DEFAULT default value
204 CONSTRAINT constraint
205 PRIMARY_KEY flag is_primary_key
206 REMARKS attribute description
207 The REMARKS field will be returned as "NULL" for Postgres ver‐
209 sions 7.1.x and older.
210 lo_creat
212 $lobjId = $dbh->func($mode, 'lo_creat');
213 Creates a new large object and returns the object-id. $mode is
215 a bitmask describing different attributes of the new object.
216 Use the following constants:
217 $dbh->{pg_INV_WRITE}
219 $dbh->{pg_INV_READ}
220 Upon failure it returns "undef".
222 lo_open
224 $lobj_fd = $dbh->func($lobjId, $mode, 'lo_open');
225 Opens an existing large object and returns an object-descriptor
227 for use in subsequent "lo_*" calls. For the mode bits see
228 "lo_creat". Returns "undef" upon failure. Note that 0 is a per‐
229 fectly correct object descriptor!
230 lo_write
232 $nbytes = $dbh->func($lobj_fd, $buf, $len, 'lo_write');
233 Writes $len bytes of $buf into the large object $lobj_fd.
235 Returns the number of bytes written and "undef" upon failure.
236 lo_read
238 $nbytes = $dbh->func($lobj_fd, $buf, $len, 'lo_read');
239 Reads $len bytes into $buf from large object $lobj_fd. Returns
241 the number of bytes read and "undef" upon failure.
242 lo_lseek
244 $loc = $dbh->func($lobj_fd, $offset, $whence, 'lo_lseek');
245 Changes the current read or write location on the large object
247 $obj_id. Currently $whence can only be 0 ("L_SET"). Returns the
248 current location and "undef" upon failure.
249 lo_tell
251 $loc = $dbh->func($lobj_fd, 'lo_tell');
252 Returns the current read or write location on the large object
254 $lobj_fd and "undef" upon failure.
255 lo_close
257 $lobj_fd = $dbh->func($lobj_fd, 'lo_close');
258 Closes an existing large object. Returns true upon success and
260 false upon failure.
261 lo_unlink
263 $ret = $dbh->func($lobjId, 'lo_unlink');
264 Deletes an existing large object. Returns true upon success and
266 false upon failure.
267 lo_import
269 $lobjId = $dbh->func($filename, 'lo_import');
270 Imports a Unix file as large object and returns the object id
272 of the new object or "undef" upon failure.
273 lo_export
275 $ret = $dbh->func($lobjId, $filename, 'lo_export');
276 Exports a large object into a Unix file. Returns false upon
278 failure, true otherwise.
279 pg_notifies
281 $ret = $dbh->func('pg_notifies');
282 Returns either "undef" or a reference to two-element array [
284 $table, $backend_pid ] of asynchronous notifications received.
285 getfd
287 $fd = $dbh->func('getfd');
288 Returns fd of the actual connection to server. Can be used with
290 select() and func('pg_notifies'). Deprecated in favor of
291 "$dbh->{pg_socket}".
292
294 Warn (boolean, inherited)
295 Implemented by DBI, no driver-specific impact.
296 Active (boolean, read-only)
298 Supported by this driver as proposed by DBI. A database handle is
299 active while it is connected and statement handle is active until
300 it is finished.
301 Executed (boolean, read-only)
303 Implemented by DBI, no driver-specific impact. Requires DBI 1.41 or
304 greater.
305 Kids (integer, read-only)
307 Implemented by DBI, no driver-specific impact.
308 ActiveKids (integer, read-only)
310 Implemented by DBI, no driver-specific impact.
311 CachedKids (hash ref)
313 Implemented by DBI, no driver-specific impact.
314 Type (scalar)
316 Implemented by DBI, no driver-specific impact.
317 ChildHandles (array ref)
319 Implemented by DBI, no driver-specific impact.
320 CompatMode (boolean, inherited)
322 Not used by this driver.
323 InactiveDestroy (boolean)
325 Implemented by DBI, no driver-specific impact. If set to true, then
326 the disconnect() method will not be automatically called when the
327 database handle goes out of scope (e.g. when exiting after a fork).
328 PrintWarn (boolean, inherited)
330 Implemented by DBI, no driver-specific impact.
331 PrintError (boolean, inherited)
333 Implemented by DBI, no driver-specific impact.
334 RaiseError (boolean, inherited)
336 Implemented by DBI, no driver-specific impact.
337 HandleError (boolean, inherited)
339 Implemented by DBI, no driver-specific impact.
340 HandleSetErr (code ref, inherited)
342 Implemented by DBI, no driver-specific impact.
343 ErrCount (unsigned integer)
345 Implemented by DBI, no driver-specific impact.
346 ShowErrorStatement (boolean, inherited)
348 Implemented by DBI, no driver-specific impact.
349 TraceLevel (integer, inherited)
351 Implemented by DBI, no driver-specific impact.
352 FetchHashKeyName (string, inherited)
354 Implemented by DBI, no driver-specific impact.
355 ChopBlanks (boolean, inherited)
357 Supported by this driver as proposed by DBI. This method is similar
358 to the SQL function "RTRIM".
359 LongReadLen (integer, inherited)
361 Not used by this driver.
362 LongTruncOk (boolean, inherited)
364 Not used by this driver.
365 Taint (boolean, inherited)
367 Implemented by DBI, no driver-specific impact.
368 TaintIn (boolean, inherited)
370 Implemented by DBI, no driver-specific impact.
371 TaintOut (boolean, inherited)
373 Implemented by DBI, no driver-specific impact.
374 Profile (inherited)
376 Implemented by DBI, no driver-specific impact.
377
379 Database Handle Methods
380 selectrow_array
382 @row_ary = $dbh->selectrow_array($statement, \%attr, @bind_values);
383 Implemented by DBI, no driver-specific impact.
385 selectrow_arrayref
387 $ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @bind_values);
388 Implemented by DBI, no driver-specific impact.
390 selectrow_hashref
392 $hash_ref = $dbh->selectrow_hashref($statement, \%attr, @bind_values);
393 Implemented by DBI, no driver-specific impact.
395 selectall_arrayref
397 $ary_ref = $dbh->selectall_arrayref($statement, \%attr, @bind_values);
398 Implemented by DBI, no driver-specific impact.
400 selectall_hashref
402 $hash_ref = $dbh->selectall_hashref($statement, $key_field);
403 Implemented by DBI, no driver-specific impact.
405 selectcol_arrayref
407 $ary_ref = $dbh->selectcol_arrayref($statement, \%attr, @bind_values);
408 Implemented by DBI, no driver-specific impact.
410 prepare
412 $sth = $dbh->prepare($statement, \%attr);
413 WARNING: DBD::Pg now uses true prepared statements by sending them
415 to the backend to be prepared by the PostgreSQL server. Statements
416 that were legal before may no longer work. See below for details.
417 Prepares a statement for later execution. PostgreSQL supports pre‐
419 pared statements, which enables DBD::Pg to only send the query
420 once, and simply send the arguments for every subsequent call to
421 execute(). DBD::Pg can use these server-side prepared statements,
422 or it can just send the entire query to the server each time. The
423 best way is automatically chosen for each query. This will be suf‐
424 ficient for most users: keep reading for a more detailed explana‐
425 tion and some optional flags.
426 Statements that do not begin with the word "SELECT", "INSERT",
428 "UPDATE", or "DELETE" will not be sent to be server-side prepared.
429 Deciding whether or not to use prepared statements depends on many
431 factors, but you can force them to be used or not used by passing
432 the "pg_server_prepare" attribute to prepare(). A "0" means to
433 never use prepared statements. This is the default when connected
434 to servers earlier than version 7.4, which is when prepared state‐
435 ments were introduced. Setting "pg_server_prepare" to "1" means
436 that prepared statements should be used whenever possible. This is
437 the default for servers version 8.0 or higher. Servers that are
438 version 7.4 get a special default value of "2", because server-side
439 statements were only partially supported in that version. In this
440 case, it only uses server-side prepares if all parameters are
441 specifically bound.
442 The pg_server_prepare attribute can also be set at connection time
444 like so:
445 $dbh = DBI->connect($DBNAME, $DBUSER, $DBPASS,
447 { AutoCommit => 0,
448 RaiseError => 1,
449 pg_server_prepare => 0 });
450 or you may set it after your database handle is created:
452 $dbh->{pg_server_prepare} = 1;
454 To enable it for just one particular statement:
456 $sth = $dbh->prepare("SELECT id FROM mytable WHERE val = ?",
458 { pg_server_prepare => 1 });
459 You can even toggle between the two as you go:
461 $sth->{pg_server_prepare} = 1;
463 $sth->execute(22);
464 $sth->{pg_server_prepare} = 0;
465 $sth->execute(44);
466 $sth->{pg_server_prepare} = 1;
467 $sth->execute(66);
468 In the above example, the first execute will use the previously
470 prepared statement. The second execute will not, but will build
471 the query into a single string and send it to the server. The third
472 one will act like the first and only send the arguments. Even if
473 you toggle back and forth, a statement is only prepared once.
474 Using prepared statements is in theory quite a bit faster: not only
476 does the PostgreSQL backend only have to prepare the query only
477 once, but DBD::Pg no longer has to worry about quoting each value
478 before sending it to the server.
479 However, there are some drawbacks. The server cannot always choose
481 the ideal parse plan because it will not know the arguments before
482 hand. But for most situations in which you will be executing simi‐
483 lar data many times, the default plan will probably work out well.
484 Further discussion on this subject is beyond the scope of this doc‐
485 umentation: please consult the pgsql-performance mailing list,
486 <http://archives.postgresql.org/pgsql-performance/>
487 Only certain commands will be sent to a server-side prepare: cur‐
489 rently these include "SELECT", "INSERT", "UPDATE", and "DELETE".
490 DBD::Pg uses a simple naming scheme for the prepared statements:
491 "dbdpg_#", where "#" starts at 1 and increases. This number is
492 tracked at the database handle level, so multiple statement handles
493 will not collide. If you use your own prepare statements, do not
494 name them "dbdpg_"!
495 You cannot send more than one command at a time in the same prepare
497 command, by separating them with semi-colons, when using server-
498 side prepares.
499 The actual "PREPARE" is not performed until the first execute is
501 called, due to the fact that information on the data types (pro‐
502 vided by "bind_param") may be given after the prepare but before
503 the execute.
504 A server-side prepare can also happen before the first execute. If
506 the server can handle the server-side prepare and the statement has
507 no placeholders, it will be prepared right away. It will also be
508 prepared if the "pg_prepare_now" attribute is passed. Similarly,
509 the <pg_prepare_now> attribute can be set to 0 to ensure that the
510 statement is not prepared immediately, although cases in which you
511 would want this may be rare. Finally, you can set the default
512 behavior of all prepare statements by setting the "pg_prepare_now"
513 attribute on the database handle:
514 $dbh->{pg_prepare_now} = 1;
516 The following two examples will be prepared right away:
518 $sth->prepare("SELECT 123"); ## no placeholders
520 $sth->prepare("SELECT 123, ?", {pg_prepare_now => 1});
522 The following two examples will NOT be prepared right away:
524 $sth->prepare("SELECT 123, ?"); ## has a placeholder
526 $sth->prepare("SELECT 123", {pg_prepare_now => 0});
528 There are times when you may want to prepare a statement yourself.
530 To do this, simply send the "PREPARE" statement directly to the
531 server (e.g. with "do"). Create a statement handle and set the pre‐
532 pared name via "pg_prepare_name" attribute. The statement handle
533 can be created with a dummy statement, as it will not be executed.
534 However, it should have the same number of placeholders as your
535 prepared statement. Example:
536 $dbh->do("PREPARE mystat AS SELECT COUNT(*) FROM pg_class WHERE reltuples < ?");
538 $sth = $dbh->prepare("SELECT ?");
539 $sth->bind_param(1, 1, SQL_INTEGER);
540 $sth->{pg_prepare_name} = "mystat";
541 $sth->execute(123);
542 The above will run this query:
544 SELECT COUNT(*) FROM pg_class WHERE reltuples < 123;
546 Note: DBD::Pg will not escape your custom prepared statement name,
548 so don't use a name that needs escaping! DBD::Pg uses the prepare
549 names "dbdpg_#" internally, so please do not use those either.
550 You can force DBD::Pg to send your query directly to the server by
552 adding the "pg_direct" attribute to your prepare call. This is not
553 recommended, but is added just in case you need it.
554 Placeholders
556 There are three types of placeholders that can be used in DBD::Pg.
557 The first is the question mark method, in which each placeholder is
558 represented by a single question mark. This is the method recom‐
559 mended by the DBI specs and is the most portable. Each question
560 mark is replaced by a "dollar sign number" in the order in which
561 they appear in the query (important when using "bind_param").
562 The second method is to use "dollar sign numbers" directly. This is
564 the method that PostgreSQL uses internally and is overall probably
565 the best method to use if you do not need compatibility with other
566 database systems. DBD::Pg, like PostgreSQL, allows the same number
567 to be used more than once in the query. Numbers must start with
568 "1" and increment by one value. If the same number appears more
569 than once in a query, it is treated as a single parameter and all
570 instances are replaced at once. Examples:
571 Not legal:
573 $SQL = "SELECT count(*) FROM pg_class WHERE relpages > $2";
575 $SQL = "SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $3";
577 Legal:
579 $SQL = "SELECT count(*) FROM pg_class WHERE relpages > $1";
581 $SQL = "SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $2";
583 $SQL = "SELECT count(*) FROM pg_class WHERE relpages BETWEEN $1 AND $2 AND reltuples > $1";
585 $SQL = "SELECT count(*) FROM pg_class WHERE relpages > $1 AND reltuples > $1";
587 In the final statement above, DBI thinks there is only one place‐
589 holder, so this statement will replace both placeholders:
590 $sth->bind_param(1, 2045);
592 While execute requires only a single argument as well:
594 $sth->execute(2045);
596 The final placeholder method is the named parameters in the format
598 ":foo". While this syntax is supported by DBD::Pg, its use is
599 highly discouraged.
600 The different types of placeholders cannot be mixed within a state‐
602 ment, but you may use different ones for each statement handle you
603 have. Again, this is not encouraged.
604 prepare_cached
606 $sth = $dbh->prepare_cached($statement, \%attr);
607 Implemented by DBI, no driver-specific impact. This method is most
609 useful when using a server that supports server-side prepares, and
610 you have asked the prepare to happen immediately via the "pg_pre‐
611 pare_now" attribute.
612 do
614 $rv = $dbh->do($statement, \%attr, @bind_values);
615 Prepare and execute a single statement. Note that an empty state‐
617 ment (string with no length) will not be passed to the server; if
618 you want a simple test, use "SELECT 123" or the ping() function. If
619 neither attr nor bind_values is given, the query will be sent
620 directly to the server without the overhead of creating a statement
621 handle and running prepare and execute.
622 last_insert_id
624 $rv = $dbh->last_insert_id($catalog, $schema, $table, $field);
625 $rv = $dbh->last_insert_id($catalog, $schema, $table, $field, \%attr);
626 Attempts to return the id of the last value to be inserted into a
628 table. You can either provide a sequence name (preferred) or pro‐
629 vide a table name with optional schema. The $catalog and $field
630 arguments are always ignored. The current value of the sequence is
631 returned by a call to the "CURRVAL()" PostgreSQL function. This
632 will fail if the sequence has not yet been used in the current
633 database connection.
634 If you do not know the name of the sequence, you can provide a ta‐
636 ble name and DBD::Pg will attempt to return the correct value. To
637 do this, there must be at least one column in the table with a "NOT
638 NULL" constraint, that has a unique constraint, and which uses a
639 sequence as a default value. If more than one column meets these
640 conditions, the primary key will be used. This involves some look‐
641 ing up of things in the system table, so DBD::Pg will cache the
642 sequence name for susequent calls. If you need to disable this
643 caching for some reason, you can control it via the "pg_cache"
644 attribute.
645 Please keep in mind that this method is far from foolproof, so make
647 your script use it properly. Specifically, make sure that it is
648 called immediately after the insert, and that the insert does not
649 add a value to the column that is using the sequence as a default
650 value.
651 Some examples:
653 $dbh->do("CREATE SEQUENCE lii_seq START 1");
655 $dbh->do("CREATE TABLE lii (
656 foobar INTEGER NOT NULL UNIQUE DEFAULT nextval('lii_seq'),
657 baz VARCHAR)");
658 $SQL = "INSERT INTO lii(baz) VALUES (?)";
659 $sth = $dbh->prepare($SQL);
660 for (qw(uno dos tres cuatro)) {
661 $sth->execute($_);
662 my $newid = $dbh->last_insert_id(C<undef>,undef,undef,undef,{sequence=>'lii_seq'});
663 print "Last insert id was $newid\n";
664 }
665 If you did not want to worry about the sequence name:
667 $dbh->do("CREATE TABLE lii2 (
669 foobar SERIAL UNIQUE,
670 baz VARCHAR)");
671 $SQL = "INSERT INTO lii2(baz) VALUES (?)";
672 $sth = $dbh->prepare($SQL);
673 for (qw(uno dos tres cuatro)) {
674 $sth->execute($_);
675 my $newid = $dbh->last_insert_id(undef,undef,"lii2",undef);
676 print "Last insert id was $newid\n";
677 }
678 commit
680 $rc = $dbh->commit;
681 Supported by this driver as proposed by DBI. See also the notes
683 about Transactions elsewhere in this document.
684 rollback
686 $rc = $dbh->rollback;
687 Supported by this driver as proposed by DBI. See also the notes
689 about Transactions elsewhere in this document.
690 begin_work
692 Supported by this driver as proposed by DBI. Note that this will
693 not issue a "begin" until immediately before the next given com‐
694 mand.
695 disconnect
697 $rc = $dbh->disconnect;
698 Supported by this driver as proposed by DBI.
700 ping
702 $rc = $dbh->ping;
703 This driver supports the "ping" method, which can be used to check
705 the validity of a database handle. The value returned is either 0,
706 indicating that the connection is no longer valid, or a positive
707 integer, indicating the following:
708 Value Meaning
710 --------------------------------------------------
711 1 Database is idle (not in a transaction)
712 2 Database is active, there is a command in progress (usually seen after a COPY command)
713 3 Database is idle within a transaction
714 4 Database is idle, within a failed transaction
715 Additional information on why a handle is not valid can be obtained
717 by using the "pg_ping" method.
718 pg_ping
720 $rc = $dbh->pg_ping;
721 This is a DBD::Pg-specific extension to the "ping" command. This
723 will check the validity of a database handle in exactly the same
724 way as "ping", but instead of returning a 0 for an invalid connec‐
725 tion, it will return a negative number. So in addition to returning
726 the positive numbers documented for "ping", it may also return the
727 following:
728 Value Meaning
730 --------------------------------------------------
731 -1 There is no connection to the database at all (e.g. after C<disconnect>)
732 -2 An unknown transaction status was returned (e.g. after forking)
733 -3 The handle exists, but no data was returned from a test query.
734 In practice, you should only ever see -1 and -2.
736 get_info
738 $value = $dbh->get_info($info_type);
739 Supports a very large set (> 250) of the information types, includ‐
741 ing the minimum recommended by DBI.
742 table_info
744 $sth = $dbh->table_info( $catalog, $schema, $table, $type );
745 Supported by this driver as proposed by DBI. This method returns
747 all tables and views visible to the current user. The $catalog
748 argument is currently unused. The schema and table arguments will
749 do a "LIKE" search if a percent sign ("%") or an underscore ("_")
750 is detected in the argument. The $type argument accepts a value of
751 either "TABLE" or "VIEW" (using both is the default action).
752 The TABLE_CAT field will always return NULL ("undef"). The TA‐
754 BLE_SCHEM field returns NULL ("undef") if the server is older than
755 version 7.4.
756 If your database supports tablespaces (version 8.0 or greater), two
758 additional columns are returned, "pg_tablespace_name" and
759 "pg_tablespace_location", that contain the name and location of the
760 tablespace associated with this table. Tables that have not been
761 assigned to a particular tablespace will return NULL ("undef") for
762 both of these columns.
763 column_info
765 $sth = $dbh->column_info( $catalog, $schema, $table, $column );
766 Supported by this driver as proposed by DBI with the follow excep‐
768 tions. These fields are currently always returned with NULL
769 ("undef") values:
770 TABLE_CAT
772 BUFFER_LENGTH
773 DECIMAL_DIGITS
774 NUM_PREC_RADIX
775 SQL_DATA_TYPE
776 SQL_DATETIME_SUB
777 CHAR_OCTET_LENGTH
778 Also, two additional non-standard fields are returned:
780 pg_type - data type with additional info i.e. "character varying(20)"
782 pg_constraint - holds column constraint definition
783 The REMARKS field will be returned as NULL ("undef" for PostgreSQL
785 versions older than 7.2. The TABLE_SCHEM field will be returned as
786 NULL ("undef") for versions older than 7.4.
787 primary_key_info
789 $sth = $dbh->primary_key_info( $catalog, $schema, $table, \%attr );
790 Supported by this driver as proposed by DBI. The $catalog argument
792 is currently unused, and the $schema argument has no effect against
793 servers running version 7.2 or older. There are no search patterns
794 allowed, but leaving the $schema argument blank will cause the
795 first table found in the schema search path to be used. An addi‐
796 tional field, "DATA_TYPE", is returned and shows the data type for
797 each of the arguments in the "COLUMN_NAME" field.
798 This method will also return tablespace information for servers
800 that support tablespaces. See the "table_info" entry for more
801 information.
802 In addition to the standard format of returning one row for each
804 column found for the primary key, you can pass the "pg_onerow"
805 attribute to force a single row to be used. If the primary key has
806 multiple columns, the "KEY_SEQ", "COLUMN_NAME", and "DATA_TYPE"
807 fields will return a comma-delimited string. If the "pg_onerow"
808 attribute is set to "2", the fields will be returned as an
809 arrayref, which can be useful when multiple columns are involved:
810 $sth = $dbh->primary_key_info('', '', 'dbd_pg_test', {pg_onerow => 2});
812 if (defined $sth) {
813 my $pk = $sth->fetchall_arrayref()->[0];
814 print "Table $pk->[2] has a primary key on these columns:\n";
815 for (my $x=0; defined $pk->[3][$x]; $x++) {
816 print "Column: $pk->[3][$x] (data type: $pk->[6][$x])\n";
817 }
818 }
819 primary_key
821 Supported by this driver as proposed by DBI.
822 foreign_key_info
824 $sth = $dbh->foreign_key_info( $pk_catalog, $pk_schema, $pk_table,
825 $fk_catalog, $fk_schema, $fk_table );
826 Supported by this driver as proposed by DBI, using the SQL/CLI
828 variant. This function returns "undef" for PostgreSQL servers ear‐
829 lier than version 7.3. There are no search patterns allowed, but
830 leaving the $schema argument blank will cause the first table found
831 in the schema search path to be used. Two additional fields,
832 "UK_DATA_TYPE" and "FK_DATA_TYPE", are returned to show the data
833 type for the unique and foreign key columns. Foreign keys that have
834 no named constraint (where the referenced column only has an unique
835 index) will return "undef" for the "UK_NAME" field.
836 tables
838 @names = $dbh->tables( $catalog, $schema, $table, $type, \%attr );
839 Supported by this driver as proposed by DBI. This method returns
841 all tables and/or views which are visible to the current user: see
842 "table_info()" for more information about the arguments. If the
843 database is version 7.3 or later, the name of the schema appears
844 before the table or view name. This can be turned off by adding in
845 the "pg_noprefix" attribute:
846 my @tables = $dbh->tables( '', '', 'dbd_pg_test', '', {pg_noprefix => 1} );
848 type_info_all
850 $type_info_all = $dbh->type_info_all;
851 Supported by this driver as proposed by DBI. Information is only
853 provided for SQL datatypes and for frequently used datatypes. The
854 mapping between the PostgreSQL typename and the SQL92 datatype (if
855 possible) has been done according to the following table:
856 +---------------+------------------------------------+
858 ⎪ typname ⎪ SQL92 ⎪
859 ⎪---------------+------------------------------------⎪
860 ⎪ bool ⎪ BOOL ⎪
861 ⎪ text ⎪ / ⎪
862 ⎪ bpchar ⎪ CHAR(n) ⎪
863 ⎪ varchar ⎪ VARCHAR(n) ⎪
864 ⎪ int2 ⎪ SMALLINT ⎪
865 ⎪ int4 ⎪ INT ⎪
866 ⎪ int8 ⎪ / ⎪
867 ⎪ money ⎪ / ⎪
868 ⎪ float4 ⎪ FLOAT(p) p<7=float4, p<16=float8 ⎪
869 ⎪ float8 ⎪ REAL ⎪
870 ⎪ abstime ⎪ / ⎪
871 ⎪ reltime ⎪ / ⎪
872 ⎪ tinterval ⎪ / ⎪
873 ⎪ date ⎪ / ⎪
874 ⎪ time ⎪ / ⎪
875 ⎪ datetime ⎪ / ⎪
876 ⎪ timespan ⎪ TINTERVAL ⎪
877 ⎪ timestamp ⎪ TIMESTAMP ⎪
878 +---------------+------------------------------------+
879 For further details concerning the PostgreSQL specific datatypes
881 please read pgbuiltin.
882 type_info
884 @type_info = $dbh->type_info($data_type);
885 Implemented by DBI, no driver-specific impact.
887 quote
889 $sql = $dbh->quote($value, $data_type);
890 This module implements its own "quote" method. In addition to the
892 DBI method it also doubles the backslash, because PostgreSQL treats
893 a backslash as an escape character.
894 NOTE: The undocumented (and invalid) support for the "SQL_BINARY"
896 data type is officially deprecated. Use "PG_BYTEA" with
897 "bind_param()" instead:
898 $rv = $sth->bind_param($param_num, $bind_value,
900 { pg_type => DBD::Pg::PG_BYTEA });
901 quote_identifier
903 Implemented by DBI, no driver-specific impact.
904 pg_server_trace
906 $dbh->pg_server_trace($filehandle);
907 Writes debugging information from the PostgreSQL backend to a file.
909 This is not the same as the trace() method and you should not use
910 this method unless you know what you are doing. If you do enable
911 this, be aware that the file will grow very large, very quick. To
912 stop logging to the file, use the "pg_server_untrace" function. The
913 first argument must be a file handle, not a filename. Example:
914 my $pid = $dbh->{pg_pid};
916 my $file = "pgbackend.$pid.debug.log";
917 open(my $fh, ">$file") or die qq{Could not open "$file": $!\n};
918 $dbh->pg_server_trace($fh);
919 ## Run code you want to trace here
920 $dbh->pg_server_untrace;
921 close($fh);
922 pg_server_untrace
924 $dbh->pg_server_untrace
925 Stop server logging to a previously opened file.
927 Database Handle Attributes
929 AutoCommit (boolean)
931 Supported by this driver as proposed by DBI. According to the clas‐
932 sification of DBI, PostgreSQL is a database in which a transaction
933 must be explicitly started. Without starting a transaction, every
934 change to the database becomes immediately permanent. The default
935 of AutoCommit is on, but this may change in the future, so it is
936 highly recommended that you explicitly set it when calling "con‐
937 nect()". For details see the notes about Transactions elsewhere in
938 this document.
939 pg_bool_tf (boolean)
941 PostgreSQL specific attribute. If true, boolean values will be
942 returned as the characters 't' and 'f' instead of '1' and '0'.
943 Driver (handle)
945 Implemented by DBI, no driver-specific impact.
946 Name (string, read-only)
948 The default DBI method is overridden by a driver specific method
949 that returns only the database name. Anything else from the connec‐
950 tion string is stripped off. Note that, in contrast to the DBI
951 specs, the DBD::Pg implementation fo this method is read-only.
952 RowCacheSize (integer)
954 Implemented by DBI, not used by this driver.
955 Username (string, read-only)
957 Supported by this driver as proposed by DBI.
958 pg_auto_escape (boolean)
960 PostgreSQL specific attribute. If true, then quotes and backslashes
961 in all parameters will be escaped in the following way:
962 escape quote with a quote (SQL)
964 escape backslash with a backslash
965 The default is on. Note that PostgreSQL also accepts quotes that
967 are escaped by a backslash. Any other ASCII character can be used
968 directly in a string constant.
969 pg_enable_utf8 (boolean)
971 PostgreSQL specific attribute. If true, then the "utf8" flag will
972 be turned for returned character data (if the data is valid UTF-8).
973 For details about the "utf8" flag, see Encode. This attribute is
974 only relevant under perl 5.8 and later.
975 NB: This attribute is experimental and may be subject to change.
977 pg_INV_READ (integer, read-only)
979 Constant to be used for the mode in "lo_creat" and "lo_open".
980 pg_INV_WRITE (integer, read-only)
982 Constant to be used for the mode in "lo_creat" and "lo_open".
983 pg_errorlevel (integer)
985 PostgreSQL specific attribute, only works for servers version 7.4
986 and above. Sets the amount of information returned by the server's
987 error messages. Valid entries are 0, 1, and 2. Any other number
988 will be forced to the default value of 1.
989 A value of 0 ("TERSE") will show severity, primary text, and posi‐
991 tion only and will usually fit on a single line. A value of 1
992 ("DEFAULT") will also show any detail, hint, or context fields. A
993 value of 2 ("VERBOSE") will show all available information.
994 pg_protocol (integer, read-only)
996 PostgreSQL specific attribute. Returns the version of the Post‐
997 greSQL server. If DBD::Pg is unable to figure out the version
998 (e.g. it was compiled against pre 7.4 libraries), it will return a
999 "0". Otherwise, servers below version 7.4 return a "2", and (cur‐
1000 rently) 7.4 and above return a "3".
1001 pg_lib_version (integer, read-only)
1003 PostgreSQL specific attribute. Indicates which version of Post‐
1004 greSQL that DBD::Pg was compiled against. In other words, which
1005 libraries were used. Returns a number with major, minor, and revi‐
1006 sion together; version 7.4.2 would be returned as 70402.
1007 pg_server_version (integer, read-only)
1009 PostgreSQL specific attribute. Indicates which version of Post‐
1010 greSQL that the current database handle is connected to. Returns a
1011 number with major, minor, and revision together; version 8.0.1
1012 would be 80001.
1013 pg_db (string, read-only)
1015 PostgreSQL specific attribute. Returns the name of the current
1016 database.
1017 pg_user (string, read-only)
1019 PostgreSQL specific attribute. Returns the name of the user that
1020 connected to the server.
1021 pg_pass (string, read-only)
1023 PostgreSQL specific attribute. Returns the password used to connect
1024 to the server.
1025 pg_host (string, read-only)
1027 PostgreSQL specific attribute. Returns the host of the current
1028 server connection. Locally connected hosts will return an empty
1029 string.
1030 pg_port (integer, read-only)
1032 PostgreSQL specific attribute. Returns the port of the connection
1033 to the server.
1034 pg_default_port (integer, read-only)
1036 PostgreSQL specific attribute. Returns the default port used if
1037 none is specifically givem.
1038 pg_options (string, read-only)
1040 PostgreSQL specific attribute. Returns the command-line options
1041 passed to the server. May be an empty string.
1042 pg_socket (number, read-only)
1044 PostgreSQL specific attribute. Returns the file description number
1045 of the connection socket to the server.
1046 pg_pid (number, read-only)
1048 PostgreSQL specific attribute. Returns the process id (PID) of the
1049 backend server process handling the connection.
1050
1052 Statement Handle Methods
1053 bind_param
1055 $rv = $sth->bind_param($param_num, $bind_value, \%attr);
1056 Allows the user to bind a value and/or a data type to a place‐
1058 holder. This is especially important when using the new server-side
1059 prepare system with PostgreSQL 7.4. See the "prepare()" method for
1060 more information.
1061 The value of $param_num is a number if using the '?' or '$1' style
1063 placeholders. If using ":foo" style placeholders, the complete name
1064 (e.g. ":foo") must be given. For numeric values, you can either use
1065 a number or use a literal '$1'. See the examples below.
1066 The $bind_value argument is fairly self-explanatory. A value of
1068 "undef" will bind a "NULL" to the placeholder. Using "undef" is
1069 useful when you want to change just the type and will be overwrit‐
1070 ing the value later. (Any value is actually usable, but "undef" is
1071 easy and efficient).
1072 The %attr hash is used to indicate the data type of the place‐
1074 holder. The default value is "varchar". If you need something
1075 else, you must use one of the values provided by DBI or by DBD::Pg.
1076 To use a SQL value, modify your "use DBI" statement at the top of
1077 your script as follows:
1078 use DBI qw(:sql_types);
1080 This will import some constants into your script. You can plug
1082 those directly into the "bind_param" call. Some common ones that
1083 you will encounter are:
1084 SQL_INTEGER
1086 To use PostgreSQL data types, import the list of values like this:
1088 use DBD::Pg qw(:pg_types);
1090 You can then set the data types by setting the value of the
1092 "pg_type" key in the hash passed to "bind_param".
1093 Data types are "sticky," in that once a data type is set to a cer‐
1095 tain placeholder, it will remain for that placeholder, unless it is
1096 explicitly set to something else afterwards. If the statement has
1097 already been prepared, and you switch the data type to something
1098 else, DBD::Pg will re-prepare the statement for you before doing
1099 the next execute.
1100 Examples:
1102 use DBI qw(:sql_types);
1104 use DBD::Pg qw(:pg_types);
1105 $SQL = "SELECT id FROM ptable WHERE size > ? AND title = ?";
1107 $sth = $dbh->prepare($SQL);
1108 ## Both arguments below are bound to placeholders as "varchar"
1110 $sth->execute(123, "Merk");
1111 ## Reset the datatype for the first placeholder to an integer
1113 $sth->bind_param(1, undef, SQL_INTEGER);
1114 ## The "undef" bound above is not used, since we supply params to execute
1116 $sth->execute(123, "Merk");
1117 ## Set the first placeholder's value and data type
1119 $sth->bind_param(1, 234, { pg_type => PG_TIMESTAMP });
1120 ## Set the second placeholder's value and data type.
1122 ## We don't send a third argument, so the default "varchar" is used
1123 $sth->bind_param("$2", "Zool");
1124 ## We realize that the wrong data type was set above, so we change it:
1126 $sth->bind_param("$1", 234, { pg_type => PG_INTEGER });
1127 ## We also got the wrong value, so we change that as well.
1129 ## Because the data type is sticky, we don't need to change it
1130 $sth->bind_param(1, 567);
1131 ## This executes the statement with 567 (integer) and "Zool" (varchar)
1133 $sth->execute();
1134 bind_param_inout
1136 Currently not supported by this driver.
1137 bind_param_array
1139 Supported by this driver as proposed by DBI.
1140 execute
1142 $rv = $sth->execute(@bind_values);
1143 Executes a previously prepared statement. In addition to "UPDATE",
1145 "DELETE", "INSERT" statements, for which it returns always the num‐
1146 ber of affected rows, the "execute" method can also be used for
1147 "SELECT ... INTO table" statements.
1148 The "prepare/bind/execute" process has changed significantly for
1150 PostgreSQL servers 7.4 and later: please see the "prepare()" and
1151 "bind_param()" entries for much more information.
1152 Setting one of the bind_values to "undef" is the equivalent of set‐
1154 ting the value to NULL in the database. Setting the bind_value to
1155 $DBDPG_DEFAULT is equivalent to sending the literal string
1156 'DEFAULT' to the backend. Note that using this option will force
1157 server-side prepares off until such time as PostgreSQL supports
1158 using DEFAULT in prepared statements.
1159 execute_array
1161 Supported by this driver as proposed by DBI.
1162 execute_for_fetch
1164 Supported by this driver as proposed by DBI.
1165 fetchrow_arrayref
1167 $ary_ref = $sth->fetchrow_arrayref;
1168 Supported by this driver as proposed by DBI.
1170 fetchrow_array
1172 @ary = $sth->fetchrow_array;
1173 Supported by this driver as proposed by DBI.
1175 fetchrow_hashref
1177 $hash_ref = $sth->fetchrow_hashref;
1178 Supported by this driver as proposed by DBI.
1180 fetchall_arrayref
1182 $tbl_ary_ref = $sth->fetchall_arrayref;
1183 Implemented by DBI, no driver-specific impact.
1185 finish
1187 $rc = $sth->finish;
1188 Supported by this driver as proposed by DBI.
1190 rows
1192 $rv = $sth->rows;
1193 Supported by this driver as proposed by DBI. In contrast to many
1195 other drivers the number of rows is available immediately after
1196 executing the statement.
1197 bind_col
1199 $rc = $sth->bind_col($column_number, \$var_to_bind, \%attr);
1200 Supported by this driver as proposed by DBI.
1202 bind_columns
1204 $rc = $sth->bind_columns(\%attr, @list_of_refs_to_vars_to_bind);
1205 Supported by this driver as proposed by DBI.
1207 dump_results
1209 $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
1210 Implemented by DBI, no driver-specific impact.
1212 blob_read
1214 $blob = $sth->blob_read($id, $offset, $len);
1215 Supported by this driver as proposed by DBI. Implemented by DBI but
1217 not documented, so this method might change.
1218 This method seems to be heavily influenced by the current implemen‐
1220 tation of blobs in Oracle. Nevertheless we try to be as compatible
1221 as possible. Whereas Oracle suffers from the limitation that blobs
1222 are related to tables and every table can have only one blob
1223 (datatype LONG), PostgreSQL handles its blobs independent of any
1224 table by using so-called object identifiers. This explains why the
1225 "blob_read" method is blessed into the STATEMENT package and not
1226 part of the DATABASE package. Here the field parameter has been
1227 used to handle this object identifier. The offset and len parame‐
1228 ters may be set to zero, in which case the driver fetches the whole
1229 blob at once.
1230 Starting with PostgreSQL 6.5, every access to a blob has to be put
1232 into a transaction. This holds even for a read-only access.
1233 See also the PostgreSQL-specific functions concerning blobs, which
1235 are available via the "func" interface.
1236 For further information and examples about blobs, please read the
1238 chapter about Large Objects in the PostgreSQL Programmer's Guide at
1239 <http://www.postgresql.org/docs/current/static/largeobjects.html>.
1240 Statement Handle Attributes
1242 NUM_OF_FIELDS (integer, read-only)
1244 Implemented by DBI, no driver-specific impact.
1245 NUM_OF_PARAMS (integer, read-only)
1247 Implemented by DBI, no driver-specific impact.
1248 NAME (array-ref, read-only)
1250 Supported by this driver as proposed by DBI.
1251 NAME_lc (array-ref, read-only)
1253 Implemented by DBI, no driver-specific impact.
1254 NAME_uc (array-ref, read-only)
1256 Implemented by DBI, no driver-specific impact.
1257 NAME_hash (hash-ref, read-only)
1259 Implemented by DBI, no driver-specific impact.
1260 NAME_lc_hash (hash-ref, read-only)
1262 Implemented by DBI, no driver-specific impact.
1263 NAME_uc_hash (hash-ref, read-only)
1265 Implemented by DBI, no driver-specific impact.
1266 TYPE (array-ref, read-only)
1268 Supported by this driver as proposed by DBI
1269 PRECISION (array-ref, read-only)
1271 Supported by this driver. "NUMERIC" types will return the preci‐
1272 sion. Types of "CHAR" and "VARCHAR" will return their size (number
1273 of characters). Other types will return the number of bytes.
1274 SCALE (array-ref, read-only)
1276 Supported by this driver as proposed by DBI. The only type that
1277 will return a value currently is "NUMERIC".
1278 NULLABLE (array-ref, read-only)
1280 Supported by this driver as proposed by DBI. This is only available
1281 for servers version 7.3 and later. Others will return "2" for all
1282 columns.
1283 CursorName (string, read-only)
1285 Not supported by this driver. See the note about Cursors elsewhere
1286 in this document.
1287 "Database" (dbh, read-only)
1289 Implemented by DBI, no driver-specific impact.
1290 "ParamValues" (hash ref, read-only)
1292 Supported by this driver as proposed by DBI. If called before "exe‐
1293 cute", the literal values passed in are returned. If called after
1294 "execute", then the quoted versions of the values are shown.
1295 "ParamTypes" (hash ref, read-only)
1297 Returns a hash of all current placeholders. The keys are the names
1298 of the placeholders, and the values are the types that have been
1299 bound to each one. Placeholders that have not yet been bound will
1300 return undef as the value.
1301 Statement (string, read-only)
1303 Supported by this driver as proposed by DBI.
1304 RowCache (integer, read-only)
1306 Not supported by this driver.
1307 pg_size (array-ref, read-only)
1309 PostgreSQL specific attribute. It returns a reference to an array
1310 of integer values for each column. The integer shows the size of
1311 the column in bytes. Variable length columns are indicated by -1.
1312 pg_type (array-ref, read-only)
1314 PostgreSQL specific attribute. It returns a reference to an array
1315 of strings for each column. The string shows the name of the
1316 data_type.
1317 pg_oid_status (integer, read-only)
1319 PostgreSQL specific attribute. It returns the OID of the last
1320 INSERT command.
1321 pg_cmd_status (integer, read-only)
1323 PostgreSQL specific attribute. It returns the type of the last com‐
1324 mand. Possible types are: "INSERT", "DELETE", "UPDATE", "SELECT".
1325
1327 Transactions
1328 Transaction behavior is controlled via the "AutoCommit" attribute. For
1330 a complete definition of "AutoCommit" please refer to the DBI documen‐
1331 tation.
1332 According to the DBI specification the default for "AutoCommit" is a
1334 true value. In this mode, any change to the database becomes valid
1335 immediately. Any "BEGIN", "COMMIT" or "ROLLBACK" statements will be
1336 rejected. DBD::Pg implements "AutoCommit" by issuing a "BEGIN" state‐
1337 ment immediately before executing a statement, and a "COMMIT" after‐
1338 wards.
1339 Savepoints
1341 PostgreSQL version 8.0 introduced the concept of savepoints, which
1343 allows transactions to be rolled back to a certain point without
1344 affecting the rest of the transaction. DBD::Pg encourages using the
1345 following methods to control savepoints:
1346 pg_savepoint
1348 Creates a savepoint. This will fail unless you are inside of a
1349 transaction. The only argument is the name of the savepoint. Note
1350 that PostgreSQL DOES allow multiple savepoints with the same name
1351 to exist.
1352 $dbh->pg_savepoint("mysavepoint");
1354 pg_rollback_to
1356 Rolls the database back to a named savepoint, discarding any work
1357 performed after that point. If more than one savepoint with that
1358 name exists, rolls back to the most recently created one.
1359 $dbh->pg_rollback_to("mysavepoint");
1361 pg_release
1363 Releases (or removes) a named savepoint. If more than one savepoint
1364 with that name exists, it will only destroy the most recently cre‐
1365 ated one. Note that all savepoints created after the one being
1366 released are also destroyed.
1367 $dbh->pg_release("mysavepoint");
1369 COPY support
1371 DBD::Pg supports the COPY command through three functions: pg_putline,
1373 pg_getline, and pg_endcopy. The COPY command allows data to be quickly
1374 loaded or read from a table. The basic process is to issue a COPY com‐
1375 mand via $dbh->do(), do either $dbh->pg_putline or $dbh->pg_getline,
1376 and then issue a $dbh->pg_endcopy (for pg_putline only).
1377 The first step is to put the server into "COPY" mode. This is done by
1379 sending a complete COPY command to the server, by using the do()
1380 method. For example:
1381 $dbh->do("COPY foobar FROM STDIN");
1383 This would tell the server to enter a COPY IN state. It is now ready to
1385 receive information via the pg_putline method. The complete syntax of
1386 the COPY command is more complex and not documented here: the canonical
1387 PostgreSQL documentation for COPY be found at:
1388 http://www.postgresql.org/docs/current/static/sql-copy.html
1390 Note that 7.2 servers can only accept a small subset of later features
1392 in the COPY command: most notably they do not accept column specifica‐
1393 tions.
1394 Once the COPY command has been issued, no other SQL commands are
1396 allowed until after pg_endcopy has been successfully called. If in a
1397 COPY IN state, you cannot use pg_getline, and if in COPY OUT state, you
1398 cannot use pg_putline.
1399 pg_putline
1401 Used to put data into a table after the server has been put into
1402 COPY IN mode by calling "COPY tablename FROM STDIN". The only argu‐
1403 ment is the data you want inserted. The default delimiter is a tab
1404 character, but this can be changed in the COPY statement. Returns a
1405 1 on sucessful input. Examples:
1406 $dbh->do("COPY mytable FROM STDIN");
1408 $dbh->pg_putline("123\tPepperoni\t3\n");
1409 $dbh->pg_putline("314\tMushroom\t8\n");
1410 $dbh->pg_putline("6\tAnchovies\t100\n");
1411 $dbh->pg_endcopy;
1412 ## This example uses explicit columns and a custom delimiter
1414 $dbh->do("COPY mytable(flavor, slices) FROM STDIN WITH DELIMITER '~'");
1415 $dbh->pg_putline("Pepperoni~123\n");
1416 $dbh->pg_putline("Mushroom~314\n");
1417 $dbh->pg_putline("Anchovies~6\n");
1418 $dbh->pg_endcopy;
1419 pg_getline
1421 Used to retrieve data from a table after the server has been put
1422 into COPY OUT mode by calling "COPY tablename TO STDOUT". The first
1423 argument to pg_getline is the variable into which the data will be
1424 stored. The second argument is the size of the variable: this
1425 should be greater than the expected size of the row. Returns a 1 on
1426 success, and an empty string when the last row has been fetched.
1427 Example:
1428 $dbh->do("COPY mytable TO STDOUT");
1430 my @data;
1431 my $x=0;
1432 1 while($dbh->pg_getline($data[$x++], 100));
1433 pop @data; ## Remove final "\\.\n" line
1434 If DBD::Pg is compiled with pre-7.4 libraries, this function will
1436 not work: you will have to use the old $dbh->func($data, 100, 'get‐
1437 line') command, and call pg_getline manually. Users are highly
1438 encouraged to upgrade to a newer version of PostgreSQL if this is
1439 the case.
1440 pg_endcopy
1442 When done with pg_putline, call pg_endcopy to put the server back
1443 in a normal state. Returns a 1 on success. This method will fail if
1444 called when not in a COPY IN or COPY OUT state. Note that you no
1445 longer need to send "\\.\n" when in COPY IN mode: pg_endcopy will
1446 do this for you automatically as needed. pg_endcopy is only needed
1447 after getline if you are using the old-style method,
1448 $dbh->func($data, 100, 'getline').
1449 Large Objects
1451 This driver supports all largeobject functions provided by libpq via
1453 the "func" method. Please note that, starting with PostgreSQL 6.5, any
1454 access to a large object -- even read-only large objects -- must be put
1455 into a transaction!
1456 Cursors
1458 Although PostgreSQL has a cursor concept, it has not been used in the
1460 current implementation. Cursors in PostgreSQL can only be used inside a
1461 transaction block. Because only one transaction block at a time is
1462 allowed, this would have implied the restriction not to use any nested
1463 "SELECT" statements. Hence the "execute" method fetches all data at
1464 once into data structures located in the front-end application. This
1465 approach must to be considered when selecting large amounts of data!
1466 Datatype bool
1468 The current implementation of PostgreSQL returns 't' for true and 'f'
1470 for false. From the Perl point of view, this is a rather unfortunate
1471 choice. DBD::Pg therefore translates the result for the "BOOL" data
1472 type in a Perlish manner: 'f' -> '0' and 't' -> '1'. This way the
1473 application does not have to check the database-specific returned val‐
1474 ues for the data-type "BOOL" because Perl treats '0' as false and '1'
1475 as true. You may set the "pg_bool_tf" attribute to a true value to
1476 change the values back to 't' and 'f' if you wish.
1477 Boolean values can be passed to PostgreSQL as TRUE, 't', 'true', 'y',
1479 'yes' or '1' for true and FALSE, 'f', 'false', 'n', 'no' or '0' for
1480 false.
1481 Schema support
1483 PostgreSQL version 7.3 introduced schema support. Note that the Post‐
1485 greSQL schema concept may differ from those of other databases. In a
1486 nutshell, a schema is a named collection of objects within a single
1487 database. Please refer to the PostgreSQL documentation for more
1488 details.
1489 Currently, DBD::Pg does not provide explicit support for PostgreSQL
1491 schemas. However, schema functionality may be used without any
1492 restrictions by explicitly addressing schema objects, e.g.
1493 my $res = $dbh->selectall_arrayref("SELECT * FROM my_schema.my_table");
1495 or by manipulating the schema search path with "SET search_path", e.g.
1497 $dbh->do("SET search_path TO my_schema, public");
1499
1501 DBI
1502
1504 DBI and DBD-Oracle by Tim Bunce (Tim.Bunce@ig.co.uk)
1505 DBD-Pg by Edmund Mergl (E.Mergl@bawue.de) and Jeffrey W. Baker
1507 (jwbaker@acm.org). By David Wheeler <david@justatheory.com>, Jason
1508 Stewart <jason@openinformatics.com>, Bruce Momjian <pgman@can‐
1509 dle.pha.pa.us>, Greg Sabino Mullane <greg@turnstep.com>, and others
1510 after v1.13.
1511 Parts of this package have been copied from DBI and DBD-Oracle.
1513 Mailing List
1515 The current maintainers may be reached through the 'dbdpg-general'
1517 mailing list: <http://gborg.postgresql.org/mailman/listinfo/dbdpg-gen‐
1518 eral/>.
1519 This list is available through Gmane (<http://www.gmane.org/>) as a
1521 newsgroup with the name: "gmane.comp.db.postgresql.dbdpg"
1522 Bug Reports
1524 If you feel certain you have found a bug, you can report it by sending
1526 an email to <bug-dbd-pg@rt.cpan.org>.
1527
1529 The DBD::Pg module is free software. You may distribute under the terms
1530 of either the GNU General Public License or the Artistic License, as
1531 specified in the Perl README file.
1532
1534 See also DBI/ACKNOWLEDGMENTS.
1535perl v5.8.8 2006-05-05 Pg(3)