Postgresql 中文操作指南

FETCH

FETCH — 使用游标从查询中检索行

FETCH — retrieve rows from a query using a cursor

Synopsis

FETCH [ direction ] [ FROM | IN ] cursor_name

where direction can be one of:

    NEXT
    PRIOR
    FIRST
    LAST
    ABSOLUTE count
    RELATIVE count
    count
    ALL
    FORWARD
    FORWARD count
    FORWARD ALL
    BACKWARD
    BACKWARD count
    BACKWARD ALL

Description

FETCH 使用先前创建的游标检索行。

FETCH retrieves rows using a previously-created cursor.

游标具有相关联的位置，该位置由 FETCH 使用。游标位置可以在查询结果的第一行之前、结果的任何特定行上或结果的最后一行之后。创建时，游标位于第一行之前。在提取一些行后，游标会定位到最近检索到的行上。如果 FETCH 运行到可用行的末尾，则游标将保持在最后一行之后，或者如果向后提取，则位于第一行之前。 FETCH ALL 或 FETCH BACKWARD ALL 始终会将游标放在最后一行之后或第一行之前。

A cursor has an associated position, which is used by FETCH. The cursor position can be before the first row of the query result, on any particular row of the result, or after the last row of the result. When created, a cursor is positioned before the first row. After fetching some rows, the cursor is positioned on the row most recently retrieved. If FETCH runs off the end of the available rows then the cursor is left positioned after the last row, or before the first row if fetching backward. FETCH ALL or FETCH BACKWARD ALL will always leave the cursor positioned after the last row or before the first row.

NEXT 、 PRIOR 、 FIRST 、 LAST 、 ABSOLUTE 、 RELATIVE 等形式会在相应移动游标后提取一行。如果没有这样的行，则返回一个空结果，并且游标将保持在第一行之前或最后一行之后（视情况而定）。

The forms NEXT, PRIOR, FIRST, LAST, ABSOLUTE, RELATIVE fetch a single row after moving the cursor appropriately. If there is no such row, an empty result is returned, and the cursor is left positioned before the first row or after the last row as appropriate.

在向前或向后移动过程中，使用 FORWARD 和 BACKWARD 的形式可以检索指定数量的行，使游标保持在最后返回的行上（如果 count 超过可用行的数量，则保持游标在所有行之后/之前）。

The forms using FORWARD and BACKWARD retrieve the indicated number of rows moving in the forward or backward direction, leaving the cursor positioned on the last-returned row (or after/before all rows, if the count exceeds the number of rows available).

RELATIVE 0 、 FORWARD 0 和 BACKWARD 0 都要求获取当前行而不移动游标，也就是说，重新获取最近获取的行。这会成功，除非游标位于第一行前或最后一行后；在该情况下，不会返回任何行。

RELATIVE 0, FORWARD 0, and BACKWARD 0 all request fetching the current row without moving the cursor, that is, re-fetching the most recently fetched row. This will succeed unless the cursor is positioned before the first row or after the last row; in which case, no row is returned.

Note

本页介绍了 SQL 命令级别中游标的使用方法。如果尝试在一个 PL/pgSQL 函数内使用游标，则规则不同——参见 Section 43.7.3 。

This page describes usage of cursors at the SQL command level. If you are trying to use cursors inside a PL/pgSQL function, the rules are different — see Section 43.7.3.

Parameters

  • direction

    • direction defines the fetch direction and number of rows to fetch. It can be one of the following:

  • count

    • count is a possibly-signed integer constant, determining the location or number of rows to fetch. For FORWARD and BACKWARD cases, specifying a negative count is equivalent to changing the sense of FORWARD and BACKWARD.

  • cursor_name

    • An open cursor’s name.

  • NEXT

    • Fetch the next row. This is the default if direction is omitted.

  • PRIOR

    • Fetch the prior row.

  • FIRST

    • Fetch the first row of the query (same as ABSOLUTE 1).

  • LAST

    • Fetch the last row of the query (same as ABSOLUTE -1).

  • ABSOLUTE _count_

    • Fetch the count'th row of the query, or the abs(_count)'th row from the end if _count is negative. Position before first row or after last row if count is out of range; in particular, ABSOLUTE 0 positions before the first row.

  • RELATIVE _count_

    • Fetch the count'th succeeding row, or the abs(_count)'th prior row if _count is negative. RELATIVE 0 re-fetches the current row, if any.

  • count

    • Fetch the next count rows (same as FORWARD _count_).

  • ALL

    • Fetch all remaining rows (same as FORWARD ALL).

  • FORWARD

    • Fetch the next row (same as NEXT).

  • FORWARD _count_

    • Fetch the next count rows. FORWARD 0 re-fetches the current row.

  • FORWARD ALL

    • Fetch all remaining rows.

  • BACKWARD

    • Fetch the prior row (same as PRIOR).

  • BACKWARD _count_

    • Fetch the prior count rows (scanning backwards). BACKWARD 0 re-fetches the current row.

  • BACKWARD ALL

    • Fetch all prior rows (scanning backwards).

