SQL 获取 PostgreSQL 中受 INSERT 或 UPDATE 影响的记录数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4038616/
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
Get count of records affected by INSERT or UPDATE in PostgreSQL
提问by Un Homme
My database driver for PostgreSQL 8/9 does not return a count of records affected when executing INSERTor UPDATE.
我的 PostgreSQL 8/9 数据库驱动程序在执行INSERT或时不返回受影响的记录计数UPDATE。
PostgreSQL offers the non-standard syntax "RETURNING" which seems like a good workaround. But what might be the syntax? The example returns the ID of a record, but I need a count.
PostgreSQL 提供了非标准语法“ RETURNING”，这似乎是一个很好的解决方法。但是语法可能是什么？该示例返回记录的 ID，但我需要一个计数。
INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets') RETURNING did;
INSERT INTO 经销商 (did, dname) VALUES (DEFAULT, 'XYZ Widgets') RETURNING did;
回答by mercurial
I know this question is oooolllllld and my solution is arguably overly complex, but that's my favorite kind of solution!
我知道这个问题是 oooolllllld 并且我的解决方案可以说过于复杂，但这是我最喜欢的解决方案！
Anyway, I had to do the same thing and got it working like this:
无论如何，我必须做同样的事情并让它像这样工作：
-- Get count from INSERT
WITH rows AS (
INSERT INTO distributors
(did, dname)
VALUES
(DEFAULT, 'XYZ Widgets'),
(DEFAULT, 'ABC Widgets')
RETURNING 1
)
SELECT count(*) FROM rows;
-- Get count from UPDATE
WITH rows AS (
UPDATE distributors
SET dname = 'JKL Widgets'
WHERE did <= 10
RETURNING 1
)
SELECT count(*) FROM rows;
One of these days I really have to get around to writing a love sonnet to PostgreSQL's WITH clause ...
有一天，我真的不得不为 PostgreSQL 的 WITH 子句写一首爱情十四行诗......
回答by Scott Bailey
I agree w/ Milen, your driver should do this for you. What driver are you using and for what language? But if you are using plpgsql, you can use GET DIAGNOSTICS my_var = ROW_COUNT;
我同意 Milen，你的司机应该为你做这件事。您使用什么驱动程序，使用什么语言？但是如果你使用的是plpgsql，你可以使用GET DIAGNOSTICS my_var = ROW_COUNT;
http://www.postgresql.org/docs/current/static/plpgsql-statements.html
http://www.postgresql.org/docs/current/static/plpgsql-statements.html
回答by Igor Cova
You can take ROW_COUNTafter update or insert with this code:
您可以ROW_COUNT使用以下代码进行更新或插入：
insert into distributors (did, dname) values (DEFAULT, 'XYZ Widgets');
get diagnostics v_cnt = row_count;
回答by Chris Halcrow
You could wrap your query in a transaction and it should show you the count before you ROLLBACKor COMMIT. Example:
您可以将您的查询包装在一个事务中，它应该在您ROLLBACK或之前显示计数COMMIT。例子：
BEGIN TRANSACTION;
INSERT .... ;
ROLLBACK TRANSACTION;
If you run the first 2 lines of the above, it should give you the count. You can then ROLLBACK(undo) the insert if you find that the number of affected lines isn't what you expected. If you're satisfied that the INSERTis correct, then you can run the same thing, but replace line 3 with COMMIT TRANSACTION;.
如果你运行上面的前两行，它应该给你计数。ROLLBACK如果您发现受影响的行数不是您所期望的，那么您可以（撤消）插入。如果您认为INSERT是正确的，那么您可以运行相同的操作，但将第 3 行替换为COMMIT TRANSACTION;.
Important note:After you run any BEGIN TRANSACTION;you musteither ROLLBACK;or COMMIT;the transaction, otherwise the transaction will create a lock that can slow down or even cripple an entire system, if you're running on a production environment.
重要提示：你运行任何后BEGIN TRANSACTION;你必须要么ROLLBACK;或COMMIT;交易，否则交易将创建一个锁，可以减缓甚至瘫痪整个系统，如果你在生产环境中运行。
回答by beldaz
It's not clear from your question how you're calling the statement. Assuming you're using something like JDBC you may be calling it as a query rather than an update. From JDBC's executeQuery:
从您的问题中不清楚您如何称呼该声明。假设您使用的是 JDBC 之类的东西，您可能将其称为查询而不是更新。从 JDBC 的executeQuery：
Executes the given SQL statement, which returns a single ResultSet object.
执行给定的 SQL 语句，该语句返回单个 ResultSet 对象。
This is therefore appropriate when you execute a statement that returns some query results, such as SELECTor INSERT ... RETURNING. If you are making an update to the database and then want to know how many tuples were affected, you need to use executeUpdatewhich returns:
因此，当您执行返回某些查询结果的语句（例如SELECT或 ）时，这是合适的INSERT ... RETURNING。如果您正在更新数据库，然后想知道有多少元组受到影响，您需要使用executeUpdatewhich 返回：
either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing
(1) SQL 数据操作语言 (DML) 语句的行数或 (2) 0 对于不返回任何内容的 SQL 语句
