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.

Answer 1

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