Re: [GENERAL] 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 SERIALPRIMARY KEY,
first_nametext UNIQUE DEFAULT NULL,
fav_color text DEFAULT NULL
);
CREATE TABLE mono (
mono_pkey SERIALPRIMARY KEY,
clrs_pkey integer REFERENCES colors,
monochrometext 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
[GENERAL] on insert rule & primary key
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 SERIALPRIMARY KEY,
first_nametext 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 [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
