PostgreSQL 9.3 Allow PL/pgSQL to access the number of rows processed by COPY

背景

Allow PL/pgSQL to access the number of rows processed by COPY (Pavel Stehule)  
The command is GET DIAGNOSTICS x = ROW_COUNT.  

加入plpgsql函数对获取copy影响行数的支持.

Allow SPI functions to access the number of rows processed by COPY (Pavel Stehule)  

SPI接口函数也增加了该支持.

http://www.postgresql.org/docs/devel/static/spi-spi-execute.html

src/backend/executor/spi.c

测试

PostgreSQL 9.2 :

digoal=# create table t(id int);  
CREATE TABLE  
digoal=# insert into t select generate_series(1,10);  
INSERT 0 10  
digoal=# do language plpgsql                     
digoal-# $$                                      
digoal$# declare                                 
digoal$# v_cnt int8;                             
digoal$# begin                                   
digoal$# copy t to '/home/pg92/t.csv' with csv;  
digoal$# get diagnostics v_cnt=ROW_COUNT;        
digoal$# raise notice 'v_cnt:%', v_cnt;          
digoal$# end;                                    
digoal$# $$;                                     
NOTICE:  v_cnt:0  
DO  
digoal=# do language plpgsql                     
$$                                      
declare                                 
v_cnt int8;                             
begin                                   
copy t from '/home/pg92/t.csv' with csv;  
get diagnostics v_cnt=ROW_COUNT;        
raise notice 'v_cnt:%', v_cnt;          
end;                                    
$$;                                     
NOTICE:  v_cnt:0  
DO  
digoal=# select count(*) from t;  
 count   
-------  
    20  
(1 row)  

PostgreSQL 9.3 :

digoal=# create table t(id int);  
CREATE TABLE  
digoal=# insert into t select generate_series(1,10);  
INSERT 0 10  
digoal=# do language plpgsql  
digoal-# $$  
digoal$# declare  
digoal$# v_cnt int8;  
digoal$# begin  
digoal$# copy t to '/home/pg93/t.csv' with csv;  
digoal$# get diagnostics v_cnt=ROW_COUNT;  
digoal$# raise notice 'v_cnt:%', v_cnt;  
digoal$# end;  
digoal$# $$;  
NOTICE:  v_cnt:10  
DO  
digoal=# do language plpgsql  
$$  
declare  
v_cnt int8;  
begin  
copy t from '/home/pg93/t.csv' with csv;  
get diagnostics v_cnt=ROW_COUNT;  
raise notice 'v_cnt:%', v_cnt;  
end;  
$$;  
NOTICE:  v_cnt:10  
DO  
digoal=# select count(*) from t;  
 count   
-------  
    20  
(1 row)  

参考

1. http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7ae1815961c635fd1a6fe72acb89fdef741a45a8

2. http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7ae1815961c635fd1a6fe72acb89fdef741a45a8

digoal’s 大量PostgreSQL文章入口