X-Git-Url: https://granicus.if.org/sourcecode?a=blobdiff_plain;f=doc%2Fsrc%2Fsgml%2Flibpq.sgml;h=2f1a248ca768c42360910e2fb320a67bd8e07f43;hb=3405f2b92532cb9559aed1316b3df2262aaadaef;hp=fdcd4eb9c1081642249d728b6091ab47cddf78c8;hpb=efe3de07e9111b7ddb5a67d807aa00dcdc0a252c;p=postgresql diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml index fdcd4eb9c1..2f1a248ca7 100644 --- a/doc/src/sgml/libpq.sgml +++ b/doc/src/sgml/libpq.sgml @@ -1,50 +1,50 @@ - + - - <application>libpq</application> - C Library + + <application>libpq</application> - C Library - - libpq - - - - C - - - - libpq is the C - application programmer's interface to PostgreSQL. - libpq is a set of library functions that allow - client programs to pass queries to the PostgreSQL - backend server and to receive the results of these queries. - - - - libpq is also the underlying engine for several - other PostgreSQL application interfaces, including - those written for C++, Perl, Python, Tcl and ECPG. - So some aspects of libpq's behavior will be - important to you if you use one of those packages. In particular, - , - and - - describe behavior that is visible to the user of any application - that uses libpq. - + + libpq + - - Some short programs are included at the end of this chapter () to show how - to write programs that use libpq. There are also several - complete examples of libpq applications in the - directory src/test/examples in the source code distribution. - + + C + - - Client programs that use libpq must - include the header file - libpq-fe.hlibpq-fe.h - and must link with the libpq library. - + + libpq is the C + application programmer's interface to PostgreSQL. + libpq is a set of library functions that allow + client programs to pass queries to the PostgreSQL + backend server and to receive the results of these queries. + + + + libpq is also the underlying engine for several + other PostgreSQL application interfaces, including + those written for C++, Perl, Python, Tcl and ECPG. + So some aspects of libpq's behavior will be + important to you if you use one of those packages. In particular, + , + and + + describe behavior that is visible to the user of any application + that uses libpq. + + + + Some short programs are included at the end of this chapter () to show how + to write programs that use libpq. There are also several + complete examples of libpq applications in the + directory src/test/examples in the source code distribution. + + + + Client programs that use libpq must + include the header file + libpq-fe.hlibpq-fe.h + and must link with the libpq library. + Database Connection Control Functions @@ -70,919 +70,1033 @@ 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. Unlike PQsetdbLogin below, - the parameter set can be extended without changing the function signature, - so use of this function (or its nonblocking analogues PQconnectStart - and PQconnectPoll) is preferred for new application programming. - - - 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 \\. - + + PGconn *PQconnectdb(const char *conninfo); + + - - The currently recognized parameter key words are: + + This function opens a new database connection using the parameters taken + from the string conninfo. Unlike PQsetdbLogin below, + the parameter set can be extended without changing the function signature, + so use of this function (or its nonblocking analogues PQconnectStart + and PQconnectPoll) is preferred for new application programming. + + + + 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: + + + + host + + + Name of host to connect to.host name + If this begins with a slash, it specifies Unix-domain + communication rather than TCP/IP communication; the value is the + name of the directory in which the socket file is stored. The + default behavior when host is not specified + is to connect to a Unix-domain + socketUnix domain socket in + /tmp (or whatever socket directory was specified + when PostgreSQL was built). On machines without + Unix-domain sockets, the default is to connect to localhost. + + + + + + hostaddr + + + Numeric IP address of host to connect to. This should be in the + standard IPv4 address format, e.g., 172.28.40.9. If + your machine supports IPv6, you can also use those addresses. + TCP/IP communication is + always used when a nonempty string is specified for this parameter. + + + + Using hostaddr instead of host allows the + application to avoid a host name look-up, which might be important in + applications with time constraints. However, Kerberos and GSSAPI authentication + requires the host name. The following therefore applies: If + host is specified without hostaddr, a host name + lookup occurs. If hostaddr is specified without + host, the value for hostaddr gives the remote + address. When Kerberos is used, a reverse name query occurs to obtain + the host name for Kerberos. If both + host and hostaddr are specified, the value for + hostaddr gives the remote address; the value for + host is ignored, unless Kerberos is used, in which case that + value is used for Kerberos authentication. (Note that authentication is + likely to fail if libpq is passed a host name + that is not the name of the machine at hostaddr.) Also, + host rather than hostaddr is used to identify + the connection in ~/.pgpass (see + ). + + + + Without either a host name or host address, + libpq will connect using a + local Unix-domain socket; or on machines without Unix-domain + sockets, it will attempt to connect to localhost. + + + + + + port + + + Port number to connect to at the server host, or socket file + name extension for Unix-domain + connections.port + + + + + + dbname + + + The database name. Defaults to be the same as the user name. + + + + + + user + + + PostgreSQL user name to connect as. + Defaults to be the same as the operating system name of the user + running the application. + + + + + + password + + + Password to be used if the server demands password authentication. + + + + + + connect_timeout + + + Maximum wait for connection, in seconds (write as a decimal integer + string). Zero or not specified means wait indefinitely. It is not + recommended to use a timeout of less than 2 seconds. + + + + + + options + + + Command-line options to be sent to the server. + + + + + + tty + + + Ignored (formerly, this specified where to send server debug output). + + + + + + sslmode + + + This option determines whether or with what priority an + SSL connection will be negotiated with the + server. There are four modes: disable will attempt + only an unencrypted SSL connection; + allow will negotiate, trying first a + non-SSL connection, then if that fails, trying an + SSL connection; prefer (the default) + will negotiate, trying first an SSL connection, + then if that fails, trying a regular non-SSL + connection; require will try only an + SSL connection. + + + + If PostgreSQL is compiled without SSL support, + using option require will cause an error, while + options allow and prefer will be + accepted but libpq will not in fact attempt + an SSL + connection.SSLwith libpq + + + + + + requiressl + + + This option is deprecated in favor of the sslmode + setting. + + + + If set to 1, an SSL connection to the server + is required (this is equivalent to sslmode + require). libpq will then refuse + to connect if the server does not accept an + SSL connection. If set to 0 (default), + libpq will negotiate the connection type with + the server (equivalent to sslmode + prefer). This option is only available if + PostgreSQL is compiled with SSL support. + + + + + + krbsrvname + + + Kerberos service name to use when authenticating with Kerberos 5 + or GSSAPI. + This must match the service name specified in the server + configuration for Kerberos authentication to succeed. (See also + and .) + + + + + + gsslib + + + GSS library to use for GSSAPI authentication. Only used on Windows. + Set to gssapi to force libpq to use the GSSAPI + library for authentication instead of the default SSPI. + + + + + + service + + + Service name to use for additional parameters. It specifies a service + name in pg_service.conf that holds additional connection parameters. + This allows applications to specify only a service name so connection parameters + can be centrally maintained. See . + + + + + + If any parameter is unspecified, then the corresponding + environment variable (see ) + is checked. If the environment variable is not set either, + then the indicated built-in defaults are used. + + + - - host + PQsetdbLoginPQsetdbLogin - - Name of host to connect to.host name - If this begins with a slash, it specifies Unix-domain - communication rather than TCP/IP communication; the value is the - name of the directory in which the socket file is stored. The - default behavior when host is not specified - is to connect to a Unix-domain - socketUnix domain socket in - /tmp (or whatever socket directory was specified - when PostgreSQL was built). On machines without - Unix-domain sockets, the default is to connect to localhost. - + + 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. + - hostaddr + PQsetdbPQsetdb - - 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 may be important in - applications with time constraints. However, Kerberos authentication - requires the host name. The following therefore applies: If - host is specified without hostaddr, a host name - lookup occurs. If hostaddr is specified without - host, the value for hostaddr gives the remote - address. When Kerberos is used, a reverse name query occurs to obtain - the host name for Kerberos. If both - host and hostaddr are specified, the value for - hostaddr gives the remote address; the value for - host is ignored, unless Kerberos is used, in which case that - value is used for Kerberos authentication. (Note that authentication is - likely to fail if libpq is passed a host name - that is not the name of the machine at hostaddr.) Also, - host rather than hostaddr is used to identify - the connection in ~/.pgpass (see - ). + + Makes a new connection to the database server. + +PGconn *PQsetdb(char *pghost, + char *pgport, + char *pgoptions, + char *pgtty, + char *dbName); + + - 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. + 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. - port + PQconnectStartPQconnectStart + PQconnectPollPQconnectPoll - - Port number to connect to at the server host, or socket file - name extension for Unix-domain - connections.port - + + nonblocking connection + Make a connection to the database server in a nonblocking manner. + + + PGconn *PQconnectStart(const char *conninfo); + + + + PostgresPollingStatusType PQconnectPoll(PGconn *conn); + + + + + These two 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 + PQconnectdb, and so the application can manage this + operation in parallel with other activities. + + + + The database connection is made using the parameters taken from the string + conninfo, passed to PQconnectStart. This string is in + the same format as described above for PQconnectdb. + + + Neither 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 PQconnectdb 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. + + + + + + + To begin a nonblocking connection request, call conn = PQconnectStart("connection_info_string"). + If conn is null, then libpq has been unable to allocate a new PGconn + structure. Otherwise, a valid PGconn pointer is returned (though not yet + representing a valid connection to the database). On return from + PQconnectStart, call status = PQstatus(conn). If status equals + CONNECTION_BAD, PQconnectStart has failed. + + + + If PQconnectStart succeeds, the next stage is to poll + libpq so that it can proceed with the connection sequence. + Use PQsocket(conn) to obtain the descriptor of the + socket underlying the database connection. + Loop thus: If PQconnectPoll(conn) last returned + PGRES_POLLING_READING, wait until the socket is ready to + read (as indicated by select(), poll(), or + similar system function). + Then call PQconnectPoll(conn) again. + Conversely, if PQconnectPoll(conn) last returned + PGRES_POLLING_WRITING, wait until the socket is ready + to write, then call PQconnectPoll(conn) again. + If you have yet to call + PQconnectPoll, i.e., just after the call to + PQconnectStart, behave as if it last returned + PGRES_POLLING_WRITING. Continue this loop until + PQconnectPoll(conn) returns + PGRES_POLLING_FAILED, indicating the connection procedure + has failed, or PGRES_POLLING_OK, indicating the connection + has been successfully made. + + + + At any time during connection, the status of the connection can be + checked by calling PQstatus. If this gives CONNECTION_BAD, then the + connection procedure has failed; if it gives CONNECTION_OK, then the + connection is ready. Both of these states are equally detectable + from the return value of PQconnectPoll, described above. Other states might also occur + during (and only during) an asynchronous connection procedure. These + indicate the current stage of the connection procedure and might be useful + to provide feedback to the user for example. These statuses are: + + + + CONNECTION_STARTED + + + Waiting for connection to be made. + + + + + + CONNECTION_MADE + + + Connection OK; waiting to send. + + + + + + CONNECTION_AWAITING_RESPONSE + + + Waiting for a response from the server. + + + + + + CONNECTION_AUTH_OK + + + Received authentication; waiting for backend start-up to finish. + + + + + + CONNECTION_SSL_STARTUP + + + Negotiating SSL encryption. + + + + + + CONNECTION_SETENV + + + Negotiating environment-driven parameter settings. + + + + + + Note that, although these constants will remain (in order to maintain + compatibility), an application should never rely upon these occurring in a + particular order, or at all, or on the status always being one of these + documented values. An application might do something like this: + +switch(PQstatus(conn)) +{ + case CONNECTION_STARTED: + feedback = "Connecting..."; + break; + + case CONNECTION_MADE: + feedback = "Connected to server..."; + break; +. +. +. + default: + feedback = "Connecting..."; +} + + + + + The connect_timeout connection parameter is ignored + when using PQconnectPoll; it is the application's + responsibility to decide whether an excessive amount of time has elapsed. + Otherwise, PQconnectStart followed by a + PQconnectPoll loop is equivalent to + PQconnectdb. + + + + Note that if PQconnectStart returns a non-null pointer, you must call + PQfinish when you are finished with it, in order to dispose of + the structure and any associated memory blocks. This must be done even if + the connection attempt fails or is abandoned. + - dbname + PQconndefaultsPQconndefaults - - The database name. Defaults to be the same as the user name. - + + 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; /* Character to display for 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. + + - user + PQfinishPQfinish - - PostgreSQL user name to connect as. - Defaults to be the same as the operating system name of the user - running the application. - + + 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. + - password + PQresetPQreset - - Password to be used if the server demands password authentication. - + + 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. + - connect_timeout + PQresetStartPQresetStart + PQresetPollPQresetPoll - - 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. - + + 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 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. + + + + + + + Connection Status Functions + + + These functions can be used to interrogate the status + of an existing database connection object. + + + + + libpq-fe.h + libpq-int.h + libpq application programmers should be careful to + maintain the PGconn abstraction. Use the accessor + functions described below to get at the contents of PGconn. + Reference to internal PGconn fields using + libpq-int.h is not recommended because they are subject to change + in the future. + + + + + The following functions return parameter values established at connection. + These values are fixed for the life of the PGconn object. + + - options + + PQdb + + PQdb + + + - Command-line options to be sent to the server. + Returns the database name of the connection. + + char *PQdb(const PGconn *conn); + - tty + + PQuser + + PQuser + + + - - Ignored (formerly, this specified where to send server debug output). - + + Returns the user name of the connection. + + char *PQuser(const PGconn *conn); + + - sslmode + + PQpass + + PQpass + + + - This option determines whether or with what priority an - SSL connection will be negotiated with the - server. There are four modes: disable will attempt - only an unencrypted SSL connection; - allow will negotiate, trying first a - non-SSL connection, then if that fails, trying an - SSL connection; prefer (the default) - will negotiate, trying first an SSL connection, - then if that fails, trying a regular non-SSL - connection; require will try only an - SSL connection. + Returns the password of the connection. + + char *PQpass(const PGconn *conn); + + + + + + + PQhost + + PQhost + + + - If PostgreSQL is compiled without SSL support, - using option require will cause an error, while - options allow and prefer will be - accepted but libpq will not in fact attempt - an SSL - connection.SSLwith libpq + Returns the server host name of the connection. + + char *PQhost(const PGconn *conn); + - requiressl + + PQport + + PQport + + + - This option is deprecated in favor of the sslmode - setting. - + Returns the port of the connection. - - 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. + + char *PQport(const PGconn *conn); + - krbsrvname + + PQtty + + PQtty + + + - Kerberos service name to use when authenticating with Kerberos 5. - This must match the service name specified in the server - configuration for Kerberos authentication to succeed. (See also - .) + Returns the debug TTY of the connection. + (This is obsolete, since the server no longer pays attention + to the TTY setting, but the function remains + for backwards compatibility.) + + + char *PQtty(const PGconn *conn); + - service + + PQoptions + + PQoptions + + + - - 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 . - + + Returns the command-line options passed in the connection request. + + char *PQoptions(const PGconn *conn); + + + - If any parameter is unspecified, then the corresponding - environment variable (see ) - is checked. If the environment variable is not set either, - then the indicated built-in defaults are used. - - - + + The following functions return status data that can change as operations + are executed on the PGconn object. - - PQsetdbLoginPQsetdbLogin - - - Makes a new connection to the database server. - -PGconn *PQsetdbLogin(const char *pghost, - const char *pgport, - const char *pgoptions, - const char *pgtty, - const char *dbName, - const char *login, - const char *pwd); - - + + + + PQstatus + + PQstatus + + - - 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. - - - + + + Returns the status of the connection. + + ConnStatusType PQstatus(const PGconn *conn); + + - - PQsetdbPQsetdb - - - Makes a new connection to the database server. - -PGconn *PQsetdb(char *pghost, - char *pgport, - char *pgoptions, - char *pgtty, - char *dbName); - - + + 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. + - - 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. - - - + + See the entry for PQconnectStart and PQconnectPoll with regards + to other status codes + that might be seen. + + + - - PQconnectStartPQconnectStart - PQconnectPollPQconnectPoll - - - nonblocking connection - Make a connection to the database server in a nonblocking manner. - -PGconn *PQconnectStart(const char *conninfo); - - -PostgresPollingStatusType PQconnectPoll(PGconn *conn); - - - - These two 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 - PQconnectdb, and so the application can manage this - operation in parallel with other activities. - - - The database connection is made using the parameters taken from the string - conninfo, passed to PQconnectStart. This string is in - the same format as described above for PQconnectdb. - - - Neither 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 PQconnectdb above for details. - - + + + PQtransactionStatus + + PQtransactionStatus + + - - - If you call PQtrace, ensure that the stream object - into which you trace will not block. - - + + + 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. + - - - You ensure that the socket is in the appropriate state - before calling PQconnectPoll, as described 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 may 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 may be - checked by calling PQstatus. If this gives CONNECTION_BAD, then the - connection procedure has failed; if it gives CONNECTION_OK, then the - connection is ready. Both of these states are equally detectable - from the return value of PQconnectPoll, described above. Other states may also occur - during (and only during) an asynchronous connection procedure. These - indicate the current stage of the connection procedure and may 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. + 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. - - - - - Note that, although these constants will remain (in order to maintain - compatibility), an application should never rely upon these occurring in a - particular order, or at all, or on the status always being one of these - documented values. An application might do something like this: - -switch(PQstatus(conn)) -{ - case CONNECTION_STARTED: - feedback = "Connecting..."; - break; - - case CONNECTION_MADE: - feedback = "Connected to server..."; - break; -. -. -. - default: - feedback = "Connecting..."; -} - - - - - The connect_timeout connection parameter is ignored - when using PQconnectPoll; it is the application's - responsibility to decide whether an excessive amount of time has elapsed. - Otherwise, PQconnectStart followed by a - PQconnectPoll loop is equivalent to - PQconnectdb. - - - - Note that if PQconnectStart returns a non-null pointer, you must call - PQfinish when you are finished with it, in order to dispose of - the structure and any associated memory blocks. This must be done even if - the connection attempt fails or is abandoned. - - - - - - PQconndefaultsPQconndefaults - - - Returns the default connection options. - -PQconninfoOption *PQconndefaults(void); - -typedef struct -{ - char *keyword; /* The keyword of the option */ - char *envvar; /* Fallback environment variable name */ - char *compiled; /* Fallback compiled in default value */ - char *val; /* Option's current value, or NULL */ - char *label; /* Label for field in connect dialog */ - char *dispchar; /* Character to display for 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 may 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. - - - - - - - PQfinishPQfinish - - - Closes the connection to the server. Also frees - memory used by the PGconn object. - -void PQfinish(PGconn *conn); - - - - - Note that even if the server connection attempt fails (as - indicated by PQstatus), the application should call PQfinish - to free the memory used by the PGconn object. - The PGconn pointer must not be used again after - PQfinish has been called. - - - + + + - - PQresetPQreset - - - Resets the communication channel to the server. - -void PQreset(PGconn *conn); - - - - - This function will close the connection - to the server and attempt to reestablish a new - connection to the same server, using all the same - parameters previously used. This may be useful for - error recovery if a working connection is lost. - - - + + + PQparameterStatus + + PQparameterStatus + + - - PQresetStartPQresetStart - PQresetPollPQresetPoll - - - Reset the communication channel to the server, in a nonblocking manner. - -int PQresetStart(PGconn *conn); - - -PostgresPollingStatusType PQresetPoll(PGconn *conn); - - - - - These functions will close the connection to the server and attempt to - reestablish a new connection to the same server, using all the same - parameters previously used. This may 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 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. - - - + + + Looks up a current parameter setting of the server. - - - + + const char *PQparameterStatus(const PGconn *conn, const char *paramName); + - -Connection Status Functions + 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. + - - These functions may be used to interrogate the status - of an existing database connection object. - + + Parameters reported as of the current release include + server_version, + server_encoding, + client_encoding, + is_superuser, + session_authorization, + DateStyle, + 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.) + Note that + server_version, + server_encoding and + integer_datetimes + cannot change after startup. + - - -libpq-fe.h -libpq-int.h -libpq application programmers should be careful to -maintain the PGconn abstraction. Use the accessor -functions described below to get at the contents of PGconn. -Reference to internal PGconn fields using -libpq-int.h is not recommended because they are subject to change -in the future. - - - - -The following functions return parameter values established at connection. -These values are fixed for the life of the PGconn object. - - - -PQdbPQdb - - - Returns the database name of the connection. - -char *PQdb(const PGconn *conn); - - - - - - -PQuserPQuser - - - Returns the user name of the connection. - -char *PQuser(const PGconn *conn); - - - - - - -PQpassPQpass - - - Returns the password of the connection. - -char *PQpass(const PGconn *conn); - - - - - - -PQhostPQhost - - - Returns the server host name of the connection. - -char *PQhost(const PGconn *conn); - - - - - - -PQportPQport - - - Returns the port of the connection. - -char *PQport(const PGconn *conn); - - - - - - -PQttyPQtty - - - Returns the debug TTY of the connection. - (This is obsolete, since the server no longer pays attention - to the TTY setting, but the function remains - for backwards compatibility.) - -char *PQtty(const PGconn *conn); - - - - - - -PQoptionsPQoptions - - - 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. - - - -PQstatusPQstatus - - - Returns the status of the connection. - -ConnStatusType PQstatus(const PGconn *conn); - - + + 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. + - 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. + 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. - See the entry for PQconnectStart and PQconnectPoll with regards - to other status codes - that might be seen. + 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. - -PQtransactionStatusPQtransactionStatus - - - Returns the current in-transaction status of the server. - -PGTransactionStatusType PQtransactionStatus(const PGconn *conn); - + + + PQprotocolVersion + + PQprotocolVersion + + -The status can be PQTRANS_IDLE (currently idle), -PQTRANS_ACTIVE (a command is in progress), -PQTRANS_INTRANS (idle, in a valid transaction block), -or PQTRANS_INERROR (idle, in a failed transaction block). -PQTRANS_UNKNOWN is reported if the connection is bad. -PQTRANS_ACTIVE is reported only when a query -has been sent to the server and not yet completed. - - - -PQtransactionStatus will give incorrect results when using -a PostgreSQL 7.3 server that has the parameter autocommit -set to off. The server-side autocommit feature has been -deprecated and does not exist in later server versions. - - - - - - -PQparameterStatusPQparameterStatus - - - Looks up a current parameter setting of the server. - -const char *PQparameterStatus(const PGconn *conn, const char *paramName); - + + + Interrogates the frontend/backend protocol being used. + + int PQprotocolVersion(const PGconn *conn); + + Applications might wish to use this to determine whether certain + features are supported. Currently, the possible values are 2 (2.0 + protocol), 3 (3.0 protocol), or zero (connection bad). This will + not change after connection startup is complete, but it could + theoretically change during a connection reset. The 3.0 protocol + will normally be used when communicating with + PostgreSQL 7.4 or later servers; pre-7.4 servers + support only protocol 2.0. (Protocol 1.0 is obsolete and not + supported by libpq.) + + + -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, -is_superuser, -session_authorization, -DateStyle, -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.) -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 may assume it is false, that is, backslashes -are treated as escapes in string literals. Also, the presence of this -parameter may 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. - - - - - -PQprotocolVersionPQprotocolVersion - - - Interrogates the frontend/backend protocol being used. - -int PQprotocolVersion(const PGconn *conn); - -Applications may wish to use this to determine whether certain features -are supported. -Currently, the possible values are 2 (2.0 protocol), 3 (3.0 protocol), -or zero (connection bad). This will not change after connection -startup is complete, but it could theoretically change during a connection -reset. The 3.0 protocol will normally be used when communicating with -PostgreSQL 7.4 or later servers; pre-7.4 servers support -only protocol 2.0. (Protocol 1.0 is obsolete and not supported by libpq.) - - - - - -PQserverVersionPQserverVersion - - - Returns an integer representing the backend version. - -int PQserverVersion(const PGconn *conn); - -Applications may use this to determine the version of the database server they -are connected to. The number is formed by converting the major, minor, and -revision numbers into two-decimal-digit numbers and appending them -together. For example, version 7.4.2 will be returned as 70402, and version -8.1 will be returned as 80100 (leading zeroes are not shown). Zero is -returned if the connection is bad. - - - + + + PQserverVersion + + PQserverVersion + + + + + + Returns an integer representing the backend version. + + int PQserverVersion(const PGconn *conn); + + Applications might use this to determine the version of the database + server they are connected to. The number is formed by converting + the major, minor, and revision numbers into two-decimal-digit + numbers and appending them together. For example, version 8.1.5 + will be returned as 80105, and version 8.2 will be returned as + 80200 (leading zeroes are not shown). Zero is returned if the + connection is bad. + + + - PQerrorMessagePQerrorMessage + + PQerrorMessage + + PQerrorMessage + + + - error message - Returns the error message most recently generated by - an operation on the connection. - -char *PQerrorMessage(const PGconn *conn); - + error message Returns the error message + most recently generated by an operation on the connection. + + + char *PQerrorMessage(const PGconn *conn); + + Nearly all libpq functions will set a message for - PQerrorMessage if they fail. - Note that by libpq convention, a nonempty - PQerrorMessage result 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 + PQerrorMessage if they fail. Note that by + libpq convention, a nonempty + PQerrorMessage result 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. @@ -997,9 +1111,11 @@ char *PQerrorMessage(const PGconn *conn); 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); - + + + int PQsocket(const PGconn *conn); + + @@ -1012,12 +1128,13 @@ int PQsocket(const PGconn *conn); (PID)PIDdetermining PID of server processin libpq of the backend server process handling this connection. - -int PQbackendPID(const PGconn *conn); - - - + + 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 @@ -1028,3134 +1145,4250 @@ int PQbackendPID(const PGconn *conn); + + PQconnectionNeedsPasswordPQconnectionNeedsPassword + + + Returns true (1) if the connection authentication method + required a password, but none was available. + Returns false (0) if not. + + + int PQconnectionNeedsPassword(const PGconn *conn); + + + + + + This function can be applied after a failed connection attempt + to decide whether to prompt the user for a password. + + + + + + PQconnectionUsedPasswordPQconnectionUsedPassword + + + Returns true (1) if the connection authentication method + used a caller-supplied password. Returns false (0) if not. + + + int PQconnectionUsedPassword(const PGconn *conn); + + + + + + This function detects whether a password supplied to the connection + function was actually used. Passwords obtained from other + sources (such as the .pgpass file) are not considered + caller-supplied. + + + + PQgetsslPQgetssl SSLin libpq Returns the SSL structure used in the connection, or null - if SSL is not in use. - -SSL *PQgetssl(const PGconn *conn); - - + 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. + + 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 this will also + correct prototype for this function. Doing this will also automatically include ssl.h from OpenSSL. - - + + + + + + + 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. + + + + Main Functions + + + + + + PQexec + + PQexec + + + + + + Submits a command to the server and waits for the result. + + + PGresult *PQexec(PGconn *conn, const char *command); + + + + + Returns a PGresult pointer or possibly a null + pointer. A non-null pointer will generally be returned except in + out-of-memory conditions or serious errors such as inability to send + the command to the server. If a null pointer is returned, it should + be treated like a PGRES_FATAL_ERROR result. Use + PQerrorMessage to get more information about such + errors. + + + + + + It is allowed to include multiple SQL commands (separated by semicolons) + in the command string. Multiple queries sent in a single + PQexec call are processed in a single transaction, unless + there are explicit BEGIN/COMMIT + commands included in the query string to divide it into multiple + transactions. Note however that the returned + PGresult structure describes only the result + of the last command executed from the string. Should one of the + commands fail, processing of the string stops with it and the returned + PGresult describes the error condition. + + + + + + + PQexecParams + + PQexecParams + + + + + + Submits a command to the server and waits for the result, + with the ability to pass parameters separately from the SQL + command text. + + +PGresult *PQexecParams(PGconn *conn, + const char *command, + int nParams, + const Oid *paramTypes, + const char * const *paramValues, + const int *paramLengths, + const int *paramFormats, + int resultFormat); + + + + + PQexecParams is like PQexec, but offers additional + functionality: parameter values can be specified separately from the command + string proper, and query results can be requested in either text or binary + format. PQexecParams is supported only in protocol 3.0 and later + connections; it will fail when using protocol 2.0. + + + + The function arguments are: + + + + conn + + + + The connection object to send the command through. + + + + + + command + + + The SQL command string to be executed. If parameters are used, + they are referred to in the command string as $1, + $2, etc. + + + + + + nParams + + + The number of parameters supplied; it is the length of the arrays + paramTypes[], paramValues[], + paramLengths[], and paramFormats[]. (The + array pointers can be NULL when nParams + is zero.) + + + + + + paramTypes[] + + + Specifies, by OID, the data types to be assigned to the + parameter symbols. If paramTypes is + NULL, or any particular element in the array + is zero, the server infers a data type for the parameter symbol + in the same way it would do for an untyped literal string. + + + + + + paramValues[] + + + Specifies the actual values of the parameters. A null pointer + in this array means the corresponding parameter is null; + otherwise the pointer points to a zero-terminated text string + (for text format) or binary data in the format expected by the + server (for binary format). + + + + + + paramLengths[] + + + Specifies the actual data lengths of binary-format parameters. + It is ignored for null parameters and text-format parameters. + The array pointer can be null when there are no binary parameters. + + + + + + paramFormats[] + + + Specifies whether parameters are text (put a zero in the + array entry for the corresponding parameter) or binary (put + a one in the array entry for the corresponding parameter). + If the array pointer is null then all parameters are presumed + to be text strings. + + + + + + resultFormat + + + Specify zero to obtain results in text format, or one to obtain + results in binary format. (There is not currently a provision + to obtain different result columns in different formats, + although that is possible in the underlying protocol.) + + + + + + + + + + + + The primary advantage of PQexecParams over + PQexec is that parameter values can be separated from the + command string, thus avoiding the need for tedious and error-prone + quoting and escaping. + + + + Unlike PQexec, PQexecParams allows at most + one SQL command in the given string. (There can be semicolons in it, + but not more than one nonempty command.) This is a limitation of the + underlying protocol, but has some usefulness as an extra defense against + SQL-injection attacks. + + + + + Specifying parameter types via OIDs is tedious, particularly if you prefer + not to hard-wire particular OID values into your program. However, you can + avoid doing so even in cases where the server by itself cannot determine the + type of the parameter, or chooses a different type than you want. In the + SQL command text, attach an explicit cast to the parameter symbol to show what + data type you will send. For example: + +SELECT * FROM mytable WHERE x = $1::bigint; + + This forces parameter $1 to be treated as bigint, whereas + by default it would be assigned the same type as x. Forcing the + parameter type decision, either this way or by specifying a numeric type OID, + is strongly recommended when sending parameter values in binary format, because + binary format has less redundancy than text format and so there is less chance + that the server will detect a type mismatch mistake for you. + + + + + + + PQprepare + + PQprepare + + + + + + Submits a request to create a prepared statement with the + given parameters, and waits for completion. + +PGresult *PQprepare(PGconn *conn, + const char *stmtName, + const char *query, + int nParams, + const Oid *paramTypes); + + + + + PQprepare creates a prepared statement for later + execution with PQexecPrepared. This feature allows + commands that will be used repeatedly to be parsed and planned just + once, rather than each time they are executed. + PQprepare is supported only in protocol 3.0 and later + connections; it will fail when using protocol 2.0. + + + + The function creates a prepared statement named + stmtName from the query string, which + must contain a single SQL command. stmtName can be + "" to create an unnamed statement, in which case any + pre-existing unnamed statement is automatically replaced; otherwise + it is an error if the statement name is already defined in the + current session. If any parameters are used, they are referred + to in the query as $1, $2, etc. + nParams is the number of parameters for which types + are pre-specified in the array paramTypes[]. (The + array pointer can be NULL when + nParams is zero.) paramTypes[] + specifies, by OID, the data types to be assigned to the parameter + symbols. If paramTypes is NULL, + or any particular element in the array is zero, the server assigns + a data type to the parameter symbol in the same way it would do + for an untyped literal string. Also, the query can use parameter + symbols with numbers higher than nParams; data types + will be inferred for these symbols as well. (See + PQdescribePrepared for a means to find out + what data types were inferred.) + + + + As with PQexec, the result is normally a + PGresult object whose contents indicate + server-side success or failure. A null result indicates + out-of-memory or inability to send the command at all. Use + PQerrorMessage to get more information about + such errors. + + + + + + Prepared statements for use with PQexecPrepared can also + be created by executing SQL statements. (But PQprepare + is more flexible since it does not require parameter types to be + pre-specified.) Also, although there is no libpq + function for deleting a prepared statement, the SQL statement + can be used for that purpose. + + + + + + + PQexecPrepared + + PQexecPrepared + + + + + + Sends a request to execute a prepared statement with given + parameters, and waits for the result. + +PGresult *PQexecPrepared(PGconn *conn, + const char *stmtName, + int nParams, + const char * const *paramValues, + const int *paramLengths, + const int *paramFormats, + int resultFormat); + + + + + PQexecPrepared is like PQexecParams, + but the command to be executed is specified by naming a + previously-prepared statement, instead of giving a query string. + This feature allows commands that will be used repeatedly to be + parsed and planned just once, rather than each time they are + executed. The statement must have been prepared previously in + the current session. PQexecPrepared is supported + only in protocol 3.0 and later connections; it will fail when + using protocol 2.0. + + + + The parameters are identical to PQexecParams, except that the + name of a prepared statement is given instead of a query string, and the + paramTypes[] parameter is not present (it is not needed since + the prepared statement's parameter types were determined when it was created). + + + + + + + PQdescribePrepared + + PQdescribePrepared + + + + + + Submits a request to obtain information about the specified + prepared statement, and waits for completion. + +PGresult *PQdescribePrepared(PGconn *conn, const char *stmtName); + + + + + PQdescribePrepared allows an application to obtain + information about a previously prepared statement. + PQdescribePrepared is supported only in protocol 3.0 + and later connections; it will fail when using protocol 2.0. + + + + stmtName can be "" or NULL to reference + the unnamed statement, otherwise it must be the name of an existing + prepared statement. On success, a PGresult with + status PGRES_COMMAND_OK is returned. The + functions PQnparams and + PQparamtype can be applied to this + PGresult to obtain information about the parameters + of the prepared statement, and the functions + PQnfields, PQfname, + PQftype, etc provide information about the + result columns (if any) of the statement. + + + + + + + PQdescribePortal + + PQdescribePortal + + + + + + Submits a request to obtain information about the specified + portal, and waits for completion. + +PGresult *PQdescribePortal(PGconn *conn, const char *portalName); + + + + + PQdescribePortal allows an application to obtain + information about a previously created portal. + (libpq does not provide any direct access to + portals, but you can use this function to inspect the properties + of a cursor created with a DECLARE CURSOR SQL command.) + PQdescribePortal is supported only in protocol 3.0 + and later connections; it will fail when using protocol 2.0. + + + + portalName can be "" or NULL to reference + the unnamed portal, otherwise it must be the name of an existing + portal. On success, a PGresult with status + PGRES_COMMAND_OK is returned. The functions + PQnfields, PQfname, + PQftype, etc can be applied to the + PGresult to obtain information about the result + columns (if any) of the portal. + + + + + + + + The PGresultPGresult + structure encapsulates the result returned by the server. + libpq application programmers should be + careful to maintain the PGresult abstraction. + Use the accessor functions below to get at the contents of + PGresult. Avoid directly referencing the + fields of the PGresult structure because they + are subject to change in the future. + + + + + PQresultStatus + + PQresultStatus + + + + + + Returns the result status of the command. + + ExecStatusType PQresultStatus(const PGresult *res); + + + + + PQresultStatus can return one of the following values: + + + + PGRES_EMPTY_QUERY + + + The string sent to the server was empty. + + + + + + PGRES_COMMAND_OK + + + Successful completion of a command returning no data. + + + + + + PGRES_TUPLES_OK + + + Successful completion of a command returning data (such as + a SELECT or SHOW). + + + + + + PGRES_COPY_OUT + + + Copy Out (from server) data transfer started. + + + + + + PGRES_COPY_IN + + + Copy In (to server) data transfer started. + + + + + + PGRES_BAD_RESPONSE + + + The server's response was not understood. + + + + + + PGRES_NONFATAL_ERROR + + + A nonfatal error (a notice or warning) occurred. + + + + + + PGRES_FATAL_ERROR + + + A fatal error occurred. + + + + + + If the result status is PGRES_TUPLES_OK, then + the functions described below can be used to retrieve the rows + returned by the query. Note that a SELECT + command that happens to retrieve zero rows still shows + PGRES_TUPLES_OK. + PGRES_COMMAND_OK is for commands that can never + return rows (INSERT, UPDATE, + etc.). A response of PGRES_EMPTY_QUERY might + indicate a bug in the client software. + + + + A result of status PGRES_NONFATAL_ERROR will + never be returned directly by PQexec or other + query execution functions; results of this kind are instead passed + to the notice processor (see ). + + + + + + + PQresStatus + + PQresStatus + + + + + + Converts the enumerated type returned by + PQresultStatus into a string constant describing the + status code. The caller should not free the result. + + + char *PQresStatus(ExecStatusType status); + + + + + + + + PQresultErrorMessage + + PQresultErrorMessage + + + + + + Returns the error message associated with the command, or an empty string + if there was no error. + + char *PQresultErrorMessage(const PGresult *res); + + If there was an error, the returned string will include a trailing + newline. The caller should not free the result directly. It will + be freed when the associated PGresult handle is + passed to PQclear. + + + + Immediately following a PQexec or + PQgetResult call, + PQerrorMessage (on the connection) will return + the same string as PQresultErrorMessage (on + the result). However, a PGresult will + retain its error message until destroyed, whereas the connection's + error message will change when subsequent operations are done. + Use PQresultErrorMessage when you want to + know the status associated with a particular + PGresult; use + PQerrorMessage when you want to know the + status from the latest operation on the connection. + + + + + + PQresultErrorFieldPQresultErrorField + + + Returns an individual field of an error report. + + char *PQresultErrorField(const PGresult *res, int fieldcode); + + fieldcode is an error field identifier; see the symbols + listed below. NULL is returned if the + PGresult is not an error or warning result, + or does not include the specified field. Field values will normally + not include a trailing newline. The caller should not free the + result directly. It will be freed when the + associated PGresult handle is passed to + PQclear. + + + + The following field codes are available: + + + PG_DIAG_SEVERITY + + + The severity; the field contents are ERROR, + FATAL, or PANIC (in an error message), + or WARNING, NOTICE, DEBUG, + INFO, or LOG (in a notice message), or + a localized translation of one of these. Always present. + + + + + + + error codes + libpq + + PG_DIAG_SQLSTATE + + + The SQLSTATE code for the error. The SQLSTATE code identifies + the type of error that has occurred; it can be used by + front-end applications to perform specific operations (such + as error handling) in response to a particular database error. + For a list of the possible SQLSTATE codes, see . This field is not localizable, + and is always present. + + + + + + PG_DIAG_MESSAGE_PRIMARY + + + The primary human-readable error message (typically one line). + Always present. + + + + + + PG_DIAG_MESSAGE_DETAIL + + + Detail: an optional secondary error message carrying more + detail about the problem. Might run to multiple lines. + + + + + + PG_DIAG_MESSAGE_HINT + + + Hint: an optional suggestion what to do about the problem. + This is intended to differ from detail in that it offers advice + (potentially inappropriate) rather than hard facts. Might + run to multiple lines. + + + + + + PG_DIAG_STATEMENT_POSITION + + + A string containing a decimal integer indicating an error cursor + position as an index into the original statement string. The + first character has index 1, and positions are measured in + characters not bytes. + + + + + + PG_DIAG_INTERNAL_POSITION + + + This is defined the same as the + PG_DIAG_STATEMENT_POSITION field, but it is used + when the cursor position refers to an internally generated + command rather than the one submitted by the client. The + PG_DIAG_INTERNAL_QUERY field will always appear when + this field appears. + + + + + + PG_DIAG_INTERNAL_QUERY + + + The text of a failed internally-generated command. This could + be, for example, a SQL query issued by a PL/pgSQL function. + + + + + + PG_DIAG_CONTEXT + + + An indication of the context in which the error occurred. + Presently this includes a call stack traceback of active + procedural language functions and internally-generated queries. + The trace is one entry per line, most recent first. + + + + + + PG_DIAG_SOURCE_FILE + + + The file name of the source-code location where the error was + reported. + + + + + + PG_DIAG_SOURCE_LINE + + + The line number of the source-code location where the error + was reported. + + + + + + PG_DIAG_SOURCE_FUNCTION + + + The name of the source-code function reporting the error. + + + + + + + + The client is responsible for formatting displayed information to meet + its needs; in particular it should break long lines as needed. + Newline characters appearing in the error message fields should be + treated as paragraph breaks, not line breaks. + + + + Errors generated internally by libpq will + have severity and primary message, but typically no other fields. + Errors returned by a pre-3.0-protocol server will include severity and + primary message, and sometimes a detail message, but no other fields. + + + + Note that error fields are only available from + PGresult objects, not + PGconn objects; there is no + PQerrorField function. + + + + + + PQclearPQclear + + + Frees the storage associated with a + PGresult. Every command result should be + freed via PQclear when it is no longer + needed. + + + void PQclear(PGresult *res); + + + + + You can keep a PGresult object around for + as long as you need it; it does not go away when you issue a new + command, nor even if you close the connection. To get rid of it, + you must call PQclear. Failure to do this + will result in memory leaks in your application. + + + + + + + PQmakeEmptyPGresult + + PQmakeEmptyPGresult + + + + + + Constructs an empty PGresult object with the given status. + + PGresult *PQmakeEmptyPGresult(PGconn *conn, ExecStatusType status); + + + + + This is libpq's internal function to allocate and + initialize an empty PGresult object. This + function returns NULL if memory could not be allocated. It is + exported because some applications find it useful to generate result + objects (particularly objects with error status) themselves. If + conn is not null and status + indicates an error, the current error message of the specified + connection is copied into the PGresult. + Note that PQclear should eventually be called + on the object, just as with a PGresult + returned by libpq itself. + + + + + + + + + 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 + + PQntuples + + + + + + Returns the number of rows (tuples) in the query result. Because + it returns an integer result, large result sets might overflow the + return value on 32-bit operating systems. + + + int PQntuples(const PGresult *res); + + + + + + + + + PQnfields + + PQnfields + + + + + + Returns the number of columns (fields) in each row of the query + result. + + + int PQnfields(const PGresult *res); + + + + + + + + PQfname + + PQfname + + + + + + Returns the column name associated with the given column number. + Column numbers start at 0. The caller should not free the result + directly. It will be freed when the associated + PGresult handle is passed to + PQclear. + + char *PQfname(const PGresult *res, + int column_number); + + + + + NULL is returned if the column number is out of range. + + + + + + + PQfnumber + + PQfnumber + + + + + + Returns the column number associated with the given column name. + + int PQfnumber(const PGresult *res, + const char *column_name); + + + + + -1 is returned if the given name does not match any column. + + + + The given name is treated like an identifier in an SQL command, + that is, it is downcased unless double-quoted. For example, given + a query result generated from the SQL command: + +SELECT 1 AS FOO, 2 AS "BAR"; + + we would have the results: + +PQfname(res, 0) foo +PQfname(res, 1) BAR +PQfnumber(res, "FOO") 0 +PQfnumber(res, "foo") 0 +PQfnumber(res, "BAR") -1 +PQfnumber(res, "\"BAR\"") 1 + + + + + + + + PQftable + + PQftable + + + + + + Returns the OID of the table from which the given column was + fetched. Column numbers start at 0. + + Oid PQftable(const PGresult *res, + int column_number); + + + + + InvalidOid is returned if the column number is out of range, + or if the specified column is not a simple reference to a table column, + or when using pre-3.0 protocol. + You can query the system table pg_class to determine + exactly which table is referenced. + + + + The type Oid and the constant + InvalidOid will be defined when you include + the libpq header file. They will both + be some integer type. + + + + + + + PQftablecol + + PQftablecol + + + + + + Returns the column number (within its table) of the column making + up the specified query result column. Query-result column numbers + start at 0, but table columns have nonzero numbers. + + int PQftablecol(const PGresult *res, + int column_number); + + + + + Zero is returned if the column number is out of range, or if the + specified column is not a simple reference to a table column, or + when using pre-3.0 protocol. + + + + + + + PQfformat + + PQfformat + + + + + + Returns the format code indicating the format of the given + column. Column numbers start at 0. + + int PQfformat(const PGresult *res, + int column_number); + + + + + Format code zero indicates textual data representation, while format + code one indicates binary representation. (Other codes are reserved + for future definition.) + + + + + + + PQftype + + PQftype + + + + + + Returns the data type associated with the given column number. + The integer returned is the internal OID number of the type. + Column numbers start at 0. + + Oid PQftype(const PGresult *res, + int column_number); + + + + + You can query the system table pg_type to + obtain the names and properties of the various data types. The + OIDs of the built-in data types are defined + in the file src/include/catalog/pg_type.h + in the source tree. + + + + + + + PQfmod + + PQfmod + + + + + + Returns the type modifier of the column associated with the + given column number. Column numbers start at 0. + + int PQfmod(const PGresult *res, + int column_number); + + + + + The interpretation of modifier values is type-specific; they + typically indicate precision or size limits. The value -1 is + used to indicate no information available. Most data + types do not use modifiers, in which case the value is always + -1. + + + + + + + PQfsize + + PQfsize + + + + + + Returns the size in bytes of the column associated with the + given column number. Column numbers start at 0. + + int PQfsize(const PGresult *res, + int column_number); + + + + + PQfsize returns the space allocated for this column + in a database row, in other words the size of the server's + internal representation of the data type. (Accordingly, it is + not really very useful to clients.) A negative value indicates + the data type is variable-length. + + + + + + + PQbinaryTuples + + PQbinaryTuples + + + + + + Returns 1 if the PGresult contains binary data + and 0 if it contains text data. + + int PQbinaryTuples(const PGresult *res); + + + + + This function is deprecated (except for its use in connection with + COPY), because it is possible for a single + PGresult to contain text data in some columns and + binary data in others. PQfformat is preferred. + PQbinaryTuples returns 1 only if all columns of the + result are binary (format 1). + + + + + + + PQgetvalue + + PQgetvalue + + + + + + Returns a single field value of one row of a + PGresult. Row and column numbers start + at 0. The caller should not free the result directly. It will + be freed when the associated PGresult handle is + passed to PQclear. + + char *PQgetvalue(const PGresult *res, + int row_number, + int column_number); + + + + + For data in text format, the value returned by + PQgetvalue is a null-terminated character + string representation of the field value. For data in binary + format, the value is in the binary representation determined by + the data type's typsend and typreceive + functions. (The value is actually followed by a zero byte in + this case too, but that is not ordinarily useful, since the + value is likely to contain embedded nulls.) + + + + An empty string is returned if the field value is null. See + PQgetisnull to distinguish null values from + empty-string values. + + + + The pointer returned by PQgetvalue points + to storage that is part of the PGresult + structure. One should not modify the data it points to, and one + must explicitly copy the data into other storage if it is to be + used past the lifetime of the PGresult + structure itself. + + + + + + + PQgetisnull + + PQgetisnull + + + null value + in libpq + + + + + + Tests a field for a null value. Row and column numbers start + at 0. + + int PQgetisnull(const PGresult *res, + int row_number, + int column_number); + + + + + This function returns 1 if the field is null and 0 if it + contains a non-null value. (Note that + PQgetvalue will return an empty string, + not a null pointer, for a null field.) + + + + + + + PQgetlength + + PQgetlength + + + + + Returns the actual length of a field value in bytes. Row and + column numbers start at 0. + + int PQgetlength(const PGresult *res, + int row_number, + int column_number); + + + + + This is the actual data length for the particular data value, + that is, the size of the object pointed to by + PQgetvalue. For text data format this is + the same as strlen(). For binary format this is + essential information. Note that one should not + rely on PQfsize to obtain the actual data + length. + + + + + + + PQnparams + + PQnparams + + + + + + Returns the number of parameters of a prepared statement. + + int PQnparams(const PGresult *res); + + + + + This function is only useful when inspecting the result of + PQdescribePrepared. For other types of queries it + will return zero. + + + + + + + PQparamtype + + PQparamtype + + + + + + Returns the data type of the indicated statement parameter. + Parameter numbers start at 0. + + Oid PQparamtype(const PGresult *res, int param_number); + + + + + This function is only useful when inspecting the result of + PQdescribePrepared. For other types of queries it + will return zero. + + + + + + + PQprint + + PQprint + + + + + + Prints out all the rows and, optionally, the column names to + the specified output stream. + +void PQprint(FILE *fout, /* output stream */ + const PGresult *res, + const PQprintOpt *po); +typedef struct { + pqbool header; /* print output field headings and row count */ + pqbool align; /* fill align the fields */ + pqbool standard; /* old brain dead format */ + pqbool html3; /* output HTML tables */ + pqbool expanded; /* expand tables */ + pqbool pager; /* use pager for output if needed */ + char *fieldSep; /* field separator */ + char *tableOpt; /* attributes for HTML table element */ + char *caption; /* HTML table caption */ + char **fieldName; /* null-terminated array of replacement field names */ +} PQprintOpt; + + + + + This function was formerly used by psql + to print query results, but this is no longer the case. Note + that it assumes all the data is in text format. + + + + + + + + Retrieving Result Information for Other Commands + + + These functions are used to extract information from + PGresult objects that are not + SELECT results. + + + + + + PQcmdStatus + + PQcmdStatus + + + + + + Returns the command status tag from the SQL command that generated + the PGresult. + + char *PQcmdStatus(PGresult *res); + + + + + Commonly this is just the name of the command, but it might include + additional data such as the number of rows processed. The caller + should not free the result directly. It will be freed when the + associated PGresult handle is passed to + PQclear. + + + + + + + PQcmdTuples + + PQcmdTuples + + + + + + Returns the number of rows affected by the SQL command. + + char *PQcmdTuples(PGresult *res); + + + + + This function returns a string containing the number of rows + affected by the SQL statement that generated the + PGresult. This function can only be used following + the execution of an INSERT, UPDATE, + DELETE, MOVE, FETCH, or + COPY statement, or an EXECUTE of a + prepared query that contains an INSERT, + UPDATE, or DELETE statement. If the + command that generated the PGresult was anything + else, PQcmdTuples returns an empty string. The caller + should not free the return value directly. It will be freed when + the associated PGresult handle is passed to + PQclear. + + + + + + + PQoidValue + + PQoidValue + + + + + + Returns the OIDOIDin libpq + of the inserted row, if the SQL command was an + INSERT that inserted exactly one row into a table that + has OIDs, or a EXECUTE of a prepared query containing + a suitable INSERT statement. Otherwise, this function + returns InvalidOid. This function will also + return InvalidOid if the table affected by the + INSERT statement does not contain OIDs. + + Oid PQoidValue(const PGresult *res); + + + + + + + + PQoidStatus + + PQoidStatus + + + + + + Returns a string with the OID of the inserted row, if the + SQL command was an INSERT + that inserted exactly one row, or a EXECUTE of + a prepared statement consisting of a suitable + INSERT. (The string will be 0 if + the INSERT did not insert exactly one row, or + if the target table does not have OIDs.) If the command was not + an INSERT, returns an empty string. + + char *PQoidStatus(const PGresult *res); + + + + + This function is deprecated in favor of + PQoidValue. It is not thread-safe. + + + + + + + + + Escaping Strings for Inclusion in SQL Commands + + + PQescapeStringConn + + + PQescapeString + + + escaping strings + in libpq + + + + PQescapeStringConn 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. + PQescapeStringConn performs this operation. + + + + + 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. + + + size_t PQescapeStringConn (PGconn *conn, + char *to, const char *from, size_t length, + int *error); + + + + + PQescapeStringConn writes an escaped version of the + from string to the to buffer, escaping + special characters so that they cannot cause any harm, and adding a + terminating zero byte. The single quotes that must surround + PostgreSQL string literals are not included in the + result string; 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. + + + + + size_t PQescapeString (char *to, const char *from, size_t length); + + + + + PQescapeString is an older, deprecated version of + PQescapeStringConn; the difference is that it does + not take conn or error parameters. + Because of this, it cannot adjust its behavior depending on the + connection properties (such as character encoding) and therefore + it might give the wrong results. Also, it has no way + to report error conditions. + + + + PQescapeString can be used safely in single-threaded + client programs that work with only one PostgreSQL + connection at a time (in this case it can find out what it needs to + know behind the scenes). In other contexts it is a security + hazard and should be avoided in favor of + PQescapeStringConn. + + + + + + Escaping Binary Strings for Inclusion in SQL Commands + + + bytea + in libpq + + + + + + PQescapeByteaConn + + PQescapeByteaConn + + + + + + Escapes binary data for use within an SQL command with the type + bytea. As with PQescapeStringConn, + this is only used when inserting data directly into an SQL command string. + + unsigned char *PQescapeByteaConn(PGconn *conn, + const unsigned char *from, + size_t from_length, + size_t *to_length); + + + + + Certain byte values must be escaped (but all + byte values can be escaped) when used as part + of a bytea literal in an SQL + statement. In general, to escape a byte, it is converted into the + three digit octal number equal to the octet value, and preceded by + usually two backslashes. The single quote (') and backslash + (\) characters have special alternative escape + sequences. See for more + information. PQescapeByteaConn performs this + operation, escaping only the minimally required bytes. + + + + The from parameter points to the first + byte of the string that is to be escaped, and the + from_length parameter gives the number of + bytes in this binary string. (A terminating zero byte is + neither necessary nor counted.) The to_length + parameter points to a variable that will hold the resultant + escaped string length. This result string length includes the terminating + zero byte of the result. + + + + PQescapeByteaConn returns an escaped version of the + from parameter binary string in memory + allocated with malloc(). This memory must be freed using + PQfreemem() when the result is no longer needed. The + return string has all special characters replaced so that they can + be properly processed by the PostgreSQL + string literal parser, and the bytea input function. A + terminating zero byte is also added. The single quotes that must + surround PostgreSQL string literals are + not part of the result string. + + + + On error, a NULL pointer is returned, and a suitable error message + is stored in the conn object. Currently, the only + possible error is insufficient memory for the result string. + + + + + + + PQescapeBytea + + PQescapeBytea + + + + + + PQescapeBytea is an older, deprecated version of + PQescapeByteaConn. + + unsigned char *PQescapeBytea(const unsigned char *from, + size_t from_length, + size_t *to_length); + + + + + The only difference from PQescapeByteaConn is that + PQescapeBytea does not take a PGconn + parameter. Because of this, it cannot adjust its behavior + depending on the connection properties (in particular, whether + standard-conforming strings are enabled) and therefore + it might give the wrong results. Also, it has no + way to return an error message on failure. + + + + PQescapeBytea can be used safely in single-threaded + client programs that work with only one PostgreSQL + connection at a time (in this case it can find out what it needs + to know behind the scenes). In other contexts it is + a security hazard and should be avoided in favor of + PQescapeByteaConn. + + + + + + + PQunescapeBytea + + PQunescapeBytea + + + + + + Converts a string representation of binary data into binary data + — the reverse of PQescapeBytea. This + is needed when retrieving bytea data in text format, + but not when retrieving it in binary format. + + + unsigned char *PQunescapeBytea(const unsigned char *from, size_t *to_length); + + + + + The from parameter points to a string + such as might be returned by PQgetvalue when applied + to a bytea column. PQunescapeBytea + converts this string representation into its binary representation. + It returns a pointer to a buffer allocated with + malloc(), or null on error, and puts the size of + the buffer in to_length. The result must be + freed using PQfreemem when it is no longer needed. + + + + This conversion is not exactly the inverse of + PQescapeBytea, because the string is not expected + to be escaped when received from PQgetvalue. + In particular this means there is no need for string quoting considerations, + and so no need for a PGconn parameter. + + + + + + + PQfreemem + + PQfreemem + + + + + + Frees memory allocated by libpq. + + void PQfreemem(void *ptr); + + + + + Frees memory allocated by libpq, particularly + PQescapeByteaConn, + PQescapeBytea, + PQunescapeBytea, + and PQnotifies. + It is particularly important that this function, rather than + free(), be used on Microsoft Windows. This is because + allocating memory in a DLL and releasing it in the application works + only if multithreaded/single-threaded, release/debug, and static/dynamic + flags are the same for the DLL and the application. On non-Microsoft + Windows platforms, this function is the same as the standard library + function free(). + + + + + + + + + + + Asynchronous Command Processing + + + nonblocking connection + + + + The PQexec function is adequate for submitting + commands in normal, synchronous applications. It has a couple of + deficiencies, however, that can be of importance to some users: + + + + + PQexec waits for the command to be completed. + The application might have other work to do (such as maintaining a + user interface), in which case it won't want to block waiting for + the response. + + + + + + Since the execution of the client application is suspended while it + waits for the result, it is hard for the application to decide that + it would like to try to cancel the ongoing command. (It can be done + from a signal handler, but not otherwise.) + + + + + + PQexec can return only one + PGresult structure. If the submitted command + string contains multiple SQL commands, all but + the last PGresult are discarded by + PQexec. + + + + + + + Applications that do not like these limitations can instead use the + underlying functions that PQexec is built from: + PQsendQuery and PQgetResult. + There are also + PQsendQueryParams, + PQsendPrepare, + PQsendQueryPrepared, + PQsendDescribePrepared, and + PQsendDescribePortal, + which can be used with PQgetResult to duplicate + the functionality of + PQexecParams, + PQprepare, + PQexecPrepared, + PQdescribePrepared, and + PQdescribePortal + respectively. + + + + + PQsendQuery + + PQsendQuery + + + + + + Submits a command to the server without waiting for the result(s). + 1 is returned if the command was successfully dispatched and 0 if + not (in which case, use PQerrorMessage to get more + information about the failure). + + int PQsendQuery(PGconn *conn, const char *command); + + + After successfully calling PQsendQuery, call + PQgetResult one or more times to obtain the + results. PQsendQuery cannot be called again + (on the same connection) until PQgetResult + has returned a null pointer, indicating that the command is done. + + + + + + + PQsendQueryParams + + PQsendQueryParams + + + + + + Submits a command and separate parameters to the server without + waiting for the result(s). + + int PQsendQueryParams(PGconn *conn, + const char *command, + int nParams, + const Oid *paramTypes, + const char * const *paramValues, + const int *paramLengths, + const int *paramFormats, + int resultFormat); + + + This is equivalent to PQsendQuery except that + query parameters can be specified separately from the query string. + The function's parameters are handled identically to + PQexecParams. Like + PQexecParams, it will not work on 2.0-protocol + connections, and it allows only one command in the query string. + + + + + + + PQsendPrepare + + PQsendPrepare + + + + + + Sends a request to create a prepared statement with the given + parameters, without waiting for completion. + + int PQsendPrepare(PGconn *conn, + const char *stmtName, + const char *query, + int nParams, + const Oid *paramTypes); + + + This is an asynchronous version of PQprepare: it + returns 1 if it was able to dispatch the request, and 0 if not. + After a successful call, call PQgetResult to + determine whether the server successfully created the prepared + statement. The function's parameters are handled identically to + PQprepare. Like + PQprepare, it will not work on 2.0-protocol + connections. + + + + + + + PQsendQueryPrepared + + PQsendQueryPrepared + + + + + + Sends a request to execute a prepared statement with given + parameters, without waiting for the result(s). + + int PQsendQueryPrepared(PGconn *conn, + const char *stmtName, + int nParams, + const char * const *paramValues, + const int *paramLengths, + const int *paramFormats, + int resultFormat); + + + This is similar to PQsendQueryParams, but + the command to be executed is specified by naming a + previously-prepared statement, instead of giving a query string. + The function's parameters are handled identically to + PQexecPrepared. Like + PQexecPrepared, it will not work on + 2.0-protocol connections. + + + + + + + PQsendDescribePrepared + + PQsendDescribePrepared + + + + + + Submits a request to obtain information about the specified + prepared statement, without waiting for completion. + + int PQsendDescribePrepared(PGconn *conn, const char *stmtName); + + + This is an asynchronous version of PQdescribePrepared: + it returns 1 if it was able to dispatch the request, and 0 if not. + After a successful call, call PQgetResult to + obtain the results. The function's parameters are handled + identically to PQdescribePrepared. Like + PQdescribePrepared, it will not work on + 2.0-protocol connections. + + + + + + + PQsendDescribePortal + + PQsendDescribePortal + + + + + + Submits a request to obtain information about the specified + portal, without waiting for completion. + + int PQsendDescribePortal(PGconn *conn, const char *portalName); + + + This is an asynchronous version of PQdescribePortal: + it returns 1 if it was able to dispatch the request, and 0 if not. + After a successful call, call PQgetResult to + obtain the results. The function's parameters are handled + identically to PQdescribePortal. Like + PQdescribePortal, it will not work on + 2.0-protocol connections. + + + + + + + PQgetResult + + PQgetResult + + + + + + Waits for the next result from a prior + PQsendQuery, + PQsendQueryParams, + PQsendPrepare, or + PQsendQueryPrepared call, and returns it. + A null pointer is returned when the command is complete and there + will be no more results. + + PGresult *PQgetResult(PGconn *conn); + + + + + PQgetResult must be called repeatedly until + it returns a null pointer, indicating that the command is done. + (If called when no command is active, + PQgetResult will just return a null pointer + at once.) Each non-null result from + PQgetResult should be processed using the + same PGresult accessor functions previously + described. Don't forget to free each result object with + PQclear when done with it. Note that + PQgetResult will block only if a command is + active and the necessary response data has not yet been read by + PQconsumeInput. + + + + + + + + Using PQsendQuery and + PQgetResult solves one of + PQexec's problems: If a command string contains + multiple SQL commands, the results of those commands + can be obtained individually. (This allows a simple form of overlapped + processing, by the way: the client can be handling the results of one + command while the server is still working on later queries in the same + command string.) However, calling PQgetResult + will still cause the client to block until the server completes the + next SQL command. This can be avoided by proper + use of two more functions: + + + + + PQconsumeInput + + PQconsumeInput + + + + + + If input is available from the server, consume it. + + int PQconsumeInput(PGconn *conn); + + + + + PQconsumeInput normally returns 1 indicating + no error, but returns 0 if there was some kind of + trouble (in which case PQerrorMessage can be + consulted). Note that the result does not say whether any input + data was actually collected. After calling + PQconsumeInput, the application can check + PQisBusy and/or + PQnotifies to see if their state has changed. + + + + PQconsumeInput can be called even if the + application is not prepared to deal with a result or notification + just yet. The function will read available data and save it in + a buffer, thereby causing a select() + read-ready indication to go away. The application can thus use + PQconsumeInput to clear the + select() condition immediately, and then + examine the results at leisure. + + + + + + + PQisBusy + + PQisBusy + + + + + + Returns 1 if a command is busy, that is, + PQgetResult would block waiting for input. + A 0 return indicates that PQgetResult can be + called with assurance of not blocking. + + int PQisBusy(PGconn *conn); + + + + + PQisBusy will not itself attempt to read data + from the server; therefore PQconsumeInput + must be invoked first, or the busy state will never end. + + + + + + + + A typical application using these functions will have a main loop that + uses select() or poll() to wait for + all the conditions that it must respond to. One of the conditions + will be input available from the server, which in terms of + select() means readable data on the file + descriptor identified by PQsocket. When the main + loop detects input ready, it should call + PQconsumeInput to read the input. It can then + call PQisBusy, followed by + PQgetResult if PQisBusy + returns false (0). It can also call PQnotifies + to detect NOTIFY messages (see ). + + + + A client that uses + PQsendQuery/PQgetResult + can also attempt to cancel a command that is still being processed + by the server; see . But regardless of + the return value of PQcancel, the application + must continue with the normal result-reading sequence using + PQgetResult. A successful cancellation will + simply cause the command to terminate sooner than it would have + otherwise. + + + + By using the functions described above, it is possible to avoid + blocking while waiting for input from the database server. However, + it is still possible that the application will block waiting to send + output to the server. This is relatively uncommon but can happen if + very long SQL commands or data values are sent. (It is much more + probable if the application sends data via COPY IN, + however.) To prevent this possibility and achieve completely + nonblocking database operation, the following additional functions + can be used. + + + + + PQsetnonblocking + + PQsetnonblocking + + + + + + Sets the nonblocking status of the connection. + + int PQsetnonblocking(PGconn *conn, int arg); + + + + + Sets the state of the connection to nonblocking if + arg is 1, or blocking if + arg is 0. Returns 0 if OK, -1 if error. + + + + In the nonblocking state, calls to + PQsendQuery, PQputline, + PQputnbytes, and + PQendcopy will not block but instead return + an error if they need to be called again. + + + + Note that PQexec does not honor nonblocking + mode; if it is called, it will act in blocking fashion anyway. + + + + + + + PQisnonblocking + + PQisnonblocking + + + + + + Returns the blocking status of the database connection. + + int PQisnonblocking(const PGconn *conn); + + + + + Returns 1 if the connection is set to nonblocking mode and 0 if + blocking. + + + + + + + PQflush + + PQflush + + + + + + Attempts to flush any queued output data to the server. Returns + 0 if successful (or if the send queue is empty), -1 if it failed + for some reason, or 1 if it was unable to send all the data in + the send queue yet (this case can only occur if the connection + is nonblocking). + + int PQflush(PGconn *conn); + + + + + + + + + After sending any command or data on a nonblocking connection, call + PQflush. If it returns 1, wait for the socket + to be write-ready and call it again; repeat until it returns 0. Once + PQflush returns 0, wait for the socket to be + read-ready and then read the response as described above. + + + + + + Cancelling Queries in Progress + + + canceling + SQL command + + + + A client application can request cancellation of a command that is + still being processed by the server, using the functions described in + this section. + + + + + PQgetCancel + + PQgetCancel + + + + + + Creates a data structure containing the information needed to cancel + a command issued through a particular database connection. + + PGcancel *PQgetCancel(PGconn *conn); + + + + + PQgetCancel creates a + PGcancelPGcancel object + given a PGconn connection object. It will return + NULL if the given conn is NULL or an invalid + connection. The PGcancel object is an opaque + structure that is not meant to be accessed directly by the + application; it can only be passed to PQcancel + or PQfreeCancel. + + + + + + + PQfreeCancel + + PQfreeCancel + + + + + + Frees a data structure created by PQgetCancel. + + void PQfreeCancel(PGcancel *cancel); + + + + + PQfreeCancel frees a data object previously created + by PQgetCancel. + + + + + + + PQcancel + + PQcancel + + + + + + Requests that the server abandon processing of the current command. + + int PQcancel(PGcancel *cancel, char *errbuf, int errbufsize); + + + + + The return value is 1 if the cancel request was successfully + dispatched and 0 if not. If not, errbuf is filled + with an error message explaining why not. errbuf + must be a char array of size errbufsize (the + recommended size is 256 bytes). + + + + Successful dispatch is no guarantee that the request will have + any effect, however. If the cancellation is effective, the current + command will terminate early and return an error result. If the + cancellation fails (say, because the server was already done + processing the command), then there will be no visible result at + all. + + + + PQcancel can safely be invoked from a signal + handler, if the errbuf is a local variable in the + signal handler. The PGcancel object is read-only + as far as PQcancel is concerned, so it can + also be invoked from a thread that is separate from the one + manipulating the PGconn object. + + + + + + + + + PQrequestCancel + + PQrequestCancel + + + + + + Requests that the server abandon processing of the current + command. + + int PQrequestCancel(PGconn *conn); + + + + + PQrequestCancel is a deprecated variant of + PQcancel. It operates directly on the + PGconn object, and in case of failure stores the + error message in the PGconn object (whence it can + be retrieved by PQerrorMessage). Although + the functionality is the same, this approach creates hazards for + multiple-thread programs and signal handlers, since it is possible + that overwriting the PGconn's error message will + mess up the operation currently in progress on the connection. + + + + + + + + + + The Fast-Path Interface + + + fast path + + + + PostgreSQL provides a fast-path interface + to send simple function calls to the server. + + + + + This interface is somewhat obsolete, as one can achieve similar + performance and greater functionality by setting up a prepared + statement to define the function call. Then, executing the statement + with binary transmission of parameters and results substitutes for a + fast-path function call. + + + + + The function PQfnPQfn + requests execution of a server function via the fast-path interface: + + PGresult *PQfn(PGconn *conn, + int fnid, + int *result_buf, + int *result_len, + int result_is_int, + const PQArgBlock *args, + int nargs); + + typedef struct { + int len; + int isint; + union { + int *ptr; + int integer; + } u; + } PQArgBlock; + + + + + The fnid argument is the OID of the function to be + executed. args and nargs define the + parameters to be passed to the function; they must match the declared + function argument list. When the isint field of a + parameter structure is true, the u.integer value is sent + to the server as an integer of the indicated length (this must be 1, + 2, or 4 bytes); proper byte-swapping occurs. When isint + is false, the indicated number of bytes at *u.ptr are + sent with no processing; the data must be in the format expected by + the server for binary transmission of the function's argument data + type. result_buf is the buffer in which to + place the return value. The caller must have allocated sufficient + space to store the return value. (There is no check!) The actual result + length will be returned in the integer pointed to by + result_len. If a 1, 2, or 4-byte integer result + is expected, set result_is_int to 1, otherwise + set it to 0. Setting result_is_int to 1 causes + libpq to byte-swap the value if necessary, so that it + is delivered as a proper int value for the client machine. + When result_is_int is 0, the binary-format byte string + sent by the server is returned unmodified. + + + + PQfn always returns a valid + PGresult pointer. The result status should be + checked before the result is used. The caller is responsible for + freeing the PGresult with + PQclear when it is no longer needed. + + + + Note that it is not possible to handle null arguments, null results, + nor set-valued results when using this interface. + + + + + + Asynchronous Notification + + + NOTIFY + in libpq + + + + PostgreSQL offers asynchronous notification + via the LISTEN and NOTIFY + commands. A client session registers its interest in a particular + notification condition with the LISTEN command (and + can stop listening with the UNLISTEN command). All + sessions listening on a particular condition will be notified + asynchronously when a NOTIFY command with that + condition name is executed by any session. No additional information + is passed from the notifier to the listener. Thus, typically, any + actual data that needs to be communicated is transferred through a + database table. Commonly, the condition name is the same as the + associated table, but it is not necessary for there to be any associated + table. + + + + libpq applications submit + LISTEN and UNLISTEN commands as + ordinary SQL commands. The arrival of NOTIFY + messages can subsequently be detected by calling + PQnotifies.PQnotifies + + + + The function PQnotifies + returns the next notification from a list of unhandled + notification messages received from the server. It returns a null pointer if + there are no pending notifications. Once a notification is + returned from PQnotifies, it is considered handled and will be + removed from the list of notifications. + + PGnotify *PQnotifies(PGconn *conn); + + typedef struct pgNotify { + char *relname; /* notification condition name */ + int be_pid; /* process ID of notifying server process */ + char *extra; /* notification parameter */ + } 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. (At present, the + extra field is unused and will always point + to an empty string.) + + + + 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. + + + + + + Functions Associated with the <command>COPY</command> Command + + + COPY + with libpq + + + + The COPY command in + PostgreSQL has options to read from or write + to the network connection used by libpq. + The functions described in this section allow applications to take + advantage of this capability by supplying or consuming copied data. + + + + The overall process is that the application first issues the SQL + COPY command via PQexec or one + of the equivalent functions. The response to this (if there is no + error in the command) will be a PGresult object bearing + a status code of PGRES_COPY_OUT or + PGRES_COPY_IN (depending on the specified copy + direction). The application should then use the functions of this + section to receive or transmit data rows. When the data transfer is + complete, another PGresult object is returned to indicate + success or failure of the transfer. Its status will be + PGRES_COMMAND_OK for success or + PGRES_FATAL_ERROR if some problem was encountered. + At this point further SQL commands can be issued via + PQexec. (It is not possible to execute other SQL + commands using the same connection while the COPY + operation is in progress.) + + + + If a COPY command is issued via + PQexec in a string that could contain additional + commands, the application must continue fetching results via + PQgetResult after completing the COPY + sequence. Only when PQgetResult returns + NULL is it certain that the PQexec + command string is done and it is safe to issue more commands. + + + + The functions of this section should be executed only after obtaining + a result status of PGRES_COPY_OUT or + PGRES_COPY_IN from PQexec or + PQgetResult. + + + + A PGresult object bearing one of these status values + carries some additional data about the COPY operation + that is starting. This additional data is available using functions + that are also used in connection with query results: + + + + + PQnfields + + PQnfields + with COPY + + + + + + Returns the number of columns (fields) to be copied. + + + + + + + PQbinaryTuples + + PQbinaryTuples + with COPY + + + + + + 0 indicates the overall copy format is textual (rows separated by + newlines, columns separated by separator characters, etc). 1 + indicates the overall copy format is binary. See for more information. + + + + + + + PQfformat + + PQfformat + with COPY + + + + + + Returns the format code (0 for text, 1 for binary) associated with + each column of the copy operation. The per-column format codes + will always be zero when the overall copy format is textual, but + the binary format can support both text and binary columns. + (However, as of the current implementation of COPY, + only binary columns appear in a binary copy; so the per-column + formats always match the overall format at present.) + + + + + + + + + These additional data values are only available when using protocol + 3.0. When using protocol 2.0, all these functions will return 0. + + + + + Functions for Sending <command>COPY</command> 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 + + PQputCopyData + + + + + + Sends data to the server during COPY_IN state. + + int PQputCopyData(PGconn *conn, + const char *buffer, + int nbytes); + + + + + Transmits the COPY data in the specified + buffer, of length nbytes, to the server. + The result is 1 if the data was sent, zero if it was not sent + because the attempt would block (this case is only possible if the + connection is in nonblocking mode), or -1 if an error occurred. + (Use PQerrorMessage to retrieve details if + the return value is -1. If the value is zero, wait for write-ready + and try again.) + + + + The application can divide the COPY data stream + into buffer loads of any convenient size. Buffer-load boundaries + have no semantic significance when sending. The contents of the + data stream must match the data format expected by the + COPY command; see for details. + + + + + + + PQputCopyEnd + + PQputCopyEnd + + + + + + Sends end-of-data indication to the server during COPY_IN state. + + int PQputCopyEnd(PGconn *conn, + const char *errormsg); + + + + + Ends the COPY_IN operation successfully if + errormsg is NULL. If + errormsg is not NULL then the + COPY is forced to fail, with the string pointed to by + errormsg used as the error message. (One should not + assume that this exact error message will come back from the server, + however, as the server might have already failed the + COPY for its own reasons. Also note that the option + to force failure does not work when using pre-3.0-protocol + connections.) + + + + The result is 1 if the termination data was sent, zero if it was + not sent because the attempt would block (this case is only possible + if the connection is in nonblocking mode), or -1 if an error + occurred. (Use PQerrorMessage to retrieve + details if the return value is -1. If the value is zero, wait for + write-ready and try again.) + + + + After successfully calling PQputCopyEnd, call + PQgetResult to obtain the final result status of the + COPY command. One can wait for this result to be + available in the usual way. Then return to normal operation. + + + + + + + + + Functions for Receiving <command>COPY</command> 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 + + 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 must 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. + + + + + + + + + Obsolete Functions for <command>COPY</command> + + + These functions represent older methods of handling COPY. + Although they still work, they are deprecated due to poor error handling, + inconvenient methods of detecting end-of-data, and lack of support for binary + or nonblocking transfers. + + + + + + PQgetline + + PQgetline + + + + + + Reads a newline-terminated line of characters (transmitted + by the server) into a buffer string of size length. + + int PQgetline(PGconn *conn, + char *buffer, + int length); + + + + + This function copies up to length-1 characters into + the buffer and converts the terminating newline into a zero byte. + PQgetline returns EOF at the + end of input, 0 if the entire line has been read, and 1 if the + buffer is full but the terminating newline has not yet been read. + + + Note that the application must check to see if a new line consists + of the two characters \., which indicates + that the server has finished sending the results of the + COPY command. If the application might receive + lines that are more than length-1 characters long, + care is needed to be sure it recognizes the \. + line correctly (and does not, for example, mistake the end of a + long data line for a terminator line). + + + + + + + PQgetlineAsync + + PQgetlineAsync + + + + + + Reads a row of COPY data (transmitted by the + server) into a buffer without blocking. + + int PQgetlineAsync(PGconn *conn, + char *buffer, + int bufsize); + + + + + This function is similar to PQgetline, but it can be used + by applications + that must read COPY data asynchronously, that is, without blocking. + Having issued the COPY command and gotten a PGRES_COPY_OUT + response, the + application should call PQconsumeInput and + PQgetlineAsync until the + end-of-data signal is detected. + + + Unlike PQgetline, this function takes + responsibility for detecting end-of-data. + + + + On each call, PQgetlineAsync will return data if a + complete data row is available in libpq's input buffer. + Otherwise, no data is returned until the rest of the row arrives. + The function returns -1 if the end-of-copy-data marker has been recognized, + or 0 if no data is available, or a positive number giving the number of + bytes of data returned. If -1 is returned, the caller must next call + PQendcopy, and then return to normal processing. + + + + The data returned will not extend beyond a data-row boundary. If possible + a whole row will be returned at one time. But if the buffer offered by + the caller is too small to hold a row sent by the server, then a partial + data row will be returned. With textual data this can be detected by testing + whether the last returned byte is \n or not. (In a binary + COPY, actual parsing of the COPY data format will be needed to make the + equivalent determination.) + The returned string is not null-terminated. (If you want to add a + terminating null, be sure to pass a bufsize one smaller + than the room actually available.) + + + + + + + PQputline + + PQputline + + + + + + Sends a null-terminated string to the server. Returns 0 if + OK and EOF if unable to send the string. + + int PQputline(PGconn *conn, + const char *string); + + + + + The COPY data stream sent by a series of calls + to PQputline has the same format as that + returned by PQgetlineAsync, except that + applications are not obliged to send exactly one data row per + PQputline call; it is okay to send a partial + line or multiple lines per call. + + + + + Before PostgreSQL protocol 3.0, it was necessary + for the application to explicitly send the two characters + \. as a final line to indicate to the server that it had + finished sending COPY data. While this still works, it is deprecated and the + special meaning of \. can be expected to be removed in a + future release. It is sufficient to call PQendcopy after + having sent the actual data. + + + + + + + + PQputnbytes + + PQputnbytes + + + + + + Sends a non-null-terminated string to the server. Returns + 0 if OK and EOF if unable to send the string. + + int PQputnbytes(PGconn *conn, + const char *buffer, + int nbytes); + + + + + This is exactly like PQputline, except that the data + buffer need not be null-terminated since the number of bytes to send is + specified directly. Use this procedure when sending binary data. + + + + + + + PQendcopy + + PQendcopy + + + + + + Synchronizes with the server. + + int PQendcopy(PGconn *conn); + + This function waits until the server has finished the copying. + It should either be issued when the last string has been sent + to the server using PQputline or when the + last string has been received from the server using + PGgetline. It must be issued or the server + will get out of sync with the client. Upon return + from this function, the server is ready to receive the next SQL + command. The return value is 0 on successful completion, + nonzero otherwise. (Use PQerrorMessage to + retrieve details if the return value is nonzero.) + + + + When using PQgetResult, the application should + respond to a PGRES_COPY_OUT result by executing + PQgetline repeatedly, followed by + PQendcopy after the terminator line is seen. + It should then return to the PQgetResult loop + until PQgetResult returns a null pointer. + Similarly a PGRES_COPY_IN result is processed + by a series of PQputline calls followed by + PQendcopy, then return to the + PQgetResult loop. This arrangement will + ensure that a COPY command embedded in a series + of SQL commands will be executed correctly. + + + + Older applications are likely to submit a COPY + via PQexec and assume that the transaction + is done after PQendcopy. This will work + correctly only if the COPY is the only + SQL command in the command string. + + + + + + + + + + + Control Functions + + + These functions control miscellaneous details of libpq's + behavior. + + + + + + PQclientEncoding + + PQclientEncoding + + + + + + Returns the client encoding. + + int PQclientEncoding(const PGconn *conn); + + + Note that it returns the encoding ID, not a symbolic string + such as EUC_JP. To convert an encoding ID to an encoding name, you + can use: + + +char *pg_encoding_to_char(int encoding_id); + + + + + + + + PQsetClientEncoding + + PQsetClientEncoding + + + + + + Sets the client encoding. + + int PQsetClientEncoding(PGconn *conn, const char *encoding); + + + conn is a connection to the server, + and encoding is the encoding you want to + use. If the function successfully sets the encoding, it returns 0, + otherwise -1. The current encoding for this connection can be + determined by using PQclientEncoding. + + + + + + + PQsetErrorVerbosity + + PQsetErrorVerbosity + + + + + + Determines the verbosity of messages returned by + PQerrorMessage and PQresultErrorMessage. + + typedef enum { + PQERRORS_TERSE, + PQERRORS_DEFAULT, + PQERRORS_VERBOSE + } PGVerbosity; + + PGVerbosity PQsetErrorVerbosity(PGconn *conn, PGVerbosity verbosity); + + + PQsetErrorVerbosity sets the verbosity mode, returning + the connection's previous setting. In TERSE mode, + returned messages include severity, primary text, and position only; + this will normally fit on a single line. The default mode produces + messages that include the above plus any detail, hint, or context + fields (these might span multiple lines). The VERBOSE + mode includes all available fields. Changing the verbosity does not + affect the messages available from already-existing + PGresult objects, only subsequently-created ones. + + + + + + + PQtrace + + PQtrace + + + + + + Enables tracing of the client/server communication to a debugging file stream. + + void PQtrace(PGconn *conn, FILE *stream); + + + + + + On Windows, if the libpq library and an application are + compiled with different flags, this function call will crash the + application because the internal representation of the FILE + pointers differ. Specifically, multithreaded/single-threaded, + release/debug, and static/dynamic flags should be the same for the + library and all applications using that library. + + + + + + + + + PQuntrace + + PQuntrace + + + + + + Disables tracing started by PQtrace. + + void PQuntrace(PGconn *conn); + + + + + + + + + + Miscellaneous Functions + + + As always, there are some functions that just don't fit anywhere. + + + + + + PQencryptPassword + + PQencryptPassword + + + + + + Prepares the encrypted form of a PostgreSQL password. + + char * PQencryptPassword(const char *passwd, const char *user); + + This function is intended to be used by client applications that + wish to send commands like ALTER USER joe PASSWORD + 'pwd'. It is good practice not to send the original cleartext + password in such a command, because it might be exposed in command + logs, activity displays, and so on. Instead, use this function to + convert the password to encrypted form before it is sent. The + arguments are the cleartext password, and the SQL name of the user + it is for. The return value is a string allocated by + malloc, or NULL if out of + memory. The caller can assume the string doesn't contain any + special characters that would require escaping. Use + PQfreemem to free the result when done with it. + + + + + + + + + Notice Processing + + + notice processing + in libpq + + + + Notice and warning messages generated by the server are not returned + by the query execution functions, since they do not imply failure of + the query. Instead they are passed to a notice handling function, and + execution continues normally after the handler returns. The default + notice handling function prints the message on + stderr, but the application can override this + behavior by supplying its own handling function. + + + + For historical reasons, there are two levels of notice handling, called + the notice receiver and notice processor. The default behavior is for + the notice receiver to format the notice and pass a string to the notice + processor for printing. However, an application that chooses to provide + its own notice receiver will typically ignore the notice processor + layer and just do all the work in the notice receiver. + + + + The function PQsetNoticeReceiver + notice + receiverPQsetNoticeReceiver sets or + examines the current notice receiver for a connection object. + Similarly, PQsetNoticeProcessor + notice + processorPQsetNoticeProcessor sets or + examines the current notice processor. + + + typedef void (*PQnoticeReceiver) (void *arg, const PGresult *res); + + PQnoticeReceiver + PQsetNoticeReceiver(PGconn *conn, + PQnoticeReceiver proc, + void *arg); + + typedef void (*PQnoticeProcessor) (void *arg, const char *message); + + PQnoticeProcessor + PQsetNoticeProcessor(PGconn *conn, + PQnoticeProcessor proc, + void *arg); + + + Each of these functions returns the previous notice receiver or + processor function pointer, and sets the new value. If you supply a + null function pointer, no action is taken, but the current pointer is + returned. + + + + When a notice or warning message is received from the server, or + generated internally by libpq, the notice + receiver function is called. It is passed the message in the form of + a PGRES_NONFATAL_ERROR + PGresult. (This allows the receiver to extract + individual fields using PQresultErrorField, or the complete + preformatted message using PQresultErrorMessage.) The same + void pointer passed to PQsetNoticeReceiver is also + passed. (This pointer can be used to access application-specific state + if needed.) + + + + The default notice receiver simply extracts the message (using + PQresultErrorMessage) and passes it to the notice + processor. + + + + The notice processor is responsible for handling a notice or warning + message given in text form. It is passed the string text of the message + (including a trailing newline), plus a void pointer that is the same + one passed to PQsetNoticeProcessor. (This pointer + can be used to access application-specific state if needed.) + + + + The default notice processor is simply: + +static void +defaultNoticeProcessor(void *arg, const char *message) +{ + fprintf(stderr, "%s", message); +} + + + + + Once you have set a notice receiver or processor, you should expect + that that function could be called as long as either the + PGconn object or PGresult objects made + from it exist. At creation of a PGresult, the + PGconn's current notice handling pointers are copied + into the PGresult for possible use by functions like + PQgetvalue. + + + + + + Environment Variables + + + environment variable + + + + The following environment variables can be used to select default + connection parameter values, which will be used by + PQconnectdb, PQsetdbLogin and + PQsetdb if no value is directly specified by the calling + code. These are useful to avoid hard-coding database connection + information into simple client applications, for example. + + + + + + PGHOST + + PGHOST sets the database server name. + If this begins with a slash, it specifies Unix-domain communication + rather than TCP/IP communication; the value is then the name of the + directory in which the socket file is stored (in a default installation + setup this would be /tmp). + + + + + + + PGHOSTADDR + + PGHOSTADDR specifies the numeric IP address of the database + server. This can be set instead of or in addition to PGHOST + to avoid DNS lookup overhead. See the documentation of + these parameters, under PQconnectdb above, for details + on their interaction. + + + When neither PGHOST nor PGHOSTADDR is set, + the default behavior is to connect using a local Unix-domain socket; or on + machines without Unix-domain sockets, libpq will + attempt to connect to localhost. + + + + + + + PGPORT + + PGPORT sets the TCP port number or Unix-domain socket + file extension for communicating with the + PostgreSQL server. + + + + + + + PGDATABASE + + PGDATABASE sets the + PostgreSQL database name. + + + + + + + PGUSER + + PGUSER sets the user name used to connect to the + database. + + + + + + + PGPASSWORD + + PGPASSWORD sets the password used if the server + demands password authentication. Use of this environment variable + is not recommended for security reasons (some operating systems + allow non-root users to see process environment variables via + ps); instead consider using the + ~/.pgpass file (see ). + + + + + + + PGPASSFILE + + PGPASSFILE specifies the name of the password file to + use for lookups. If not set, it defaults to ~/.pgpass + (see ). + + + + + + + PGSERVICE + + PGSERVICE + sets the service name to be looked up in + pg_service.conf. This offers a shorthand way + of setting all the parameters. + + + + + + + PGREALM + + PGREALM sets the Kerberos realm to use with + PostgreSQL, if it is different from the + local realm. If PGREALM is set, + libpq applications will attempt + authentication with servers for this realm and use separate ticket + files to avoid conflicts with local ticket files. This + environment variable is only used if Kerberos authentication is + selected by the server. + + + + + + + PGOPTIONS + + PGOPTIONS sets additional run-time options for the + PostgreSQL server. + + + + + + + PGSSLMODE + + PGSSLMODE determines whether and with what priority + an SSL connection will be negotiated with the server. + There are four modes: disable will attempt only an + unencrypted SSL connection; allow will + negotiate, trying first a non-SSL connection, then if + that fails, trying an SSL connection; prefer + (the default) will negotiate, trying first an SSL + connection, then if that fails, trying a regular non-SSL + connection; require will try only an SSL + connection. If PostgreSQL is compiled without SSL + support, using option require will cause an error, while + options allow and prefer will be accepted + but libpq will not in fact attempt an SSL + connection. + + + + + + + PGREQUIRESSL + + PGREQUIRESSL sets whether or not the connection must + be made over SSL. If set to 1, + libpq will refuse to connect if the server does not + accept an SSL connection (equivalent to + sslmode prefer). This option is deprecated + in favor of the sslmode setting, and is only available + if PostgreSQL is compiled with SSL support. + + + + + + + PGSSLKEY + + PGSSLKEY specifies the hardware token that stores the + secret key for the client certificate. The value of this variable + should consist of a colon-separated engine name (engines are + OpenSSL loadable modules) and an engine-specific key + identifier. If this is not set, the secret key must be kept in a + file. + + + + + + + PGKRBSRVNAME + + PGKRBSRVNAME sets the Kerberos service name to use + when authenticating with Kerberos 5 or GSSAPI. + + + + + + + PGGSSLIB + + PGGSSLIB sets the GSS library to use for GSSAPI + authentication. + + + + + + + PGCONNECT_TIMEOUT + + PGCONNECT_TIMEOUT sets the maximum number of seconds + that libpq will wait when attempting to + connect to the PostgreSQL server. If + unset or set to zero, libpq will wait + indefinitely. It is not recommended to set the timeout to less than + 2 seconds. + + + + + + + The following environment variables can be used to specify default + behavior for each PostgreSQL session. (See + also the + and + commands for ways to set default behavior on a per-user or per-database + basis.) + + + + + + PGDATESTYLE + + PGDATESTYLE sets the default style of date/time + representation. (Equivalent to SET datestyle TO + ....) + + + + + + + PGTZ + + PGTZ sets the default time zone. (Equivalent to + SET timezone TO ....) + + + + + + + PGCLIENTENCODING + + PGCLIENTENCODING sets the default client character + set encoding. (Equivalent to SET client_encoding TO + ....) + + + + + + + PGGEQO + + PGGEQO sets the default mode for the genetic query + optimizer. (Equivalent to SET geqo TO ....) + + + + + Refer to the SQL command for information on correct values for these + environment variables. + + + + The following environment variables determine internal behavior of + libpq; they override compiled-in defaults. + + + + + + PGSYSCONFDIR + + PGSYSCONFDIR sets the directory containing the + pg_service.conf file. + + + + + + + PGLOCALEDIR + + PGLOCALEDIR sets the directory containing the + locale files for message internationalization. + + + + - + - -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. - + + The Password File - - Main Functions + + password file + + + .pgpass + - - - -PQexecPQexec - - - Submits a command to the server - and waits for the result. - -PGresult *PQexec(PGconn *conn, const char *command); - - - - - Returns a PGresult pointer or possibly a null pointer. - A non-null pointer will generally be returned except in - out-of-memory conditions or serious errors such as inability - to send the command to the server. - If a null pointer is returned, it - should be treated like a PGRES_FATAL_ERROR result. - Use PQerrorMessage to get more information - about such errors. - - - - - -It is allowed to include multiple SQL commands (separated by semicolons) in -the command string. Multiple queries sent in a single PQexec -call are processed in a single transaction, unless there are explicit -BEGIN/COMMIT commands included in the query string to divide it into multiple -transactions. Note however that the returned PGresult -structure describes only the result of the last command executed from the -string. Should one of the commands fail, processing of the string stops with -it and the returned PGresult describes the error -condition. - - - - - -PQexecParamsPQexecParams - - - 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. - - - -If parameters are used, they are referred to in the command string -as $1, $2, etc. -nParams is the number of parameters supplied; it is the length -of the arrays paramTypes[], paramValues[], -paramLengths[], and paramFormats[]. (The -array pointers may 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. -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 may be null when there are no binary -parameters. -paramFormats[] specifies whether parameters are text (put a zero -in the array) or binary (put a one in the array). If the array pointer is -null then all parameters are presumed to be text. -resultFormat is 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 may be separated from the command string, thus -avoiding the need for tedious and error-prone quoting and escaping. - -Unlike PQexec, PQexecParams allows at most one SQL -command in the given string. (There can be semicolons in it, but not more -than one nonempty command.) This is a limitation of the underlying protocol, -but has some usefulness as an extra defense against SQL-injection attacks. - - - - -Specifying parameter types via OIDs is tedious, particularly if you prefer -not to hard-wire particular OID values into your program. However, you can -avoid doing so even in cases where the server by itself cannot determine the -type of the parameter, or chooses a different type than you want. In the -SQL command text, attach an explicit cast to the parameter symbol to show what -data type you will send. For example, - -select * from mytable where x = $1::bigint; - -This forces parameter $1 to be treated as bigint, whereas -by default it would be assigned the same type as x. Forcing the -parameter type decision, either this way or by specifying a numeric type OID, -is strongly recommended when sending parameter values in binary format, because -binary format has less redundancy than text format and so there is less chance -that the server will detect a type mismatch mistake for you. - - - - - - -PQpreparePQprepare - - - 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 may 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 -may 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 may use parameter symbols with numbers higher than -nParams; data types will be inferred for these symbols as -well. - - - -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. - - - -At present, there is no way to determine the actual data type inferred for -any parameters whose types are not specified in paramTypes[]. -This is a libpq omission that will probably be rectified -in a future release. - - - - - -Prepared statements for use with PQexecPrepared can also -be created by executing SQL statements. (But PQprepare -is more flexible since it does not require parameter types to be -pre-specified.) Also, although there is no libpq -function for deleting a prepared statement, the SQL statement can -be used for that purpose. - - - - - -PQexecPreparedPQexecPrepared - - - 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). - - - - - - - -The -PGresultPGresult -structure encapsulates the result returned by the server. -libpq application programmers should be -careful to maintain the PGresult abstraction. -Use the accessor functions below to get at the contents of -PGresult. Avoid directly referencing the -fields of the PGresult structure because they -are subject to change in the future. - - - -PQresultStatusPQresultStatus - - - Returns the result status of the command. - -ExecStatusType PQresultStatus(const PGresult *res); - - - - -PQresultStatus can return one of the following values: - - - - PGRES_EMPTY_QUERY - - The string sent to the server was empty. - - - - - PGRES_COMMAND_OK - - Successful completion of a command returning no data. - - - - - PGRES_TUPLES_OK - - Successful completion of a command returning data (such as - a SELECT or SHOW). - - - - - PGRES_COPY_OUT - - Copy Out (from server) data transfer started. - - - - - PGRES_COPY_IN - - Copy In (to server) data transfer started. - - - - - PGRES_BAD_RESPONSE - - The server's response was not understood. - - - - - PGRES_NONFATAL_ERROR - - A nonfatal error (a notice or warning) occurred. - - - - - PGRES_FATAL_ERROR - - A fatal error occurred. - - - - -If the result status is PGRES_TUPLES_OK, then the -functions described below can be used to retrieve the rows returned by -the query. Note that a SELECT command that happens -to retrieve zero rows still shows PGRES_TUPLES_OK. -PGRES_COMMAND_OK is for commands that can never -return rows (INSERT, UPDATE, -etc.). A response of PGRES_EMPTY_QUERY may 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 ). - - - - - -PQresStatusPQresStatus - - - 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); - - - - - - -PQresultErrorMessagePQresultErrorMessage - - -Returns the error message associated with the command, or an empty string -if there was no error. - -char *PQresultErrorMessage(const PGresult *res); - -If there was an error, the returned string will include a trailing newline. -The caller should not free the result directly. It will be freed when the -associated PGresult handle is passed to -PQclear. - - - -Immediately following a PQexec or PQgetResult -call, PQerrorMessage (on the connection) will return the same -string as PQresultErrorMessage (on the result). However, a -PGresult will retain its error message -until destroyed, whereas the connection's error message will change when -subsequent operations are done. Use PQresultErrorMessage when you want to -know the status associated with a particular PGresult; use PQerrorMessage -when you want to know the status from the latest operation on the connection. - - - - - -PQresultErrorFieldPQresultErrorField - - -Returns an individual field of an error report. - -char *PQresultErrorField(const PGresult *res, int fieldcode); - -fieldcode is an error field identifier; see the symbols -listed below. NULL is returned if the -PGresult is not an error or warning result, -or does not include the specified field. Field values will normally -not include a trailing newline. The caller should not free the -result directly. It will be freed when the -associated PGresult handle is passed to -PQclear. - - - -The following field codes are available: - - - -PG_DIAG_SEVERITY - - -The severity; the field contents are ERROR, -FATAL, or PANIC (in an error message), or -WARNING, NOTICE, DEBUG, -INFO, or LOG (in a notice message), or a -localized translation of one of these. Always present. - - - - - - - error codes - libpq - -PG_DIAG_SQLSTATE - - - -The SQLSTATE code for the error. The SQLSTATE code identifies the type -of error that has occurred; it can be used by front-end applications -to perform specific operations (such as error handling) in response to -a particular database error. For a list of the possible SQLSTATE -codes, see . This field is not -localizable, and is always present. - - - - - -PG_DIAG_MESSAGE_PRIMARY - - -The primary human-readable error message (typically one line). Always -present. - - - - - -PG_DIAG_MESSAGE_DETAIL - - -Detail: an optional secondary error message carrying more detail about -the problem. May 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. May run to multiple lines. - - - - - -PG_DIAG_STATEMENT_POSITION - - -A string containing a decimal integer indicating an error cursor -position as an index into the original statement string. The first -character has index 1, and positions are measured in characters not -bytes. - - - - - -PG_DIAG_INTERNAL_POSITION - - -This is defined the same as the PG_DIAG_STATEMENT_POSITION -field, but it is used when the cursor position refers to an internally -generated command rather than the one submitted by the client. -The PG_DIAG_INTERNAL_QUERY field will always appear when this field -appears. - - - - - -PG_DIAG_INTERNAL_QUERY - - -The text of a failed internally-generated command. -This could be, for example, a SQL query issued by a PL/pgSQL function. - - - - - -PG_DIAG_CONTEXT - - -An indication of the context in which the error occurred. -Presently this includes a call stack traceback of active -procedural language functions and internally-generated queries. -The trace is one entry per line, most recent first. - - - - - -PG_DIAG_SOURCE_FILE - - -The file name of the source-code location where the error was -reported. - - - - - -PG_DIAG_SOURCE_LINE - - -The line number of the source-code location where the error was -reported. - - - - - -PG_DIAG_SOURCE_FUNCTION - - -The name of the source-code function reporting the error. - - - - - - - -The client is responsible for formatting displayed information to meet -its needs; in particular it should break long lines as needed. -Newline characters appearing in the error message fields should be -treated as paragraph breaks, not line breaks. - - - -Errors generated internally by libpq will -have severity and primary message, but typically no other fields. -Errors returned by a pre-3.0-protocol server will include severity and -primary message, and sometimes a detail message, but no other fields. - - - -Note that error fields are only available from -PGresult objects, not -PGconn objects; there is no -PQerrorField function. - - - - - -PQclearPQclear - - - Frees the storage associated with a PGresult. - Every command result should be freed via PQclear when - it is no longer needed. - -void PQclear(PGresult *res); - - - - - You can keep a PGresult object around for as long as you - need it; it does not go away when you issue a new command, - nor even if you close the connection. To get rid of it, - you must call PQclear. Failure to do this will - result in memory leaks in your application. - - - - - -PQmakeEmptyPGresultPQmakeEmptyPGresult - - - 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. Note -that PQclear should eventually be called on the -object, just as with a PGresult returned by -libpq itself. - - - - - - - - - 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). For objects with other status -values they will act as though the result has zero rows and zero columns. - - - - -PQntuplesPQntuples - - - Returns the number of rows (tuples) - in the query result. - -int PQntuples(const PGresult *res); - - - - - - -PQnfieldsPQnfields - - - Returns the number of columns (fields) - in each row of the query result. - -int PQnfields(const PGresult *res); - - - - - - -PQfnamePQfname - - -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. - - - - - -PQfnumberPQfnumber - - - Returns the column number associated with the given column name. - -int PQfnumber(const PGresult *res, - const char *column_name); - - + + 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). + - - -1 is returned if the given name does not match any column. - + + 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. + - - 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 - - - - - - -PQftablePQftable - - - 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. - - - - - -PQftablecolPQftablecol - - - 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. - - - - - -PQfformatPQfformat - - - 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.) - - - - - -PQftypePQftype - - - 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. - - - - - -PQfmodPQfmod - - - 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. - - - - - -PQfsizePQfsize - - - 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. - - - - - -PQbinaryTuplesPQbinaryTuples - - -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). - - - - - -PQgetvaluePQgetvalue - - - 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. - - - - - -PQgetisnullPQgetisnull -null valuein libpq - - Tests a field for a null value. - Row and column numbers start at 0. - -int PQgetisnull(const PGresult *res, - int row_number, - int column_number); - - - - -This function returns 1 if the field is null and 0 if -it contains a non-null value. (Note that PQgetvalue -will return an empty string, not a null pointer, for a null field.) - - - - - -PQgetlengthPQgetlength - - - 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. - - - - - -PQprintPQprint - - - 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); + + 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. + + -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. - - - - - - - - Retrieving Result Information for Other Commands - - -These functions are used to extract information from -PGresult objects that are not SELECT -results. - - - - -PQcmdStatusPQcmdStatus - - - 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 may 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. - - - - - -PQcmdTuplesPQcmdTuples - - - 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 an 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. - - - - - -PQoidValuePQoidValue - - - Returns the OIDOIDin - libpq of the inserted row, if the SQL - command was an INSERT that inserted exactly one - row into a table that has OIDs, or a EXECUTE of - a prepared query containing a suitable INSERT - statement. Otherwise, this function returns - InvalidOid. This function will also - return InvalidOid if the table affected - by the INSERT statement does not contain OIDs. - -Oid PQoidValue(const PGresult *res); - - - - - - -PQoidStatusPQoidStatus - - - Returns a string with the OID of the inserted row, if the - SQL command was an - INSERT that inserted exactly one row, or - a EXECUTE of a prepared statement - consisting of a suitable INSERT. (The string will be - 0 if the INSERT did not - insert exactly one row, or if the target table does not have - OIDs.) If the command was not an INSERT, - returns an empty string. - -char *PQoidStatus(const PGresult *res); - - - - -This function is deprecated in favor of PQoidValue. -It is not thread-safe. - - - - - - - - - Escaping Strings for Inclusion in SQL Commands - - PQescapeString - escaping strings - - -PQescapeString 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. -PQescapeString performs this operation. - - - - -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. - -size_t PQescapeString (char *to, const char *from, size_t length); - - - - -The parameter from points to the first character of the string -that is to be escaped, and the length parameter gives the -number of characters 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, PQescapeString 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 character than twice -the value of length, otherwise the behavior is -undefined. A call to PQescapeString writes an escaped -version of the from string to the to -buffer, replacing special characters so that they cannot cause any -harm, and adding a terminating zero byte. The single quotes that -must surround PostgreSQL string literals are not -included in the result string; they should be provided in the SQL -command that the result is inserted into. - - -PQescapeString returns the number of characters written -to to, not including the terminating zero byte. - - -Behavior is undefined if the to and from -strings overlap. - - - - - - Escaping Binary Strings for Inclusion in SQL Commands - - - bytea - in libpq + + The Connection Service File + + + connection service file + + + pg_service.conf - - - PQescapeByteaPQescapeBytea - - Escapes binary data for use within an SQL command with the type - bytea. As with PQescapeString, - this is only used when inserting data directly into an SQL command string. - -unsigned char *PQescapeBytea(const unsigned char *from, - size_t from_length, - size_t *to_length); - - - - - Certain byte values must be escaped (but all - byte values can be escaped) when used as part - of a bytea literal in an SQL - statement. In general, to escape a byte, it is converted into the - three digit octal number equal to the octet value, and preceded by - two backslashes. The single quote (') and backslash - (\) characters have special alternative escape - sequences. See for more - information. PQescapeBytea performs this - operation, escaping only the minimally required bytes. + 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 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. The result string length includes the terminating - zero byte of the result. + To use this feature, copy + share/pg_service.conf.sample to + etc/pg_service.conf and edit the file to add + service names and parameters. This file can be used for client-only + installs too. The file's location can also be specified by the + PGSYSCONFDIR environment variable. + + + + + LDAP Lookup of Connection Parameters + + + LDAP connection parameter lookup + - PQescapeBytea returns an escaped version of the - from parameter binary string in memory - allocated with malloc() (a null pointer is returned if - memory could not be allocated). This memory must 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. + If libpq has been compiled with LDAP support (option + 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. - - - - PQunescapeByteaPQunescapeBytea - - Converts an escaped 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. + LDAP connection parameter lookup uses the connection service file + pg_service.conf (see ). A line in a + pg_service.conf stanza that starts with + ldap:// will be recognized as an LDAP URL and an + LDAP query will be performed. The result must be a list of + keyword = value pairs which will be used to set + connection options. The URL must conform to RFC 1959 and be of the + form + + ldap://[hostname[:port]]/search_base?attribute?search_scope?filter + + where hostname defaults to + localhost and port + defaults to 389. + - -unsigned char *PQunescapeBytea(const unsigned char *from, size_t *to_length); - - - - - The from parameter points to an escaped 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. + + 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. - - - - PQfreememPQfreemem - - Frees memory allocated by libpq. - -void PQfreemem(void *ptr); - - - - - Frees memory allocated by libpq, particularly - PQescapeBytea, - PQunescapeBytea, - and PQnotifies. - It is needed by Microsoft Windows, which cannot free memory across - DLLs, unless multithreaded DLLs ( in VC6) are used. - On other platforms, this function is the same as the standard library function free(). + 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) + - - - - - - - - -Asynchronous Command Processing - - nonblocking connection - - -The PQexec function is adequate for submitting commands in -normal, synchronous -applications. It has a couple of deficiencies, however, that can be of importance to some users: - - - - -PQexec waits for the command to be completed. The application may 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, and -PQsendQueryPrepared, -which can be used with PQgetResult to duplicate the -functionality of -PQexecParams, -PQprepare, and -PQexecPrepared -respectively. - - - -PQsendQueryPQsendQuery - - - 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 may not be called - again (on the same connection) until PQgetResult has returned a null pointer, - indicating that the command is done. - - - - - -PQsendQueryParamsPQsendQueryParams - - - 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); - + + 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=*) + + - 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. - - - - - -PQsendPreparePQsendPrepare - - - 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. - - - - - -PQsendQueryPreparedPQsendQueryPrepared - - - 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. - - - - - -PQgetResultPQgetResult - - - Waits for the next result from a prior - PQsendQuery, - PQsendQueryParams, - PQsendPrepare, or - PQsendQueryPrepared call, - and returns it. A null pointer is returned when the command is complete - and there will be no more results. - -PGresult *PQgetResult(PGconn *conn); - - - - - PQgetResult must be called repeatedly until it returns a null pointer, - indicating that the command is done. (If called when no command is - active, PQgetResult will just return a null pointer at once.) - Each non-null result from PQgetResult should be processed using - the same PGresult accessor functions previously described. - Don't forget to free each result object with PQclear when done with it. - Note that PQgetResult will block only if a command is active and the - necessary response data has not yet been read by PQconsumeInput. - - - - - - - -Using PQsendQuery and PQgetResult -solves one of PQexec's problems: -If a command string contains multiple SQL commands, the results of those -commands can be obtained individually. (This allows a simple form of -overlapped processing, by the way: the client can be handling the -results of one command while the server is still working on later -queries in the same command string.) However, calling PQgetResult will -still cause the client to block until the server completes the -next SQL command. This can be avoided by proper use of two -more functions: - - - -PQconsumeInputPQconsumeInput - - - 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 may check -PQisBusy and/or PQnotifies to see if -their state has changed. - - -PQconsumeInput may 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. - - - - - -PQisBusyPQisBusy - - -Returns 1 if a command is busy, that is, PQgetResult would block -waiting for input. A 0 return indicates that PQgetResult can -be called with assurance of not blocking. - -int PQisBusy(PGconn *conn); - - - - -PQisBusy will not itself attempt to read data from the server; -therefore PQconsumeInput must be invoked first, or the busy -state will never end. - - - - - - - -A typical application using these functions will have a main loop that uses -select() or poll() to wait for all the -conditions that it must -respond to. One of the conditions will be input available from the server, -which in terms of select() means readable data on the file -descriptor identified by PQsocket. -When the main loop detects input ready, it should call -PQconsumeInput to read the input. It can then call -PQisBusy, followed by PQgetResult -if PQisBusy returns false (0). It can also call -PQnotifies to detect NOTIFY messages (see ). - - - -A client that uses -PQsendQuery/PQgetResult can -also attempt to cancel a command that is still being processed by the -server; see . But regardless of the return value -of PQcancel, the application must continue with the -normal result-reading sequence using PQgetResult. -A successful cancellation will simply cause the command to terminate -sooner than it would have otherwise. - - - -By using the functions described above, it is possible to avoid blocking -while waiting for input from the database server. However, it is still -possible that the application will block waiting to send output to the -server. This is relatively uncommon but can happen if very long SQL commands -or data values are sent. (It is much more probable if the application -sends data via COPY IN, however.) To prevent this possibility and achieve -completely nonblocking database operation, the following additional -functions may be used. - - - - PQsetnonblockingPQsetnonblocking - - - Sets the nonblocking status of the connection. - -int PQsetnonblocking(PGconn *conn, int arg); - - + + SSL Support - - 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. - - - + + SSL + - -PQisnonblockingPQisnonblocking - - - 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. - - - - - -PQflushPQflush - - -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. - - - - - -Cancelling Queries in Progress - -cancelingSQL command - - -A client application can request cancellation of -a command that is still being processed by the -server, using the functions described in this section. - - - -PQgetCancelPQgetCancel - - - Creates a data structure containing the information needed to cancel - a command issued through a particular database connection. - -PGcancel *PQgetCancel(PGconn *conn); - - - - -PQgetCancel creates a -PGcancelPGcancel object given -a PGconn connection object. It will return NULL if the -given conn is NULL or an invalid connection. The -PGcancel object is an opaque structure that is not meant -to be accessed directly by the application; it can only be passed to -PQcancel or PQfreeCancel. - - - - - -PQfreeCancelPQfreeCancel - - - Frees a data structure created by PQgetCancel. - -void PQfreeCancel(PGcancel *cancel); - - - - -PQfreeCancel frees a data object previously created -by PQgetCancel. - - - - - -PQcancelPQcancel - - - Requests that the server abandon - processing of the current command. - -int PQcancel(PGcancel *cancel, char *errbuf, int errbufsize); - - - - -The return value is 1 if the cancel request was successfully -dispatched and 0 if not. If not, errbuf is filled with an error -message explaining why not. errbuf must be a char array of size -errbufsize (the recommended size is 256 bytes). - - - -Successful dispatch is no guarantee that the request will have any effect, -however. If the cancellation is effective, the current command will terminate -early and return an error result. If the cancellation fails (say, because the -server was already done processing the command), then there will be no visible -result at all. - - - -PQcancel can safely be invoked from a signal handler, -if the errbuf is a local variable in the signal handler. The -PGcancel object is read-only as far as -PQcancel is concerned, so it can also be invoked from a -thread that is separate from the one manipulating the PGconn -object. - - - - - - - -PQrequestCancelPQrequestCancel - - - Requests that the server abandon - processing of the current command. - -int PQrequestCancel(PGconn *conn); - - - - -PQrequestCancel is a deprecated variant of -PQcancel. It operates directly on the -PGconn object, and in case of failure stores the -error message in the PGconn object (whence it can be -retrieved by PQerrorMessage). Although the -functionality is the same, this approach creates hazards for multiple-thread -programs and signal handlers, since it is possible that overwriting the -PGconn's error message will mess up the operation currently -in progress on the connection. - - - - - - - - - -The Fast-Path Interface - -fast path - - -PostgreSQL provides a fast-path interface to send -simple function calls to the server. - - - - -This interface is somewhat obsolete, as one may achieve similar performance -and greater functionality by setting up a prepared statement to define the -function call. Then, executing the statement with binary transmission of -parameters and results substitutes for a fast-path function call. - - - - -The function PQfnPQfn -requests execution of a server function via the fast-path interface: - -PGresult *PQfn(PGconn *conn, - int fnid, - int *result_buf, - int *result_len, - int result_is_int, - const PQArgBlock *args, - int nargs); + + PostgreSQL has native support for using SSL + connections to encrypt client/server communications for increased + security. See for details about the server-side + SSL functionality. + -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. - - - - - -Asynchronous Notification + + 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. + - - NOTIFY - in libpq - + + To verify the server certificate is trustworthy, place certificates of + the certificate authorities (CA) you trust in the + file ~/.postgresql/root.crt in the user's home directory. + (On Microsoft Windows the file is named + %APPDATA%\postgresql\root.crt.) + libpq will then verify that the server's + certificate is signed by one of the trusted certificate authorities. + The SSL connection will fail if the server does not present a trusted + certificate. Certificate Revocation List (CRL) entries are also checked + if the file ~/.postgresql/root.crl exists + (%APPDATA%\postgresql\root.crl on Microsoft + Windows). + - -PostgreSQL offers asynchronous notification via the -LISTEN and NOTIFY commands. A client session registers its interest in a particular -notification condition with the LISTEN command (and can stop listening -with the UNLISTEN command). All sessions listening on a -particular condition will be notified asynchronously when a NOTIFY command with that -condition name is executed by any session. No additional information is -passed from the notifier to the listener. Thus, typically, any actual data -that needs to be communicated is transferred through a database table. -Commonly, the condition name is the same as the associated table, but it is -not necessary for there to be any associated table. - - - -libpq applications submit -LISTEN and UNLISTEN commands as -ordinary SQL commands. The arrival of NOTIFY -messages can subsequently be detected by calling -PQnotifies.PQnotifies - - - -The function PQnotifies - returns the next notification from a list of unhandled - notification messages received from the server. It returns a null pointer if - there are no pending notifications. Once a notification is - returned from PQnotifies, it is considered handled and will be - removed from the list of notifications. - -PGnotify *PQnotifies(PGconn *conn); + + 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, unless the secret key for the certificate is stored in a + hardware token, as specified by PGSSLKEY. 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. + -typedef struct pgNotify { - char *relname; /* notification condition name */ - int be_pid; /* process ID of notifying server process */ - char *extra; /* notification parameter */ -} 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. -(At present, the extra field is unused and will -always point to an empty string.) - - - - 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. - - - - - -Functions Associated with the <command>COPY</command> Command - - - COPY - with libpq - - - - The COPY command in PostgreSQL - has options to read from or write to the network connection used by - libpq. The functions described in this section - allow applications to take advantage of this capability by supplying or - consuming copied data. - - - - The overall process is that the application first issues the SQL - COPY command via PQexec or one - of the equivalent functions. The response to this (if there is no error - in the command) will be a PGresult object bearing a status - code of PGRES_COPY_OUT or PGRES_COPY_IN - (depending on the specified copy direction). The application should then - use the functions of this section to receive or transmit data rows. - When the data transfer is complete, another PGresult object - is returned to indicate success or failure of the transfer. Its status - will be PGRES_COMMAND_OK for success or - PGRES_FATAL_ERROR if some problem was encountered. - At this point further SQL commands may 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: - - - -PQnfieldsPQnfieldswith COPY - - - Returns the number of columns (fields) to be copied. - - - - - -PQbinaryTuplesPQbinaryTupleswith COPY - - - 0 indicates the overall copy format is textual (rows - separated by newlines, columns separated by separator - characters, etc). - 1 indicates the overall copy format is binary. - See - for more information. - - - - - -PQfformatPQfformatwith COPY - - - Returns the format code (0 for text, 1 for binary) associated - with each column of the copy operation. The per-column format - codes will always be zero when the overall copy format is textual, - but the binary format can support both text and binary columns. - (However, as of the current implementation of COPY, - only binary columns appear in a binary copy; so the per-column - formats always match the overall format at present.) - - - - - - - - -These additional data values are only available when using protocol 3.0. -When using protocol 2.0, all these functions will return 0. - - - - - Functions for Sending <command>COPY</command> 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. - - - - -PQputCopyDataPQputCopyData - - - 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 may divide the COPY data stream into buffer loads of any -convenient size. Buffer-load boundaries have no semantic significance when -sending. The contents of the data stream must match the data format expected -by the COPY command; see - for details. - - - - - -PQputCopyEndPQputCopyEnd - - - 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 may wait for -this result to be available in the usual way. Then return to normal -operation. - - - - - - - - - Functions for Receiving <command>COPY</command> 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. - - - - -PQgetCopyDataPQgetCopyData - - - 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 must 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 may wait for -this result to be available in the usual way. Then return to normal -operation. - - - - - - - - - Obsolete Functions for <command>COPY</command> - - - 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. - - - - -PQgetlinePQgetline - - - 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). - - - - - -PQgetlineAsyncPQgetlineAsync - - - 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.) - - - - - -PQputlinePQputline - - -Sends a null-terminated string to the server. -Returns 0 if OK and EOF if unable to send the string. - -int PQputline(PGconn *conn, - const char *string); - - - - -The COPY data stream sent by a series of calls to -PQputline has the same format as that returned by -PQgetlineAsync, except that applications are not -obliged to send exactly one data row per PQputline -call; it is okay to send a partial line or multiple lines per call. - - - - -Before PostgreSQL protocol 3.0, it was necessary -for the application to explicitly send the two characters -\. as a final line to indicate to the server that it had -finished sending COPY data. While this still works, it is deprecated and the -special meaning of \. can be expected to be removed in a -future release. It is sufficient to call PQendcopy after -having sent the actual data. - - - - - - -PQputnbytesPQputnbytes - - -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. - - - - - -PQendcopyPQendcopy - - - 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. - - - - - - - - - - -Control Functions - - -These functions control miscellaneous details of -libpq's behavior. - - - - -PQsetErrorVerbosityPQsetErrorVerbosity - - -Determines the verbosity of messages returned by -PQerrorMessage and PQresultErrorMessage. - -typedef enum { - PQERRORS_TERSE, - PQERRORS_DEFAULT, - PQERRORS_VERBOSE -} PGVerbosity; + + If the environment variable PGSSLKEY is set, its value + should consist of a colon-separated engine name and key identifier. In + this case, libpq will load the specified + engine, i.e. the OpenSSL module which supports special + hardware, and reference the key with the specified identifier. + Identifiers are engine-specific. Typically, cryptography hardware tokens + do not reveal secret keys to the application. Instead, applications + delegate all cryptography operations which require the secret key to + the hardware token. + -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 may 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. - - - - - -PQtracePQtrace - - - Enables tracing of the client/server communication to a debugging file stream. - -void PQtrace(PGconn *conn, FILE *stream); - - - - -On Windows, if the libpq library and an application are -compiled with different flags, this function call will crash the -application because the internal representation of the FILE -pointers differ. Specifically, multithreaded/single-threaded, -release/debug, and static/dynamic flags should be the same for the -library and all applications using that library. - - - - - - -PQuntracePQuntrace - - - Disables tracing started by PQtrace. - -void PQuntrace(PGconn *conn); - - - - - - - - - -Miscellaneous Functions - - -As always, there are some functions that just don't fit anywhere. - - - - -PQencryptPasswordPQencryptPassword - - -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 malloc'd string, or NULL if -out-of-memory. The caller may assume the string doesn't contain any special -characters that would require escaping. Use PQfreemem to free -the result when done with it. - - - - - - - - -Notice Processing - - - notice processing - in libpq - - - -Notice and warning messages generated by the server are not returned by the -query execution functions, since they do not imply failure of the query. -Instead they are passed to a notice handling function, and execution continues -normally after the handler returns. The default notice handling function -prints the message on stderr, but the application can -override this behavior by supplying its own handling function. - - - -For historical reasons, there are two levels of notice handling, called the -notice receiver and notice processor. The default behavior is for the notice -receiver to format the notice and pass a string to the notice processor -for printing. However, an application that chooses to provide its own notice -receiver will typically ignore the notice processor layer and just do all the -work in the notice receiver. - - - -The function PQsetNoticeReceiver -notice receiverPQsetNoticeReceiver -sets or examines the current notice receiver for a connection object. -Similarly, PQsetNoticeProcessor -notice processorPQsetNoticeProcessor -sets or examines the current notice processor. + + If you are using SSL inside your application (in addition + to inside libpq), you can use + PQinitSSL(int) to tell libpq + that the SSL library has already been initialized by your + application. + + See + for details on the SSL API. + - -typedef void (*PQnoticeReceiver) (void *arg, const PGresult *res); + + Libpq/Client SSL File Usage + + + + File + Contents + Effect + + + + + + + ~/.postgresql/postgresql.crt + client certificate + requested by server + + + + ~/.postgresql/postgresql.key + client private key + proves client certificate sent by owner; does not indicate + certificate owner is trustworthy + + + + ~/.postgresql/root.crt + trusted certificate authorities + checks 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 + + + + +
-PQnoticeReceiver -PQsetNoticeReceiver(PGconn *conn, - PQnoticeReceiver proc, - void *arg); + -typedef void (*PQnoticeProcessor) (void *arg, const char *message); -PQnoticeProcessor -PQsetNoticeProcessor(PGconn *conn, - PQnoticeProcessor proc, - void *arg); - + + Behavior in Threaded Programs -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. - - - - - -Environment Variables - - - environment variable - - - -The following environment variables can be used to select default -connection parameter values, which will be used by -PQconnectdb, PQsetdbLogin and -PQsetdb if no value is directly specified by the calling -code. These are useful to avoid hard-coding database connection -information into simple client applications, for example. - - - - - - PGHOST - -PGHOST sets the database server name. -If this begins with a slash, it specifies Unix-domain communication -rather than TCP/IP communication; the value is then the name of the -directory in which the socket file is stored (in a default installation -setup this would be /tmp). - - - - - - PGHOSTADDR - -PGHOSTADDR specifies the numeric IP address of the database -server. This can be set instead of or in addition to PGHOST -to avoid DNS lookup overhead. See the documentation of -these parameters, under PQconnectdb above, for details -on their interaction. - - -When neither PGHOST nor PGHOSTADDR is set, -the default behavior is to connect using a local Unix-domain socket; or on -machines without Unix-domain sockets, libpq will -attempt to connect to localhost. - - - - - - PGPORT - -PGPORT sets the TCP port number or Unix-domain -socket file extension for communicating with the -PostgreSQL server. - - - - - - PGDATABASE - -PGDATABASE sets the -PostgreSQL database name. - - - - - - PGUSER - -PGUSER -sets the user name used to connect to the database. - - - - - - PGPASSWORD - -PGPASSWORD -sets the password used if the server demands password -authentication. Use of this environment variable is not -recommended for security reasons (some operating systems -allow non-root users to see process environment variables via -ps); instead consider using the -~/.pgpass file (see ). - - - - - - PGPASSFILE - -PGPASSFILE -specifies the name of the password file to use for lookups. -If not set, it defaults to ~/.pgpass -(see ). - - - - - - PGSERVICE - -PGSERVICE -sets the service name to be looked up in pg_service.conf. -This offers a shorthand way of setting all the parameters. - - - - - - PGREALM - -PGREALM sets the Kerberos realm to use with -PostgreSQL, if it is different from the local realm. -If PGREALM is set, libpq -applications will attempt authentication with servers for this realm and use -separate ticket files to avoid conflicts with local -ticket files. This environment variable is only -used if Kerberos authentication is selected by the server. - - - - - - PGOPTIONS - -PGOPTIONS sets additional run-time options for -the PostgreSQL server. - - - - - - PGSSLMODE - -PGSSLMODE determines whether and with what priority an -SSL connection will be negotiated with the server. There are -four modes: disable will attempt only an unencrypted -SSL connection; allow will negotiate, -trying first a non-SSL connection, then if that fails, -trying an SSL connection; prefer -(the default) will negotiate, trying first an SSL -connection, then if that fails, trying a regular non-SSL -connection; require will try only an SSL -connection. If PostgreSQL is compiled without SSL support, -using option require will cause an error, while options -allow and prefer will be accepted but -libpq will not in fact attempt an SSL -connection. - - - - - - PGREQUIRESSL - -PGREQUIRESSL sets whether or not the connection must be -made over SSL. If set to -1, libpq -will refuse to connect if the server does not accept -an SSL connection (equivalent to sslmode -prefer). -This option is deprecated in favor of the sslmode -setting, and is only available if -PostgreSQL is compiled with SSL support. - - - - - - PGKRBSRVNAME - -PGKRBSRVNAME sets the Kerberos service name to use when -authenticating with Kerberos 5. - - - - - - PGCONNECT_TIMEOUT - -PGCONNECT_TIMEOUT sets the maximum number of seconds -that libpq will wait when attempting to -connect to the PostgreSQL server. If unset -or set to zero, libpq will wait indefinitely. -It is not recommended to set the timeout to less than 2 seconds. - - - - - - -The following environment variables can be used to specify default -behavior for each PostgreSQL session. -(See also the - and - -commands for ways to set default behavior on a per-user or per-database -basis.) - - - - - - PGDATESTYLE - -PGDATESTYLE -sets the default style of date/time representation. -(Equivalent to SET datestyle TO ....) - - - - - - PGTZ - -PGTZ -sets the default time zone. -(Equivalent to SET timezone TO ....) - - - - - - PGCLIENTENCODING - -PGCLIENTENCODING -sets the default client character set encoding. -(Equivalent to SET client_encoding TO ....) - - - - - - PGGEQO - -PGGEQO -sets the default mode for the genetic query optimizer. -(Equivalent to SET geqo TO ....) - - - - -Refer to the SQL command - -for information on correct values for these environment variables. - - - -The following environment variables determine internal behavior of -libpq; they override compiled-in defaults. - - - - - - PGSYSCONFDIR - -PGSYSCONFDIR -sets the directory containing the pg_service.conf file. - - - - - - PGLOCALEDIR - -PGLOCALEDIR -sets the directory containing the locale files for message -internationalization. - - - - - - - - - -The Password File - - - password file - - - .pgpass - - - -The file .pgpass in a user's home directory or the -file referenced by PGPASSFILE can contain passwords to -be used if the connection requires a password (and no password has been -specified otherwise). On Microsoft Windows the file is named -%APPDATA%\postgresql\pgpass.conf (where -%APPDATA% refers to the Application Data subdirectory in -the user's profile). - - - -This file should contain lines of the following format: - -hostname:port:database:username:password - -Each of the first four fields may 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 hostname of localhost matches both host (TCP) -and local (Unix domain socket) connections coming from the -local machine. - - - -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. -(The file permissions are not currently checked on Microsoft Windows, -however.) - - - - - -The Connection Service File - - - connection service file - - - pg_service.conf - - - -The connection service file allows libpq connection parameters to be -associated with a single service name. That service name can then be -specified by a libpq connection, and the associated settings will be -used. This allows connection parameters to be modified without requiring -a recompile of the libpq application. The service name can also be -specified using the PGSERVICE environment variable. - - -To use this feature, copy -share/pg_service.conf.sample to -etc/pg_service.conf and edit the file to add -service names and parameters. This file can be used for client-only -installs too. The file's location can also be specified by the -PGSYSCONFDIR environment variable. - - - - - -SSL Support - - - SSL - + + threads + with libpq + - PostgreSQL has native support for using - SSL connections to encrypt client/server communications - for increased security. See for details - about the server-side SSL functionality. + libpq is reentrant and thread-safe if the + configure command-line option + --enable-thread-safety was used when the + PostgreSQL distribution was built. In + addition, you might need to use additional compiler command-line + options when you compile your application code. Refer to your + system's documentation for information about how to build + thread-enabled applications, or look in + src/Makefile.global for PTHREAD_CFLAGS + and PTHREAD_LIBS. This function allows the querying of + libpq's thread-safe status: + + + + PQisthreadsafe + + PQisthreadsafe + + + + + + Returns the thread safety status of the + libpq library. + + int PQisthreadsafe(); + + + + + Returns 1 if the libpq is thread-safe + and 0 if it is not. + + + + + - If the server demands a client certificate, - libpq - will send the certificate stored in file - ~/.postgresql/postgresql.crt within the user's home directory. - A matching private key file ~/.postgresql/postgresql.key - must also be present, and must not be world-readable. - (On Microsoft Windows these files are named - %APPDATA%\postgresql\postgresql.crt and - %APPDATA%\postgresql\postgresql.key.) + 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.) - If the file ~/.postgresql/root.crt is present in the user's - home directory, - libpq will use the certificate list stored - therein to verify the server's certificate. - (On Microsoft Windows the file is named - %APPDATA%\postgresql\root.crt.) - The SSL connection will - fail if the server does not present a certificate; therefore, to - use this feature the server must also have a root.crt file. + PGresult objects are read-only after creation, and so + can be passed around freely between threads. - If you are using SSL inside your application (in addition to - inside libpq), you can use PQinitSSL(int) - to tell libpq that the SSL library - has already been initialized by your application. + 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. + - - - - -Behavior in Threaded Programs - - - threads - with libpq - - - -libpq is reentrant and thread-safe if the -configure command-line option ---enable-thread-safety was used when the -PostgreSQL distribution was built. In -addition, you might need to use additional 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. - - - -One 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. - - + + 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. + + @@ -4168,8 +5401,8 @@ your library might not match the library used to build the binaries. To build (i.e., compile and link) a program using - libpq you need to - do all of the following things: + libpq you need to do all of the following + things: @@ -4178,8 +5411,8 @@ your library might not match the library used to build the binaries. #include <libpq-fe.h> - If you failed to do that then you will normally get error - messages from your compiler similar to + 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) @@ -4235,10 +5468,11 @@ 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 + -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 option before the option. For example: @@ -4257,8 +5491,8 @@ cc -o testprog testprog1.o testprog2.o -L/usr/local/pgsql/lib -lpq - Error messages that point to problems in this area could look - like the following. + 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' @@ -4269,8 +5503,8 @@ testlibpq.o(.text+0xa4): undefined reference to `PQerrorMessage' /usr/bin/ld: cannot find -lpq - This means you forgot the option or did not specify - the right directory. + This means you forgot the option or did not + specify the right directory. @@ -4769,20 +6003,3 @@ main(int argc, char **argv) - -