— 创建测试表
CREATE TABLE fgedu_products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price NUMERIC(10,2) NOT NULL,
stock INTEGER NOT NULL,
active BOOLEAN DEFAULT TRUE
);

— 输出结果
CREATE TABLE

— 插入测试数据
INSERT INTO fgedu_products(name, price, stock, active)
VALUES
(‘iPhone 15’, 7999.00, 100, TRUE),
(‘iPad Air’, 4999.00, 50, TRUE),
(‘MacBook Pro’, 14999.00, 30, FALSE);

— 输出结果
INSERT 0 3

— 创建视图
CREATE VIEW fgedu_active_products AS
SELECT id, name, price, stock
FROM fgedu_products
WHERE active = TRUE;

— 输出结果
CREATE VIEW

— 创建规则实现视图更新
CREATE OR REPLACE RULE rl_active_products_insert AS
ON INSERT TO fgedu_active_products
DO INSTEAD
INSERT INTO fgedu_products(name, price, stock, active)
VALUES(NEW.name, NEW.price, NEW.stock, TRUE);

— 输出结果
CREATE RULE

CREATE OR REPLACE RULE rl_active_products_update AS
ON UPDATE TO fgedu_active_products
DO INSTEAD
UPDATE fgedu_products
SET name = NEW.name, price = NEW.price, stock = NEW.stock
WHERE id = OLD.id AND active = TRUE;

— 输出结果
CREATE RULE

CREATE OR REPLACE RULE rl_active_products_delete AS
ON DELETE TO fgedu_active_products
DO INSTEAD
UPDATE fgedu_products SET active = FALSE WHERE id = OLD.id;

— 输出结果
CREATE RULE

— 测试规则
INSERT INTO fgedu_active_products(name, price, stock)
VALUES(‘AirPods Pro’, 1999.00, 200);

— 输出结果
INSERT 0 1

— 查看数据
SELECT * FROM fgedu_products;

— 输出结果
id | name | price | stock | active
—-+———–+——–+——-+——–
1 | iPhone 15 | 7999.00| 100 | t
2 | iPad Air | 4999.00| 50 | t
3 | MacBook Pro|14999.00| 30 | f
4 | AirPods Pro|1999.00| 200 | t
(4 rows)

— 测试更新
UPDATE fgedu_active_products SET price = 2199.00 WHERE id = 4;

— 输出结果
UPDATE 1

— 查看更新结果
SELECT * FROM fgedu_products WHERE id = 4;

— 输出结果
id | name | price | stock | active
—-+———–+——–+——-+——–
4 | AirPods Pro|2199.00| 200 | t
(1 row)

— 测试删除
DELETE FROM fgedu_active_products WHERE id = 4;

— 输出结果
DELETE 1

— 查看删除结果
SELECT * FROM fgedu_products WHERE id = 4;

— 输出结果
id | name | price | stock | active
—-+———–+——–+——-+——–
4 | AirPods Pro|2199.00| 200 | f
(1 row)

— 创建审计表
CREATE TABLE fgedu_product_audit (
id BIGSERIAL PRIMARY KEY,
product_id INTEGER NOT NULL,
operation VARCHAR(20) NOT NULL,
old_data JSONB,
new_data JSONB,
operation_time TIMESTAMP DEFAULT NOW(),
user_name VARCHAR(100) DEFAULT CURRENT_USER
);

— 输出结果
CREATE TABLE

— 创建触发器函数
CREATE OR REPLACE FUNCTION fgedu_product_audit()
RETURNS TRIGGER
AS $$
BEGIN
IF TG_OP = ‘INSERT’ THEN
INSERT INTO fgedu_product_audit(
product_id,
operation,
new_data
)
VALUES(
NEW.id,
‘INSERT’,
to_jsonb(NEW)
);
RETURN NEW;
ELSIF TG_OP = ‘UPDATE’ THEN
INSERT INTO fgedu_product_audit(
product_id,
operation,
old_data,
new_data
)
VALUES(
NEW.id,
‘UPDATE’,
to_jsonb(OLD),
to_jsonb(NEW)
);
RETURN NEW;
ELSIF TG_OP = ‘DELETE’ THEN
INSERT INTO fgedu_product_audit(
product_id,
operation,
old_data
)
VALUES(
OLD.id,
‘DELETE’,
to_jsonb(OLD)
);
RETURN OLD;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 创建触发器
CREATE TRIGGER trg_product_audit
AFTER INSERT OR UPDATE OR DELETE ON fgedu_products
FOR EACH ROW
EXECUTE FUNCTION fgedu_product_audit();

