database/sql: allow drivers to support additional Value types

[theory]

It'd be useful if drivers could augment the list of data types supported by database/sql/convert.go. The ability to implement driver.Valuer and sql.Scanner for one's own structs is great, but there are core types I'd like to be able to use, as well. For example, PostgreSQL supports array types. I'd love to be able to convert between slices and SQL arrays. It makes sense that database/sql would not support this, but if there was an interface for the pg or pgx drivers to add it for core types, it would be super useful. Perhaps Driver could have methods for this kind of conversion? Maybe something like:

if scanned, ok := driver.Scan(src); ok {
    return scanned
}

if val, ok := driver.Value(src); ok {
    return val
}

Then the drivers could be updated to, say, convert between []string and TEXT[].

[rsc]

/cc @bradfitz

[theory]

I'm thinking maybe create a new interface that drivers can implement. If they do, use it, if not, don't. Should ensure compatibility.

[kardianos]

Databases can support non-standard result types. Common ones that are found include arrays, JSON, XML, table valued types, IP addresses, and GIS types.

For scanning custom result types I propose the following changes are made:

package driver

type ScanAssigner interface {
    Rows
    Assign(dest, src interface{}) (ok bool, err error)
}

package sql

// Existing struct
type Rows struct {
    // New field, assigned when Rows is created.
    assigner driver.ScanAssigner
}

// modify existing function, pass new assigner from Rows into convertAssign.
func convertAssign(assigner driver.ScanAssigner, dest, src interface{}) error {
    ok, err := assigner.Assign(dest, src) // Before anything existing.
    if err != nil {
        return err
    }
    if ok {
        return nil
    }
    // Existing function
}

I think the above should allow returning any database supported type. Using any type as input parameters will be another issue.

[derekperkins]

This would also allow drivers to truly support uint64 values. Discussion about that was shut down in #9373, but the MySQL implementation requires a hack that returns a uint64 value as a string. I'm really hoping that this makes it into Go 1.9.

[gopherbot]

CL https://golang.org/cl/38533 mentions this issue.