CODEWITHSUNDEEP
postgresqlgo
王奕然
王奕然

Reputation: 4049

go insert composite type array unsupported type

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);

insert composite type array

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)
    }

error

sql: converting argument $1 type: unsupported type parsetest.url, a struct

library

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

Upvotes: 6

Views: 5136

Answers (2)

flamingcow
flamingcow

Reputation: 341

Reasonably complete composite literal parser:

type parseState int     

const ( 
        state_initial     parseState = iota // start
        state_value_start                   // no bytes read from value yet
        state_value                         // unquoted value
        state_quoted                        // inside quote
        state_value_end                     // after a close quote
        state_end                           // after close paren
)

func parseComposite(in []byte) ([]string, error) {
        state := state_initial   
        ret := []string{}
        val := []byte{}      

        for _, b := range in {
                switch state {                       

                case state_initial:               
                        if b != '(' {
                                return nil, fmt.Errorf("initial character not ')': %v", in)
                        } else {
                                state = state_value_start
                        }

                case state_value_start:
                        if b == '"' {
                                state = state_quoted       
                                continue
                        } 
                        fallthrough

                case state_value:
                        if b == ',' {       
                                ret = append(ret, string(val))
                                val = nil
                                state = state_value_start    
                        } else if b == ')' {
                                ret = append(ret, string(val))
                                val = nil
                                state = state_end       
                        } else {                                                                                                                      
                                val = append(val, b) 
                        }

                case state_quoted:
                        if b == '"' {      
                                ret = append(ret, string(val))
                                val = nil
                                state = state_value_end
                        } else {  
                                val = append(val, b)          
                        }   

                case state_value_end:
                        if b == ',' {
                                state = state_value_start
                        } else if b == ')' {    
                                state = state_end
                        } else {          
                                return nil, fmt.Errorf("invalid delimiter after closing quote: %v", in)
                        }

                case state_end:
                        return nil, fmt.Errorf("trailing bytes: %v", in)
                }        
        }                                             

        if state != state_end {                
                return nil, fmt.Errorf("unterminated value: %v", in)
        }                                   

        return ret, nil                
}

Upvotes: 0

mkopriva
mkopriva

Reputation: 38203

Note that custom composite types are not fully supported by lib/pq.

If all you want is to be able to store the urls then the simplest approach would be to implement the driver.Valuer interface on the url type and then use it as you do with pq.Array:

func (u url) Value() (driver.Value, error) {
    return fmt.Sprintf("(%s,%d)", u.url, u.status), nil
}

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

more info on that can be found here: https://github.com/lib/pq/issues/544

Note that I haven't tried this with arrays, only with slices, so you may have to switch from using an array to using a slice, i.e. instead of var urls [1]url you would use var urls = make([]url, 1).

If you also want to be able to retrieve the array of urls back from the db, then you'll have to implement the sql.Scanner interface, however here the pq.Array is not very reliable and you'll have to implement the scanner on the slice type and do all the parsing yourself.

The general format of composite types is (val1, val2, ...) note that you have to put double quotes around values that contain commas or parentheses. For example to construct a value of the url type you would use the literal expression: (http://example.com,4). More info in the docs.

The format for an array of composite types is {"(val1, val2, ...)" [, ...]}, note that in this case if you need to put double quotes around the values you need to escape them. For example {"(http://example.com,4)","(\"http://example.com/?list=foo,bar,baz\",3)"}

So as you can see the more complex the data in the composite type the more complex will be the parsing as well.

Here's a crude example (does not handle quoted values):

type urlslice []url

func (s *urlslice) Scan(src interface{}) error {
    var a []byte // the pq array as bytes
    switch v := src.(type) {
    case []byte:
        a = v
    case string:
        a = []byte(v)
    case nil:
        *s = nil
        return nil
    default:
        return fmt.Errorf("urlslice.Scan unexpected src type %T", src)
    }

    a = a[1 : len(a)-1] // drop curly braces
    for i := 0; i < len(a); i++ {
        if a[i] == '"' && (len(a) > (i+1) && a[i+1] == '(') { // element start?
            i += 2 // move past `"(`
            j := i // start of url.url
            u := url{}

            for ; i < len(a) && a[i] != ','; i++ {
            }
            u.url = string(a[j:i])

            i += 1 // move past `,`
            j = i  // start of url.status
            for ; i < len(a) && a[i] != ')'; i++ {
            }
            i64, err := strconv.ParseInt(string(a[j:i]), 10, 64)
            if err != nil {
                return err
            }
            u.status = int(i64)
            *s = append(*s, u)

            i += 2 // move past `)",`
        }
    }
    return nil
}

for completeness, here's the Valuer interface implemented by the slice type, again not handling proper quoting of values that may require it:

func (s urlslice) Value() (driver.Value, error) {
    data := []byte{'{'}
    for _, url := range s {
        data = append(data, '"', '(')
        data = append(data, []byte(url.url)...)
        data = append(data, ',')
        data = strconv.AppendInt(data, int64(url.status), 10)
        data = append(data, ')', '"', ',')
    }
    data[len(data)-1] = '}' // replace last ',' with '}' to close the array
    return data, nil
}

With the urlslice implementing the two interfaces directly you can stop using pq.Array.

var urls = urlslice{{
    url:    "http://example.com",
    status: 4,
}}
update := `UPDATE "public"."tiantang_page" SET "urls"=$1 where "href"=$2`
r, err := db.Exec(update, urls, href)
if err != nil {
    log.Fatal(err)
}

var urls2 urlslice
selurls := `SELECT "urls" FROM "public"."tiantang_page" where "href" = $1`
if err := db.QueryRow(selurls, href).Scan(&urls2); err != nil {
     log.Fatal(err)
}

Please keep in mind that both of the above examples should be considered only as hints of the direction to take in solving this problem. Not only are the two examples incomplete in that they don't handle quoted values, but they are also not very elegant implementations.

Upvotes: 4

Related Questions