Get count of records affected by INSERT or UPDATE in PostgreSQL

My database driver for PostgreSQL 8/9 does not return a count of records affected when executing INSERT or UPDATE.

PostgreSQL offers the non-standard syntax "RETURNING" which seems like a good workaround. But what might be the syntax? The example returns the ID of a record, but I need a count.

INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets') RETURNING did;

Solution 1:

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 INSERT
WITH rows AS (
    INSERT INTO distributors
        (did, dname)
    VALUES
        (DEFAULT, 'XYZ Widgets'),
        (DEFAULT, 'ABC Widgets')
    RETURNING 1
)
SELECT count(*) FROM rows;

-- Get count from UPDATE
WITH 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 ...

Solution 2:

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