PostgreSQL PL/pgSQL 开发

PL/pgSQL 基础

1. 什么是 PL/pgSQL

PL/pgSQL 是 PostgreSQL 的过程化编程语言，它允许你在数据库中创建：

函数（Functions）
存储过程（Procedures，PostgreSQL 11+）
触发器（Triggers）
匿名块（Anonymous Blocks）

2. PL/pgSQL 基本结构

PL/pgSQL 代码的基本结构如下：

sql

-- 匿名块示例
DO $$
DECLARE
    -- 声明变量
    v_count INTEGER;
BEGIN
    -- 执行语句
    SELECT COUNT(*) INTO v_count FROM pg_tables;
    
    -- 输出结果
    RAISE NOTICE '数据库中有 % 个表', v_count;
END $$;

3. 变量声明与赋值

sql

-- 变量声明示例
DO $$
DECLARE
    -- 基本类型变量
    v_integer INTEGER := 10;
    v_text TEXT := 'Hello, PL/pgSQL';
    v_boolean BOOLEAN := true;
    v_date DATE := CURRENT_DATE;
    v_timestamp TIMESTAMP := CURRENT_TIMESTAMP;
    
    -- 引用表类型
    v_emp employees%ROWTYPE;
    
    -- 引用表列类型
    v_emp_id employees.employee_id%TYPE;
    
    -- 数组类型
    v_array INTEGER[] := ARRAY[1, 2, 3, 4, 5];
BEGIN
    -- 赋值方式 1：使用 := 操作符
    v_integer := v_integer + 5;
    
    -- 赋值方式 2：使用 INTO 子句
    SELECT employee_id INTO v_emp_id FROM employees WHERE employee_id = 1;
    
    -- 赋值方式 3：使用 SELECT INTO
    SELECT * INTO v_emp FROM employees WHERE employee_id = 1;
    
    RAISE NOTICE 'v_integer = %, v_emp_id = %', v_integer, v_emp_id;
END $$;

PL/pgSQL 函数开发

1. 创建简单函数

sql

-- 创建一个简单的函数，计算两个数的和
CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN a + b;
END;
$$;

-- 调用函数
SELECT add_numbers(10, 20);

2. 创建返回表的函数

sql

