Postgresql 中文操作指南

43.7. Cursors #

与其一次执行整个查询，不如设置一个封装查询的 cursor ，然后一次读取几行查询结果。这样做的一个原因是避免在结果包含大量行时出现内存溢出。（但是， PL/pgSQL 用户通常不必为此担心，因为 FOR 循环会自动在内部使用游标来避免内存问题。）一个更有趣的用法是返回函数创建的游标引用，允许调用者读取行。这提供了一种从函数返回大型行集的有效方法。

43.7.1. Declaring Cursor Variables #

PL/pgSQL 中对游标的所有访问都通过游标变量进行，游标变量始终属于特殊数据类型 refcursor 。创建游标变量的一种方法是简单地将其声明为类型为 refcursor 的变量。另一种方法是使用游标声明语法，通常为：

name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;

( FOR 为了与 Oracle 相兼容，可以替换为 IS 。)如果指定了 SCROLL ，游标能够向后滚动；如果指定了 NO SCROLL ，将拒绝向后提取，如果未出现任何规范，则允许或不允许向后提取取决于查询。如果指定了 arguments ，则它是一个逗号分隔的对 name datatype 的列表，用于定义在给定查询中要由参数值替换的名称。这些名称的实际替换值将在稍后打开游标时指定。

举例：

DECLARE
    curs1 refcursor;
    curs2 CURSOR FOR SELECT * FROM tenk1;
    curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;

这三个变量都具有数据类型 refcursor ，但第一个变量可与任何查询一起使用，而第二个变量已具有完全指定的查询 bound ，最后一个变量已绑定到参数化查询。（当打开游标时， key 将被一个整数参数值替换。）变量 curs1 被称为 unbound ，因为它未绑定到任何特定查询。

当游标的查询使用 FOR UPDATE/SHARE 时， SCROLL 选项不可用。此外，最好在涉及易失函数的查询中使用 NO SCROLL 。 SCROLL 的实现假设重新读取查询输出将给出一致的结果，而易失函数可能无法做到这一点。

43.7.2. Opening Cursors #

在游标可用于检索行之前，必须对其 opened 。(这相当于 SQL 命令 DECLARE CURSOR 。)PL/pgSQL 有三种形式的 OPEN 语句，其中两种使用非绑定的游标变量，而第三种使用绑定的游标变量。

Note

通过 Section 43.7.4 中描述的 FOR 语句，还可以使用绑定游标变量而不显式打开游标。一个 FOR 循环将在循环完成后打开游标，然后再次关闭它。

打开游标涉及创建服务器内部数据结构，称为_portal_，它保存游标查询的执行状态。门户有一个名称，它在门户生存期间必须在会话中唯一。默认情况下，PL/pgSQL将为它创建的每个门户分配一个唯一的名称。但是，如果你为游标变量分配一个非空字符串值，该字符串将用作其门户名称。此功能可按 Section 43.7.3.5中所述使用。

43.7.2.1. OPEN FOR query #

OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR query;

游标变量被打开并给定指定要执行的查询。游标不能已经打开，并且必须声明为未绑定的游标变量（即作为简单的 refcursor 变量）。查询必须是 SELECT ，或返回行（例如 EXPLAIN ）的其他内容。查询的处理方式与 PL/pgSQL 中的其他 SQL 命令相同：替换 PL/pgSQL 变量名称，并将查询计划缓存起来以供可能重复使用。当 PL/pgSQL 变量替换到游标查询中时，替换的值是它在 OPEN 时的值；对该变量的后续更改不会影响游标的行为。 SCROLL 和 NO SCROLL 选项的含义与绑定游标相同。

示例：

OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

43.7.2.2. OPEN FOR EXECUTE #

OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR EXECUTE query_string
                                     [ USING expression [, ... ] ];

