一、PostgreSQL程序化开发基础

在CentOS 7/8环境下进行PostgreSQL数据库编程，首先需要确保开发环境完备。建议通过以下命令安装必备组件：

bash sudo yum install postgresql-server postgresql-contrib sudo systemctl enable postgresql

函数与存储过程的本质区别在于：
- 函数(Function)：必须返回值，可参与SQL表达式
- 存储过程(Procedure)：PostgreSQL 11+支持，无返回值要求，侧重业务逻辑封装

二、PL/pgSQL函数开发详解

2.1 基础函数模板

sql
CREATE OR REPLACE FUNCTION public.calculatetax( IN amount NUMERIC, OUT taxamount NUMERIC
) LANGUAGE plpgsql
AS $$
BEGIN
-- 基础税率计算
IF amount <= 5000 THEN
taxamount := amount * 0.05; ELSE taxamount := amount * 0.08 - 150;
END IF;

-- 日志记录（实际项目应使用表日志）
RAISE NOTICE '计税完成：原始金额%, 税额%', amount, tax_amount;

END;
$$;

2.2 进阶开发技巧

1. 参数验证：使用ASSERT或异常处理
   sql BEGIN ASSERT amount > 0, '金额必须为正数'; -- ...业务逻辑... EXCEPTION WHEN others THEN RAISE EXCEPTION '计算失败：%', SQLERRM; END;

2. 动态SQL执行：适用于表名参数化场景
   sql EXECUTE format('SELECT count(*) FROM %I WHERE create_date > $1', table_name) USING start_date;

三、存储过程实战案例

3.1 订单处理流程

sql
CREATE PROCEDURE processorder( orderid INT,
paymentmethod VARCHAR(20) ) LANGUAGE plpgsql AS $$ DECLARE orderstatus VARCHAR(20);
BEGIN
-- 开启事务块（存储过程默认自动提交）
BEGIN
-- 检查订单状态
SELECT status INTO orderstatus FROM orders WHERE id = orderid;

    IF order_status != '待支付' THEN
        RAISE EXCEPTION '订单状态异常';
    END IF;

    -- 支付处理
    INSERT INTO payment_logs(order_id, method, amount)
    SELECT id, payment_method, total_amount 
    FROM orders WHERE id = order_id;

    -- 更新库存
    UPDATE products p
    SET stock = stock - oi.quantity
    FROM order_items oi
    WHERE oi.product_id = p.id AND oi.order_id = order_id;

    -- 修改订单状态
    UPDATE orders SET status = '已完成' WHERE id = order_id;

    COMMIT;
EXCEPTION WHEN others THEN
    ROLLBACK;
    RAISE;
END;

END;
$$;

四、性能优化关键点

1. 计划缓存管理：

   
   
   
   • 使用STABLE/IMMUTABLE标记恒定函数
   • 避免在WHERE条件中调用VOLATILE函数

2. 索引友好设计：sql
   -- 不良实践（索引失效）
   WHERE lower(username) = 'admin';

-- 优化方案
WHERE username ILIKE 'admin';

1. 批量处理优化：sql
   -- 单条处理（性能差）
   FOR rec IN SELECT * FROM large_table LOOP
   -- 处理逻辑
   END LOOP;

-- 批量处理（推荐）
WITH batch AS (
SELECT * FROM largetable LIMIT 1000 ) UPDATE targettable t
SET value = b.value
FROM batch b WHERE t.id = b.id;

五、企业级开发规范

1. 版本控制策略：

   
   
   
   • 每个函数/存储过程单独.sql文件存放
   • 使用CREATE OR REPLACE替代直接DROP

2. 文档注释标准：sql
   /**

   
   
   
   • @func 员工薪资计算
   • @param emp_id 员工ID
   • @param month 计算月份(YYYYMM)
   • @return NUMERIC 应发工资
   • @auth finance_dba
   • @version 1.2
     */
     CREATE FUNCTION calc_salary(...)

3. 权限管理原则：
   sql REVOKE ALL ON FUNCTION sensitive_data() FROM PUBLIC; GRANT EXECUTE ON FUNCTION sensitive_data() TO finance_role;