Golang反映SQL扫描报告错误

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" = '[e-mail]' LIMIT 1 OFFSET 0;
       id       | no | identity |  name   |                  head                  |      email       |            site            | create_time | city_id | update_time | timezone
----------------+----+----------+---------+----------------------------------------+------------------+----------------------------+-------------+---------+-------------+----------
 95083655397376 |    | fixopen  | fixopen | /uploader/52e2762edf4e633666000867.png | [e-mail] | 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
", 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\" = '[e-mail]' LIMIT 1 OFFSET 0"
    fmt.Printf("query command is %v
", command)
    // fmt.Printf("meta field is %v
", fields)
    // fmt.Printf("field addr is %#v
", fieldAddrs)
    err = db.QueryRow(command).Scan(fieldAddrs...)
    if err == nil {
        result := resultValuePtr.Interface()
        fmt.Printf("result is %#v
", result)
    } else {
        fmt.Printf("Select one error is %v
", err)
        //panic(errors.New("halt"))
    }
}

database schema and data show in comment.

the query command is:

SELECT "id", "no", "identity", "name", "head", "email", "site", "create_time", "city_id", "update_time", "timezone" FROM "users" WHERE "email" = '[e-mail]' LIMIT 1 OFFSET 0

same as comment, this command execute with psql return one row data.

sql Scan method execute error, output:

Select one error is sql: Scan error on column index 4: unsupported driver -> Scan pair: []uint8 -> *url.URL

My golang version is go version go1.5.2 darwin/amd64

PostgreSQL version is 9.4.2

use github.com/lib/pq access DB.

It's the db driver not support scan string column to url.URL?

what do I can?

Answer 1

url.URL doesn't implement sql.Scanner, so it cannot be scanned from DB. You can either make a wrapper type that does implement 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
}

or scan the URL into a string separately and then parse it:

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