Using GET DIAGNOSTICS integer_var = ROW_COUNT; capture rows effect by the last SQL

背景

在PostgreSQL中,执行insert,update,delete时，数据库都会返回给客户端有多少条SQL被insert,update或delete了。

如：

oup=> insert into dual values ('ok'),('ok');  
INSERT 0 2  
Time: 1.208 ms  
oup=> update dual set p_1=1;  
UPDATE 3  
Time: 1.250 ms  
oup=> delete from dual;  
DELETE 3  

同样，执行select时，除了返回给客户端所需要的记录之外，在结果的最后，也会附上有多少条记录返回了。

oup=> select * from dual;  
  
p_1  
  
—–  
  
ok  
  
ok  
  
ok  
  
ok  
  
(4 rows)  

在PLPGSQL中，如何来获得这些值呢？

PostgreSQL提供了一个方法，

GET DIAGNOSTICS variable = item [ , ... ];  

通过这个语法可以获得最近一条sql的row_count也可以获得最近一条SQL插入的表的OID；

如:

首先创建一个测试表

create table tbl_test(id int,col2 text); insert into tbl_test values (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');  

然后创建一个函数：

create or replace function f_test (i_1 text)  
  
returns setof int as $BODY$  
  
declare  
  
v_1 int;  
  
begin  
  
update tbl_test set col2='abcd' ;  
  
GET DIAGNOSTICS v_1 = ROW_COUNT;  
  
return next v_1;  
  
update tbl_test set col2=i_1 where id=1;  
  
GET DIAGNOSTICS v_1 = ROW_COUNT;  
  
return next v_1;  
  
perform * from tbl_test;  
  
GET DIAGNOSTICS v_1 = ROW_COUNT;  
  
return next v_1;  
  
return;  
  
end;  
  
$BODY$  
  
language plpgsql;  

测试:

oup=> select f_test('nihao');   
f_test   
——–   
5   
1   
5   
(3 rows)  

得到的结果和预期一致。

Time: 1.646 ms   
oup=> select * from tbl_test;   
id | col2   
—-+——-   
2 | abcd  
3 | abcd   
4 | abcd   
5 | abcd   
1 | nihao   
(5 rows)  

结果的更新也和预期一致。

下面来测试一下使用perform记录行数的效率：

create or replace function f_test (i_1 text) returns setof int as $BODY$   
declare   
v_1 int;   
begin   
v_1 := 0;   
update tbl_test set col2='abcd' ;   
GET DIAGNOSTICS v_1 = ROW_COUNT;   
return next v_1;   
update tbl_test set col2=i_1 where id=1;   
GET DIAGNOSTICS v_1 = ROW_COUNT;   
return next v_1;   
perform * from dual;   
GET DIAGNOSTICS v_1 = ROW_COUNT;   
return next v_1;   
return;   
end;   
$BODY$ language plpgsql;  

测试返回的结果：

oup=> select count(*) from dual;   
count   
———-   
12582912   
(1 row)   
Time: 1232.586 ms   
  
oup=> select f_test('nihao');   
f_test   
———-   
12582912   
(1 row)   
Time: 1857.694 ms  

返回的结果一直，但是使用PERFORM的效率要低一点，毕竟是有更多一点开销的，

不过1千多万记录才慢这么点，特殊情况下还是可以接受了。

再来一个测试返回OID的：

create table tbl_test1 (id int,col2 varchar(10)) with oids;   
insert into tbl_test1 values (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');  
  
create or replace function f_test (i_1 text) returns setof int as $BODY$   
declare v_1 int;   
begin v_1 := 0;   
insert into tbl_test1 values (1,i_1);   
GET DIAGNOSTICS v_1 = RESULT_OID;   
return next v_1;   
insert into tbl_test values (1,i_1);   
GET DIAGNOSTICS v_1 = RESULT_OID;   
return next v_1;   
return;   
end;   
$BODY$ language plpgsql;  

测试返回结果如下

oup=> select f_test('ab');   
f_test   
——–   
16602 0   
(2 rows)  

达到预期结果

参考

http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

digoal’s 大量PostgreSQL文章入口