Golang将一个结构体切片作为用户定义类型的数组传递给存储过程

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

Golang pass a slice of structs to a stored procedure as a array of user defined types

问题

我有一个结构体切片,我想将其作为用户定义类型的数组传递给存储过程,在Go语言中我无法找到一种方法来实现这一点。

例如,在Go语言中的结构体定义如下:

type a struct {
	ID      int       `db:"id"`
	Name    string    `db:"name"`
	Created time.Time `db:"created"`
	IsNew   bool      `db:"is_new"`
}

用户定义类型的创建语句如下:

CREATE TYPE custom_type AS
(
    id int,
    name varchar,
    created timestamp,
    is_new boolean
)

然后是存储过程:

create or replace procedure custom_procedure(
   input custom_type[]
)

到目前为止,我尝试了以下方法:

func Save(records []a) error {
    _, err := p.client.Exec("CALL custom_procedure($1)", pq.Array(records))
    return err
}

但是我得到了一个错误:"sql: converting argument $1 type: unsupported type a, a struct"

英文:

I have a slice of structs that I want to pass into a stored procedure to be used as an array of user defined types t but I can't figure out a way of doing this is in Go.

For example the structs in go:

type a struct {
	ID           int `db:"id"`
	Name         string `db:"name"`
	Created      time.Time `db:"created"`
	IsNew		 bool	`db:"is_new"`
}

And the create statement for the user defined type

CREATE TYPE custom_type AS
(
    id int,
    name varchar,
    created timestamp,
    is_new boolean
)

and then the stored procedure

create or replace procedure custom_procedure(
   input custom_type[]
)

So far I have tried doing

func Save(records []a) error {
_, err := p.client.Exec("CALL custom_procedure($1)", pq.Array(records))
return err
}

but I just get an error "sql: converting argument $1 type: unsupported type a, a struct"

答案1

得分: 3

你需要在类型a上实现driver.Valuer接口,并使Value方法返回a实例的PostgreSQL复合类型字面量。

你可以阅读这个文档,了解如何正确构造复合行类型值。只需记住,由于你使用了pq.Array,它会对Value方法的输出进行引号处理,因此你自己不应在输出周围加上引号,也不应使用ROW关键字。

例如:

type a struct {
	ID      int       `db:"id"`
	Name    string    `db:"name"`
	Created time.Time `db:"created"`
	IsNew   bool      `db:"is_new"`
}

func (v a) Value() (driver.Value, error) {
	s := fmt.Sprintf("(%d,%q,%s,%t)",
		v.ID,
		v.Name,
		v.Created.Format("2006-01-02 15:04:05"),
		v.IsNew,
	)
	return []byte(s), nil
}
英文:

You'll have to implement the driver.Valuer interface on the a type and have the Value method return a postgres composite type literal of the instance of a.

You can read this documentation on how to properly construct composite row type values. Just keep in mind that, since you're using pq.Array, which will quote the output of the Value method, you yourself SHOULD NOT put quotes around the output and also you SHOULD NOT use the ROW keyword.

For example:

type a struct {
	ID      int       `db:"id"`
	Name    string    `db:"name"`
	Created time.Time `db:"created"`
	IsNew   bool      `db:"is_new"`
}

func (v a) Value() (driver.Value, error) {
	s := fmt.Sprintf("(%d,%q,%s,%t)",
		v.ID,
		v.Name,
		v.Created.Format("2006-01-02 15:04:05"),
		v.IsNew,
	)
	return []byte(s), nil
}

huangapple
  • 本文由 发表于 2021年6月27日 04:19:44
  • 转载请务必保留本文链接:https://go.coder-hub.com/68145980.html
匿名

发表评论

匿名网友

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

确定