Golang中的Postgresql动态查询构建

Question

How can I build a dynamic UPDATE query depending on the arguments that I have/get?

Need to update the column only if the argument value is not empty.

This is my code:

func main() {

    err := DBConnect() //DB connection function
    if err != nil {
        fmt.Println("Error", "DB Connect fail")
        return
    }

    tx, err := Db.Begin()
    if err != nil {
        fmt.Println("Error", err)
        fmt.Println("Error", "Unable to start DB")
        return
    }

    defer func() {
        if err != nil {
            tx.Rollback()
        } else {
            tx.Commit()
        }
    }()

    var arg []interface{}

    i := 1
    col1 := "value1"
    col2 := "value2"
    col3 := "" 
    col4 := ""
    col5 := "value5"

    query := "UPDATE schema1.products SET "

    if col1 != "" {
        query = query + "col1=$" + fmt.Sprintf("%d", i) + ", "
        arg = append(arg, col1)
        i++
    }
    if col2 != "" {
        query = query + "col2=$" + fmt.Sprintf("%d", i) + ", "
        arg = append(arg, col2)
        i++
    }
    if col3 != "" {
        query = query + "col3=$" + fmt.Sprintf("%d", i) + ", "
        arg = append(arg, col3)
        i++
    }
    if col4 != "" {
    query = query + "col4=$" + fmt.Sprintf("%d", i) + ", "
    arg = append(arg, col4)
    i++
    }

    query = query + "WHERE col5=$" + fmt.Sprintf("%d", i)
    arg = append(arg, col5)

    stmt, err := tx.Prepare(query)
    if err != nil {
        fmt.Println("Error", err)
        fmt.Println("Error", "Query prepare failed")
        return
    }

    res, err := stmt.Exec(arg...)
    if err != nil {
        fmt.Println("Error", err)
        fmt.Println("Error", "Query Exec failed")
        return
    }
    n, err := res.RowsAffected()
    if err != nil {
        fmt.Println("Error", err)
        fmt.Println("Error", "Unable to get rows affected")
        return
    }
    if n > 1 {
        fmt.Println("Error", err)
        fmt.Println("Error", "More than one row updated")
        return
    }
    return
}

So the basic requirement is to update the columns only if the appropriate Input values are not null.

UPDATE:

func GetRow(arg1, arg2 string) (data [][]string, err error) {
    row, err := Db.Query("SELECT * FROM schema1.products WHERE ($1='' OR col1=$1) AND ($2='' OR col2=$2)", arg1, arg2)
    if err != nil {
        fmt.Println("Error", err)
        fmt.Println("Error", "Unable to Query DB")
        return
    }
    defer sql.Close(row)
    _, data, err = sql.Scan(row)
    if err != nil {
        fmt.Println("Error", err)
        fmt.Println("Error", "Unable to scan rows")
        return
    }
    if len(data) < 0 {
        fmt.Println("Error", "No Data found")
        return
    }
    return
}

The query in the GetRow function will include the conditions in WHERE clause only if arguments are not empty. So I need not worry whether the arg1 and arg2 are empty or not. I am Looking for a way to do the same in case of UPDATE query as well.