CREATE FUNCTION#

CREATE FUNCTION
CREATE FUNCTION
Назад НаверхSQL КомандыНачало Далее

CREATE FUNCTION

CREATE FUNCTION — определить новую функцию

Синтаксис

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | WINDOW
    | { IMMUTABLE | STABLE | VOLATILE }
    | [ NOT ] LEAKPROOF
    | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
    | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | ROWS result_rows
    | SUPPORT support_function
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
    | sql_body
  } ...

Описание

CREATE FUNCTION определяет новую функцию. CREATE OR REPLACE FUNCTION либо создает новую функцию, либо заменяет существующее определение. Чтобы иметь возможность определить функцию, пользователь должен иметь привилегию USAGE на языке.

Если указано имя схемы, то функция создается в указанной схеме. В противном случае она создается в текущей схеме. Имя новой функции не должно совпадать с существующей функцией или процедурой с теми же типами входных аргументов в той же схеме. Однако функции и процедуры с разными типами аргументов могут иметь одно и то же имя (это называется перегрузкой).

Для замены текущего определения существующей функции используйте CREATE OR REPLACE FUNCTION. Нельзя изменить имя или типы аргументов функции таким образом (если вы попытаетесь это сделать, вы фактически создадите новую, отдельную функцию). Кроме того, CREATE OR REPLACE FUNCTION не позволяет изменить тип возвращаемого значения существующей функции. Для этого вы должны удалить и создать функцию заново. (При использовании параметров OUT это означает, что вы не можете изменить типы любых параметров OUT, кроме как путем удаления функции).

Когда используется команда CREATE OR REPLACE FUNCTION для замены существующей функции, владение и разрешения функции не изменяются. Все остальные свойства функции получают значения, указанные или подразумеваемые в команде. Вы должны быть владельцем функции, чтобы заменить ее (это включает в себя быть членом владеющей роли).

Если вы удаляете и затем создаете функцию заново, новая функция не является той же самой сущностью, что и старая; вам придется удалить существующие правила, представления, триггеры и т. д., которые ссылаются на старую функцию. Используйте команду CREATE OR REPLACE FUNCTION для изменения определения функции без нарушения объектов, которые ссылаются на функцию. Также, команда ALTER FUNCTION может быть использована для изменения большинства вспомогательных свойств существующей функции.

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

Чтобы создать функцию, необходимо иметь привилегию USAGE на типы аргументов и возвращаемый тип.

Ссылайтесь на Раздел 36.3 для получения дополнительной информации о написании функций.

Параметры

name

Имя (опционально с указанием схемы) функции для создания.

argmode

Режим аргумента: IN, OUT, INOUT или VARIADIC. Если не указан, используется значение по умолчанию - IN. Только аргументы с режимом OUT могут следовать за аргументом с режимом VARIADIC. Кроме того, аргументы с режимами OUT и INOUT не могут использоваться вместе с обозначением RETURNS TABLE.

argname

Имя аргумента. Некоторые языки (включая SQL и PL/pgSQL) позволяют использовать имя в теле функции. Для других языков имя входного аргумента является просто дополнительной документацией, насколько это касается самой функции; но вы можете использовать имена входных аргументов при вызове функции для улучшения читаемости (см. Раздел 4.3). В любом случае, имя выходного аргумента имеет значение, поскольку оно определяет имя столбца в типе результата строки. (Если вы опустите имя для выходного аргумента, система выберет имя столбца по умолчанию).

argtype

Тип(ы) данных аргументов функции (опционально с указанием схемы), если они есть. Типы аргументов могут быть базовыми, составными или доменными типами, или могут ссылаться на тип столбца таблицы.

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

Тип столбца ссылается на запись table_name.column_name%TYPE. Использование этой функции иногда может помочь сделать функцию независимой от изменений в определении таблицы.

default_expr

Выражение, которое будет использоваться в качестве значения по умолчанию, если параметр не указан. Выражение должно быть приводимым к типу аргумента параметра. Только входные (включая INOUT) параметры могут иметь значение по умолчанию. Все входные параметры, следующие за параметром со значением по умолчанию, также должны иметь значения по умолчанию.

rettype

