Временные таблицы и функции в PostgreSQL: Упаковываем сложную логику в многоразовые конструкции

Информация о материале: Автор: Андрей К.; Категория: Scripts; Опубликовано: 09 апреля 2026; Просмотров: 428

Введение

Вы уже освоили GROUP BY, оконные функции и сложные JOIN. Ваши запросы стали мощными, но... громоздкими. Огромные простыни SQL-кода с вложенными подзапросами становятся нечитаемыми, а при повторном использовании приходится копировать десятки строк.

Знакомая ситуация? Тогда пришло время познакомиться с временными таблицами и научиться упаковывать бизнес-логику в функции PostgreSQL.

Сегодня на примере всё той же базы интернет-магазина (products и categories) мы разберем:

Как создавать временные таблицы и зачем они нужны.
Как использовать их внутри пользовательских функций.
Как анализировать массивы тегов через две временные таблицы.
Как ускорять работу через индексы.
Как строить иерархические отчеты с рекурсией.

Часть 1. Зачем нужны временные таблицы?

Представьте задачу: нужно собрать отчет по «витрине» — дорогие товары из электроники, но без учета самых дешевых позиций в каждой категории.

Обычный запрос может легко разрастись на 20-30 строк с вложенными WITH, двойными фильтрациями и самоприсоединениями. Читать и отлаживать такое — боль.

Временная таблица (CREATE TEMP TABLE) позволяет разбить задачу на два простых шага:

Сначала положить промежуточный результат в «песочницу».
Потом работать с ней как с обычной таблицей.

Пример 1: Создаем витрину дорогой электроники

-- Шаг 1. Создаём временную таблицу с дорогой электроникой
CREATE TEMP TABLE temp_expensive_electronics AS
SELECT 
    p.product_name, 
    p.price, 
    c.categori_name
FROM products p
JOIN categories c ON p.category = c.categori_id
WHERE c.categori_name IN ('Смартфоны', 'Ноутбуки')
  AND p.price > 50000;

-- Шаг 2. Используем её для аналитики
SELECT
    LEFT(product_name, 20) as short_name,
    price,
    CASE
        WHEN price > 100000 THEN 'Флагман'
        ELSE 'Средний+'
    END as segment
FROM temp_expensive_electronics
ORDER BY price DESC;

Что важно знать о CREATE TEMP TABLE:

Область видимости: Таблица существует только в рамках вашей текущей сессии. Другие пользователи или даже вы сами из другого окна редактора её не увидят.
Время жизни: Автоматически удаляется при отключении от базы данных.
Удобство отладки: Если запрос упал с ошибкой, данные во временной таблице остаются. Вы можете спокойно сделать SELECT * FROM temp_... и посмотреть, на каком этапе данные «поломались».

Часть 2. Временные таблицы внутри функций

Теперь самое интересное — объединим мощь временных таблиц и пользовательских функций. Это позволяет создавать настоящие программные модули прямо в SQL.

Задача: найти ценовые аномалии — товары, которые отличаются от средней цены по своей категории более чем на 30%.

Пример 2: Функция поиска аномалий

Здесь мы не будем дважды сканировать таблицу products. Мы создадим временную таблицу, внутри которой оконной функцией сразу посчитаем среднее, а затем просто отфильтруем её.

CREATE OR REPLACE FUNCTION get_price_anomalies(p_category_id INTEGER)
RETURNS TABLE(
    product_name VARCHAR(255),
    price NUMERIC,
    avg_price NUMERIC,
    deviation_percent NUMERIC
) AS $$
BEGIN
    -- Временная таблица со статистикой
    CREATE TEMP TABLE temp_products_with_stats AS
    SELECT
        p.product_name::VARCHAR(255) as product_name,
        p.price,
        AVG(p.price) OVER (PARTITION BY p.category) as avg_price
    FROM products p
    WHERE p.category = p_category_id
      AND p.is_active = true;

    -- Возвращаем аномалии
    RETURN QUERY
    SELECT
        t.product_name,
        t.price,
        ROUND(t.avg_price, 2),
        ROUND(ABS(t.price - t.avg_price) / t.avg_price * 100, 1)
    FROM temp_products_with_stats t
    WHERE ABS(t.price - t.avg_price) > t.avg_price * 0.3;

    -- Чистим за собой (хороший тон)
    DROP TABLE temp_products_with_stats;
