NAME

Pg::PQ - Perl wrapper for PostgreSQL libpq

SYNOPSIS

  use Pg::PQ qw(:pgres_polling);

  my $dbc = Pg::PQ::Conn->new(dbname => 'test',
                              host => 'dbserver');

  $dbc->sendQuery("select * from foo");

  while (1) {
    $dbc->consumeInput;
    last unless $dbc->busy
    # do something else
    ...
  }

  my $res = $dbc->result;
  my @rows = $res->rows;

  print "query result:\n", Dumper \@rows;

DESCRIPTION

  *******************************************************************
  ***                                                             ***
  *** NOTE: This is a very early release that may contain lots of ***
  *** bugs. The API is not stable and may change between releases ***
  ***                                                             ***
  *******************************************************************

This module is a thin wrapper around PostgreSQL libpq C API.

Its main purpose is to let query a PostgreSQL database asynchronously from inside common non-blocking frameworks as AnyEvent, POE or even Coro.

Pg::PQ::Conn class

These are the methods available from the class Pg::PQ::Conn:

@defaults = Pg::PQ::Conn::defaults();

Returns a list of hashes describing the acceptable connection options.

$dbc = Pg::PQ::Conn->new($conninfo)

$dbc = Pg::PQ::Conn->new(%conninfo)

$dbc = Pg::PQ::Conn->new(\%conninfo)

(wraps PQconnectdb)

This method creates a new Pg::PQ::Conn object and connects to the database defined by the parameters given as a string ($conninfo) or as a set of key value pairs (%conninfo).

For example:

  # parameters as an string:
  my $dbc = Pg::PQ::Conn->new("dbname=testdb user=jsmith passwd=jsmith11");

  # as key-value pairs:
  my $dbc = Pg::PQ::Conn->new(dbname => 'testdb',
                              user   => 'jsmith',
                              passwd => 'jsmith11');

The set of parameters accepted is as follows:

host

Name of host to connect to. If this begins with a slash, it specifies Unix-domain communication rather than TCP/IP communication; the value is the name of the directory in which the socket file is stored. The default behavior when host is not specified is to connect to a Unix-domain socket in /tmp (or whatever socket directory was specified when PostgreSQL was built). On machines without Unix-domain sockets, the default is to connect to localhost.

hostaddr

Numeric IP address of host to connect to. This should be in the standard IPv4 address format, e.g., 172.28.40.9. If your machine supports IPv6, you can also use those addresses. TCP/IP communication is always used when a nonempty string is specified for this parameter.

Using hostaddr instead of host allows the application to avoid a host name look-up, which might be important in applications with time constraints. However, a host name is required for Kerberos, GSSAPI, or SSPI authentication, as well as for full SSL certificate verification.

The following rules are used:

If host is specified without hostaddr, a host name lookup occurs.
If hostaddr is specified without host, the value for hostaddr gives the server address. The connection attempt will fail in any of the cases where a host name is required.
If both host and hostaddr are specified, the value for hostaddr gives the server address. The value for host is ignored unless needed for authentication or verification purposes, in which case it will be used as the host name.

Note that authentication is likely to fail if host is not the name of the machine at hostaddr. Also, note that host rather than hostaddr is used to identify the connection in ~/.pgpass (see Section 31.14 of the PostgreSQL documentation).
Without either a host name or host address, libpq will connect using a local Unix-domain socket; or on machines without Unix-domain sockets, it will attempt to connect to localhost.

port

Port number to connect to at the server host, or socket file name extension for Unix-domain connections.

dbname

The database name. Defaults to be the same as the user name.

user

PostgreSQL user name to connect as. Defaults to be the same as the operating system name of the user running the application.

password

Password to be used if the server demands password authentication.

connect_timeout

Maximum wait for connection, in seconds (write as a decimal integer string). Zero or not specified means wait indefinitely. It is not recommended to use a timeout of less than 2 seconds.

options

Adds command-line options to send to the server at run-time. For example, setting this to -c geqo=off sets the session's value of the geqo parameter to off. For a detailed discussion of the available options, consult Chapter 18 of the PostgreSQL documentation.

application_name

Specifies a value for the application_name configuration parameter.

fallback_application_name

Specifies a fallback value for the application_name configuration parameter. This value will be used if no value has been given for application_name via a connection parameter or the PGAPPNAME environment variable.

Specifying a fallback name is useful in generic utility programs that wish to set a default application name but allow it to be overridden by the user.

keepalives

Controls whether client-side TCP keepalives are used. The default value is 1, meaning on, but you can change this to 0, meaning off, if keepalives are not wanted.

This parameter is ignored for connections made via a Unix-domain socket.

keepalives_idle

Controls the number of seconds of inactivity after which TCP should send a keepalive message to the server. A value of zero uses the system default. This parameter is ignored for connections made via a Unix-domain socket, or if keepalives are disabled. It is only supported on systems where the TCP_KEEPIDLE or TCP_KEEPALIVE socket option is available, and on Windows; on other systems, it has no effect.

keepalives_interval

Controls the number of seconds after which a TCP keepalive message that is not acknowledged by the server should be retransmitted. A value of zero uses the system default. This parameter is ignored for connections made via a Unix-domain socket, or if keepalives are disabled. It is only supported on systems where the TCP_KEEPINTVL socket option is available, and on Windows; on other systems, it has no effect.

