libpq C libpq is the C application programmer's interface to PostgreSQL. libpq is a set of library functions that allow client programs to pass queries to the PostgreSQL backend server and to receive the results of these queries. libpq is also the underlying engine for several other PostgreSQL application interfaces, including those written for C++, Perl, Python, Tcl and ECPG. So some aspects of libpq's behavior will be important to you if you use one of those packages. In particular, , and describe behavior that is visible to the user of any application that uses libpq. Some short programs are included at the end of this chapter () to show how to write programs that use libpq. There are also several complete examples of libpq applications in the directory src/test/examples in the source code distribution. Client programs that use libpq must include the header file libpq-fe.hlibpq-fe.h and must link with the libpq library. The following functions deal with making a connection to a PostgreSQL backend server. An application program can have several backend connections open at one time. (One reason to do that is to access more than one database.) Each connection is represented by a PGconnPGconn object, which is obtained from the function PQconnectdb, PQconnectdbParams, or PQsetdbLogin. Note that these functions will always return a non-null object pointer, unless perhaps there is too little memory even to allocate the PGconn object. The PQstatus function should be called to check whether a connection was successfully made before queries are sent via the connection object. On Unix, forking a process with open libpq connections can lead to unpredictable results because the parent and child processes share the same sockets and operating system resources. For this reason, such usage is not recommended, though doing an exec from the child process to load a new executable is safe. On Windows, there is a way to improve performance if a single database connection is repeatedly started and shutdown. Internally, libpq calls WSAStartup() and WSACleanup() for connection startup and shutdown, respectively. WSAStartup() increments an internal Windows library reference count which is decremented by WSACleanup(). When the reference count is just one, calling WSACleanup() frees all resources and all DLLs are unloaded. This is an expensive operation. To avoid this, an application can manually call WSAStartup() so resources will not be freed when the last database connection is closed. PQconnectdbParamsPQconnectdbParams Makes a new connection to the database server. PGconn *PQconnectdbParams(const char **keywords, const char **values, int expand_dbname); This function opens a new database connection using the parameters taken from two NULL-terminated arrays. The first, keywords, is defined as an array of strings, each one being a key word. The second, values, gives the value for each key word. Unlike PQsetdbLogin below, the parameter set can be extended without changing the function signature, so use of this function (or its nonblocking analogs PQconnectStartParams and PQconnectPoll) is preferred for new application programming. When expand_dbname is non-zero, the dbname key word value is allowed to be recognized as a conninfo string. See below for details. The passed arrays can be empty to use all default parameters, or can contain one or more parameter settings. They should be matched in length. Processing will stop with the last non-NULL element of the keywords array. The currently recognized parameter key words are: host Name of host to connect to.host name 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 socketUnix 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 ). 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.port 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 . application_name Specifies a value for the configuration parameter. fallback_application_name Specifies a fallback value for the 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. tty Ignored (formerly, this specified where to send server debug output). 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: Option Description 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 See for a detailed description of how these options work. 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.SSLwith libpq requiressl This option is deprecated in favor of the sslmode setting. If set to 1, an SSL connection to the server is required (this is equivalent to sslmode require). libpq will then refuse to connect if the server does not accept an SSL connection. If set to 0 (default), libpq will negotiate the connection type with the server (equivalent to sslmode prefer). This option is only available if PostgreSQL is compiled with SSL support. 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. requirepeer For Unix-domain socket connections, if this parameter is set, the client checks at the beginning of the connection that the server process runs under the specified user name, otherwise the connection is aborted with an error. This parameter can be used to achieve the kind of server authentication that SSL certificates achieve on TCP/IP connections. (Note that if the Unix-domain socket is in /tmp or another publically writable location, any user could start a server there. Use this parameter to ensure that you are connected to a server run by a trusted user, e.g., requirepeer=postgres.) This option is only supported on some platforms, currently Linux, FreeBSD, NetBSD, OpenBSD, and Solaris. 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 and .) 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 . If any parameter is unspecified, then the corresponding environment variable (see ) is checked. If the environment variable is not set either, then the indicated built-in defaults are used. If expand_dbname is non-zero and dbname contains an = sign, it is taken as a conninfo string in exactly the same way as if it had been passed to PQconnectdb(see below). Previously processed key words will be overridden by key words in the conninfo string. In general key words are processed from the beginning of these arrays in index order. The effect of this is that when key words are repeated, the last processed value is retained. Therefore, through careful placement of the dbname key word, it is possible to determine what may be overridden by a conninfo string, and what may not. PQconnectdbPQconnectdb Makes a new connection to the database server. PGconn *PQconnectdb(const char *conninfo); This function opens a new database connection using the parameters taken from the string conninfo. The passed string can be empty to use all default parameters, or it can contain one or more parameter settings separated by whitespace. Each parameter setting is in the form keyword = value. Spaces around the equal sign are optional. To write an empty value, or a value containing spaces, surround it with single quotes, e.g., keyword = 'a value'. Single quotes and backslashes within the value must be escaped with a backslash, i.e., \' and \\. The currently recognized parameter key words are the same as above. PQsetdbLoginPQsetdbLogin Makes a new connection to the database server. PGconn *PQsetdbLogin(const char *pghost, const char *pgport, const char *pgoptions, const char *pgtty, const char *dbName, const char *login, const char *pwd); This is the predecessor of PQconnectdb with a fixed set of parameters. It has the same functionality except that the missing parameters will always take on default values. Write NULL or an empty string for any one of the fixed parameters that is to be defaulted. If the dbName contains an = sign, it is taken as a conninfo string in exactly the same way as if it had been passed to PQconnectdb, and the remaining parameters are then applied as above. PQsetdbPQsetdb Makes a new connection to the database server. PGconn *PQsetdb(char *pghost, char *pgport, char *pgoptions, char *pgtty, char *dbName); This is a macro that calls PQsetdbLogin with null pointers for the login and pwd parameters. It is provided for backward compatibility with very old programs. PQconnectStartParamsPQconnectStartParams PQconnectStartPQconnectStart PQconnectPollPQconnectPoll nonblocking connection Make a connection to the database server in a nonblocking manner. PGconn *PQconnectStartParams(const char **keywords, const char **values, int expand_dbname); PGconn *PQconnectStart(const char *conninfo); PostgresPollingStatusType PQconnectPoll(PGconn *conn); These three functions 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 PQconnectdbParams or PQconnectdb, and so the application can manage this operation in parallel with other activities. With PQconnectStartParams, the database connection is made using the parameters taken from the keywords and values arrays, and controlled by expand_dbname, as described above for PQconnectdbParams. With PQconnectStart, the database connection is made using the parameters taken from the string conninfo as described above for PQconnectdb. Neither PQconnectStartParams nor PQconnectStart nor PQconnectPoll 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 PQconnectdbParams above for details. If you call PQtrace, ensure that the stream object into which you trace will not block. You ensure that the socket is in the appropriate state before calling PQconnectPoll, as described below. Note: use of PQconnectStartParams is analogous to PQconnectStart shown below. To begin a nonblocking connection request, call conn = PQconnectStart("connection_info_string"). If conn is null, then libpq has been unable to allocate a new PGconn structure. Otherwise, a valid PGconn pointer is returned (though not yet representing a valid connection to the database). On return from PQconnectStart, call status = PQstatus(conn). If status equals CONNECTION_BAD, PQconnectStart has failed. If PQconnectStart succeeds, the next stage is to poll libpq so that it can proceed with the connection sequence. Use PQsocket(conn) to obtain the descriptor of the socket underlying the database connection. Loop thus: If PQconnectPoll(conn) last returned PGRES_POLLING_READING, wait until the socket is ready to read (as indicated by select(), poll(), or similar system function). Then call PQconnectPoll(conn) again. Conversely, if PQconnectPoll(conn) last returned PGRES_POLLING_WRITING, wait until the socket is ready to write, then call PQconnectPoll(conn) again. If you have yet to call PQconnectPoll, i.e., just after the call to PQconnectStart, behave as if it last returned PGRES_POLLING_WRITING. Continue this loop until PQconnectPoll(conn) 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 PQstatus. 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 PQconnectPoll, 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: switch(PQstatus(conn)) { case CONNECTION_STARTED: feedback = "Connecting..."; break; case CONNECTION_MADE: feedback = "Connected to server..."; break; . . . default: feedback = "Connecting..."; } The connect_timeout connection parameter is ignored when using PQconnectPoll; it is the application's responsibility to decide whether an excessive amount of time has elapsed. Otherwise, PQconnectStart followed by a PQconnectPoll loop is equivalent to PQconnectdb. Note that if PQconnectStart returns a non-null pointer, you must call PQfinish when you are finished with it, in order to dispose of the structure and any associated memory blocks. This must be done even if the connection attempt fails or is abandoned. PQconndefaultsPQconndefaults Returns the default connection options. PQconninfoOption *PQconndefaults(void); typedef struct { char *keyword; /* The keyword of the option */ char *envvar; /* Fallback environment variable name */ char *compiled; /* Fallback compiled in default value */ char *val; /* Option's current value, or NULL */ char *label; /* Label for field in connect dialog */ char *dispchar; /* Indicates how to display this field in a connect dialog. Values are: "" Display entered value as is "*" Password field - hide value "D" Debug option - don't show by default */ int dispsize; /* Field size in characters for dialog */ } PQconninfoOption; Returns a connection options array. This can be used to determine all possible PQconnectdb options and their current default values. The return value points to an array of PQconninfoOption structures, which ends with an entry having a null keyword pointer. The null pointer is returned if memory could not be allocated. Note that the current default values (val fields) will depend on environment variables and other context. Callers must treat the connection options data as read-only. After processing the options array, free it by passing it to PQconninfoFree. If this is not done, a small amount of memory is leaked for each call to PQconndefaults. PQconninfoParsePQconninfoParse Returns parsed connection options from the provided connection string. PQconninfoOption *PQconninfoParse(const char *conninfo, char **errmsg); Parses a connection string and returns the resulting options as an array; or returns NULL if there is a problem with the connection string. This can be used to determine the PQconnectdb options in the provided connection string. The return value points to an array of PQconninfoOption structures, which ends with an entry having a null keyword pointer. Note that only options explicitly specified in the string will have values set in the result array; no defaults are inserted. If errmsg is not NULL, then *errmsg is set to NULL on success, else to a malloc'd error string explaining the problem. (It is also possible for *errmsg to be set to NULL even when NULL is returned; this indicates an out-of-memory situation.) After processing the options array, free it by passing it to PQconninfoFree. If this is not done, some memory is leaked for each call to PQconninfoParse. Conversely, if an error occurs and errmsg is not NULL, be sure to free the error string using PQfreemem. PQfinishPQfinish Closes the connection to the server. Also frees memory used by the PGconn object. void PQfinish(PGconn *conn); Note that even if the server connection attempt fails (as indicated by PQstatus), the application should call PQfinish to free the memory used by the PGconn object. The PGconn pointer must not be used again after PQfinish has been called. PQresetPQreset Resets the communication channel to the server. void PQreset(PGconn *conn); 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. PQresetStartPQresetStart PQresetPollPQresetPoll Reset the communication channel to the server, in a nonblocking manner. int PQresetStart(PGconn *conn); PostgresPollingStatusType PQresetPoll(PGconn *conn); 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 PQreset (above) in that they act in a nonblocking manner. These functions suffer from the same restrictions as PQconnectStartParams, PQconnectStart and PQconnectPoll. To initiate a connection reset, call PQresetStart. If it returns 0, the reset has failed. If it returns 1, poll the reset using PQresetPoll in exactly the same way as you would create the connection using PQconnectPoll. These functions can be used to interrogate the status of an existing database connection object. libpq-fe.h libpq-int.h libpq application programmers should be careful to maintain the PGconn abstraction. Use the accessor functions described below to get at the contents of PGconn. Reference to internal PGconn fields using libpq-int.h is not recommended because they are subject to change in the future. The following functions return parameter values established at connection. These values are fixed for the life of the PGconn object. PQdb PQdb Returns the database name of the connection. char *PQdb(const PGconn *conn); PQuser PQuser Returns the user name of the connection. char *PQuser(const PGconn *conn); PQpass PQpass Returns the password of the connection. char *PQpass(const PGconn *conn); PQhost PQhost Returns the server host name of the connection. char *PQhost(const PGconn *conn); PQport PQport Returns the port of the connection. char *PQport(const PGconn *conn); PQtty PQtty Returns the debug TTY of the connection. (This is obsolete, since the server no longer pays attention to the TTY setting, but the function remains for backwards compatibility.) char *PQtty(const PGconn *conn); PQoptions PQoptions Returns the command-line options passed in the connection request. char *PQoptions(const PGconn *conn); The following functions return status data that can change as operations are executed on the PGconn object. PQstatus PQstatus Returns the status of the connection. ConnStatusType PQstatus(const PGconn *conn); 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 PQfinish, 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 PQreset. See the entry for PQconnectStartParams, PQconnectStart and PQconnectPoll with regards to other status codes that might be seen. PQtransactionStatus PQtransactionStatus Returns the current in-transaction status of the server. PGTransactionStatusType PQtransactionStatus(const PGconn *conn); The status can be PQTRANS_IDLE (currently idle), PQTRANS_ACTIVE (a command is in progress), PQTRANS_INTRANS (idle, in a valid transaction block), or PQTRANS_INERROR (idle, in a failed transaction block). PQTRANS_UNKNOWN is reported if the connection is bad. PQTRANS_ACTIVE is reported only when a query has been sent to the server and not yet completed. PQtransactionStatus 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. PQparameterStatus PQparameterStatus Looks up a current parameter setting of the server. const char *PQparameterStatus(const PGconn *conn, const char *paramName); Certain parameter values are reported by the server automatically at connection startup or whenever their values change. PQparameterStatus can be used to interrogate these settings. It returns the current value of a parameter if known, or NULL 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 cannot 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 PQparameterStatus 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 PQparameterStatus.) For server_version, see also PQserverVersion, 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. Although the returned pointer is declared const, it in fact points to mutable storage associated with the PGconn structure. It is unwise to assume the pointer will remain valid across queries. PQprotocolVersion PQprotocolVersion Interrogates the frontend/backend protocol being used. int PQprotocolVersion(const PGconn *conn); 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.) PQserverVersion PQserverVersion Returns an integer representing the backend version. int PQserverVersion(const PGconn *conn); 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. PQerrorMessage PQerrorMessage error message Returns the error message most recently generated by an operation on the connection. char *PQerrorMessage(const PGconn *conn); Nearly all libpq functions will set a message for PQerrorMessage if they fail. Note that by libpq convention, a nonempty PQerrorMessage result can be multiple lines, and will include a trailing newline. The caller should not free the result directly. It will be freed when the associated PGconn handle is passed to PQfinish. The result string should not be expected to remain the same across operations on the PGconn structure. PQsocketPQsocket 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.) int PQsocket(const PGconn *conn); PQbackendPIDPQbackendPID Returns the process ID (PID)PIDdetermining PID of server processin libpq of the backend server process handling this connection. int PQbackendPID(const PGconn *conn); 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! PQconnectionNeedsPasswordPQconnectionNeedsPassword Returns true (1) if the connection authentication method required a password, but none was available. Returns false (0) if not. int PQconnectionNeedsPassword(const PGconn *conn); This function can be applied after a failed connection attempt to decide whether to prompt the user for a password. PQconnectionUsedPasswordPQconnectionUsedPassword Returns true (1) if the connection authentication method used a password. Returns false (0) if not. int PQconnectionUsedPassword(const PGconn *conn); This function can be applied after either a failed or successful connection attempt to detect whether the server demanded a password. PQgetsslPQgetssl SSLin libpq Returns the SSL structure used in the connection, or null if SSL is not in use. SSL *PQgetssl(const PGconn *conn); This structure can be used to verify encryption levels, check server certificates, and more. Refer to the OpenSSL documentation for information about this structure. You must define USE_SSL in order to get the correct prototype for this function. Doing so will also automatically include ssl.h from OpenSSL. Once a connection to a database server has been successfully established, the functions described here are used to perform SQL queries and commands. PQexec PQexec Submits a command to the server and waits for the result. PGresult *PQexec(PGconn *conn, const char *command); Returns a PGresult pointer or possibly a null pointer. A non-null pointer will generally be returned except in out-of-memory conditions or serious errors such as inability to send the command to the server. If a null pointer is returned, it should be treated like a PGRES_FATAL_ERROR result. Use PQerrorMessage 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 PQexec 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 PGresult structure 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 PGresult describes the error condition. PQexecParams PQexecParams Submits a command to the server and waits for the result, with the ability to pass parameters separately from the SQL command text. PGresult *PQexecParams(PGconn *conn, const char *command, int nParams, const Oid *paramTypes, const char * const *paramValues, const int *paramLengths, const int *paramFormats, int resultFormat); PQexecParams is like PQexec, but offers additional functionality: parameter values can be specified separately from the command string proper, and query results can be requested in either text or binary format. PQexecParams is supported only in protocol 3.0 and later connections; it will fail when using protocol 2.0. The function arguments are: conn The connection object to send the command through. command The SQL command string to be executed. If parameters are used, they are referred to in the command string as $1, $2, etc. nParams The number of parameters supplied; it is the length of the arrays paramTypes[], paramValues[], paramLengths[], and paramFormats[]. (The array pointers can be NULL when nParams is zero.) paramTypes[] Specifies, by OID, the data types to be assigned to the parameter symbols. If paramTypes is NULL, or any particular element in the array is zero, the server infers a data type for the parameter symbol in the same way it would do for an untyped literal string. paramValues[] Specifies the actual values of the parameters. A null pointer in this array means the corresponding parameter is null; otherwise the pointer points to a zero-terminated text string (for text format) or binary data in the format expected by the server (for binary format). paramLengths[] Specifies the actual data lengths of binary-format parameters. It is ignored for null parameters and text-format parameters. The array pointer can be null when there are no binary parameters. paramFormats[] Specifies whether parameters are text (put a zero in the array entry for the corresponding parameter) or binary (put a one in the array entry for the corresponding parameter). If the array pointer is null then all parameters are presumed to be text strings. Values passed in binary format require knowledge of the internal representation expected by the backend. For example, integers must be passed in network byte order. Passing numeric values requires knowledge of the server storage format, as implemented in src/backend/utils/adt/numeric.c::numeric_send() and src/backend/utils/adt/numeric.c::numeric_recv(). resultFormat Specify zero to obtain results in text format, or one to obtain results in binary format. (There is not currently a provision to obtain different result columns in different formats, although that is possible in the underlying protocol.) The primary advantage of PQexecParams over PQexec is that parameter values can be separated from the command string, thus avoiding the need for tedious and error-prone quoting and escaping. Unlike PQexec, PQexecParams allows at most one SQL command in the given string. (There can be semicolons in it, but not more than one nonempty command.) This is a limitation of the underlying protocol, but has some usefulness as an extra defense against SQL-injection attacks. Specifying parameter types via OIDs is tedious, particularly if you prefer not to hard-wire particular OID values into your program. However, you can avoid doing so even in cases where the server by itself cannot determine the type of the parameter, or chooses a different type than you want. In the SQL command text, attach an explicit cast to the parameter symbol to show what data type you will send. For example: SELECT * FROM mytable WHERE x = $1::bigint; This forces parameter $1 to be treated as bigint, whereas by default it would be assigned the same type as x. Forcing the parameter type decision, either this way or by specifying a numeric type OID, is strongly recommended when sending parameter values in binary format, because binary format has less redundancy than text format and so there is less chance that the server will detect a type mismatch mistake for you. PQprepare PQprepare Submits a request to create a prepared statement with the given parameters, and waits for completion. PGresult *PQprepare(PGconn *conn, const char *stmtName, const char *query, int nParams, const Oid *paramTypes); PQprepare creates a prepared statement for later execution with PQexecPrepared. This feature allows commands that will be used repeatedly to be parsed and planned just once, rather than each time they are executed. PQprepare is supported only in protocol 3.0 and later connections; it will fail when using protocol 2.0. The function creates a prepared statement named stmtName from the query string, which must contain a single SQL command. stmtName 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. nParams is the number of parameters for which types are pre-specified in the array paramTypes[]. (The array pointer can be NULL when nParams is zero.) paramTypes[] specifies, by OID, the data types to be assigned to the parameter symbols. If paramTypes is NULL, or any particular element in the array is zero, the server assigns a data type to the parameter symbol in the same way it would do for an untyped literal string. Also, the query can use parameter symbols with numbers higher than nParams; data types will be inferred for these symbols as well. (See PQdescribePrepared for a means to find out what data types were inferred.) As with PQexec, the result is normally a PGresult object whose contents indicate server-side success or failure. A null result indicates out-of-memory or inability to send the command at all. Use PQerrorMessage to get more information about such errors. Prepared statements for use with PQexecPrepared can also be created by executing SQL statements. (But PQprepare is more flexible since it does not require parameter types to be pre-specified.) Also, although there is no libpq function for deleting a prepared statement, the SQL statement can be used for that purpose. PQexecPrepared PQexecPrepared Sends a request to execute a prepared statement with given parameters, and waits for the result. PGresult *PQexecPrepared(PGconn *conn, const char *stmtName, int nParams, const char * const *paramValues, const int *paramLengths, const int *paramFormats, int resultFormat); PQexecPrepared is like PQexecParams, 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. PQexecPrepared is supported only in protocol 3.0 and later connections; it will fail when using protocol 2.0. The parameters are identical to PQexecParams, except that the name of a prepared statement is given instead of a query string, and the paramTypes[] parameter is not present (it is not needed since the prepared statement's parameter types were determined when it was created). PQdescribePrepared PQdescribePrepared Submits a request to obtain information about the specified prepared statement, and waits for completion. PGresult *PQdescribePrepared(PGconn *conn, const char *stmtName); PQdescribePrepared allows an application to obtain information about a previously prepared statement. PQdescribePrepared is supported only in protocol 3.0 and later connections; it will fail when using protocol 2.0. stmtName can be "" or NULL to reference the unnamed statement, otherwise it must be the name of an existing prepared statement. On success, a PGresult with status PGRES_COMMAND_OK is returned. The functions PQnparams and PQparamtype can be applied to this PGresult to obtain information about the parameters of the prepared statement, and the functions PQnfields, PQfname, PQftype, etc provide information about the result columns (if any) of the statement. PQdescribePortal PQdescribePortal Submits a request to obtain information about the specified portal, and waits for completion. PGresult *PQdescribePortal(PGconn *conn, const char *portalName); PQdescribePortal 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.) PQdescribePortal is supported only in protocol 3.0 and later connections; it will fail when using protocol 2.0. portalName can be "" or NULL to reference the unnamed portal, otherwise it must be the name of an existing portal. On success, a PGresult with status PGRES_COMMAND_OK is returned. The functions PQnfields, PQfname, PQftype, etc can be applied to the PGresult to obtain information about the result columns (if any) of the portal. The PGresultPGresult structure encapsulates the result returned by the server. libpq application programmers should be careful to maintain the PGresult abstraction. Use the accessor functions below to get at the contents of PGresult. Avoid directly referencing the fields of the PGresult structure because they are subject to change in the future. PQresultStatus PQresultStatus Returns the result status of the command. ExecStatusType PQresultStatus(const PGresult *res); PQresultStatus can return 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 PQexec or other query execution functions; results of this kind are instead passed to the notice processor (see ). PQresStatus PQresStatus Converts the enumerated type returned by PQresultStatus into a string constant describing the status code. The caller should not free the result. char *PQresStatus(ExecStatusType status); PQresultErrorMessage PQresultErrorMessage Returns the error message associated with the command, or an empty string if there was no error. char *PQresultErrorMessage(const PGresult *res); If there was an error, the returned string will include a trailing newline. The caller should not free the result directly. It will be freed when the associated PGresult handle is passed to PQclear. Immediately following a PQexec or PQgetResult call, PQerrorMessage (on the connection) will return the same string as PQresultErrorMessage (on the result). However, a PGresult will retain its error message until destroyed, whereas the connection's error message will change when subsequent operations are done. Use PQresultErrorMessage when you want to know the status associated with a particular PGresult; use PQerrorMessage when you want to know the status from the latest operation on the connection. PQresultErrorFieldPQresultErrorField Returns an individual field of an error report. char *PQresultErrorField(const PGresult *res, int fieldcode); fieldcode is an error field identifier; see the symbols listed below. NULL is returned if the PGresult is not an error or warning result, or does not include the specified field. Field values will normally not include a trailing newline. The caller should not free the result directly. It will be freed when the associated PGresult handle is passed to PQclear. The following field codes are available: PG_DIAG_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. error codes libpq PG_DIAG_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 . This field is not localizable, and is always present. PG_DIAG_MESSAGE_PRIMARY The primary human-readable error message (typically one line). Always present. PG_DIAG_MESSAGE_DETAIL Detail: an optional secondary error message carrying more detail about the problem. Might run to multiple lines. PG_DIAG_MESSAGE_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. PG_DIAG_STATEMENT_POSITION A string containing a decimal 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. PG_DIAG_INTERNAL_POSITION This is defined the same as the PG_DIAG_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 PG_DIAG_INTERNAL_QUERY field will always appear when this field appears. PG_DIAG_INTERNAL_QUERY The text of a failed internally-generated command. This could be, for example, a SQL query issued by a PL/pgSQL function. PG_DIAG_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. PG_DIAG_SOURCE_FILE The file name of the source-code location where the error was reported. PG_DIAG_SOURCE_LINE The line number of the source-code location where the error was reported. PG_DIAG_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 PGresult objects, not PGconn objects; there is no PQerrorField function. PQclearPQclear Frees the storage associated with a PGresult. Every command result should be freed via PQclear when it is no longer needed. void PQclear(PGresult *res); You can keep a PGresult object around for as long as you need it; it does not go away when you issue a new command, nor even if you close the connection. To get rid of it, you must call PQclear. Failure to do this will result in memory leaks in your application. These functions are used to extract information from a PGresult object that represents a successful query result (that is, one that has status PGRES_TUPLES_OK). They can also be used to extract information from a successful Describe operation: a Describe's result has all the same column information that actual execution of the query would provide, but it has zero rows. For objects with other status values, these functions will act as though the result has zero rows and zero columns. PQntuples PQntuples Returns the number of rows (tuples) in the query result. Because it returns an integer result, large result sets might overflow the return value on 32-bit operating systems. int PQntuples(const PGresult *res); PQnfields PQnfields Returns the number of columns (fields) in each row of the query result. int PQnfields(const PGresult *res); PQfname PQfname Returns the column name associated with the given column number. Column numbers start at 0. The caller should not free the result directly. It will be freed when the associated PGresult handle is passed to PQclear. char *PQfname(const PGresult *res, int column_number); NULL is returned if the column number is out of range. PQfnumber PQfnumber Returns the column number associated with the given column name. int PQfnumber(const PGresult *res, const char *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: PQfname(res, 0) foo PQfname(res, 1) BAR PQfnumber(res, "FOO") 0 PQfnumber(res, "foo") 0 PQfnumber(res, "BAR") -1 PQfnumber(res, "\"BAR\"") 1 PQftable PQftable Returns the OID of the table from which the given column was fetched. Column numbers start at 0. Oid PQftable(const PGresult *res, int column_number); InvalidOid 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. The type Oid and the constant InvalidOid will be defined when you include the libpq header file. They will both be some integer type. PQftablecol PQftablecol 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. int PQftablecol(const PGresult *res, int column_number); Zero 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. PQfformat PQfformat Returns the format code indicating the format of the given column. Column numbers start at 0. int PQfformat(const PGresult *res, int column_number); Format code zero indicates textual data representation, while format code one indicates binary representation. (Other codes are reserved for future definition.) PQftype PQftype Returns the data type associated with the given column number. The integer returned is the internal OID number of the type. Column numbers start at 0. Oid PQftype(const PGresult *res, int column_number); You can query the system table pg_type to obtain the names and properties of the various data types. The OIDs of the built-in data types are defined in the file src/include/catalog/pg_type.h in the source tree. PQfmod PQfmod Returns the type modifier of the column associated with the given column number. Column numbers start at 0. int PQfmod(const PGresult *res, int column_number); The interpretation of modifier values is type-specific; they typically indicate precision or size limits. The value -1 is used to indicate no information available. Most data types do not use modifiers, in which case the value is always -1. PQfsize PQfsize Returns the size in bytes of the column associated with the given column number. Column numbers start at 0. int PQfsize(const PGresult *res, int column_number); PQfsize returns the space allocated for this column in a database row, in other words the size of the server's internal representation of the data type. (Accordingly, it is not really very useful to clients.) A negative value indicates the data type is variable-length. PQbinaryTuples PQbinaryTuples Returns 1 if the PGresult contains binary data and 0 if it contains text data. int PQbinaryTuples(const PGresult *res); This function is deprecated (except for its use in connection with COPY), because it is possible for a single PGresult to contain text data in some columns and binary data in others. PQfformat is preferred. PQbinaryTuples returns 1 only if all columns of the result are binary (format 1). PQgetvalue PQgetvalue Returns a single field value of one row of a PGresult. Row and column numbers start at 0. The caller should not free the result directly. It will be freed when the associated PGresult handle is passed to PQclear. char *PQgetvalue(const PGresult *res, int row_number, int column_number); For data in text format, the value returned by PQgetvalue is a null-terminated character string representation of the field value. For data in binary format, the value is in the binary representation determined by the data type's typsend and typreceive functions. (The value is actually followed by a zero byte in this case too, but that is not ordinarily useful, since the value is likely to contain embedded nulls.) An empty string is returned if the field value is null. See PQgetisnull to distinguish null values from empty-string values. The pointer returned by PQgetvalue points to storage that is part of the PGresult structure. One should not modify the data it points to, and one must explicitly copy the data into other storage if it is to be used past the lifetime of the PGresult structure itself. PQgetisnull PQgetisnull null value in libpq Tests a field for a null value. Row and column numbers start at 0. int PQgetisnull(const PGresult *res, int row_number, int column_number); This function returns 1 if the field is null and 0 if it contains a non-null value. (Note that PQgetvalue will return an empty string, not a null pointer, for a null field.) PQgetlength PQgetlength Returns the actual length of a field value in bytes. Row and column numbers start at 0. int PQgetlength(const PGresult *res, int row_number, int column_number); This is the actual data length for the particular data value, that is, the size of the object pointed to by PQgetvalue. For text data format this is the same as strlen(). For binary format this is essential information. Note that one should not rely on PQfsize to obtain the actual data length. PQnparams PQnparams Returns the number of parameters of a prepared statement. int PQnparams(const PGresult *res); This function is only useful when inspecting the result of PQdescribePrepared. For other types of queries it will return zero. PQparamtype PQparamtype Returns the data type of the indicated statement parameter. Parameter numbers start at 0. Oid PQparamtype(const PGresult *res, int param_number); This function is only useful when inspecting the result of PQdescribePrepared. For other types of queries it will return zero. PQprint PQprint Prints out all the rows and, optionally, the column names to the specified output stream. void PQprint(FILE *fout, /* output stream */ const PGresult *res, const PQprintOpt *po); typedef struct { pqbool header; /* print output field headings and row count */ pqbool align; /* fill align the fields */ pqbool standard; /* old brain dead format */ pqbool html3; /* output HTML tables */ pqbool expanded; /* expand tables */ pqbool pager; /* use pager for output if needed */ char *fieldSep; /* field separator */ char *tableOpt; /* attributes for HTML table element */ char *caption; /* HTML table caption */ char **fieldName; /* null-terminated array of replacement field names */ } PQprintOpt; This function was formerly used by psql to print query results, but this is no longer the case. Note that it assumes all the data is in text format. These functions are used to extract other information from PGresult objects. PQcmdStatus PQcmdStatus Returns the command status tag from the SQL command that generated the PGresult. char *PQcmdStatus(PGresult *res); Commonly this is just the name of the command, but it might include additional data such as the number of rows processed. The caller should not free the result directly. It will be freed when the associated PGresult handle is passed to PQclear. PQcmdTuples PQcmdTuples Returns the number of rows affected by the SQL command. char *PQcmdTuples(PGresult *res); This function returns a string containing the number of rows affected by the SQL statement that generated the PGresult. 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 PGresult was anything else, PQcmdTuples returns an empty string. The caller should not free the return value directly. It will be freed when the associated PGresult handle is passed to PQclear. PQoidValue PQoidValue Returns the OIDOIDin libpq 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 InvalidOid. This function will also return InvalidOid if the table affected by the INSERT statement does not contain OIDs. Oid PQoidValue(const PGresult *res); PQoidStatus PQoidStatus Returns a string with the OID of the inserted row, if the SQL command was an INSERT that inserted exactly one row, or a EXECUTE of a prepared statement consisting of a suitable INSERT. (The string will be 0 if the INSERT did not insert exactly one row, or if the target table does not have OIDs.) If the command was not an INSERT, returns an empty string. char *PQoidStatus(const PGresult *res); This function is deprecated in favor of PQoidValue. It is not thread-safe. escaping strings in libpq PQescapeLiteral PQescapeLiteral char *PQescapeLiteral(PGconn *conn, const char *str, size_t length); PQescapeLiteral 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. PQescapeLiteral performs this operation. PQescapeLiteral returns an escaped version of the str parameter in memory allocated with malloc(). This memory should be freed using PQfreemem() when the result is no longer needed. A terminating zero byte is not required, and should not be counted in length. (If a terminating zero byte is found before length bytes are processed, PQescapeLiteral stops at the zero; the behavior is thus rather like strncpy.) The return string has all special characters replaced so that they can be properly processed by the PostgreSQL string literal parser. A terminating zero byte is also added. The single quotes that must surround PostgreSQL string literals are included in the result string. On error, PQescapeLiteral returns NULL and a suitable message is stored in the conn object. It is especially important to do proper escaping when handling strings that were received from an untrustworthy source. Otherwise there is a security risk: you are vulnerable to SQL injection attacks wherein unwanted SQL commands are fed to your database. Note that it is not necessary nor correct to do escaping when a data value is passed as a separate parameter in PQexecParams or its sibling routines. PQescapeIdentifier PQescapeIdentifier char *PQescapeIdentifier(PGconn *conn, const char *str, size_t length); PQescapeIdentifier 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. PQescapeIdentifier returns a version of the str parameter escaped as an SQL identifier in memory allocated with malloc(). This memory must be freed using PQfreemem() when the result is no longer needed. A terminating zero byte is not required, and should not be counted in length. (If a terminating zero byte is found before length bytes are processed, PQescapeIdentifier stops at the zero; the behavior is thus rather like strncpy.) The return string has all special characters replaced so that it will be properly processed as an SQL identifier. A terminating zero byte is also added. The return string will also be surrounded by double quotes. On error, PQescapeIdentifier returns NULL and a suitable message is stored in the conn object. As with string literals, to prevent SQL injection attacks, SQL identifiers must be escaped when they are received from an untrustworthy source. PQescapeStringConn PQescapeStringConn size_t PQescapeStringConn(PGconn *conn, char *to, const char *from, size_t length, int *error); PQescapeStringConn escapes string literals, much like PQescapeLiteral. Unlike PQescapeLiteral, the caller is responsible for providing an appropriately sized buffer. Furthermore, PQescapeStringConn 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. The parameter from points to the first character of the string that is to be escaped, and the length parameter gives the number of bytes in this string. A terminating zero byte is not required, and should not be counted in length. (If a terminating zero byte is found before length bytes are processed, PQescapeStringConn stops at the zero; the behavior is thus rather like strncpy.) to shall point to a buffer that is able to hold at least one more byte than twice the value of length, otherwise the behavior is undefined. Behavior is likewise undefined if the to and from strings overlap. If the error parameter is not NULL, then *error is set to zero on success, nonzero on error. Presently the only possible error conditions involve invalid multibyte encoding in the source string. The output string is still generated on error, but it can be expected that the server will reject it as malformed. On error, a suitable message is stored in the conn object, whether or not error is NULL. PQescapeStringConn returns the number of bytes written to to, not including the terminating zero byte. PQescapeString PQescapeString size_t PQescapeString (char *to, const char *from, size_t length); PQescapeString is an older, deprecated version of PQescapeStringConn; the difference is that it does not take conn or error parameters. Because of this, it cannot adjust its behavior depending on the connection properties (such as character encoding) and therefore it might give the wrong results. Also, it has no way to report error conditions. PQescapeString can be used safely in single-threaded client programs that work with only one PostgreSQL connection at a time (in this case it can find out what it needs to know behind the scenes). In other contexts it is a security hazard and should be avoided in favor of PQescapeStringConn. PQescapeByteaConn PQescapeByteaConn Escapes binary data for use within an SQL command with the type bytea. As with PQescapeStringConn, this is only used when inserting data directly into an SQL command string. unsigned char *PQescapeByteaConn(PGconn *conn, const unsigned char *from, size_t from_length, size_t *to_length); Certain byte values must be escaped (but all byte values can be escaped) when used as part of a bytea literal in an SQL statement. In general, to escape a byte, it is converted into the three digit octal number equal to the octet value, and preceded by usually two backslashes. The single quote (') and backslash (\) characters have special alternative escape sequences. See for more information. PQescapeByteaConn performs this operation, escaping only the minimally required bytes. The from parameter points to the first byte of the string that is to be escaped, and the from_length parameter gives the number of bytes in this binary string. (A terminating zero byte is neither necessary nor counted.) The to_length parameter points to a variable that will hold the resultant escaped string length. This result string length includes the terminating zero byte of the result. PQescapeByteaConn returns an escaped version of the from parameter binary string in memory allocated with malloc(). This memory should be freed using PQfreemem() when the result is no longer needed. The return string has all special characters replaced so that they can be properly processed by the PostgreSQL string literal parser, and the bytea input function. A terminating zero byte is also added. The single quotes that must surround PostgreSQL string literals are not part of the result string. On error, a null pointer is returned, and a suitable error message is stored in the conn object. Currently, the only possible error is insufficient memory for the result string. PQescapeBytea PQescapeBytea PQescapeBytea is an older, deprecated version of PQescapeByteaConn. unsigned char *PQescapeBytea(const unsigned char *from, size_t from_length, size_t *to_length); The only difference from PQescapeByteaConn is that PQescapeBytea does not take a PGconn parameter. Because of this, it cannot adjust its behavior depending on the connection properties (in particular, whether standard-conforming strings are enabled) and therefore it might give the wrong results. Also, it has no way to return an error message on failure. PQescapeBytea can be used safely in single-threaded client programs that work with only one PostgreSQL connection at a time (in this case it can find out what it needs to know behind the scenes). In other contexts it is a security hazard and should be avoided in favor of PQescapeByteaConn. PQunescapeBytea PQunescapeBytea Converts a string representation of binary data into binary data — the reverse of PQescapeBytea. This is needed when retrieving bytea data in text format, but not when retrieving it in binary format. unsigned char *PQunescapeBytea(const unsigned char *from, size_t *to_length); The from parameter points to a string such as might be returned by PQgetvalue when applied to a bytea column. PQunescapeBytea converts this string representation into its binary representation. It returns a pointer to a buffer allocated with malloc(), or NULL on error, and puts the size of the buffer in to_length. The result must be freed using PQfreemem when it is no longer needed. This conversion is not exactly the inverse of PQescapeBytea, because the string is not expected to be escaped when received from PQgetvalue. In particular this means there is no need for string quoting considerations, and so no need for a PGconn parameter. nonblocking connection The PQexec function is adequate for submitting commands in normal, synchronous applications. It has a couple of deficiencies, however, that can be of importance to some users: PQexec waits for the command to be completed. The application might have other work to do (such as maintaining a user interface), in which case it won't want to block waiting for the response. Since the execution of the client application is suspended while it waits for the result, it is hard for the application to decide that it would like to try to cancel the ongoing command. (It can be done from a signal handler, but not otherwise.) PQexec can return only one PGresult structure. If the submitted command string contains multiple SQL commands, all but the last PGresult are discarded by PQexec. Applications that do not like these limitations can instead use the underlying functions that PQexec is built from: PQsendQuery and PQgetResult. There are also PQsendQueryParams, PQsendPrepare, PQsendQueryPrepared, PQsendDescribePrepared, and PQsendDescribePortal, which can be used with PQgetResult to duplicate the functionality of PQexecParams, PQprepare, PQexecPrepared, PQdescribePrepared, and PQdescribePortal respectively. PQsendQuery PQsendQuery Submits a command to the server without waiting for the result(s). 1 is returned if the command was successfully dispatched and 0 if not (in which case, use PQerrorMessage to get more information about the failure). int PQsendQuery(PGconn *conn, const char *command); After successfully calling PQsendQuery, call PQgetResult one or more times to obtain the results. PQsendQuery cannot be called again (on the same connection) until PQgetResult has returned a null pointer, indicating that the command is done. PQsendQueryParams PQsendQueryParams Submits a command and separate parameters to the server without waiting for the result(s). int PQsendQueryParams(PGconn *conn, const char *command, int nParams, const Oid *paramTypes, const char * const *paramValues, const int *paramLengths, const int *paramFormats, int resultFormat); This is equivalent to PQsendQuery except that query parameters can be specified separately from the query string. The function's parameters are handled identically to PQexecParams. Like PQexecParams, it will not work on 2.0-protocol connections, and it allows only one command in the query string. PQsendPrepare PQsendPrepare Sends a request to create a prepared statement with the given parameters, without waiting for completion. int PQsendPrepare(PGconn *conn, const char *stmtName, const char *query, int nParams, const Oid *paramTypes); This is an asynchronous version of PQprepare: it returns 1 if it was able to dispatch the request, and 0 if not. After a successful call, call PQgetResult to determine whether the server successfully created the prepared statement. The function's parameters are handled identically to PQprepare. Like PQprepare, it will not work on 2.0-protocol connections. PQsendQueryPrepared PQsendQueryPrepared Sends a request to execute a prepared statement with given parameters, without waiting for the result(s). int PQsendQueryPrepared(PGconn *conn, const char *stmtName, int nParams, const char * const *paramValues, const int *paramLengths, const int *paramFormats, int resultFormat); This is similar to PQsendQueryParams, 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 PQexecPrepared. Like PQexecPrepared, it will not work on 2.0-protocol connections. PQsendDescribePrepared PQsendDescribePrepared Submits a request to obtain information about the specified prepared statement, without waiting for completion. int PQsendDescribePrepared(PGconn *conn, const char *stmtName); This is an asynchronous version of PQdescribePrepared: it returns 1 if it was able to dispatch the request, and 0 if not. After a successful call, call PQgetResult to obtain the results. The function's parameters are handled identically to PQdescribePrepared. Like PQdescribePrepared, it will not work on 2.0-protocol connections. PQsendDescribePortal PQsendDescribePortal Submits a request to obtain information about the specified portal, without waiting for completion. int PQsendDescribePortal(PGconn *conn, const char *portalName); This is an asynchronous version of PQdescribePortal: it returns 1 if it was able to dispatch the request, and 0 if not. After a successful call, call PQgetResult to obtain the results. The function's parameters are handled identically to PQdescribePortal. Like PQdescribePortal, it will not work on 2.0-protocol connections. PQgetResult PQgetResult Waits for the next result from a prior PQsendQuery, PQsendQueryParams, PQsendPrepare, or PQsendQueryPrepared call, and returns it. A null pointer is returned when the command is complete and there will be no more results. PGresult *PQgetResult(PGconn *conn); PQgetResult must be called repeatedly until it returns a null pointer, indicating that the command is done. (If called when no command is active, PQgetResult will just return a null pointer at once.) Each non-null result from PQgetResult should be processed using the same PGresult accessor functions previously described. Don't forget to free each result object with PQclear when done with it. Note that PQgetResult will block only if a command is active and the necessary response data has not yet been read by PQconsumeInput. Using PQsendQuery and PQgetResult solves one of PQexec'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 PQgetResult will still cause the client to block until the server completes the next SQL command. This can be avoided by proper use of two more functions: PQconsumeInput PQconsumeInput If input is available from the server, consume it. int PQconsumeInput(PGconn *conn); PQconsumeInput normally returns 1 indicating no error, but returns 0 if there was some kind of trouble (in which case PQerrorMessage can be consulted). Note that the result does not say whether any input data was actually collected. After calling PQconsumeInput, the application can check PQisBusy and/or PQnotifies to see if their state has changed. PQconsumeInput can be called even if the application is not prepared to deal with a result or notification just yet. The function will read available data and save it in a buffer, thereby causing a select() read-ready indication to go away. The application can thus use PQconsumeInput to clear the select() condition immediately, and then examine the results at leisure. PQisBusy PQisBusy Returns 1 if a command is busy, that is, PQgetResult would block waiting for input. A 0 return indicates that PQgetResult can be called with assurance of not blocking. int PQisBusy(PGconn *conn); PQisBusy will not itself attempt to read data from the server; therefore PQconsumeInput must be invoked first, or the busy state will never end. A typical application using these functions will have a main loop that uses select() or poll() to wait for all the conditions that it must respond to. 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 PQsocket. When the main loop detects input ready, it should call PQconsumeInput to read the input. It can then call PQisBusy, followed by PQgetResult if PQisBusy returns false (0). It can also call PQnotifies to detect NOTIFY messages (see ). A client that uses PQsendQuery/PQgetResult can also attempt to cancel a command that is still being processed by the server; see . But regardless of the return value of PQcancel, the application must continue with the normal result-reading sequence using PQgetResult. 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 following additional functions can be used. PQsetnonblocking PQsetnonblocking Sets the nonblocking status of the connection. int PQsetnonblocking(PGconn *conn, int arg); Sets the state of the connection to nonblocking if arg is 1, or blocking if arg is 0. Returns 0 if OK, -1 if error. In the nonblocking state, calls to PQsendQuery, PQputline, PQputnbytes, and PQendcopy will not block but instead return an error if they need to be called again. Note that PQexec does not honor nonblocking mode; if it is called, it will act in blocking fashion anyway. PQisnonblocking PQisnonblocking Returns the blocking status of the database connection. int PQisnonblocking(const PGconn *conn); Returns 1 if the connection is set to nonblocking mode and 0 if blocking. PQflush PQflush 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). int PQflush(PGconn *conn); After sending any command or data on a nonblocking connection, call PQflush. If it returns 1, wait for the socket to be write-ready and call it again; repeat until it returns 0. Once PQflush returns 0, wait for the socket to be read-ready and then read the response as described above. canceling SQL command A client application can request cancellation of a command that is still being processed by the server, using the functions described in this section. PQgetCancel PQgetCancel Creates a data structure containing the information needed to cancel a command issued through a particular database connection. PGcancel *PQgetCancel(PGconn *conn); PQgetCancel creates a PGcancelPGcancel object given a PGconn connection object. It will return NULL if the given conn is NULL or an invalid connection. The PGcancel object is an opaque structure that is not meant to be accessed directly by the application; it can only be passed to PQcancel or PQfreeCancel. PQfreeCancel PQfreeCancel Frees a data structure created by PQgetCancel. void PQfreeCancel(PGcancel *cancel); PQfreeCancel frees a data object previously created by PQgetCancel. PQcancel PQcancel Requests that the server abandon processing of the current command. int PQcancel(PGcancel *cancel, char *errbuf, int errbufsize); The return value is 1 if the cancel request was successfully dispatched and 0 if not. If not, errbuf is filled with an error message explaining why not. errbuf must be a char array of size errbufsize (the recommended size is 256 bytes). Successful dispatch is no guarantee that the request will have any effect, however. If the cancellation is effective, the current command will terminate early and return an error result. If the cancellation fails (say, because the server was already done processing the command), then there will be no visible result at all. PQcancel can safely be invoked from a signal handler, if the errbuf is a local variable in the signal handler. The PGcancel object is read-only as far as PQcancel is concerned, so it can also be invoked from a thread that is separate from the one manipulating the PGconn object. PQrequestCancel PQrequestCancel Requests that the server abandon processing of the current command. int PQrequestCancel(PGconn *conn); PQrequestCancel is a deprecated variant of PQcancel. It operates directly on the PGconn object, and in case of failure stores the error message in the PGconn object (whence it can be retrieved by PQerrorMessage). Although the functionality is the same, this approach creates hazards for multiple-thread programs and signal handlers, since it is possible that overwriting the PGconn's error message will mess up the operation currently in progress on the connection. fast path PostgreSQL provides a fast-path interface to send simple function calls to the server. This interface is somewhat obsolete, as one can achieve similar performance and greater functionality by setting up a prepared statement to define the function call. Then, executing the statement with binary transmission of parameters and results substitutes for a fast-path function call. The function PQfnPQfn requests execution of a server function via the fast-path interface: PGresult *PQfn(PGconn *conn, int fnid, int *result_buf, int *result_len, int result_is_int, const PQArgBlock *args, int nargs); typedef struct { int len; int isint; union { int *ptr; int integer; } u; } PQArgBlock; The fnid argument is the OID of the function to be executed. args and nargs define the parameters to be passed to the function; they must match the declared function argument list. When the isint field of a parameter structure is true, the u.integer value is sent to the server as an integer of the indicated length (this must be 1, 2, or 4 bytes); proper byte-swapping occurs. When isint is false, the indicated number of bytes at *u.ptr are sent with no processing; the data must be in the format expected by the server for binary transmission of the function's argument data type. result_buf is the buffer in which to place the return value. The caller must have allocated sufficient space to store the return value. (There is no check!) The actual result length will be returned in the integer pointed to by result_len. If a 1, 2, or 4-byte integer result is expected, set result_is_int to 1, otherwise set it to 0. Setting result_is_int to 1 causes libpq to byte-swap the value if necessary, so that it is delivered as a proper int value for the client machine. When result_is_int is 0, the binary-format byte string sent by the server is returned unmodified. PQfn always returns a valid PGresult pointer. The result status should be checked before the result is used. The caller is responsible for freeing the PGresult with PQclear when it is no longer needed. Note that it is not possible to handle null arguments, null results, nor set-valued results when using this interface. NOTIFY in libpq 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 PQnotifies.PQnotifies The function PQnotifies returns the next notification from a list of unhandled notification messages received from the server. It returns a null pointer if there are no pending notifications. Once a notification is returned from PQnotifies, it is considered handled and will be removed from the list of notifications. PGnotify *PQnotifies(PGconn *conn); typedef struct pgNotify { char *relname; /* notification channel name */ int be_pid; /* process ID of notifying server process */ char *extra; /* notification payload string */ } PGnotify; After processing a PGnotify object returned by PQnotifies, be sure to free it with PQfreemem. It is sufficient to free the PGnotify pointer; the relname and extra fields do not represent separate allocations. (The names of these fields are historical; in particular, channel names need not have anything to do with relation names.) gives a sample program that illustrates the use of asynchronous notification. PQnotifies 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 PQnotifies after each PQexec. 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 PQconsumeInput, then check PQnotifies. You can use select() to wait for data to arrive from the server, thereby using no CPU power unless there is something to do. (See PQsocket to obtain the file descriptor number to use with select().) Note that this will work OK whether you submit commands with PQsendQuery/PQgetResult or simply use PQexec. You should, however, remember to check PQnotifies after each PQgetResult or PQexec, to see if any notifications came in during the processing of the command. COPY with libpq The COPY command in PostgreSQL has options to read from or write to the network connection used by libpq. The functions described in this section allow applications to take advantage of this capability by supplying or consuming copied data. The overall process is that the application first issues the SQL COPY command via PQexec or one of the equivalent functions. The response to this (if there is no error in the command) will be a PGresult object bearing a status code of PGRES_COPY_OUT or PGRES_COPY_IN (depending on the specified copy direction). The application should then use the functions of this section to receive or transmit data rows. When the data transfer is complete, another PGresult object is returned to indicate success or failure of the transfer. Its status will be PGRES_COMMAND_OK for success or PGRES_FATAL_ERROR if some problem was encountered. At this point further SQL commands can be issued via PQexec. (It is not possible to execute other SQL commands using the same connection while the COPY operation is in progress.) If a COPY command is issued via PQexec in a string that could contain additional commands, the application must continue fetching results via PQgetResult after completing the COPY sequence. Only when PQgetResult returns NULL is it certain that the PQexec command string is done and it is safe to issue more commands. The functions of this section should be executed only after obtaining a result status of PGRES_COPY_OUT or PGRES_COPY_IN from PQexec or PQgetResult. A PGresult object bearing one of these status values carries some additional data about the COPY operation that is starting. This additional data is available using functions that are also used in connection with query results: PQnfields PQnfields with COPY Returns the number of columns (fields) to be copied. PQbinaryTuples PQbinaryTuples with COPY 0 indicates the overall copy format is textual (rows separated by newlines, columns separated by separator characters, etc). 1 indicates the overall copy format is binary. See for more information. PQfformat PQfformat with COPY Returns the format code (0 for text, 1 for binary) associated with each column of the copy operation. The per-column format codes will always be zero when the overall copy format is textual, but the binary format can support both text and binary columns. (However, as of the current implementation of COPY, only binary columns appear in a binary copy; so the per-column formats always match the overall format at present.) These additional data values are only available when using protocol 3.0. When using protocol 2.0, all these functions will return 0. These functions are used to send data during COPY FROM STDIN. They will fail if called when the connection is not in COPY_IN state. PQputCopyData PQputCopyData Sends data to the server during COPY_IN state. int PQputCopyData(PGconn *conn, const char *buffer, int nbytes); Transmits the COPY data in the specified buffer, of length nbytes, to the server. The result is 1 if the data was sent, zero if it was not sent because the attempt would block (this case is only possible if the connection is in nonblocking mode), or -1 if an error occurred. (Use PQerrorMessage to retrieve details if the return value is -1. If the value is zero, wait for write-ready and try again.) The application can divide the COPY data stream into buffer loads of any convenient size. Buffer-load boundaries have no semantic significance when sending. The contents of the data stream must match the data format expected by the COPY command; see for details. PQputCopyEnd PQputCopyEnd Sends end-of-data indication to the server during COPY_IN state. int PQputCopyEnd(PGconn *conn, const char *errormsg); Ends the COPY_IN operation successfully if errormsg is NULL. If errormsg is not NULL then the COPY is forced to fail, with the string pointed to by errormsg used as the error message. (One should not assume that this exact error message will come back from the server, however, as the server might have already failed the COPY for its own reasons. Also note that the option to force failure does not work when using pre-3.0-protocol connections.) The result is 1 if the termination data was sent, zero if it was not sent because the attempt would block (this case is only possible if the connection is in nonblocking mode), or -1 if an error occurred. (Use PQerrorMessage to retrieve details if the return value is -1. If the value is zero, wait for write-ready and try again.) After successfully calling PQputCopyEnd, call PQgetResult to obtain the final result status of the COPY command. One can wait for this result to be available in the usual way. Then return to normal operation. These functions are used to receive data during COPY TO STDOUT. They will fail if called when the connection is not in COPY_OUT state. PQgetCopyData PQgetCopyData Receives data from the server during COPY_OUT state. int PQgetCopyData(PGconn *conn, char **buffer, int async); Attempts to obtain another row of data from the server during a COPY. Data is always returned one data row at a time; if only a partial row is available, it is not returned. Successful return of a data row involves allocating a chunk of memory to hold the data. The buffer parameter must be non-NULL. *buffer is set to point to the allocated memory, or to NULL in cases where no buffer is returned. A non-NULL result buffer should be freed using PQfreemem when no longer needed. When a row is successfully returned, the return value is the number of data bytes in the row (this will always be greater than zero). The returned string is always null-terminated, though this is probably only useful for textual COPY. A result of zero indicates that the COPY is still in progress, but no row is yet available (this is only possible when async is true). A result of -1 indicates that the COPY is done. A result of -2 indicates that an error occurred (consult PQerrorMessage for the reason). When async is true (not zero), PQgetCopyData will not block waiting for input; it will return zero if the COPY is still in progress but no complete row is available. (In this case wait for read-ready and then call PQconsumeInput before calling PQgetCopyData again.) When async is false (zero), PQgetCopyData will block until data is available or the operation completes. After PQgetCopyData returns -1, call PQgetResult to obtain the final result status of the COPY command. One can wait for this result to be available in the usual way. Then return to normal operation. These functions represent older methods of handling COPY. Although they still work, they are deprecated due to poor error handling, inconvenient methods of detecting end-of-data, and lack of support for binary or nonblocking transfers. PQgetline PQgetline Reads a newline-terminated line of characters (transmitted by the server) into a buffer string of size length. int PQgetline(PGconn *conn, char *buffer, int length); This function copies up to length-1 characters into the buffer and converts the terminating newline into a zero byte. PQgetline returns EOF at the end of input, 0 if the entire line has been read, and 1 if the buffer is full but the terminating newline has not yet been read. Note that the application must check to see if a new line consists of the two characters \., which indicates that the server has finished sending the results of the COPY command. If the application might receive lines that are more than length-1 characters long, care is needed to be sure it recognizes the \. line correctly (and does not, for example, mistake the end of a long data line for a terminator line). PQgetlineAsync PQgetlineAsync Reads a row of COPY data (transmitted by the server) into a buffer without blocking. int PQgetlineAsync(PGconn *conn, char *buffer, int bufsize); This function is similar to PQgetline, but it can be used by applications that must read COPY data asynchronously, that is, without blocking. Having issued the COPY command and gotten a PGRES_COPY_OUT response, the application should call PQconsumeInput and PQgetlineAsync until the end-of-data signal is detected. Unlike PQgetline, this function takes responsibility for detecting end-of-data. On each call, PQgetlineAsync will return data if a complete data row is available in libpq's input buffer. Otherwise, no data is returned until the rest of the row arrives. The function returns -1 if the end-of-copy-data marker has been recognized, or 0 if no data is available, or a positive number giving the number of bytes of data returned. If -1 is returned, the caller must next call PQendcopy, and then return to normal processing. The data returned will not extend beyond a data-row boundary. If possible a whole row will be returned at one time. But if the buffer offered by the caller is too small to hold a row sent by the server, then a partial data row will be returned. With textual data this can be detected by testing whether the last returned byte is \n or not. (In a binary COPY, actual parsing of the COPY data format will be needed to make the equivalent determination.) The returned string is not null-terminated. (If you want to add a terminating null, be sure to pass a bufsize one smaller than the room actually available.) PQputline PQputline Sends a null-terminated string to the server. Returns 0 if OK and EOF if unable to send the string. int PQputline(PGconn *conn, const char *string); The COPY data stream sent by a series of calls to PQputline has the same format as that returned by PQgetlineAsync, except that applications are not obliged to send exactly one data row per PQputline call; it is okay to send a partial line or multiple lines per call. Before PostgreSQL protocol 3.0, it was necessary for the application to explicitly send the two characters \. as a final line to indicate to the server that it had finished sending COPY data. While this still works, it is deprecated and the special meaning of \. can be expected to be removed in a future release. It is sufficient to call PQendcopy after having sent the actual data. PQputnbytes PQputnbytes Sends a non-null-terminated string to the server. Returns 0 if OK and EOF if unable to send the string. int PQputnbytes(PGconn *conn, const char *buffer, int nbytes); This is exactly like PQputline, except that the data buffer need not be null-terminated since the number of bytes to send is specified directly. Use this procedure when sending binary data. PQendcopy PQendcopy Synchronizes with the server. int PQendcopy(PGconn *conn); This function waits until the server has finished the copying. It should either be issued when the last string has been sent to the server using PQputline or when the last string has been received from the server using PGgetline. It must be issued or the server will get out of sync with the client. Upon return from this function, the server is ready to receive the next SQL command. The return value is 0 on successful completion, nonzero otherwise. (Use PQerrorMessage to retrieve details if the return value is nonzero.) When using PQgetResult, the application should respond to a PGRES_COPY_OUT result by executing PQgetline repeatedly, followed by PQendcopy after the terminator line is seen. It should then return to the PQgetResult loop until PQgetResult returns a null pointer. Similarly a PGRES_COPY_IN result is processed by a series of PQputline calls followed by PQendcopy, then return to the PQgetResult loop. This arrangement will ensure that a COPY command embedded in a series of SQL commands will be executed correctly. Older applications are likely to submit a COPY via PQexec and assume that the transaction is done after PQendcopy. This will work correctly only if the COPY is the only SQL command in the command string. These functions control miscellaneous details of libpq's behavior. PQclientEncoding PQclientEncoding Returns the client encoding. int PQclientEncoding(const PGconn *conn); Note that it returns the encoding ID, not a symbolic string such as EUC_JP. To convert an encoding ID to an encoding name, you can use: char *pg_encoding_to_char(int encoding_id); PQsetClientEncoding PQsetClientEncoding Sets the client encoding. int PQsetClientEncoding(PGconn *conn, const char *encoding); conn is a connection to the server, and encoding is the encoding you want to use. If the function successfully sets the encoding, it returns 0, otherwise -1. The current encoding for this connection can be determined by using PQclientEncoding. PQsetErrorVerbosity PQsetErrorVerbosity Determines the verbosity of messages returned by PQerrorMessage and PQresultErrorMessage. typedef enum { PQERRORS_TERSE, PQERRORS_DEFAULT, PQERRORS_VERBOSE } PGVerbosity; PGVerbosity PQsetErrorVerbosity(PGconn *conn, PGVerbosity verbosity); PQsetErrorVerbosity sets the verbosity mode, returning the connection's previous setting. In TERSE mode, returned messages include severity, primary text, and position only; this will normally fit on a single line. The default mode produces messages that include the above plus any detail, hint, or context fields (these might span multiple lines). The VERBOSE mode includes all available fields. Changing the verbosity does not affect the messages available from already-existing PGresult objects, only subsequently-created ones. PQtrace PQtrace Enables tracing of the client/server communication to a debugging file stream. void PQtrace(PGconn *conn, FILE *stream); On Windows, if the libpq library and an application are compiled with different flags, this function call will crash the application because the internal representation of the FILE pointers differ. Specifically, multithreaded/single-threaded, release/debug, and static/dynamic flags should be the same for the library and all applications using that library. PQuntrace PQuntrace Disables tracing started by PQtrace. void PQuntrace(PGconn *conn); As always, there are some functions that just don't fit anywhere. PQfreemem PQfreemem Frees memory allocated by libpq. void PQfreemem(void *ptr); Frees memory allocated by libpq, particularly PQescapeByteaConn, PQescapeBytea, PQunescapeBytea, and PQnotifies. It is particularly important that this function, rather than free(), be used on Microsoft Windows. This is because allocating memory in a DLL and releasing it in the application works only if multithreaded/single-threaded, release/debug, and static/dynamic flags are the same for the DLL and the application. On non-Microsoft Windows platforms, this function is the same as the standard library function free(). PQconninfoFree PQconninfoFree Frees the data structures allocated by PQconndefaults or PQconninfoParse. void PQconninfoFree(PQconninfoOption *connOptions); A simple PQfreemem will not do for this, since the array contains references to subsidiary strings. PQencryptPassword PQencryptPassword Prepares the encrypted form of a PostgreSQL password. char * PQencryptPassword(const char *passwd, const char *user); This function is intended to be used by client applications that wish to send commands like ALTER USER joe PASSWORD 'pwd'. It is good practice not to send the original cleartext password in such a command, because it might be exposed in command logs, activity displays, and so on. Instead, use this function to convert the password to encrypted form before it is sent. The arguments are the cleartext password, and the SQL name of the user it is for. The return value is a string allocated by malloc, or NULL if out of memory. The caller can assume the string doesn't contain any special characters that would require escaping. Use PQfreemem to free the result when done with it. PQmakeEmptyPGresult PQmakeEmptyPGresult Constructs an empty PGresult object with the given status. PGresult *PQmakeEmptyPGresult(PGconn *conn, ExecStatusType status); This is libpq's internal function to allocate and initialize an empty PGresult object. This function returns NULL if memory could not be allocated. It is exported because some applications find it useful to generate result objects (particularly objects with error status) themselves. If conn is not null and status indicates an error, the current error message of the specified connection is copied into the PGresult. Also, if conn is not null, any event procedures registered in the connection are copied into the PGresult. (They do not get PGEVT_RESULTCREATE calls, but see PQfireResultCreateEvents.) Note that PQclear should eventually be called on the object, just as with a PGresult returned by libpq itself. PQfireResultCreateEvents PQfireResultCreateEvents Fires a PGEVT_RESULTCREATE event (see ) for each event procedure registered in the PGresult object. Returns non-zero for success, zero if any event procedure fails. int PQfireResultCreateEvents(PGconn *conn, PGresult *res); The conn argument is passed through to event procedures but not used directly. It can be NULL if the event procedures won't use it. Event procedures that have already received a PGEVT_RESULTCREATE or PGEVT_RESULTCOPY event for this object are not fired again. The main reason that this function is separate from PQmakeEmptyPGResult is that it is often appropriate to create a PGresult and fill it with data before invoking the event procedures. PQcopyResult PQcopyResult Makes a copy of a PGresult object. The copy is not linked to the source result in any way and PQclear must be called when the copy is no longer needed. If the function fails, NULL is returned. PGresult *PQcopyResult(const PGresult *src, int flags); This is not intended to make an exact copy. The returned result is always put into PGRES_TUPLES_OK status, and does not copy any error message in the source. (It does copy the command status string, however.) The flags argument determines what else is copied. It is a bitwise OR of several flags. PG_COPYRES_ATTRS specifies copying the source result's attributes (column definitions). PG_COPYRES_TUPLES specifies copying the source result's tuples. (This implies copying the attributes, too.) PG_COPYRES_NOTICEHOOKS specifies copying the source result's notify hooks. PG_COPYRES_EVENTS specifies copying the source result's events. (But any instance data associated with the source is not copied.) PQsetResultAttrs PQsetResultAttrs Sets the attributes of a PGresult object. int PQsetResultAttrs(PGresult *res, int numAttributes, PGresAttDesc *attDescs); The provided attDescs are copied into the result. If the attDescs pointer is NULL or numAttributes is less than one, the request is ignored and the function succeeds. If res already contains attributes, the function will fail. If the function fails, the return value is zero. If the function succeeds, the return value is non-zero. PQsetvalue PQsetvalue Sets a tuple field value of a PGresult object. int PQsetvalue(PGresult *res, int tup_num, int field_num, char *value, int len); The function will automatically grow the result's internal tuples array as needed. However, the tup_num argument must be less than or equal to PQntuples, meaning this function can only grow the tuples array one tuple at a time. But any field of any existing tuple can be modified in any order. If a value at field_num already exists, it will be overwritten. If len is -1 or value is NULL, the field value will be set to an SQL null value. The value is copied into the result's private storage, thus is no longer needed after the function returns. If the function fails, the return value is zero. If the function succeeds, the return value is non-zero. PQresultAlloc PQresultAlloc Allocate subsidiary storage for a PGresult object. void *PQresultAlloc(PGresult *res, size_t nBytes); Any memory allocated with this function will be freed when res is cleared. If the function fails, the return value is NULL. The result is guaranteed to be adequately aligned for any type of data, just as for malloc. notice processing in libpq Notice and warning messages generated by the server are not returned by the query execution functions, since they do not imply failure of the query. Instead they are passed to a notice handling function, and execution continues normally after the handler returns. The default notice handling function prints the message on stderr, but the application can override this behavior by supplying its own handling function. For historical reasons, there are two levels of notice handling, called the notice receiver and notice processor. The default behavior is for the notice receiver to format the notice and pass a string to the notice processor for printing. However, an application that chooses to provide its own notice receiver will typically ignore the notice processor layer and just do all the work in the notice receiver. The function PQsetNoticeReceiver notice receiverPQsetNoticeReceiver sets or examines the current notice receiver for a connection object. Similarly, PQsetNoticeProcessor notice processorPQsetNoticeProcessor sets or examines the current notice processor. typedef void (*PQnoticeReceiver) (void *arg, const PGresult *res); PQnoticeReceiver PQsetNoticeReceiver(PGconn *conn, PQnoticeReceiver proc, void *arg); typedef void (*PQnoticeProcessor) (void *arg, const char *message); PQnoticeProcessor PQsetNoticeProcessor(PGconn *conn, PQnoticeProcessor proc, void *arg); Each of these functions returns the previous notice receiver or processor function pointer, and sets the new value. If you supply a null function pointer, no action is taken, but the current pointer is returned. When a notice or warning message is received from the server, or generated internally by libpq, the notice receiver function is called. It is passed the message in the form of a PGRES_NONFATAL_ERROR PGresult. (This allows the receiver to extract individual fields using PQresultErrorField, or the complete preformatted message using PQresultErrorMessage.) The same void pointer passed to PQsetNoticeReceiver is also passed. (This pointer can be used to access application-specific state if needed.) The default notice receiver simply extracts the message (using PQresultErrorMessage) and passes it to the notice processor. The notice processor is responsible for handling a notice or warning message given in text form. It is passed the string text of the message (including a trailing newline), plus a void pointer that is the same one passed to PQsetNoticeProcessor. (This pointer can be used to access application-specific state if needed.) The default notice processor is simply: static void defaultNoticeProcessor(void *arg, const char *message) { fprintf(stderr, "%s", message); } Once you have set a notice receiver or processor, you should expect that that function could be called as long as either the PGconn object or PGresult objects made from it exist. At creation of a PGresult, the PGconn's current notice handling pointers are copied into the PGresult for possible use by functions like PQgetvalue. libpq's event system is designed to notify registered event handlers about interesting libpq events, such as the creation or destruction of PGconn and PGresult objects. A principal use case is that this allows applications to associate their own data with a PGconn or PGresult and ensure that that data is freed at an appropriate time. Each registered event handler is associated with two pieces of data, known to libpq only as opaque void * pointers. There is a passthrough pointer that is provided by the application when the event handler is registered with a PGconn. The passthrough pointer never changes for the life of the PGconn and all PGresults generated from it; so if used, it must point to long-lived data. In addition there is an instance data pointer, which starts out NULL in every PGconn and PGresult. This pointer can be manipulated using the PQinstanceData, PQsetInstanceData, PQresultInstanceData and PQsetResultInstanceData functions. Note that unlike the passthrough pointer, instance data of a PGconn is not automatically inherited by PGresults created from it. libpq does not know what passthrough and instance data pointers point to (if anything) and will never attempt to free them — that is the responsibility of the event handler. The enum PGEventId names the types of events handled by the event system. All its values have names beginning with PGEVT. For each event type, there is a corresponding event info structure that carries the parameters passed to the event handlers. The event types are: PGEVT_REGISTER The register event occurs when PQregisterEventProc is called. It is the ideal time to initialize any instanceData an event procedure may need. Only one register event will be fired per event handler per connection. If the event procedure fails, the registration is aborted. typedef struct { PGconn *conn; } PGEventRegister; When a PGEVT_REGISTER event is received, the evtInfo pointer should be cast to a PGEventRegister *. This structure contains a PGconn that should be in the CONNECTION_OK status; guaranteed if one calls PQregisterEventProc right after obtaining a good PGconn. When returning a failure code, all cleanup must be performed as no PGEVT_CONNDESTROY event will be sent. PGEVT_CONNRESET The connection reset event is fired on completion of PQreset or PQresetPoll. In both cases, the event is only fired if the reset was successful. If the event procedure fails, the entire connection reset will fail; the PGconn is put into CONNECTION_BAD status and PQresetPoll will return PGRES_POLLING_FAILED. typedef struct { PGconn *conn; } PGEventConnReset; When a PGEVT_CONNRESET event is received, the evtInfo pointer should be cast to a PGEventConnReset *. Although the contained PGconn was just reset, all event data remains unchanged. This event should be used to reset/reload/requery any associated instanceData. Note that even if the event procedure fails to process PGEVT_CONNRESET, it will still receive a PGEVT_CONNDESTROY event when the connection is closed. PGEVT_CONNDESTROY The connection destroy event is fired in response to PQfinish. It is the event procedure's responsibility to properly clean up its event data as libpq has no ability to manage this memory. Failure to clean up will lead to memory leaks. typedef struct { PGconn *conn; } PGEventConnDestroy; When a PGEVT_CONNDESTROY event is received, the evtInfo pointer should be cast to a PGEventConnDestroy *. This event is fired prior to PQfinish performing any other cleanup. The return value of the event procedure is ignored since there is no way of indicating a failure from PQfinish. Also, an event procedure failure should not abort the process of cleaning up unwanted memory. PGEVT_RESULTCREATE The result creation event is fired in response to any query execution function that generates a result, including PQgetResult. This event will only be fired after the result has been created successfully. typedef struct { PGconn *conn; PGresult *result; } PGEventResultCreate; When a PGEVT_RESULTCREATE event is received, the evtInfo pointer should be cast to a PGEventResultCreate *. The conn is the connection used to generate the result. This is the ideal place to initialize any instanceData that needs to be associated with the result. If the event procedure fails, the result will be cleared and the failure will be propagated. The event procedure must not try to PQclear the result object for itself. When returning a failure code, all cleanup must be performed as no PGEVT_RESULTDESTROY event will be sent. PGEVT_RESULTCOPY The result copy event is fired in response to PQcopyResult. This event will only be fired after the copy is complete. Only event procedures that have successfully handled the PGEVT_RESULTCREATE or PGEVT_RESULTCOPY event for the source result will receive PGEVT_RESULTCOPY events. typedef struct { const PGresult *src; PGresult *dest; } PGEventResultCopy; When a PGEVT_RESULTCOPY event is received, the evtInfo pointer should be cast to a PGEventResultCopy *. The src result is what was copied while the dest result is the copy destination. This event can be used to provide a deep copy of instanceData, since PQcopyResult cannot do that. If the event procedure fails, the entire copy operation will fail and the dest result will be cleared. When returning a failure code, all cleanup must be performed as no PGEVT_RESULTDESTROY event will be sent for the destination result. PGEVT_RESULTDESTROY The result destroy event is fired in response to a PQclear. It is the event procedure's responsibility to properly clean up its event data as libpq has no ability to manage this memory. Failure to clean up will lead to memory leaks. typedef struct { PGresult *result; } PGEventResultDestroy; When a PGEVT_RESULTDESTROY event is received, the evtInfo pointer should be cast to a PGEventResultDestroy *. This event is fired prior to PQclear performing any other cleanup. The return value of the event procedure is ignored since there is no way of indicating a failure from PQclear. Also, an event procedure failure should not abort the process of cleaning up unwanted memory. PGEventProc PGEventProc PGEventProc is a typedef for a pointer to an event procedure, that is, the user callback function that receives events from libpq. The signature of an event procedure must be int eventproc(PGEventId evtId, void *evtInfo, void *passThrough) The evtId parameter indicates which PGEVT event occurred. The evtInfo pointer must be cast to the appropriate structure type to obtain further information about the event. The passThrough parameter is the pointer provided to PQregisterEventProc when the event procedure was registered. The function should return a non-zero value if it succeeds and zero if it fails. A particular event procedure can be registered only once in any PGconn. This is because the address of the procedure is used as a lookup key to identify the associated instance data. On Windows, functions can have two different addresses: one visible from outside a DLL and another visible from inside the DLL. One should be careful that only one of these addresses is used with libpq's event-procedure functions, else confusion will result. The simplest rule for writing code that will work is to ensure that event procedures are declared static. If the procedure's address must be available outside its own source file, expose a separate function to return the address. PQregisterEventProc PQregisterEventProc Registers an event callback procedure with libpq. int PQregisterEventProc(PGconn *conn, PGEventProc proc, const char *name, void *passThrough); An event procedure must be registered once on each PGconn you want to receive events about. There is no limit, other than memory, on the number of event procedures that can be registered with a connection. The function returns a non-zero value if it succeeds and zero if it fails. The proc argument will be called when a libpq event is fired. Its memory address is also used to lookup instanceData. The name argument is used to refer to the event procedure in error messages. This value cannot be NULL or a zero-length string. The name string is copied into the PGconn, so what is passed need not be long-lived. The passThrough pointer is passed to the proc whenever an event occurs. This argument can be NULL. PQsetInstanceData PQsetInstanceData Sets the connection conn's instanceData for procedure proc to data. This returns non-zero for success and zero for failure. (Failure is only possible if proc has not been properly registered in conn.) int PQsetInstanceData(PGconn *conn, PGEventProc proc, void *data); PQinstanceData PQinstanceData Returns the connection conn's instanceData associated with procedure proc, or NULL if there is none. void *PQinstanceData(const PGconn *conn, PGEventProc proc); PQresultSetInstanceData PQresultSetInstanceData Sets the result's instanceData for proc to data. This returns non-zero for success and zero for failure. (Failure is only possible if proc has not been properly registered in the result.) int PQresultSetInstanceData(PGresult *res, PGEventProc proc, void *data); PQresultInstanceData PQresultInstanceData Returns the result's instanceData associated with proc, or NULL if there is none. void *PQresultInstanceData(const PGresult *res, PGEventProc proc); Here is a skeleton example of managing private data associated with libpq connections and results. /* The instanceData */ typedef struct { int n; char *str; } mydata; /* PGEventProc */ static int myEventProc(PGEventId evtId, void *evtInfo, void *passThrough); int main(void) { mydata *data; PGresult *res; PGconn *conn = PQconnectdb("dbname = postgres"); if (PQstatus(conn) != CONNECTION_OK) { fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn)); PQfinish(conn); return 1; } /* called once on any connection that should receive events. * Sends a PGEVT_REGISTER to myEventProc. */ if (!PQregisterEventProc(conn, myEventProc, "mydata_proc", NULL)) { fprintf(stderr, "Cannot register PGEventProc\n"); PQfinish(conn); return 1; } /* conn instanceData is available */ data = PQinstanceData(conn, myEventProc); /* Sends a PGEVT_RESULTCREATE to myEventProc */ res = PQexec(conn, "SELECT 1 + 1"); /* result instanceData is available */ data = PQresultInstanceData(res, myEventProc); /* If PG_COPYRES_EVENTS is used, sends a PGEVT_RESULTCOPY to myEventProc */ res_copy = PQcopyResult(res, PG_COPYRES_TUPLES | PG_COPYRES_EVENTS); /* result instanceData is available if PG_COPYRES_EVENTS was * used during the PQcopyResult call. */ data = PQresultInstanceData(res_copy, myEventProc); /* Both clears send a PGEVT_RESULTDESTROY to myEventProc */ PQclear(res); PQclear(res_copy); /* Sends a PGEVT_CONNDESTROY to myEventProc */ PQfinish(conn); return 0; } static int myEventProc(PGEventId evtId, void *evtInfo, void *passThrough) { switch (evtId) { case PGEVT_REGISTER: { PGEventRegister *e = (PGEventRegister *)evtInfo; mydata *data = get_mydata(e->conn); /* associate app specific data with connection */ PQsetInstanceData(e->conn, myEventProc, data); break; } case PGEVT_CONNRESET: { PGEventConnReset *e = (PGEventConnReset *)evtInfo; mydata *data = PQinstanceData(e->conn, myEventProc); if (data) memset(data, 0, sizeof(mydata)); break; } case PGEVT_CONNDESTROY: { PGEventConnDestroy *e = (PGEventConnDestroy *)evtInfo; mydata *data = PQinstanceData(e->conn, myEventProc); /* free instance data because the conn is being destroyed */ if (data) free_mydata(data); break; } case PGEVT_RESULTCREATE: { PGEventResultCreate *e = (PGEventResultCreate *)evtInfo; mydata *conn_data = PQinstanceData(e->conn, myEventProc); mydata *res_data = dup_mydata(conn_data); /* associate app specific data with result (copy it from conn) */ PQsetResultInstanceData(e->result, myEventProc, res_data); break; } case PGEVT_RESULTCOPY: { PGEventResultCopy *e = (PGEventResultCopy *)evtInfo; mydata *src_data = PQresultInstanceData(e->src, myEventProc); mydata *dest_data = dup_mydata(src_data); /* associate app specific data with result (copy it from a result) */ PQsetResultInstanceData(e->dest, myEventProc, dest_data); break; } case PGEVT_RESULTDESTROY: { PGEventResultDestroy *e = (PGEventResultDestroy *)evtInfo; mydata *data = PQresultInstanceData(e->result, myEventProc); /* free instance data because the result is being destroyed */ if (data) free_mydata(data); break; } /* unknown event id, just return TRUE. */ default: break; } return TRUE; /* event processing succeeded */ } ]]> environment variable The following environment variables can be used to select default connection parameter values, which will be used by PQconnectdb, PQsetdbLogin and PQsetdb if no value is directly specified by the calling code. These are useful to avoid hard-coding database connection information into simple client applications, for example. PGHOST PGHOST behaves the same as the connection parameter. PGHOSTADDR PGHOSTADDR behaves the same as the connection parameter. This can be set instead of or in addition to PGHOST to avoid DNS lookup overhead. PGPORT PGPORT behaves the same as the connection parameter. PGDATABASE PGDATABASE behaves the same as the connection parameter. PGUSER PGUSER behaves the same as the connection parameter. PGPASSWORD PGPASSWORD behaves the same as the connection parameter. Use of this environment variable is not recommended for security reasons, as some operating systems allow non-root users to see process environment variables via ps; instead consider using the ~/.pgpass file (see ). PGPASSFILE PGPASSFILE specifies the name of the password file to use for lookups. If not set, it defaults to ~/.pgpass (see ). PGSERVICE PGSERVICE behaves the same as the connection parameter. PGSERVICEFILE PGSERVICEFILE specifies the name of the per-user connection service file. If not set, it defaults to ~/.pg_service.conf (see ). PGREALM PGREALM sets the Kerberos realm to use with PostgreSQL, if it is different from the local realm. If PGREALM is set, libpq applications will attempt authentication with servers for this realm and use separate ticket files to avoid conflicts with local ticket files. This environment variable is only used if Kerberos authentication is selected by the server. PGOPTIONS PGOPTIONS behaves the same as the connection parameter. PGAPPNAME PGAPPNAME behaves the same as the connection parameter. PGSSLMODE PGSSLMODE behaves the same as the connection parameter. PGREQUIRESSL PGREQUIRESSL behaves the same as the connection parameter. PGSSLCERT PGSSLCERT behaves the same as the connection parameter. PGSSLKEY PGSSLKEY behaves the same as the connection parameter. PGSSLROOTCERT PGSSLROOTCERT behaves the same as the connection parameter. PGSSLCRL PGSSLCRL behaves the same as the connection parameter. PGREQUIREPEER PGREQUIREPEER behaves the same as the connection parameter. PGKRBSRVNAME PGKRBSRVNAME behaves the same as the connection parameter. PGGSSLIB PGGSSLIB behaves the same as the connection parameter. PGCONNECT_TIMEOUT PGCONNECT_TIMEOUT behaves the same as the connection parameter. The following environment variables can be used to specify default behavior for each PostgreSQL session. (See also the and commands for ways to set default behavior on a per-user or per-database basis.) PGDATESTYLE PGDATESTYLE sets the default style of date/time representation. (Equivalent to SET datestyle TO ....) PGTZ PGTZ sets the default time zone. (Equivalent to SET timezone TO ....) PGCLIENTENCODING PGCLIENTENCODING sets the default client character set encoding. (Equivalent to SET client_encoding TO ....) PGGEQO PGGEQO sets the default mode for the genetic query optimizer. (Equivalent to SET geqo TO ....) Refer to the SQL command for information on correct values for these environment variables. The following environment variables determine internal behavior of libpq; they override compiled-in defaults. PGSYSCONFDIR PGSYSCONFDIR sets the directory containing the pg_service.conf file and in a future version possibly other system-wide configuration files. PGLOCALEDIR PGLOCALEDIR sets the directory containing the locale files for message internationalization. password file .pgpass The file .pgpass in a user's home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). On Microsoft Windows the file is named %APPDATA%\postgresql\pgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile). This file should contain lines of the following format: hostname:port:database:username:password Each of the first four fields can be a literal value, or *, which matches anything. The password field from the first line that matches the current connection parameters will be used. (Therefore, put more-specific entries first when you are using wildcards.) If an entry needs to contain : or \, escape this character with \. A host name of localhost matches both TCP (host name localhost) and Unix domain socket (pghost empty or the default socket directory) connections coming from the local machine. In a standby server, a database name of replication matches streaming replication connections made to the master server. On Unix systems, the permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored. On Microsoft Windows, it is assumed that the file is stored in a directory that is secure, so no special permissions check is made. connection service file pg_service.conf .pg_service.conf The connection service file allows libpq connection parameters to be associated with a single service name. That service name can then be specified by a libpq connection, and the associated settings will be used. This allows connection parameters to be modified without requiring a recompile of the libpq application. The service name can also be specified using the PGSERVICE environment variable. The connection service file can be a per-user service file at ~/.pg_service.conf or the location specified by the environment variable PGSERVICEFILE, or it can be a system-wide file at etc/pg_service.conf or in the directory specified by the environment variable PGSYSCONFDIR. If service definitions with the same name exist in the user and the system file, the user file takes precedence. The file uses an INI file format where the section name is the service name and the parameters are connection parameters; see for a list. For example: # comment [mydb] host=somehost port=5433 user=admin An example file is provided at share/pg_service.conf.sample. LDAP connection parameter lookup If libpq has been compiled with LDAP support (option --with-ldap for configure) it is possible to retrieve connection options like host or dbname via LDAP from a central server. The advantage is that if the connection parameters for a database change, the connection information doesn't have to be updated on all client machines. LDAP connection parameter lookup uses the connection service file pg_service.conf (see ). A line in a pg_service.conf stanza that starts with ldap:// will be recognized as an LDAP URL and an LDAP query will be performed. The result must be a list of keyword = value pairs which will be used to set connection options. The URL must conform to RFC 1959 and be of the form ldap://[hostname[:port]]/search_base?attribute?search_scope?filter where hostname defaults to localhost and port defaults to 389. Processing of pg_service.conf is terminated after a successful LDAP lookup, but is continued if the LDAP server cannot be contacted. This is to provide a fallback with further LDAP URL lines that point to different LDAP servers, classical keyword = value pairs, or default connection options. If you would rather get an error message in this case, add a syntactically incorrect line after the LDAP URL. A sample LDAP entry that has been created with the LDIF file version:1 dn:cn=mydatabase,dc=mycompany,dc=com changetype:add objectclass:top objectclass:groupOfUniqueNames cn:mydatabase uniqueMember:host=dbserver.mycompany.com uniqueMember:port=5439 uniqueMember:dbname=mydb uniqueMember:user=mydb_user uniqueMember:sslmode=require might be queried with the following LDAP URL: ldap://ldap.mycompany.com/dc=mycompany,dc=com?uniqueMember?one?(cn=mydatabase) You can also mix regular service file entries with LDAP lookups. A complete example for a stanza in pg_service.conf would be: # only host and port are stored in LDAP, specify dbname and user explicitly [customerdb] dbname=customer user=appuser ldap://ldap.acme.com/cn=dbserver,cn=hosts?pgconnectinfo?base?(objectclass=*) SSL PostgreSQL has native support for using SSL connections to encrypt client/server communications for increased security. See for details about the server-side SSL functionality. libpq reads the system-wide OpenSSL configuration file. By default, this file is named openssl.cnf and is located in the directory reported by openssl version -d. This default can be overridden by setting environment variable OPENSSL_CONF to the name of the desired configuration file. By default, PostgreSQL will not perform any verification of the server certificate. This means that it is possible to spoof the server identity (for example by modifying a DNS record or by taking over the server IP address) without the client knowing. In order to prevent spoofing, SSL certificate verification must be used. If the parameter sslmode is set to verify-ca, libpq will verify that the server is trustworthy by checking the certificate chain up to a trusted certificate authority (CA). If sslmode is set to verify-full, libpq will also verify that the server host name matches its certificate. The SSL connection will fail if the server certificate cannot be verified. verify-full is recommended in most security-sensitive environments. In verify-full mode, the cn (Common Name) attribute of the certificate is matched against the host name. If the cn attribute starts with an asterisk (*), it will be treated as a wildcard, and will match all characters except a dot (.). This means the certificate will not match subdomains. If the connection is made using an IP address instead of a host name, the IP address will be matched (without doing any DNS lookups). To allow server certificate verification, the certificate(s) of one or more trusted CAs must be placed in the file ~/.postgresql/root.crt in the user's home directory. (On Microsoft Windows the file is named %APPDATA%\postgresql\root.crt.) Certificate Revocation List (CRL) entries are also checked if the file ~/.postgresql/root.crl exists (%APPDATA%\postgresql\root.crl on Microsoft Windows). The location of the root certificate file and the CRL can be changed by setting the connection parameters sslrootcert and sslcrl or the environment variables PGSSLROOTCERT and PGSSLCRL. If the server requests a trusted client certificate, libpq will send the certificate stored in file ~/.postgresql/postgresql.crt in the user's home directory. The certificate must be signed by one of the certificate authorities (CA) trusted by the server. A matching private key file ~/.postgresql/postgresql.key must also be present. The private key file must not allow any access to world or group; achieve this by the command chmod 0600 ~/.postgresql/postgresql.key. On Microsoft Windows these files are named %APPDATA%\postgresql\postgresql.crt and %APPDATA%\postgresql\postgresql.key, and there is no special permissions check since the directory is presumed secure. The location of the certificate and key files can be overridden by the connection parameters sslcert and sslkey or the environment variables PGSSLCERT and PGSSLKEY. In some cases, the client certificate might be signed by an intermediate certificate authority, rather than one that is directly trusted by the server. To use such a certificate, append the certificate of the signing authority to the postgresql.crt file, then its parent authority's certificate, and so on up to a root authority that is trusted by the server. The root certificate should be included in every case where postgresql.crt contains more than one certificate. Note that root.crt lists the top-level CAs that are considered trusted for signing server certificates. In principle it need not list the CA that signed the client's certificate, though in most cases that CA would also be trusted for server certificates. The different values for the sslmode parameter provide different levels of protection. SSL can provide protection against three types of attacks: Type Description Eavesdropping If a third party can examine the network traffic between the client and the server, it can read both connection information (including the user name and password) and the data that is passed. SSL uses encryption to prevent this. Man in the middle (MITM) If a third party can modify the data while passing between the client and server, it can pretend to be the server and therefore see and modify data even if it is encrypted. The third party can then forward the connection information and data to the original server, making it impossible to detect this attack. Common vectors to do this include DNS poisoning and address hijacking, whereby the client is directed to a different server than intended. There are also several other attack methods that can accomplish this. SSL uses certificate verification to prevent this, by authenticating the server to the client. Impersonation If a third party can pretend to be an authorized client, it can simply access data it should not have access to. Typically this can happen through insecure password management. SSL uses client certificates to prevent this, by making sure that only holders of valid certificates can access the server. For a connection to be known secure, SSL usage must be configured on both the client and the server before the connection is made. If it is only configured on the server, the client may end up sending sensitive information (e.g. passwords) before it knows that the server requires high security. In libpq, secure connections can be ensured by setting the sslmode parameter to verify-full or verify-ca, and providing the system with a root certificate to verify against. This is analogous to using an https URL for encrypted web browsing. Once the server has been authenticated, the client can pass sensitive data. This means that up until this point, the client does not need to know if certificates will be used for authentication, making it safe to specify that only in the server configuration. All SSL options carry overhead in the form of encryption and key-exchange, so there is a tradeoff that has to be made between performance and security. The following table illustrates the risks the different sslmode values protect against, and what statement they make about security and overhead: sslmode Eavesdropping protection MITM protection Statement disabled No No I don't care about security, and I don't want to pay the overhead of encryption. allow Maybe No I don't care about security, but I will pay the overhead of encryption if the server insists on it. prefer Maybe No I don't care about encryption, but I wish to pay the overhead of encryption if the server supports it. require Yes No I want my data to be encrypted, and I accept the overhead. I trust that the network will make sure I always connect to the server I want. verify-ca Yes Depends on CA-policy I want my data encrypted, and I accept the overhead. I want to be sure that I connect to a server that I trust. verify-full Yes Yes I want my data encrypted, and I accept the overhead. I want to be sure that I connect to a server I trust, and that it's the one I specify. The difference between verify-ca and verify-full depends on the policy of the root CA. If a public CA is used, verify-ca allows connections to a server that somebody else may have registered with the CA. In this case, verify-full should always be used. If a local CA is used, or even a self-signed certificate, using verify-ca often provides enough protection. The default value for sslmode is prefer. As is shown in the table, this makes no sense from a security point of view, and it only promises performance overhead if possible. It is only provided as the default for backwards compatibility, and is not recommended in secure deployments. File Contents Effect ~/.postgresql/postgresql.crt client certificate requested by server ~/.postgresql/postgresql.key client private key proves client certificate sent by owner; does not indicate certificate owner is trustworthy ~/.postgresql/root.crt trusted certificate authorities checks that server certificate is signed by a trusted certificate authority ~/.postgresql/root.crl certificates revoked by certificate authorities server certificate must not be on this list If your application initializes libssl and/or libcrypto libraries and libpq is built with SSL support, you should call PQinitOpenSSL to tell libpq that the libssl and/or libcrypto libraries have been initialized by your application, so that libpq will not also initialize those libraries. See for details on the SSL API. PQinitOpenSSL PQinitOpenSSL Allows applications to select which security libraries to initialize. void PQinitOpenSSL(int do_ssl, int do_crypto); When do_ssl is non-zero, libpq will initialize the OpenSSL library before first opening a database connection. When do_crypto is non-zero, the libcrypto library will be initialized. By default (if PQinitOpenSSL is not called), both libraries are initialized. When SSL support is not compiled in, this function is present but does nothing. If your application uses and initializes either OpenSSL or its underlying libcrypto library, you must call this function with zeroes for the appropriate parameter(s) before first opening a database connection. Also be sure that you have done that initialization before opening a database connection. PQinitSSL PQinitSSL Allows applications to select which security libraries to initialize. void PQinitSSL(int do_ssl); This function is equivalent to PQinitOpenSSL(do_ssl, do_ssl). It is sufficient for applications that initialize both or neither of OpenSSL and libcrypto. PQinitSSL has been present since PostgreSQL 8.0, while PQinitOpenSSL was added in PostgreSQL 8.4, so PQinitSSL might be preferable for applications that need to work with older versions of libpq. threads with libpq libpq is reentrant and thread-safe by default. You might need to use special compiler command-line options when you compile your application code. Refer to your system's documentation for information about how to build thread-enabled applications, or look in src/Makefile.global for PTHREAD_CFLAGS and PTHREAD_LIBS. This function allows the querying of libpq's thread-safe status: PQisthreadsafe PQisthreadsafe Returns the thread safety status of the libpq library. int PQisthreadsafe(); Returns 1 if the libpq is thread-safe and 0 if it is not. One thread restriction is that no two threads attempt to manipulate the same PGconn object at the same time. In particular, you cannot issue concurrent commands from different threads through the same connection object. (If you need to run concurrent commands, use multiple connections.) PGresult objects are read-only after creation, and so can be passed around freely between threads. The deprecated functions PQrequestCancel and PQoidStatus are not thread-safe and should not be used in multithread programs. PQrequestCancel can be replaced by PQcancel. PQoidStatus can be replaced by PQoidValue. If you are using Kerberos inside your application (in addition to inside libpq), you will need to do locking around Kerberos calls because Kerberos functions are not thread-safe. See function PQregisterThreadLock in the libpq source code for a way to do cooperative locking between libpq and your application. If you experience problems with threaded applications, run the program in src/tools/thread to see if your platform has thread-unsafe functions. This program is run by configure, but for binary distributions your library might not match the library used to build the binaries. compiling libpq applications To build (i.e., compile and link) a program using libpq you need to do all of the following things: Include the libpq-fe.h header file: #include <libpq-fe.h> If you failed to do that then you will normally get error messages from your compiler similar to: foo.c: In function `main': foo.c:34: `PGconn' undeclared (first use in this function) foo.c:35: `PGresult' undeclared (first use in this function) foo.c:54: `CONNECTION_BAD' undeclared (first use in this function) foo.c:68: `PGRES_COMMAND_OK' undeclared (first use in this function) foo.c:95: `PGRES_TUPLES_OK' undeclared (first use in this function) Point your compiler to the directory where the PostgreSQL header files were installed, by supplying the -Idirectory option to your compiler. (In some cases the compiler will look into the directory in question by default, so you can omit this option.) For instance, your compile command line could look like: cc -c -I/usr/local/pgsql/include testprog.c If you are using makefiles then add the option to the CPPFLAGS variable: CPPFLAGS += -I/usr/local/pgsql/include If there is any chance that your program might be compiled by other users then you should not hardcode the directory location like that. Instead, you can run the utility pg_configpg_configwith libpq to find out where the header files are on the local system: $ pg_config --includedir /usr/local/include Failure to specify the correct option to the compiler will result in an error message such as: testlibpq.c:8:22: libpq-fe.h: No such file or directory When linking the final program, specify the option -lpq so that the libpq library gets pulled in, as well as the option -Ldirectory to point the compiler to the directory where the libpq library resides. (Again, the compiler will search some directories by default.) For maximum portability, put the -L option before the -lpq option. For example: cc -o testprog testprog1.o testprog2.o -L/usr/local/pgsql/lib -lpq You can find out the library directory using pg_config as well: $ pg_config --libdir /usr/local/pgsql/lib Error messages that point to problems in this area could look like the following: testlibpq.o: In function `main': testlibpq.o(.text+0x60): undefined reference to `PQsetdbLogin' testlibpq.o(.text+0x71): undefined reference to `PQstatus' testlibpq.o(.text+0xa4): undefined reference to `PQerrorMessage' This means you forgot -lpq. /usr/bin/ld: cannot find -lpq This means you forgot the -L option or did not specify the right directory. These examples and others can be found in the directory src/test/examples in the source code distribution. #include #include static void exit_nicely(PGconn *conn) { PQfinish(conn); exit(1); } int main(int argc, char **argv) { const char *conninfo; PGconn *conn; PGresult *res; int nFields; int i, j; /* * If the user supplies a parameter on the command line, use it as the * conninfo string; otherwise default to setting dbname=postgres and using * environment variables or defaults for all other connection parameters. */ if (argc > 1) conninfo = argv[1]; else conninfo = "dbname = postgres"; /* Make a connection to the database */ conn = PQconnectdb(conninfo); /* Check to see that the backend connection was successfully made */ if (PQstatus(conn) != CONNECTION_OK) { fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn)); exit_nicely(conn); } /* * Our test case here involves using a cursor, for which we must be inside * a transaction block. We could do the whole thing with a single * PQexec() of "select * from pg_database", but that's too trivial to make * a good example. */ /* Start a transaction block */ res = PQexec(conn, "BEGIN"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } /* * Should PQclear PGresult whenever it is no longer needed to avoid memory * leaks */ PQclear(res); /* * Fetch rows from pg_database, the system catalog of databases */ res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } PQclear(res); res = PQexec(conn, "FETCH ALL in myportal"); if (PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } /* first, print out the attribute names */ nFields = PQnfields(res); for (i = 0; i < nFields; i++) printf("%-15s", PQfname(res, i)); printf("\n\n"); /* next, print out the rows */ for (i = 0; i < PQntuples(res); i++) { for (j = 0; j < nFields; j++) printf("%-15s", PQgetvalue(res, i, j)); printf("\n"); } PQclear(res); /* close the portal ... we don't bother to check for errors ... */ res = PQexec(conn, "CLOSE myportal"); PQclear(res); /* end the transaction */ res = PQexec(conn, "END"); PQclear(res); /* close the connection to the database and cleanup */ PQfinish(conn); return 0; } ]]> #include #include #include #include #include static void exit_nicely(PGconn *conn) { PQfinish(conn); exit(1); } int main(int argc, char **argv) { const char *conninfo; PGconn *conn; PGresult *res; PGnotify *notify; int nnotifies; /* * If the user supplies a parameter on the command line, use it as the * conninfo string; otherwise default to setting dbname=postgres and using * environment variables or defaults for all other connection parameters. */ if (argc > 1) conninfo = argv[1]; else conninfo = "dbname = postgres"; /* Make a connection to the database */ conn = PQconnectdb(conninfo); /* Check to see that the backend connection was successfully made */ if (PQstatus(conn) != CONNECTION_OK) { fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn)); exit_nicely(conn); } /* * Issue LISTEN command to enable notifications from the rule's NOTIFY. */ res = PQexec(conn, "LISTEN TBL2"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } /* * should PQclear PGresult whenever it is no longer needed to avoid memory * leaks */ PQclear(res); /* Quit after four notifies are received. */ nnotifies = 0; while (nnotifies < 4) { /* * Sleep until something happens on the connection. We use select(2) * to wait for input, but you could also use poll() or similar * facilities. */ int sock; fd_set input_mask; sock = PQsocket(conn); if (sock < 0) break; /* shouldn't happen */ FD_ZERO(&input_mask); FD_SET(sock, &input_mask); if (select(sock + 1, &input_mask, NULL, NULL, NULL) < 0) { fprintf(stderr, "select() failed: %s\n", strerror(errno)); exit_nicely(conn); } /* Now check for input */ PQconsumeInput(conn); while ((notify = PQnotifies(conn)) != NULL) { fprintf(stderr, "ASYNC NOTIFY of '%s' received from backend pid %d\n", notify->relname, notify->be_pid); PQfreemem(notify); nnotifies++; } } fprintf(stderr, "Done.\n"); /* close the connection to the database and cleanup */ PQfinish(conn); return 0; } ]]>