Присвоение значения переменной 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;

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

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

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

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

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

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

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

PERFORM query;

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

Пример:

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;

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

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

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

Если в предложении INTO не указано STRICT, то target будет установлено в первую строку, возвращенную командой, или в null, если команда не вернула ни одной строки. (Обратите внимание, что “первая строка” не определена, если вы не использовали ORDER BY). Любые строки результата после первой строки отбрасываются. Вы можете проверить специальную переменную FOUND (см. Раздел 40.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: username = 'nosuchuser'
CONTEXT:  PL/pgSQL function get_userid(text) line 6 at SQL statement

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

EXECUTE command-string [В [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;

Более чистый подход - использовать спецификацию %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.

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

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

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

Эта команда позволяет получить индикаторы состояния системы. CURRENT - это шумовое слово (но см. также GET STACKED DIAGNOSTICS в Раздел 40.6.8.1). Каждый item - это ключевое слово, идентифицирующее значение статуса, которое должно быть присвоено указанной переменной variable (которая должна быть правильного типа данных для его получения). В настоящее время доступные элементы состояния показаны в Таблица 40.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;  -- ignore the error
END;

BEGIN
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN  -- ignore the error
END;

Какой из них предпочтительнее - вопрос вкуса.