英文:
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
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论