# FreeBSD 12下用Clang开发 Postgresql v9.6.1 的 C 扩展
#作者：老农民
#QQ: 46715422
#Email: flyingercn@wo.cn
#微信： ericliu_77



root@FreeBSD:/soft # uname -a && clang --version
FreeBSD FreeBSD 12.0-CURRENT FreeBSD 12.0-CURRENT #0 r308137: Mon Oct 31 20:28:04 UTC 2016     root@releng3.nyi.freebsd.org:/usr/obj/usr/src/sys/GENERIC  i386
FreeBSD clang version 3.8.0 (tags/RELEASE_380/final 262564) (based on LLVM 3.8.0)
Target: i386-unknown-freebsd12.0
Thread model: posix
InstalledDir: /usr/bin
root@FreeBSD:/soft # 


# 假设我的 Postgresql 安装在 /usr/local/pgsql

# 创建用户 root，创建测试库 cext

su - postgres -c "/usr/local/pgsql/bin/createuser -h localhost -p 5432 -d -A -P -e root"
su - postgres -c "/usr/local/pgsql/bin/createdb cext -O root -E UTF8"

root@FreeBSD:/soft # su - postgres -c "/usr/local/pgsql/bin/psql cext" 
psql (9.6.1)
输入 "help" 来获取帮助信息.

cext=# create table tb_cext(id int,name text,age int);
CREATE TABLE
cext=# insert into tb_cext 
cext-# (id,name,age)
cext-# values 
cext-# (1,'a',12),cext-# (2,'b',19),cext-# (3,'c',33),cext-# (4,'d',cext-# (5,'e',15),cext-# (6,'f',19);
INSERT 0 6

cext=# select * from tb_cext; 
 id | name | age 
----+------+-----
  1 | a    |  12
  2 | b    |  19
  3 | c    |  33
  4 | d    |  19
  5 | e    |  15
  6 | f    |  19
(6 行记录)

# 在当前目录下建立C源文件,C代码如下：
vi tb_cext.c

#include "postgres.h"
#include "executor/spi.h"
#include "utils/builtins.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

int extdelete(int key);

int
extdelete(int key)
{
    char command[128];
    int ret;
    int proc;

    sprintf(command,"delete from tb_cext where age = %d ; ",key);

    SPI_connect();
    ret = SPI_exec( command,0);
    proc = SPI_processed;
    SPI_finish();
    return (proc);
}

# 编译
clang -fpic -I/usr/local/include/ -I/usr/local/pgsql/include/server/ -shared -o tb_cext.so tb_cext.c

# 将 tb_cext.so 复制到 /usr/local/pgsql/lib 下
mv tb_cext.so /usr/local/pgsql/lib/

# 加载C扩展到服务器
root@FreeBSD:/soft # su - postgres -c "/usr/local/pgsql/bin/psql cext"
psql (9.6.1)
输入 "help" 来获取帮助信息.
cext=# load 'tb_cext';
LOAD
cext=#

-- 创建函数 extdel()
cext=# create function extdel(integer) returns integer as '/usr/local/pgsql/lib/tb_cext.so','extdelete' language c strict;
CREATE FUNCTION


-- 执行函数 extdel()，删除年龄等于19岁的
cext=# select extdel(19);
 extdel 
--------
      3
(1 行记录)


-- 执行后的结果集
cext=# select * from tb_cext; 
 id | name | age 
----+------+-----
  1 | a    |  12
  3 | c    |  33
  5 | e    |  15
(3 行记录)

要买正宗新疆特产，微信扫下面二维码，加老农民咨询购买：