Re: triggers vs "NEW" pseudorecord - Mailing list pgsql-general

On Sun, Mar 04, 2001 at 07:07:24AM -0500, Jan Wieck wrote:
> will trillich wrote:
> > so altho the docs elsewhere say NOT to rely on access to the
> > pseudo table NEW within a trigger function, this part does work
> > like it should. but when i add SELECT or UPDATE it complains of
> > "NEW used in non-RULE query" -- what's the distinction?
>
>     Can't  reproduce  such an error here - neither with 7.0.3 nor
>     with 7.1.  Could you please post  a  complete,  reproduceable
>     example   of   the   failure.    Tables,  functions,  trigger
>     declarations, queries.

many thanks for your reply! and now ai have a new question (of course)--

i think i figured out the original snag -- i moved my assignments
OUT of the DECLARE section, and everything got much better:

    declare
        x char(1) := NEW.afield from 1 for 1; -- BOOM ("new in non-rule query")

now i do

    declare
        x char(1);
    begin
        x := NEW.afield from 1 for 1; -- no worries, mate

and it works like a charm. (maybe this is a feature. :)

--

but now that i have it working, it's horrendously slow and i can't
figure out how or where to optimize my results...

    ratings table insertions propagate to
    -    course  table (which also links to course)
    -    topic   table (which also links to school)
    -    school  table
    -    prof    table (which also links to course and person)
    -    faculty table (which also links to school and person)
    -    student table

first i check to be sure each selector/seeker is legal, then i
update all six table, which seems like double work... any
suggestions are welcome:

    SELECT school.id
    INTO schoolID
        WHERE school.code = NEW.schoolcode;
    IF NOT FOUND THEN
        RAISE EXCEPTION ...;
    END IF;

    SELECT topic.id
    INTO topicID
        WHERE topic.code = NEW.topiccode AND topic.school = schoolID;
    IF NOT FOUND THEN
        RAISE EXCEPTION ...;
    END IF;

    SELECT course.id
    INTO courseID
        WHERE course.code = NEW.coursecode AND course.topic = topicID;
    IF NOT FOUND THEN
        RAISE EXCEPTION ...;
    END IF;

    SELECT person.id
    INTO facID -- all activity for this faculty member
        WHERE faculty.login = NEW.faclogin AND faculty.id = person.id AND prof.school = schoolID;
    IF NOT FOUND THEN
        RAISE EXCEPTION ...;
    END IF;

    SELECT person.id
    INTO profID -- feedback for this person teaching this course
        WHERE person.login = NEW.proflogin AND prof.id = person.id AND prof.course = courseID;
    IF NOT FOUND THEN
        RAISE EXCEPTION ...;
    END IF;

    SELECT person.id
    INTO stuID -- track feedback from this student
        WHERE person.login = NEW.stulogin AND student.id = person.id;
    IF NOT FOUND THEN
        RAISE EXCEPTION ...;
    END IF;

-- ---------------------------------------------------
-- and after all that, NOW we gotta UPDATE them all...
-- ---------------------------------------------------

IF feedback = ''A'' THEN
    UPDATE student SET a = a + 1 WHERE id = stuID;
    UPDATE faculty SET a = a + 1 WHERE id = facID  AND school = schoolID;
    UPDATE prof    SET a = a + 1 WHERE id = profID AND course = courseID;
    UPDATE school  SET a = a + 1 WHERE id = schoolID;
    UPDATE topic   SET a = a + 1 WHERE id = topicID AND school = schoolID;
    UPDATE course  SET a = a + 1 WHERE id = courseID AND topic = topicID;
//snip//for each grade type//
END IF


if any of y'all'uns have some ideas, i'd like to hear them--

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