From: Ron Peterson <rpeterso@mtholyoke.edu>
To: pgsql-sql@postgresql.org
Subject: insert rule doesn't see id field
Date: Tue, 7 Jan 2003 09:54:22 -0500
Message-ID: <20030107145422.GE12245@mtholyoke.edu> (raw)
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 ----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rpeterso@mtholyoke.edu
Subject: Re: insert rule doesn't see id field
In-Reply-To: <20030107145422.GE12245@mtholyoke.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox