如何获取PostgreSQL中删除的行数?

你需要 PQcmdTuples 函数从 libpq .例如,在PHP中,包装为 pg_affected_rows .

你可以使用 RETURNING 条款:

DELETE FROM table WHERE condition IS TRUE RETURNING *;

之后,您只需检查返回的行数。你可以简化它 CTE :

WITH deleted AS (DELETE FROM table WHERE condition IS TRUE RETURNING *) SELECT count(*) FROM deleted;

这应该只返回删除的行数。

这在Java中应该是简单的。

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

见 java.sql.Statement .

在使用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")

get diagnostics用于显示修改/删除的记录数。

样本代码

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