Debian 环境下 PostgreSQL 函数开发实战
一 环境准备与工具链
- 确认数据库版本与文档路径：在 psql 中执行 SELECT version(); 获取版本号，然后查阅对应版本的官方文档（例如 PostgreSQL 12 的 C 语言函数章节）。开发 C 语言函数需安装服务器开发头文件与构建工具，在 Debian/Ubuntu 上执行：sudo apt-get install postgresql-server-dev-<版本> 与构建工具（如 make、gcc）。若使用 C++，可安装 libpqxx-dev；若仅连接数据库而非写服务器端函数，可安装 libpq-dev。以上准备可避免编译时报 “postgres.h: No such file or directory” 等典型错误。
二 快速上手 PL/pgSQL 函数
- 基本语法骨架：CREATE OR REPLACE FUNCTION 名称(参数列表) RETURNS 返回类型 AS $$ … $$ LANGUAGE plpgsql; 适合数据封装、业务规则与事务内逻辑。示例：两数相加、返回表集、插入并返回主键、带 OUT 参数与异常处理的“过程风格”函数。
- 示例 1 两数相加（SQL 函数）
- CREATE OR REPLACE FUNCTION add_two_numbers(a integer, b integer)
RETURNS integer AS $$
SELECT a + b;
$$ LANGUAGE SQL;
- 调用：SELECT add_two_numbers(5, 3);
- 示例 2 返回表集（SETOF）
- CREATE OR REPLACE FUNCTION get_planets()
RETURNS SETOF planets AS $$
SELECT * FROM planets;
$$ LANGUAGE SQL;
- 调用：SELECT * FROM get_planets() WHERE id = 1;
- 示例 3 插入并返回新 ID（PL/pgSQL）
- CREATE OR REPLACE FUNCTION add_planet(name text)
RETURNS bigint AS $$
DECLARE
new_row bigint;
BEGIN
INSERT INTO planets(name) VALUES (add_planet.name) RETURNING id INTO new_row;
RETURN new_row;
END;
$$ LANGUAGE plpgsql;
- 调用：SELECT add_planet(‘Jakku’);
- 示例 4 带 OUT 参数与异常处理（过程风格）
- CREATE OR REPLACE FUNCTION plus_and_minus(
IN a INTEGER, IN b NUMERIC,
OUT c NUMERIC, OUT d NUMERIC
)
AS $$
BEGIN
c := a - b;
d := a + b;
END;
$$ LANGUAGE plpgsql;
- 调用：SELECT plus_and_minus(7, 5); 或 SELECT * FROM plus_and_minus(7, 5);
- 示例 5 带异常与行数统计
- CREATE OR REPLACE FUNCTION p_dwa_erp_ledger_jq_month_new(
v_mouth varchar(8),
OUT v_retcode text, OUT v_retinfo text, OUT v_row_num integer
)
AS $BODY$
BEGIN
INSERT INTO table_new(id, name, age)
SELECT t.id, t.name, m.age
FROM student t, employees m
WHERE t.id = m.id;
GET DIAGNOSTICS v_row_num := ROW_COUNT;
v_retcode := ‘SUCCESS’;
v_retinfo := ‘结束’;
EXCEPTION
WHEN OTHERS THEN
v_retcode := ‘FAIL’;
v_retinfo := SQLERRM;
END;
$BODY$ LANGUAGE plpgsql;
- 调用：SELECT * FROM p_dwa_erp_ledger_jq_month_new(‘12’);
- 安全与权限要点
- 默认以调用者权限执行（SECURITY INVOKER）。若使用 SECURITY DEFINER，务必显式设置 search_path，避免搜索路径劫持与权限提升风险。
三 C 语言函数与扩展开发
- 适用场景与优势：需要极致性能、访问服务器内部 API、实现自定义数据类型/操作符/索引支持等。C 函数以共享库（.so）形式由数据库动态加载，遵循 PostgreSQL 的 C 函数接口规范（包含 PG_FUNCTION_INFO_V1、Datum、宏如 PG_GETARG_INT32、返回 Int32GetDatum 等）。
- 最小示例（add 函数）
- 头文件与宏：#include “postgres.h”、#include “fmgr.h”、PG_MODULE_MAGIC;、PG_FUNCTION_INFO_V1(add_function);
- 函数体：Datum add_function(PG_FUNCTION_ARGS) { int32 a = PG_GETARG_INT32(0); int32 b = PG_GETARG_INT32(1); return Int32GetDatum(a + b); }
- 编译为共享库（示例）：gcc -fPIC -shared -o add_func.so add_func.c -I/usr/include/postgresql/<版本>/server
- 在数据库中创建函数：CREATE FUNCTION add_function(integer, integer) RETURNS integer AS ‘add_func’, ‘add_function’ LANGUAGE C;
- 扩展骨架与构建
- 目录结构：my_extension/my_extension.control、my_extension/src/my_extension.c
- 控制文件示例：Name: my_extension Version: 1.0 Description: A simple example Author: You License: PostgreSQL
- 可使用 pg_regress 进行回归测试，便于发布与持续集成。
- 常见坑位
- 编译时找不到头文件：安装对应版本的 server-dev 包，并正确设置包含路径。
- 符号导出与调用约定：遵循 PostgreSQL C 接口规范，使用 PG_FUNCTION_INFO_V1 与正确的 Datum 返回宏。
四 触发器与 Go 语言实践
- 触发器基础：触发器是事件处理器，可在 INSERT/UPDATE/DELETE/TRUNCATE 前后、逐行或逐语句执行；常用于审计、约束与派生列计算。
- 使用 Go 编写触发器（c-shared）
- 构建共享库：go build -o mytrigger.so -buildmode=c-shared mytrigger.go
- C 接口桥接：在 Go 文件中引入 “C”，导出函数（//export mytrigger），并通过 C 结构（如 FunctionCallInfoData、TriggerData）访问触发上下文与行数据；可调用 elog_info 输出日志、读取列值、在 BEFORE 触发器中修改 NEW 行数据。
- 在数据库中创建触发器函数（LANGUAGE C）并绑定到表事件，注意触发器函数签名与返回类型需符合 PostgreSQL 触发器协议。
五 部署测试与运维要点
- 版本匹配与兼容性：扩展与 C 函数需与目标数据库主版本匹配（头文件与 ABI）。在升级 PostgreSQL 主版本后，需重新编译扩展与 C 函数。
- 权限与安全
- 函数默认 SECURITY INVOKER；使用 SECURITY DEFINER 时显式设置 search_path，仅授予必要权限，避免提升风险。
- 调试与日志
- C/Go 层可使用 PostgreSQL 提供的日志接口（如 elog/ereport）输出诊断信息；SQL/PL/pgSQL 可用 RAISE 与 GET DIAGNOSTICS 获取上下文与行数统计。
- 回归测试
- 使用 pg_regress 编写 SQL 用例，覆盖正常路径、边界与异常路径，确保升级与重构的稳定性。
