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

— 输出结果
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 OR REPLACE FUNCTION fgedu_get_product_count()
RETURNS INTEGER
AS $$
BEGIN
RETURN (SELECT COUNT(*) FROM fgedu_products);
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试函数
SELECT fgedu_get_product_count();

— 输出结果
fgedu_get_product_count
————————
3
(1 row)

— 创建带参数的函数
CREATE OR REPLACE FUNCTION fgedu_get_product_by_id(IN p_id INTEGER)
RETURNS fgedu_products%ROWTYPE
AS $$
DECLARE
v_product fgedu_products%ROWTYPE;
BEGIN
SELECT * INTO v_product FROM fgedu_products WHERE id = p_id;

IF NOT FOUND THEN
RAISE EXCEPTION ‘Product with ID % not found’, p_id;
END IF;

RETURN v_product;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试带参数的函数
SELECT * FROM fgedu_get_product_by_id(1);

— 输出结果
id | name | price | stock | created_at
—-+———–+——–+——-+—————————-
1 | iPhone 15 | 7999.00| 100 | 2026-04-08 01:30:00
(1 row)

— 创建返回表的函数
CREATE OR REPLACE FUNCTION fgedu_get_products_by_price_range(
IN p_min_price NUMERIC(10,2),
IN p_max_price NUMERIC(10,2)
)
RETURNS TABLE(
id INTEGER,
name VARCHAR(100),
price NUMERIC(10,2),
stock INTEGER
)
AS $$
BEGIN
RETURN QUERY
SELECT
id,
name,
price,
stock
FROM fgedu_products
WHERE price BETWEEN p_min_price AND p_max_price
ORDER BY price;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试返回表的函数
SELECT * FROM fgedu_get_products_by_price_range(5000.00, 10000.00);

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

— 创建带默认参数的函数
CREATE OR REPLACE FUNCTION fgedu_get_products_by_stock(
IN p_min_stock INTEGER DEFAULT 0
)
RETURNS TABLE(
id INTEGER,
name VARCHAR(100),
price NUMERIC(10,2),
stock INTEGER
)
AS $$
BEGIN
RETURN QUERY
SELECT
id,
name,
price,
stock
FROM fgedu_products
WHERE stock >= p_min_stock
ORDER BY stock DESC;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试带默认参数的函数
SELECT * FROM fgedu_get_products_by_stock();

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

SELECT * FROM fgedu_get_products_by_stock(40);

— 输出结果
id | name | price | stock
—-+———–+——–+——-
1 | iPhone 15 | 7999.00| 100
2 | iPad Air | 4999.00| 50
(2 rows)

— 创建订单表
CREATE TABLE fgedu_orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL,
total_amount NUMERIC(10,2) NOT NULL,
status VARCHAR(20) DEFAULT ‘pending’,
created_at TIMESTAMP DEFAULT NOW()
);

— 输出结果
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,
created_at TIMESTAMP DEFAULT NOW()
);

— 输出结果
CREATE TABLE

— 创建客户表
CREATE TABLE fgedu_customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);

— 输出结果
CREATE TABLE

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

— 输出结果
INSERT 0 2

— 创建存储过程
CREATE OR REPLACE PROCEDURE fgedu_create_order(
IN p_customer_id INTEGER,
IN p_product_ids INTEGER[],
IN p_quantities INTEGER[],
OUT p_order_id INTEGER
)
AS $$
DECLARE
v_order_id INTEGER;
v_total_amount NUMERIC(10,2) := 0;
v_i INTEGER := 1;
v_product_price NUMERIC(10,2);
v_product_stock INTEGER;
BEGIN
— 开始事务
BEGIN
— 创建订单
INSERT INTO fgedu_orders(customer_id, total_amount, status)
VALUES(p_customer_id, 0, ‘pending’)
RETURNING id INTO v_order_id;