— 输出结果
CREATE TRIGGER

— 测试触发器
INSERT INTO fgedu_products(name, price, stock)
VALUES(‘Apple Watch’, 2999.00, 150);

— 输出结果
INSERT 0 1

— 查看审计日志
SELECT product_id, operation, operation_time, user_name
FROM fgedu_product_audit
ORDER BY operation_time DESC
LIMIT 5;

— 输出结果
product_id | operation | operation_time | user_name
————+———–+————————+———-
5 | INSERT | 2026-04-07 21:30:00 | fgedu
(1 row)

— 测试更新
UPDATE fgedu_products SET price = 3199.00 WHERE id = 5;

— 输出结果
UPDATE 1

— 查看审计日志
SELECT product_id, operation, operation_time, user_name
FROM fgedu_product_audit
ORDER BY operation_time DESC
LIMIT 5;

— 输出结果
product_id | operation | operation_time | user_name
————+———–+————————+———-
5 | UPDATE | 2026-04-07 21:35:00 | fgedu
5 | INSERT | 2026-04-07 21:30:00 | fgedu
(2 rows)

— 测试删除
DELETE FROM fgedu_products WHERE id = 5;

— 输出结果
DELETE 1

— 查看审计日志
SELECT product_id, operation, operation_time, user_name
FROM fgedu_product_audit
ORDER BY operation_time DESC
LIMIT 5;

— 输出结果
product_id | operation | operation_time | user_name
————+———–+————————+———-
5 | DELETE | 2026-04-07 21:40:00 | fgedu
5 | UPDATE | 2026-04-07 21:35:00 | fgedu
5 | INSERT | 2026-04-07 21:30:00 | fgedu
(3 rows)

— 创建销售表
CREATE TABLE fgedu_sales (
id SERIAL PRIMARY KEY,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL,
price NUMERIC(10,2) NOT NULL,
sale_date DATE DEFAULT CURRENT_DATE
);

— 输出结果
CREATE TABLE

— 创建销售视图（使用规则）
CREATE VIEW fgedu_sales_view AS
SELECT s.id, p.name AS product_name, s.quantity, s.price, s.sale_date
FROM fgedu_sales s
JOIN fgedu_products p ON s.product_id = p.id
WHERE p.active = TRUE;

— 输出结果
CREATE VIEW

— 创建规则实现视图插入
CREATE OR REPLACE RULE rl_sales_insert AS
ON INSERT TO fgedu_sales_view
DO INSTEAD (
SELECT id INTO STRICT NEW.product_id FROM fgedu_products WHERE name = NEW.product_name AND active = TRUE;
INSERT INTO fgedu_sales(product_id, quantity, price, sale_date)
VALUES(NEW.product_id, NEW.quantity, NEW.price, NEW.sale_date);
);

— 输出结果
CREATE RULE

— 创建触发器实现库存更新
CREATE OR REPLACE FUNCTION fgedu_update_stock()
RETURNS TRIGGER
AS $$
BEGIN
UPDATE fgedu_products
SET stock = stock – NEW.quantity
WHERE id = NEW.product_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 创建触发器
CREATE TRIGGER trg_update_stock
AFTER INSERT ON fgedu_sales
FOR EACH ROW
EXECUTE FUNCTION fgedu_update_stock();

— 输出结果
CREATE TRIGGER

— 测试混合方案
INSERT INTO fgedu_sales_view(product_name, quantity, price)
VALUES(‘iPhone 15’, 2, 7999.00);

— 输出结果
INSERT 0 1

— 查看销售记录
SELECT * FROM fgedu_sales;

