Stored Procedure with if else statement in PostgreSQL

user_10019093 0
Viewed 479

I'm trying to find the best way to make a 'like' button on my application. I would like to hit the same endpoint and have a stored procedure take care of the logic. This is what I have so far, but it doesn't seem to work. My goal is to test if a record exist, if it does then delete the record, else create the record. Please let me know what I'm doing wrong, or if a stored procedure is the way to go here. Below is the logic that I want in the procedure, but currently not working. Thanks

IF (SELECT * FROM favorite_movies WHERE movie_id = '577922' and user_id = '5' limit 1)then
 delete from favorite_movies WHERE movie_id = 577922 and user_id = 5;
  else 
  INSERT INTO favorite_movies (user_id, movie_id)VALUES (5, 577922);
END IF

The error I'm getting is

SQL Error [42601]: ERROR: syntax error at or near "IF"
  Position: 1
sql-insert sql-delete plpgsql postgresql sql
1 Answers

No need for plpgsql. Postgres supports returning in common-table-expressions, which make it possible to implement the logic in a single query:

with 
    param (movie_id, user_id) as (select 577922, 5),    -- query parameters
    del as (                                            -- try to delete
        delete from favorite_movies m
        using param p
        where m.movie_id = p.movie_id and m.user_id = p.user_id
        returning 1
    )
insert into favorite_movies (movie_id, user_id)         -- if nothing was deleted, then insert 
select p.* 
from param p
where not exists (select 1 from del)

Basically this implements the "toggle" functionality. The first time the query is executed, there is nothing to delete, so it inserts a new row; the next time, it deletes the row, and hence does not insert, and so on.

Demo on DB Fiddle

user_106767160
Related