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


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://wfsys.gitbook.io/wt-knowledge-base/sql/custom-functions/pl-functions.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.