CREATE or REPLACE FUNCTION insert_p_date() RETURNS text as ```
$$ DECLARE sql_string text; sdate timestamp without time zone; ldate timestamp without time zone; _wtid integer; --一段时间内每个月第一天 cur1 cursor for select date(zz) from generate_series(date_trunc('month',to_date('20171001','yyyymmdd')),date_trunc('month',to_date('20180401','yyyymmdd')),'1 month') as tt(zz); curs2 refcursor; BEGIN --打开 open cur1; loop fetch cur1 into sdate; -- 假如没有数据，结束循环 Exit when NOT found; --一段时间内的最后一个月的最后一天 select (date_trunc('month',sdate) + INTERVAL '1 MONTH - 1 day') into ldate; --上个月最后一天 --select date_trunc('day', date_trunc('month',sdate)) - interval '1 day' into ldate; --断点抛出一个月第一天 raise notice 'sdate==================================================================%', sdate; --定义一个月内的内的所有单台设备
open curs2 for select distinct wtid from statisticdata_bak where rectime between sdate and ldate;loop --取记录 fetch curs2 into _wtid; -- 假如没有检索到数据，结束循环 Exit when NOT found; --抛出设备信息 raise notice '_wtid=%', _wtid; --执行数据导入按照设备每个月 sql_string := 'insert into statisticdata select * from statisticdata_bak where wtid='||_wtid||' and rectime between '''||sdate||''' and '''||ldate||''';'; execute sql_string; raise notice '执行完成=%', _wtid; end loop; close curs2; end loop; --关闭游标1 close cur1;--返回结果 RETURN '导入成功！'; END; $$ LANGUAGE plpgsql;
