How to get the number of deleted rows in PostgreSQL?

I am looking for a way to return the number of rows affected by a DELETE clause in PostgreSQL. The documentation states that;

> 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.

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 wanted to present an alternative that I have found later. It can be found in here, explained under 38.5.5. Obtaining the Result Status title.

You can use RETURNING clause:

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][1]:

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

This should return just the number of deleted rows.

[1]: http://www.postgresql.org/docs/9.0/static/queries-with.html "Common Table Expressions"

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

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

This should be simple in Java.

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

See java.sql.Statement.

in Python using psycopg2, the rowcount attribute can be used. Here is an example to find out how many rows were deleted...

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")

This works in functions. It works with other operations like INSERT as well.

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;

You need the PQcmdTuples function from libpq. Which in PHP for example is wrapped as pg_affected_rows.