Debian PostgreSQL函数库使用教程

PostgreSQL函数库是扩展数据库功能的核心组件，允许开发者通过自定义逻辑封装重复操作。本文以Debian系统为例，介绍内置函数库的使用及自定义函数（PL/pgSQL、C语言）的创建与管理，覆盖从基础到进阶的关键场景。

一、内置函数库快速入门

PostgreSQL内置了丰富的函数库，涵盖字符串、数值、日期、聚合、条件、数组、JSON等类别，无需额外安装即可使用。以下是常见函数示例：

1. 字符串函数

• CONCAT：连接多个字符串。
  SELECT CONCAT('Hello', ' ', 'World'); → 输出Hello World
• SUBSTRING：提取字符串片段（语法：SUBSTRING(string FROM start [FOR length])）。
  SELECT SUBSTRING('Hello World' FROM 7 FOR 5); → 输出World
• LOWER/UPPER：转换字符串大小写。
  SELECT LOWER('Hello World'); → 输出hello world
SELECT UPPER('Hello World'); → 输出HELLO WORLD

2. 数值函数

• ABS：返回数值绝对值。
  SELECT ABS(-10); → 输出10
• ROUND：四舍五入（可指定小数位数）。
  SELECT ROUND(3.14159, 2); → 输出3.14
• SQRT：计算平方根。
  SELECT SQRT(16); → 输出4

3. 日期与时间函数

• NOW：返回当前日期和时间。
  SELECT NOW(); → 输出2025-09-26 14:30:00+08（格式取决于时区）
• CURRENT_DATE：返回当前日期。
  SELECT CURRENT_DATE; → 输出2025-09-26
• DATE_PART：提取日期部分（如年、月、日）。
  SELECT DATE_PART('year', NOW()); → 输出2025

4. 聚合函数

• COUNT：统计行数。
  SELECT COUNT(*) FROM users; → 输出用户表的行数
• SUM：计算数值列总和。
  SELECT SUM(amount) FROM transactions; → 输出交易表的总金额
• AVG：计算数值列平均值。
  SELECT AVG(score) FROM students; → 输出学生表的平均分数

5. 条件函数

• CASE：根据条件返回不同值。
  SELECT CASE WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END FROM users; → 根据年龄返回Adult或Minor
• COALESCE：返回第一个非NULL值。
  SELECT COALESCE(column1, column2, 'Default Value') FROM table; → 若column1为NULL，则返回column2，否则返回column1；若均为NULL，则返回Default Value

6. 数组函数

• ARRAY_AGG：将多行数据聚合成数组。
  SELECT ARRAY_AGG(column_name) FROM table_name; → 输出某列的所有值组成的数组
• UNNEST：将数组展开为多行。
  SELECT UNNEST(array_column) FROM table; → 将数组列的每个元素拆分为单独的行

7. JSON函数

• json_build_object：构建JSON对象。
  SELECT json_build_object('name', 'John', 'age', 30); → 输出{"name": "John", "age": 30}
• json_array_agg：将多行数据聚合成JSON数组。
  SELECT json_array_agg(column_name) FROM table_name; → 输出某列的所有值组成的JSON数组

二、自定义函数（PL/pgSQL）创建与管理

PL/pgSQL是PostgreSQL的默认过程语言，适合编写逻辑复杂的函数（如包含循环、条件判断的操作）。以下是在Debian上的完整流程：

1. 准备工作

确保已安装PostgreSQL及开发工具（用于后续自定义函数）：

sudo apt update
sudo apt install postgresql postgresql-contrib build-essential

2. 连接到PostgreSQL

使用postgres用户连接到数据库（默认数据库名为postgres）：

sudo -u postgres psql

3. 创建函数示例

以下是一个简单的PL/pgSQL函数，用于计算两个整数的和：

CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

• 语法说明：
  • CREATE OR REPLACE FUNCTION：创建或替换已有函数（避免函数已存在时报错）。
  • (a INTEGER, b INTEGER)：输入参数及类型。
  • RETURNS INTEGER：指定返回值类型。
  • AS $$ ... $$：函数体的开始与结束（$$是分隔符，可替换为其他符号，如$func$）。
  • LANGUAGE plpgsql：指定函数语言为PL/pgSQL。

