Создание и использование функции в Базе данных Azure для PostgreSQL

  • 5 мин

Мы уже узнали, что PostgreSQL поддерживает разные языки. Функции можно разделить на четыре разных типа:

  • Функции, написанные в SQL.
  • Процедурные языковые функции, написанные на поддерживаемом процедурном языке, например PL.pgSQL.
  • Внутренние функции.
  • Функции C-языка.

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

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

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

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

Волатильность функции значительно отличается от эффективности, с которой оптимизатор запросов обрабатывает его.

Создание функции

Функция возвращает одно значение и может использоваться в инструкции SELECT.

Синтаксис создания функции:

CREATE [OR REPLACE] FUNCTION
myfunction ([inputparam] type {default})
RETURNS returntype AS
$$
SQL body
$$
LANGUAGE 'language_name';
CREATE FUNCTION

Как и в случае с хранимыми процедурами, символ $$ используется для запуска и окончания строки.

Функции принимают следующие параметры:

  • имя — при необходимости укажите имя схемы.
  • argmode — режим аргумента. Может быть IN, OUT, INOUT или VARIADIC. Значение по умолчанию — IN. VARDIAC — это неопределенное число входных аргументов одного типа, за которым следует аргументы OUT. Аргументы OUT и INOUT нельзя использовать вместе с нотацией RETURNS TABLE.
  • argname — имя аргумента.
  • argtype — тип данных аргумента. Могут быть базовыми, составными или доменными типами или ссылаться на тип столбца таблицы. Тип столбца записывается как table_name.column_name%TYPE. Этот тип данных может помочь сделать функцию независимо от изменений определения таблицы.
  • t_expr — значение по умолчанию (одного типа), если параметр не указан. Только параметры IN и INOUT имеют значение по умолчанию. Входные параметры после параметра со значением по умолчанию также должны иметь значения по умолчанию.
  • rettype — возвращаемый тип данных, который может быть базовым, составным или доменным типом или ссылаться на тип столбца таблицы. Если функция не возвращает значение, укажите тип возвращаемого значения как void. При наличии параметров OUT или INOUT предложение RETURNS может быть опущено. Если он присутствует, он должен согласиться с типом результата, подразумеваемым выходными параметрами: RECORD, если есть несколько выходных параметров или тот же тип, что и один выходной параметр. Модификатор SETOF указывает, что функция возвращает набор элементов, а не один элемент. Тип столбца определяется с помощью table_name.
  • column_name — имя выходного столбца в синтаксисе RETURNS TABLE. Этот параметр объявляет именованный параметр OUT, за исключением того, что функция RETURNS TABLE также подразумевает ЗНАЧЕНИЕ SETOF.
  • column_type — тип данных выходного столбца в синтаксисе RETURNS TABLE.
  • lang_name — язык, используемый для написания процедуры. Значение по умолчанию — sql, если указан sql_body. Может быть sql, c, внутренний или имя определяемого пользователем процедурного языка, например plpgsql.

Используйте ключевые слова IMMUTABLE, STABLE или VOLATILE в качестве указания оптимизатору запросов о функции. VOLATILE — это значение по умолчанию.

Вызов функции

Функцию можно использовать в запросе, передавая в него все соответствующие параметры. Например:

SELECT myfunction(3), CatID, CatName
    FROM myCats

Встроенные функции

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

Пример встроенной строковой функции — подстрока .

substring (*string* text [ FROM *start* integer ] [ FOR *count* integer ] ) → text

Эта функция принимает три входных параметра:

  • Строка (текстовый тип)
  • ОТ начала (тип целое число)
  • ЧИСЛО FOR (целое число типа)

Подстрока возвращает часть входного текста, начиная с начального символа, и останавливается после количества символов. Например:

substring('Thomas' from 2 for 3) → hom
substring('Thomas' from 3) → omas
substring('Thomas' for 2) → Th

Эта функция аналогична функции "substr":

substr ( *string* text, *start* integer [, *count* integer ] ) → text
substr('alphabet', 3) → phabet
substr('alphabet', 3, 2) → ph

Заметка

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