OPEN несвязанная_переменная_курсора [[NO] SCROLL] FOR query;

Курсорная переменная открывается и получает конкретный запрос для выполнения. Курсор не может уже быть открытым, а курсорная переменная обязана быть несвязанной (то есть просто переменной типа refcursor). Запрос должен быть командой SELECT или любой другой, которая возвращает строки (к примеру EXPLAIN). Запрос обрабатывается так же, как и другие команды SQL в PL/pgSQL: имена переменных PL/pgSQL заменяются на значения, план запроса кешируется для повторного использования. Подстановка значений переменных PL/pgSQL проводится при открытии курсора командой OPEN, последующие изменения значений переменных не влияют на работу курсора. SCROLL и NO SCROLL имеют тот же смысл, что и для связанного курсора.

Пример:

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

OPEN несвязанная_переменная_курсора [[NO] SCROLL] FOR EXECUTE строка_запроса
                                     [USING выражение [, ...]];

Переменная курсора открывается и получает конкретный запрос для выполнения. Курсор не может быть уже открыт и он должен быть объявлен как несвязанная переменная курсора (то есть, как просто переменная refcursor). Запрос задаётся строковым выражением, так же, как в команде EXECUTE. Как обычно, это даёт возможность гибко менять план запроса от раза к разу (см. Подраздел 42.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, так что заключать его в апострофы не нужно.

OPEN связанная_переменная_курсора [( [имя_аргумента :=] значение_аргумента [, ...] )];

Эта форма OPEN используется для открытия курсорной переменной, которая была связана с запросом при объявлении. Курсор не может уже быть открытым. Список фактических значений аргументов должен присутствовать только в том случае, если курсор объявлялся с параметрами. Эти значения будут подставлены в запрос.

План запроса для связанного курсора всегда считается кешируемым. В этом случае нет эквивалента EXECUTE. Обратите внимание, что SCROLL и NO SCROLL не могут быть указаны в этой форме OPEN, возможность прокрутки назад была определена при объявлении курсора.

При передаче значений аргументов можно использовать позиционную или именную нотацию. В позиционной нотации все аргументы указываются по порядку. В именной нотации имя каждого аргумента отделяется от выражения аргумента с помощью :=. Это подобно вызову функций, описанному в Разделе 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;

FETCH [направление { FROM | IN }] курсор INTO цель;

FETCH извлекает следующую строку (в указанном направлении) из курсора в цель. В качестве цели может быть переменная-кортеж, переменная типа record или разделённый запятыми список простых переменных, как и в SELECT INTO. Если подходящей строки нет, цели присваивается NULL. Как и в SELECT INTO, проверить, была ли получена запись, можно при помощи специальной переменной FOUND. Если строка не найдена, курсор устанавливается после последней строки или перед первой строкой в зависимости от направления движения.

Здесь направление может быть любым допустимым в SQL-команде FETCH вариантом, кроме тех, что извлекают более одной строки. А именно: NEXT, PRIOR, FIRST, LAST, ABSOLUTE число, RELATIVE число, FORWARD или BACKWARD. Без указания направления подразумевается вариант NEXT. Везде, где используется число, оно может определяться любым целочисленным выражением (в отличие от SQL-команды FETCH, допускающей только целочисленные константы). Значения направления, которые требуют перемещения назад, приведут к ошибке, если курсор не был объявлен или открыт с указанием SCROLL.

курсор это переменная с типом 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;

MOVE [направление { FROM | IN }] курсор;

MOVE перемещает курсор без извлечения данных. MOVE работает точно так же как и FETCH, но при этом только перемещает курсор и не извлекает строку, к которой он переместился. Для направления можно использовать любой вариант, разрешённый в SQL-команде FETCH, включая извлекающие более одной строки. Курсор будет перемещён на последнюю такую строку. (Однако вариант, в котором направление является выражением числа без ключевого слова, устарел в PL/pgSQL. Этот синтаксис неоднозначен в случаях, когда направление опущено, поэтому может завершаться ошибкой, если число не является константой.) Как и в SELECT INTO, проверить, была ли обнаружена строка для перемещения, можно при помощи специальной переменной FOUND. Если строка не найдена, курсор устанавливается после последней строки или перед первой строкой в зависимости от направления движения.

Примеры:

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

UPDATE таблица SET ... WHERE CURRENT OF курсор;
DELETE FROM таблица WHERE CURRENT OF курсор;

Когда курсор позиционирован на строку таблицы, эту строку можно изменить или удалить при помощи курсора. Есть ограничения на то, каким может быть запрос курсора (в частности, не должно быть группировок), и крайне желательно использовать указание FOR UPDATE. За дополнительными сведениями обратитесь к странице справки DECLARE.

Пример:

UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;

CLOSE курсор;

CLOSE закрывает связанный с курсором портал. Используется для того, чтобы освободить ресурсы раньше, чем закончится транзакция, или чтобы освободить курсорную переменную для повторного открытия.

Пример:

CLOSE curs1;

Курсоры можно возвращать из функции на PL/pgSQL. Это полезно, когда нужно вернуть множество строк и столбцов, особенно если выборки очень большие. Для этого, в функции открывается курсор и его имя возвращается вызывающему (или просто открывается курсор, используя указанное имя портала, каким-либо образом известное вызывающему). Вызывающий затем может извлекать строки из курсора. Курсор может быть закрыт вызывающим или он будет автоматически закрыт при завершении транзакции.

Имя портала, используемое для курсора, может быть указано разработчиком или будет генерироваться автоматически. Чтобы указать имя портала, нужно просто присвоить строку в переменную refcursor перед его открытием. Значение строки переменной refcursor будет использоваться командой OPEN как имя портала. Однако если переменная refcursor имеет значение NULL (как и установлено по умолчанию), OPEN автоматически генерирует имя, которое не конфликтует с любым существующим порталом, и присваивает его переменной refcursor.

Следующий пример показывает один из способов передачи имени курсора вызывающему:

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;

-- для использования курсоров, необходимо начать транзакцию
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;

-- для использования курсоров необходимо начать транзакцию
BEGIN;

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

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