Postgres extension 的新春祝福(2024 版)

大家好， 今天是春节前的最后一个工作日，记得去年的春节前也写了一篇文章<<来自postgres的新春祝福>> https://www.modb.pro/db/607582

所以再次打算写一个PG extension, 实现屏幕打印墨天伦 春节快乐的字体！

本篇即为简单，不讨论PG extension的工作原理，我们创建一个名字叫happy2024的extension.

创建文件happy2024 以及文件如下：

INFRA [postgres@ljzdccapp006 happy2024]# tree
.
├── happy2024--1.0.sql
├── happy2024.c
├── happy2024.control
└── Makefile

0 directories, 4 files


复制

我们直接动手写代码:

写一个C语言的文件 happy2024.c

#include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(happy2024);

Datum happy2024(PG_FUNCTION_ARGS);

Datum happy2024(PG_FUNCTION_ARGS) {
     PG_RETURN_TEXT_P(cstring_to_text("墨天伦 祝你春节快乐！"));
}


复制

写一个happy2024–1.0.sql 文件：

CREATE FUNCTION happy2024()          
RETURNS text
AS '$libdir/happy2024'
LANGUAGE C STRICT PARALLEL RESTRICTED;


复制

写一个happy2024.control 文件：

# happy2024 extension
 
default_version = '1.0'
module_pathname = '$libdir/happy2024'
relocatable = true


复制

最后我们编写Makefile 文件：

# contrib/happy2024/Makefile
  
MODULES = happy2024
EXTENSION =happy2024
DATA = happy2024--1.0.sql
MODULES = ysl
  
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)


复制

文件夹下happy2024文件编写好了之后，我们打成zip包到PG 15.3的源码contrib/下面

INFRA [postgres@ljzdccapp006 postgresql-15.3]# cp ~/happy2024.zip  /opt/pgapp/postgresql-15.3/contrib/

复制

解压安装：make && make install

INFRA [postgres@ljzdccapp006 contrib]# unzip happy2024.zip
INFRA [postgres@ljzdccapp006 happy2024]# make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -O2 -fPIC -I. -I./ -I/opt/pgsql-15/include/server -I/opt/pgsql-15/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o happy2024.o happy2024.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -O2 -fPIC happy2024.o -L/opt/pgsql-15/lib  -L/usr/lib64/llvm5.0/lib  -Wl,--as-needed -Wl,-rpath,'/opt/pgsql-15/lib',--enable-new-dtags  -shared -o happy2024.so
/opt/rh/llvm-toolset-7/root/usr/bin//clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2  -I. -I./ -I/opt/pgsql-15/include/server -I/opt/pgsql-15/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -flto=thin -emit-llvm -c -o happy2024.bc happy2024.c
INFRA [postgres@ljzdccapp006 happy2024]# make install
/bin/mkdir -p '/opt/pgsql-15/share/extension'
/bin/mkdir -p '/opt/pgsql-15/share/extension'
/bin/mkdir -p '/opt/pgsql-15/lib'
/bin/install -c -m 644 .//happy2024.control '/opt/pgsql-15/share/extension/'
/bin/install -c -m 644 .//happy2024--1.0.sql  '/opt/pgsql-15/share/extension/'
/bin/install -c -m 755  happy2024.so '/opt/pgsql-15/lib/'
/bin/mkdir -p '/opt/pgsql-15/lib/bitcode/happy2024'
/bin/mkdir -p '/opt/pgsql-15/lib/bitcode'/happy2024/
/bin/install -c -m 644 happy2024.bc '/opt/pgsql-15/lib/bitcode'/happy2024/./
cd '/opt/pgsql-15/lib/bitcode' && /usr/lib64/llvm5.0/bin/llvm-lto -thinlto -thinlto-action=thinlink -o happy2024.index.bc happy2024/happy2024.bc


复制

进入数据库创建 extension

postgres=# create extension happy2024;
CREATE EXTENSION


复制

最后我们测试C语言创建的函数：

postgres=# select happy2024();
       happy2024       
-----------------------
 墨天伦 祝你春节快乐！
(1 row)


复制

春节前体验了一次自定义extension的全过程， 很简单，很方便就可以扩展功能数据库上的功能！

最后祝广大墨天伦的网友 新春快乐 多多挣钱！

Have a fun 😃!