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

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

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

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

В целом, любую команду 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), существует два способа действий. Если команда SQL возвращает не более одной строки или же требуется только первая строка результата, следует записать команду как обычно, но добавить INTO предложение для сохранения выходных данных, как описано в Раздел 5.6.5.3. Чтобы обработать все результирующие строки, следует использовать команду в качестве источника данных для FOR цикла, как описано в Раздел 5.6.6.6.

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

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

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

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

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

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

PERFORM запрос;

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

Пример:

PERFORM create_mv('cs_session_page_requests_mv', my_query);

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

SELECT select_expressions INTO [STRICT] target FROM ...;
INSERT ... RETURNING выражения INTO [STRICT] target;
UPDATE ... RETURNING выражения INTO [STRICT] target;
DELETE ... RETURNING выражения INTO [STRICT] target;
MERGE ... RETURNING выражения INTO [STRICT] target;

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

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

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

Если параметр STRICT не указан в предложении INTO , то target будет присвоено значение первой строки, возвращаемой командой, или значения NULL, если команда не вернула строк. (Примечание: «первая строка» не является однозначно определенным, если не используется ORDER BY.) Все остальные строки результата, следующие за первой, отбрасываются. Для проверки того, была ли возвращена строка, можно использовать специальную FOUND переменную (см. Раздел 5.6.5.5) to determine whether a row was returned:

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: username = 'nosuchuser'
CONTEXT:  PL/pgSQL function get_userid(text) line 6 at SQL statement

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

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

где строка-команды представляет собой выражение, возвращающее строку (типа text), которая содержит команду для выполнения. Необязательный параметр target представляет собой переменную типа record, переменную строки или разделенный запятыми список простых переменных и полей записей/строк, в которых будут сохранены результаты выполнения команды. Необязательные USING выражения передают значения для вставки в команду.

В вычисленной строке команды подстановка PL/pgSQL переменных не производится. Любые необходимые значения переменных должны быть вставлены в строку команды при ее формировании; также можно использовать параметры, как описано ниже.

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

Предложение INTO предложение определяет, куда должны быть сохранены результаты команды SQL, возвращающей строки. Если указана переменная строки или список переменных, они должны точно соответствовать структуре результатов команды; если же указана переменная типа record, она автоматически подстроится под структуру результата. Если возвращается несколько строк, только первая будет присвоена INTO переменным. Если строки не возвращаются, значение NULL присваивается INTO переменным. Если 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 со спецификатором %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 с простой постоянной строкой команды и некоторыми USING параметрами, как в первом примере выше, функционально эквивалентен простому написанию команды непосредственно в PL/pgSQL и разрешению автоматической замены PL/pgSQL переменных. Важное различие заключается в том, что EXECUTE будет заново планировать команду при каждом выполнении, создавая план, специфичный для текущих значений параметров; в то время как PL/pgSQL в противном случае может создать общий план и кэшировать его для повторного использования. В ситуациях, когда оптимальный план сильно зависит от значений параметров, может быть полезно использовать EXECUTE для гарантированного предотвращения выбора общего плана.

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

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);

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

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

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

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

GET DIAGNOSTICS integer_var = ROW_COUNT;

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

Другие 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;  -- игнорировать ошибку
END;

BEGIN
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN  -- игнорировать ошибку
END;

Выбор предпочтительного варианта — это вопрос вкуса.