blocks|key|1411217|text|您可以使用RETURNING子句：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1411218|DELETE+FROM+table+WHERE+condition+IS+TRUE+RETURNING+*;|code-block|syntax|javascript|1411219|之后，您只需检查返回的行数。您可以使用CTE简化它|1411220|WITH+deleted+AS+(DELETE+FROM+table+WHERE+condition+IS+TRUE+RETURNING+*)+SELECT+count(*)+FROM+deleted;|1411221|这应该只返回已删除的行数。|1411222|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/9.0/static/queries-with.html^0|5|9|0|0|J|3|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|11|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|12|8|@]|D|@$9|13|A|14|1|15]]|E|$]]|$1|M|3|N|5|H|7|16|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|17|8|@]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|18|8|@]|D|@]|E|$]]]|R|$S|$5|T|U|V|E|$W|X]]]]

You can use <code>RETURNING</code> clause:

<pre><code>DELETE FROM table WHERE condition IS TRUE RETURNING *;
</code></pre>

After that you just have to check number of rows returned. You can streamline it with <a href="http://www.postgresql.org/docs/9.0/static/queries-with.html">CTE</a>:

<pre><code>WITH deleted AS (DELETE FROM table WHERE condition IS TRUE RETURNING *) SELECT count(*) FROM deleted;
</code></pre>

This should return just the number of deleted rows.

blocks|key|1236405|text|GET诊断信息用于显示已修改/删除的记录的数量。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1236406|样本代码|1236407|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:+%25',+count;
+++++++++++++end+loop;
++++++++end;
$BODY$a|code-block|syntax|javascript|1236408|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

GET DIAGNOSTICS is used to display number of modified/deleted records.

Sample code

<pre><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
</code></pre>

blocks|key|1234902|text|这在Java中应该很简单。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1234903|Statement+stmt+=+connection.createStatement();
int+rowsAffected+=+stmt.executeUpdate("delete+from+your_table");
System.out.println("deleted:+"+%2B+rowsAffected);|code-block|syntax|javascript|1234904|见java.sql.Statement。|offset|length|1234905|entityMap|0|LINK|mutability|MUTABLE|url|http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html#executeUpdate%2528java.lang.String%2529^0|0|0|1|I|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

This should be simple in Java. 

<pre><code>Statement stmt = connection.createStatement();
int rowsAffected = stmt.executeUpdate("delete from your_table");
System.out.println("deleted: " + rowsAffected);
</code></pre>

See <a href="http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html#executeUpdate%28java.lang.String%29" rel="noreferrer">java.sql.Statement</a>.

blocks|key|1411168|text|在使用psycopg2的Python中，可以使用行计数属性。下面是一个示例，可以找出删除了多少行.|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1411169|cur+=+connection.cursor()
try:
++++cur.execute("DELETE+FROM+table+WHERE+col1+=+%25s",+(value,))
++++connection.commit()
++++count+=+cur.rowcount
++++cur.close()
++++print("A+total+of+%25s+rows+were+deleted."+%25+count)
except:
++++connection.rollback()
++++print("An+error+as+occurred,+No+rows+were+deleted")|code-block|syntax|javascript|1411170|entityMap|0|LINK|mutability|MUTABLE|url|http://initd.org/psycopg/docs/cursor.html#cursor.rowcount^0|O|3|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

in Python using psycopg2, the <a href="http://initd.org/psycopg/docs/cursor.html#cursor.rowcount" rel="noreferrer">rowcount</a> attribute can be used. Here is an example to find out how many rows were deleted...

<pre><code>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")
</code></pre>

blocks|key|1411263|text|这在功能上是可行的。它也适用于其他操作，如INSERT。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1411264|DECLARE+_result+INTEGER;
...
DELETE+FROM+mytable+WHERE+amount+=+0;++--+or+whatever+other+operation+you+desire
GET+DIAGNOSTICS+_result+=+ROW_COUNT;
IF+_result+>+0+THEN
++++RAISE+NOTICE+'Removed+%25+rows+with+amount+=+0',+_result;
END+IF;|code-block|syntax|javascript|1411265|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This works in functions. It works with other operations like INSERT as well.
<pre><code>DECLARE _result INTEGER;
...
DELETE FROM mytable WHERE amount = 0; -- or whatever other operation you desire
GET DIAGNOSTICS _result = ROW_COUNT;
IF _result &gt; 0 THEN
 RAISE NOTICE 'Removed % rows with amount = 0', _result;
END IF;
</code></pre>

blocks|key|1411144|text|您需要来自PQcmdTuples的libpq函数。例如，在PHP中，它被包装为pg_affected_rows。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1411145|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/current/static/libpq-exec.html#LIBPQ-EXEC-NONSELECT|1|http://www.postgresql.org/docs/current/static/libpq.html|2|http://www.php.net/manual/en/function.pg-affected-rows.php^0|5|B|H|5|13|G|5|B|0|H|5|1|13|G|2|0^^$0|@$1|2|3|4|5|6|7|R|8|@$9|S|A|T|B|C]|$9|U|A|V|B|C]|$9|W|A|X|B|C]]|D|@$9|Y|A|Z|1|10]|$9|11|A|12|1|13]|$9|14|A|15|1|16]]|E|$]]|$1|F|3|-4|5|6|7|17|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]|N|$5|I|J|K|E|$L|O]]|P|$5|I|J|K|E|$L|Q]]]]

You need the <a href="http://www.postgresql.org/docs/current/static/libpq-exec.html#LIBPQ-EXEC-NONSELECT" rel="nofollow noreferrer"><code>PQcmdTuples</code></a> function from <a href="http://www.postgresql.org/docs/current/static/libpq.html" rel="nofollow noreferrer"><code>libpq</code></a>. Which in PHP for example is wrapped as <a href="http://www.php.net/manual/en/function.pg-affected-rows.php" rel="nofollow noreferrer"><code>pg_affected_rows</code></a>.

I am looking for a way to return the number of rows affected by a DELETE clause in PostgreSQL. The <a href="http://www.postgresql.org/docs/8.2/static/sql-delete.html" rel="noreferrer">documentation</a> states that;
<blockquote>
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.
</blockquote>
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?
<hr />
EDIT:
I wanted to present an alternative that I have found later. It can be found in <a href="http://developer.postgresql.org/pgdocs/postgres/plpgsql-statements.html" rel="noreferrer">here</a>, explained under 38.5.5. Obtaining the Result Status title.

How to get the number of deleted rows in PostgreSQL?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正在寻找一种在PostgreSQL中返回受DELETE子句影响的行数的方法。指出；成功完成后，DELETE命令返回窗体的命令标记。删除计数计数是已删除的行数。如果计数为0，则没有符合条件的行(这不被视为错误)。如果DELETE命令包含返回子句，则结果与SELECT语句的结果类似，SELECT语句包含返回列表中定义的列和值，这些列和值由命令删除的行计算。但我很难找到一个很好的例子。有人能帮我吗?我

问如何获取PostgreSQL中已删除的行数？
EN

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何获取PostgreSQL中已删除的行数？EN

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何获取PostgreSQL中已删除的行数？
EN