Dynamic get query in go using pgx

Question

So I found this: https://betterprogramming.pub/dynamic-sql-query-with-go-8aeedaa02907

it explains how to create a dynamic query for the basic sql package. I would like to do the same thing in pgx but i cant get it to work. Here is my code:

func (db *Database) Query(query string, queryParams ...interface{}) {

    rows, _ := db.conn.Query(context.Background(), query)
    defer rows.Close()

    fieldDescriptions := rows.FieldDescriptions()

    var cols []string
    for _, fieldDesc := range fieldDescriptions {
        cols = append(cols, string(fieldDesc.Name))
    }

    row := make([][]byte, len(cols))
    rowPtr := make([]interface{}, len(cols))
    for i := range row {
        rowPtr[i] = &row[i]
    }

    for rows.Next() {
        err := rows.Scan(rowPtr...)
        if err != nil {
            fmt.Println(err.Error())
        }

        fmt.Println(rowPtr)
    }
}

With this I get an error:

can't scan into dest[0]: cannot assign 1 into *[]uint8

The first item in the row is an id, the first has 1, so its trying to put the int into a *[]uint8, no idea whats going on there.

If i take out row stuff the

row := make([][]byte, len(cols))

for i := range row {
    rowPtr[i] = &row[i]
}

and just try to read directly to a variadic array of interface{} I would have thought it would read into it, since the Scan takes an interface{}, but i just get nil for everything. Anyone able to shed any light on any of this?

score 0 · Answer 1 · answered Sep 06 '22 at 18:30

Is this what you are looking for: https://github.com/DrakoRod/pgx-rowstojson/blob/master/main.go

https://github.com/jackc/pgx/issues/417

Copying the relevant section here for quick reference:

// Convert pgx.Rows to json object in []byte format
func PgSqlRowsToJson(rows pgx.Rows) []byte {
    fieldDescriptions := rows.FieldDescriptions()
    var columns []string
    for _, col := range fieldDescriptions {
        columns = append(columns, string(col.Name))
    }
    count := len(columns)
    tableData := make([]map[string]interface{}, 0)
    valuePtrs := make([]interface{}, count)

    for rows.Next() {
        values, _ := rows.Values()
        for i, v := range values {
            valuePtrs[i] = reflect.New(reflect.TypeOf(v)).Interface() // allocate pointer to type
        }
        break
    }

    for rows.Next() {
        rows.Scan(valuePtrs...)
        entry := make(map[string]interface{})
        for i, col := range columns {
            var v interface{}
            val := reflect.ValueOf(valuePtrs[i]).Elem().Interface() // dereference pointer
            b, ok := val.([]byte)
            if ok {
                v = string(b)
            } else {
                v = val
            }
            entry[col] = v
        }
        tableData = append(tableData, entry)
    }
    jsonData, _ := json.Marshal(tableData)

    return jsonData
}

Dynamic get query in go using pgx

1 Answers1