2 # $Id: Pg.pm,v 1.1 2004-04-29 09:21:28 ivan Exp $
4 # Copyright (c) 1997,1998,1999,2000 Edmund Mergl
5 # Copyright (c) 2002 Jeffrey W. Baker
6 # Portions Copyright (c) 1994,1995,1996,1997 Tim Bunce
8 # You may distribute under the terms of either the GNU General Public
9 # License or the Artistic License, as specified in the Perl README file.
14 $DBD::Pg::VERSION = '1.22';
22 @ISA = qw(DynaLoader Exporter);
26 PG_BOOL PG_BYTEA PG_CHAR PG_INT8 PG_INT2 PG_INT4 PG_TEXT PG_OID
27 PG_FLOAT4 PG_FLOAT8 PG_ABSTIME PG_RELTIME PG_TINTERVAL PG_BPCHAR
28 PG_VARCHAR PG_DATE PG_TIME PG_DATETIME PG_TIMESPAN PG_TIMESTAMP
31 Exporter::export_ok_tags('pg_types');
33 require_version DBI 1.00;
35 bootstrap DBD::Pg $VERSION;
37 $err = 0; # holds error code for DBI::err
38 $errstr = ""; # holds error string for DBI::errstr
39 $drh = undef; # holds driver handle once initialized
43 my($class, $attr) = @_;
47 # not a 'my' since we use it above to prevent multiple drivers
49 $drh = DBI::_new_drh($class, {
51 'Version' => $VERSION,
52 'Err' => \$DBD::Pg::err,
53 'Errstr' => \$DBD::Pg::errstr,
54 'Attribution' => 'PostgreSQL DBD by Edmund Mergl',
60 ## Used by both the dr and db packages
61 sub pg_server_version {
63 return $dbh->{pg_server_version} if defined $dbh->{pg_server_version};
64 my ($version) = $dbh->selectrow_array("SELECT version();");
65 return 0 unless $version =~ /^PostgreSQL ([\d\.]+)/;
66 $dbh{pg_server_version} = $1;
67 return $dbh{pg_server_version};
72 my $version = DBD::Pg::pg_server_version($dbh);
73 $version =~ /^(\d+\.\d+)/;
74 return $1 < 7.3 ? "" : "pg_catalog.";
81 { package DBD::Pg::dr; # ====== DRIVER ======
86 my $dbh = DBD::Pg::dr::connect($drh, 'dbname=template1') or return undef;
87 $dbh->{AutoCommit} = 1;
88 my $CATALOG = DBD::Pg::pg_use_catalog($dbh);
89 my $sth = $dbh->prepare("SELECT datname FROM ${CATALOG}pg_database ORDER BY datname");
90 $sth->execute or return undef;
91 my (@sources, @datname);
92 while (@datname = $sth->fetchrow_array) {
93 push @sources, "dbi:Pg:dbname=$datname[0]";
102 my($drh, $dbname, $user, $auth)= @_;
104 # create a 'blank' dbh
107 $Name =~ s/^.*dbname\s*=\s*//;
108 $Name =~ s/\s*;.*$//;
110 $user = "" unless defined($user);
111 $auth = "" unless defined($auth);
113 $user = $ENV{DBI_USER} if $user eq "";
114 $auth = $ENV{DBI_PASS} if $auth eq "";
116 $user = "" unless defined($user);
117 $auth = "" unless defined($auth);
119 my($dbh) = DBI::_new_dbh($drh, {
121 'User' => $user, 'CURRENT_USER' => $user,
124 # Connect to the database..
125 DBD::Pg::db::_login($dbh, $dbname, $user, $auth) or return undef;
133 { package DBD::Pg::db; # ====== DATABASE ======
138 my($dbh, $statement, @attribs)= @_;
140 # create a 'blank' sth
142 my $sth = DBI::_new_sth($dbh, {
143 'Statement' => $statement,
146 DBD::Pg::st::_prepare($sth, $statement, @attribs) or return undef;
155 local $SIG{__WARN__} = sub { } if $dbh->{PrintError};
156 local $dbh->{RaiseError} = 0 if $dbh->{RaiseError};
157 my $ret = DBD::Pg::db::_ping($dbh);
162 # Column expected in statement handle returned.
163 # table_cat, table_schem, table_name, column_name, data_type, type_name,
164 # column_size, buffer_length, DECIMAL_DIGITS, NUM_PREC_RADIX, NULLABLE,
165 # REMARKS, COLUMN_DEF, SQL_DATA_TYPE, SQL_DATETIME_SUB, CHAR_OCTET_LENGTH,
166 # ORDINAL_POSITION, IS_NULLABLE
167 # The result set is ordered by TABLE_CAT, TABLE_SCHEM,
168 # TABLE_NAME and ORDINAL_POSITION.
173 # my ($dbh, $catalog, $schema, $table, $column) = @_;
174 my $CATALOG = DBD::Pg::pg_use_catalog($dbh);
177 my @flds = qw/catname n.nspname c.relname a.attname/;
179 for my $idx (0 .. $#attrs) {
180 next if ($flds[$idx] eq 'catname'); # Skip catalog
181 if(defined $attrs[$idx] and length $attrs[$idx]) {
182 # Insure that the value is enclosed in single quotes.
183 $attrs[$idx] =~ s/^'?(\w+)'?$/'$1'/;
184 if ($attrs[$idx] =~ m/[,%]/) {
185 # contains a meta character.
186 push( @wh, q{( } . join ( " OR "
188 ? qq{$flds[$idx] ILIKE $_ }
189 : qq{$flds[$idx] = $_ }
190 } (split /,/, $attrs[$idx]) )
195 push( @wh, qq{$flds[$idx] = $attrs[$idx]} );
201 $wh = join( " AND ", '', @wh ) if (@wh);
202 my $version = DBD::Pg::pg_server_version($dbh);
203 $version =~ /^(\d+\.\d+)/;
205 my $showschema = $version < 7.3 ? "NULL::text" : "n.nspname";
206 my $schemajoin = $version < 7.3 ? "" : "LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)";
207 my $col_info_sql = qq{
209 NULL::text AS "TABLE_CAT"
210 , $showschema AS "TABLE_SCHEM"
211 , c.relname AS "TABLE_NAME"
212 , a.attname AS "COLUMN_NAME"
213 , t.typname AS "DATA_TYPE"
214 , NULL::text AS "TYPE_NAME"
215 , a.attlen AS "COLUMN_SIZE"
216 , NULL::text AS "BUFFER_LENGTH"
217 , NULL::text AS "DECIMAL_DIGITS"
218 , NULL::text AS "NUM_PREC_RADIX"
219 , a.attnotnull AS "NULLABLE"
220 , NULL::text AS "REMARKS"
221 , a.atthasdef AS "COLUMN_DEF"
222 , NULL::text AS "SQL_DATA_TYPE"
223 , NULL::text AS "SQL_DATETIME_SUB"
224 , NULL::text AS "CHAR_OCTET_LENGTH"
225 , a.attnum AS "ORDINAL_POSITION"
226 , a.attnotnull AS "IS_NULLABLE"
227 , a.atttypmod as atttypmod
228 , a.attnotnull as attnotnull
229 , a.atthasdef as atthasdef
232 ${CATALOG}pg_attribute a
233 , ${CATALOG}pg_type t
234 , ${CATALOG}pg_class c
239 AND t.oid = a.atttypid
240 AND c.relkind in ('r','v')
245 my $sth = $dbh->prepare( $col_info_sql ) or return undef;
251 sub primary_key_info {
253 my ($catalog, $schema, $table) = @_;
255 my $CATALOG = DBD::Pg::pg_use_catalog($dbh);
257 # TABLE_CAT:, TABLE_SCHEM:, TABLE_NAME:, COLUMN_NAME:, KEY_SEQ:
260 my @wh = (); my @dat = (); # Used to hold data for the attributes.
262 my $version = DBD::Pg::pg_server_version($dbh);
263 $version =~ /^(\d+\.\d+)/;
266 my @flds = qw/catname u.usename bc.relname/;
267 $flds[1] = 'n.nspname' unless ($version < 7.3);
269 for my $idx (0 .. $#attrs) {
270 next if ($flds[$idx] eq 'catname'); # Skip catalog
271 if(defined $attrs[$idx] and length $attrs[$idx]) {
272 if ($attrs[$idx] =~ m/[,%_?]/) {
273 # contains a meta character.
274 push( @wh, q{( } . join ( " OR "
275 , map { push(@dat, $_);
277 ? qq{$flds[$idx] iLIKE ? }
278 : qq{$flds[$idx] = ? }
279 } (split /,/, $attrs[$idx]) )
284 push( @dat, $attrs[$idx] );
285 push( @wh, qq{$flds[$idx] = ? } );
291 $wh = join( " AND ", '', @wh ) if (@wh);
293 # Base primary key selection query borrowed from phpPgAdmin.
294 my $showschema = $version < 7.3 ? "NULL::text" : "n.nspname";
295 my $schemajoin = $version < 7.3 ? "" : "LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = bc.relnamespace)";
296 my $pri_key_sql = qq{
298 NULL::text AS "TABLE_CAT"
299 , $showschema AS "TABLE_SCHEM"
300 , bc.relname AS "TABLE_NAME"
301 , a.attname AS "COLUMN_NAME"
302 , a.attnum AS "KEY_SEQ"
303 , ic.relname AS "PK_NAME"
306 , ${CATALOG}pg_attribute a
307 , ${CATALOG}pg_class ic
308 , ${CATALOG}pg_class bc
312 AND i.indexrelid = ic.oid
315 i.indkey[0] = a.attnum
317 i.indkey[1] = a.attnum
319 i.indkey[2] = a.attnum
321 i.indkey[3] = a.attnum
323 i.indkey[4] = a.attnum
325 i.indkey[5] = a.attnum
327 i.indkey[6] = a.attnum
329 i.indkey[7] = a.attnum
331 i.indkey[8] = a.attnum
333 i.indkey[9] = a.attnum
335 i.indkey[10] = a.attnum
337 i.indkey[11] = a.attnum
339 i.indkey[12] = a.attnum
341 AND a.attrelid = bc.oid
342 AND i.indproc = '0'::oid
343 AND i.indisprimary = 't'
348 my $sth = $dbh->prepare( $pri_key_sql ) or return undef;
354 sub foreign_key_info {
355 # todo: verify schema work as expected
356 # add code to handle multiple-column keys correctly
357 # return something nicer for pre-7.3?
358 # try to clean up SQL, perl code
359 # create a test script?
362 my ($pk_catalog, $pk_schema, $pk_table,
363 $fk_catalog, $fk_schema, $fk_table) = @_;
365 # this query doesn't work for Postgres before 7.3
366 my $version = $dbh->pg_server_version;
367 $version =~ /^(\d+)\.(\d)/;
368 return undef if ($1.$2 < 73);
370 # Used to hold data for the attributes.
373 # SQL to find primary/unique keys of a table
376 NULL::text AS PKTABLE_CAT,
377 pknam.nspname AS PKTABLE_SCHEM,
378 pkc.relname AS PKTABLE_NAME,
379 pka.attname AS PKCOLUMN_NAME,
380 NULL::text AS FKTABLE_CAT,
381 NULL::text AS FKTABLE_SCHEM,
382 NULL::text AS FKTABLE_NAME,
383 NULL::text AS FKCOLUMN_NAME,
384 pkcon.conkey[1] AS KEY_SEQ,
386 WHEN pkcon.confupdtype = 'c' THEN 0
387 WHEN pkcon.confupdtype = 'r' THEN 1
388 WHEN pkcon.confupdtype = 'n' THEN 2
389 WHEN pkcon.confupdtype = 'a' THEN 3
390 WHEN pkcon.confupdtype = 'd' THEN 4
393 WHEN pkcon.confdeltype = 'c' THEN 0
394 WHEN pkcon.confdeltype = 'r' THEN 1
395 WHEN pkcon.confdeltype = 'n' THEN 2
396 WHEN pkcon.confdeltype = 'a' THEN 3
397 WHEN pkcon.confdeltype = 'd' THEN 4
399 NULL::text AS FK_NAME,
400 pkcon.conname AS PK_NAME,
402 WHEN pkcon.condeferrable = 'f' THEN 7
403 WHEN pkcon.condeferred = 't' THEN 6
404 WHEN pkcon.condeferred = 'f' THEN 5
405 END AS DEFERRABILITY,
407 WHEN pkcon.contype = 'p' THEN 'PRIMARY'
408 WHEN pkcon.contype = 'u' THEN 'UNIQUE'
409 END AS UNIQUE_OR_PRIMARY
411 pg_constraint AS pkcon
413 pg_class pkc ON pkc.oid=pkcon.conrelid
415 pg_namespace pknam ON pkcon.connamespace=pknam.oid
417 pg_attribute pka ON pka.attnum=pkcon.conkey[1] AND pka.attrelid=pkc.oid
420 # SQL to find foreign keys of a table
423 NULL::text AS PKTABLE_CAT,
424 pknam.nspname AS PKTABLE_SCHEM,
425 pkc.relname AS PKTABLE_NAME,
426 pka.attname AS PKCOLUMN_NAME,
427 NULL::text AS FKTABLE_CAT,
428 fknam.nspname AS FKTABLE_SCHEM,
429 fkc.relname AS FKTABLE_NAME,
430 fka.attname AS FKCOLUMN_NAME,
431 fkcon.conkey[1] AS KEY_SEQ,
433 WHEN fkcon.confupdtype = 'c' THEN 0
434 WHEN fkcon.confupdtype = 'r' THEN 1
435 WHEN fkcon.confupdtype = 'n' THEN 2
436 WHEN fkcon.confupdtype = 'a' THEN 3
437 WHEN fkcon.confupdtype = 'd' THEN 4
440 WHEN fkcon.confdeltype = 'c' THEN 0
441 WHEN fkcon.confdeltype = 'r' THEN 1
442 WHEN fkcon.confdeltype = 'n' THEN 2
443 WHEN fkcon.confdeltype = 'a' THEN 3
444 WHEN fkcon.confdeltype = 'd' THEN 4
446 fkcon.conname AS FK_NAME,
447 pkcon.conname AS PK_NAME,
449 WHEN fkcon.condeferrable = 'f' THEN 7
450 WHEN fkcon.condeferred = 't' THEN 6
451 WHEN fkcon.condeferred = 'f' THEN 5
452 END AS DEFERRABILITY,
454 WHEN pkcon.contype = 'p' THEN 'PRIMARY'
455 WHEN pkcon.contype = 'u' THEN 'UNIQUE'
456 END AS UNIQUE_OR_PRIMARY
458 pg_constraint AS fkcon
460 pg_constraint AS pkcon ON fkcon.confrelid=pkcon.conrelid
461 AND fkcon.confkey=pkcon.conkey
463 pg_class fkc ON fkc.oid=fkcon.conrelid
465 pg_class pkc ON pkc.oid=fkcon.confrelid
467 pg_namespace pknam ON pkcon.connamespace=pknam.oid
469 pg_namespace fknam ON fkcon.connamespace=fknam.oid
471 pg_attribute fka ON fka.attnum=fkcon.conkey[1] AND fka.attrelid=fkc.oid
473 pg_attribute pka ON pka.attnum=pkcon.conkey[1] AND pka.attrelid=pkc.oid
476 # if schema are provided, use this SQL
477 my $pk_schema_sql = " AND pknam.nspname = ? ";
478 my $fk_schema_sql = " AND fknam.nspname = ? ";
482 # if $fk_table: generate SQL stub, which will be same
483 # whether or not $pk_table supplied
486 $key_sql = $fkey_sql . qq{
490 push @dat, $fk_table;
494 $key_sql .= $fk_schema_sql;
495 push @dat,$fk_schema;
499 # if $fk_table and $pk_table: (defined by DBI, not SQL/CLI)
500 # return foreign key of $fk_table that refers to $pk_table
502 if ($pk_table and $fk_table)
508 push @dat, $pk_table;
512 $key_sql .= $pk_schema_sql;
513 push @dat,$pk_schema;
517 # if $fk_table but no $pk_table:
518 # return all foreign keys of $fk_table, and all
519 # primary keys of tables to which $fk_table refers
520 if (!$pk_table and $fk_table)
522 # find primary/unique keys referenced by $fk_table
523 # (this one is a little tricky)
524 $key_sql .= ' UNION ' . $pkey_sql . qq{
531 pg_constraint AS fkcon
533 pg_constraint AS pkcon ON fkcon.confrelid=pkcon.conrelid AND
534 fkcon.confkey=pkcon.conkey
536 pg_class fkc ON fkc.oid=fkcon.conrelid
541 push @dat, $fk_table;
545 $key_sql .= $pk_schema_sql;
546 push @dat,$fk_schema;
550 # if $pk_table but no $fk_table:
551 # return primary key of $pk_table and all foreign keys
552 # that reference $pk_table
553 # question: what about unique keys?
554 # (DBI and SQL/CLI both state to omit unique keys)
556 if ($pk_table and !$fk_table)
558 # find primary key (only!) of $pk_table
559 $key_sql = $pkey_sql . qq{
569 $key_sql .= $pk_schema_sql;
570 push @dat,$pk_schema;
573 # find all foreign keys that reference $pk_table
574 $key_sql .= 'UNION ' . $fkey_sql . qq{
580 push @dat, $pk_table;
584 $key_sql .= $fk_schema_sql;
585 push @dat,$pk_schema;
589 return undef unless $key_sql;
590 my $sth = $dbh->prepare( $key_sql ) or
598 sub table_info { # DBI spec: TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS
600 my ($catalog, $schema, $table, $type) = @_;
605 my $version = DBD::Pg::pg_server_version($dbh);
606 $version =~ /^(\d+\.\d+)/;
608 my $CATALOG = DBD::Pg::pg_use_catalog($dbh);
611 (defined $catalog and $catalog eq '%')
612 and (defined $schema and $schema eq '')
613 and (defined $table and $table eq '')
617 NULL::text AS "TABLE_CAT"
618 , NULL::text AS "TABLE_SCHEM"
619 , NULL::text AS "TABLE_NAME"
620 , NULL::text AS "TABLE_TYPE"
621 , NULL::text AS "REMARKS"
625 (defined $catalog and $catalog eq '')
626 and (defined $schema and $schema eq '%')
627 and (defined $table and $table eq '')
629 $tbl_sql = ($version < 7.3) ? q{
631 NULL::text AS "TABLE_CAT"
632 , NULL::text AS "TABLE_SCHEM"
633 , NULL::text AS "TABLE_NAME"
634 , NULL::text AS "TABLE_TYPE"
635 , NULL::text AS "REMARKS"
638 NULL::text AS "TABLE_CAT"
639 , n.nspname AS "TABLE_SCHEM"
640 , NULL::text AS "TABLE_NAME"
641 , NULL::text AS "TABLE_TYPE"
642 , NULL::text AS "REMARKS"
643 FROM pg_catalog.pg_namespace n
648 (defined $catalog and $catalog eq '')
649 and (defined $schema and $schema eq '')
650 and (defined $table and $table eq '')
651 and (defined $type and $type eq '%')
653 # From the postgresql 7.2.1 manual 3.5 pg_class
654 # 'r' = ordinary table
659 #, 't' = secondary TOAST table
662 NULL::text AS "TABLE_CAT"
663 , NULL::text AS "TABLE_SCHEM"
664 , NULL::text AS "TABLE_NAME"
665 , 'table' AS "TABLE_TYPE"
666 , 'ordinary table - r' AS "REMARKS"
669 NULL::text AS "TABLE_CAT"
670 , NULL::text AS "TABLE_SCHEM"
671 , NULL::text AS "TABLE_NAME"
672 , 'index' AS "TABLE_TYPE"
673 , 'index - i' AS "REMARKS"
676 NULL::text AS "TABLE_CAT"
677 , NULL::text AS "TABLE_SCHEM"
678 , NULL::text AS "TABLE_NAME"
679 , 'sequence' AS "TABLE_TYPE"
680 , 'sequence - S' AS "REMARKS"
683 NULL::text AS "TABLE_CAT"
684 , NULL::text AS "TABLE_SCHEM"
685 , NULL::text AS "TABLE_NAME"
686 , 'view' AS "TABLE_TYPE"
687 , 'view - v' AS "REMARKS"
690 NULL::text AS "TABLE_CAT"
691 , NULL::text AS "TABLE_SCHEM"
692 , NULL::text AS "TABLE_NAME"
693 , 'special' AS "TABLE_TYPE"
694 , 'special - s' AS "REMARKS"
697 NULL::text AS "TABLE_CAT"
698 , NULL::text AS "TABLE_SCHEM"
699 , NULL::text AS "TABLE_NAME"
700 , 'secondary' AS "TABLE_TYPE"
701 , 'secondary TOAST table - t' AS "REMARKS"
706 my $showschema = $version < 7.3 ? "NULL::text" : "n.nspname";
707 my $schemajoin = $version < 7.3 ? "" : "LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)";
708 my $schemacase = $version < 7.3 ? "CASE WHEN c.relname ~ '^pg_' THEN 'SYSTEM TABLE' ELSE 'TABLE' END" :
709 "CASE WHEN n.nspname ~ '^pg_' THEN 'SYSTEM TABLE' ELSE 'TABLE' END";
711 SELECT NULL::text AS "TABLE_CAT"
712 , $showschema AS "TABLE_SCHEM"
713 , c.relname AS "TABLE_NAME"
715 WHEN c.relkind = 'v' THEN 'VIEW'
718 , d.description AS "REMARKS"
719 FROM ${CATALOG}pg_user AS u
720 , ${CATALOG}pg_class AS c
722 ${CATALOG}pg_description AS d
723 ON (c.relfilenode = d.objoid AND d.objsubid = 0)
727 AND c.relhasrules = FALSE) OR
729 AND c.relhasrules = TRUE))
730 AND c.relname !~ '^xin[vx][0-9]+'
731 AND c.relowner = u.usesysid
735 # Did we receive any arguments?
738 my @flds = qw/catname n.nspname c.relname c.relkind/;
740 for my $idx (0 .. $#attrs) {
741 next if ($flds[$idx] eq 'catname'); # Skip catalog
742 if(defined $attrs[$idx] and length $attrs[$idx]) {
743 # Change the "name" of the types to the real value.
744 if ($flds[$idx] =~ m/relkind/) {
745 $attrs[$idx] =~ s/^\'?table\'?/'r'/i;
746 $attrs[$idx] =~ s/^\'?index\'?/'i'/i;
747 $attrs[$idx] =~ s/^\'?sequence\'?/'S'/i;
748 $attrs[$idx] =~ s/^\'?view\'?/'v'/i;
749 $attrs[$idx] =~ s/^\'?special\'?/'s'/i;
750 $attrs[$idx] =~ s/^\'?secondary\'?/'t'/i;
752 # Insure that the value is enclosed in single quotes.
753 $attrs[$idx] =~ s/^'?(\w+)'?$/'$1'/;
754 if ($attrs[$idx] =~ m/[,%]/) {
755 # contains a meta character.
756 push( @wh, q{( } . join ( " OR "
758 ? qq{$flds[$idx] LIKE $_ }
759 : qq{$flds[$idx] = $_ }
760 } (split /,/, $attrs[$idx]) )
765 push( @wh, qq{$flds[$idx] = $attrs[$idx]} );
772 $wh = join( " AND ",'', @wh );
774 SELECT NULL::text AS "TABLE_CAT"
775 , $showschema AS "TABLE_SCHEM"
776 , c.relname AS "TABLE_NAME"
778 WHEN c.relkind = 'r' THEN
779 CASE WHEN n.nspname ~ '^pg_' THEN 'SYSTEM TABLE' ELSE 'TABLE' END
780 WHEN c.relkind = 'v' THEN 'VIEW'
781 WHEN c.relkind = 'i' THEN 'INDEX'
782 WHEN c.relkind = 'S' THEN 'SEQUENCE'
783 WHEN c.relkind = 's' THEN 'SPECIAL'
784 WHEN c.relkind = 't' THEN 'SECONDARY'
787 , d.description AS "REMARKS"
788 FROM ${CATALOG}pg_class AS c
790 ${CATALOG}pg_description AS d
791 ON (c.relfilenode = d.objoid AND d.objsubid = 0)
794 c.relname !~ '^xin[vx][0-9]+'
802 my $sth = $dbh->prepare( $tbl_sql ) or return undef;
811 my $version = DBD::Pg::pg_server_version($dbh);
812 $version =~ /^(\d+\.\d+)/;
814 my $SQL = ($version < 7.3) ?
815 "SELECT relname AS \"TABLE_NAME\"
818 AND relname !~ '^pg_'
819 AND relname !~ '^xin[vx][0-9]+'
821 "SELECT n.nspname AS \"SCHEMA_NAME\", c.relname AS \"TABLE_NAME\"
822 FROM pg_catalog.pg_class c
823 LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
824 WHERE c.relkind = 'r'
825 AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
826 AND pg_catalog.pg_table_is_visible(c.oid)
828 my $sth = $dbh->prepare($SQL) or return undef;
829 $sth->execute or return undef;
830 my (@tables, @relname);
831 while (@relname = $sth->fetchrow_array) {
832 push @tables, $version < 7.3 ? $relname[0] : "$relname[0].$relname[1]";
840 sub table_attributes {
841 my ($dbh, $table) = @_;
842 my $CATALOG = DBD::Pg::pg_use_catalog($dbh);
844 my $attrs = $dbh->selectall_arrayref(
845 "select a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum
846 from ${CATALOG}pg_attribute a,
847 ${CATALOG}pg_class c,
850 and a.attrelid = c.oid
852 and t.oid = a.atttypid
856 return $result unless scalar(@$attrs);
858 # Select the array value for tables primary key.
859 my $pk_key_sql = qq{SELECT pg_index.indkey
860 FROM ${CATALOG}pg_class, ${CATALOG}pg_index
862 pg_class.oid = pg_index.indrelid
863 AND pg_class.relname = '$table'
864 AND pg_index.indisprimary = 't'
866 # Expand this (returned as a string) a real array.
868 my $pkeys = $dbh->selectrow_array( $pk_key_sql );
869 if (defined $pkeys) {
870 foreach (split( /\s+/, $pkeys))
876 (@pk) ? "AND pg_attribute.attnum in (" . join ( ", ", @pk ) . ")"
879 # Get the primary key
880 my $pri_key = $dbh->selectcol_arrayref("SELECT pg_attribute.attname
881 FROM ${CATALOG}pg_class, ${CATALOG}pg_attribute, ${CATALOG}pg_index
882 WHERE pg_class.oid = pg_attribute.attrelid
883 AND pg_class.oid = pg_index.indrelid
885 AND pg_index.indisprimary = 't'
886 AND pg_class.relname = ?
887 ORDER BY pg_attribute.attnum
889 $pri_key = [] unless $pri_key;
891 foreach my $attr (reverse @$attrs) {
892 my ($col_name, $col_type, $size, $mod, $notnull, $hasdef, $attnum) = @$attr;
896 } elsif ($mod > 0xffff) {
897 my $prec = ($mod & 0xffff) - 4;
901 } elsif ($mod >= 4) {
908 # Get the default value, if any
909 my ($default) = $dbh->selectrow_array("SELECT adsrc FROM ${CATALOG}pg_attrdef WHERE adnum = $attnum") if -1 == $attnum;
910 $default = '' unless $default;
912 # Test for any constraints
913 # Note: as of PostgreSQL 7.3 pg_relcheck has been replaced
914 # by pg_constraint. To maintain compatibility, check
915 # version number and execute appropriate query.
917 my $version = pg_server_version( $dbh );
919 my $con_query = $version < 7.3
920 ? "SELECT rcsrc FROM pg_relcheck WHERE rcname = '${table}_$col_name'"
921 : "SELECT consrc FROM pg_catalog.pg_constraint WHERE contype = 'c' AND conname = '${table}_$col_name'";
922 my ($constraint) = $dbh->selectrow_array($con_query);
923 $constraint = '' unless $constraint;
925 # Check to see if this is the primary key
926 my $is_primary_key = scalar(grep { /^$col_name$/i } @$pri_key) ? 1 : 0;
934 CONSTRAINT => $constraint,
935 PRIMARY_KEY => $is_primary_key,
950 # LITERAL_PREFIX => 3,
951 # LITERAL_SUFFIX => 4,
952 # CREATE_PARAMS => 5,
954 # CASE_SENSITIVE => 7,
956 # UNSIGNED_ATTRIBUTE => 9,
958 # AUTO_INCREMENT =>11,
959 # LOCAL_TYPE_NAME =>12,
960 # MINIMUM_SCALE =>13,
961 # MAXIMUM_SCALE =>14,
967 COLUMN_SIZE => 2, # was PRECISION originally
974 UNSIGNED_ATTRIBUTE=> 9,
975 FIXED_PREC_SCALE => 10, # was MONEY originally
976 AUTO_UNIQUE_VALUE => 11, # was AUTO_INCREMENT originally
977 LOCAL_TYPE_NAME => 12,
980 NUM_PREC_RADIX => 15,
984 # typname |typlen|typprtlen| SQL92
985 # --------------+------+---------+ -------
986 # bool | 1| 1| BOOLEAN
987 # text | -1| -1| like VARCHAR, but automatic storage allocation
988 # bpchar | -1| -1| CHARACTER(n) bp=blank padded
989 # varchar | -1| -1| VARCHAR(n)
990 # int2 | 2| 5| SMALLINT
991 # int4 | 4| 10| INTEGER
994 # float4 | 4| 12| FLOAT(p) for p<7=float4, for p<16=float8
995 # float8 | 8| 24| REAL
998 # tinterval | 12| 47| /
1001 # datetime | 8| 47| /
1002 # timespan | 12| 47| INTERVAL
1003 # timestamp | 4| 19| TIMESTAMP
1004 # --------------+------+---------+
1006 # DBI type definitions / PostgreSQL definitions # type needs to be DBI-specific (not pg_type)
1009 # SQL_CHAR 1 1042 bpchar
1010 # SQL_NUMERIC 2 700 float4
1011 # SQL_DECIMAL 3 700 float4
1012 # SQL_INTEGER 4 23 int4
1013 # SQL_SMALLINT 5 21 int2
1014 # SQL_FLOAT 6 700 float4
1015 # SQL_REAL 7 701 float8
1016 # SQL_DOUBLE 8 20 int8
1017 # SQL_DATE 9 1082 date
1018 # SQL_TIME 10 1083 time
1019 # SQL_TIMESTAMP 11 1296 timestamp
1020 # SQL_VARCHAR 12 1043 varchar
1024 # name type prec prefix suffix create params null case se unsign mon incr local min max
1026 [ 'bytea', -2, 4096, '\'', '\'', undef, 1, '1', 3, undef, '0', '0', 'BYTEA', undef, undef, undef ],
1027 [ 'bool', 0, 1, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'BOOLEAN', undef, undef, undef ],
1028 [ 'int8', 8, 20, undef, undef, undef, 1, '0', 2, '0', '0', '0', 'LONGINT', undef, undef, undef ],
1029 [ 'int2', 5, 5, undef, undef, undef, 1, '0', 2, '0', '0', '0', 'SMALLINT', undef, undef, undef ],
1030 [ 'int4', 4, 10, undef, undef, undef, 1, '0', 2, '0', '0', '0', 'INTEGER', undef, undef, undef ],
1031 [ 'text', 12, 4096, '\'', '\'', undef, 1, '1', 3, undef, '0', '0', 'TEXT', undef, undef, undef ],
1032 [ 'float4', 6, 12, undef, undef, 'precision', 1, '0', 2, '0', '0', '0', 'FLOAT', undef, undef, undef ],
1033 [ 'float8', 7, 24, undef, undef, 'precision', 1, '0', 2, '0', '0', '0', 'REAL', undef, undef, undef ],
1034 [ 'abstime', 10, 20, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'ABSTIME', undef, undef, undef ],
1035 [ 'reltime', 10, 20, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'RELTIME', undef, undef, undef ],
1036 [ 'tinterval', 11, 47, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'TINTERVAL', undef, undef, undef ],
1037 [ 'money', 0, 24, undef, undef, undef, 1, '0', 2, undef, '1', '0', 'MONEY', undef, undef, undef ],
1038 [ 'bpchar', 1, 4096, '\'', '\'', 'max length', 1, '1', 3, undef, '0', '0', 'CHARACTER', undef, undef, undef ],
1039 [ 'bpchar', 12, 4096, '\'', '\'', 'max length', 1, '1', 3, undef, '0', '0', 'CHARACTER', undef, undef, undef ],
1040 [ 'varchar', 12, 4096, '\'', '\'', 'max length', 1, '1', 3, undef, '0', '0', 'VARCHAR', undef, undef, undef ],
1041 [ 'date', 9, 10, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'DATE', undef, undef, undef ],
1042 [ 'time', 10, 16, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'TIME', undef, undef, undef ],
1043 [ 'datetime', 11, 47, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'DATETIME', undef, undef, undef ],
1044 [ 'timespan', 11, 47, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'INTERVAL', undef, undef, undef ],
1045 [ 'timestamp', 10, 19, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'TIMESTAMP', undef, undef, undef ]
1047 # intentionally omitted: char, all geometric types, all array types
1053 # Characters that need to be escaped by quote().
1054 my %esc = ( "'" => '\\047', # '\\' . sprintf("%03o", ord("'")), # ISO SQL 2
1055 '\\' => '\\134', # '\\' . sprintf("%03o", ord("\\")),
1058 # Set up lookup for SQL types we don't want to escape.
1059 my %no_escape = map { $_ => 1 }
1060 DBI::SQL_INTEGER, DBI::SQL_SMALLINT, DBI::SQL_DECIMAL,
1061 DBI::SQL_FLOAT, DBI::SQL_REAL, DBI::SQL_DOUBLE, DBI::SQL_NUMERIC;
1064 my ($dbh, $str, $data_type) = @_;
1065 return "NULL" unless defined $str;
1066 return $str if $data_type && $no_escape{$data_type};
1068 $dbh->DBI::set_err(1, "Use of SQL_BINARY invalid in quote()")
1069 if $data_type && $data_type == DBI::SQL_BINARY;
1071 $str =~ s/(['\\\0])/$esc{$1}/g;
1075 } # end of package DBD::Pg::db
1077 { package DBD::Pg::st; # ====== STATEMENT ======
1089 DBD::Pg - PostgreSQL database driver for the DBI module
1095 $dbh = DBI->connect("dbi:Pg:dbname=$dbname", "", "");
1097 # for some advanced uses you may need PostgreSQL type values:
1098 use DBD::Oracle qw(:pg_types);
1100 # See the DBI module documentation for full details
1104 DBD::Pg is a Perl module which works with the DBI module to provide access to
1105 PostgreSQL databases.
1107 =head1 MODULE DOCUMENTATION
1109 This documentation describes driver specific behavior and restrictions. It is
1110 not supposed to be used as the only reference for the user. In any case
1111 consult the DBI documentation first!
1113 =head1 THE DBI CLASS
1115 =head2 DBI Class Methods
1121 To connect to a database with a minimum of parameters, use the following
1124 $dbh = DBI->connect("dbi:Pg:dbname=$dbname", "", "");
1126 This connects to the database $dbname at localhost without any user
1127 authentication. This is sufficient for the defaults of PostgreSQL.
1129 The following connect statement shows all possible parameters:
1131 $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host;port=$port;" .
1132 "options=$options;tty=$tty", "$username", "$password");
1134 If a parameter is undefined PostgreSQL first looks for specific environment
1135 variables and then it uses hard coded defaults:
1137 parameter environment variable hard coded default
1138 --------------------------------------------------
1139 dbname PGDATABASE current userid
1140 host PGHOST localhost
1142 options PGOPTIONS ""
1144 username PGUSER current userid
1145 password PGPASSWORD ""
1147 If a host is specified, the postmaster on this host needs to be started with
1148 the C<-i> option (TCP/IP sockets).
1150 The options parameter specifies runtime options for the Postgres
1151 backend. Common usage is to increase the number of buffers with the C<-B>
1152 option. Also important is the C<-F> option, which disables automatic fsync()
1153 call after each transaction. For further details please refer to the
1156 For authentication with username and password appropriate entries have to be
1157 made in pg_hba.conf. Please refer to the L<pg_hba.conf> and the L<pg_passwd>
1158 for the different types of authentication. Note that for these two parameters
1159 DBI distinguishes between empty and undefined. If these parameters are
1160 undefined DBI substitutes the values of the environment variables DBI_USER and
1161 DBI_PASS if present.
1163 =item B<available_drivers>
1165 @driver_names = DBI->available_drivers;
1167 Implemented by DBI, no driver-specific impact.
1169 =item B<data_sources>
1171 @data_sources = DBI->data_sources('Pg');
1173 The driver supports this method. Note that the necessary database connection to
1174 the database template1 will be done on the localhost without any
1175 user-authentication. Other preferences can only be set with the environment
1176 variables PGHOST, DBI_USER and DBI_PASS.
1180 DBI->trace($trace_level, $trace_file)
1182 Implemented by DBI, no driver-specific impact.
1186 =head2 DBI Dynamic Attributes
1190 =head1 METHODS COMMON TO ALL HANDLES
1198 Supported by the driver as proposed by DBI. For the connect method it returns
1199 PQstatus. In all other cases it returns PQresultStatus of the current handle.
1205 Supported by the driver as proposed by DBI. It returns the PQerrorMessage
1206 related to the current handle.
1212 This driver does not (yet) support the state method.
1216 $h->trace($trace_level, $trace_filename);
1218 Implemented by DBI, no driver-specific impact.
1222 $h->trace_msg($message_text);
1224 Implemented by DBI, no driver-specific impact.
1228 This driver supports a variety of driver specific functions accessible via the
1231 $attrs = $dbh->func($table, 'table_attributes');
1233 This method returns for the given table a reference to an array of hashes:
1237 SIZE attribute size (-1 for variable size)
1238 NULLABLE flag nullable
1239 DEFAULT default value
1240 CONSTRAINT constraint
1241 PRIMARY_KEY flag is_primary_key
1243 $lobjId = $dbh->func($mode, 'lo_creat');
1245 Creates a new large object and returns the object-id. $mode is a bit-mask
1246 describing different attributes of the new object. Use the following
1249 $dbh->{pg_INV_WRITE}
1252 Upon failure it returns undef.
1254 $lobj_fd = $dbh->func($lobjId, $mode, 'lo_open');
1256 Opens an existing large object and returns an object-descriptor for use in
1257 subsequent lo_* calls. For the mode bits see lo_create. Returns undef upon
1258 failure. Note that 0 is a perfectly correct object descriptor!
1260 $nbytes = $dbh->func($lobj_fd, $buf, $len, 'lo_write');
1262 Writes $len bytes of $buf into the large object $lobj_fd. Returns the number
1263 of bytes written and undef upon failure.
1265 $nbytes = $dbh->func($lobj_fd, $buf, $len, 'lo_read');
1267 Reads $len bytes into $buf from large object $lobj_fd. Returns the number of
1268 bytes read and undef upon failure.
1270 $loc = $dbh->func($lobj_fd, $offset, $whence, 'lo_lseek');
1272 Change the current read or write location on the large object
1273 $obj_id. Currently $whence can only be 0 (L_SET). Returns the current location
1274 and undef upon failure.
1276 $loc = $dbh->func($lobj_fd, 'lo_tell');
1278 Returns the current read or write location on the large object $lobj_fd and
1281 $lobj_fd = $dbh->func($lobj_fd, 'lo_close');
1283 Closes an existing large object. Returns true upon success and false upon
1286 $lobj_fd = $dbh->func($lobj_fd, 'lo_unlink');
1288 Deletes an existing large object. Returns true upon success and false upon
1291 $lobjId = $dbh->func($filename, 'lo_import');
1293 Imports a Unix file as large object and returns the object id of the new
1294 object or undef upon failure.
1296 $ret = $dbh->func($lobjId, 'lo_export', 'filename');
1298 Exports a large object into a Unix file. Returns false upon failure, true
1301 $ret = $dbh->func($line, 'putline');
1303 Used together with the SQL-command 'COPY table FROM STDIN' to copy large
1304 amount of data into a table avoiding the overhead of using single
1305 insert commands. The application must explicitly send the two characters "\."
1306 to indicate to the backend that it has finished sending its data. See test.pl
1307 for an example on how to use this function.
1309 $ret = $dbh->func($buffer, length, 'getline');
1311 Used together with the SQL-command 'COPY table TO STDOUT' to dump a complete
1312 table. See test.pl for an example on how to use this function.
1314 $ret = $dbh->func('pg_notifies');
1316 Returns either undef or a reference to two-element array [ $table,
1317 $backend_pid ] of asynchronous notifications received.
1319 $fd = $dbh->func('getfd');
1321 Returns fd of the actual connection to server. Can be used with select() and
1322 func('pg_notifies').
1326 =head1 ATTRIBUTES COMMON TO ALL HANDLES
1330 =item B<Warn> (boolean, inherited)
1332 Implemented by DBI, no driver-specific impact.
1334 =item B<Active> (boolean, read-only)
1336 Supported by the driver as proposed by DBI. A database handle is active while
1337 it is connected and statement handle is active until it is finished.
1339 =item B<Kids> (integer, read-only)
1341 Implemented by DBI, no driver-specific impact.
1343 =item B<ActiveKids> (integer, read-only)
1345 Implemented by DBI, no driver-specific impact.
1347 =item B<CachedKids> (hash ref)
1349 Implemented by DBI, no driver-specific impact.
1351 =item B<CompatMode> (boolean, inherited)
1353 Not used by this driver.
1355 =item B<InactiveDestroy> (boolean)
1357 Implemented by DBI, no driver-specific impact.
1359 =item B<PrintError> (boolean, inherited)
1361 Implemented by DBI, no driver-specific impact.
1363 =item B<RaiseError> (boolean, inherited)
1365 Implemented by DBI, no driver-specific impact.
1367 =item B<HandleError> (boolean, inherited)
1369 Implemented by DBI, no driver-specific impact.
1371 =item B<ChopBlanks> (boolean, inherited)
1373 Supported by the driver as proposed by DBI. This method is similar to the
1376 =item B<LongReadLen> (integer, inherited)
1378 Implemented by DBI, not used by the driver.
1380 =item B<LongTruncOk> (boolean, inherited)
1382 Implemented by DBI, not used by the driver.
1384 =item B<Taint> (boolean, inherited)
1386 Implemented by DBI, no driver-specific impact.
1390 Implemented by DBI, no driver-specific impact.
1394 =head1 DBI DATABASE HANDLE OBJECTS
1396 =head2 Database Handle Methods
1400 =item B<selectrow_array>
1402 @row_ary = $dbh->selectrow_array($statement, \%attr, @bind_values);
1404 Implemented by DBI, no driver-specific impact.
1406 =item B<selectrow_arrayref>
1408 $ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @bind_values);
1410 Implemented by DBI, no driver-specific impact.
1412 =item B<selectrow_hashref>
1414 $hash_ref = $dbh->selectrow_hashref($statement, \%attr, @bind_values);
1416 Implemented by DBI, no driver-specific impact.
1418 =item B<selectall_arrayref>
1420 $ary_ref = $dbh->selectall_arrayref($statement, \%attr, @bind_values);
1422 Implemented by DBI, no driver-specific impact.
1424 =item B<selectall_hashref>
1426 $hash_ref = $dbh->selectall_hashref($statement, $key_field);
1428 Implemented by DBI, no driver-specific impact.
1430 =item B<selectcol_arrayref>
1432 $ary_ref = $dbh->selectcol_arrayref($statement, \%attr, @bind_values);
1434 Implemented by DBI, no driver-specific impact.
1438 $sth = $dbh->prepare($statement, \%attr);
1440 PostgreSQL does not have the concept of preparing a statement. Hence the
1441 prepare method just stores the statement after checking for place-holders. No
1442 information about the statement is available after preparing it.
1444 =item B<prepare_cached>
1446 $sth = $dbh->prepare_cached($statement, \%attr);
1448 Implemented by DBI, no driver-specific impact. This method is not useful for
1449 this driver, because preparing a statement has no database interaction.
1453 $rv = $dbh->do($statement, \%attr, @bind_values);
1455 Implemented by DBI, no driver-specific impact. See the notes for the execute
1456 method elsewhere in this document.
1462 Supported by the driver as proposed by DBI. See also the notes about
1463 B<Transactions> elsewhere in this document.
1467 $rc = $dbh->rollback;
1469 Supported by the driver as proposed by DBI. See also the notes about
1470 B<Transactions> elsewhere in this document.
1474 $rc = $dbh->disconnect;
1476 Supported by the driver as proposed by DBI.
1482 This driver supports the ping-method, which can be used to check the validity
1483 of a database-handle. The ping method issues an empty query and checks the
1488 $sth = $dbh->table_info;
1490 Supported by the driver as proposed by DBI. This method returns all tables and
1491 views which are owned by the current user. It does not select any indexes and
1492 sequences. Also System tables are not selected. As TABLE_QUALIFIER the reltype
1493 attribute is returned and the REMARKS are undefined.
1495 =item B<foreign_key_info>
1497 $sth = $dbh->foreign_key_info( $pk_catalog, $pk_schema, $pk_table,
1498 $fk_catalog, $fk_schema, $fk_table );
1500 Supported by the driver as proposed by DBI. Unimplemented for Postgres
1501 servers before 7.3 (returns undef). Currently only returns information
1502 about first column of any multiple-column keys.
1506 @names = $dbh->tables;
1508 Supported by the driver as proposed by DBI. This method returns all tables and
1509 views which are owned by the current user. It does not select any indexes and
1510 sequences, or system tables.
1512 =item B<type_info_all>
1514 $type_info_all = $dbh->type_info_all;
1516 Supported by the driver as proposed by DBI. Only for SQL data-types and for
1517 frequently used data-types information is provided. The mapping between the
1518 PostgreSQL typename and the SQL92 data-type (if possible) has been done
1519 according to the following table:
1521 +---------------+------------------------------------+
1523 |---------------+------------------------------------|
1526 | bpchar | CHAR(n) |
1527 | varchar | VARCHAR(n) |
1532 | float4 | FLOAT(p) p<7=float4, p<16=float8 |
1540 | timespan | TINTERVAL |
1541 | timestamp | TIMESTAMP |
1542 +---------------+------------------------------------+
1544 For further details concerning the PostgreSQL specific data-types please read
1549 @type_info = $dbh->type_info($data_type);
1551 Implemented by DBI, no driver-specific impact.
1555 $sql = $dbh->quote($value, $data_type);
1557 This module implements its own quote method. In addition to the DBI method it
1558 also doubles the backslash, because PostgreSQL treats a backslash as an escape
1561 B<NOTE:> The undocumented (and invalid) support for the C<SQL_BINARY> data
1562 type is officially deprecated. Use C<PG_BYTEA> with C<bind_param()> instead:
1564 $rv = $sth->bind_param($param_num, $bind_value,
1565 { pg_type => DBD::Pg::PG_BYTEA });
1569 =head2 Database Handle Attributes
1573 =item B<AutoCommit> (boolean)
1575 Supported by the driver as proposed by DBI. According to the classification of
1576 DBI, PostgreSQL is a database, in which a transaction must be explicitly
1577 started. Without starting a transaction, every change to the database becomes
1578 immediately permanent. The default of AutoCommit is on, which corresponds to
1579 the default behavior of PostgreSQL. When setting AutoCommit to off, a
1580 transaction will be started and every commit or rollback will automatically
1581 start a new transaction. For details see the notes about B<Transactions>
1582 elsewhere in this document.
1584 =item B<Driver> (handle)
1586 Implemented by DBI, no driver-specific impact.
1588 =item B<Name> (string, read-only)
1590 The default method of DBI is overridden by a driver specific method, which
1591 returns only the database name. Anything else from the connection string is
1592 stripped off. Note, that here the method is read-only in contrast to the DBI
1595 =item B<RowCacheSize> (integer)
1597 Implemented by DBI, not used by the driver.
1599 =item B<pg_auto_escape> (boolean)
1601 PostgreSQL specific attribute. If true, then quotes and backslashes in all
1602 parameters will be escaped in the following way:
1604 escape quote with a quote (SQL)
1605 escape backslash with a backslash
1607 The default is on. Note, that PostgreSQL also accepts quotes, which are
1608 escaped by a backslash. Any other ASCII character can be used directly in a
1611 =item B<pg_enable_utf8> (boolean)
1613 PostgreSQL specific attribute. If true, then the utf8 flag will be
1614 turned for returned character data (if the data is valid utf8). For
1615 details about the utf8 flag, see L<Encode>. This is only relevant under
1616 perl 5.8 and higher.
1618 B<NB>: This attribute is experimental and may be subject to change.
1620 =item B<pg_INV_READ> (integer, read-only)
1622 Constant to be used for the mode in lo_creat and lo_open.
1624 =item B<pg_INV_WRITE> (integer, read-only)
1626 Constant to be used for the mode in lo_creat and lo_open.
1630 =head1 DBI STATEMENT HANDLE OBJECTS
1632 =head2 Statement Handle Methods
1638 $rv = $sth->bind_param($param_num, $bind_value, \%attr);
1640 Supported by the driver as proposed by DBI.
1642 B<NOTE:> The undocumented (and invalid) support for the C<SQL_BINARY>
1643 SQL type is officially deprecated. Use C<PG_BYTEA> instead:
1645 $rv = $sth->bind_param($param_num, $bind_value,
1646 { pg_type => DBD::Pg::PG_BYTEA });
1648 =item B<bind_param_inout>
1650 Not supported by this driver.
1654 $rv = $sth->execute(@bind_values);
1656 Supported by the driver as proposed by DBI. In addition to 'UPDATE', 'DELETE',
1657 'INSERT' statements, for which it returns always the number of affected rows,
1658 the execute method can also be used for 'SELECT ... INTO table' statements.
1660 =item B<fetchrow_arrayref>
1662 $ary_ref = $sth->fetchrow_arrayref;
1664 Supported by the driver as proposed by DBI.
1666 =item B<fetchrow_array>
1668 @ary = $sth->fetchrow_array;
1670 Supported by the driver as proposed by DBI.
1672 =item B<fetchrow_hashref>
1674 $hash_ref = $sth->fetchrow_hashref;
1676 Supported by the driver as proposed by DBI.
1678 =item B<fetchall_arrayref>
1680 $tbl_ary_ref = $sth->fetchall_arrayref;
1682 Implemented by DBI, no driver-specific impact.
1688 Supported by the driver as proposed by DBI.
1694 Supported by the driver as proposed by DBI. In contrast to many other drivers
1695 the number of rows is available immediately after executing the statement.
1699 $rc = $sth->bind_col($column_number, \$var_to_bind, \%attr);
1701 Supported by the driver as proposed by DBI.
1703 =item B<bind_columns>
1705 $rc = $sth->bind_columns(\%attr, @list_of_refs_to_vars_to_bind);
1707 Supported by the driver as proposed by DBI.
1709 =item B<dump_results>
1711 $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
1713 Implemented by DBI, no driver-specific impact.
1717 $blob = $sth->blob_read($id, $offset, $len);
1719 Supported by this driver as proposed by DBI. Implemented by DBI but not
1720 documented, so this method might change.
1722 This method seems to be heavily influenced by the current implementation of
1723 blobs in Oracle. Nevertheless we try to be as compatible as possible. Whereas
1724 Oracle suffers from the limitation that blobs are related to tables and every
1725 table can have only one blob (data-type LONG), PostgreSQL handles its blobs
1726 independent of any table by using so called object identifiers. This explains
1727 why the blob_read method is blessed into the STATEMENT package and not part of
1728 the DATABASE package. Here the field parameter has been used to handle this
1729 object identifier. The offset and len parameter may be set to zero, in which
1730 case the driver fetches the whole blob at once.
1732 Starting with PostgreSQL-6.5 every access to a blob has to be put into a
1733 transaction. This holds even for a read-only access.
1735 See also the PostgreSQL-specific functions concerning blobs which are
1736 available via the func-interface.
1738 For further information and examples about blobs, please read the chapter
1739 about Large Objects in the PostgreSQL Programmer's Guide.
1743 =head2 Statement Handle Attributes
1747 =item B<NUM_OF_FIELDS> (integer, read-only)
1749 Implemented by DBI, no driver-specific impact.
1751 =item B<NUM_OF_PARAMS> (integer, read-only)
1753 Implemented by DBI, no driver-specific impact.
1755 =item B<NAME> (array-ref, read-only)
1757 Supported by the driver as proposed by DBI.
1759 =item B<NAME_lc> (array-ref, read-only)
1761 Implemented by DBI, no driver-specific impact.
1763 =item B<NAME_uc> (array-ref, read-only)
1765 Implemented by DBI, no driver-specific impact.
1767 =item B<TYPE> (array-ref, read-only)
1769 Supported by the driver as proposed by DBI, with the restriction, that the
1770 types are PostgreSQL specific data-types which do not correspond to
1771 international standards.
1773 =item B<PRECISION> (array-ref, read-only)
1775 Not supported by the driver.
1777 =item B<SCALE> (array-ref, read-only)
1779 Not supported by the driver.
1781 =item B<NULLABLE> (array-ref, read-only)
1783 Not supported by the driver.
1785 =item B<CursorName> (string, read-only)
1787 Not supported by the driver. See the note about B<Cursors> elsewhere in this
1790 =item B<Statement> (string, read-only)
1792 Supported by the driver as proposed by DBI.
1794 =item B<RowCache> (integer, read-only)
1796 Not supported by the driver.
1798 =item B<pg_size> (array-ref, read-only)
1800 PostgreSQL specific attribute. It returns a reference to an array of integer
1801 values for each column. The integer shows the size of the column in
1802 bytes. Variable length columns are indicated by -1.
1804 =item B<pg_type> (hash-ref, read-only)
1806 PostgreSQL specific attribute. It returns a reference to an array of strings
1807 for each column. The string shows the name of the data_type.
1809 =item B<pg_oid_status> (integer, read-only)
1811 PostgreSQL specific attribute. It returns the OID of the last INSERT command.
1813 =item B<pg_cmd_status> (integer, read-only)
1815 PostgreSQL specific attribute. It returns the type of the last
1816 command. Possible types are: INSERT, DELETE, UPDATE, SELECT.
1820 =head1 FURTHER INFORMATION
1824 The transaction behavior is now controlled with the attribute AutoCommit. For
1825 a complete definition of AutoCommit please refer to the DBI documentation.
1827 According to the DBI specification the default for AutoCommit is TRUE. In this
1828 mode, any change to the database becomes valid immediately. Any 'begin',
1829 'commit' or 'rollback' statement will be rejected.
1831 If AutoCommit is switched-off, immediately a transaction will be started by
1832 issuing a 'begin' statement. Any 'commit' or 'rollback' will start a new
1833 transaction. A disconnect will issue a 'rollback' statement.
1835 =head2 Large Objects
1837 The driver supports all large-objects related functions provided by libpq via
1838 the func-interface. Please note, that starting with PostgreSQL 6.5 any access
1839 to a large object - even read-only - has to be put into a transaction!
1843 Although PostgreSQL has a cursor concept, it has not been used in the current
1844 implementation. Cursors in PostgreSQL can only be used inside a transaction
1845 block. Because only one transaction block at a time is allowed, this would
1846 have implied the restriction, not to use any nested SELECT statements. Hence
1847 the execute method fetches all data at once into data structures located in
1848 the frontend application. This has to be considered when selecting large
1851 =head2 Data-Type bool
1853 The current implementation of PostgreSQL returns 't' for true and 'f' for
1854 false. From the Perl point of view a rather unfortunate choice. The DBD::Pg
1855 module translates the result for the data-type bool in a perl-ish like manner:
1856 'f' -> '0' and 't' -> '1'. This way the application does not have to check the
1857 database-specific returned values for the data-type bool, because Perl treats
1858 '0' as false and '1' as true.
1860 Boolean values can be passed to PostgreSQL as TRUE, 't', 'true', 'y', 'yes' or
1861 '1' for true and FALSE, 'f', 'false', 'n', 'no' or '0' for false.
1863 =head2 Schema support
1865 PostgreSQL version 7.3 introduced schema support. Note that the PostgreSQL
1866 schema concept may differ to that of other databases. Please refer to the
1867 PostgreSQL documentation for more details.
1869 Currently DBD::Pg does not provide explicit support for PostgreSQL schemas.
1870 However, schema functionality may be used without any restrictions by
1871 explicitly addressing schema objects, e.g.
1873 my $res = $dbh->selectall_arrayref("SELECT * FROM my_schema.my_table");
1875 or by manipulating the schema search path with SET search_path, e.g.
1877 $dbh->do("SET search_path TO my_schema, public");
1879 B<NOTE:> If you create an object with the same name as a PostgreSQL system
1880 object (as contained in the pg_catalog schema) and explicitly set the search
1881 path so that pg_catalog comes after the new object's schema, some DBD::Pg
1882 methods (particularly those querying PostgreSQL system objects) may fail.
1883 This problem should be fixed in a future release of DBD::Pg. Creating objects
1884 with the same name as system objects (or beginning with 'pg_') is not
1885 recommended practice and should be avoided in any case.
1893 DBI and DBD-Oracle by Tim Bunce (Tim.Bunce@ig.co.uk)
1895 DBD-Pg by Edmund Mergl (E.Mergl@bawue.de) and Jeffrey W. Baker
1896 (jwbaker@acm.org). By David Wheeler <david@wheeler.net>, Jason
1897 Stewart <jason@openinformatics.com> and Bruce Momjian
1898 <pgman@candle.pha.pa.us> after v1.13.
1900 Major parts of this package have been copied from DBI and DBD-Oracle.
1904 The DBD::Pg module is free software. You may distribute under the terms of
1905 either the GNU General Public License or the Artistic License, as specified in
1906 the Perl README file.
1908 =head1 ACKNOWLEDGMENTS
1910 See also B<DBI/ACKNOWLEDGMENTS>.