PostgreSQL教程FG173-PG服务端编程接口（SPI）：基础使用与开发
本文档风哥主要介绍PostgreSQL数据库服务端编程接口（SPI）的基础使用与开发，包括SPI连接管理、SQL执行、结果处理、C函数开发等内容，风哥教程参考PostgreSQL官方文档Server Programming Interface、Writing Procedural Language Handlers等内容，适合高级开发人员在生产环境中使用。
Part01-基础概念与理论知识
1.1 PostgreSQL数据库SPI概述
SPI（Server Programming Interface）是PostgreSQL提供的服务端编程接口，允许在C语言编写的函数和扩展中执行SQL命令。更多视频教程www.fgedu.net.cn。SPI提供了一套完整的API，包括连接管理、命令执行、结果处理、事务控制等功能，是开发PostgreSQL扩展和自定义函数的核心接口。
- 在C函数内部执行任意SQL命令
- 支持事务控制和嵌套事务
- 提供完整的错误处理机制
- 支持游标和批量数据处理
- 与PostgreSQL内存管理集成
1.2 PostgreSQL数据库SPI核心函数
SPI提供了一系列核心函数用于数据库操作：SPI_connect()和SPI_finish()用于连接管理；SPI_execute()和SPI_exec()用于执行SQL命令；SPI_prepare()和SPI_execute_plan()用于预编译和执行计划；SPI_cursor_*系列函数用于游标操作。学习交流加群风哥微信: itpux-com。
— 连接管理函数
— SPI_connect() – 建立SPI连接
— SPI_finish() – 结束SPI连接
— SPI_push() – 保存SPI状态
— SPI_pop() – 恢复SPI状态
— 执行函数
— SPI_execute(const char *command, bool read_only, long count) – 执行SQL命令
— SPI_exec(const char *command, long count) – 执行SQL命令简化版
— SPI_execute_with_args() – 带参数执行
— 预编译函数
— SPI_prepare(const char *command, int nargs, Oid *argtypes) – 预编译SQL
— SPI_execute_plan(SPIPlanPtr plan, Datum *values, const char *nulls, bool read_only, long count) – 执行计划
— SPI_freeplan(SPIPlanPtr plan) – 释放计划
— 结果处理函数
— SPI_getvalue(HeapTuple tuple, TupleDesc tupdesc, int colno) – 获取列值
— SPI_getbinval(HeapTuple tuple, TupleDesc tupdesc, int colno, bool *isnull) – 获取二进制值
— SPI_fnumber(TupleDesc tupdesc, const char *fname) – 获取列号
— 游标函数
— SPI_cursor_open() – 打开游标
— SPI_cursor_fetch() – 获取数据
— SPI_cursor_close() – 关闭游标
1.3 PostgreSQL数据库SPI使用场景
SPI主要用于以下场景：开发自定义C语言函数；开发PostgreSQL扩展；开发过程语言处理器；开发触发器函数；开发数据类型和操作符。通过SPI，开发者可以在C层面访问和操作PostgreSQL数据库，实现高性能的数据处理。
Part02-生产环境规划与建议
2.1 PostgreSQL数据库SPI开发设计原则
SPI开发设计原则：正确管理内存，使用palloc/pfree分配和释放内存；正确处理错误，使用PG_TRY/PG_CATCH捕获异常；正确管理连接，确保SPI_connect和SPI_finish配对使用；避免内存泄漏，释放所有分配的资源。
2.2 PostgreSQL数据库SPI安全注意事项
SPI安全注意事项：验证输入参数，防止SQL注入；使用参数化查询，避免拼接SQL字符串；正确处理权限，确保函数以适当的权限执行；保护敏感数据，避免泄露敏感信息。
2.3 PostgreSQL数据库SPI性能优化
SPI性能优化要点：使用预编译计划减少解析开销；批量处理数据减少函数调用次数；合理使用游标处理大数据集；避免在循环中频繁调用SPI函数。
Part03-生产环境项目实施方案
3.1 PostgreSQL数据库SPI连接管理
3.1.1 基本连接示例
— 文件: fgedu_spi_example.c
#include “postgres.h”
#include “fmgr.h”
#include “executor/spi.h”
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(fgedu_spi_connect_test);
Datum
fgedu_spi_connect_test(PG_FUNCTION_ARGS)
{
int ret;
int64 result = 0;
/* 建立SPI连接 */
ret = SPI_connect();
if (ret != SPI_OK_CONNECT)
elog(ERROR, “SPI_connect failed: %d”, ret);
/* 执行SQL查询 */
ret = SPI_exec(“SELECT COUNT(*) FROM pg_class”, 1);
if (ret != SPI_OK_SELECT)
{
SPI_finish();
elog(ERROR, “SPI_exec failed: %d”, ret);
}
/* 处理结果 */
if (SPI_processed > 0)
{
HeapTuple tuple = SPI_tuptable->vals[0];
TupleDesc tupdesc = SPI_tuptable->tupdesc;
bool isnull;
result = DatumGetInt64(SPI_getbinval(tuple, tupdesc, 1, &isnull));
if (isnull)
result = 0;
}
/* 结束SPI连接 */
SPI_finish();
PG_RETURN_INT64(result);
}
— 编译Makefile
MODULES = fgedu_spi_example
EXTENSION = fgedu_spi_example
DATA = fgedu_spi_example–1.0.sql
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) –pgxs)
include $(PGXS)
— 编译安装
$ make
$ make install
— 输出结果
gcc -Wall -Wmissing-prototypes -Wpointer-arith -fPIC -I. -I./ -I/postgresql/fgapp/include/server -c -o fgedu_spi_example.o fgedu_spi_example.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -shared -o fgedu_spi_example.so fgedu_spi_example.o -L/postgresql/fgapp/lib -Wl,–as-needed
— 创建函数
CREATE FUNCTION fgedu_spi_connect_test() RETURNS bigint
AS ‘fgedu_spi_example’, ‘fgedu_spi_connect_test’
LANGUAGE C STRICT;
— 测试函数
SELECT fgedu_spi_connect_test();
— 输出结果
fgedu_spi_connect_test
————————
356
(1 row)
3.2 PostgreSQL数据库SPI执行SQL命令
3.2.1 执行SELECT查询
#include “postgres.h”
#include “fmgr.h”
#include “executor/spi.h”
#include “utils/builtins.h”
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(fgedu_spi_select_example);
Datum
fgedu_spi_select_example(PG_FUNCTION_ARGS)
{
int ret;
text *result_text;
char *query;
/* 获取参数 */
int32 user_id = PG_GETARG_INT32(0);
/* 建立连接 */
SPI_connect();
/* 构建查询 – 使用参数化更安全 */
query = psprintf(“SELECT name FROM fgedu_users WHERE id = %d”, user_id);
/* 执行查询 */
ret = SPI_exec(query, 1);
pfree(query);
if (ret != SPI_OK_SELECT)
{
SPI_finish();
elog(ERROR, “Query failed”);
}
/* 处理结果 */
if (SPI_processed > 0)
{
HeapTuple tuple = SPI_tuptable->vals[0];
TupleDesc tupdesc = SPI_tuptable->tupdesc;
Datum name_datum;
bool isnull;
name_datum = SPI_getbinval(tuple, tupdesc, 1, &isnull);
if (isnull)
result_text = cstring_to_text(“NULL”);
else
result_text = DatumGetTextPP(name_datum);
}
else
{
result_text = cstring_to_text(“NOT FOUND”);
}
SPI_finish();
PG_RETURN_TEXT_P(result_text);
}
— 创建函数
CREATE FUNCTION fgedu_spi_select_example(user_id integer) RETURNS text
AS ‘fgedu_spi_example’, ‘fgedu_spi_select_example’
LANGUAGE C STRICT;
— 测试
SELECT fgedu_spi_select_example(1);
— 输出结果
fgedu_spi_select_example
————————–
fgedu
(1 row)
3.2.2 执行INSERT/UPDATE/DELETE
#include “postgres.h”
#include “fmgr.h”
#include “executor/spi.h”
#include “utils/builtins.h”
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(fgedu_spi_insert_example);
Datum
fgedu_spi_insert_example(PG_FUNCTION_ARGS)
{
int ret;
char *query;
int64 affected_rows;
/* 获取参数 */
text *name = PG_GETARG_TEXT_PP(0);
text *email = PG_GETARG_TEXT_PP(1);
char *name_str = text_to_cstring(name);
char *email_str = text_to_cstring(email);
/* 建立连接 */
SPI_connect();
/* 构建INSERT语句 */
query = psprintf(
“INSERT INTO fgedu_users(name, email) VALUES(‘%s’, ‘%s’)”,
name_str, email_str
);
/* 执行INSERT */
ret = SPI_exec(query, 0);
pfree(query);
pfree(name_str);
pfree(email_str);
if (ret != SPI_OK_INSERT)
{
SPI_finish();
elog(ERROR, “INSERT failed: %d”, ret);
}
affected_rows = SPI_processed;
SPI_finish();
PG_RETURN_INT64(affected_rows);
}
— 创建函数
CREATE FUNCTION fgedu_spi_insert_example(name text, email text) RETURNS bigint
AS ‘fgedu_spi_example’, ‘fgedu_spi_insert_example’
LANGUAGE C STRICT;
— 测试
SELECT fgedu_spi_insert_example(‘test_user’, ‘test@fgedu.net.cn’);
— 输出结果
fgedu_spi_insert_example
————————–
1
(1 row)
3.3 PostgreSQL数据库SPI处理查询结果
3.3.1 遍历查询结果
#include “postgres.h”
#include “fmgr.h”
#include “executor/spi.h”
#include “utils/builtins.h”
#include “funcapi.h”
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(fgedu_spi_process_results);
Datum
fgedu_spi_process_results(PG_FUNCTION_ARGS)
{
int ret;
FuncCallContext *funcctx;
TupleDesc tupdesc;
AttInMetadata *attinmeta;
HeapTuple tuple;
/* 第一次调用初始化 */
if (SRF_IS_FIRSTCALL())
{
MemoryContext oldcontext;
funcctx = SRF_FIRSTCALL_INIT();
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
/* 建立SPI连接 */
SPI_connect();
/* 执行查询 */
ret = SPI_exec(“SELECT id, name, email FROM fgedu_users LIMIT 10”, 0);
if (ret != SPI_OK_SELECT)
elog(ERROR, “Query failed”);
/* 保存结果 */
funcctx->user_fctx = SPI_tuptable;
funcctx->max_calls = SPI_processed;
/* 创建返回类型描述 */
tupdesc = CreateTemplateTupleDesc(3);
TupleDescInitEntry(tupdesc, (AttrNumber) 1, “id”, INT4OID, -1, 0);
TupleDescInitEntry(tupdesc, (AttrNumber) 2, “name”, TEXTOID, -1, 0);
TupleDescInitEntry(tupdesc, (AttrNumber) 3, “email”, TEXTOID, -1, 0);
attinmeta = TupleDescGetAttInMetadata(tupdesc);
funcctx->attinmeta = attinmeta;
MemoryContextSwitchTo(oldcontext);
}
/* 每次调用处理一行 */
funcctx = SRF_PERCALL_SETUP();
if (funcctx->call_cntr < funcctx->max_calls)
{
SPITupleTable *tuptable = (SPITupleTable *) funcctx->user_fctx;
HeapTuple result_tuple;
Datum result;
char *values[3];
bool isnull;
/* 获取当前行 */
tuple = tuptable->vals[funcctx->call_cntr];
/* 提取值 */
values[0] = SPI_getvalue(tuple, tuptable->tupdesc, 1);
values[1] = SPI_getvalue(tuple, tuptable->tupdesc, 2);
values[2] = SPI_getvalue(tuple, tuptable->tupdesc, 3);
/* 构建返回元组 */
result_tuple = BuildTupleFromCStrings(funcctx->attinmeta, values);
result = HeapTupleGetDatum(result_tuple);
SRF_RETURN_NEXT(funcctx, result);
}
else
{
/* 结束处理 */
SPI_finish();
SRF_RETURN_DONE(funcctx);
}
}
— 创建函数
CREATE FUNCTION fgedu_spi_process_results() RETURNS TABLE(id integer, name text, email text)
AS ‘fgedu_spi_example’, ‘fgedu_spi_process_results’
LANGUAGE C;
— 测试
SELECT * FROM fgedu_spi_process_results();
— 输出结果
id | name | email
—-+————+——————-
1 | fgedu | fgedu@test.com
2 | test_user | test@fgedu.net.cn
3 | user3 | user3@test.com
(3 rows)
Part04-生产案例与实战讲解
4.1 PostgreSQL数据库使用SPI开发C函数实战
本案例演示如何使用SPI开发一个完整的C语言函数，实现复杂的数据处理逻辑。学习交流加群风哥QQ113257174。
— fgedu_table_stats.c
#include “postgres.h”
#include “fmgr.h”
#include “executor/spi.h”
#include “utils/builtins.h”
#include “catalog/pg_type.h”
PG_MODULE_MAGIC;
/* 定义返回类型结构 */
typedef struct {
int64 row_count;
float8 avg_value;
float8 min_value;
float8 max_value;
float8 sum_value;
} FgeduStats;
PG_FUNCTION_INFO_V1(fgedu_table_stats);
Datum
fgedu_table_stats(PG_FUNCTION_ARGS)
{
int ret;
FgeduStats *stats;
char *table_name;
char *column_name;
char *query;
/* 获取参数 */
table_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
column_name = text_to_cstring(PG_GETARG_TEXT_PP(1));
/* 分配返回结构 */
stats = (FgeduStats *) palloc0(sizeof(FgeduStats));
/* 建立SPI连接 */
ret = SPI_connect();
if (ret != SPI_OK_CONNECT)
{
pfree(table_name);
pfree(column_name);
pfree(stats);
elog(ERROR, “SPI_connect failed”);
}
/* 构建查询 */
query = psprintf(
“SELECT COUNT(*) as cnt, AVG(%s) as avg, MIN(%s) as min, MAX(%s) as max, SUM(%s) as sum ”
“FROM %s”,
column_name, column_name, column_name, column_name, table_name
);
/* 执行查询 */
ret = SPI_exec(query, 1);
pfree(query);
if (ret == SPI_OK_SELECT && SPI_processed > 0)
{
HeapTuple tuple = SPI_tuptable->vals[0];
TupleDesc tupdesc = SPI_tuptable->tupdesc;
bool isnull;
stats->row_count = DatumGetInt64(
SPI_getbinval(tuple, tupdesc, 1, &isnull)
);
stats->avg_value = DatumGetFloat8(
SPI_getbinval(tuple, tupdesc, 2, &isnull)
);
stats->min_value = DatumGetFloat8(
SPI_getbinval(tuple, tupdesc, 3, &isnull)
);
stats->max_value = DatumGetFloat8(
SPI_getbinval(tuple, tupdesc, 4, &isnull)
);
stats->sum_value = DatumGetFloat8(
SPI_getbinval(tuple, tupdesc, 5, &isnull)
);
}
/* 结束SPI连接 */
SPI_finish();
pfree(table_name);
pfree(column_name);
/* 返回复合类型 */
PG_RETURN_POINTER(stats);
}
— 创建复合类型
CREATE TYPE fgedu_stats_type AS (
row_count bigint,
avg_value float8,
min_value float8,
max_value float8,
sum_value float8
);
— 创建函数
CREATE FUNCTION fgedu_table_stats(table_name text, column_name text)
RETURNS fgedu_stats_type
AS ‘fgedu_table_stats’, ‘fgedu_table_stats’
LANGUAGE C STRICT;
— 测试
SELECT * FROM fgedu_table_stats(‘fgedu_sales_data’, ‘amount’);
— 输出结果
row_count | avg_value | min_value | max_value | sum_value
———–+——————+———–+———–+————-
1000 | 5234.56789012345 | 156.23 | 9987.65 | 5234567.89
(1 row)
4.2 PostgreSQL数据库使用SPI开发触发器实战
本案例演示如何使用SPI开发触发器函数，实现数据变更审计功能。更多学习教程公众号风哥教程itpux_com。
— fgedu_audit_trigger.c
#include “postgres.h”
#include “fmgr.h”
#include “executor/spi.h”
#include “commands/trigger.h”
#include “utils/builtins.h”
#include “utils/rel.h”
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(fgedu_audit_trigger_func);
Datum
fgedu_audit_trigger_func(PG_FUNCTION_ARGS)
{
TriggerData *trigdata;
HeapTuple new_tuple;
HeapTuple old_tuple;
TupleDesc tupdesc;
char *operation;
char *table_name;
char *query;
int ret;
int i;
/* 确保是触发器调用 */
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, “fgedu_audit_trigger_func: not called by trigger manager”);
trigdata = (TriggerData *) fcinfo->context;
/* 确定操作类型 */
if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
operation = “INSERT”;
else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
operation = “UPDATE”;
else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
operation = “DELETE”;
else
elog(ERROR, “Unsupported trigger operation”);
/* 获取表名 */
table_name = SPI_getrelname(trigdata->tg_relation);
tupdesc = trigdata->tg_relation->rd_att;
/* 建立SPI连接 */
SPI_connect();
/* 获取新旧数据 */
new_tuple = trigdata->tg_newtuple;
old_tuple = trigdata->tg_trigtuple;
/* 构建审计记录 */
if (strcmp(operation, “DELETE”) == 0)
{
/* DELETE操作，记录旧数据 */
char *old_data = SPI_getvalue(old_tuple, tupdesc, 1);
query = psprintf(
“INSERT INTO fgedu_audit_log(table_name, operation, old_data, new_data, audit_time) ”
“VALUES(‘%s’, ‘%s’, ‘%s’, NULL, NOW())”,
table_name, operation, old_data ? old_data : “NULL”
);
}
else
{
/* INSERT或UPDATE操作，记录新数据 */
char *new_data = SPI_getvalue(new_tuple, tupdesc, 1);
query = psprintf(
“INSERT INTO fgedu_audit_log(table_name, operation, old_data, new_data, audit_time) ”
“VALUES(‘%s’, ‘%s’, NULL, ‘%s’, NOW())”,
table_name, operation, new_data ? new_data : “NULL”
);
}
/* 执行审计记录插入 */
ret = SPI_exec(query, 0);
pfree(query);
if (ret != SPI_OK_INSERT)
elog(WARNING, “Failed to insert audit record”);
SPI_finish();
/* 返回结果 */
if (strcmp(operation, “DELETE”) == 0)
PG_RETURN_POINTER(old_tuple);
else
PG_RETURN_POINTER(new_tuple);
}
— 创建审计表
CREATE TABLE fgedu_audit_log (
id SERIAL PRIMARY KEY,
table_name VARCHAR(100),
operation VARCHAR(20),
old_data TEXT,
new_data TEXT,
audit_time TIMESTAMP DEFAULT NOW()
);
— 创建触发器函数
CREATE FUNCTION fgedu_audit_trigger_func()
RETURNS TRIGGER
AS ‘fgedu_audit_trigger’, ‘fgedu_audit_trigger_func’
LANGUAGE C;
— 创建触发器
CREATE TRIGGER fgedu_users_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON fgedu_users
FOR EACH ROW EXECUTE FUNCTION fgedu_audit_trigger_func();
— 测试触发器
INSERT INTO fgedu_users(name, email) VALUES(‘audit_test’, ‘audit@test.com’);
— 查看审计日志
SELECT * FROM fgedu_audit_log ORDER BY id DESC LIMIT 5;
— 输出结果
id | table_name | operation | old_data | new_data | audit_time
—-+————–+———–+———-+————–+—————————-
1 | fgedu_users | INSERT | NULL | audit_test | 2026-04-07 16:30:25.123456
(1 row)
4.3 PostgreSQL数据库使用SPI开发扩展实战
本案例演示如何使用SPI开发一个完整的PostgreSQL扩展。from PostgreSQL视频:www.itpux.com。
— fgedu_utils/
— ├── fgedu_utils.control
— ├── fgedu_utils–1.0.sql
— ├── fgedu_utils.c
— └── Makefile
— fgedu_utils.control
# fgedu_utils extension
comment = ‘FGEDU utility functions using SPI’
default_version = ‘1.0’
module_pathname = ‘$libdir/fgedu_utils’
relocatable = true
— fgedu_utils–1.0.sql
— 创建扩展函数
CREATE FUNCTION fgedu_table_exists(table_name text) RETURNS boolean
AS ‘fgedu_utils’, ‘fgedu_table_exists’
LANGUAGE C STRICT;
CREATE FUNCTION fgedu_column_exists(table_name text, column_name text) RETURNS boolean
AS ‘fgedu_utils’, ‘fgedu_column_exists’
LANGUAGE C STRICT;
CREATE FUNCTION fgedu_get_table_size(table_name text) RETURNS bigint
AS ‘fgedu_utils’, ‘fgedu_get_table_size’
LANGUAGE C STRICT;
— fgedu_utils.c
#include “postgres.h”
#include “fmgr.h”
#include “executor/spi.h”
#include “utils/builtins.h”
#include “catalog/pg_type.h”
PG_MODULE_MAGIC;
/* 检查表是否存在 */
PG_FUNCTION_INFO_V1(fgedu_table_exists);
Datum
fgedu_table_exists(PG_FUNCTION_ARGS)
{
char *table_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
char *query;
int ret;
bool exists = false;
SPI_connect();
query = psprintf(
“SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = ‘%s’)”,
table_name
);
ret = SPI_exec(query, 1);
pfree(query);
if (ret == SPI_OK_SELECT && SPI_processed > 0)
{
HeapTuple tuple = SPI_tuptable->vals[0];
TupleDesc tupdesc = SPI_tuptable->tupdesc;
bool isnull;
exists = DatumGetBool(SPI_getbinval(tuple, tupdesc, 1, &isnull));
}
SPI_finish();
pfree(table_name);
PG_RETURN_BOOL(exists);
}
/* 检查列是否存在 */
PG_FUNCTION_INFO_V1(fgedu_column_exists);
Datum
fgedu_column_exists(PG_FUNCTION_ARGS)
{
char *table_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
char *column_name = text_to_cstring(PG_GETARG_TEXT_PP(1));
char *query;
int ret;
bool exists = false;
SPI_connect();
query = psprintf(
“SELECT EXISTS (SELECT 1 FROM information_schema.columns ”
“WHERE table_name = ‘%s’ AND column_name = ‘%s’)”,
table_name, column_name
);
ret = SPI_exec(query, 1);
pfree(query);
if (ret == SPI_OK_SELECT && SPI_processed > 0)
{
HeapTuple tuple = SPI_tuptable->vals[0];
TupleDesc tupdesc = SPI_tuptable->tupdesc;
bool isnull;
exists = DatumGetBool(SPI_getbinval(tuple, tupdesc, 1, &isnull));
}
SPI_finish();
pfree(table_name);
pfree(column_name);
PG_RETURN_BOOL(exists);
}
/* 获取表大小 */
PG_FUNCTION_INFO_V1(fgedu_get_table_size);
Datum
fgedu_get_table_size(PG_FUNCTION_ARGS)
{
char *table_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
char *query;
int ret;
int64 size = 0;
SPI_connect();
query = psprintf(“SELECT pg_total_relation_size(‘%s’)”, table_name);
ret = SPI_exec(query, 1);
pfree(query);
if (ret == SPI_OK_SELECT && SPI_processed > 0)
{
HeapTuple tuple = SPI_tuptable->vals[0];
TupleDesc tupdesc = SPI_tuptable->tupdesc;
bool isnull;
size = DatumGetInt64(SPI_getbinval(tuple, tupdesc, 1, &isnull));
}
SPI_finish();
pfree(table_name);
PG_RETURN_INT64(size);
}
— Makefile
MODULES = fgedu_utils
EXTENSION = fgedu_utils
DATA = fgedu_utils–1.0.sql
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) –pgxs)
include $(PGXS)
— 编译安装
$ make
$ make install
— 创建扩展
CREATE EXTENSION fgedu_utils;
— 测试扩展
SELECT fgedu_table_exists(‘fgedu_users’);
SELECT fgedu_column_exists(‘fgedu_users’, ‘name’);
SELECT fgedu_get_table_size(‘fgedu_users’);
— 输出结果
fgedu_table_exists
——————–
t
(1 row)
fgedu_column_exists
———————
t
(1 row)
fgedu_get_table_size
———————-
81920
(1 row)
Part05-风哥经验总结与分享
5.1 PostgreSQL数据库SPI最佳实践
SPI开发最佳实践：始终检查SPI函数返回值；正确配对SPI_connect和SPI_finish；使用PG_TRY/PG_CATCH处理错误；使用psprintf构建查询字符串；释放所有分配的内存资源。
Datum
fgedu_spi_best_practice(PG_FUNCTION_ARGS)
{
int ret;
MemoryContext oldcontext;
/* 使用PG_TRY/PG_CATCH确保资源释放 */
PG_TRY();
{
/* 建立SPI连接 */
ret = SPI_connect();
if (ret != SPI_OK_CONNECT)
elog(ERROR, “SPI_connect failed”);
/* 在合适的内存上下文中分配资源 */
oldcontext = MemoryContextSwitchTo(
CurTransactionContext
);
/* 执行SPI操作 */
/* … */
/* 恢复内存上下文 */
MemoryContextSwitchTo(oldcontext);
/* 结束SPI连接 */
SPI_finish();
}
PG_CATCH();
{
/* 确保在异常情况下释放资源 */
SPI_finish();
PG_RE_THROW();
}
PG_END_TRY();
PG_RETURN_VOID();
}
5.2 PostgreSQL数据库SPI调试技巧
SPI调试技巧：使用elog输出调试信息；检查SPI_processed获取处理的行数；检查SPI_tuptable获取查询结果；使用SPI_result获取错误代码。
5.3 PostgreSQL数据库SPI常见问题
SPI常见问题：内存泄漏导致服务器内存耗尽；忘记调用SPI_finish导致连接泄漏；错误处理不当导致事务回滚；SQL注入导致安全问题。
- SPI_OK_CONNECT – 连接成功
- SPI_OK_SELECT – SELECT成功
- SPI_OK_INSERT – INSERT成功
- SPI_OK_UPDATE – UPDATE成功
- SPI_OK_DELETE – DELETE成功
- SPI_ERROR_CONNECT – 连接失败
- SPI_ERROR_COPY – COPY失败
- SPI_ERROR_OPUNKNOWN – 未知操作
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
