38、PostgreSQL存储过程与函数：PL/pgSQL、C语言调用存储过程、参数模式（IN/OUT/INOUT）

各位好，今天我们来聊聊PostgreSQL里一个非常核心的话题——存储过程与函数。说实话，我在做嵌入式数据库开发的头两年，对存储过程是有点抵触的。总觉得逻辑写在应用层更灵活，何必跑到数据库里折腾？直到有一次，我在一个数据采集项目中，需要在百万级记录里做批量清洗和聚合，应用层来回传输数据，慢得让人抓狂。后来我把核心逻辑写成了PL/pgSQL函数，直接在数据库里跑，性能提升了将近20倍。嗯，从那以后，我对存储过程的态度就彻底变了。

这一讲，我们重点看三个东西：PL/pgSQL怎么写、C语言怎么调用存储过程、以及IN/OUT/INOUT参数模式到底怎么用。说白了，就是让你既能写数据库端的逻辑，又能从C程序里灵活地调用它。

38.1 PL/pgSQL基础：函数与存储过程的区别

在PostgreSQL里，函数和存储过程其实差别不大。函数用CREATE FUNCTION，必须返回一个值；存储过程用CREATE PROCEDURE，可以不返回值。我个人习惯，如果只是执行一些操作（比如插入、更新、清理），就用存储过程；如果需要计算并返回结果，就用函数。

来看一个简单的PL/pgSQL函数示例：

-- 一个简单的PL/pgSQL函数：根据设备ID查询最新温度
CREATE OR REPLACE FUNCTION get_latest_temperature(device_id INT)
RETURNS FLOAT
LANGUAGE plpgsql
AS $$
DECLARE
    temp FLOAT;
BEGIN
    SELECT temperature INTO temp
    FROM sensor_data
    WHERE device = device_id
    ORDER BY record_time DESC
    LIMIT 1;

    RETURN temp;
END;
$$;

这个函数接收一个设备ID，返回最新的温度值。注意那个LANGUAGE plpgsql，它告诉PostgreSQL我们用的是PL/pgSQL语言。我在项目中经常用这种函数来做实时查询，比在应用层拼SQL要安全得多，也快得多。

小提示：PL/pgSQL里变量赋值用 :=，不是 =。我第一次写的时候就踩了这个坑，编译报错半天没反应过来。

38.2 参数模式：IN、OUT、INOUT

参数模式是很多初学者容易搞混的地方。我简单解释一下：

  • IN：输入参数，函数内部只能读，不能改。这是默认模式。
  • OUT：输出参数，函数内部可以赋值，调用者能拿到这个值。
  • INOUT：既是输入又是输出，函数内部可以读也可以改。

你想想看，为什么需要OUT参数？因为有时候一个函数需要返回多个值。比如我要查一个设备的最高温度和最低温度，用OUT参数就非常方便。

-- 使用OUT参数返回多个值
CREATE OR REPLACE FUNCTION get_temp_range(device_id INT, OUT min_temp FLOAT, OUT max_temp FLOAT)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT MIN(temperature), MAX(temperature)
    INTO min_temp, max_temp
    FROM sensor_data
    WHERE device = device_id;
END;
$$;

调用的时候，可以这样：

SELECT * FROM get_temp_range(101);

它会返回两列：min_temp和max_temp。我个人觉得，当返回结果超过两个字段时，用OUT参数比拼凑JSON或数组要清晰得多。

再看INOUT的例子：

-- INOUT参数：传入一个值，处理后返回
CREATE OR REPLACE FUNCTION scale_value(INOUT value FLOAT, scale_factor FLOAT)
LANGUAGE plpgsql
AS $$
BEGIN
    value := value * scale_factor;
END;
$$;

调用：

SELECT scale_value(100.0, 0.8);
-- 返回 80.0
注意：INOUT参数在函数内部可以修改，但修改会影响到调用者传入的变量。如果你在C语言里调用，要小心原始数据被意外改变。我曾经在一个批量处理函数里用了INOUT，结果把原始数据覆盖了，排查了好久才发现。

38.3 C语言调用PostgreSQL存储过程

好，接下来是重头戏——怎么在C程序里调用这些存储过程。我们用的是libpq库，这是PostgreSQL官方的C语言接口。

基本流程很简单：连接数据库 → 准备SQL语句 → 执行 → 处理结果。但调用存储过程时，有几个细节需要注意。

38.3.1 调用无参函数

#include <libpq-fe.h>
#include <stdio.h>

void call_function_no_args() {
    PGconn *conn = PQconnectdb("dbname=test_db user=postgres");
    if (PQstatus(conn) != CONNECTION_OK) {
        fprintf(stderr, "连接失败: %s", PQerrorMessage(conn));
        PQfinish(conn);
        return;
    }

    PGresult *res = PQexec(conn, "SELECT get_current_timestamp();");
    if (PQresultStatus(res) == PGRES_TUPLES_OK) {
        char *ts = PQgetvalue(res, 0, 0);
        printf("当前时间戳: %s\n", ts);
    }

    PQclear(res);
    PQfinish(conn);
}

