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)
达到预期结果

不错的资料，学习。