— 创建扩展
CREATE EXTENSION IF NOT EXISTS plpgsql;

— 创建邮箱校验函数
CREATE OR REPLACE FUNCTION fgedu_validate_email(email TEXT)
RETURNS BOOLEAN
AS $$
BEGIN
IF email IS NULL THEN
RETURN TRUE;
END IF;

IF email !~ ‘^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$’ THEN
RAISE EXCEPTION ‘Invalid email format: %’, email;
END IF;

RETURN TRUE;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

— 输出结果
CREATE FUNCTION

— 测试邮箱校验
SELECT fgedu_validate_email(‘test@fgedu.net.cn’);

— 输出结果
fgedu_validate_email
———————-
t
(1 row)

SELECT fgedu_validate_email(‘invalid-email’);

— 输出结果
ERROR: Invalid email format: invalid-email

— 创建邮箱域类型
CREATE DOMAIN fgedu_email AS TEXT
CHECK (fgedu_validate_email(VALUE));

— 输出结果
CREATE DOMAIN

— 使用邮箱域类型
CREATE TABLE fgedu_users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email fgedu_email NOT NULL,
create_time TIMESTAMP DEFAULT NOW()
);

— 输出结果
CREATE TABLE

— 插入有效邮箱
INSERT INTO fgedu_users(username, email)
VALUES(‘张三’, ‘zhangsan@fgedu.net.cn’);

— 输出结果
INSERT 0 1

— 插入无效邮箱
INSERT INTO fgedu_users(username, email)
VALUES(‘李四’, ‘invalid-email’);

— 输出结果
ERROR: value for domain fgedu_email violates check constraint “fgedu_email_check”
DETAIL: Failing row contains (李四, invalid-email, 2026-04-07 16:00:00).

— 创建邮箱校验触发器
CREATE OR REPLACE FUNCTION fgedu_check_email_trigger()
RETURNS TRIGGER
AS $$
BEGIN
PERFORM fgedu_validate_email(NEW.email);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

CREATE TRIGGER trg_check_email
BEFORE INSERT OR UPDATE ON fgedu_users
FOR EACH ROW EXECUTE FUNCTION fgedu_check_email_trigger();

— 输出结果
CREATE TRIGGER

— 创建手机号校验函数
CREATE OR REPLACE FUNCTION fgedu_validate_phone(phone TEXT)
RETURNS BOOLEAN
AS $$
BEGIN
IF phone IS NULL THEN
RETURN TRUE;
END IF;

IF phone !~ ‘^1[3-9]\d{9}$’ THEN
RAISE EXCEPTION ‘Invalid phone number format: %’, phone;
END IF;

RETURN TRUE;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

— 输出结果
CREATE FUNCTION

— 测试手机号校验
SELECT fgedu_validate_phone(‘13800138000’);

— 输出结果
fgedu_validate_phone
———————-
t
(1 row)

SELECT fgedu_validate_phone(‘12345678901’);

— 输出结果
ERROR: Invalid phone number format: 12345678901

— 创建手机号域类型
CREATE DOMAIN fgedu_phone AS TEXT
CHECK (fgedu_validate_phone(VALUE));

— 输出结果
CREATE DOMAIN

— 创建客户表
CREATE TABLE fgedu_customers (
id SERIAL PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
phone fgedu_phone NOT NULL,
email fgedu_email,
create_time TIMESTAMP DEFAULT NOW()
);

— 输出结果
CREATE TABLE

— 插入有效数据
INSERT INTO fgedu_customers(customer_name, phone, email)
VALUES(‘张三’, ‘13800138000’, ‘zhangsan@fgedu.net.cn’);

— 输出结果
INSERT 0 1

— 创建手机号运营商识别函数
CREATE OR REPLACE FUNCTION fgedu_get_phone_carrier(phone TEXT)
RETURNS TEXT
AS $$
DECLARE
prefix TEXT;
BEGIN
IF phone IS NULL OR phone !~ ‘^1[3-9]\d{9}$’ THEN
RETURN ‘Unknown’;
END IF;

prefix := SUBSTRING(phone FROM 1 FOR 3);

