create or replace function creatData2() returns
boolean AS
$BODY$
declare ii integer;
begin
II:=1;
FOR ii IN 1..10000000 LOOP
INSERT INTO ipm_model_history_data (res_model, res_id) VALUES (116, ii);
end loop;
return true;
end;
$BODY$
LANGUAGE plpgsql;
select * from creatData2() as tab;
要記錄某一時段機器CPU、內存、硬盤的信息，展示的時間粒度為分鐘，但是為了精確，輸入數據源的時間粒度為6s。這個統計過程可以在應用層做好，每分鐘插入一次，也可以在數據庫層寫個存儲過程來完成，根據傳入數據的時間來判斷是更新數據庫舊數據還是插入新數據。
Postgresql V8.3
CREATE OR REPLACE FUNCTION insert_host_status(_log_date timestamp without time zone, _host inet, _cpu integer, _mem integer, _disk integer)
RETURNS void AS
$BODY$
DECLARE
new_start timestamp without time zone;
current_start timestamp without time zone;
c_id integer;
c_log_date timestamp without time zone;
c_cpu integer;
c_mem integer;
c_disk integer;
c_count integer;
date_span interval;
BEGIN
-- insert or update
SELECT id, log_date, cpu, mem, disk, count INTO c_id, c_log_date, c_cpu, c_mem, c_disk, c_count FROM host_status_byminute WHERE host=_host ORDER BY id DESC limit 1;
SELECT timestamp_mi(_log_date, c_log_date) INTO date_span;
IF date_span >= '00:00:60' OR c_id IS NULL THEN
INSERT INTO host_status_byminute (log_date, host, cpu, mem, disk, count) values (_log_date, _host, _cpu, _mem, _disk, 1);
ELSIF date_span >= '-00:00:60' THEN
c_mem := ((c_mem * c_count) + _mem)/(c_count + 1);
c_cpu := ((c_cpu * c_count) + _cpu)/(c_count + 1);
c_disk := ((c_disk * c_count) + _disk)/(c_count + 1);
c_count := c_count + 1;
UPDATE host_status_byminute SET mem=c_mem, cpu=c_cpu, disk=c_disk, count=c_count WHERE id=c_id;
END IF;
-- delete old data
SELECT date_mii(date(now()), 6) INTO new_start;
SELECT date(log_date) from host_status_byminute limit 1 INTO current_start; -- omit a bug happened when date is disordered.
IF new_start > current_start THEN
DELETE FROM host_status_byminute where log_date new_start;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION insert_host_status(timestamp without time zone, inet, integer, integer, integer) OWNER TO dbuser_test;
