Multiple RULES on Views

Started by David E. Wheeleralmost 21 years ago3 messagesgeneral
Jump to latest
#1David E. Wheeler
david@kineticode.com

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!

#2Sebastian Böck
sebastianboeck@freenet.de
In reply to: David E. Wheeler (#1)
Re: Multiple RULES on Views

David Wheeler wrote:

[...]

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?

Yes, this is the correct way to do updateable views.

Multiple rules on the same table and same event type are
applied in alphabetical name order.

See:
http://www.postgresql.org/docs/current/static/sql-createrule.html
for more details and the above quote.

HTH

Sebastian

#3David E. Wheeler
david@kineticode.com
In reply to: Sebastian Böck (#2)
Re: Multiple RULES on Views

On Apr 22, 2005, at 2:09 AM, Sebastian Böck wrote:

Multiple rules on the same table and same event type are
applied in alphabetical name order.

Ah, I'd missed that. Curious that it worked for me, then, since my
rules were named insert_one, promote_one, and nothing_one, as
nothing_one comes after promote_one alphabetically, and promote_one ran
but nothing_one did not...

Regards,

David