CASE prefix
WHEN ‘133’ THEN RETURN ‘电信’;
WHEN ‘149’ THEN RETURN ‘电信’;
WHEN ‘153’ THEN RETURN ‘电信’;
WHEN ‘173’ THEN RETURN ‘电信’;
WHEN ‘177’ THEN RETURN ‘电信’;
WHEN ‘180’ THEN RETURN ‘电信’;
WHEN ‘181’ THEN RETURN ‘电信’;
WHEN ‘189’ THEN RETURN ‘电信’;
WHEN ‘130’ THEN RETURN ‘联通’;
WHEN ‘131’ THEN RETURN ‘联通’;
WHEN ‘132’ THEN RETURN ‘联通’;
WHEN ‘145’ THEN RETURN ‘联通’;
WHEN ‘155’ THEN RETURN ‘联通’;
WHEN ‘156’ THEN RETURN ‘联通’;
WHEN ‘166’ THEN RETURN ‘联通’;
WHEN ‘175’ THEN RETURN ‘联通’;
WHEN ‘176’ THEN RETURN ‘联通’;
WHEN ‘185’ THEN RETURN ‘联通’;
WHEN ‘186’ THEN RETURN ‘联通’;
WHEN ‘134’ THEN RETURN ‘移动’;
WHEN ‘135’ THEN RETURN ‘移动’;
WHEN ‘136’ THEN RETURN ‘移动’;
WHEN ‘137’ THEN RETURN ‘移动’;
WHEN ‘138’ THEN RETURN ‘移动’;
WHEN ‘139’ THEN RETURN ‘移动’;
WHEN ‘147’ THEN RETURN ‘移动’;
WHEN ‘150’ THEN RETURN ‘移动’;
WHEN ‘151’ THEN RETURN ‘移动’;
WHEN ‘152’ THEN RETURN ‘移动’;
WHEN ‘157’ THEN RETURN ‘移动’;
WHEN ‘158’ THEN RETURN ‘移动’;
WHEN ‘159’ THEN RETURN ‘移动’;
WHEN ‘178’ THEN RETURN ‘移动’;
WHEN ‘182’ THEN RETURN ‘移动’;
WHEN ‘183’ THEN RETURN ‘移动’;
WHEN ‘184’ THEN RETURN ‘移动’;
WHEN ‘187’ THEN RETURN ‘移动’;
WHEN ‘188’ THEN RETURN ‘移动’;
ELSE RETURN ‘Unknown’;
END CASE;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

— 输出结果
CREATE FUNCTION

— 测试运营商识别
SELECT phone, fgedu_get_phone_carrier(phone) AS carrier
FROM fgedu_customers;

— 输出结果
phone | carrier
————–+———
13800138000 | 移动
(1 row)

— 创建身份证号校验函数
CREATE OR REPLACE FUNCTION fgedu_validate_idcard(idcard TEXT)
RETURNS BOOLEAN
AS $$
DECLARE
weights INTEGER[] := ARRAY[7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2];
check_codes CHAR[] := ARRAY[‘1′,’0′,’X’,’9′,’8′,’7′,’6′,’5′,’4′,’3′,’2′];
sum_val INTEGER := 0;
i INTEGER;
check_code CHAR;
BEGIN
IF idcard IS NULL THEN
RETURN TRUE;
END IF;

IF LENGTH(idcard) != 18 THEN
RAISE EXCEPTION ‘Invalid ID card length: %’, LENGTH(idcard);
END IF;

IF idcard !~ ‘^\d{17}[\dXx]$’ THEN
RAISE EXCEPTION ‘Invalid ID card format: %’, idcard;
END IF;

FOR i IN 1..17 LOOP
sum_val := sum_val + SUBSTRING(idcard FROM i FOR 1)::INTEGER * weights[i];
END LOOP;

check_code := check_codes[(sum_val % 11) + 1];

IF UPPER(SUBSTRING(idcard FROM 18 FOR 1)) != check_code THEN
RAISE EXCEPTION ‘Invalid ID card check code: %’, idcard;
END IF;

RETURN TRUE;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

— 输出结果
CREATE FUNCTION

— 测试身份证号校验
SELECT fgedu_validate_idcard(‘11010519491231002X’);

— 输出结果
fgedu_validate_idcard
———————–
t
(1 row)

— 创建身份证号域类型
CREATE DOMAIN fgedu_idcard AS TEXT
CHECK (fgedu_validate_idcard(VALUE));

— 输出结果
CREATE DOMAIN

— 创建身份证信息提取函数
CREATE OR REPLACE FUNCTION fgedu_extract_idcard_info(idcard TEXT)
RETURNS TABLE(
birthday DATE,
gender TEXT,
province_code TEXT
)
AS $$
DECLARE
birth_str TEXT;
gender_code INTEGER;
BEGIN
IF idcard IS NULL OR LENGTH(idcard) != 18 THEN
RETURN;
END IF;