keepalives_count

Controls the number of TCP keepalives that can be lost before the client's connection to the server is considered dead. A value of zero uses the system default. This parameter is ignored for connections made via a Unix-domain socket, or if keepalives are disabled. It is only supported on systems where the TCP_KEEPINTVL socket option is available; on other systems, it has no effect.

sslmode

This option determines whether or with what priority a secure SSL TCP/IP connection will be negotiated with the server. There are six modes:

disable: only try a non-SSL connection
allow: first try a non-SSL connection; if that fails, try an SSL connection
prefer (default): first try an SSL connection; if that fails, try a non-SSL connection
require: only try an SSL connection
verify-ca: only try an SSL connection, and verify that the server certificate is issued by a trusted CA
verify-full: only try an SSL connection, verify that the server certificate is issued by a trusted CA and that the server host name matches that in the certificate

sslmode is ignored for Unix domain socket communication. If PostgreSQL is compiled without SSL support, using options require, verify-ca, or verify-full will cause an error, while options allow and prefer will be accepted but libpq will not actually attempt an SSL connection.

sslcert

This parameter specifies the file name of the client SSL certificate, replacing the default ~/.postgresql/postgresql.crt. This parameter is ignored if an SSL connection is not made.

sslkey

This parameter specifies the location for the secret key used for the client certificate. It can either specify a file name that will be used instead of the default ~/.postgresql/postgresql.key, or it can specify a key obtained from an external "engine" (engines are OpenSSL loadable modules). An external engine specification should consist of a colon-separated engine name and an engine-specific key identifier. This parameter is ignored if an SSL connection is not made.

sslrootcert

This parameter specifies the name of a file containing SSL certificate authority (CA) certificate(s). If the file exists, the server's certificate will be verified to be signed by one of these authorities. The default is ~/.postgresql/root.crt.

sslcrl

This parameter specifies the file name of the SSL certificate revocation list (CRL). Certificates listed in this file, if it exists, will be rejected while attempting to authenticate the server's certificate. The default is ~/.postgresql/root.crl.

krbsrvname

Kerberos service name to use when authenticating with Kerberos 5 or GSSAPI. This must match the service name specified in the server configuration for Kerberos authentication to succeed. (See also Section 19.3.5 and Section 19.3.3. of the PostgreSQL documentation).

gsslib

GSS library to use for GSSAPI authentication. Only used on Windows. Set to gssapi to force libpq to use the GSSAPI library for authentication instead of the default SSPI.

service

Service name to use for additional parameters. It specifies a service name in pg_service.conf that holds additional connection parameters. This allows applications to specify only a service name so connection parameters can be centrally maintained. See Section 31.15 of the PostgreSQL documentation.

If any parameter is unspecified, then the corresponding environment variable (see Section 31.13 of the PostgreSQL documentation) is checked. If the environment variable is not set either, then the indicated built-in defaults are used.

$dbc = Pg::PQ::Conn->start($conninfo)

$dbc = Pg::PQ::Conn->start(%conninfo)

$dbc = Pg::PQ::Conn->start(\%conninfo)

(wraps PQconnectStart)

This method is similar to "new" but returns inmediately, without waiting for the network connection to the database or the protocol handshake to be completed.

Combined with "connectPoll" described below allows to establish database connections asynchronously (see "Non-blocking connecting to the database").

$poll_status = $dbc->connectPoll

(wraps PQconnectPoll)

This method returns the polling status when connecting asynchronously to a database. Returns any of the "pgres_polling" constants (see "Constants" bellow).

This method combined with "start" are used to open a connection to a database server such that your application's thread of execution is not blocked on remote I/O whilst doing so. The point of this approach is that the waits for I/O to complete can occur in the application's main loop, rather than down inside "start" and so the application can manage this operation in parallel with other activities (see "Non-blocking connecting to the database").

Neither start nor connectPoll will block, so long as a number of restrictions are met:

The hostaddr and host parameters are used appropriately to ensure that name and reverse name queries are not made. See the documentation of these parameters under "new" above for details.
If you call "trace", ensure that the stream object into which you trace will not block.
You ensure that the socket is in the appropriate state before calling connectPoll, as described in "Non-blocking connecting to the database".

$dbc->db

Returns the database name.

$dbc->user

Returns the user name.

$dbc->pass

Returns the login password.

$dbc->host

Returns the name of the server

$dbc->port

Returns the remote port of the connection

$dbc->options

Return the options passed to the constructor

$dbc->status

Return the status of the connection

The status can be one of a number of values. However, only two of these are seen outside of an asynchronous connection procedure: CONNECTION_OK and CONNECTION_BAD. A good connection to the database has the status CONNECTION_OK. A failed connection attempt is signaled by status CONNECTION_BAD. Ordinarily, an OK status will remain so until "finish" (called implicitly by DESTROY), but a communications failure might result in the status changing to CONNECTION_BAD prematurely. In that case the application could try to recover by calling "reset".

See the entry for "start" and "connectPoll" with regards to other status codes that might be seen.

$dbc->transactionStatus

Returns the current in-transaction status of the server.

The status can be:

