Оптимизация SQL
Полезные скрипты
Использование PGAdmin
Сквозной пример в PostgreSQL
Видеомонтаж
Использование нейросетей
Интерактивные материалы
Офисные приложения

Как отлаживать функции в PostgreSQL с помощью RAISE NOTICE

Информация о материале
Автор: Андрей К.
Категория: Scripts
Просмотров: 976

Введение

Вы написали функцию в PostgreSQL. Она компилируется без ошибок. Вы запускаете её — и получаете результат. Но что-то не так. Средняя цена подозрительно высокая. Количество товаров — ноль, хотя вы точно знаете, что они есть. Или функция просто возвращает NULL, а вы теряетесь в догадках.

Где ошибка?

Многие разработчики начинают «гадать»: переписывать WHERE, менять JOIN, добавлять COALESCE наугад. Это долго и неэффективно.

Сегодня я покажу вам самый простой, встроенный и не требующий плагинов инструмент отладки в PostgreSQL — RAISE NOTICE.

Это ваш print() или console.log() в мире хранимых процедур. Мы разберем его на реальных данных нашего учебного магазина канцтоваров и электроники.

Что такое RAISE NOTICE?

RAISE NOTICE — это команда языка plpgsql, которая выводит сообщения в консоль клиента (например, вкладка «Messages» в DBeaver или pgAdmin) или в лог-файл сервера PostgreSQL.

Ключевые особенности:

  • Не прерывает выполнение: В отличие от RAISE EXCEPTION, функция продолжает работать дальше.

  • Подстановка значений: Поддерживает плейсхолдеры %, работающие аналогично printf в языке C или format в Python.

  • Автоматическое приведение типов: PostgreSQL сам преобразует числа, даты, булевы значения и даже массивы в читаемый текст.

Базовый синтаксис

sql
-- Простое сообщение
RAISE NOTICE 'Привет, мир!';

-- С подстановкой одного значения
RAISE NOTICE 'Цена товара: %', price;

-- С подстановкой нескольких значений
RAISE NOTICE 'Товар: %, Цена: %, Категория: %', name, price, category_name;

Просто, не правда ли? Но именно эта простота экономит часы отладки. Давайте посмотрим, как это работает на практике.

Пример 1: Базовая отладка функции с агрегацией

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

Где проблема? То ли в категории нет товаров, то ли все товары неактивны (is_active = false), то ли цены не заполнены?

Добавим RAISE NOTICE на каждом шаге.

Код функции

sql
CREATE OR REPLACE FUNCTION debug_avg_price(p_category_id INTEGER)
RETURNS NUMERIC AS $$
DECLARE
    v_avg_price NUMERIC;
    v_count INTEGER;
BEGIN
    -- Отладка: выводим входной параметр
    RAISE NOTICE '=== НАЧАЛО ФУНКЦИИ ===';
    RAISE NOTICE 'Начинаем анализ категории: %', p_category_id;

    -- Основной запрос
    SELECT COUNT(*), AVG(price)
    INTO v_count, v_avg_price 
    FROM products
    WHERE category = p_category_id AND is_active = true;

    -- Отладка: выводим промежуточные результаты
    RAISE NOTICE 'Найдено товаров в категории: %', v_count;
    RAISE NOTICE 'Рассчитанная средняя цена: %', v_avg_price;

    -- Отладка: выводим предупреждение, если товаров нет
    IF v_count = 0 THEN
        RAISE NOTICE 'ВНИМАНИЕ: В категории % нет активных товаров!', p_category_id;
    END IF;

    RAISE NOTICE '=== ЗАВЕРШЕНИЕ ФУНКЦИИ ===';
    RETURN v_avg_price;
END;
$$ LANGUAGE plpgsql;

Тестирование

Сценарий А: Категория со смартфонами (ID = 6)

sql
SELECT debug_avg_price(6);

Вывод в консоли (Messages):

text
NOTICE: === НАЧАЛО ФУНКЦИИ ===
NOTICE: Начинаем анализ категории: 6
NOTICE: Найдено товаров в категории: 3
NOTICE: Рассчитанная средняя цена: 65323.333333333333
NOTICE: === ЗАВЕРШЕНИЕ ФУНКЦИИ ===