— 处理订单详情
WHILE v_i <= array_length(p_product_ids, 1) LOOP -- 获取产品信息 SELECT price, stock INTO v_product_price, v_product_stock FROM fgedu_products WHERE id = p_product_ids[v_i]; -- 检查库存 IF v_product_stock < p_quantities[v_i] THEN RAISE EXCEPTION 'Insufficient stock for product %', p_product_ids[v_i]; END IF; -- 插入订单详情 INSERT INTO fgedu_order_items(order_id, product_id, quantity, price) VALUES(v_order_id, p_product_ids[v_i], p_quantities[v_i], v_product_price); -- 计算总金额 v_total_amount := v_total_amount + (v_product_price * p_quantities[v_i]); -- 更新库存 UPDATE fgedu_products SET stock = stock - p_quantities[v_i] WHERE id = p_product_ids[v_i]; v_i := v_i + 1; END LOOP; -- 更新订单总金额 UPDATE fgedu_orders SET total_amount = v_total_amount WHERE id = v_order_id; -- 设置输出参数 p_order_id := v_order_id; -- 提交事务 EXCEPTION WHEN OTHERS THEN -- 回滚事务 RAISE EXCEPTION 'Error creating order: %', SQLERRM; END; END; $$ LANGUAGE plpgsql; -- 输出结果 CREATE PROCEDURE -- 测试存储过程 CALL fgedu_create_order(1, ARRAY[1, 2], ARRAY[2, 1], NULL); -- 输出结果 ERROR: procedure fgedu_create_order(integer, integer[], integer[], unknown) does not exist HINT: No procedure matches the given name and argument types. You might need to add explicit type casts. -- 正确的调用方式 DO $$ DECLARE v_order_id INTEGER; BEGIN CALL fgedu_create_order(1, ARRAY[1, 2], ARRAY[2, 1], v_order_id); RAISE NOTICE 'Created order with ID: %', v_order_id; END; $$; -- 输出结果 NOTICE: Created order with ID: 1 DO -- 查看订单 SELECT * FROM fgedu_orders; -- 输出结果 id | customer_id | total_amount | status | created_at ----+-------------+--------------+---------+---------------------------- 1 | 1 | 20997.00| pending | 2026-04-08 01:45:00 (1 row) -- 查看订单详情 SELECT * FROM fgedu_order_items; -- 输出结果 id | order_id | product_id | quantity | price | created_at ----+----------+------------+----------+--------+---------------------------- 1 | 1 | 1 | 2 | 7999.00| 2026-04-08 01:45:00 2 | 1 | 2 | 1 | 4999.00| 2026-04-08 01:45:00 (2 rows) -- 查看库存更新 SELECT id, name, stock FROM fgedu_products; -- 输出结果 id | name | stock ----+-----------+------- 1 | iPhone 15 | 98 2 | iPad Air | 49 3 | MacBook Pro| 30 (3 rows)

— 查看函数
SELECT
proname AS function_name,
proargtypes::regtype[] AS arguments,
prorettype::regtype AS return_type,
prosrc AS source_code
FROM pg_proc
WHERE proname LIKE ‘fgedu%’;

— 输出结果
function_name | arguments | return_type | prosrc
————————–+————————-+—————————+—————————————————————————————————————————————————————–
fgedu_get_product_count | {} | integer |
BEGIN
RETURN (SELECT COUNT(*) FROM fgedu_products);
END;

— 查看存储过程
SELECT
proname AS procedure_name,
proargtypes::regtype[] AS arguments,
prosrc AS source_code
FROM pg_proc
WHERE proname LIKE ‘fgedu%’ AND prokind = ‘p’;

— 输出结果
procedure_name | arguments | prosrc
——————-+————————-+—————————————————————————————————————————————————————–
fgedu_create_order| integer, integer[], integer[], integer |
DECLARE
v_order_id INTEGER;
v_total_amount NUMERIC(10,2) := 0;
v_i INTEGER := 1;
v_product_price NUMERIC(10,2);
v_product_stock INTEGER;
BEGIN
— 开始事务
BEGIN
— 创建订单
INSERT INTO fgedu_orders(customer_id, total_amount, status)
VALUES(p_customer_id, 0, ‘pending’)
RETURNING id INTO v_order_id;

