go 插入复合类型数组不支持的类型

Question

sql

CREATE TABLE public.tiantang_page (

    href varchar NOT NULL,

    status int4 NOT NULL,

    description varchar NOT NULL,

    urls url[] NULL

);

CREATE TYPE url AS (

    url varchar,

    status int4);

插入复合类型数组

type url  struct {

    url string

    status int 

}

    var urls [1]url

    urls[0] = url{

        url:    "",

        status: 0,

    }

    update := "UPDATE \"public\".\"tiantang_page\" SET \"urls\"=$1 where \"href\"=$2;"

    r, err := db.Exec(update, pq.Array(urls),href)

    if err != nil {

        log.Fatal(err)

    }

错误

sql：转换参数 $1 类型：不支持的类型 parsetest.url，一个结构

图书馆

https://godoc.org/github.com/lib/pq

Answer 1

请注意，. 不完全支持自定义复合类型lib/pq。如果您想要的只是能够存储 url，那么最简单的方法是driver.Valuer在url类型上实现接口，然后像使用它一样使用它pq.Array：func (u url) Value() (driver.Value, error) {&nbsp; &nbsp; return fmt.Sprintf("(%s,%d)", u.url, u.status), nil}// ...r, err := db.Exec(update, pq.Array(urls), href)有关更多信息，请访问：https://github.com/lib/pq/issues/544请注意，我没有尝试过使用数组，仅使用切片，因此您可能必须从使用数组切换到使用切片，即而不是var urls [1]url使用var urls = make([]url, 1).如果您还希望能够从数据库检索 url 数组，那么您必须实现该sql.Scanner接口，但是这里pq.Array不太可靠，您必须在切片类型上实现扫描仪并执行全部自己解析。请注意，复合类型的一般格式是(val1, val2, ...)，必须在包含逗号或括号的值两边加上双引号。例如，要构造 url 类型的值，您可以使用文字表达式：(http://example.com,4)。文档中的更多信息。复合类型数组的格式为，请{"(val1, val2, ...)" [, ...]}注意，在这种情况下，如果您需要在需要转义它们的值两边加上双引号。例如{"(http://example.com,4)","(\"http://example.com/?list=foo,bar,baz\",3)"}正如您所看到的，复合类型中的数据越复杂，解析也就越复杂。这是一个粗略的示例（不处理带引号的值）：type urlslice []urlfunc (s *urlslice) Scan(src interface{}) error {&nbsp; &nbsp; var a []byte // the pq array as bytes&nbsp; &nbsp; switch v := src.(type) {&nbsp; &nbsp; case []byte:&nbsp; &nbsp; &nbsp; &nbsp; a = v&nbsp; &nbsp; case string:&nbsp; &nbsp; &nbsp; &nbsp; a = []byte(v)&nbsp; &nbsp; case nil:&nbsp; &nbsp; &nbsp; &nbsp; *s = nil&nbsp; &nbsp; &nbsp; &nbsp; return nil&nbsp; &nbsp; default:&nbsp; &nbsp; &nbsp; &nbsp; return fmt.Errorf("urlslice.Scan unexpected src type %T", src)&nbsp; &nbsp; }&nbsp; &nbsp; a = a[1 : len(a)-1] // drop curly braces&nbsp; &nbsp; for i := 0; i < len(a); i++ {&nbsp; &nbsp; &nbsp; &nbsp; if a[i] == '"' && (len(a) > (i+1) && a[i+1] == '(') { // element start?&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; i += 2 // move past `"(`&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; j := i // start of url.url&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; u := url{}&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for ; i < len(a) && a[i] != ','; i++ {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; u.url = string(a[j:i])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; i += 1 // move past `,`&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; j = i&nbsp; // start of url.status&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for ; i < len(a) && a[i] != ')'; i++ {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; i64, err := strconv.ParseInt(string(a[j:i]), 10, 64)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if err != nil {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return err&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; u.status = int(i64)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; *s = append(*s, u)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; i += 2 // move past `)",`&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; &nbsp; return nil}为了完整起见，这里是切片类型实现的 Valuer 接口，同样不处理可能需要它的值的正确引用：func (s urlslice) Value() (driver.Value, error) {&nbsp; &nbsp; data := []byte{'{'}&nbsp; &nbsp; for _, url := range s {&nbsp; &nbsp; &nbsp; &nbsp; data = append(data, '"', '(')&nbsp; &nbsp; &nbsp; &nbsp; data = append(data, []byte(url.url)...)&nbsp; &nbsp; &nbsp; &nbsp; data = append(data, ',')&nbsp; &nbsp; &nbsp; &nbsp; data = strconv.AppendInt(data, int64(url.status), 10)&nbsp; &nbsp; &nbsp; &nbsp; data = append(data, ')', '"', ',')&nbsp; &nbsp; }&nbsp; &nbsp; data[len(data)-1] = '}' // replace last ',' with '}' to close the array&nbsp; &nbsp; return data, nil}通过urlslice直接实现这两个接口，您可以停止使用pq.Array.var urls = urlslice{{&nbsp; &nbsp; url:&nbsp; &nbsp; "http://example.com",&nbsp; &nbsp; status: 4,}}update := `UPDATE "public"."tiantang_page" SET "urls"=$1 where "href"=$2`r, err := db.Exec(update, urls, href)if err != nil {&nbsp; &nbsp; log.Fatal(err)}var urls2 urlsliceselurls := `SELECT "urls" FROM "public"."tiantang_page" where "href" = $1`if err := db.QueryRow(selurls, href).Scan(&urls2); err != nil {&nbsp; &nbsp; &nbsp;log.Fatal(err)}请记住，上述两个示例仅应被视为解决此问题的方向的提示。这两个示例不仅不完整，因为它们不处理带引号的值，而且它们也不是非常优雅的实现。

