< Prev: Struct mapping
Up: Basics
Next: Logging >

Basics: Fetch and Exec

Contents

Overview

  contents
If you look at the Query Builder Reference, you will notice that the query builders possess some combination of the Fetch and Exec methods.
Postgres
Query Fetch Exec
SELECT ✓ ✓
INSERT ✓ ✓
UPDATE ✓ ✓
DELETE ✓ ✓

MySQL
Query Fetch Exec
SELECT ✓
INSERT ✓
UPDATE ✓
DELETE ✓

These are what they do

ExecFlag

  contents
Exec takes in an ExecFlag. Here are the possible values: 
// Postgres
const (
    Erowsaffected ExecFlag = 1 << iota
)

// MySQL
const (
    ElastInsertID ExecFlag = 1 << iota // ElastInsertID only applies to INSERT
    Erowsaffected ExecFlag
)

The ExecFlag dictates whether Exec will compute the lastInsertID or rowsAffected. If not specified, the value of the lastInsertID or rowsAffected returned will always be 0.

Only MySQL has the ElastInsertID flag, and it is only applicable to the InsertQuery. For Postgres, use Returningx/ReturningRowx with Fetch to get the lastInsertID.

Getting the number of rows affected by INSERT, UPDATE or DELETE

  contents
You need to pass in the Erowsaffected ExecFlag to Exec. 
// Postgres
rowsAffected, err := InsertInto(tbl).
    Column(tbl.A, tbl.B).
    Values("a", "b").
    Exec(DB, Erowsaffected)
//           ^ Erowsaffected must be used otherwise
//             rowsAffected will always be 0

// MySQL
lastInsertID, rowsAffected, err := InsertInto(tbl).
    Column(tbl.A, tbl.B).
    Values("a", "b").
    Exec(DB, ElastInsertID|Erowsaffected)
//           ^ ElastInsertID and Erowsaffected must be used otherwise
//             lastInsertID and rowsAffected will always be 0
Why would I want to call Fetch on INSERT, UPDATE or DELETE? (postgres only)
The RETURNING clause effectively turns INSERT, UPDATE and DELETE into pseudo-SELECTs that return the rows that were affected. It could be advantageous to collect a slice of structs containing the rows that you just inserted, updated or deleted. In particular, this is the only way for Postgres to return an inserted row's auto incremented id. It has an advantage over how MySQL does it, because you can INSERT multiple rows and return the ID of every inserted row. MySQL only has the ability to return the ID of the last inserted row.