英文:
PostgreSQL inserting multiple tables and rows
问题
我有两个结构体，一个是Company，另一个是Service。它们之间有一个"一对多"的关系，即一个公司可以有多个服务。我想编写一个SQL查询，可以在一次查询中插入一个公司和多个附属于该公司的服务。
原始的SQL查询如下：
WITH company AS (INSERT INTO companies(id, name) VALUES('1', 'acme') RETURNING id)INSERT INTO services(id, company_id, name) VALUES('1', (SELECT company.id FROM company), 'cool service'),('2', (SELECT company.id FROM company), 'cooler service');
我正在尝试使用Go的sql包来模拟这个查询。以下是我目前的尝试，我在顶部添加了结构体以增加清晰度：
c := &Company{ID: uuid.NewV4().String(),Name: "test comp",}s := []*Service{&Service{ID: uuid.NewV4().String(),CompanyID: c.ID,Name: "test svc",},}c.Services = svalues := []interface{}{c.ID,c.Name,}q := `WITH company AS (INSERT INTO companies(id, name) VALUES ($1, $2) RETURNING id)INSERT INTO services(id, company_id, name) VALUES`for _, row := range c.Services {q += "($1, (SELECT company.id FROM company), $2),"values = append(values, row.ID, row.Name)}q = strings.TrimSuffix(q, ",")stmt, err := s.DB.Prepare(q)if err != nil {return err}if _, err := stmt.Exec(values...); err != nil {return err}
但是，我不确定还有其他方法可以实现这个目标。使用这种方法，我得到以下错误：
ERROR #08P01 bind message supplies 5 parameters, but prepared statement "1" requires 3
这是有道理的，因为我在执行exec时传递了5个参数，而预处理语句"1"只需要3个参数。但是，有没有办法在不将查询拆分为多个查询的情况下执行我的查询呢？
英文:
I have 2 structs one Company the other Service. They have a has-many relationship company to service. I'm trying to write an SQL query that will insert a company and multiple services attached to that company in one query.
RAW SQL:
WITH company AS ( INSERT INTO companies(id, name) VALUES('1', 'acme') RETURNING id)INSERT INTO services(id, company_id, name) VALUES('1', (select company.id from company), 'cool service'),('2', (select company.id from company), 'cooler service');
I'm trying to imitate this using go's sql package. This is my attempt thus far: I've added the structs at the top just for clarity
c := &Company{ID: uuid.NewV4().String(),Name: "test comp",}s := []*Service{&Service{ID: uuid.NewV4().String(),CompanyID: c.ID,Name: "test svc",},}c.Service = svalues := []interface{}{c.ID,c.Name,}q := `WITH company as (INSERT INTO companies(id, name) VALUES ($1, $2)) INSERT INTO services(id, company_id, name) VALUES`for _, row := range c.Services {q += "($1, $2, $3),"values = append(values, row.ID, row.CompanyID)}q = strings.TrimSuffix(q, ",")stmt, err := s.DB.Prepare(q)if err != nil {return err}if _, err := stmt.Exec(values...); err != nil {return err}
I'm not sure how else to go about this but with this method I get this error:
ERROR #08P01 bind message supplies 5 parameters, but prepared statement "1" requires 3
Which makes sense I'm passing 5 parameters to exec when prepared statement "1" which I'm guessing is the second one only requires 3. But how can I perform my query without having to split it up into more than 1 query?
答案1
得分: 0
正如你的错误消息所说，你的SQL语句只使用了3个绑定变量：
WITH company as (INSERT INTO companies(id, name) VALUES ($1, $2)) INSERT INTO services(id, company_id, name) VALUES
和：
q += "($1, $2, $3),"
你只是在每一行中重复使用$1、$2和$3。所以你构建的查询将会是这样的：
WITH company as (INSERT INTO companies(id, name) VALUES ($1, $2)) INSERT INTO services(id, company_id, name) VALUES($1, $2, $3),($1, $2, $3),($1, $2, $3),($1, $2, $3),
显然这不是你想要的结果。你需要在每次循环迭代中递增你的绑定变量编号。
我的方法可能是这样的：
var i int = 3for _, row := range c.Services {q += fmt.Sprintf("($%d, $%d, $%d),", i, i+1, i+2)values = append(values, row.ID, row.CompanyID)i += 3}
英文:
Just as your error message says, your SQL statement only uses 3 bind variables:
WITH company as (INSERT INTO companies(id, name) VALUES ($1, $2)) INSERT INTO services(id, company_id, name) VALUES
and:
q += "($1, $2, $3),"
You're simply repeating $1, $2, and $3 for each of your rows. So your constructed query will look something like:
WITH company as (INSERT INTO companies(id, name) VALUES ($1, $2)) INSERT INTO services(id, company_id, name) VALUES($1, $2, $3),($1, $2, $3),($1, $2, $3),($1, $2, $3),
Clearly that's not what you want. You need to increment your bind variable numbering with each iteration through the loop.
My approach would probably be something like:
var i int = 3for _, row := range c.Services {q += fmt.Sprintf("($%d, $%d, $%d),", i, i+1, i+2)values = append(values, row.ID, row.CompanyID)i += 3}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论