CREATE FUNCTION

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

Синтаксис

CREATE [ OR REPLACE ] FUNCTION
    имя ( [ [ режим_аргумента ] [ имя_аргумента ] тип_аргумента [ { DEFAULT | = } выражение_по_умолчанию ] [, ...] ] )
    [ RETURNS возвращаемый_тип
      | RETURNS TABLE ( имя_столбца тип_столбца [, ...] ) ]
  { LANGUAGE имя_языка
    | TRANSFORM { FOR TYPE имя_типа } [, ... ]
    | 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 стоимость_выполнения
    | ROWS строк_в_результате
    | SUPPORT вспомогательная_функция
    | SET параметр_конфигурации { TO значение | = значение | FROM CURRENT }
    | AS 'определение'
    | AS 'объектный_файл', 'ссылочный_символ'
    | тело_sql  
  } ...

Описание

Команда 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 для типов ее аргументов и возвращаемого типа.

Более подробную информацию о написании функций см. в разделе Пользовательские функции.

Параметры

имя

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

режим_аргумента

Режим аргумента: IN (входной), OUT (выходной), INOUT (входной и выходной) или VARIADIC (переменный). Если этот параметр опущен, значение по умолчанию равно IN. За аргументом VARIADIC могут следовать только аргументы OUT. Также аргументы OUT и INOUT нельзя использовать с предложением RETURNS TABLE.

имя_аргумента

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

тип_аргумента

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

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

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

выражение_по_умолчанию

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

тип_результата

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

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

Указание SETOF показывает, что функция возвращает множество, а не единственный элемент.

Ссылка на тип столбца записывается в виде имя_таблицы.имя_столбца%TYPE.

имя_столбца

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

тип_столбца

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

имя_языка

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

TRANSFORM { FOR TYPE имя_типа } [, ... ] }

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

WINDOW

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

IMMUTABLE
STABLE
VOLATILE

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

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

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

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

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

LEAKPROOF

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

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

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

PARALLEL

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

Функции должны быть помечены как небезопасные для параллельного выполнения (PARALLEL UNSAFE), если они изменяют состояние базы данных, или вносят изменения в транзакцию, например используя субтранзакции, или обращаются к последовательностям, или пытаются сохранять настройки параметров (например, используя setval). Функции должны быть помечены как ограниченно параллельные (PARALLEL RESTRICTED), если они обращаются к временным таблицам, состоянию клиентского подключения, курсорам, подготовленным операторам или разнообразному состоянию обслуживающего процесса, которые система не может синхронизировать в параллельном режиме (например, setseed может выполнять только ведущий процесс группы, так как изменения, внесенные другим процессом, не передаются ведущему). В целом, если функция помечена как безопасная (PARALLEL SAFE), являясь на самом деле ограниченной или небезопасной, либо помечена как ограниченно безопасная, не являясь безопасной, она может выдавать ошибки или давать неправильные результаты при использовании в параллельном запросе. Функции на языке C/RUST теоретически могут демонстрировать совершенно неопределенное поведение при неправильной маркировке, поскольку система не может защитить себя от произвольного кода на C/RUST, но в большинстве случаев результат будет не хуже, чем для любой другой функции. При возникновении сомнений функции следует помечать как UNSAFE, что является значением по умолчанию.

COST стоимость_выполнения

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

ROWS строк_в_результате

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

SUPPORT вспомогательная_функция

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

параметр_конфигурации
значение

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

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

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

определение

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

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

объектный_файл, ссылочный_символ

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

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

тело_sql

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

RETURN выражение

или блок операторов

BEGIN ATOMIC
  оператор;
  оператор;
  ...
  оператор;
END

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

Перегрузка

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

Две функции считаются одинаковыми, если они имеют одинаковые имена и типы входных аргументов, игнорируя параметры 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, то при оценке строгости проверяется, чтобы весь переменный массив в целом не был равен 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;

Функция увеличения целого числа, использующая именованный аргумент, на языке 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
    -- Установить безопасный путь поиска: сначала доверенная схема (или схемы), затем 'pg_temp'.
    SET search_path = admin, pg_temp;

Эта функция должна обращаться к таблице admin.pwds, но без предложения SET или с предложением SET, включающим только admin; ее можно «обмануть», создав временную таблицу с именем pwds.

Еще один момент, который следует иметь в виду, — это то, что по умолчанию право выполнения для создаваемых функций имеет роль PUBLIC (дополнительную информацию см. в разделе Права). Однако зачастую требуется разрешить доступ к функции, работающей в контексте безопасности определившего, только некоторым пользователям. Для этого необходимо отозвать стандартные права 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. Ее реализацию в QHB можно использовать совместимым образом, но у нее есть множество расширений. И наоборот, в стандарте SQL указан ряд дополнительных возможностей, не реализованных в QHB.

У совместимости существуют следующие важные ограничения:

  • OR REPLACE является расширением QHB.

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

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

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

  • В стандарте определен только язык SQL.

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

  • Для тела функции в стиле LANGUAGE SQL в стандарте SQL указана только форма тело_sql.

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

См. также

ALTER FUNCTION, DROP FUNCTION, GRANT, LOAD, REVOKE

Copyright © KVANTOM LLC, 2019-2023