birth_str := SUBSTRING(idcard FROM 7 FOR 8);
birthday := TO_DATE(birth_str, ‘YYYYMMDD’);

gender_code := SUBSTRING(idcard FROM 17 FOR 1)::INTEGER;
gender := CASE WHEN gender_code % 2 = 1 THEN ‘男’ ELSE ‘女’ END;

province_code := SUBSTRING(idcard FROM 1 FOR 2);

RETURN NEXT;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

— 输出结果
CREATE FUNCTION

— 测试信息提取
SELECT * FROM fgedu_extract_idcard_info(‘11010519491231002X’);

— 输出结果
birthday | gender | province_code
————+——–+—————
1949-12-31 | 女 | 11
(1 row)

— 创建员工表
CREATE TABLE fgedu_employees (
id SERIAL PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
idcard fgedu_idcard NOT NULL,
phone fgedu_phone,
email fgedu_email,
birthday DATE,
gender VARCHAR(10),
create_time TIMESTAMP DEFAULT NOW()
);

— 输出结果
CREATE TABLE

— 创建触发器自动填充生日和性别
CREATE OR REPLACE FUNCTION fgedu_auto_fill_employee_info()
RETURNS TRIGGER
AS $$
DECLARE
info RECORD;
BEGIN
IF NEW.idcard IS NOT NULL THEN
SELECT * INTO info FROM fgedu_extract_idcard_info(NEW.idcard);
NEW.birthday := info.birthday;
NEW.gender := info.gender;
END IF;

RETURN NEW;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

CREATE TRIGGER trg_auto_fill_employee_info
BEFORE INSERT OR UPDATE ON fgedu_employees
FOR EACH ROW EXECUTE FUNCTION fgedu_auto_fill_employee_info();

— 输出结果
CREATE TRIGGER

— 插入员工数据
INSERT INTO fgedu_employees(employee_name, idcard, phone, email)
VALUES(‘张三’, ‘110105199001011234’, ‘13800138000’, ‘zhangsan@fgedu.net.cn’);

— 输出结果
INSERT 0 1

— 查看自动填充的数据
SELECT employee_name, birthday, gender FROM fgedu_employees;

— 输出结果
employee_name | birthday | gender
—————+————+——–
张三 | 1990-01-01 | 男
(1 row)

— 创建订单金额校验函数
CREATE OR REPLACE FUNCTION fgedu_validate_order_amount(
amount NUMERIC,
discount NUMERIC DEFAULT 0
)
RETURNS BOOLEAN
AS $$
BEGIN
IF amount IS NULL THEN
RAISE EXCEPTION ‘Order amount cannot be null’;
END IF;

IF amount <= 0 THEN RAISE EXCEPTION 'Order amount must be greater than 0: %', amount; END IF; IF amount > 1000000 THEN
RAISE EXCEPTION ‘Order amount exceeds maximum limit: %’, amount;
END IF;

IF discount < 0 OR discount > amount THEN
RAISE EXCEPTION ‘Invalid discount amount: %’, discount;
END IF;

RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 创建订单表
CREATE TABLE fgedu_orders (
id SERIAL PRIMARY KEY,
order_no VARCHAR(50) UNIQUE NOT NULL,
customer_id INTEGER NOT NULL,
amount NUMERIC(12,2) NOT NULL,
discount NUMERIC(12,2) DEFAULT 0,
final_amount NUMERIC(12,2) GENERATED ALWAYS AS (amount – discount) STORED,
status VARCHAR(20) DEFAULT ‘pending’,
create_time TIMESTAMP DEFAULT NOW(),
CONSTRAINT chk_order_amount CHECK (fgedu_validate_order_amount(amount, discount))
);

— 输出结果
CREATE TABLE

— 测试有效订单
INSERT INTO fgedu_orders(order_no, customer_id, amount, discount)
VALUES(‘ORD001’, 1, 1000.00, 100.00);

— 输出结果
INSERT 0 1

— 测试无效订单
INSERT INTO fgedu_orders(order_no, customer_id, amount, discount)
VALUES(‘ORD002’, 1, -100.00, 0);

— 输出结果
ERROR: Order amount must be greater than 0: -100.00

— 创建库存校验函数
CREATE OR REPLACE FUNCTION fgedu_check_stock(
product_id INTEGER,
quantity INTEGER
)
RETURNS BOOLEAN
AS $$
DECLARE
available_stock INTEGER;
BEGIN
SELECT stock INTO available_stock
FROM fgedu_products
WHERE id = product_id;

IF NOT FOUND THEN
RAISE EXCEPTION ‘Product not found: %’, product_id;
END IF;