Тип данных возвращаемого значения (опционально с указанием схемы). Тип возвращаемого значения может быть базовым, составным или доменным типом, или может ссылаться на тип столбца таблицы. В зависимости от языка реализации также может быть разрешено указывать “псевдо-типы”, такие как cstring. Если функция не должна возвращать значение, укажите void в качестве типа возвращаемого значения.

Когда есть параметры OUT или INOUT, можно опустить фразу RETURNS. Если она присутствует, она должна соответствовать типу результата, подразумеваемому выходными параметрами: RECORD, если есть несколько выходных параметров, или тот же тип, что и у единственного выходного параметра.

Модификатор SETOF указывает, что функция вернет набор элементов, а не один элемент.

Тип столбца ссылается, записывая table_name.имя_столбца%TYPE.

column_name

Имя выходной колонки в синтаксисе RETURNS TABLE. Это фактически еще один способ объявления именованного параметра OUT, за исключением того, что RETURNS TABLE также подразумевает RETURNS SETOF.

column_type

Тип данных выходного столбца в синтаксисе RETURNS TABLE.

lang_name

Имя языка, на котором реализована функция. Это может быть sql, c, internal или имя определенного пользователем процедурного языка, например, plpgsql. По умолчанию используется sql, если указан параметр sql_body. Заключение имени в апострофы является устаревшим и требует совпадения регистра.

TRANSFORM { FOR TYPE type_name } [, ... ] }

Списки, которые преобразования вызова функции должны применять. Преобразования преобразуют между SQL-типами и типами данных, специфичными для языка; см. CREATE TRANSFORM. Реализации процедурных языков обычно имеют жестко закодированные знания о встроенных типах, поэтому их не нужно перечислять здесь. Если реализация процедурного языка не знает, как обрабатывать тип и преобразование не предоставлено, она будет прибегать к поведению по умолчанию для преобразования данных типов, но это зависит от реализации.

WINDOW

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

IMMUTABLE
STABLE
VOLATILE

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

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

STABLE указывает, что функция не может изменять базу данных и что в пределах одного сканирования таблицы она всегда будет возвращать одинаковый результат для одинаковых аргументов, но результат может измениться между SQL-запросами. Это подходящий выбор для функций, результаты которых зависят от поиска в базе данных, параметров (например, текущей временной зоны) и т. д. (Он не подходит для AFTER триггеров, которые хотят запросить строки, измененные текущей командой). Также обратите внимание, что функции семейства current_timestamp считаются стабильными, так как их значения не изменяются в пределах транзакции.

VOLATILE указывает, что значение функции может изменяться даже в рамках одного сканирования таблицы, поэтому никакие оптимизации не могут быть выполнены. Относительно немногие функции базы данных являются "volatile" в этом смысле; некоторые примеры: random(), currval(), timeofday(). Однако следует отметить, что любая функция, имеющая побочные эффекты, должна быть классифицирована как "volatile", даже если ее результат вполне предсказуем, чтобы предотвратить оптимизацию вызовов; примером является setval().

См. дополнительные сведения в Раздел 36.7.

LEAKPROOF

LEAKPROOF указывает, что функция не имеет побочных эффектов. Она не раскрывает никакой информации о своих аргументах, кроме своего возвращаемого значения. Например, функция, которая выбрасывает сообщение об ошибке для некоторых значений аргументов, но не для других, или которая включает значения аргументов в любое сообщение об ошибке, не является непроницаемой. Это влияет на то, как система выполняет запросы к представлениям, созданным с использованием опции security_barrier, или таблицам с включенным уровнем защиты на уровне строк. Система будет применять условия из политик безопасности и представлений с защитным барьером перед любыми условиями, предоставленными пользователем, из самого запроса, содержащими непроницаемые функции, чтобы предотвратить непреднамеренное раскрытие данных. Функции и операторы, помеченные как непроницаемые, считаются надежными и могут выполняться перед условиями из политик безопасности и представлений с защитным барьером. Кроме того, функции, которые не принимают аргументы или которые не получают аргументы из представления с защитным барьером или таблицы, не должны быть помечены как непроницаемые, чтобы выполняться перед условиями безопасности. См. CREATE VIEW и Раздел 39.5. Эту опцию может установить только суперпользователь.

CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT
STRICT

CALLED ON NULL INPUT (по умолчанию) указывает, что функция будет вызываться нормально, когда некоторые из ее аргументов являются null. Затем на авторе функции лежит ответственность проверить наличие null значений, если это необходимо, и отреагировать соответствующим образом.

RETURNS NULL ON NULL INPUT или STRICT указывает, что функция всегда возвращает null, когда любой из ее аргументов равен null. Если этот параметр указан, функция не выполняется, когда есть аргументы null; вместо этого автоматически предполагается null результат.

[EXTERNAL] SECURITY INVOKER
[EXTERNAL] SECURITY DEFINER

SECURITY INVOKER указывает, что функция должна выполняться с привилегиями пользователя, который ее вызывает. Это значение по умолчанию. SECURITY DEFINER указывает, что функция должна выполняться с привилегиями пользователя, который ее владеет. Для получения информации о том, как безопасно писать функции SECURITY DEFINER, см. ниже.

Ключевое слово EXTERNAL разрешено для соответствия SQL, но оно является необязательным, поскольку, в отличие от SQL, эта функция применяется ко всем функциям, а не только к внешним.

PARALLEL

PARALLEL UNSAFE указывает, что функция не может выполняться в параллельном режиме, и наличие такой функции в SQL-запросе принуждает к последовательному плану выполнения. Это значение по умолчанию. PARALLEL RESTRICTED указывает, что функция может выполняться в параллельном режиме, но выполнение ограничено лидером параллельной группы. PARALLEL SAFE указывает, что функция безопасна для выполнения в параллельном режиме без ограничений.

Все функции должны быть помечены как "parallel unsafe", если они изменяют состояние базы данных или вносят изменения в транзакцию, такие как использование подтранзакций, доступ к последовательностям или попытки внести постоянные изменения в настройки (например, setval). Они должны быть помечены как "parallel restricted", если они обращаются к временным таблицам, состоянию клиентского подключения, курсорам, подготовленным операторам или другим локальным состояниям бэкенда, которые система не может синхронизировать в параллельном режиме (например, setseed не может быть выполнен, кроме как групповым лидером, потому что изменение, сделанное другим процессом, не будет отражено в лидере). Обычно, если функция помечена как "safe", когда она является "restricted" или "unsafe", или если она помечена как "restricted", когда на самом деле она является "unsafe", она может вызывать ошибки или давать неверные ответы при использовании в параллельном запросе. Функции на языке C в теории могут проявлять полностью неопределенное поведение, если они помечены неправильно, так как система не может защитить себя от произвольного кода на C, но в большинстве случаев результат будет не хуже, чем для любой другой функции. В случае сомнений функции должны быть помечены как UNSAFE, что является значением по умолчанию.

COST execution_cost

Положительное число, указывающее предполагаемую стоимость выполнения функции в единицах cpu_operator_cost. Если функция возвращает набор, это стоимость на каждую возвращаемую строку. Если стоимость не указана, предполагается 1 единица для функций на языке C и внутренних функций, и 100 единиц для функций на всех остальных языках. Большие значения заставляют планировщик стараться избегать частого выполнения функции.

ROWS result_rows

Положительное число, указывающее ожидаемое количество строк, которое планировщик должен ожидать от функции. Это разрешено только тогда, когда функция объявлена как возвращающая набор. По умолчанию предполагается 1000 строк.

SUPPORT support_function

Имя (опционально с указанием схемы) функции поддержки планировщика, которую следует использовать для этой функции. См. Раздел 36.10 для получения подробной информации. Для использования этой опции вы должны быть суперпользователем.

configuration_parameter
value

Предложение SET приводит к установке указанного параметра конфигурации в указанное значение при входе в функцию, а затем восстанавливает его предыдущее значение при выходе из функции. SET FROM CURRENT сохраняет значение параметра, которое является текущим при выполнении CREATE FUNCTION, в качестве значения, которое будет применяться при входе в функцию.

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

См. SET и Глава 19 для получения дополнительной информации о допустимых именах параметров и их значениях.

definition

