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.

What language are you working in? PHP? Is this part of a script inside a PostgreSQL function like PL/php: commandprompt.com/community/plphp or an external PHP interpreter connecting to PostgreSQL? — kgrittn
– kgrittn, Commented Apr 11, 2012 at 21:20

Anonymous · Accepted Answer · 2012-04-11 20:54:33Z

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

answered Apr 11, 2012 at 20:54

Anonymous

18.8k2 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting number of rows affected by an UPDATE in PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related