Pg - Perl5 extension for PostgreSQL


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);


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 inter­
       face 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++-program­
       mers.


GUIDELINES

       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 `undef'.

       old style

       All functions and constants are imported into the calling
       packages name-space. In order to to get a uniform naming,

       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 !


CAVEATS

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


FUNCTIONS

       The functions have been divided into three sections: Con­
       nection, Result, Large Objects. For details please read
       the libpq manpage.

       1. Connection

       With these functions you can establish and close a connec­
       tion 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 envi­
       ronment variables or by hard coded defaults:

           --------------------------------------------------
           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 identifica­
       tion. 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 pos­
       sible 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


           $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.

           $pguser = $conn->pass

       Returns the Postgres password of the connection.

           $pghost = $conn->host

       Returns the host name of the connection.

           $pgport = $conn->port

       Returns the port of the connection.

           $pgtty = $conn->tty

       Returns the tty of the connection.

           $pgoptions = $conn->options

       Returns the options used in the connection.

           $status = $conn->status

       Returns the status of the connection. For comparing the
       status you may use the following constants:

         - PGRES_CONNECTION_OK
         - PGRES_CONNECTION_BAD

           $errorMessage = $conn->errorMessage

       Returns the last error message associated with this con­
       nection.

           $fd = $conn->socket

       Obtain the file descriptor number for the backend

       end connection is currently open.

           $pid = $conn->backendPID

       Returns the process-id of the corresponding backend pro­
       ceess.

           $conn->trace(debug_port)

       Messages passed between frontend and backend are echoed to
       the debug_port file stream.

           $conn->untrace

       Disables tracing.

           $result = $conn->exec($query)

       Submits a query to the backend. The return value is a
       pointer to the PGresult structure, which contains the com­
       plete query-result returned by the backend. In case of
       failure, the pointer points to an empty structure. In
       this, the perl implementation differs from the C-implemen­
       tation. Using the old style, even the empty structure has
       to be freed using PQfree. Before using $result you should
       call resultStatus to ensure, that the query was properly
       executed.

           ($table, $pid) = $conn->notifies

       Checks for asynchronous notifications. This functions dif­
       fers from the C-counterpart which returns a pointer to a
       new allocated structure, whereas the perl implementation
       returns a list. $table is the table which has been lis­
       tened to and $pid is the process id of the backend.

           $ret = $conn->sendQuery($string, $query)

       Submit a query to Postgres without waiting for the
       result(s). After successfully calling PQsendQuery, call
       PQgetResult one or more times to obtain the query results.
       PQsendQuery may not be called again until getResult has
       returned NULL, indicating that the query is done.

           $result = $conn->getResult

       Wait for the next result from a prior PQsendQuery, and
       return it.  NULL is returned when the query is complete
       and there will be no more results.  getResult  will block
       only if a query is active and the necessary response data
       has not yet been read by PQconsumeInput.

           $ret = $conn->isBusy

       would block waiting for input.  A FALSE  return indicates
       that PQgetResult can be called with assurance of not
       blocking.

           $result = $conn->consumeInput

       If input is available from the backend, consume it. After
       calling consumeInput, the application may check isBusy
       and/or notifies to see if their state has changed.

           $ret = $conn->getline($string, $length)

       Reads a string up to $length - 1 characters from the back­
       end.  getline returns EOF at EOF, 0 if the entire line has
       been read, and 1 if the buffer is full. If a line consists
       of the two characters "\." the backend has finished send­
       ing the results of the copy command.

           $ret = $conn->putline($string)

       Sends a string to the backend. The application must
       explicitly send the two characters "\." to indicate to the
       backend that it has finished sending its data.

           $ret = $conn->getlineAsync($buffer, $bufsize)

       Non-blocking version of getline. It reads up to $bufsize
       characters from the backend. getlineAsync returns -1 if
       the end-of-copy-marker has been recognized, 0 if no data
       is avilable, and >0 the number of bytes returned.

           $ret = $conn->putnbytes($buffer, $nbytes)

       Sends n bytes to the backend. Returns 0 if OK, EOF if not.

           $ret = $conn->endcopy

       This function waits  until the backend has finished the
       copy.  It should either be issued when the last string has
       been sent to  the  backend  using  putline or when the
       last string has been received from the backend using get­
       line. endcopy returns 0 on success, 1 on failure.

           $result = $conn->makeEmptyPGresult($status);

       Returns a newly allocated, initialized result with given
       status.

       2. Result

       With these functions you can send commands to a database
       and investigate the results. In Libpq the result of a com­
       mand is represented by a structure called PGresult. Using

       of this structure.

           $result_status = $result->resultStatus

       Returns the status of the result. For comparing the status
       you may use one of the following constants depending upon
       the command executed:

         - PGRES_EMPTY_QUERY
         - PGRES_COMMAND_OK
         - PGRES_TUPLES_OK
         - PGRES_COPY_OUT
         - PGRES_COPY_IN
         - PGRES_BAD_RESPONSE
         - PGRES_NONFATAL_ERROR
         - PGRES_FATAL_ERROR

       Use the functions below to access the contents of the
       PGresult structure.

           $ntuples = $result->ntuples

       Returns the number of tuples in the query result.

           $nfields = $result->nfields

       Returns the number of fields in the query result.

           $ret = $result->binaryTuples

       Returns 1 if the tuples in the query result are bianry.

           $fname = $result->fname($field_num)

       Returns the field name associated with the given field
       number.

           $fnumber = $result->fnumber($field_name)

       Returns the field number associated with the given field
       name.

           $ftype = $result->ftype($field_num)

       Returns the oid of the type of the given field number.

           $fsize = $result->fsize($field_num)

       Returns the size in bytes of the type of the given field
       number.  It returns -1 if the field has a variable length.

           $fmod = $result->fmod($field_num)

       associated with the given field index. Field indices start
       at 0.

           $cmdStatus = $result->cmdStatus

       Returns the command status of the last query command.  In
       case of DELETE it returns also the number of deleted
       tuples.  In case of INSERT it returns also the OID of the
       inserted tuple followed by 1 (the number of affected
       tuples).

           $oid = $result->oidStatus

       In case the last query was an INSERT command it returns
       the oid of the inserted tuple.

           $oid = $result->cmdTuples

       In case the last query was an INSERT or DELETE command it
       returns the number of affected tuples.

           $value = $result->getvalue($tup_num, $field_num)

       Returns the value of the given tuple and field. This is a
       null-terminated ASCII string. Binary cursors will not
       work.

           $length = $result->getlength($tup_num, $field_num)

       Returns the length of the value for a given tuple and
       field.

           $null_status = $result->getisnull($tup_num, $field_num)

       Returns the NULL status for a given tuple and field.

           PQclear($result)

       Old style only !  Frees all memory of the given result.

           $res->fetchrow

       New style only !  Fetches the next row from the server and
       returns NULL if all rows have been processed. Columns
       which have NULL as value will be set to `undef'.

           $result->print($fout, $header, $align, $standard, $html3, $expanded, $pager, $fieldSep, $tableOpt, $caption, ...)

       Prints out all the tuples in an intelligent  manner. This
       function differs from the C-counterpart. The struct
       PQprintOpt has been implemented with a list. This list is
       of variable length, in order to care for the character
       array fieldName in PQprintOpt.  The arguments $header,

       flags. The arguments $fieldSep, $tableOpt, $caption are
       strings. You may append additional strings, which will be
       taken as replacement for the field names.

           $result->displayTuples($fp, $fillAlign, $fieldSep, $printHeader, qiet)

       Kept for backward compatibility. Use print.

           $result->printTuples($fout, $printAttName, $terseOutput, $width)

       Kept for backward compatibility. Use print.

       3. Large Objects

       These functions provide file-oriented access to user data.
       The large object interface is modeled after the Unix file
       system interface with analogies of open, close, read,
       write, lseek, tell. In order to get a consistent naming,
       all function names have been prepended with 'PQ' (old
       style only).

           $lobj_fd = $conn->lo_open($lobjId, $mode)

       Opens an existing large object and returns an object id.
       For the mode bits see lo_create. Returns -1 upon failure.

           $ret = $conn->lo_close($lobj_fd)

       Closes an existing large object. Returns 0 upon success
       and -1 upon failure.

           $nbytes = $conn->lo_read($lobj_fd, $buf, $len)

       Reads $len bytes into $buf from large object $lobj_fd.
       Returns the number of bytes read and -1 upon failure.

           $nbytes = $conn->lo_write($lobj_fd, $buf, $len)

       Writes $len bytes of $buf into the large object $lobj_fd.
       Returns the number of bytes written and -1 upon failure.

           $ret = $conn->lo_lseek($lobj_fd, $offset, $whence)

       Change the current read or write location on the large
       object $obj_id. Currently $whence can only be 0 (L_SET).

           $lobjId = $conn->lo_creat($mode)

       Creates a new large object. $mode is a bit-mask describing
       different attributes of the new object. Use the following
       constants:

         - PGRES_INV_ARCHIVE
         - PGRES_INV_WRITE
         - PGRES_INV_READ

       Upon failure it returns PGRES_InvalidOid.

           $location = $conn->lo_tell($lobj_fd)

       Returns the current read or write location on the large
       object $lobj_fd.

           $ret = $conn->lo_unlink($lobjId)

       Deletes a large object. Returns -1 upon failure.

           $lobjId = $conn->lo_import($filename)

       Imports a Unix file as large object and returns the object
       id of the new object.

           $ret = $conn->lo_export($lobjId, $filename)

       Exports a large object into a Unix file.  Returns -1 upon
       failure, 1 otherwise.


AUTHOR

           Edmund Mergl <E.Mergl@bawue.de>


SEE ALSO

       the libpq manpage, the large_objects manpage


Man(1) output converted with man2html