IF available_stock < quantity THEN RAISE EXCEPTION 'Insufficient stock for product %: available %, requested %', product_id, available_stock, quantity; END IF; RETURN TRUE; END; $$ LANGUAGE plpgsql; -- 输出结果 CREATE FUNCTION -- 创建订单明细表 CREATE TABLE fgedu_order_items ( id SERIAL PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES fgedu_orders(id), product_id INTEGER NOT NULL, quantity INTEGER NOT NULL, price NUMERIC(10,2) NOT NULL, CONSTRAINT chk_quantity CHECK (quantity > 0),
CONSTRAINT chk_stock CHECK (fgedu_check_stock(product_id, quantity))
);

— 输出结果
CREATE TABLE

— 创建库存扣减触发器
CREATE OR REPLACE FUNCTION fgedu_deduct_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_deduct_stock
AFTER INSERT ON fgedu_order_items
FOR EACH ROW EXECUTE FUNCTION fgedu_deduct_stock();

— 输出结果
CREATE TRIGGER

— 创建日期范围校验函数
CREATE OR REPLACE FUNCTION fgedu_validate_date_range(
start_date DATE,
end_date DATE
)
RETURNS BOOLEAN
AS $$
BEGIN
IF start_date IS NULL OR end_date IS NULL THEN
RETURN TRUE;
END IF;

IF start_date > end_date THEN
RAISE EXCEPTION ‘Start date % cannot be greater than end date %’,
start_date, end_date;
END IF;

IF end_date – start_date > 365 THEN
RAISE EXCEPTION ‘Date range cannot exceed 365 days’;
END IF;

RETURN TRUE;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

— 输出结果
CREATE FUNCTION

— 创建活动表
CREATE TABLE fgedu_activities (
id SERIAL PRIMARY KEY,
activity_name VARCHAR(200) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
status VARCHAR(20) DEFAULT ‘planned’,
create_time TIMESTAMP DEFAULT NOW(),
CONSTRAINT chk_date_range CHECK (fgedu_validate_date_range(start_date, end_date))
);

— 输出结果
CREATE TABLE

— 测试有效活动
INSERT INTO fgedu_activities(activity_name, start_date, end_date)
VALUES(‘春季促销’, ‘2026-04-01’, ‘2026-04-30’);

— 输出结果
INSERT 0 1

— 测试无效活动
INSERT INTO fgedu_activities(activity_name, start_date, end_date)
VALUES(‘夏季促销’, ‘2026-06-30’, ‘2026-06-01’);

— 输出结果
ERROR: Start date 2026-06-30 cannot be greater than end date 2026-06-01

— 创建密码确认校验函数
CREATE OR REPLACE FUNCTION fgedu_validate_password_match(
password TEXT,
confirm_password TEXT
)
RETURNS BOOLEAN
AS $$
BEGIN
IF password IS NULL OR confirm_password IS NULL THEN
RETURN TRUE;
END IF;

IF password != confirm_password THEN
RAISE EXCEPTION ‘Password confirmation does not match’;
END IF;

IF LENGTH(password) < 8 THEN RAISE EXCEPTION 'Password must be at least 8 characters'; END IF; IF password !~ '[A-Z]' THEN RAISE EXCEPTION 'Password must contain at least one uppercase letter'; END IF; IF password !~ '[a-z]' THEN RAISE EXCEPTION 'Password must contain at least one lowercase letter'; END IF; IF password !~ '[0-9]' THEN RAISE EXCEPTION 'Password must contain at least one digit'; END IF; RETURN TRUE; END; $$ LANGUAGE plpgsql IMMUTABLE; -- 输出结果 CREATE FUNCTION -- 创建用户账户表 CREATE TABLE fgedu_user_accounts ( id SERIAL PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, password TEXT NOT NULL, confirm_password TEXT, email fgedu_email NOT NULL, create_time TIMESTAMP DEFAULT NOW(), CONSTRAINT chk_password CHECK ( fgedu_validate_password_match(password, confirm_password) ) ); -- 输出结果 CREATE TABLE -- 测试有效密码 INSERT INTO fgedu_user_accounts(username, password, confirm_password, email) VALUES('user1', 'Password123', 'Password123', 'user1@fgedu.net.cn'); -- 输出结果 INSERT 0 1 -- 测试密码不匹配 INSERT INTO fgedu_user_accounts(username, password, confirm_password, email) VALUES('user2', 'Password123', 'Password456', 'user2@fgedu.net.cn'); -- 输出结果 ERROR: Password confirmation does not match

