在pgadmin/navicat中debug lightdb plpgsql存储过程
1、lightdb默认集成了pldebugger。pg用户也可从https://github.com/EnterpriseDB/pldebugger下载最新版本release，如1.5版本，放到contrib目录，解压，编译。
[zjh@hs-10-20-30-193 pldebugger]$ make make -C ../../src/backend generated-headers make[1]: Entering directory `/home/zjh/Sources/postgresql-13.3/src/backend' make -C catalog distprep generated-header-symlinks make[2]: Entering directory `/home/zjh/Sources/postgresql-13.3/src/backend/catalog' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/home/zjh/Sources/postgresql-13.3/src/backend/catalog' make -C utils distprep generated-header-symlinks make[2]: Entering directory `/home/zjh/Sources/postgresql-13.3/src/backend/utils' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/home/zjh/Sources/postgresql-13.3/src/backend/utils' make[1]: Leaving directory `/home/zjh/Sources/postgresql-13.3/src/backend' [zjh@hs-10-20-30-193 pldebugger]$ make install make -C ../../src/backend generated-headers make[1]: Entering directory `/home/zjh/Sources/postgresql-13.3/src/backend' make -C catalog distprep generated-header-symlinks make[2]: Entering directory `/home/zjh/Sources/postgresql-13.3/src/backend/catalog' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/home/zjh/Sources/postgresql-13.3/src/backend/catalog' make -C utils distprep generated-header-symlinks make[2]: Entering directory `/home/zjh/Sources/postgresql-13.3/src/backend/utils' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/home/zjh/Sources/postgresql-13.3/src/backend/utils' make[1]: Leaving directory `/home/zjh/Sources/postgresql-13.3/src/backend' /usr/bin/mkdir -p '/home/zjh/stage/lightdb-x/lib' /usr/bin/mkdir -p '/home/zjh/stage/lightdb-x/share/extension' /usr/bin/mkdir -p '/home/zjh/stage/lightdb-x/share/extension' /usr/bin/mkdir -p '/home/zjh/stage/lightdb-x/share/doc//extension' /usr/bin/install -c -m 755 plugin_debugger.so '/home/zjh/stage/lightdb-x/lib/plugin_debugger.so' /usr/bin/install -c -m 644 ./pldbgapi.control '/home/zjh/stage/lightdb-x/share/extension/' /usr/bin/install -c -m 644 ./pldbgapi--1.1.sql ./pldbgapi--unpackaged--1.1.sql ./pldbgapi--1.0--1.1.sql '/home/zjh/stage/lightdb-x/share/extension/' /usr/bin/install -c -m 644 ./README.pldebugger '/home/zjh/stage/lightdb-x/share/doc//extension/'
2、在lightdb.conf的shared_preload_libraries中增加plugin_debugger，如下：
shared_preload_libraries=lt_stat_statements,lt_stat_activity,lt_prewarm,lt_cron,lt_hint_plan,lt_show_plans,lt_sql_inspect,plugin_debugger #必须得是最后一个插件
3、重启lightdb实例，lt_ctl -D $LTDATA restart
4、在对应的数据库创建pldbgapi。如下：
create extension pldbgapi;
成功后，在ide中可以看到创建了一堆函数，如下：
5、创建测试函数如下：
CREATE OR REPLACE FUNCTION public.somefunc() RETURNS integer LANGUAGE plpgsql AS $function$ << outerblock >> DECLARE quantity integer := 30; BEGIN RAISE NOTICE 'Quantity here is %', quantity; -- Prints 30 quantity := 50; -- -- Create a subblock -- DECLARE quantity integer := 80; BEGIN RAISE NOTICE 'Quantity here is %', quantity; -- Prints 80 RAISE NOTICE 'Outer quantity here is %', outerblock.quantity; -- Prints 50 END; RAISE NOTICE 'Quantity here is %', quantity; -- Prints 50 RETURN quantity; END; $function$ ;
打印错误信息
Oracle dbms_output.put_line用于输出打印信息
postgresql使用RAISE level format;打印信息
不同的 level 代表了错误的不同严重级别，包括：
DEBUG LOG NOTICE INFO WARNING EXCEPTION
示例：
DO $$
BEGIN
RAISE DEBUG 'This is a debug text.';
RAISE INFO 'This is an information.';
RAISE LOG 'This is a log.';
RAISE WARNING 'This is a warning at %', now();
RAISE NOTICE 'This is a notice %%';
END $$;
INFO: This is an information.
WARNING: This is a warning at 2020-05-16 11:27:06.138569+08
NOTICE: This is a notice %
从结果可以看出，并非所有的消息都会打印到客户端和服务器日志中。这个可以通过配置参数 client_min_messages 和 log_min_messages 进行设置。
对于 EXCEPTION 级别的错误，可以支持额外的选项：
RAISE [ EXCEPTION ] format USING option = expression [, ... ];
RAISE [ EXCEPTION ] condition_name USING option = expression [, ... ];
RAISE [ EXCEPTION ] SQLSTATE 'sqlstate' USING option = expression [, ... ];
RAISE [ EXCEPTION ] USING option = expression [, ... ];
其中，option 可以是以下选项：
MESSAGE，设置错误消息。如果 RAISE 语句中已经包含了 format 字符串，不能再使用该选项。
DETAIL，指定错误详细信息。
HINT，设置一个提示信息。
ERRCODE，指定一个错误码（SQLSTATE）。可以是文档中的条件名称或者五个字符组成的 SQLSTATE 代码。
COLUMN、CONSTRAINT、DATATYPE、TABLE、SCHEMA，返回相关对象的名称。
以下是一些示例：
RAISE EXCEPTION 'Nonexistent ID --> %', user_id
USING HINT = 'Please check your user ID';
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';
RAISE division_by_zero;
RAISE SQLSTATE '22012';
改写例子：
Oracle原写法
DBMS_OUTPUT.PUT_LINE('PROCEDURE [P_ASD] BEGIN … ’ || TO_CHAR(SYSDATE, ‘YYYY-MM-DD HH24:MI:SS’));
postgresql改写：
raise notice ‘PROCEDURE [P_ASD] BEGIN …%’ , TO_CHAR(SYSDATE, ‘YYYY-MM-DD HH24:MI:SS’);
-- 创建测试存储过程 drop table if exists accounts; create table accounts ( id int generated by default as identity, name varchar(100) not null, balance dec(15,2) not null, primary key(id) ); insert into accounts(name,balance) values('Bob',10000); insert into accounts(name,balance) values('Alice',10000); select * from accounts; create or replace procedure transfer( sender int, receiver int, amount dec ) language plpgsql as $$ begin -- subtracting the amount from the sender's account update accounts set balance = balance - amount where id = sender; -- adding the amount to the receiver's account update accounts set balance = balance + amount where id = receiver; commit; end;$$
;
navicat调试plpgsql
首先，安装16.x版navicat for postgresql。
创建上述存储过程或函数。
