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

— 输出结果
CREATE TABLE

— 插入测试数据
INSERT INTO fgedu_employees(name, department, salary, active)
VALUES
(‘张三’, ‘技术部’, 8000.00, TRUE),
(‘李四’, ‘销售部’, 6000.00, TRUE),
(‘王五’, ‘技术部’, 9000.00, FALSE),
(‘赵六’, ‘财务部’, 7000.00, TRUE);

— 输出结果
INSERT 0 4

— 创建视图
CREATE VIEW fgedu_active_employees AS
SELECT id, name, department, salary
FROM fgedu_employees
WHERE active = TRUE;

— 输出结果
CREATE VIEW

— 测试视图
SELECT * FROM fgedu_active_employees;

— 输出结果
id | name | department | salary
—-+——+————+——–
1 | 张三 | 技术部 | 8000.00
2 | 李四 | 销售部 | 6000.00
4 | 赵六 | 财务部 | 7000.00
(3 rows)

— 创建SELECT规则
CREATE OR REPLACE RULE rl_employee_dept_filter AS
ON SELECT TO fgedu_active_employees
WHERE NEW.department = ‘技术部’
DO INSTEAD
SELECT id, name, department, salary
FROM fgedu_employees
WHERE active = TRUE AND department = ‘技术部’;

— 输出结果
CREATE RULE

— 测试规则
SELECT * FROM fgedu_active_employees WHERE department = ‘技术部’;

— 输出结果
id | name | department | salary
—-+——+————+——–
1 | 张三 | 技术部 | 8000.00
(1 row)

— 创建审计表
CREATE TABLE fgedu_employee_audit (
id BIGSERIAL PRIMARY KEY,
employee_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

— 创建INSERT规则
CREATE OR REPLACE RULE rl_employee_insert_audit AS
ON INSERT TO fgedu_employees
DO ALSO
INSERT INTO fgedu_employee_audit(
employee_id,
operation,
new_data
)
VALUES(
NEW.id,
‘INSERT’,
to_jsonb(NEW)
);

— 输出结果
CREATE RULE

— 测试INSERT规则
INSERT INTO fgedu_employees(name, department, salary)
VALUES(‘孙七’, ‘销售部’, 6500.00);

— 输出结果
INSERT 0 1

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

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

— 创建INSTEAD OF INSERT规则
CREATE OR REPLACE RULE rl_employee_insert_instead AS
ON INSERT TO fgedu_active_employees
DO INSTEAD
INSERT INTO fgedu_employees(name, department, salary, active)
VALUES(NEW.name, NEW.department, NEW.salary, TRUE);

— 输出结果
CREATE RULE

— 测试INSTEAD OF INSERT规则
INSERT INTO fgedu_active_employees(name, department, salary)
VALUES(‘周八’, ‘技术部’, 8500.00);

— 输出结果
INSERT 0 1

— 查看数据
SELECT * FROM fgedu_employees WHERE name = ‘周八’;

— 输出结果
id | name | department | salary | active
—-+——+————+———+——–
6 | 周八 | 技术部 | 8500.00 | t
(1 row)

— 创建UPDATE规则
CREATE OR REPLACE RULE rl_employee_update_audit AS
ON UPDATE TO fgedu_employees
DO ALSO
INSERT INTO fgedu_employee_audit(
employee_id,
operation,
old_data,
new_data
)
VALUES(
OLD.id,
‘UPDATE’,
to_jsonb(OLD),
to_jsonb(NEW)
);

— 输出结果
CREATE RULE

— 测试UPDATE规则
UPDATE fgedu_employees SET salary = 9000.00 WHERE id = 1;

— 输出结果
UPDATE 1

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

— 输出结果
employee_id | operation | operation_time | user_name
————-+———–+————————+———-
1 | UPDATE | 2026-04-07 21:05:00 | fgedu
6 | INSERT | 2026-04-07 21:00:00 | fgedu
5 | INSERT | 2026-04-07 20:55:00 | fgedu
(3 rows)

— 创建DELETE规则
CREATE OR REPLACE RULE rl_employee_delete_audit AS
ON DELETE TO fgedu_employees
DO ALSO
INSERT INTO fgedu_employee_audit(
employee_id,
operation,
old_data
)
VALUES(
OLD.id,
‘DELETE’,
to_jsonb(OLD)
);

— 输出结果
CREATE RULE

— 测试DELETE规则
DELETE FROM fgedu_employees WHERE id = 5;

— 输出结果
DELETE 1

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

— 输出结果
employee_id | operation | operation_time | user_name
————-+———–+————————+———-
5 | DELETE | 2026-04-07 21:10:00 | fgedu
1 | UPDATE | 2026-04-07 21:05:00 | fgedu
6 | INSERT | 2026-04-07 21:00:00 | fgedu
5 | INSERT | 2026-04-07 20:55:00 | fgedu
(4 rows)

— 创建INSTEAD OF UPDATE规则
CREATE OR REPLACE RULE rl_active_employee_update_instead AS
ON UPDATE TO fgedu_active_employees
DO INSTEAD
UPDATE fgedu_employees
SET name = NEW.name, department = NEW.department, salary = NEW.salary
WHERE id = OLD.id AND active = TRUE;

— 输出结果
CREATE RULE

— 测试INSTEAD OF UPDATE规则
UPDATE fgedu_active_employees SET salary = 9500.00 WHERE id = 1;

— 输出结果
UPDATE 1

— 查看数据
SELECT id, name, department, salary FROM fgedu_employees WHERE id = 1;

— 输出结果
id | name | department | salary
—-+——+————+——–
1 | 张三 | 技术部 | 9500.00
(1 row)

— 创建销售表
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,
status VARCHAR(20) DEFAULT ‘pending’
);

