Get count of records affected by INSERT or UPDATE in PostgreSQL
I know this question is oooolllllld and my solution is arguably overly complex, but that's my favorite kind of solution!
Anyway, I had to do the same thing and got it working like this:
-- Get count from INSERTWITH rows AS ( INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets'), (DEFAULT, 'ABC Widgets') RETURNING 1)SELECT count(*) FROM rows;-- Get count from UPDATEWITH rows AS ( UPDATE distributors SET dname = 'JKL Widgets' WHERE did <= 10 RETURNING 1)SELECT count(*) FROM rows;
One of these days I really have to get around to writing a love sonnet to PostgreSQL's WITH clause ...
I agree w/ Milen, your driver should do this for you. What driver are you using and for what language? But if you are using plpgsql, you can use GET DIAGNOSTICS my_var = ROW_COUNT;
http://www.postgresql.org/docs/current/static/plpgsql-statements.html
You can take ROW_COUNT
after update or insert with this code:
insert into distributors (did, dname) values (DEFAULT, 'XYZ Widgets');get diagnostics v_cnt = row_count;