Postgresql 中文操作指南
43.7. Cursors #
与其一次执行整个查询,不如设置一个封装查询的 cursor ,然后一次读取几行查询结果。这样做的一个原因是避免在结果包含大量行时出现内存溢出。(但是, PL/pgSQL 用户通常不必为此担心,因为 FOR 循环会自动在内部使用游标来避免内存问题。)一个更有趣的用法是返回函数创建的游标引用,允许调用者读取行。这提供了一种从函数返回大型行集的有效方法。
Rather than executing a whole query at once, it is possible to set up a cursor that encapsulates the query, and then read the query result a few rows at a time. One reason for doing this is to avoid memory overrun when the result contains a large number of rows. (However, PL/pgSQL users do not normally need to worry about that, since FOR loops automatically use a cursor internally to avoid memory problems.) A more interesting usage is to return a reference to a cursor that a function has created, allowing the caller to read the rows. This provides an efficient way to return large row sets from functions.
43.7.1. Declaring Cursor Variables #
PL/pgSQL 中对游标的所有访问都通过游标变量进行,游标变量始终属于特殊数据类型 refcursor 。创建游标变量的一种方法是简单地将其声明为类型为 refcursor 的变量。另一种方法是使用游标声明语法,通常为:
All access to cursors in PL/pgSQL goes through cursor variables, which are always of the special data type refcursor. One way to create a cursor variable is just to declare it as a variable of type refcursor. Another way is to use the cursor declaration syntax, which in general is:
name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;
( FOR 为了与 Oracle 相兼容,可以替换为 IS 。)如果指定了 SCROLL ,游标能够向后滚动;如果指定了 NO SCROLL ,将拒绝向后提取,如果未出现任何规范,则允许或不允许向后提取取决于查询。如果指定了 arguments ,则它是一个逗号分隔的对 name datatype 的列表,用于定义在给定查询中要由参数值替换的名称。这些名称的实际替换值将在稍后打开游标时指定。
(FOR can be replaced by IS for Oracle compatibility.) If SCROLL is specified, the cursor will be capable of scrolling backward; if NO SCROLL is specified, backward fetches will be rejected; if neither specification appears, it is query-dependent whether backward fetches will be allowed. arguments, if specified, is a comma-separated list of pairs name_ _datatype that define names to be replaced by parameter values in the given query. The actual values to substitute for these names will be specified later, when the cursor is opened.
举例:
Some examples:
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 ,因为它未绑定到任何特定查询。
All three of these variables have the data type refcursor, but the first can be used with any query, while the second has a fully specified query already bound to it, and the last has a parameterized query bound to it. (key will be replaced by an integer parameter value when the cursor is opened.) The variable curs1 is said to be unbound since it is not bound to any particular query.
当游标的查询使用 FOR UPDATE/SHARE 时, SCROLL 选项不可用。此外,最好在涉及易失函数的查询中使用 NO SCROLL 。 SCROLL 的实现假设重新读取查询输出将给出一致的结果,而易失函数可能无法做到这一点。
The SCROLL option cannot be used when the cursor’s query uses FOR UPDATE/SHARE. Also, it is best to use NO SCROLL with a query that involves volatile functions. The implementation of SCROLL assumes that re-reading the query’s output will give consistent results, which a volatile function might not do.
43.7.2. Opening Cursors #
在游标可用于检索行之前,必须对其 opened 。(这相当于 SQL 命令 DECLARE CURSOR 。)PL/pgSQL 有三种形式的 OPEN 语句,其中两种使用非绑定的游标变量,而第三种使用绑定的游标变量。
Before a cursor can be used to retrieve rows, it must be opened. (This is the equivalent action to the SQL command DECLARE CURSOR.) PL/pgSQL has three forms of the OPEN statement, two of which use unbound cursor variables while the third uses a bound cursor variable.
Note
通过 Section 43.7.4 中描述的 FOR 语句,还可以使用绑定游标变量而不显式打开游标。一个 FOR 循环将在循环完成后打开游标,然后再次关闭它。
Bound cursor variables can also be used without explicitly opening the cursor, via the FOR statement described in Section 43.7.4. A FOR loop will open the cursor and then close it again when the loop completes.
打开游标涉及创建服务器内部数据结构,称为_portal_,它保存游标查询的执行状态。门户有一个名称,它在门户生存期间必须在会话中唯一。默认情况下,PL/pgSQL将为它创建的每个门户分配一个唯一的名称。但是,如果你为游标变量分配一个非空字符串值,该字符串将用作其门户名称。此功能可按 Section 43.7.3.5中所述使用。
Opening a cursor involves creating a server-internal data structure called a portal, which holds the execution state for the cursor’s query. A portal has a name, which must be unique within the session for the duration of the portal’s existence. By default, PL/pgSQL will assign a unique name to each portal it creates. However, if you assign a non-null string value to a cursor variable, that string will be used as its portal name. This feature can be used as described in 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 选项的含义与绑定游标相同。
The cursor variable is opened and given the specified query to execute. The cursor cannot be open already, and it must have been declared as an unbound cursor variable (that is, as a simple refcursor variable). The query must be a SELECT, or something else that returns rows (such as EXPLAIN). The query is treated in the same way as other SQL commands in PL/pgSQL: PL/pgSQL variable names are substituted, and the query plan is cached for possible reuse. When a PL/pgSQL variable is substituted into the cursor query, the value that is substituted is the one it has at the time of the OPEN; subsequent changes to the variable will not affect the cursor’s behavior. The SCROLL and NO SCROLL options have the same meanings as for a bound cursor.
示例:
An example:
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_选项具有与绑定游标相同的含义。
The cursor variable is opened and given the specified query to execute. The cursor cannot be open already, and it must have been declared as an unbound cursor variable (that is, as a simple refcursor variable). The query is specified as a string expression, in the same way as in the EXECUTE command. As usual, this gives flexibility so the query plan can vary from one run to the next (see Section 43.11.2), and it also means that variable substitution is not done on the command string. As with EXECUTE, parameter values can be inserted into the dynamic command via format() and USING. The SCROLL and NO SCROLL options have the same meanings as for a bound cursor.
示例:
An example:
OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;
在这个示例中,表名称通过 format() 插入到查询中。 col1 的比较值通过 USING 参数插入,因此不需要引用。
In this example, the table name is inserted into the query via format(). The comparison value for col1 is inserted via a USING parameter, so it needs no quoting.
43.7.2.3. Opening a Bound Cursor #
OPEN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ];
这种形式的 OPEN 用于打开游标变量,其查询在声明时与其绑定。游标不能已经打开。当且仅当声明游标需要参数时,才必须出现实参值表达式的列表。这些值将替换在查询中。
This form of OPEN is used to open a cursor variable whose query was bound to it when it was declared. The cursor cannot be open already. A list of actual argument value expressions must appear if and only if the cursor was declared to take arguments. These values will be substituted in the query.
绑定游标的查询计划始终被视为可缓存的;在这种情况下没有相当于 EXECUTE 的内容。请注意, SCROLL 和 NO SCROLL 不能在 OPEN 中指定,因为游标的滚动行为已经确定。
The query plan for a bound cursor is always considered cacheable; there is no equivalent of EXECUTE in this case. Notice that SCROLL and NO SCROLL cannot be specified in OPEN, as the cursor’s scrolling behavior was already determined.
可以使用_positional_或_named_符号传递参数值。在位置符号中,所有参数按顺序指定。在命名符号中,使用_:=_指定每个参数的名称以将其与参数表达式分隔开。类似于调用函数(在 Section 4.3中描述),它还允许混合位置和命名符号。
Argument values can be passed using either positional or named notation. In positional notation, all arguments are specified in order. In named notation, each argument’s name is specified using := to separate it from the argument expression. Similar to calling functions, described in Section 4.3, it is also allowed to mix positional and named notation.
示例(这些示例使用上述游标声明示例):
Examples (these use the cursor declaration examples above):
OPEN curs2;
OPEN curs3(42);
OPEN curs3(key := 42);
由于变量替换是在绑定游标的查询上完成的,因此实际上有两种方法可以将值传递到游标中:一种是使用 OPEN 的显式参数,另一种是通过在查询中引用 PL/pgSQL 变量。但是,只有在声明绑定游标之前声明的变量才会被替换到其中。在这两种情况下,要传递的值在 OPEN 时确定。例如,获得与上面 curs3 示例相同效果的另一种方法是
Because variable substitution is done on a bound cursor’s query, there are really two ways to pass values into the cursor: either with an explicit argument to OPEN, or implicitly by referencing a PL/pgSQL variable in the query. However, only variables declared before the bound cursor was declared will be substituted into it. In either case the value to be passed is determined at the time of the OPEN. For example, another way to get the same effect as the curs3 example above is
DECLARE
key integer;
curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
BEGIN
key := 42;
OPEN curs4;
43.7.3. Using Cursors #
游标一旦被打开,就可以使用此处描述的语句进行操作。
Once a cursor has been opened, it can be manipulated with the statements described here.
这些操作不需要在最初打开游标的同一函数中进行。你可以将 refcursor 值从一个函数返回,让调用方对游标进行操作。(在内部,refcursor 值只是包含游标有效查询的门户的字符串名称。这个名称可以到处传递,分配给其他 refcursor 变量,等等,而不会干扰门户。)
These manipulations need not occur in the same function that opened the cursor to begin with. You can return a refcursor value out of a function and let the caller operate on the cursor. (Internally, a refcursor value is simply the string name of the portal containing the active query for the cursor. This name can be passed around, assigned to other refcursor variables, and so on, without disturbing the portal.)
所有门户在事务结束时都会隐式关闭。因此,只能在事务结束之前使用 refcursor 值来引用打开的游标。
All portals are implicitly closed at transaction end. Therefore a refcursor value is usable to reference an open cursor only until the end of the transaction.
43.7.3.1. FETCH #
FETCH [ direction { FROM | IN } ] cursor INTO target;
FETCH 将下一行从游标检索到目标,该目标可以是行变量、记录变量或逗号分隔的简单变量列表,就像 SELECT INTO 一样。如果没有下一行,目标将设置为 NULL(s)。与 SELECT INTO 一样,可以检查特殊变量 FOUND,以查看是否获得了行。
FETCH retrieves the next row from the cursor into a target, which might be a row variable, a record variable, or a comma-separated list of simple variables, just like SELECT INTO. If there is no next row, the target is set to NULL(s). As with SELECT INTO, the special variable FOUND can be checked to see whether a row was obtained or not.
direction 子句可以是 SQL FETCH 命令中允许的任何变体,但不能获取多于一行;即它可以是 NEXT 、 PRIOR 、 FIRST 、 LAST 、 ABSOLUTE count 、 RELATIVE count 、 FORWARD 或 BACKWARD 。省略 direction 与指定 NEXT 相同。在使用 count 的形式中, count 任何整数值表达式(与 SQL FETCH 命令不同,后者只允许一个整数常量)。如果游标是用 SCROLL 选项声明或打开,那么需要向后移动的 direction 值可能会失败。
The direction clause can be any of the variants allowed in the SQL FETCH command except the ones that can fetch more than one row; namely, it can be NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, FORWARD, or BACKWARD. Omitting direction is the same as specifying NEXT. In the forms using a count, the count can be any integer-valued expression (unlike the SQL FETCH command, which only allows an integer constant). direction values that require moving backward are likely to fail unless the cursor was declared or opened with the SCROLL option.
cursor 必须是引用打开游标门户的 refcursor 变量的名称。
cursor must be the name of a refcursor variable that references an open cursor portal.
示例:
Examples:
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 repositions a cursor without retrieving any data. MOVE works exactly like the FETCH command, except it only repositions the cursor and does not return the row moved to. As with SELECT INTO, the special variable FOUND can be checked to see whether there was a next row to move to.
示例:
Examples:
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 参考页面。
When a cursor is positioned on a table row, that row can be updated or deleted using the cursor to identify the row. There are restrictions on what the cursor’s query can be (in particular, no grouping) and it’s best to use FOR UPDATE in the cursor. For more information see the DECLARE reference page.
示例:
An example:
UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;
43.7.3.4. CLOSE #
CLOSE cursor;
CLOSE 关闭打开游标下的门户。这可用于在事务结束之前释放资源,或释放游标变量以再次打开。
CLOSE closes the portal underlying an open cursor. This can be used to release resources earlier than end of transaction, or to free up the cursor variable to be opened again.
示例:
An example:
CLOSE curs1;
43.7.3.5. Returning Cursors #
PL/pgSQL 函数可以向调用方返回游标。这可用于返回多行或多列,尤其当结果集非常大的时候。要做到这一点,函数可以打开游标并将游标名称返回给调用方(或只使用或以其他方式让调用方知道的门户名称打开游标)。然后调用方可以从游标中提取行。游标可以由调用方关闭,也可以在事务关闭时自动关闭。
PL/pgSQL functions can return cursors to the caller. This is useful to return multiple rows or columns, especially with very large result sets. To do this, the function opens the cursor and returns the cursor name to the caller (or simply opens the cursor using a portal name specified by or otherwise known to the caller). The caller can then fetch rows from the cursor. The cursor can be closed by the caller, or it will be closed automatically when the transaction closes.
用于游标的门户名称可以由程序员指定或自动生成。要指定门户名称,只需在打开它之前将一个字符串分配给 refcursor 变量即可。refcursor 变量的字符串值将由 OPEN 用作底层门户的名称。但是,如果 refcursor 变量的值为 null(在默认情况下它会为 null),那么 OPEN 将自动生成一个与任何现有门户不冲突的名称,并将它分配给 refcursor 变量。
The portal name used for a cursor can be specified by the programmer or automatically generated. To specify a portal name, simply assign a string to the refcursor variable before opening it. The string value of the refcursor variable will be used by OPEN as the name of the underlying portal. However, if the refcursor variable’s value is null (as it will be by default), then OPEN automatically generates a name that does not conflict with any existing portal, and assigns it to the refcursor variable.
Note
在 PostgreSQL 16 之前,受限游标变量被初始化为包含它们自己的名称,而不是保留为 null,这样底层门户名称将默认与游标变量的名称相同。这样做是因为在不同函数中以相似方式命名的游标之间产生冲突的风险太大。
Prior to PostgreSQL 16, bound cursor variables were initialized to contain their own names, rather than being left as null, so that the underlying portal name would be the same as the cursor variable’s name by default. This was changed because it created too much risk of conflicts between similarly-named cursors in different functions.
下面的示例显示了调用方如何提供游标名称的一种方法:
The following example shows one way a cursor name can be supplied by the caller:
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;
以下示例使用自动游标名称生成:
The following example uses automatic cursor name generation:
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;
下面的示例演示了从单个函数中返回多个游标的一种方法:
The following example shows one way to return multiple cursors from a single function:
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 语句允许迭代游标返回的行。语法如下:
There is a variant of the FOR statement that allows iterating through the rows returned by a cursor. The syntax is:
[ <<label>> ]
FOR recordvar IN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ] LOOP
statements
END LOOP [ label ];
游标变量一定在声明时已绑定到某个查询,并且它_cannot_已经是打开状态。_FOR_语句会自动打开该游标,并且在循环退出时再次关闭该游标。实际参数值表达式的列表必须出现(只要、并且只有当游标声明要使用参数时)。这些值将按与_OPEN_期间完全相同的方式替换在查询中(参见 Section 43.7.2.3)。
The cursor variable must have been bound to some query when it was declared, and it cannot be open already. The FOR statement automatically opens the cursor, and it closes the cursor again when the loop exits. A list of actual argument value expressions must appear if and only if the cursor was declared to take arguments. These values will be substituted in the query, in just the same way as during an OPEN (see Section 43.7.2.3).
变量 recordvar 自动定义为类型 record,并且只存在于循环内部(循环内部将忽略变量名称的任何现有定义)。游标返回的每一行都会顺序分配给这个记录变量,然后执行循环体。
The variable recordvar is automatically defined as type record and exists only inside the loop (any existing definition of the variable name is ignored within the loop). Each row returned by the cursor is successively assigned to this record variable and the loop body is executed.