NAME

Pg - Perl extension for Postgres95

SYNOPSIS

new style:

use Pg; $conn = Pg::new('', '', '', '', 'template1'); $result = $conn-&gtexec("create database test");

you may also use the old style:

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

DESCRIPTION

The Pg module permits you to access all functions of the Libpq interface of Postgres95. Libpq is the programmer's interface to Postgres95. 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.

GUIDELINES

old style

All functions and constants are imported into the calling packages namespace. 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 !

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.

CAVEATS

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

FUNCTIONS

The functions have been divided into three sections: Connection, Result, Large Objects.

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. Using the appropriate methods you can access almost all fields of this structure.

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

Opens a new connection to the backend. You may use an empty string for any argument, in which case first the environment is checked and then hardcoded defaults are used. 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-&gtstatus to ensure, that the connection was properly made. Use the methods below to access the contents of the PGconn structure.

PQfinish($conn)

Old style only ! Closes the connection to the backend and frees all memory.

$conn-&gtreset

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

$dbName = $conn-&gtdb

Returns the database name of the connection.

$pghost = $conn-&gthost

Returns the host name of the connection.

$pgoptions = $conn-&gtoptions

Returns the options used in the connection.

$pgport = $conn-&gtport

Returns the port of the connection.

$pgtty = $conn-&gttty

Returns the tty of the connection.

$status = $conn-&gtstatus

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

$errorMessage = $conn-&gterrorMessage

Returns the last error message associated with this connection.

$conn-&gttrace(debug_port)

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

$conn-&gtuntrace

Disables tracing.

$result = $conn-&gtexec($query)

Submits a query to the backend. The return value is a pointer to the PGresult structure, which contains the complete 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-implementation. 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.

$ret = $conn-&gtgetline($string, $length)

Reads a string up to $length - 1 characters from the backend. 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 sending the results of the copy command.

$conn-&gtputline($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-&gtendcopy

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 getline. endcopy returns 0 on success, nonzero otherwise.

($table, $pid) = $conn-&gtnotifies

Checks for asynchronous notifications. This functions differs 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 listened to and $pid is the process id of the backend.

2. Result

With these functions you can send commands to a database and investigate the results. In Libpq the result of a command is represented by a structure called PGresult. Using the appropriate methods you can access almost all fields of this structure.

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

$ntups = $result-&gtntuples

Returns the number of tuples in the query result.

$nfields = $result-&gtnfields

Returns the number of fields in the query result.

$fname = $result-&gtfname($field_num)

Returns the field name associated with the given field number.

$fnumber = $result-&gtfnumber($field_name)

Returns the field number associated with the given field name.

$ftype = $result-&gtftype($field_num)

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

$fsize = $result-&gtfsize($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.

$value = $result-&gtgetvalue($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-&gtgetlength($tup_num, $field_num)

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

$null_status = $result-&gtgetisnull($tup_num, $field_num)

Returns the NULL status for a given tuple and field.

$result_status = $result-&gtresultStatus

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

$cmdStatus = $result-&gtcmdStatus

Returns the command status of the last query command.

$oid = $result-&gtoidStatus

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

$result-&gtprintTuples($fout, $printAttName, $terseOutput, $width)

Kept for backward compatibility. Use print.

$result-&gtprint($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 by a list. This list is of variable length, in order to care for the character array fieldName in PQprintOpt. The arguments $header, $align, $standard, $html3, $expanded, $pager are boolean flags. The arguments $fieldSep, $tableOpt, $caption are strings. You may append additional strings, which will be taken as replacement for the field names.

PQclear($result)

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

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 analogues 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).

$lobjId = $conn-&gtlo_creat($mode)

Creates a new large object. $mode is a bitmask describing different attributes of the new object. Use the following constants: - PGRES_INV_SMGRMASK - PGRES_INV_ARCHIVE - PGRES_INV_WRITE - PGRES_INV_READ

Upon failure it returns PGRES_InvalidOid.

$ret = $conn-&gtlo_unlink($lobjId)

Deletes a large object. Returns -1 upon failure.

$lobj_fd = $conn-&gtlo_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-&gtlo_close($lobj_fd)

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

$nbytes = $conn-&gtlo_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-&gtlo_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-&gtlo_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).

$location = $conn-&gtlo_tell($lobj_fd)

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

$lobjId = $conn-&gtlo_import($filename)

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

$ret = $conn-&gtlo_export($lobjId, $filename)

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

AUTHOR

Edmund Mergl &ltE.Mergl@bawue.de&gt

SEE ALSO

libpq(3), large_objects(3).

cpanm Pg

perl -MCPAN -e shell
install Pg