Handling Dynamic Queries (cant scan into struct)

Question

When using go-pg where the structure of queries is static - querying/scanning directly into a known struct works like a dream. But, I am struggling to handle dynamic queries - ones where there is no struct to scan into.

For example, depending on some run time parameters - queries could look like:

select foo from table

or it could be

select foo,bar,baz from table1

or

select x,y,z from table2

I've been trying to figure out how to use load the results into a map. The code below throws an error "invalid character '\' looking for beginning of value"

m := make(map[string]interface{})
_,err:=db.Query(&m, "select foo,bar from table1")
if err!=nil{
   fmt.Println(err)
}

I'm just starting to learn go - and am totally lost. Any tips on how to handle dynamic queries

Answer 1

You can achieve this by first scanning the database row values into a slice and subsequently building a map holding the values of the row.

Here is an example where the query results are scanned into an slice of pointers to variables of type interface{}.

sql := "select foo,bar from table1"
rows, err := db.Query(sql)
columns, err := rows.Columns()

// for each database row / record, a map with the column names and row values is added to the allMaps slice 
var allMaps []map[string]interface{}

for rows.Next() {
    values := make([]interface{}, len(columns))
    pointers := make([]interface{}, len(columns))
    for i,_ := range values {
        pointers[i] = &values[i]
    }
    err := rows.Scan(pointers...)
    resultMap := make(map[string]interface{})
    for i,val := range values {
        fmt.Printf("Adding key=%s val=%v\n", columns[i], val)
        resultMap[columns[i]] = val 
    }
    allMaps = append(allMaps, resultMap)
}

For brevity, no error checking is performed on any errors.