Основные операторы

В этом и следующих разделах описываются все типы операторов, которые явно понятны PL/pgSQL. Все, что не распознается как один из этих типов операторов, считается командой SQL и отправляется на выполнение главному движку базы данных, как описано в подразделе Выполнение команд SQL.

Присваивание

Присвоение значения переменной PL/pgSQL записывается следующим образом:

переменная { := | = } выражение;

Как говорилось ранее, выражение в таком операторе вычисляется с помощью команды SQL SELECT, передаваемой основному движку базы данных. Выражение должно выдавать одно значение (возможно, значение строки, если это переменная строки или записи). Целевая переменная может быть простой переменной (при необходимости дополненной именем блока), полем строки или целевой записью либо элементом или срезом целевого массива. Вместо PL/SQL-совместимого сочетания := можно использовать просто знак равенства (=).

Если тип данных результата выражения не совпадает с типом данных переменной, это значение будет преобразовано, как если бы использовалось приведение присваивания (см. раздел Сохранение значений). Если для пары задействованных типов данных приведение присваивания неизвестно, интерпретатор 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;

Выполнение команд 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 для захвата вывода, как описано в подразделе Выполнение команды с результатом из одной строки. Чтобы обработать все строки вывода, напишите команду как источник данных для цикла FOR, как описано в подразделе Цикл по результатам запроса.

Обычно недостаточно просто выполнить статически определенные команды SQL. Как правило, желательно, чтобы команда использовала разные значения данных или даже менялась в более фундаментальном смысле, например, каждый раз использовала разные имена таблиц. Опять же, это можно сделать двумя способами, в зависимости от ситуации.

Значения переменных PL/pgSQL могут автоматически вставляться в оптимизированные команды SQL: SELECT, INSERT, UPDATE, DELETE, MERGE и определенные служебные команды, содержащие одну из вышеперечисленных команды, например, EXPLAIN и CREATE TABLE ... AS SELECT. Любое имя переменной PL/pgSQL, встречающееся в тексте такой команды, заменяется параметром запроса, а затем текущее значение этой переменной подставляется в качестве значения параметра во время выполнения. Это полностью совпадает с описанной ранее обработкой для выражений; подробную информацию см. в подразделе Подстановка переменных.

При выполнении оптимизируемой команды SQL таким способом PL/pgSQL может кешировать и повторно использовать план выполнения команды, как описано в подразделе Кеширование плана.

Неоптимизируемые команды SQL (также называемые служебными командами) неспособны принимать параметры запроса. Поэтому в таких командах автоматическая подстановка переменных PL/pgSQL не работает. Чтобы включить непостоянный текст в служебную команду, выполняемую из PL/pgSQL, следует собрать такую команду в виде строки, а затем выполнить ее с EXECUTE, как описано в подразделе Выполнение динамических команд.

Также EXECUTE следует использовать, если вы хотите модифицировать команду иным способом, нежели просто передавая ей значения данных, например, изменить имя таблицы.

Иногда бывает полезно вычислить выражение или запрос SELECT, но отбросить результат, например, при вызове функции с побочными эффектами, не имеющей полезного значения результата. Чтобы сделать это в PL/pgSQL, воспользуйтесь оператором PERFORM:

PERFORM запрос;

Эта команда выполняет запрос и отбрасывает результат. Напишите запрос тем же способом, что и в команде SQL SELECT, но замените исходное ключевое слово SELECT на PERFORM. Для запросов WITH используйте PERFORM, а затем поместите запрос в скобки. (В этом случае запрос может вернуть только одну строку.) Переменные PL/pgSQL будут подставляться в запрос так же, как описано выше, и план кешируется таким же образом. Кроме того, специальная переменная FOUND устанавливается в true, если запрос выдал хотя бы одну строку, или в false, если он не выдал ни одной строки (см. подраздел Получение статуса результата).

Примечание
Можно было бы ожидать, что к этому же результату приведет написание SELECT напрямую, но в настоящее время единственным приемлемым способом сделать это является PERFORM. Команда SQL, которая может возвращать строки, например SELECT, будет отклонена с ошибкой, если в ней нет предложения INTO, как описывается в следующем подразделе.

Пример:

PERFORM create_mv('cs_session_page_requests_mv', my_query);

Выполнение команды с результатом из одной строки

Результат команды SQL, выдающей одну строку (возможно, из нескольких столбцов), может быть присвоен переменной записи, переменной типа строки или списку скалярных переменных. Это делается путем написания базовой команды SQL и добавления предложения INTO. Например,

SELECT выражения_select INTO [STRICT] цель FROM ...;
INSERT ... RETURNING выражения INTO [STRICT] цель;
UPDATE ... RETURNING выражения INTO [STRICT] цель;
DELETE ... RETURNING выражения INTO [STRICT] цель;

где цель может быть переменной записи, переменной строки или разделенным запятыми списком простых переменных и полей записи/строки. В остальную часть запроса (то есть везде, кроме предложения INTO) будут подставляться переменные PL/pgSQL, как описано выше, и план будет кеширован таким же образом. Это работает для SELECT, INSERT/UPDATE/DELETE с RETURNING и определенных служебных команд, которые возвращают наборы строк (например, EXPLAIN). За исключением предложения INTO, это такая же команда SQL, как если бы она была написана вне PL/pgSQL.

