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

user_8587779 0
Viewed 497

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"

go postgresql
1 Answers

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
}
user_9659000
Related