Getting number of rows affected by an UPDATE in PostgreSQL

Question

variations of this question has been asked on SO and on many blogs but none offers a straight-forward answer. I hope there is one. I am updating PostgreSQL 9.0 (from CodeIgniter, PHP framework):

$sql_order = "UPDATE meters SET billed=true"; 
$query = $this->db->query($sql_order);

I simply need a count of rows that were affected by the update, but there seems to be no way to do this with PostgreSQL. The query is now returning a boolean - true. The manual and web talk refer to the RETURNING syntax, to GET DIAGNOSTICS, and to a default return type from UPDATE. I haven't been able to get any of these to work. Is there a straightforward way of getting rows affect count without having to embed this simple operation into a procedure or transaction.

Answer 1

In Java I would have used the following:

Statement stmt = connection.createStatement();
int rowsAffected = stmt.executeUpdate("UPDATE ...");

In PHP I believe pg_affected_rows is the way. And in your particular case $this->db->affected_rows()