How to pass variable to a do block with sqlx?

Question

I am trying to run the following do block script in sql through sqlx. As do block doesn't accept parameters, I tried to prepare it beforehand.

func LinkSessionUser(sessionId string, userId string, result *bool) error  {
    db := database.GetDB()
    statement, err := db.Preparex(`
        do
        $$
        begin
        if (exists(select id from "session" where id = $1) and exists(select id from "user" where id = $2)) then
          return insert into "session_user" (session_id, user_id) values ($1, $2) on conflict do nothing;
        else
          raise exception "Either session(id=%) or user(id=%) doesn't exist" $1, $2;
        end if;
        end
        $$;
    `)
    if err != nil {
        return err
    }
    return statement.Get(result, sessionId, userId)
}

But as I run it, I got the following errors:

sql: expected 0 arguments, got 2

How can I fix this issue? Should I be using Preparex to replace prepare in sql?

Answer 1

"Should I be using Preparex to replace prepare in sql?" -- Yes, however the problem you've encountered has nothing to do with Preparex, or the Go language itself, for that matter.

The code block of the DO command is a string literal, this string literal will not be, and has no reason to be, parsed during the PREPARE command execution, which is why the resulting prepared statement will have no defined parameters. As you correctly point out, DO does not support input parameters, but trying to get around that limitation by wrapping DO in a prepared statement will simply not produce the result you were hoping for.

If you need to conditionally execute parameterized SQL statements then you should use the CREATE FUNCTION command and explicitly execute the resulting function.