英文:
How to write Scan() for custom type in PostgreSQL column in Go
问题
所以我有一个表模式，看起来像下面这样
CREATE TABLE artists (id SERIAL PRIMARY KEY,foo_user_id TEXT NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,time_span TIME_SPAN NOT NULL,items artist [] NOT NULL);
此外，我还在PostgreSQL中创建了自定义类型，定义如下
CREATE TYPE artist AS (artist_name TEXT,foo_artist_id TEXT,artist_image_url TEXT,artist_rank int);
我正在尝试查询所有具有与我传递给函数的"foo_user_id"相等的行。以下是示例代码。
func GetHistoricalTopArtists(foo_user_id string) ([]TopArtists, error) {// 连接到数据库等..// 创建预处理语句stmtStr := `SELECT * FROM artists WHERE foo_user_id=$1`// 错误检查...// 遍历所有行以获取[]TopArtists数组defer rows.Close()for rows.Next() {topArtist := new(TopArtists)err := rows.Scan(&topArtist.Id, &topArtist.FooUserId, &topArtist.CreatedAt, &topArtist.TimeSpan, &topArtist.Artists)if err != nil {log.Fatalf("Something went wrong %v", err)}topArtists = append(topArtists, *topArtist)}}
为了在Go中表示这些数据，我创建了以下结构体
// 表示一行type TopArtists struct {Id int64 `json:"id" db:"id"`FooUserId string `json:"foo_user_id" db:"foo_user_id"`CreatedAt string `json:"created_at" db:"created_at"`TimeSpan string `json:"time_span" db:"time_span"`Artists []Artist `json:"items" db:"items"`}// 表示artist列type Artist struct {ArtistName string `json:"artist_name"`ArtistId string `json:"foo_artist_id"`ArtistImageURL string `json:"artist_image_url"`ArtistRank int `json:"artist_rank"`}
当我调用执行查询的函数（我上面描述的函数）时，我得到以下错误。
在列索引4上的扫描错误，名称为"items"：不支持的扫描，将driver.Value类型[]uint8存储到类型*[]database.Artist中。
我有一个Value()函数，但我不确定如何为我创建的自定义结构体数组实现Scan()函数。
这是我的Value()函数，我尝试阅读文档和类似的帖子以扫描原始类型（字符串、整数等）的数组，但我无法将逻辑应用于自定义的PostgreSQL类型。
func (a Artist) Value() (driver.Value, error) {s := fmt.Sprintf("(%s, %s, %s, %d)",a.ArtistName,a.FooArtistId,a.ArtistImageURL,a.ArtistRank)return []byte(s), nil}
英文:
So I have this table Schema that looks like the following
CREATE TABLE artists (id SERIAL PRIMARY KEY,foo_user_id TEXT NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,time_span TIME_SPAN NOT NULL,items artist [] NOT NULL);
In addition, I also have the custom type created in PostgreSQL that is defined as follows
CREATE TYPE artist AS (artist_name TEXT,foo_artist_id TEXT,artist_image_url TEXT,artist_rank int);
I am trying to query all rows that have the "foo_user_id" equal to what I pass into the function. Here is the sample code.
func GetHistoricalTopArtists(foo_user_id string) ([]TopArtists, error) {// connect to DB etc..// create prepared statementstmtStr := `SELECT * FROM artists WHERE foo_user_id=$1`// error check...// iterate through all rows to get an array of []TopArtistsdefer rows.Close()for rows.Next() {topArtist := new(TopArtists)err := rows.Scan(&topArtist.Id, &topArtist.FooUserId, &topArtist.CreatedAt, &topArtist.TimeSpan, &topArtist.Artists)if err != nil {log.Fatalf("Something went wrong %v", err)}topArtists = append(topArtists, *topArtist)}}
To represent this data in Go I created the following structs
// Represents a rowtype TopArtists struct {Id int64 `json:"id" db:"id"`FooUserId string `json:"foo_user_id" db:"foo_user_id"`CreatedAt string `json:"created_at" db:"created_at"`TimeSpan string `json:"time_span" db:"time_span"`Artists []Artist `json:"items" db:"items"`}// Represents the artist columntype Artist struct {ArtistName string `json:"artist_name"`ArtistId string `json:"foo_artist_id"`ArtistImageURL string `json:"artist_image_url"`ArtistRank int `json:"artist_rank"`}
When I call the function that does the query (the one I described above). I get the following error.
Scan error on column index 4, name "items": unsupported Scan, storing driver.Value type []uint8 into type *[]database.Artist.
I have a Value() function, but I am unsure how to implement a Scan() function for the array of the custom struct I have made.
Here is my Value() function, I have attempted to read documentation and similar posts on scanning arrays of primitive types (strings, int, etc) but I could not apply the logic to custom PostgreSQL types.
func (a Artist) Value() (driver.Value, error) {s := fmt.Sprintf("(%s, %s, %s, %d)",a.ArtistName,a.FooArtistId,a.ArtistImageURL,a.ArtistRank)return []byte(s), nil}
答案1
得分: 1
> @mkopriva - ...你需要声明一个切片类型，例如 type ArtistSlice []Artist，将其作为字段的类型，并在该类型上实现 Value/Scan 方法。
在PostgreSQL中创建了自定义的Composite Types Artist，它有一个严格的结构，如下所示：
{(david,38,url,1),(david2,2,"url 2",2)}
然后，你需要使用自定义的marshal/unmarshal算法来实现Value/Scan方法。
例如：
type Artists []Artistfunc (a *Artists) Scan(value interface{}) error {source, ok := value.(string) // 输入示例：{(david,38,url,1),(david2,2,"url 2",2)}if !ok {return errors.New("不兼容的类型")}var res Artistsartists := strings.Split(source, ",")for _, artist := range artists {for _, old := range []string{"\\", "\"", "{", "}", "(", ")"} {artist = strings.ReplaceAll(artist, old, "")}artistRawData := strings.Split(artist, ",")i, err := strconv.Atoi(artistRawData[1])if err != nil {return fmt.Errorf("在第%d次迭代中解析ArtistRank原始数据(%s)时出错：%v", i, artist, err)}res = append(res, Artist{ArtistName: artistRawData[0],ArtistId: artistRawData[1],ArtistImageURL: artistRawData[2],ArtistRank: i,})}*a = resreturn nil}
英文:
> @mkopriva - ...You need to declare a slice type, e.g. type ArtistSlice []Artist,
> use that as the field's type, and implement the Value/Scan methods on
> that.
Created custom Composite Types Artist in Postgresq has a strict struct as
{(david,38,url,1),(david2,2,"url 2",2)}
then you have to implement Value/Scan method with custom marshal/unmarshal algorithm
For example
type Artists []Artistfunc (a *Artists) Scan(value interface{}) error {source, ok := value.(string) // input example 👉🏻 {"(david,38,url,1)","(david2,2,\"url 2\",2)"}if !ok {return errors.New("incompatible type")}var res Artistsartists := strings.Split(source, "\",\"")for _, artist := range artists {for _, old := range []string{"\\\"","\"","{", "}","(",")"} {artist = strings.ReplaceAll(artist, old, "")}artistRawData := strings.Split(artist, ",")i, err := strconv.Atoi(artistRawData[1])if err != nil {return fmt.Errorf("parce ArtistRank raw data (%s) in %d iteration error: %v", artist, i, err)}res = append(res, Artist{ArtistName: artistRawData[0],ArtistId: artistRawData[1],ArtistImageURL: artistRawData[2],ArtistRank: i,})}*a = resreturn nil}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论