9.1第三十一章
Chapter 31. libpq - C 函数库
libpq是PostgreSQL的C应用程序编程接口。libpq是一套允许客户端程序传递查询到PostgreSQL后台服务并接收结果集的库函数。
libpq 同时也是其他几个 PostgreSQL 应用接口后面的引擎，包括 C++，Perl，Python，Tcl 和 ECPG。 所以如果你使用这些软件包，libpq某些方面的特性会对你非常重要。特别是 31.13小节，31.14小节和 31.17 小节描述了使用 libpq 的任何应用程序的用户的行为都是可见的。
本章结尾的地方（31.20小节）包含了几个小的示例程序用以显示如何编写使用 libpq 的程序。也有很多完整的 libpq 示例程序随源码发布包发布，存在于 src/test/examples 目录中。
使用 libpq 的客户程序必须包含头文件 libpq-fe.h，且在连接的时候必须使用 libpq 库。
31.1 数据库连接控制函数
以下函数处理建立到 PostgreSQL 后台服务程序的连接。一个应用程序可以同时拥有多个后台连接。（原因之一是需要访问多个数据库。）每个连接都是用一个从函数 PQconnectdb()、PQconnectdbParams() 或 PQsetdbLogin() 获得的 PGconn 对象表示。注意，这些函数总是返回一个非空的对象指针，除非内存少到连个PGconn对象都分配不出来。在把查询发送给联接对象之前，可以调用PQstatus函数来检查一下联接是否成功。
| 警告 | |
|---|---|
|
在 Unix 平台中，fork 一个有打开了的 libpq 连接的进程会导致不可预料的结果，因为父进程和子进程共享共享相同的套接字和操作系统资源。 由于这个原因，不推荐这么使用，从子进程中执行一个 exec 来加载一个新的可执行文件会比较安全。 | |
注：在 Windows 中，如果一个单个的数据库连接被反复的打开和关闭，有一个方法可以提高性能。 在内部，libpq 分别调用 WSAStartup() 和 WSACleanup() 用于打开和关闭连接。WSAStartup() 增加一个 Windows 库的内部引用计数器的值而 WSACleanup() 则则减少它。当引用计数器为 1 的时候，再调用 WSACleanup() 将释放所有的资源，所有的 DLL 将被卸载。这是一个 非常昂贵的操作。为了避免这种操作，应用程序可以手工调用 WSAStartup() 因而当最后一个数据库连接被关闭后资源将不会被释放。
PQconnectdbParams
- 建立一个到数据库服务器的连接。
PGconn *PQconnectdbParams(const char **keywords, const char **values, int expand_dbname);
- 本函数使用以两个 NULL 终止的数组中的参数来建立一个新的数据库连接。第一个参数 keywords 是一个字符串数组，每一项都是一个关键字。第二个参数 values 给出对应关键字的值。不同于以下的 PQsetdbLogin，可以扩展参数集而不需要改变函数的声明，所以建议新的应用程序使用本函数（或者与其类似的非阻塞函数 PQconnectStartParams 和 PQconnectPoll）。
- 当 expand_dbname 不为零，dbname 关键字的值允许作为 conninfo 串来理解。参考以下的详细信息。
- 传入的数组可以为空以表示全部使用默认参数，或者可以包含一个或者多个参数。它们必须长短匹配。处理过程会一直处理直到碰到 keywords 数组中最后一个非空的元素。
- 当前支持的参数关键字为：
- host
- Name of host to connect to. If this begins with a slash, it specifies Unix-domain communication rather than TCP/IP communication; the value is the name of the directory in which the socket file is stored. The default behavior when host is not specified is to connect to a Unix-domain socket in /tmp (or whatever socket directory was specified when PostgreSQL was built). On machines without Unix-domain sockets, the default is to connect to localhost.
- hostaddr
- Numeric IP address of host to connect to. This should be in the standard IPv4 address format, e.g., 172.28.40.9. If your machine supports IPv6, you can also use those addresses. TCP/IP communication is always used when a nonempty string is specified for this parameter.
- Using hostaddr instead of host allows the application to avoid a host name look-up, which might be important in applications with time constraints. However, a host name is required for Kerberos, GSSAPI, or SSPI authentication methods, as well as for verify-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 network address. The connection attempt will fail if the authentication method requires a host name.
- If both host and hostaddr are specified, the value for hostaddr gives the server network address. The value for host is ignored unless the authentication method requires it, 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 server at network address hostaddr. Also, note that host rather than hostaddr is used to identify the connection in ~/.pgpass (see Section 31.14).
- Without either a host name or host address, libpq will connect using a local Unix-domain socket; or on machines without Unix-domain sockets, it will attempt to connect to localhost.
- port
- Port number to connect to at the server host, or socket file name extension for Unix-domain connections.
- dbname
- The database name. Defaults to be the same as the user name.
- user
- PostgreSQL user name to connect as. Defaults to be the same as the operating system name of the user running the application.
- password
- Password to be used if the server demands password authentication.
- connect_timeout
- Maximum wait for connection, in seconds (write as a decimal integer string). Zero or not specified means wait indefinitely. It is not recommended to use a timeout of less than 2 seconds.
- client_encoding
- This sets the client_encoding configuration parameter for this connection. In addition to the values accepted by the corresponding server option, you can use auto to determine the right encoding from the current locale in the client (LC_CTYPE environment variable on Unix systems).
- options
- Adds command-line options to send to the server at run-time. For example, setting this to -c geqo=off sets the session's value of the geqo parameter to off. For a detailed discussion of the available options, consult Chapter 18.
- application_name
- Specifies a value for the application_name configuration parameter.
- fallback_application_name
- Specifies a fallback value for the application_name configuration parameter. This value will be used if no value has been given for application_name via a connection parameter or the PGAPPNAME environment variable. Specifying a fallback name is useful in generic utility programs that wish to set a default application name but allow it to be overridden by the user.
- keepalives
- Controls whether client-side TCP keepalives are used. The default value is 1, meaning on, but you can change this to 0, meaning off, if keepalives are not wanted. This parameter is ignored for connections made via a Unix-domain socket.
- keepalives_idle
- Controls the number of seconds of inactivity after which TCP should send a keepalive message to the server. A value of zero uses the system default. This parameter is ignored for connections made via a Unix-domain socket, or if keepalives are disabled. It is only supported on systems where the TCP_KEEPIDLE or TCP_KEEPALIVE socket option is available, and on Windows; on other systems, it has no effect.
- keepalives_interval
- Controls the number of seconds after which a TCP keepalive message that is not acknowledged by the server should be retransmitted. A value of zero uses the system default. This parameter is ignored for connections made via a Unix-domain socket, or if keepalives are disabled. It is only supported on systems where the TCP_KEEPINTVL socket option is available, and on Windows; on other systems, it has no effect.
- keepalives_count
- Controls the number of TCP keepalives that can be lost before the client's connection to the server is considered dead. A value of zero uses the system default. This parameter is ignored for connections made via a Unix-domain socket, or if keepalives are disabled. It is only supported on systems where the TCP_KEEPINTVL socket option is available; on other systems, it has no effect.
- 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:
- 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 certificate authority (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 Section 31.17 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.
- 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
- This parameter specifies the operating-system user name of the server, for example requirepeer=postgres. When making a Unix-domain socket connection, if this parameter is set, the client checks at the beginning of the connection that the server process is running under the specified user name; if it is not, the connection is aborted with an error. This parameter can be used to provide server authentication similar to that available with SSL certificates on TCP/IP connections. (Note that if the Unix-domain socket is in /tmp or another publicly writable location, any user could start a server listening there. Use this parameter to ensure that you are connected to a server run by a trusted user.) This option is only supported on platforms for which the peer authentication method is implemented; see Section 19.3.7.
- krbsrvname
- Kerberos service name to use when authenticating with Kerberos 5 or GSSAPI. This must match the service name specified in the server configuration for Kerberos authentication to succeed. (See also Section 19.3.5 and Section 19.3.3.)
- gsslib
- GSS library to use for GSSAPI authentication. Only used on Windows. Set to gssapi to force libpq to use the GSSAPI library for authentication instead of the default SSPI.
- service
- Service name to use for additional parameters. It specifies a service name in pg_service.conf that holds additional connection parameters. This allows applications to specify only a service name so connection parameters can be centrally maintained. See Section 31.15.
- If any parameter is unspecified, then the corresponding environment variable (see Section 31.13) 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.
PQconnectdb
- 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.
PQsetdbLogin
- 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.
PQsetdb
- 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.
PQconnectStartParams PQconnectStart PQconnectPoll
- 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 call returns CONNECTION_BAD, then the connection procedure has failed; if the call returns 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.
PQconndefaults
- 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.
PQconninfoParse
- 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 function can be used to extract 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 and the function to return NULL; this indicates an out-of-memory condition.)
- 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.
PQfinish
- 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.
PQreset
- 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.
PQresetStart PQresetPoll
- 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.
PQpingParams
- PQpingParams reports the status of the server. It accepts connection parameters identical to those of PQconnectdbParams, described above. It is not, however, necessary to supply correct user name, password, or database name values to obtain the server status.
PGPing PQpingParams(const char **keywords, const char **values, int expand_dbname);
- The function returns one of the following values:
- PQPING_OK
- The server is running and appears to be accepting connections.
- PQPING_REJECT
- The server is running but is in a state that disallows connections (startup, shutdown, or crash recovery).
- PQPING_NO_RESPONSE
- The server could not be contacted. This might indicate that the server is not running, or that there is something wrong with the given connection parameters (for example, wrong port number), or that there is a network connectivity problem (for example, a firewall blocking the connection request).
- PQPING_NO_ATTEMPT
- No attempt was made to contact the server, because the supplied parameters were obviously incorrect or there was some client-side problem (for example, out of memory).
PQping
- PQping reports the status of the server. It accepts connection parameters identical to those of PQconnectdb, described above. It is not, however, necessary to supply correct user name, password, or database name values to obtain the server status.
PGPing PQping(const char *conninfo);
- The return values are the same as for PQpingParams.
31.2. Connection Status Functions
These functions can be used to interrogate the status of an existing database connection object.
Tip: 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
- Returns the database name of the connection.
char *PQdb(const PGconn *conn);
PQuser
- Returns the user name of the connection.
char *PQuser(const PGconn *conn);
PQpass
- Returns the password of the connection.
char *PQpass(const PGconn *conn);
PQhost
- Returns the server host name of the connection.
char *PQhost(const PGconn *conn);
PQport
- Returns the port of the connection.
char *PQport(const PGconn *conn);
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 backward compatibility.)
char *PQtty(const PGconn *conn);
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
- 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 returned.
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.
Caution 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
- 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
- Interrogates the frontend/backend protocol being used.
int PQprotocolVersion(const PGconn *conn);
- Applications might wish to use this function to determine whether certain features are supported. Currently, the possible values are 2 (2.0 protocol), 3 (3.0 protocol), or zero (connection bad). The protocol version 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
- Returns an integer representing the backend version.
int PQserverVersion(const PGconn *conn);
- Applications might use this function 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
- 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 consist of 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.
PQsocket
- 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);
PQbackendPID
- Returns the process ID (PID) of the backend 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!
PQconnectionNeedsPassword
- 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.
PQconnectionUsedPassword
- 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.
PQgetssl
- 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.
31.3. Command Execution Functions
Once a connection to a database server has been successfully established, the functions described here are used to perform SQL queries and commands.
31.3.1. Main Functions
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.
The command string can include multiple SQL commands (separated by semicolons). 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
- 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.
Tip: 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
- 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 PREPARE statements. Also, although there is no libpq function for deleting a prepared statement, the SQL DEALLOCATE statement can be used for that purpose.
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
- 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
- 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 PGresult 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
- 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.
- PGRES_COPY_BOTH
- Copy In/Out (to and from server) data transfer started. This is currently used only for streaming replication.
- 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 Section 31.11).
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
- 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.
PQresultErrorField
- 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.
- 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 Appendix A. 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.
PQclear
- 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.
31.3.2. Retrieving Query Result Information
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
- 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
- Returns the number of columns (fields) in each row of the query result.
int PQnfields(const PGresult *res);
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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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.
31.3.3. Retrieving Other Result Information
These functions are used to extract other information from PGresult objects.
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
- 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
- Returns the OID of the inserted row, if the SQL command was an INSERT that inserted exactly one row into a table that has OIDs, or a EXECUTE of a prepared query containing a suitable INSERT statement. Otherwise, this function returns 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
- This function is deprecated in favor of PQoidValue and is not thread-safe. It returns a string with the OID of the inserted row, while PQoidValue returns the OID value.
char *PQoidStatus(const PGresult *res);
31.3.4. Escaping Strings for Inclusion in SQL Commands
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.
Tip: 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
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.
Tip: As with string literals, to prevent SQL injection attacks, SQL identifiers must be escaped when they are received from an untrustworthy source.
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 is an older, deprecated version of PQescapeStringConn.
size_t PQescapeString (char *to, const char *from, size_t length);
- The only difference from PQescapeStringConn is that PQescapeString does not take PGconn 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 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
- 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 when used as part of a bytea literal in an SQL statement. PQescapeByteaConn escapes bytes using either hex encoding or backslash escaping. See Section 8.4 for more information.
- 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 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, PQescapeBytea can only be used safely in client programs that use a single PostgreSQL connection at a time (in this case it can find out what it needs to know "behind the scenes"). It might give the wrong results if used in programs that use multiple database connections (use PQescapeByteaConn in such cases).
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.
31.4. Asynchronous Command Processing
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
- 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
- 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
- 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
- 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
- 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
- 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
- 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.
Note: Even when PQresultStatus indicates a fatal error, PQgetResult should be called until it returns a null pointer to allow libpq to process the error information completely.
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
- 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
- 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 Section 31.7).
A client that uses PQsendQuery/PQgetResult can also attempt to cancel a command that is still being processed by the server; see Section 31.5. 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
- 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
- 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
- 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.
31.5. Canceling Queries in Progress
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
- 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 PGcancel 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
- Frees a data structure created by PQgetCancel.
void PQfreeCancel(PGcancel *cancel);
- PQfreeCancel frees a data object previously created by PQgetCancel.
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 explanatory error message. 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 is a deprecated variant of PQcancel.
int PQrequestCancel(PGconn *conn);
- Requests that the server abandon processing of the current command. 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.
31.6. The Fast-Path Interface
PostgreSQL provides a fast-path interface to send simple function calls to the server.
Tip: 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 PQfn 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.
31.7. Asynchronous Notification
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.
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.)
Example 31-2 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.
31.8. Functions Associated with the COPY Command
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
- Returns the number of columns (fields) to be copied.
PQbinaryTuples
- 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 COPY for more information.
PQfformat
- 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.)
Note: These additional data values are only available when using protocol 3.0. When using protocol 2.0, all these functions will return 0.
31.8.1. Functions for Sending COPY Data
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
- 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 COPY for details.
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.
31.8.2. Functions for Receiving COPY Data
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
- 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.
31.8.3. Obsolete Functions for COPY
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
- 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
- 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
- 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.
Note: 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
- 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
- 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.
31.9. Control Functions
These functions control miscellaneous details of libpq's behavior.
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
- 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
- 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
- Enables tracing of the client/server communication to a debugging file stream.
void PQtrace(PGconn *conn, FILE *stream);
Note: 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
- Disables tracing started by PQtrace.
void PQuntrace(PGconn *conn);
31.10. Miscellaneous Functions
As always, there are some functions that just don't fit anywhere.
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
- 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
- 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
- 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
- Fires a PGEVT_RESULTCREATE event (see Section 31.12) 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
- 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
- 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
- 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
- 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.
PQlibVersion
- Return the version of libpq that is being used.
int PQlibVersion(void);
- The result of this function can be used to determine, at run time, if specific functionality is available in the currently loaded version of libpq. The function can be used, for example, to determine which connection options are available for PQconnectdb or if the hex bytea output added in PostgreSQL 9.0 is supported.
- The number is formed by converting the major, minor, and revision numbers into two-decimal-digit numbers and appending them together. For example, version 9.1 will be returned as 90100, and version 9.1.2 will be returned as 90102 (leading zeroes are not shown).
Note: This function appeared in PostgreSQL version 9.1, so it cannot be used to detect required functionality in earlier versions, since linking to it will create a link dependency on version 9.1.
31.11. Notice Processing
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 sets or examines the current notice receiver for a connection object. Similarly, PQsetNoticeProcessor 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.
31.12. Event System
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.
31.12.1. Event Types
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.
31.12.2. Event Callback Procedure
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.
Caution 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.
31.12.3. Event Support Functions
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
- 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
- Returns the connection conn's instanceData associated with procedure proc, or NULL if there is none.
void *PQinstanceData(const PGconn *conn, PGEventProc proc);
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
- Returns the result's instanceData associated with proc, or NULL if there is none.
void *PQresultInstanceData(const PGresult *res, PGEventProc proc);
31.12.4. Event Example
Here is a skeleton example of managing private data associated with libpq connections and results.
/* required header for libpq events (note: includes libpq-fe.h) */
#include <libpq-events.h>
/* 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 */
}
31.13. Environment Variables
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 behaves the same as the host connection parameter.
- PGHOSTADDR behaves the same as the hostaddr connection parameter. This can be set instead of or in addition to PGHOST to avoid DNS lookup overhead.
- PGPORT behaves the same as the port connection parameter.
- PGDATABASE behaves the same as the dbname connection parameter.
- PGUSER behaves the same as the user connection parameter.
- PGPASSWORD behaves the same as the password 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 Section 31.14).
- PGPASSFILE specifies the name of the password file to use for lookups. If not set, it defaults to ~/.pgpass (see Section 31.14).
- PGSERVICE behaves the same as the service connection parameter.
- PGSERVICEFILE specifies the name of the per-user connection service file. If not set, it defaults to ~/.pg_service.conf (see Section 31.15).
- 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 behaves the same as the options connection parameter.
- PGAPPNAME behaves the same as the application_name connection parameter.
- PGSSLMODE behaves the same as the sslmode connection parameter.
- PGREQUIRESSL behaves the same as the requiressl connection parameter.
- PGSSLCERT behaves the same as the sslcert connection parameter.
- PGSSLKEY behaves the same as the sslkey connection parameter.
- PGSSLROOTCERT behaves the same as the sslrootcert connection parameter.
- PGSSLCRL behaves the same as the sslcrl connection parameter.
- PGREQUIREPEER behaves the same as the requirepeer connection parameter.
- PGKRBSRVNAME behaves the same as the krbsrvname connection parameter.
- PGGSSLIB behaves the same as the gsslib connection parameter.
- PGCONNECT_TIMEOUT behaves the same as the connect_timeout connection parameter.
- PGCLIENTENCODING behaves the same as the client_encoding connection parameter.
The following environment variables can be used to specify default behavior for each PostgreSQL session. (See also the ALTER ROLE and ALTER DATABASE commands for ways to set default behavior on a per-user or per-database basis.)
- PGDATESTYLE sets the default style of date/time representation. (Equivalent to SET datestyle TO ....)
- PGTZ sets the default time zone. (Equivalent to SET timezone TO ....)
- PGGEQO sets the default mode for the genetic query optimizer. (Equivalent to SET geqo TO ....)
Refer to the SQL command SET for information on correct values for these environment variables.
The following environment variables determine internal behavior of libpq; they override compiled-in defaults.
- PGSYSCONFDIR sets the directory containing the pg_service.conf file and in a future version possibly other system-wide configuration files.
- PGLOCALEDIR sets the directory containing the locale files for message internationalization.
31.14. The Password File
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. The database field is of limited usefulness because users have the same password for all databases in the same cluster.
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.
31.15. The Connection Service File
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 Section 31.1 for a list. For example:
# comment [mydb] host=somehost port=5433 user=admin
An example file is provided at share/pg_service.conf.sample.
31.16. LDAP Lookup of Connection Parameters
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 Section 31.15). 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=*)
31.17. SSL Support
PostgreSQL has native support for using SSL connections to encrypt client/server communications for increased security. See Section 17.9 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. 31.17.1. Client Verification of Server Certificates
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. 31.17.2. Client Certificates
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.
31.17.3. Protection Provided in Different Modes
The different values for the sslmode parameter provide different levels of protection. SSL can provide protection against three types of attacks:
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. Table 31-1 illustrates the risks the different sslmode values protect against, and what statement they make about security and overhead.
Table 31-1. SSL Mode Descriptions
| sslmode | Eavesdropping protection | MITM protection | Statement |
| disable | No | No | 我不关心安全问题，也不想为加密而付出一定的开销。 |
| allow | Maybe | No | 我不关心安全问题，但如果服务器坚持要加密，我也愿意为加密而付出开销。 |
| prefer | Maybe | No | 我不关心安全问题，但如果服务器支持，我希望进行加密且愿意付出一定的开销。 |
| require | Yes | No | 我希望进行加密，而且我接受开销。而且我相信网络能够保证我是连接到了我希望连接到的服务器。 |
| verify-ca | Yes | 依赖于CA策略 | 我希望进行加密，而且我接受开销。而且我希望确保我能连接到信任的服务器。 |
| 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 backward compatibility, and is not recommended in secure deployments.
31.17.4. SSL Client File Usage
Table 31-2 summarizes the files that are relevant to the SSL setup on the client.
Table 31-2. Libpq/Client SSL File Usage
| File | Contents | Effect |
| ~/.postgresql/postgresql.crt | 客户端证书 | 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 |
31.17.5. SSL Library Initialization
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 http://h71000.www7.hp.com/doc/83final/BA554_90007/ch04.html for details on the SSL API.
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
- 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.
31.18. Behavior in Threaded Programs
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
- 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.
31.19. 生成 libpq 程序
要生成（例如：编译，连接）一个使用 libpq 的程序，你需要按照以下步骤去做：
- 包含 libpq-fe.h 头文件
#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_config 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.
31.20. 示例程序
这些示例以及其他的都可以在源码发布包的 src/test/examples 中找到。
Example 31-1. libpq 示例程序1
/*
* testlibpq.c
*
* Test the C version of libpq, the PostgreSQL frontend library.
*/
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>
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;
}
Example 31-2. libpq 示例程序2
/*
* testlibpq2.c
* Test of the asynchronous notification interface
*
* Start this program, then from psql in another window do
* NOTIFY TBL2;
* Repeat four times to get this program to exit.
*
* Or, if you want to get fancy, try this:
* populate a database with the following commands
* (provided in src/test/examples/testlibpq2.sql):
*
* CREATE TABLE TBL1 (i int4);
*
* CREATE TABLE TBL2 (i int4);
*
* CREATE RULE r1 AS ON INSERT TO TBL1 DO
* (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
*
* and do this four times:
*
* INSERT INTO TBL1 VALUES (10);
*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <errno.h>
#include <sys/time.h>
#include <libpq-fe.h>
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;
}
示例 31-3. libpq 示例程序3
/*
* testlibpq3.c
* Test out-of-line parameters and binary I/O.
*
* Before running this, populate a database with the following commands
* (provided in src/test/examples/testlibpq3.sql):
*
* CREATE TABLE test1 (i int4, t text, b bytea);
*
* INSERT INTO test1 values (1, 'joes place', '\\000\\001\\002\\003\\004');
* INSERT INTO test1 values (2, 'ho there', '\\004\\003\\002\\001\\000');
*
* The expected output is:
*
* tuple 0: got
* i = (4 bytes) 1
* t = (11 bytes) 'joe's place'
* b = (5 bytes) \000\001\002\003\004
*
* tuple 0: got
* i = (4 bytes) 2
* t = (8 bytes) 'ho there'
* b = (5 bytes) \004\003\002\001\000
*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include <libpq-fe.h>
/* for ntohl/htonl */
#include <netinet/in.h>
#include <arpa/inet.h>
static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
/*
* This function prints a query result that is a binary-format fetch from
* a table defined as in the comment above. We split it out because the
* main() function uses it twice.
*/
static void
show_binary_results(PGresult *res)
{
int i,
j;
int i_fnum,
t_fnum,
b_fnum;
/* Use PQfnumber to avoid assumptions about field order in result */
i_fnum = PQfnumber(res, "i");
t_fnum = PQfnumber(res, "t");
b_fnum = PQfnumber(res, "b");
for (i = 0; i < PQntuples(res); i++)
{
char *iptr;
char *tptr;
char *bptr;
int blen;
int ival;
/* Get the field values (we ignore possibility they are null!) */
iptr = PQgetvalue(res, i, i_fnum);
tptr = PQgetvalue(res, i, t_fnum);
bptr = PQgetvalue(res, i, b_fnum);
/*
* The binary representation of INT4 is in network byte order, which
* we'd better coerce to the local byte order.
*/
ival = ntohl(*((uint32_t *) iptr));
/*
* The binary representation of TEXT is, well, text, and since libpq
* was nice enough to append a zero byte to it, it'll work just fine
* as a C string.
*
* The binary representation of BYTEA is a bunch of bytes, which could
* include embedded nulls so we have to pay attention to field length.
*/
blen = PQgetlength(res, i, b_fnum);
printf("tuple %d: got\n", i);
printf(" i = (%d bytes) %d\n",
PQgetlength(res, i, i_fnum), ival);
printf(" t = (%d bytes) '%s'\n",
PQgetlength(res, i, t_fnum), tptr);
printf(" b = (%d bytes) ", blen);
for (j = 0; j < blen; j++)
printf("\\%03o", bptr[j]);
printf("\n\n");
}
}
int
main(int argc, char **argv)
{
const char *conninfo;
PGconn *conn;
PGresult *res;
const char *paramValues[1];
int paramLengths[1];
int paramFormats[1];
uint32_t binaryIntVal;
/*
* 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);
}
/*
* The point of this program is to illustrate use of PQexecParams() with
* out-of-line parameters, as well as binary transmission of data.
*
* This first example transmits the parameters as text, but receives the
* results in binary format. By using out-of-line parameters we can
* avoid a lot of tedious mucking about with quoting and escaping, even
* though the data is text. Notice how we don't have to do anything
* special with the quote mark in the parameter value.
*/
/* Here is our out-of-line parameter value */
paramValues[0] = "joe's place";
res = PQexecParams(conn,
"SELECT * FROM test1 WHERE t = $1",
1, /* one param */
NULL, /* let the backend deduce param type */
paramValues,
NULL, /* don't need param lengths since text */
NULL, /* default to all text params */
1); /* ask for binary results */
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
show_binary_results(res);
PQclear(res);
/*
* In this second example we transmit an integer parameter in binary
* form, and again retrieve the results in binary form.
*
* Although we tell PQexecParams we are letting the backend deduce
* parameter type, we really force the decision by casting the parameter
* symbol in the query text. This is a good safety measure when sending
* binary parameters.
*/
/* Convert integer value "2" to network byte order */
binaryIntVal = htonl((uint32_t) 2);
/* Set up parameter arrays for PQexecParams */
paramValues[0] = (char *) &binaryIntVal;
paramLengths[0] = sizeof(binaryIntVal);
paramFormats[0] = 1; /* binary */
res = PQexecParams(conn,
"SELECT * FROM test1 WHERE i = $1::int4",
1, /* one param */
NULL, /* let the backend deduce param type */
paramValues,
paramLengths,
paramFormats,
1); /* ask for binary results */
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
show_binary_results(res);
PQclear(res);
/* close the connection to the database and cleanup */
PQfinish(conn);
return 0;
}
