一、环境:
DB:9.4beta3
二、准备:
postgres=# create table test(id int);
CREATE TABLE
postgres=# insert into test select generate_series(1,20);
INSERT 0 20
postgres=# select * from test;
id
----
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
(20 rows)
三、创建函数:
CREATE OR REPLACE FUNCTION fun_affect_rows()
RETURNS text AS
$BODY$
declare
v_count int;begin
insert into test values(99),(98);
GET DIAGNOSTICS v_count = ROW_COUNT;
raise notice '本次插入数据量 %', v_count;
delete from test where id < 15;
GET DIAGNOSTICS v_count = ROW_COUNT;
raise notice '本次删除数据量 %', v_count;
update test set id = 100 where id >90;
GET DIAGNOSTICS v_count = ROW_COUNT;
raise notice '本次更新数据量 %', v_count;
return '测试完毕';
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION fun_affect_rows()
OWNER TO postgres;
四、运行情况:
postgres=# select * from fun_affect_rows();
NOTICE: 本次插入数据量 2
NOTICE: 本次删除数据量 14
NOTICE: 本次更新数据量 2
fun_affect_rows
-----------------
测试完毕
(1 row)
如果要返回DML的结果,那就用returning就可以了
五、参考
http://www.postgresql.org/docs/9.0/static/plpgsql-statements.html