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 ----
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