— 输出结果
id | product_id | quantity | price | sale_date
—-+————+———-+——–+————
1 | 1 | 2 | 7999.00| 2026-04-07
(1 row)

— 查看库存更新
SELECT id, name, stock FROM fgedu_products WHERE id = 1;

— 输出结果
id | name | stock
—-+———–+——-
1 | iPhone 15 | 98
(1 row)

— 创建测试表
CREATE TABLE fgedu_users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
password VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);

— 输出结果
CREATE TABLE

— 方案1：使用规则实现简单的审计
CREATE TABLE fgedu_user_audit_rule (
id BIGSERIAL PRIMARY KEY,
user_id INTEGER NOT NULL,
operation VARCHAR(20) NOT NULL,
operation_time TIMESTAMP DEFAULT NOW()
);

— 输出结果
CREATE TABLE

— 创建规则
CREATE OR REPLACE RULE rl_user_insert_audit AS
ON INSERT TO fgedu_users
DO ALSO
INSERT INTO fgedu_user_audit_rule(user_id, operation)
VALUES(NEW.id, ‘INSERT’);

— 输出结果
CREATE RULE

— 方案2：使用触发器实现审计
CREATE TABLE fgedu_user_audit_trigger (
id BIGSERIAL PRIMARY KEY,
user_id INTEGER NOT NULL,
operation VARCHAR(20) NOT NULL,
old_data JSONB,
new_data JSONB,
operation_time TIMESTAMP DEFAULT NOW(),
user_name VARCHAR(100) DEFAULT CURRENT_USER
);

— 输出结果
CREATE TABLE

— 创建触发器函数
CREATE OR REPLACE FUNCTION fgedu_user_audit_trigger_func()
RETURNS TRIGGER
AS $$
BEGIN
IF TG_OP = ‘INSERT’ THEN
INSERT INTO fgedu_user_audit_trigger(
user_id,
operation,
new_data
)
VALUES(
NEW.id,
‘INSERT’,
to_jsonb(NEW)
);
ELSIF TG_OP = ‘UPDATE’ THEN
INSERT INTO fgedu_user_audit_trigger(
user_id,
operation,
old_data,
new_data
)
VALUES(
NEW.id,
‘UPDATE’,
to_jsonb(OLD),
to_jsonb(NEW)
);
ELSIF TG_OP = ‘DELETE’ THEN
INSERT INTO fgedu_user_audit_trigger(
user_id,
operation,
old_data
)
VALUES(
OLD.id,
‘DELETE’,
to_jsonb(OLD)
);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 创建触发器
CREATE TRIGGER trg_user_audit
AFTER INSERT OR UPDATE OR DELETE ON fgedu_users
FOR EACH ROW
EXECUTE FUNCTION fgedu_user_audit_trigger_func();

— 输出结果
CREATE TRIGGER

— 测试两种方案
INSERT INTO fgedu_users(username, email, password)
VALUES(‘user1’, ‘user1@fgedu.net.cn’, ‘password123’);

— 输出结果
INSERT 0 1

— 查看规则审计
SELECT * FROM fgedu_user_audit_rule;

— 输出结果
id | user_id | operation | operation_time
—-+———+———–+————————
1 | 1 | INSERT | 2026-04-07 21:45:00
(1 row)

— 查看触发器审计
SELECT user_id, operation, operation_time, user_name
FROM fgedu_user_audit_trigger;

— 输出结果
user_id | operation | operation_time | user_name
———+———–+————————+———-
1 | INSERT | 2026-04-07 21:45:00 | fgedu
(1 row)

— 测试更新操作
UPDATE fgedu_users SET email = ‘user1_new@fgedu.net.cn’ WHERE id = 1;

— 输出结果
UPDATE 1

— 查看触发器审计（规则不会记录更新）
SELECT user_id, operation, operation_time, user_name
FROM fgedu_user_audit_trigger
ORDER BY operation_time DESC
LIMIT 5;

— 输出结果
user_id | operation | operation_time | user_name
———+———–+————————+———-
1 | UPDATE | 2026-04-07 21:50:00 | fgedu
1 | INSERT | 2026-04-07 21:45:00 | fgedu
(2 rows)

