PostgreSQL always returns success for delete operations (sqlc related)

Question

Since PostgreSQL always returns success for delete operations (sqlc related), how can I write the PostgreSQL syntax that can be read by sqlc to check the rows affected after execute delete operations, so that the method generated by sqlc will return (int, err) instead of err only?

Answer 1

You can use the :execrows query annotation to return the number of affected rows from the result returned by ExecContext.

see https://docs.sqlc.dev/en/v1.12.0/reference/query-annotations.html

e.g.

given the table...

CREATE TABLE authors (
  id         SERIAL PRIMARY KEY,
  bio        text   NOT NULL
);

You can annotate the query with :execrows like so...

-- name: DeleteAuthor :execrows
DELETE FROM authors WHERE id = $1;

Which should give code generated like so - notice return is (int64, error) - not just error...

package db

import (
    "context"
)

const deleteAuthor = `-- name: DeleteAuthor :execrows
DELETE FROM authors WHERE id = $1
`

func (q *Queries) DeleteAuthor(ctx context.Context, id int32) (int64, error) {
    result, err := q.db.ExecContext(ctx, deleteAuthor, id)
    if err != nil {
        return 0, err
    }
    return result.RowsAffected()
}
​

I made a working playground for this here https://play.sqlc.dev/p/2d7791f88a5e2414eb18b6b6b07846dd61df00a03007df869ff3821cb1b12dcd