-
Notifications
You must be signed in to change notification settings - Fork 923
-
I'm using sqlc with pgx/v5 in go, for a delete query with :one annotation for eg.
-- name: DeleteEmployee :one delete from employee where employee_id = $1 returning employee_name;
I was expecting it to fail withpgx.ErrTooManyRows if the query returns more than one row but what i found was it picks the first row and just ignores the rest. sqlc codegen for pgx uses QueryRow method of pgx which has the behavior of ignoring all the rows but one if there are multiple, would it be possible to use Query along with CollectExactlyOneRow in place of QueryRow(jackc/pgx#1411 (comment)) ?
sqlc/internal/codegen/golang/templates/pgx/queryCode.tmpl
Lines 26 to 47 in 34f8c1b
Beta Was this translation helpful? Give feedback.
All reactions
Replies: 3 comments 4 replies
-
I made an enhancement request.
I think this should be done as adding a new annotation. I don't think :one should suddenly start erroring on places it did not error before. Someone might WANT to select just the first row from many. (see the discussion on the pgx thread too)
Beta Was this translation helpful? Give feedback.
All reactions
-
What do you want to change?
Pgx now supports CollectExactlyOneRow. This returns error when there is more than 1 resulting row, but doesn't fetch them all.
This could be done by either modifying :one behaviour, or adding :exactlyone annotation.
I think adding :exactlyone annotation is better, because erroring on :one on select where sqlc currently doesn't error is backwards incompatible change, and it doesn't strike me as correct anyway (:one can mean I actually want the first from many, see the pgx thread).
See:
jackc/pgx#1411 (comment)
#3956
If this is desired, I can take a shot at this feature and try to add it myself since it doesn't strike me as that hard (and, sqlc already has annotations that are just for go and just for pgx), but, let's see :)
Beta Was this translation helpful? Give feedback.
All reactions
-
@karelbilek I closed your issue, mainly so that we can continue the discussion here before we decide on anything.
:exactlyone is too long for my taste. Also, we have to remember that we want this to work for other engines / packages. Is there a way to get this behavior using database/sql?
Beta Was this translation helpful? Give feedback.
All reactions
-
Hm, sqlc already has annotations that only work for pgx?
Beta Was this translation helpful? Give feedback.
All reactions
-
Unfortunately, I don't know much about all the other engines that sqlc supports.
Beta Was this translation helpful? Give feedback.
All reactions
-
The pgx logic itself doesn't seem that complicated though; it just collects one row and then looks if there is more rows left and then errors
Beta Was this translation helpful? Give feedback.
All reactions
-
https://github.com/jackc/pgx/blob/25cba152992a6af96866cb794449f2ed674bb5e4/rows.go#L498
this functionality could be easily used directly in other engines
Beta Was this translation helpful? Give feedback.