PG插件开发
1.概述
相关目录结构如下:
在contrib目录下进行插件开发。
2.定义插件——PGSQL
在contrib下创建目录add_ten,并完成相应的.sql/.control/Makefile文件。
# add_ten--1.0.sql
CREATE OR REPLACE FUNCTION add_ten(sum INTEGER)
RETURNS INTEGER AS $$
DECLARE
result INTEGER;
BEGIN
result=sum+10;
RETURN result;
END; $$
LANGUAGE plpgsql;
# add_ten.control
comment = 'Add ten to given number' # 备注
default_version = '1.0' # 对应sql文件名中的版本
module_pathname = '$libdir/add_ten'
relocatable = false
# Makefile
EXTENSION = add_ten #对应文件夹的名字
DATA = add_ten--1.0.sql #对应sql文件的名字
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
make install
后会在share/extention/
下生成对应的.control和.sql文件
3.定义插件——C
在contrib下创建目录add_ten,并完成相应的.sql/.c/.control/Makefile文件。
# add_cfun--1.0.sql
CREATE OR REPLACE FUNCTION add_cfun(arg INTEGER) RETURNS INTEGER AS
'MODULE_PATHNAME','add_cfun'
LANGUAGE C STRICT;
# add_cfun.c
#include "/home/postgres/postgresql-14/include/server/postgres.h"
#include "/home/postgres/postgresql-14/include/server/fmgr.h"
#include "/home/postgres/postgresql-14/include/server/utils/errcodes.h"
PG_MODULE_MAGIC;
PGDLLEXPORT Datum add_cfun(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(add_cfun);
Datum
add_cfun(PG_FUNCTION_ARGS)
{
int32 arg = PG_GETARG_INT32(0);
PG_RETURN_INT32(arg + 10);
}
# add_cfun.control
comment = 'add cfun'
default_version = '1.0'
module_pathname = '$libdir/add_cfun'
relocatable = true
# Makefile
MODULES = add_cfun
EXTENSION = add_cfun
DATA = add_cfun--1.0.sql
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
make install
后会在share/extention/
下生成对应的.control和.sql文件,在当前目录下生成.o/.so文件。
4.添加插件
//查询可用插件版本
select name from pg_available_extensions;
//查询已安装插件版本
/dx
#安装插件
create extension add_cfun;
//使用插件
select add_cfun(3);
5.make install时可能出现的问题
# pg_config executable not found
echo "export PATH=$PATH:$PGHOME/bin" >> /etc/profile
source /etc/profile
6.参考资料
https://postgreshelp.com/postgresql-extension/