-
-# $Id: Pg.pm,v 1.1 2004-04-29 09:21:28 ivan Exp $
-#
-# Copyright (c) 1997,1998,1999,2000 Edmund Mergl
-# Copyright (c) 2002 Jeffrey W. Baker
-# Portions Copyright (c) 1994,1995,1996,1997 Tim Bunce
-#
-# You may distribute under the terms of either the GNU General Public
-# License or the Artistic License, as specified in the Perl README file.
-
-
-require 5.004;
-
-$DBD::Pg::VERSION = '1.22';
-
-{
- package DBD::Pg;
-
- use DBI ();
- use DynaLoader ();
- use Exporter ();
- @ISA = qw(DynaLoader Exporter);
-
- %EXPORT_TAGS = (
- pg_types => [ qw(
- PG_BOOL PG_BYTEA PG_CHAR PG_INT8 PG_INT2 PG_INT4 PG_TEXT PG_OID
- PG_FLOAT4 PG_FLOAT8 PG_ABSTIME PG_RELTIME PG_TINTERVAL PG_BPCHAR
- PG_VARCHAR PG_DATE PG_TIME PG_DATETIME PG_TIMESPAN PG_TIMESTAMP
- )]);
-
- Exporter::export_ok_tags('pg_types');
-
- require_version DBI 1.00;
-
- bootstrap DBD::Pg $VERSION;
-
- $err = 0; # holds error code for DBI::err
- $errstr = ""; # holds error string for DBI::errstr
- $drh = undef; # holds driver handle once initialized
-
- sub driver{
- return $drh if $drh;
- my($class, $attr) = @_;
-
- $class .= "::dr";
-
- # not a 'my' since we use it above to prevent multiple drivers
-
- $drh = DBI::_new_drh($class, {
- 'Name' => 'Pg',
- 'Version' => $VERSION,
- 'Err' => \$DBD::Pg::err,
- 'Errstr' => \$DBD::Pg::errstr,
- 'Attribution' => 'PostgreSQL DBD by Edmund Mergl',
- });
-
- $drh;
- }
-
- ## Used by both the dr and db packages
- sub pg_server_version {
- my $dbh = shift;
- return $dbh->{pg_server_version} if defined $dbh->{pg_server_version};
- my ($version) = $dbh->selectrow_array("SELECT version();");
- return 0 unless $version =~ /^PostgreSQL ([\d\.]+)/;
- $dbh{pg_server_version} = $1;
- return $dbh{pg_server_version};
- }
-
- sub pg_use_catalog {
- my $dbh = shift;
- my $version = DBD::Pg::pg_server_version($dbh);
- $version =~ /^(\d+\.\d+)/;
- return $1 < 7.3 ? "" : "pg_catalog.";
- }
-
- 1;
-}
-
-
-{ package DBD::Pg::dr; # ====== DRIVER ======
- use strict;
-
- sub data_sources {
- my $drh = shift;
- my $dbh = DBD::Pg::dr::connect($drh, 'dbname=template1') or return undef;
- $dbh->{AutoCommit} = 1;
- my $CATALOG = DBD::Pg::pg_use_catalog($dbh);
- my $sth = $dbh->prepare("SELECT datname FROM ${CATALOG}pg_database ORDER BY datname");
- $sth->execute or return undef;
- my (@sources, @datname);
- while (@datname = $sth->fetchrow_array) {
- push @sources, "dbi:Pg:dbname=$datname[0]";
- }
- $sth->finish;
- $dbh->disconnect;
- return @sources;
- }
-
-
- sub connect {
- my($drh, $dbname, $user, $auth)= @_;
-
- # create a 'blank' dbh
-
- my $Name = $dbname;
- $Name =~ s/^.*dbname\s*=\s*//;
- $Name =~ s/\s*;.*$//;
-
- $user = "" unless defined($user);
- $auth = "" unless defined($auth);
-
- $user = $ENV{DBI_USER} if $user eq "";
- $auth = $ENV{DBI_PASS} if $auth eq "";
-
- $user = "" unless defined($user);
- $auth = "" unless defined($auth);
-
- my($dbh) = DBI::_new_dbh($drh, {
- 'Name' => $Name,
- 'User' => $user, 'CURRENT_USER' => $user,
- });
-
- # Connect to the database..
- DBD::Pg::db::_login($dbh, $dbname, $user, $auth) or return undef;
-
- $dbh;
- }
-
-}
-
-
-{ package DBD::Pg::db; # ====== DATABASE ======
- use strict;
- use Carp ();
-
- sub prepare {
- my($dbh, $statement, @attribs)= @_;
-
- # create a 'blank' sth
-
- my $sth = DBI::_new_sth($dbh, {
- 'Statement' => $statement,
- });
-
- DBD::Pg::st::_prepare($sth, $statement, @attribs) or return undef;
-
- $sth;
- }
-
-
- sub ping {
- my($dbh) = @_;
-
- local $SIG{__WARN__} = sub { } if $dbh->{PrintError};
- local $dbh->{RaiseError} = 0 if $dbh->{RaiseError};
- my $ret = DBD::Pg::db::_ping($dbh);
-
- return $ret;
- }
-
- # Column expected in statement handle returned.
- # table_cat, table_schem, table_name, column_name, data_type, type_name,
- # column_size, buffer_length, DECIMAL_DIGITS, NUM_PREC_RADIX, NULLABLE,
- # REMARKS, COLUMN_DEF, SQL_DATA_TYPE, SQL_DATETIME_SUB, CHAR_OCTET_LENGTH,
- # ORDINAL_POSITION, IS_NULLABLE
- # The result set is ordered by TABLE_CAT, TABLE_SCHEM,
- # TABLE_NAME and ORDINAL_POSITION.
-
- sub column_info {
- my ($dbh) = shift;
- my @attrs = @_;
- # my ($dbh, $catalog, $schema, $table, $column) = @_;
- my $CATALOG = DBD::Pg::pg_use_catalog($dbh);
-
- my @wh = ();
- my @flds = qw/catname n.nspname c.relname a.attname/;
-
- for my $idx (0 .. $#attrs) {
- next if ($flds[$idx] eq 'catname'); # Skip catalog
- if(defined $attrs[$idx] and length $attrs[$idx]) {
- # Insure that the value is enclosed in single quotes.
- $attrs[$idx] =~ s/^'?(\w+)'?$/'$1'/;
- if ($attrs[$idx] =~ m/[,%]/) {
- # contains a meta character.
- push( @wh, q{( } . join ( " OR "
- , map { m/\%/
- ? qq{$flds[$idx] ILIKE $_ }
- : qq{$flds[$idx] = $_ }
- } (split /,/, $attrs[$idx]) )
- . q{ )}
- );
- }
- else {
- push( @wh, qq{$flds[$idx] = $attrs[$idx]} );
- }
- }
- }
-
- my $wh = ""; # ();
- $wh = join( " AND ", '', @wh ) if (@wh);
- my $version = DBD::Pg::pg_server_version($dbh);
- $version =~ /^(\d+\.\d+)/;
- $version = $1;
- my $showschema = $version < 7.3 ? "NULL::text" : "n.nspname";
- my $schemajoin = $version < 7.3 ? "" : "LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)";
- my $col_info_sql = qq{
- SELECT
- NULL::text AS "TABLE_CAT"
- , $showschema AS "TABLE_SCHEM"
- , c.relname AS "TABLE_NAME"
- , a.attname AS "COLUMN_NAME"
- , t.typname AS "DATA_TYPE"
- , NULL::text AS "TYPE_NAME"
- , a.attlen AS "COLUMN_SIZE"
- , NULL::text AS "BUFFER_LENGTH"
- , NULL::text AS "DECIMAL_DIGITS"
- , NULL::text AS "NUM_PREC_RADIX"
- , a.attnotnull AS "NULLABLE"
- , NULL::text AS "REMARKS"
- , a.atthasdef AS "COLUMN_DEF"
- , NULL::text AS "SQL_DATA_TYPE"
- , NULL::text AS "SQL_DATETIME_SUB"
- , NULL::text AS "CHAR_OCTET_LENGTH"
- , a.attnum AS "ORDINAL_POSITION"
- , a.attnotnull AS "IS_NULLABLE"
- , a.atttypmod as atttypmod
- , a.attnotnull as attnotnull
- , a.atthasdef as atthasdef
- , a.attnum as attnum
- FROM
- ${CATALOG}pg_attribute a
- , ${CATALOG}pg_type t
- , ${CATALOG}pg_class c
- $schemajoin
- WHERE
- a.attrelid = c.oid
- AND a.attnum >= 0
- AND t.oid = a.atttypid
- AND c.relkind in ('r','v')
- $wh
- ORDER BY 2, 3, 4
- };
-
- my $sth = $dbh->prepare( $col_info_sql ) or return undef;
- $sth->execute();
-
- return $sth;
- }
-
- sub primary_key_info {
- my $dbh = shift;
- my ($catalog, $schema, $table) = @_;
- my @attrs = @_;
- my $CATALOG = DBD::Pg::pg_use_catalog($dbh);
-
- # TABLE_CAT:, TABLE_SCHEM:, TABLE_NAME:, COLUMN_NAME:, KEY_SEQ:
- # , PK_NAME:
-
- my @wh = (); my @dat = (); # Used to hold data for the attributes.
-
- my $version = DBD::Pg::pg_server_version($dbh);
- $version =~ /^(\d+\.\d+)/;
- $version = $1;
-
- my @flds = qw/catname u.usename bc.relname/;
- $flds[1] = 'n.nspname' unless ($version < 7.3);
-
- for my $idx (0 .. $#attrs) {
- next if ($flds[$idx] eq 'catname'); # Skip catalog
- if(defined $attrs[$idx] and length $attrs[$idx]) {
- if ($attrs[$idx] =~ m/[,%_?]/) {
- # contains a meta character.
- push( @wh, q{( } . join ( " OR "
- , map { push(@dat, $_);
- m/[%_?]/
- ? qq{$flds[$idx] iLIKE ? }
- : qq{$flds[$idx] = ? }
- } (split /,/, $attrs[$idx]) )
- . q{ )}
- );
- }
- else {
- push( @dat, $attrs[$idx] );
- push( @wh, qq{$flds[$idx] = ? } );
- }
- }
- }
-
- my $wh = '';
- $wh = join( " AND ", '', @wh ) if (@wh);
-
- # Base primary key selection query borrowed from phpPgAdmin.
- my $showschema = $version < 7.3 ? "NULL::text" : "n.nspname";
- my $schemajoin = $version < 7.3 ? "" : "LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = bc.relnamespace)";
- my $pri_key_sql = qq{
- SELECT
- NULL::text AS "TABLE_CAT"
- , $showschema AS "TABLE_SCHEM"
- , bc.relname AS "TABLE_NAME"
- , a.attname AS "COLUMN_NAME"
- , a.attnum AS "KEY_SEQ"
- , ic.relname AS "PK_NAME"
- FROM
- ${CATALOG}pg_index i
- , ${CATALOG}pg_attribute a
- , ${CATALOG}pg_class ic
- , ${CATALOG}pg_class bc
- $schemajoin
- WHERE
- i.indrelid = bc.oid
- AND i.indexrelid = ic.oid
- AND
- (
- i.indkey[0] = a.attnum
- OR
- i.indkey[1] = a.attnum
- OR
- i.indkey[2] = a.attnum
- OR
- i.indkey[3] = a.attnum
- OR
- i.indkey[4] = a.attnum
- OR
- i.indkey[5] = a.attnum
- OR
- i.indkey[6] = a.attnum
- OR
- i.indkey[7] = a.attnum
- OR
- i.indkey[8] = a.attnum
- OR
- i.indkey[9] = a.attnum
- OR
- i.indkey[10] = a.attnum
- OR
- i.indkey[11] = a.attnum
- OR
- i.indkey[12] = a.attnum
- )
- AND a.attrelid = bc.oid
- AND i.indproc = '0'::oid
- AND i.indisprimary = 't'
- $wh
- ORDER BY 2, 3, 5
- };
-
- my $sth = $dbh->prepare( $pri_key_sql ) or return undef;
- $sth->execute(@dat);
-
- return $sth;
- }
-
- sub foreign_key_info {
- # todo: verify schema work as expected
- # add code to handle multiple-column keys correctly
- # return something nicer for pre-7.3?
- # try to clean up SQL, perl code
- # create a test script?
-
- my $dbh = shift;
- my ($pk_catalog, $pk_schema, $pk_table,
- $fk_catalog, $fk_schema, $fk_table) = @_;
-
- # this query doesn't work for Postgres before 7.3
- my $version = $dbh->pg_server_version;
- $version =~ /^(\d+)\.(\d)/;
- return undef if ($1.$2 < 73);
-
- # Used to hold data for the attributes.
- my @dat = ();
-
- # SQL to find primary/unique keys of a table
- my $pkey_sql = qq{
- SELECT
- NULL::text AS PKTABLE_CAT,
- pknam.nspname AS PKTABLE_SCHEM,
- pkc.relname AS PKTABLE_NAME,
- pka.attname AS PKCOLUMN_NAME,
- NULL::text AS FKTABLE_CAT,
- NULL::text AS FKTABLE_SCHEM,
- NULL::text AS FKTABLE_NAME,
- NULL::text AS FKCOLUMN_NAME,
- pkcon.conkey[1] AS KEY_SEQ,
- CASE
- WHEN pkcon.confupdtype = 'c' THEN 0
- WHEN pkcon.confupdtype = 'r' THEN 1
- WHEN pkcon.confupdtype = 'n' THEN 2
- WHEN pkcon.confupdtype = 'a' THEN 3
- WHEN pkcon.confupdtype = 'd' THEN 4
- END AS UPDATE_RULE,
- CASE
- WHEN pkcon.confdeltype = 'c' THEN 0
- WHEN pkcon.confdeltype = 'r' THEN 1
- WHEN pkcon.confdeltype = 'n' THEN 2
- WHEN pkcon.confdeltype = 'a' THEN 3
- WHEN pkcon.confdeltype = 'd' THEN 4
- END AS DELETE_RULE,
- NULL::text AS FK_NAME,
- pkcon.conname AS PK_NAME,
- CASE
- WHEN pkcon.condeferrable = 'f' THEN 7
- WHEN pkcon.condeferred = 't' THEN 6
- WHEN pkcon.condeferred = 'f' THEN 5
- END AS DEFERRABILITY,
- CASE
- WHEN pkcon.contype = 'p' THEN 'PRIMARY'
- WHEN pkcon.contype = 'u' THEN 'UNIQUE'
- END AS UNIQUE_OR_PRIMARY
- FROM
- pg_constraint AS pkcon
- JOIN
- pg_class pkc ON pkc.oid=pkcon.conrelid
- JOIN
- pg_namespace pknam ON pkcon.connamespace=pknam.oid
- JOIN
- pg_attribute pka ON pka.attnum=pkcon.conkey[1] AND pka.attrelid=pkc.oid
- };
-
- # SQL to find foreign keys of a table
- my $fkey_sql = qq{
- SELECT
- NULL::text AS PKTABLE_CAT,
- pknam.nspname AS PKTABLE_SCHEM,
- pkc.relname AS PKTABLE_NAME,
- pka.attname AS PKCOLUMN_NAME,
- NULL::text AS FKTABLE_CAT,
- fknam.nspname AS FKTABLE_SCHEM,
- fkc.relname AS FKTABLE_NAME,
- fka.attname AS FKCOLUMN_NAME,
- fkcon.conkey[1] AS KEY_SEQ,
- CASE
- WHEN fkcon.confupdtype = 'c' THEN 0
- WHEN fkcon.confupdtype = 'r' THEN 1
- WHEN fkcon.confupdtype = 'n' THEN 2
- WHEN fkcon.confupdtype = 'a' THEN 3
- WHEN fkcon.confupdtype = 'd' THEN 4
- END AS UPDATE_RULE,
- CASE
- WHEN fkcon.confdeltype = 'c' THEN 0
- WHEN fkcon.confdeltype = 'r' THEN 1
- WHEN fkcon.confdeltype = 'n' THEN 2
- WHEN fkcon.confdeltype = 'a' THEN 3
- WHEN fkcon.confdeltype = 'd' THEN 4
- END AS DELETE_RULE,
- fkcon.conname AS FK_NAME,
- pkcon.conname AS PK_NAME,
- CASE
- WHEN fkcon.condeferrable = 'f' THEN 7
- WHEN fkcon.condeferred = 't' THEN 6
- WHEN fkcon.condeferred = 'f' THEN 5
- END AS DEFERRABILITY,
- CASE
- WHEN pkcon.contype = 'p' THEN 'PRIMARY'
- WHEN pkcon.contype = 'u' THEN 'UNIQUE'
- END AS UNIQUE_OR_PRIMARY
- FROM
- pg_constraint AS fkcon
- JOIN
- pg_constraint AS pkcon ON fkcon.confrelid=pkcon.conrelid
- AND fkcon.confkey=pkcon.conkey
- JOIN
- pg_class fkc ON fkc.oid=fkcon.conrelid
- JOIN
- pg_class pkc ON pkc.oid=fkcon.confrelid
- JOIN
- pg_namespace pknam ON pkcon.connamespace=pknam.oid
- JOIN
- pg_namespace fknam ON fkcon.connamespace=fknam.oid
- JOIN
- pg_attribute fka ON fka.attnum=fkcon.conkey[1] AND fka.attrelid=fkc.oid
- JOIN
- pg_attribute pka ON pka.attnum=pkcon.conkey[1] AND pka.attrelid=pkc.oid
- };
-
- # if schema are provided, use this SQL
- my $pk_schema_sql = " AND pknam.nspname = ? ";
- my $fk_schema_sql = " AND fknam.nspname = ? ";
-
- my $key_sql;
-
- # if $fk_table: generate SQL stub, which will be same
- # whether or not $pk_table supplied
- if ($fk_table)
- {
- $key_sql = $fkey_sql . qq{
- WHERE
- fkc.relname = ?
- };
- push @dat, $fk_table;
-
- if ($fk_schema)
- {
- $key_sql .= $fk_schema_sql;
- push @dat,$fk_schema;
- }
- }
-
- # if $fk_table and $pk_table: (defined by DBI, not SQL/CLI)
- # return foreign key of $fk_table that refers to $pk_table
- # (if any)
- if ($pk_table and $fk_table)
- {
- $key_sql .= qq{
- AND
- pkc.relname = ?
- };
- push @dat, $pk_table;
-
- if ($pk_schema)
- {
- $key_sql .= $pk_schema_sql;
- push @dat,$pk_schema;
- }
- }
-
- # if $fk_table but no $pk_table:
- # return all foreign keys of $fk_table, and all
- # primary keys of tables to which $fk_table refers
- if (!$pk_table and $fk_table)
- {
- # find primary/unique keys referenced by $fk_table
- # (this one is a little tricky)
- $key_sql .= ' UNION ' . $pkey_sql . qq{
- WHERE
- pkcon.conname IN
- (
- SELECT
- pkcon.conname
- FROM
- pg_constraint AS fkcon
- JOIN
- pg_constraint AS pkcon ON fkcon.confrelid=pkcon.conrelid AND
- fkcon.confkey=pkcon.conkey
- JOIN
- pg_class fkc ON fkc.oid=fkcon.conrelid
- WHERE
- fkc.relname = ?
- )
- };
- push @dat, $fk_table;
-
- if ($fk_schema)
- {
- $key_sql .= $pk_schema_sql;
- push @dat,$fk_schema;
- }
- }
-
- # if $pk_table but no $fk_table:
- # return primary key of $pk_table and all foreign keys
- # that reference $pk_table
- # question: what about unique keys?
- # (DBI and SQL/CLI both state to omit unique keys)
-
- if ($pk_table and !$fk_table)
- {
- # find primary key (only!) of $pk_table
- $key_sql = $pkey_sql . qq{
- WHERE
- pkc.relname = ?
- AND
- pkcon.contype = 'p'
- };
- @dat = ($pk_table);
-
- if ($pk_schema)
- {
- $key_sql .= $pk_schema_sql;
- push @dat,$pk_schema;
- }
-
- # find all foreign keys that reference $pk_table
- $key_sql .= 'UNION ' . $fkey_sql . qq{
- WHERE
- pkc.relname = ?
- AND
- pkcon.contype = 'p'
- };
- push @dat, $pk_table;
-
- if ($pk_schema)
- {
- $key_sql .= $fk_schema_sql;
- push @dat,$pk_schema;
- }
- }
-
- return undef unless $key_sql;
- my $sth = $dbh->prepare( $key_sql ) or
- return undef;
- $sth->execute(@dat);
-
- return $sth;
- }
-
-
- sub table_info { # DBI spec: TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS
- my $dbh = shift;
- my ($catalog, $schema, $table, $type) = @_;
- my @attrs = @_;
-
- my $tbl_sql = ();
-
- my $version = DBD::Pg::pg_server_version($dbh);
- $version =~ /^(\d+\.\d+)/;
- $version = $1;
- my $CATALOG = DBD::Pg::pg_use_catalog($dbh);
-
- if ( # Rules 19a
- (defined $catalog and $catalog eq '%')
- and (defined $schema and $schema eq '')
- and (defined $table and $table eq '')
- ) {
- $tbl_sql = q{
- SELECT
- NULL::text AS "TABLE_CAT"
- , NULL::text AS "TABLE_SCHEM"
- , NULL::text AS "TABLE_NAME"
- , NULL::text AS "TABLE_TYPE"
- , NULL::text AS "REMARKS"
- };
- }
- elsif (# Rules 19b
- (defined $catalog and $catalog eq '')
- and (defined $schema and $schema eq '%')
- and (defined $table and $table eq '')
- ) {
- $tbl_sql = ($version < 7.3) ? q{
- SELECT
- NULL::text AS "TABLE_CAT"
- , NULL::text AS "TABLE_SCHEM"
- , NULL::text AS "TABLE_NAME"
- , NULL::text AS "TABLE_TYPE"
- , NULL::text AS "REMARKS"
- } : q{
- SELECT
- NULL::text AS "TABLE_CAT"
- , n.nspname AS "TABLE_SCHEM"
- , NULL::text AS "TABLE_NAME"
- , NULL::text AS "TABLE_TYPE"
- , NULL::text AS "REMARKS"
- FROM pg_catalog.pg_namespace n
- ORDER BY 1
- };
- }
- elsif (# Rules 19c
- (defined $catalog and $catalog eq '')
- and (defined $schema and $schema eq '')
- and (defined $table and $table eq '')
- and (defined $type and $type eq '%')
- ) {
- # From the postgresql 7.2.1 manual 3.5 pg_class
- # 'r' = ordinary table
- #, 'i' = index
- #, 'S' = sequence
- #, 'v' = view
- #, 's' = special
- #, 't' = secondary TOAST table
- $tbl_sql = q{
- SELECT
- NULL::text AS "TABLE_CAT"
- , NULL::text AS "TABLE_SCHEM"
- , NULL::text AS "TABLE_NAME"
- , 'table' AS "TABLE_TYPE"
- , 'ordinary table - r' AS "REMARKS"
- union
- SELECT
- NULL::text AS "TABLE_CAT"
- , NULL::text AS "TABLE_SCHEM"
- , NULL::text AS "TABLE_NAME"
- , 'index' AS "TABLE_TYPE"
- , 'index - i' AS "REMARKS"
- union
- SELECT
- NULL::text AS "TABLE_CAT"
- , NULL::text AS "TABLE_SCHEM"
- , NULL::text AS "TABLE_NAME"
- , 'sequence' AS "TABLE_TYPE"
- , 'sequence - S' AS "REMARKS"
- union
- SELECT
- NULL::text AS "TABLE_CAT"
- , NULL::text AS "TABLE_SCHEM"
- , NULL::text AS "TABLE_NAME"
- , 'view' AS "TABLE_TYPE"
- , 'view - v' AS "REMARKS"
- union
- SELECT
- NULL::text AS "TABLE_CAT"
- , NULL::text AS "TABLE_SCHEM"
- , NULL::text AS "TABLE_NAME"
- , 'special' AS "TABLE_TYPE"
- , 'special - s' AS "REMARKS"
- union
- SELECT
- NULL::text AS "TABLE_CAT"
- , NULL::text AS "TABLE_SCHEM"
- , NULL::text AS "TABLE_NAME"
- , 'secondary' AS "TABLE_TYPE"
- , 'secondary TOAST table - t' AS "REMARKS"
- };
- }
- else {
- # Default SQL
- my $showschema = $version < 7.3 ? "NULL::text" : "n.nspname";
- my $schemajoin = $version < 7.3 ? "" : "LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)";
- my $schemacase = $version < 7.3 ? "CASE WHEN c.relname ~ '^pg_' THEN 'SYSTEM TABLE' ELSE 'TABLE' END" :
- "CASE WHEN n.nspname ~ '^pg_' THEN 'SYSTEM TABLE' ELSE 'TABLE' END";
- $tbl_sql = qq{
- SELECT NULL::text AS "TABLE_CAT"
- , $showschema AS "TABLE_SCHEM"
- , c.relname AS "TABLE_NAME"
- , CASE
- WHEN c.relkind = 'v' THEN 'VIEW'
- ELSE $schemacase
- END AS "TABLE_TYPE"
- , d.description AS "REMARKS"
- FROM ${CATALOG}pg_user AS u
- , ${CATALOG}pg_class AS c
- LEFT JOIN
- ${CATALOG}pg_description AS d
- ON (c.relfilenode = d.objoid AND d.objsubid = 0)
- $schemajoin
- WHERE
- ((c.relkind = 'r'
- AND c.relhasrules = FALSE) OR
- (c.relkind = 'v'
- AND c.relhasrules = TRUE))
- AND c.relname !~ '^xin[vx][0-9]+'
- AND c.relowner = u.usesysid
- ORDER BY 1, 2, 3
- };
-
- # Did we receive any arguments?
- if (@attrs) {
- my @wh = ();
- my @flds = qw/catname n.nspname c.relname c.relkind/;
-
- for my $idx (0 .. $#attrs) {
- next if ($flds[$idx] eq 'catname'); # Skip catalog
- if(defined $attrs[$idx] and length $attrs[$idx]) {
- # Change the "name" of the types to the real value.
- if ($flds[$idx] =~ m/relkind/) {
- $attrs[$idx] =~ s/^\'?table\'?/'r'/i;
- $attrs[$idx] =~ s/^\'?index\'?/'i'/i;
- $attrs[$idx] =~ s/^\'?sequence\'?/'S'/i;
- $attrs[$idx] =~ s/^\'?view\'?/'v'/i;
- $attrs[$idx] =~ s/^\'?special\'?/'s'/i;
- $attrs[$idx] =~ s/^\'?secondary\'?/'t'/i;
- }
- # Insure that the value is enclosed in single quotes.
- $attrs[$idx] =~ s/^'?(\w+)'?$/'$1'/;
- if ($attrs[$idx] =~ m/[,%]/) {
- # contains a meta character.
- push( @wh, q{( } . join ( " OR "
- , map { m/\%/
- ? qq{$flds[$idx] LIKE $_ }
- : qq{$flds[$idx] = $_ }
- } (split /,/, $attrs[$idx]) )
- . q{ )}
- );
- }
- else {
- push( @wh, qq{$flds[$idx] = $attrs[$idx]} );
- }
- }
- }
-
- my $wh = ();
- if (@wh) {
- $wh = join( " AND ",'', @wh );
- $tbl_sql = qq{
- SELECT NULL::text AS "TABLE_CAT"
- , $showschema AS "TABLE_SCHEM"
- , c.relname AS "TABLE_NAME"
- , CASE
- WHEN c.relkind = 'r' THEN
- CASE WHEN n.nspname ~ '^pg_' THEN 'SYSTEM TABLE' ELSE 'TABLE' END
- WHEN c.relkind = 'v' THEN 'VIEW'
- WHEN c.relkind = 'i' THEN 'INDEX'
- WHEN c.relkind = 'S' THEN 'SEQUENCE'
- WHEN c.relkind = 's' THEN 'SPECIAL'
- WHEN c.relkind = 't' THEN 'SECONDARY'
- ELSE 'UNKNOWN'
- END AS "TABLE_TYPE"
- , d.description AS "REMARKS"
- FROM ${CATALOG}pg_class AS c
- LEFT JOIN
- ${CATALOG}pg_description AS d
- ON (c.relfilenode = d.objoid AND d.objsubid = 0)
- $schemajoin
- WHERE
- c.relname !~ '^xin[vx][0-9]+'
- $wh
- ORDER BY 2, 3
- };
- }
- }
- }
-
- my $sth = $dbh->prepare( $tbl_sql ) or return undef;
- $sth->execute();
-
- return $sth;
- }
-
-
- sub tables {
- my($dbh) = @_;
- my $version = DBD::Pg::pg_server_version($dbh);
- $version =~ /^(\d+\.\d+)/;
- $version = $1;
- my $SQL = ($version < 7.3) ?
- "SELECT relname AS \"TABLE_NAME\"
- FROM pg_class
- WHERE relkind = 'r'
- AND relname !~ '^pg_'
- AND relname !~ '^xin[vx][0-9]+'
- ORDER BY 1" :
- "SELECT n.nspname AS \"SCHEMA_NAME\", c.relname AS \"TABLE_NAME\"
- FROM pg_catalog.pg_class c
- LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
- WHERE c.relkind = 'r'
- AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
- AND pg_catalog.pg_table_is_visible(c.oid)
- ORDER BY 1,2";
- my $sth = $dbh->prepare($SQL) or return undef;
- $sth->execute or return undef;
- my (@tables, @relname);
- while (@relname = $sth->fetchrow_array) {
- push @tables, $version < 7.3 ? $relname[0] : "$relname[0].$relname[1]";
- }
- $sth->finish;
-
- return @tables;
- }
-
-
- sub table_attributes {
- my ($dbh, $table) = @_;
- my $CATALOG = DBD::Pg::pg_use_catalog($dbh);
- my $result = [];
- my $attrs = $dbh->selectall_arrayref(
- "select a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum
- from ${CATALOG}pg_attribute a,
- ${CATALOG}pg_class c,
- ${CATALOG}pg_type t
- where c.relname = ?
- and a.attrelid = c.oid
- and a.attnum >= 0
- and t.oid = a.atttypid
- order by 1
- ", undef, $table);
-
- return $result unless scalar(@$attrs);
-
- # Select the array value for tables primary key.
- my $pk_key_sql = qq{SELECT pg_index.indkey
- FROM ${CATALOG}pg_class, ${CATALOG}pg_index
- WHERE
- pg_class.oid = pg_index.indrelid
- AND pg_class.relname = '$table'
- AND pg_index.indisprimary = 't'
- };
- # Expand this (returned as a string) a real array.
- my @pk = ();
- my $pkeys = $dbh->selectrow_array( $pk_key_sql );
- if (defined $pkeys) {
- foreach (split( /\s+/, $pkeys))
- {
- push @pk, $_;
- }
- }
- my $pk_bt =
- (@pk) ? "AND pg_attribute.attnum in (" . join ( ", ", @pk ) . ")"
- : "";
-
- # Get the primary key
- my $pri_key = $dbh->selectcol_arrayref("SELECT pg_attribute.attname
- FROM ${CATALOG}pg_class, ${CATALOG}pg_attribute, ${CATALOG}pg_index
- WHERE pg_class.oid = pg_attribute.attrelid
- AND pg_class.oid = pg_index.indrelid
- $pk_bt
- AND pg_index.indisprimary = 't'
- AND pg_class.relname = ?
- ORDER BY pg_attribute.attnum
- ", undef, $table );
- $pri_key = [] unless $pri_key;
-
- foreach my $attr (reverse @$attrs) {
- my ($col_name, $col_type, $size, $mod, $notnull, $hasdef, $attnum) = @$attr;
- my $col_size = do {
- if ($size > 0) {
- $size;
- } elsif ($mod > 0xffff) {
- my $prec = ($mod & 0xffff) - 4;
- $mod >>= 16;
- my $dig = $mod;
- $dig;
- } elsif ($mod >= 4) {
- $mod - 4;
- } else {
- $mod;
- }
- };
-
- # Get the default value, if any
- my ($default) = $dbh->selectrow_array("SELECT adsrc FROM ${CATALOG}pg_attrdef WHERE adnum = $attnum") if -1 == $attnum;
- $default = '' unless $default;
-
- # Test for any constraints
- # Note: as of PostgreSQL 7.3 pg_relcheck has been replaced
- # by pg_constraint. To maintain compatibility, check
- # version number and execute appropriate query.
-
- my $version = pg_server_version( $dbh );
-
- my $con_query = $version < 7.3
- ? "SELECT rcsrc FROM pg_relcheck WHERE rcname = '${table}_$col_name'"
- : "SELECT consrc FROM pg_catalog.pg_constraint WHERE contype = 'c' AND conname = '${table}_$col_name'";
- my ($constraint) = $dbh->selectrow_array($con_query);
- $constraint = '' unless $constraint;
-
- # Check to see if this is the primary key
- my $is_primary_key = scalar(grep { /^$col_name$/i } @$pri_key) ? 1 : 0;
-
- push @$result,
- { NAME => $col_name,
- TYPE => $col_type,
- SIZE => $col_size,
- NOTNULL => $notnull,
- DEFAULT => $default,
- CONSTRAINT => $constraint,
- PRIMARY_KEY => $is_primary_key,
- };
- }
-
- return $result;
- }
-
-
- sub type_info_all {
- my ($dbh) = @_;
-
- #my $names = {
- # TYPE_NAME => 0,
- # DATA_TYPE => 1,
- # PRECISION => 2,
- # LITERAL_PREFIX => 3,
- # LITERAL_SUFFIX => 4,
- # CREATE_PARAMS => 5,
- # NULLABLE => 6,
- # CASE_SENSITIVE => 7,
- # SEARCHABLE => 8,
- # UNSIGNED_ATTRIBUTE => 9,
- # MONEY =>10,
- # AUTO_INCREMENT =>11,
- # LOCAL_TYPE_NAME =>12,
- # MINIMUM_SCALE =>13,
- # MAXIMUM_SCALE =>14,
- # };
-
- my $names = {
- TYPE_NAME => 0,
- DATA_TYPE => 1,
- COLUMN_SIZE => 2, # was PRECISION originally
- LITERAL_PREFIX => 3,
- LITERAL_SUFFIX => 4,
- CREATE_PARAMS => 5,
- NULLABLE => 6,
- CASE_SENSITIVE => 7,
- SEARCHABLE => 8,
- UNSIGNED_ATTRIBUTE=> 9,
- FIXED_PREC_SCALE => 10, # was MONEY originally
- AUTO_UNIQUE_VALUE => 11, # was AUTO_INCREMENT originally
- LOCAL_TYPE_NAME => 12,
- MINIMUM_SCALE => 13,
- MAXIMUM_SCALE => 14,
- NUM_PREC_RADIX => 15,
- };
-
-
- # typname |typlen|typprtlen| SQL92
- # --------------+------+---------+ -------
- # bool | 1| 1| BOOLEAN
- # text | -1| -1| like VARCHAR, but automatic storage allocation
- # bpchar | -1| -1| CHARACTER(n) bp=blank padded
- # varchar | -1| -1| VARCHAR(n)
- # int2 | 2| 5| SMALLINT
- # int4 | 4| 10| INTEGER
- # int8 | 8| 20| /
- # money | 4| 24| /
- # float4 | 4| 12| FLOAT(p) for p<7=float4, for p<16=float8
- # float8 | 8| 24| REAL
- # abstime | 4| 20| /
- # reltime | 4| 20| /
- # tinterval | 12| 47| /
- # date | 4| 10| /
- # time | 8| 16| /
- # datetime | 8| 47| /
- # timespan | 12| 47| INTERVAL
- # timestamp | 4| 19| TIMESTAMP
- # --------------+------+---------+
-
- # DBI type definitions / PostgreSQL definitions # type needs to be DBI-specific (not pg_type)
- #
- # SQL_ALL_TYPES 0
- # SQL_CHAR 1 1042 bpchar
- # SQL_NUMERIC 2 700 float4
- # SQL_DECIMAL 3 700 float4
- # SQL_INTEGER 4 23 int4
- # SQL_SMALLINT 5 21 int2
- # SQL_FLOAT 6 700 float4
- # SQL_REAL 7 701 float8
- # SQL_DOUBLE 8 20 int8
- # SQL_DATE 9 1082 date
- # SQL_TIME 10 1083 time
- # SQL_TIMESTAMP 11 1296 timestamp
- # SQL_VARCHAR 12 1043 varchar
-
- my $ti = [
- $names,
- # name type prec prefix suffix create params null case se unsign mon incr local min max
- #
- [ 'bytea', -2, 4096, '\'', '\'', undef, 1, '1', 3, undef, '0', '0', 'BYTEA', undef, undef, undef ],
- [ 'bool', 0, 1, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'BOOLEAN', undef, undef, undef ],
- [ 'int8', 8, 20, undef, undef, undef, 1, '0', 2, '0', '0', '0', 'LONGINT', undef, undef, undef ],
- [ 'int2', 5, 5, undef, undef, undef, 1, '0', 2, '0', '0', '0', 'SMALLINT', undef, undef, undef ],
- [ 'int4', 4, 10, undef, undef, undef, 1, '0', 2, '0', '0', '0', 'INTEGER', undef, undef, undef ],
- [ 'text', 12, 4096, '\'', '\'', undef, 1, '1', 3, undef, '0', '0', 'TEXT', undef, undef, undef ],
- [ 'float4', 6, 12, undef, undef, 'precision', 1, '0', 2, '0', '0', '0', 'FLOAT', undef, undef, undef ],
- [ 'float8', 7, 24, undef, undef, 'precision', 1, '0', 2, '0', '0', '0', 'REAL', undef, undef, undef ],
- [ 'abstime', 10, 20, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'ABSTIME', undef, undef, undef ],
- [ 'reltime', 10, 20, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'RELTIME', undef, undef, undef ],
- [ 'tinterval', 11, 47, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'TINTERVAL', undef, undef, undef ],
- [ 'money', 0, 24, undef, undef, undef, 1, '0', 2, undef, '1', '0', 'MONEY', undef, undef, undef ],
- [ 'bpchar', 1, 4096, '\'', '\'', 'max length', 1, '1', 3, undef, '0', '0', 'CHARACTER', undef, undef, undef ],
- [ 'bpchar', 12, 4096, '\'', '\'', 'max length', 1, '1', 3, undef, '0', '0', 'CHARACTER', undef, undef, undef ],
- [ 'varchar', 12, 4096, '\'', '\'', 'max length', 1, '1', 3, undef, '0', '0', 'VARCHAR', undef, undef, undef ],
- [ 'date', 9, 10, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'DATE', undef, undef, undef ],
- [ 'time', 10, 16, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'TIME', undef, undef, undef ],
- [ 'datetime', 11, 47, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'DATETIME', undef, undef, undef ],
- [ 'timespan', 11, 47, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'INTERVAL', undef, undef, undef ],
- [ 'timestamp', 10, 19, '\'', '\'', undef, 1, '0', 2, undef, '0', '0', 'TIMESTAMP', undef, undef, undef ]
- #
- # intentionally omitted: char, all geometric types, all array types
- ];
- return $ti;
- }
-
-
- # Characters that need to be escaped by quote().
- my %esc = ( "'" => '\\047', # '\\' . sprintf("%03o", ord("'")), # ISO SQL 2
- '\\' => '\\134', # '\\' . sprintf("%03o", ord("\\")),
- );
-
- # Set up lookup for SQL types we don't want to escape.
- my %no_escape = map { $_ => 1 }
- DBI::SQL_INTEGER, DBI::SQL_SMALLINT, DBI::SQL_DECIMAL,
- DBI::SQL_FLOAT, DBI::SQL_REAL, DBI::SQL_DOUBLE, DBI::SQL_NUMERIC;
-
- sub quote {
- my ($dbh, $str, $data_type) = @_;
- return "NULL" unless defined $str;
- return $str if $data_type && $no_escape{$data_type};
-
- $dbh->DBI::set_err(1, "Use of SQL_BINARY invalid in quote()")
- if $data_type && $data_type == DBI::SQL_BINARY;
-
- $str =~ s/(['\\\0])/$esc{$1}/g;
- return "'$str'";
- }
-
-} # end of package DBD::Pg::db
-
-{ package DBD::Pg::st; # ====== STATEMENT ======
-
- # all done in XS
-
-}
-
-1;
-
-__END__
-
-=head1 NAME
-
-DBD::Pg - PostgreSQL database driver for the DBI module
-
-=head1 SYNOPSIS
-
- use DBI;
-
- $dbh = DBI->connect("dbi:Pg:dbname=$dbname", "", "");
-
- # for some advanced uses you may need PostgreSQL type values:
- use DBD::Oracle qw(:pg_types);
-
- # See the DBI module documentation for full details
-
-=head1 DESCRIPTION
-
-DBD::Pg is a Perl module which works with the DBI module to provide access to
-PostgreSQL databases.
-
-=head1 MODULE DOCUMENTATION
-
-This documentation describes driver specific behavior and restrictions. It is
-not supposed to be used as the only reference for the user. In any case
-consult the DBI documentation first!
-
-=head1 THE DBI CLASS
-
-=head2 DBI Class Methods
-
-=over 4
-
-=item B<connect>
-
-To connect to a database with a minimum of parameters, use the following
-syntax:
-
- $dbh = DBI->connect("dbi:Pg:dbname=$dbname", "", "");
-
-This connects to the database $dbname at localhost without any user
-authentication. This is sufficient for the defaults of PostgreSQL.
-
-The following connect statement shows all possible parameters:
-
- $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host;port=$port;" .
- "options=$options;tty=$tty", "$username", "$password");
-
-If a parameter is undefined PostgreSQL first looks for specific environment
-variables and then it uses hard coded defaults:
-
- parameter environment variable hard coded default
- --------------------------------------------------
- dbname PGDATABASE current userid
- host PGHOST localhost
- port PGPORT 5432
- options PGOPTIONS ""
- tty PGTTY ""
- username PGUSER current userid
- password PGPASSWORD ""
-
-If a host is specified, the postmaster on this host needs to be started with
-the C<-i> option (TCP/IP sockets).
-
-The options parameter specifies runtime options for the Postgres
-backend. Common usage is to increase the number of buffers with the C<-B>
-option. Also important is the C<-F> option, which disables automatic fsync()
-call after each transaction. For further details please refer to the
-L<postgres>.
-
-For authentication with username and password appropriate entries have to be
-made in pg_hba.conf. Please refer to the L<pg_hba.conf> and the L<pg_passwd>
-for the different types of authentication. Note that for these two parameters
-DBI distinguishes between empty and undefined. If these parameters are
-undefined DBI substitutes the values of the environment variables DBI_USER and
-DBI_PASS if present.
-
-=item B<available_drivers>
-
- @driver_names = DBI->available_drivers;
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<data_sources>
-
- @data_sources = DBI->data_sources('Pg');
-
-The driver supports this method. Note that the necessary database connection to
-the database template1 will be done on the localhost without any
-user-authentication. Other preferences can only be set with the environment
-variables PGHOST, DBI_USER and DBI_PASS.
-
-=item B<trace>
-
- DBI->trace($trace_level, $trace_file)
-
-Implemented by DBI, no driver-specific impact.
-
-=back
-
-=head2 DBI Dynamic Attributes
-
-See Common Methods.
-
-=head1 METHODS COMMON TO ALL HANDLES
-
-=over 4
-
-=item B<err>
-
- $rv = $h->err;
-
-Supported by the driver as proposed by DBI. For the connect method it returns
-PQstatus. In all other cases it returns PQresultStatus of the current handle.
-
-=item B<errstr>
-
- $str = $h->errstr;
-
-Supported by the driver as proposed by DBI. It returns the PQerrorMessage
-related to the current handle.
-
-=item B<state>
-
- $str = $h->state;
-
-This driver does not (yet) support the state method.
-
-=item B<trace>
-
- $h->trace($trace_level, $trace_filename);
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<trace_msg>
-
- $h->trace_msg($message_text);
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<func>
-
-This driver supports a variety of driver specific functions accessible via the
-func interface:
-
- $attrs = $dbh->func($table, 'table_attributes');
-
-This method returns for the given table a reference to an array of hashes:
-
- NAME attribute name
- TYPE attribute type
- SIZE attribute size (-1 for variable size)
- NULLABLE flag nullable
- DEFAULT default value
- CONSTRAINT constraint
- PRIMARY_KEY flag is_primary_key
-
- $lobjId = $dbh->func($mode, 'lo_creat');
-
-Creates a new large object and returns the object-id. $mode is a bit-mask
-describing different attributes of the new object. Use the following
-constants:
-
- $dbh->{pg_INV_WRITE}
- $dbh->{pg_INV_READ}
-
-Upon failure it returns undef.
-
- $lobj_fd = $dbh->func($lobjId, $mode, 'lo_open');
-
-Opens an existing large object and returns an object-descriptor for use in
-subsequent lo_* calls. For the mode bits see lo_create. Returns undef upon
-failure. Note that 0 is a perfectly correct object descriptor!
-
- $nbytes = $dbh->func($lobj_fd, $buf, $len, 'lo_write');
-
-Writes $len bytes of $buf into the large object $lobj_fd. Returns the number
-of bytes written and undef upon failure.
-
- $nbytes = $dbh->func($lobj_fd, $buf, $len, 'lo_read');
-
-Reads $len bytes into $buf from large object $lobj_fd. Returns the number of
-bytes read and undef upon failure.
-
- $loc = $dbh->func($lobj_fd, $offset, $whence, 'lo_lseek');
-
-Change the current read or write location on the large object
-$obj_id. Currently $whence can only be 0 (L_SET). Returns the current location
-and undef upon failure.
-
- $loc = $dbh->func($lobj_fd, 'lo_tell');
-
-Returns the current read or write location on the large object $lobj_fd and
-undef upon failure.
-
- $lobj_fd = $dbh->func($lobj_fd, 'lo_close');
-
-Closes an existing large object. Returns true upon success and false upon
-failure.
-
- $lobj_fd = $dbh->func($lobj_fd, 'lo_unlink');
-
-Deletes an existing large object. Returns true upon success and false upon
-failure.
-
- $lobjId = $dbh->func($filename, 'lo_import');
-
-Imports a Unix file as large object and returns the object id of the new
-object or undef upon failure.
-
- $ret = $dbh->func($lobjId, 'lo_export', 'filename');
-
-Exports a large object into a Unix file. Returns false upon failure, true
-otherwise.
-
- $ret = $dbh->func($line, 'putline');
-
-Used together with the SQL-command 'COPY table FROM STDIN' to copy large
-amount of data into a table avoiding the overhead of using single
-insert commands. The application must explicitly send the two characters "\."
-to indicate to the backend that it has finished sending its data. See test.pl
-for an example on how to use this function.
-
- $ret = $dbh->func($buffer, length, 'getline');
-
-Used together with the SQL-command 'COPY table TO STDOUT' to dump a complete
-table. See test.pl for an example on how to use this function.
-
- $ret = $dbh->func('pg_notifies');
-
-Returns either undef or a reference to two-element array [ $table,
-$backend_pid ] of asynchronous notifications received.
-
- $fd = $dbh->func('getfd');
-
-Returns fd of the actual connection to server. Can be used with select() and
-func('pg_notifies').
-
-=back
-
-=head1 ATTRIBUTES COMMON TO ALL HANDLES
-
-=over 4
-
-=item B<Warn> (boolean, inherited)
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<Active> (boolean, read-only)
-
-Supported by the driver as proposed by DBI. A database handle is active while
-it is connected and statement handle is active until it is finished.
-
-=item B<Kids> (integer, read-only)
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<ActiveKids> (integer, read-only)
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<CachedKids> (hash ref)
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<CompatMode> (boolean, inherited)
-
-Not used by this driver.
-
-=item B<InactiveDestroy> (boolean)
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<PrintError> (boolean, inherited)
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<RaiseError> (boolean, inherited)
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<HandleError> (boolean, inherited)
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<ChopBlanks> (boolean, inherited)
-
-Supported by the driver as proposed by DBI. This method is similar to the
-SQL-function RTRIM.
-
-=item B<LongReadLen> (integer, inherited)
-
-Implemented by DBI, not used by the driver.
-
-=item B<LongTruncOk> (boolean, inherited)
-
-Implemented by DBI, not used by the driver.
-
-=item B<Taint> (boolean, inherited)
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<private_*>
-
-Implemented by DBI, no driver-specific impact.
-
-=back
-
-=head1 DBI DATABASE HANDLE OBJECTS
-
-=head2 Database Handle Methods
-
-=over 4
-
-=item B<selectrow_array>
-
- @row_ary = $dbh->selectrow_array($statement, \%attr, @bind_values);
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<selectrow_arrayref>
-
- $ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @bind_values);
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<selectrow_hashref>
-
- $hash_ref = $dbh->selectrow_hashref($statement, \%attr, @bind_values);
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<selectall_arrayref>
-
- $ary_ref = $dbh->selectall_arrayref($statement, \%attr, @bind_values);
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<selectall_hashref>
-
- $hash_ref = $dbh->selectall_hashref($statement, $key_field);
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<selectcol_arrayref>
-
- $ary_ref = $dbh->selectcol_arrayref($statement, \%attr, @bind_values);
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<prepare>
-
- $sth = $dbh->prepare($statement, \%attr);
-
-PostgreSQL does not have the concept of preparing a statement. Hence the
-prepare method just stores the statement after checking for place-holders. No
-information about the statement is available after preparing it.
-
-=item B<prepare_cached>
-
- $sth = $dbh->prepare_cached($statement, \%attr);
-
-Implemented by DBI, no driver-specific impact. This method is not useful for
-this driver, because preparing a statement has no database interaction.
-
-=item B<do>
-
- $rv = $dbh->do($statement, \%attr, @bind_values);
-
-Implemented by DBI, no driver-specific impact. See the notes for the execute
-method elsewhere in this document.
-
-=item B<commit>
-
- $rc = $dbh->commit;
-
-Supported by the driver as proposed by DBI. See also the notes about
-B<Transactions> elsewhere in this document.
-
-=item B<rollback>
-
- $rc = $dbh->rollback;
-
-Supported by the driver as proposed by DBI. See also the notes about
-B<Transactions> elsewhere in this document.
-
-=item B<disconnect>
-
- $rc = $dbh->disconnect;
-
-Supported by the driver as proposed by DBI.
-
-=item B<ping>
-
- $rc = $dbh->ping;
-
-This driver supports the ping-method, which can be used to check the validity
-of a database-handle. The ping method issues an empty query and checks the
-result status.
-
-=item B<table_info>
-
- $sth = $dbh->table_info;
-
-Supported by the driver as proposed by DBI. This method returns all tables and
-views which are owned by the current user. It does not select any indexes and
-sequences. Also System tables are not selected. As TABLE_QUALIFIER the reltype
-attribute is returned and the REMARKS are undefined.
-
-=item B<foreign_key_info>
-
- $sth = $dbh->foreign_key_info( $pk_catalog, $pk_schema, $pk_table,
- $fk_catalog, $fk_schema, $fk_table );
-
-Supported by the driver as proposed by DBI. Unimplemented for Postgres
-servers before 7.3 (returns undef). Currently only returns information
-about first column of any multiple-column keys.
-
-=item B<tables>
-
- @names = $dbh->tables;
-
-Supported by the driver as proposed by DBI. This method returns all tables and
-views which are owned by the current user. It does not select any indexes and
-sequences, or system tables.
-
-=item B<type_info_all>
-
- $type_info_all = $dbh->type_info_all;
-
-Supported by the driver as proposed by DBI. Only for SQL data-types and for
-frequently used data-types information is provided. The mapping between the
-PostgreSQL typename and the SQL92 data-type (if possible) has been done
-according to the following table:
-
- +---------------+------------------------------------+
- | typname | SQL92 |
- |---------------+------------------------------------|
- | bool | BOOL |
- | text | / |
- | bpchar | CHAR(n) |
- | varchar | VARCHAR(n) |
- | int2 | SMALLINT |
- | int4 | INT |
- | int8 | / |
- | money | / |
- | float4 | FLOAT(p) p<7=float4, p<16=float8 |
- | float8 | REAL |
- | abstime | / |
- | reltime | / |
- | tinterval | / |
- | date | / |
- | time | / |
- | datetime | / |
- | timespan | TINTERVAL |
- | timestamp | TIMESTAMP |
- +---------------+------------------------------------+
-
-For further details concerning the PostgreSQL specific data-types please read
-the L<pgbuiltin>.
-
-=item B<type_info>
-
- @type_info = $dbh->type_info($data_type);
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<quote>
-
- $sql = $dbh->quote($value, $data_type);
-
-This module implements its own quote method. In addition to the DBI method it
-also doubles the backslash, because PostgreSQL treats a backslash as an escape
-character.
-
-B<NOTE:> The undocumented (and invalid) support for the C<SQL_BINARY> data
-type is officially deprecated. Use C<PG_BYTEA> with C<bind_param()> instead:
-
- $rv = $sth->bind_param($param_num, $bind_value,
- { pg_type => DBD::Pg::PG_BYTEA });
-
-=back
-
-=head2 Database Handle Attributes
-
-=over 4
-
-=item B<AutoCommit> (boolean)
-
-Supported by the driver as proposed by DBI. According to the classification of
-DBI, PostgreSQL is a database, in which a transaction must be explicitly
-started. Without starting a transaction, every change to the database becomes
-immediately permanent. The default of AutoCommit is on, which corresponds to
-the default behavior of PostgreSQL. When setting AutoCommit to off, a
-transaction will be started and every commit or rollback will automatically
-start a new transaction. For details see the notes about B<Transactions>
-elsewhere in this document.
-
-=item B<Driver> (handle)
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<Name> (string, read-only)
-
-The default method of DBI is overridden by a driver specific method, which
-returns only the database name. Anything else from the connection string is
-stripped off. Note, that here the method is read-only in contrast to the DBI
-specs.
-
-=item B<RowCacheSize> (integer)
-
-Implemented by DBI, not used by the driver.
-
-=item B<pg_auto_escape> (boolean)
-
-PostgreSQL specific attribute. If true, then quotes and backslashes in all
-parameters will be escaped in the following way:
-
- escape quote with a quote (SQL)
- escape backslash with a backslash
-
-The default is on. Note, that PostgreSQL also accepts quotes, which are
-escaped by a backslash. Any other ASCII character can be used directly in a
-string constant.
-
-=item B<pg_enable_utf8> (boolean)
-
-PostgreSQL specific attribute. If true, then the utf8 flag will be
-turned for returned character data (if the data is valid utf8). For
-details about the utf8 flag, see L<Encode>. This is only relevant under
-perl 5.8 and higher.
-
-B<NB>: This attribute is experimental and may be subject to change.
-
-=item B<pg_INV_READ> (integer, read-only)
-
-Constant to be used for the mode in lo_creat and lo_open.
-
-=item B<pg_INV_WRITE> (integer, read-only)
-
-Constant to be used for the mode in lo_creat and lo_open.
-
-=back
-
-=head1 DBI STATEMENT HANDLE OBJECTS
-
-=head2 Statement Handle Methods
-
-=over 4
-
-=item B<bind_param>
-
- $rv = $sth->bind_param($param_num, $bind_value, \%attr);
-
-Supported by the driver as proposed by DBI.
-
-B<NOTE:> The undocumented (and invalid) support for the C<SQL_BINARY>
-SQL type is officially deprecated. Use C<PG_BYTEA> instead:
-
- $rv = $sth->bind_param($param_num, $bind_value,
- { pg_type => DBD::Pg::PG_BYTEA });
-
-=item B<bind_param_inout>
-
-Not supported by this driver.
-
-=item B<execute>
-
- $rv = $sth->execute(@bind_values);
-
-Supported by the driver as proposed by DBI. In addition to 'UPDATE', 'DELETE',
-'INSERT' statements, for which it returns always the number of affected rows,
-the execute method can also be used for 'SELECT ... INTO table' statements.
-
-=item B<fetchrow_arrayref>
-
- $ary_ref = $sth->fetchrow_arrayref;
-
-Supported by the driver as proposed by DBI.
-
-=item B<fetchrow_array>
-
- @ary = $sth->fetchrow_array;
-
-Supported by the driver as proposed by DBI.
-
-=item B<fetchrow_hashref>
-
- $hash_ref = $sth->fetchrow_hashref;
-
-Supported by the driver as proposed by DBI.
-
-=item B<fetchall_arrayref>
-
- $tbl_ary_ref = $sth->fetchall_arrayref;
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<finish>
-
- $rc = $sth->finish;
-
-Supported by the driver as proposed by DBI.
-
-=item B<rows>
-
- $rv = $sth->rows;
-
-Supported by the driver as proposed by DBI. In contrast to many other drivers
-the number of rows is available immediately after executing the statement.
-
-=item B<bind_col>
-
- $rc = $sth->bind_col($column_number, \$var_to_bind, \%attr);
-
-Supported by the driver as proposed by DBI.
-
-=item B<bind_columns>
-
- $rc = $sth->bind_columns(\%attr, @list_of_refs_to_vars_to_bind);
-
-Supported by the driver as proposed by DBI.
-
-=item B<dump_results>
-
- $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh);
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<blob_read>
-
- $blob = $sth->blob_read($id, $offset, $len);
-
-Supported by this driver as proposed by DBI. Implemented by DBI but not
-documented, so this method might change.
-
-This method seems to be heavily influenced by the current implementation of
-blobs in Oracle. Nevertheless we try to be as compatible as possible. Whereas
-Oracle suffers from the limitation that blobs are related to tables and every
-table can have only one blob (data-type LONG), PostgreSQL handles its blobs
-independent of any table by using so called object identifiers. This explains
-why the blob_read method is blessed into the STATEMENT package and not part of
-the DATABASE package. Here the field parameter has been used to handle this
-object identifier. The offset and len parameter may be set to zero, in which
-case the driver fetches the whole blob at once.
-
-Starting with PostgreSQL-6.5 every access to a blob has to be put into a
-transaction. This holds even for a read-only access.
-
-See also the PostgreSQL-specific functions concerning blobs which are
-available via the func-interface.
-
-For further information and examples about blobs, please read the chapter
-about Large Objects in the PostgreSQL Programmer's Guide.
-
-=back
-
-=head2 Statement Handle Attributes
-
-=over 4
-
-=item B<NUM_OF_FIELDS> (integer, read-only)
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<NUM_OF_PARAMS> (integer, read-only)
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<NAME> (array-ref, read-only)
-
-Supported by the driver as proposed by DBI.
-
-=item B<NAME_lc> (array-ref, read-only)
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<NAME_uc> (array-ref, read-only)
-
-Implemented by DBI, no driver-specific impact.
-
-=item B<TYPE> (array-ref, read-only)
-
-Supported by the driver as proposed by DBI, with the restriction, that the
-types are PostgreSQL specific data-types which do not correspond to
-international standards.
-
-=item B<PRECISION> (array-ref, read-only)
-
-Not supported by the driver.
-
-=item B<SCALE> (array-ref, read-only)
-
-Not supported by the driver.
-
-=item B<NULLABLE> (array-ref, read-only)
-
-Not supported by the driver.
-
-=item B<CursorName> (string, read-only)
-
-Not supported by the driver. See the note about B<Cursors> elsewhere in this
-document.
-
-=item B<Statement> (string, read-only)
-
-Supported by the driver as proposed by DBI.
-
-=item B<RowCache> (integer, read-only)
-
-Not supported by the driver.
-
-=item B<pg_size> (array-ref, read-only)
-
-PostgreSQL specific attribute. It returns a reference to an array of integer
-values for each column. The integer shows the size of the column in
-bytes. Variable length columns are indicated by -1.
-
-=item B<pg_type> (hash-ref, read-only)
-
-PostgreSQL specific attribute. It returns a reference to an array of strings
-for each column. The string shows the name of the data_type.
-
-=item B<pg_oid_status> (integer, read-only)
-
-PostgreSQL specific attribute. It returns the OID of the last INSERT command.
-
-=item B<pg_cmd_status> (integer, read-only)
-
-PostgreSQL specific attribute. It returns the type of the last
-command. Possible types are: INSERT, DELETE, UPDATE, SELECT.
-
-=back
-
-=head1 FURTHER INFORMATION
-
-=head2 Transactions
-
-The transaction behavior is now controlled with the attribute AutoCommit. For
-a complete definition of AutoCommit please refer to the DBI documentation.
-
-According to the DBI specification the default for AutoCommit is TRUE. In this
-mode, any change to the database becomes valid immediately. Any 'begin',
-'commit' or 'rollback' statement will be rejected.
-
-If AutoCommit is switched-off, immediately a transaction will be started by
-issuing a 'begin' statement. Any 'commit' or 'rollback' will start a new
-transaction. A disconnect will issue a 'rollback' statement.
-
-=head2 Large Objects
-
-The driver supports all large-objects related functions provided by libpq via
-the func-interface. Please note, that starting with PostgreSQL 6.5 any access
-to a large object - even read-only - has to be put into a transaction!
-
-=head2 Cursors
-
-Although PostgreSQL has a cursor concept, it has not been used in the current
-implementation. Cursors in PostgreSQL can only be used inside a transaction
-block. Because only one transaction block at a time is allowed, this would
-have implied the restriction, not to use any nested SELECT statements. Hence
-the execute method fetches all data at once into data structures located in
-the frontend application. This has to be considered when selecting large
-amounts of data!
-
-=head2 Data-Type bool
-
-The current implementation of PostgreSQL returns 't' for true and 'f' for
-false. From the Perl point of view a rather unfortunate choice. The DBD::Pg
-module translates the result for the data-type bool in a perl-ish like manner:
-'f' -> '0' and 't' -> '1'. This way the application does not have to check the
-database-specific returned values for the data-type bool, because Perl treats
-'0' as false and '1' as true.
-
-Boolean values can be passed to PostgreSQL as TRUE, 't', 'true', 'y', 'yes' or
-'1' for true and FALSE, 'f', 'false', 'n', 'no' or '0' for false.
-
-=head2 Schema support
-
-PostgreSQL version 7.3 introduced schema support. Note that the PostgreSQL
-schema concept may differ to that of other databases. Please refer to the
-PostgreSQL documentation for more details.
-
-Currently DBD::Pg does not provide explicit support for PostgreSQL schemas.
-However, schema functionality may be used without any restrictions by
-explicitly addressing schema objects, e.g.
-
- my $res = $dbh->selectall_arrayref("SELECT * FROM my_schema.my_table");
-
-or by manipulating the schema search path with SET search_path, e.g.
-
- $dbh->do("SET search_path TO my_schema, public");
-
-B<NOTE:> If you create an object with the same name as a PostgreSQL system
-object (as contained in the pg_catalog schema) and explicitly set the search
-path so that pg_catalog comes after the new object's schema, some DBD::Pg
-methods (particularly those querying PostgreSQL system objects) may fail.
-This problem should be fixed in a future release of DBD::Pg. Creating objects
-with the same name as system objects (or beginning with 'pg_') is not
-recommended practice and should be avoided in any case.
-
-=head1 SEE ALSO
-
-L<DBI>
-
-=head1 AUTHORS
-
-DBI and DBD-Oracle by Tim Bunce (Tim.Bunce@ig.co.uk)
-
-DBD-Pg by Edmund Mergl (E.Mergl@bawue.de) and Jeffrey W. Baker
-(jwbaker@acm.org). By David Wheeler <david@wheeler.net>, Jason
-Stewart <jason@openinformatics.com> and Bruce Momjian
-<pgman@candle.pha.pa.us> after v1.13.
-
-Major parts of this package have been copied from DBI and DBD-Oracle.
-
-=head1 COPYRIGHT
-
-The DBD::Pg module is free software. You may distribute under the terms of
-either the GNU General Public License or the Artistic License, as specified in
-the Perl README file.
-
-=head1 ACKNOWLEDGMENTS
-
-See also B<DBI/ACKNOWLEDGMENTS>.
-
-=cut
-
projects / freeside.git / commitdiff