os: centos 7.4
db: postgresql 11.5

postgresql 11 之前的版本，function 和 procedure 是一回事，但是从 11 开始就不一样了。

版本

# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
# 
# su - postgres
Last login: Sat Oct 26 22:55:25 CST 2019 on pts/0
$
$ psql -c "select version();"
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)

procedure commmit

# su - postgres
$ psql
psql (11.5)
Type "help" for help.

postgres=# 
postgres=# postgres=# \h create procedure
Command:     CREATE PROCEDURE
Description: define a new procedure
Syntax:
CREATE [ OR REPLACE ] PROCEDURE
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...

创建个批量插入函数

postgres=# create table tmp_t0 (
 id   int8,
 name varchar(100)
 );

postgres=# 
postgres=# create or replace procedure sp_insert(
  in_lv_cnt       int8,
  in_lv_batchsize int8
)
language plpgsql
as $$
   declare
      lv_i int8;
   begin
      lv_i := 0;
      while lv_i < in_lv_cnt
      loop
          insert into tmp_t0 
		  select id,
		         md5(id::varchar) as name
            from generate_series(1, in_lv_batchsize) as id
		  ;
         lv_i := lv_i + 1;
         raise notice 'inserted % batch', lv_i;
         commit;
      end loop;
      return;
   end;
$$;

调用 procedure，发现使用 select 方式已经不行了，需要使用 call 。

postgres=# select sp_insert(10,100);
ERROR:  sp_insert(integer, integer) is a procedure
LINE 1: select sp_insert(10,100);
               ^
HINT:  To call a procedure, use CALL.

postgres=# 
postgres=# 
postgres=# call sp_insert(10,100);
NOTICE:  inserted 1 batch
NOTICE:  inserted 2 batch
NOTICE:  inserted 3 batch
NOTICE:  inserted 4 batch
NOTICE:  inserted 5 batch
NOTICE:  inserted 6 batch
NOTICE:  inserted 7 batch
NOTICE:  inserted 8 batch
NOTICE:  inserted 9 batch
NOTICE:  inserted 10 batch
CALL

查看 插入的数据量

postgres=# select count(1) from tmp_t0;
 count 
-------
  1000
(1 row)

procedure rollback

包含个 rollback 试试

postgres=# truncate table tmp_t0;
postgres=# 
postgres=# create or replace procedure sp_insert_rollback(
  in_lv_cnt       int8,
  in_lv_batchsize int8
)
language plpgsql
as $$
   declare
      lv_i int8;
   begin
      lv_i := 0;
      while lv_i < in_lv_cnt
      loop
          insert into tmp_t0 
		  select id,
		         md5(id::varchar) as name
            from generate_series(1, in_lv_batchsize) as id
		  ;
         
		 if (lv_i=5) then
		     raise notice 'rollback inserted % batch', lv_i;
			 rollback;
		 else
		     raise notice 'inserted % batch', lv_i;
			 commit;
		 end if;
         
		 lv_i := lv_i + 1;
         
      end loop;
      return;
   end;
$$;

调用 procedure，发现使用 select 方式已经不行了，需要使用 call 。

postgres=# call sp_insert_rollback(10,100);
NOTICE:  inserted 0 batch
NOTICE:  inserted 1 batch
NOTICE:  inserted 2 batch
NOTICE:  inserted 3 batch
NOTICE:  inserted 4 batch
NOTICE:  rollback inserted 5 batch
NOTICE:  inserted 6 batch
NOTICE:  inserted 7 batch
NOTICE:  inserted 8 batch
NOTICE:  inserted 9 batch
CALL

查看 插入的数据量

postgres=# select count(1) from tmp_t0;

 count 
-------
   900
(1 row)

nice

参考：
http://postgres.cn/docs/11/sql-createprocedure.html