将数组传递给go-pg查询

I'm using Go-pg and when I use the following way to execute sql query:

db.Query(&result, sqlQuery, params)

where params is a structure like the following:

type MyParams struct {
    Codes []int
}

and sqlQuery is

SELECT id FROM bar WHERE code_id IN (?codes)

in the actual SQL query I get query like this:

SELECT id FROM bar WHERE code_id IN ('[7,45]')

Is it possible to have int array placeholder passed properly to have a query:

SELECT id FROM bar WHERE code_id IN (7,45)

There are a couple things you can do:

1. Keep using in (...) and use pg.In.
2. Use = any(array) in your SQL instead of in (list) and pg.Array in Go to send a proper array to PostgreSQL.

The first looks like:

db.Query(&result, `SELECT id FROM bar WHERE code_id IN (?)`, pg.In(params.Codes))

the second looks like:

db.Query(&result, `SELECT id FROM bar WHERE code_id = ANY (?)`, pg.Array(params.Codes))

A way is to create a string with delimiter required to make an string and then pass it to the db.query to fetch the result.

package main

import (
    "fmt"
    "strconv"
    "strings"
)

func main() {
    a := []int{7,45,32}
    str := ConvertToString(a, ",")
    query := `Select * from table1 where ID IN(`+ str +`)`
    fmt.Println(query)
}

func ConvertToString(a []int, delim string) string{
    var b string
    for _, v := range a{
        b += strconv.Itoa(v)
        b += ","
    }
    return strings.Trim(b,",")
}

Working code on Playground.

Edited:-

You can use Golang sqlx package for your requirement.

database/sql package does not inspect your query and it passes your arguments directly to the driver, it makes dealing with queries with IN clauses difficult:

SELECT * FROM users WHERE level IN (?);

When this gets prepared as a statement on the backend, the bindvar ? will only correspond to a single argument, but what is often desired is for that to be a variable number of arguments depending on the length of some slice, eg:

var levels = []int{4, 6, 7}
rows, err := db.Query("SELECT * FROM users WHERE level IN (?);", levels)

You can try to do something like this:

// Slice with your integer IDs as strings.
ids := []string{}

// Convert integer ID to string ID.
for _, i := range []int{7, 45} {
    ids = append(ids, strconv.Itoa(i))
}

Now you can use Join func, and final query will look pretty simple, like this:

in := strings.Join(ids, ",")
query := "SELECT id FROM bar WHERE code_id IN (" + in + ")"