— 场景1：视图可更新性
CREATE TABLE fgedu_customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
active BOOLEAN DEFAULT TRUE
);

— 输出结果
CREATE TABLE

INSERT INTO fgedu_customers(name, email)
VALUES(‘张三’, ‘zhangsan@fgedu.net.cn’),
(‘李四’, ‘lisi@fgedu.net.cn’);

— 输出结果
INSERT 0 2

— 创建活跃客户视图
CREATE VIEW fgedu_active_customers AS
SELECT id, name, email
FROM fgedu_customers
WHERE active = TRUE;

— 输出结果
CREATE VIEW

— 创建规则使视图可更新
CREATE OR REPLACE RULE rl_active_customers_insert AS
ON INSERT TO fgedu_active_customers
DO INSTEAD
INSERT INTO fgedu_customers(name, email, active)
VALUES(NEW.name, NEW.email, TRUE);

— 输出结果
CREATE RULE

CREATE OR REPLACE RULE rl_active_customers_update AS
ON UPDATE TO fgedu_active_customers
DO INSTEAD
UPDATE fgedu_customers
SET name = NEW.name, email = NEW.email
WHERE id = OLD.id AND active = TRUE;

— 输出结果
CREATE RULE

CREATE OR REPLACE RULE rl_active_customers_delete AS
ON DELETE TO fgedu_active_customers
DO INSTEAD
UPDATE fgedu_customers SET active = FALSE WHERE id = OLD.id;

— 输出结果
CREATE RULE

— 测试视图更新
INSERT INTO fgedu_active_customers(name, email)
VALUES(‘王五’, ‘wangwu@fgedu.net.cn’);

— 输出结果
INSERT 0 1

— 查看数据
SELECT * FROM fgedu_customers;

— 输出结果
id | name | email | active
—-+——+——————+——–
1 | 张三 | zhangsan@fgedu.net.cn| t
2 | 李四 | lisi@fgedu.net.cn | t
3 | 王五 | wangwu@fgedu.net.cn | t
(3 rows)

— 场景2：查询重写
CREATE TABLE fgedu_sales_history (
id SERIAL PRIMARY KEY,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL,
price NUMERIC(10,2) NOT NULL,
sale_date DATE NOT NULL
);

— 输出结果
CREATE TABLE

— 插入历史数据
INSERT INTO fgedu_sales_history(product_id, quantity, price, sale_date)
VALUES
(1, 5, 7999.00, ‘2026-03-01’),
(2, 3, 4999.00, ‘2026-03-05’),
(1, 2, 7999.00, ‘2026-03-10’);

— 输出结果
INSERT 0 3

— 创建销售视图
CREATE VIEW fgedu_monthly_sales AS
SELECT
EXTRACT(YEAR FROM sale_date) AS year,
EXTRACT(MONTH FROM sale_date) AS month,
product_id,
SUM(quantity) AS total_quantity,
SUM(price * quantity) AS total_amount
FROM fgedu_sales_history
GROUP BY year, month, product_id
ORDER BY year, month, product_id;

— 输出结果
CREATE VIEW

— 创建规则优化查询
CREATE OR REPLACE RULE rl_monthly_sales_filter AS
ON SELECT TO fgedu_monthly_sales
WHERE NEW.month = 3
DO INSTEAD
SELECT
EXTRACT(YEAR FROM sale_date) AS year,
EXTRACT(MONTH FROM sale_date) AS month,
product_id,
SUM(quantity) AS total_quantity,
SUM(price * quantity) AS total_amount
FROM fgedu_sales_history
WHERE EXTRACT(MONTH FROM sale_date) = 3
GROUP BY year, month, product_id
ORDER BY year, month, product_id;

— 输出结果
CREATE RULE

— 测试查询重写
EXPLAIN ANALYZE SELECT * FROM fgedu_monthly_sales WHERE month = 3;

