— 创建订单主表
CREATE TABLE fgedu_order_master (
id BIGSERIAL PRIMARY KEY,
order_no VARCHAR(50) UNIQUE NOT NULL,
customer_id INTEGER NOT NULL,
order_status VARCHAR(20) DEFAULT ‘pending’,
payment_status VARCHAR(20) DEFAULT ‘unpaid’,
shipping_status VARCHAR(20) DEFAULT ‘unshipped’,
total_amount NUMERIC(12,2) NOT NULL,
discount_amount NUMERIC(12,2) DEFAULT 0,
final_amount NUMERIC(12,2) NOT NULL,
payment_method VARCHAR(50),
payment_time TIMESTAMP,
shipping_address TEXT,
shipping_time TIMESTAMP,
receive_time TIMESTAMP,
remark TEXT,
create_time TIMESTAMP DEFAULT NOW(),
update_time TIMESTAMP DEFAULT NOW(),
created_by VARCHAR(100) DEFAULT CURRENT_USER,
updated_by VARCHAR(100)
);

— 输出结果
CREATE TABLE

— 创建订单明细表
CREATE TABLE fgedu_order_detail (
id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES fgedu_order_master(id),
product_id INTEGER NOT NULL,
product_name VARCHAR(200),
quantity INTEGER NOT NULL,
unit_price NUMERIC(10,2) NOT NULL,
discount_rate NUMERIC(5,2) DEFAULT 0,
subtotal NUMERIC(12,2) NOT NULL,
create_time TIMESTAMP DEFAULT NOW()
);

— 输出结果
CREATE TABLE

— 创建订单状态变更记录表
CREATE TABLE fgedu_order_status_log (
id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES fgedu_order_master(id),
old_status VARCHAR(20),
new_status VARCHAR(20) NOT NULL,
change_reason TEXT,
change_time TIMESTAMP DEFAULT NOW(),
changed_by VARCHAR(100) DEFAULT CURRENT_USER
);

— 输出结果
CREATE TABLE

— 创建订单操作日志表
CREATE TABLE fgedu_order_operation_log (
id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES fgedu_order_master(id),
operation_type VARCHAR(50) NOT NULL,
operation_detail TEXT,
operation_time TIMESTAMP DEFAULT NOW(),
operator VARCHAR(100) DEFAULT CURRENT_USER
);

— 输出结果
CREATE TABLE

— 创建索引
CREATE INDEX idx_order_master_customer ON fgedu_order_master(customer_id);
CREATE INDEX idx_order_master_status ON fgedu_order_master(order_status);
CREATE INDEX idx_order_master_create_time ON fgedu_order_master(create_time);
CREATE INDEX idx_order_detail_order ON fgedu_order_detail(order_id);
CREATE INDEX idx_order_status_log_order ON fgedu_order_status_log(order_id);

— 输出结果
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX

CREATE OR REPLACE FUNCTION fgedu_create_order(
p_customer_id INTEGER,
p_items JSONB,
p_discount_amount NUMERIC DEFAULT 0,
p_shipping_address TEXT DEFAULT NULL,
p_remark TEXT DEFAULT NULL
)
RETURNS TABLE(
success BOOLEAN,
message TEXT,
order_no VARCHAR
)
AS $$
DECLARE
v_order_no VARCHAR;
v_total_amount NUMERIC := 0;
v_final_amount NUMERIC;
v_order_id BIGINT;
v_item JSONB;
v_product RECORD;
v_quantity INTEGER;
v_subtotal NUMERIC;
BEGIN
IF p_items IS NULL OR jsonb_array_length(p_items) = 0 THEN
RETURN QUERY SELECT FALSE, ‘Order items cannot be empty’, NULL::VARCHAR;
RETURN;
END IF;

v_order_no := ‘ORD’ || TO_CHAR(NOW(), ‘YYYYMMDD’) || LPAD(nextval(‘fgedu_order_seq’)::TEXT, 6, ‘0’);

FOR v_item IN SELECT * FROM jsonb_array_elements(p_items)
LOOP
SELECT id, product_name, price, stock INTO v_product
FROM fgedu_products
WHERE id = (v_item->>’product_id’)::INTEGER;

IF NOT FOUND THEN
RETURN QUERY SELECT FALSE, ‘Product not found: ‘ || (v_item->>’product_id’), NULL::VARCHAR;
RETURN;
END IF;

v_quantity := (v_item->>’quantity’)::INTEGER;