— 处理订单详情
WHILE v_i <= array_length(p_product_ids, 1) LOOP -- 获取产品信息 SELECT price, stock INTO v_product_price, v_product_stock FROM fgedu_products WHERE id = p_product_ids[v_i]; -- 检查库存 IF v_product_stock < p_quantities[v_i] THEN RAISE EXCEPTION 'Insufficient stock for product %', p_product_ids[v_i]; END IF; -- 插入订单详情 INSERT INTO fgedu_order_items(order_id, product_id, quantity, price) VALUES(v_order_id, p_product_ids[v_i], p_quantities[v_i], v_product_price); -- 计算总金额 v_total_amount := v_total_amount + (v_product_price * p_quantities[v_i]); -- 更新库存 UPDATE fgedu_products SET stock = stock - p_quantities[v_i] WHERE id = p_product_ids[v_i]; v_i := v_i + 1; END LOOP; -- 更新订单总金额 UPDATE fgedu_orders SET total_amount = v_total_amount WHERE id = v_order_id; -- 设置输出参数 p_order_id := v_order_id; -- 提交事务 EXCEPTION WHEN OTHERS THEN -- 回滚事务 RAISE EXCEPTION 'Error creating order: %', SQLERRM; END; END; -- 修改函数 CREATE OR REPLACE FUNCTION fgedu_get_product_count() RETURNS INTEGER AS $$ BEGIN RETURN (SELECT COUNT(*) FROM fgedu_products WHERE stock > 0);
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试修改后的函数
SELECT fgedu_get_product_count();

— 输出结果
fgedu_get_product_count
————————
3
(1 row)

— 删除函数
DROP FUNCTION IF EXISTS fgedu_get_product_by_id(integer);

— 输出结果
DROP FUNCTION

— 删除存储过程
DROP PROCEDURE IF EXISTS fgedu_create_order(integer, integer[], integer[], integer);

— 输出结果
DROP PROCEDURE

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

— 输出结果
CREATE TABLE

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

— 输出结果
INSERT 0 4

— 创建员工管理函数
CREATE OR REPLACE FUNCTION fgedu_get_employee_by_id(IN p_id INTEGER)
RETURNS fgedu_employees%ROWTYPE
AS $$
DECLARE
v_employee fgedu_employees%ROWTYPE;
BEGIN
SELECT * INTO v_employee FROM fgedu_employees WHERE id = p_id;

IF NOT FOUND THEN
RAISE EXCEPTION ‘Employee with ID % not found’, p_id;
END IF;

RETURN v_employee;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试函数
SELECT * FROM fgedu_get_employee_by_id(1);

— 输出结果
id | name | department | salary | hire_date | active
—-+——+————+———+————+——–
1 | 张三 | 技术部 | 8000.00 | 2026-04-08 | t
(1 row)

— 创建部门员工统计函数
CREATE OR REPLACE FUNCTION fgedu_get_department_stats()
RETURNS TABLE(
department VARCHAR(100),
employee_count INTEGER,
avg_salary NUMERIC(10,2),
min_salary NUMERIC(10,2),
max_salary NUMERIC(10,2)
)
AS $$
BEGIN
RETURN QUERY
SELECT
department,
COUNT(*) AS employee_count,
AVG(salary) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM fgedu_employees
WHERE active = TRUE
GROUP BY department
ORDER BY department;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试函数
SELECT * FROM fgedu_get_department_stats();

— 输出结果
department | employee_count | avg_salary | min_salary | max_salary
————+—————-+————+————+————
技术部 | 2 | 8500.00 | 8000.00 | 9000.00
财务部 | 1 | 7000.00 | 7000.00 | 7000.00
销售部 | 1 | 6000.00 | 6000.00 | 6000.00
(3 rows)