PQTRANS_IDLE: Currently idle.
PQTRANS_ACTIVE: A command is in progress. This status is reported only when a query has been sent to the server and not yet completed.
PQTRANS_INTRANS: Idle, in a valid transaction block.
PQTRANS_INERROR: Idle, in a failed transaction block.
PQTRANS_UNKNOWN: Is reported if the connection is bad.

PostgreSQL documentation contains the following warning:

   Caution: transactionStatus will give incorrect results when using a
   PostgreSQL 7.3 server that has the parameter autocommit set to
   off. The server-side autocommit feature has been deprecated and
   does not exist in later server versions.

$dbc->parameterStatus

Looks up a current parameter setting of the server.

Certain parameter values are reported by the server automatically at connection startup or whenever their values change. parameterStatus can be used to interrogate these settings. It returns the current value of a parameter if known, or undef if the parameter is not known.

Parameters reported as of the current release include:

  server_version, server_encoding, client_encoding, application_name,
  is_superuser, session_authorization, DateStyle, IntervalStyle,
  TimeZone, integer_datetimes and standard_conforming_strings.

  server_encoding, TimeZone, and integer_datetimes were not reported
  by releases before 8.0.

  standard_conforming_strings was not reported by releases before 8.1.

  IntervalStyle was not reported by releases before 8.4.

  application_name was not reported by releases before 9.0.

Note that server_version, server_encoding and integer_datetimes can not change after startup.

Pre-3.0-protocol servers do not report parameter settings, but libpq includes logic to obtain values for server_version and client_encoding anyway. Applications are encouraged to use parameterStatus rather than ad hoc code to determine these values (beware however that on a pre-3.0 connection, changing client_encoding via SET after connection startup will not be reflected by parameterStatus). For server_version, see also serverVersion, which returns the information in a numeric form that is much easier to compare against.

If no value for standard_conforming_strings is reported, applications can assume it is off, that is, backslashes are treated as escapes in string literals. Also, the presence of this parameter can be taken as an indication that the escape string syntax (E'...') is accepted.

$dbc->protocolVersion

Interrogates the frontend/backend protocol being used.

Applications might wish to use this to determine whether certain features are supported. Currently, the possible values are 2 (2.0 protocol), 3 (3.0 protocol), or zero (connection bad). This will not change after connection startup is complete, but it could theoretically change during a connection reset. The 3.0 protocol will normally be used when communicating with PostgreSQL 7.4 or later servers; pre-7.4 servers support only protocol 2.0. (Protocol 1.0 is obsolete and not supported by libpq.)

$dbc->serverVersion

Returns an integer representing the backend version.

Applications might use this to determine the version of the database server they are connected to. The number is formed by converting the major, minor, and revision numbers into two-decimal-digit numbers and appending them together. For example, version 8.1.5 will be returned as 80105, and version 8.2 will be returned as 80200 (leading zeroes are not shown). Zero is returned if the connection is bad.

$dbc->errorMessage

Returns the error message most recently generated by an operation on the connection.

Nearly all libpq functions will set a message for errorMessage if they fail. Error messages can be multiline.

Note that the returned string will not contain any trailing newline character (as the libpq C version does).

$dbc->socket

Obtains the file descriptor number of the connection socket to the server. A valid descriptor will be greater than or equal to 0; a result of -1 indicates that no server connection is currently open (this will not change during normal operation, but could change during connection setup or reset).

$dbc->backendPID

Returns the process ID (PID) of the backend server process handling this connection.

The backend PID is useful for debugging purposes and for comparison to NOTIFY messages (which include the PID of the notifying backend process). Note that the PID belongs to a process executing on the database server host, not the local host!

$dbc->connectionNeedsPassword

Returns true if the connection authentication method required a password, but none was available. Returns false if not.

This function can be applied after a failed connection attempt to decide whether to prompt the user for a password.

$dbc->connectionUsedPassword

Returns true if the connection authentication method used a password. Returns false if not.

This function can be applied after either a failed or successful connection attempt to detect whether the server demanded a password.

$dbc->finish

Closes the connection to the server and frees the underlaying libpq PGconn data structure. This method is automatically called by DESTROY so usually there is no need to call it explicitly.

$dbc->reset

This function will close the connection to the server and attempt to reestablish a new connection to the same server, using all the same parameters previously used. This might be useful for error recovery if a working connection is lost.

$dbc->resetStart

$dbc->resetPoll

Reset the communication channel to the server, in a non-blocking manner.

These functions will close the connection to the server and attempt to reestablish a new connection to the same server, using all the same parameters previously used. This can be useful for error recovery if a working connection is lost. They differ from "reset" in that they act in a non-blocking manner. These functions suffer from the same restrictions as "start" and "connectPoll".

To initiate a connection reset, call resetStart. If it returns 0, the reset has failed. If it returns 1, poll the reset using resetPoll in exactly the same way as you would create the connection using connectPoll.

$dbc->trace($fh)

Enables tracing of the client/server communication to a debugging file handle. For instance:

  $dbc->trace(*STDERR);

$dbc->untrace

Disables tracing started by "trace".

$dbc->execQuery

(wraps PQexec and PQexecParams)

This method submits a command to the server and waits for the result.

