1

I'm struggling to properly utilize sqlx and the pq driver for Postgres to create a row in the database. Let's start simple:

I have a user, role and user_role table. I want to insert a role into the database and get the ID of the inserted row. This works flawlessly using the following sql:

const createRoleSQL = "INSERT INTO role (name) VALUES (:name) RETURNING id"

To make that work in go, I prepare the statement at some point:

createStmt, err := db.PrepareNamed(createRoleSQL)
if err != nil {
    // ...
}

When creating, I run the query as part of a transaction tx. role is obviously a struct with the correct fields and db tags:

if err := tx.NamedStmt(createStmt).QueryRow(role).Scan(&role.ID); err != nil {
    // ...
}

This works perfectly fine.

Now I wanted to extend that and insert a new role and assign it to a user:

const createUserRoleSQL = `
DO $$
DECLARE role_id role.id%TYPE;
BEGIN
    INSERT INTO role (name) VALUES ($2) RETURNING id INTO role_id;
    INSERT INTO user_role (user_id, role_id) VALUES ($1, role_id);
END $$`

createStmt, err := db.Preparex(createUserRoleSQL)
if err != nil {
    // ...
}

if err := tx.Stmtx(createStmt).QueryRow(userID, role.Name).Scan(&role.ID); err != nil {
    // ...
}

Unfortunately this fails with sql: expected 0 arguments, got 2. Is it possible to achieve what I want to do, with a single query?

LuMa
  • 1,673
  • 3
  • 19
  • 41
  • I guess that $1 and $2 are not understood as parameters. Did you check using same way as ":name". Here it may be ":1" and ":2" – igr Sep 15 '19 at 13:04
  • No, `$` is the correct notation in this case. – LuMa Sep 15 '19 at 16:19
  • 1
    According to the [documentation](https://www.postgresql.org/docs/current/sql-do.html), the ```DO``` statement takes no parameters. Thus I would also guess ```$``` is invalid. Have you tried it with ```:```? Perhaps you also might be able to build your SQL command string directly with the parameters (eventhough this is indeed not that nice) – Islingre Sep 16 '19 at 00:02
  • Indeed, that's the issue. I guess I will split that up into two queries and run them in a single transaction. – LuMa Sep 16 '19 at 18:18
  • Feel free to post this as an answer to have it marked as solution. – LuMa Sep 16 '19 at 18:19

0 Answers0