— 步骤1：创建扩展目录
mkdir -p /tmp/fgedu_extension
cd /tmp/fgedu_extension

— 步骤2：创建控制文件
cat > fgedu_extension.control << 'EOF' # fgedu_extension.control comment = 'A simple PostgreSQL extension for fgedu' default_version = '1.0' module_pathname = '$libdir/fgedu_extension' relocatable = false EOF -- 步骤3：创建SQL文件 cat > fgedu_extension–1.0.sql << 'EOF' -- fgedu_extension--1.0.sql -- 创建函数 CREATE OR REPLACE FUNCTION fgedu_hello() RETURNS TEXT AS $$ BEGIN RETURN 'Hello from fgedu extension!'; END; $$ LANGUAGE plpgsql; -- 创建函数 CREATE OR REPLACE FUNCTION fgedu_add(integer, integer) RETURNS integer AS $$ BEGIN RETURN $1 + $2; END; $$ LANGUAGE plpgsql; -- 创建表 CREATE TABLE IF NOT EXISTS fgedu_extension_log ( id SERIAL PRIMARY KEY, message TEXT NOT NULL, created_at TIMESTAMP DEFAULT NOW() ); -- 创建函数 CREATE OR REPLACE FUNCTION fgedu_log_message(text) RETURNS void AS $$ BEGIN INSERT INTO fgedu_extension_log(message) VALUES($1); END; $$ LANGUAGE plpgsql; EOF -- 步骤4：创建Makefile cat > Makefile << 'EOF' MODULES = fgedu_extension DATA = fgedu_extension--1.0.sql PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS) EOF -- 步骤5：编译扩展 make -- 输出结果 gcc -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/usr/local/include/postgresql/server -c -o fgedu_extension.o fgedu_extension.c ld -shared -o fgedu_extension.so fgedu_extension.o -- 步骤6：安装扩展 make install -- 输出结果 /bin/mkdir -p '/usr/local/lib/postgresql' /bin/install -c -m 755 fgedu_extension.so '/usr/local/lib/postgresql/' /bin/mkdir -p '/usr/local/share/postgresql/extension' /bin/install -c -m 644 fgedu_extension.control '/usr/local/share/postgresql/extension/' /bin/install -c -m 644 fgedu_extension--1.0.sql '/usr/local/share/postgresql/extension/'

— 连接数据库
psql -U fgedu -d fgedudb

— 输出结果
psql (18.0)
Type “help” for help.

fgedudb=#

— 安装扩展
CREATE EXTENSION fgedu_extension;

— 输出结果
CREATE EXTENSION

— 查看已安装的扩展
SELECT * FROM pg_extension;

— 输出结果
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
————+———-+————–+—————-+————+———–+—————
plpgsql | 10 | 11 | f | 1.0 | |
fgedu_extension | 16384 | 2200 | f | 1.0 | |
(2 rows)

— 查看扩展的对象
SELECT nspname, proname FROM pg_proc
WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = ‘public’)
AND proname LIKE ‘fgedu%’;

— 输出结果
nspname | proname
———+————-
public | fgedu_add
public | fgedu_hello
public | fgedu_log_message
(3 rows)

— 测试扩展函数
SELECT fgedu_hello();

— 输出结果
fgedu_hello
———————-
Hello from fgedu extension!
(1 row)

— 测试加法函数
SELECT fgedu_add(10, 20);

— 输出结果
fgedu_add
———–
30
(1 row)

— 测试日志函数
SELECT fgedu_log_message(‘Test message from extension’);

— 输出结果
fgedu_log_message
——————-

(1 row)

— 查看日志
SELECT * FROM fgedu_extension_log;

— 输出结果
id | message | created_at
—-+————————–+—————————-
1 | Test message from extension | 2026-04-07 22:00:00
(1 row)

— 卸载扩展
DROP EXTENSION fgedu_extension;

— 输出结果
DROP EXTENSION

— 重新安装扩展
CREATE EXTENSION fgedu_extension;

— 输出结果
CREATE EXTENSION

