Trigger to update a column during insert in postgres

Question

I have two tables (in postgres) - ads and logs. After every insert into logs table, depending on the action it should increment the count of a column in the ads table.

I have tried to write a function which the trigger will call, but it throws a error while trying to create the function. I am new to postgresql triggers and function, so can someone point out what is wrong with this.

create function update_ad_count (action character(4), ad_id INT) returns void 
as $$case when action='VIEW' then
(UPDATE ads SET views = (SELECT views+1 FROM ads WHERE id=ad_id), updated = now() WHERE id=ad_id;)
end;$$
language sql

The error I get is

ERROR:  syntax error at or near "case"
LINE 2: as $$case when action=\'VIEW\' then
             ^

Update: @Tomalak: Thanks for the function and updating the update statement (couldn't resist the pun).

I learnt after lot of googling, that the function shouldn't have any params and use NEW.col_name and should return a TRIGGER.

Answer 1

First off, your UPDATE call should be:

UPDATE ads SET views = views + 1, updated = NOW() WHERE id = ad_id;

According to the documentation, something like this should do it:

CREATE FUNCTION update_ad_count (action CHARACTER(4), ad_id INT) 
RETURNS VOID
AS $$ 
BEGIN
  IF action = 'VIEW' THEN
    UPDATE ads SET views = views + 1, updated = NOW() WHERE id = ad_id;
  END IF;
END;
$$ LANGUAGE plpgsql;

CASE is not a flow-control statement, it cannot be used like an IF statement. It is a value-producing statement (an expression) and must be used as such, e.g. you must SELECT/UPDATE from it.

Answer 2

I'm not hugely familiar with postgresql, but based on what I do know... shouldn't it be "$$ case" instead of "$$case"?