Совет
Обратите внимание, что эта интерпретация SELECT с INTO сильно отличается от обычной команды SELECT INTO QHB, в которой целью INTO является вновь созданная таблица. Если вы хотите создать таблицу из результата SELECT внутри функции PL/pgSQL, используйте синтаксис CREATE TABLE... AS SELECT.

Если в качестве цели используется строка или список переменных, столбцы результата команды должны точно соответствовать структуре цели в отношении количества и типов данных, иначе произойдет ошибка во время выполнения. Когда целью является переменная записи, она автоматически конфигурируется в тип строки столбцов результата команды.

Предложение INTO может находиться практически в любом месте команды SQL. Обычно оно записывается либо непосредственно перед или сразу после списка выражения_select в команде SELECT, либо в конце команды для других типов команд. Рекомендуется следовать этому соглашению на случай, если анализатор PL/pgSQL в будущих версиях станет более строгим.

Если в предложении INTO не указан STRICT, то цели будет присвоена первая строка, возвращаемая запросом, или значение NULL, если команда не вернула ни одной строки. (Обратите внимание, что «первая строка» не определяется четко, если вы не использовали предложение ORDER BY). Все строки результата после первой отбрасываются. Чтобы определить, была ли возвращена строка, можно проверить специальную переменную FOUND (см. подраздел Получение статуса результата):

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: username = 'nosuchuser'
CONTEXT:  PL/pgSQL function get_userid(text) line 6 at SQL statement
-- ОШИБКА: параметр не вернул ни одной строки
-- ДЕТАЛИЗАЦИЯ: параметры: username = 'nosuchuser'
-- КОНТЕКСТ: строка 6 функции PL/pgSQL get_userid(text) в операторе SQL

Примечание
Параметр STRICT соответствует поведению SELECT INTO и связанных операторов в Oracle PL/SQL.

Выполнение динамических команд

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

EXECUTE командная-строка [ INTO [STRICT] цель ] [ USING выражение [, ... ] ];

где командная-строка представляет собой выражение, выдающее строку (типа text), содержащую выполняемую команду. Необязательная цель является переменной записи, переменной строки или разделенным запятыми списком простых переменных и полей записи/строки, в которых будут сохраняться результаты команды. Необязательные выражения 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 не имеет отношения к оператору SQL EXECUTE, поддерживаемому сервером QHB. Оператор EXECUTE сервера нельзя напрямую использовать в функциях PL/pgSQL (и не нужно).

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

Как всегда, нужно позаботиться о том, чтобы значения 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 см. в разделе Функции и операторы сравнения.)

Обратите внимание, что знаки доллара полезны только для экранирования фиксированного текста. Было бы очень плохой идеей написать этот пример так:

EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = $$'
        || newvalue
        || '$$ WHERE key = '
        || quote_literal(keyvalue);

потому что он бы сломался, если бы содержимое newvalue содержало $$. Та же проблема касается и любого другого разделителя в знаках доллара, который вы можете выбрать. Таким образом, для безопасного заключения к в кавычки текста, который не известен заранее, нужно использовать quote_literal, quote_nullable или quote_ident, в зависимости от ситуации.

Также динамические операторы SQL можно безопасно создавать с помощью функции format (см. подраздел Функция format). Например:

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 можно увидеть в Примере 10, где для определения новой функции создается и выполняется команда CREATE FUNCTION.

Получение статуса результата

Существует несколько способов определить эффект команды. Первый метод заключается в использовании команды GET DIAGNOSTICS, которая имеет вид:

GET [ CURRENT ] DIAGNOSTICS переменная { = | := } элемент [, ... ];

Эта команда позволяет получить системные индикаторы состояния. CURRENT — неучитываемое слово (но см. также описание GET STACKED DIAGNOSTICS в подразделе Получение информации об ошибке). Каждый элемент является ключевым словом, определяющим значение состояния, назначаемое указанной переменной (которая должна иметь правильный тип данных, чтобы его получить). Доступные в настоящее время элементы состояния показаны в Таблице 1. Вместо принятой в стандарте SQL синтаксической единицы = можно использовать сочетание двоеточия и знака равенства (:=). Пример: 

GET DIAGNOSTICS integer_var = ROW_COUNT;

Таблица 1. Доступные элементы диагностики

ИмяТипОписание
ROW_COUNTbigintколичество строк, обработанных самой последней командой SQL
PG_CONTEXTtextстрока(и) текста, описывающего текущий стек вызовов (см. подраздел Получение информации о месте выполнения)
PG_ROUTINE_OIDoidOID текущей функции

Второй метод определения эффектов команды — это проверка специальной переменной с именем 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 EXECUTE устанавливают FOUND в true, если запрос возвращает хотя бы одну строку, и false, если не возвращается ни одной строки.

Другие операторы PL/pgSQL не изменяют состояние FOUND. В частности, обратите внимание, что EXECUTE изменяет вывод GET DIAGNOSTICS, но не меняет FOUND.

FOUND — локальная переменная в каждой функции PL/pgSQL; любые изменения в ней затрагивают только текущую функцию.

Вообще ничего не делать

Иногда бывает полезен оператор-местозаполнитель, который ничего не делает. Например, он может показать, что одна ветвь цепочки если/то/иначе намеренно оставлена пустой. Для этого используется оператор 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 позволяет просто ничего не писать.

Copyright © KVANTOM LLC, 2019-2025