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 <= 1000000 loop
		insert into tb_test(name1,name2,name3) values('nameA'||i,'nameB'||i,'nameB'||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,
name1 varchar(20),
name2 varchar(20),
name3 varchar(20),
createtime timestamp default current_timestamp,
modifytime  timestamp default current_timestamp
);

调用:

call sp_insert_data_pg01();