Курсоры

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

Объявление переменных курсора

Все обращение к курсорам в 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, но первую можно использовать с любым запросом, тогда как со второй уже связан полностью определенный запрос, а с последней связан параметризованный запрос. (key будет заменен на целочисленное значение параметра при открытии курсора.) Переменная curs1 называется несвязанной, поскольку она не связана ни с каким конкретным запросом.

Параметр SCROLL нельзя использовать, когда в запросе курсора применяется FOR UPDATE/SHARE. Кроме того, с запросом, включающим изменчивые функции, лучше всего использовать NO SCROLL. Реализация SCROLL предполагает, что повторное чтение вывода запроса даст согласованные результаты, которых изменчивая функция гарантировать не может.

Открытие курсоров

Прежде чем курсор можно будет использовать для извлечения строк, его необходимо открыть. (Это действие равнозначно команде SQL DECLARE CURSOR). В PL/pgSQL имеется три формы оператора OPEN, две из которых используют несвязанные переменные курсора, а третья — связанную.

Примечание
Связанные переменные курсора также можно использовать без явного открытия курсора, с помощью оператора FOR, описанного в подразделе Цикл по результату курсора. Цикл FOR откроет курсор, а затем снова закроет его по завершении цикла.

Открытие курсора включает в себя создание внутренней для сервера структуры данных, называемой порталом, который удерживает состояние выполнения для запроса курсора. У портала есть имя, которое должно быть уникальным в рамках сеанса на протяжении существования портала. По умолчанию PL/pgSQL будет присваивать уникальное имя каждому создаваемому им порталу. Однако если присвоить переменной курсора строковое значение, отличное от NULL, эта строка будет использоваться в качестве имени его портала. Использование этого функционала описано в подразделе Возврат курсоров.

OPEN FOR запрос

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

Переменная курсора открывается и получает указанный запрос для выполнения. Курсор уже не может быть открытым и должен быть объявлен как несвязанная переменная курсора (то есть как простая переменная 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 несвязанная_переменная_курсора [ [ NO ] SCROLL ] FOR EXECUTE строка_запроса
                                                  [ USING выражение [, ... ] ];

Переменная курсора открывается и получает заданный запрос для выполнения. Курсор не может быть уже открытым и должен быть объявлен как несвязанная переменная курсора (то есть как простая переменная 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 связанная_переменная_курсора [ ( [ имя_аргумента := ] значение_аргумента [, ...] ) ];

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

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

Значения аргумента можно передать посредством позиционной или именованной нотации. В позиционной нотации все аргументы указываются по порядку. В именованной нотации имя каждого аргумента отделяется от выражения аргумента с помощью символов :=. Как и в случае вызова функций, описанного в разделе Вызов функции, также допускается смешивание позиционной и именованной нотации.

Примеры (здесь используются объявления курсора из примеров выше):

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 [ направление { FROM | IN } ] курсор INTO цель;

Оператор FETCH извлекает следующую строку (в указанном направлении) из курсора в цель, которая может быть переменной строки, переменной записи или списком разделенных запятыми простых переменных, подобно 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

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/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;

CLOSE

CLOSE курсор;

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

Пример:

CLOSE curs1;

Возврат курсоров

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

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

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

Цикл по результату курсора

Существует вариант оператора FOR, позволяющий перебирать строки, возвращаемые курсором. Синтаксис:

[ <<метка>> ]
FOR переменная_записи IN связанная_переменная_курсора [ ( [ имя_аргумента := ] значение_аргумента [, ...] ) ] LOOP
    операторы
END LOOP [ метка ];

Переменная курсора при объявлении должна быть привязана к какому-либо запросу и уже не может быть открытой. Оператор FOR автоматически открывает курсор и снова закрывает его при выходе из цикла. Список фактических выражений значений аргументов должен присутствовать тогда и только тогда, когда курсор был объявлен как принимающий аргументы. Эти значения будут подставлены в запросе точно так же, как во время OPEN (см. подраздел Открытие связанного курсора).

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

Copyright © KVANTOM LLC, 2019-2025