Определяет новую функцию.
CREATE [OR REPLACE] FUNCTION name
( [ [argmode] [argname] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
[ RETURNS rettype
| RETURNS TABLE ( column_name column_type [, ...] ) ]
{ LANGUAGE langname
| WINDOW
| IMMUTABLE | STABLE | VOLATILE | [NOT] LEAKPROOF
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL
| [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER
| EXECUTE ON { ANY | MASTER | ALL SEGMENTS | INITPLAN }
| COST execution_cost
| SET configuration_parameter { TO value | = value | FROM CURRENT }
| AS 'definition'
| AS 'obj_file', 'link_symbol' } ...
[ WITH ({ DESCRIBE = describe_function
} [, ...] ) ]Оператор CREATE FUNCTION определяет новую функцию. Ключевое слово CREATE OR REPLACE FUNCTION либо создаёт новую функцию, либо заменяет существующее определение.
Имя новой функции не должно совпадать с именем уже существующей функции, имеющей такие же типы входных параметров в одной схеме. Однако функции с различными типами параметров могут иметь одинаковые имена (перегрузка).
Чтобы обновить определение существующей функции, используйте CREATE OR REPLACE FUNCTION. Изменить таким способом имя или типы параметров функции невозможно (в этом случае будет создана новая, отличная функция). Аналогично, CREATE OR REPLACE FUNCTION не позволяет изменить тип возвращаемого значения существующей функции. Для этого функцию необходимо удалить и заново создать. При использовании параметра OUT это означает, что нельзя изменить тип любого OUT-параметра без удаления функции. Если вы удаляете и повторно создаёте функцию, необходимо также удалить все существующие объекты (правила, представления, триггеры и т.д.), ссылающиеся на старую функцию. Используйте CREATE OR REPLACE FUNCTION, чтобы изменить определение функции, не уничтожая при этом объекты, ссылающиеся на неё.
Пользователь, создавший функцию, становится её владельцем.
Для возможности создания функций необходимо иметь привилегию USAGE на типы параметров и возвращаемый тип.
Чтобы предотвратить рассинхронизацию данных между различными сегментами базы данных YMatrix, любая функция, классифицируемая как STABLE или VOLATILE, которая содержит SQL-запросы или каким-либо образом изменяет базу данных, не может выполняться на уровне сегментов. Например, выполнение функций вроде random() или timeofday() на распределённых данных в базах данных YMatrix запрещено, поскольку это может привести к несогласованности данных между экземплярами сегментов.
Для обеспечения согласованности данных функции VOLATILE и STABLE можно безопасно использовать в операторах, которые оцениваются и выполняются на главном сервере (master). Например, следующие операторы всегда выполняются на главной базе данных (операторы без предложения FROM):
SELECT setval('myseq', 201);
SELECT foo();Если в предложении FROM оператора содержится распределённая таблица, и используемые в нём функции возвращают только набор строк, то их можно выполнять на сегментах:
SELECT * FROM foo();Исключение из этого правила составляют функции, возвращающие ссылки на таблицы (rangeFuncs), и функции, использующие тип данных refCursor. Обратите внимание, что в базах данных YMatrix нельзя возвращать refcursor из функций любого типа.
Атрибуты нестабильности (IMMUTABLE, STABLE, VOLATILE) и атрибут EXECUTE ON определяют два различных аспекта — когда и где функция выполняется. Как правило, свойство нестабильности указывает, когда функция выполняется, а атрибут EXECUTE ON — где она выполняется.
Например, функции, определённые с атрибутом IMMUTABLE, могут выполняться во время планирования запроса, тогда как функции с атрибутом VOLATILE должны выполняться для каждой строки запроса. Функции с атрибутом EXECUTE ON MASTER выполняются только на главном узле, а функции с атрибутом EXECUTE ON ALL SEGMENTS — только на всех первичных сегментах (но не на главном узле).
Определяемые пользователем функции, которые выполняют только команды SELECT над реплицированными таблицами, могут выполняться на сегментах. Реплицированные таблицы, созданные с помощью предложения DISTRIBUTED REPLICATED, хранят все свои строки на каждом сегменте. Поэтому безопасно читать их на сегментах, однако обновления реплицированных таблиц должны выполняться только на главном экземпляре.
name: Имя создаваемой функции (может быть указано с именем схемы).
argmode: Режим параметра: IN, OUT, INOUT или VARIADIC. Если не указан, по умолчанию используется IN. Только параметры OUT могут следовать после параметра, объявленного как VARIADIC. Аналогично, параметры OUT и INOUT нельзя использовать с нотацией RETURNS TABLE.
argname: Имя параметра. Некоторые языки (в настоящее время только SQL и PL/pgSQL) позволяют ссылаться на имя параметра внутри тела функции. Для других языков имена входных параметров служат лишь дополнительной документацией самой функции, однако их можно использовать при вызове функции для повышения читаемости. В то же время имена выходных параметров имеют значение, поскольку они определяют имена столбцов в типе результирующей строки. (Если имя выходного параметра не указано, система сгенерирует имя столбца по умолчанию.)
argtype: Тип данных параметра функции (может быть указан с именем схемы). Тип параметра может быть базовым типом, составным типом, типом домена или ссылкой на тип столбца таблицы. В зависимости от языка реализации, вы также можете указать псевдотипы, такие как cstring. Псевдотипы означают, что фактический тип параметра не полностью определён или находится за пределами набора обычных SQL-типов данных.
default_expr: Выражение, используемое как значение по умолчанию для параметра, если значение не указано. Выражение должно быть приводимым к типу параметра. Только параметры IN и INOUT могут иметь значения по умолчанию. Каждый последующий входной параметр в списке параметров также должен иметь значение по умолчанию, если какой-либо предшествующий ему параметр имеет таковое.
rettype: Тип возвращаемых данных (может быть указан с именем схемы). Возвращаемый тип может быть базовым типом, составным типом, типом домена или ссылкой на тип столбца таблицы. В зависимости от языка реализации, вы также можете указать псевдотипы, такие как cstring. Если функция не должна возвращать значение, укажите void в качестве типа возврата. Когда имеются параметры OUT или INOUT, предложение RETURNS может быть опущено. Если оно присутствует, оно должно соответствовать типу результата, подразумеваемому выходными параметрами: RECORD — если несколько выходных параметров, или тот же тип, что и у единственного выходного параметра. Модификатор SETOF указывает, что функция будет возвращать набор элементов, а не один элемент. Указывайте тип столбца с помощью конструкции tablename.columnname%TYPE.
column_name: Имя выходного столбца в синтаксисе RETURNS TABLE. Это фактически альтернативный способ объявления именованных параметров OUT, за исключением того, что RETURNS TABLE также подразумевает RETURNS SETOF.
column_type: Тип данных выходного столбца в синтаксисе RETURNS TABLE.
langname: Имя языка, на котором написана функция. Это может быть SQL, C, internal или имя пользовательского процедурного языка.
WINDOW: WINDOW указывает, что функция является оконной, а не обычной. В настоящее время это полезно только для функций, написанных на C. Атрибут WINDOW не может быть изменён при замене существующего определения функции.
IMMUTABLE: Указывает, что функция не может изменять базу данных и всегда возвращает одинаковый результат при одинаковых значениях параметров. Она не выполняет обращений к базе данных и не использует информацию, прямо не присутствующую в списке параметров. Если этот параметр указан, любой вызов функции с полностью константными аргументами может быть немедленно заменён значением функции.
STABLE: Указывает, что функция не может изменять базу данных и будет стабильно возвращать одинаковые результаты для одинаковых значений параметров в рамках одного сканирования таблицы, но её результаты могут меняться между разными SQL-запросами. Это подходит для функций, результат которых зависит от обращений к базе данных, значений параметров (например, текущего часового пояса) и т.п. Обратите внимание, что семейство функций current_timestamp считается стабильным, поскольку их значения не меняются в течение транзакции.
VOLATILE: Указывает, что значение функции может меняться даже в рамках одного сканирования таблицы, поэтому никакие оптимизации невозможны. Относительно немногие функции базы данных действительно являются изменчивыми (volatile). Примеры: random() и timeofday(). Однако любая функция с побочными эффектами должна классифицироваться как volatile, даже если её результат предсказуем, чтобы предотвратить удаление вызова в ходе оптимизации. Пример — setval().
LEAKPROOF: Указывает, что функция не имеет побочных эффектов. Помимо своего возвращаемого значения, она не раскрывает никакой информации о своих параметрах. Функции, которые генерируют сообщения об ошибках для некоторых значений параметров, но не для других, или включают значения параметров в сообщения об ошибках, не являются LEAKPROOF. Оптимизатор запросов может «продвигать» (push down) leakproof-функции (но не другие) в представления, созданные с опцией security_barrier. См. CREATE VIEW и CREATE RULE. Эта опция может быть установлена только суперпользователями.
CALLED ON NULL INPUT: (По умолчанию) Функция будет вызываться обычным образом, когда некоторые из её параметров равны NULL. Автор функции несёт ответственность за проверку на NULL (при необходимости) и соответствующее реагирование.
RETURNS NULL ON NULL INPUT или STRICT: Указывает, что функция всегда возвращает NULL, если любой из её параметров равен NULL. При указании этой опции функция не выполняется при передаче NULL-аргументов; вместо этого автоматически принимается, что результат равен NULL.
NO SQL, CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA: Эти атрибуты информируют оптимизатор запросов о том, содержит ли функция SQL-операторы и, если да, читают ли они и/или записывают данные.
[EXTERNAL] SECURITY INVOKER: (По умолчанию) Функция будет выполняться с привилегиями пользователя, который вызывает функцию. SECURITY DEFINER указывает, что функция должна выполняться с привилегиями пользователя, создавшего функцию. Ключевое слово EXTERNAL допускается для соответствия стандарту SQL, но является необязательным, поскольку данная возможность применима не только к внешним функциям, но и ко всем функциям.
EXECUTE ON ANY, EXECUTE ON MASTER, EXECUTE ON ALL SEGMENTS, EXECUTE ON INITPLAN: Атрибут EXECUTE ON определяет, где (на мастер-узле или экземплярах сегментов) функция будет выполняться при вызове во время выполнения запроса.
COST execution_cost: Положительное число, указывающее оценочную стоимость выполнения функции в единицах cpu_operator_cost. Если функция возвращает набор, execution_cost указывает стоимость на одну возвращаемую строку. Если не указано, функции на языке C и внутренние функции по умолчанию имеют стоимость 1, а функции на других языках — 100. Указание большего значения execution_cost заставит оптимизатор реже выполнять эту функцию.
SET configuration_parameter { TO value | = value | FROM CURRENT }: При входе в функцию предложение SET устанавливает значение указанного параметра конфигурации сессии. При выходе из функции параметр восстанавливается до предыдущего значения. SET FROM CURRENT сохраняет текущее значение параметра на момент выполнения команды CREATE FUNCTION как значение, которое будет применяться при входе в функцию.
AS 'definition': Определяет функцию как строковую константу; смысл зависит от языка. Это может быть внутреннее имя функции, путь к целевому файлу, текст SQL-команды или код на процедурном языке.
obj_file, link_symbol: Используются для динамически загружаемых функций на языке C, когда имя функции в исходном коде C отличается от имени SQL-функции. Строка obj_file — это имя файла, содержащего динамически загружаемый объект, а link_symbol — имя функции в исходном коде C. Если символ связи не указан, предполагается, что он совпадает с именем SQL-функции. Все функции должны иметь уникальные имена в C, поэтому перегруженные SQL-функции должны иметь различные имена в C (например, с использованием типа параметра как части имени C). Рекомендуется размещать общие библиотеки относительно $libdir (расположенного в $GPHOME/lib) или через путь к динамической библиотеке (задаётся параметром серверной конфигурации dynamic_library_path).
describe_function: Разрешает имя функции обратного вызова, которая будет выполнена при запросе, вызывающем эту функцию. Функция обратного вызова возвращает дескриптор кортежа, представляющий тип результата.
Весь скомпилированный код (файлы разделяемых библиотек), используемый для пользовательских функций, должен находиться в одном и том же месте на каждом узле массива YMatrix (мастер и все сегменты). Это место также должно быть в переменной LD_LIBRARY_PATH, чтобы сервер мог найти файл. Рекомендуется размещать разделяемые библиотеки на всех мастер-узлах массива YMatrix относительно $libdir (расположенного в $GPHOME/lib) или через путь к динамической библиотеке (задаётся параметрами конфигурации сервера dynamic_library_path).
Для входных параметров и возвращаемых значений допускается использование полного синтаксиса типов SQL. Однако некоторые детали спецификации типа (например, поле точности у типа numeric) зависят от реализации самой функции, и команда CREATE FUNCTION их не распознаёт и не контролирует.
YMatrix поддерживает перегрузку функций. Одно и то же имя может использоваться для нескольких различных функций, если у них разные типы входных параметров. Однако все функции должны иметь уникальные имена в C, поэтому перегруженные C-функции должны иметь различные имена в C (например, с использованием типа параметра как части имени C).
Если две функции имеют одинаковые имена и типы входных параметров, а выходные параметры игнорируются, они считаются одинаковыми. Например, следующие операторы конфликтуют:
CREATE FUNCTION foo(int) ...
CREATE FUNCTION foo(int, out text) ...Функции с разными списками типов параметров при создании не считаются конфликтующими, но если заданы значения параметров по умолчанию, они могут конфликтовать при использовании. Например:
CREATE FUNCTION foo(int) ...
CREATE FUNCTION foo(int, int default 42) ...Так как неясно, какая функция должна быть вызвана, выполнение foo(10) завершится ошибкой.
Когда повторный вызов CREATE FUNCTION ссылается на тот же целевой файл, файл загружается только один раз. Чтобы удалить и перезагрузить файл, используйте команду LOAD.
Вы должны иметь привилегию USAGE для языка, чтобы определять функции с использованием этого языка.
Часто бывает удобно использовать долларные кавычки вместо обычных одинарных кавычек при написании функции, определяющей строку. Если вы не используете долларные кавычки, вам нужно экранировать все одинарные кавычки и обратные слэши в определении функции, удваивая их. Строковая константа с долларными кавычками состоит из знака доллара ($), необязательного токена из нуля или более символов, ещё одного знака доллара, любой последовательности символов, составляющих содержимое строки, знака доллара, того же самого токена, что и в начальной ссылке, и завершающего знака доллара. В строках с долларными кавычками одинарные кавычки, обратные слэши или любые другие символы можно использовать без экранирования. Содержимое строки всегда интерпретируется буквально. Например, следующие два варианта задают строку "Dianne's horse" с использованием долларных кавычек:
$$Dianne's horse$$
$SomeTag$Dianne's horse$SomeTag$Если к функции присоединено предложение SET, команда SET LOCAL, выполненная для той же переменной внутри функции, ограничена этой функцией; при выходе из функции предыдущее значение параметра конфигурации всё равно восстанавливается. Однако обычная команда SET (без ключевого слова LOCAL) переопределяет предложение SET из CREATE FUNCTION, точно так же, как и предыдущие команды SET LOCAL. Если текущая транзакция не откатывается, эффект команды сохраняется и после выхода из функции.
Если функция с параметрами VARIADIC объявлена как STRICT, проверка строгости будет осуществляться на предмет того, является ли вариадический массив в целом непустым. Если массив содержит пустые элементы, PL/pgSQL всё равно вызовет функцию.
При замене существующей функции с помощью CREATE OR REPLACE FUNCTION действуют ограничения на изменение имён параметров. Вы не можете изменить имя, назначенное какому-либо входному параметру (хотя можете добавить имя параметру, которому ранее имя не было присвоено). Если есть несколько выходных параметров, имя выходного параметра изменить нельзя, поскольку это изменит имя столбца анонимного составного типа, описывающего результат функции. Эти ограничения введены для обеспечения того, чтобы существующие вызовы функции не нарушались при её замене.
В некоторых случаях, если данные в таблице, указанной в предложении FROM, распределены по сегментам базы данных YMatrix, YMatrix не поддерживает использование функций в запросах. Например, рассмотрим следующий SQL-запрос, содержащий функцию func():
SELECT func(a) FROM table1;Эта функция не поддерживается в запросе, если одновременно выполняются все следующие условия:
Если ни одно из этих условий не выполняется, функция поддерживается. В частности, функция поддерживается при выполнении любого из следующих условий:
Большинство функций, которые выполняют запросы к таблицам, могут запускаться только на мастер-узле. Однако функции, которые выполняют только SELECT-запросы к реплицированным таблицам, могут работать на сегментах. Если функция обращается к хэш-распределённой таблице или случайно распределённой таблице, она должна быть определена с использованием свойства EXECUTE ON MASTER. В противном случае при использовании такой функции в сложном запросе могут быть получены неверные результаты. Без этого свойства оптимизатор может решить, что выгоднее передать вызов функции на экземпляр сегмента.
Ниже приведены ограничения функций, определённых с атрибутом EXECUTE ON MASTER или EXECUTE ON ALL SEGMENTS:
Атрибут EXECUTE ON INITPLAN указывает, что функция содержит SQL-команду, которая направляет запросы на экземпляры сегментов и требует специальной обработки на мастер-узле. YMatrix обрабатывает такие функции на мастер-узле следующим образом:
В этом простом примере функция get_data() используется в команде CTAS для создания таблицы на основе данных из таблицы country. Эта функция содержит команду SELECT, извлекающую данные из таблицы country, и использует атрибут EXECUTE ON INITPLAN:
CREATE TABLE country(
c_id integer, c_name text, region int)
DISTRIBUTED RANDOMLY;
INSERT INTO country VALUES (11,'INDIA', 1 ), (22,'CANADA', 2), (33,'USA', 3);
CREATE OR REPLACE FUNCTION get_data()
RETURNS TABLE (
c_id integer, c_name text
)
AS $$
SELECT
c.c_id, c.c_name
FROM
country c;
$$
LANGUAGE SQL EXECUTE ON INITPLAN;
CREATE TABLE t AS SELECT * FROM get_data() DISTRIBUTED RANDOMLY;Если вы используете EXPLAIN ANALYZE VERBOSE для просмотра плана запроса команды CTAS, то в плане будет видно, что функция выполняется как часть узла InitPlan, и один из перечисленных срезов помечен как entry db. План запроса для простой команды CTAS без функции не содержит узла InitPlan и среза entrydb.
Если функция не имеет атрибута EXECUTE ON INITPLAN, команда CTAS вернёт ошибку, указывающую, что функция не может быть выполнена на срезе QE.
Когда функция использует атрибут EXECUTE ON INITPLAN, с помощью команды вида CREATE TABLE t AS SELECT * FROM get_data() результат функции собирается на мастер-узле, после чего результат перераспределяется по экземплярам сегментов при вставке данных. Если функция возвращает большой объём данных, мастер-узел может стать узким местом при сборе и перераспределении данных. Производительность можно улучшить, переписав функцию так, чтобы команда CTAS выполнялась внутри пользовательской функции, а имя таблицы передавалось как входной параметр. В этом примере функция сама выполняет команду CTAS и не требует атрибута EXECUTE ON INITPLAN. Выполнение команды SELECT создаёт таблицу t1 с помощью функции, которая запускает команду CTAS.
CREATE OR REPLACE FUNCTION my_ctas(_tbl text) RETURNS VOID AS
$$
BEGIN
EXECUTE format('CREATE TABLE %s AS SELECT c.c_id, c.c_name FROM country c DISTRIBUTED RANDOMLY', _tbl);
END
$$
LANGUAGE plpgsql;
SELECT my_ctas('t1');Очень простая функция сложения:
CREATE FUNCTION add(integer, integer) RETURNS integer
AS 'select $1 + $2;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;Инкремент целого числа с использованием имени параметра в PL/pgSQL:
CREATE OR REPLACE FUNCTION increment(i integer) RETURNS
integer AS $$
BEGIN
RETURN i + 1;
END;
$$ LANGUAGE plpgsql;Добавление значения памяти по умолчанию для каждого сегмента хоста в функцию PL/pgSQL:
CREATE OR REPLACE FUNCTION function_with_query() RETURNS
SETOF text AS $$
BEGIN
RETURN QUERY
EXPLAIN ANALYZE SELECT * FROM large_table;
END;
$$ LANGUAGE plpgsql
SET statement_mem='256MB';Возврат массива ENUM с использованием полиморфных типов:
CREATE TYPE rainbow AS ENUM('red','orange','yellow','green','blue','indigo','violet');
CREATE FUNCTION return_enum_as_array( anyenum, anyelement, anyelement )
RETURNS TABLE (ae anyenum, aa anyarray) AS $$
SELECT $1, array[$2, $3]
$$ LANGUAGE SQL STABLE;
SELECT * FROM return_enum_as_array('red'::rainbow, 'green'::rainbow, 'blue'::rainbow);Возврат записи, содержащей несколько выходных параметров:
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(4);Функция определена с EXECUTE ON ALL SEGMENTS, чтобы выполняться на всех основных экземплярах. Команда SELECT запускает функцию, возвращающую время выполнения на каждом экземпляре сегмента:
CREATE FUNCTION run_on_segs (text) returns setof text as $$
begin
return next ($1 || ' - ' || now()::text );
end;
$$ language plpgsql VOLATILE EXECUTE ON ALL SEGMENTS;
SELECT run_on_segs('my test');Эта функция ищет названия деталей в таблице parts. Таблица parts является реплицированной, поэтому функция может выполняться как на мастер-узле, так и на основном сегменте:
CREATE OR REPLACE FUNCTION get_part_name(partno int) RETURNS text AS
$$
DECLARE
result text := ' ';
BEGIN
SELECT part_name INTO result FROM parts WHERE part_id = partno;
RETURN result;
END;
$$ LANGUAGE plpgsql;Если вы выполните SELECT get_part_name(100); на мастер-узле, функция будет выполнена на мастер-узле. (Мастер-экземпляр направляет запрос на один из основных сегментов.) Если таблица orders является распределённой, и вы выполните следующий запрос, функция get_part_name() будет выполнена на основном сегменте:
SELECT order_id, get_part_name(orders.part_no) FROM orders;CREATE FUNCTION определён в SQL:1999 и более поздних версиях. Версия YMatrix похожа, но не полностью совместима. Эти свойства не являются переносимыми и недоступны в разных языках.
Для совместимости с некоторыми другими системами управления базами данных argmode можно указывать до или после argname. Однако только первый способ соответствует стандарту.
Что касается значений параметров по умолчанию, стандарт SQL предусматривает только синтаксис с ключевым словом DEFAULT. Синтаксис с = используется в T-SQL и Firebird.