# Как активировать PL/Python, примеры функции и процедуры

Чтобы использовать **PL/Python**, необходимо, чтобы модуль Python был установлен в вашей операционной системе для вашей версии PostgreSQL (например, пакет `postgresql-plpython3` в Linux), после чего его можно включить в нужной базе данных.

#### 1. Активация PL/Python

Активировать расширение нужно в целевой базе данных от имени суперпользователя (`postgres`):

SQL

``` CREATE EXTENSION IF NOT EXISTS plpython3u; ```

**Важная деталь:** Буква `u` на конце расширения означает *untrusted* (недоверенный). Это значит, что функции на PL/Python могут обращаться к файловой системе сервера, сети и операционной системе, поэтому создавать функции на PL/Python может только суперпользователь СУБД.

#### 2. Пример функции (Function)

Функции в PostgreSQL возвращают значение. В PL/Python вы можете импортировать любые сторонние библиотеки Python (например, `json`, `math`, `requests`, `pandas` и др.).

Пример функции для парсинга и валидации JSON-строки с расчетом итоговой стоимости:

SQL

``` CREATE OR REPLACE FUNCTION calculate_cart_total(cart_json text) RETURNS numeric AS $$ import json try: data = json.loads(cart_json) items = data.get("items", []) total = 0.0 for item in items: price = item.get("price", 0) quantity = item.get("quantity", 1) total += price * quantity return round(total, 2) except Exception as e: # raise_notice или log позволяют отправлять сообщения в клиент/лог базы plpy.notice(f"Ошибка при обработке JSON: {e}") return 0.0 $$ LANGUAGE plpython3u; ```

**Вызов функции:**

SQL

``` SELECT calculate_cart_total('{"items": [{"price": 100.5, "quantity": 2}, {"price": 50.0, "quantity": 1}]}'); -- Вернет: 251.00 ```

#### 3. Пример процедуры (Procedure)

Процедуры в PostgreSQL не возвращают результат и поддерживают управление транзакциями (`plpy.commit()`, `plpy.rollback()`).

Специальный модуль `plpy` внутри PL/Python позволяет выполнять SQL-запросы внутри базы.

Пример процедуры, которая находит устаревшие записи, отправляет их во внешнюю систему (имитация) и удаляет их:

SQL

``` CREATE OR REPLACE PROCEDURE cleanup_old_logs(days_old int) AS $$ import time # Выполняем SQL-запрос к БД с помощью plpy.execute plan = plpy.prepare( "SELECT id, message FROM system_logs WHERE created_at < NOW() - ($1 || ' days')::INTERVAL", ["int"] ) result = plpy.execute(plan, [days_old]) plpy.info(f"Найдено записей для удаления: {len(result)}") if len(result) > 0: # Логика обработки записей на Python for row in result: log_id = row["id"] # Здесь можно вызвать внешний API, сохранить резервную копию и т.д. # Удаление записей delete_plan = plpy.prepare( "DELETE FROM system_logs WHERE created_at < NOW() - ($1 || ' days')::INTERVAL", ["int"] ) plpy.execute(delete_plan, [days_old]) # Фиксация изменений plpy.commit() $$ LANGUAGE plpython3u; ```

**Вызов процедуры:**

SQL

``` CALL cleanup_old_logs(30); ```

#### Дополнительные возможности `plpy`

Внутри PL/Python вам автоматически доступен встроенный объект `plpy`:

`plpy.execute("SELECT ...")` — выполнение произвольного SQL-запроса.

`plpy.prepare(query, [types])` — подготовка SQL-запроса для многократного выполнения.

`plpy.notice(msg)`, `plpy.info(msg)`, `plpy.warning(msg)` — вывод сообщений пользователю.

`plpy.error(msg)` — генерация исключения, отменяющего транзакцию.

### Возвращают ли функции PL/Python наборы строк (recordsets) которые можно использовать в SQL-запросах?

**Да, функции PL/Python могут возвращать наборы строк.** Их можно использовать непосредственно в блоке `FROM` SQL-запроса, как обычную таблицу или представление (View).

Возвращать наборы данных из PL/Python можно двумя основными способами:

**Список словарей / списков / кортежей** — если функция формирует и отдает все данные сразу.

**Итератор / Генератор (`yield`)** — если данных много и их нужно отдавать по одной строке (ленивые вычисления), чтобы не перегружать оперативную память.

#### Вариант 1. Возврат всех строк через список (List of Dicts)

Для возврата таблицы используется конструкция `RETURNS TABLE(...)` или `RETURNS SETOF record_type`. Внутри Python-кода вы возвращаете обычный список Python-словарей, где ключи соответствуют именам колонок.

SQL

``` CREATE OR REPLACE FUNCTION generate_user_report(min_age int) RETURNS TABLE(user_id int, full_name text, status text) AS $$ # В реальности данные могут запрашиваться из внешнего API или рассчитываться users = [ {"user_id": 1, "full_name": "Алексей Иванов", "age": 25}, {"user_id": 2, "full_name": "Мария Сидорова", "age": 19}, {"user_id": 3, "full_name": "Иван Петров", "age": 34} ] result = [] for user in users: if user["age"] >= min_age: result.append({ "user_id": user["user_id"], "full_name": user["full_name"], "status": "Adult" if user["age"] >= 21 else "Young" }) return result $$ LANGUAGE plpython3u; ```

##### Использование функции в SQL:

SQL

``` SELECT * FROM generate_user_report(20) WHERE status = 'Adult'; ```

#### Вариант 2. Построчная отдача через генератор (`yield`)

Если функция должна выгрузить 100 000+ строк из внешнего ресурса, сохранение их всех в список в памяти вызовет высокую нагрузку на RAM. В PL/Python можно использовать встроенный оператор Python `yield`.

SQL

``` CREATE OR REPLACE FUNCTION stream_numbers(limit_num int) RETURNS TABLE(num int, square int) AS $$ for i in range(1, limit_num + 1): # Отдаем по одной строке за раз yield {"num": i, "square": i * i} $$ LANGUAGE plpython3u; ```

##### Использование в SQL-запросе:

SQL

``` SELECT num, square FROM stream_numbers(5) WHERE square > 10; ```

**Результат:**

num	square
4	16
5	25

#### Вариант 3. Возврат результата встроенного SQL-запроса (`plpy.execute`)

Если функция выполняет внутренний SQL-запрос с помощью `plpy.execute()`, возвращаемый объект `PLPyResult` уже является итерируемым набором строк, поэтому его можно вернуть напрямую:

SQL

``` CREATE OR REPLACE FUNCTION get_active_users() RETURNS TABLE(id int, email text) AS $$ # plpy.execute возвращает результат, который совместим с SETOF/TABLE res = plpy.execute("SELECT id, email FROM users WHERE is_active = true") return res $$ LANGUAGE plpython3u; ```