> For the complete documentation index, see [llms.txt](https://wfsys.gitbook.io/wt-knowledge-base/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://wfsys.gitbook.io/wt-knowledge-base/sql/custom-functions/pl-functions.md).

# Функции на процедурном языке 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](https://postgrespro.ru/docs/postgrespro/15/plpgsql). В этой статье кратко рассмотрим особенности функций на процедурном языке.

## Структура PL/pgSQL <a href="#plpgsql-structure" id="plpgsql-structure"></a>

### Аргументы и выходные значения <a href="#arguments-and-out-values" id="arguments-and-out-values"></a>

В плане объявления аргументов и выходных значений синтаксис функций на PL/pgSQL соответствуют синтаксису SQL-функций, которые рассматриваются в статье:

{% content-ref url="/pages/fEKUiOgWfpjAQNwAANpn" %}
[Функции на языке запросов (SQL)](/wt-knowledge-base/sql/custom-functions/sql-functions.md)
{% endcontent-ref %}

Функции на PL/pgSQL могут принимать в качестве аргументов все поддерживаемые сервером скалярные типы данных или массивы и возвращать в качестве результата любой из этих типов. Они могут принимать и возвращать именованные составные типы (строковый тип). Есть возможность объявить функцию на PL/pgSQL, возвращающую `record` - строковый тип, чьи столбцы будут определены в спецификации вызывающего запроса.

Функции на PL/pgSQL могут возвращать "множества" (или таблицы) любого типа, которые могут быть возвращены в виде одного объекта. Такие функции генерируют вывод, выполняя команду `RETURN NEXT` для каждого элемента результирующего набора или `RETURN QUERY` для вывода результата запроса.

При отсутствии полезного возвращаемого значения функция на PL/pgSQL может возвращать `void`.

Функции на PL/pgSQL можно объявить с выходными параметрами вместо явного задания типа возвращаемого значения. Это не добавляет никаких фундаментальных возможностей языку, но часто бывает удобно, особенно для возвращения нескольких значений.

### Тело функции <a href="#function-body" id="function-body"></a>

Основное отличие функций на языке PL/pgSQL заключается в блочной структуре текста тела функции:

```plsql
CREATE FUNCTION имя()
  RETURNS тип_результата AS
$BODY$
DECLARE
  -- Объявление переменных 
BEGIN
  -- Операторы
END;
$BODY$
  LANGUAGE plpgsql;
```

В блоке `DECLARE` объявляются переменные, которым можно задавать константные значения, либо присваивать результаты выполнения SQL-команд и вызова других функций.

{% hint style="info" %}
Правила выполнения команд SQL, логика присваивания значений рассматриваются в разделе [41.5. Основные операторы](https://postgrespro.ru/docs/postgrespro/15/plpgsql-statements) на официальном сайте.
{% endhint %}

После ключевого слова `BEGIN` задаются управляющие структуры ([41.6. Управляющие структуры](https://postgrespro.ru/docs/postgrespro/15/plpgsql-control-structures)):

* Условные операторы `IF` и `CASE` - позволяют выполнять команды в зависимости от определённых условий;
* Простые циклы `LOOP`, `WHILE` и `FOR` - позволяют повторить серию команд;
* Цикл по результатам запроса `FOR`;
* Цикл по элементам массива `FOREACH`;
* Секция `EXCEPTION` - позволяет перехватывать и обрабатывать ошибки.

### Пример <a href="#example-plpgsql" id="example-plpgsql"></a>

В качестве примера рассмотрим функцию удаления записи:

```plsql
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()*. Если идентификатор используется, то отправляем запись в архив, иначе удаляем.

## Анонимный блок кода <a href="#anonymous-code-block" id="anonymous-code-block"></a>

Процедурный язык позволяет создавать анонимные функции, которые можно использовать напрямую в тэге `<SqlQuery>` в серверном xml-файле, что открывает возможности применять переменные и управляющие структуры.

Ключевое слово `DO` описывает блок кода, который выполняет разовую анонимную функцию на процедурном языке:

```plsql
DO
$BODY$
DECLARE
  -- Объявление переменных
BEGIN
  -- Операторы
END;
$BODY$;
```

Блок кода воспринимается, как тело функции, которая не имеет параметров и возвращает `void`. Этот код разбирается и выполняется один раз.

### Пример <a href="#example-block-do" id="example-block-do"></a>

Простой пример анонимной функции:

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