Get affected rows PostgreSQL C#-CodePudding

Previously I had method which depending on whether I have version or not would either increment it or insert new column:

public void Increment(int id, long version)
    {
        if (version == 0)
        {
            _db.ExecuteNonQuery($@"
                INSERT INTO table (id, version)
                VALUES (:id, 0)
                ON CONFLICT DO NOTHING;
            ", _db.CreateParameter("id", id));
        }

        var affectedRows = _db.ExecuteNonQuery($@"
            UPDATE table
            SET version = version   1
            WHERE id = :id AND version = :preVersion;
            ",
            _db.CreateParameter("id", id),
            _db.CreateParameter("version", preVersion));

        if (affectedRows != 1)
            throw new Exception("None Updated");
    }

now I will not longer have version passed to me, so I am trying to use IF EXISTS statement to figure out whether I should insert or update. However, the problem is that the way I write my sql it doesn't return affected rows count and I am not sure how to change it. Currently it looks something like this:

public void Increment(int id)
    {
        var affectedRows = _db.ExecuteNonQuery($@"
        DO
        $do$
        BEGIN
            IF EXISTS (SELECT 1 FROM table WHERE id = {id}) THEN
                UPDATE table
                SET version = version   1, updated = '{DateTime.Now}'
                WHERE id = {id} AND (updated IS NULL);
            ELSE 
                INSERT INTO table (id, version, updated)
                VALUES ({id}, 0, '{DateTime.Now}');
            END IF;
        END
        $do$");

        if (affectedRows != 1)
            throw new Exception($"None updated");
    }

CodePudding user response：

Assuming that id is either a primary key or that it has an unique constraint then use your second example with this query, based on yours. It tries to insert, if that fails because id already exists then update but only if updated is null. I guess that this is exactly your logic but reordered and reworded in plain SQL.

INSERT INTO the_table (id, version, updated) VALUES ({id}, 0, current_timestamp)
ON CONFLICT (id) DO UPDATE 
SET version = version   1, updated = current_timestamp WHERE updated IS NULL;

I have substituted {DateTime.Now} with SQL-standard current_timestamp, hopefully for better readability.