Postgres + Node - Parameter Query with $1, $2, $3 giving error

Question

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])

It's still giving me the same error

Alice W.
– Alice W.

2019-06-07 23:01:04 +00:00
Commented Jun 7, 2019 at 23:01 — Alice W.
– Alice W., Commented Jun 7, 2019 at 23:01

Deadron · Accepted Answer · 2019-06-07 23:18:17Z

3

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

answered Jun 7, 2019 at 23:18

Deadron

5,2891 gold badge18 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres + Node - Parameter Query with $1, $2, $3 giving error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related