PostgreSQL的SPI接口用法
SPI(Server Programming Interface)是PostgreSQL内部C函数访问数据库的标准接口，允许在C函数中执行SQL语句、读取结果集。所有SPI调用遵循严格的生命周期模型：
SPI_connect() → 执行查询 → 读取结果 → SPI_finish()
核心API分类
- 结果读取
SPI_processed: 语句处理的行数
SPI_tuptable: 结果集
SPI_getvalue: 取字段值(string)
SPI_getbinval: 取字段值(Datum)
SPI_fnumber: 字段名->编号 - 游标操作
SPI_cursor_open -> SPI_cursor_fetch -> SPI_cursor_close - 查询执行（预编译）
SPI_prepare -> SPI_execute_plan/SPI_execp -> SPI_freeplan/SPI_keepplan/SPI_saveplan - 查询执行（简单）
SPI_execute -> 直接传入SQL字符串
SPI_execute_with_args -> SQL字符串 + 参数值/类型 - 生命周期管理
SPI_connect -> SPI_finish
简单SQL执行
Datum
crosstab(PG_FUNCTION_ARGS)
{
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
char *sql = text_to_cstring(PG_GETARG_TEXT_PP(0)); // 用户传入的 SQL
SPITupleTable *spi_tuptable;
TupleDesc spi_tupdesc;
uint64 proc;
int ret;
// ====== 第 1 步：连接 SPI ======
if ((ret = SPI_connect()) < 0)
elog(ERROR, "crosstab: SPI_connect returned %d", ret);
// ====== 第 2 步：执行查询 ======
// 参数: SQL字符串, 只读?, 最大行数(0=全部)
ret = SPI_execute(sql, true, 0);
proc = SPI_processed; // 获取返回的总行数
// 检查执行结果
if (ret != SPI_OK_SELECT || proc == 0)
{
SPI_finish(); // 不要忘记断开！
rsinfo->isDone = ExprEndResult;
PG_RETURN_NULL();
}
// ====== 第 3 步：获取结果集 ======
spi_tuptable = SPI_tuptable; // 全局结果集指针
spi_tupdesc = spi_tuptable->tupdesc; // 列描述符
// 检查返回的列数
if (spi_tupdesc->natts != 3)
ereport(ERROR, ...);
// ====== 第 4 步：遍历每一行 ======
for (call_cntr = 0; call_cntr < proc; call_cntr++)
{
HeapTuple tuple = spi_tuptable->vals[call_cntr];
char *rowname, *category, *values;
// SPI_getvalue: 将第 i 列转为 C 字符串
rowname = SPI_getvalue(tuple, spi_tupdesc, 1); // 第1列
category = SPI_getvalue(tuple, spi_tupdesc, 2); // 第2列
values = SPI_getvalue(tuple, spi_tupdesc, 3); // 第3列
// ... 业务处理 ...
pfree(rowname); // SPI_getvalue 分配的字符串需要手动释放
pfree(category);
pfree(values);
}
// ====== 第 5 步：断开 SPI ======
SPI_finish();
// ... 返回结果 ...
}
关键模式：SPI_connect -> SPI_execute(sql,read_only,tcount) -> 读取SPI_tuptable -> SPI_finish
预编译计划 + 参数化查询
Datum
check_primary_key(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
Trigger *trigger = trigdata->tg_trigger;
HeapTuple tuple = trigdata->tg_trigtuple; // 触发器的行
Relation rel = trigdata->tg_relation;
TupleDesc tupdesc = rel->rd_att;
char *relname = "referenced_table"; // 被引用表名
char *fkey_cols[] = {"col1", "col2"}; // 外键列名
int nkeys = 2;
Datum kvals[2]; // 参数值数组
Oid argtypes[2]; // 参数类型数组
char sql[8192];
SPIPlanPtr pplan;
int ret, i;
// ====== 第 1 步：连接 SPI ======
if ((ret = SPI_connect()) < 0)
elog(ERROR, "SPI_connect returned %d", ret);
// ====== 第 2 步：提取触发器中变更行的字段值 ======
for (i = 0; i < nkeys; i++)
{
int fnum = SPI_fnumber(tupdesc, fkey_cols[i]); // 列名→列号
if (fnum <= 0)
ereport(ERROR, (errmsg("column \"%s\" not found", fkey_cols[i])));
bool isnull;
kvals[i] = SPI_getbinval(tuple, tupdesc, fnum, &isnull); // 取 Datum 值
if (isnull)
{
SPI_finish(); // NULL 值不需要检查，直接返回
return PointerGetDatum(tuple);
}
argtypes[i] = SPI_gettypeid(tupdesc, fnum); // 获取列类型 OID
}
// ====== 第 3 步：构造参数化 SQL ======
// SELECT 1 FROM referenced_table WHERE col1 = $1 AND col2 = $2
snprintf(sql, sizeof(sql), "select 1 from %s where ", relname);
for (i = 0; i < nkeys; i++)
snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql),
"%s = $%d %s",
fkey_cols[i], i + 1,
(i < nkeys - 1) ? "and " : "");
// ====== 第 4 步：预编译查询计划 ======
pplan = SPI_prepare(sql, nkeys, argtypes);
if (pplan == NULL)
elog(ERROR, "SPI_prepare failed: %s", SPI_result_code_string(SPI_result));
// ====== 第 5 步：将计划保存到长期内存（跨调用复用） ======
if (SPI_keepplan(pplan))
elog(ERROR, "SPI_keepplan failed");
// ====== 第 6 步：执行预编译计划 ======
// 参数: 计划, 值数组, NULL标记数组(全非NULL), 最大行数(1)
ret = SPI_execp(pplan, kvals, NULL, 1);
if (ret < 0)
elog(ERROR, "SPI_execp returned %d", ret);
// ====== 第 7 步：检查结果 ======
if (SPI_processed == 0)
ereport(ERROR,
(errmsg("tuple references non-existent key"),
errdetail("Trigger \"%s\" found orphaned reference.", trigger->tgname)));
SPI_finish();
return PointerGetDatum(tuple);
}
关键模式：SPI_prepare(sql, nargs, argtypes) -> SPI_keepplan(plan) -> SPI_execp(plan, values, nulls, tcount)
带类型的参数执行
当不需要复用计划时，用SPI_execute_with_args比SPI_prepare + SPI_execp更简洁
Datum my_insert_function(PG_FUNCTION_ARGS)
{
char *name = PG_GETARG_CSTRING(0);
int32 age = PG_GETARG_INT32(1);
int ret;
SPI_connect();
// 构造参数化 INSERT
Oid argtypes[] = {TEXTOID, INT4OID};
Datum values[] = {CStringGetDatum(name), Int32GetDatum(age)};
char nulls[] = {' ', ' '}; // ' ' = NOT NULL, 'n' = NULL
ret = SPI_execute_with_args(
"INSERT INTO users (name, age) VALUES ($1, $2)",
2, // 参数个数
argtypes, // 类型数组
values, // 值数组
nulls, // NULL 标记数组
false, // read_only
0 // tcount (0=unlimited)
);
if (ret != SPI_OK_INSERT)
elog(ERROR, "insert failed: %s", SPI_result_code_string(ret));
uint64 rows_inserted = SPI_processed;
SPI_finish();
PG_RETURN_INT64(rows_inserted);
}
游标操作
适用于返回大量数据、需要分批处理的场景
Datum fetch_large_result(PG_FUNCTION_ARGS)
{
Portal portal;
uint64 total_rows = 0;
int ret;
SPI_connect();
// 打开游标
portal = SPI_cursor_open_with_args(
"my_cursor",
"SELECT id, data FROM large_table WHERE status = $1",
1, // nargs
(Oid[]){INT4OID}, // argtypes
(Datum[]){Int32GetDatum(1)}, // values
(char[]){" "}, // nulls
true, // read_only
0 // cursor_options
);
// 分批取数据
while (true)
{
SPI_cursor_fetch(portal, true, 1000); // 每次取 1000 行
if (SPI_processed == 0)
break;
// 处理这 1000 行 ...
total_rows += SPI_processed;
}
SPI_cursor_close(portal);
SPI_finish();
PG_RETURN_INT64(total_rows);
}
修改元组
Datum trigger_modify_func(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
HeapTuple oldtuple = trigdata->tg_trigtuple;
Relation rel = trigdata->tg_relation;
SPI_connect();
// 修改第3列为新值，第5列设为 NULL
int attnum[] = {3, 5};
Datum newvals[] = {
CStringGetTextDatum("new_value"), // 列3的新值
(Datum) 0 // 列5设为 NULL
};
char nulls[] = {' ', 'n'}; // 列3非NULL, 列5为NULL
HeapTuple newtuple = SPI_modifytuple(
rel,
oldtuple,
2, // 要修改的列数
attnum, // 要修改的列号
newvals, // 新值
nulls // NULL 标记
);
SPI_finish();
return PointerGetDatum(newtuple);
}
浙公网安备 33010602011771号