Hi All,
I recently had a need to have conditional update rules on a view. This didn't work too well:
CREATE RULE insert_one AS
ON INSERT TO one WHERE NEW.id IS NULL
DO INSTEAD (
INSERT INTO _simple (id, guid, state, name, description)
VALUES (NEXTVAL('seq_kinetic'), NEW.guid, NEW.state, NEW.name, NEW.description);
INSERT INTO simple_one (id, bool)
VALUES (CURRVAL('seq_kinetic'), NEW.bool);
);CREATE RULE promote_one AS
ON INSERT TO one WHERE NEW.id IS NOT NULL
DO INSTEAD (
UPDATE _simple
SET guid = NEW.guid, state = NEW.state, name = NEW.name, description = NEW.description
WHERE id = NEW.id;
INSERT INTO simple_one (id, bool) VALUES (NEW.ID, NEW.bool); );
I found this in the docs to explain the issue:
There is a catch if you try to use conditional rules for view updates: there must be an unconditional INSTEAD rule for each action you wish to allow on the view.
Well, I didn't have an unconditional update rule, so I added one without removing the other two:
CREATE RULE nothing_one AS ON INSERT TO one DO INSTEAD NOTHING;
And it worked! Now I can have an insert do an INSERT or UPDATE on another table magically.
But my question is this: Is this a known and supported behavior? If not, is it likely to change? If so, how is the order or rules evaluated when a query is sent to the database? Order of definition? Alphabetically?
TIA,
David
PS: Please Cc me in replies as I am not subscribed to the list. Thanks!
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
