mardi 23 mai 2017

UDF, Triggers, and IF/ELSE or CASE in PostgreSQL

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 IF/ELSE IF or 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!

Aucun commentaire:

Enregistrer un commentaire