PGSQL日志存储过程

1、创建日志表

create table dwd_log_table
(error_id varchar(500),
data_dt varchar(500),
work_name varchar(500),
work_step varchar(500),
start_dt_time varchar(500),
end_dt_time varchar(500),
v_Sqlstate varchar(500),
v_Sqlcode  varchar(500),
v_errortext varchar(500),
tab_conuts varchar(500),
v_des varchar(500)
);
comment on table u_bk.dwd_log_table is '人口库日志表';
comment on column u_bk.dwd_log_table.error_id  is '出错编号';
comment on column u_bk.dwd_log_table.data_dt  is '出错数据日期';
comment on column u_bk.dwd_log_table.work_name  is '出错任务';
comment on column u_bk.dwd_log_table.work_step  is '任务步骤';
comment on column u_bk.dwd_log_table.start_dt_time  is '开始日期';
comment on column u_bk.dwd_log_table.end_dt_time  is '结束日期';
comment on column u_bk.dwd_log_table.v_Sqlstate  is 'sql执行';
comment on column u_bk.dwd_log_table.v_Sqlcode  is '错误信息';
comment on column u_bk.dwd_log_table.v_errortext  is '错误语句';
comment on column u_bk.dwd_log_table.tab_conuts  is '表记录数';
comment on column u_bk.dwd_log_table.v_des  is '备注';

2、创建日志存储过程

create function p_dwd_logs(v_function_name character varying, v_word_id character varying, v_work_step character varying, v_work_table character varying, v_tab_conuts character varying, v_begin_time timestamp without time zone, v_sql_code character varying, v_sql_status character varying, v_sql_log character varying, v_remark character varying) returns void
    language plpgsql
as
$$
declare

v_end_time timestamp ;
v_fun_time varchar;

Begin

v_end_time := now();
--v_fun_time:=extract(epoch FROM (v_end_time - v_begin_time))||'s';
v_fun_time:=date_part('second',(v_end_time-v_begin_time))||'s';

insert into dwd_log_tables
(
function_name,--存储过程名称
word_id,--步骤序号
work_step,--步骤描述
work_table,--目标表+操作类型
tab_conuts,--影响记录数
begin_time,--开始时间
end_time,--结束时间
fun_time,--执行用时（秒）
sql_code,--SQL执行腳本
sql_status,--执行状态
sql_log,--执行日志
remark--备注
)
Select
v_function_name,
v_word_id,
v_work_step,
v_work_table,
v_tab_conuts,
v_begin_time,
v_end_time,
v_fun_time,
v_sql_code,
v_sql_status,
v_sql_log,
v_remark;

Exception

When others Then

RAISE NOTICE '日志文件异常,没有插入数据!';

end;
$$;