[OT] postgresql triggers

[will trillich <will@serensoft.com>]

i'm posting questions like this to the postgresql-general list,
and am deafened by the silence, so i'll try here. (you folks
don't know when to quit when it comes to helping newbies... :)

postgresql 7.0.3 / potato

direct from the documentation (.../postgresql-doc/html/user/c40874340.htm)
here's a function/procedure and trigger to call it:

CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS '
    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();

and when inserting to the table, the trigger is activated, the
function is called, and the pseudo table NEW.* is correctly
populated with values intended for the insertion.

when i expand this and try to add a SELECT on other tables, or an
UPDATE to another table or two, postgresql gripes about

	NEW used in non-RULE query...

as if you can only rely on NEW.* in functions and sql statements
called by rules, as opposed to triggers.

so --

how can i call UPDATE or SELECT within a trigger-activated
function BEFORE the insert? (or, what am i not grokking?)

-- 
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!