38.3.2 调用带IN参数的函数

这个最常用。比如我们前面写的get_latest_temperature函数：

void call_function_with_in() {
    PGconn *conn = PQconnectdb("dbname=test_db user=postgres");
    // ... 连接检查省略 ...

    char sql[256];
    int device_id = 101;
    snprintf(sql, sizeof(sql),
             "SELECT get_latest_temperature(%d);", device_id);

    PGresult *res = PQexec(conn, sql);
    if (PQresultStatus(res) == PGRES_TUPLES_OK) {
        float temp = atof(PQgetvalue(res, 0, 0));
        printf("设备 %d 的最新温度: %.2f\n", device_id, temp);
    }

    PQclear(res);
    PQfinish(conn);
}
重要提醒：直接用 snprintf 拼接SQL有SQL注入风险。如果参数来自用户输入，一定要用参数化查询。libpq支持 PQexecParams 来做参数化绑定，后面我们会专门讲。

38.3.3 调用带OUT参数的函数

带OUT参数的函数，在C语言里调用时，返回的结果是一个多列的行。比如我们之前写的get_temp_range：

void call_function_with_out() {
    PGconn *conn = PQconnectdb("dbname=test_db user=postgres");
    // ... 连接检查 ...

    PGresult *res = PQexec(conn, "SELECT * FROM get_temp_range(101);");
    if (PQresultStatus(res) == PGRES_TUPLES_OK) {
        float min_t = atof(PQgetvalue(res, 0, 0));
        float max_t = atof(PQgetvalue(res, 0, 1));
        printf("温度范围: %.2f ~ %.2f\n", min_t, max_t);
    }

    PQclear(res);
    PQfinish(conn);
}

你看，其实就是把函数当成一个表来查。PostgreSQL的OUT参数在C语言层面，就是结果集的普通列。

38.3.4 调用存储过程（无返回值）

如果调用的是CREATE PROCEDURE创建的存储过程，用CALL语句：

void call_procedure() {
    PGconn *conn = PQconnectdb("dbname=test_db user=postgres");
    // ... 连接检查 ...

    PGresult *res = PQexec(conn, "CALL cleanup_old_data(7);");
    if (PQresultStatus(res) == PGRES_COMMAND_OK) {
        printf("存储过程执行成功\n");
    }

    PQclear(res);
    PQfinish(conn);
}

注意，存储过程没有结果集，所以状态检查用PGRES_COMMAND_OK，而不是PGRES_TUPLES_OK。

38.4 知识结构图

下面这张图，我把这一讲的核心知识点串了一下。你可以看到从PL/pgSQL定义，到参数模式，再到C语言调用的完整链路。

PostgreSQL 存储过程与函数 · 知识结构 PL/pgSQL 定义 CREATE FUNCTION（有返回值） CREATE PROCEDURE（无返回值） 参数模式：IN / OUT / INOUT IN：只读输入 OUT：只写输出 INOUT：读写 C语言调用（libpq：PQexec / CALL）

38.5 避坑指南与最佳实践

最后，分享几个我在实际项目中踩过的坑：

  • 参数类型要匹配：PostgreSQL的INT和C语言的int虽然名字像，但在某些平台上长度可能不同。我建议在C代码里统一用 int32_t 或 int64_t，避免移植问题。
  • 事务管理：存储过程内部默认是自动提交的。如果你在C程序里调用多个存储过程，最好显式地用 BEGIN 和 COMMIT 包裹起来。我曾经因为忘记提交，导致数据不一致，排查了一整个下午。
  • 错误处理：每次调用 PQexec 后，一定要检查 PQresultStatus。不要假设调用一定成功。数据库连接可能断开，存储过程可能抛出异常。
  • 性能考量：如果在一个循环里反复调用同一个存储过程，每次都要经过SQL解析和计划生成。这种情况下，可以考虑用 PREPARE 预编译语句，或者把循环逻辑直接写在PL/pgSQL里。

核心总结：

  • PL/pgSQL 函数用 SELECT 调用，存储过程用 CALL 调用。
  • OUT参数在C语言里就是结果集的列，按位置取值即可。
  • INOUT参数要小心，它会修改传入的值。
  • libpq 的 PQexec 是最直接的调用方式，但复杂场景建议用 PQexecParams 做参数化查询。

好了，这一讲的内容就到这里。存储过程与函数的组合使用，在实际项目中非常灵活。你可以在数据库端封装复杂的业务逻辑，然后在C程序里像调用普通函数一样调用它们。既保持了代码的简洁，又充分利用了数据库的计算能力。嗯，希望这些经验对你有帮助。

公众号:蓝海资料掘金营，微信deep3321
← 上一章 📖 返回目录 下一章 →