41.7. Курсоры #
Вместо того чтобы сразу выполнять весь запрос, есть возможность настроить курсор, инкапсулирующий запрос, и затем получать результат запроса по нескольку строк за раз. Одна из причин так делать заключается в том, чтобы избежать переполнения памяти, когда результат содержит большое количество строк. (Пользователям PL/pgSQL не нужно об этом беспокоиться, так как циклы FOR
автоматически используют курсоры, чтобы избежать проблем с памятью.) Более интересным вариантом использования является возврат из функции ссылки на курсор, что позволяет вызывающему получать строки запроса. Это эффективный способ получать большие наборы строк из функций.
41.7.1. Объявление курсорных переменных #
Доступ к курсорам в PL/pgSQL осуществляется через курсорные переменные, которые всегда имеют специальный тип данных refcursor
. Один из способов создать курсорную переменную, просто объявить её как переменную типа refcursor
. Другой способ заключается в использовании синтаксиса объявления курсора, который в общем виде выглядит так:
имя
[ [ NO ] SCROLL ] CURSOR [ (аргументы
) ] FORзапрос
;
(Для совместимости с Oracle, FOR
можно заменять на IS
.) С указанием SCROLL
курсор можно будет прокручивать назад. При NO SCROLL
прокрутка назад не разрешается. Если ничего не указано, то возможность прокрутки назад зависит от запроса. Если указаны аргументы
, то они должны представлять собой пары
, разделённые через запятую. Эти пары определяют имена, которые будут заменены значениями параметров в данном запросе. Фактические значения для замены этих имён появятся позже, при открытии курсора.имя
тип_данных
Примеры:
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
), так как к ней не привязан никакой запрос.
Реализация SCROLL
рассчитывает на то, что в запросе курсора используется FOR UPDATE/SHARE
. Кроме того, с запросом, включающим изменчивые функции, лучше всего использовать NO SCROLL
. Реализация SCROLL
предполагает, что повторное чтение вывода запроса даст согласованные результаты, чего нельзя гарантировать при использовании изменчивой функции.
41.7.2. Открытие курсора #
Прежде чем получать строки из курсора, его нужно открыть. (Это эквивалентно действию SQL-команды DECLARE CURSOR
.) В PL/pgSQL есть три формы оператора OPEN
, две из которых используются для несвязанных курсорных переменных, а третья для связанных.
Примечание
Связанные курсорные переменные можно использовать с циклом FOR
без явного открытия курсора, как описано в Подразделе 41.7.4. Цикл FOR
откроет курсор, а затем закроет его по завершении цикла.
Открытие курсора подразумевает создание внутренней для сервера структуры данных под названием портал, который удерживает состояние выполнения для запроса курсора. У портала есть имя, которое должно быть уникальным в рамках сеанса на протяжении существования портала. По умолчанию PL/pgSQL присваивает уникальное имя создаваемому им порталу. Однако если присвоить переменной курсора непустое строковое значение, в качестве имени портала будет использовано именно оно. Использование этой функциональности описано в Подразделе 41.7.3.5.
41.7.2.1. OPEN FOR
query
#
OPENнесвязанная_переменная_курсора
[[NO] SCROLL] FORquery
;
Курсорная переменная открывается и получает конкретный запрос для выполнения. Курсор не может уже быть открытым, а курсорная переменная обязана быть несвязанной (то есть просто переменной типа refcursor
). Запрос должен быть командой SELECT
или любой другой, которая возвращает строки (к примеру EXPLAIN
). Запрос обрабатывается так же, как и другие команды SQL в PL/pgSQL: имена переменных PL/pgSQL заменяются на значения, план запроса кешируется для повторного использования. Подстановка значений переменных PL/pgSQL проводится при открытии курсора командой OPEN
, последующие изменения значений переменных не влияют на работу курсора. SCROLL
и NO SCROLL
имеют тот же смысл, что и для связанного курсора.
Пример:
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
41.7.2.2. OPEN FOR EXECUTE
#
OPENнесвязанная_переменная_курсора
[[NO] SCROLL] FOR EXECUTEстрока_запроса
[USINGвыражение
[, ...]];
Переменная курсора открывается и получает конкретный запрос для выполнения. Курсор не может быть уже открыт и он должен быть объявлен как несвязанная переменная курсора (то есть, как просто переменная refcursor
). Запрос задаётся строковым выражением, так же, как в команде EXECUTE
. Как обычно, это даёт возможность гибко менять план запроса от раза к разу (см. Подраздел 41.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
, так что заключать его в апострофы не нужно.
41.7.2.3. Открытие связанного курсора #
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;
41.7.3. Использование курсоров #
После того как курсор будет открыт, с ним можно работать при помощи описанных здесь операторов.
Работать с курсором необязательно в той же функции, где он был открыт. Из функции можно вернуть значение с типом refcursor
, что позволит вызывающему продолжить работу с курсором. (Внутри refcursor
представляет собой обычное строковое имя портала, содержащего активный запрос курсора. Это имя можно передавать, присваивать другим переменным с типом refcursor
и так далее, при этом портал не нарушается.)
Все порталы неявно закрываются в конце транзакции, поэтому значение refcursor
можно использовать для ссылки на открытый курсор только до конца транзакции.
41.7.3.1. FETCH
#
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;
41.7.3.2. MOVE
#
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;
41.7.3.3. UPDATE/DELETE WHERE CURRENT OF
#
UPDATEтаблица
SET ... WHERE CURRENT OFкурсор
; DELETE FROMтаблица
WHERE CURRENT OFкурсор
;
Когда курсор позиционирован на строку таблицы, эту строку можно изменить или удалить при помощи курсора. Есть ограничения на то, каким может быть запрос курсора (в частности, не должно быть группировок), и крайне желательно использовать указание FOR UPDATE
. За дополнительными сведениями обратитесь к странице справки DECLARE.
Пример:
UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;
41.7.3.4. CLOSE
#
CLOSE курсор
;
CLOSE
закрывает связанный с курсором портал. Используется для того, чтобы освободить ресурсы раньше, чем закончится транзакция, или чтобы освободить курсорную переменную для повторного открытия.
Пример:
CLOSE curs1;
41.7.3.5. Возврат курсора из функции #
Курсоры можно возвращать из функции на PL/pgSQL. Это полезно, когда нужно вернуть множество строк и столбцов, особенно если выборки очень большие. Для этого, в функции открывается курсор и его имя возвращается вызывающему (или просто открывается курсор, используя указанное имя портала, каким-либо образом известное вызывающему). Вызывающий затем может извлекать строки из курсора. Курсор может быть закрыт вызывающим или он будет автоматически закрыт при завершении транзакции.
Имя портала, используемое для курсора, может быть указано разработчиком или будет генерироваться автоматически. Чтобы указать имя портала, нужно просто присвоить строку в переменную refcursor
перед его открытием. Значение строки переменной refcursor
будет использоваться командой OPEN
как имя портала. Однако если переменная refcursor
имеет значение NULL (как и установлено по умолчанию), OPEN
автоматически генерирует имя, которое не конфликтует с любым существующим порталом, и присваивает его переменной refcursor
.
Примечание
В версиях 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; -- для использования курсоров, необходимо начать транзакцию 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;
41.7.4. Обработка курсора в цикле #
Один из вариантов цикла FOR
позволяет перебирать строки, возвращённые курсором. Вот его синтаксис:
[ <<метка
>> ] FORпеременная-запись
INсвязанная_переменная_курсора
[ ( [имя_аргумента
:= ]значение_аргумента
[, ...] ) ] LOOPоператоры
END LOOP [метка
];
Курсорная переменная должна быть связана с запросом при объявлении. Курсор не может быть открытым. Команда FOR
автоматически открывает курсор и автоматически закрывает при завершении цикла. Список фактических значений аргументов должен присутствовать только в том случае, если курсор объявлялся с параметрами. Эти значения будут подставлены в запрос, как и при выполнении OPEN
(см. Подраздел 41.7.2.3).
Данная переменная-запись
автоматически определяется как переменная типа record
и существует только внутри цикла (другие объявленные переменные с таким именем игнорируется в цикле). Каждая возвращаемая курсором строка последовательно присваивается этой переменной и выполняется тело цикла.