Outputs

在成功完成时，一个 FETCH 命令会返回类似于以下形式的命令标记

On successful completion, a FETCH command returns a command tag of the form

FETCH count

count 是获取的行数（可能为零）。注意在 psql 中，命令标记不会实际显示，因为 psql 会显示获取的行。

The count is the number of rows fetched (possibly zero). Note that in psql, the command tag will not actually be displayed, since psql displays the fetched rows instead.

Notes

如果打算使用 FETCH （ FETCH NEXT 或 FETCH FORWARD 除外）的任何变量，且计数为正，则游标应使用 SCROLL 选项声明。对于简单的查询，PostgreSQL 将允许从未使用 SCROLL 声明的游标向后获取，但最好不要依赖此行为。如果游标使用 NO SCROLL 声明，则不允许向后获取。

The cursor should be declared with the SCROLL option if one intends to use any variants of FETCH other than FETCH NEXT or FETCH FORWARD with a positive count. For simple queries PostgreSQL will allow backwards fetch from cursors not declared with SCROLL, but this behavior is best not relied on. If the cursor is declared with NO SCROLL, no backward fetches are allowed.

ABSOLUTE 抓取的效率并不比通过相对移动方式来导航到所需行更高：底层实现必须遍历所有介于其中的行。负绝对抓取更糟糕：必须读取查询直到最后一行，然后从那里向后遍历。但是，倒带回查询的开始（对于 FETCH ABSOLUTE 0 而言）是快速的。

ABSOLUTE fetches are not any faster than navigating to the desired row with a relative move: the underlying implementation must traverse all the intermediate rows anyway. Negative absolute fetches are even worse: the query must be read to the end to find the last row, and then traversed backward from there. However, rewinding to the start of the query (as with FETCH ABSOLUTE 0) is fast.

DECLARE 用于定义游标。使用 MOVE 来更改游标位置，而不检索数据。

DECLARE is used to define a cursor. Use MOVE to change cursor position without retrieving data.

Examples

下面的示例使用游标遍历表：

The following example traverses a table using a cursor:

BEGIN WORK;

-- Set up a cursor:
DECLARE liahona SCROLL CURSOR FOR SELECT * FROM films;

-- Fetch the first 5 rows in the cursor liahona:
FETCH FORWARD 5 FROM liahona;

 code  |          title          | did | date_prod  |   kind   |  len
-------+-------------------------+-----+------------+----------+-------
 BL101 | The Third Man           | 101 | 1949-12-23 | Drama    | 01:44
 BL102 | The African Queen       | 101 | 1951-08-11 | Romantic | 01:43
 JL201 | Une Femme est une Femme | 102 | 1961-03-12 | Romantic | 01:25
 P_301 | Vertigo                 | 103 | 1958-11-14 | Action   | 02:08
 P_302 | Becket                  | 103 | 1964-02-03 | Drama    | 02:28

-- Fetch the previous row:
FETCH PRIOR FROM liahona;

 code  |  title  | did | date_prod  |  kind  |  len
-------+---------+-----+------------+--------+-------
 P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08

-- Close the cursor and end the transaction:
CLOSE liahona;
COMMIT WORK;

Compatibility

SQL 标准仅将 FETCH 定义用于嵌入式 SQL 中。此处描述的 FETCH 的变体会将数据返回，就像它是 SELECT 结果，而不是将其置于主机变量中。除了这点以外， FETCH 完全向上兼容 SQL 标准。

The SQL standard defines FETCH for use in embedded SQL only. The variant of FETCH described here returns the data as if it were a SELECT result rather than placing it in host variables. Other than this point, FETCH is fully upward-compatible with the SQL standard.

涉及 FORWARD 和 BACKWARD 的 FETCH 形式，以及形式 FETCH _count_ 和 FETCH ALL ，其中 FORWARD 是隐式，是 PostgreSQL 的扩展。

The FETCH forms involving FORWARD and BACKWARD, as well as the forms FETCH _count_ and FETCH ALL, in which FORWARD is implicit, are PostgreSQL extensions.

SQL 标准只允许 FROM 在游标名称之前；使用 IN 或完全省略它们只是扩展。

The SQL standard allows only FROM preceding the cursor name; the option to use IN, or to leave them out altogether, is an extension.

See Also

CLOSE ， DECLARE ， MOVE

CLOSE, DECLARE, MOVE