CODEWITHSUNDEEP
postgresqlgo
Archana Moharana
Archana Moharana

Reputation: 35

How to retrieve values in GoLang database/sql, when my structure in db(here postgres) is unknown?

I use Go with PostgreSQL using github.com/lib/pq and able to successfully fetch the records when my structure is known. Now my query is how to fetch records when my structure changes dynamically?

By rows.columns I am able to fetch the column names, but could you help me with fetching the values of these columns for all the rows. I referred this link answered by @Luke, still, here the person has a structure defined. Is it possible to retrieve a column value by name using GoLang database/sql

type Person struct {
    Id int
    Name string
}

Meanwhile I do not have a structure that is fixed, so how will I iterate through all the columns that too again for all rows. My approach would be a pointer to loop through all columns at first, then another one for going to next row. Still not able to code this, Could you please help me with this, like how to proceed and get the values.

Upvotes: 2

Views: 4955

Answers (2)

mmm444
mmm444

Reputation: 193

This function prints the result of a query without knowing anything about the column types and count. It is a variant of the previous answer without using the reflect package.

func printQueryResult(db *sql.DB, query string) error {
    rows, err := db.Query(query)
    if err != nil {
        return fmt.Errorf("canot run query %s: %w", query, err)
    }
    defer rows.Close()

    cols, _ := rows.Columns()
    row := make([]interface{}, len(cols))
    rowPtr := make([]interface{}, len(cols))
    for i := range row {
        rowPtr[i] = &row[i]
    }
    fmt.Println(cols)
    for rows.Next() {
        err = rows.Scan(rowPtr...)
        if err != nil {
            fmt.Println("cannot scan row:", err)
        }
        fmt.Println(row...)
    }
    return rows.Err()
}

The trick is that rows.Scan can scan values into *interface{} but you have to wrap it in interface{} to be able to pass it to Scan using ....

Upvotes: 1

mkopriva
mkopriva

Reputation: 38233

Since you don't know the structure up front you can return the rows as a two dimensional slice of empty interfaces. However for the row scan to work you'll need to pre-allocate the values to the appropriate type and to do this you can use the ColumnTypes method and the reflect package. Keep in mind that not every driver provides access to the columns' types so make sure the one you use does.

rows, err := db.Query("select * from foobar")
if err != nil {
    return err
}
defer rows.Close()

// get column type info
columnTypes, err := rows.ColumnTypes()
if err != nil {
    return err
}

// used for allocation & dereferencing
rowValues := make([]reflect.Value, len(columnTypes))
for i := 0; i < len(columnTypes); i++ {
    // allocate reflect.Value representing a **T value
    rowValues[i] = reflect.New(reflect.PtrTo(columnTypes[i].ScanType()))
}

resultList := [][]interface{}{}
for rows.Next() {
    // initially will hold pointers for Scan, after scanning the
    // pointers will be dereferenced so that the slice holds actual values
    rowResult := make([]interface{}, len(columnTypes))
    for i := 0; i < len(columnTypes); i++ {
        // get the **T value from the reflect.Value
        rowResult[i] = rowValues[i].Interface()
    }

    // scan each column value into the corresponding **T value
    if err := rows.Scan(rowResult...); err != nil {
        return err
    }

    // dereference pointers
    for i := 0; i < len(rowValues); i++ {
        // first pointer deref to get reflect.Value representing a *T value,
        // if rv.IsNil it means column value was NULL
        if rv := rowValues[i].Elem(); rv.IsNil() {
            rowResult[i] = nil
        } else {
            // second deref to get reflect.Value representing the T value
            // and call Interface to get T value from the reflect.Value
            rowResult[i] = rv.Elem().Interface()
        }
    }

    resultList = append(resultList, rowResult)

}
if err := rows.Err(); err != nil {
    return err
}

fmt.Println(resultList)

Upvotes: 2

Related Questions