Returns a Pg::PQ::Result object or undef. A valid object will generally be returned except in out-of-memory conditions or serious errors such as inability to send the command to the server. If undef is returned, it should be treated like a PGRES_FATAL_ERROR result. Use "errorMessage" to get more information about such errors.

It is allowed to include multiple SQL commands (separated by semicolons) in the command string. Multiple queries sent in a single execQuery call are processed in a single transaction, unless there are explicit BEGIN/COMMIT commands included in the query string to divide it into multiple transactions. Note however that the returned Pg::PQ::Result object describes only the result of the last command executed from the string.

Should one of the commands fail, processing of the string stops with it and the returned Pg::PQ::Result object describes the error condition.

$res = $dbc->prepare($name => $query)

Submits a request to create a prepared statement with the given parameters, and waits for completion.

prepare creates a prepared statement for later execution with "execQueryPrepared". This feature allows commands that will be used repeatedly to be parsed and planned just once, rather than each time they are executed. prepare is supported only in protocol 3.0 and later connections; it will fail when using protocol 2.0.

The method creates a prepared statement named $name from the $query string, which must contain a single SQL command. $name can be "" to create an unnamed statement, in which case any pre-existing unnamed statement is automatically replaced; otherwise it is an error if the statement name is already defined in the current session. If any parameters are used, they are referred to in the query as $1, $2, etc. (see "describePrepared" for a means to find out what data types were inferred).

As with "execQuery", the result is normally a Pg::PQ::Result object whose contents indicate server-side success or failure. An undefined result indicates out-of-memory or inability to send the command at all. Use "errorMessage" to get more information about such errors.

Prepared statements for use with "execQueryPrepared" can also be created by executing SQL PREPARE statements. Also, although there is no libpq function for deleting a prepared statement, the SQL DEALLOCATE statement can be used for that purpose.

$res = $dbc->execQueryPrepared($name => @args)

(wraps PQexecPrepared)

This method sends a request to execute a prepared statement with given parameters, and waits for the result.

execQueryPrepared is like "execQuery", but the command to be executed is specified by naming a previously-prepared statement, instead of giving a query string. This feature allows commands that will be used repeatedly to be parsed and planned just once, rather than each time they are executed. The statement must have been prepared previously in the current session. execQueryPrepared is supported only in protocol 3.0 and later connections; it will fail when using protocol 2.0.

The parameters are identical to execQuery, except that the name of a prepared statement is given instead of a query string.

$res = $dbc->describePrepared($name)

Submits a request to obtain information about the specified prepared statement, and waits for completion.

describePrepared allows an application to obtain information about a previously prepared statement. It is supported only in protocol 3.0 and later connections; it will fail when using protocol 2.0.

$name can be "" to reference the unnamed statement, otherwise it must be the name of an existing prepared statement. On success, a Pg::PQ::Result object with status PGRES_COMMAND_OK is returned. The functions "nParams" and "paramType" can be applied to this Pg::PQ::Result object to obtain information about the parameters of the prepared statement, and the methods "nFields", "fName", "fType", etc provide information about the result columns (if any) of the statement.

$res = $dbc->describePortal($portalName)

Submits a request to obtain information about the specified portal, and waits for completion.

describePortal allows an application to obtain information about a previously created portal (libpq does not provide any direct access to portals, but you can use this function to inspect the properties of a cursor created with a DECLARE CURSOR SQL command). describePortal is supported only in protocol 3.0 and later connections; it will fail when using protocol 2.0.

$name can be "" to reference the unnamed portal, otherwise it must be the name of an existing portal. On success, a Pg::PQ::Result object with status PGRES_COMMAND_OK is returned. Its methods "nFields", "fName", "fType", etc can be called to obtain information about the result columns (if any) of the portal.

$ok = $dbc->sendQuery($query, @args)

(wraps PQsendQuery and PQsendQueryParams)

This method submits a query to the server without waiting for the result(s). 1 is returned if the query was successfully dispatched and 0 if not (in which case, use "errorMessage" to get more information about the failure).

After successfully calling sendQuery, call "result" one or more times to obtain the results.

sendQuery can not be called again on the same connection until result has returned undef, indicating that the command is done.

$ok = $dbc->sendPrepare($name => $query)

Sends a request to create a prepared statement without waiting for completion.

This is an asynchronous version of "prepare". It returns 1 if it was able to dispatch the request, and 0 if not. After a successful call, call "result" to determine whether the server successfully created the prepared statement.

Like "prepare", it will not work on 2.0-protocol connections.

$ok = $dbc->sendQueryPrepared($name, @args)

Sends a request to execute a prepared statement with given parameters, without waiting for the result(s).

This is similar to "sendQuery", but the command to be executed is specified by naming a previously-prepared statement, instead of giving a query string. The function's parameters are handled identically to "execQueryPrepared".

It will not work on 2.0-protocol connections.

$ok = $dbc->sendDescribePrepared($name)

Submits a request to obtain information about the specified prepared statement, without waiting for completion.

This is an asynchronous version of "describePrepared": it returns 1 if it was able to dispatch the request, and 0 if not. After a successful call, call "result" to obtain the results.

It will not work on 2.0-protocol connections.

$ok = $dbc->sendDescribePortal($name)

Submits a request to obtain information about the specified portal, without waiting for completion.