— 步骤1：创建升级脚本
cat > fgedu_extension–1.0–1.1.sql << 'EOF' -- fgedu_extension--1.0--1.1.sql -- 添加新函数 CREATE OR REPLACE FUNCTION fgedu_multiply(integer, integer) RETURNS integer AS $$ BEGIN RETURN $1 * $2; END; $$ LANGUAGE plpgsql; -- 修改现有函数 CREATE OR REPLACE FUNCTION fgedu_hello() RETURNS TEXT AS $$ BEGIN RETURN 'Hello from fgedu extension v1.1!'; END; $$ LANGUAGE plpgsql; -- 添加新表 CREATE TABLE IF NOT EXISTS fgedu_extension_stats ( id SERIAL PRIMARY KEY, function_name TEXT NOT NULL, call_count INTEGER DEFAULT 0, last_called TIMESTAMP ); EOF -- 步骤2：更新控制文件 cat > fgedu_extension.control << 'EOF' # fgedu_extension.control comment = 'A simple PostgreSQL extension for fgedu' default_version = '1.1' module_pathname = '$libdir/fgedu_extension' relocatable = false EOF -- 步骤3：编译并安装 make clean make make install -- 步骤4：升级扩展 ALTER EXTENSION fgedu_extension UPDATE TO '1.1'; -- 输出结果 ALTER EXTENSION -- 测试升级后的扩展 SELECT fgedu_hello(); -- 输出结果 fgedu_hello ------------------------- Hello from fgedu extension v1.1! (1 row) -- 测试新函数 SELECT fgedu_multiply(10, 20); -- 输出结果 fgedu_multiply ---------------- 200 (1 row) -- 查看新表 \d fgedu_extension_stats -- 输出结果 Table "public.fgedu_extension_stats" Column | Type | Modifiers --------------+-----------------------------+--------------------------------------------------- id | integer | not null default nextval('fgedu_extension_stats_id_seq'::regclass) function_name| text | not null call_count | integer | default 0 last_called | timestamp without time zone | Indexes: "fgedu_extension_stats_pkey" PRIMARY KEY, btree (id) -- 查看扩展版本 SELECT extname, extversion FROM pg_extension WHERE extname = 'fgedu_extension'; -- 输出结果 extname | extversion -----------------+------------ fgedu_extension | 1.1 (1 row)

— 步骤1：创建扩展目录
mkdir -p /tmp/fgedu_basic_ext
cd /tmp/fgedu_basic_ext

— 步骤2：创建控制文件
cat > fgedu_basic_ext.control << 'EOF' # fgedu_basic_ext.control comment = 'A basic PostgreSQL extension for fgedu' default_version = '1.0' relocatable = true EOF -- 步骤3：创建SQL文件 cat > fgedu_basic_ext–1.0.sql << 'EOF' -- fgedu_basic_ext--1.0.sql -- 创建函数 CREATE OR REPLACE FUNCTION fgedu_basic_hello() RETURNS TEXT AS $$ BEGIN RETURN 'Hello from basic extension!'; END; $$ LANGUAGE plpgsql; -- 创建函数 CREATE OR REPLACE FUNCTION fgedu_basic_greet(text) RETURNS TEXT AS $$ BEGIN RETURN 'Hello, ' || $1 || '!'; END; $$ LANGUAGE plpgsql; -- 创建视图 CREATE OR REPLACE VIEW fgedu_basic_info AS SELECT version() AS postgresql_version, current_database() AS database_name, current_user AS current_user; EOF -- 步骤4：安装扩展 -- 复制文件到扩展目录 cp fgedu_basic_ext.control /usr/local/share/postgresql/extension/ cp fgedu_basic_ext--1.0.sql /usr/local/share/postgresql/extension/ -- 连接数据库 psql -U fgedu -d fgedudb -- 安装扩展 CREATE EXTENSION fgedu_basic_ext; -- 输出结果 CREATE EXTENSION -- 测试函数 SELECT fgedu_basic_hello(); -- 输出结果 fgedu_basic_hello ------------------------ Hello from basic extension! (1 row) -- 测试greet函数 SELECT fgedu_basic_greet('World'); -- 输出结果 fgedu_basic_greet -------------------- Hello, World! (1 row) -- 测试视图 SELECT * FROM fgedu_basic_info; -- 输出结果 postgresql_version | database_name | current_user ----------------------------------+---------------+-------------- PostgreSQL 18.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.0, 64-bit | fgedudb | fgedu (1 row)

— 步骤1：创建扩展目录
mkdir -p /tmp/fgedu_advanced_ext
cd /tmp/fgedu_advanced_ext

