gpt4 book ai didi

javascript - Postgres + Node - 带有 $1、$2、$3 的参数查询给出错误

转载 作者:搜寻专家 更新时间:2023-10-31 23:44:17 24 4
gpt4 key购买 nike

当我尝试使用参数($1、$2、$3)运行我的查询时,我遇到了这个 nodejs 错误。该查询在 postgres 中有效。如果我不使用参数($1、$2、$3)而是用值代替它们,它也会起作用。有人可以帮我找出问题所在吗?

   error: bind message supplies 3 parameters, but prepared statement "" requires 0 

我正在尝试运行的查询:

var theQuery =   `DO
$do$
BEGIN
IF EXISTS (SELECT * FROM commentsvoted WHERE id = $1 AND user_id = $2) THEN
with t as (
SELECT votes FROM commentsvoted WHERE commentsvoted.id = $1 AND
commentsvoted.user_id = $2
),
cv as (
UPDATE commentsvoted
SET votes = (CASE WHEN t.votes THEN NULL ELSE true END)
FROM t
WHERE commentsvoted.id = $1 AND commentsvoted.user_id = $2
)
UPDATE comments
SET upvoted = (CASE WHEN t.votes THEN upvoted - 1
WHEN t.votes IS NULL THEN upvoted + 1
ELSE upvoted
END),
downvoted = (CASE WHEN NOT t.votes THEN downvoted - 1
ELSE downvoted
END)

FROM t
WHERE comment_id = $1 AND posted_by = $2;
ELSE
with t as (UPDATE comments SET upvoted = upvoted + 1
WHERE comment_id = $1 AND posted_by = $2)
INSERT INTO commentsvoted(id, user_id, votes, thread_id)
VALUES($1, $2, true, $3);
END IF;
END
$do$`;


client.query(theQuery, [commentId, userId, threadId])

最佳答案

Postgresql 不支持匿名 block 的参数

相关文件https://www.postgresql.org/docs/9.4/sql-do.html

相关工单https://github.com/brianc/node-postgres/issues/812

迁移到使用存储过程或函数似乎是您继续进行的最佳方式。工单有使用 CREATE TEMPORARY FUNCTION 的建议

关于javascript - Postgres + Node - 带有 $1、$2、$3 的参数查询给出错误,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/56501962/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com