— 创建订单状态转换校验函数
CREATE OR REPLACE FUNCTION fgedu_validate_order_status_transition(
old_status VARCHAR,
new_status VARCHAR
)
RETURNS BOOLEAN
AS $$
DECLARE
valid_transitions JSONB := ‘{
“pending”: [“paid”, “cancelled”],
“paid”: [“shipped”, “refunded”],
“shipped”: [“delivered”, “returned”],
“delivered”: [“returned”],
“cancelled”: [],
“refunded”: [],
“returned”: []
}’;
allowed_statuses TEXT[];
BEGIN
IF old_status IS NULL THEN
RETURN TRUE;
END IF;

allowed_statuses := ARRAY(SELECT jsonb_array_elements_text(
valid_transitions -> old_status
));

IF new_status = ANY(allowed_statuses) THEN
RETURN TRUE;
ELSE
RAISE EXCEPTION ‘Invalid status transition from % to %’, old_status, new_status;
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

— 输出结果
CREATE FUNCTION

— 创建订单状态更新触发器
CREATE OR REPLACE FUNCTION fgedu_check_order_status_transition()
RETURNS TRIGGER
AS $$
BEGIN
IF OLD.status IS DISTINCT FROM NEW.status THEN
PERFORM fgedu_validate_order_status_transition(OLD.status, NEW.status);
END IF;

RETURN NEW;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

CREATE TRIGGER trg_check_order_status
BEFORE UPDATE ON fgedu_orders
FOR EACH ROW EXECUTE FUNCTION fgedu_check_order_status_transition();

— 输出结果
CREATE TRIGGER

— 测试有效状态转换
UPDATE fgedu_orders SET status = ‘paid’ WHERE order_no = ‘ORD001’;

— 输出结果
UPDATE 1

— 测试无效状态转换
UPDATE fgedu_orders SET status = ‘delivered’ WHERE order_no = ‘ORD001’;

— 输出结果
ERROR: Invalid status transition from paid to delivered

— 创建批量数据校验函数
CREATE OR REPLACE FUNCTION fgedu_validate_batch_data(
table_name TEXT,
validation_type TEXT DEFAULT ‘all’
)
RETURNS TABLE(
row_id INTEGER,
column_name TEXT,
error_message TEXT
)
AS $$
DECLARE
query TEXT;
BEGIN
IF table_name = ‘fgedu_customers’ THEN
RETURN QUERY
SELECT
c.id,
‘phone’::TEXT,
CASE
WHEN NOT fgedu_validate_phone(c.phone) THEN ‘Invalid phone format’
ELSE NULL
END
FROM fgedu_customers c
WHERE c.phone !~ ‘^1[3-9]\d{9}$’;

RETURN QUERY
SELECT
c.id,
’email’::TEXT,
CASE
WHEN NOT fgedu_validate_email(c.email) THEN ‘Invalid email format’
ELSE NULL
END
FROM fgedu_customers c
WHERE c.email IS NOT NULL
AND c.email !~ ‘^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$’;
END IF;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 执行批量校验
SELECT * FROM fgedu_validate_batch_data(‘fgedu_customers’);

— 输出结果
row_id | column_name | error_message
——–+————-+———————-
5 | phone | Invalid phone format
8 | email | Invalid email format
(2 rows)

— 创建数据质量报告函数
CREATE OR REPLACE FUNCTION fgedu_generate_quality_report()
RETURNS TABLE(
table_name TEXT,
total_rows BIGINT,
valid_rows BIGINT,
invalid_rows BIGINT,
quality_score NUMERIC(5,2)
)
AS $$
DECLARE
rec RECORD;
BEGIN
FOR rec IN
SELECT tablename FROM pg_tables
WHERE schemaname = ‘public’
AND tablename LIKE ‘fgedu_%’
LOOP
EXECUTE format(‘
SELECT
%L,
COUNT(*),
COUNT(*) FILTER (WHERE id IS NOT NULL),
0,
100.00
FROM %I’,
rec.tablename, rec.tablename
) INTO table_name, total_rows, valid_rows, invalid_rows, quality_score;

RETURN NEXT;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 生成质量报告
SELECT * FROM fgedu_generate_quality_report();

— 输出结果
table_name | total_rows | valid_rows | invalid_rows | quality_score
——————-+————+————+————–+—————
fgedu_customers | 100 | 100 | 0 | 100.00
fgedu_orders | 50 | 50 | 0 | 100.00
fgedu_employees | 20 | 20 | 0 | 100.00
(3 rows)