6

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.

ted.strauss
  • 4,119
  • 4
  • 34
  • 57
  • What language are you working in? PHP? Is this part of a script inside a PostgreSQL function like PL/php: http://www.commandprompt.com/community/plphp/ or an external PHP interpreter connecting to PostgreSQL? – kgrittn Apr 11 '12 at 21:20

1 Answers1

8

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

Anonymous
  • 18,162
  • 2
  • 41
  • 64