Функции на процедурном языке PL/pgSQL
PostgreSQL позволяет разрабатывать пользовательские функции не только на SQL и C, но и на процедурных языках (PL, Procedural Language). В настоящее время стандартный дистрибутив PostgreSQL включает четыре процедурных языка: PL/pgSQL, PL/Tcl, PL/Perl и PL/Python. В своей практике мы используем язык PL/pgSQL.
Ключевые преимущества PL/pgSQL:
добавляет управляющие структуры к языку SQL;
может выполнять сложные вычисления;
наследует все пользовательские типы, функции и операторы;
прост в использовании.
Подробно о процедурном языке PL/pgSQL можете почитать на официальном сайте в разделе Глава 41. PL/pgSQL — процедурный язык SQL. В этой статье кратко рассмотрим особенности функций на процедурном языке.
Структура PL/pgSQL
Аргументы и выходные значения
В плане объявления аргументов и выходных значений синтаксис функций на PL/pgSQL соответствуют синтаксису SQL-функций, которые рассматриваются в статье:
Функции на языке запросов (SQL)Функции на PL/pgSQL могут принимать в качестве аргументов все поддерживаемые сервером скалярные типы данных или массивы и возвращать в качестве результата любой из этих типов. Они могут принимать и возвращать именованные составные типы (строковый тип). Есть возможность объявить функцию на PL/pgSQL, возвращающую record
- строковый тип, чьи столбцы будут определены в спецификации вызывающего запроса.
Функции на PL/pgSQL могут возвращать "множества" (или таблицы) любого типа, которые могут быть возвращены в виде одного объекта. Такие функции генерируют вывод, выполняя команду RETURN NEXT
для каждого элемента результирующего набора или RETURN QUERY
для вывода результата запроса.
При отсутствии полезного возвращаемого значения функция на PL/pgSQL может возвращать void
.
Функции на PL/pgSQL можно объявить с выходными параметрами вместо явного задания типа возвращаемого значения. Это не добавляет никаких фундаментальных возможностей языку, но часто бывает удобно, особенно для возвращения нескольких значений.
Тело функции
Основное отличие функций на языке PL/pgSQL заключается в блочной структуре текста тела функции:
CREATE FUNCTION имя()
RETURNS тип_результата AS
$BODY$
DECLARE
-- Объявление переменных
BEGIN
-- Операторы
END;
$BODY$
LANGUAGE plpgsql;
В блоке DECLARE
объявляются переменные, которым можно задавать константные значения, либо присваивать результаты выполнения SQL-команд и вызова других функций.
После ключевого слова BEGIN
задаются управляющие структуры (41.6. Управляющие структуры):
Условные операторы
IF
иCASE
- позволяют выполнять команды в зависимости от определённых условий;Простые циклы
LOOP
,WHILE
иFOR
- позволяют повторить серию команд;Цикл по результатам запроса
FOR
;Цикл по элементам массива
FOREACH
;Секция
EXCEPTION
- позволяет перехватывать и обрабатывать ошибки.
Пример
В качестве примера рассмотрим функцию удаления записи:
CREATE OR REPLACE FUNCTION template.item_try_delete(in_item_id smallint)
RETURNS boolean AS
$BODY$
BEGIN
PERFORM * FROM template.item WHERE item_id = in_item_id;
IF NOT FOUND THEN
RETURN TRUE;
END IF;
IF (
SELECT used
FROM template.is_used('item', 'item_id', ARRAY[]::text[], ARRAY[]::text[], in_item_id)
)
THEN
UPDATE template.item
SET
archive = true
WHERE
item_id = in_item_id;
RETURN FALSE;
ELSE
DELETE FROM template.item
WHERE
item_id = in_item_id;
RETURN TRUE;
END IF;
END;
$BODY$
LANGUAGE plpgsql;
Первым делом проверяем, существует ли удаляемая запись. Если записи в таблице нет, то завершаем выполнении функции с помощью оператора RETURN
. Затем проверяем, есть ли в других таблицах ссылки на удаляемую запись, для этого вызываем функцию template.is_used(). Если идентификатор используется, то отправляем запись в архив, иначе удаляем.
Анонимный блок кода
Процедурный язык позволяет создавать анонимные функции, которые можно использовать напрямую в тэге <SqlQuery>
в серверном xml-файле, что открывает возможности применять переменные и управляющие структуры.
Ключевое слово DO
описывает блок кода, который выполняет разовую анонимную функцию на процедурном языке:
DO
$BODY$
DECLARE
-- Объявление переменных
BEGIN
-- Операторы
END;
$BODY$;
Блок кода воспринимается, как тело функции, которая не имеет параметров и возвращает void
. Этот код разбирается и выполняется один раз.
Пример
Простой пример анонимной функции:
DO
$BODY$
DECLARE
_item_id smallint = 3;
BEGIN
IF EXISTS(SELECT * FROM template.city WHERE city_id = _item_id)
THEN
INSERT INTO template.city(title)
VALUES ('Москва')
RETURNING city_id INTO _item_id;
ELSE
UPDATE template.city
SET title = 'Москва'
WHERE city_id = _item_id;
END IF;
-- Можем использовать _item_id
-- с новым значением, если выполнялась команда INSERT
-- либо со старым, если выполнялась команда UPDATE
END;
$BODY$;
В примере проверяем существование записи города в таблице template.city. Если такой записи нет, то создаем, иначе обновляем существующую.
Last updated