July 28th, 2002, 10:01 AM
I was wondering how to do something like this.
values entered for example into
goals = '2' , assists = '1'
and in the same table
I would like too have the DB automatically
points = '3'
and further more how about percentages can pg convert entries say for
shots on goal = '10'
compared to total goals automatically %20.0
July 28th, 2002, 01:02 PM
Yes, use TRIGGERs together with FUNCTIONs.
CREATE TRIGGER trigger_name
AFTER INSERT OR UPDATE ON table_name FOR EACH ROW
EXECUTE PROCEDURE function_name(goals, assists);
Check out the docs for more info.
July 28th, 2002, 05:32 PM
Or better yet (and easier) just have your two fields in your base table, and define a view that handles the calculation:
CREATE TABLE mytable(
player varchar(32) NOT NULL, /* I'm guessing you'll want this */
goals int NOT NULL,
assists int NOT NULL
/* etc... any other columns you want... */
This means whenever you change value in 'goals' or 'assists', you don't have to re-calculate a field. Any amount of calculations you want can be dealt with this way. Calculated fields in general should be avoided from base tables, because the proper place to handle them is usually in views. This is one of the rules of database normalization.
CREATE VIEW vw_mytable AS
SELECT player, goals, assists,
(goals + assists) AS points
ORDER BY player ASC;
July 28th, 2002, 09:38 PM
great stuff! exactly what I needed.
I think I will really like postgres and the docs