PostgreSQL用C完成存储过程例子

目的：用C完成一个存储过程例子，存储过程实现对表某一段进行update。

准备工作

1、安装数据库

2、建立表test

1highgo=# create table test(id int, name text, label int);
2CREATE TABLE

3、建立C文件,C代码如下：

1#include "postgres.h"
2#include "executor/spi.h"
3#include "utils/builtins.h"
4
5#ifdef PG_MODULE_MAGIC
6PG_MODULE_MAGIC;
7#endif
8
9int mydelete(int key);
10
11int
12mydelete(int key)
13{
14    char command[128];  //视命令长短建立相应大小的数组
15    int ret;
16    int proc;                       //对表数据操作的行数
17
18    /* 将命令赋值到command */
19    sprintf(command, "update test set label = 0 where id = %d and label = 1; ", key);
20
21    SPI_connect();             //内部链接
22    ret = SPI_exec( command, 0);  //执行操作
23    proc = SPI_processed;       //为行数赋值
24    SPI_finish();                //中断连接
25    return (proc);               //将操作行数作为返回结果
26}

数据库api参考文档：http://www.postgresql.org/docs/9.4/static/spi.html

编译到安装

4、gcc编译

gcc -fpic -I/opt/HighGo/db/20150401/include/postgresql/server/ -shared -o myapi.so myapi.c

5、复制到lib目录下

cp myapi.so /opt/HighGo/db/20150401/lib/postgresql/

6、加载到服务器

1highgo=# load 'myapi';
2LOAD

7、建立函数

1highgo=# create function mydele(integer) returns integer as '$libdir/myapi.so','mydelete' language c strict;
2CREATE FUNCTION
3highgo=#

8、效果

1highgo=# insert into test values (1,'jim',1);
2INSERT 0 1
3highgo=# insert into test values (2,'tom',1);
4INSERT 0 1
5highgo=# select * from test;
6 id | name | label 
7----+------+-------
8  1 | jim  |     1
9  2 | tom  |     1
10
11highgo=# select mydele(1);
12 mydele 
13--------
14      1
15(1 row)
16highgo=# select * from test;
17 id | name | label 
18----+------+-------
19  2 | tom  |     1
20  1 | jim  |     0