Сценарий Б: Пустая категория «Телевизоры» (ID = 8)

sql
SELECT debug_avg_price(8);

Вывод в консоли:

text
NOTICE: === НАЧАЛО ФУНКЦИИ ===
NOTICE: Начинаем анализ категории: 8
NOTICE: Найдено товаров в категории: 0
NOTICE: Рассчитанная средняя цена: <NULL>
NOTICE: ВНИМАНИЕ: В категории 8 нет активных товаров!
NOTICE: === ЗАВЕРШЕНИЕ ФУНКЦИИ ===

Анализ:
Без единого изменения в бизнес-логике мы получили полную картину происходящего внутри функции. Мы видим, что запрос отработал, но вернул ноль строк. Это сразу указывает на проблему с данными, а не с синтаксисом.

Пример 2: Отладка внутри циклов

Ошибки особенно коварны, когда они прячутся внутри циклов. Предположим, функция должна массово обновить цены (например, применить скидку или наценку ко всем товарам категории). Если на пятом товаре цена становится отрицательной или происходит сбой — отследить это без логирования невозможно.

Добавим RAISE NOTICE на каждую итерацию.

Код функции с циклом

sql
CREATE OR REPLACE FUNCTION debug_loop_example(p_category_id INTEGER, p_multiplier NUMERIC)
RETURNS INTEGER AS $$
DECLARE
    v_product RECORD;
    v_counter INTEGER := 0;
    v_new_price NUMERIC;
BEGIN
    RAISE NOTICE '=== МАССОВОЕ ИЗМЕНЕНИЕ ЦЕН ===';
    RAISE NOTICE 'Категория ID: %, Множитель: %', p_category_id, p_multiplier;

    FOR v_product IN
        SELECT product_id, product_name, price
        FROM products 
        WHERE category = p_category_id AND is_active = true
    LOOP
        v_counter := v_counter + 1;
        v_new_price := ROUND(v_product.price * p_multiplier, 2);

        -- Отладка: выводим информацию по каждому товару
        RAISE NOTICE 'Товар #%: %, старая цена=%, новая цена=%',
            v_counter, v_product.product_name, v_product.price, v_new_price;

        -- Выполняем обновление
        UPDATE products 
        SET price = v_new_price, updated_at = CURRENT_TIMESTAMP
        WHERE product_id = v_product.product_id;

        -- Отладка: проверяем, что обновилось
        IF FOUND THEN
            RAISE NOTICE ' -> Обновление успешно для товара #%', v_counter;
        ELSE
            RAISE NOTICE ' -> ОШИБКА: товар #% не обновился!', v_counter;
        END IF;
    END LOOP;

    RAISE NOTICE '=== ИТОГО ОБРАБОТАНО ТОВАРОВ: % ===', v_counter;
    RETURN v_counter;
END;
$$ LANGUAGE plpgsql;

Тестирование

Поднимем цены в категории «Смартфоны» на 10%:

sql
SELECT debug_loop_example(6, 1.1);

Вывод в консоли (фрагмент):

text
NOTICE: === МАССОВОЕ ИЗМЕНЕНИЕ ЦЕН ===
NOTICE: Категория ID: 6, Множитель: 1.1
NOTICE: Товар #1: iPhone 15 Pro, старая цена=89990, новая цена=98989.00
NOTICE:  -> Обновление успешно для товара #1
NOTICE: Товар #2: Samsung Galaxy S24, старая цена=79990, новая цена=87989.00
NOTICE:  -> Обновление успешно для товара #2
NOTICE: Товар #3: Xiaomi Redmi Note 13, старая цена=25990, новая цена=28589.00
NOTICE:  -> Обновление успешно для товара #3
NOTICE: === ИТОГО ОБРАБОТАНО ТОВАРОВ: 3 ===

Польза:
Теперь мы видим каждую итерацию. Если бы на втором товаре цена вдруг стала NULL или отрицательной (например, из-за ошибки в формуле), мы бы сразу заметили это в логе, не ломая голову над итоговым отчетом.