END;
$$ LANGUAGE plpgsql;

-- Вызываем функцию для категории "Смартфоны" (ID = 6)
SELECT * FROM get_price_anomalies(6);

Ключевые моменты кода:

RETURNS TABLE: Позволяет функции возвращать набор строк, к которому можно применить SELECT * FROM.
OVER (PARTITION BY...): Мы использовали оконную функцию внутри создания временной таблицы. Это гениальный ход: средняя цена считается на лету, без дополнительного сканирования диска.
Явное приведение типов (::VARCHAR(255)): Важный нюанс для тех, кто использует строгую типизацию. Без этого PostgreSQL может ругаться на несоответствие TEXT и VARCHAR.
DROP TABLE в конце: Хотя таблица и так умрет после завершения сессии или транзакции, внутри функций, которые вызываются часто, лучше чистить мусор вручную, чтобы не забивать системные каталоги pg_temp.

Часть 3. Высший пилотаж: Две временные таблицы и массивы

У нас в таблице products есть поле tags типа TEXT[] (массив). Это прекрасный способ хранить характеристики, но анализировать массивы напрямую сложно.

Задача: Написать функцию, которая принимает два артикула (sku1 и sku2) и возвращает:

Количество общих тегов.
Уникальные теги первого товара.
Уникальные теги второго товара.

Пример 3: Сравнение тегов через нормализацию

Здесь мы создадим две временные таблицы, «развернув» массивы в строки с помощью unnest.

CREATE OR REPLACE FUNCTION compare_tag_sets(sku1 TEXT, sku2 TEXT)
RETURNS TABLE(
    common_tags BIGINT,
    unique_tags_1 TEXT[],
    unique_tags_2 TEXT[]
) AS $$
DECLARE
    tags1 TEXT[];
    tags2 TEXT[];
BEGIN
    -- Получаем массивы тегов
    SELECT tags INTO tags1 FROM products WHERE sku = sku1;
    SELECT tags INTO tags2 FROM products WHERE sku = sku2;

    -- Создаем две независимые временные таблицы
    CREATE TEMP TABLE temp_tags1 AS SELECT unnest(tags1) as tag;
    CREATE TEMP TABLE temp_tags2 AS SELECT unnest(tags2) as tag;

    -- Считаем метрики
    RETURN QUERY
    SELECT
        (SELECT COUNT(*) FROM temp_tags1 t1 INNER JOIN temp_tags2 t2 ON t1.tag = t2.tag),
        ARRAY(SELECT tag FROM temp_tags1 EXCEPT SELECT tag FROM temp_tags2),
        ARRAY(SELECT tag FROM temp_tags2 EXCEPT SELECT tag FROM temp_tags1);

    DROP TABLE temp_tags1, temp_tags2;
END;
$$ LANGUAGE plpgsql;

-- Сравниваем iPhone 15 Pro и Samsung Galaxy S24
SELECT * FROM compare_tag_sets('IPH15PRO256', 'SGS24U512');

Что мы получили?
Мы превратили сложную задачу по работе с массивами в элементарные операции теории множеств (INNER JOIN и EXCEPT) над обычными таблицами.

Практическое применение: Такую функцию можно легко встроить в блок рекомендаций на сайте: «Товар X имеет 3 общих тега с товаром Y. Возможно, вас заинтересует?».

Часть 4. Производительность: Не забываем про индексы!

Существует опасное заблуждение: «Временная таблица маленькая, индексы ей не нужны». Это правда только если в ней 10 строк. Но что если в категории «Книги» 10 000 товаров, и мы кладем их во временную таблицу, а потом несколько раз фильтруем по цене?

По умолчанию временные таблицы не наследуют индексы исходных таблиц. Без индекса сканирование будет последовательным (Seq Scan).

Пример 4: Ускоряем работу явным индексом