Answer 2

相当完整的复合文字解析器：type parseState int&nbsp; &nbsp; &nbsp;const (&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; state_initial&nbsp; &nbsp; &nbsp;parseState = iota // start&nbsp; &nbsp; &nbsp; &nbsp; state_value_start&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;// no bytes read from value yet&nbsp; &nbsp; &nbsp; &nbsp; state_value&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;// unquoted value&nbsp; &nbsp; &nbsp; &nbsp; state_quoted&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // inside quote&nbsp; &nbsp; &nbsp; &nbsp; state_value_end&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;// after a close quote&nbsp; &nbsp; &nbsp; &nbsp; state_end&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;// after close paren)func parseComposite(in []byte) ([]string, error) {&nbsp; &nbsp; &nbsp; &nbsp; state := state_initial&nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; ret := []string{}&nbsp; &nbsp; &nbsp; &nbsp; val := []byte{}&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; for _, b := range in {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; switch state {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; case state_initial:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if b != '(' {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return nil, fmt.Errorf("initial character not ')': %v", in)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } else {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; state = state_value_start&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; case state_value_start:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if b == '"' {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; state = state_quoted&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; continue&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fallthrough&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; case state_value:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if b == ',' {&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ret = append(ret, string(val))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; val = nil&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; state = state_value_start&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } else if b == ')' {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ret = append(ret, string(val))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; val = nil&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; state = state_end&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } else {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; val = append(val, b)&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; case state_quoted:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if b == '"' {&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ret = append(ret, string(val))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; val = nil&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; state = state_value_end&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } else {&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; val = append(val, b)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; case state_value_end:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if b == ',' {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; state = state_value_start&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } else if b == ')' {&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; state = state_end&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } else {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return nil, fmt.Errorf("invalid delimiter after closing quote: %v", in)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; case state_end:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return nil, fmt.Errorf("trailing bytes: %v", in)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; if state != state_end {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return nil, fmt.Errorf("unterminated value: %v", in)&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; return ret, nil&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;}&nbsp; &nbsp;&nbsp;