Multiple RULES on Views

From: David Wheeler <david(at)kineticode(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Multiple RULES on Views
Date: 2005-04-22 06:45:30
Message-ID: d3f55b978c88af2305bd64aefc1d5fb7@kineticode.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Patrick.FICHE 2005-04-22 07:36:06 Re: Use of temporary tables in functions
Previous Message David Wheeler 2005-04-22 06:31:04 Re: Waiting for Disconnect