如何在Go中为PostgreSQL列编写自定义类型的Scan()函数

huangapple go评论83阅读模式
英文:

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 statement
stmtStr := `SELECT * FROM artists WHERE foo_user_id=$1`

// error check...

// iterate through all rows to get an array of []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)
	}
}

To represent this data in Go I created the following structs

// Represents a row
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"`
}

// Represents the artist column
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"`
}

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 []Artist

func (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 Artists
	artists := 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 = res
	return 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 []Artist

func (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 Artists
	artists := 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 = res
	return nil
}

huangapple
  • 本文由 发表于 2021年10月28日 03:10:43
  • 转载请务必保留本文链接:https://go.coder-hub.com/69744248.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定