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