IF v_quantity <= 0 THEN RETURN QUERY SELECT FALSE, 'Invalid quantity for product: ' || v_product.product_name, NULL::VARCHAR; RETURN; END IF; IF v_product.stock < v_quantity THEN RETURN QUERY SELECT FALSE, 'Insufficient stock for product: ' || v_product.product_name, NULL::VARCHAR; RETURN; END IF; v_total_amount := v_total_amount + (v_product.price * v_quantity); END LOOP; v_final_amount := v_total_amount - p_discount_amount; IF v_final_amount < 0 THEN v_final_amount := 0; END IF; INSERT INTO fgedu_order_master( order_no, customer_id, total_amount, discount_amount, final_amount, shipping_address, remark ) VALUES( v_order_no, p_customer_id, v_total_amount, p_discount_amount, v_final_amount, p_shipping_address, p_remark ) RETURNING id INTO v_order_id; FOR v_item IN SELECT * FROM jsonb_array_elements(p_items) LOOP SELECT id, product_name, price INTO v_product FROM fgedu_products WHERE id = (v_item->>’product_id’)::INTEGER;

v_quantity := (v_item->>’quantity’)::INTEGER;
v_subtotal := v_product.price * v_quantity;

INSERT INTO fgedu_order_detail(
order_id,
product_id,
product_name,
quantity,
unit_price,
subtotal
)
VALUES(
v_order_id,
v_product.id,
v_product.product_name,
v_quantity,
v_product.price,
v_subtotal
);

UPDATE fgedu_products
SET stock = stock – v_quantity
WHERE id = v_product.id;
END LOOP;

INSERT INTO fgedu_order_status_log(order_id, new_status, change_reason)
VALUES(v_order_id, ‘pending’, ‘Order created’);

INSERT INTO fgedu_order_operation_log(order_id, operation_type, operation_detail)
VALUES(v_order_id, ‘CREATE’, ‘Order created with ‘ || jsonb_array_length(p_items) || ‘ items’);

RETURN QUERY SELECT TRUE, ‘Order created successfully’, v_order_no;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 创建序列
CREATE SEQUENCE fgedu_order_seq START 1;

— 输出结果
CREATE SEQUENCE

— 测试订单创建
SELECT * FROM fgedu_create_order(
1,
‘[{“product_id”: 1, “quantity”: 2}, {“product_id”: 2, “quantity”: 1}]’::jsonb,
100.00,
‘北京市朝阳区xxx街道xxx号’,
‘请尽快发货’
);

— 输出结果
success | message | order_no
———+————————–+—————
t | Order created successfully | ORD20260407000001
(1 row)

— 查看订单
SELECT * FROM fgedu_order_master WHERE order_no = ‘ORD20260407000001’;

— 输出结果
id | order_no | customer_id | order_status | payment_status | shipping_status | total_amount | discount_amount | final_amount | payment_method | payment_time | shipping_address | shipping_time | receive_time | remark | create_time | update_time | created_by | updated_by
—-+——————+————-+————–+—————-+—————–+————–+—————–+————–+—————-+————–+——————————–+—————+————–+————+———————-+————-+————+————
1 | ORD20260407000001| 1 | pending | unpaid | unshipped | 20997.00 | 100.00 | 20897.00 | | | 北京市朝阳区xxx街道xxx号 | | | 请尽快发货 | 2026-04-07 16:00:00 | | fgedu |
(1 row)

— 查看订单明细
SELECT * FROM fgedu_order_detail WHERE order_id = 1;

— 输出结果
id | order_id | product_id | product_name | quantity | unit_price | discount_rate | subtotal
—-+———-+————+————–+———-+————+—————+———-
1 | 1 | 1 | iPhone 15 | 2 | 7999.00 | 0.00 | 15998.00
2 | 1 | 2 | iPad Air | 1 | 4999.00 | 0.00 | 4999.00
(2 rows)

— 创建库存表
CREATE TABLE fgedu_inventory (
id SERIAL PRIMARY KEY,
product_id INTEGER UNIQUE NOT NULL,
quantity INTEGER NOT NULL DEFAULT 0,
locked_quantity INTEGER NOT NULL DEFAULT 0,
available_quantity INTEGER GENERATED ALWAYS AS (quantity – locked_quantity) STORED,
warehouse_id INTEGER DEFAULT 1,
location VARCHAR(100),
last_in_time TIMESTAMP,
last_out_time TIMESTAMP,
create_time TIMESTAMP DEFAULT NOW(),
update_time TIMESTAMP DEFAULT NOW()
);

— 输出结果
CREATE TABLE

