CREATE FUNCTION

примечание

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

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

При замене функции через CREATE OR REPLACE FUNCTION сохраняются ее права доступа и владелец. Остальные характеристики функции будут переопределены на основании указанных или подразумеваемых значений. Для замены функции пользователь должен быть владельцем функции или являться прямым или косвенным членом роли владельца.

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

Владельцем функции становится пользователь, создавший ее.

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

Смотрите раздел «Пользовательские функции» для получения дополнительной информации о написании функций.

Параметры

name: Указывает имя функции, при необходимости дополненное схемой, которую необходимо создать.

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

argname: Задает имя аргумента. Некоторые языки (включая SQL и PL/pgSQL) позволяют обращаться к аргументу по имени в теле функции. В других языках имя входного аргумента служит в основном для удобства чтения и документирования, но при вызове функции можно использовать имена для улучшения читаемости. В случае с выходными аргументами имя имеет значение — оно определяет имя столбца в результирующем наборе строк. Если имя не указано, система применит имя по умолчанию.

argtype

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

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

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

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

rettype

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

Если используются параметры OUT или INOUT, выражение RETURNS может быть опущено. При этом, если оно указано, тип должен соответствовать возвращаемому значению, определенному выходными параметрами: RECORD, если таких параметров несколько, или типу единственного параметра.

Модификатор SETOF обозначает, что функция возвращает не одно значение, а набор значений.

Тип столбца можно указать через table_name.column_name%TYPE.

column_name: Задает имя выходного столбца в конструкции RETURNS TABLE. Это по сути то же, что указание параметра OUT, но в синтаксисе RETURNS TABLE, который также подразумевает RETURNS SETOF.

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

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

TRANSFORM {FOR TYPE type_name} [, ... ] }: Указывает, какие преобразования типов должны применяться при вызове функции. Преобразования переводят SQL-типы в формат, подходящий для конкретного языка. Подробнее смотрите CREATE TRANSFORM. Встроенные типы обычно уже известны процедурному языку, и их указывать не требуется. Если язык не знает, как обрабатывать определенный тип и не задано преобразование, он применит поведение по умолчанию, зависящее от реализации.

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

IMMUTABLE STABLE VOLATILE

Описывают поведение функции для оптимизатора. Указывается только один вариант. Если ни один не указан, по умолчанию применяется VOLATILE.

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

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

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

Дополнительные сведения смотрите в разделе «Категории волатильности функций».

LEAKPROOF

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

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

Устанавливать этот параметр может только суперпользователь.

Смотрите CREATE VIEW и раздел «Правила и привилегии».

CALLED ON NULL INPUT: Вызывает функцию даже при передаче аргументов со значением NULL. В этом случае проверка и обработка NULL лежит на создателе функции. Это поведение по умолчанию.

RETURNS NULL ON NULL INPUT STRICT: Не вызывает функцию при наличии NULL среди аргументов, результатом автоматически считается NULL.

[EXTERNAL] SECURITY INVOKER [EXTERNAL] SECURITY DEFINER: SECURITY INVOKER указывает на то, что функция должна выполняться с правами пользователя, который вызывает ее. Это поведение по умолчанию. SECURITY DEFINER указывает на то, что функция должна выполняться с правами владельца этой функции. Для получения информации о том, как безопасно записывать функции SECURITY DEFINER, смотрите ниже.
Ключевое слово EXTERNAL разрешено для соответствия SQL, но необязательно, поскольку, в отличие от SQL, эта функция применяется ко всем функциям, а не только к внешним.

PARALLEL

Указывает, может ли функция использоваться в параллельных запросах:

PARALLEL UNSAFE — функция не может исполняться в параллельных запросах и делает план выполнения последовательным (значение по умолчанию).
PARALLEL RESTRICTED — допускается параллельное выполнение, но только в ведущем процессе группы.
PARALLEL SAFE – функцию можно безопасно запускать в параллельном режиме без ограничений, включая процессы параллельных рабочих потоков.

