var (
    newServices                                                                                                             *models.UpsertIntoServices
    companyCode, serviceCode, approvalEmail, approvalName, coApprovalEmail, coApprovalName, technicianEmail, technicianName string
    departmentApproval, companyApproval, xapiensApproval, isActual, isMinus                                                 bool
    id                                                                                                                      uint
)

tx, txErr := s.db.Begin()
if txErr != nil {
    return newServices, txErr
}

qapi := `
insert into company_services 
    (company_code, service_code, department_approval, company_approval, xapiens_approval, approval_email, approval_name, co_approval_email,technician_email, co_approval_name, technician_name, is_actual, is_minus)
    values 
        ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
    on conflict (company_code, service_code)
do update set company_code =excluded.company_code, service_code =excluded.service_code;
`

//insert data into new_services
sqlErr := tx.QueryRowContext(ctx, qapi, data.CompanyCode, data.ServiceCode, data.DepartmentApproval, data.CompanyApproval, data.XapiensApproval, data.ApprovalEmail, data.ApprovalName, data.CoApprovalEmail, data.TechnicianEmail, data.CoApprovalName, data.TechnicianName, data.IsActual, data.IsMinus).Scan(
    &id,
    &companyCode,
    &serviceCode,
    &departmentApproval,
    &companyApproval,
    &xapiensApproval,
    &approvalEmail,
    &approvalName,
    &coApprovalEmail,
    &technicianEmail,
    &coApprovalName,
    &technicianName,
    &isActual,
    &isMinus,
)

// checking if its rows has any errors but no result here
if sqlErr != nil {
    tx.Rollback()
    log.Println("sql Error on Repository Upsert Flagging", sqlErr)
    return newServices, sqlErr
}

// commit transaction query
txErr = tx.Commit()
if txErr != nil {
    return newServices, txErr
}

CodePudding user response：

Use ExecContext to execute insert/update query. Try the below code

 var (
    newServices                                                                                                             *models.UpsertIntoServices
    companyCode, serviceCode, approvalEmail, approvalName, coApprovalEmail, coApprovalName, technicianEmail, technicianName string
    departmentApproval, companyApproval, xapiensApproval, isActual, isMinus                                                 bool
    id                                                                                                                      uint
)

tx, txErr := s.db.Begin()
if txErr != nil {
    return newServices, txErr
}

qapi := `
insert into company_services 
    (company_code, service_code, department_approval, company_approval, xapiens_approval, approval_email, approval_name, co_approval_email,technician_email, co_approval_name, technician_name, is_actual, is_minus)
    values 
        ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
    on conflict (company_code, service_code)
do update set company_code =excluded.company_code, service_code =excluded.service_code;
`

//insert data into new_services
result, sqlErr := tx.ExecContext(ctx, qapi, data.CompanyCode, data.ServiceCode, data.DepartmentApproval, data.CompanyApproval, data.XapiensApproval, data.ApprovalEmail, data.ApprovalName, data.CoApprovalEmail, data.TechnicianEmail, data.CoApprovalName, data.TechnicianName, data.IsActual, data.IsMinus)

// checking if its rows has any errors but no result here
if sqlErr != nil {
    tx.Rollback()
    log.Println("sql Error on Repository Upsert Flagging", sqlErr)
    return newServices, sqlErr
}
//You can know lastInsertId and affectedRows
lastInsertId, _ := result.LastInsertId()
affectedRowsCnt,_ :=  result.RowsAffected()
fmt.Println(lastInsertId,affectedRowsCnt)

// commit transaction query
txErr = tx.Commit()
if txErr != nil {
    return newServices, txErr
}