— 输出结果
QUERY PLAN
———————————————————————————————
HashAggregate (cost=23.93..23.98 rows=2 width=32) (actual time=0.019..0.020 rows=2 loops=1)
Group Key: date_part(‘year’::text, sale_date), date_part(‘month’::text, sale_date), product_id
-> Seq Scan on fgedu_sales_history (cost=0.00..23.90 rows=3 width=20) (actual time=0.005..0.007 rows=3 loops=1)
Filter: (date_part(‘month’::text, sale_date) = ‘3’::double precision)
Rows Removed by Filter: 0
Planning Time: 0.045 ms
Execution Time: 0.032 ms
(7 rows)

— 场景1：复杂的业务逻辑
CREATE TABLE fgedu_orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL,
total_amount NUMERIC(10,2) NOT NULL,
order_date DATE DEFAULT CURRENT_DATE,
status VARCHAR(20) DEFAULT ‘pending’
);

— 输出结果
CREATE TABLE

CREATE TABLE fgedu_order_items (
id SERIAL PRIMARY KEY,
order_id INTEGER REFERENCES fgedu_orders(id),
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL,
price NUMERIC(10,2) NOT NULL
);

— 输出结果
CREATE TABLE

— 创建触发器函数计算订单总额
CREATE OR REPLACE FUNCTION fgedu_calculate_order_total()
RETURNS TRIGGER
AS $$
DECLARE
v_total NUMERIC(10,2);
BEGIN
SELECT SUM(quantity * price) INTO v_total
FROM fgedu_order_items
WHERE order_id = NEW.order_id;

UPDATE fgedu_orders
SET total_amount = v_total
WHERE id = NEW.order_id;

RETURN NEW;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 创建触发器
CREATE TRIGGER trg_calculate_order_total
AFTER INSERT OR UPDATE OR DELETE ON fgedu_order_items
FOR EACH ROW
EXECUTE FUNCTION fgedu_calculate_order_total();

— 输出结果
CREATE TRIGGER

— 测试复杂业务逻辑
INSERT INTO fgedu_orders(customer_id, total_amount)
VALUES(1, 0);

— 输出结果
INSERT 0 1

— 插入订单详情
INSERT INTO fgedu_order_items(order_id, product_id, quantity, price)
VALUES(1, 1, 2, 7999.00),
(1, 2, 1, 4999.00);

— 输出结果
INSERT 0 2

— 查看订单总额
SELECT id, customer_id, total_amount, status FROM fgedu_orders WHERE id = 1;

— 输出结果
id | customer_id | total_amount | status
—-+————-+————–+——–
1 | 1 | 20997.00| pending
(1 row)

— 更新订单详情
UPDATE fgedu_order_items SET quantity = 3 WHERE id = 1;

— 输出结果
UPDATE 1

— 查看订单总额
SELECT id, customer_id, total_amount, status FROM fgedu_orders WHERE id = 1;

— 输出结果
id | customer_id | total_amount | status
—-+————-+————–+——–
1 | 1 | 28996.00| pending
(1 row)

— 场景2：数据验证
CREATE OR REPLACE FUNCTION fgedu_validate_product()
RETURNS TRIGGER
AS $$
BEGIN
— 验证价格
IF NEW.price <= 0 THEN RAISE EXCEPTION 'Price must be greater than 0'; END IF; -- 验证库存 IF NEW.stock < 0 THEN RAISE EXCEPTION 'Stock cannot be negative'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; -- 输出结果 CREATE FUNCTION -- 创建触发器 CREATE TRIGGER trg_validate_product BEFORE INSERT OR UPDATE ON fgedu_products FOR EACH ROW EXECUTE FUNCTION fgedu_validate_product(); -- 输出结果 CREATE TRIGGER -- 测试数据验证 INSERT INTO fgedu_products(name, price, stock) VALUES('Test Product', -100, 10); -- 输出结果 ERROR: Price must be greater than 0 -- 测试库存验证 INSERT INTO fgedu_products(name, price, stock) VALUES('Test Product', 100, -5); -- 输出结果 ERROR: Stock cannot be negative -- 测试成功插入 INSERT INTO fgedu_products(name, price, stock) VALUES('Test Product', 100, 10); -- 输出结果 INSERT 0 1