Thread: auto calculate?

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Posts
    8
    Rep Power
    0

    auto calculate?


    Newbie here.

    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
    calculate the

    points = '3'

    and further more how about percentages can pg convert entries say for

    ie:

    shots on goal = '10'

    compared to total goals automatically %20.0

    ?


    thanks alot!
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Posts
    56
    Rep Power
    13
    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.
  4. #3
  5. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Or better yet (and easier) just have your two fields in your base table, and define a view that handles the calculation:

    Define table:
    Code:
    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... */
    );
    Define view:
    Code:
    CREATE VIEW vw_mytable AS
    SELECT player, goals, assists, 
    (goals + assists) AS points
    FROM mytable
    ORDER BY player ASC;
    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.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Posts
    8
    Rep Power
    0

    Talking


    great stuff! exactly what I needed.

    I think I will really like postgres and the docs

IMN logo majestic logo threadwatch logo seochat tools logo