在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;$$
;