在PostgreSQL自定义函数中引用其它lib库中的函数

今天看到pgfans上有人提了一个如下问题.

于是想着着手实验一把，步骤如下

创建一个第三方库

自定义库源码

/home/jintao/personal/code/testfunc/func.c中写入如下内容

#include "func.h"

int fun(int add)
{
    return add + 100;
}

/home/jintao/personal/code/testfunc/func.h中写入如下内容

#ifndef __FUNC_H__
#define __FUNC_H__

#include <stdio.h>

int fun(int add);

#endif

自定义库编译安装

编译生成so文件

gcc func.c -I . -shared -fPIC -o libfunc.so

创建新扩展test

新建目录

PostgreSQL源代码中contrib下新建目录

mkdir test
cd test

编写控制文件

vim test.control

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

主要代码文件

vim test.c

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

#include "func.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(test_add_fun);
PG_FUNCTION_INFO_V1(test_add_100_fun);


Datum test_add_fun(PG_FUNCTION_ARGS);
Datum test_add_100_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);
}


Datum test_add_100_fun(PG_FUNCTION_ARGS)
{
    int a = PG_GETARG_INT32(0);

    PG_RETURN_INT32(fun(a));
}

相关SQL

vim test—1.0.sql

/* contrib/test/test--1.0.sql */
--complain if script is source in psql rather than bia ALTER EXTENSION

CREATE TABLE test_table(oid int, namespace_oid int, name text, time timestamp);

CREATE FUNCTION test_add_fun(int, int)
RETURNS int
AS 'MODULE_PATHNAME', 'test_add_fun'
LANGUAGE C STRICT PARALLEL RESTRICTED;

CREATE FUNCTION test_add_100_fun(int)
RETURNS int
AS 'MODULE_PATHNAME', 'test_add_100_fun'
LANGUAGE C STRICT PARALLEL RESTRICTED;

makefile代码

vim Makefile

# contrib/test/Makefile

MODULE_big = test
OBJS = \
    $(WIN32RES) \
    test.o

EXTENSION = test
DATA = test--1.0.sql
PGFILEDESC = "test - just test"

REGRESS = test

# 引用的库
SHLIB_LINK = /home/jintao/personal/code/testfunc/libfunc.so

# 编译时选项
PG_LIBS=-lfunc
PG_LDFLAGS=-L/home/jintao/personal/code/testfunc
PG_CPPFLAGS=-I/home/jintao/personal/code/testfunc

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

编译

jintao@linux:~/personal/code/postgresql/contrib/test$ make
make -C ../../src/backend generated-headers
make[1]: Entering directory '/home/jintao/personal/code/postgresql/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory '/home/jintao/personal/code/postgresql/src/backend/catalog'
make[2]: Nothing to be done for 'distprep'.
make[2]: Nothing to be done for 'generated-header-symlinks'.
make[2]: Leaving directory '/home/jintao/personal/code/postgresql/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory '/home/jintao/personal/code/postgresql/src/backend/utils'
make[2]: Nothing to be done for 'distprep'.
make[2]: Nothing to be done for 'generated-header-symlinks'.
make[2]: Leaving directory '/home/jintao/personal/code/postgresql/src/backend/utils'
make[1]: Leaving directory '/home/jintao/personal/code/postgresql/src/backend'
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -O0 -fPIC -I/home/jintao/personal/code/testfunc -I. -I. -I../../src/include  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o test.o test.c -MMD -MP -MF .deps/test.Po
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -O0 -fPIC -shared -o test.so  test.o -L/home/jintao/personal/code/testfunc -L../../src/port -L../../src/common   -Wl,--as-needed -Wl,-rpath,'/opt/pg-master/lib',--enable-new-dtags  /home/jintao/personal/code/testfunc/libfunc.so

安装

make -C ../../src/backend generated-headers
make[1]: Entering directory '/home/jintao/personal/code/postgresql/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory '/home/jintao/personal/code/postgresql/src/backend/catalog'
make[2]: Nothing to be done for 'distprep'.
make[2]: Nothing to be done for 'generated-header-symlinks'.
make[2]: Leaving directory '/home/jintao/personal/code/postgresql/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory '/home/jintao/personal/code/postgresql/src/backend/utils'
make[2]: Nothing to be done for 'distprep'.
make[2]: Nothing to be done for 'generated-header-symlinks'.
make[2]: Leaving directory '/home/jintao/personal/code/postgresql/src/backend/utils'
make[1]: Leaving directory '/home/jintao/personal/code/postgresql/src/backend'
/usr/bin/mkdir -p '/opt/pg-master/lib/postgresql'
/usr/bin/mkdir -p '/opt/pg-master/share/postgresql/extension'
/usr/bin/mkdir -p '/opt/pg-master/share/postgresql/extension'
/usr/bin/install -c -m 755  test.so '/opt/pg-master/lib/postgresql/test.so'
/usr/bin/install -c -m 644 ./test.control '/opt/pg-master/share/postgresql/extension/'
/usr/bin/install -c -m 644 ./test--1.0.sql  '/opt/pg-master/share/postgresql/extension/'

验证

jintao@linux:~/personal/code/testfunc$ psql 
psql (15beta1)
Type "help" for help.

mydb=# create extension test ;
CREATE EXTENSION
mydb=# 
mydb=# \dx+ test 
      Objects in extension "test"
           Object description           
----------------------------------------
 function test_add_100_fun(integer)
 function test_add_fun(integer,integer)
 table test_table
(3 rows)

mydb=# select test_add_100_fun(1);
 test_add_100_fun 
------------------
              101
(1 row)

可以看到SQL可以被正常执行.