Строковая константа, определяющая функцию; значение зависит от языка. Это может быть внутреннее имя функции, путь к объектному файлу, SQL-команда или текст на процедурном языке.

Часто полезно использовать кавычки доллара (см. Раздел 4.1.2.4) для написания строки определения функции, а не обычного синтаксиса с апострофами. Без использования кавычек доллара, любые апострофы или обратные косые черты в определении функции должны быть удвоены для экранирования.

obj_file, link_symbol

Эта форма предложения AS используется для динамически загружаемых функций на языке C, когда имя функции на языке C в исходном коде отличается от имени SQL функции. Строка obj_file - это имя файла общей библиотеки, содержащей скомпилированную C функцию, и она интерпретируется так же, как и для команды LOAD. Строка link_symbol - это символ связи функции, то есть имя функции в исходном коде на языке C. Если символ связи не указан, предполагается, что он совпадает с именем определяемой SQL функции. Имена C всех функций должны быть разными, поэтому вы должны давать перегруженным C функциям разные имена C (например, использовать типы аргументов в качестве части имен C).

Когда повторные вызовы CREATE FUNCTION относятся к тому же объектному файлу, файл загружается только один раз за сессию. Чтобы выгрузить и перезагрузить файл (возможно, во время разработки), запустите новую сессию.

sql_body

Тело функции LANGUAGE SQL. Это может быть как один оператор. 

RETURN expression

или блок 

BEGIN ATOMIC
  statement;
  statement;
  ...
  statement;
END

Это похоже на написание текста тела функции в виде строковой константы (см. definition выше), но есть некоторые отличия: эта форма работает только для LANGUAGE SQL, форма со строковой константой работает для всех языков. Эта форма разбирается во время определения функции, форма со строковой константой разбирается во время выполнения; поэтому эта форма не поддерживает полиморфные типы аргументов и другие конструкции, которые не могут быть разрешены во время определения функции. Эта форма отслеживает зависимости между функцией и объектами, используемыми в теле функции, поэтому DROP ... CASCADE будет работать правильно, в то время как форма, использующая строковые литералы, может оставить висячие функции. Наконец, эта форма более совместима со стандартом SQL и другими реализациями SQL.

Перегрузка

Tantor SE позволяет использовать перегрузку функций; то есть, одно и то же имя может быть использовано для нескольких различных функций, при условии, что у них есть разные типы входных аргументов. Независимо от того, используете ли вы это или нет, эта возможность требует предосторожности при вызове функций в базах данных, где некоторые пользователи не доверяют другим пользователям; см. Раздел 10.3.

Две функции считаются одинаковыми, если они имеют одинаковые имена и типы аргументов input, игнорируя любые параметры OUT. Таким образом, например, эти объявления конфликтуют: 

CREATE FUNCTION foo(int) ...
CREATE FUNCTION foo(int, out text) ...

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

CREATE FUNCTION foo(int) ...
CREATE FUNCTION foo(int, int default 42) ...

Вызов foo(10) завершится неудачей из-за неоднозначности о том, какая функция должна быть вызвана.

Примечания

Вся полная синтаксическая структура типа SQL разрешена для объявления аргументов и возвращаемого значения функции. Однако, модификаторы типа, заключенные в скобки (например, поле точности для типа numeric), отбрасываются командой CREATE FUNCTION. Таким образом, например, CREATE FUNCTION foo (varchar(10)) ... эквивалентно CREATE FUNCTION foo (varchar) ....

При замене существующей функции с помощью CREATE OR REPLACE FUNCTION существуют ограничения на изменение имен параметров. Вы не можете изменить имя, уже назначенное любому входному параметру (хотя вы можете добавить имена к параметрам, которым ранее не было имен). Если есть более одного выходного параметра, вы не можете изменить имена выходных параметров, потому что это изменит имена столбцов анонимного составного типа, описывающего результат функции. Эти ограничения сделаны для того, чтобы существующие вызовы функции не перестали работать при ее замене.

Если функция объявлена с использованием ключевого слова STRICT и аргументом VARIADIC, проверка строгости проверяет, что вариативный массив в целом не является нулевым. Функция все равно будет вызвана, если в массиве есть нулевые элементы.