CREATE OR REPLACE FUNCTION fast_category_stats(category_id INTEGER)
RETURNS TABLE(price_segment TEXT, count INTEGER, avg_price NUMERIC) AS $$
BEGIN
    CREATE TEMP TABLE temp_filtered_products AS
    SELECT product_id, price, product_name
    FROM products
    WHERE category = category_id AND is_active = true;

    -- ВОТ ЭТО ВАЖНО! Создаём индекс для ускорения WHERE и GROUP BY
    CREATE INDEX idx_temp_price ON temp_filtered_products(price);

    RETURN QUERY
    SELECT
        CASE
            WHEN price < 10000 THEN 'Бюджет'
            WHEN price < 50000 THEN 'Средний'
            ELSE 'Премиум'
        END,
        COUNT(*),
        ROUND(AVG(price), 2)
    FROM temp_filtered_products
    GROUP BY 1
    ORDER BY MIN(price);

    DROP TABLE temp_filtered_products;
END;
$$ LANGUAGE plpgsql;

Совет: Если вы используете временную таблицу для JOIN с другой таблицей или для сложной фильтрации (WHERE price > 1000), всегда оценивайте объем данных. Для 500+ строк индекс на колонках фильтрации даст прирост скорости в 5-10 раз.

Часть 5. Финальный аккорд: Рекурсия + Временная таблица

Соберем все знания вместе и решим реальную бизнес-задачу. Категории у нас иерархические (Электроника -> Смартфоны -> ...). Нужно построить отчет по всей ветке каталога: сколько товаров и какова их суммарная стоимость на каждом уровне вложенности.

Пример 5: Отчет по иерархии

CREATE OR REPLACE FUNCTION get_hierarchy_report(root_category_id INTEGER)
RETURNS TABLE(
    category_name TEXT, 
    level INT,
    products_count BIGINT, 
    total_value NUMERIC
) AS $$
BEGIN
    -- Шаг 1. Строим дерево через рекурсивный CTE и сохраняем во временную таблицу
    CREATE TEMP TABLE temp_category_tree AS
    WITH RECURSIVE cat_tree AS (
        SELECT
            c.categori_id, c.categori_name, c.level, c.parent_id
        FROM categories c
        WHERE c.categori_id = root_category_id
        
        UNION ALL
        
        SELECT
            c.categori_id,
            c.categori_name,
            c.level,
            c.parent_id
        FROM categories c
        INNER JOIN cat_tree ct ON c.parent_id = ct.categori_id
    )
    SELECT * FROM cat_tree;

    -- Шаг 2. Джойним временную таблицу с товарами и считаем агрегаты
    RETURN QUERY
    SELECT
        ct.categori_name::TEXT,
        ct.level,
        COUNT(p.product_id)::BIGINT,
        COALESCE(SUM(p.price), 0)::NUMERIC
    FROM temp_category_tree ct
    LEFT JOIN products p ON p.category = ct.categori_id
    GROUP BY ct.categori_name, ct.level
    ORDER BY ct.level, ct.categori_name;

    DROP TABLE temp_category_tree;
END;
$$ LANGUAGE plpgsql;

-- Получаем весь отчет по электронике (ID = 1)
SELECT * FROM get_hierarchy_report(1);

Разбор решения:

WITH RECURSIVE: Строит дерево от корня (root_category_id) до последнего листа.
CREATE TEMP TABLE ... AS: Мы сохраняем результат рекурсии. Это позволяет не повторять тяжелый рекурсивный обход при каждом обращении к данным.
LEFT JOIN: Присоединяем товары. LEFT гарантирует, что мы увидим папку «Телевизоры», даже если там пусто.

На выходе — готовая сводка по всему дереву категорий одним вызовом функции.

Заключение

Временные таблицы в PostgreSQL — это не просто «костыль для новичков». Это мощный инструмент архитектуры данных, который позволяет:

Упрощать код: Убивать многоэтажные вложенные запросы.
Ускорять работу: За счет материализации промежуточных итогов и добавления индексов.
Инкапсулировать логику: Создавать чистые функции, которые удобно переиспользовать в отчетах, триггерах и ETL-процессах.

Сегодня мы научились создавать временные таблицы, использовать их в функциях для анализа аномалий, сравнивать массивы тегов, ускорять код индексами и строить иерархические отчеты.

Статья подготовлена по материалам обучающего видео для вашего удобства.

Оптимизация SQL

Полезные скрипты

Использование PGAdmin

Сквозной пример в PostgreSQL

Видеомонтаж

Использование нейросетей

Интерактивные материалы

Офисные приложения