Thread: triggers vs "NEW" pseudorecord

okay. postgres 7.0.3 here, on debian potato/stable.

from the docs at /usr/share/doc/postgresql-doc/user/c40874340.html
here is a working trigger:

CREATE TABLE emp (
    empname text,
    salary int4,
    last_date datetime,
    last_user name);

CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS ' -- <= missing quote!
    BEGIN
        -- Check that empname and salary are given
        IF NEW.empname ISNULL THEN
            RAISE EXCEPTION ''empname cannot be NULL value'';
        END IF;
        IF NEW.salary ISNULL THEN
            RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
        END IF;

        -- Who works for us when she must pay for?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;
        END IF;

        -- Remember who changed the payroll when
        NEW.last_date := ''now'';
        NEW.last_user := getpgusername();
        RETURN NEW;
    END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

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?

what types of operations are NOT LEGAL within such a
trigger-invoked function? (i'd like to be able to UPDATE other
tables and SELECT from various tables within the function. bad
dog?)

will trillich wrote:
> okay. postgres 7.0.3 here, on debian potato/stable.

    OK - so far.

> [...]
>
> 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.

>
> what types of operations are NOT LEGAL within such a
> trigger-invoked function? (i'd like to be able to UPDATE other
> tables and SELECT from various tables within the function. bad
> dog?)

    That's definitely possible and the PL/pgSQL  regression  test
    suite does it.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

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!