diff options
Diffstat (limited to 'doc/src/sgml/html/libpq-exec.html')
| -rw-r--r-- | doc/src/sgml/html/libpq-exec.html | 1050 |
1 files changed, 1050 insertions, 0 deletions
diff --git a/doc/src/sgml/html/libpq-exec.html b/doc/src/sgml/html/libpq-exec.html new file mode 100644 index 0000000..ad92536 --- /dev/null +++ b/doc/src/sgml/html/libpq-exec.html @@ -0,0 +1,1050 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>34.3. Command Execution Functions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="libpq-status.html" title="34.2. Connection Status Functions" /><link rel="next" href="libpq-async.html" title="34.4. Asynchronous Command Processing" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">34.3. Command Execution Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="libpq-status.html" title="34.2. Connection Status Functions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="libpq.html" title="Chapter 34. libpq — C Library">Up</a></td><th width="60%" align="center">Chapter 34. <span class="application">libpq</span> — C Library</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="libpq-async.html" title="34.4. Asynchronous Command Processing">Next</a></td></tr></table><hr /></div><div class="sect1" id="LIBPQ-EXEC"><div class="titlepage"><div><div><h2 class="title" style="clear: both">34.3. Command Execution Functions</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="libpq-exec.html#LIBPQ-EXEC-MAIN">34.3.1. Main Functions</a></span></dt><dt><span class="sect2"><a href="libpq-exec.html#LIBPQ-EXEC-SELECT-INFO">34.3.2. Retrieving Query Result Information</a></span></dt><dt><span class="sect2"><a href="libpq-exec.html#LIBPQ-EXEC-NONSELECT">34.3.3. Retrieving Other Result Information</a></span></dt><dt><span class="sect2"><a href="libpq-exec.html#LIBPQ-EXEC-ESCAPE-STRING">34.3.4. Escaping Strings for Inclusion in SQL Commands</a></span></dt></dl></div><p> + Once a connection to a database server has been successfully + established, the functions described here are used to perform + SQL queries and commands. + </p><div class="sect2" id="LIBPQ-EXEC-MAIN"><div class="titlepage"><div><div><h3 class="title">34.3.1. Main Functions</h3></div></div></div><p> + </p><div class="variablelist"><dl class="variablelist"><dt id="LIBPQ-PQEXEC"><span class="term"><code class="function">PQexec</code><a id="id-1.7.3.10.3.2.1.1.1.2" class="indexterm"></a></span></dt><dd><p> + Submits a command to the server and waits for the result. + +</p><pre class="synopsis"> +PGresult *PQexec(PGconn *conn, const char *command); +</pre><p> + </p><p> + Returns a <code class="structname">PGresult</code> 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. The <a class="xref" href="libpq-exec.html#LIBPQ-PQRESULTSTATUS"><code class="function">PQresultStatus</code></a> function + should be called to check the return value for any errors (including + the value of a null pointer, in which case it will return + <code class="symbol">PGRES_FATAL_ERROR</code>). Use + <a class="xref" href="libpq-status.html#LIBPQ-PQERRORMESSAGE"><code class="function">PQerrorMessage</code></a> to get more information about such + errors. + </p></dd></dl></div><p> + + The command string can include multiple SQL commands + (separated by semicolons). Multiple queries sent in a single + <a class="xref" href="libpq-exec.html#LIBPQ-PQEXEC"><code class="function">PQexec</code></a> call are processed in a single transaction, unless + there are explicit <code class="command">BEGIN</code>/<code class="command">COMMIT</code> + commands included in the query string to divide it into multiple + transactions. (See <a class="xref" href="protocol-flow.html#PROTOCOL-FLOW-MULTI-STATEMENT" title="55.2.2.1. Multiple Statements in a Simple Query">Section 55.2.2.1</a> + for more details about how the server handles multi-query strings.) + Note however that the returned + <code class="structname">PGresult</code> 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 + <code class="structname">PGresult</code> describes the error condition. + </p><p> + </p><div class="variablelist"><dl class="variablelist"><dt id="LIBPQ-PQEXECPARAMS"><span class="term"><code class="function">PQexecParams</code><a id="id-1.7.3.10.3.3.1.1.1.2" class="indexterm"></a></span></dt><dd><p> + Submits a command to the server and waits for the result, + with the ability to pass parameters separately from the SQL + command text. + +</p><pre class="synopsis"> +PGresult *PQexecParams(PGconn *conn, + const char *command, + int nParams, + const Oid *paramTypes, + const char * const *paramValues, + const int *paramLengths, + const int *paramFormats, + int resultFormat); +</pre><p> + </p><p> + <a class="xref" href="libpq-exec.html#LIBPQ-PQEXECPARAMS"><code class="function">PQexecParams</code></a> is like <a class="xref" href="libpq-exec.html#LIBPQ-PQEXEC"><code class="function">PQexec</code></a>, 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. + </p><p> + The function arguments are: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="parameter"><code>conn</code></em></span></dt><dd><p> + The connection object to send the command through. + </p></dd><dt><span class="term"><em class="parameter"><code>command</code></em></span></dt><dd><p> + The SQL command string to be executed. If parameters are used, + they are referred to in the command string as <code class="literal">$1</code>, + <code class="literal">$2</code>, etc. + </p></dd><dt><span class="term"><em class="parameter"><code>nParams</code></em></span></dt><dd><p> + The number of parameters supplied; it is the length of the arrays + <em class="parameter"><code>paramTypes[]</code></em>, <em class="parameter"><code>paramValues[]</code></em>, + <em class="parameter"><code>paramLengths[]</code></em>, and <em class="parameter"><code>paramFormats[]</code></em>. (The + array pointers can be <code class="symbol">NULL</code> when <em class="parameter"><code>nParams</code></em> + is zero.) + </p></dd><dt><span class="term"><em class="parameter"><code>paramTypes[]</code></em></span></dt><dd><p> + Specifies, by OID, the data types to be assigned to the + parameter symbols. If <em class="parameter"><code>paramTypes</code></em> is + <code class="symbol">NULL</code>, 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. + </p></dd><dt><span class="term"><em class="parameter"><code>paramValues[]</code></em></span></dt><dd><p> + 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). + </p></dd><dt><span class="term"><em class="parameter"><code>paramLengths[]</code></em></span></dt><dd><p> + 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. + </p></dd><dt><span class="term"><em class="parameter"><code>paramFormats[]</code></em></span></dt><dd><p> + 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. + </p><p> + Values passed in binary format require knowledge of + the internal representation expected by the backend. + For example, integers must be passed in network byte + order. Passing <code class="type">numeric</code> values requires + knowledge of the server storage format, as implemented + in + <code class="filename">src/backend/utils/adt/numeric.c::numeric_send()</code> and + <code class="filename">src/backend/utils/adt/numeric.c::numeric_recv()</code>. + </p></dd><dt><span class="term"><em class="parameter"><code>resultFormat</code></em></span></dt><dd><p> + 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.) + </p></dd></dl></div><p> + </p></dd></dl></div><p> + </p><p> + The primary advantage of <a class="xref" href="libpq-exec.html#LIBPQ-PQEXECPARAMS"><code class="function">PQexecParams</code></a> over + <a class="xref" href="libpq-exec.html#LIBPQ-PQEXEC"><code class="function">PQexec</code></a> is that parameter values can be separated from the + command string, thus avoiding the need for tedious and error-prone + quoting and escaping. + </p><p> + Unlike <a class="xref" href="libpq-exec.html#LIBPQ-PQEXEC"><code class="function">PQexec</code></a>, <a class="xref" href="libpq-exec.html#LIBPQ-PQEXECPARAMS"><code class="function">PQexecParams</code></a> 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. + </p><div class="tip"><h3 class="title">Tip</h3><p> + 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: +</p><pre class="programlisting"> +SELECT * FROM mytable WHERE x = $1::bigint; +</pre><p> + This forces parameter <code class="literal">$1</code> to be treated as <code class="type">bigint</code>, whereas + by default it would be assigned the same type as <code class="literal">x</code>. 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. + </p></div><p> + </p><div class="variablelist"><dl class="variablelist"><dt id="LIBPQ-PQPREPARE"><span class="term"><code class="function">PQprepare</code><a id="id-1.7.3.10.3.7.1.1.1.2" class="indexterm"></a></span></dt><dd><p> + Submits a request to create a prepared statement with the + given parameters, and waits for completion. +</p><pre class="synopsis"> +PGresult *PQprepare(PGconn *conn, + const char *stmtName, + const char *query, + int nParams, + const Oid *paramTypes); +</pre><p> + </p><p> + <a class="xref" href="libpq-exec.html#LIBPQ-PQPREPARE"><code class="function">PQprepare</code></a> creates a prepared statement for later + execution with <a class="xref" href="libpq-exec.html#LIBPQ-PQEXECPREPARED"><code class="function">PQexecPrepared</code></a>. This feature allows + commands to be executed repeatedly without being parsed and + planned each time; see <a class="xref" href="sql-prepare.html" title="PREPARE"><span class="refentrytitle">PREPARE</span></a> for details. + </p><p> + The function creates a prepared statement named + <em class="parameter"><code>stmtName</code></em> from the <em class="parameter"><code>query</code></em> string, which + must contain a single SQL command. <em class="parameter"><code>stmtName</code></em> can be + <code class="literal">""</code> 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 <code class="literal">$1</code>, <code class="literal">$2</code>, etc. + <em class="parameter"><code>nParams</code></em> is the number of parameters for which types + are pre-specified in the array <em class="parameter"><code>paramTypes[]</code></em>. (The + array pointer can be <code class="symbol">NULL</code> when + <em class="parameter"><code>nParams</code></em> is zero.) <em class="parameter"><code>paramTypes[]</code></em> + specifies, by OID, the data types to be assigned to the parameter + symbols. If <em class="parameter"><code>paramTypes</code></em> is <code class="symbol">NULL</code>, + 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 <em class="parameter"><code>nParams</code></em>; data types + will be inferred for these symbols as well. (See + <a class="xref" href="libpq-exec.html#LIBPQ-PQDESCRIBEPREPARED"><code class="function">PQdescribePrepared</code></a> for a means to find out + what data types were inferred.) + </p><p> + As with <a class="xref" href="libpq-exec.html#LIBPQ-PQEXEC"><code class="function">PQexec</code></a>, the result is normally a + <code class="structname">PGresult</code> 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 + <a class="xref" href="libpq-status.html#LIBPQ-PQERRORMESSAGE"><code class="function">PQerrorMessage</code></a> to get more information about + such errors. + </p></dd></dl></div><p> + + Prepared statements for use with <a class="xref" href="libpq-exec.html#LIBPQ-PQEXECPREPARED"><code class="function">PQexecPrepared</code></a> can also + be created by executing SQL <a class="xref" href="sql-prepare.html" title="PREPARE"><span class="refentrytitle">PREPARE</span></a> + statements. Also, although there is no <span class="application">libpq</span> + function for deleting a prepared statement, the SQL <a class="xref" href="sql-deallocate.html" title="DEALLOCATE"><span class="refentrytitle">DEALLOCATE</span></a> statement + can be used for that purpose. + </p><p> + </p><div class="variablelist"><dl class="variablelist"><dt id="LIBPQ-PQEXECPREPARED"><span class="term"><code class="function">PQexecPrepared</code><a id="id-1.7.3.10.3.8.1.1.1.2" class="indexterm"></a></span></dt><dd><p> + Sends a request to execute a prepared statement with given + parameters, and waits for the result. +</p><pre class="synopsis"> +PGresult *PQexecPrepared(PGconn *conn, + const char *stmtName, + int nParams, + const char * const *paramValues, + const int *paramLengths, + const int *paramFormats, + int resultFormat); +</pre><p> + </p><p> + <a class="xref" href="libpq-exec.html#LIBPQ-PQEXECPREPARED"><code class="function">PQexecPrepared</code></a> is like <a class="xref" href="libpq-exec.html#LIBPQ-PQEXECPARAMS"><code class="function">PQexecParams</code></a>, + 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. + </p><p> + The parameters are identical to <a class="xref" href="libpq-exec.html#LIBPQ-PQEXECPARAMS"><code class="function">PQexecParams</code></a>, except that the + name of a prepared statement is given instead of a query string, and the + <em class="parameter"><code>paramTypes[]</code></em> parameter is not present (it is not needed since + the prepared statement's parameter types were determined when it was created). + </p></dd><dt id="LIBPQ-PQDESCRIBEPREPARED"><span class="term"><code class="function">PQdescribePrepared</code><a id="id-1.7.3.10.3.8.1.2.1.2" class="indexterm"></a></span></dt><dd><p> + Submits a request to obtain information about the specified + prepared statement, and waits for completion. +</p><pre class="synopsis"> +PGresult *PQdescribePrepared(PGconn *conn, const char *stmtName); +</pre><p> + </p><p> + <a class="xref" href="libpq-exec.html#LIBPQ-PQDESCRIBEPREPARED"><code class="function">PQdescribePrepared</code></a> allows an application to obtain + information about a previously prepared statement. + </p><p> + <em class="parameter"><code>stmtName</code></em> can be <code class="literal">""</code> or <code class="symbol">NULL</code> to reference + the unnamed statement, otherwise it must be the name of an existing + prepared statement. On success, a <code class="structname">PGresult</code> with + status <code class="literal">PGRES_COMMAND_OK</code> is returned. The + functions <a class="xref" href="libpq-exec.html#LIBPQ-PQNPARAMS"><code class="function">PQnparams</code></a> and + <a class="xref" href="libpq-exec.html#LIBPQ-PQPARAMTYPE"><code class="function">PQparamtype</code></a> can be applied to this + <code class="structname">PGresult</code> to obtain information about the parameters + of the prepared statement, and the functions + <a class="xref" href="libpq-exec.html#LIBPQ-PQNFIELDS"><code class="function">PQnfields</code></a>, <a class="xref" href="libpq-exec.html#LIBPQ-PQFNAME"><code class="function">PQfname</code></a>, + <a class="xref" href="libpq-exec.html#LIBPQ-PQFTYPE"><code class="function">PQftype</code></a>, etc. provide information about the + result columns (if any) of the statement. + </p></dd><dt id="LIBPQ-PQDESCRIBEPORTAL"><span class="term"><code class="function">PQdescribePortal</code><a id="id-1.7.3.10.3.8.1.3.1.2" class="indexterm"></a></span></dt><dd><p> + Submits a request to obtain information about the specified + portal, and waits for completion. +</p><pre class="synopsis"> +PGresult *PQdescribePortal(PGconn *conn, const char *portalName); +</pre><p> + </p><p> + <a class="xref" href="libpq-exec.html#LIBPQ-PQDESCRIBEPORTAL"><code class="function">PQdescribePortal</code></a> allows an application to obtain + information about a previously created portal. + (<span class="application">libpq</span> does not provide any direct access to + portals, but you can use this function to inspect the properties + of a cursor created with a <code class="command">DECLARE CURSOR</code> SQL command.) + </p><p> + <em class="parameter"><code>portalName</code></em> can be <code class="literal">""</code> or <code class="symbol">NULL</code> to reference + the unnamed portal, otherwise it must be the name of an existing + portal. On success, a <code class="structname">PGresult</code> with status + <code class="literal">PGRES_COMMAND_OK</code> is returned. The functions + <a class="xref" href="libpq-exec.html#LIBPQ-PQNFIELDS"><code class="function">PQnfields</code></a>, <a class="xref" href="libpq-exec.html#LIBPQ-PQFNAME"><code class="function">PQfname</code></a>, + <a class="xref" href="libpq-exec.html#LIBPQ-PQFTYPE"><code class="function">PQftype</code></a>, etc. can be applied to the + <code class="structname">PGresult</code> to obtain information about the result + columns (if any) of the portal. + </p></dd></dl></div><p> + </p><p> + The <code class="structname">PGresult</code><a id="id-1.7.3.10.3.9.2" class="indexterm"></a> + structure encapsulates the result returned by the server. + <span class="application">libpq</span> application programmers should be + careful to maintain the <code class="structname">PGresult</code> abstraction. + Use the accessor functions below to get at the contents of + <code class="structname">PGresult</code>. Avoid directly referencing the + fields of the <code class="structname">PGresult</code> structure because they + are subject to change in the future. + + </p><div class="variablelist"><dl class="variablelist"><dt id="LIBPQ-PQRESULTSTATUS"><span class="term"><code class="function">PQresultStatus</code><a id="id-1.7.3.10.3.9.7.1.1.2" class="indexterm"></a></span></dt><dd><p> + Returns the result status of the command. +</p><pre class="synopsis"> +ExecStatusType PQresultStatus(const PGresult *res); +</pre><p> + </p><p> + <a class="xref" href="libpq-exec.html#LIBPQ-PQRESULTSTATUS"><code class="function">PQresultStatus</code></a> can return one of the following values: + + </p><div class="variablelist"><dl class="variablelist"><dt id="LIBPQ-PGRES-EMPTY-QUERY"><span class="term"><code class="literal">PGRES_EMPTY_QUERY</code></span></dt><dd><p> + The string sent to the server was empty. + </p></dd><dt id="LIBPQ-PGRES-COMMAND-OK"><span class="term"><code class="literal">PGRES_COMMAND_OK</code></span></dt><dd><p> + Successful completion of a command returning no data. + </p></dd><dt id="LIBPQ-PGRES-TUPLES-OK"><span class="term"><code class="literal">PGRES_TUPLES_OK</code></span></dt><dd><p> + Successful completion of a command returning data (such as + a <code class="command">SELECT</code> or <code class="command">SHOW</code>). + </p></dd><dt id="LIBPQ-PGRES-COPY-OUT"><span class="term"><code class="literal">PGRES_COPY_OUT</code></span></dt><dd><p> + Copy Out (from server) data transfer started. + </p></dd><dt id="LIBPQ-PGRES-COPY-IN"><span class="term"><code class="literal">PGRES_COPY_IN</code></span></dt><dd><p> + Copy In (to server) data transfer started. + </p></dd><dt id="LIBPQ-PGRES-BAD-RESPONSE"><span class="term"><code class="literal">PGRES_BAD_RESPONSE</code></span></dt><dd><p> + The server's response was not understood. + </p></dd><dt id="LIBPQ-PGRES-NONFATAL-ERROR"><span class="term"><code class="literal">PGRES_NONFATAL_ERROR</code></span></dt><dd><p> + A nonfatal error (a notice or warning) occurred. + </p></dd><dt id="LIBPQ-PGRES-FATAL-ERROR"><span class="term"><code class="literal">PGRES_FATAL_ERROR</code></span></dt><dd><p> + A fatal error occurred. + </p></dd><dt id="LIBPQ-PGRES-COPY-BOTH"><span class="term"><code class="literal">PGRES_COPY_BOTH</code></span></dt><dd><p> + Copy In/Out (to and from server) data transfer started. This + feature is currently used only for streaming replication, + so this status should not occur in ordinary applications. + </p></dd><dt id="LIBPQ-PGRES-SINGLE-TUPLE"><span class="term"><code class="literal">PGRES_SINGLE_TUPLE</code></span></dt><dd><p> + The <code class="structname">PGresult</code> contains a single result tuple + from the current command. This status occurs only when + single-row mode has been selected for the query + (see <a class="xref" href="libpq-single-row-mode.html" title="34.6. Retrieving Query Results Row-by-Row">Section 34.6</a>). + </p></dd><dt id="LIBPQ-PGRES-PIPELINE-SYNC"><span class="term"><code class="literal">PGRES_PIPELINE_SYNC</code></span></dt><dd><p> + The <code class="structname">PGresult</code> represents a + synchronization point in pipeline mode, requested by + <a class="xref" href="libpq-pipeline-mode.html#LIBPQ-PQPIPELINESYNC"><code class="function">PQpipelineSync</code></a>. + This status occurs only when pipeline mode has been selected. + </p></dd><dt id="LIBPQ-PGRES-PIPELINE-ABORTED"><span class="term"><code class="literal">PGRES_PIPELINE_ABORTED</code></span></dt><dd><p> + The <code class="structname">PGresult</code> represents a pipeline that has + received an error from the server. <code class="function">PQgetResult</code> + must be called repeatedly, and each time it will return this status code + until the end of the current pipeline, at which point it will return + <code class="literal">PGRES_PIPELINE_SYNC</code> and normal processing can + resume. + </p></dd></dl></div><p> + + If the result status is <code class="literal">PGRES_TUPLES_OK</code> or + <code class="literal">PGRES_SINGLE_TUPLE</code>, then + the functions described below can be used to retrieve the rows + returned by the query. Note that a <code class="command">SELECT</code> + command that happens to retrieve zero rows still shows + <code class="literal">PGRES_TUPLES_OK</code>. + <code class="literal">PGRES_COMMAND_OK</code> is for commands that can never + return rows (<code class="command">INSERT</code> or <code class="command">UPDATE</code> + without a <code class="literal">RETURNING</code> clause, + etc.). A response of <code class="literal">PGRES_EMPTY_QUERY</code> might + indicate a bug in the client software. + </p><p> + A result of status <code class="symbol">PGRES_NONFATAL_ERROR</code> will + never be returned directly by <a class="xref" href="libpq-exec.html#LIBPQ-PQEXEC"><code class="function">PQexec</code></a> or other + query execution functions; results of this kind are instead passed + to the notice processor (see <a class="xref" href="libpq-notice-processing.html" title="34.13. Notice Processing">Section 34.13</a>). + </p></dd><dt id="LIBPQ-PQRESSTATUS"><span class="term"><code class="function">PQresStatus</code><a id="id-1.7.3.10.3.9.7.2.1.2" class="indexterm"></a></span></dt><dd><p> + Converts the enumerated type returned by + <a class="xref" href="libpq-exec.html#LIBPQ-PQRESULTSTATUS"><code class="function">PQresultStatus</code></a> into a string constant describing the + status code. The caller should not free the result. + +</p><pre class="synopsis"> +char *PQresStatus(ExecStatusType status); +</pre><p> + </p></dd><dt id="LIBPQ-PQRESULTERRORMESSAGE"><span class="term"><code class="function">PQresultErrorMessage</code><a id="id-1.7.3.10.3.9.7.3.1.2" class="indexterm"></a></span></dt><dd><p> + Returns the error message associated with the command, or an empty string + if there was no error. +</p><pre class="synopsis"> +char *PQresultErrorMessage(const PGresult *res); +</pre><p> + 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 <code class="structname">PGresult</code> handle is + passed to <a class="xref" href="libpq-exec.html#LIBPQ-PQCLEAR"><code class="function">PQclear</code></a>. + </p><p> + Immediately following a <a class="xref" href="libpq-exec.html#LIBPQ-PQEXEC"><code class="function">PQexec</code></a> or + <a class="xref" href="libpq-async.html#LIBPQ-PQGETRESULT"><code class="function">PQgetResult</code></a> call, + <a class="xref" href="libpq-status.html#LIBPQ-PQERRORMESSAGE"><code class="function">PQerrorMessage</code></a> (on the connection) will return + the same string as <a class="xref" href="libpq-exec.html#LIBPQ-PQRESULTERRORMESSAGE"><code class="function">PQresultErrorMessage</code></a> (on + the result). However, a <code class="structname">PGresult</code> will + retain its error message until destroyed, whereas the connection's + error message will change when subsequent operations are done. + Use <a class="xref" href="libpq-exec.html#LIBPQ-PQRESULTERRORMESSAGE"><code class="function">PQresultErrorMessage</code></a> when you want to + know the status associated with a particular + <code class="structname">PGresult</code>; use + <a class="xref" href="libpq-status.html#LIBPQ-PQERRORMESSAGE"><code class="function">PQerrorMessage</code></a> when you want to know the + status from the latest operation on the connection. + </p></dd><dt id="LIBPQ-PQRESULTVERBOSEERRORMESSAGE"><span class="term"><code class="function">PQresultVerboseErrorMessage</code><a id="id-1.7.3.10.3.9.7.4.1.2" class="indexterm"></a></span></dt><dd><p> + Returns a reformatted version of the error message associated with + a <code class="structname">PGresult</code> object. +</p><pre class="synopsis"> +char *PQresultVerboseErrorMessage(const PGresult *res, + PGVerbosity verbosity, + PGContextVisibility show_context); +</pre><p> + In some situations a client might wish to obtain a more detailed + version of a previously-reported error. + <a class="xref" href="libpq-exec.html#LIBPQ-PQRESULTVERBOSEERRORMESSAGE"><code class="function">PQresultVerboseErrorMessage</code></a> addresses this need + by computing the message that would have been produced + by <a class="xref" href="libpq-exec.html#LIBPQ-PQRESULTERRORMESSAGE"><code class="function">PQresultErrorMessage</code></a> if the specified + verbosity settings had been in effect for the connection when the + given <code class="structname">PGresult</code> was generated. If + the <code class="structname">PGresult</code> is not an error result, + <span class="quote">“<span class="quote">PGresult is not an error result</span>”</span> is reported instead. + The returned string includes a trailing newline. + </p><p> + Unlike most other functions for extracting data from + a <code class="structname">PGresult</code>, the result of this function is a freshly + allocated string. The caller must free it + using <code class="function">PQfreemem()</code> when the string is no longer needed. + </p><p> + A NULL return is possible if there is insufficient memory. + </p></dd><dt id="LIBPQ-PQRESULTERRORFIELD"><span class="term"><code class="function">PQresultErrorField</code><a id="id-1.7.3.10.3.9.7.5.1.2" class="indexterm"></a></span></dt><dd><p> + Returns an individual field of an error report. +</p><pre class="synopsis"> +char *PQresultErrorField(const PGresult *res, int fieldcode); +</pre><p> + <em class="parameter"><code>fieldcode</code></em> is an error field identifier; see the symbols + listed below. <code class="symbol">NULL</code> is returned if the + <code class="structname">PGresult</code> 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 <code class="structname">PGresult</code> handle is passed to + <a class="xref" href="libpq-exec.html#LIBPQ-PQCLEAR"><code class="function">PQclear</code></a>. + </p><p> + The following field codes are available: + </p><div class="variablelist"><dl class="variablelist"><dt id="LIBPQ-PG-DIAG-SEVERITY"><span class="term"><code class="symbol">PG_DIAG_SEVERITY</code></span></dt><dd><p> + The severity; the field contents are <code class="literal">ERROR</code>, + <code class="literal">FATAL</code>, or <code class="literal">PANIC</code> (in an error message), + or <code class="literal">WARNING</code>, <code class="literal">NOTICE</code>, <code class="literal">DEBUG</code>, + <code class="literal">INFO</code>, or <code class="literal">LOG</code> (in a notice message), or + a localized translation of one of these. Always present. + </p></dd><dt id="LIBPQ-PG-DIAG-SEVERITY-NONLOCALIZED"><span class="term"><code class="symbol">PG_DIAG_SEVERITY_NONLOCALIZED</code></span></dt><dd><p> + The severity; the field contents are <code class="literal">ERROR</code>, + <code class="literal">FATAL</code>, or <code class="literal">PANIC</code> (in an error message), + or <code class="literal">WARNING</code>, <code class="literal">NOTICE</code>, <code class="literal">DEBUG</code>, + <code class="literal">INFO</code>, or <code class="literal">LOG</code> (in a notice message). + This is identical to the <code class="symbol">PG_DIAG_SEVERITY</code> field except + that the contents are never localized. This is present only in + reports generated by <span class="productname">PostgreSQL</span> versions 9.6 + and later. + </p></dd><dt id="LIBPQ-PG-DIAG-SQLSTATE"><span class="term"><code class="symbol">PG_DIAG_SQLSTATE</code><a id="id-1.7.3.10.3.9.7.5.2.2.1.3.1.2" class="indexterm"></a></span></dt><dd><p> + 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 <a class="xref" href="errcodes-appendix.html" title="Appendix A. PostgreSQL Error Codes">Appendix A</a>. This field is not localizable, + and is always present. + </p></dd><dt id="LIBPQ-PG-DIAG-MESSAGE-PRIMARY"><span class="term"><code class="symbol">PG_DIAG_MESSAGE_PRIMARY</code></span></dt><dd><p> + The primary human-readable error message (typically one line). + Always present. + </p></dd><dt id="LIBPQ-PG-DIAG-MESSAGE-DETAIL"><span class="term"><code class="symbol">PG_DIAG_MESSAGE_DETAIL</code></span></dt><dd><p> + Detail: an optional secondary error message carrying more + detail about the problem. Might run to multiple lines. + </p></dd><dt id="LIBPQ-PG-DIAG-MESSAGE-HINT"><span class="term"><code class="symbol">PG_DIAG_MESSAGE_HINT</code></span></dt><dd><p> + 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. + </p></dd><dt id="LIBPQ-PG-DIAG-STATEMENT-POSITION"><span class="term"><code class="symbol">PG_DIAG_STATEMENT_POSITION</code></span></dt><dd><p> + 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. + </p></dd><dt id="LIBPQ-PG-DIAG-INTERNAL-POSITION"><span class="term"><code class="symbol">PG_DIAG_INTERNAL_POSITION</code></span></dt><dd><p> + This is defined the same as the + <code class="symbol">PG_DIAG_STATEMENT_POSITION</code> field, but it is used + when the cursor position refers to an internally generated + command rather than the one submitted by the client. The + <code class="symbol">PG_DIAG_INTERNAL_QUERY</code> field will always appear when + this field appears. + </p></dd><dt id="LIBPQ-PG-DIAG-INTERNAL-QUERY"><span class="term"><code class="symbol">PG_DIAG_INTERNAL_QUERY</code></span></dt><dd><p> + The text of a failed internally-generated command. This could + be, for example, an SQL query issued by a PL/pgSQL function. + </p></dd><dt id="LIBPQ-PG-DIAG-CONTEXT"><span class="term"><code class="symbol">PG_DIAG_CONTEXT</code></span></dt><dd><p> + 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. + </p></dd><dt id="LIBPQ-PG-DIAG-SCHEMA-NAME"><span class="term"><code class="symbol">PG_DIAG_SCHEMA_NAME</code></span></dt><dd><p> + If the error was associated with a specific database object, + the name of the schema containing that object, if any. + </p></dd><dt id="LIBPQ-PG-DIAG-TABLE-NAME"><span class="term"><code class="symbol">PG_DIAG_TABLE_NAME</code></span></dt><dd><p> + If the error was associated with a specific table, the name of the + table. (Refer to the schema name field for the name of the + table's schema.) + </p></dd><dt id="LIBPQ-PG-DIAG-COLUMN-NAME"><span class="term"><code class="symbol">PG_DIAG_COLUMN_NAME</code></span></dt><dd><p> + If the error was associated with a specific table column, the name + of the column. (Refer to the schema and table name fields to + identify the table.) + </p></dd><dt id="LIBPQ-PG-DIAG-DATATYPE-NAME"><span class="term"><code class="symbol">PG_DIAG_DATATYPE_NAME</code></span></dt><dd><p> + If the error was associated with a specific data type, the name of + the data type. (Refer to the schema name field for the name of + the data type's schema.) + </p></dd><dt id="LIBPQ-PG-DIAG-CONSTRAINT-NAME"><span class="term"><code class="symbol">PG_DIAG_CONSTRAINT_NAME</code></span></dt><dd><p> + If the error was associated with a specific constraint, the name + of the constraint. Refer to fields listed above for the + associated table or domain. (For this purpose, indexes are + treated as constraints, even if they weren't created with + constraint syntax.) + </p></dd><dt id="LIBPQ-PG-DIAG-SOURCE-FILE"><span class="term"><code class="symbol">PG_DIAG_SOURCE_FILE</code></span></dt><dd><p> + The file name of the source-code location where the error was + reported. + </p></dd><dt id="LIBPQ-PG-DIAG-SOURCE-LINE"><span class="term"><code class="symbol">PG_DIAG_SOURCE_LINE</code></span></dt><dd><p> + The line number of the source-code location where the error + was reported. + </p></dd><dt id="LIBPQ-PG-DIAG-SOURCE-FUNCTION"><span class="term"><code class="symbol">PG_DIAG_SOURCE_FUNCTION</code></span></dt><dd><p> + The name of the source-code function reporting the error. + </p></dd></dl></div><p> + </p><div class="note"><h3 class="title">Note</h3><p> + The fields for schema name, table name, column name, data type name, + and constraint name are supplied only for a limited number of error + types; see <a class="xref" href="errcodes-appendix.html" title="Appendix A. PostgreSQL Error Codes">Appendix A</a>. Do not assume that + the presence of any of these fields guarantees the presence of + another field. Core error sources observe the interrelationships + noted above, but user-defined functions may use these fields in other + ways. In the same vein, do not assume that these fields denote + contemporary objects in the current database. + </p></div><p> + 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. + </p><p> + Errors generated internally by <span class="application">libpq</span> will + have severity and primary message, but typically no other fields. + </p><p> + Note that error fields are only available from + <code class="structname">PGresult</code> objects, not + <code class="structname">PGconn</code> objects; there is no + <code class="function">PQerrorField</code> function. + </p></dd><dt id="LIBPQ-PQCLEAR"><span class="term"><code class="function">PQclear</code><a id="id-1.7.3.10.3.9.7.6.1.2" class="indexterm"></a></span></dt><dd><p> + Frees the storage associated with a + <code class="structname">PGresult</code>. Every command result should be + freed via <a class="xref" href="libpq-exec.html#LIBPQ-PQCLEAR"><code class="function">PQclear</code></a> when it is no longer + needed. + +</p><pre class="synopsis"> +void PQclear(PGresult *res); +</pre><p> + </p><p> + You can keep a <code class="structname">PGresult</code> 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 <a class="xref" href="libpq-exec.html#LIBPQ-PQCLEAR"><code class="function">PQclear</code></a>. Failure to do this + will result in memory leaks in your application. + </p></dd></dl></div><p> + </p></div><div class="sect2" id="LIBPQ-EXEC-SELECT-INFO"><div class="titlepage"><div><div><h3 class="title">34.3.2. Retrieving Query Result Information</h3></div></div></div><p> + These functions are used to extract information from a + <code class="structname">PGresult</code> object that represents a successful + query result (that is, one that has status + <code class="literal">PGRES_TUPLES_OK</code> or <code class="literal">PGRES_SINGLE_TUPLE</code>). + 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. + </p><div class="variablelist"><dl class="variablelist"><dt id="LIBPQ-PQNTUPLES"><span class="term"><code class="function">PQntuples</code><a id="id-1.7.3.10.4.3.1.1.2" class="indexterm"></a></span></dt><dd><p> + Returns the number of rows (tuples) in the query result. + (Note that <code class="structname">PGresult</code> objects are limited to no more + than <code class="literal">INT_MAX</code> rows, so an <code class="type">int</code> result is + sufficient.) + +</p><pre class="synopsis"> +int PQntuples(const PGresult *res); +</pre><p> + + </p></dd><dt id="LIBPQ-PQNFIELDS"><span class="term"><code class="function">PQnfields</code><a id="id-1.7.3.10.4.3.2.1.2" class="indexterm"></a></span></dt><dd><p> + Returns the number of columns (fields) in each row of the query + result. + +</p><pre class="synopsis"> +int PQnfields(const PGresult *res); +</pre><p> + </p></dd><dt id="LIBPQ-PQFNAME"><span class="term"><code class="function">PQfname</code><a id="id-1.7.3.10.4.3.3.1.2" class="indexterm"></a></span></dt><dd><p> + 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 + <code class="structname">PGresult</code> handle is passed to + <a class="xref" href="libpq-exec.html#LIBPQ-PQCLEAR"><code class="function">PQclear</code></a>. +</p><pre class="synopsis"> +char *PQfname(const PGresult *res, + int column_number); +</pre><p> + </p><p> + <code class="symbol">NULL</code> is returned if the column number is out of range. + </p></dd><dt id="LIBPQ-PQFNUMBER"><span class="term"><code class="function">PQfnumber</code><a id="id-1.7.3.10.4.3.4.1.2" class="indexterm"></a></span></dt><dd><p> + Returns the column number associated with the given column name. +</p><pre class="synopsis"> +int PQfnumber(const PGresult *res, + const char *column_name); +</pre><p> + </p><p> + -1 is returned if the given name does not match any column. + </p><p> + 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: +</p><pre class="programlisting"> +SELECT 1 AS FOO, 2 AS "BAR"; +</pre><p> + we would have the results: +</p><pre class="programlisting"> +PQfname(res, 0) <em class="lineannotation"><span class="lineannotation">foo</span></em> +PQfname(res, 1) <em class="lineannotation"><span class="lineannotation">BAR</span></em> +PQfnumber(res, "FOO") <em class="lineannotation"><span class="lineannotation">0</span></em> +PQfnumber(res, "foo") <em class="lineannotation"><span class="lineannotation">0</span></em> +PQfnumber(res, "BAR") <em class="lineannotation"><span class="lineannotation">-1</span></em> +PQfnumber(res, "\"BAR\"") <em class="lineannotation"><span class="lineannotation">1</span></em> +</pre><p> + </p></dd><dt id="LIBPQ-PQFTABLE"><span class="term"><code class="function">PQftable</code><a id="id-1.7.3.10.4.3.5.1.2" class="indexterm"></a></span></dt><dd><p> + Returns the OID of the table from which the given column was + fetched. Column numbers start at 0. +</p><pre class="synopsis"> +Oid PQftable(const PGresult *res, + int column_number); +</pre><p> + </p><p> + <code class="literal">InvalidOid</code> is returned if the column number is out of range, + or if the specified column is not a simple reference to a table column. + You can query the system table <code class="literal">pg_class</code> to determine + exactly which table is referenced. + </p><p> + The type <code class="type">Oid</code> and the constant + <code class="literal">InvalidOid</code> will be defined when you include + the <span class="application">libpq</span> header file. They will both + be some integer type. + </p></dd><dt id="LIBPQ-PQFTABLECOL"><span class="term"><code class="function">PQftablecol</code><a id="id-1.7.3.10.4.3.6.1.2" class="indexterm"></a></span></dt><dd><p> + 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. +</p><pre class="synopsis"> +int PQftablecol(const PGresult *res, + int column_number); +</pre><p> + </p><p> + 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. + </p></dd><dt id="LIBPQ-PQFFORMAT"><span class="term"><code class="function">PQfformat</code><a id="id-1.7.3.10.4.3.7.1.2" class="indexterm"></a></span></dt><dd><p> + Returns the format code indicating the format of the given + column. Column numbers start at 0. +</p><pre class="synopsis"> +int PQfformat(const PGresult *res, + int column_number); +</pre><p> + </p><p> + Format code zero indicates textual data representation, while format + code one indicates binary representation. (Other codes are reserved + for future definition.) + </p></dd><dt id="LIBPQ-PQFTYPE"><span class="term"><code class="function">PQftype</code><a id="id-1.7.3.10.4.3.8.1.2" class="indexterm"></a></span></dt><dd><p> + 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. +</p><pre class="synopsis"> +Oid PQftype(const PGresult *res, + int column_number); +</pre><p> + </p><p> + You can query the system table <code class="literal">pg_type</code> to + obtain the names and properties of the various data types. The + <acronym class="acronym">OID</acronym>s of the built-in data types are defined + in the file <code class="filename">catalog/pg_type_d.h</code> + in the <span class="productname">PostgreSQL</span> + installation's <code class="filename">include</code> directory. + </p></dd><dt id="LIBPQ-PQFMOD"><span class="term"><code class="function">PQfmod</code><a id="id-1.7.3.10.4.3.9.1.2" class="indexterm"></a></span></dt><dd><p> + Returns the type modifier of the column associated with the + given column number. Column numbers start at 0. +</p><pre class="synopsis"> +int PQfmod(const PGresult *res, + int column_number); +</pre><p> + </p><p> + The interpretation of modifier values is type-specific; they + typically indicate precision or size limits. The value -1 is + used to indicate <span class="quote">“<span class="quote">no information available</span>”</span>. Most data + types do not use modifiers, in which case the value is always + -1. + </p></dd><dt id="LIBPQ-PQFSIZE"><span class="term"><code class="function">PQfsize</code><a id="id-1.7.3.10.4.3.10.1.2" class="indexterm"></a></span></dt><dd><p> + Returns the size in bytes of the column associated with the + given column number. Column numbers start at 0. +</p><pre class="synopsis"> +int PQfsize(const PGresult *res, + int column_number); +</pre><p> + </p><p> + <a class="xref" href="libpq-exec.html#LIBPQ-PQFSIZE"><code class="function">PQfsize</code></a> 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. + </p></dd><dt id="LIBPQ-PQBINARYTUPLES"><span class="term"><code class="function">PQbinaryTuples</code><a id="id-1.7.3.10.4.3.11.1.2" class="indexterm"></a></span></dt><dd><p> + Returns 1 if the <code class="structname">PGresult</code> contains binary data + and 0 if it contains text data. +</p><pre class="synopsis"> +int PQbinaryTuples(const PGresult *res); +</pre><p> + </p><p> + This function is deprecated (except for its use in connection with + <code class="command">COPY</code>), because it is possible for a single + <code class="structname">PGresult</code> to contain text data in some columns and + binary data in others. <a class="xref" href="libpq-exec.html#LIBPQ-PQFFORMAT"><code class="function">PQfformat</code></a> is preferred. + <a class="xref" href="libpq-exec.html#LIBPQ-PQBINARYTUPLES"><code class="function">PQbinaryTuples</code></a> returns 1 only if all columns of the + result are binary (format 1). + </p></dd><dt id="LIBPQ-PQGETVALUE"><span class="term"><code class="function">PQgetvalue</code><a id="id-1.7.3.10.4.3.12.1.2" class="indexterm"></a></span></dt><dd><p> + Returns a single field value of one row of a + <code class="structname">PGresult</code>. Row and column numbers start + at 0. The caller should not free the result directly. It will + be freed when the associated <code class="structname">PGresult</code> handle is + passed to <a class="xref" href="libpq-exec.html#LIBPQ-PQCLEAR"><code class="function">PQclear</code></a>. +</p><pre class="synopsis"> +char *PQgetvalue(const PGresult *res, + int row_number, + int column_number); +</pre><p> + </p><p> + For data in text format, the value returned by + <a class="xref" href="libpq-exec.html#LIBPQ-PQGETVALUE"><code class="function">PQgetvalue</code></a> 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 <code class="function">typsend</code> and <code class="function">typreceive</code> + 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.) + </p><p> + An empty string is returned if the field value is null. See + <a class="xref" href="libpq-exec.html#LIBPQ-PQGETISNULL"><code class="function">PQgetisnull</code></a> to distinguish null values from + empty-string values. + </p><p> + The pointer returned by <a class="xref" href="libpq-exec.html#LIBPQ-PQGETVALUE"><code class="function">PQgetvalue</code></a> points + to storage that is part of the <code class="structname">PGresult</code> + 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 <code class="structname">PGresult</code> + structure itself. + </p></dd><dt id="LIBPQ-PQGETISNULL"><span class="term"><code class="function">PQgetisnull</code><a id="id-1.7.3.10.4.3.13.1.2" class="indexterm"></a><a id="id-1.7.3.10.4.3.13.1.3" class="indexterm"></a></span></dt><dd><p> + Tests a field for a null value. Row and column numbers start + at 0. +</p><pre class="synopsis"> +int PQgetisnull(const PGresult *res, + int row_number, + int column_number); +</pre><p> + </p><p> + This function returns 1 if the field is null and 0 if it + contains a non-null value. (Note that + <a class="xref" href="libpq-exec.html#LIBPQ-PQGETVALUE"><code class="function">PQgetvalue</code></a> will return an empty string, + not a null pointer, for a null field.) + </p></dd><dt id="LIBPQ-PQGETLENGTH"><span class="term"><code class="function">PQgetlength</code><a id="id-1.7.3.10.4.3.14.1.2" class="indexterm"></a></span></dt><dd><p> + Returns the actual length of a field value in bytes. Row and + column numbers start at 0. +</p><pre class="synopsis"> +int PQgetlength(const PGresult *res, + int row_number, + int column_number); +</pre><p> + </p><p> + This is the actual data length for the particular data value, + that is, the size of the object pointed to by + <a class="xref" href="libpq-exec.html#LIBPQ-PQGETVALUE"><code class="function">PQgetvalue</code></a>. For text data format this is + the same as <code class="function">strlen()</code>. For binary format this is + essential information. Note that one should <span class="emphasis"><em>not</em></span> + rely on <a class="xref" href="libpq-exec.html#LIBPQ-PQFSIZE"><code class="function">PQfsize</code></a> to obtain the actual data + length. + </p></dd><dt id="LIBPQ-PQNPARAMS"><span class="term"><code class="function">PQnparams</code><a id="id-1.7.3.10.4.3.15.1.2" class="indexterm"></a></span></dt><dd><p> + Returns the number of parameters of a prepared statement. +</p><pre class="synopsis"> +int PQnparams(const PGresult *res); +</pre><p> + </p><p> + This function is only useful when inspecting the result of + <a class="xref" href="libpq-exec.html#LIBPQ-PQDESCRIBEPREPARED"><code class="function">PQdescribePrepared</code></a>. For other types of queries it + will return zero. + </p></dd><dt id="LIBPQ-PQPARAMTYPE"><span class="term"><code class="function">PQparamtype</code><a id="id-1.7.3.10.4.3.16.1.2" class="indexterm"></a></span></dt><dd><p> + Returns the data type of the indicated statement parameter. + Parameter numbers start at 0. +</p><pre class="synopsis"> +Oid PQparamtype(const PGresult *res, int param_number); +</pre><p> + </p><p> + This function is only useful when inspecting the result of + <a class="xref" href="libpq-exec.html#LIBPQ-PQDESCRIBEPREPARED"><code class="function">PQdescribePrepared</code></a>. For other types of queries it + will return zero. + </p></dd><dt id="LIBPQ-PQPRINT"><span class="term"><code class="function">PQprint</code><a id="id-1.7.3.10.4.3.17.1.2" class="indexterm"></a></span></dt><dd><p> + Prints out all the rows and, optionally, the column names to + the specified output stream. +</p><pre class="synopsis"> +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; +</pre><p> + </p><p> + This function was formerly used by <span class="application">psql</span> + to print query results, but this is no longer the case. Note + that it assumes all the data is in text format. + </p></dd></dl></div></div><div class="sect2" id="LIBPQ-EXEC-NONSELECT"><div class="titlepage"><div><div><h3 class="title">34.3.3. Retrieving Other Result Information</h3></div></div></div><p> + These functions are used to extract other information from + <code class="structname">PGresult</code> objects. + </p><div class="variablelist"><dl class="variablelist"><dt id="LIBPQ-PQCMDSTATUS"><span class="term"><code class="function">PQcmdStatus</code><a id="id-1.7.3.10.5.3.1.1.2" class="indexterm"></a></span></dt><dd><p> + Returns the command status tag from the SQL command that generated + the <code class="structname">PGresult</code>. +</p><pre class="synopsis"> +char *PQcmdStatus(PGresult *res); +</pre><p> + </p><p> + 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 <code class="structname">PGresult</code> handle is passed to + <a class="xref" href="libpq-exec.html#LIBPQ-PQCLEAR"><code class="function">PQclear</code></a>. + </p></dd><dt id="LIBPQ-PQCMDTUPLES"><span class="term"><code class="function">PQcmdTuples</code><a id="id-1.7.3.10.5.3.2.1.2" class="indexterm"></a></span></dt><dd><p> + Returns the number of rows affected by the SQL command. +</p><pre class="synopsis"> +char *PQcmdTuples(PGresult *res); +</pre><p> + </p><p> + This function returns a string containing the number of rows + affected by the <acronym class="acronym">SQL</acronym> statement that generated the + <code class="structname">PGresult</code>. This function can only be used following + the execution of a <code class="command">SELECT</code>, <code class="command">CREATE TABLE AS</code>, + <code class="command">INSERT</code>, <code class="command">UPDATE</code>, <code class="command">DELETE</code>, + <code class="command">MERGE</code>, <code class="command">MOVE</code>, <code class="command">FETCH</code>, + or <code class="command">COPY</code> statement, or an <code class="command">EXECUTE</code> of a + prepared query that contains an <code class="command">INSERT</code>, + <code class="command">UPDATE</code>, <code class="command">DELETE</code>, + or <code class="command">MERGE</code> statement. + If the command that generated the <code class="structname">PGresult</code> was anything + else, <a class="xref" href="libpq-exec.html#LIBPQ-PQCMDTUPLES"><code class="function">PQcmdTuples</code></a> returns an empty string. The caller + should not free the return value directly. It will be freed when + the associated <code class="structname">PGresult</code> handle is passed to + <a class="xref" href="libpq-exec.html#LIBPQ-PQCLEAR"><code class="function">PQclear</code></a>. + </p></dd><dt id="LIBPQ-PQOIDVALUE"><span class="term"><code class="function">PQoidValue</code><a id="id-1.7.3.10.5.3.3.1.2" class="indexterm"></a></span></dt><dd><p> + Returns the OID<a id="id-1.7.3.10.5.3.3.2.1.1" class="indexterm"></a> + of the inserted row, if the <acronym class="acronym">SQL</acronym> command was an + <code class="command">INSERT</code> that inserted exactly one row into a table that + has OIDs, or a <code class="command">EXECUTE</code> of a prepared query containing + a suitable <code class="command">INSERT</code> statement. Otherwise, this function + returns <code class="literal">InvalidOid</code>. This function will also + return <code class="literal">InvalidOid</code> if the table affected by the + <code class="command">INSERT</code> statement does not contain OIDs. +</p><pre class="synopsis"> +Oid PQoidValue(const PGresult *res); +</pre><p> + </p></dd><dt id="LIBPQ-PQOIDSTATUS"><span class="term"><code class="function">PQoidStatus</code><a id="id-1.7.3.10.5.3.4.1.2" class="indexterm"></a></span></dt><dd><p> + This function is deprecated in favor of + <a class="xref" href="libpq-exec.html#LIBPQ-PQOIDVALUE"><code class="function">PQoidValue</code></a> and is not thread-safe. + It returns a string with the OID of the inserted row, while + <a class="xref" href="libpq-exec.html#LIBPQ-PQOIDVALUE"><code class="function">PQoidValue</code></a> returns the OID value. +</p><pre class="synopsis"> +char *PQoidStatus(const PGresult *res); +</pre><p> + </p></dd></dl></div></div><div class="sect2" id="LIBPQ-EXEC-ESCAPE-STRING"><div class="titlepage"><div><div><h3 class="title">34.3.4. Escaping Strings for Inclusion in SQL Commands</h3></div></div></div><a id="id-1.7.3.10.6.2" class="indexterm"></a><div class="variablelist"><dl class="variablelist"><dt id="LIBPQ-PQESCAPELITERAL"><span class="term"><code class="function">PQescapeLiteral</code><a id="id-1.7.3.10.6.3.1.1.2" class="indexterm"></a></span></dt><dd><p> +</p><pre class="synopsis"> +char *PQescapeLiteral(PGconn *conn, const char *str, size_t length); +</pre><p> + </p><p> + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPELITERAL"><code class="function">PQescapeLiteral</code></a> 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. + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPELITERAL"><code class="function">PQescapeLiteral</code></a> performs this operation. + </p><p> + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPELITERAL"><code class="function">PQescapeLiteral</code></a> returns an escaped version of the + <em class="parameter"><code>str</code></em> parameter in memory allocated with + <code class="function">malloc()</code>. This memory should be freed using + <code class="function">PQfreemem()</code> when the result is no longer needed. + A terminating zero byte is not required, and should not be + counted in <em class="parameter"><code>length</code></em>. (If a terminating zero byte is found + before <em class="parameter"><code>length</code></em> bytes are processed, + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPELITERAL"><code class="function">PQescapeLiteral</code></a> stops at the zero; the behavior is + thus rather like <code class="function">strncpy</code>.) The + return string has all special characters replaced so that they can + be properly processed by the <span class="productname">PostgreSQL</span> + string literal parser. A terminating zero byte is also added. The + single quotes that must surround <span class="productname">PostgreSQL</span> + string literals are included in the result string. + </p><p> + On error, <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPELITERAL"><code class="function">PQescapeLiteral</code></a> returns <code class="symbol">NULL</code> and a suitable + message is stored in the <em class="parameter"><code>conn</code></em> object. + </p><div class="tip"><h3 class="title">Tip</h3><p> + 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 + <span class="quote">“<span class="quote">SQL injection</span>”</span> attacks wherein unwanted SQL commands are + fed to your database. + </p></div><p> + Note that it is neither necessary nor correct to do escaping when a data + value is passed as a separate parameter in <a class="xref" href="libpq-exec.html#LIBPQ-PQEXECPARAMS"><code class="function">PQexecParams</code></a> or + its sibling routines. + </p></dd><dt id="LIBPQ-PQESCAPEIDENTIFIER"><span class="term"><code class="function">PQescapeIdentifier</code><a id="id-1.7.3.10.6.3.2.1.2" class="indexterm"></a></span></dt><dd><p> +</p><pre class="synopsis"> +char *PQescapeIdentifier(PGconn *conn, const char *str, size_t length); +</pre><p> + </p><p> + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPEIDENTIFIER"><code class="function">PQescapeIdentifier</code></a> escapes a string for + use as an SQL identifier, such as a table, column, or function name. + This is useful when a user-supplied identifier might contain + special characters that would otherwise not be interpreted as part + of the identifier by the SQL parser, or when the identifier might + contain upper case characters whose case should be preserved. + </p><p> + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPEIDENTIFIER"><code class="function">PQescapeIdentifier</code></a> returns a version of the + <em class="parameter"><code>str</code></em> parameter escaped as an SQL identifier + in memory allocated with <code class="function">malloc()</code>. This memory must be + freed using <code class="function">PQfreemem()</code> when the result is no longer + needed. A terminating zero byte is not required, and should not be + counted in <em class="parameter"><code>length</code></em>. (If a terminating zero byte is found + before <em class="parameter"><code>length</code></em> bytes are processed, + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPEIDENTIFIER"><code class="function">PQescapeIdentifier</code></a> stops at the zero; the behavior is + thus rather like <code class="function">strncpy</code>.) The + return string has all special characters replaced so that it + will be properly processed as an SQL identifier. A terminating zero byte + is also added. The return string will also be surrounded by double + quotes. + </p><p> + On error, <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPEIDENTIFIER"><code class="function">PQescapeIdentifier</code></a> returns <code class="symbol">NULL</code> and a suitable + message is stored in the <em class="parameter"><code>conn</code></em> object. + </p><div class="tip"><h3 class="title">Tip</h3><p> + As with string literals, to prevent SQL injection attacks, + SQL identifiers must be escaped when they are received from an + untrustworthy source. + </p></div></dd><dt id="LIBPQ-PQESCAPESTRINGCONN"><span class="term"><code class="function">PQescapeStringConn</code><a id="id-1.7.3.10.6.3.3.1.2" class="indexterm"></a></span></dt><dd><p> +</p><pre class="synopsis"> +size_t PQescapeStringConn(PGconn *conn, + char *to, const char *from, size_t length, + int *error); +</pre><p> + </p><p> + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPESTRINGCONN"><code class="function">PQescapeStringConn</code></a> escapes string literals, much like + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPELITERAL"><code class="function">PQescapeLiteral</code></a>. Unlike <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPELITERAL"><code class="function">PQescapeLiteral</code></a>, + the caller is responsible for providing an appropriately sized buffer. + Furthermore, <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPESTRINGCONN"><code class="function">PQescapeStringConn</code></a> does not generate the + single quotes that must surround <span class="productname">PostgreSQL</span> string + literals; they should be provided in the SQL command that the + result is inserted into. The parameter <em class="parameter"><code>from</code></em> points to + the first character of the string that is to be escaped, and the + <em class="parameter"><code>length</code></em> parameter gives the number of bytes in this + string. A terminating zero byte is not required, and should not be + counted in <em class="parameter"><code>length</code></em>. (If a terminating zero byte is found + before <em class="parameter"><code>length</code></em> bytes are processed, + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPESTRINGCONN"><code class="function">PQescapeStringConn</code></a> stops at the zero; the behavior is + thus rather like <code class="function">strncpy</code>.) <em class="parameter"><code>to</code></em> shall point + to a buffer that is able to hold at least one more byte than twice + the value of <em class="parameter"><code>length</code></em>, otherwise the behavior is undefined. + Behavior is likewise undefined if the <em class="parameter"><code>to</code></em> and + <em class="parameter"><code>from</code></em> strings overlap. + </p><p> + If the <em class="parameter"><code>error</code></em> parameter is not <code class="symbol">NULL</code>, then + <code class="literal">*error</code> 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 + <em class="parameter"><code>conn</code></em> object, whether or not <em class="parameter"><code>error</code></em> is <code class="symbol">NULL</code>. + </p><p> + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPESTRINGCONN"><code class="function">PQescapeStringConn</code></a> returns the number of bytes written + to <em class="parameter"><code>to</code></em>, not including the terminating zero byte. + </p></dd><dt id="LIBPQ-PQESCAPESTRING"><span class="term"><code class="function">PQescapeString</code><a id="id-1.7.3.10.6.3.4.1.2" class="indexterm"></a></span></dt><dd><p> + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPESTRING"><code class="function">PQescapeString</code></a> is an older, deprecated version of + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPESTRINGCONN"><code class="function">PQescapeStringConn</code></a>. +</p><pre class="synopsis"> +size_t PQescapeString (char *to, const char *from, size_t length); +</pre><p> + </p><p> + The only difference from <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPESTRINGCONN"><code class="function">PQescapeStringConn</code></a> is that + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPESTRING"><code class="function">PQescapeString</code></a> does not take <code class="structname">PGconn</code> + or <em class="parameter"><code>error</code></em> parameters. + Because of this, it cannot adjust its behavior depending on the + connection properties (such as character encoding) and therefore + <span class="emphasis"><em>it might give the wrong results</em></span>. Also, it has no way + to report error conditions. + </p><p> + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPESTRING"><code class="function">PQescapeString</code></a> can be used safely in + client programs that work with only one <span class="productname">PostgreSQL</span> + connection at a time (in this case it can find out what it needs to + know <span class="quote">“<span class="quote">behind the scenes</span>”</span>). In other contexts it is a security + hazard and should be avoided in favor of + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPESTRINGCONN"><code class="function">PQescapeStringConn</code></a>. + </p></dd><dt id="LIBPQ-PQESCAPEBYTEACONN"><span class="term"><code class="function">PQescapeByteaConn</code><a id="id-1.7.3.10.6.3.5.1.2" class="indexterm"></a></span></dt><dd><p> + Escapes binary data for use within an SQL command with the type + <code class="type">bytea</code>. As with <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPESTRINGCONN"><code class="function">PQescapeStringConn</code></a>, + this is only used when inserting data directly into an SQL command string. +</p><pre class="synopsis"> +unsigned char *PQescapeByteaConn(PGconn *conn, + const unsigned char *from, + size_t from_length, + size_t *to_length); +</pre><p> + </p><p> + Certain byte values must be escaped when used as part of a + <code class="type">bytea</code> literal in an <acronym class="acronym">SQL</acronym> statement. + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPEBYTEACONN"><code class="function">PQescapeByteaConn</code></a> escapes bytes using + either hex encoding or backslash escaping. See <a class="xref" href="datatype-binary.html" title="8.4. Binary Data Types">Section 8.4</a> for more information. + </p><p> + The <em class="parameter"><code>from</code></em> parameter points to the first + byte of the string that is to be escaped, and the + <em class="parameter"><code>from_length</code></em> parameter gives the number of + bytes in this binary string. (A terminating zero byte is + neither necessary nor counted.) The <em class="parameter"><code>to_length</code></em> + 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. + </p><p> + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPEBYTEACONN"><code class="function">PQescapeByteaConn</code></a> returns an escaped version of the + <em class="parameter"><code>from</code></em> parameter binary string in memory + allocated with <code class="function">malloc()</code>. This memory should be freed using + <code class="function">PQfreemem()</code> when the result is no longer needed. The + return string has all special characters replaced so that they can + be properly processed by the <span class="productname">PostgreSQL</span> + string literal parser, and the <code class="type">bytea</code> input function. A + terminating zero byte is also added. The single quotes that must + surround <span class="productname">PostgreSQL</span> string literals are + not part of the result string. + </p><p> + On error, a null pointer is returned, and a suitable error message + is stored in the <em class="parameter"><code>conn</code></em> object. Currently, the only + possible error is insufficient memory for the result string. + </p></dd><dt id="LIBPQ-PQESCAPEBYTEA"><span class="term"><code class="function">PQescapeBytea</code><a id="id-1.7.3.10.6.3.6.1.2" class="indexterm"></a></span></dt><dd><p> + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPEBYTEA"><code class="function">PQescapeBytea</code></a> is an older, deprecated version of + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPEBYTEACONN"><code class="function">PQescapeByteaConn</code></a>. +</p><pre class="synopsis"> +unsigned char *PQescapeBytea(const unsigned char *from, + size_t from_length, + size_t *to_length); +</pre><p> + </p><p> + The only difference from <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPEBYTEACONN"><code class="function">PQescapeByteaConn</code></a> is that + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPEBYTEA"><code class="function">PQescapeBytea</code></a> does not take a <code class="structname">PGconn</code> + parameter. Because of this, <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPEBYTEA"><code class="function">PQescapeBytea</code></a> can + only be used safely in client programs that use a single + <span class="productname">PostgreSQL</span> connection at a time (in this case + it can find out what it needs to know <span class="quote">“<span class="quote">behind the + scenes</span>”</span>). It <span class="emphasis"><em>might give the wrong results</em></span> if + used in programs that use multiple database connections (use + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPEBYTEACONN"><code class="function">PQescapeByteaConn</code></a> in such cases). + </p></dd><dt id="LIBPQ-PQUNESCAPEBYTEA"><span class="term"><code class="function">PQunescapeBytea</code><a id="id-1.7.3.10.6.3.7.1.2" class="indexterm"></a></span></dt><dd><p> + Converts a string representation of binary data into binary data + — the reverse of <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPEBYTEA"><code class="function">PQescapeBytea</code></a>. This + is needed when retrieving <code class="type">bytea</code> data in text format, + but not when retrieving it in binary format. + +</p><pre class="synopsis"> +unsigned char *PQunescapeBytea(const unsigned char *from, size_t *to_length); +</pre><p> + </p><p> + The <em class="parameter"><code>from</code></em> parameter points to a string + such as might be returned by <a class="xref" href="libpq-exec.html#LIBPQ-PQGETVALUE"><code class="function">PQgetvalue</code></a> when applied + to a <code class="type">bytea</code> column. <a class="xref" href="libpq-exec.html#LIBPQ-PQUNESCAPEBYTEA"><code class="function">PQunescapeBytea</code></a> + converts this string representation into its binary representation. + It returns a pointer to a buffer allocated with + <code class="function">malloc()</code>, or <code class="symbol">NULL</code> on error, and puts the size of + the buffer in <em class="parameter"><code>to_length</code></em>. The result must be + freed using <a class="xref" href="libpq-misc.html#LIBPQ-PQFREEMEM"><code class="function">PQfreemem</code></a> when it is no longer needed. + </p><p> + This conversion is not exactly the inverse of + <a class="xref" href="libpq-exec.html#LIBPQ-PQESCAPEBYTEA"><code class="function">PQescapeBytea</code></a>, because the string is not expected + to be <span class="quote">“<span class="quote">escaped</span>”</span> when received from <a class="xref" href="libpq-exec.html#LIBPQ-PQGETVALUE"><code class="function">PQgetvalue</code></a>. + In particular this means there is no need for string quoting considerations, + and so no need for a <code class="structname">PGconn</code> parameter. + </p></dd></dl></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="libpq-status.html" title="34.2. Connection Status Functions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="libpq.html" title="Chapter 34. libpq — C Library">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="libpq-async.html" title="34.4. Asynchronous Command Processing">Next</a></td></tr><tr><td width="40%" align="left" valign="top">34.2. Connection Status Functions </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 34.4. Asynchronous Command Processing</td></tr></table></div></body></html>
\ No newline at end of file |