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 'объектный_файл
', 'объектный_символ
' } ...
Описание
Команда 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
для типов её аргументов и возвращаемого типа.
За дополнительной информацией о разработке функций обратитесь к Разделу 38.3.
Параметры
имя
Имя создаваемой функции (возможно, дополненное схемой).
режим_аргумента
Режим аргумента:
IN
(входной),OUT
(выходной),INOUT
(входной и выходной) илиVARIADIC
(переменный). По умолчанию подразумеваетсяIN
. За единственным аргументомVARIADIC
могут следовать только аргументыOUT
. Кроме того, аргументыOUT
иINOUT
нельзя использовать с предложениемRETURNS TABLE
.имя_аргумента
Имя аргумента. Некоторые языки (включая SQL и PL/pgSQL) позволяют использовать это имя в теле функции. Для других языков это имя служит просто дополнительным описанием, если говорить о самой функции; однако вы можете указывать имена аргументов при вызове функции для улучшения читаемости (см. Раздел 4.3). Имя выходного аргумента в любом случае имеет значение, так как оно определяет имя столбца в типе результата. (Если вы опустите имя выходного аргумента, система выберет для него имя по умолчанию.)
тип_аргумента
Тип данных аргумента функции (возможно, дополненный схемой), при наличии аргументов. Тип аргументов может быть базовым, составным или доменным, либо это может быть ссылка на столбец таблицы.
В зависимости от языка реализации также может допускаться указание «псевдотипов», например,
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
. Стиль написания этого имени в апострофах считается устаревшим и требует точного совпадения регистра.TRANSFORM { FOR TYPE
имя_типа
} [, ... ] }Устанавливает список трансформаций, которые должны применяться при вызове функции. Трансформации выполняют преобразования между типами SQL и типами данных, специфичными для языков; см. CREATE TRANSFORM. Преобразования встроенных типов обычно жёстко предопределены в реализациях процедурных языков, так что их здесь указывать не нужно. Если реализация процедурного языка не может обработать тип и трансформация для него отсутствует, будет выполнено преобразование типов по умолчанию, но это зависит от реализации.
WINDOW
Указание
WINDOW
показывает, что создаётся не простая, а оконная функция. В настоящее время это имеет смысл только для функций, написанных на C. АтрибутWINDOW
нельзя изменить, модифицируя впоследствии определение функции.IMMUTABLE
STABLE
VOLATILE
Эти атрибуты информируют оптимизатор запросов о поведении функции. Одновременно можно указать не более одного атрибута. Если никакой атрибут не задан, по умолчанию подразумевается
VOLATILE
.Характеристика
IMMUTABLE
(постоянная) показывает, что функция не может модифицировать базу данных и всегда возвращает один и тот же результат при определённых значениях аргументов; то есть, она не обращается к базе данных и не использует информацию, не переданную ей явно в списке аргументов. Если функция имеет такую характеристику, любой её вызов с аргументами-константами можно немедленно заменить значением функции.Характеристика
STABLE
(стабильная) показывает, что функция не может модифицировать базу данных и в рамках одного сканирования таблицы она всегда возвращает один и тот же результат для определённых значений аргументов, но этот результат может быть разным в разных операторах SQL. Это подходящий выбор для функций, результаты которых зависят от содержимого базы данных и настраиваемых параметров (например, текущего часового пояса). (Но этот вариант не подходит для триггеровAFTER
, желающих прочитать строки, изменённые текущей командой.) Также заметьте, что функции семействаcurrent_timestamp
также считаются стабильными, так как их результаты не меняются внутри транзакции.Характеристика
VOLATILE
(изменчивая) показывает, что результат функции может меняться даже в рамках одного сканирования таблицы, так что её вызовы нельзя оптимизировать. Изменчивы в этом смысле относительно немногие функции баз данных, например:random()
,currval()
иtimeofday()
. Но заметьте, что любая функция с побочными эффектами должна быть классифицирована как изменчивая, даже если её результат вполне предсказуем, чтобы её вызовы не были соптимизированы; пример такой функции:setval()
.За дополнительными подробностями обратитесь к Разделу 38.7.
LEAKPROOF
Характеристика
LEAKPROOF
(герметичная) показывает, что функция не имеет побочных эффектов. Она не раскрывает информацию о своих аргументах, кроме как возвращая результат. Например, функция, которая выдаёт сообщение об ошибке с некоторыми, но не всеми значениями аргументов, либо выводит значения аргументов в сообщении об ошибке, не является герметичной. Это влияет на то, как система выполняет запросы к представлениям, созданным с барьером безопасности (с указаниемsecurity_barrier
), или к таблицам с включённой защитой строк. Во избежание неконтролируемой утечки данных система будет проверять условия из политик защиты и определений представлений с барьерами безопасности перед любыми условиями, которые задаёт пользователь в самом запросе и в которых задействуются негерметичные функции. Функции и операторы, помеченные как герметичные, считаются доверенными и могут выполняться перед условиями из политик защиты и представлений с барьерами безопасности. При этом функции, которые не имеют аргументов или которым не передаются никакие аргументы из представления с барьером безопасности или таблицы, не требуется помечать как герметичные, чтобы они выполнялись до условий, связанных с безопасностью. См. CREATE VIEW и Раздел 41.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
(безопасность определившего) определяет, что функция выполняется с правами пользователя, владеющего ей.Ключевое слово
EXTERNAL
(внешняя) допускается для соответствия стандарту SQL, но является необязательным, так как, в отличие от SQL, эта характеристика распространяется на все функции, а не только внешние.PARALLEL
Указание
PARALLEL UNSAFE
означает, что эту функцию нельзя выполнять в параллельном режиме и присутствие такой функции в операторе SQL приводит к выбору последовательного плана выполнения. Это характеристика функции по умолчанию. УказаниеPARALLEL RESTRICTED
означает, что функцию можно выполнять в параллельном режиме, но только в ведущем процессе группы.PARALLEL SAFE
показывает, что функция безопасна для выполнения в параллельном режиме без ограничений.Функции должны помечаться как небезопасные для параллельного выполнения, если они изменяют состояние базы данных, вносят изменения в транзакции, например, используя подтранзакции, обращаются к последовательностям или пытаются сохранять параметры (например, используя
setval
). Ограниченно параллельными должны помечаться функции, которые обращаются к временным таблицам, состоянию клиентского подключения, курсорам, подготовленным операторам или разнообразному состоянию обслуживающего процесса, которое система не может синхронизировать в параллельном режиме (например,setseed
может выполнять только ведущий процесс группы, так как изменения, внесённые другим процессом, не передаются ведущему). Вообще, если функция помечена как безопасная, тогда как она является ограниченной или небезопасной, либо если она помечена как ограниченно безопасная, не являясь безопасной, при попытке вызвать её в параллельном запросе она может выдавать ошибки или неверные результаты. Функции на языке C при неправильной пометке теоретически могут проявлять полностью неопределённое поведение, так как система никак не может защититься от произвольного кода на C, но чаще все они будут вести себя не хуже, чем любая другая функция. В случае сомнений функцию следует помечать как небезопасную (UNSAFE
), что и имеет место по умолчанию.COST
стоимость_выполнения
Положительное число, задающее примерную стоимость выполнения функции, в единицах cpu_operator_cost. Если функция возвращает множество, это число задаёт стоимость для одной строки. Если стоимость не указана, для функций на C и внутренних функций она считается равной 1 единице, а для функций на всех других языках — 100 единицам. При больших значениях планировщик будет стараться не вызывать эту функцию чаще, чем это необходимо.
ROWS
строк_в_результате
Положительное число, задающее примерное число строк, которое будет ожидать планировщик на выходе этой функции. Это указание допустимо, только если функция объявлена как возвращающая множество. Предполагаемое по умолчанию значение — 1000 строк.
SUPPORT
вспомогательная_функция
Имя (возможно, дополненное схемой) вспомогательной функции для планировщика, которая будет использоваться этой функцией. За подробностями обратитесь к Разделу 38.11. Для использования этого указания нужно быть суперпользователем.
параметр_конфигурации
значение
Предложение
SET
определяет, что при вызове функции указанный параметр конфигурации должен принять заданное значение, а затем восстановить своё предыдущее значение при завершении функции. ПредложениеSET FROM CURRENT
сохраняет в качестве значения, которое будет применено при входе в функцию, значение, действующее в момент выполненияCREATE FUNCTION
.Если в определение функции добавлено
SET
, то действие командыSET LOCAL
, выполняемой внутри функции для того же параметра, ограничивается телом функции: предыдущее значение параметра так же будет восстановлено при завершении функции. Однако обычная командаSET
(безLOCAL
) переопределяет предложениеSET
, как и предыдущую командуSET LOCAL
: действие такой команды будет сохранено и после завершения функции, если только не произойдёт откат транзакции.За подробными сведениями об именах и значениях параметров обратитесь к SET и Главе 19.
определение
Строковая константа, определяющая реализацию функции; её значение зависит от языка. Это может быть имя внутренней функции, путь к объектному файлу, команда SQL или код функции на процедурном языке.
Часто бывает полезно заключать определение функции в доллары (см. Подраздел 4.1.2.4), а не в традиционные апострофы. Если не использовать доллары, все апострофы и обратные косые черты в определении функции придётся экранировать, дублируя их.
объектный_файл
,объектный_символ
Эта форма предложения
AS
применяется для динамически загружаемых функций на языке C, когда имя функции в коде C не совпадает с именем функции в SQL. Строкаобъектный_файл
задаёт имя файла разделяемой библиотеки, содержащей скомпилированную функцию на C, и воспринимается как параметр команды LOAD. Строкаобъектный_символ
задаёт символ скомпонованной функции, то есть имя функции в исходном коде на языке C. Если объектный символ опущен, предполагается, что он совпадает с именем определяемой SQL-функции. В C имена всех функций должны быть различными, поэтому перегружаемым функциям, реализованным на C, нужно давать разные имена (например, включать в имена C обозначения типов аргументов).Если повторные вызовы
CREATE FUNCTION
ссылаются на один и тот же объектный файл, он загружается в рамках сеанса только один раз. Чтобы выгрузить и загрузить этот файл снова (например, в процессе разработки), начните новый сеанс.
Перегрузка
Postgres Pro допускает перегрузку функций; то есть, позволяет использовать одно имя для нескольких различных функций, если у них различаются типы входных аргументов. Независимо от того, используете вы эту возможность или нет, она требует предосторожности при вызове функций в базах данных, где одни пользователи не доверяют другим; см. Раздел 10.3.
Две функции считаются совпадающими, если они имеют одинаковые имена и типы входных аргументов, параметры 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;
Функция увеличения целого числа на 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 -- Установить безопасный путь поиска: сначала доверенная схема(ы), затем 'pg_temp'. SET search_path = admin, pg_temp;
Эта функция должна обращаться к таблице admin.pwds
, но без предложения SET
или с предложением SET
, включающим только admin
, её можно «обмануть», создав временную таблицу pwds
.
До PostgreSQL 8.3 предложение SET
отсутствовало, так что старые функции могут содержать довольно сложную логику для сохранения, изменения и восстановления переменной search_path
. Существующее теперь предложение 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. Её реализация в Postgres Pro близка к стандартизированной, но совместима с ней не полностью. К отличиям относятся непереносимые атрибуты, а также поддержка различных языков.
Для совместимости с другими СУБД режим_аргумента
можно записать после имя_аргумента
или перед ним, но стандарту соответствует только первый вариант.
Для определения значений по умолчанию для параметров стандарт SQL поддерживает только синтаксис с ключевым словом DEFAULT
. Синтаксис со знаком =
используется в T-SQL и Firebird.