on insert rule & primary key

[Scott Frankel <leknarf@xxxxxxxxxxx>]

My original post got eaten.  Apologies in advance if you receive this 
message twice.

I am trying to construct a rule that performs an UPDATE if specific 
conditions are met in an INSERT statement.  Limiting UPDATE's SET 
action to just the new row by testing for the new primary key is 
failing for some reason.  Yet if I eliminate the test, all rows in the 
table are updated.

The actual rule I'm building must handle several OR clauses in its 
conditional test, so I've included that in the following sample.  The 
output I would've expected would have both the Carlos and Miranda 
inserts yielding their favorite color, azul.

Any suggestions on how I can construct the rule to automatically and 
correctly fill the fav_color field?

Thanks in advance!
Scott




CREATE TABLE colors (
clrs_pkey     SERIAL    PRIMARY KEY,
first_name    text      UNIQUE DEFAULT NULL,
fav_color     text      DEFAULT NULL
);

CREATE RULE color_rule AS ON INSERT
TO ONLY colors
WHERE
first_name = 'carlos' OR
first_name = 'miranda'
DO UPDATE ONLY colors SET fav_color = 'azul'
WHERE clrs_pkey = NEW.clrs_pkey;

INSERT INTO colors (first_name, fav_color) VALUES ('carmen', 'verde');
INSERT INTO colors (first_name) VALUES ('carlos');
INSERT INTO colors (first_name, fav_color) VALUES ('rocio', 'rojo');
INSERT INTO colors (first_name, fav_color) VALUES ('miranda', 'negro');


test=> SELECT * FROM ONLY colors;
 clrs_pkey | first_name | fav_color
-----------+------------+-----------
         1 | carmen     | verde
         2 | carlos     |
         5 | rocio      | rojo
         6 | miranda    | negro
(4 rows)


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
     message can get through to the mailing list cleanly