Postgresql 函数返回受影响的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38875955/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql function return affected row count
提问by David Barbata
I created a function. I defined returning value void. But I want to show affected row count. How can I do?
我创建了一个函数。我定义了返回值无效。但我想显示受影响的行数。我能怎么做？
CREATE OR REPLACE FUNCTION update() RETURNS void AS
$BODY$
BEGIN
update test_a set name='cde' where name='abc';
update test_b set name='mno' where name='klm';
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION update()
OWNER TO postgres;
回答by emacsx
you should look into GET DIAGNOSTICS, since this is a PLpgSQL function. You might also find the Postgres SQL extension RETURNING * for UPDATE/INSERTS useful.
您应该查看 GET DIAGNOSTICS，因为这是一个 PLpgSQL 函数。您可能还会发现用于 UPDATE/INSERTS 的 Postgres SQL 扩展 RETURNING * 很有用。
CREATE OR REPLACE FUNCTION update() RETURNS void AS
$BODY$
DECLARE
a_count integer;
b_count integer;
BEGIN
update test_a set name='cde' where name='abc';
GET DIAGNOSTICS a_count = ROW_COUNT;
update test_b set name='mno' where name='klm';
GET DIAGNOSTICS b_count = ROW_COUNT;
RAISE NOTICE 'The rows affected by A=% and B=%', a_count, b_count ;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION update()
OWNER TO postgres;
Depending on what you might want to achieve, the special boolean variable "FOUND" could serve; UPDATE, INSERT, and DELETE statements set FOUND true if at least one row is affected, false if no row is affected.
根据您可能想要实现的目标，可以使用特殊的布尔变量“FOUND”；如果至少有一行受到影响，UPDATE、INSERT 和 DELETE 语句将 FOUND 设置为 true，如果没有行受到影响，则设置为 false。
