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

примечание

Эта страница переведена при помощи нейросети GigaChat.

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

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

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

variable { := | = } expression;

Как объяснялось ранее, выражение в таком операторе вычисляется с помощью команды 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/MERGE с 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 query;

Это команда выполняет запрос (query) и отбрасывает результат. Запишите query так же, как бы записали команду 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_expressions INTO [STRICT] target FROM ...;
INSERT ... RETURNING expressions INTO [STRICT] target;
UPDATE ... RETURNING expressions INTO [STRICT] target;
DELETE ... RETURNING expressions INTO [STRICT] target;
MERGE ... RETURNING выражения INTO [STRICT] цель;

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

Совет

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

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

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

Если STRICT не указан в предложении INTO, то target будет установлен на первую строку, возвращаемую командой, или на нулевые значения, если команда не вернула ни одной строки. (Обратите внимание, что «первая строка» не определена до тех пор, пока не был использован 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/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: $1 = 'nosuchuser'
CONTEXT:  PL/pgSQL function get_userid(text) line 6 at SQL statement

Примечание

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

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

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

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

где 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;

Более чистый подход – использовать спецификацию format() с функцией %I для вставки имен таблиц или столбцов с автоматической кавычкой:

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.

Примечание

Инструкция PL/pgSQL EXECUTE не связана с инструкцией EXECUTE SQL, поддерживаемой сервером PostgreSQL. Инструкцию сервера EXECUTE нельзя использовать непосредственно внутри функций PL/pgSQL (и она не нужна).

Пример. Цитирование значений в динамических запросах

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

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

Как всегда, необходимо соблюдать осторожность, чтобы убедиться, что нулевые значения в запросе не дают нежелательных результатов. Например, предложение WHERE:

'WHERE key = ' || quote_nullable(keyvalue)

Никогда не будет успешным, если keyvalue равно нулю, потому что результат использования оператора равенства = с операндом, равным нулю, всегда равен нулю. Если нужно, чтобы ноль работал как обычное ключевое значение, то нужно переписать вышеуказанное как:

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

В настоящее время IS NOT DISTINCT FROM обрабатывается гораздо менее эффективно, чем =, поэтому не делайте этого, если этого не требуется. См. раздел «Функции и операторы сравнения» для получения дополнительной информации о нулевых значениях и IS DISTINCT.

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

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

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

Динамические операторы SQL также могут быть безопасно построены с использованием функции 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 можно увидеть в примере «Портирование функции, которая создает другую функцию из PL/SQL в PL/pgSQL», который строит и выполняет команду CREATE FUNCTION для определения новой функции.

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

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

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

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

GET DIAGNOSTICS integer_var = ROW_COUNT;

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

Имя	Тип	Описание
ROW_COUNT	bigint	Количество строк, обработанных последней командой SQL
PG_CONTEXT	text	Строка(и) текста, описывающая текущую стек вызовов
PG_ROUTINE_OID	oid	OID текущей функции

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

Ничего не делать

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