Функциям следует присваивать метку «небезопасные для параллельного выполнения», если они изменяют состояние базы данных, меняют состояние транзакций (кроме использования подпотранзакций для восстановления после ошибок), обращаются к последовательностям (например, вызывая currval), или вносят постоянные изменения в настройки. Им следует присвоить метку «ограниченная параллельность», если они обращаются к временным таблицам, состоянию клиентского подключения, курсорам, подготовленным операторам или различным локальным состояниям сервера, которые система не может синхронизировать в параллельном режиме (например, setseed не может быть выполнен ничем другим, кроме лидера группы, потому что изменение, сделанное другим процессом, не отразится в лидере). В общем случае, если функция помечается как безопасная, хотя фактически она ограниченно-параллельная или небезопасная, или если она помечается как ограниченно-параллельная, хотя на самом деле она небезопасна, она может вызывать ошибки или выдавать неверные ответы при использовании в параллельном запросе. Теоретически функции языка C могут демонстрировать совершенно неопределенное поведение, если они неправильно обозначены, поскольку система не способна защитить себя от произвольного кода на языке C, но во многих вероятных случаях результат не будет хуже, чем для любой другой функции. При сомнениях функции следует маркировать как UNSAFE, что является поведением по умолчанию.

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

ROWS result_rows: Задает положительное число, отражающее примерное количество строк, которое планировщик должен ожидать получить от функции. Применим только к функциям, возвращающим множества. По умолчанию предполагается 1000 строк.

SUPPORT support_function: Задает имя функции поддержки планировщика, при необходимости дополненное схемой. Может использоваться только суперпользователем. Подробнее описано в разделе «Информация об оптимизации функций».

configuration_parameter value

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

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

Смотрите SET и главу «Конфигурация сервера» для подробностей о параметрах и допустимых значениях.

definition: Задает строковое представление тела функции. Зависит от используемого языка: может быть внутреннее имя, путь к объектному файлу, SQL-команда или текст на процедурном языке.
Часто удобно использовать кавычки с долларом, чтобы избежать экранирования символов. Без использования кавычек с долларом любые одинарные кавычки или обратные слеши в определении функции должны быть экранированы путем их удвоения.

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

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

sql_body: Задает тело функции LANGUAGE SQL. Может быть либо выражением:

RETURN expression

либо блоком:

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

Этот способ задания тела функции похож на указание текста функции в виде строковой константы (смотрите параметр definition выше), но между ними есть важные различия:

Такая форма доступна только для функций на языке SQL, тогда как строковая константа может использоваться для любых языков.
В этом варианте тело функции разбирается во время создания функции, а строка-константа разбирается уже при выполнении, поэтому здесь нельзя использовать полиморфные типы аргументов и другие конструкции, которые невозможно определить при создании.
При использовании этой формы отслеживаются зависимости между функцией и объектами, которые она использует, поэтому DROP ... CASCADE корректно удалит зависимые объекты. В случае строковой константы такие связи не фиксируются, и можно получить неполноценные функции.
Кроме того, эта форма ближе к стандарту SQL и лучше совместима с другими СУБД.

Перегрузка

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

Функции считаются одинаковыми, если у них совпадают имена и типы входных параметров — параметры 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 foo(varchar(10)) ... эквивалентно CREATE FUNCTION foo(varchar) ....

При замене функции с помощью CREATE OR REPLACE FUNCTION есть ограничения:

нельзя переименовывать входные параметры, у которых уже есть имя;
нельзя менять имена выходных параметров, если их больше одного — это изменит структуру результата, описываемого анонимным составным типом;
допустимо добавлять имена к безымянным параметрам.

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

Если функция объявлена как STRICT и использует параметр VARIADIC, то проверка строгого поведения (STRICT) проверяет только, что весь массив передан не NULL. При этом допускается наличие NULL внутри элементов массива.

Примеры

Сложение двух целых чисел, используя функцию 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;

Увеличение целого числа на 1, используя имя аргумента, на языке 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. Однако, если в search_path отсутствует явное указание схемы или указана только admin, то возможно создание временной таблицы с тем же именем pwds, что позволит перехватить обращение функции.

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

Также необходимо помнить, что по умолчанию функциям предоставляется право выполнения (EXECUTE) для всех пользователей (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, однако реализация в PostgreSQL включает множество расширений. Некоторые из них:

Ключевое слово OR REPLACE является расширением PostgreSQL.
Для совместимости с другими СУБД допускается указание argmode как до, так и после имени аргумента, но стандартом считается только первое расположение.
Стандарт SQL предусматривает задание значений по умолчанию только с помощью DEFAULT. Использование = характерно для T-SQL и Firebird.
SETOF не является частью стандарта.
Из языков стандартизирован только SQL.
Все параметры, кроме CALLED ON NULL INPUT и RETURNS NULL ON NULL INPUT, являются нестандартизированными.
В теле функции допустим только формат sql_body по стандарту.

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

Синтаксис​

Описание​

Параметры​

Перегрузка​

Примечания​

Примеры​

Безопасное написание функций SECURITY DEFINER​

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

Смотрите также​