This is an asynchronous version of "describePortal": it returns 1 if it was able to dispatch the request, and 0 if not. After a successful call, call "result" to obtain the results.

It will not work on 2.0-protocol connections.

$res = $dbc->result

(wraps PQgetResult)

This method waits for the next result from a prior "sendQuery", "sendPrepare", "sendQueryPrepared", "sendDescribePrepare" or "sendDescribePortal" method call, and returns it. "undef" is returned when the command is complete and there will be no more results.

result must be called repeatedly until it returns undef indicating that the command is done (if called when no command is active, result will just return undef at once).

Each non undefined result from result should be processed using the accessor methods for the Pg::PQ::Result class described below.

Note that result will block only if a command is active and the necessary response data has not yet been read by "consumeInput".

Using sendQuery and result solves one of "exec"'s problems: if a command string contains multiple SQL commands, the results of those commands can be obtained individually.

This allows a simple form of overlapped processing, by the way: the client can be handling the results of one command while the server is still working on later queries in the same command string.

However, calling result will still cause the client to block until the server completes the next SQL command. This can be avoided by proper use of the consumeInput and "busy" methods described next.

$ok = $dbc->consumeInput

If input is available from the server, consume it.

consumeInput normally returns 1 indicating "no error", but returns 0 if there was some kind of trouble (in which case "errorMessage" can be consulted). Note that the result does not say whether any input data was actually collected.

After calling consumeInput, the application can check "busy" and/or "notifies" to see if their state has changed.

consumeInput can be called even if the application is not prepared to deal with a result or notification just yet. The method will read available data and save it in a buffer, thereby causing a select read-ready indication to go away.

$ok = $dbc->busy

Returns 1 if a command is busy, that is, "result" would block waiting for input. A 0 return indicates that result can be called with assurance of not blocking.

busy will not itself attempt to read data from the server; therefore "consumeInput" must be invoked first, or the busy state will never end.

$nb = $dbc->nonBlocking

$dbc->nonBlocking($bool)

This methods get and sets the non blocking status of the database connection.

$dbc->flush

Attempts to flush any queued output data to the server. Returns 0 if successful (or if the send queue is empty), -1 if it failed for some reason, or 1 if it was unable to send all the data in the send queue yet (this case can only occur if the connection is nonblocking).

$dbc->notifies

Returns the name of the next notification from the list of unhandled notification messages received from the server or undef if the list is empty. See "Asynchronous notification" below.

On list context besides the notification name, the pid of the originating process and the payload are also returned. For instance:

  my ($name, $pid, $extra) = $dbc->notifies;

$esc = $dbc->escapeLiteral($literal)

escapeLiteral escapes a string for use within an SQL command. This is useful when inserting data values as literal constants in SQL commands. Certain characters (such as quotes and backslashes) must be escaped to prevent them from being interpreted specially by the SQL parser.

The return string has all special characters replaced so that they can be properly processed by the PostgreSQL string literal parser. The single quotes that must surround PostgreSQL string literals are included in the result string.

On error, escapeLiteral returns undef and a suitable message is stored in the Pg::PQ::Conn object.

$esc = $conn->escapeIdentifier($identifier)

escapeIdentifier escapes a string for use as an SQL identifier, such as a table, column, or function name. This is useful when a user-supplied identifier might contain special characters that would otherwise not be interpreted as part of the identifier by the SQL parser, or when the identifier might contain upper case characters whose case should be preserved.

escapeIdentifier returns a version of the str parameter escaped as an SQL identifier. The return string has all special characters replaced so that it will be properly processed as an SQL identifier.

The return string will also be surrounded by double quotes.

On error, escapeIdentifier returns undef and a suitable message is stored in the connection object.

$esc = $dbc->escapeString($str)

escapeString escapes string literals, much like "escapeLiteral" but it does not generate the single quotes that must surround PostgreSQL string literals; they should be provided in the SQL command that the result is inserted into.

Returns undef on error (presently the only possible error conditions involve invalid multibyte encoding in the source string) and a suitable error message is stored in the connection object.

Pg::PQ::Result class

$status = $res->status

Returns the result status of the command.

$status can take one of the following values:

PGRES_EMPTY_QUERY: The string sent to the server was empty.
PGRES_COMMAND_OK: Successful completion of a command returning no data.
PGRES_TUPLES_OK: Successful completion of a command returning data (such as a SELECT or SHOW).
PGRES_COPY_OUT: Copy Out (from server) data transfer started.
PGRES_COPY_IN: Copy In (to server) data transfer started.
PGRES_BAD_RESPONSE: The server's response was not understood.
PGRES_NONFATAL_ERROR: A nonfatal error (a notice or warning) occurred.
PGRES_FATAL_ERROR: A fatal error occurred.

If the result status is PGRES_TUPLES_OK, then the functions described below can be used to retrieve the rows returned by the query.

Note that a SELECT command that happens to retrieve zero rows still shows PGRES_TUPLES_OK. PGRES_COMMAND_OK is for commands that can never return rows (INSERT, UPDATE, etc.). A response of PGRES_EMPTY_QUERY might indicate a bug in the client software.

A result of status PGRES_NONFATAL_ERROR will never be returned directly by "exec" or other query execution methods; results of this kind are instead passed to the notice processor (see Section 31.11).

