inserting to a view -> cascading to other tables - Mailing list pgsql-general

hi. i've got a scenario i'd like to run past the pg/sql
experts...

short version: how can i have a trigger after insert, take the
fresh data and propagate changes to other tables in the database?

long version:

i'm using postgresql 7.0.3 on debian/potato -- and in trying to
implement a feedback mechanism for students to evaluate courses
and faculty, i'm running into trouble: i'd like to make a
trigger that propagates feedback into cumulative totals for
faculty, for courses, and for students... that is, when student
'miss scarlet' gives 'professor plum' an 'A', his record would be
updated to reflect that: i.e. "UPDATE _faculty SET a=a+1
WHERE..."

the alternative is having to run something like this
periodically, bringing everything to a screeching halt:
    SELECT rating,COUNT(rating)
    FROM _rating
    WHERE _rating.rating='a'
      AND _rating.who=_who.id
      AND _who.name='professor plum';
for each letter grade, for each professor. or, all at once:
    SELECT rating,COUNT(rating)
    FROM _rating
    WHERE _rating.who = _who.id
    GROUP BY rating,_who.name
and similar nausea for each course, and each student. ick!
hopefully i can avoid all that by propagating any insert to the
_ratings table, across _faculty, _student and _course tables...

here's the scoop:

i've got course tables, professor tables, and student tables; the
fancy part is the 'ratings' table which allows students to give
feedback to the professors, which also apply to the classes
themselves.

    CREATE TABLE _rating ( ... );

for the ratings table, i've got a view set up as a cross-linking
mechanism (viewing student and professor names, as opposed to id
numbers). this rule works correctly:

    CREATE VIEW rating AS SELECT ... ;

    CREATE RULE rating_insert AS ON INSERT TO rating
        WHERE
            _faculty.who = get_whoid( NEW.facultyname )
            AND
            _student.who = get_whoid( NEW.studentname )
            AND
            _course.id   =
                get_courseid(
                    get_topicid(NEW.topiccode),
                    NEW.coursecode
                )
    DO INSTEAD
        INSERT INTO
            _rating (
                who,
                course,
                student,
                anon,
                rating,
                comments
            ) VALUES (
                _faculty.who,
                _course.id,
                _student.who,
                NEW.anon,
                NEW.rating,
                NEW.comments
            )
        ;

that part works like a dream. (secondary question: is that the
way to do this? i've got a WHERE claus to set cursors, so that
_faculty.who, _course.id and _student.who reflect who we're
actually trying to cross-link. is there a better way?) i don't
think there's anything germane about having a rule that pulls a
trigger, but i may be mistaken, so i mention it... :)

the _rating.rating field is actually "letter grade" such as A, B,
C, D, or F. when a record is created, i want to update the
_faculty table, the _course table and the _student table:

    CREATE FUNCTION _rating_propagate( _rating ) RETURNS OPAQUE AS '
        DECLARE
            n ALIAS FOR $1;
            opinion   char(1) := upper(substring(n.rating from 1 for 1));
        BEGIN
            IF opinion = ''A'' THEN
    -- A == excellent
    UPDATE _student SET a = a + 1 WHERE _student.who = n.student;
    UPDATE _faculty SET a = a + 1 WHERE _faculty.who = n.who;
    UPDATE _course  SET a = a + 1 WHERE _course.id   = n.course;

[snipping other similar grading chunks]

            RETURN n;
        END;
    ' LANGUAGE 'plpgsql';

there seems to be no problem here, either. but now we get to the
trigger itself:

    CREATE TRIGGER _rating_propagate
        BEFORE INSERT ON _rating
        FOR EACH ROW EXECUTE PROCEDURE _rating_propagate( NEW );

altho there was an example in the /usr/share/doc/postgre*/ area
showing that NEW could be used in a trigger, psql (more likely,
the server) complains at the use of NEW in a non-rule item.

during preliminary stages i had the propagate thing working, but
there was enough else screwy that i didn't save its state. so now
when i insert into view "rating" the rule correctly inserts
relevant items into actual table "_rating" instead, but the
trigger doesn't propagate anything. any ideas?

--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
        -- Isaac Asimov, 'The Genetic Code'

will@serensoft.com
http://groups.yahoo.com/group/newbieDoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!