Примеры

Добавление двух целых чисел с использованием SQL-функции: 

CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

Та же функция, написанная в более соответствующем стилю SQL, с использованием имен аргументов и тела без кавычек: 

CREATE FUNCTION add(a integer, b integer) RETURNS integer
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT
    RETURN a + b;

Увеличьте целое число, используя имя аргумента, в PL/pgSQL: 

CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
        BEGIN
                RETURN i + 1;
        END;
$$ LANGUAGE plpgsql;

Вернуть запись, содержащую несколько выходных параметров: 

CREATE FUNCTION dup(in int, out f1 int, out f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

Вы можете сделать то же самое более подробно с явно указанным именем составного типа: 

CREATE TYPE dup_result AS (f1 int, f2 text);

CREATE FUNCTION dup(int) RETURNS dup_result
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

Еще один способ вернуть несколько столбцов - использовать функцию TABLE: 

CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

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

Создание безопасных функций с использованием SECURITY DEFINER

Поскольку функция SECURITY DEFINER выполняется с привилегиями пользователя, владеющего ею, необходимо обеспечить защиту от возможного злоупотребления. Для обеспечения безопасности search_path должен быть настроен таким образом, чтобы исключить любые схемы, доступные для записи ненадежными пользователями. Это предотвращает возможность создания злонамеренными пользователями объектов (например, таблиц, функций и операторов), которые могут замаскировать объекты, предназначенные для использования функцией. Особенно важной в этом отношении является временная схема, которая по умолчанию ищется первой и обычно доступна для записи всем. Безопасное расположение может быть достигнуто путем принудительного поиска временной схемы в конце. Для этого напишите pg_temp. как последний элемент в search_path. Эта функция иллюстрирует безопасное использование: 

CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
        SELECT  (pwd = $2) INTO passed
        FROM    pwds
        WHERE   username = $1;

        RETURN passed;
END;
$$  LANGUAGE plpgsql
    SECURITY DEFINER
    -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
    SET search_path = admin, pg_temp;

Цель этой функции - получить доступ к таблице admin.pwds. Но без предложения SET, или с предложением SET, упоминающей только admin, функция может быть подвергнута воздействию путем создания временной таблицы с именем pwds.

Если функция определителя безопасности намеревается создавать роли, и если она выполняется как не-суперпользователь, createrole_self_grant также должно быть установлено на известное значение с использованием предложения SET.

Важно помнить, что по умолчанию для новых созданных функций предоставляется право выполнения для PUBLIC (см. Раздел 5.7 для получения дополнительной информации). Часто вам захочется ограничить использование функции с определенным определителем безопасности только для некоторых пользователей. Для этого вы должны отозвать привилегии PUBLIC по умолчанию, а затем выборочно предоставить право выполнения. Чтобы избежать ситуации, когда новая функция доступна всем, создайте ее и установите привилегии в рамках одной транзакции. Например: 

BEGIN;
CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... SECURITY DEFINER;
REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins;
COMMIT;

Совместимость

Команда CREATE FUNCTION определена в стандарте SQL. Реализация Tantor SE может быть использована совместимым образом, но имеет множество расширений. С другой стороны, стандарт SQL определяет ряд необязательных функций, которые не реализованы в Tantor SE.

Следующие вопросы совместимости являются важными:

  • OR REPLACE - это расширение PostgreSQL.

  • Для совместимости с некоторыми другими системами баз данных, argmode может быть записан либо перед, либо после argname. Но только первый способ соответствует стандартам.

  • Для значений параметров стандарт SQL определяет только синтаксис с использованием ключевого слова DEFAULT. Синтаксис с использованием = используется в T-SQL и Firebird.

  • Модификатор SETOF является расширением PostgreSQL.

  • Только SQL является стандартизированным языком.

  • Все остальные атрибуты, кроме CALLED ON NULL INPUT и RETURNS NULL ON NULL INPUT, не являются стандартизированными.

  • Для тела функций LANGUAGE SQL стандарт SQL только определяет форму sql_body.

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

Назад Наверх Далее
CREATE FOREIGN TABLE Начало CREATE GROUP