Re: triggers vs "NEW" pseudorecord

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!