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

INSERT INTO foo (foo) VALUES (foo(foo));

Первое вхождение foo должно быть синтаксически именем таблицы, поэтому оно не будет заменено, даже если у функции есть переменная с именем foo. Второе вхождение должно быть именем столбца этой таблицы, поэтому оно также не будет заменено. Аналогично, третье вхождение должно быть именем функции, поэтому оно также не будет заменено. Только последнее вхождение может быть ссылкой на переменную функции PL/pgSQL.

Другой способ понять это заключается в том, что подстановка переменных может только вставлять значения данных в SQL-команду; она не может динамически изменять ссылки на объекты базы данных, используемые в команде. (Если нужно сделать это, необходимо динамически создавать строку команды, как объясняется в разделе Раздел 40.5.4).

Поскольку имена переменных синтаксически не отличаются от имен столбцов таблицы, может возникнуть неоднозначность в операторах, которые также относятся к таблицам: заданное имя предназначено для ссылки на столбец таблицы или переменную? Давайте изменить предыдущий пример на

INSERT INTO dest (col) SELECT foo + bar FROM src;

Здесь, dest и src должны быть именами таблиц, а col должен быть столбцом dest, но foo и bar могут быть как переменными функции, так и столбцами src.

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

Самое простое решение - переименовать переменную или столбец. Общее правило кодирования заключается в использовании разных соглашений по именованию для переменных PL/pgSQL и имен столбцов. Например, если вы последовательно называете переменные функции v_something, а ни одно из ваших имен столбцов не начинается с v_, конфликтов не возникнет.

В качестве альтернативы вы можете указать неоднозначные ссылки, чтобы сделать их ясными. В приведенном выше примере src.foo будет являться однозначной ссылкой на столбец таблицы. Чтобы создать однозначную ссылку на переменную, объявите ее в помеченном блоке и используйте метку блока (см. Раздел 40.2). Например,

<<block>>
DECLARE
    foo int;
BEGIN
    foo := ...;
    INSERT INTO dest (col) SELECT block.foo + bar FROM src;

Здесь block.foo означает переменную, даже если в src есть столбец foo. Параметры функции, а также специальные переменные, такие как FOUND, могут быть полным именем функции, потому что они неявно объявлены во внешнем блоке, помеченном именем функции.

Иногда непрактично исправлять все неоднозначные ссылки в большом объеме кода PL/pgSQL. В таких случаях вы можете указать, что PL/pgSQL должен разрешать неоднозначные ссылки как переменную (что совместимо с поведением PL/pgSQL до версии PostgreSQL 9.0) или как столбец таблицы (что совместимо с некоторыми другими системами, такими как Oracle).

Для изменения этого поведения на системном уровне установите параметр конфигурации plpgsql.variable_conflict на одно из значений error, use_variable или use_column (где error является значением по умолчанию). Этот параметр влияет на последующие компиляции операторов в функциях PL/pgSQL, но не на операторы, уже скомпилированные в текущей сессии. Поскольку изменение этого параметра может вызвать неожиданные изменения в поведении функций PL/pgSQL, его можно изменить только суперпользователем.

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

#variable_conflict error
#variable_conflict use_variable
#variable_conflict use_column

Эти команды влияют только на функцию, в которой они написаны, и переопределяют установку plpgsql.variable_conflict. Примером является

CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
    #variable_conflict use_variable
    DECLARE
        curtime timestamp := now();
    BEGIN
        UPDATE users SET last_modified = curtime, comment = comment
          WHERE users.id = id;
    END;
$$ LANGUAGE plpgsql;

В команде UPDATE curtime, comment и id будут ссылаться на переменные и параметры функции, независимо от того, есть ли у таблицы users столбцы с такими именами. Обратите внимание, что мы должны были указать квалификатор для ссылки на users.id в предложении WHERE, чтобы она относилась к столбцу таблицы. Но мы не должны были указывать квалификатор для ссылки на comment в списке UPDATE, потому что синтаксически это должен быть столбец users. Мы могли бы написать ту же функцию, не зависящую от настройки variable_conflict, следующим образом:

CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
    <<fn>>
    DECLARE
        curtime timestamp := now();
    BEGIN
        UPDATE users SET last_modified = fn.curtime, comment = stamp_user.comment
          WHERE users.id = stamp_user.id;
    END;
$$ LANGUAGE plpgsql;

Вставка переменных не происходит в строке команды, переданной в EXECUTE или одну из его вариантов. Если вам нужно вставить изменяющееся значение в такую команду, сделайте это в составе конструирования строкового значения или используйте USING, как показано в Раздел 40.5.4.

Подстановка переменных в настоящее время работает только в командах SELECT, INSERT, UPDATE, DELETE и командах, содержащих одну из них (например, EXPLAIN и CREATE TABLE ... AS SELECT), поскольку основной SQL-движок позволяет использовать параметры запроса только в этих командах. Чтобы использовать имя или значение, не являющееся константой, в других типах операторов (общими для утилитарных операторов), вы должны сконструировать утилитарный оператор в виде строки и выполнить его с помощью EXECUTE.

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

Поскольку каждое выражение и команда SQL сначала выполняются в функции, интерпретатор PL/pgSQL анализирует и анализирует команду, чтобы создать подготовленный оператор, используя функцию SPI_prepare менеджера SPI. Последующие посещения этого выражения или команды повторно используют подготовленный оператор. Таким образом, функция с условными кодовыми путями, которые редко посещаются, никогда не будет иметь издержек на анализ тех команд, которые никогда не выполняются в текущей сессии. Недостатком является то, что ошибки в конкретном выражении или команде не могут быть обнаружены до тех пор, пока эта часть функции не будет достигнута во время выполнения. (Тривиальные синтаксические ошибки будут обнаружены во время первого прохода разбора, но что-то более глубокое не будет обнаружено до выполнения).

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

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

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

Если одна и та же функция используется в качестве триггера для нескольких таблиц, PL/pgSQL готовит и кеширует операторы независимо для каждой такой таблицы - то есть, есть кеш для каждой комбинации триггерной функции и таблицы, а не только для каждой функции. Это устраняет некоторые проблемы с различными типами данных; например, триггерная функция сможет успешно работать с столбцом с именем key, даже если у него разные типы в разных таблицах.

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

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

CREATE FUNCTION logfunc1(logtxt text) RETURNS void AS $$
    BEGIN
        INSERT INTO logtable VALUES (logtxt, 'now');
    END;
$$ LANGUAGE plpgsql;

и

CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$
    DECLARE
        curtime timestamp;
    BEGIN
        curtime := 'now';
        INSERT INTO logtable VALUES (logtxt, curtime);
    END;
$$ LANGUAGE plpgsql;

В случае функции logfunc1 главный парсер Tantor BE знает, когда анализирует INSERT, что строка 'now' должна быть интерпретирована как timestamp, потому что целевая колонка logtable имеет такой тип. Таким образом, 'now' будет преобразована в постоянную timestamp при анализе INSERT, а затем использована во всех вызовах функции logfunc1 в течение сессии. Не нужно говорить, что это не то, что хотел программист. Лучшей идеей является использование функции now() или current_timestamp.

В случае функции logfunc2 основной парсер Tantor BE не знает, каким типом должна стать строка 'now', поэтому он возвращает значение данных типа text, содержащее строку now. При последующем присваивании локальной переменной curtime интерпретатор PL/pgSQL преобразует эту строку в тип timestamp, вызывая функции textout и timestamp_in для преобразования. Таким образом, вычисленный временной штамп обновляется при каждом выполнении, как ожидает программист. Хотя это работает ожидаемым образом, это не очень эффективно, поэтому использование функции now() все равно было бы лучшей идеей.