pg创建存储过程批量提交

环境:

PG:11.10

 

注意pg11版本有存储过程和函数之分，而10只能创建函数,10里面的函数里不能包含有事物

 

CREATE OR REPLACE PROCEDURE "public"."sp_insert_data_pg01"()
 AS $BODY$
declare 
  i integer := 1;
BEGIN
 
while i <= 10000000 loop
insert into tb_test(name01,name02,name03,name04,name05,name06,name07,name08,name09,name10,name11,name12,name13,name14,name15) values('name01'||i,'name02'||i,'name03'||i,'name04'||i,'name05'||i,'name06'||i,'name07'||i,'name08'||i,'name09'||i,'name10'||i,'name11'||i,'name12'||i,'name13'||i,'name14'||i,'name15'||i);
    if mod(i,10000)=0 THEN
    commit;
    end if;
    i = i+1;
end loop;
RETURN;
END$BODY$
  LANGUAGE plpgsql

 

 

 

create table tb_test
(
id bigserial primary key not null,
name01 varchar(20),
name02 varchar(20),
name03 varchar(20),
name04 varchar(20),
name05 varchar(20),
name06 varchar(20),
name07 varchar(20),
name08 varchar(20),
name09 varchar(20),
name10 varchar(20),
name11 varchar(20),
name12 varchar(20),
name13 varchar(20),
name14 varchar(20),
name15 varchar(20),
createtime timestamp default current_timestamp,
modifytime timestamp default current_timestamp
);

 

调用:

call sp_insert_data_pg01();