postgresql 如何获取PostgreSQL中删除的行数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2251567/
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
How to get the number of deleted rows in PostgreSQL?
提问by Erkan Haspulat
I am looking for a way to return the number of rows affected by a DELETE clause in PostgreSQL. The documentationstates that;
我正在寻找一种方法来返回受 PostgreSQL 中 DELETE 子句影响的行数。该文件指出；
On successful completion, a DELETE command returns a command tag of the form
DELETE count
The count is the number of rows deleted. If count is 0, no rows matched the condition (this is not considered an error).
If the DELETE command contains a RETURNING clause, the result will be similar to that of a SELECT statement containing the columns and values defined in the RETURNING list, computed over the row(s) deleted by the command.
成功完成后，DELETE 命令返回一个命令标签的形式
删除计数
计数是删除的行数。如果计数为 0，则没有与条件匹配的行（这不被视为错误）。
如果 DELETE 命令包含 RETURNING 子句，则结果将类似于包含在 RETURNING 列表中定义的列和值的 SELECT 语句的结果，对命令删除的行进行计算。
But I am having trouble finding a good example of it. Can anyone help me with this, how can I find out how many rows were deleted?
但是我很难找到一个很好的例子。谁能帮我解决这个问题，我怎么知道删除了多少行？
EDIT:I accepted Milen's solution, but I wanted to present an alternative that I have found later. It can be found in here, explained under 38.5.5. Obtaining the Result Statustitle.
编辑：我接受了 Milen 的解决方案，但我想提出一个我后来发现的替代方案。它可以在这里找到，在38.5.5下解释。获取结果状态标题。
采纳答案by Milen A. Radev
You need the PQcmdTuplesfunction from libpq. Which in PHP for example is wrapped as pg_affected_rows.
您需要PQcmdTuples来自libpq. 例如，在 PHP 中，它被包装为pg_affected_rows.
回答by Jakub Fedyczak
You can use RETURNINGclause:
您可以使用RETURNING条款：
DELETE FROM table WHERE condition IS TRUE RETURNING *;
After that you just have to check number of rows returned. You can streamline it with CTE:
之后，您只需检查返回的行数。您可以使用CTE简化它：
WITH deleted AS (DELETE FROM table WHERE condition IS TRUE RETURNING *) SELECT count(*) FROM deleted;
This should return just the number of deleted rows.
这应该只返回已删除的行数。
回答by cope360
This should be simple in Java.
这在 Java 中应该很简单。
Statement stmt = connection.createStatement();
int rowsAffected = stmt.executeUpdate("delete from your_table");
System.out.println("deleted: " + rowsAffected);
See java.sql.Statement.
回答by Ankur Srivastava
GET DIAGNOSTICS is used to display number of modified/deleted records.
GET DIAGNOSTICS 用于显示修改/删除记录的数量。
Sample code
示例代码
CREATE OR REPLACE FUNCTION fnName()
RETURNS void AS
$BODY$
declare
count numeric;
begin
count := 0;
LOOP
-- condition here update or delete;
GET DIAGNOSTICS count = ROW_COUNT;
raise notice 'Value: %', count;
end loop;
end;
$BODY$a
回答by Dave
in Python using psycopg2, the rowcountattribute can be used. Here is an example to find out how many rows were deleted...
在使用 psycopg2 的 Python 中，可以使用rowcount属性。下面是一个例子来找出有多少行被删除......
cur = connection.cursor()
try:
cur.execute("DELETE FROM table WHERE col1 = %s", (value,))
connection.commit()
count = cur.rowcount
cur.close()
print("A total of %s rows were deleted." % count)
except:
connection.rollback()
print("An error as occurred, No rows were deleted")
