PL/pgSQL实战指南：从环境搭建到存储过程与触发器开发

1. 环境准备先把 PostgreSQL 跑起来说实话很多人学 PL/pgSQL 卡住的第一关不是语法而是压根没把数据库环境捣鼓好。我见过太多人在网上问“为什么我的 psql 连不上”“为什么初始化失败”点进去一看基本都是安装环节出了岔子。所以这篇实战笔记我先花点篇幅把环境这件事说透你照着做就不会在第一步浪费时间。PostgreSQL 的安装方式官方提供了安装包、源码编译和 Docker 三种主流路线。我个人最推荐的是如果你在 Windows 上直接下载 EDB 的图形化安装包如果你在 Linux 上优先用系统自带的包管理器。至于 Docker适合那些不想污染本机环境的开发者但要注意容器里的数据卷挂载和端口映射问题否则容器一删数据就没了那真是欲哭无泪。以 Windows 为例安装包下载完成后双击运行基本上就是一路 Next。有一步会要求你设置 postgres 超级用户的密码这个密码务必记牢后面连接数据库、创建角色、授予权限都靠它。还有一步是选择端口号默认 5432除非你本机有冲突否则别改。安装完成后你会得到一个 pgAdmin 图形管理工具但我更建议你顺手把 psql 命令行工具加到系统 PATH 环境变量里因为后面很多操作在命令行下效率更高也更容易理解本质。为什么我强调要自己动手从命令行连一次数据库因为这是你理解 PostgreSQL 连接方式的基础。打开终端输入psql -U postgres -h localhost -p 5432回车后会提示输入密码。-U指定用户-h指定主机-p指定端口。如果你是在本机默认安装也可以直接输入psql -U postgres因为默认主机就是 localhost默认端口就是 5432。连接成功后你会看到类似postgres#的提示符这就意味着你已经进入了 PostgreSQL 的命令行交互环境。接下来我们创建一个专门用于练习的数据库和用户。别直接在 postgres 这个超级用户的库里写东西这就像用 root 账号干日常开发风险太大。执行CREATE USER demo_user WITH PASSWORD demo_password; CREATE DATABASE demo_db OWNER demo_user;这两条命令分别创建了一个名为demo_user的数据库用户和一个名为demo_db的数据库并且把数据库的属主指定为demo_user。这样后面我们就在这个独立的用户和数据库里折腾即使把表删了、函数写错了也不会影响到系统库。然后在 psql 里切换连接\c demo_db demo_user\c是 psql 里切换数据库连接的命令后面跟数据库名和用户名。切换成功后提示符会变成demo_db。到这里你的演练场就准备好了。1.1 一张学生成绩表作为入门练习素材学编程语言最怕的就是不知道写什么例子。我这里用一张学生成绩表来贯穿全文它足够简单又能覆盖 PL/pgSQL 里最常用的操作查询、计算、判断、循环、异常处理。CREATE TABLE student_score ( id SERIAL PRIMARY KEY, student_name VARCHAR(50) NOT NULL, subject VARCHAR(50) NOT NULL, score NUMERIC(5, 2) CHECK (score 0 AND score 100) );这张表有四个字段自增主键id、学生姓名student_name、科目subject、分数score。SERIAL是 PostgreSQL 里的自增类型底层会自动创建一个序列很好用。NUMERIC(5, 2)表示总共 5 位数字其中小数点后保留 2 位用来存 0 到 100 之间的分数足够了。CHECK约束用来保证分数不会为负数或者超过 100。插入几条测试数据INSERT INTO student_score (student_name, subject, score) VALUES (张三, 数学, 85.5), (张三, 英语, 92.0), (李四, 数学, 78.0), (李四, 英语, 65.5), (王五, 数学, 88.0), (王五, 英语, 74.5);到这里环境准备完毕。我记得第一次接触 PostgreSQL 的时候光是在安装那一步就卡了半天因为网上教程参差不齐有说装完要配置 pg_hba.conf 的有说要用 pgAdmin 创建服务器的。其实配置和工具都有各自的场景初学阶段只需要老老实实装好、能连上、能建库建表就够了。后面遇到权限问题再去研究配置文件一步步来才不会焦虑。严格来讲PL/pgSQL 是一种可编程语言在 PostgreSQL 里它允许你写函数、写存储过程、写触发器把业务逻辑下沉到数据库层执行。它的语法混合了 SQL、过程语言如变量声明、判断、循环和异常处理用起来有点像 Oracle 的 PL/SQL又有点像 SQL Server 的 T-SQL。但如果你之前没有任何存储过程开发经验也不用慌我把里面的关键点拆成几个部分逐个击破。2. 函数与存储过程先搞清楚概念再说写代码你在网上搜“函数”“存储过程”会看到各种说法有的说函数有返回值存储过程没有有的说函数可以在 SQL 里直接调用存储过程不行还有的说 PostgreSQL 11 之后存储过程有了事务控制能力而函数不行。这些说法都对但不完整。从实际使用的角度我更愿意这么跟你解释它们的关系函数像是一个计算器你给它输入它给你输出适合封装查询和计算逻辑存储过程更像是一条流水线它可以一步步执行多个操作。如果你只是想在 SELECT 语句里做点计算或者查出一批数据然后做点逻辑处理函数就够了。但当你需要在数据库端完成一系列操作比如先插入日志、再更新订单状态、最后清理过期数据而且中间还可能要根据某一步的结果决定后续动作时存储过程更合适。2.1 函数FUNCTION的适用场景函数最大的优势是可以被嵌套在任何 SQL 表达式中。比如SELECT my_function(id) FROM some_table或者WHERE my_function(value) 100。这一点在报表查询、数据加工场景里特别实用你能把自己常用的逻辑封装成一个函数然后在各种 SQL 里反复调用。举个例子我们前面建了学生成绩表现在需要把百分制分数转换成等级。这个逻辑如果只在某一条 SQL 里用可以直接用 CASE WHEN 写但如果很多地方都要用最好封装成一个函数CREATE OR REPLACE FUNCTION score_to_grade(score NUMERIC) RETURNS TEXT AS $$ BEGIN IF score 90 THEN RETURN A; ELSIF score 80 THEN RETURN B; ELSIF score 70 THEN RETURN C; ELSIF score 60 THEN RETURN D; ELSE RETURN F; END IF; END; $$ LANGUAGE plpgsql;这个函数接收一个 NUMERIC 类型的分数返回一个 TEXT 类型的等级。CREATE OR REPLACE FUNCTION的意思是如果这个函数已经存在就用新的定义替换旧的定义这在开发迭代过程中很常用。$$是 PL/pgSQL 函数的定界符用来包裹函数体避免函数体内部的单引号与字符串冲突。创建完成后你就可以在任意 SQL 里调用它了SELECT student_name, subject, score, score_to_grade(score) AS grade FROM student_score;你会发现函数就像是你 SQL 语句里的一个“自定义关键字”怎么用都行。这就是函数存在的核心价值封装计算逻辑降低重复代码。2.2 存储过程PROCEDURE的适用场景存储过程的语法在 PostgreSQL 11 之前其实跟函数一模一样只是没有返回值。但从 11 开始PostgreSQL 引入了真正的存储过程可以用CALL语句调用而且支持在过程中使用事务控制语句如 COMMIT、ROLLBACK。函数不行函数内部不允许提交或回滚事务。存储过程适合干嘛呢最常见的就是批量数据处理。比如我们想对成绩表做一个“成绩归档”操作把 60 分以下的学生记录复制到一张补考名单表里同时更新原表状态。这个过程涉及多步操作而且可能需要事务控制用存储过程就非常自然。CREATE OR REPLACE PROCEDURE archive_failed_students() LANGUAGE plpgsql AS $$ BEGIN CREATE TABLE IF NOT EXISTS failed_students ( id SERIAL PRIMARY KEY, student_name VARCHAR(50), subject VARCHAR(50), score NUMERIC(5, 2), archived_at TIMESTAMP DEFAULT NOW() ); INSERT INTO failed_students (student_name, subject, score) SELECT student_name, subject, score FROM student_score WHERE score 60; COMMIT; END; $$;调用方式不再是 SELECT而是CALL archive_failed_students();顺带说一句CREATE OR REPLACE PROCEDURE的写法在 PostgreSQL 11 之后才支持如果你用的是老版本得先 DROP 再 CREATE比较麻烦这也是为什么我建议直接用新版本的原因。2.3 函数和存储过程怎么选一张表说清楚很多人问我到底是写函数还是写存储过程我这里给一个非常简单的判断标准至少帮你在 80% 的场景下做对选择。对比维度函数FUNCTION存储过程PROCEDURE返回值必须有返回值类型在声明时指定没有返回值以结果集或输出参数方式传递结果调用方式SELECT 语句或表达式内直接调用CALL 语句单独调用事务控制不支持事务提交和回滚支持事务控制COMMIT/ROLLBACK常见用途封装计算逻辑、单条查询、标量计算批量数据处理、多步骤业务逻辑、数据维护SQL 嵌入可在任意 SQL 中嵌套使用不能嵌套在 SQL 中必须使用 CALL性能表现适合频繁调用开销相对可控适合低频但复杂的逻辑避免在循环中调用如果你写的逻辑只是做一个计算、返回一个值选函数如果你要执行一系列操作步骤而且操作之间有关系、有依赖选存储过程。就这么简单。函数不能做事务控制这是很多从 SQL Server 转过来的同学最容易踩坑的地方。在 T-SQL 里你可以在存储过程或函数内部写事务但 PL/pgSQL 里函数是不允许的。所以如果你的业务逻辑真的需要 COMMIT请用存储过程。3. PL/pgSQL 从零到一变量、判断、循环、异常环境搭好了函数和存储过程的区别也讲清楚了接下来该动真格了。这一章我不会像官方文档那样罗列所有语法点而是挑最常见的、写业务逻辑最常用的几个点配合例子让你快速上手。在正式开始之前先建立一个概念PL/pgSQL 是一种块结构语言一个块由声明部分、执行部分、异常处理部分组成。声明部分用DECLARE关键字开始执行部分用BEGIN开始异常处理用EXCEPTION开始。这个结构有点像 Pascal和 C 系语言的函数写法完全不同。我第一次接触时也觉得奇怪但用习惯了会发现这种结构非常清晰变量、逻辑、异常各归其位。打个比方PL/pgSQL 的块结构就像是一间装修好的房间声明部分是进门处的玄关你在这里列出要用的物品清单执行部分像是客厅主要活动都在这里发生异常处理部分是安全出口出了意外可以从这里逃离。3.1 变量声明与赋值光有概念不够动手敲一遍PL/pgSQL 里变量的声明用DECLARE可以声明任何 PostgreSQL 类型包括表字段类型。这里有一个非常棒的特性你可以用表名.字段名%TYPE的写法来声明一个与表字段相同类型的变量这样即使表结构变了你的代码依然能正确运行。同理还有%ROWTYPE可以声明一个与整行记录相同类型的变量。我们直接看一个综合一点的例子。写一个函数传入学生 id返回这个学生的数学成绩和英语成绩的差值CREATE OR REPLACE FUNCTION get_score_diff(p_student_id INT) RETURNS NUMERIC AS $$ DECLARE math_score NUMERIC(5,2); english_score NUMERIC(5,2); diff_score NUMERIC(5,2); BEGIN SELECT score INTO math_score FROM student_score WHERE student_name (SELECT student_name FROM student_score WHERE id p_student_id) AND subject 数学 LIMIT 1; SELECT score INTO english_score FROM student_score WHERE student_name (SELECT student_name FROM student_score WHERE id p_student_id) AND subject 英语 LIMIT 1; IF math_score IS NULL OR english_score IS NULL THEN RAISE EXCEPTION 该学生数学或英语成绩不存在; END IF; diff_score : math_score - english_score; RETURN diff_score; END; $$ LANGUAGE plpgsql;这里面有几个关键点p_student_id是参数名我习惯在参数前加p_前缀来区分普通变量这是 Oracle 开发者的老传统虽然 PostgreSQL 不强制但建议保留因为能在长函数里一眼看出谁是参数、谁是局部变量避免混淆。SELECT ... INTO ...是 PL/pgSQL 里把查询结果赋给变量的标准写法。注意如果查询结果有多行会报错如果没有行变量会被置为 NULL。所以如果你不确定查询必然返回一条记录最好加上LIMIT 1或者用后面的异常处理。RAISE EXCEPTION是抛出异常的语句类似其他语言中的 throw。抛出后函数会终止执行事务也会回滚这点要小心使用。:是 PL/pgSQL 的赋值运算符注意和 SQL 中的等于号区分。写完函数后调用一下SELECT get_score_diff(1);如果一切正常你会看到张三的数学与英语成绩差值即 85.5 - 92.0 -6.50。返回负数说明英语比数学高。这个例子虽然简单但已经涵盖了函数定义、变量声明、SELECT INTO、IF 判断、RAISE EXCEPTION、RETURN 这些最基础的内容。你可以试着改一改比如求平均分、求最高分、判断是否及格多跑几个例子手感和理解都会好很多。3.2 IF、CASE 和循环业务逻辑的三板斧真实业务里函数和存储过程的复杂度主要来源于条件和循环你不可能永远只是算个加减乘除。PL/pgSQL 的IF写法和其他语言差不多关键是必须写成IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF注意是ELSIF不是ELSE IF这一点写错就报语法错误。CASE 语句在 PL/pgSQL 里有两种用法一种是查询里常见的CASE WHEN ... THEN ... END另一种是 PL/pgSQL 的过程化 CASE 语句。简单场景直接用 IF 就够当判断条件比较多、而且多是等值判断时CASE 更清晰。循环也是常客。最常用的有三种FOR ... IN ... LOOP用来遍历一个数值范围FOR record IN SELECT ... LOOP用来遍历查询结果集WHILE condition LOOP用来做条件循环我举一个遍历查询结果的例子。写一个函数根据科目名称返回所有该科目学生的平均分、最高分、最低分并把每个学生的分数等级输出到控制台CREATE OR REPLACE FUNCTION analyze_subject(p_subject VARCHAR) RETURNS TABLE(avg_score NUMERIC, max_score NUMERIC, min_score NUMERIC) AS $$ DECLARE rec RECORD; avg_val NUMERIC(5,2); max_val NUMERIC(5,2); min_val NUMERIC(5,2); BEGIN SELECT AVG(score), MAX(score), MIN(score) INTO avg_val, max_val, min_val FROM student_score WHERE subject p_subject; FOR rec IN SELECT student_name, score, score_to_grade(score) AS grade FROM student_score WHERE subject p_subject ORDER BY score DESC LOOP RAISE NOTICE 学生 %, 分数 %, 等级 %, rec.student_name, rec.score, rec.grade; END LOOP; RETURN QUERY SELECT avg_val, max_val, min_val; END; $$ LANGUAGE plpgsql;这里注意两个地方RETURNS TABLE(...)语法声明函数返回一张表这样函数就可以像表一样被查询。这是 PL/pgSQL 里非常实用的功能相当于把一个动态查询封装成了表函数。RAISE NOTICE会把信息打印到控制台调试代码时非常有用。默认情况下 psql 会显示这些通知信息方便你判断循环是否正确执行。RECORD类型是一个弱类型行变量可以用来接收查询结果集的每一行。它和%ROWTYPE的区别在于%ROWTYPE是绑定到具体表结构上的而RECORD没有固定结构你往里面放什么字段它就能有什么字段。调用这个函数SELECT * FROM analyze_subject(数学);你会在控制台看到每个学生的分数等级通知然后返回一行统计结果平均分、最高分、最低分。循环别滥用。我见过有人在一个函数里用循环逐行 UPDATE 几千条数据这种写法非常慢。如果可能尽量用一条 UPDATE 语句搞定PostgreSQL 的优化器比你手写循环高效得多。循环更适合那种必须逐行判断、逐行处理的场景比如需要调用外部服务、需要根据每行的不同情况做不同的插入操作。3.3 游标处理大数据量的正确姿态数据量大时直接把整个查询结果加载到内存里是个糟糕的主意。虽然FOR rec IN SELECT ...已经做了隐式游标管道化处理但在某些场景下你需要手动控制游标的打开、抓取和关闭。PL/pgSQL 里游标的使用大致分为两步先声明游标变量再使用OPEN、FETCH、CLOSE操作。举个实际场景我们要写一个存储过程把成绩表里的数据逐条迁移到历史表同时给每条数据打上迁移时间戳。CREATE OR REPLACE PROCEDURE migrate_scores() LANGUAGE plpgsql AS $$ DECLARE cur CURSOR FOR SELECT id, student_name, subject, score FROM student_score; rec RECORD; BEGIN CREATE TEMP TABLE IF NOT EXISTS temp_migrated ( id INT, student_name VARCHAR(50), subject VARCHAR(50), score NUMERIC(5,2), migrated_at TIMESTAMP DEFAULT NOW() ); OPEN cur; LOOP FETCH NEXT FROM cur INTO rec; EXIT WHEN NOT FOUND; INSERT INTO temp_migrated (id, student_name, subject, score) VALUES (rec.id, rec.student_name, rec.subject, rec.score); RAISE NOTICE Migrated student %, rec.student_name; END LOOP; CLOSE cur; COMMIT; END; $$;FETCH NEXT FROM ... INTO ...每次抓取一行把这个行数据放进 RECORD 变量里。EXIT WHEN NOT FOUND是标准的循环退出方式当游标没有更多行时自动结束循环。OPEN、FETCH、CLOSE这套组合拳虽然比较繁琐但能让你精确控制每一步尤其适合数据量大到不能一次性载入内存的场景。实际上PostgreSQL 的FOR ... IN SELECT循环底层也会自动使用游标而且内存管理更智能所以除非你要在循环中途暂停、或者在多个函数间共享游标状态否则FOR循环更推荐。把这部分理解成“游标让你知道底层发生了什么”初学者能意识到有这东西存在用到的时候再查语法就比背代码强得多。3.4 异常处理别让程序崩溃才想起来程序不做异常处理跟开车不系安全带一样平时没事出一次事就是大事。PL/pgSQL 的异常处理结构是BEGIN ... EXCEPTION WHEN ... THEN ... END你可以捕获特定的异常也可以捕获所有异常。PostgreSQL 内置了大量异常码常用的有unique_violation唯一约束冲突、division_by_zero除零、not_null_violation非空约束冲突、undefined_table表不存在等。如果你不关心具体是什么错可以直接写WHEN OTHERS THEN。举一个典型的防除零异常的例子写一个函数计算两个数的商如果除数为 0 就返回一个默认值CREATE OR REPLACE FUNCTION safe_divide(p_dividend NUMERIC, p_divisor NUMERIC) RETURNS NUMERIC AS $$ BEGIN RETURN p_dividend / p_divisor; EXCEPTION WHEN division_by_zero THEN RETURN NULL; WHEN others THEN RAISE EXCEPTION Unexpected error: %, SQLERRM; END; $$ LANGUAGE plpgsql;SQLERRM是 PostgreSQL 提供的内置变量存放错误消息文本你可以把它拼到自己的错误信息里方便排查。这种“精确捕获一类异常其他异常另做处理”的写法比一上来就WHEN OTHERS更专业。还有一点要特别提醒异常处理会显著增加性能开销。如果你写了一个非常频繁调用的函数里面又捕获了大量异常系统的性能肯定会受影响。能通过逻辑判断提前规避的异常比如先判断除数是否为 0就尽量别依赖异常处理。异常是最后的防线不是常规的流程控制工具。4. 实战从零搭建一个成绩管理工具集前两章我们打了基础这一章把前面学的知识点串起来做一个成绩管理工具集。这个工具集包含了三个函数和一个存储过程覆盖了 PL/pgSQL 最常见的开发模式。先说设计思路。成绩管理最核心的需求是录入成绩、查询成绩统计、更新成绩、处理不及格名单。我按照这个思路设计四个独立的模块每个模块用 PL/pgSQL 实现最后放在一起就构成了一个可以直接用于小项目的小工具集。4.1 函数一自动生成补考名单考试结束后最费时间的就是整理不及格名单。这个工作交给数据库干净利落。CREATE OR REPLACE FUNCTION generate_retake_list(p_subject VARCHAR) RETURNS TABLE(student_name VARCHAR, score NUMERIC) AS $$ BEGIN RETURN QUERY SELECT s.student_name, s.score FROM student_score s WHERE s.subject p_subject AND s.score 60 ORDER BY s.score ASC; END; $$ LANGUAGE plpgsql;这个函数接收一个科目名称返回所有该科目不及格的学生姓名和分数。注意 RETURN QUERY 后面直接跟 SELECT 语句函数会自动把结果集作为返回表内容返回。这个功能在报表、导出场景里都特别方便。调用方式SELECT * FROM generate_retake_list(英语);4.2 函数二按班级维度统计各科目平均分实际开发中统计需求往往不会那么简单。有时你要按班级分组有时要按年级分组还有可能多个维度交叉统计。虽然这些统计用一条 SQL 也能写但如果统计逻辑特别复杂把它封装成函数会让调用方省心很多。由于我们这张表里没有班级字段我加一个重新建表再插入数据有点啰嗦我们直接把统计函数做得相对通用一点传一个科目名称按分数段统计人数分布。CREATE OR REPLACE FUNCTION score_distribution(p_subject VARCHAR) RETURNS TABLE(grade_range VARCHAR, student_count BIGINT) AS $$ BEGIN RETURN QUERY SELECT CASE WHEN score 90 THEN 优秀(90-100) WHEN score 80 THEN 良好(80-89) WHEN score 70 THEN 中等(70-79) WHEN score 60 THEN 及格(60-69) ELSE 不及格(60) END AS grade_range, COUNT(*) FROM student_score WHERE subject p_subject GROUP BY grade_range ORDER BY grade_range; END; $$ LANGUAGE plpgsql;这个函数的意义在于把成绩分布统计逻辑封装起来应用层只需要调用函数不用关心具体的 CASE WHEN 和 GROUP BY 怎么写。如果以后统计口径变了比如 85 分以上算优秀只需要修改函数内部逻辑所有调用方自动生效。4.3 存储过程日常维护全流程存储过程适合做多步骤的维护工作。我这里设计一个“成绩月报生成流程”它需要完成以下步骤创建月报临时表、计算每个学生的平均分和等级、把结果插入月报表、清理无效数据。CREATE OR REPLACE PROCEDURE generate_monthly_report() LANGUAGE plpgsql AS $$ BEGIN DROP TABLE IF EXISTS monthly_report; CREATE TABLE monthly_report ( student_name VARCHAR(50), avg_score NUMERIC(5,2), grade VARCHAR(2), generated_at TIMESTAMP DEFAULT NOW() ); INSERT INTO monthly_report (student_name, avg_score, grade) SELECT student_name, AVG(score) AS avg_score, score_to_grade(AVG(score)) AS grade FROM student_score GROUP BY student_name ORDER BY avg_score DESC; COMMIT; RAISE NOTICE 月度成绩报告生成完成共 % 名学生, (SELECT COUNT(*) FROM monthly_report); END; $$;这个存储过程把多个 SQL 操作封装成一个整体调用方只需要执行CALL generate_monthly_report();剩下的全部在数据库端完成。RAISE NOTICE能在执行后给出反馈你可以用这个输出机制在调试复杂存储过程时打印中间状态非常有用。4.4 触发器成绩录入时的自动校验与日志很多同学在学 PL/pgSQL 时容易忽略触发器但它在实际生产环境里非常常见。触发器本质上是一种特殊的“事件驱动”的数据库处理机制当某个表的某类操作INSERT、UPDATE、DELETE发生时自动执行预先定义好的函数。我这边做一个简单但实用的触发器在成绩表插入数据后自动往操作日志表写入一条记录。先建日志表CREATE TABLE score_op_log ( id SERIAL PRIMARY KEY, student_name VARCHAR(50), subject VARCHAR(50), old_score NUMERIC(5,2), new_score NUMERIC(5,2), op_type VARCHAR(10), op_time TIMESTAMP DEFAULT NOW() );再写触发器函数。注意触发器的函数必须返回TRIGGER类型并且在函数里可以使用NEW和OLD两个特殊变量分别代表插入/更新后的新行和更新/删除前的旧行。CREATE OR REPLACE FUNCTION log_score_change() RETURNS TRIGGER AS $$ BEGIN IF TG_OP INSERT THEN INSERT INTO score_op_log(student_name, subject, new_score, op_type) VALUES (NEW.student_name, NEW.subject, NEW.score, INSERT); ELSIF TG_OP UPDATE THEN INSERT INTO score_op_log(student_name, subject, old_score, new_score, op_type) VALUES (NEW.student_name, NEW.subject, OLD.score, NEW.score, UPDATE); ELSIF TG_OP DELETE THEN INSERT INTO score_op_log(student_name, subject, old_score, op_type) VALUES (OLD.student_name, OLD.subject, OLD.score, DELETE); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;最后把触发器绑定到成绩表上CREATE TRIGGER trg_score_change AFTER INSERT OR UPDATE OR DELETE ON student_score FOR EACH ROW EXECUTE FUNCTION log_score_change();测试一下INSERT INTO student_score (student_name, subject, score) VALUES (赵六, 物理, 55.0); UPDATE student_score SET score 75.0 WHERE student_name 赵六 AND subject 物理;然后查询日志表SELECT * FROM score_op_log;你会看到两条记录一条 INSERT一条 UPDATE完整记录了这次成绩变动。这种“人过留痕”的操作日志在真实项目中非常重要无论是审计还是排查问题都能派上大用场。如果你是在 PostgreSQL 10 或更早版本上执行上述语句触发器的写法有一点不同需要写成EXECUTE PROCEDURE log_score_change();而不是EXECUTE FUNCTION。虽然在 PostgreSQL 11 起两种写法都支持但PROCEDURE这个写法在触发器的语境里容易和存储过程混淆所以新版本更推荐用FUNCTION关键字。5. 性能、安全与调试那些文档里不写的坑写 PL/pgSQL 函数容易写好难。很多初学者把函数写出来能跑就万事大吉但放到生产环境里一遇大数据量就原形毕露。这一章的内容是我这些年踩坑踩出来的不一定全面但每一条都是真实教训。5.1 函数为什么越跑越慢防止性能炸弹的几条军规在 PL/pgSQL 里最容易遇到的性能问题是“逐行操作”。比如在一个循环里逐条 UPDATE或者重复执行同一条 SELECT 语句。数据库不是编程语言里的数组逐行处理的代价非常高。我见过最夸张的一个案例有人写了一个存储过程循环 5 万次每次执行 UPDATE 语句跑了整整一个多小时才完成。我帮他把循环改造成一条 UPDATE ... FROM ... 的批量写法几秒钟就完成了快了几千倍。那怎么避免性能炸弹几条军规能用集合操作绝不用循环。先想清楚能不能用 JOIN、子查询、UPDATE ... FROM 这类一次性处理方式不行再考虑循环。尽量给函数的参数和变量使用恰当的类型。VARCHAR 别动不动就 255NUMERIC 别动不动就高精度类型越精确计算越快。在 SQL 函数中避免在 SELECT 语句里调用 PL/pgSQL 函数处理大结果集。如果函数被数万行数据触发最好用等价的 SQL 表达式代替。如果是频繁调用的函数考虑加上STABLE或IMMUTABLE标记。这能让查询优化器更好地缓存和优化调用减少不必要的重复计算。大量数据插入前先关闭表的索引和触发器插入完成后再重建。这招在数据迁移场景里特别管用能省掉 90% 的时间。STABLE、IMMUTABLE、VOLATILE这三个标记要深入理解一下。它们告诉 PostgreSQL这个函数在什么情况下返回值会变化。IMMUTABLE表示输入相同则输出永远相同比如score_to_grade(85.0)永远是 B这样 PostgreSQL 可以对它做提前计算和索引优化。STABLE表示同一个查询里返回值不变比如now()。VOLATILE表示每次调用结果都可能不同比如随机数函数。默认是VOLATILE但很多计算型函数其实可以标记为STABLE或IMMUTABLE能给优化器更多发挥空间。5.2 权限管理不要让每个用户都能 DROP 表PostgreSQL 的权限控制很精细但也很容易让人摸不着头脑。很多开发者喜欢用超级用户 postgres 去执行所有操作这在开发环境无所谓生产环境就是灾难。函数和存储过程的权限主要通过REVOKE和GRANT命令控制。默认情况下函数和存储过程的执行权限会授予 PUBLIC意味着所有数据库用户都能调用。如果你写的函数内部有敏感操作比如删除表或转账那就必须限制访问。到这里的逻辑非常直接。你需要创建一个只具有最小权限的角色REVOKE ALL ON FUNCTION generate_retake_list(VARCHAR) FROM PUBLIC; GRANT EXECUTE ON FUNCTION generate_retake_list(VARCHAR) TO demo_user;如果你想让函数以调用者的身份执行默认行为那么函数内部涉及的表的权限由调用者决定。如果你希望函数不管谁来调用都以定义者的身份执行可以在函数定义时加上SECURITY DEFINER。这个特性很强大但也很危险如果你没做好权限控制任何有函数调用权限的人都能绕过自己的表权限去访问高权限数据。我建议初学者先不要碰SECURITY DEFINER默认的SECURITY INVOKER更安全。这里分享一个安全方面的真实教训我刚工作时公司有一个存储过程被开发写成了SECURITY DEFINER里面还偷偷放了 DDL 操作结果一个实习生把这条存储过程在测试环境跑了一次直接把一张核心表删了。还好有备份否则后果不堪设想。权限和函数属性真的不能乱来。5.3 调试技巧RAISE NOTICE 是你最好的朋友PostgreSQL 没有像 SQL Server 那种图形化调试器那么顺手但借助RAISE的各种级别和终端输出足够你排查绝大多数问题。RAISE可以输出不同级别的信息DEBUG、INFO、NOTICE、WARNING、EXCEPTION。调试时最常用的是NOTICE它会输出到客户端控制台。然后是EXCEPTION它会中止执行并抛出错误同时支持RAISE EXCEPTION USING ERRCODE ...指定自定义错误码。什么时候用RAISE我的习惯是在怀疑变量值不对的地方打一行RAISE NOTICE 变量名: %, 变量名;然后执行函数看输出就能定位问题。这比你在脑子里推演要快得多。RAISE NOTICE 当前学生: %, 分数: %, 等级: %, rec.student_name, rec.score, rec.grade;如果函数执行级别不够你还可以用SET client_min_messages TO debug;在 psql 里打开更详细的日志级别。这样连优化器底层的执行计划都能看到一部分。另外如果你使用的参数比较复杂比如传入一个数组或者 JSON用RAISE NOTICE输出时可能显示不全。这时候可以把参数CAST成 TEXT 再输出或者用RAISE NOTICE %, to_json(参数);保证所有信息都能完整呈现。5.4 常见问题速查表我把这几年回答过的关于 PL/pgSQL 的常见问题整理成一张表建议你收藏一下遇到问题先来这里查。问题可能的原因解决思路ERROR: function xxx does not exist参数类型不匹配检查函数名称和参数类型可能是 INT 和 NUMERIC 不匹配ERROR: column x does not exist表结构里没有这个字段或字段名写错查看表结构\d 表名注意大小写敏感问题ERROR: relation x does not exist表不存在或 schema 不对确认表名和 schema必要时加 schema 前缀ERROR: IF/ELSIF syntax error把 ELSIF 写成了 ELSEIF改成ELSIF这是 PL/pgSQL 的关键字ERROR: RETURN QUERY cannot have a RETURN statement函数里同时用了 RETURN QUERY 和 RETURN要么一路用 RETURN QUERY要么用 RETURN 表达式函数执行特别慢循环里做了逐行 SQL 操作改成批量操作或用集合方式重写存储过程里 COMMIT 不起作用PostgreSQL 11 之前不支持存储过程事务升级到 11 以上并确认用 PROCEDURE 而非 FUNCTIONpsql: error: connection refused数据库端口没开或 PostgreSQL 服务没启动检查监听端口和 pg_hba.conf 配置无法创建锁文件权限不够数据目录权限不对通常出现在 Docker 或 Linux 下检查数据目录属主和权限用chown postgres:postgres修正这个表不是万能的但覆盖面还可以至少能帮初学者省去一大部分 Google 的时间。顺便提一句如果遇到psql: error: connection refused你还可以看看 PostgreSQL 服务到底启动没有。Linux 下用systemctl status postgresql查看Windows 下用服务管理器Docker 下用docker ps。有时候服务根本没起来你再怎么配 pg_hba.conf 都是白搭。6. 写在最后的实操体会写 PL/pgSQL 这几年我最大的感受是这个语言不难但非常反直觉。用惯了 Python、Java 这类通用编程语言的人刚接触 PL/pgSQL 时可能会有一种“使不上劲”的感觉——明明很简单的逻辑写出来却啰嗦得很。但当你真正把它用熟了会发现在数据库端完成数据校验、计算、统计、迁移这些操作时PL/pgSQL 的效率和安全性是应用层代码无法比拟的因为数据根本不需要在数据库和应用服务器之间传来传去。我自己平时写 PL/pgSQL 的工作流很简单先在 psql 里写一个简化版本测试通过后加上RAISE NOTICE调试信息逐步增加逻辑每一步都能看到中间结果。这样即使出了问题也能很快定位到是哪一步出了问题。等全部逻辑跑通后再回头删掉调试信息把函数或存储过程正式创建到数据库里。如果你正在学 PostgreSQL我建议你把这篇文章里的例子自己敲一遍边敲边想这个函数的参数为什么这样设计这个存储过程为什么要分步骤执行如果数据量扩大十倍这个逻辑还能跑得动吗带着问题去写代码成长速度会比单纯看文档快得多。从安装数据库开始到建表、写第一个函数、写第一个存储过程、加上触发器、再到考虑权限和性能这一条线走下来你就已经掌握了 PL/pgSQL 最核心的知识框架。剩下的细节比如数组处理、JSON 操作、窗口函数、全文检索、复制与高可用部署都是在这个框架上加砖添瓦。遇到具体需求时再查文档一点都不迟。