— 输出结果
CREATE TABLE

— 创建产品表
CREATE TABLE fgedu_products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price NUMERIC(10,2) NOT NULL,
stock INTEGER NOT NULL
);

— 输出结果
CREATE TABLE

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

— 输出结果
INSERT 0 3

— 创建销售视图
CREATE VIEW fgedu_sales_view AS
SELECT s.id, p.name AS product_name, s.quantity, s.price, s.sale_date, s.status
FROM fgedu_sales s
JOIN fgedu_products p ON s.product_id = p.id;

— 输出结果
CREATE VIEW

— 创建INSTEAD OF INSERT规则
CREATE OR REPLACE RULE rl_sales_insert_instead AS
ON INSERT TO fgedu_sales_view
DO INSTEAD (
— 查找产品ID
SELECT id INTO STRICT NEW.product_id FROM fgedu_products WHERE name = NEW.product_name;
— 插入销售记录
INSERT INTO fgedu_sales(product_id, quantity, price, sale_date, status)
VALUES(NEW.product_id, NEW.quantity, NEW.price, NEW.sale_date, NEW.status);
— 更新库存
UPDATE fgedu_products SET stock = stock – NEW.quantity WHERE id = NEW.product_id;
);

— 输出结果
CREATE RULE

— 测试规则
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 | status
—-+————+———-+——–+————+——–
1 | 1 | 2 | 7999.00| 2026-04-07 | pending
(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,
role VARCHAR(20) DEFAULT ‘user’,
created_at TIMESTAMP DEFAULT NOW()
);

— 输出结果
CREATE TABLE

— 创建用户视图
CREATE VIEW fgedu_user_view AS
SELECT id, username, email, role FROM fgedu_users;

— 输出结果
CREATE VIEW

— 创建权限控制规则
CREATE OR REPLACE RULE rl_user_select_permission AS
ON SELECT TO fgedu_user_view
WHERE current_user <> ‘postgres’
DO INSTEAD
SELECT id, username, email,
CASE WHEN current_user = username THEN role ELSE ‘user’ END
FROM fgedu_users
WHERE id = NEW.id;

— 输出结果
CREATE RULE

— 插入测试数据
INSERT INTO fgedu_users(username, email, password, role)
VALUES
(‘user1’, ‘user1@fgedu.net.cn’, ‘password123’, ‘user’),
(‘admin’, ‘admin@fgedu.net.cn’, ‘password123’, ‘admin’);

— 输出结果
INSERT 0 2

— 测试权限控制规则
— 以admin用户登录
SET SESSION AUTHORIZATION admin;

— 查看自己的信息
SELECT * FROM fgedu_user_view WHERE id = 2;

— 输出结果
id | username | email | role
—-+———-+——————+——
2 | admin | admin@fgedu.net.cn| admin
(1 row)

— 查看其他用户信息
SELECT * FROM fgedu_user_view WHERE id = 1;

— 输出结果
id | username | email | role
—-+———-+——————+——
1 | user1 | user1@fgedu.net.cn| user
(1 row)

— 切换回postgres用户
SET SESSION AUTHORIZATION postgres;

— 创建订单表
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 TABLE fgedu_customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL
);

— 输出结果
CREATE TABLE

— 插入客户数据
INSERT INTO fgedu_customers(name, email)
VALUES(‘张三’, ‘zhangsan@fgedu.net.cn’),
(‘李四’, ‘lisi@fgedu.net.cn’);

— 输出结果
INSERT 0 2

— 创建订单视图
CREATE VIEW fgedu_order_view AS
SELECT o.id, c.name AS customer_name, o.total_amount, o.order_date, o.status
FROM fgedu_orders o
JOIN fgedu_customers c ON o.customer_id = c.id;

— 输出结果
CREATE VIEW

— 创建INSTEAD OF INSERT规则（处理复杂的订单创建）
CREATE OR REPLACE RULE rl_order_insert_instead AS
ON INSERT TO fgedu_order_view
DO INSTEAD (
— 查找客户ID
SELECT id INTO STRICT NEW.customer_id FROM fgedu_customers WHERE name = NEW.customer_name;
— 插入订单
INSERT INTO fgedu_orders(customer_id, total_amount, order_date, status)
VALUES(NEW.customer_id, NEW.total_amount, NEW.order_date, NEW.status)
RETURNING id INTO NEW.order_id;
— 这里可以添加订单详情的插入逻辑
);

— 输出结果
CREATE RULE

— 测试复杂规则
INSERT INTO fgedu_order_view(customer_name, total_amount)
VALUES(‘张三’, 15998.00);

— 输出结果
INSERT 0 1

— 查看订单
SELECT * FROM fgedu_orders;

— 输出结果
id | customer_id | total_amount | order_date | status
—-+————-+————–+————+——–
1 | 1 | 15998.00| 2026-04-07 | pending
(1 row)

— 查看订单视图
SELECT * FROM fgedu_order_view;

— 输出结果
id | customer_name | total_amount | order_date | status
—-+—————+————–+————+——–
1 | 张三 | 15998.00| 2026-04-07 | pending
(1 row)