Server Programming Interface(SPI)是PostgreSQL内核中的一个模块,这个模块让内核开发者可以在C函数中执行SQL语句,并具备管理事务的能力。通过它我们可以用C语言去调用数据库里的各种SQL。
这个SPI_比较便利的一点在于,我们可以在自定义的Extension中也可以使用它,去用C调用SQL执行,也就是说,如果我们想的话,我们可以在Extension里用C语言定义一个函数,在函数里拼接出我们想要执行的SQL,并调用SPI_的相关接口函数,在数据库里实际执行该SQL。因为是以Extension的形式去做的,所以,不想使用了,直接drop extension就可以了。
在spi.h里我们可以看到有很多的SPI_的函数。具体的可以参考官方文档PostgreSQL: Documentation: 14: Chapter 47. Server Programming Interface
因为此前发过自定义PostgreSQL的extension的文章,在此就不做赘述了,直接放链接:如何为PostgreSQL创建自定义Extension
下面,我将举个小例子,在Extension里使用这些SPI_接口函数,来实现一个数据库里的函数,在PostgreSQL里执行SQL。关键的部分如下,通过逻辑,其实可以看出来,我这里做了一个巨鸡肋的功能————通过函数输入一个表名和一个路径文件,函数拼接了一个COPY的SQL来实现copy to拷贝表的功能,但是为了演示SPI_的接口函数已经足够了。
PG_FUNCTION_INFO_V1(pg_copy_plugin_out);
Datum pg_copy_plugin_out(PG_FUNCTION_ARGS)
{
int SPI_connect(void);
int SPI_exec(const char * command, long count);
int SPI_finish(void);
char *plugintableName = text_to_cstring(PG_GETARG_TEXT_PP(0));
char *filepath = text_to_cstring(PG_GETARG_TEXT_PP(1));
char command[128];
sprintf(command,"copy %s to \'%s\';", plugintableName,filepath);
SPI_connect();
SPI_exec(command, 0);
SPI_finish();
SPI_connect的主要作用是连接一个C函数到 SPI 管理器 。
SPI_exec的作用是执行一个读/写命令。
SPI_finish的作用是将一个C函数从 SPI 管理器断开。
我这里是拼接后直接执行了,因此只用到这几个,如果想获取prepare语句,但不执行等,可以去使用其他类型的SPI_接口函数,具体可以参考手册。
其实在后边返回值那里,我遇到了点障碍,所以,我加了一个 ereport(ERROR,去抛出一个报错,但却能让我的功能正常完成。
[postgres@localhost pg_copy_plugin]$ ll
total 16
-rw-r--r-- 1 postgres dba 217 Jun 15 05:13 Makefile
-rw-r--r-- 1 postgres dba 642 Aug 1 15:26 pg_copy_plugin--1.0.sql
-rw-r--r-- 1 postgres dba 1013 Aug 1 17:29 pg_copy_plugin.c
-rw-r--r-- 1 postgres dba 137 Jun 15 05:12 pg_copy_plugin.control
[postgres@localhost pg_copy_plugin]$ make
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/home/postgres/soft/include/server -I/home/postgres/soft/include/internal -D_GNU_SOURCE -c -o pg_copy_plugin.o pg_copy_plugin.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC pg_copy_plugin.o -L/home/postgres/soft/lib -Wl,--as-needed -Wl,-rpath,'/home/postgres/soft/lib',--enable-new-dtags -shared -o pg_copy_plugin.so
[postgres@localhost pg_copy_plugin]$ make install
/bin/mkdir -p '/home/postgres/soft/share/extension'
/bin/mkdir -p '/home/postgres/soft/share/extension'
/bin/mkdir -p '/home/postgres/soft/lib'
/bin/install -c -m 644 .//pg_copy_plugin.control '/home/postgres/soft/share/extension/'
/bin/install -c -m 644 .//pg_copy_plugin--1.0.sql '/home/postgres/soft/share/extension/'
/bin/install -c -m 755 pg_copy_plugin.so '/home/postgres/soft/lib/'
依次的编译安装,make阶段产生了.so的文件,make install阶段,把.control和.sql文件拷贝到share路径下,将.so文件拷贝到lib路径下。
我们到数据库里去安装这个自定义的插件,下边的这个pg_copy_plugin_out 就是我们刚才定义的那个函数。
[postgres@localhost pg_copy_plugin]$ psql
psql (15beta1)
Type "help" for help.
postgres=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)
postgres=# create extension pg_copy_plugin;
CREATE EXTENSION
postgres=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------------------+------------------+--------------------------------------------+------
public | pg_bak_tab | text | ta character varying, fa character varying | func
public | pg_copy_plugin_out | text | plugintablename text, filename text | func
(2 rows)
来验证一下这个函数的功能,可以看到,他是使用c实现的,输入的两个参数是两个text类型,返回值也是text。
可以看到,这个函数通过使用SPI_的接口函数,实现了在C语言层面根据输入值拼接SQL并在数据库执行的功能。(…其实在数据库里用plpgsql写一个函数就可以达到同样的效果,所以我说很鸡肋。但是本文主要就是展示SPI_接口函数在Extension里的一个大致使用)
postgres=# \! ls -l /home/postgres/ysl.sql
ls: cannot access /home/postgres/ysl.sql: No such file or directory
postgres=# select pg_copy_plugin_out('t1','/home/postgres/ysl.sql');
ERROR: copy t1 to '/home/postgres/ysl.sql';
postgres=# \! ls -l /home/postgres/ysl.sql
-rw-r--r-- 1 postgres dba 11679 Aug 1 17:55 /home/postgres/ysl.sql
postgres=# \! head -10 /home/postgres/ysl.sql
1
2
3
4
5
6
7
8
9
10
到此,这个演示结束了。感兴趣的也可以去尝试使用这些SPI_的接口函数,去实现一些功能。