Функции на процедурном языке 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-функций, которые рассматриваются в статье:

pageФункции на языке запросов (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-команд и вызова других функций.

Правила выполнения команд SQL, логика присваивания значений рассматриваются в разделе 41.5. Основные операторы на официальном сайте.

После ключевого слова 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. Если такой записи нет, то создаем, иначе обновляем существующую.