-1

I have this table

CREATE TABLE game (
    idp                   serial NOT NULL,
    start_pl              integer NOT NULL,
    finis_pl              integer NOT NULL,
    points                 integer NOT NULL
    );

When I insert or update column finis_pl, I want to autocomplete column points. For example when insert number 1 in finis_pl column I want to autoinsert value 15 in points column.

I made a function using CASE (because I have only 16 possible values for inserting in points) but I can't make it work with a trigger.

Any idea how to make it work?

Jacob Schoen
  • 14,034
  • 15
  • 82
  • 102
alinux
  • 1
  • 2
  • 1
    "*can't make it work with a trigger*" is not a valid PostgreSQL error message. Why don't you show us what you have tried so far? –  Sep 13 '11 at 15:18
  • Yeah, a trigger is the only way that I know of to do something like this. –  Sep 13 '11 at 15:22
  • CREATE OR REPLACE FUNCTION calc() RETURNS trigger AS $BODY$ DECLARE value game.points%TYPE; BEGIN IF NEW.finish_pl=1 WHERE game.idp = NEW.idp THEN UPDATE game SET points = 15 WHERE idp = NEW.idp; END IF; RETURN NEW; END;$BODY$ LANGUAGE plpgsql VOLATILE CREATE TRIGGER tr_calc BEFORE INSERT OR UPDATE OF finish_pl ON game FOR EACH ROW EXECUTE PROCEDURE calc(); – alinux Sep 13 '11 at 15:29
  • i made this function but i don't know how to implement it: – alinux Sep 13 '11 at 15:32
  • CREATE OR REPLACE FUNCTION calcul_punt(val integer) RETURNS integer AS $BODY$ DECLARE result varchar; BEGIN IF val <1 OR val > 15 THEN val:=0; END IF; SELECT INTO result CASE val WHEN 1 THEN 15 WHEN 2 THEN 14 WHEN 3 THEN 13 ............ WHEN 8 THEN 8 WHEN 9 THEN 7 WHEN 10 THEN 6 WHEN 11 THEN 5 WHEN 12 THEN 4 WHEN 13 THEN 3 WHEN 14 THEN 2 WHEN 15 THEN 1 END; RETURN result; END; $BODY$ LANGUAGE plpgsql VOLATILE – alinux Sep 13 '11 at 15:35
  • Please modify the question [by clicking on the edit link](http://stackoverflow.com/posts/7404262/edit) to provide new or more specific information about your question. Don't use comments to elaborate on your question. – SingleNegationElimination Sep 13 '11 at 16:45

1 Answers1

1

Instead of storing redundant data in the database (i.e. both finish_pl and points), you could just store finish_pl and create a view that in addition to the columns of game would also compute and include the points column.

NPE
  • 486,780
  • 108
  • 951
  • 1,012