# FIXME: revise last paragraph notice processor reference.

$str = $res->statusMessage

Returns the status as a human readable string.

$err = $res->errorMessage

Returns the error message associated with the command or an empty string is there was no error.

Immediately following a Pg::PQ::Conn::exec or Pg::PQ::Conn::result call, $dbc->errorMessage (on the connection object) will return the same string as $res->errorMessage (on the result). However, a Pg::PQ::Result will retain its error message until destroyed, whereas the connection's error message will change when subsequent operations are done.

$field = $res->errorField($fieldCode)

Returns an individual field of an error report.

$desc = $res->errorDescription

Return a hash reference whose entries describe the error as follows:

severity

The severity. The field contents are ERROR, FATAL, or PANIC (in an error message), or WARNING, NOTICE, DEBUG, INFO, or LOG (in a notice message), or a localized translation of one of these. Always present.

sqlstate

The SQLSTATE code for the error.

The SQLSTATE code identifies the type of error that has occurred; it can be used by front-end applications to perform specific operations (such as error handling) in response to a particular database error. For a list of the possible SQLSTATE codes, see Appendix A of the PostgreSQL documentation.

This field is not localizable, and is always present.

primary

The primary human-readable error message (typically one line). Always present.

detail

Detail. An optional secondary error message carrying more detail about the problem. Might run to multiple lines.

hint

Hint. An optional suggestion what to do about the problem. This is intended to differ from detail in that it offers advice (potentially inappropriate) rather than hard facts. Might run to multiple lines.

statement_position

An integer indicating an error cursor position as an index into the original statement string. The first character has index 1, and positions are measured in characters not bytes.

internal_position

This is defined the same as the statement_position field, but it is used when the cursor position refers to an internally generated command rather than the one submitted by the client.

The internal_query field will always appear when this field appears.

internal_query

The text of a failed internally-generated command. This could be, for example, a SQL query issued by a PL/pgSQL function.

context

An indication of the context in which the error occurred. Presently this includes a call stack traceback of active procedural language functions and internally-generated queries. The trace is one entry per line, most recent first.

source_file

The file name of the source-code location where the error was reported.

source_line

The line number of the source-code location where the error was reported.

source_function

The name of the source-code function reporting the error.

The client is responsible for formatting displayed information to meet its needs; in particular it should break long lines as needed. Newline characters appearing in the error message fields should be treated as paragraph breaks, not line breaks.

Errors generated internally by libpq will have severity and primary message, but typically no other fields. Errors returned by a pre-3.0-protocol server will include severity and primary message, and sometimes a detail message, but no other fields.

Note that error fields are only available from Pg::PQ::Result objects; there is no Pq::PQ::Conn::errorDescription method.

$n = $res->nRows

Returns the number of rows in the query result.

$n = $res->nColumns

Returns the number of columns in the query result.

$name = $res->columnName($index)

Returns the column name associated with the given column number. Column numbers start at 0.

@names = $res->columnNames

Returns a list with the response column names.

$n = $res->columnNumber($column_name)

Returns the column number associated with the given column name.

-1 is returned if the given name does not match any column.

The given name is treated like an identifier in an SQL command, that is, it is downcased unless double-quoted. For example, given a query result generated from the SQL command:

  SELECT 1 AS FOO, 2 AS "BAR";

we would have the results:

  $res->columnName(0);          # foo
  $res->columnName(1);          # BAR
  $res->columnNumber('FOO');    # 0
  $res->columnNumber('foo');    # 0
  $res->columnNumber('BAR');    # -1
  $res->columnNumber('"BAR"');  # 1

$oid = $res->columnTable($index)

Returns the OID of the table from which the given column was fetched. Column numbers start at 0.

undef is returned if the column number is out of range, or if the specified column is not a simple reference to a table column, or when using pre-3.0 protocol. You can query the system table pg_class to determine exactly which table is referenced.

$col = $res->columnTableColumn($index)

Returns the column number (within its table) of the column making up the specified query result column. Query-result column numbers start at 0, but table columns have nonzero numbers.

$isNull = $res->null($row, $column)

Tests a field for a null value. Row and column numbers start at 0.

This function returns 1 if the field is null and 0 if it contains a non-null value.

$data = $res->value($row, $column)

Returns a single field value of one row. Row and column numbers start at 0.

@fields = $res->row($index)

Returns a list of the fields in the indicated row.

$h = $res->rowAsHash($index)

$h = $res->rowAsHash($index, @field_names)

Returns the row as a hash.

The field names to be used as the hash keys can be given, otherwise they are extracted from the result object.

@fields = $res->column($index)

Return a list of the fields in the indicated column.

$nRows = $res->rows

@rows = $res->rows

In scalar context this method returns the number of rows in the result set.

In list context it return a list of arrays containing the values on every row of the result set.

@hashes = $res->rowsAsHashes

@hashes = $res->rowsAsHashes(@field_names)

Returns a list of hashes with the result values (see also rowAsHash).

$nColumns = $res->columns

@columns = $res->columns

In scalar context this method returns the number of columns in the result set.

In list context it return a list of arrays containing the values on every column of the result set.

$status = $res->cmdStatus

