PLPGSQL
drop FUNCTION if Exists repair_report_zero_data(t_name VARCHAR);
CREATE OR REPLACE FUNCTION repair_report_zero_data(t_name VARCHAR)
RETURNS text AS $$
DECLARE
cur_name CURSOR FOR select DISTINCT report_api_name.name as api_name from report_api_name where status =1 ORDER BY name ;
name_recordes RECORD;
msg text DEFAULT '成功';
begin
OPEN cur_name;
LOOP
--获取记录
fetch cur_name into name_recordes;
EXIT WHEN NOT FOUND;
EXECUTE
'insert into '||t_name||' (id,report_date,upstream, downstream, code, name, total, success, fail, create_time, wait, business_fail, system_fail, re_push, up_spend_time, report_time, auto_push, hand_push, down_spend_time, input_count, output_count)
select (substr(REPLACE(EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)||'''',''.'',''''),2,10)||substr(RANDOM()||'''',5,8))::BIGINT,report_date, n.upstream, n.downstream,n.code,n.name as name,0 as total,0 as success,0 as fail,CURRENT_DATE as create_time,0 as wait,0 as business_fail,0 as system_fail,0 as re_push,0 as up_spend_time,(bb.report_date)::TIMESTAMP as report_time,0 as auto_push,0 as hand_push,0 as down_spend_time,0 as input_count,0 as output_count from (select DISTINCT report_date from '||t_name||' ORDER BY report_date asc)bb left join report_api_name n on n.name = '''||name_recordes.api_name||''' where bb.report_date not in ( select report_date from ( select DISTINCT report_date,name from '||t_name||' ORDER BY name ,report_date)aa where name = '''||name_recordes.api_name||''') GROUP BY report_date, n.upstream, n.downstream,n.code,n.name;update '||t_name||' set report_time = (report_date)::TIMESTAMP ;' ;
end loop;
-- 关闭游标
CLOSE cur_name;
return msg;
end; $$
LANGUAGE plpgsql;
SELECT repair_report_zero_data('report_api_hour');
注意：本文归作者所有，未经作者允许，不得转载
