Requirement description
When encountering a requirement, you need to insert 50,000 pieces of data into the table and plan to use stored procedures. However, the postgres database does not have SQL to build stored procedures, so it is implemented using functions.
The integrity of the table data structure requires two records to be inserted at a time, and the two records are constrained by foreign keys. The partner_id field value of record1 is the value of the primary key id of record2, and the partner_id field value of record2 is the value of the primary key id of record1.
accomplish
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;
question
In this way, the insertion can only be inserted once, because the sequence value is obtained outside the for loop, and the value of id will not be assigned again with the loop, and the primary key will conflict.
way
Thinking of cycling the function, I wrote another function loop to execute the previous function, and removed the for loop statements FOR i IN 1..500000 LOOP and end LOOP in the previous function;
Write another function below to execute function 1
create or replace function loopCreate() returns void as $BODY$ begin for i in 1..50000 LOOP PERFORM creatData(); end LOOP; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE;
Execute functions
select * from loopCreate() as tab;
OK, it's done, 100,000 pieces of data are inserted in seconds, 2.1 seconds.
PS: CSDN's markdown editor is really difficult to use. If the text is a little longer, it will get stuck. Moreover, line breaks are often automatically adjusted and jumped back to the previous line. Helplessly, now I just write and paste it in another place...
Supplement: A small problem traversing in postgresql stored procedures
question
To implement this function, the SQL statement after r in is a variable, you need to modify the following code
"sqltext" = 'select "ID","ZONENAME" from "ZONE_INFO" where "ID"<>0'; for r in "sqltext" loop return next r; end loop;
Solution:
sqltext = 'select "ID","ZONENAME" from "ZONE_INFO" where "ID" <>0'; for r in execute sqltext loop return next r; end loop;
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.