PostgreSQL 使用 Python 和 JavaScript 编写自定义函数指南

PG 类型到 Python 类型的映射通常是自动的，但需要注意边界情况。例如，NULL 在 PG 中对应 Python 的 None，日期时间对象会转换为 Python 的 datetime 实例。

3.3 访问数据库（SPI）

如果需要在函数内查询其他表，可以使用 SPI 模块：

import spi

def get_user_count():
    result = spi.execute("SELECT count(*) FROM users")
    return result[0]["count"]

这种方式比多次往返应用层更高效，但要注意事务控制。

3.4 异常处理

PG 函数抛出异常会中断当前事务。建议捕获 Python 异常并转换为 PG 错误信息：

try:
    # risky operation
except Exception as e:
    raise spi.error(f"Processing failed: {e}")

3.5 实战案例

案例 1：正则提取邮箱

import re

CREATE OR REPLACE FUNCTION extract_emails(text_input text)
RETURNS text[] AS $$
    pattern = r'\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}\b'
    return re.findall(pattern, text_input)
$$ LANGUAGE plpythonu;

案例 2：调用 scikit-learn 模型（需预加载）

由于每次调用都加载模型开销大，建议在初始化时缓存模型对象：

import pickle

# 假设模型已序列化存储
_model = None

def load_model():
    global _model
    if _model is None:
        with open('/path/to/model.pkl', 'rb') as f:
            _model = pickle.load(f)
    return _model

四、PL/V8 详解

4.1 基础函数结构

JS 函数定义类似：

CREATE OR REPLACE FUNCTION multiply(a int, b int)
RETURNS int AS $$
    return a * b;
$$ LANGUAGE plv8;

4.2 数据类型映射

数字类型通常直接映射，布尔值对应 true/false。数组和对象在 PG 中表现为 jsonb 或特定 PG 类型。

4.3 访问数据库（plv8.execute）

function getUserCount() {
    var res = plv8.execute("SELECT count(*) FROM users");
    return res[0].count;
}

4.4 JSON 处理优势

JS 对 JSON 的原生支持使得处理嵌套结构非常方便，无需像 SQL 那样层层嵌套函数。

4.5 实战案例

案例 1：动态 JSON 构建

function build_profile(name, age) {
    return JSON.stringify({ name: name, age: age });
}

案例 2：Lodash 风格工具函数（需内联库）

若需使用 Lodash，需在启动脚本中引入，或使用 CDN 加载（视部署环境而定）。

五、高级技巧与优化

5.1 全局变量与缓存（PL/Python）

利用模块级变量缓存热点数据，减少重复 IO。但需注意多进程并发下的线程安全问题。

5.2 返回结果集（SETOF）

当函数需要返回多行数据时，使用 SETOF 类型。

PL/Python:

def get_rows():
    for i in range(5):
        yield {"id": i, "val": i*2}

PL/V8:

function get_rows() {
    for (var i = 0; i < 5; i++) {
        yield { id: i, val: i * 2 };
    }
}

5.3 表函数（TABLE）

配合 TABLE 关键字定义返回结构，适合复杂业务逻辑封装。

六、安全与限制

6.1 沙箱机制

plpythonu 默认无沙箱，意味着代码可以访问系统资源。务必审查输入参数，防止注入攻击。

6.2 安全最佳实践

1. 最小权限原则：仅授予必要权限。
2. 输入验证：所有外部传入数据必须清洗。
3. 定期审计：检查函数定义变更。

6.3 资源限制

设置 work_mem 和 statement_timeout 防止长耗时函数拖垮数据库。

七、性能对比与选型建议

7.1 微基准测试（100 万次调用）

简单数学运算中，PL/pgSQL 最快；涉及复杂逻辑或库调用时，PL/Python 和 PL/V8 差异不大，主要取决于语言生态。

7.2 选型决策树

• 纯数值计算：PL/pgSQL
• 文本/NLP/ML：PL/Python
• Web 全栈团队：PL/V8

八、调试与维护

8.1 日志输出

PL/Python:

import logging
logging.info("Debug info")

PL/V8:

console.log("Debug info");

需配置 log_min_messages 查看。

8.2 查看函数定义

SELECT prosrc FROM pg_proc WHERE proname = 'your_func';

8.3 版本管理

建议将函数定义纳入 Git 管理，配合迁移脚本（如 Flyway 或 Liquibase）发布。

九、典型应用场景总结

1. ETL 预处理：在入库前清洗数据。
2. 实时风控：结合规则引擎快速判断。
3. 报表聚合：减少应用层计算压力。
4. AI 推理：低延迟模型预测。

掌握这些扩展能力，能让 PostgreSQL 不仅仅是一个存储引擎，更成为一个强大的计算平台。