Postgresql 中文操作指南

34.6. Retrieving Query Results Row-by-Row #

通常，libpq 收集 SQL 命令的整个结果，并将其作为单个 PGresult 返回到应用程序。这对于返回大量行的命令可能是不可行的。对于此类情况，应用程序可以在 single-row mode 中使用 PQsendQuery 和 PQgetResult 。在此模式中，结果行逐行返回到应用程序，就像从服务器收到它们一样。

Ordinarily, libpq collects an SQL command’s entire result and returns it to the application as a single PGresult. This can be unworkable for commands that return a large number of rows. For such cases, applications can use PQsendQuery and PQgetResult in single-row mode. In this mode, the result row(s) are returned to the application one at a time, as they are received from the server.

要进入单行模式，请在成功调用 PQsendQuery （或同级函数）后立即调用 PQsetSingleRowMode 。此模式选择仅对当前正在执行的查询有效。然后重复调用 PQgetResult ，直到它返回 null，如 Section 34.4 中所述。如果查询返回任何行，则它们将作为各个 PGresult 对象返回，它们看起来像普通查询结果一样，但状态代码为 PGRES_SINGLE_TUPLE 而非 PGRES_TUPLES_OK 。在最后一行之后，或如果查询返回零行，则返回一个状态为 PGRES_TUPLES_OK 的零行对象；这是不会再到达更多行的信号。（但请注意，仍然需要继续调用 PQgetResult ，直到它返回 null。）所有这些 PGresult 对象都将包含普通 PGresult 对象对查询相同的行描述数据（列名称、类型等）。应像往常一样用 PQclear 释放每个对象。

To enter single-row mode, call PQsetSingleRowMode immediately after a successful call of PQsendQuery (or a sibling function). This mode selection is effective only for the currently executing query. Then call PQgetResult repeatedly, until it returns null, as documented in Section 34.4. If the query returns any rows, they are returned as individual PGresult objects, which look like normal query results except for having status code PGRES_SINGLE_TUPLE instead of PGRES_TUPLES_OK. After the last row, or immediately if the query returns zero rows, a zero-row object with status PGRES_TUPLES_OK is returned; this is the signal that no more rows will arrive. (But note that it is still necessary to continue calling PQgetResult until it returns null.) All of these PGresult objects will contain the same row description data (column names, types, etc.) that an ordinary PGresult object for the query would have. Each object should be freed with PQclear as usual.

使用管道模式时，需要为管道中的每个查询激活单行模式，然后再使用 PQgetResult 检索该查询的结果。有关更多信息，请参阅 Section 34.5。

When using pipeline mode, single-row mode needs to be activated for each query in the pipeline before retrieving results for that query with PQgetResult. See Section 34.5 for more information.

  • PQsetSingleRowMode #

    • Select single-row mode for the currently-executing query.

int PQsetSingleRowMode(PGconn *conn);

  • This function can only be called immediately after PQsendQuery or one of its sibling functions, before any other operation on the connection such as PQconsumeInput or PQgetResult. If called at the correct time, the function activates single-row mode for the current query and returns 1. Otherwise the mode stays unchanged and the function returns 0. In any case, the mode reverts to normal after completion of the current query.

Caution

在处理查询时，服务器可能会返回一些行，然后遇到错误，导致查询中止。通常，libpq 会丢弃任何此类行并仅报告错误。但在单行模式下，这些行将已经返回给应用程序。因此，应用程序将看到一些 PGRES_SINGLE_TUPLE PGresult 对象，后跟 PGRES_FATAL_ERROR 对象。为了获得适当的事务行为，如果查询最终失败，则必须设计应用程序来丢弃或撤销已使用先前处理的行执行的任何操作。

While processing a query, the server may return some rows and then encounter an error, causing the query to be aborted. Ordinarily, libpq discards any such rows and reports only the error. But in single-row mode, those rows will have already been returned to the application. Hence, the application will see some PGRES_SINGLE_TUPLE PGresult objects followed by a PGRES_FATAL_ERROR object. For proper transactional behavior, the application must be designed to discard or undo whatever has been done with the previously-processed rows, if the query ultimately fails.