PostgreSQL 扩展Extension 简单小实例

摘要:PostgreSQL被设计为易于扩展.PostgreSQL引入了一种方式来安装contrib模块，称为扩展（extensions）。
此方法适用于所有使用扩展规范构建的contrib模块，包括如下：
扩展SQL文件（ extension_name.sql ）
扩展控制文件 （ extension_name.control ）

扩展库文件（extension_name.so）

mkdir test

2.进入目录下创建四个文件:Makefile ,  test–1.0.sql , test.c  ,test.control

me@me:~/Desktop/postgresql-10.1/contrib/test$ ls
Makefile  test--1.0.sql  test.c  test.control

3.Makefile文件内容:

# contrib/test/Makefile


MODULES = test

EXTENSION =test
DATA = test--1.0.sql
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
else
subdir = contrib/test
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif

4.test–1.0.sql文件内容:

/* contrib/test/test--1.0.sql */

--complain if script is sourced in psql rather than via ALTER EXTENSION
\echo Use "CRAETE EXTENSION test_tabble" to load this file. \quit

CREATE TABLE test_table(oid integer,namespace_oid integer,name text,time timestamp);        /* 创建一个表格 */


CREATE FUNCTION test_add_fun(integer,integer)           /* 创建一个函数 */
RETURNS integer
AS 'MODULE_PATHNAME' , 'test_add_fun'
LANGUAGE C STRICT PARALLEL RESTRICTED;

5.test.c文件内容:

#include "postgres.h"
#include "fmgr.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif


PG_FUNCTION_INFO_V1(test_add_fun);

Datum test_add_fun(PG_FUNCTION_ARGS);

Datum test_add_fun(PG_FUNCTION_ARGS)
{
	int sum ,a,b;
	
	a=PG_GETARG_INT32(0);
	b=PG_GETARG_INT32(1);

	sum = a + b;
	PG_RETURN_INT32(sum);
}

6.test.control文件内容:

# test extension

comment = 'only test publication'
default_version = '1.0'
module_pathname = '$libdir/test'
relocatable = true

7. 返回上一层目录contrib根目录下Makefile文件:添加扩展名

8. 进入test目录make & make install:

me@me:~/Desktop/postgresql-10.1/contrib$ cd test
me@me:~/Desktop/postgresql-10.1/contrib/test$ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I. -I../../src/include  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o test.o test.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,'/opt/HighGo/postgrsql10.1/lib',--enable-new-dtags  -shared -o test.so test.o
me@me:~/Desktop/postgresql-10.1/contrib/test$ make install
/bin/mkdir -p '/opt/HighGo/postgrsql10.1/share/postgresql/extension'
/bin/mkdir -p '/opt/HighGo/postgrsql10.1/share/postgresql/extension'
/bin/mkdir -p '/opt/HighGo/postgrsql10.1/lib/postgresql'
/usr/bin/install -c -m 644 ./test.control '/opt/HighGo/postgrsql10.1/share/postgresql/extension/'
/usr/bin/install -c -m 644 ./test--1.0.sql  '/opt/HighGo/postgrsql10.1/share/postgresql/extension/'
/usr/bin/install -c -m 755  test.so '/opt/HighGo/postgrsql10.1/lib/postgresql/'

9.重启psql客户端:

me@me:/opt/HighGo/postgresql10.1/bin$ pg_ctl restart
2018-04-09 14:25:11.925 CST [3251] 日志:  接收到快速 (fast) 停止请求
等待服务器进程关闭 ....2018-04-09 14:25:11.930 CST [3251] 日志:  中断任何激活事务
2018-04-09 14:25:11.931 CST [3251] 日志:  工作进程: logical replication launcher (PID 3258) 已退出, 退出代码 1
2018-04-09 14:25:11.931 CST [3253] 日志:  正在关闭
2018-04-09 14:25:11.949 CST [3251] 日志:  数据库系统已关闭
 完成
服务器进程已经关闭
等待服务器进程启动 ....2018-04-09 14:25:12.040 CST [6760] 日志:  listening on IPv6 address "::1", port 5866
2018-04-09 14:25:12.040 CST [6760] 日志:  listening on IPv4 address "127.0.0.1", port 5866
2018-04-09 14:25:12.043 CST [6760] 日志:  listening on Unix socket "/tmp/.s.PGSQL.5866"
2018-04-09 14:25:12.061 CST [6761] 日志:  数据库上次关闭时间为 2018-04-09 14:25:11 CST
2018-04-09 14:25:12.065 CST [6760] 日志:  数据库系统准备接受连接
 完成
服务器进程已经启动

10.检查验证输出:

me@me:/opt/HighGo/postgrsql10.1/bin$ ./psql
psql (10.1)
输入 "help" 来获取帮助信息.

me=# \d
Did not find any relations.
me=# create extension test;
CREATE EXTENSION
me=# \d
                关联列表
 架构模式 |    名称    |  类型  | 拥有者 
----------+------------+--------+--------
 public   | test_table | 数据表 | me
(1 行记录)

me=# \sf test_add_fun
CREATE OR REPLACE FUNCTION public.test_add_fun(integer, integer)
 RETURNS integer
 LANGUAGE c
 PARALLEL RESTRICTED STRICT
AS '$libdir/test', $function$test_add_fun$function$
me=# select test_add_fun(2,3);
 test_add_fun 
--------------
            5
(1 行记录)

me=# drop extension test;
DROP EXTENSION

done
转载自：https://blog.csdn.net/liguangxianbin/article/details/79865747