I’m getting this nodejs error when I try running my query with params ($1, $2, $3). The query works in postgres. It also works if I don’t use the params ($1, $2, $3) and instead substitute values in for them. Could someone help me identify the issue?
error: bind message supplies 3 parameters, but prepared statement "" requires 0
The query I’m trying to run:
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])
Advertisement
Answer
Postgresql does not support parameters to an anonymous block
The relevant doc https://www.postgresql.org/docs/9.4/sql-do.html
Relevant ticket https://github.com/brianc/node-postgres/issues/812
Migrating into using a stored procedure or function appears to be your best way to proceed. The ticket has an suggestion using CREATE TEMPORARY FUNCTION