— 步骤2：创建控制文件
cat > fgedu_advanced_ext.control << 'EOF' # fgedu_advanced_ext.control comment = 'An advanced PostgreSQL extension for fgedu' default_version = '1.0' module_pathname = '$libdir/fgedu_advanced_ext' relocatable = false EOF -- 步骤3：创建C语言文件 cat > fgedu_advanced_ext.c << 'EOF' #include "postgres.h" #include "fmgr.h" PG_MODULE_MAGIC; PG_FUNCTION_INFO_V1(fgedu_fibonacci); Datum fgedu_fibonacci(PG_FUNCTION_ARGS) { int n = PG_GETARG_INT32(0); int a = 0, b = 1, c, i; if (n < 0) { ereport(ERROR, ( errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("Fibonacci number must be non-negative" ))); } if (n == 0) { PG_RETURN_INT32(a); } else if (n == 1) { PG_RETURN_INT32(b); } for (i = 2; i <= n; i++) { c = a + b; a = b; b = c; } PG_RETURN_INT32(b); } EOF -- 步骤4：创建SQL文件 cat > fgedu_advanced_ext–1.0.sql << 'EOF' -- fgedu_advanced_ext--1.0.sql -- 创建函数 CREATE OR REPLACE FUNCTION fgedu_fibonacci(integer) RETURNS integer AS '$libdir/fgedu_advanced_ext', 'fgedu_fibonacci' LANGUAGE C STRICT; -- 创建函数 CREATE OR REPLACE FUNCTION fgedu_fibonacci_sequence(integer) RETURNS TABLE(n integer, value integer) AS $$ DECLARE i integer; BEGIN FOR i IN 0..$1 LOOP n := i; value := fgedu_fibonacci(i); RETURN NEXT; END LOOP; END; $$ LANGUAGE plpgsql; EOF -- 步骤5：创建Makefile cat > Makefile << 'EOF' MODULES = fgedu_advanced_ext DATA = fgedu_advanced_ext--1.0.sql PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS) EOF -- 步骤6：编译并安装 make make install -- 步骤7：安装扩展 psql -U fgedu -d fgedudb -c "CREATE EXTENSION fgedu_advanced_ext;" -- 输出结果 CREATE EXTENSION -- 测试函数 SELECT fgedu_fibonacci(10); -- 输出结果 fgedu_fibonacci ----------------- 55 (1 row) -- 测试序列函数 SELECT * FROM fgedu_fibonacci_sequence(10); -- 输出结果 n | value ----+------- 0 | 0 1 | 1 2 | 1 3 | 2 4 | 3 5 | 5 6 | 8 7 | 13 8 | 21 9 | 34 10 | 55 (11 rows) -- 测试错误处理 SELECT fgedu_fibonacci(-1); -- 输出结果 ERROR: Fibonacci number must be non-negative

— 步骤1：创建扩展目录
mkdir -p /tmp/fgedu_complex_ext
cd /tmp/fgedu_complex_ext

— 步骤2：创建控制文件
cat > fgedu_complex_ext.control << 'EOF' # fgedu_complex_ext.control comment = 'A complex PostgreSQL extension for fgedu' default_version = '1.0' module_pathname = '$libdir/fgedu_complex_ext' relocatable = false EOF -- 步骤3：创建SQL文件 cat > fgedu_complex_ext–1.0.sql << 'EOF' -- fgedu_complex_ext--1.0.sql -- 创建类型 CREATE TYPE fgedu_user_type AS ( id integer, name text, email text ); -- 创建函数 CREATE OR REPLACE FUNCTION fgedu_create_user(integer, text, text) RETURNS fgedu_user_type AS $$ DECLARE result fgedu_user_type; BEGIN result.id := $1; result.name := $2; result.email := $3; RETURN result; END; $$ LANGUAGE plpgsql; -- 创建函数 CREATE OR REPLACE FUNCTION fgedu_get_user_info(fgedu_user_type) RETURNS text AS $$ BEGIN RETURN 'User: ' || $1.name || ' (ID: ' || $1.id || ', Email: ' || $1.email || ')'; END; $$ LANGUAGE plpgsql; -- 创建表 CREATE TABLE IF NOT EXISTS fgedu_user_log ( id SERIAL PRIMARY KEY, user_info fgedu_user_type, action TEXT NOT NULL, created_at TIMESTAMP DEFAULT NOW() ); -- 创建函数 CREATE OR REPLACE FUNCTION fgedu_log_user_action(fgedu_user_type, text) RETURNS void AS $$ BEGIN INSERT INTO fgedu_user_log(user_info, action) VALUES($1, $2); END; $$ LANGUAGE plpgsql; -- 创建视图 CREATE OR REPLACE VIEW fgedu_user_log_view AS SELECT id, (user_info).id AS user_id, (user_info).name AS user_name, (user_info).email AS user_email, action, created_at FROM fgedu_user_log; EOF -- 步骤4：创建Makefile cat > Makefile << 'EOF' MODULES = fgedu_complex_ext DATA = fgedu_complex_ext--1.0.sql PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS) EOF -- 步骤5：编译并安装 make make install -- 步骤6：安装扩展 psql -U fgedu -d fgedudb -c "CREATE EXTENSION fgedu_complex_ext;" -- 输出结果 CREATE EXTENSION -- 测试类型和函数 SELECT fgedu_create_user(1, '张三', 'zhangsan@fgedu.net.cn'); -- 输出结果 fgedu_create_user ------------------------------ (1,张三,zhangsan@fgedu.net.cn) (1 row) -- 测试获取用户信息 SELECT fgedu_get_user_info(fgedu_create_user(1, '张三', 'zhangsan@fgedu.net.cn')); -- 输出结果 fgedu_get_user_info ---------------------------------- User: 张三 (ID: 1, Email: zhangsan@fgedu.net.cn) (1 row) -- 测试日志函数 SELECT fgedu_log_user_action(fgedu_create_user(1, '张三', 'zhangsan@fgedu.net.cn'), 'login'); -- 输出结果 fgedu_log_user_action ----------------------- (1 row) -- 查看日志 SELECT * FROM fgedu_user_log_view; -- 输出结果 id | user_id | user_name | user_email | action | created_at ----+---------+-----------+------------------------+--------+---------------------------- 1 | 1 | 张三 | zhangsan@fgedu.net.cn | login | 2026-04-07 22:30:00 (1 row)