triggers vs "NEW" pseudorecord

From: will trillich <will(at)serensoft(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: triggers vs "NEW" pseudorecord
Date: 2001-03-04 03:20:15
Message-ID: 20010303212015.A2019@mail.serensoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Christopher Sawtell 2001-03-04 06:49:35 Re: AW: AW: Addison-Wesley looking for authors
Previous Message Josh Rovero 2001-03-04 01:10:54 Re: PostgreSQL 7 on Solaris 8 - Is it working?