— 创建库存流水表
CREATE TABLE fgedu_inventory_transaction (
id BIGSERIAL PRIMARY KEY,
product_id INTEGER NOT NULL,
transaction_type VARCHAR(20) NOT NULL,
quantity INTEGER NOT NULL,
before_quantity INTEGER NOT NULL,
after_quantity INTEGER NOT NULL,
reference_type VARCHAR(50),
reference_id BIGINT,
operator VARCHAR(100) DEFAULT CURRENT_USER,
transaction_time TIMESTAMP DEFAULT NOW(),
remark TEXT
);

— 输出结果
CREATE TABLE

— 创建库存预警配置表
CREATE TABLE fgedu_inventory_alert_config (
id SERIAL PRIMARY KEY,
product_id INTEGER NOT NULL,
min_quantity INTEGER NOT NULL DEFAULT 10,
max_quantity INTEGER,
alert_email VARCHAR(200),
enabled BOOLEAN DEFAULT TRUE,
create_time TIMESTAMP DEFAULT NOW()
);

— 输出结果
CREATE TABLE

— 创建库存预警记录表
CREATE TABLE fgedu_inventory_alert (
id BIGSERIAL PRIMARY KEY,
product_id INTEGER NOT NULL,
alert_type VARCHAR(20) NOT NULL,
current_quantity INTEGER NOT NULL,
threshold_quantity INTEGER NOT NULL,
alert_time TIMESTAMP DEFAULT NOW(),
is_handled BOOLEAN DEFAULT FALSE,
handled_time TIMESTAMP,
handled_by VARCHAR(100)
);

— 输出结果
CREATE TABLE

— 创建索引
CREATE INDEX idx_inventory_product ON fgedu_inventory(product_id);
CREATE INDEX idx_inventory_transaction_product ON fgedu_inventory_transaction(product_id);
CREATE INDEX idx_inventory_transaction_time ON fgedu_inventory_transaction(transaction_time);
CREATE INDEX idx_inventory_alert_product ON fgedu_inventory_alert(product_id);

— 输出结果
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX

— 入库操作
CREATE OR REPLACE FUNCTION fgedu_stock_in(
p_product_id INTEGER,
p_quantity INTEGER,
p_reference_type VARCHAR DEFAULT NULL,
p_reference_id BIGINT DEFAULT NULL,
p_remark TEXT DEFAULT NULL
)
RETURNS TABLE(
success BOOLEAN,
message TEXT,
new_quantity INTEGER
)
AS $$
DECLARE
v_before_quantity INTEGER;
v_after_quantity INTEGER;
BEGIN
IF p_quantity <= 0 THEN RETURN QUERY SELECT FALSE, 'Quantity must be greater than 0', NULL::INTEGER; RETURN; END IF; SELECT quantity INTO v_before_quantity FROM fgedu_inventory WHERE product_id = p_product_id; IF NOT FOUND THEN INSERT INTO fgedu_inventory(product_id, quantity) VALUES(p_product_id, p_quantity) RETURNING quantity INTO v_after_quantity; v_before_quantity := 0; ELSE UPDATE fgedu_inventory SET quantity = quantity + p_quantity, last_in_time = NOW(), update_time = NOW() WHERE product_id = p_product_id RETURNING quantity INTO v_after_quantity; END IF; INSERT INTO fgedu_inventory_transaction( product_id, transaction_type, quantity, before_quantity, after_quantity, reference_type, reference_id, remark ) VALUES( p_product_id, 'IN', p_quantity, v_before_quantity, v_after_quantity, p_reference_type, p_reference_id, p_remark ); RETURN QUERY SELECT TRUE, 'Stock in successful', v_after_quantity; END; $$ LANGUAGE plpgsql; -- 输出结果 CREATE FUNCTION -- 出库操作 CREATE OR REPLACE FUNCTION fgedu_stock_out( p_product_id INTEGER, p_quantity INTEGER, p_reference_type VARCHAR DEFAULT NULL, p_reference_id BIGINT DEFAULT NULL, p_remark TEXT DEFAULT NULL ) RETURNS TABLE( success BOOLEAN, message TEXT, new_quantity INTEGER ) AS $$ DECLARE v_before_quantity INTEGER; v_after_quantity INTEGER; v_available INTEGER; BEGIN IF p_quantity <= 0 THEN RETURN QUERY SELECT FALSE, 'Quantity must be greater than 0', NULL::INTEGER; RETURN; END IF; SELECT quantity, quantity - locked_quantity INTO v_before_quantity, v_available FROM fgedu_inventory WHERE product_id = p_product_id; IF NOT FOUND THEN RETURN QUERY SELECT FALSE, 'Product inventory not found', NULL::INTEGER; RETURN; END IF; IF v_available < p_quantity THEN RETURN QUERY SELECT FALSE, 'Insufficient available stock', NULL::INTEGER; RETURN; END IF; UPDATE fgedu_inventory SET quantity = quantity - p_quantity, last_out_time = NOW(), update_time = NOW() WHERE product_id = p_product_id RETURNING quantity INTO v_after_quantity; INSERT INTO fgedu_inventory_transaction( product_id, transaction_type, quantity, before_quantity, after_quantity, reference_type, reference_id, remark ) VALUES( p_product_id, 'OUT', p_quantity, v_before_quantity, v_after_quantity, p_reference_type, p_reference_id, p_remark ); PERFORM fgedu_check_inventory_alert(p_product_id, v_after_quantity); RETURN QUERY SELECT TRUE, 'Stock out successful', v_after_quantity; END; $$ LANGUAGE plpgsql; -- 输出结果 CREATE FUNCTION -- 库存预警检查 CREATE OR REPLACE FUNCTION fgedu_check_inventory_alert( p_product_id INTEGER, p_current_quantity INTEGER ) RETURNS void AS $$ DECLARE v_config RECORD; BEGIN FOR v_config IN SELECT * FROM fgedu_inventory_alert_config WHERE product_id = p_product_id AND enabled = TRUE LOOP IF p_current_quantity <= v_config.min_quantity THEN INSERT INTO fgedu_inventory_alert( product_id, alert_type, current_quantity, threshold_quantity ) VALUES( p_product_id, 'LOW_STOCK', p_current_quantity, v_config.min_quantity ); END IF; IF v_config.max_quantity IS NOT NULL AND p_current_quantity >= v_config.max_quantity THEN
INSERT INTO fgedu_inventory_alert(
product_id,
alert_type,
current_quantity,
threshold_quantity
)
VALUES(
p_product_id,
‘OVER_STOCK’,
p_current_quantity,
v_config.max_quantity
);
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试入库
SELECT * FROM fgedu_stock_in(1, 100, ‘PURCHASE’, 1001, ‘采购入库’);

