Создание и использование функции в Базе данных 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

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

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

  • name — при необходимости укажите имя схемы.
  • 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.

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

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

  • String (текст);
  • FROM start (целое число);
  • ЧИСЛО FOR (целое число типа)

Функция substring возвращает часть входного текста, начиная с номера символа start и следующие count символов. Например:

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

Эта функция аналогична подстроки:

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

Примечание.

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