Returns the command status tag from the SQL command that generated the PGresult.

Commonly this is just the name of the command, but it might include additional data such as the number of rows processed.

$nRows = $res->cmdRows

Returns the number of rows affected by the SQL command.

This function returns a string containing the number of rows affected by the SQL statement that generated the Pg::PQ::Result object. This function can only be used following the execution of a SELECT, CREATE TABLE AS, INSERT, UPDATE, DELETE, MOVE, FETCH, or COPY statement, or an EXECUTE of a prepared query that contains an INSERT, UPDATE, or DELETE statement. If the command that generated the result object was anything else, cmdRows returns undef.

$oid = $res->oidValue

Returns the OID of the inserted row, if the SQL command was an INSERT that inserted exactly one row into a table that has OIDs, or a EXECUTE of a prepared query containing a suitable INSERT statement.

Otherwise, this function returns undef. This function will also return undef if the table affected by the INSERT statement does not contain OIDs.

$n = $res->nParams

Return the number of parameters on the prepared query.

$oid = $res->paramType($ix)

Returns the type of the parameter at the given index on a prepared query.

Pg::PQ::Cancel class

The cancel object is an artifact provided by the C libpq library to allow interrupting database requests from signal handlers or from other threads.

Due to the way signals and threads are handled in Perl it becomes mostly useless here so the functionality is currently disabled.

You can use the cancel method from the Pg::PQ::Conn and non-blocking request to obtain a similar functionality.

Constants

The following constants can be imported from this module:

:copyres

  PG_COPYRES_ATTRS
  PG_COPYRES_TUPLES
  PG_COPYRES_EVENTS
  PG_COPYRES_NOTICEHOOKS

:connection

  CONNECTION_OK
  CONNECTION_BAD
  CONNECTION_STARTED
  CONNECTION_MADE
  CONNECTION_AWAITING_RESPONSE
  CONNECTION_AUTH_OK
  CONNECTION_SETENV
  CONNECTION_SSL_STARTUP
  CONNECTION_NEEDED

:pgres_polling

  PGRES_POLLING_FAILED
  PGRES_POLLING_READING
  PGRES_POLLING_WRITING
  PGRES_POLLING_OK
  PGRES_POLLING_ACTIVE

:pgres

  PGRES_EMPTY_QUERY
  PGRES_COMMAND_OK
  PGRES_TUPLES_OK
  PGRES_COPY_OUT
  PGRES_COPY_IN
  PGRES_BAD_RESPONSE
  PGRES_NONFATAL_ERROR
  PGRES_FATAL_ERROR

:pqtrans

  PQTRANS_IDLE
  PQTRANS_ACTIVE
  PQTRANS_INTRANS
  PQTRANS_INERROR
  PQTRANS_UNKNOWN

:pqerrors

  PQERRORS_TERSE
  PQERRORS_DEFAULT
  PQERRORS_VERBOSE

Miscellaneous subs

$ver = Pg::PQ::libVersion(): Returns the version of the libpq library used to compile this module.

Non-blocking database access

Non-blocking connecting to the database

To begin a nonblocking connection request, call $dbc = Pg::PQ->start($conninfo). If $dbc is undefined, then libpq has been unable to allocate a new Pg::PQ::Conn object. Otherwise, a valid Pg::PQ::Conn object is returned (though not yet representing a valid connection to the database).

On return from start, call $status = $dbc->status. If $status equals CONNECTION_BAD, start has failed.

If start succeeds, the next stage is to poll libpq so that it can proceed with the connection sequence. Use socket to obtain the descriptor of the socket underlying the database connection.

Loop thus:

If connectPoll last returned PGRES_POLLING_READING, wait until the socket is ready to read (as indicated by select, poll, or similar system function). Then call connectPoll again.
Conversely, if connectPoll last returned PGRES_POLLING_WRITING, wait until the socket is ready to write, then call connectPoll again.
If you have yet to call connectPoll, i.e., just after the call to start, behave as if it last returned PGRES_POLLING_WRITING.
Continue this loop until connectPoll returns PGRES_POLLING_FAILED, indicating the connection procedure has failed, or PGRES_POLLING_OK, indicating the connection has been successfully made.

At any time during connection, the status of the connection can be checked by calling status. If this gives CONNECTION_BAD, then the connection procedure has failed; if it gives CONNECTION_OK, then the connection is ready. Both of these states are equally detectable from the return value of connectPoll, described above.

Other states might also occur during (and only during) an asynchronous connection procedure. These indicate the current stage of the connection procedure and might be useful to provide feedback to the user for example. These statuses are:

CONNECTION_STARTED: Waiting for connection to be made.
CONNECTION_MADE: Connection OK; waiting to send.
CONNECTION_AWAITING_RESPONSE: Waiting for a response from the server.
CONNECTION_AUTH_OK: Received authentication; waiting for backend start-up to finish.
CONNECTION_SSL_STARTUP: Negotiating SSL encryption.
CONNECTION_SETENV: Negotiating environment-driven parameter settings.

Note that, although these constants will remain (in order to maintain compatibility), an application should never rely upon these occurring in a particular order, or at all, or on the status always being one of these documented values. An application might do something like this:

  given($dbc->status) {
      when (CONNECTION_STARTED) {
          say "Connecting...";
      }
      when (CONNECTION_MADE) {
          say "Connected to server...";
      }
      ...
      default {
          say "Connecting...";
      }
  }