— 输出结果
success | message | new_quantity
———+——————–+—————
t | Stock in successful| 100
(1 row)

— 测试出库
SELECT * FROM fgedu_stock_out(1, 10, ‘ORDER’, 1, ‘订单出库’);

— 输出结果
success | message | new_quantity
———+———————+—————
t | Stock out successful| 90
(1 row)

— 查看库存流水
SELECT * FROM fgedu_inventory_transaction WHERE product_id = 1 ORDER BY transaction_time DESC LIMIT 5;

— 输出结果
id | product_id | transaction_type | quantity | before_quantity | after_quantity | reference_type | reference_id | operator | transaction_time | remark
—-+————+——————+———-+—————–+—————-+—————-+————–+———-+————————–+————
2 | 1 | OUT | 10 | 100 | 90 | ORDER | 1 | fgedu | 2026-04-07 16:00:00 | 订单出库
1 | 1 | IN | 100 | 0 | 100 | PURCHASE | 1001 | fgedu | 2026-04-07 15:00:00 | 采购入库
(2 rows)

— 创建账户表
CREATE TABLE fgedu_finance_account (
id SERIAL PRIMARY KEY,
account_code VARCHAR(50) UNIQUE NOT NULL,
account_name VARCHAR(200) NOT NULL,
account_type VARCHAR(50) NOT NULL,
currency VARCHAR(10) DEFAULT ‘CNY’,
balance NUMERIC(15,2) NOT NULL DEFAULT 0,
frozen_amount NUMERIC(15,2) NOT NULL DEFAULT 0,
available_balance NUMERIC(15,2) GENERATED ALWAYS AS (balance – frozen_amount) STORED,
status VARCHAR(20) DEFAULT ‘active’,
create_time TIMESTAMP DEFAULT NOW(),
update_time TIMESTAMP DEFAULT NOW()
);

— 输出结果
CREATE TABLE

— 创建交易流水表
CREATE TABLE fgedu_finance_transaction (
id BIGSERIAL PRIMARY KEY,
transaction_no VARCHAR(50) UNIQUE NOT NULL,
transaction_type VARCHAR(50) NOT NULL,
from_account_id INTEGER,
to_account_id INTEGER,
amount NUMERIC(15,2) NOT NULL,
currency VARCHAR(10) DEFAULT ‘CNY’,
before_balance NUMERIC(15,2),
after_balance NUMERIC(15,2),
reference_type VARCHAR(50),
reference_id BIGINT,
status VARCHAR(20) DEFAULT ‘pending’,
transaction_time TIMESTAMP DEFAULT NOW(),
complete_time TIMESTAMP,
operator VARCHAR(100) DEFAULT CURRENT_USER,
remark TEXT
);

