我对带有变量的Go sql查询不了解什么？

Question

I'm brand new to Go, and I've started working on some postgres queries, and I'm having very little luck.

I have a package that's just going to have some database queries in it. Here's my code.

main.go

package main

import (
    "fmt"
)

func main() {

    fmt.Println("Querying data")
    myqueries.SelectAll("mytable")

}

myqueries.go

package myqueries

import (
    "database/sql"
    "fmt"
)

func SelectAll (table string) {
    db, err := sql.Open("postgres","user=postgres dbname=mydb sslmode=disable")

        if err != nil {
                 fmt.Println(err)
        }

        defer db.Close()

        rows, err := db.Query("SELECT * FROM $1", table)

        if err != nil {
                fmt.Println(err)
        } else {

                PrintRows(rows)
        }

}

func PrintRows(rows *sql.Rows) {
    for rows.Next() {
        var firstname string
        var lastname string

        err := rows.Scan(&firstname, &lastname)

        if err != nil {
            fmt.Println(err)
        }
        fmt.Println("first name | last name")

        fmt.Println("%v | %v
", firstname, lastname)

    }
}

The error I get is pq: syntax error at or near "$1"

which is from myqueries.go file in the db.Query.

I've tried several variations of this, but nothing has worked yet. Any help is appreciated.

Answer 1

It looks like you are using https://github.com/lib/pq based on the error message and it's docs say that

pq uses the Postgres-native ordinal markers, as shown above

I've never known a database engine that allows the parameterized values in anything other than values. I think you are going to have to resort to string concatenation. I don't have a Go compiler available to me right now, but try something like this. Because you are inserting the table name by concatination, you need it sanitized. pq.QuoteIdentifier should be able to help with that.

func SelectAll (table string) {
    db, err := sql.Open("postgres","user=postgres dbname=mydb sslmode=disable")

        if err != nil {
                 fmt.Println(err)
        }

        defer db.Close()

        table = pq.QuoteIdentifier(table)
        rows, err := db.Query(fmt.Sprintf("SELECT * FROM %v", table))

        if err != nil {
                fmt.Println(err)
        } else {

                PrintRows(rows)
        }

}

EDIT: Thanks to hobbs to pointing out pq.QuoteIdentifier