之前写过一篇文章，PostgreSQL编写自定义extension。https://www.modb.pro/db/196831
openGauss是基于PostgreSQL发展而来，虽然有很多扩展不能直接兼容，但是也可以经过适配来进行使用,而且除此之外，openGauss把一些比较好用的插件的功能直接内置到了数据库里，提供给我们很多的便利。
下面我将介绍如何在openGauss/MogDB上编写自定义extension（实现自定义C函数），并介绍几个容易遇到的坑和解决方法，供参考。
和PostgreSQL类似，编写自定义extension比较关键的四个文件如下
omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ ls
Makefile ysla--1.0.sql ysla.c ysla.control
其中
一个格式为extension_name.control的控制文件，它告诉openGauss关于您的扩展程序的一些基础知识
一个扩展程序的SQL脚本文件，格式为extension--version.sql
一个c实现的extension_name.c文件，可以根据功能需求编写
一个Makefile文件帮助编译
在本文中，我定义了一个名为ysla的extension，比较简单，扩展包含一个表和一个函数，仅仅用一个函数验证extension的功能是否正常。（简单用C函数实现一个减法函数，输入两个值，然后用两个值做减法）
一、文件准备
在使用openGauss的初始系统用户下建立一个test_gauss_extension目录，开始编写如下四个文件
omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ pwd
/home/omm2/test_gauss_extension
omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ ls
Makefile ysla--1.0.sql ysla.c ysla.control
1.编写ysla–1.0.sql文件
/* ysla--1.0.sql */
--complain if script is sourced in psql rather than via ALTER EXTENSION
echo Use "CREATE EXTENSION ysla" to load this file. quit
CREATE TABLE tab_ysla(oid integer,namespace_oid integer,name text,time timestamp); /* 创建一个表 */
CREATE FUNCTION sub_ab(int,int) /* 创建一个函数 */
RETURNS int
AS '$libdir/ysla.so'
LANGUAGE C STRICT;
2.编写ysla.control文件
# ysl extension
comment = ' xmaster '
default_version = '1.0'
#module_pathname = 'ysla.so'
relocatable = true
3.编写Makefile文件
# contrib/ysl/Makefile
#MODULES = ysla
EXTENSION =ysla
DATA = ysla--1.0.sql
MODULES = ysla
exclude_option = -fPIE
override CPPFLAGS := $(filter-out $(exclude_option),$(CPPFLAGS))
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
4.编写ysla.c文件
#include "postgres.h"
#include "fmgr.h"
PG_MODULE_MAGIC;
extern "C" Datum sub_ab(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(sub_ab);
Datum
sub_ab(PG_FUNCTION_ARGS)
{
int32 arg_a = PG_GETARG_INT32(0);
int32 arg_b = PG_GETARG_INT32(1);
PG_RETURN_INT32(arg_a - arg_b);
}
二、编译及插件使用
1.编译插件
omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ ls
Makefile ysla--1.0.sql ysla.c ysla.control
1.1 执行make编译
omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ make
g++ -std=c++11 -D_GLIBCXX_USE_CXX11_ABI=0 -fsigned-char -DSTREAMPLAN -DPGXC -march=armv8-a+crc+lse -O2 -g3 -D__USE_NUMA -D__ARM_LSE -Wall -Wpointer-arith -Wno-write-strings -fnon-call-exceptions -fno-common -freg-struct-return -pipe -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -DENABLE_GSTRACE -fno-aggressive-loop-optimizations -Wno-attributes -fno-omit-frame-pointer -fno-expensive-optimizations -Wno-unused-but-set-variable -fPIE -I. -I. -I/opt/openGauss5.0/soft/include/postgresql/server -I/opt/openGauss5.0/soft/include/postgresql/internal -I/opt/openGauss5.0/soft/lib/postgresql/pgxs/src/makefiles/../../src/lib/gstrace -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/unixodbc/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/libobs/comm/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/libcgroup/comm/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/openssl/comm/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/boost/comm/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/llvm/comm/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/kerberos/comm/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/cjson/comm/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/numactl/comm/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/zlib1.2.11/comm/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/lz4/comm/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/zstd/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/libcurl/comm/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/component/dcf/include -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/masstree/comm/include -DOPENEULER_MAJOR -c -o ysla.o ysla.c
g++ -std=c++11 -D_GLIBCXX_USE_CXX11_ABI=0 -fsigned-char -DSTREAMPLAN -DPGXC -march=armv8-a+crc+lse -O2 -g3 -D__USE_NUMA -D__ARM_LSE -Wall -Wpointer-arith -Wno-write-strings -fnon-call-exceptions -fno-common -freg-struct-return -pipe -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -DENABLE_GSTRACE -fno-aggressive-loop-optimizations -Wno-attributes -fno-omit-frame-pointer -fno-expensive-optimizations -Wno-unused-but-set-variable -fstack-protector -Wl,-z,relro,-z,now -Wl,-z,noexecstack -std=c++14 -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -fpic -L/opt/openGauss5.0/soft/lib -pthread -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/zlib1.2.11/comm/lib -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/zlib1.2.11/comm/include -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/zstd/lib -I/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/zstd/include -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/platform/Huawei_Secure_C/comm/lib -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/openssl/comm/lib -L/usr1/build/workspace/openGauss-third_party_binarylibs/buildtools/libstd/gcc7.3.0/comm/lib -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/libcgroup/comm/lib -L -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/unixodbc/lib -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/libobs/comm/lib -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/kerberos/comm/lib -L/opt/openGauss5.0/soft/lib/postgresql/pgxs/src/makefiles/../../src/gstrace//common -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/numactl/comm/lib -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/libcurl/comm/lib -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/masstree/comm/lib -L/usr1/gauss_jenkins/jenkins/workspace/openGauss/thirdparty/dependency/libxml2/install_comm/lib -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/libxml2/comm/lib -L/usr1/build/workspace/openGauss-third_party_binarylibs/kernel/dependency/jemalloc/release/lib -shared -o ysla.so ysla.o
rm ysla.o
omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ ls
Makefile ysla--1.0.sql ysla.c ysla.control ysla.so
1.2 执行make insall安装插件
omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ ls
Makefile ysla--1.0.sql ysla.c ysla.control ysla.so
omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ make install
/usr/bin/mkdir -p '/opt/openGauss5.0/soft/share/postgresql/extension'
/usr/bin/mkdir -p '/opt/openGauss5.0/soft/share/postgresql/extension'
/usr/bin/mkdir -p '/opt/openGauss5.0/soft/lib/postgresql'
/bin/sh /opt/openGauss5.0/soft/lib/postgresql/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./ysla.control '/opt/openGauss5.0/soft/share/postgresql/extension/'
/bin/sh /opt/openGauss5.0/soft/lib/postgresql/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./ysla--1.0.sql '/opt/openGauss5.0/soft/share/postgresql/extension/'
/bin/sh /opt/openGauss5.0/soft/lib/postgresql/pgxs/src/makefiles/../../config/install-sh -c -m 755 ysla.so '/opt/openGauss5.0/soft/lib/postgresql/'
make install后会自动根据环境变量拷贝文件到对应的目录
omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ ll /opt/openGauss5.0/soft/share/postgresql/extension/ysla*
-rw-r--r-- 1 omm2 omm2 435 Jan 23 17:19 /opt/openGauss5.0/soft/share/postgresql/extension/ysla--1.0.sql
-rw-r--r-- 1 omm2 omm2 155 Jan 23 17:19 /opt/openGauss5.0/soft/share/postgresql/extension/ysla.control
omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ ll /opt/openGauss5.0/soft/lib/postgresql/ysla*
-rwxr-xr-x 1 omm2 omm2 244152 Jan 23 17:19 /opt/openGauss5.0/soft/lib/postgresql/ysla.so*
2.使用插件
创建ysla的extension然后验证功能，功能正常。
omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ gsql -r
gsql ((openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:36:31 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=# create extension ysla ;
CREATE EXTENSION
openGauss=# dx
List of installed extensions
Name | Version | Schema | Description
-----------------+---------+------------+--------------------------------------------------
dist_fdw | 1.0 | pg_catalog | foreign-data wrapper for distfs access
file_fdw | 1.0 | pg_catalog | foreign-data wrapper for flat file access
hstore | 1.1 | pg_catalog | data type for storing sets of (key, value) pairs
log_fdw | 1.0 | pg_catalog | Foreign Data Wrapper for accessing logging data
mot_fdw | 1.0 | pg_catalog | foreign-data wrapper for MOT access
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
security_plugin | 1.0 | pg_catalog | provides security functionality
ysla | 1.0 | public | xmaster
(8 rows)
openGauss=# df sub_ab
List of functions
Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind
--------+--------+------------------+---------------------+--------+------------+------------+---------
public | sub_ab | integer | integer, integer | normal | f | f | f
(1 row)
openGauss=# select sub_ab(667,1);
sub_ab
--------
666
(1 row)
三、相关问题
1.$libdir/proc_srclib/ 前缀问题
对于编译的时候，enable_default_cfunc_libpath这个参数开启的话，会自动在前面加一个 libdir/proc_srclib/ 前缀，libdir 代表是pg_config --pkglibdir的值 。所以要让so文件拷贝使用绝对路径，就要把这个参数关掉，否则要使用相对路径。
omm2@ubuntu-linux-22-04-desktop:~/test_gauss_extension$ pg_config --pkglibdir
/opt/openGauss5.0/soft/lib/postgresql
2.Makefile增加参数
Makefile文件里增加如下两条，修复 gcc 编译时候的参数，去掉 -fPIE。
exclude_option = -fPIE
override CPPFLAGS := $(filter-out $(exclude_option),$(CPPFLAGS))
否则可能会有例如这种报错
ERROR: could not find function "fun_ysl" in file "/opt/mogdb/app/lib/postgresql/ysl.so"
3.extern “C”
C 语言中并没有重载和类这些特性，编译出的符号与 C+ +不同，例如 print(int i)，不会被编译为_print_int，而是直接编译为_print 等。因此如果直接在 C+ +中调用 C 的函数会失败，加上 extern “C”，指示编译器这部分代码按 C 语言来进行编译，而不是 C++。
例如：
extern "C" Datum sub_ab(PG_FUNCTION_ARGS);
4.函数的定义需要注意，有些关键字PostgreSQL支持，但是openGauss不支持
例如openGauss的函数不支持PARALLEL语法等，有些opengauss不支持的不可以使用，这个需要注意。
5.关于不能drop extension问题
验证过插件后发现不能drop掉插件，有如下的报错
openGauss=# drop extension ysla;
ERROR: EXTENSION is not yet supported.
去翻了一下openGauss的源码，发现如果support_extended_features不开启的话，这种自定义的插件是不能删除的。
开启这个参数后，再删除extension，发现可以正常移除了。
openGauss=# show support_extended_features;
support_extended_features
---------------------------
on
(1 row)
openGauss=# create extension ysla ;
CREATE EXTENSION
openGauss=# dx ysla
List of installed extensions
Name | Version | Schema | Description
------+---------+--------+-------------
ysla | 1.0 | public | xmaster
(1 row)
openGauss=# drop extension ysla;
DROP EXTENSION
openGauss=# dx ysla
List of installed extensions
Name | Version | Schema | Description
------+---------+--------+-------------
(0 rows)