— 创建薪资调整函数
CREATE OR REPLACE FUNCTION fgedu_adjust_salary(
IN p_employee_id INTEGER,
IN p_percentage NUMERIC(5,2)
)
RETURNS NUMERIC(10,2)
AS $$
DECLARE
v_new_salary NUMERIC(10,2);
BEGIN
UPDATE fgedu_employees
SET salary = salary * (1 + p_percentage / 100)
WHERE id = p_employee_id
RETURNING salary INTO v_new_salary;

IF NOT FOUND THEN
RAISE EXCEPTION ‘Employee with ID % not found’, p_employee_id;
END IF;

RETURN v_new_salary;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试薪资调整函数
SELECT fgedu_adjust_salary(1, 10.00);

— 输出结果
fgedu_adjust_salary
——————–
8800.00
(1 row)

— 查看调整后的薪资
SELECT id, name, salary FROM fgedu_employees WHERE id = 1;

— 输出结果
id | name | salary
—-+——+——–
1 | 张三 | 8800.00
(1 row)

— 创建库存管理存储过程
CREATE OR REPLACE PROCEDURE fgedu_manage_inventory(
IN p_product_id INTEGER,
IN p_quantity INTEGER,
IN p_operation VARCHAR(10) — ‘add’ or ‘remove’
)
AS $$
BEGIN
— 开始事务
BEGIN
IF p_operation = ‘add’ THEN
— 增加库存
UPDATE fgedu_products
SET stock = stock + p_quantity
WHERE id = p_product_id;
ELSIF p_operation = ‘remove’ THEN
— 减少库存
UPDATE fgedu_products
SET stock = stock – p_quantity
WHERE id = p_product_id AND stock >= p_quantity;

IF NOT FOUND THEN
RAISE EXCEPTION ‘Insufficient stock or product not found’;
END IF;
ELSE
RAISE EXCEPTION ‘Invalid operation: %’, p_operation;
END IF;

— 提交事务
EXCEPTION
WHEN OTHERS THEN
— 回滚事务
RAISE EXCEPTION ‘Error managing inventory: %’, SQLERRM;
END;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE PROCEDURE

— 测试存储过程
— 增加库存
CALL fgedu_manage_inventory(1, 10, ‘add’);

— 输出结果
CALL

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

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

— 减少库存
CALL fgedu_manage_inventory(1, 5, ‘remove’);

— 输出结果
CALL

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

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

— 创建批量订单处理存储过程
CREATE OR REPLACE PROCEDURE fgedu_process_batch_orders(
IN p_orders JSONB
)
AS $$
DECLARE
v_order JSONB;
v_customer_id INTEGER;
v_product_ids INTEGER[];
v_quantities INTEGER[];
v_order_id INTEGER;
BEGIN
— 开始事务
BEGIN
— 遍历订单
FOR v_order IN SELECT * FROM jsonb_array_elements(p_orders) LOOP
v_customer_id := (v_order->>’customer_id’)::INTEGER;

— 解析产品和数量
SELECT
ARRAY_AGG((item->>’product_id’)::INTEGER),
ARRAY_AGG((item->>’quantity’)::INTEGER)
INTO v_product_ids, v_quantities
FROM jsonb_array_elements(v_order->’items’) AS item;

— 创建订单
CALL fgedu_create_order(v_customer_id, v_product_ids, v_quantities, v_order_id);

RAISE NOTICE ‘Created order % for customer %’, v_order_id, v_customer_id;
END LOOP;

— 提交事务
EXCEPTION
WHEN OTHERS THEN
— 回滚事务
RAISE EXCEPTION ‘Error processing batch orders: %’, SQLERRM;
END;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE PROCEDURE

— 重新创建订单存储过程
CREATE OR REPLACE PROCEDURE fgedu_create_order(
IN p_customer_id INTEGER,
IN p_product_ids INTEGER[],
IN p_quantities INTEGER[],
OUT p_order_id INTEGER
)
AS $$
DECLARE
v_order_id INTEGER;
v_total_amount NUMERIC(10,2) := 0;
v_i INTEGER := 1;
v_product_price NUMERIC(10,2);
v_product_stock INTEGER;
BEGIN
— 开始事务
BEGIN
— 创建订单
INSERT INTO fgedu_orders(customer_id, total_amount, status)
VALUES(p_customer_id, 0, ‘pending’)
RETURNING id INTO v_order_id;