The connect_timeout connection parameter is ignored when using start and connectPoll; it is the application's responsibility to decide whether an excessive amount of time has elapsed. Otherwise, start followed by a connectPoll loop is equivalent to new.

Non-blocking quering the database

A typical non-blocking application will have a main loop that uses select or poll to wait for all the conditions that it must respond to.

After some query is dispatched to the database using any of the asynchronous send methods (sendQuery, sendPrepare, sendQueryPrepared, sendDescribePrepared or sendDescribePortal) one of the conditions will be input available from the server, which in terms of select means readable data on the file descriptor identified by socket.

When the main loop detects input ready, it should call consumeInput to read the input. It can then call isBusy, followed by result if busy returns false (0).

It can also call notifies to detect NOTIFY messages (see Section 31.7 of the PostgreSQL documentation).

A client that uses sendQuery/result can also attempt to cancel a command that is still being processed by the server (see Section 31.5 of the PostgreSQL documentation). But regardless of the return value of cancel, the application must continue with the normal result-reading sequence using result. A successful cancellation will simply cause the command to terminate sooner than it would have otherwise.

By using the functions described above, it is possible to avoid blocking while waiting for input from the database server. However, it is still possible that the application will block waiting to send output to the server. This is relatively uncommon but can happen if very long SQL commands or data values are sent (it is much more probable if the application sends data via COPY IN, however).

To prevent this possibility and achieve completely nonblocking database operation, the nonblocking mode has to be activated for the session using $dbc->nonBlocking(1).

After sending any command or data on a nonblocking connection, call flush. If it returns 1, wait for the socket to be write-ready and call it again; repeat until it returns 0. Once flush returns 0, wait for the socket to be read-ready and then read the response as described above.

Asynchronous notifications

PostgreSQL offers asynchronous notification via the LISTEN and NOTIFY commands. A client session registers its interest in a particular notification channel with the LISTEN command (and can stop listening with the UNLISTEN command). All sessions listening on a particular channel will be notified asynchronously when a NOTIFY command with that channel name is executed by any session. A "payload" string can be passed to communicate additional data to the listeners.

libpq applications submit LISTEN, UNLISTEN, and NOTIFY commands as ordinary SQL commands. The arrival of NOTIFY messages can subsequently be detected by calling notifies.

The method notifies returns the next notification (Pg::PQ::Notify) from a list of unhandled notification messages received from the server. It returns undef if there are no pending notifications.

Once a notification is returned from notifies, it is considered handled and will be removed from the list of notifications.

notifies does not actually read data from the server; it just returns messages previously absorbed by another libpq function.

In prior releases of libpq, the only way to ensure timely receipt of NOTIFY messages was to constantly submit commands, even empty ones, and then check notifies after each exec. While this still works, it is deprecated as a waste of processing power.

A better way to check for NOTIFY messages when you have no useful commands to execute is to call consumeInput, then check notifies. You can use the select builtin to wait for data to arrive from the server, thereby using no CPU power unless there is something to do (see socket to obtain the file descriptor number to use with select).

Note that this will work OK whether you submit commands with sendQuery/result or simply use exec. You should, however, remember to check notifies after each result or exec, to see if any notifications came in during the processing of the command.

TODO

Supoprt binary data transfer.
Wrap the COPY API.
Non-blocking cancels.
Write a test suite.

BUGS AND SUPPORT

This is a very early release that may contain lots of bugs.

Send bug reports by email or using the CPAN bug tracker at https://rt.cpan.org/Dist/Display.html?Status=Active&Queue=Pg-PQ.

Known bugs and limitations

Currently all the data is transferred as text, that means that strings are truncated at the first '\0' character.
Currently the utf-8 encoding is hard-coded into the wrapper. In theory, this is the *right thing to do* as postgres is able to convert from/to client utf8 to the server representation.

But anyway, if you find some encoding related problem when connecting to a database configured to use a different encoding, don't hesitate to post a bug report on the module bug tracker!

Commercial support

This module was implemented during the development of QVD (http://theqvd.com) the Linux VDI platform.

Commercial support, professional services and custom software development services around this module are available from QindelGroup (http://qindel.com). Send us an email with a rough description of your requirements and we will get back to you ASAP.

AUTHOR

Salvador Fandiño <sfandino@yahoo.com>

COPYRIGHT AND LICENSE

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.10.1 or, at your option, any later version of Perl 5 you may have available.

The documentation of this module is based on the original libpq documentation that has the following copyright:

To install Pg::PQ, copy and paste the appropriate command in to your terminal.

cpanm

cpanm Pg::PQ

CPAN shell

perl -MCPAN -e shell
install Pg::PQ

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

Pg::PQ::Conn class

Pg::PQ::Result class

Pg::PQ::Cancel class

Constants

Miscellaneous subs

Non-blocking database access

Non-blocking connecting to the database

Non-blocking quering the database

Asynchronous notifications

SEE ALSO

TODO

BUGS AND SUPPORT

Known bugs and limitations

Commercial support

AUTHOR

COPYRIGHT AND LICENSE

Module Install Instructions