From: Ron Peterson <rpeterso@mtholyoke.edu>
Date: Tue, 7 Jan 2003 09:54:22 -0500
To: pgsql-sql@postgresql.org
Subject: insert rule doesn't see id field
Message-ID: <20030107145422.GE12245@mtholyoke.edu>
Mail-Followup-To: pgsql-sql@postgresql.org
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.3.28i
Organization: Mount Holyoke College

Two seperate problems, really, but first the SQL:

CREATE SEQUENCE person_id_seq;
CREATE TABLE person (

    name_last           VARCHAR( 50 )
                        NOT NULL,

    name_first          VARCHAR( 50 )
                        NOT NULL,

    id                  INTEGER
                        DEFAULT nextval( 'person_id_seq' )
                        PRIMARY KEY
);
CREATE INDEX person_name_idx ON person ( name_last, name_first );

CREATE TRIGGER person_id_noup
    BEFORE UPDATE ON person
    FOR EACH ROW
    EXECUTE PROCEDURE noup( 'id' );

CREATE RULE person_insert AS
ON INSERT TO person
DO
    INSERT INTO person_log ( name_last, name_first, mod_type, person_id )
    VALUES ( new.name_last, new.name_first, 'I', new.id );

(Problem 1)

My insert rule creates a record in person_log just fine.  It inserts
values for all of the fields except person_id.  Why doesn't new.id
contain a value?  Corresponding update and delete rules work as
expected.

(Problem 2)

I thought that the idea behind noup was to protect single columns from
update.  However, when I apply the noup trigger as above, I can't
update /any/ column.  Is this the intended behaviour?

e.g.

directory=# select * from person;
 name_last | name_first | id
-----------+------------+----
 Peterson  | Ronald     |  1
 Humbert   | Humbert    |  2
(2 rows)

directory=# update person set name_first='Ron' where name_first='Ronald';
NOTICE:  id: update not allowed
UPDATE 0

-- 
Ron Peterson                          -o)
Network & Systems Manager             /\\
Mount Holyoke College                _\_v
http://www.mtholyoke.edu/~rpeterso   ----