PostgreSQL的 SPI_介面函式

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的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_的介面函式，去實現一些功能。