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;
DELETE FROM table WHERE condition IS TRUE
更低效?例如,当评估 CTE 时,Postgres 是否会在某处临时存储已删除的行,或者它是否足够聪明,只看到仅使用计数,而将被删除的行作为它们被删除而计数? - EM0GET 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
在Java中,这应该很简单。
Statement stmt = connection.createStatement();
int rowsAffected = stmt.executeUpdate("delete from your_table");
System.out.println("deleted: " + rowsAffected);
在使用Python的psycopg2库时,可以使用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")
这适用于函数。它也可以与其他操作(如INSERT)一起使用。
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 % rows with amount = 0', _result;
END IF;