Работа №4 функции, определенные пользователем

Типы функций PostgreSQL.. Создание и модификация хранимых процедур. Использование параметров в хранимых процедурах. Создание PL/pgSQL-функции. Обработка ошибок в PL/pgSQL-функциях. Триггеры.

1Типы функций PostgreSql

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

• функции языка запросов или хранимые процедуры (функции, написанные на SQL),

• функции процедурного языка (функции, написанные, например, на PL/pgSQL),

• внутренние функции (статично связанные функции, написанные на языке С),

• С-функции (динамически связываемые функции, написанные на языке С/С++).

Все перечисленные функции могут иметь аргументы (параметры), относящиеся к базовым типам, составным типам или их комбинации. Кроме того, функции могут возвращать значения базового или составного типа или их последовательности.

Для создания функций в PostgreSQL используется инструкция CREATE FUNCTION, синтаксис которой имеет следующий вид:

CREATE [ OR REPLACE ] FUNCTION

имя_функции ( [ [ [ IN] | OUT | INOUT | VARIADIC] [имя_параметра] тип_параметра [{ DEFAULT | = } выражение ] [, ...] ] )

[ RETURNS тип_возвращаемого_значения | RETURNS TABLE ( столбец тип [, ...] ) ]

{ LANGUAGE

| WINDOW

| { IMMUTABLE | STABLE | [ VOLATILE ] }

| { [CALLED ON NULL INPUT] | RETURNS NULL ON NULL INPUT | STRICT }

| {[ [ EXTERNAL ] SECURITY INVOKER] | [ EXTERNAL ] SECURITY DEFINER }

| AS 'тело_функции'

} ...

где

[[IN] | OUT | INOUT | VARIADIC  спецификация параметров: входных (IN), выходных (OUT), входных-выходных (INOUT) и массив входных параметров переменной длины (VARIADIC).

RETURNS | RETURNS TABLE  определяет тип возвращаемого значения.

SQL-функция может ничего не возвращать (VOID), возвращать первую строку из результата последнего запроса, содержащегося в теле функции, или набор значений некоторого типа (RETURNS SETOF или RETURNS TABLE).

Примечание

Считается, что первая строка из множества строк, являющихся результатом выполнения некоторого запроса, определена нечетко, если в запросе не используется ORDER BY.

Если функция определена с возвращаемым значением, отличным от VOID, то последней командой в теле функции должна быть SELECT, INSERT, UPDATE или DELETE с выражением, которое совместимо по типу с возвращаемым значением функции.

LANGUAGE  язык, на котором написана функция (например, SQL).

WINDOW  оконная функция. Оконная функция выполняет вычисление для набора строк, которые так или иначе связаны с текущей строкой курсора. Это похоже на вычисления с использованием стандартных агрегирующих функций, но в отличие от агрегирующих функций, использование оконной функции не требует объединения строк в группы с выводом единственной результирующей строки для каждой группы. Практически этот параметр применим лишь для функций, написанных на С.

IMMUTABLE | STABLE | [VOLATILE]  информация для оптимизатора запросов, указывающая, что: функция не будет модифицировать БД и всегда возвращает то же самое значение при одних и тех же значениях параметров (IMMUTABLE), функция не будет модифицировать БД и не изменяет возвращаемое значение в рамках одной транзакции (однократного прохода таблицы) (STABLE), функция может изменять возвращаемое значение даже в рамках одной транзакции (VOLATILE).

CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT  функция может вызываться при null-значениях некоторых ее параметров (CALLED ON NULL INPUT), в противном случае функция всегда возвращает null, если любой из ее параметров имеет значение null.

SECURITY INVOKER | SECURITY DEFINER  функция будет выполняться с правами того пользователя, который ее вызывает (INVOKER), или того, который ее создал (DEFINER).

После служебного слова AS идет строка, содержащая тело функции. Это может бать имя встроенной функции, SQL-скрипт, текст на процедурном языке или путь к объектному файлу. Вместо одинарных кавычек для ограничения тела функции удобнее использовать два подряд стоящих символа доллара ($$), иначе при использовании в определении функции одинарных кавычек или обратной косой черты их придется дублировать.

Для изменения и удаления функций можно использовать инструкции ALTER FUNCTION и DROP FUNCTION. При использовании ALTER FUNCTION сохраняются все права доступа, назначенные ранее модифицируемой функции.