CODEWITHSUNDEEP
postgresqlplpgsql
WHOATEMYNOODLES
WHOATEMYNOODLES

Reputation: 897

Can't figure out why I'm getting error in case statement

BEGIN
IF EXISTS(SELECT * FROM user_likes ul WHERE user_likes.user_id = $2 AND user_likes.comment_id = $1) THEN
    IF(CASE WHEN ul.liked END) THEN
        UPDATE user_likes SET liked = null WHERE user_id = $2 AND comment_id = $1;
        UPDATE event_comments SET num_likes = num_likes - 1 WHERE comment_id = $1;
    ELSE
        UPDATE user_likes SET liked = true WHERE user_id = $2 AND comment_id = $1;
        UPDATE event_comments SET num_likes = num_likes + 1 WHERE comment_id = $1;
    END IF;
ELSE
    INSERT INTO user_likes(comment_id, user_id, liked)
    VALUES($1, $2, true);
    UPDATE event_comments SET num_likes = num_likes + 1 WHERE event_comments.comment_id = 1;

END IF;
END;

Getting this error: 

ERROR: syntax error at or near "END"
LINE 9: IF(CASE WHEN ul.liked END) THEN

I'm trying to check from the exists table ul if the column ul.liked is true

Upvotes: 0

Views: 82

Answers (1)

user330315
user330315

Reputation:

The immediate problem is that you don't need the CASE expression inside the IF:

So (assuming liked is indeed a boolean column) this:

IF(CASE WHEN ul.liked END) THEN

should be:

IF ul.liked THEN

This assumes you have a record variable defined named ul that contains a field named liked

But I think the whole approach can be simplified to a single UPDATE ON CONFLICT statement. And you shouldn't store NULL as a flag for "not liked", I think it would be better to use false in that case.

Something like this: 

with new_like as (
  INSERT INTO user_likes(comment_id, user_id, liked)
  VALUES($1, $2, true)
  ON CONFLICT (comment_id, user_id) DO UPDATE 
    SET liked = case 
                  when user_likes.liked then null 
                  else excluded.liked
                end
  returning *
) 
UPDATE event_comments 
   SET num_likes = num_likes + case when nl.liked is null then -1 else 1 end
FROM new_like nl
WHERE event_comments.comment_id = nl.comment_id;

Or alternatively, not using NULL but false instead: 

with new_like as (
  INSERT INTO user_likes(comment_id, user_id, liked)
  VALUES($1, $2, true)
  ON CONFLICT (comment_id, user_id) DO UPDATE 
    SET liked = NOT user_likes.liked --<< simply flips the value 
  returning *
) 
UPDATE event_comments 
   SET num_likes = num_likes + case when nl.liked then -1 else 1 end
FROM new_like nl
WHERE event_comments.comment_id = nl.comment_id;

Upvotes: 1

Related Questions