postgresql - golang reflect for sql scan报错

Question

/**
-- name is character varying(64)
CREATE TABLE users
(
  id bigint NOT NULL,
  no character(24),
  identity name,
  name name,
  head character varying(256),
  email character varying(256),
  site character varying(256),
  create_time timestamp without time zone,
  city_id bigint,
  timezone jsonb,
  update_time timestamp without time zone,
  CONSTRAINT user__pk PRIMARY KEY (id)
)

spottly=# SELECT "id", "no", "identity", "name", "head", "email", "site", "create_time", "city_id", "update_time", "timezone" FROM "users" WHERE "email" = 'fixopen@yeah.net' LIMIT 1 OFFSET 0;
       id       | no | identity |  name   |                  head                  |      email       |            site            | create_time | city_id | update_time | timezone
----------------+----+----------+---------+----------------------------------------+------------------+----------------------------+-------------+---------+-------------+----------
 95083655397376 |    | fixopen  | fixopen | /uploader/52e2762edf4e633666000867.png | fixopen@yeah.net | http://spottly.com/fixopen |             |         |             |
(1 row)
*/

package main

import (
    "database/sql"
    //"errors"
    "fmt"
    _ "github.com/lib/pq"
    "log"
    "net/url"
    "reflect"
    "strings"
    "time"
)

type User struct {
    Id          uint64    `json:"id"`
    No          *string   `json:"no"`
    Identity    string    `json:"identity"`
    Name        string    `json:"name"`
    Head        url.URL   `json:"head"`
    Email       *string   `json:"email"`
    Site        url.URL   `json:"site"`
    Create_time time.Time `json:"create-time"`
    City_id     *uint64   `json:"city-id"`
    Update_time time.Time `json:"update-time"`
    Timezone    *string   `json:"timezone"`
}

func main() {
    connectionString := "host=localhost port=5432 user=postgres dbname=spottly password=123456 sslmode=disable"
    db, err := sql.Open("postgres", connectionString)
    defer db.Close()
    if err != nil {
        log.Fatal(err)
    }

    t := reflect.TypeOf(User{})
    //u := reflect.New(t).Elem().Interface()
    //fmt.Printf("u is %T, %#v\n", u, u)

    resultValuePtr := reflect.New(t)
    resultValue := resultValuePtr.Elem()
    fieldCount := t.NumField()
    fields := make([]reflect.StructField, fieldCount)
    for i := 0; i < fieldCount; i++ {
        fields[i] = t.Field(i)
    }
    columns := make([]string, fieldCount)
    fieldAddrs := make([]interface{}, fieldCount)
    for i := 0; i < fieldCount; i++ {
        columns[i] = strings.ToLower(fields[i].Name)
        fieldAddrs[i] = resultValue.Field(i).Addr().Interface()
    }
    columnNames := strings.Join(columns, "\", \"")
    command := "SELECT \"" + columnNames + "\" FROM \"users\" WHERE \"email\" = 'fixopen@yeah.net' LIMIT 1 OFFSET 0"
    fmt.Printf("query command is %v\n", command)
    // fmt.Printf("meta field is %v\n", fields)
    // fmt.Printf("field addr is %#v\n", fieldAddrs)
    err = db.QueryRow(command).Scan(fieldAddrs...)
    if err == nil {
        result := resultValuePtr.Interface()
        fmt.Printf("result is %#v\n", result)
    } else {
        fmt.Printf("Select one error is %v\n", err)
        //panic(errors.New("halt"))
    }
}

数据库架构和数据显示在评论中。

查询命令为:

SELECT "id", "no", "identity", "name", "head", "email", "site", "create_time", "city_id", "update_time", "timezone"FROM "users"WHERE "email"= 'fixopen@yeah.net' LIMIT 1 OFFSET 0

与注释相同，此命令执行psql返回一行数据。

sql Scan方法执行错误，输出:

选择一个错误是 sql: Scan error on column index 4: unsupported driver -> Scan pair: []uint8 -> *url.URL

我的golang版本是go version go1.5.2 darwin/amd64

PostgreSQL 版本为 9.4.2

使用 github.com/lib/pq 访问数据库。

是数据库驱动不支持扫描字符串列到url.URL吗？

我能做什么？

Answer 1

url.URL 没有实现 sql.Scanner , 所以它不能从数据库中扫描。您可以创建一个实现 sql.Scanner 的包装器类型:

type sqlURL struct { url.URL }
func (su *sqlURL) Scan(v interface{}) error {
    u, err := url.Parse(string(v.([]byte)))
    if err != nil {
        return err
    }
    *su = sqlURL{*u}
    return nil
}

或者将URL单独扫描成字符串再解析:

var urlStr string
err = db.QueryRow(command).Scan(/* ... */, &urlStr)
// check err
u, err := url.Parse(urlStr)
// check err
user.Site = *u