域名預(yù)訂/競(jìng)價(jià)，好“米”不錯(cuò)過(guò)

這篇文章主要介紹了postgres 使用存儲(chǔ)過(guò)程批量插入數(shù)據(jù)的操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧

參考官方文檔

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;

插入1千萬(wàn)條數(shù)據(jù)耗時(shí)610s，當(dāng)然字段不多的情況下。

補(bǔ)充：Postgresql存儲(chǔ)過(guò)程--更新或者插入數(shù)據(jù)

要記錄某一時(shí)段機(jī)器CPU、內(nèi)存、硬盤(pán)的信息，展示的時(shí)間粒度為分鐘，但是為了精確，輸入數(shù)據(jù)源的時(shí)間粒度為6s。這個(gè)統(tǒng)計(jì)過(guò)程可以在應(yīng)用層做好，每分鐘插入一次，也可以在數(shù)據(jù)庫(kù)層寫(xiě)個(gè)存儲(chǔ)過(guò)程來(lái)完成，根據(jù)傳入數(shù)據(jù)的時(shí)間來(lái)判斷是更新數(shù)據(jù)庫(kù)舊數(shù)據(jù)還是插入新數(shù)據(jù)。

同時(shí)，這些數(shù)據(jù)只需要保留一周，更老的數(shù)據(jù)需要被刪除。刪除動(dòng)作可以每天定時(shí)執(zhí)行一次，也可以寫(xiě)在存儲(chǔ)過(guò)程中每次檢查一下。

考慮到性能在此時(shí)沒(méi)什么太大約束，而后面存儲(chǔ)過(guò)程的接口方式更漂亮些，不用應(yīng)用層去關(guān)心數(shù)據(jù)到底組織成什么樣，因此實(shí)現(xiàn)了一個(gè)如下：

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;

文章來(lái)源：腳本之家

來(lái)源地址：https://www.jb51.net/article/205221.htm

申請(qǐng)創(chuàng)業(yè)報(bào)道，分享創(chuàng)業(yè)好點(diǎn)子。點(diǎn)擊此處，共同探討創(chuàng)業(yè)新機(jī)遇！