打开游标变量，并为它提供要执行的指定查询。游标不能已经是打开状态，并且它必须声明为未绑定游标变量（即，作为一个简单的_refcursor_变量）。查询指定为一个字符串表达式，与_EXECUTE_命令中的方式相同。和通常一样，这提供了灵活性，因此查询计划可以一次执行与下一次执行之间有所不同（请参见 Section 43.11.2），它还意味着不会针对命令字符串执行变量替换。与_EXECUTE_一样，可以通过_format()_和_USING_将参数值插入动态命令。_SCROLL_和_NO SCROLL_选项具有与绑定游标相同的含义。

示例：

OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;

在这个示例中，表名称通过 format() 插入到查询中。 col1 的比较值通过 USING 参数插入，因此不需要引用。

43.7.2.3. Opening a Bound Cursor #

OPEN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ];

这种形式的 OPEN 用于打开游标变量，其查询在声明时与其绑定。游标不能已经打开。当且仅当声明游标需要参数时，才必须出现实参值表达式的列表。这些值将替换在查询中。

绑定游标的查询计划始终被视为可缓存的；在这种情况下没有相当于 EXECUTE 的内容。请注意， SCROLL 和 NO SCROLL 不能在 OPEN 中指定，因为游标的滚动行为已经确定。

可以使用_positional_或_named_符号传递参数值。在位置符号中，所有参数按顺序指定。在命名符号中，使用_:=_指定每个参数的名称以将其与参数表达式分隔开。类似于调用函数（在 Section 4.3中描述），它还允许混合位置和命名符号。

示例（这些示例使用上述游标声明示例）：

OPEN curs2;
OPEN curs3(42);
OPEN curs3(key := 42);

由于变量替换是在绑定游标的查询上完成的，因此实际上有两种方法可以将值传递到游标中：一种是使用 OPEN 的显式参数，另一种是通过在查询中引用 PL/pgSQL 变量。但是，只有在声明绑定游标之前声明的变量才会被替换到其中。在这两种情况下，要传递的值在 OPEN 时确定。例如，获得与上面 curs3 示例相同效果的另一种方法是

DECLARE
    key integer;
    curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
BEGIN
    key := 42;
    OPEN curs4;

43.7.3. Using Cursors #

游标一旦被打开，就可以使用此处描述的语句进行操作。

这些操作不需要在最初打开游标的同一函数中进行。你可以将 refcursor 值从一个函数返回，让调用方对游标进行操作。（在内部，refcursor 值只是包含游标有效查询的门户的字符串名称。这个名称可以到处传递，分配给其他 refcursor 变量，等等，而不会干扰门户。）

所有门户在事务结束时都会隐式关闭。因此，只能在事务结束之前使用 refcursor 值来引用打开的游标。

43.7.3.1. FETCH #

FETCH [ direction { FROM | IN } ] cursor INTO target;

FETCH 将下一行从游标检索到目标，该目标可以是行变量、记录变量或逗号分隔的简单变量列表，就像 SELECT INTO 一样。如果没有下一行，目标将设置为 NULL(s)。与 SELECT INTO 一样，可以检查特殊变量 FOUND，以查看是否获得了行。

direction 子句可以是 SQL FETCH 命令中允许的任何变体，但不能获取多于一行；即它可以是 NEXT 、 PRIOR 、 FIRST 、 LAST 、 ABSOLUTE count 、 RELATIVE count 、 FORWARD 或 BACKWARD 。省略 direction 与指定 NEXT 相同。在使用 count 的形式中， count 任何整数值表达式（与 SQL FETCH 命令不同，后者只允许一个整数常量）。如果游标是用 SCROLL 选项声明或打开，那么需要向后移动的 direction 值可能会失败。

cursor 必须是引用打开游标门户的 refcursor 变量的名称。

示例：

FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
FETCH LAST FROM curs3 INTO x, y;
FETCH RELATIVE -2 FROM curs4 INTO x;

43.7.3.2. MOVE #

MOVE [ direction { FROM | IN } ] cursor;

MOVE 对游标重新定位而不检索任何数据。MOVE 的工作方式与 FETCH 命令完全相同，不同之处在于它只重新定位游标，而不返回移动到的行。与 SELECT INTO 一样，可以检查特殊变量 FOUND，以查看是否有下一行可以移动到。

