opengauss创建函数

环境:

OS:Centos 7

DB:3.0

 

建表语句

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
);

 

函数

CREATE OR REPLACE FUNCTION "public"."sp_insert_data_pg"()
  RETURNS "pg_catalog"."void" AS $BODY$BEGIN
    -- Routine body goes here...
declare 
  i integer := 1;
begin    
while i <= 1000000 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;
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

 

调用

call sp_insert_data_pg();