需求描述

碰到需求,需要往表里插入5萬條數(shù)據(jù), 打算使用存儲過程,但是postgres 數(shù)據(jù)庫沒有建存儲過程的SQL, 所以使用函數(shù)來實現(xiàn).

表數(shù)據(jù)結(jié)構(gòu)完整性要求一次插入兩條記錄, 兩條記錄相互外鍵約束, record1 的 partner_id 字段值是 record2 的主鍵id的值, record2 的 partner_id 字段值是 record1 的主鍵id的值.

實現(xiàn)

create
or replace function creatData() returns boolean as $BODY$
declare ii integer;
declare id1 integer;
declare id2 integer;
begin
ii = 1;
id1 = nextval(‘seq_table’);
id2 = nextval(‘seq_table’);
FOR ii IN 1..50000 LOOP
insert
into
table1
values(
id1,
10,
10250,
5001,
‘2017-08-07 14:00:00’,
‘2017-08-07 15:00:00’,
id2,
true,
864,
16950,
0,
0,
0,
null,
20,
null,
18050,
‘2017-08-07 13:55:08’,
18051,
‘2017-08-07 13:57:28’,
false,
401,
10,
null,
null,
null,
‘DA-HZ001000003’,
‘2017-08-07 13:54:08’,
‘2017-08-07 13:57:28’,
10251
);
insert
into
table1
values(
id2,
10,
10251,
5001,
‘2017-08-07 14:00:00’,
‘2017-08-07 15:00:00’,
id1,
true,
864,
16950,
0,
0,
0,
null,
20,
null,
18050,
‘2017-08-07 13:55:08’,
18051,
‘2017-08-07 13:57:28’,
false,
401,
10,
null,
null,
null,
‘DA-HZ001000003’,
‘2017-08-07 13:54:08’,
‘2017-08-07 13:57:28’,
10250
);
end LOOP;
return true;
end;
$BODY$ LANGUAGE plpgsql;