— 处理订单详情
WHILE v_i <= array_length(p_product_ids, 1) LOOP -- 获取产品信息 SELECT price, stock INTO v_product_price, v_product_stock FROM fgedu_products WHERE id = p_product_ids[v_i]; -- 检查库存 IF v_product_stock < p_quantities[v_i] THEN RAISE EXCEPTION 'Insufficient stock for product %', p_product_ids[v_i]; END IF; -- 插入订单详情 INSERT INTO fgedu_order_items(order_id, product_id, quantity, price) VALUES(v_order_id, p_product_ids[v_i], p_quantities[v_i], v_product_price); -- 计算总金额 v_total_amount := v_total_amount + (v_product_price * p_quantities[v_i]); -- 更新库存 UPDATE fgedu_products SET stock = stock - p_quantities[v_i] WHERE id = p_product_ids[v_i]; v_i := v_i + 1; END LOOP; -- 更新订单总金额 UPDATE fgedu_orders SET total_amount = v_total_amount WHERE id = v_order_id; -- 设置输出参数 p_order_id := v_order_id; -- 提交事务 EXCEPTION WHEN OTHERS THEN -- 回滚事务 RAISE EXCEPTION 'Error creating order: %', SQLERRM; END; END; $$ LANGUAGE plpgsql; -- 输出结果 CREATE PROCEDURE -- 测试批量订单处理 DO $$ BEGIN CALL fgedu_process_batch_orders('[{ "customer_id": 1, "items": [ {"product_id": 1, "quantity": 1}, {"product_id": 3, "quantity": 1} ] }, { "customer_id": 2, "items": [ {"product_id": 2, "quantity": 2} ] }]'); END; $$; -- 输出结果 NOTICE: Created order 2 for customer 1 NOTICE: Created order 3 for customer 2 DO -- 查看订单 SELECT * FROM fgedu_orders; -- 输出结果 id | customer_id | total_amount | status | created_at ----+-------------+--------------+---------+---------------------------- 1 | 1 | 20997.00| pending | 2026-04-08 01:45:00 2 | 1 | 22998.00| pending | 2026-04-08 02:00:00 3 | 2 | 9998.00| pending | 2026-04-08 02:00:00 (3 rows) -- 查看订单详情 SELECT * FROM fgedu_order_items WHERE order_id IN (2, 3); -- 输出结果 id | order_id | product_id | quantity | price | created_at ----+----------+------------+----------+--------+---------------------------- 3 | 2 | 1 | 1 | 7999.00| 2026-04-08 02:00:00 4 | 2 | 3 | 1 | 14999.00| 2026-04-08 02:00:00 5 | 3 | 2 | 2 | 4999.00| 2026-04-08 02:00:00 (3 rows)

— 创建销售分析函数
CREATE OR REPLACE FUNCTION fgedu_sales_analysis(
IN p_start_date DATE,
IN p_end_date DATE
)
RETURNS TABLE(
period VARCHAR(20),
total_orders INTEGER,
total_amount NUMERIC(10,2),
avg_order_amount NUMERIC(10,2),
top_product VARCHAR(100)
)
AS $$
DECLARE
v_period VARCHAR(20);
v_total_orders INTEGER;
v_total_amount NUMERIC(10,2);
v_avg_order_amount NUMERIC(10,2);
v_top_product VARCHAR(100);
BEGIN
— 按日期范围分析
FOR v_period IN
SELECT DISTINCT TO_CHAR(created_at, ‘YYYY-MM’) AS period
FROM fgedu_orders
WHERE created_at BETWEEN p_start_date AND p_end_date
ORDER BY period
LOOP
— 计算订单数量和总金额
SELECT
COUNT(*),
SUM(total_amount),
AVG(total_amount)
INTO v_total_orders, v_total_amount, v_avg_order_amount
FROM fgedu_orders
WHERE TO_CHAR(created_at, ‘YYYY-MM’) = v_period
AND created_at BETWEEN p_start_date AND p_end_date;