示例：

MOVE curs1;
MOVE LAST FROM curs3;
MOVE RELATIVE -2 FROM curs4;
MOVE FORWARD 2 FROM curs4;

43.7.3.3. UPDATE/DELETE WHERE CURRENT OF #

UPDATE table SET ... WHERE CURRENT OF cursor;
DELETE FROM table WHERE CURRENT OF cursor;

当游标定位在表行上时，可以使用游标来识别该行，从而更新或删除该行。游标的查询有一些限制（特别是没有分组），最好在游标中使用 FOR UPDATE 。有关更多信息，请参阅 DECLARE 参考页面。

示例：

UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;

43.7.3.4. CLOSE #

CLOSE cursor;

CLOSE 关闭打开游标下的门户。这可用于在事务结束之前释放资源，或释放游标变量以再次打开。

示例：

CLOSE curs1;

43.7.3.5. Returning Cursors #

PL/pgSQL 函数可以向调用方返回游标。这可用于返回多行或多列，尤其当结果集非常大的时候。要做到这一点，函数可以打开游标并将游标名称返回给调用方（或只使用或以其他方式让调用方知道的门户名称打开游标）。然后调用方可以从游标中提取行。游标可以由调用方关闭，也可以在事务关闭时自动关闭。

用于游标的门户名称可以由程序员指定或自动生成。要指定门户名称，只需在打开它之前将一个字符串分配给 refcursor 变量即可。refcursor 变量的字符串值将由 OPEN 用作底层门户的名称。但是，如果 refcursor 变量的值为 null（在默认情况下它会为 null），那么 OPEN 将自动生成一个与任何现有门户不冲突的名称，并将它分配给 refcursor 变量。

Note

在 PostgreSQL 16 之前，受限游标变量被初始化为包含它们自己的名称，而不是保留为 null，这样底层门户名称将默认与游标变量的名称相同。这样做是因为在不同函数中以相似方式命名的游标之间产生冲突的风险太大。

下面的示例显示了调用方如何提供游标名称的一种方法：

CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
    OPEN $1 FOR SELECT col FROM test;
    RETURN $1;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;

以下示例使用自动游标名称生成：

CREATE FUNCTION reffunc2() RETURNS refcursor AS '
DECLARE
    ref refcursor;
BEGIN
    OPEN ref FOR SELECT col FROM test;
    RETURN ref;
END;
' LANGUAGE plpgsql;

-- need to be in a transaction to use cursors.
BEGIN;
SELECT reffunc2();

      reffunc2
--------------------
 <unnamed cursor 1>
(1 row)

FETCH ALL IN "<unnamed cursor 1>";
COMMIT;

下面的示例演示了从单个函数中返回多个游标的一种方法：

CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGIN
    OPEN $1 FOR SELECT * FROM table_1;
    RETURN NEXT $1;
    OPEN $2 FOR SELECT * FROM table_2;
    RETURN NEXT $2;
END;
$$ LANGUAGE plpgsql;

-- need to be in a transaction to use cursors.
BEGIN;

SELECT * FROM myfunc('a', 'b');

FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;

43.7.4. Looping through a Cursor’s Result #

有一个变体 FOR 语句允许迭代游标返回的行。语法如下：

[ <<label>> ]
FOR recordvar IN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ] LOOP
    statements
END LOOP [ label ];

游标变量一定在声明时已绑定到某个查询，并且它_cannot_已经是打开状态。_FOR_语句会自动打开该游标，并且在循环退出时再次关闭该游标。实际参数值表达式的列表必须出现（只要、并且只有当游标声明要使用参数时）。这些值将按与_OPEN_期间完全相同的方式替换在查询中（参见 Section 43.7.2.3）。

变量 recordvar 自动定义为类型 record，并且只存在于循环内部（循环内部将忽略变量名称的任何现有定义）。游标返回的每一行都会顺序分配给这个记录变量，然后执行循环体。