VIEW, MATERIALIZED VIEW, функции, триггеры
Представления (VIEW) и функции — это мощные инструменты для абстракции, инкапсуляции бизнес-логики и упрощения работы с данными в PostgreSQL.
💡 Главная идея: «Не повторяй себя» (DRY). Сложная логика хранится в одном месте и переиспользуется во всём приложении.
VIEW (представление) — это виртуальная таблица, результат сохранённого SQL-запроса.
┌─────────────────────────────────────────────────────────────┐
│ ИСХОДНЫЕ ДАННЫЕ │
├─────────────────────────────────────────────────────────────┤
│ Таблица users (100 столбцов, 1 млн строк) │
│ ┌─────┬──────┬─────────────┬──────────┬─────────────┐ │
│ │ id │ name │ email │ password │ is_active │ ... │
│ └─────┴──────┴─────────────┴──────────┴─────────────┘ │
└─────────────────────────────────────────────────────────────┘
│
▼
CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE is_active = TRUE;
│
▼
┌─────────────────────────────────────────────────────────────┐
│ ПРЕДСТАВЛЕНИЕ │
├─────────────────────────────────────────────────────────────┤
│ VIEW active_users (3 столбца, только активные) │
│ ┌─────┬──────┬─────────────┐ │
│ │ id │ name │ email │ │
│ └─────┴──────┴─────────────┘ │
│ │
│ ⚠️ Данные НЕ хранятся — запрос выполняется каждый раз │
└─────────────────────────────────────────────────────────────┘
-- Простое представление
CREATE VIEW active_users AS
SELECT id, name, email, city, created_at
FROM users
WHERE is_active = TRUE;
-- Использование (как обычная таблица)
SELECT * FROM active_users WHERE city = 'Moscow';
SELECT name, email
FROM active_users
WHERE created_at >= '2026-01-01'
ORDER BY created_at DESC;┌─────────────────────────────────────────────────────────────┐
│ 1️⃣ УПРОЩЕНИЕ СЛОЖНЫХ ЗАПРОСОВ │
├─────────────────────────────────────────────────────────────┤
│ Было (30 строк SQL в каждом запросе): │
│ SELECT u.name, COUNT(o.id) AS orders, SUM(o.total) ... │
│ FROM users u JOIN orders o ON ... WHERE ... GROUP BY ... │
│ │
│ Стало (одна строка): │
│ SELECT * FROM user_order_stats WHERE city = 'Moscow'; │
└─────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────┐
│ 2️⃣ БЕЗОПАСНОСТЬ (скрытие чувствительных данных) │
├─────────────────────────────────────────────────────────────┤
│ Таблица users: id, name, email, password_hash, ssn, ... │
│ │
│ VIEW for_app: id, name, email │
│ VIEW for_admin: id, name, email, password_hash │
│ │
│ ✅ Приложение видит только нужные столбцы │
│ ✅ Пароли и персональные данные скрыты │
└─────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────┐
│ 3️⃣ ОБРАТНАЯ СОВМЕСТИМОСТЬ │
├─────────────────────────────────────────────────────────────┤
│ Было: users(name, email) │
│ Стало: users(first_name, last_name, email, phone) │
│ │
│ Создайте VIEW для совместимости: │
│ CREATE VIEW users_legacy AS │
│ SELECT first_name || ' ' || last_name AS name, email │
│ FROM users; │
│ │
│ ✅ Старое приложение продолжает работать │
└─────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────┐
│ 4️⃣ ЦЕНТРАЛИЗАЦИЯ ЛОГИКИ │
├─────────────────────────────────────────────────────────────┤
│ Логика в одном месте → легко изменять │
│ Все запросы используют VIEW → консистентность данных │
└─────────────────────────────────────────────────────────────┘
Простые VIEW можно обновлять напрямую:
-- Простая VIEW (обновляемая)
CREATE VIEW active_users AS
SELECT id, name, email, city
FROM users
WHERE is_active = TRUE;
-- ✅ Работает:
UPDATE active_users SET email = 'new@example.com' WHERE id = 1;
INSERT INTO active_users (name, email, city) VALUES ('John', 'j@test.com', 'Moscow');
DELETE FROM active_users WHERE id = 5;Сложные VIEW (с JOIN, GROUP BY, DISTINCT) НЕ обновляются автоматически:
-- Сложная VIEW (НЕ обновляемая напрямую)
CREATE VIEW user_order_stats AS
SELECT
u.id,
u.name,
COUNT(o.id) AS order_count,
SUM(o.total_amount) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;
-- ❌ ОШИБКА:
UPDATE user_order_stats SET order_count = 10 WHERE id = 1;
-- ERROR: cannot update view with JOIN or GROUP BYПозволяют обновлять сложные VIEW через триггеры:
-- Триггерная функция
CREATE OR REPLACE FUNCTION handle_user_order_stats_update()
RETURNS TRIGGER AS $$
BEGIN
-- Обновляем базовую таблицу users
UPDATE users SET name = NEW.name WHERE id = NEW.id;
-- Возвращаем NEW для продолжения
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Создаём INSTEAD OF триггер
CREATE TRIGGER update_user_order_stats_trigger
INSTEAD OF UPDATE ON user_order_stats
FOR EACH ROW
EXECUTE FUNCTION handle_user_order_stats_update();
-- Теперь работает:
UPDATE user_order_stats SET name = 'New Name' WHERE id = 1;-- Просмотр определения VIEW
SELECT definition FROM pg_views WHERE viewname = 'active_users';
-- Изменение VIEW (пересоздание)
CREATE OR REPLACE VIEW active_users AS
SELECT id, name, email, city, created_at, updated_at
FROM users
WHERE is_active = TRUE;
-- Удаление VIEW
DROP VIEW active_users;
-- Удаление с проверкой существования
DROP VIEW IF EXISTS active_users;
-- Переименование
ALTER VIEW active_users RENAME TO active_users_v1;MATERIALIZED VIEW — это «снимок» данных, который физически хранится на диске.
┌─────────────────────────────────────────────────────────────┐
│ VIEW │
├─────────────────────────────────────────────────────────────┤
│ ┌─────────────┐ ┌──────────────┐ ┌─────────────┐ │
│ │ SELECT * │────▶│ Выполнение │────▶│ Результат │ │
│ │ FROM view │ │ запроса │ │ каждый раз │ │
│ └─────────────┘ └──────────────┘ └─────────────┘ │
│ │
│ ✅ Всегда актуальные данные │
│ ❌ Медленно для сложных запросов │
└─────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────┐
│ MATERIALIZED VIEW │
├─────────────────────────────────────────────────────────────┤
│ ┌─────────────┐ ┌──────────────┐ ┌─────────────┐ │
│ │ REFRESH │────▶│ Выполнение │────▶│ Сохранение │ │
│ │ MATERIALIZED│ │ запроса │ │ на диск │ │
│ └─────────────┘ └──────────────┘ └─────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────┐ ┌──────────────┐ │
│ │ SELECT * │────▶│ Чтение из │ │
│ │ FROM matview│ │ памяти/диска │ │
│ └─────────────┘ └──────────────┘ │
│ │
│ ✅ Очень быстро (данные уже вычислены) │
│ ❌ Данные устаревают до REFRESH │
└─────────────────────────────────────────────────────────────┘
-- Создание материализованного представления
CREATE MATERIALIZED VIEW user_stats AS
SELECT
city,
COUNT(*) AS user_count,
AVG(age) AS avg_age,
MIN(age) AS min_age,
MAX(age) AS max_age
FROM users
GROUP BY city;
-- Использование (быстро!)
SELECT * FROM user_stats WHERE user_count > 100;
SELECT city, avg_age FROM user_stats ORDER BY avg_age DESC;-- Полное обновление (блокирует чтение)
REFRESH MATERIALIZED VIEW user_stats;
-- Обновление без блокировки чтения (требует уникальный индекс!)
CREATE UNIQUE INDEX idx_user_stats_city ON user_stats(city);
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;Без CONCURRENTLY:
┌─────────────────────────────────────────┐
│ Время 0:00 - Начало REFRESH │
│ ⛔ Чтение заблокировано │
│ Время 0:05 - REFRESH завершён │
│ ✅ Чтение разрешено │
└─────────────────────────────────────────┘
С CONCURRENTLY:
┌─────────────────────────────────────────┐
│ Время 0:00 - Начало REFRESH │
│ ✅ Чтение разрешено (старые данные) │
│ Время 0:05 - REFRESH завершён │
│ ✅ Мгновенное переключение на новые │
└─────────────────────────────────────────┘
| Сценарий | VIEW | MATERIALIZED VIEW |
|---|---|---|
| Данные меняются часто | ✅ | ❌ |
| Данные меняются редко | ❌ | ✅ |
| Сложные агрегации | ❌ | ✅ |
| Требуется актуальность | ✅ | ❌ |
| Важна скорость чтения | ❌ | ✅ |
| Много одновременных чтений | ❌ | ✅ |
-- Дашборд с метриками (обновляется раз в час)
CREATE MATERIALIZED VIEW dashboard_metrics AS
SELECT
DATE_TRUNC('hour', created_at) AS hour,
COUNT(*) AS orders_count,
SUM(total_amount) AS revenue,
AVG(total_amount) AS avg_order
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY DATE_TRUNC('hour', created_at);
-- Обновление по расписанию (через pg_cron или внешний планировщик)
-- 0 * * * * REFRESH MATERIALIZED VIEW CONCURRENTLY dashboard_metrics;
-- Отчёт по продажам (обновляется раз в день)
CREATE MATERIALIZED VIEW daily_sales_report AS
SELECT
DATE_TRUNC('day', order_date)::date AS date,
COUNT(*) AS orders,
SUM(total_amount) AS revenue,
COUNT(DISTINCT user_id) AS unique_customers
FROM orders
GROUP BY DATE_TRUNC('day', order_date)::date
ORDER BY date;-- Проверка времени последнего обновления
SELECT
schemaname,
matviewname,
pg_size_pretty(pg_relation_size(matviewname::regclass)) AS size
FROM pg_matviews
WHERE schemaname = 'public';
-- Удаление
DROP MATERIALIZED VIEW user_stats;
-- Переименование
ALTER MATERIALIZED VIEW user_stats RENAME TO user_stats_v1;
-- Изменение владельца
ALTER MATERIALIZED VIEW user_stats OWNER TO analytics;Функции — это именованные блоки кода, которые выполняют логику и возвращают результат.
CREATE OR REPLACE FUNCTION get_user_count()
RETURNS INTEGER AS $$
BEGIN
RETURN (SELECT COUNT(*) FROM users);
END;
$$ LANGUAGE plpgsql;
-- Вызов функции
SELECT get_user_count();Результат:
┌─────────────────┐
│ get_user_count │
├─────────────────┤
│ 1250 │
└─────────────────┘
CREATE OR REPLACE FUNCTION имя_функции(параметры)
RETURNS тип_возврата
LANGUAGE язык
AS $$
ОБЪЯВЛЕНИЕ_ПЕРЕМЕННЫХ
BEGIN
-- Основной код
RETURN результат;
END;
$$;-- Функция с одним параметром
CREATE OR REPLACE FUNCTION get_user_by_id(user_id INTEGER)
RETURNS TABLE(id INTEGER, name TEXT, email TEXT) AS $$
BEGIN
RETURN QUERY
SELECT u.id, u.name, u.email
FROM users u
WHERE u.id = user_id;
END;
$$ LANGUAGE plpgsql;
-- Вызов
SELECT * FROM get_user_by_id(1);Результат:
┌─────┬───────┬──────────────────┐
│ id │ name │ email │
├─────┼───────┼──────────────────┤
│ 1 │ Alice │ alice@test.com │
└─────┴───────┴──────────────────┘
CREATE OR REPLACE FUNCTION get_users_by_city(
city_name TEXT DEFAULT 'Moscow',
limit_count INTEGER DEFAULT 10
)
RETURNS TABLE(id INTEGER, name TEXT) AS $$
BEGIN
RETURN QUERY
SELECT u.id, u.name
FROM users u
WHERE u.city = city_name
LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;
-- Вызов с разными параметрами
SELECT * FROM get_users_by_city(); -- Moscow, 10
SELECT * FROM get_users_by_city('London'); -- London, 10
SELECT * FROM get_users_by_city('Paris', 5); -- Paris, 5
SELECT * FROM get_users_by_city(limit_count := 20); -- Moscow, 20-- IN (входной) — по умолчанию
CREATE FUNCTION add(a INTEGER, b INTEGER) RETURNS INTEGER AS $$
BEGIN
RETURN a + b;
END;
$$ LANGUAGE plpgsql;
-- OUT (выходной)
CREATE FUNCTION get_user_stats(
user_id INTEGER,
OUT order_count INTEGER,
OUT total_spent NUMERIC
) AS $$
BEGIN
SELECT COUNT(*), SUM(total_amount)
INTO order_count, total_spent
FROM orders WHERE user_id = user_id;
END;
$$ LANGUAGE plpgsql;
-- Вызов
SELECT * FROM get_user_stats(1);-- Возврат таблицы
CREATE OR REPLACE FUNCTION get_active_users()
RETURNS SETOF users AS $$
BEGIN
RETURN QUERY
SELECT * FROM users WHERE is_active = TRUE;
END;
$$ LANGUAGE plpgsql;
-- Вызов (как таблица)
SELECT * FROM get_active_users();
-- С фильтрацией
SELECT * FROM get_active_users() WHERE city = 'Moscow';CREATE OR REPLACE FUNCTION calculate_user_discount(user_id INTEGER)
RETURNS NUMERIC AS $$
DECLARE
total_spent NUMERIC;
discount_percent NUMERIC;
user_level TEXT;
BEGIN
-- Получаем общую сумму покупок
SELECT COALESCE(SUM(total_amount), 0)
INTO total_spent
FROM orders
WHERE user_id = user_id;
-- Определяем уровень и скидку
IF total_spent >= 100000 THEN
user_level := 'VIP';
discount_percent := 15.0;
ELSIF total_spent >= 50000 THEN
user_level := 'Gold';
discount_percent := 10.0;
ELSIF total_spent >= 10000 THEN
user_level := 'Silver';
discount_percent := 5.0;
ELSE
user_level := 'Basic';
discount_percent := 0.0;
END IF;
-- Логируем (для отладки)
RAISE NOTICE 'User %: level=%, spent=%, discount=%%',
user_id, user_level, total_spent, discount_percent;
RETURN discount_percent;
END;
$$ LANGUAGE plpgsql;
-- Вызов
SELECT calculate_user_discount(1);CREATE OR REPLACE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
RETURN a / b;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'Деление на ноль!';
RETURN NULL;
WHEN OTHERS THEN
RAISE NOTICE 'Произошла ошибка: %', SQLERRM;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- Вызов
SELECT safe_divide(10, 2); -- 5.0
SELECT safe_divide(10, 0); -- NULL + NOTICECREATE OR REPLACE FUNCTION generate_numbers(start_num INTEGER, end_num INTEGER)
RETURNS SETOF INTEGER AS $$
DECLARE
current_num INTEGER := start_num;
BEGIN
WHILE current_num <= end_num LOOP
RETURN NEXT current_num;
current_num := current_num + 1;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
-- Вызов
SELECT * FROM generate_numbers(1, 5);Результат:
┌─────────────────────┐
│ generate_numbers │
├─────────────────────┤
│ 1 │
│ 2 │
│ 3 │
│ 4 │
│ 5 │
└─────────────────────┘
CREATE OR REPLACE FUNCTION process_orders()
RETURNS INTEGER AS $$
DECLARE
order_record RECORD;
processed_count INTEGER := 0;
BEGIN
FOR order_record IN
SELECT id, total_amount FROM orders WHERE status = 'pending'
LOOP
-- Обрабатываем каждый заказ
UPDATE orders SET status = 'processed' WHERE id = order_record.id;
processed_count := processed_count + 1;
RAISE NOTICE 'Processed order %: %', order_record.id, order_record.total_amount;
END LOOP;
RETURN processed_count;
END;
$$ LANGUAGE plpgsql;
-- Вызов
SELECT process_orders();PostgreSQL поддерживает несколько языков для функций.
Простые функции на чистом SQL:
CREATE OR REPLACE FUNCTION get_active_users_sql()
RETURNS SETOF users AS $$
SELECT * FROM users WHERE is_active = TRUE;
$$ LANGUAGE SQL;
-- Вызов
SELECT * FROM get_active_users_sql();Преимущества:
Ограничения:
Процедурный язык PostgreSQL:
CREATE OR REPLACE FUNCTION complex_calculation(user_id INTEGER)
RETURNS NUMERIC AS $$
DECLARE
result NUMERIC;
BEGIN
-- Переменные, циклы, условия, исключения
SELECT COALESCE(SUM(total_amount), 0)
INTO result
FROM orders WHERE user_id = user_id;
IF result > 1000 THEN
result := result * 0.9; -- Скидка 10%
END IF;
RETURN result;
END;
$$ LANGUAGE plpgsql;Преимущества:
Ограничения:
Функции на Python:
-- Включить расширение
CREATE EXTENSION IF NOT EXISTS plpython3u;
-- Функция на Python
CREATE OR REPLACE FUNCTION add(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
return a + b
$$ LANGUAGE plpython3u;
-- Сложная логика
CREATE OR REPLACE FUNCTION calculate_hash(data TEXT)
RETURNS TEXT AS $$
import hashlib
return hashlib.sha256(data.encode()).hexdigest()
$$ LANGUAGE plpython3u;
-- Работа с данными
CREATE OR REPLACE FUNCTION process_json(data JSONB)
RETURNS JSONB AS $$
# Доступ к данным как к dict
result = {
'keys': list(data.keys()),
'count': len(data)
}
return result
$$ LANGUAGE plpython3u;Преимущества:
Ограничения:
CREATE EXTENSION IF NOT EXISTS plperl;
CREATE OR REPLACE FUNCTION regex_match(text TEXT, pattern TEXT)
RETURNS BOOLEAN AS $$
return $text =~ /$pattern/;
$$ LANGUAGE plperl;Максимальная производительность:
-- Компиляция как разделяемая библиотека
-- gcc -shared -fPIC -o myfunc.so myfunc.c
CREATE FUNCTION my_c_function(INTEGER) RETURNS INTEGER
AS '/path/to/myfunc.so', 'my_function'
LANGUAGE C STRICT;Преимущества:
Ограничения:
| Язык | Скорость | Гибкость | Сложность |
|---|---|---|---|
| SQL | ⚡⚡⚡ | ⭐ | Низкая |
| PL/pgSQL | ⚡⚡ | ⭐⭐⭐ | Средняя |
| PL/Python | ⚡ | ⭐⭐⭐⭐⭐ | Низкая |
| PL/Perl | ⚡ | ⭐⭐⭐⭐ | Средняя |
| C | ⚡⚡⚡⚡ | ⭐⭐ | Высокая |
Триггеры — это функции, которые автоматически выполняются при событиях (INSERT, UPDATE, DELETE).
┌─────────────────────────────────────────────────────────────┐
│ СОБЫТИЕ │
│ INSERT / UPDATE / DELETE / TRUNCATE │
└─────────────────────────────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────┐
│ ТРИГГЕР │
│ BEFORE / AFTER / INSTEAD OF │
│ FOR EACH ROW / FOR EACH STATEMENT │
└─────────────────────────────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────┐
│ ТРИГГЕРНАЯ ФУНКЦИЯ │
│ Доступ к NEW и OLD │
│ Возврат NEW / OLD / NULL │
└─────────────────────────────────────────────────────────────┘
-- Функция для автоматического updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Создание триггера
CREATE TRIGGER update_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();Запрос: UPDATE users SET name = 'New Name' WHERE id = 1;
┌─────────────────────────────────────────────────────────────┐
│ 1. BEFORE UPDATE триггеры │
│ └─> update_updated_at_column() │
│ NEW.updated_at = NOW() │
└─────────────────────────────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────┐
│ 2. Основное обновление │
│ UPDATE users SET name = 'New Name', updated_at = ... │
│ WHERE id = 1 │
└─────────────────────────────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────┐
│ 3. AFTER UPDATE триггеры │
│ └─> log_changes() │
│ INSERT INTO audit_log ... │
└─────────────────────────────────────────────────────────────┘
-- BEFORE INSERT (валидация, модификация данных)
CREATE TRIGGER before_insert_trigger
BEFORE INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION check_email_format();
-- BEFORE UPDATE (модификация перед записью)
CREATE TRIGGER before_update_trigger
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();
-- AFTER INSERT (логирование, уведомления)
CREATE TRIGGER after_insert_trigger
AFTER INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION send_notification();
-- AFTER UPDATE (аудит, синхронизация)
CREATE TRIGGER after_update_trigger
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION sync_to_cache();
-- AFTER DELETE (логирование, каскады)
CREATE TRIGGER after_delete_trigger
AFTER DELETE FROM orders
FOR EACH ROW
EXECUTE FUNCTION log_deletion();
-- INSTEAD OF (для VIEW)
CREATE TRIGGER instead_of_insert_trigger
INSTEAD OF INSERT ON user_orders_view
FOR EACH ROW
EXECUTE FUNCTION handle_view_insert();-- FOR EACH ROW (вызывается для каждой затронутой строки)
CREATE TRIGGER row_level_trigger
AFTER UPDATE ON users
FOR EACH ROW -- ← вызван 100 раз для 100 строк
EXECUTE FUNCTION log_change();
-- FOR EACH STATEMENT (вызывается один раз на оператор)
CREATE TRIGGER statement_level_trigger
AFTER UPDATE ON users
FOR EACH STATEMENT -- ← вызван 1 раз независимо от строк
EXECUTE FUNCTION log_statement();UPDATE users SET status = 'active'; -- 100 строк затронуто
FOR EACH ROW: FOR EACH STATEMENT:
┌─────────────────┐ ┌─────────────────┐
│ Вызов 1 │ │ Вызов 1 │
│ Вызов 2 │ │ (один раз) │
│ ... │ │ │
│ Вызов 100 │ │ │
│ Итого: 100 раз │ │ Итого: 1 раз │
└─────────────────┘ └─────────────────┘
CREATE OR REPLACE FUNCTION log_salary_changes()
RETURNS TRIGGER AS $$
BEGIN
-- OLD — старые данные (доступен в UPDATE, DELETE)
-- NEW — новые данные (доступен в INSERT, UPDATE)
-- OLD: id=1, salary=50000, name='Alice'
-- NEW: id=1, salary=60000, name='Alice'
IF TG_OP = 'UPDATE' THEN
IF OLD.salary != NEW.salary THEN
INSERT INTO salary_log (user_id, old_salary, new_salary, changed_at)
VALUES (NEW.id, OLD.salary, NEW.salary, NOW());
END IF;
END IF;
RETURN NEW; -- Важно для BEFORE триггеров!
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER salary_change_trigger
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_changes();-- Доступные переменные в триггерной функции:
-- NEW — новая строка (INSERT/UPDATE)
-- OLD — старая строка (UPDATE/DELETE)
-- TG_OP — операция ('INSERT', 'UPDATE', 'DELETE', 'TRUNCATE')
-- TG_TABLE_NAME — имя таблицы
-- TG_TABLE_SCHEMA — схема таблицы
-- TG_LEVEL — 'ROW' или 'STATEMENT'
-- TG_WHEN — 'BEFORE', 'AFTER', 'INSTEAD OF'
CREATE OR REPLACE FUNCTION universal_audit_trigger()
RETURNS TRIGGER AS $$
DECLARE
old_data JSONB;
new_data JSONB;
BEGIN
IF TG_OP = 'INSERT' THEN
new_data := to_jsonb(NEW);
INSERT INTO audit_log (table_name, operation, new_data, changed_at)
VALUES (TG_TABLE_NAME, TG_OP, new_data, NOW());
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
old_data := to_jsonb(OLD);
new_data := to_jsonb(NEW);
INSERT INTO audit_log (table_name, operation, old_data, new_data, changed_at)
VALUES (TG_TABLE_NAME, TG_OP, old_data, new_data, NOW());
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
old_data := to_jsonb(OLD);
INSERT INTO audit_log (table_name, operation, old_data, changed_at)
VALUES (TG_TABLE_NAME, TG_OP, old_data, NOW());
RETURN OLD;
ELSIF TG_OP = 'TRUNCATE' THEN
-- Для TRUNCATE нет NEW или OLD
INSERT INTO audit_log (table_name, operation, changed_at)
VALUES (TG_TABLE_NAME, TG_OP, NOW());
RETURN NULL;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;CREATE TRIGGER multi_event_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION universal_audit_trigger();-- Триггер только для определённых условий
CREATE TRIGGER active_user_trigger
AFTER INSERT ON users
FOR EACH ROW
WHEN (NEW.is_active = TRUE)
EXECUTE FUNCTION send_welcome_email();
-- Триггер только при изменении конкретного столбца
CREATE TRIGGER salary_changed_trigger
AFTER UPDATE ON employees
FOR EACH ROW
WHEN (OLD.salary IS DISTINCT FROM NEW.salary)
EXECUTE FUNCTION log_salary_change();-- Просмотр триггеров таблицы
SELECT
trigger_name,
event_manipulation,
event_object_table,
action_timing
FROM information_schema.triggers
WHERE event_object_table = 'users';
-- Отключение триггера
ALTER TABLE users DISABLE TRIGGER update_users_updated_at;
-- Отключение всех триггеров
ALTER TABLE users DISABLE TRIGGER ALL;
-- Включение триггера
ALTER TABLE users ENABLE TRIGGER update_users_updated_at;
-- Удаление триггера
DROP TRIGGER update_users_updated_at ON users;-- Таблица аудита
CREATE TABLE audit_log (
id SERIAL PRIMARY KEY,
table_name TEXT NOT NULL,
operation TEXT NOT NULL,
row_id INTEGER,
old_data JSONB,
new_data JSONB,
changed_by TEXT DEFAULT current_user,
changed_at TIMESTAMP DEFAULT NOW()
);
-- Универсальная триггерная функция
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO audit_log (table_name, operation, row_id, new_data)
VALUES (TG_TABLE_NAME, TG_OP, NEW.id, to_jsonb(NEW));
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO audit_log (table_name, operation, row_id, old_data, new_data)
VALUES (TG_TABLE_NAME, TG_OP, NEW.id, to_jsonb(OLD), to_jsonb(NEW));
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO audit_log (table_name, operation, row_id, old_data)
VALUES (TG_TABLE_NAME, TG_OP, OLD.id, to_jsonb(OLD));
RETURN OLD;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- Применение ко всем таблицам
CREATE TRIGGER audit_users_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();
CREATE TRIGGER audit_orders_trigger
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();
-- Запрос аудита
SELECT
table_name,
operation,
changed_at,
changed_by,
old_data->>'name' AS old_name,
new_data->>'name' AS new_name
FROM audit_log
WHERE table_name = 'users'
ORDER BY changed_at DESC
LIMIT 10;-- Таблица кэша
CREATE TABLE user_stats_cache (
user_id INTEGER PRIMARY KEY,
order_count INTEGER DEFAULT 0,
total_spent NUMERIC DEFAULT 0,
last_order_date TIMESTAMP,
updated_at TIMESTAMP DEFAULT NOW()
);
-- Функция обновления кэша
CREATE OR REPLACE FUNCTION update_user_stats_cache()
RETURNS TRIGGER AS $$
DECLARE
v_user_id INTEGER;
BEGIN
-- Определяем user_id из NEW или OLD
IF TG_OP = 'DELETE' THEN
v_user_id := OLD.user_id;
ELSE
v_user_id := NEW.user_id;
END IF;
-- Обновляем кэш
INSERT INTO user_stats_cache (user_id, order_count, total_spent, last_order_date, updated_at)
SELECT
user_id,
COUNT(*),
COALESCE(SUM(total_amount), 0),
MAX(order_date),
NOW()
FROM orders
WHERE user_id = v_user_id
ON CONFLICT (user_id) DO UPDATE SET
order_count = EXCLUDED.order_count,
total_spent = EXCLUDED.total_spent,
last_order_date = EXCLUDED.last_order_date,
updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Триггер
CREATE TRIGGER update_cache_trigger
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION update_user_stats_cache();
-- Быстрый запрос (без агрегации)
SELECT u.name, c.order_count, c.total_spent
FROM users u
JOIN user_stats_cache c ON u.id = c.user_id;-- Функция валидации email
CREATE OR REPLACE FUNCTION validate_email_format()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.email !~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN
RAISE EXCEPTION 'Некорректный email: %', NEW.email;
END IF;
IF LENGTH(NEW.email) > 255 THEN
RAISE EXCEPTION 'Email слишком длинный: % символов', LENGTH(NEW.email);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Триггер
CREATE TRIGGER validate_email_trigger
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION validate_email_format();
-- Использование:
-- ✅ INSERT INTO users (email) VALUES ('test@example.com');
-- ❌ INSERT INTO users (email) VALUES ('invalid-email'); -- Ошибка!-- Добавляем столбец deleted_at
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP;
-- Функция для мягкого удаления
CREATE OR REPLACE FUNCTION soft_delete_trigger()
RETURNS TRIGGER AS $$
BEGIN
-- Вместо удаления устанавливаем deleted_at
NEW.deleted_at := NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Триггер
CREATE TRIGGER soft_delete_trigger
BEFORE DELETE ON users
FOR EACH ROW EXECUTE FUNCTION soft_delete_trigger();
-- VIEW для активных пользователей
CREATE VIEW active_users AS
SELECT * FROM users WHERE deleted_at IS NULL;
-- Теперь DELETE фактически делает UPDATE:
-- DELETE FROM users WHERE id = 1; -- Установит deleted_at = NOW()
-- Восстановление удалённого
UPDATE users SET deleted_at = NULL WHERE id = 1;-- Функция ограничения
CREATE OR REPLACE FUNCTION limit_user_orders()
RETURNS TRIGGER AS $$
DECLARE
order_count INTEGER;
BEGIN
SELECT COUNT(*) INTO order_count
FROM orders WHERE user_id = NEW.user_id;
IF order_count >= 100 THEN
RAISE EXCEPTION 'Пользователь % не может иметь более 100 заказов', NEW.user_id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Триггер
CREATE TRIGGER limit_orders_trigger
BEFORE INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION limit_user_orders();-- Таблицы для партиций
CREATE TABLE orders_2025_q1 (CHECK (order_date >= '2025-01-01' AND order_date < '2025-04-01')) INHERITS (orders);
CREATE TABLE orders_2025_q2 (CHECK (order_date >= '2025-04-01' AND order_date < '2025-07-01')) INHERITS (orders);
-- Функция маршрутизации
CREATE OR REPLACE FUNCTION orders_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.order_date >= '2025-01-01' AND NEW.order_date < '2025-04-01' THEN
INSERT INTO orders_2025_q1 VALUES (NEW.*);
ELSIF NEW.order_date >= '2025-04-01' AND NEW.order_date < '2025-07-01' THEN
INSERT INTO orders_2025_q2 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Дата заказа вне диапазона партиций';
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- Триггер
CREATE TRIGGER orders_insert_trigger
BEFORE INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION orders_insert_trigger();-- 1. Используйте VIEW для упрощения сложных запросов
CREATE VIEW user_order_summary AS
SELECT u.id, u.name, COUNT(o.id) AS orders, SUM(o.total) AS total
FROM users u JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;
-- 2. Используйте MATERIALIZED VIEW для тяжёлых отчётов
CREATE MATERIALIZED VIEW daily_report AS
SELECT DATE_TRUNC('day', created_at)::date AS date, COUNT(*) AS orders
FROM orders GROUP BY 1;
-- 3. Обновляйте материализованные представления по расписанию
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_report;
-- 4. Используйте триггеры для аудита и автоматизации
CREATE TRIGGER audit_trigger AFTER UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION audit_function();
-- 5. Обрабатывайте исключения в функциях
CREATE FUNCTION safe_operation() RETURNS VOID AS $$
BEGIN
-- операция
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE 'Ошибка: %', SQLERRM;
END;
$$ LANGUAGE plpgsql;
-- 6. Документируйте функции
COMMENT ON FUNCTION get_user_discount IS 'Расчёт скидки для пользователя на основе истории заказов';-- 1. Не используйте триггеры для сложной бизнес-логики
-- ❌ Триггер отправляет email, вызывает API, пишет в очередь
-- ✅ Триггер должен быть быстрым и атомарным
-- 2. Не создавайте цепочки триггеров
-- ❌ Триггер A вызывает триггер B, который вызывает триггер C
-- ✅ Используйте одну функцию или хуки приложения
-- 3. Не используйте MATERIALIZED VIEW для часто меняющихся данных
-- ❌ CREATE MATERIALIZED VIEW real_time_stats AS SELECT ...
-- ✅ Используйте обычную VIEW или кэширование в приложении
-- 4. Не забывайте RETURN в триггерах
-- ❌ CREATE FUNCTION bad_trigger() RETURNS TRIGGER AS $$
-- BEGIN UPDATE ...; END; $$ -- Нет RETURN!
-- ✅ BEGIN UPDATE ...; RETURN NEW; END;
-- 5. Не используйте функции в WHERE без индексов
-- ❌ SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- ✅ CREATE INDEX idx_lower_email ON users(LOWER(email));□ VIEW упрощает запросы?
□ MATERIALIZED VIEW обновляется по расписанию?
□ Функции имеют обработку исключений?
□ Триггеры быстрые (< 10 мс)?
□ Триггеры не вызывают другие триггеры?
□ Есть аудит критических изменений?
□ Функции задокументированы (COMMENT)?
| Критерий | VIEW | MATERIALIZED VIEW |
|---|---|---|
| Хранение | Виртуально | На диске |
| Актуальность | Всегда | До REFRESH |
| Скорость чтения | Медленно | Быстро |
| Требует места | Нет | Да |
| Возврат | Синтаксис |
|---|---|
| Скаляр | RETURNS INTEGER |
| Таблица | RETURNS TABLE(col1 TYPE, col2 TYPE) |
| Множество строк | RETURNS SETOF table_name |
| Одна строка | RETURNS table_name%ROWTYPE |
| Событие | WHEN | Доступ |
|---|---|---|
| INSERT | BEFORE, AFTER | NEW |
| UPDATE | BEFORE, AFTER | NEW, OLD |
| DELETE | BEFORE, AFTER | OLD |
| TRUNCATE | AFTER | — |
Представления и функции — это фундамент для создания поддерживаемых и производительных приложений на PostgreSQL.
Запомните:
Следующий шаг: Проверьте знания в разделе вопросов по представлениям и функциям!
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.