4. 调用函数

使用SELECT语句调用函数：

SELECT add_numbers(1, 2);

输出结果为3。

5. 查看函数源代码

通过pg_proc系统表查看函数的SQL代码：

SELECT proname, prosrc FROM pg_proc WHERE proname = 'add_numbers';

• proname：函数名。
• prosrc：函数源代码。

6. 修改函数

直接使用CREATE OR REPLACE FUNCTION重新创建函数（修改参数、返回值或函数体）：

CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
    RETURN a * b; -- 修改为计算乘积
END;
$$ LANGUAGE plpgsql;

7. 删除函数

使用DROP FUNCTION删除函数（需指定参数类型，避免同名函数冲突）：

DROP FUNCTION add_numbers(integer, integer);

三、自定义函数（C语言）创建与管理

C语言函数适用于对性能要求极高的场景（如复杂算法、底层系统调用）。以下是在Debian上的实现步骤：

1. 安装依赖

安装PostgreSQL开发库（包含postgres.h头文件及链接库）：

sudo apt install libpq-dev

2. 编写C代码

创建一个简单的C函数mydelete.c，用于模拟删除操作（返回固定值1）：

#include "postgres.h"
#include "fmgr.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(mydelete);

Datum
mydelete(PG_FUNCTION_ARGS)
{
    int32 key = PG_GETARG_INT32(0); // 获取输入参数
    // 此处可添加实际删除逻辑（如调用SPI执行SQL）
    PG_RETURN_INT32(1); // 返回操作结果
}

• 关键宏：
  • PG_MODULE_MAGIC：标识模块为PostgreSQL扩展（必需）。
  • PG_FUNCTION_INFO_V1：声明函数信息（版本兼容）。
• 参数获取：使用PG_GETARG_*系列宏（如PG_GETARG_INT32获取整数参数）。
• 返回值：使用PG_RETURN_*系列宏（如PG_RETURN_INT32返回整数）。

3. 编译为共享库

使用gcc编译C代码为共享库（.so文件）：

gcc -fpic -I/usr/include/postgresql/server/ -shared -o mydelete.so mydelete.c

• -fpic：生成位置无关代码（共享库必需）。
• -I/usr/include/postgresql/server/：包含PostgreSQL头文件路径（Debian默认路径）。
• -shared：生成共享库。
• -o mydelete.so：输出文件名。

4. 复制共享库到PostgreSQL目录

将编译后的.so文件复制到PostgreSQL的共享库目录（通常为/usr/lib/postgresql/<version>/lib/）：

sudo cp mydelete.so /usr/lib/postgresql/<version>/lib/

• 替换<version>为PostgreSQL版本号（如15，可通过SELECT version();查看）。

5. 加载扩展

在PostgreSQL中创建扩展（关联共享库）：

CREATE EXTENSION mydelete;

6. 创建函数并使用

创建SQL函数，调用C语言函数：

CREATE FUNCTION delete_record(integer) RETURNS integer
AS 'mydelete', 'mydelete'
LANGUAGE c;

• 'mydelete'：共享库名称（无需后缀.so）。
• 'mydelete'：C函数名称（需与代码中的Datum mydelete(PG_FUNCTION_ARGS)一致）。

7. 调用C函数

使用SELECT语句调用函数：

SELECT delete_record(123);

输出结果为1（模拟删除操作的返回值）。

8. 卸载扩展

若不再需要扩展，可使用DROP EXTENSION卸载：

DROP EXTENSION mydelete;

四、注意事项

1. 权限管理：创建函数需具备相应权限（如CREATE FUNCTION），生产环境建议使用专用用户。
2. 错误处理：PL/pgSQL可使用EXCEPTION块捕获异常，C语言需通过elog函数记录错误。
3. 性能优化：C语言函数适合高频或复杂操作，但开发成本高；PL/pgSQL适合业务逻辑封装。
4. 版本兼容：自定义函数需与PostgreSQL版本匹配（如C函数接口可能随版本变化）。

通过以上步骤，你可在Debian系统上高效使用PostgreSQL函数库，满足从基础到高级的需求。