英文:
Read a Postgresql array directly into a Golang Slice
问题
我有一个查询,返回一个包含字符串数组(character varying[]
)的单列行:
{http://wp.me/p62MJv-Jc,http://tyrant.click/1LGBoD6}
有没有一种简单的方法可以直接将其读入到 Golang 的切片中?例如:
var arr []string
for rows.Next() {
rows.Scan(&arr)
fmt.Println(len(arr))
}
输出结果为:
0
英文:
I have a query which returns a row with a single column containing an array of strings (character varying[]
):
{http://wp.me/p62MJv-Jc,http://tyrant.click/1LGBoD6}
Is there any easy way to read this directly into a Golang slice? E.g.
var arr []string
for rows.Next() {
rows.Scan(&arr)
fmt.Println(len(arr))
}
Produces:
0
答案1
得分: 17
我认为这应该可以完成任务。在 SQL 中使用 array_to_json,然后将 JSON 字符串解组为 Golang 切片。
sql-> select array_to_json(arr) from ....
var arrStr string
var arr []string
for rows.Next() {
rows.Scan(&arrStr)
json.Unmarshal([]byte(arrStr), &arr)
fmt.Println(len(arr))
}
请注意,这是一个示例代码片段,用于说明如何使用 array_to_json
和 json.Unmarshal
来实现你的目标。你可能需要根据实际情况进行适当的修改。
英文:
I think this should do the job. Using array_to_json in sql. Then unmarshalling the json-string to golang slice
sql-> select array_to_json(arr) from ....
var arrStr string
var arr []string
for rows.Next() {
rows.Scan(&arrStr)
json.Unmarshal([]byte(arrStr), &arr)
fmt.Println(len(arr))
}
答案2
得分: 16
如Victor在原始帖子的评论中提到的那样,这篇文章通过解释pq.Array()
很好地回答了这个问题。
直接从链接中摘录的内容:
要将Postgres数组值读入Go切片中,请使用:
func getTags(db *sql.DB, title string) (tags []string) { // select查询,返回一个数组类型的列 sel := "SELECT tags FROM posts WHERE title=$1" // 使用pq.Array将输出参数包装起来以接收它 if err := db.QueryRow(sel, title).Scan(pq.Array(&tags)); err != nil { log.Fatal(err) } return }
我在自己的项目中刚刚使用了这个方法,可以确认它是有效的。
英文:
As mentioned by Victor in the comments on the original post, this post answers the question well with its explanation of pq.Array()
.
Taken directly from the link:
> To read a Postgres array value into a Go slice, use:
>
> func getTags(db *sql.DB, title string) (tags []string) {
> // the select query, returning 1 column of array type
> sel := "SELECT tags FROM posts WHERE title=$1"
>
> // wrap the output parameter in pq.Array for receiving into it
> if err := db.QueryRow(sel, title).Scan(pq.Array(&tags)); err != nil {
> log.Fatal(err)
> }
>
> return
> }
I've just got this working in a project of my own as well, so can confirm it works.
答案3
得分: 2
目前,使用lib/pq
库没有直接的方法将PostgreSQL数组加载到Go切片中。也许在某个时候会有这样的方法,但是关于是否应该由库本身透明地处理这个问题存在一些争议。
然而,一种选择是将结果加载到一个字符串中(看起来像{item1,item2,"comma,item"}
),然后使用正则表达式将该字符串拆分为字符串切片,如下面的代码所示(部分代码来自Andrew Harris的this Gist):
import (
"regexp"
"strings"
)
var (
// 未引用的数组值不能包含以下字符:(" , \ { } 空格 NULL)
// 并且必须至少有一个字符
unquotedChar = `[^",\\{}\s(NULL)]`
unquotedValue = fmt.Sprintf("(%s)+", unquotedChar)
// 引用的数组值由双引号括起来,可以是任何字符,除了"或\,必须进行反斜杠转义:
quotedChar = `[^"\\]|\\"|\\\\`
quotedValue = fmt.Sprintf("\"(%s)*\"", quotedChar)
// 数组值可以是引用的或未引用的:
arrayValue = fmt.Sprintf("(?P<value>(%s|%s))", unquotedValue, quotedValue)
// 如果有多个值,则数组值用逗号分隔:
arrayExp = regexp.MustCompile(fmt.Sprintf("((%s)(,)?)", arrayValue))
)
// 解析数组类型的输出字符串。
// 使用的正则表达式:(((?P<value>(([^",\\{}\s(NULL)])+|"([^"\\]|\\")*")))(,)?)
func pgArrayToSlice(array string) []string {
var valueIndex int
results := make([]string, 0)
matches := arrayExp.FindAllStringSubmatch(array, -1)
for _, match := range matches {
s := match[valueIndex]
// 字符串可能被引号包围,因此去除它们:
s = strings.Trim(s, "\"")
results = append(results, s)
}
return results
}
以下是如何使用它的示例:
rows, err := db.Query("SELECT link FROM links")
if err != nil {
panic(err)
}
var tmp string
for rows.Next() {
rows.Scan(&tmp)
links := pgArrayToSlice(tmp)
fmt.Println(len(links), links)
}
数据库中的数据如下:
# \d links
表 "public.links"
Column | Type | Modifiers
--------+--------+-----------
link | text[] |
# select * from links;
link
------------------------------
{this,that}
{another,thing}
{}
{"test,123","one,two,three"}
(5 rows)
上述Go代码的输出如下:
2 []string{"this,", "that"}
2 []string{"another,", "thing"}
2 []string{"another,", "thing"}
0 []string{}
2 []string{"test,123\",", "one,two,three"}
英文:
At the moment, there is no direct way to load a PostgreSQL array into a Go slice using the lib/pq
library. It may be at some point, but there has been some debate over whether such a thing should be handled transparently by the library itself.
However, one option is to load the result into a string (that looks like {item1,item2,"comma,item"}
), and then split that string into a string slice using a regular expression, as done in the code below (taken in part from this Gist by Andrew Harris):
import (
"regexp"
"strings"
)
var (
// unquoted array values must not contain: (" , \ { } whitespace NULL)
// and must be at least one char
unquotedChar = `[^",\\{}\s(NULL)]`
unquotedValue = fmt.Sprintf("(%s)+", unquotedChar)
// quoted array values are surrounded by double quotes, can be any
// character except " or \, which must be backslash escaped:
quotedChar = `[^"\\]|\\"|\\\\`
quotedValue = fmt.Sprintf("\"(%s)*\"", quotedChar)
// an array value may be either quoted or unquoted:
arrayValue = fmt.Sprintf("(?P<value>(%s|%s))", unquotedValue, quotedValue)
// Array values are separated with a comma IF there is more than one value:
arrayExp = regexp.MustCompile(fmt.Sprintf("((%s)(,)?)", arrayValue))
)
// Parse the output string from the array type.
// Regex used: (((?P<value>(([^",\\{}\s(NULL)])+|"([^"\\]|\\"|\\\\)*")))(,)?)
func pgArrayToSlice(array string) []string {
var valueIndex int
results := make([]string, 0)
matches := arrayExp.FindAllStringSubmatch(array, -1)
for _, match := range matches {
s := match[valueIndex]
// the string _might_ be wrapped in quotes, so trim them:
s = strings.Trim(s, "\"")
results = append(results, s)
}
return results
}
Here is how it might be used:
rows, err := db.Query("SELECT link FROM links")
if err != nil {
panic(err)
}
var tmp string
for rows.Next() {
rows.Scan(&tmp)
links := pgArrayToSlice(tmp)
fmt.Println(len(links), links)
}
With the following in the database:
# \d links
Table "public.links"
Column | Type | Modifiers
--------+--------+-----------
link | text[] |
# select * from links;
link
------------------------------
{this,that}
{another,thing}
{}
{"test,123","one,two,three"}
(5 rows)
This is what is output by the Go code above:
2 []string{"this,", "that"}
2 []string{"another,", "thing"}
2 []string{"another,", "thing"}
0 []string{}
2 []string{"test,123\",", "one,two,three"}
答案4
得分: 2
我看到这段代码的变体在各个地方都有,但对于某些测试集来说,它对我来说不起作用。
这是我写的一段代码,它可以处理我提供的所有测试值(测试用例如下)。而且速度也快了80%。
func ParsePGArray(array string) ([]string, error) {
var out []string
var arrayOpened, quoteOpened, escapeOpened bool
item := &bytes.Buffer{}
for _, r := range array {
switch {
case !arrayOpened:
if r != '{' {
return nil, errors.New("Doesn't appear to be a postgres array. Doesn't start with an opening curly brace.")
}
arrayOpened = true
case escapeOpened:
item.WriteRune(r)
escapeOpened = false
case quoteOpened:
switch r {
case '\\':
escapeOpened = true
case '"':
quoteOpened = false
if item.String() == "NULL" {
item.Reset()
}
default:
item.WriteRune(r)
}
case r == '}':
// done
out = append(out, item.String())
return out, nil
case r == '"':
quoteOpened = true
case r == ',':
// end of item
out = append(out, item.String())
item.Reset()
default:
item.WriteRune(r)
}
}
return nil, errors.New("Doesn't appear to be a postgres array. Premature end of string.")
}
以下是测试用例:
scanTests := []struct {
in string
out []string
}{
{"{one,two}", []string{"one", "two"}},
{`{"one, sdf",two}`, []string{"one, sdf", "two"}},
{`{"\"one\""`,two}`, []string{`"one"`, "two"}},
{`{"\\one\\",two}`, []string{`\one\`, "two"}},
{`{"{one}",two}`, []string{`{one}`, "two"}},
{`{"one two"}`, []string{"one two"}},
{`{"one,two"}`, []string{"one,two"}},
{`{abcdef:83bf98cc-fec9-4e77-b4cf-99f9fb6655fa-0NH:zxcvzxc:wers:vxdfw-asdf-asdf}`, []string{"abcdef:83bf98cc-fec9-4e77-b4cf-99f9fb6655fa-0NH:zxcvzxc:wers:vxdfw-asdf-asdf"}},
{`{"",two}`, []string{"","two"}},
{`{" ","NULL"}`, []string{" ",""}},
}
英文:
I've seen variations of this code all over the place, but it doesn't work for me for certain test sets.
Here something I wrote which handles all the test values I've thrown at it (test cases follow). It's also about 80% faster.
func ParsePGArray(array string) ([]string, error) {
var out []string
var arrayOpened,quoteOpened,escapeOpened bool
item := &bytes.Buffer{}
for _, r := range array {
switch {
case !arrayOpened:
if r != '{' {
return nil, errors.New("Doesn't appear to be a postgres array. Doesn't start with an opening curly brace.")
}
arrayOpened = true
case escapeOpened:
item.WriteRune(r)
escapeOpened = false
case quoteOpened:
switch r {
case '\\':
escapeOpened = true
case '"':
quoteOpened = false
if item.String() == "NULL" {
item.Reset()
}
default:
item.WriteRune(r)
}
case r == '}':
// done
out = append(out, item.String())
return out, nil
case r == '"':
quoteOpened = true
case r == ',':
// end of item
out = append(out, item.String())
item.Reset()
default:
item.WriteRune(r)
}
}
return nil, errors.New("Doesn't appear to be a postgres array. Premature end of string.")
}
Here are the tests cases:
scanTests := []struct {
in string
out []string
}{
{"{one,two}", []string{"one", "two"}},
{`{"one, sdf",two}`, []string{"one, sdf", "two"}},
{`{"\"one\"",two}`, []string{`"one"`, "two"}},
{`{"\\one\\",two}`, []string{`\one\`, "two"}},
{`{"{one}",two}`, []string{`{one}`, "two"}},
{`{"one two"}`, []string{`one two`}},
{`{"one,two"}`, []string{`one,two`}},
{`{abcdef:83bf98cc-fec9-4e77-b4cf-99f9fb6655fa-0NH:zxcvzxc:wers:vxdfw-asdf-asdf}`, []string{"abcdef:83bf98cc-fec9-4e77-b4cf-99f9fb6655fa-0NH:zxcvzxc:wers:vxdfw-asdf-asdf"}},
{`{"",two}`, []string{"","two"}},
{`{" ","NULL"}`, []string{" ",""}},
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论