英文:
How to pass variable to a do block with sqlx?
问题
我正在尝试通过sqlx运行以下的do块脚本。由于do块不接受参数，我尝试事先准备好它。
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)
}
但是当我运行它时，我得到了以下错误：
sql: expected 0 arguments, got 2
我该如何解决这个问题？我应该使用Preparex来替换sql中的prepare吗？
英文:
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?
答案1
得分: 4
"我应该使用Preparex来替换sql中的prepare吗？" -- 是的，然而你遇到的问题与Preparex或者Go语言本身无关。
DO命令的代码块是一个字符串字面量，这个字符串字面量在PREPARE命令执行期间不会被解析，也没有理由被解析，这就是为什么生成的预处理语句没有定义的参数。正如你正确指出的，DO不支持输入参数，但是试图通过将DO包装在一个预处理语句中来绕过这个限制只会产生你所希望的结果。
如果你需要有条件地执行带参数的SQL语句，那么你应该使用CREATE FUNCTION命令并显式地执行生成的函数。
英文:
"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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论