From: | Ron Peterson <rpeterso(at)mtholyoke(dot)edu> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | insert rule doesn't see id field |
Date: | 2003-01-07 14:54:22 |
Message-ID: | 20030107145422.GE12245@mtholyoke.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-patches pgsql-sql |
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 ----
From | Date | Subject | |
---|---|---|---|
Next Message | Ron Peterson | 2003-01-07 14:57:45 | Re: insert rule doesn't see id field |
Previous Message | Lee Kindness | 2003-01-07 11:20:32 | Re: PostgreSQL libraries - PThread Support, but not use... |
From | Date | Subject | |
---|---|---|---|
Next Message | Ron Peterson | 2003-01-07 14:57:45 | Re: insert rule doesn't see id field |
Previous Message | Tomasz Myrta | 2003-01-07 14:29:10 | Re: A problem about alter table |