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

參考官方文檔

create or 批量replace function creatData2() returns
boolean AS
$BODY$
declare ii integer;
begin
II:=1;
FOR ii IN 1..10000000 loop="loop"
INSERT INヾ(′▽?zhuān)??TO ipm_model_history_data (res_model, res_id) VAL??UES (116, ii);
end loop="";
return(°o°) true;
end;
$BODY$
LANGUAGE plpgsql;
select * from creatData2() as tab;

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

補充：Postgresql存儲過(guò)程--更新或者插入數據

要記錄某一時(shí)段機器CPU、存儲插入內存、過(guò)程硬盤(pán)的批量信息，展示的使用數據時(shí)間粒度為分鐘，但是存儲插入為了精確，輸入數據源的過(guò)程時(shí)間粒度為6s。這個(gè)統計過(guò)程可以在應用層做好，批量每分鐘插入一次，使用數據也可以(yi)在??數據庫層寫(xiě)個(gè)存儲過(guò)程來(lái)完成，存儲插入根據傳ヽ(′▽?zhuān)?ノ入數ヽ(′▽?zhuān)?ノ據的過(guò)程時(shí)間來(lái)判斷是更新數據庫舊數據還是插入新數據。

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

Postgresql V8.3
CREATE OR REPLACE FUNヾ(′ω｀)?CTION insert_host_status??(_log_da(?⊿?)te timestamp without time zone, _host inet, _cpu integer, _mem intege(′?ω?`)r, _disk integer)
RETURNS void AS
$BODY$
DECLARE
new_start timestamp?? without time zone;
current_start time??stamp 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
SELEC(╬?益?)T id, log_date, cpu, mem, disk, count INTO c_id, c_log_date, c_c(????)pu, c_mem, c_disk, c_count FROM host(◎_◎;)_status_byminute WHERE host=_host ORDER BY id DESC limit 1;
SELECT timesta(???)mp_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)(′▽?zhuān)?) + _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_(T_T)byminute SET mem=c_mem, cpu=c_cpu, disk=c_di??sk, count=c_count WHERE id=c_id;
END IF;
-- delete old data
SELECT date_mii(da??te(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 > curr??ent_start THEN
DELETE FROM host_s(╯°□°）╯︵ ┻━┻tatus_byminute where log_date < new_start;
END IF;
END;
$B??ODY$
LANGUAGE 'plpgsql' VOLA??TILE
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