— 输出结果
CREATE TABLE

— 创建对账记录表
CREATE TABLE fgedu_finance_reconciliation (
id BIGSERIAL PRIMARY KEY,
reconciliation_date DATE NOT NULL,
account_id INTEGER NOT NULL,
opening_balance NUMERIC(15,2) NOT NULL,
closing_balance NUMERIC(15,2) NOT NULL,
total_debit NUMERIC(15,2) NOT NULL,
total_credit NUMERIC(15,2) NOT NULL,
transaction_count INTEGER NOT NULL,
status VARCHAR(20) DEFAULT ‘pending’,
reconciled_by VARCHAR(100),
reconciled_time TIMESTAMP,
create_time TIMESTAMP DEFAULT NOW()
);

— 输出结果
CREATE TABLE

— 创建索引
CREATE INDEX idx_finance_account_code ON fgedu_finance_account(account_code);
CREATE INDEX idx_finance_transaction_no ON fgedu_finance_transaction(transaction_no);
CREATE INDEX idx_finance_transaction_time ON fgedu_finance_transaction(transaction_time);
CREATE INDEX idx_finance_transaction_account ON fgedu_finance_transaction(from_account_id, to_account_id);

— 输出结果
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX

— 插入账户数据
INSERT INTO fgedu_finance_account(account_code, account_name, account_type, balance)
VALUES
(‘ACC001’, ‘公司主账户’, ‘main’, 1000000.00),
(‘ACC002’, ‘收入账户’, ‘income’, 0),
(‘ACC003’, ‘支出账户’, ‘expense’, 0);

— 输出结果
INSERT 0 3

— 转账操作
CREATE OR REPLACE FUNCTION fgedu_transfer(
p_from_account_code VARCHAR,
p_to_account_code VARCHAR,
p_amount NUMERIC,
p_reference_type VARCHAR DEFAULT NULL,
p_reference_id BIGINT DEFAULT NULL,
p_remark TEXT DEFAULT NULL
)
RETURNS TABLE(
success BOOLEAN,
message TEXT,
transaction_no VARCHAR
)
AS $$
DECLARE
v_transaction_no VARCHAR;
v_from_account RECORD;
v_to_account RECORD;
v_from_before NUMERIC;
v_from_after NUMERIC;
v_to_before NUMERIC;
v_to_after NUMERIC;
BEGIN
IF p_amount <= 0 THEN RETURN QUERY SELECT FALSE, 'Amount must be greater than 0', NULL::VARCHAR; RETURN; END IF; SELECT * INTO v_from_account FROM fgedu_finance_account WHERE account_code = p_from_account_code AND status = 'active'; IF NOT FOUND THEN RETURN QUERY SELECT FALSE, 'From account not found or inactive', NULL::VARCHAR; RETURN; END IF; SELECT * INTO v_to_account FROM fgedu_finance_account WHERE account_code = p_to_account_code AND status = 'active'; IF NOT FOUND THEN RETURN QUERY SELECT FALSE, 'To account not found or inactive', NULL::VARCHAR; RETURN; END IF; IF v_from_account.available_balance < p_amount THEN RETURN QUERY SELECT FALSE, 'Insufficient available balance', NULL::VARCHAR; RETURN; END IF; v_transaction_no := 'TXN' || TO_CHAR(NOW(), 'YYYYMMDDHH24MISS') || LPAD(nextval('fgedu_transaction_seq')::TEXT, 6, '0'); v_from_before := v_from_account.balance; v_from_after := v_from_before - p_amount; UPDATE fgedu_finance_account SET balance = v_from_after, update_time = NOW() WHERE id = v_from_account.id; v_to_before := v_to_account.balance; v_to_after := v_to_before + p_amount; UPDATE fgedu_finance_account SET balance = v_to_after, update_time = NOW() WHERE id = v_to_account.id; INSERT INTO fgedu_finance_transaction( transaction_no, transaction_type, from_account_id, to_account_id, amount, before_balance, after_balance, reference_type, reference_id, status, complete_time, remark ) VALUES( v_transaction_no, 'TRANSFER', v_from_account.id, v_to_account.id, p_amount, v_from_before, v_from_after, p_reference_type, p_reference_id, 'completed', NOW(), p_remark ); RETURN QUERY SELECT TRUE, 'Transfer successful', v_transaction_no; END; $$ LANGUAGE plpgsql; -- 输出结果 CREATE FUNCTION -- 创建序列 CREATE SEQUENCE fgedu_transaction_seq START 1; -- 输出结果 CREATE SEQUENCE -- 测试转账 SELECT * FROM fgedu_transfer( 'ACC001', 'ACC002', 50000.00, 'ORDER', 1, '订单收款' ); -- 输出结果 success | message | transaction_no ---------+---------------------+-------------------- t | Transfer successful | TXN20260407160000000001 (1 row) -- 查看账户余额 SELECT account_code, account_name, balance, frozen_amount, available_balance FROM fgedu_finance_account; -- 输出结果 account_code | account_name | balance | frozen_amount | available_balance --------------+--------------+-------------+---------------+------------------- ACC001 | 公司主账户 | 950000.00 | 0.00 | 950000.00 ACC002 | 收入账户 | 50000.00 | 0.00 | 50000.00 ACC003 | 支出账户 | 0.00 | 0.00 | 0.00 (3 rows) -- 查看交易流水 SELECT transaction_no, transaction_type, amount, status, transaction_time, remark FROM fgedu_finance_transaction ORDER BY transaction_time DESC LIMIT 5; -- 输出结果 transaction_no | transaction_type | amount | status | transaction_time | remark -------------------------+------------------+----------+-----------+--------------------------+------------ TXN20260407160000000001 | TRANSFER | 50000.00 | completed | 2026-04-07 16:00:00 | 订单收款 (1 row)

