41.5. Основные операторы #
В этом и последующих разделах описаны все типы операторов, которые понимает PL/pgSQL. Все, что не признается в качестве одного из этих типов операторов, считается командой SQL и отправляется для исполнения в основную машину базы данных, как описано в Подразделе 41.5.2.
41.5.1. Присваивания #
Присваивание значения переменной PL/pgSQL записывается в виде:
переменная
{ := | = }выражение
;
Как описывалось ранее, выражение в таком операторе вычисляется с помощью SQL-команды SELECT
, посылаемой в основную машину базы данных. Выражение должно получить одно значение (возможно, значение строки, если это переменная-кортеж или переменная типа record
). Целевая переменная может быть простой переменной (возможно, дополненной именем блока); полем в целевом кортеже или записи; или элементом или срезом целевого массива. Для присваивания можно использовать знак равенства (=
) вместо совместимого с PL/SQL :=
.
Если тип данных результата выражения не соответствует типу данных переменной, это значение будет преобразовано к нужному типу с использованием приведения присваивания (см. Раздел 10.4). В случае отсутствия приведения присваивания для этой пары типов, интерпретатор PL/pgSQL попытается преобразовать значение результата через текстовый формат, то есть применив функцию вывода типа результата, а за ней функцию ввода типа переменной. Заметьте, что при этом функция ввода может выдавать ошибки времени выполнения, если не воспримет строковое представление значения результата.
Примеры:
tax := subtotal * 0.06; my_record.user_id := 20; my_array[j] := 20; my_array[1:3] := array[1,2,3]; complex_array[n].realpart = 12.3;
41.5.2. Выполнение команд SQL #
Вообще говоря, в функции на PL/pgSQL можно выполнить любую команду SQL, не возвращающую строк, просто написав эту команду. Например, можно создать таблицу и заполнить её данными, написав
CREATE TABLE mytable (id int primary key, data text); INSERT INTO mytable VALUES (1,'one'), (2,'two');
Если команда всё же возвращает строки (например, SELECT
или INSERT
/UPDATE
/DELETE
/MERGE
с предложением RETURNING
), есть два варианта действий. Если команда возвращает максимум одну строку или вам интересна только первая строка результата, напишите команду как обычно, но добавьте предложение INTO
для захвата вывода, как описано в Подразделе 41.5.3. Чтобы обрабатывать все результирующие строки, запишите команду в качестве источника данных для цикла FOR
, как описано в Подразделе 41.6.6.
Обычно недостаточно выполнять только статически определённые SQL-команды. Как правило, желательно, чтобы в команде использовались различные значения данных или она менялась более кардинально, например использовала разные имена таблиц от запуска к запуску. Это можно сделать двумя способами.
Значения переменных PL/pgSQL могут автоматически вставляться в оптимизируемые SQL-команды: SELECT
, INSERT
, UPDATE
, DELETE
, MERGE
и определённые служебные команды, содержащие вышеперечисленные, например EXPLAIN
и CREATE TABLE ... AS SELECT
. Имя любой переменной PL/pgSQL в текстах этих команд рассматривается как параметр, и значение переменной подставляется в качестве значения параметра во время выполнения. Это в точности совпадает с описанной ранее обработкой для выражений; за подробностями обратитесь к Подразделу 41.11.1.
При выполнении оптимизируемой SQL-команды таким образом, PL/pgSQL может кешировать и повторно использовать план выполнения команды, как обсуждается в Подразделе 41.11.2.
Неоптимизируемые SQL-команды (также называемые служебными командами) не принимают параметры запроса. Поэтому в таких командах не работает автоматическая замена переменных PL/pgSQL. Чтобы включить изменяемый текст в служебную команду, запускаемую из PL/pgSQL, необходимо составить текст команды в виде строки и выполнить её в EXECUTE
, как описано в Подразделе 41.5.4.
Если нужно изменять команду, не просто передавая ей разные значения данных, а например меняя имя таблицы, также нужно использовать оператор EXECUTE
.
Иногда бывает полезно вычислить значение выражения или запроса SELECT
, но отказаться от результата, например, при вызове функции, у которой есть побочные эффекты, но нет полезного результата. Для этого в PL/pgSQL, используется оператор PERFORM
:
PERFORM запрос
;
Эта команда выполняет запрос
и отбрасывает результат. Запросы
пишутся таким же образом, как и в команде SQL SELECT
, но ключевое слово SELECT
заменяется на PERFORM
. Для запросов WITH
после PERFORM
нужно поместить запрос в скобки. (В этом случае запрос может вернуть только одну строку.) Переменные PL/pgSQL будут подставлены в запрос так же, как описано выше, план запроса также кешируется. Кроме того, специальная переменная FOUND
принимает значение true, если запрос возвращает, по крайней мере, одну строку, или false, если не возвращает ни одной строки (см. Подраздел 41.5.5).
Примечание
Можно предположить, что такой же результат получается непосредственно командой SELECT
, но в настоящее время использование PERFORM
является единственным способом. Команда SQL, которая может возвращать строки, например SELECT
, будет отклонена с ошибкой, если не имеет предложения INTO
, как описано в следующем разделе.
Пример:
PERFORM create_mv('cs_session_page_requests_mv', my_query);
41.5.3. Выполнение команды, возвращающей одну строку #
Результат SQL-команды, возвращающей одну строку (возможно из нескольких столбцов), может быть присвоен переменной типа record
, переменной-кортежу или списку скалярных переменных. Для этого нужно к основной команде SQL добавить предложение INTO
. Так, например:
SELECTвыражения_select
INTO [STRICT]цель
FROM ...; INSERT ... RETURNINGвыражения
INTO [STRICT]цель
; UPDATE ... RETURNINGвыражения
INTO [STRICT]цель
; DELETE ... RETURNINGвыражения
INTO [STRICT]цель
; MERGE ... RETURNINGвыражения
INTO [STRICT]цель
;
где цель
может быть переменной типа record
, переменной-кортежем или разделённым запятыми списком скалярных переменных, полей записи/строки. Переменные PL/pgSQL подставляются в оставшуюся часть команды (то есть везде, кроме предложения INTO
), как было описано выше, и план выполнения кешируется так же. Это работает для команд SELECT
, INSERT
/UPDATE
/DELETE
/MERGE
с предложением RETURNING
и определённых служебных команд, возвращающих результат в виде набора строк (таких как EXPLAIN
). За исключением предложения INTO
, это те же SQL-команды, как их можно написать вне PL/pgSQL.
Подсказка
Обратите внимание, что данная интерпретация SELECT
с INTO
полностью отличается от Postgres Pro команды SELECT INTO
, где в INTO
указывается вновь создаваемая таблица. Если вы хотите в функции на PL/pgSQL создать таблицу, основанную на результате команды SELECT
, используйте синтаксис CREATE TABLE ... AS SELECT
.
Если результат команды присваивается переменной-кортежу или списку переменных, то они должны в точности соответствовать по количеству и типам данных столбцам результата, иначе произойдёт ошибка во время выполнения. Если используется переменная типа record
, то она автоматически приводится к типу строки результата команды.
Предложение INTO может появиться практически в любом месте SQL-команды. Обычно его записывают непосредственно перед или сразу после списка выражения_select
в SELECT
или в конце команды для команд других типов. Рекомендуется следовать этому соглашению на случай, если правила разбора PL/pgSQL ужесточатся в будущих версиях.
Если указание STRICT
отсутствует в предложении INTO
, то цели
присваивается первая строка, возвращённая командой; или NULL, если команда не вернула строки. (Заметим, что понятие «первая строка» определяется неоднозначно без ORDER BY
.) Все остальные строки результата после первой отбрасываются. Можно проверить специальную переменную FOUND
(см. Подраздел 41.5.5), чтобы определить, была ли возвращена запись:
SELECT * INTO myrec FROM emp WHERE empname = myname; IF NOT FOUND THEN RAISE EXCEPTION 'Сотрудник % не найден', myname; END IF;
Если добавлено указание STRICT
, то команда должна вернуть ровно одну строку или произойдёт ошибка во время выполнения: либо NO_DATA_FOUND
(нет строк), либо TOO_MANY_ROWS
(более одной строки). Можно использовать секцию исключений в блоке для обработки ошибок, например:
BEGIN SELECT * INTO STRICT myrec FROM emp WHERE empname = myname; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE EXCEPTION 'Сотрудник % не найден', myname; WHEN TOO_MANY_ROWS THEN RAISE EXCEPTION 'Сотрудник % уже существует', myname; END;
После успешного выполнения команды с указанием STRICT
, значение переменной FOUND
всегда принимает значение true.
Для INSERT
/UPDATE
/DELETE
/MERGE
с RETURNING
, PL/pgSQL возвращает ошибку, если выбрано более одной строки, даже в том случае, когда указание STRICT
отсутствует. Так происходит потому, что у этих команд нет возможности, типа ORDER BY
, указать какая из задействованных строк должна быть возвращена.
Если для функции включён режим print_strict_params
, то при возникновении ошибки, связанной с нарушением условия STRICT
, в детальную (DETAIL
) часть сообщения об ошибке будет включена информация о параметрах, переданных команде. Изменить значение print_strict_params
можно установкой параметра plpgsql.print_strict_params
. Но это повлияет только на функции, скомпилированные после изменения. Для конкретной функции можно использовать указание компилятора, например:
CREATE FUNCTION get_userid(username text) RETURNS int AS $$ #print_strict_params on DECLARE userid int; BEGIN SELECT users.userid INTO STRICT userid FROM users WHERE users.username = get_userid.username; RETURN userid; END; $$ LANGUAGE plpgsql;
В случае сбоя будет сформировано примерно такое сообщение об ошибке
ERROR: query returned no rows DETAIL: parameters: username = 'nosuchuser' CONTEXT: PL/pgSQL function get_userid(text) line 6 at SQL statement
Примечание
С указанием STRICT
поведение SELECT INTO
и связанных операторов соответствует принятому в Oracle PL/SQL.
41.5.4. Выполнение динамически формируемых команд #
Часто требуется динамически формировать команды внутри функций на PL/pgSQL, то есть такие команды, в которых при каждом выполнении могут использоваться разные таблицы или типы данных. Обычно PL/pgSQL кеширует планы выполнения (как описано в Подразделе 41.11.2), но в случае с динамическими командами это не будет работать. Для исполнения динамических команд предусмотрен оператор EXECUTE
:
EXECUTEстрока-команды
[ INTO [STRICT]цель
] [ USINGвыражение
[, ... ] ];
где строка-команды
это выражение, формирующее строку (типа text
) с текстом команды, которую нужно выполнить. Необязательная цель
— это переменная-запись, переменная-кортеж или разделённый запятыми список простых переменных и полей записи/кортежа, куда будут помещены результаты команды. Необязательные выражения в USING
формируют значения, которые будут вставлены в команду.
В сформированном тексте команды замена имён переменных PL/pgSQL на их значения проводиться не будет. Все необходимые значения переменных должны быть вставлены в командную строку при её построении, либо нужно использовать параметры, как описано ниже.
Также нет никакого кеширования плана для команд, выполняемых с помощью EXECUTE
. Вместо этого план создаётся каждый раз при выполнении. Таким образом, строка команды может динамически создаваться внутри функции для выполнения действий с различными таблицами и столбцами.
Предложение INTO
указывает, куда должны быть помещены результаты SQL-команды, возвращающей строки. Если используется переменная-кортеж или список переменных, то они должны в точности соответствовать структуре результата команды; если используется переменная типа record
, она автоматически приводится к типу строки результата запроса. Если возвращается несколько строк, то только первая будет присвоена переменной(ым) в INTO
. Если не возвращается ни одной строки, то присваивается NULL. Без предложения INTO
результаты команды отбрасываются.
С указанием STRICT
команда должна вернуть ровно одну строку, иначе выдаётся сообщение об ошибке.
В тексте команды можно использовать значения параметров, ссылки на параметры обозначаются как $1
, $2
и т. д. Эти символы указывают на значения, находящиеся в предложении USING
. Такой метод зачастую предпочтительнее, чем вставка значений в команду в виде текста: он позволяет исключить во время выполнения дополнительные расходы на преобразования значений в текст и обратно, и не открывает возможности для SQL-инъекций, не требуя применять экранирование или кавычки для спецсимволов. Пример:
EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2' INTO c USING checked_user, checked_date;
Обратите внимание, что символы параметров можно использовать только вместо значений данных. Если же требуется динамически формировать имена таблиц или столбцов, их необходимо вставлять в виде текста. Например, если в предыдущем запросе необходимо динамически задавать имя таблицы, можно сделать следующее:
EXECUTE 'SELECT count(*) FROM ' || quote_ident(tabname) || ' WHERE inserted_by = $1 AND inserted <= $2' INTO c USING checked_user, checked_date;
Более аккуратным решением будет использование указания %I
с функцией format()
, позволяющее вставить имя таблицы или столбца, которое будет автоматически заключено в кавычки:
EXECUTE format('SELECT count(*) FROM %I ' 'WHERE inserted_by = $1 AND inserted <= $2', tabname) INTO c USING checked_user, checked_date;
(Этот пример задействует SQL-правило, согласно которому строковые значения, разделённые символом новой строки, соединяются вместе.)
Ещё одно ограничение состоит в том, что символы параметров могут использоваться только в оптимизируемых SQL-командах (SELECT
, INSERT
, UPDATE
, DELETE
, MERGE
и некоторых командах, содержащих одну из них). В операторы других типов (обычно называемые служебными) значения нужно вставлять в текстовом виде, даже если это просто значения данных.
Команда EXECUTE
c неизменяемым текстом и параметрами USING (как в первом примере выше), функционально эквивалентна команде, записанной напрямую в PL/pgSQL, в которой переменные PL/pgSQL автоматически заменяются значениями. Важное отличие в том, что EXECUTE
при каждом исполнении заново строит план команды с учётом текущих значений параметров, тогда как PL/pgSQL строит общий план выполнения и кеширует его при повторном использовании. В тех случаях, когда наилучший план выполнения сильно зависит от значений параметров, может быть полезно использовать EXECUTE
для гарантии того, что не будет выбран общий план.
В настоящее время команда SELECT INTO
не поддерживается в EXECUTE
, вместо этого нужно выполнять обычный SELECT
и указать INTO
для самой команды EXECUTE
.
Примечание
Оператор EXECUTE
в PL/pgSQL не имеет отношения к одноимённому SQL-оператору сервера Postgres Pro. Серверный EXECUTE
не может напрямую использоваться в функциях на PL/pgSQL (и в этом нет необходимости).
Пример 41.1. Использование кавычек в динамических запросах
При работе с динамическими командами часто приходится иметь дело с экранированием одинарных кавычек. Рекомендуемым методом для взятия текста в кавычки в теле функции является экранирование знаками доллара. (Если имеется унаследованный код, не использующий этот метод, пожалуйста, обратитесь к обзору в Подразделе 41.12.1, это поможет сэкономить усилия при переводе кода к более приемлемому виду.)
Динамические значения требуют особого внимания, так как они могут содержать апострофы. Например, можно использовать функцию format()
(предполагается, что тело функции заключается в доллары, так что апострофы дублировать не нужно):
EXECUTE format('UPDATE tbl SET %I = $1 ' 'WHERE key = $2', colname) USING newvalue, keyvalue;
Также можно напрямую вызывать функции заключения в кавычки:
EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = ' || quote_literal(newvalue) || ' WHERE key = ' || quote_literal(keyvalue);
Этот пример демонстрирует использование функций quote_ident
и quote_literal
(см. Раздел 9.4). Для надёжности, выражения, содержащие идентификаторы столбцов и таблиц должны использовать функцию quote_ident
при добавлении в текст запроса. А для выражений со значениями, которые должны быть обычными строками, используется функция quote_literal
. Эти функции выполняют соответствующие шаги, чтобы вернуть текст, заключённый в двойные или одинарные кавычки соответственно и с правильно экранированными специальными символами.
Так как функция quote_literal
помечена как STRICT
, то она всегда возвращает NULL, если переданный ей аргумент имеет значение NULL. В приведённом выше примере, если newvalue
или keyvalue
были NULL, вся строка с текстом запроса станет NULL, что приведёт к ошибке в EXECUTE
. Для предотвращения этой проблемы используйте функцию quote_nullable
, которая работает так же, как quote_literal
за исключением того, что при вызове с пустым аргументом возвращает строку 'NULL'. Например:
EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = ' || quote_nullable(newvalue) || ' WHERE key = ' || quote_nullable(keyvalue);
Если вы имеете дело со значениями, которые могут быть пустыми, то, как правило, нужно использовать quote_nullable
вместо quote_literal
.
Как обычно, необходимо убедиться, что значения NULL в запросе не принесут неожиданных результатов. Например, следующее условие WHERE
'WHERE key = ' || quote_nullable(keyvalue)
никогда не выполнится, если keyvalue
— NULL, так как применение =
с операндом, имеющим значение NULL, всегда даёт NULL. Если требуется, чтобы NULL обрабатывалось как обычное значение, то условие выше нужно переписать так:
'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)
(В настоящее время IS NOT DISTINCT FROM
работает менее эффективно, чем =
, так что используйте этот способ, только если это действительно необходимо. Подробнее особенности NULL и IS DISTINCT
описаны в Разделе 9.2.)
Обратите внимание, что использование знака $ полезно только для взятия в кавычки фиксированного текста. Плохая идея написать этот пример так:
EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = $$' || newvalue || '$$ WHERE key = ' || quote_literal(keyvalue);
потому что newvalue
может также содержать $$
. Эта же проблема может возникнуть и с любым другим разделителем, используемым после знака $
. Поэтому, чтобы безопасно заключить заранее неизвестный текст в кавычки, нужно использовать соответствующие функции: quote_literal
, quote_nullable
, или quote_ident
.
Динамические операторы SQL также можно безопасно сформировать, используя функцию format
(см. Подраздел 9.4.1). Например:
EXECUTE format('UPDATE tbl SET %I = %L ' 'WHERE key = %L', colname, newvalue, keyvalue);
Указание %I
равнозначно вызову quote_ident
, а %L
— вызову quote_nullable
. Функция format
может применяться в сочетании с предложением USING
:
EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname) USING newvalue, keyvalue;
Эта форма лучше, так как с ней переменные обрабатываются в их собственном формате данных, а не преобразуются безусловно в текст, чтобы затем выводиться с использованием %L
. Она также и более эффективна.
Более объёмный пример использования динамической команды и EXECUTE
можно увидеть в Примере 41.10. В нём создаётся и динамически выполняется команда CREATE FUNCTION
для определения новой функции.
41.5.5. Статус выполнения команды #
Определить результат команды можно несколькими способами. Во-первых, можно воспользоваться командой GET DIAGNOSTICS
, имеющей форму:
GET [ CURRENT ] DIAGNOSTICSпеременная
{ = | := }элемент
[ , ... ];
Эта команда позволяет получить системные индикаторы состояния. Слово CURRENT
не несёт смысловой нагрузки (но см. также описание GET STACKED DIAGNOSTICS
в Подразделе 41.6.8.1). Каждый элемент
представляется ключевым словом, указывающим, какое значение состояния нужно присвоить заданной переменной
(она должна иметь подходящий тип данных, чтобы принять его). Доступные в настоящее время элементы состояния показаны в Таблице 41.1. Вместо принятого в стандарте SQL присваивания (=
) можно применять присваивание с двоеточием (:=
). Например:
GET DIAGNOSTICS integer_var = ROW_COUNT;
Таблица 41.1. Доступные элементы диагностики
Имя | Тип | Описание |
---|---|---|
ROW_COUNT | bigint | число строк, обработанных последней командой SQL |
PG_CONTEXT | text | строки текста, описывающие текущий стек вызовов (см. Подраздел 41.6.9) |
PG_ROUTINE_OID | oid | OID текущей функции |
Второй способ определения статуса выполнения команды заключается в проверке значения специальной переменной FOUND
, имеющей тип boolean
. При вызове функции на PL/pgSQL, переменная FOUND
инициализируется в ложь. Далее, значение переменной изменяется следующими операторами:
SELECT INTO
записывает вFOUND
true, если строка присвоена, или false, если строки не были получены.PERFORM
записывает вFOUND
true, если строки выбраны (и отброшены) или false, если строки не выбраны.UPDATE
,INSERT
,DELETE
иMERGE
записывают вFOUND
true, если при их выполнении была задействована хотя бы одна строка, или false, если ни одна строка не была задействована.FETCH
записывают вFOUND
true, если команда вернула строку, или false, если строка не выбрана.MOVE
записывают вFOUND
true при успешном перемещении курсора, в противном случае — false.FOR
, как иFOREACH
, записывает вFOUND
true, если была произведена хотя бы одна итерация цикла, в противном случае — false. При этом значениеFOUND
будет установлено только после выхода из цикла. Пока цикл выполняется, оператор цикла не изменяет значение переменной. Но другие операторы внутри цикла могут менять значениеFOUND
.RETURN QUERY
иRETURN QUERY EXECUTE
записывают вFOUND
true, если запрос вернул хотя бы одну строку, или false, если строки не выбраны.
Другие операторы PL/pgSQL не меняют значение FOUND
. Помните в частности, что EXECUTE
изменяет вывод GET DIAGNOSTICS
, но не меняет FOUND
.
FOUND
является локальной переменной в каждой функции PL/pgSQL и любые её изменения, влияют только на текущую функцию.
41.5.6. Не делать ничего #
Иногда бывает полезен оператор, который не делает ничего. Например, он может показывать, что одна из ветвей if/then/else сознательно оставлена пустой. Для этих целей используется NULL
:
NULL;
В следующем примере два фрагмента кода эквивалентны:
BEGIN y := x / 0; EXCEPTION WHEN division_by_zero THEN NULL; -- ошибка игнорируется END;
BEGIN y := x / 0; EXCEPTION WHEN division_by_zero THEN -- ошибка игнорируется END;
Какой вариант выбрать — дело вкуса.
Примечание
В Oracle PL/SQL не допускаются пустые списки операторов, поэтому NULL
обязателен в подобных ситуациях. В PL/pgSQL разрешается не писать ничего.