1

I have a games table, that records p1_id, p1_score, p2_id, p2_score, and winner_id. I would like to have a trigger that uses a user defined function to automatically fill in the winner (as practice of using both).

I have the trigger:

CREATE TRIGGER determine_winner 
BEFORE INSERT ON games
FOR EACH ROW 
EXECUTE PROCEDURE calculate_winner();

and the UDF:

CREATE OR REPLACE FUNCTION calculate_winner()
RETURNS TRIGGER AS $d$
BEGIN
IF NEW.p1_score > NEW.p2_score THEN
  NEW.winner_id = NEW.p1_id;
END IF;

IF NEW.p1_score < NEW.p2_score THEN
  NEW.winner_id = NEW.p2_id;
END IF;

IF NEW.p1_score = NEW.p2_score THEN
 NEW.winner_id = 0;
END IF;

RETURN NEW;
END;
$d$ LANGUAGE plpgsql;

This works perfectly, but I don't like at all using three IF statements, but cannot get a CASE statement to work. Many examples seem to be in different versions of SQL that do not work. Can anyone help with this?

Thanks!

Update: For reference, a ELSIF also works, which looks like: 

IF NEW.p1_score > NEW.p2_score THEN
   NEW.winner_id = NEW.p1_id;
ELSIF NEW.p1_score < NEW.p2_score THEN
  NEW.winner_id = NEW.p2_id;
ELSE
 NEW.winner_id = 0;
END IF;
glenrothes
  • 1,543
  • 1
  • 14
  • 17
  • 2
    [`ELSIF`](https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#AEN65879) – Nick Barnes May 24 '17 at 02:35
  • @NickBarnes Thanks! So simple. I'll update to just asking for a working CASE statement :) – glenrothes May 24 '17 at 11:45
  • [This tutorial](http://www.postgresqltutorial.com/plpgsql-case-statement/) has a function with a case. – LukStorms May 24 '17 at 12:00
  • It'd help if you included your non-working example. Just a guess, but you might be confusing the syntax of the procedural [case statement](https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#AEN65945) with the SQL [case expression](https://www.postgresql.org/docs/current/static/functions-conditional.html#FUNCTIONS-CASE). – Nick Barnes May 24 '17 at 14:16
  • @LukStorms Thanks, that looks like an amazing resource that has turned up in none of my searches so far! – glenrothes May 24 '17 at 15:04
  • @dominicfraser Good that it helped. :) But personally I would stick to the IF .. ELSIF .. syntax in this case. And use CASE only for selects. – LukStorms May 24 '17 at 15:18
  • @dominicfraser Well, CASE is more usefull in a procedure when you use it like a switch. F.e. `CASE value WHEN 1 THEN x='A'; WHEN 2 THEN x='B'; ELSE x='C'; END CASE;` – LukStorms May 24 '17 at 15:27

0 Answers0