pg.pm

关系型数据库 Postgresql 6.5.2

#-------------------------------------------------------
#
# $Id: Pg.pm,v 1.8 1998/09/27 19:12:22 mergl Exp $
#
# Copyright (c) 1997, 1998  Edmund Mergl
#
#-------------------------------------------------------

package Pg;

#use strict;
use Carp;
use vars qw($VERSION @ISA @EXPORT $AUTOLOAD);

require Exporter;
require DynaLoader;
require AutoLoader;
require 5.002;

@ISA = qw(Exporter DynaLoader);

# Items to export into callers namespace by default.
@EXPORT = qw(
	PQconnectdb
	PQsetdbLogin
	PQsetdb
	PQconndefaults
	PQfinish
	PQreset
	PQrequestCancel
	PQdb
	PQuser
	PQpass
	PQhost
	PQport
	PQtty
	PQoptions
	PQstatus
	PQerrorMessage
	PQsocket
	PQbackendPID
	PQtrace
	PQuntrace
	PQexec
	PQnotifies
	PQsendQuery
	PQgetResult
	PQisBusy
	PQconsumeInput
	PQgetline
	PQputline
	PQgetlineAsync
	PQputnbytes
	PQendcopy
	PQmakeEmptyPGresult
	PQresultStatus
	PQntuples
	PQnfields
	PQbinaryTuples
	PQfname
	PQfnumber
	PQftype
	PQfsize
	PQfmod
	PQcmdStatus
	PQoidStatus
	PQcmdTuples
	PQgetvalue
	PQgetlength
	PQgetisnull
	PQclear
	PQprint
	PQdisplayTuples
	PQprintTuples
	PQlo_open
	PQlo_close
	PQlo_read
	PQlo_write
	PQlo_lseek
	PQlo_creat
	PQlo_tell
	PQlo_unlink
	PQlo_import
	PQlo_export
	PGRES_CONNECTION_OK
	PGRES_CONNECTION_BAD
	PGRES_EMPTY_QUERY
	PGRES_COMMAND_OK
	PGRES_TUPLES_OK
	PGRES_COPY_OUT
	PGRES_COPY_IN
	PGRES_BAD_RESPONSE
	PGRES_NONFATAL_ERROR
	PGRES_FATAL_ERROR
	PGRES_INV_SMGRMASK
	PGRES_INV_ARCHIVE
	PGRES_INV_WRITE
	PGRES_INV_READ
	PGRES_InvalidOid
);

$Pg::VERSION = '1.8.0';

sub AUTOLOAD {
    # This AUTOLOAD is used to 'autoload' constants from the constant()
    # XS function.  If a constant is not found then control is passed
    # to the AUTOLOAD in AutoLoader.

    my $constname;
    ($constname = $AUTOLOAD) =~ s/.*:://;
    my $val = constant($constname, @_ ? $_[0] : 0);
    if ($! != 0) {
	if ($! =~ /Invalid/) {
	    $AutoLoader::AUTOLOAD = $AUTOLOAD;
	    goto &AutoLoader::AUTOLOAD;
	}
	else {
		croak "Your vendor has not defined Pg macro $constname";
	}
    }
    eval "sub $AUTOLOAD { $val }";
    goto &$AUTOLOAD;
}

bootstrap Pg $VERSION;

sub doQuery {

    my $conn      = shift;
    my $query     = shift;
    my $array_ref = shift;

    my ($result, $status, $i, $j);

    if ($result = $conn->exec($query)) {
        if (2 == ($status = $result->resultStatus)) {
            for $i (0..$result->ntuples - 1) {
                for $j (0..$result->nfields - 1) {
                    $$array_ref[$i][$j] = $result->getvalue($i, $j);
                }
            }
        }
    }
    return $status;
}


1;

__END__


=head1 NAME

Pg - Perl5 extension for PostgreSQL


=head1 SYNOPSIS

new style:

    use Pg;
    $conn = Pg::connectdb("dbname=template1");
    $result = $conn->exec("create database pgtest");


old style (depreciated):

    use Pg;
    $conn = PQsetdb('', '', '', '', template1);
    $result = PQexec($conn, "create database pgtest");
    PQclear($result);
    PQfinish($conn);


=head1 DESCRIPTION

The Pg module permits you to access all functions of the 
Libpq interface of PostgreSQL. Libpq is the programmer's 
interface to PostgreSQL. Pg tries to resemble this 
interface as close as possible. For examples of how to 
use this module, look at the file test.pl. For further 
examples look at the Libpq applications in 
../src/test/examples and ../src/test/regress. 

