postgres 使用存储过程批量插入数据的操作

不念不忘少年蓝@ 2022-10-26 15:28 14阅读 0赞

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千万条数据耗时610s，当然字段不多的情况下。

补充：Postgresql存储过程--更新或者插入数据

要记录某一时段机器CPU、内存、硬盘的信息，展示的时间粒度为分钟，但是为了精确，输入数据源的时间粒度为6s。这个统计过程可以在应用层做好，每分钟插入一次，也可以在数据库层写个存储过程来完成，根据传入数据的时间来判断是更新数据库旧数据还是插入新数据。

同时，这些数据只需要保留一周，更老的数据需要被删除。删除动作可以每天定时执行一次，也可以写在存储过程中每次检查一下。

考虑到性能在此时没什么太大约束，而后面存储过程的接口方式更漂亮些，不用应用层去关心数据到底组织成什么样，因此实现了一个如下：

1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
34  
35  
36  
37  
38  
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;  
以上为个人经验，希望能给大家一个参考