Курсоры
Эта страница переведена при помощи нейросети GigaChat.
Вместо выполнения всего запроса сразу можно настроить курсор, который включает запрос, а затем считывать результат запроса несколько строк за раз. Одна из причин для этого – избежать переполнения памяти, когда результат содержит большое количество строк. Однако пользователям PL/pgSQL обычно не нужно беспокоиться об этом, поскольку FOR циклы автоматически используют внутренний курсор для предотвращения проблем с памятью. Более интересное использование – вернуть ссылку на курсор, созданный функцией, что позволяет вызывающему считывать строки. Это обеспечивает эффективный способ возврата больших наборов строк из функций.
Объявление переменных курсора
Доступ ко всем курсорам в PL/pgSQL осуществляется через переменные курсора, которые всегда имеют специальный тип данных refcursor. Один из способов создания переменной курсора - просто объявить ее как переменную типа refcursor. Другой способ - использовать синтаксис объявления курсора, который в общем виде выглядит так:
name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;
FOR можно заменить на IS для совместимости с Oracle. Если указано 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, но первая может использоваться с любым запросом, вторая уже имеет полностью определенный запрос, связанный с ней, а последняя связана с параметризованным запросом. key будет заменен целочисленным параметром при открытии курсора. Переменная curs1 называется не связанной, поскольку она не привязана к какому-либо конкретному запросу.
Параметр SCROLL не может использоваться при запросе курсора с использованием FOR UPDATE/SHARE. Также лучше всего использовать NO SCROLL с запросом, который включает нестабильные функции. Реализация SCROLL предполагает, что повторное чтение вывода запроса даст согласованные результаты, чего нестабильная функция может и не сделать.
Открытие курсоров
Перед тем, как можно будет использовать курсор для получения строк, его необходимо открыть. Это эквивалентно команде SQL DECLARE CURSOR. В PL/pgSQL есть три формы оператора OPEN, две из которых используют не связанные переменные курсора, а третья использует связанную переменную курсора.
Связанные переменные курсора также могут использоваться без явного открытия курсора с помощью оператора FOR, описанного в разделе «Обработка курсора в цикле». Цикл FOR откроет курсор и снова закроет его после завершения цикла.
Открытие курсора включает создание внутренней структуры данных сервера под названием портал, которая хранит состояние выполнения запроса курсора. Порталу присваивается имя, которое должно быть уникальным в рамках сеанса на протяжении существования портала. По умолчанию PL/pgSQL назначает уникальное имя каждому создаваемому порталу. Однако, если присвоить непустую строку переменной курсора, эта строка будет использоваться в качестве имени портала. Эта особенность используется, как описано в разделе Возвращение курсоров.
OPEN FOR query
OPEN unbound_cursorvar [ [ 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 FOR EXECUTE
OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR EXECUTE query_string
[ USING expression [, ... ] ];
Переменная курсора открывается и получает указанный запрос для выполнения. Курсор не может быть уже открыт и он должен быть объявлен как несвязанная переменная курсора (то есть, как просто переменная refcursor). Запрос указывается как выражение строки, таким же образом, как и в команде EXECUTE. Как обычно, это дает гибкость так что план запроса может изменяться от одного запуска к следующему (см. раздел «Кеширование планов»), а также означает, что замена переменных не выполняется в строке команды. Как и с EXECUTE, значения параметров могут быть вставлены в динамическую команду через format() и USING. Параметры SCROLL и NO SCROLL имеют то же значение, что и для связанного курсора.
Пример:
OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;
В этом примере имя таблицы вставляется в запрос через format(). Значение сравнения для col1 вставляется с помощью параметра USING, поэтому кавычки не требуются.
Открытие связанного курсора
OPEN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ];
Эта форма OPEN используется для открытия курсорной переменной, чей запрос был связан с ней при объявлении. Курсор уже не может быть открыт. Список выражений значений фактических аргументов должен появиться тогда и только тогда, когда курсор был объявлен для принятия аргументов. Эти значения будут подставлены в запрос.
План запроса для привязанного курсора всегда считается кешируемым, в данном случае нет эквивалента EXECUTE. Обратите внимание, что SCROLL и NO SCROLL не могут быть указаны в OPEN, поскольку поведение прокрутки курсора уже было определено.
Значения аргументов могут быть переданы с использованием либо позиционной, либо именованной нотации. В позиционной нотации все аргументы указываются по порядку. В именованной нотации имя каждого аргумента указывается с помощью := для отделения его от выражения аргумента. Аналогично вызову функций, описанному в разделе «Вызов функций», также допускается смешивать позиционную и именованную нотацию.
Примеры (эти примеры используют приведенные выше объявления курсоров):
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;
Использование курсоров
После того как курсор был открыт, с ним можно работать при помощи описанных здесь операторов.
Эти манипуляции не обязательно должны происходить в одной и той же функции, которая изначально открыла курсор. Можно вернуть значение refcursor из функции и позволить вызывающему оператору работать с курсором. Внутренне, значение refcursor – это просто имя строки так называемого портала, содержащего активный запрос для курсора. Это имя может передаваться, присваиваться другим refcursor переменным и т.д., без нарушения работы портала.
Все порталы закрываются автоматически при завершении транзакции. Поэтому значение refcursor может использоваться для ссылки на открытый курсор только до конца транзакции.
FETCH
FETCH [ direction { FROM | IN } ] cursor INTO target;
FETCH извлекает следующую строку из курсора в целевую переменную, которая может быть переменной строки, переменной записи или разделенным запятыми списком простых переменных, точно так же, как и SELECT INTO. Если следующей строки нет, то цель устанавливается в NULL. Как и с SELECT INTO, специальная переменная FOUND может быть проверена для определения того, была ли получена строка или нет.
Предложение direction может быть любой из допустимых вариантов в команде SQL FETCH, за исключением тех, которые могут извлечь более одной строки, а именно, это может быть NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, FORWARD или BACKWARD. Пропуск direction эквивалентен указанию NEXT. В формах, использующих count, выражение count может быть любым выражением со значением целого числа (в отличие от команды SQL FETCH, которая допускает только целую константу). Значения direction, требующие перемещения назад, скорее всего, не будут работать, если курсор был объявлен или открыт без опции SCROLL.
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;
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;
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;
CLOSE
CLOSE cursor;
CLOSE закрывает портал, лежащий в основе открытого курсора. Его можно использовать для освобождения ресурсов раньше окончания транзакции или для освобождения переменной курсора, чтобы ее можно было снова открыть.
Пример:
CLOSE curs1;
Возврат курсора из функции
Функции PL/pgSQL могут возвращать курсоры вызывающему объекту. Это полезно для возврата нескольких строк или столбцов, особенно с очень большими наборами результатов. Для этого функция открывает курсор и возвращает имя курсора вызывающему объекту, или просто открывает курсор, используя имя портала, указанное вызывающим объектом или иным образом известное вызывающему объекту. Вызывающий объект может затем извлекать строки из курсора. Курсор может быть закрыт вызывающим объектом, либо он будет автоматически закрыт при закрытии транзакции.
Имя портала, используемое для курсора, может быть указано программистом или автоматически сгенерировано. Чтобы указать имя портала, просто присвойте строку переменной refcursor перед ее открытием. Строковое значение переменной refcursor будет использоваться OPEN в качестве имени основного портала. Однако, если переменная refcursor равна нулю, 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;
-- 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;
Обработка курсора в цикле
Существует вариант оператора FOR, который позволяет выполнять итерацию по строкам, возвращаемым курсором. Синтаксис следующий:
[ <<label>> ]
FOR recordvar IN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ] LOOP
statements
END LOOP [ label ];
Переменная курсора должна быть связана с каким-либо запросом при ее объявлении, и она не может быть уже открыта. Оператор FOR автоматически открывает курсор, а затем закрывает его снова, когда цикл завершается. Список выражений значений фактических аргументов должен появляться тогда и только тогда, когда курсор был объявлен для принятия аргументов. Эти значения будут подставлены в запрос точно так же, как и во время выполнения команды OPEN (см. раздел «Открытие связанного курсора»).
Переменная recordvar автоматически определяется как тип record и существует только внутри цикла (любое существующее определение имени переменной игнорируется внутри цикла). Каждая строка, возвращаемая курсором, последовательно присваивается этой переменной записи, и выполняется тело цикла.