You have the choice between the old C-style and a 
new, more Perl-ish style. The old style has the 
benefit, that existing Libpq applications can be 
ported to perl just by prepending every variable 
with a '$'. The new style uses class packages and 
might be more familiar for C++-programmers. 


=head1 GUIDELINES

=head2 new style

The new style uses blessed references as objects. 
After creating a new connection or result object, 
the relevant Libpq functions serve as virtual methods. 
One benefit of the new style: you do not have to care 
about freeing the connection- and result-structures. 
Perl calls the destructor whenever the last reference 
to an object goes away. 

The method fetchrow can be used to fetch the next row from 
the server: while (@row = $result->fetchrow).
Columns which have NULL as value will be set to C<undef>.


=head2 old style

All functions and constants are imported into the calling 
packages name-space. In order to to get a uniform naming, 
all functions start with 'PQ' (e.g. PQlo_open) and all 
constants start with 'PGRES_' (e.g. PGRES_CONNECTION_OK). 

There are two functions, which allocate memory, that has 
to be freed by the user: 

    PQsetdb, use PQfinish to free memory.
    PQexec,  use PQclear to free memory.

Pg.pm contains one convenience function: doQuery. It fills a
two-dimensional array with the result of your query. Usage:

    Pg::doQuery($conn, "select attr1, attr2 from tbl", \@ary);

    for $i ( 0 .. $#ary ) {
        for $j ( 0 .. $#{$ary[$i]} ) {
            print "$ary[$i][$j]\t";
        }
        print "\n";
    }

Notice the inner loop !


=head1 CAVEATS

There are few exceptions, where the perl-functions differs 
from the C-counterpart: PQprint, PQnotifies and PQconndefaults. 
These functions deal with structures, which have been 
implemented in perl using lists or hash. 


=head1 FUNCTIONS

The functions have been divided into three sections: 
Connection, Result, Large Objects. For details please 
read L<libpq>.


=head2 1. Connection

With these functions you can establish and close a connection to a 
database. In Libpq a connection is represented by a structure called
PGconn. 

When opening a connection a given database name is always converted to 
lower-case, unless it is surrounded by double quotes. All unspecified 
parameters are replaced by environment variables or by hard coded defaults: 

    parameter  environment variable  hard coded default
    --------------------------------------------------
    host       PGHOST                localhost
    port       PGPORT                5432
    options    PGOPTIONS             ""
    tty        PGTTY                 ""
    dbname     PGDATABASE            current userid
    user       PGUSER                current userid
    password   PGPASSWORD            ""

Using appropriate methods you can access almost all fields of the 
returned PGconn structure. 

    $conn = Pg::setdbLogin($pghost, $pgport, $pgoptions, $pgtty, $dbname, $login, $pwd)

Opens a new connection to the backend. The connection identifier $conn 
( a pointer to the PGconn structure ) must be used in subsequent commands 
for unique identification. Before using $conn you should call $conn->status 
to ensure, that the connection was properly made. 

    $conn = Pg::setdb($pghost, $pgport, $pgoptions, $pgtty, $dbname)

The method setdb should be used when username/password authentication is 
not needed. 

    $conn = Pg::connectdb("option1=value option2=value ...")

Opens a new connection to the backend using connection information in a 
string. Possible options are: host, port, options, tty, dbname, user, password. 
The connection identifier $conn (a pointer to the PGconn structure) 
must be used in subsequent commands for unique identification. Before using 
$conn you should call $conn->status to ensure, that the connection was 
properly made. 

    $Option_ref = Pg::conndefaults()

    while(($key, $val) = each %$Option_ref) {
        print "$key, $val\n";

Returns a reference to a hash containing as keys all possible options for 
connectdb(). The values are the current defaults. This function differs from 
his C-counterpart, which returns the complete conninfoOption structure. 

    PQfinish($conn)

Old style only !
Closes the connection to the backend and frees the connection data structure. 

    $conn->reset

Resets the communication port with the backend and tries
to establish a new connection.

    $ret = $conn->requestCancel

Abandon processing of the current query. Regardless  of the return value of 
requestCancel, the application must continue with the normal result-reading 
sequence using getResult. If the current query is part of a transaction, 
cancellation will abort the whole transaction. 

    $dbname = $conn->db

Returns the database name of the connection.

    $pguser = $conn->user

Returns the Postgres user name of the connection.