41.5. Основные операторы#
41.5. Основные операторы
В этом разделе и последующих мы описываем все типы операторов, которые явно понимаются PL/pgSQL. Любая команда, не распознанная как один из этих типов операторов, считается SQL-командой и отправляется в основной движок базы данных для выполнения, как описано в Раздел 41.5.2.
41.5.1. Назначение
Присвоение значения переменной PL/pgSQL записывается следующим образом:
variable{ := | = }expression;
Как уже объяснялось ранее, выражение в таком операторе вычисляется
с помощью команды SQL SELECT, отправляемой в основной
движок базы данных. Выражение должно возвращать одно значение (возможно
значение строки, если переменная является переменной строки или записи). Целевая
переменная может быть простой переменной (опционально с указанием имени блока),
полем строки или записи, или элементом или срезом
целевого массива. Вместо 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-команд
В общем случае, любая SQL-команда, которая не возвращает строки, может быть выполнена внутри функции PL/pgSQL, просто написав команду. Например, вы можете создать и заполнить таблицу, написав
CREATE TABLE mytable (id int primary key, data text); INSERT INTO mytable VALUES (1,'one'), (2,'two');
Если команда возвращает строки (например, SELECT,
или INSERT/UPDATE/DELETE
с 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 query;
Это выполняет query и отбрасывает результат. Напишите query так же, как вы бы написали команду 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-команды, возвращающей одну строку (возможно, с несколькими столбцами), может быть присвоен переменной типа запись, переменной типа строки или списку скалярных переменных. Для этого нужно написать базовую SQL-команду и добавить предложение INTO. Например,
SELECTselect_expressionsINTO [STRICT]targetFROM ...; INSERT ... RETURNINGexpressionsINTO [STRICT]target; UPDATE ... RETURNINGexpressionsINTO [STRICT]target; DELETE ... RETURNINGexpressionsINTO [STRICT]target;
где target может быть переменной типа запись, переменной строки или списком простых переменных и полей записи/строки.
Переменные PL/pgSQL будут подставлены в остальную часть команды (то есть все, кроме предложения INTO), как описано выше, и план будет кеширован таким же образом.
Это работает для команд SELECT, INSERT/UPDATE/DELETE с предложением RETURNING и некоторых утилитарных команд, возвращающих наборы строк, таких как EXPLAIN.
За исключением предложения INTO, SQL-команда будет такой же, как если бы она была написана вне PL/pgSQL.
Подсказка
Обратите внимание, что данная интерпретация команды SELECT с использованием INTO существенно отличается от обычной команды SELECT INTO в Tantor SE, где INTO указывает на создание новой таблицы. Если вы хотите создать таблицу на основе результата команды SELECT внутри функции PL/pgSQL, используйте синтаксис CREATE TABLE ... AS SELECT.
Если в качестве цели используется переменная строки или список переменных, столбцы результата команды должны точно соответствовать структуре цели по количеству и типам данных, иначе произойдет ошибка времени выполнения. Когда переменная записи является целью, она автоматически настраивается на тип строки столбцов результата команды.
Предложение INTO может появляться практически в любом месте SQL-команды. Обычно она записывается либо непосредственно перед, либо непосредственно после списка select_expressions в команде SELECT, или в конце команды для других типов команд. Рекомендуется следовать этой конвенции на случай, если парсер PL/pgSQL станет более строгим в будущих версиях.
Если в предложении INTO не указано STRICT, то target будет установлено в первую строку, возвращенную командой, или в null, если команда не вернула ни одной строки. (Обратите внимание, что “первая строка” не определена, если вы не использовали ORDER BY). Любые строки результата после первой строки отбрасываются. Вы можете проверить специальную переменную FOUND (см. Раздел 41.5.5) для определения, была ли возвращена строка.
SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN
RAISE EXCEPTION 'employee % not found', 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 'employee % not found', myname;
WHEN TOO_MANY_ROWS THEN
RAISE EXCEPTION 'employee % not unique', myname;
END;
Успешное выполнение команды с использованием STRICT всегда устанавливает значение FOUND в true.
Для INSERT/UPDATE/DELETE с использованием
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: $1 = '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:
EXECUTEcommand-string[В [STRICT]target] [USINGexpression[, ... ]];
где command-string - это выражение, возвращающее строку (типа text), содержащую команду для выполнения. Опциональный параметр target - это переменная записи, переменная строки или список простых переменных и полей записи/строки, в которые будут сохранены результаты команды. Опциональные выражения USING предоставляют значения, которые будут вставлены в команду.
При вычислении строки команды не происходит подстановки переменных PL/pgSQL. Любые необходимые значения переменных должны быть вставлены в строку команды при ее конструировании; или вы можете использовать параметры, как описано ниже.
Также, для команд, выполняемых через EXECUTE, нет кэширования плана. Вместо этого, команда всегда планируется каждый раз при выполнении оператора. Таким образом, строка команды может быть динамически создана внутри функции для выполнения операций над различными таблицами и столбцами.
Предложение INTO указывает, куда должны быть присвоены результаты SQL-команды, возвращающей строки. Если предоставлены переменная строки или список переменных, они должны точно соответствовать структуре результатов команды; если предоставлена переменная записи, она автоматически настраивается на соответствие структуре результата. Если возвращается несколько строк, только первая будет присвоена переменной(ы) INTO. Если не возвращается ни одной строки, переменной(ы) 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 с простой постоянной командной строкой и некоторыми параметрами USING, как в первом примере выше, функционально эквивалентно простому написанию команды непосредственно в PL/pgSQL и автоматическому замене переменных PL/pgSQL. Важное отличие заключается в том, что EXECUTE будет повторно планировать команду при каждом выполнении, генерируя план, который специфичен для текущих значений параметров, тогда как PL/pgSQL может создавать общий план и кешировать его для повторного использования. В ситуациях, когда лучший план зависит от значений параметров, может быть полезно использовать EXECUTE, чтобы убедиться, что выбран общий план.
В настоящее время SELECT INTO не поддерживается внутри EXECUTE; вместо этого выполните обычную команду SELECT и укажите INTO как часть самой команды EXECUTE.
Примечание
Оператор PL/pgSQL
EXECUTE не связано с
EXECUTE SQL-оператором, поддерживаемым сервером
Tantor SE. Оператор 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 аргументом она возвращает строку NULL. Например,
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = '
|| quote_nullable(newvalue)
|| ' WHERE key = '
|| quote_nullable(keyvalue);
Если вы работаете с значениями, которые могут быть null, обычно следует использовать тег quote_nullable вместо тега quote_literal.
Как всегда, необходимо обеспечить, чтобы нулевые значения в запросе не приводили к непреднамеренным результатам. Например, условие WHERE
'WHERE key = ' || quote_nullable(keyvalue)
никогда не выполнится, если keyvalue равно null, потому что
результат использования оператора равенства = с null операндом
всегда равен null. Если вы хотите, чтобы null работал как обычное значение ключа,
вам нужно переписать вышеприведенное как
'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)
(В настоящее время IS NOT DISTINCT FROM обрабатывается намного менее эффективно, чем =, поэтому не используйте это, если необходимо. См. Раздел 9.2 для получения дополнительной информации о значениях NULL и IS DISTINCT).
Обратите внимание, что использование долларовых кавычек полезно только для цитирования фиксированного текста. Попытка написать этот пример следующим образом была бы очень плохой идеей:
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 ] DIAGNOSTICSvariable{ = | := }item[ , ... ];
Эта команда позволяет получить индикаторы состояния системы.
CURRENT - это шумовое слово (но см. также GET STACKED
DIAGNOSTICS в Раздел 41.6.8.1).
Каждый item - это ключевое слово, идентифицирующее
значение статуса, которое должно быть присвоено указанной переменной variable
(которая должна быть правильного типа данных для его получения). В настоящее время
доступные элементы состояния показаны
в Таблица 41.1. Вместо стандартного SQL-компонента =
можно использовать двоеточие-равно (:=). Пример:
GET DIAGNOSTICS integer_var = ROW_COUNT;
Таблица 41.1. Доступные элементы диагностики
| Имя | Тип | Описание |
|---|---|---|
ROW_COUNT | bigint | количество обработанных строк последней выполненной SQL-команды |
PG_CONTEXT | text | строка(и) текста, описывающая текущий стек вызовов (см. Раздел 41.6.9) |
Второй способ определения эффектов команды - проверить специальную переменную с именем FOUND, которая имеет тип boolean. FOUND изначально устанавливается в значение false в каждом вызове функции PL/pgSQL. Она устанавливается каждым из следующих типов операторов:
Оператор
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 EXECUTEstatements устанавливают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; -- ignore the error
END;
BEGIN
y := x / 0;
EXCEPTION
WHEN division_by_zero THEN -- ignore the error
END;
Какой из них предпочтительнее - вопрос вкуса.
Примечание
В PL/SQL Oracle пустые списки операторов не допускаются, поэтому операторы NULL являются обязательными в таких ситуациях. В PL/pgSQL вы можете просто ничего не писать.