SEO

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

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/curre...reaterule.html
for more details and the above quote.

HTH

Sebastian

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings