Search Postgresql Archives

Re: on insert rule & primary key

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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.



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux