dongyan4424 2017-02-25 21:42
浏览 206
已采纳

PostgreSQL插入多个表和行

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 = s

values := []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条回答 默认 最新

  • duanshan2988 2017-02-25 22:05
    关注

    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 = 3
    for _, row := range c.Services {
        q += fmt.Sprintf("($%d, $%d, $%d),", i, i+1, i+2)
        values = append(values, row.ID, row.CompanyID)
        i += 3
    }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 联想电脑重装系统时无法发现硬盘
  • ¥15 MATLAB与UR10e实体机械臂建立通讯
  • ¥15 c++题需要快一点不用opencv
  • ¥15 关于#java#的问题:想要咨询Flowable流程引擎框架的问题
  • ¥15 vscode里面怎么用plaformio强调串口啊
  • ¥20 针对计算后数据做一致性检验可以用Bland Altman法吗
  • ¥15 win32如何自绘编辑框的背景图片(语言-c++|操作系统-windows)
  • ¥15 微信夜间被转走了1w对,当天手机剪切板里就出现了这个乱码,有铁子可以看看是啥吗可以
  • ¥50 跑通github上的代码 深度学习 pytorch
  • ¥50 求写,批处理调用分区助手分区脚本