2

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.

Improve this question
2
  • Yes, there's a couple of ways to do so, but I have to ask: WHY? This, to me, sounds like an X-Y problem. What are you trying to do, that has you end up writing code that queries the DB, without knowing how many fields, or even what fields you're selecting, and what are you trying to do with the data afterwards? Commented May 11, 2020 at 11:36
  • Actually I am having a table whose columns change dynamically, Like the columns are not fixed nor do I know what are those columns(it's in client DB). My requirement is to fetch those values from there and view in UI. So as the columns are not fixed so I am confused about how to deal with this. I hope you get it. And may I know whats are the ways you have. Commented May 11, 2020 at 13:28

2 Answers 2

Reset to default
2

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thanks, I was able to retrieve all the records from the Postgres in map[string]interface{}. But now I have a query how to update the existing columns. For example I have a table foobar, columns are c1, c2, c3, c4 and I have got a request to update the values of c2 and c3. through the code I am able to fetch the column names, now how to update them? Like how will I match my request to the fetched column names? Hope you get it..
@ArchanaMoharana right, that kind of slipped my mind. If the column names are required, which in most cases they probably are, map[string]interface{} is what you should use to hold the values.
@ArchanaMoharana do you want to update the specific column in every row? Or do you have a primary key value that you can use to identify the row to be updated? Either way, you'll have to build the UPDATE sql first and then execute it passing in the values in the same order as the target columns of the update command.
It might be for few rows, or for all the rows or a single row the updation. No, currently I do not have a primary key value, could you suggest me what to do in this case? or is it necessary to have a primary key there?
@ArchanaMoharana If you're updating all rows in a table then it's easy, you just generate the UPDATE sql with the list of target columns and the list of value placeholders starting from 1 and you do not need a WHERE clause. If you need to update only specific rows, one or more but not all, then you'll need to also generate the WHERE clause with value placeholders in the conditionals. It's not a simple task but it's doable. Having a primary key in a table is recommended, if the table doesn't have one and you do not have control over the database then that's a pity!
|
1

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 ....

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.