将 Postgresql 数组直接读入 Golang Slice
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30489163/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Read a Postgresql array directly into a Golang Slice
提问by p_mcp
I have a query which returns a row with a single column containing an array of strings (character varying[]):
我有一个查询，它返回一行，其中一行包含一个字符串数组 ( 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.
有没有什么简单的方法可以将它直接读入 Golang 切片？例如
var arr []string
for rows.Next() {
rows.Scan(&arr)
fmt.Println(len(arr))
}
Produces:
产生：
0
采纳答案by lukeic
As mentioned by Victor in the comments on the original post, thispost answers the question well with its explanation of pq.Array().
正如维克多在原帖的评论中提到的，这篇文章很好地回答了这个问题，对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=" // 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 }
要将 Postgres 数组值读入 Go 切片，请使用：
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=" // 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.
我也刚刚在我自己的项目中进行了这项工作，因此可以确认它有效。
回答by Prata
I think this should do the job. Using array_to_jsonin sql. Then unmarshallingthe json-string to golang slice
我认为这应该可以完成工作。在 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))
}
回答by Herman Schaaf
At the moment, there is no direct way to load a PostgreSQL array into a Go slice using the lib/pqlibrary. 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.
目前，没有使用该lib/pq库将 PostgreSQL 数组加载到 Go 切片中的直接方法。可能会在某个时候出现，但是对于此类事情是否应该由图书馆本身透明地处理存在一些争论。
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 Gistby Andrew Harris):
但是，一种选择是将结果加载到一个字符串中（看起来像{item1,item2,"comma,item"}），然后使用正则表达式将该字符串拆分为一个字符串切片，如下面的代码所示（部分来自Andrew Harris 的Gist）：
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:
这是上面 Go 代码的输出：
2 []string{"this,", "that"}
2 []string{"another,", "thing"}
2 []string{"another,", "thing"}
0 []string{}
2 []string{"test,123\",", "one,two,three"}
回答by Russ Egan
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.
这是我写的一些东西，它处理我抛出的所有测试值（测试用例如下）。它也快了大约 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.")
}
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{" ",""}},
}
