CREATE FUNCTION
Создает новую функцию.
Синтаксис
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>
| ROWS <result_rows>
| 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 либо создает новую функцию, либо заменяет существующее определение.
Имя новой функции не должно совпадать ни с одной существующей функцией с теми же типами входных аргументов в той же схеме. Однако функции с разными типами аргументов могут иметь одно и то же имя (overloading).
Чтобы обновить определение существующей функции, используйте CREATE OR REPLACE FUNCTION.
Таким способом нельзя изменить имя или типы аргументов функции (на самом деле это создало бы новую, отдельную функцию).
Также CREATE OR REPLACE FUNCTION не позволит изменить возвращаемый тип существующей функции.
Чтобы сделать это, необходимо удалить и заново создать функцию.
При использовании параметров OUT это означает, что вы не можете изменить типы каких-либо параметров OUT, кроме как удалив функцию.
Если вы удаляете и затем заново создаете функцию, вам придется удалить существующие объекты (правила, представления, триггеры и т.п.), которые ссылаются на удаленную функцию.
Используйте CREATE OR REPLACE FUNCTION, чтобы изменить определение функции без нарушения работы объектов, которые на нее ссылаются.
Пользователь, который создает функцию, становится владельцем функции.
Чтобы иметь возможность создать функцию, необходимо иметь привилегию USAGE для типов аргументов и возвращаемого типа.
Дополнительные сведения о создании функций см. в разделе User Defined Functions документации PostgreSQL.
Ограниченное использование функций VOLATILE и STABLE
Чтобы предотвратить рассинхронизацию данных между сегментами в Greengage DB, любая функция, классифицированная как STABLE или VOLATILE, не может выполняться на уровне сегментов, если она содержит SQL или каким-либо образом изменяет базу данных.
Например, такие функции, как random() или timeofday(), не допускается выполнять на распределенных данных в Greengage DB, поскольку они потенциально могут привести к несогласованности данных между экземплярами сегментов.
Чтобы обеспечить согласованность данных, функции VOLATILE и STABLE можно безопасно использовать в операторах, которые вычисляются и выполняются на мастере.
Например, следующие операторы всегда выполняются на мастере (операторы без выражения FROM):
SELECT setval('myseq', 201);
SELECT foo();В случаях, когда оператор содержит выражение FROM с распределенной таблицей, а функция, используемая в FROM, просто возвращает набор строк, выполнение на сегментах может быть разрешено:
SELECT * FROM foo();Исключением из этого правила являются функции, которые возвращают ссылку на таблицу (rangeFuncs), или функции, которые используют тип данных refCursor.
Обратите внимание, что вы не можете возвращать refcursor из функции любого вида в Greengage DB.
Атрибуты волатильности функций и атрибуты EXECUTE ON
Атрибуты волатильности (IMMUTABLE, STABLE, VOLATILE) и атрибуты EXECUTE ON задают два разных аспекта выполнения функции.
В общем случае волатильность указывает, когда выполняется функция, а EXECUTE ON указывает, где она выполняется.
Например, функция, определенная с атрибутом IMMUTABLE, может выполняться на этапе планирования запроса, тогда как функция с атрибутом VOLATILE должна выполняться для каждой строки в запросе.
Функция с атрибутом EXECUTE ON MASTER выполняется только на мастере, а функция с атрибутом EXECUTE ON ALL SEGMENTS выполняется на всех первичных экземплярах сегментов (не на мастере).
Функции и реплицируемые таблицы
Пользовательская функция, которая выполняет только команды SELECT над реплицируемыми таблицами, может выполняться на сегментах.
Реплицируемые таблицы, созданные с выражением DISTRIBUTED REPLICATED, хранят все свои строки на каждом сегменте.
Чтение таких таблиц на сегментах является безопасным, но обновления реплицируемых таблиц должны выполняться на мастере.
Параметры
| Параметр | Описание |
|---|---|
name |
Имя (опционально указанное со схемой) создаваемой функции |
argmode |
Режим аргумента: |
argname |
Имя аргумента. Некоторые языки (в настоящее время только SQL и PL/pgSQL) позволяют использовать имя в теле функции. Для других языков имя входного аргумента является лишь дополнительной справочной информацией, но вы можете использовать имена входных аргументов при вызове функции для улучшения читаемости. В любом случае имя выходного аргумента является значимым, поскольку оно определяет имя столбца в типе строки результата. Если вы опустите имя выходного аргумента, система выберет имя столбца по умолчанию |
argtype |
Типы данных аргументов функции (опционально указанные со схемой), если они есть.
Типы аргументов могут быть базовыми, составными или доменными, либо могут ссылаться на тип столбца таблицы.
В зависимости от языка реализации также может быть разрешено указывать псевдотипы, такие как |
default_expr |
Выражение, используемое в качестве значения по умолчанию, если параметр не указан.
Выражение должно приводиться к типу аргумента параметра.
Только параметры |
rettype |
Возвращаемый тип данных (опционально указанный со схемой).
Возвращаемый тип может быть базовым, составным или доменным, либо может ссылаться на тип столбца таблицы.
В зависимости от языка реализации также может быть разрешено указывать псевдотипы, такие как |
column_name |
Имя выходного столбца в синтаксисе |
column_type |
Тип данных выходного столбца в синтаксисе |
langname |
Имя языка, на котором реализована функция.
Это может быть |
WINDOW |
|
IMMUTABLE |
Эти атрибуты информируют оптимизатор запросов о поведении функции.
Можно указать не более одного варианта.
Если ни один из них не указан, по умолчанию предполагается |
CALLED ON NULL INPUT |
|
NO SQL |
Атрибуты информируют оптимизатор запросов о том, содержит ли функция SQL-операторы и, если содержит, читают ли эти операторы данные и/или изменяют их |
[EXTERNAL] SECURITY INVOKER |
|
EXECUTE ON ANY |
Атрибуты Greengage DB не поддерживает атрибут Сведения об использовании атрибутов |
COST <execution_cost> |
Положительное число, определяющее оценочную стоимость выполнения функции в единицах cpu_operator_cost.
Если функция возвращает набор, |
ROWS <result_rows> |
Положительное число, задающее примерное количество строк, которое планировщик ожидает получить на выходе этой функции.
Это указание допустимо, если функция объявлена как возвращающая множество.
Предполагаемое по умолчанию значение — |
SET <configuration_parameter> { TO <value> | = <value> | FROM CURRENT } |
Выражение |
definition |
Строковая константа, определяющая функцию; смысл зависит от языка. Это может быть имя внутренней функции, путь к объектному файлу, SQL-команда или текст на процедурном языке |
obj_file, link_symbol |
Эта форма выражения |
describe_function |
Имя callback-функции, которая выполняется при разборе запроса, вызывающего эту функцию. Callback-функция возвращает описание строки (tuple descriptor), которое указывает тип результата |
Примечания
Любой скомпилированный код (файлы общих библиотек) для пользовательских функций должен быть размещен по одному и тому же пути на каждом хосте в кластере Greengage DB (мастер и все сегменты).
Это местоположение также должно находиться в LD_LIBRARY_PATH, чтобы сервер мог найти файлы.
Рекомендуется размещать общих библиотеки либо относительно $libdir (который находится в $GPHOME/lib), либо через динамический путь библиотек (задается параметром конфигурации сервера dynamic_library_path) на всех экземплярах мастера в кластере Greengage DB.
Полный синтаксис SQL-типа допускается для входных аргументов и возвращаемого значения.
Однако некоторые детали спецификации типа (например, поле точности для типа numeric) зависят от реализации самой функции и не распознаются и не проверяются командой CREATE FUNCTION.
Greengage DB поддерживает перегрузку имен функций (function overloading). Одно и то же имя может использоваться для нескольких разных функций, если у них различаются типы входных аргументов. Однако имена функций на C должны быть разными, поэтому функциям с перегрузкой на C нужно задавать разные C-имена (например, включать типы аргументов в C-имя).
Две функции считаются одинаковыми, если у них совпадают имена и типы входных аргументов без учета параметров OUT.
Таким образом, например, следующие объявления функций конфликтуют:
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 для этого языка.
Для записи строки определения функции может быть удобно использовать заключение строк в символ доллара (dollar quoting) вместо обычного синтаксиса с одинарными кавычками.
Без заключения строки в символ доллара любые одинарные кавычки или косая черта в определении функции должны экранироваться удвоением.
Строковая константа, заключенная в симол доллара, состоит из знака доллара ($), опционального тега из нуля или более символов, еще одного знака доллара, произвольной последовательности символов (содержимого строки), знака доллара, того же тега, который начинал эту запись, и еще одного знака доллара.
Внутри такой строки одинарные кавычки, косая черта или любые символы могут использоваться без экранирования.
Содержимое строки всегда записывается буквально.
Например, строку "Dianne’s horse" можно заключить в символы доллара двумя способами:
$$Dianne's horse$$ $SomeTag$Dianne's horse$SomeTag$
Если к функции добавлено выражение SET, эффекты команды SET LOCAL, выполненной внутри функции для той же переменной, ограничиваются функцией; предыдущее значение параметра конфигурации все равно восстанавливается при выходе из функции.
Однако обычная команда SET (без LOCAL) переопределяет выражение SET в CREATE FUNCTION, так же как это произошло бы для предыдущей команды SET LOCAL.
Эффекты такой команды будут сохраняться после выхода из функции, если не произошло отката транзакции.
Если функция с аргументом VARIADIC объявлена как STRICT, проверка строгого режима проверяет, что весь вариативный массив целиком не равен NULL.
PL/pgSQL в любом случае вызовет функцию, если массив содержит элементы NULL.
При замене существующей функции с помощью CREATE OR REPLACE FUNCTION существуют ограничения на изменение имен параметров.
Нельзя изменить имя, уже назначенное любому входному параметру (хотя можно добавить имена параметрам, у которых их не было).
Если выходных параметров больше одного, нельзя изменять имена выходных параметров, потому что это изменит имена столбцов анонимного составного типа, описывающего результат функции.
Эти ограничения введены, чтобы существующие вызовы функции не перестали работать после ее замены.
Использование функций в запросах с распределенными данными
В некоторых случаях Greengage DB не поддерживает использование функций в запросе, где данные таблицы в выражении FROM распределены по сегментам Greengage DB.
Например, следующий SQL-запрос содержит функцию func():
SELECT func(a) FROM table1;Функция не поддерживается для использования в запросе, если выполняются все следующие условия:
-
Данные таблицы
table1распределены по сегментам Greengage DB. -
Функция
func()читает или изменяет данные из распределенных таблиц. -
Функция
func()возвращает более одной строки или принимает аргумент (a), который берется изtable1.
Если какое-либо условие не выполняется, функция поддерживается. В частности, функция поддерживается, если выполняется любое из следующих условий:
-
Функция
func()не обращается к данным распределенных таблиц или обращается только к данным, которые находятся на мастере Greengage DB. -
Таблица
table1является таблицей только мастера. -
Функция
func()возвращает только одну строку и принимает только входные аргументы, являющиеся константными значениями. -
Функция поддерживается, если ее можно изменить так, чтобы она не требовала входных аргументов.
Использование атрибутов EXECUTE ON
Большинство функций, которые выполняют запросы для доступа к таблицам, могут выполняться только на мастере.
Однако функции, которые выполняют только запросы SELECT к реплицируемым таблицам, могут выполняться на сегментах.
Если функция обращается к хеш-распределенной таблице или к таблице со случайным распределением, функцию следует определить с атрибутом EXECUTE ON MASTER.
В противном случае функция может возвращать некорректные результаты, когда используется в сложном запросе.
Без этого атрибута оптимизация планировщика может решить, что выгодно перенести вызов функции на экземпляры сегментов.
Ограничения для функций, определенных с атрибутом EXECUTE ON MASTER или EXECUTE ON ALL SEGMENTS:
-
Функция должна быть функцией, возвращающей набор (set-returning function).
-
Функция не может находиться в выражении
FROM. -
Функция не может находиться в списке
SELECTзапроса с выражениемFROM. -
При использовании такой функции запрос переключается с оптимизатора GPORCA на Postgres Planner.
Атрибут EXECUTE ON INITPLAN означает, что функция содержит SQL-команду, которая отправляет запросы на экземпляры сегментов и требует специальной обработки на мастере в Greengage DB.
Когда это возможно, Greengage DB обрабатывает функцию на мастере следующим образом:
-
Greengage DB выполняет функцию как часть узла InitPlan на мастере и временно сохраняет результат ее работы.
-
В MainPlan плана запроса функция вызывается в EntryDB (специальном исполнителе запросов (QE), который выполняется на мастере), и Greengage DB возвращает данные, которые были сохранены при выполнении функции как части InitPlan.
-
В MainPlan функция не выполняется.
Пример ниже использует функцию 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;Если вы посмотрите план запроса команды CTAS с помощью EXPLAIN ANALYZE VERBOSE, план покажет, что функция выполняется как часть узла InitPlan, а один из перечисленных слайсов (slice) будет помечен как entry db.
План запроса простой команды CTAS без функции не содержит узла InitPlan и слайса entry db.
Если бы функция не содержала атрибут EXECUTE ON INITPLAN, команда CTAS вернула бы ошибку function cannot execute on a QE slice.
Когда функция использует атрибут 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');Эта функция ищет наименование детали в таблице деталей. Таблица деталей является реплицируемой, поэтому функция может выполняться на мастере или на первичных сегментах:
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 и более поздних стандартах.
Версия Greengage DB похожа, но не полностью совместима.
Атрибуты не являются переносимыми, так же как и разные доступные языки.
Для совместимости с некоторыми другими СУБД argmode можно записывать либо перед argname, либо после.
При этом только первый вариант соответствует стандарту.
Для значений параметров по умолчанию стандарт SQL определяет только синтаксис с ключевым словом DEFAULT.
Синтаксис с = используется в T-SQL и Firebird.