-- 创建返回表的函数
CREATE OR REPLACE FUNCTION get_employees_by_department(p_department_id INTEGER)
RETURNS TABLE(
    employee_id INTEGER,
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    hire_date DATE
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        e.employee_id,
        e.first_name,
        e.last_name,
        e.email,
        e.hire_date
    FROM employees e
    WHERE e.department_id = p_department_id;
END;
$$;

-- 调用返回表的函数
SELECT * FROM get_employees_by_department(10);

3. 创建存储过程

PostgreSQL 11+ 支持存储过程，存储过程可以修改数据库状态并返回结果：

sql

-- 创建存储过程
CREATE OR REPLACE PROCEDURE update_employee_salary(
    p_employee_id INTEGER,
    p_percentage_increase NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 更新工资
    UPDATE employees
    SET salary = salary * (1 + p_percentage_increase / 100)
    WHERE employee_id = p_employee_id;
    
    -- 提交事务（可选，默认自动提交）
    COMMIT;
    
    RAISE NOTICE '员工 % 的工资已增加 %%%', p_employee_id, p_percentage_increase;
END;
$$;

-- 调用存储过程
CALL update_employee_salary(100, 10);

PL/pgSQL 控制结构

1. 条件控制（IF-ELSE）

sql

-- IF-ELSE 示例
CREATE OR REPLACE FUNCTION check_salary_level(p_salary NUMERIC)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    v_level TEXT;
BEGIN
    IF p_salary < 5000 THEN
        v_level := '低级';
    ELSIF p_salary < 10000 THEN
        v_level := '中级';
    ELSE
        v_level := '高级';
    END IF;
    
    RETURN v_level;
END;
$$;

-- 调用函数
SELECT check_salary_level(7500);

2. 循环控制

2.1 LOOP 循环

sql

-- LOOP 循环示例
DO $$
DECLARE
    v_counter INTEGER := 1;
BEGIN
    LOOP
        EXIT WHEN v_counter > 5;
        
        RAISE NOTICE '计数器值: %', v_counter;
        v_counter := v_counter + 1;
    END LOOP;
END $$;

2.2 WHILE 循环

sql

-- WHILE 循环示例
DO $$
DECLARE
    v_counter INTEGER := 1;
BEGIN
    WHILE v_counter <= 5 LOOP
        RAISE NOTICE '计数器值: %', v_counter;
        v_counter := v_counter + 1;
    END LOOP;
END $$;

2.3 FOR 循环

sql

-- FOR 循环示例
DO $$
BEGIN
    -- 数字范围循环
    FOR v_counter IN 1..5 LOOP
        RAISE NOTICE '计数器值: %', v_counter;
    END LOOP;
    
    -- 逆序循环
    FOR v_counter IN REVERSE 5..1 LOOP
        RAISE NOTICE '逆序计数器值: %', v_counter;
    END LOOP;
    
    -- 查询结果循环
    FOR v_rec IN SELECT table_name FROM pg_tables WHERE schemaname = 'public' LIMIT 5 LOOP
        RAISE NOTICE '表名: %', v_rec.table_name;
    END LOOP;
END $$;

3. 异常处理

sql

-- 异常处理示例
CREATE OR REPLACE FUNCTION divide_numbers(a NUMERIC, b NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    -- 尝试执行可能出错的操作
    RETURN a / b;
EXCEPTION
    -- 捕获特定异常
    WHEN division_by_zero THEN
        RAISE NOTICE '除数不能为零';
        RETURN NULL;
    
    -- 捕获所有其他异常
    WHEN OTHERS THEN
        RAISE NOTICE '发生错误: %', SQLERRM;
        RETURN NULL;
END;
$$;

-- 调用函数测试异常处理
SELECT divide_numbers(10, 2);   -- 正常返回 5
SELECT divide_numbers(10, 0);   -- 返回 NULL 并输出提示

PL/pgSQL 触发器开发

1. 创建行级触发器

sql

-- 创建一个日志表
CREATE TABLE employee_audit (
    audit_id SERIAL PRIMARY KEY,
    employee_id INTEGER,
    old_salary NUMERIC,
    new_salary NUMERIC,
    change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    changed_by TEXT DEFAULT CURRENT_USER
);

-- 创建触发器函数
CREATE OR REPLACE FUNCTION audit_employee_salary()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- 仅当工资变化时记录
    IF NEW.salary <> OLD.salary THEN
        INSERT INTO employee_audit (
            employee_id, old_salary, new_salary
        ) VALUES (
            OLD.employee_id, OLD.salary, NEW.salary
        );
    END IF;
    
    RETURN NEW;
END;
$$;

-- 创建行级触发器
CREATE TRIGGER trg_audit_employee_salary
AFTER UPDATE ON employees
FOR EACH ROW
WHEN (NEW.salary <> OLD.salary)
EXECUTE FUNCTION audit_employee_salary();

-- 测试触发器
UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 100;
SELECT * FROM employee_audit;

2. 创建语句级触发器

sql

-- 创建语句级触发器函数
CREATE OR REPLACE FUNCTION trg_statement_example()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_affected_rows INTEGER;
BEGIN
    GET DIAGNOSTICS v_affected_rows = ROW_COUNT;
    
    RAISE NOTICE '语句级触发器: 影响了 % 行', v_affected_rows;
    
    -- 语句级触发器对于 INSERT/UPDATE/DELETE 返回 NULL
    RETURN NULL;
END;
$$;

-- 创建语句级触发器
CREATE TRIGGER trg_statement_after_update_employees
AFTER UPDATE ON employees
EXECUTE FUNCTION trg_statement_example();

-- 测试语句级触发器
UPDATE employees SET salary = salary * 1.02 WHERE department_id = 10;

PL/pgSQL 性能优化

1. 避免不必要的计算

sql

-- 优化前：每次循环都计算相同的值
DO $$
DECLARE
    v_result INTEGER;
BEGIN
    FOR i IN 1..1000000 LOOP
        v_result := i * (SELECT MAX(employee_id) FROM employees);
    END LOOP;
END $$;

-- 优化后：只计算一次最大值
DO $$
DECLARE
    v_result INTEGER;
    v_max_emp_id INTEGER;
BEGIN
    SELECT MAX(employee_id) INTO v_max_emp_id FROM employees;
    
    FOR i IN 1..1000000 LOOP
        v_result := i * v_max_emp_id;
    END LOOP;
END $$;

2. 使用批量操作

sql

-- 优化前：逐行插入
DO $$
DECLARE
    v_counter INTEGER := 1;
BEGIN
    WHILE v_counter <= 1000 LOOP
        INSERT INTO test_table (id, name) VALUES (v_counter, 'Test ' || v_counter);
        v_counter := v_counter + 1;
    END LOOP;
END $$;

-- 优化后：批量插入
DO $$
BEGIN
    INSERT INTO test_table (id, name)
    SELECT 
        generate_series(1, 1000) AS id,
        'Test ' || generate_series(1, 1000) AS name;
END $$;

3. 使用 RETURN QUERY 替代循环

sql

-- 优化前：使用循环构建结果
CREATE OR REPLACE FUNCTION get_employees_optimized(p_department_id INTEGER)
RETURNS TABLE(employee_id INTEGER, full_name TEXT)
LANGUAGE plpgsql
AS $$
DECLARE
    v_rec RECORD;
BEGIN
    FOR v_rec IN 
        SELECT employee_id, first_name || ' ' || last_name AS full_name
        FROM employees
        WHERE department_id = p_department_id
    LOOP
        employee_id := v_rec.employee_id;
        full_name := v_rec.full_name;
        RETURN NEXT;
    END LOOP;
END;
$$;

-- 优化后：使用 RETURN QUERY
CREATE OR REPLACE FUNCTION get_employees_optimized(p_department_id INTEGER)
RETURNS TABLE(employee_id INTEGER, full_name TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        e.employee_id,
        e.first_name || ' ' || e.last_name AS full_name
    FROM employees e
    WHERE e.department_id = p_department_id;
END;
$$;

4. 使用适当的游标策略

sql

-- 使用 SCROLL 游标（可前后移动）
DO $$
DECLARE
    cur_employees CURSOR SCROLL FOR
        SELECT employee_id, first_name, last_name
        FROM employees
        WHERE department_id = 10;
    v_rec RECORD;
BEGIN
    OPEN cur_employees;
    
    -- 获取第一行
    FETCH NEXT FROM cur_employees INTO v_rec;
    RAISE NOTICE '第一行: % %', v_rec.first_name, v_rec.last_name;
    
    -- 获取下一行
    FETCH NEXT FROM cur_employees INTO v_rec;
    RAISE NOTICE '第二行: % %', v_rec.first_name, v_rec.last_name;
    
    -- 回退到前一行
    FETCH PRIOR FROM cur_employees INTO v_rec;
    RAISE NOTICE '回到前一行: % %', v_rec.first_name, v_rec.last_name;
    
    CLOSE cur_employees;
END $$;

PL/pgSQL 最佳实践

1. 命名规范

函数/存储过程：使用 f_ 或 p_ 前缀，如 f_get_employee() 或 p_update_salary()
触发器函数：使用 trg_ 前缀，如 trg_audit_employee()
变量：使用 v_ 前缀，如 v_employee_id
参数：使用 p_ 前缀，如 p_department_id
常量：使用全大写，如 MAX_SALARY

2. 安全性最佳实践

最小权限原则：函数应使用最小必要的权限执行
使用 SECURITY DEFINER 谨慎：仅当必要时使用，避免权限提升
验证输入参数：始终验证用户输入，防止 SQL 注入
避免硬编码敏感信息：如密码、密钥等

3. 可维护性最佳实践

编写清晰的注释：解释函数用途、参数和返回值
保持函数简短：每个函数只做一件事
使用模块化设计：将复杂逻辑拆分为多个小函数
版本控制：使用 CREATE OR REPLACE 确保函数可以更新
测试函数：编写单元测试验证函数行为

4. 性能最佳实践

使用 IMMUTABLE 或 STABLE 标记：帮助 PostgreSQL 优化查询
避免在循环中执行 DML 语句：使用批量操作替代
使用 RETURN QUERY 替代 RETURN NEXT：提高返回结果的性能
合理使用索引：为函数依赖的表创建合适的索引
监控函数性能：使用 pg_stat_statements 监控函数执行情况

PL/pgSQL 调试技术

1. 使用 RAISE 语句

sql

-- 使用不同级别的 RAISE 语句
DO $$
BEGIN
    RAISE DEBUG '调试信息: %', '这是调试级别';
    RAISE LOG '日志信息: %', '这是日志级别';
    RAISE NOTICE '通知信息: %', '这是通知级别';
    RAISE WARNING '警告信息: %', '这是警告级别';
    RAISE EXCEPTION '错误信息: %', '这是错误级别';
END $$;

2. 使用 pgAdmin 调试器

pgAdmin 提供了图形化的 PL/pgSQL 调试器，可以：

设置断点
单步执行代码
查看变量值
检查调用栈

3. 使用自动跟踪

sql

-- 启用自动跟踪
ALTER SYSTEM SET auto_explain.log_min_duration = '100ms';
ALTER SYSTEM SET auto_explain.log_analyze = true;
ALTER SYSTEM SET auto_explain.log_buffers = true;
ALTER SYSTEM SET auto_explain.log_nested_statements = true;

-- 重新加载配置
SELECT pg_reload_conf();

常见问题（FAQ）

Q1：函数和存储过程有什么区别？

A1：函数和存储过程的主要区别：

特性	函数	存储过程
调用方式	`SELECT function_name()`	`CALL procedure_name()`
返回值	必须有返回值	可以没有返回值
事务控制	不能使用 `COMMIT`/`ROLLBACK`	可以使用 `COMMIT`/`ROLLBACK`
副作用	最好只返回结果，不修改数据	可以修改数据库状态
用途	查询数据、计算值	执行数据修改、批量操作

Q2：如何查看 PL/pgSQL 函数的定义？

A2：查看函数定义的方法：

sql

-- 方法 1：使用 pg_proc 和 pg_namespace
SELECT 
    nspname AS schema_name,
    proname AS function_name,
    pg_get_functiondef(p.oid) AS function_definition
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE proname = 'function_name';

-- 方法 2：使用 \df 命令（psql 中）
\df+ function_name

-- 方法 3：使用 information_schema
SELECT routine_definition
FROM information_schema.routines
WHERE routine_name = 'function_name' AND routine_type = 'FUNCTION';

Q3：如何优化 PL/pgSQL 函数的性能？

A3：优化 PL/pgSQL 函数性能的方法：

使用正确的 volatility 标记：

sql

CREATE OR REPLACE FUNCTION f_immutable_example(x INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
IMMUTABLE  -- 此函数结果仅依赖输入参数
AS $$

BEGIN RETURN x * 2; END; $$;


2. **避免在循环中执行查询**：使用批量操作或 `RETURN QUERY`

3. **使用 `LANGUAGE SQL` 替代 `LANGUAGE plpgsql`**：对于简单函数，SQL 函数可能更高效

4. **优化内部查询**：确保函数内部的 SQL 查询使用了适当的索引

5. **减少函数调用次数**：在可能的情况下，将函数逻辑内联到查询中

### Q4：如何处理 PL/pgSQL 中的 NULL 值？

A4：处理 NULL 值的方法：

1. **使用 `COALESCE` 函数**：
```sql
v_value := COALESCE(p_input_value, default_value);

使用 IS NULL 或 IS NOT NULL 判断：

sql

IF p_input_value IS NULL THEN
    -- 处理 NULL 情况
END IF;

使用 NULLIF 函数：

sql

-- 如果 x 等于 y，返回 NULL，否则返回 x
v_result := NULLIF(x, y);

Q5：如何在 PL/pgSQL 中返回多个结果集？

A5：在 PostgreSQL 中，函数不能直接返回多个结果集，但可以：

返回集合类型：返回表类型或数组

使用 OUT 参数：

sql

CREATE OR REPLACE FUNCTION f_multiple_out(
    IN p_id INTEGER,
    OUT o_name TEXT,
    OUT o_value INTEGER
)
LANGUAGE plpgsql
AS $$

BEGIN SELECT name, value INTO o_name, o_value FROM my_table WHERE id = p_id; END; $$;


3. **使用临时表**：在函数中创建临时表，调用者可以查询该表

### Q6：如何监控 PL/pgSQL 函数的执行情况？

A6：监控函数执行情况的方法：

1. **使用 `pg_stat_statements`**：
```sql
-- 安装扩展
CREATE EXTENSION pg_stat_statements;

-- 监控函数执行
SELECT 
    calls,
    total_time,
    mean_time,
    max_time,
    query
FROM pg_stat_statements
WHERE query LIKE '%function_name%'
ORDER BY total_time DESC;

使用 EXPLAIN ANALYZE：

sql

EXPLAIN ANALYZE SELECT * FROM function_name(parameter);

使用 auto_explain 扩展：自动记录慢查询的执行计划

Q7：如何处理 PL/pgSQL 中的大对象？

A7：处理大对象（LOBs）的方法：

使用 bytea 类型：适合存储较小的二进制数据

使用大对象 API：适合存储大型二进制数据

sql

-- 创建大对象
DO $$
DECLARE
    v_loid OID;
    v_fd INTEGER;
BEGIN
    -- 创建新的大对象
    v_loid := lo_creat(0);
    
    -- 打开大对象进行写入
    v_fd := lo_open(v_loid, 2); -- 2 = WRITE
    
    -- 写入数据
    PERFORM lo_write(v_fd, 'Hello, Large Object!');
    
    -- 关闭大对象
    PERFORM lo_close(v_fd);
    
    RAISE NOTICE '创建的大对象 OID: %', v_loid;
END $$;

Q8：如何在 PL/pgSQL 中使用事务？

A8：在 PL/pgSQL 中使用事务的方法：

自动事务管理：PostgreSQL 自动为每个函数调用创建事务

显式事务控制：在存储过程中可以使用 COMMIT 和 ROLLBACK

sql

CREATE OR REPLACE PROCEDURE p_transaction_example()
LANGUAGE plpgsql
AS $$

BEGIN -- 开始事务（可选，默认自动开始）

-- 执行第一个操作
UPDATE table1 SET column1 = value1 WHERE id = 1;

-- 保存点
SAVEPOINT my_savepoint;

-- 执行第二个操作
UPDATE table2 SET column2 = value2 WHERE id = 1;

-- 如果出现错误，回滚到保存点
-- ROLLBACK TO my_savepoint;

-- 提交事务
COMMIT;

EXCEPTION WHEN OTHERS THEN -- 回滚事务 ROLLBACK; RAISE; END; $$;

PostgreSQL PL/pgSQL 开发 ​

PL/pgSQL 基础 ​

1. 什么是 PL/pgSQL ​

2. PL/pgSQL 基本结构 ​

3. 变量声明与赋值 ​

PL/pgSQL 函数开发 ​

1. 创建简单函数 ​

2. 创建返回表的函数 ​

3. 创建存储过程 ​

PL/pgSQL 控制结构 ​

1. 条件控制（IF-ELSE） ​

2. 循环控制 ​

2.1 LOOP 循环 ​

2.2 WHILE 循环 ​

2.3 FOR 循环 ​

3. 异常处理 ​

PL/pgSQL 触发器开发 ​

1. 创建行级触发器 ​

2. 创建语句级触发器 ​

PL/pgSQL 性能优化 ​

1. 避免不必要的计算 ​

2. 使用批量操作 ​

3. 使用 RETURN QUERY 替代循环 ​

4. 使用适当的游标策略 ​

PL/pgSQL 最佳实践 ​

1. 命名规范 ​

2. 安全性最佳实践 ​

3. 可维护性最佳实践 ​

4. 性能最佳实践 ​

PL/pgSQL 调试技术 ​

1. 使用 RAISE 语句 ​

2. 使用 pgAdmin 调试器 ​

3. 使用自动跟踪 ​

常见问题（FAQ） ​

Q1：函数和存储过程有什么区别？ ​

Q2：如何查看 PL/pgSQL 函数的定义？ ​

Q3：如何优化 PL/pgSQL 函数的性能？ ​

Q5：如何在 PL/pgSQL 中返回多个结果集？ ​

Q7：如何处理 PL/pgSQL 中的大对象？ ​

Q8：如何在 PL/pgSQL 中使用事务？ ​