Пример 3: Сравнение «ДО» и «ПОСЛЕ»

Один из самых эффективных приемов отладки — логировать состояние данных до выполнения операции и сразу после неё. Это особенно полезно при обновлении сложных полей, таких как массивы (TEXT[]) или JSON.

Код функции сравнения тегов

sql
CREATE OR REPLACE FUNCTION debug_compare_before_after(p_product_id INTEGER, p_new_tags TEXT[])
RETURNS BOOLEAN AS $$
DECLARE
    v_old_tags TEXT[];
    v_new_tags TEXT[];
    v_product_name TEXT;
BEGIN
    -- Получаем текущее состояние
    SELECT product_name, tags INTO v_product_name, v_old_tags
    FROM products WHERE product_id = p_product_id;

    RAISE NOTICE '=== ОБНОВЛЕНИЕ ТЕГОВ ===';
    RAISE NOTICE 'Товар: % (ID: %)', v_product_name, p_product_id;
    RAISE NOTICE 'ТЕГИ ДО: %', v_old_tags;
    RAISE NOTICE 'ТЕГИ ПОСЛЕ (планируемые): %', p_new_tags;

    -- Выполняем обновление
    UPDATE products
    SET tags = p_new_tags, updated_at = CURRENT_TIMESTAMP
    WHERE product_id = p_product_id;

    -- Проверяем результат
    SELECT tags INTO v_new_tags
    FROM products WHERE product_id = p_product_id;

    RAISE NOTICE 'ТЕГИ ПОСЛЕ (фактические): %', v_new_tags;

    -- Сравниваем
    IF v_new_tags = p_new_tags THEN
        RAISE NOTICE '✅ Обновление успешно: теги совпадают с ожидаемыми';
        RETURN TRUE;
    ELSE
        RAISE NOTICE '❌ ОШИБКА: теги не совпадают! Ожидали: %, Получили: %', p_new_tags, v_new_tags;
        RETURN FALSE;
    END IF;
END;
$$ LANGUAGE plpgsql;

Тестирование

Обновим теги для iPhone 15 Pro:

sql
SELECT debug_compare_before_after(1, ARRAY['смартфон', 'apple', 'флагман', 'хит']);

Вывод в консоли:

text
NOTICE: === ОБНОВЛЕНИЕ ТЕГОВ ===
NOTICE: Товар: iPhone 15 Pro (ID: 1)
NOTICE: ТЕГИ ДО: {смартфон,apple,флагман,популярный}
NOTICE: ТЕГИ ПОСЛЕ (планируемые): {смартфон,apple,флагман,хит}
NOTICE: ТЕГИ ПОСЛЕ (фактические): {смартфон,apple,флагман,хит}
NOTICE: ✅ Обновление успешно: теги совпадают с ожидаемыми

Три золотых правила отладки с RAISE NOTICE

Запомните эти простые правила, и любая функция перестанет быть «черным ящиком»:

  1. На входе: Всегда выводите входящие параметры. Это помогает понять, с какими данными функция была вызвана на самом деле.

    sql
    RAISE NOTICE 'Вызов функции my_func с параметрами: p1=%, p2=%', p1, p2;

  2. После важных запросов: Выводите количество найденных/измененных строк (ROW_COUNT, FOUND) или ключевые агрегаты (v_avg, v_sum).

    sql
    GET DIAGNOSTICS v_row_count = ROW_COUNT;
RAISE NOTICE 'Запрос затронул строк: %', v_row_count;

  3. Внутри циклов: Выводите номер итерации и значения переменных, которые меняются. Это спасет вас при обработке 10 000 записей, если ошибка возникает только на 342-й строке.

Заключение

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

В следующий раз, когда ваша функция поведет себя странно, не гадайте — просто добавьте RAISE NOTICE.

 

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

Поиск по материалам сайта
RAISE NOTICE в PostgreSQL

Освойте RAISE NOTICE — встроенный инструмент отладки

БОЛЬШЕ СМОТРЕТЬ ТУТ

 Picture background