GPT 完成PG extension 开发： 2025 嘛钱不钱的 乐呵乐呵得了

又到了每年除夕前上班得最后一天， 记得在喜迎2024年得时候写了一个 PG_EXTENSION https://www.modb.pro/db/1755155378081976320

今年我们再写一遍同样的文章， 我们用GPT 帮我们来完成。

描述一下需求：

Please help write a postgres extension via C language: 
Extension_name: happy_2025 
Extension_version: v2025 
Extension_including: just one function named happy2025 retrun value "Happy 2025! 嘛钱不钱的 乐呵乐呵得了！"
复制

我们拷贝一下GPT生成的代码：

happy_2025.c

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

PG_MODULE_MAGIC;


// Function declaration
PG_FUNCTION_INFO_V1(happy_2025);


Datum
happy_2025(PG_FUNCTION_ARGS)
{
    PG_RETURN_TEXT_P(cstring_to_text("Happy 2025! 嘛钱不钱的 乐呵乐呵得了！"));
}

复制

happy_2025.controlCreate a file named happy_2025.control for PostgreSQL to recognize the extension:

# happy_2025 extension
comment = 'Happy 2025 Extension'
default_version = 'v2025'
relocatable = true
module_pathname = '$libdir/happy_2025'

复制

happy_2025–v2025.sqlCreate an SQL file to define the function in PostgreSQL:

-- happy_2025 extension version v2025
CREATE FUNCTION happy_2025() RETURNS TEXT
AS 'MODULE_PATHNAME', 'happy_2025'
LANGUAGE C IMMUTABLE STRICT;

复制

我们还需要编写一个Makefile文件：

# /opt/pgapp/pg_happy2025/Makefile
MODULES = happy_2025
EXTENSION =happy_2025
DATA = happy_2025--v2025.sql
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

复制

我们将文件： 打包到一个文件夹下 pg_happy2025
happy_2025.c
happy_2025.control
happy_2025–v2025.sql
Makefile

INFRA [postgres@dc02psqldbuat04 pg_happy2025]# tree
.
├── happy_2025.c
├── happy_2025.control
├── happy_2025--v2025.sql
└── Makefile

复制

我们把源码拷贝到PG大版本源码的contrib下

cp -r /opt/pgapp/pg_happy2025 /opt/pgapp/postgresql-16.1/contrib

复制

安装：make & make install

INFRA [postgres@dc02psqldbuat04 pg_happy2025]# make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -O2 -fPIC -fvisibility=hidden -I. -I./ -I/opt/pgsql-16/include/server -I/opt/pgsql-16/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o happy_2025.o happy_2025.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -O2 -fPIC -fvisibility=hidden happy_2025.o -L/opt/pgsql-16/lib  -L/opt/rh/llvm-toolset-7/root/usr/lib64  -Wl,--as-needed -Wl,-rpath,'/opt/pgsql-16/lib',--enable-new-dtags  -fvisibility=hidden -shared -o happy_2025.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-16/include/server -I/opt/pgsql-16/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -flto=thin -emit-llvm -c -o happy_2025.bc happy_2025.c
INFRA [postgres@dc02psqldbuat04 pg_happy2025]# make install
/usr/bin/mkdir -p '/opt/pgsql-16/share/extension'
/usr/bin/mkdir -p '/opt/pgsql-16/share/extension'
/usr/bin/mkdir -p '/opt/pgsql-16/lib'
/usr/bin/install -c -m 644 .//happy_2025.control '/opt/pgsql-16/share/extension/'
/usr/bin/install -c -m 644 .//happy_2025--v2025.sql  '/opt/pgsql-16/share/extension/'
/usr/bin/install -c -m 755  happy_2025.so '/opt/pgsql-16/lib/'
/usr/bin/mkdir -p '/opt/pgsql-16/lib/bitcode/happy_2025'
/usr/bin/mkdir -p '/opt/pgsql-16/lib/bitcode'/happy_2025/
/usr/bin/install -c -m 644 happy_2025.bc '/opt/pgsql-16/lib/bitcode'/happy_2025/./
cd '/opt/pgsql-16/lib/bitcode' && /opt/rh/llvm-toolset-7/root/usr/bin/llvm-lto -thinlto -thinlto-action=thinlink -o happy_2025.index.bc happy_2025/happy_2025.bc

复制

创建插件：

postgres=# create extension happy_2025;
CREATE EXTENSION
postgres=# \dx
                               List of installed extensions
     Name     | Version |   Schema   |                    Description                     
--------------+---------+------------+----------------------------------------------------
happy_2025   | v2025   | public     | Happy 2025 Extension


复制

测试函数：

postgres=# select happy_2025();
              happy_2025               
---------------------------------------
Happy 2025! 嘛钱不钱的 乐呵乐呵得了！
(1 row)

复制

Have a fun 🙂 !