Re: on insert rule & primary key

Problem solved. Hacking away 'til the wee hours yielded a solution
using an ON UPDATE rule, adding a row to a new table. Successful test
sample follows, for anyone interested.

Scott

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

CREATE TABLE mono (
mono_pkey SERIAL PRIMARY KEY,
clrs_pkey integer REFERENCES colors,
monochrome text DEFAULT NULL
);

CREATE RULE mono_rule
AS ON UPDATE TO colors
WHERE
NEW.fav_color = 'blanco' OR
NEW.fav_color = 'negro'
DO INSERT INTO mono
(clrs_pkey, monochrome) VALUES (NEW.clrs_pkey, 'mono')
;

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

UPDATE ONLY colors SET fav_color = 'blanco' WHERE clrs_pkey = 1;
UPDATE ONLY colors SET fav_color = 'negro' WHERE clrs_pkey = 3;

test=> SELECT * FROM mono;
mono_pkey | clrs_pkey | monochrome
-----------+-----------+------------
1 | 1 | mono
2 | 3 | mono
(2 rows)

On Apr 27, 2005, at 1:20 PM, Scott Frankel wrote:

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