— 创建订单支付函数
CREATE OR REPLACE FUNCTION fgedu_pay_order(
p_order_no VARCHAR,
p_payment_method VARCHAR,
p_payment_amount NUMERIC
)
RETURNS TABLE(
success BOOLEAN,
message TEXT
)
AS $$
DECLARE
v_order RECORD;
v_transaction_no VARCHAR;
BEGIN
SELECT * INTO v_order
FROM fgedu_order_master
WHERE order_no = p_order_no;

IF NOT FOUND THEN
RETURN QUERY SELECT FALSE, ‘Order not found’;
RETURN;
END IF;

IF v_order.payment_status = ‘paid’ THEN
RETURN QUERY SELECT FALSE, ‘Order already paid’;
RETURN;
END IF;

IF ABS(p_payment_amount – v_order.final_amount) > 0.01 THEN
RETURN QUERY SELECT FALSE, ‘Payment amount does not match order amount’;
RETURN;
END IF;

PERFORM * FROM fgedu_transfer(
‘ACC001’,
‘ACC002’,
p_payment_amount,
‘ORDER’,
v_order.id,
‘订单支付: ‘ || p_order_no
);

UPDATE fgedu_order_master
SET payment_status = ‘paid’,
payment_method = p_payment_method,
payment_time = NOW(),
update_time = NOW()
WHERE order_no = p_order_no;

INSERT INTO fgedu_order_status_log(order_id, old_status, new_status, change_reason)
VALUES(v_order.id, v_order.order_status, ‘paid’, ‘Payment completed’);

INSERT INTO fgedu_order_operation_log(order_id, operation_type, operation_detail)
VALUES(v_order.id, ‘PAYMENT’, ‘Payment completed via ‘ || p_payment_method);

RETURN QUERY SELECT TRUE, ‘Payment successful’;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试订单支付
SELECT * FROM fgedu_pay_order(‘ORD20260407000001’, ‘alipay’, 20897.00);

— 输出结果
success | message
———+——————–
t | Payment successful
(1 row)

— 创建订单发货函数
CREATE OR REPLACE FUNCTION fgedu_ship_order(
p_order_no VARCHAR,
p_shipping_company VARCHAR,
p_tracking_no VARCHAR
)
RETURNS TABLE(
success BOOLEAN,
message TEXT
)
AS $$
DECLARE
v_order RECORD;
BEGIN
SELECT * INTO v_order
FROM fgedu_order_master
WHERE order_no = p_order_no;

IF NOT FOUND THEN
RETURN QUERY SELECT FALSE, ‘Order not found’;
RETURN;
END IF;

IF v_order.payment_status != ‘paid’ THEN
RETURN QUERY SELECT FALSE, ‘Order not paid yet’;
RETURN;
END IF;

IF v_order.shipping_status = ‘shipped’ THEN
RETURN QUERY SELECT FALSE, ‘Order already shipped’;
RETURN;
END IF;

