GaussDB(DWS)存储过程中实现作业执行过程日志记录方法

GaussDB(DWS)存储过程中实现作业执行过程日志记录方法
具体示例如下：

--存储过程日志记录
--创建日志表
create table fun_all_execute_log(
id varchar2(32) default lower(sys_guid()),
pro_name varchar2(60),
patch_num int,
log_date date,
deal_date date,
log_mesage text);
--创建测试表和数据准备：
create table test(no int,id int);
insert into test select dbms_random.value(1,1000)::int,generate_series(1,100);
--创建业务存储过程：
CREATE OR REPLACE FUNCTION fun_affect_rows(out exe_info text)
  LANGUAGE plpgsql
  as $$
  declare v_count int;
  pro_result text;
  fun_name   text;
  patch_no   int;
begin
  fun_name := 'fun_affect_rows';
  select nvl(max(patch_num), '0') + 1 into patch_no from fun_all_execute_log where pro_name = fun_name;
  insert into test  values (dbms_random.value(1, 1000)::int,generate_series(1, dbms_random.value(10000, 20000)::int));
  GET DIAGNOSTICS v_count = ROW_COUNT;
  exe_info = sysdate || '# step1：本次插入数据量:' || v_count || '行。;';
  delete from test where no = dbms_random.value(1, 1000)::int;
  GET DIAGNOSTICS v_count = ROW_COUNT;
  exe_info = exe_info || sysdate || '# step2：本次删除数据量:' || v_count || '行。;';
  update test set id = dbms_random.value(1, 100)::int where no = dbms_random.value(1, 1000)::int and c1 = 0;
  exe_info = exe_info || sysdate || '# step3：本次更新数据量:' || sql%rowcount || '行。';
  RAISE INFO '本次信息为: %', exe_info;
  insert into fun_all_execute_log(pro_name, patch_num, log_date, deal_date, log_mesage)
  values (fun_name,patch_no,sysdate,split_part(regexp_split_to_table(exe_info, ';'), '#', 1),split_part(regexp_split_to_table(exe_info, ';'), '#', 2));
EXCEPTION
  WHEN OTHERS THEN
    pro_result := exe_info || sysdate || '# z exception error message is: ' || sqlerrm;
    insert into fun_all_execute_log(pro_name, patch_num, log_date, deal_date, log_mesage)
    values(fun_name,patch_no,sysdate,split_part(regexp_split_to_table(pro_result, ';'), '#', 1),split_part(regexp_split_to_table(pro_result, ';'), '#', 2));
END; $$;
--调用存储过程
select fun_affect_rows();
--查询日志
select * from fun_all_execute_log order by log_date desc,deal_date,log_mesage;