Trigger for Update

[praj18]

I am trying to create something that is similar to that of Facebook's reactions.
I have a separate users table and a posts table. Below are the reactions table and the post_reactions table.

Reactions Table

id	post_id	user_id	reaction	timestamp
1	124	1111	love	xxx
2	166	1223	sad	xxx
3	124	1543	love	xxxx

Post_reaction Table

id	post_id	like_count	love_count	angry_count	laugh_count	sad_count	updated_at
1	124	0	2	0	210	0	xxx
2	166	0	0	0	0	100	xx

Each time a user reacts to a post, it gets added to the reactions table. Similarly, if they update their reaction or remove it, it gets reflected on the table.

I intend to set a trigger so that each time the reactions table gets updated, it reflects in the post_reactions table, so that it is easier to get the aggregate when displaying it to the end user.

I have set a trigger for INSERT and DELETE that looks something like this

create or replace function public.handle_reaction_insert_delete() 
returns trigger as $$
begin
        IF (TG_OP = 'DELETE') THEN

          IF (old.reaction = 'like') THEN
              UPDATE post_reaction SET like_count = like_count - 1 WHERE post_id = OLD.post_id;
            RETURN OLD;
          ELSIF (old.reaction = 'love') THEN
              UPDATE post_reaction SET love_count = love_count - 1 WHERE post_id = OLD.post_id;
            RETURN OLD;
          ELSIF (old.reaction = 'laugh') THEN
              UPDATE post_reaction SET laugh_count = laugh_count - 1 WHERE post_id = OLD.post_id;
            RETURN OLD;
          ELSIF (old.reaction = 'wow') THEN
              UPDATE post_reaction SET wow_count = wow_count - 1 WHERE post_id = OLD.post_id;
            RETURN OLD;
          ELSIF (old.reaction = 'sad') THEN
              UPDATE post_reaction SET sad_count = sad_count - 1 WHERE post_id = OLD.post_id;
            RETURN OLD;
          ELSIF (old.reaction = 'angry') THEN
              UPDATE post_reaction SET angry_count = angry_count - 1 WHERE post_id = OLD.post_id;
            RETURN OLD;
          END IF;
        ELSIF (TG_OP = 'INSERT') THEN

          IF (new.reaction = 'like') THEN
              UPDATE post_reaction SET like_count = like_count + 1 WHERE post_id = new.post_id;
            RETURN NEW;
          ELSIF (new.reaction = 'love') THEN
              UPDATE post_reaction SET love_count = love_count + 1 WHERE post_id = new.post_id;
            RETURN NEW;
          ELSIF (new.reaction = 'laugh') THEN
              UPDATE post_reaction SET laugh_count = laugh_count + 1 WHERE post_id = new.post_id;
            RETURN NEW;
          ELSIF (new.reaction = 'wow') THEN
              UPDATE post_reaction SET wow_count = wow_count + 1 WHERE post_id = new.post_id;
            RETURN NEW;
          ELSIF (new.reaction = 'sad') THEN
              UPDATE post_reaction SET sad_count = sad_count + 1 WHERE post_id = new.post_id;
            RETURN NEW;
          ELSIF (new.reaction = 'angry') THEN
              UPDATE post_reaction SET angry_count = angry_count + 1 WHERE post_id = new.post_id;
            RETURN NEW;
          END IF;
        END IF;
        RETURN NULL;
end;
$$ language plpgsql;

create trigger insert_delete_reaction_count
  after INSERT OR DELETE on reactions
  for each row execute procedure public.handle_reaction_insert_delete();

My question is, how do I do handle the UPDATE function?
When a user changes their reaction from e.g. like to love, the reactions table changes the user's reaction type. However, I do not know how to reflect this on the post_reactions table, as I need to -1 on the like_count and +1 on the love_count.

Do I perform a trigger that is something like this?

create or replace function public.handle_reaction_update() 
returns trigger as $$
begin
        IF (old.reaction = 'like') THEN
              UPDATE post_reaction SET like_count = like_count - 1 WHERE post_id = OLD.post_id;
            
            IF (new.reaction = 'love') THEN
              UPDATE post_reaction SET love_count = love_count + 1 WHERE post_id = NEW.post_id;
            RETURN NEW;
            ELSIF (new.reaction = 'laugh') THEN
              UPDATE post_reaction SET laugh_count = laugh_count + 1 WHERE post_id = NEW.post_id;
            RETURN NEW;
            ELSIF (new.reaction = 'wow') THEN
              UPDATE post_reaction SET wow_count = wow_count + 1 WHERE post_id = NEW.post_id;
            RETURN NEW;
            ELSIF (new.reaction = 'sad') THEN
              UPDATE post_reaction SET sad_count = sad_count + 1 WHERE post_id = NEW.post_id;
            RETURN NEW;
            ELSIF (new.reaction = 'angry') THEN
              UPDATE post_reaction SET angry_count = angry_count + 1 WHERE post_id = NEW.post_id;
            RETURN NEW;
           END IF;
         END IF;
        ELSEIF (old.reaction = 'love') THEN
              UPDATE post_reaction SET love_count = love_count - 1 WHERE post_id = NEW.post_id;
            IF (new.reaction = 'like') THEN
              UPDATE post_reaction SET like_count = like_count + 1 WHERE post_id = OLD.post_id;
            RETURN NEW;
            ELSIF (new.reaction = 'laugh') THEN
              UPDATE post_reaction SET laugh_count = laugh_count + 1 WHERE post_id = NEW.post_id;
            RETURN NEW;
          ....

$$ language plpgsql;

create trigger update_reaction_count
  after UPDATE on reactions
  for each row execute procedure public.handle_reaction_update();

Or is there a better way?

[wolfgangwalther]

I see 3 approaches to solve that:

1. Use a MATERIALIZED VIEW to aggregate the data. Then refresh that CONCURRENTLY periodically. This means, that the total numbers of reactions might not always be accurate immediately. In the future, incremental view maintenance might be implemented in PG, which would solve that.

2. Use the trigger approach, but aggregate the full post_reaction row after each insert/update/delete to reactions.

3. Use the trigger approach and create the UPDATE statement you're using right now via dynamic SQL. In this case you don't need all the IF conditions.

[praj18]

Thank you so much for your reply. I have been attempting to create an update statement using dynamic SQL for this, however, I can't seem to wrap my head around it.

Would you be so kind as to help me out here with the 3rd approach?

[wolfgangwalther]

Your generic statement for the AFTER UPDATE ON reactions trigger seems to look like this:

UPDATE post_reaction
   SET XXX_count = XXX_count - 1,
       YYY_count = YYY_count + 1
 WHERE post_id = NEW.post_id;

The dynamic query should look something like:

EXECUTE FORMAT($f$
  UPDATE ...
$f$, OLD.reaction, NEW.reaction);

From the docs for the FORMAT() function, you can tell that you need %s here to format the argument as a simple string without quoting.

Putting it together:

EXECUTE FORMAT($f$
  UPDATE post_reaction
     SET %1$s_count = %1$s_count - 1,
         %2$s_count = %2$s_count + 1
   WHERE post_id = NEW.post_id;
$f$, OLD.reaction, NEW.reaction);

Now you could extend this a bit further to dynamically inject the two SET parts depending on the TG_OP using simple string concatenation - and then use the same function for all three triggers.

[praj18]

Thank you so much. You have been of great help!

I just had to alter it slightly because it was giving me the "you're missing a FROM clause for 'New'" error.

So now it looks like this, for anyone having similar issues.

...
WHERE post_id = %3$s;
$f$, OLD.reaction, NEW.reaction, NEW.post_id);