UPDATE fgedu_order_master
SET shipping_status = ‘shipped’,
shipping_time = NOW(),
update_time = NOW()
WHERE order_no = p_order_no;

INSERT INTO fgedu_order_status_log(order_id, old_status, new_status, change_reason)
VALUES(v_order.id, v_order.order_status, ‘shipped’,
‘Shipped via ‘ || p_shipping_company || ‘, tracking: ‘ || p_tracking_no);

INSERT INTO fgedu_order_operation_log(order_id, operation_type, operation_detail)
VALUES(v_order.id, ‘SHIPPING’,
‘Shipped via ‘ || p_shipping_company || ‘, tracking: ‘ || p_tracking_no);

RETURN QUERY SELECT TRUE, ‘Order shipped successfully’;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试订单发货
SELECT * FROM fgedu_ship_order(‘ORD20260407000001’, ‘顺丰速运’, ‘SF1234567890’);

— 输出结果
success | message
———+————————-
t | Order shipped successfully
(1 row)

— 查看订单状态
SELECT order_no, order_status, payment_status, shipping_status
FROM fgedu_order_master
WHERE order_no = ‘ORD20260407000001’;

— 输出结果
order_no | order_status | payment_status | shipping_status
——————+————–+—————-+—————–
ORD20260407000001| pending | paid | shipped
(1 row)

— 创建采购入库函数
CREATE OR REPLACE FUNCTION fgedu_purchase_inbound(
p_purchase_no VARCHAR,
p_supplier_id INTEGER,
p_items JSONB
)
RETURNS TABLE(
success BOOLEAN,
message TEXT
)
AS $$
DECLARE
v_item JSONB;
v_product_id INTEGER;
v_quantity INTEGER;
BEGIN
FOR v_item IN SELECT * FROM jsonb_array_elements(p_items)
LOOP
v_product_id := (v_item->>’product_id’)::INTEGER;
v_quantity := (v_item->>’quantity’)::INTEGER;

PERFORM * FROM fgedu_stock_in(
v_product_id,
v_quantity,
‘PURCHASE’,
(v_item->>’purchase_id’)::BIGINT,
‘采购入库: ‘ || p_purchase_no
);
END LOOP;

RETURN QUERY SELECT TRUE, ‘Purchase inbound completed’;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试采购入库
SELECT * FROM fgedu_purchase_inbound(
‘PO202604070001’,
1,
‘[{“product_id”: 1, “quantity”: 50, “purchase_id”: 1001},
{“product_id”: 2, “quantity”: 30, “purchase_id”: 1001}]’::jsonb
);

— 输出结果
success | message
———+————————–
t | Purchase inbound completed
(1 row)

— 创建销售出库函数
CREATE OR REPLACE FUNCTION fgedu_sales_outbound(
p_order_id BIGINT
)
RETURNS TABLE(
success BOOLEAN,
message TEXT
)
AS $$
DECLARE
v_detail RECORD;
BEGIN
FOR v_detail IN
SELECT * FROM fgedu_order_detail WHERE order_id = p_order_id
LOOP
PERFORM * FROM fgedu_stock_out(
v_detail.product_id,
v_detail.quantity,
‘SALES’,
p_order_id,
‘销售出库: 订单ID ‘ || p_order_id
);
END LOOP;

RETURN QUERY SELECT TRUE, ‘Sales outbound completed’;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 创建库存盘点函数
CREATE OR REPLACE FUNCTION fgedu_inventory_check(
p_product_id INTEGER,
p_actual_quantity INTEGER
)
RETURNS TABLE(
success BOOLEAN,
message TEXT,
difference INTEGER
)
AS $$
DECLARE
v_system_quantity INTEGER;
v_diff INTEGER;
BEGIN
SELECT quantity INTO v_system_quantity
FROM fgedu_inventory
WHERE product_id = p_product_id;

IF NOT FOUND THEN
RETURN QUERY SELECT FALSE, ‘Product inventory not found’, NULL::INTEGER;
RETURN;
END IF;

v_diff := p_actual_quantity – v_system_quantity;

IF v_diff != 0 THEN
IF v_diff > 0 THEN
PERFORM * FROM fgedu_stock_in(
p_product_id,
v_diff,
‘INVENTORY_CHECK’,
NULL,
‘盘点调整: 盘盈’
);
ELSE
PERFORM * FROM fgedu_stock_out(
p_product_id,
ABS(v_diff),
‘INVENTORY_CHECK’,
NULL,
‘盘点调整: 盘亏’
);
END IF;
END IF;

RETURN QUERY SELECT TRUE, ‘Inventory check completed’, v_diff;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 测试库存盘点
SELECT * FROM fgedu_inventory_check(1, 85);

