insert rule doesn't see id field

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

Responses

Browse pgsql-patches by date

  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...

Browse pgsql-sql by date

  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