Calculate number of rows affected by batch query in PostgreSQL

Question

First of all, yes I've read documentation for DO statement :) http://www.postgresql.org/docs/9.1/static/sql-do.html

So my question:

I need to execute some dynamic block of code that contains UPDATE statements and calculate the number of all affected rows. I'm using Ado.Net provider.

In Oracle the solution would have 4 steps:

1. add InputOutput parameter "N" to command
2. add BEGIN ... END; to command
3. add :N := :N + sql%rowcount after each statement.
4. It's done! We can read N parameter from command, after execute it.

How can I do it with PostgreSQL? I'm using npgsql provider but can migrate to devard if it helps.

Liquid

Answer 1

My solution is quite simple. In Oracle I need to use variables to calculate the sum of updated rows because command.ExecuteNonQuery() returns only the count of rows affected by the last UPDATE in the batch.

However, npgsql returns the sum of all rows updated by all UPDATE queries. So I only need to call command.ExecuteNonQuery() and get the result without any variables. Much easier than with Oracle.

Liquid

Answer 2

DO statement blocks are good to execute dynamic SQL. They are no good to return values. Use a plpgsql function for that.

The key statement you need is:

GET DIAGNOSTICS integer_var = ROW_COUNT;

Details in the manual.

Example code:

CREATE OR REPLACE FUNCTION f_upd_some()
  RETURNS integer AS
$func$
DECLARE
   ct int;
   i  int;
BEGIN
   EXECUTE 'UPDATE tbl1 ...';       -- something dynamic here
   GET DIAGNOSTICS ct = ROW_COUNT;  -- initialize with 1st count

   UPDATE tbl2 ...;                 -- nothing dynamic here 
   GET DIAGNOSTICS i = ROW_COUNT;
   ct := ct + i;                    -- add up

   RETURN ct;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM f_upd_some();

Quiddity