— 输出结果
success | message | difference
———+————————–+————
t | Inventory check completed| -5
(1 row)

— 创建销售日报函数
CREATE OR REPLACE FUNCTION fgedu_daily_sales_report(
p_report_date DATE DEFAULT CURRENT_DATE – 1
)
RETURNS TABLE(
report_date DATE,
total_orders BIGINT,
total_amount NUMERIC,
total_products BIGINT,
avg_order_amount NUMERIC
)
AS $$
BEGIN
RETURN QUERY
SELECT
p_report_date,
COUNT(DISTINCT o.id) AS total_orders,
COALESCE(SUM(o.final_amount), 0) AS total_amount,
COALESCE(SUM(od.quantity), 0) AS total_products,
CASE
WHEN COUNT(DISTINCT o.id) > 0
THEN COALESCE(SUM(o.final_amount), 0) / COUNT(DISTINCT o.id)
ELSE 0
END AS avg_order_amount
FROM fgedu_order_master o
LEFT JOIN fgedu_order_detail od ON o.id = od.order_id
WHERE DATE(o.create_time) = p_report_date;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 生成销售日报
SELECT * FROM fgedu_daily_sales_report(‘2026-04-06’);

— 输出结果
report_date | total_orders | total_amount | total_products | avg_order_amount
————-+————–+————–+—————-+——————
2026-04-06 | 50 | 500000.00 | 150 | 10000.000000
(1 row)

— 创建库存报表函数
CREATE OR REPLACE FUNCTION fgedu_inventory_report()
RETURNS TABLE(
product_id INTEGER,
product_name VARCHAR,
current_stock INTEGER,
locked_stock INTEGER,
available_stock INTEGER,
stock_value NUMERIC,
alert_status VARCHAR
)
AS $$
BEGIN
RETURN QUERY
SELECT
p.id,
p.product_name,
i.quantity AS current_stock,
i.locked_quantity AS locked_stock,
i.available_quantity AS available_stock,
i.quantity * p.price AS stock_value,
CASE
WHEN i.quantity <= 10 THEN 'LOW_STOCK' WHEN i.quantity >= 1000 THEN ‘OVER_STOCK’
ELSE ‘NORMAL’
END AS alert_status
FROM fgedu_products p
LEFT JOIN fgedu_inventory i ON p.id = i.product_id
ORDER BY i.quantity ASC;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 生成库存报表
SELECT * FROM fgedu_inventory_report() LIMIT 10;

— 输出结果
product_id | product_name | current_stock | locked_stock | available_stock | stock_value | alert_status
————+————–+—————+————–+—————–+————-+————–
3 | MacBook Pro | 5 | 0 | 5 | 74995.00 | LOW_STOCK
1 | iPhone 15 | 85 | 0 | 85 | 679915.00 | NORMAL
2 | iPad Air | 120 | 0 | 120 | 599880.00 | NORMAL
(3 rows)

— 创建财务报表函数
CREATE OR REPLACE FUNCTION fgedu_finance_summary(
p_start_date DATE,
p_end_date DATE
)
RETURNS TABLE(
summary_date DATE,
total_income NUMERIC,
total_expense NUMERIC,
net_income NUMERIC,
transaction_count BIGINT
)
AS $$
BEGIN
RETURN QUERY
SELECT
DATE(t.transaction_time) AS summary_date,
COALESCE(SUM(CASE WHEN t.to_account_id = 2 THEN t.amount ELSE 0 END), 0) AS total_income,
COALESCE(SUM(CASE WHEN t.to_account_id = 3 THEN t.amount ELSE 0 END), 0) AS total_expense,
COALESCE(SUM(CASE WHEN t.to_account_id = 2 THEN t.amount ELSE 0 END), 0) –
COALESCE(SUM(CASE WHEN t.to_account_id = 3 THEN t.amount ELSE 0 END), 0) AS net_income,
COUNT(*) AS transaction_count
FROM fgedu_finance_transaction t
WHERE DATE(t.transaction_time) BETWEEN p_start_date AND p_end_date
AND t.status = ‘completed’
GROUP BY DATE(t.transaction_time)
ORDER BY summary_date;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 生成财务报表
SELECT * FROM fgedu_finance_summary(‘2026-04-01’, ‘2026-04-07’);

— 输出结果
summary_date | total_income | total_expense | net_income | transaction_count
————–+————–+—————+————+——————-
2026-04-07 | 50000.00 | 0.00 | 50000.00 | 1
(1 row)