postgresql自定义函数实现，通过contrib模块进行扩展

概要

PostgreSQL从设计上是可扩展的，从用户的角度可以通过四种方式对PostgresSQL进行扩展

• query language functions (functions written in SQL)
• procedural language functions (functions written in, for example, PL/pgSQL or PL/Tcl)
• internal functions
• C-language functions

本文重点介绍C-language functions，即函数通过contrib模块进行扩展。

• 用户定义函数简介

用户定义函数(User-defined functions),可以是使用C（或者与C兼容的开发语言，比如C++）编写，这样函数可以被编译成动态库，并被PostgreSQL服务进程加载。用户定义函数与internal functions内部函数本质上是一样的，只不过内部函数是需要重新编译PostgresSQL内核的。编写上也可以参考内部函数的源码，可以给我们提供了丰富的例子。

实例

• PostgreSQL开发环境搭建
  参考上一篇文章源码编译安装postgresql

• 编写代码
  1.进入postgres目录，会看到contrib目录，如下图
  

[root@10 postgres]# cd contrib/

进入目录后会看到好多文件夹，每一文件夹就代表一个用户定义模块，里面可能包括一个或多个用户定义函数。

2.创建用户定义函数的目录

[root@10 contrib]# mkdir pginvoke

3.C范例程序pginvoke.c

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

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(pgEuropean);

Datum pgEuropean(PG_FUNCTION_ARGS)
{
	float ret,a,b;
	
	a=PG_GETARG_FLOAT4(0);
	b=PG_GETARG_FLOAT4(1);

	ret = a / b;
	PG_RETURN_FLOAT4(ret);
}

4.编写sql文件pginvoke–1.0.sql

/* contrib/pginvoke/pginvoke--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 pg_invoke(para1 float4,para12 float4, ret float);/* 创建一个表格 */
 
 
CREATE FUNCTION pgEuropean(float4,float4)/* 创建一个函数 */
RETURNS float4
AS 'MODULE_PATHNAME' , 'pgEuropean'
LANGUAGE C STRICT PARALLEL RESTRICTED;

5.编写控制文件pginvoke.control

# pginvoke extension 

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

6.编写Makefile

# contrib/pginvoke/Makefile

MODULES = pginvoke

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

编译

1.完成后目录结构如下

[root@10 pginvoke]# pwd
[root@10 pginvoke]# pwd
/root/git/postgres/contrib/pginvoke
[root@10 pginvoke]# ll
total 16
-rw-r--r-- 1 root root 334 Feb 17 20:52 Makefile
-rw-r--r-- 1 root root 296 Feb 17 20:51 pginvoke--1.0.sql
-rw-r--r-- 1 root root 265 Feb 17 20:50 pginvoke.c
-rw-r--r-- 1 root root 136 Feb 17 20:51 pginvoke.control

2.在上级目录下修改Makefile

[root@10 pginvoke]# cd ../
[root@10 contrib]# pwd
/root/git/postgres/contrib
[root@10 contrib]# vi Makefile

###
 51                 unaccent        \
 52                 vacuumlo        \
 53                 test            \
 54                 pginvoke

3.编译

[root@10 contrib]# cd /root/git/postgres/contrib/
[root@10 contrib]# make
.....
[root@10 contrib]# cd pginvoke/
[root@10 pginvoke]# pwd
/root/git/postgres/contrib/pginvoke
[root@10 pginvoke]# ll
total 36
-rw-r--r-- 1 root root   334 Feb 17 21:11 Makefile
-rw-r--r-- 1 root root   381 Feb 17 21:11 pginvoke--1.0.sql
-rw-r--r-- 1 root root   267 Feb 17 21:12 pginvoke.c
-rw-r--r-- 1 root root   144 Feb 17 21:12 pginvoke.control
-rw-r--r-- 1 root root  1800 Feb 17 21:12 pginvoke.o
-rwxr-xr-x 1 root root 16320 Feb 17 21:12 pginvoke.so

可以看到在pginvoke目录下回生成pginvoke.so

安装

1.自动安装
直接执行make install
2.手动安装
copy 下面3个文件到postgres的安装目录

cp pginvoke.control /usr/local/pgsql/share/extension
cp pginvoke--1.0.sql  /usr/local/pgsql/share/extension/
cp pginvoke.so '/usr/local/pgsql/lib/

验证

1.查看已安装的用户定义函数，目前并没有我们创建的函数。

su[root@10 ~]# su - postgres
Last login: Thu Feb 17 15:21:04 CST 2022 from 10.0.2.2 on pts/1
[postgres@10 ~]$ psql
psql (15devel)
Type "help" for help.

postgres=# \d
                  List of relations
 Schema |          Name           | Type  |  Owner
--------+-------------------------+-------+----------
 public | pg_stat_statements      | view  | postgres
 public | pg_stat_statements_info | view  | postgres
 public | test_table              | table | postgres
(3 rows)

2.查看可安装的函数，可以看到pginvoke

postgres=# select name from pg_available_extensions;
        name
--------------------
 plpgsql
.....
 pg_trgm
 pginvoke
(44 rows)
postgres=#

3.创建扩展模块pginvoke

postgres=# create extension pginvoke;
CREATE EXTENSION

4.再次查看已安装模块

已经创建成功。

5.查看函数创建脚本

postgres=# \sf
function name is required
postgres=# \sf pgEuropean
CREATE OR REPLACE FUNCTION public.pgeuropean(real, real)
 RETURNS real
 LANGUAGE c
 PARALLEL RESTRICTED STRICT
AS '$libdir/pginvoke', $function$pgEuropean$function$

6.调用函数

postgres=# select pgEuropean(10.0,3.0);
 pgeuropean
------------
  3.3333333
(1 row)

7.移除用户扩展函数

postgres=# drop extension pginvoke;
DROP EXTENSION
postgres=# \d
                  List of relations
 Schema |          Name           | Type  |  Owner
--------+-------------------------+-------+----------
 public | pg_stat_statements      | view  | postgres
 public | pg_stat_statements_info | view  | postgres
 public | test_table              | table | postgres
(3 rows)