Sqlx: NamedExec batch INSERT does not work with ON CONFLICT

[bretep]

Recently commited feature #285 does not support ON CONFLICT.

example:

sots := []*Sot{&sot1, &sot2}
db.NamedExec("INSERT INTO sot (sot_identifier, name)
            VALUES (:sot_identifier, :name)
            ON CONFLICT (sot_identifier) DO
                UPDATE SET (name) = (
                EXCLUDED.name)", sots)

Here is the query generated by NamedExec:

INSERT INTO sot (sot_identifier, name)
            VALUES ($1, $2)
            ON CONFLICT (sot_identifier) DO
                UPDATE SET (name) = (
                EXCLUDED.name),(
                EXCLUDED.name)

[bretep]

@hmgle is this something you could easily fix?

[bretep]

Looks like sqlx.Named also has the same issue.

[hmgle]

This bug may be caused by the fixBound function. I will check it later.

[hmgle]

This line EndBracketsReg = regexp.MustCompile(\([^()]*\)\s*$) assumes that the value_list part is at the end of bound. We cat fix the regexp like this:

var valueBracketReg = regexp.MustCompile(`\([^(]*\?+[^)]*\)`)

func fixBound(bound string, loop int) string {
    loc := valueBracketReg.FindStringIndex(bound)
    if len(loc) != 2 {
        return bound
    }

    var buffer bytes.Buffer

    buffer.WriteString(bound[0:loc[1]])
    for i := 0; i < loop-1; i++ {
        buffer.WriteString(",")
        buffer.WriteString(bound[loc[0]:loc[1]])
    }
    buffer.WriteString(bound[loc[1]:])
    return buffer.String()
}

The patch:

-var (
-   EndBracketsReg = regexp.MustCompile(`\([^()]*\)\s*$`)
-)
+var valueBracketReg = regexp.MustCompile(`\([^(]*\?+[^)]*\)`)

 func fixBound(bound string, loop int) string {
-   endBrackets := EndBracketsReg.FindString(bound)
-   if endBrackets == "" {
+   loc := valueBracketReg.FindStringIndex(bound)
+   if len(loc) != 2 {
        return bound
    }
+
    var buffer bytes.Buffer
-   buffer.WriteString(bound)
+
+   buffer.WriteString(bound[0:loc[1]])
    for i := 0; i < loop-1; i++ {
        buffer.WriteString(",")
-       buffer.WriteString(endBrackets)
+       buffer.WriteString(bound[loc[0]:loc[1]])
    }
+   buffer.WriteString(bound[loc[1]:])
    return buffer.String()
 }

[smartfly]

good

[asadhu]

@bretep we are facing a similar issue using ON CONFLICT, which release would have this fix?