Урок 8. Функции и триггеры в PostgreSQL: Автоматизация и расширение возможностей вашей базы данных

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

Создание пользовательских функций

Функции в PostgreSQL — это блоки кода, которые выполняют определенные действия и могут возвращать результат. Давайте создадим простую функцию, которая будет рассчитывать общую стоимость заказа:

CREATE OR REPLACE FUNCTION calculate_total_price(order_id INTEGER)
RETURNS DECIMAL AS $
DECLARE
    total DECIMAL;
BEGIN
    SELECT SUM(price * quantity)
    INTO total
    FROM order_items
    WHERE order_id = calculate_total_price.order_id;
    
    RETURN COALESCE(total, 0);
END;
$ LANGUAGE plpgsql;

Эта функция принимает ID заказа, суммирует стоимость всех товаров в заказе и возвращает общую сумму.

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

SELECT calculate_total_price(1);

Функции с табличным возвращаемым значением

PostgreSQL позволяет создавать функции, которые возвращают целые таблицы. Вот пример функции, которая возвращает все товары в заказе:

CREATE OR REPLACE FUNCTION get_order_items(order_id INTEGER)
RETURNS TABLE (
    item_name VARCHAR,
    quantity INTEGER,
    price DECIMAL
) AS $
BEGIN
    RETURN QUERY
    SELECT products.name, order_items.quantity, order_items.price
    FROM order_items
    JOIN products ON order_items.product_id = products.id
    WHERE order_items.order_id = get_order_items.order_id;
END;
$ LANGUAGE plpgsql;

Использование функции с табличным возвращаемым значением:

SELECT * FROM get_order_items(1);

Введение в триггеры

Триггеры — это специальные функции, которые автоматически выполняются при определенных событиях в базе данных (например, при вставке, обновлении или удалении данных).

Давайте создадим триггер, который будет автоматически обновлять статус заказа на «Выполнен», когда все товары в заказе будут отмечены как отправленные:

CREATE OR REPLACE FUNCTION update_order_status()
RETURNS TRIGGER AS $
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM order_items
        WHERE order_id = NEW.order_id AND shipped = FALSE
    ) THEN
        UPDATE orders
        SET status = 'Completed'
        WHERE id = NEW.order_id;
    END IF;
    RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER order_item_shipped
AFTER UPDATE OF shipped ON order_items
FOR EACH ROW
WHEN (NEW.shipped = TRUE)
EXECUTE FUNCTION update_order_status();

Этот триггер будет срабатывать каждый раз, когда статус отправки товара в заказе меняется на TRUE. Если все товары в заказе отправлены, статус заказа автоматически изменится на «Выполнен».

Примеры использования функций и триггеров

1. Автоматическое обновление временных меток:

CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $
BEGIN
    NEW.modified_at = NOW();
    RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER update_customer_modtime
BEFORE UPDATE ON customers
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();

2. Ведение журнала изменений:

CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(50),
    action VARCHAR(10),
    user_id INTEGER,
    changed_at TIMESTAMP DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER AS $
BEGIN
    INSERT INTO audit_log (table_name, action, user_id)
    VALUES (TG_TABLE_NAME, TG_OP, current_user);
    RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER log_customer_changes
AFTER INSERT OR UPDATE OR DELETE ON customers
FOR EACH ROW
EXECUTE FUNCTION log_changes();

Функции и триггеры в PostgreSQL предоставляют мощные инструменты для автоматизации процессов и расширения функциональности вашей базы данных. Они позволяют реализовать сложную бизнес-логику непосредственно на уровне базы данных, что часто приводит к повышению производительности и улучшению целостности данных. Однако важно использовать их с осторожностью, так как чрезмерное использование триггеров может усложнить понимание и отладку вашей системы.

Домашнее задание:

1. Создайте функцию, которая возвращает среднюю стоимость товаров в заказе.
2. Напишите триггер, который не позволяет удалять записи из таблицы клиентов, а вместо этого помечает их как неактивные.
3. Создайте функцию, которая возвращает топ-5 самых продаваемых товаров за последний месяц.

Удачи в изучении функций и триггеров в PostgreSQL!

Чтобы освоить работу с JSON в PostgreSQL, ознакомьтесь с уроком 9: Мастерство JSON в PostgreSQL.