习惯了 Oracle 的朋友，非常熟悉使 plsql developer 工具的存储过程调试功能，目前 PostgreSQL 本身不支持函数调试，但可以通过安装外部模块实现这一重要功能，本文以 PostgreSQL 9.4beta3 为例子，演示 pldebugger 模块的安装和使用。
安装 Pldebugger
下载 pldebugger
1
2
|
cd /opt/pgsql_9.4beta3/share/contrib/
git clone git:
|
安装 pldebugger
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
[root
root
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I/opt/pgsql_9.4beta3/lib/pgxs/src/makefiles/../../src/pl/plpgsql/src -I. -I./ -I/opt/pgsql_9.4beta3/include/server -I/opt/pgsql_9.4beta3/include/internal -D_GNU_SOURCE -c -o plpgsql_debugger.o plpgsql_debugger.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I./ -I/opt/pgsql_9.4beta3/include/server -I/opt/pgsql_9.4beta3/include/internal -D_GNU_SOURCE -c -o plugin_debugger.o plugin_debugger.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I./ -I/opt/pgsql_9.4beta3/include/server -I/opt/pgsql_9.4beta3/include/internal -D_GNU_SOURCE -c -o dbgcomm.o dbgcomm.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I./ -I/opt/pgsql_9.4beta3/include/server -I/opt/pgsql_9.4beta3/include/internal -D_GNU_SOURCE -c -o pldbgapi.o pldbgapi.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -shared -o plugin_debugger.so plpgsql_debugger.o plugin_debugger.o dbgcomm.o pldbgapi.o -L/opt/pgsql_9.4beta3/lib -Wl,--as-needed -Wl,-rpath,'/opt/pgsql_9.4beta3/lib',--enable-new-dtags
root
/bin/mkdir -p '/opt/pgsql_9.4beta3/lib'
/bin/mkdir -p '/opt/pgsql_9.4beta3/share/extension'
/bin/mkdir -p '/opt/pgsql_9.4beta3/share/extension'
/bin/mkdir -p '/opt/pgsql_9.4beta3/share/doc/extension'
/usr/bin/install -c -m 755 plugin_debugger.so '/opt/pgsql_9.4beta3/lib/plugin_debugger.so'
/usr/bin/install -c -m 644 pldbgapi.control '/opt/pgsql_9.4beta3/share/extension/'
/usr/bin/install -c -m 644 pldbgapi--1.0.sql pldbgapi--unpackaged--1.0.sql '/opt/pgsql_9.4beta3/share/extension/'
/usr/bin/install -c -m 644 README.pldebugger '/opt/pgsql_9.4beta3/share/doc/extension/'
|
修改 postgresql.conf
1
|
shared_preload_libraries = '$libdir/plugin_debugger'
|
备注：此参数修改后需要重启数据库生效。
重启数据库
1
|
pg94@db1-> pg_ctl restart -m fast
|
创建 Pldbgapi 扩展
1
2
3
4
5
6
|
pg94@db1-> psql francs
psql (9.4beta3)
Type "help" for help.
francs=# create extension pldbgapi ;
CREATE EXTENSION
|
函数调试测试
1
2
3
4
5
6
7
8
9
|
CREATE OR REPLACE FUNCTION func_add(i_a int4, i_b int4 ) RETURNS int4 AS $$
DECLARE
v_add int4 ;
begin
RAISE NOTICE '% + %', i_a, i_b;
v_add:=i_a+i_b;
return v_add;
end;
$$ LANGUAGE 'plpgsql';
|
备注：接下来打开本地 pgAdmin 工具，连接到相应库，找到对应函数，单击右键，出现如图：
之后出现调试界面，如下图：
备注： 太棒了！
原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/tech/database/239623.html
