竟然没有人顶,幸亏没有在这干等。
经过这几天的摸索,也算有了一点眉目。
贴出来,算是给自己一个交代吧。#include "executor/spi.h" /* 用SPI的时候要用的头文件 */
#include "commands/trigger.h" /* -"- 用触发器要用的头 */
#include "postgres.h"#define _debug_ 1/*extern Datum trig_update_oid(PG_FUNCTION_ARGS);*/
/*触发器trig_update_oid 当在表中插入(或删除)一个元组时,
将该元组的 oid 在表oids中插入(或删除)。只能以AFTER创建 */PG_FUNCTION_INFO_V1(trig_update_oid);Datum
trig_update_oid(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *)fcinfo->context;
HeapTuple rettuple;
char cmd[200];
char crttb[] = "create table oids (tup_oid oid primary key)";
int ret; /* 确信触发器数据指向我预期的地方 */
if(!CALLED_AS_TRIGGER(fcinfo)){
elog(ERROR, "trigf_update_oid(): not fired by trigger manager");
}
/* 返回给执行者的元组 */
if( TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event) ){#ifdef _debug_
elog(NOTICE,"trig_update_oid:fired by update");
#endif PG_RETURN_POINTER(rettuple);
}
else{
rettuple = trigdata->tg_trigtuple;
} if (TRIGGER_FIRED_BEFORE(trigdata->tg_event)){
#ifdef _debug_
elog(NOTICE,"trig_update_oid:fired before");
#endif PG_RETURN_NULL();
} /*是否是由INSERT触发*/
if(TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)){
sprintf(cmd,"insert into oids values(%d)",HeapTupleGetOid(rettuple) );
}
else{
sprintf(cmd,"delete from oids where tup_oid = %d",HeapTupleGetOid(rettuple));
}
#ifdef _debug_
elog(NOTICE,"trig_update_oid:cmd = %s",cmd);
#endif if(SPI_connect() == SPI_ERROR_CONNECT){
elog(ERROR,"Can't connect to the SPI manager");
} ret = SPI_exec("select relname from pg_class where relname = 'oids'" ,0); if(ret < 0 ){
elog(ERROR,"SPI_exec returns error!");
} if(SPI_processed <= 0){
ret = SPI_exec(crttb,0);
if(ret < 0 ){
elog(ERROR,"SPI_exec returns error!");
}
}
ret = SPI_exec(cmd,0); if(ret < 0 ){
elog(ERROR,"SPI_exec returns error!");
} SPI_finish();
PG_RETURN_POINTER(rettuple);
}创建函数----CREATE FUNCTION function_name() AS 'file_path','trig_update_oids' language 'c';
创建触发器-------CREATE TRIGGER trigger_name AFTER ON table_name FOR EACH ROW EXECUTE PROCEDURE function_name;
经过这几天的摸索,也算有了一点眉目。
贴出来,算是给自己一个交代吧。#include "executor/spi.h" /* 用SPI的时候要用的头文件 */
#include "commands/trigger.h" /* -"- 用触发器要用的头 */
#include "postgres.h"#define _debug_ 1/*extern Datum trig_update_oid(PG_FUNCTION_ARGS);*/
/*触发器trig_update_oid 当在表中插入(或删除)一个元组时,
将该元组的 oid 在表oids中插入(或删除)。只能以AFTER创建 */PG_FUNCTION_INFO_V1(trig_update_oid);Datum
trig_update_oid(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *)fcinfo->context;
HeapTuple rettuple;
char cmd[200];
char crttb[] = "create table oids (tup_oid oid primary key)";
int ret; /* 确信触发器数据指向我预期的地方 */
if(!CALLED_AS_TRIGGER(fcinfo)){
elog(ERROR, "trigf_update_oid(): not fired by trigger manager");
}
/* 返回给执行者的元组 */
if( TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event) ){#ifdef _debug_
elog(NOTICE,"trig_update_oid:fired by update");
#endif PG_RETURN_POINTER(rettuple);
}
else{
rettuple = trigdata->tg_trigtuple;
} if (TRIGGER_FIRED_BEFORE(trigdata->tg_event)){
#ifdef _debug_
elog(NOTICE,"trig_update_oid:fired before");
#endif PG_RETURN_NULL();
} /*是否是由INSERT触发*/
if(TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)){
sprintf(cmd,"insert into oids values(%d)",HeapTupleGetOid(rettuple) );
}
else{
sprintf(cmd,"delete from oids where tup_oid = %d",HeapTupleGetOid(rettuple));
}
#ifdef _debug_
elog(NOTICE,"trig_update_oid:cmd = %s",cmd);
#endif if(SPI_connect() == SPI_ERROR_CONNECT){
elog(ERROR,"Can't connect to the SPI manager");
} ret = SPI_exec("select relname from pg_class where relname = 'oids'" ,0); if(ret < 0 ){
elog(ERROR,"SPI_exec returns error!");
} if(SPI_processed <= 0){
ret = SPI_exec(crttb,0);
if(ret < 0 ){
elog(ERROR,"SPI_exec returns error!");
}
}
ret = SPI_exec(cmd,0); if(ret < 0 ){
elog(ERROR,"SPI_exec returns error!");
} SPI_finish();
PG_RETURN_POINTER(rettuple);
}创建函数----CREATE FUNCTION function_name() AS 'file_path','trig_update_oids' language 'c';
创建触发器-------CREATE TRIGGER trigger_name AFTER ON table_name FOR EACH ROW EXECUTE PROCEDURE function_name;
解决方案 »
- mysql administrator 恢复备份的问题
- 关于时间类型转换的问题。。。
- mysqldumpslow sql语句分析
- windows下php+postgresql的配置问题
- 这条sql库的语句,用oracle 的怎么写
- 分数不够再给 mysql odbc 配置 如果设置root有密码就出错,没有密码就不出错,其他用户出错信息如下:
- mysql主从出错,网上搜不出来解决办法,求指导.
- http://www.pdfan.com/ 欢迎各位指点指点我做的这个网页,不过里面就一张页面,实在找不到可以免费提供php和mysql空间的地方。
- 新人求教,关于sql查询语句的
- 两个表中数据分类汇总合并的问题
- Linux 下用c语言访问mysql的问题;
- 字段自动变值,怎么做,没多少分了,就20了,谢谢
经过这几天的摸索,也算有了一点眉目。
贴出来,算是给自己一个交代吧。
#include "executor/spi.h " /* 用SPI的时候要用的头文件 */
#include "commands/trigger.h " /* - "- 用触发器要用的头 */
#include "postgres.h "
#define _debug_ 1
/*extern Datum trig_update_oid(PG_FUNCTION_ARGS);*/
/*触发器trig_update_oid 当在表中插入(或删除)一个元组时,
将该元组的 oid 在表oids中插入(或删除)。只能以AFTER创建 */
PG_FUNCTION_INFO_V1(trig_update_oid);
Datum
trig_update_oid(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *)fcinfo- >context;
HeapTuple rettuple;
char cmd[200];
char crttb[] = "create table oids (tup_oid oid primary key) ";
int ret;
/* 确信触发器数据指向我预期的地方 */
if(!CALLED_AS_TRIGGER(fcinfo)){
elog(ERROR, "trigf_update_oid(): not fired by trigger manager ");
}
/* 返回给执行者的元组 */
if( TRIGGER_FIRED_BY_UPDATE(trigdata- >tg_event) ){
#ifdef _debug_
elog(NOTICE, "trig_update_oid:fired by update ");
#endif
PG_RETURN_POINTER(rettuple);
}
else{
rettuple = trigdata- >tg_trigtuple;
}
if (TRIGGER_FIRED_BEFORE(trigdata- >tg_event)){
#ifdef _debug_
elog(NOTICE, "trig_update_oid:fired before ");
#endif
PG_RETURN_NULL();
}
/*是否是由INSERT触发*/
if(TRIGGER_FIRED_BY_INSERT(trigdata- >tg_event)){
sprintf(cmd, "insert into oids values(%d) ",HeapTupleGetOid(rettuple) );
}
else{
sprintf(cmd, "delete from oids where tup_oid = %d ",HeapTupleGetOid(rettuple));
}
#ifdef _debug_
elog(NOTICE, "trig_update_oid:cmd = %s ",cmd);
#endif
if(SPI_connect() == SPI_ERROR_CONNECT){
elog(ERROR, "Can't connect to the SPI manager ");
}
ret = SPI_exec( "select relname from pg_class where relname = 'oids' " ,0);
if(ret < 0 ){
elog(ERROR, "SPI_exec returns error! ");
}
if(SPI_processed <= 0){
ret = SPI_exec(crttb,0);
if(ret < 0 ){
elog(ERROR, "SPI_exec returns error! ");
}
}
ret = SPI_exec(cmd,0);
if(ret < 0 ){
elog(ERROR, "SPI_exec returns error! ");
}
SPI_finish();
PG_RETURN_POINTER(rettuple);
}
创建函数----CREATE FUNCTION function_name() AS 'file_path','trig_update_oids' language 'c';
创建触发器-------CREATE TRIGGER trigger_name AFTER ON table_name FOR EACH ROW EXECUTE PROCEDURE function_name;