libpq The libpq library is a C language API that provides access to the PostgreSQL back end. In fact, most of the provided client tools (like psql ) use this library as their connection route to the back end. The libpq provides many functions that can control nearly every aspect of the client/server. Although an in-depth discussion of every function is outside the scope of this chapter, the two most popular functions it provides are as follows : PQconnectdb also provides the following functions: PQconnectdb and PQexec are both discussed in more detail in the following sections. PQconnectdb The PQconnectdb function accepts several options as shown here. In this example, your user -defined object name is PGconnectID , but it could be anything. PGconnectID *PQconnectdb(const char * conninfo ) conninfo would contain one of the following (in the form option=value ): | Option | Description | | host | The hostname (or UNIX path ) to connect to. | | hostaddr | For TCP/IP connections, the IP address. | | port | The port of the server. | | dbname | The specific database to connect to. | | user | Connect as this user. | | password | If authentication is required, the password. | | options | Trace/debug options. | | tty | The file or tty to send debugging information to. | | requiressl | Set to 1 to mandate SSL connections. | If the conninfo string is not specified, the following environmental variables can be set to specify the connection options: | PGDATABASE | Sets the database name. | | PGDATESTYLE | Sets the default date style. | | PGGEQO | Sets the default Genetic Optimizer. | | PGHOST | Sets the database host. | | PGOPTIONS | Sets various run-time options. | | PGPASSWORD | Sets the user's password. | | PGPORT | Sets the server port. | | PGREALM | Sets the Kerberos realm. | | PGTTY | Sets the debug/error tty or file. | | PGTZ | Sets the default time zone. | | PGUSER | Sets the username. | The following functions depend on the connection pointer returned earlier ( PGconnectID , in this case). -
char *PQdb(const PGconnectID, *conn) Returns the name of the currently connected database. -
char *PQuser(const PGconnectID, *conn) Returns the name of the currently connected user. -
char *PQpass (const PGconnectID, *conn) Returns the password of the current connection. -
char *PQhost (const PGconnectID, *conn) Returns the hostname of the back-end server. -
char *PQport (const PGconnectID, *conn) Returns the server port of the current connection. -
char *PQtty (const PGconnectID, *conn) Returns the debug file/ tty of the connection. -
char *PQoptions (const PGconnectID, *conn) Returns the debug/trace connection options. -
char *PQerrormessage (const PGconnectID, *conn) Returns the last error message generated by the connection. -
int *PQbackendPID (const PGconnectID, *conn) Returns the PID of the back-end server process. -
ConnStatusType PQstatus(const PGconnectID, *conn) Returns one of the following status conditions: CONNECTION_STARTED CONNECTION_MADE CONNECTION_AWAITING_RESPONSE CONNECTION_AUTH_OK CONNECTION_SETENV CONNECTION_OK CONNECTION_BAD PQconnectdb also provides these functions: -
void PQreset(PGconn *conn) Resets the communication port with the back end. -
void PQfinish(PGconn *conn) Closes the connection to the back end and frees memory used by the PGconn object. PQexec The PQexec function sends a requested query to a connected back-end server. When a response is received, it is stored in a pointer. In this case, the pointer is called PGresult ; however, this is variable. PGresult *PQexec(PGconn *conn, const char *query) The following functions act on the preceding returned pointer ( PGresult ): -
char *PQresultErrorMessage(const PGresult *res) This function returns the last error message specifically associated with a particular PGresult . This function differs from PQerrormessage , which returns the last error associated with a particular connection but not a specific result. -
int PQntuples(const PGresult *res) Returns the number of rows in the query result. -
int PQnfields(const PGresult *res) Returns the number of fields in each row of the query result. -
char *PQfname(const PGresult *res, int field_index) Returns the field name associated with the given field index. Field indices start at 0. -
int PQfnumber(const PGresult *res, const char *field_name) Returns the field index associated with the specified field name. A value of “1 is returned if the given name does not match any field. -
Oid PQftype(const PGresult *res, int field_index) Returns an integer that represents the field type associated with the field index. The system table pg_type contains the names and properties of the various data types. The OIDs of the built-in data types are defined in src/include/catalog/pg_type.h in the source tree. -
int PQfsize(const PGresult *res, int field_index) Returns the number of bytes of field data in the specified field index. -
char* PQgetvalue(const PGresult *res, int tup_num, int field_num) Returns a single field value of one row of a PGresult . In most instances, the value returned by PQgetvalue is a null- terminated ASCII representation of the value. -
int PQgetlength(const PGresult *res, int tup_num, int field_num) Returns the length of a field in bytes. -
int PQgetisnull(const PGresult *res, int tup_num, int field_num) Returns a value of 1 if the field is NULL ; otherwise , returns a 0. -
char * PQcmdTuples(const PGresult *res) Returns the number of rows affected by the SQL command. This function only measures the effects of INSERT , DELETE , or UPDATE commands. -
Oid PQoidValue(const PGresult *res) If the SQL command was an INSERT , returns the OID of the tuple inserted; otherwise, returns InvalidOid . -
void PQclear(PQresult *res) Frees the storage associated with the PGresult . Every query result should be freed via PQclear when it is no longer needed. PGresult does not go away after use, even if the connection is closed. Failure to do this will result in memory leaks in the front-end application. |
