英文:
Can you retrieve a list of updated rows when running an UPDATE query using the Go sqlx package with Postgres?
问题
我正在使用Go的sqlx包（标准database/sql包的扩展）与Postgres数据库运行一个查询。它的代码大致如下：
result, err := s.getDB(tx).Exec(`
UPDATE table_name
SET var_name = 0
WHERE var_name = 1;`)
if err != nil {
return nil, err
}
在不使用托管事务的情况下，如何获取已更新行的列表？或者至少获取已更新行的主键列表？
英文:
I have a query I'm running using Go's sqlx package (an extension to the standard database/sql package) with a Postgres database. It looks something like this:
result, err := s.getDB(tx).Exec(`
UPDATE table_name
SET var_name = 0
WHERE var_name = 1;`)
if err != nil {
return nil, err
}
Without using a managed transaction, how can I retrieve the list of updated rows? Or at least a list of primary keys for the updated rows?
答案1
得分: 3
使用PostgreSQL的RETURNING子句和db.Query/db.Select变体来替代Exec，例如：
//获取更新行的主键
db := s.getDB(tx)
results := []int{}
err = db.Select(&results, "UPDATE mytable SET var_name = 0 WHERE var_name = 1 RETURNING id")
要检索多个列，只需在RETURNING后面列出列名，例如：
rows, err := db.Queryx("UPDATE mytable SET var_name = 0 WHERE var_name = 1 RETURNING id,var_name,other_columns")
for rows.Next() {
//对结果进行处理
}
英文:
Use PostgreSQL's RETURNING clause and db.Query/db.Select variants instead of Exec, e.g.
//get the primary key of updated rows
db := s.getDB(tx)
results := []int{}
err = db.Select(&results, "UPDATE mytable SET var_name = 0 WHERE var_name = 1 RETURNING id")
To retrieve more than one columns, just list the column names after RETURNING, e.g.
rows, err := db.Queryx("UPDATE mytable SET var_name = 0 WHERE var_name = 1 RETURNING id,var_name,other_columns")
for rows.Next() {
//do something with the result
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论