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

40.5.1. Присваивания

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

variable { := | = } expression;

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

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

Примеры:

tax := subtotal * 0.06;
my_record.user_id := 20;

40.5.2. Выполнение команды, не возвращающей результат

В функции на PL/pgSQL можно выполнить любую команду SQL, не возвращающую строк, просто написав эту команду (например, INSERT без предложения RETURNING).

Имя любой переменной PL/pgSQL в тексте команды рассматривается как параметр, а затем текущее значение переменной подставляется в качестве значения параметра во время выполнения. Это в точности совпадает с описанной ранее обработкой для выражений; за подробностями обратитесь к Подразделу 40.10.1.

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

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

PERFORM query;

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

Пример:

PERFORM create_mv('cs_session_page_requests_mv', my_query);

40.5.3. Выполнение запроса, возвращающего одну строку

Результат SQL-команды, возвращающей одну строку (возможно из нескольких столбцов), может быть присвоен переменной типа record, переменной строкового типа или списку скалярных переменных. Для этого нужно к основной команде SQL добавить предложение INTO. Так, например:

SELECT select_expressions INTO [STRICT] target FROM ...;
INSERT ... RETURNING expressions INTO [STRICT] target;
UPDATE ... RETURNING expressions INTO [STRICT] target;
DELETE ... RETURNING expressions INTO [STRICT] target;

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

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

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

Если указание STRICT отсутствует в предложении INTO, то в target присваивается первая строка, возвращённая запросом; или NULL, если запрос не вернул строк. (Заметим, что понятие "первая строка" определяется неоднозначно без ORDER BY.) Все остальные строки результата после первой отбрасываются. Можно проверить специальную переменную FOUND (см. Подраздел 40.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 всегда устанавливается в истину.

Для 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

Как действовать в случаях, когда требуется обработать несколько строк результата, описано в Подразделе 40.6.4.

40.5.4. Выполнение динамически формируемых команд

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

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

где command-string это выражение, формирующее строку (типа text) с текстом команды, которую нужно выполнить. Необязательный target, куда присваивается результат команды, может быть переменной типа record, строковой переменной или разделённым через запятую списком скалярных переменных, полей записи/строки. Необязательные выражения в 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 '
    || tabname::regclass
    || ' WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

Ещё одно ограничение состоит в том, что символы параметров могут использоваться только в командах SELECT, INSERT, UPDATE и DELETE. В других типах операторов (обычно называемых утилитами), необходимо вставлять значения в виде текста даже там, где требуются просто значения.

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

В настоящее время команда SELECT INTO не поддерживается в EXECUTE, вместо этого нужно выполнять обычный SELECT и указать INTO для самой команды EXECUTE.

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

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

'WHERE key = ' || quote_nullable(keyvalue)

'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)

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

EXECUTE format('UPDATE tbl SET %I = %L WHERE key = %L', colname, newvalue, keyvalue);

EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
   USING newvalue, keyvalue;

Более объёмный пример использования динамической команды и EXECUTE можно увидеть в Примере 40-9. В нём создаётся и динамически выполняется команда CREATE FUNCTION для определения новой функции.

40.5.5. Статус выполнения команды

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

GET [ CURRENT ] DIAGNOSTICS variable { = | := } item [ , ... ];

Эта команда позволяет получить значения индикаторов состояния системы. Каждый item является ключевым словом, идентифицирующим значение состояния, которое будет присвоено указанной переменной. Переменная должна быть соответствующего типа данных. В настоящий момент доступны следующие индикаторы: ROW_COUNT — количество строк, обработанных последней командой SQL; RESULT_OID — OID последней строки, вставленной последней выполненной командой SQL. Обратите внимание, что получать RESULT_OID имеет смысл только после вставки (INSERT) записей в таблицу, содержащую OID. Для GET DIAGNOSTICS можно использовать двоеточие-равно (:=) вместо = в стандарте SQL.

Пример:

GET DIAGNOSTICS integer_var = ROW_COUNT;

Второй способ определения статуса выполнения команды заключается в проверке значения специальной переменной FOUND, имеющей тип boolean. При вызове функции на PL/pgSQL, переменная FOUND инициализируется в ложь. Далее, значение переменной изменяется следующими операторами:

• SELECT INTO устанавливает FOUND в истину, если строка присвоена, или в ложь, если строки не выбраны.

• PERFORM устанавливает FOUND в истину если строки выбраны (затем они отбрасываются), или в ложь, если строки не выбраны.

• UPDATE, INSERT и DELETE устанавливают FOUND в истину, если при их выполнении была задействована хотя бы одна строка, или в ложь, если ни одна строка не была задействована.

• FETCH устанавливают FOUND в истину, если команда вернула строку, или ложь, если строка не выбрана.

• MOVE устанавливает FOUND в истину при успешном перемещении курсора, в противном случае — в ложь.

• FOR, как и FOREACH, устанавливают FOUND в истину, если была произведена хотя бы одна итерация цикла, в противном случае - в ложь. При этом значение FOUND будет установлено только после выхода из цикла. Пока цикл выполняется, оператор цикла не изменяет значение переменной. Но другие операторы внутри цикла могут менять значение FOUND.

• RETURN QUERY и RETURN QUERY EXECUTE устанавливают FOUND в истину, если запрос вернул хотя бы одну строку, или в ложь, если строки не выбраны.

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

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

40.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;

Какой вариант выбрать — дело вкуса.