In some situations it’s not obvious how to use the RETURNING
SQL clause with Gorm and databases that support it, like CockroachDB.
The particular use case where I found it challenging is the following: I want
to update multiple records with a WHERE clause, ORDER BY clause and
LIMIT clause and immediately return the updated records without a second query.
The simplified CockroachDB table schema without indices and noise is:
1
2
3
4
5
|
column_name | data_type | is_nullable | column_default
--------------+-----------+-------------+----------------
field1 | INT8 | true | NULL
field2 | INT8 | true | NULL
created_at | INT8 | true | NULL
|
My initial attempts were as follows.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
type Record struct {
Field1 int
Field2 int
CreatedAt int64
}
func update(field1 int, field2 int, limit int) ([]Record, error) {
var records []Record
err := db.Model(&records).
Table("table_name").
Clauses(clause.Returning{}).
Where("field1 = ?", field1).
Updates(map[string]interface{}{"field2":field2}).
Order("created_at ASC").
Limit(limit).
Error
if err != nil {
return nil, err
}
return records, nil
}
|
Gorm builds the SQL statement successfully, but for some reason doesn’t include the
ORDER BY and LIMIT clauses, which are very important in my particular case.
Ok, let’s try with a raw SQL statement.
1
2
3
4
5
6
7
8
|
func update(field1 int, field2 int, limit int) ([]Record, error) {
var records []Record
sql := `UPDATE table_name SET field2 = $1 WHERE field1 = $2 ORDER BY created_at ASC LIMIT $3 RETURNING *`
if err := db.Model(&records).Table("table_name").Exec(sql, field1, field2, limit).Error; err != nil {
return nil, err
}
return records, nil
}
|
Gorm executes the complete correct query, but I couldn’t find a way to return/populate the records slice - it was
always empty (tried with different arrangements and examples from the docs). Without any deep investigation on why
that happens, it seems that Gorm just ignores the RETURNING data when executing raw SQL statements.
What finally worked for me seems a bit like a hack, but it does the job. By using the ORDER BY and LIMIT
clauses directly in the WHERE clause, the query works correctly as I need it.
1
2
3
4
5
6
7
8
9
10
11
12
|
func update(field1 int, field2 int, limit int) ([]Record, error) {
var records []Record
err := db.Model(&records).
Table("table_name").
Clauses(clause.Returning{}).
Where("field1 = $1 ORDER BY created_at LIMIT $2", field1, limit).
Updates(map[string]interface{}{"field2": field2}).
Error
if err != nil {
return nil, err
}
return records, nil
|
The examples use Updates with map[string]interface{} but the issue described here is not affected
by that. Using struct or other ways for updating produces the same results.
note
It looks like PostgreSQL does not work with LIMIT when using a RETURNING clause so another solution could be used, like SELECT FOR UPDATE -> then update, and return the updated record.