在学习postgreSQL手册中的服务器编程中用C写触发器的完整例子时,遇到一些问题。在这里记录相关问题并分享一下。 该例子为《PostgreSQL9.0.4中文文档》的36.4 “一个完整的例子”或《PostgreSQL 8.1 中文文档》的33.4.“ 一个完整的例子” http://www.php100.com/manual/PostgreSQL8/trigger-example.html,其为用C 写的触发器的例子。 首先是定义表ttest,这个没什么问题。 CREATE TABLE ttest (x integer); 下面是较长一段触发器函数的源代码,这里需要注意的是在使用9.2或以后版本的postgreSQL时,需要加入头文件#include "utils/rel.h" ,它定义了关系描述符,否则会报错: 其实在9.2之后的官方英文文档中的该例子中加入了头文件#include "utils/rel.h" 。 ttest.c:In function ‘trigf’: ttest.c:44:error: dereferencing pointer to incomplete type
#include "postgres.h" #include "executor/spi.h" /* 用 SPI 的时候要调用的头文件*/ #include "commands/trigger.h" /* ...和触发器 */ #include "utils/rel.h" /*关系描述符的定义*/ #ifdef PG_MODULE_MAGIC #endif PG_MODULE_MAGIC; extern Datum trigf(PG_FUNCTION_ARGS); PG_FUNCTION_INFO_V1(trigf); Datum trigf(PG_FUNCTION_ARGS) { TriggerData *trigdata = (TriggerData *) fcinfo->context; TupleDesc tupdesc; HeapTuple rettuple; char *when; bool checknull = false; bool isnull; int ret, i; /* 确信自己是作为触发器调用的 */ if (!CALLED_AS_TRIGGER(fcinfo)) elog(ERROR, "trigf: not called by trigger manager"); /*返回给执行者的行 */ if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) rettuple = trigdata->tg_newtuple; else rettuple = trigdata->tg_trigtuple; /* 检查 NULL 值 */ if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event) && TRIGGER_FIRED_BEFORE(trigdata->tg_event)) checknull = true; if (TRIGGER_FIRED_BEFORE(trigdata->tg_event)) when = "before"; else when = "after "; tupdesc = trigdata->tg_relation->rd_att; /* 与 SPI 管理器连接 */ if ((ret = SPI_connect()) < 0) elog(ERROR, "trigf (fired %s): SPI_connect returned %d", when, ret); /* 获取表中的行数量 */ ret = SPI_exec("SELECT count(*) FROM ttest", 0); if (ret < 0) elog(ERROR, "trigf (fired %s): SPI_exec returned %d", when, ret); /* count(*) 返回 int8 ,所以要小心转换*/ i = DatumGetInt64(SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isnull)); elog (INFO, "trigf (fired %s): there are %d rows in ttest", when, i); SPI_finish(); if (checknull) { SPI_getbinval(rettuple, tupdesc, 1, &isnull); if (isnull) rettuple = NULL; } return PointerGetDatum(rettuple); }
文档中没有说明该C语言函数的编译,以下为编译:
[postgres@pg_1 ~]$ gcc -fPIC -I /home/postgres/Downloads/postgresql-9.4.5/src/include -c ttest.c
[postgres@pg_1 ~]$ gcc -fPIC -shared -o ttest.so ttest.o
[postgres@pg_1 ~]$ cp ttest.so $libdir
其中/home/postgres/Downloads/postgresql-9.4.5/src/include为postgresql源码src下include的路径,编译完成的动态库so需放在postgreSQL的lib下,即$libdir或直接路径(如/home/postgres/pgsql/lib)。
这里用shared就需要加上-fPIC,否则会报错:
/usr/bin/ld:ttest.o: relocation R_X86_64_32 against `.rodata' can not be used when making ashared object; recompile with -fPIC
ttest.o:could not read symbols: Bad value
这时可加载ttest了
postgres=#load 'ttest.so';
LOAD
postgres=#CREATE FUNCTION trigf() RETURNS trigger
postgres-# AS '$libdir/ttest'
postgres-# LANGUAGE C;
CREATEFUNCTION
以下和文档中一致,没有异议。
postgres=#CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest
postgres-# FOR EACH ROW EXECUTE PROCEDURE trigf();
CREATETRIGGER
postgres=#CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttest
postgres-# FOR EACH ROW EXECUTE PROCEDURE trigf();
CREATETRIGGER
postgres=#INSERT INTO ttest VALUES (NULL);
INFO: trigf (fired before): there are 0 rows inttest
INSERT 0 0
postgres=# SELECT * FROM ttest; x ---(0 rows)
postgres=# INSERT INTO ttest VALUES (1);
INFO: trigf (fired before): there are 0 rows inttest
INFO: trigf (fired after ): there are 1 rows inttest
INSERT 0 1
postgres=#SELECT * FROM ttest; x --- 1(1 row)
总结一下:1,注意头文件是否正确指定;
2,头文件的引用目录是否正确,这里为/home/postgres/Downloads/postgresql-9.4.5/src/include;
3,编译时需要加上-fPIC;
4,生成的动态链接库so需放在postgreSQL的lib下;
5,创建function时指定名称或路径。 }