— 查找Top产品
SELECT p.name INTO v_top_product
FROM fgedu_order_items oi
JOIN fgedu_products p ON oi.product_id = p.id
JOIN fgedu_orders o ON oi.order_id = o.id
WHERE TO_CHAR(o.created_at, ‘YYYY-MM’) = v_period
AND o.created_at BETWEEN p_start_date AND p_end_date
GROUP BY p.name
ORDER BY SUM(oi.quantity * oi.price) DESC
LIMIT 1;

— 返回结果
period := v_period;
total_orders := v_total_orders;
total_amount := v_total_amount;
avg_order_amount := v_avg_order_amount;
top_product := v_top_product;

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

— 输出结果
CREATE FUNCTION

— 测试销售分析函数
SELECT * FROM fgedu_sales_analysis(‘2026-01-01’, ‘2026-12-31’);

— 输出结果
period | total_orders | total_amount | avg_order_amount | top_product
———+————–+————–+——————+—————
2026-04 | 3 | 53993.00| 17997.67 | MacBook Pro
(1 row)

— 创建数据同步存储过程
CREATE OR REPLACE PROCEDURE fgedu_sync_data(
IN p_source_table TEXT,
IN p_target_table TEXT,
IN p_condition TEXT DEFAULT NULL
)
AS $$
DECLARE
v_sql TEXT;
BEGIN
— 构建同步SQL
v_sql := ‘INSERT INTO ‘ || quote_ident(p_target_table) || ‘
SELECT * FROM ‘ || quote_ident(p_source_table);

IF p_condition IS NOT NULL THEN
v_sql := v_sql || ‘ WHERE ‘ || p_condition;
END IF;

— 执行同步
EXECUTE v_sql;

RAISE NOTICE ‘Synced data from % to %’, p_source_table, p_target_table;
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION ‘Error syncing data: %’, SQLERRM;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE PROCEDURE

— 创建目标表
CREATE TABLE fgedu_products_backup (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price NUMERIC(10,2) NOT NULL,
stock INTEGER NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);

— 输出结果
CREATE TABLE

— 测试数据同步
CALL fgedu_sync_data(‘fgedu_products’, ‘fgedu_products_backup’);

— 输出结果
NOTICE: Synced data from fgedu_products to fgedu_products_backup
CALL

— 查看同步结果
SELECT * FROM fgedu_products_backup;

— 输出结果
id | name | price | stock | created_at
—-+———–+——–+——-+—————————-
1 | iPhone 15 | 7999.00| 102 | 2026-04-08 01:30:00
2 | iPad Air | 4999.00| 47 | 2026-04-08 01:30:00
3 | MacBook Pro|14999.00| 29 | 2026-04-08 01:30:00
(3 rows)

— 创建定期清理存储过程
CREATE OR REPLACE PROCEDURE fgedu_cleanup_old_data(
IN p_table_name TEXT,
IN p_date_column TEXT,
IN p_days INTEGER
)
AS $$
DECLARE
v_sql TEXT;
BEGIN
— 构建清理SQL
v_sql := ‘DELETE FROM ‘ || quote_ident(p_table_name) || ‘
WHERE ‘ || quote_ident(p_date_column) || ‘ < NOW() - ' || p_days || ' * INTERVAL ''1 day'''; -- 执行清理 EXECUTE v_sql; RAISE NOTICE 'Cleaned up data in % older than % days', p_table_name, p_days; EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION 'Error cleaning up data: %', SQLERRM; END; $$ LANGUAGE plpgsql; -- 输出结果 CREATE PROCEDURE -- 测试数据清理 CALL fgedu_cleanup_old_data('fgedu_orders', 'created_at', 365); -- 输出结果 NOTICE: Cleaned up data in fgedu_orders older than 365 days CALL