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 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 …