From 917bbebf7ffd4466e1eeaba70b71fb60423e3ece Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 2 Sep 2006 17:06:52 +0000 Subject: Apply a simple solution to the problem of making INSERT/UPDATE/DELETE RETURNING play nice with views/rules. To wit, have the rule rewriter rewrite any RETURNING clause found in a rule to produce what the rule's triggering query asked for in its RETURNING clause, in particular drop the RETURNING clause if no RETURNING in the triggering query. This leaves the responsibility for knowing how to produce the view's output columns on the rule author, without requiring any fundamental changes in rule semantics such as adding new rule event types would do. The initial implementation constrains things to ensure that there is exactly one, unconditionally invoked RETURNING clause among the rules for an event --- later we might be able to relax that, but for a post feature freeze fix it seems better to minimize how much invention we do. Per gripe from Jaime Casanova. --- doc/src/sgml/ref/create_rule.sgml | 28 ++++++++++++++++++--- doc/src/sgml/rules.sgml | 52 +++++++++++++++++++++++++++------------ 2 files changed, 60 insertions(+), 20 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/create_rule.sgml b/doc/src/sgml/ref/create_rule.sgml index 1ab861ddf17..9380ab5a184 100644 --- a/doc/src/sgml/ref/create_rule.sgml +++ b/doc/src/sgml/ref/create_rule.sgml @@ -1,5 +1,5 @@ @@ -70,7 +70,9 @@ CREATE [ OR REPLACE ] RULE name AS ON INSERT, ON UPDATE, and ON DELETE rules (or any subset of those that's sufficient for your purposes) to replace update actions on the view - with appropriate updates on other tables. + with appropriate updates on other tables. If you want to support + INSERT RETURNING and so on, then be sure to put a suitable + RETURNING clause into each of these rules. @@ -87,7 +89,8 @@ CREATE [ OR REPLACE ] RULE name AS understands it will never be called on to update the dummy table. Then make the conditional rules non-INSTEAD; in the cases where they are applied, they add to the default - INSTEAD NOTHING action. + INSTEAD NOTHING action. (This method does not + currently work to support RETURNING queries, however.) @@ -201,13 +204,30 @@ CREATE [ OR REPLACE ] RULE name AS be allowed to define a rule on it. + + In a rule for INSERT, UPDATE, or + DELETE on a view, you can add a RETURNING + clause that emits the view's columns. This clause will be used to compute + the outputs if the rule is triggered by an INSERT RETURNING, + UPDATE RETURNING, or DELETE RETURNING command + respectively. When the rule is triggered by a command without + RETURNING, the rule's RETURNING clause will be + ignored. The current implementation allows only unconditional + INSTEAD rules to contain RETURNING; furthermore + there can be at most one RETURNING clause among all the rules + for the same event. (This ensures that there is only one candidate + RETURNING clause to be used to compute the results.) + RETURNING queries on the view will be rejected if + there is no RETURNING clause in any available rule. + + It is very important to take care to avoid circular rules. For example, though each of the following two rule definitions are accepted by PostgreSQL, the SELECT command would cause PostgreSQL to report an error because - the query cycled too many times: + of recursive expansion of a rule: CREATE RULE "_RETURN" AS diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml index ad5a2d4bfea..84cf3bce622 100644 --- a/doc/src/sgml/rules.sgml +++ b/doc/src/sgml/rules.sgml @@ -1,4 +1,4 @@ - + The Rule System @@ -873,7 +873,7 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; - They can be INSTEAD or ALSO (default). + They can be INSTEAD or ALSO (the default). @@ -920,7 +920,8 @@ CREATE [ OR REPLACE ] RULE name AS Initially the query-tree list is empty. There can be zero (NOTHING key word), one, or multiple actions. To simplify, we will look at a rule with one action. This rule - can have a qualification or not and it can be INSTEAD or ALSO (default). + can have a qualification or not and it can be INSTEAD or + ALSO (the default). @@ -932,22 +933,13 @@ CREATE [ OR REPLACE ] RULE name AS - So we have four cases that produce the following query trees for + So we have three cases that produce the following query trees for a one-action rule. - No qualification and ALSO - - - the query tree from the rule action with the original query - tree's qualification added - - - - - - No qualification but INSTEAD + No qualification, with either ALSO or + INSTEAD the query tree from the rule action with the original query @@ -1283,7 +1275,7 @@ SELECT shoelace_data.sl_name, 0, A simple way to protect view relations from the mentioned possibility that someone can try to run INSERT, UPDATE, or DELETE on them is - to let those query trees get thrown away. So we create the rules + to let those query trees get thrown away. So we could create the rules CREATE RULE shoe_ins_protect AS ON INSERT TO shoe @@ -1338,6 +1330,34 @@ CREATE RULE shoelace_del AS ON DELETE TO shoelace + + If you want to support RETURNING queries on the view, + you need to make the rules include RETURNING clauses that + compute the view rows. This is usually pretty trivial for views on a + single table, but it's a bit tedious for join views such as + shoelace. An example for the insert case is + + +CREATE RULE shoelace_ins AS ON INSERT TO shoelace + DO INSTEAD + INSERT INTO shoelace_data VALUES ( + NEW.sl_name, + NEW.sl_avail, + NEW.sl_color, + NEW.sl_len, + NEW.sl_unit + ) + RETURNING + shoelace_data.*, + (SELECT shoelace_data.sl_len * u.un_fact + FROM unit u WHERE shoelace_data.sl_unit = u.un_name); + + + Note that this one rule supports both INSERT and + INSERT RETURNING queries on the view — the + RETURNING clause is simply ignored for INSERT. + + Now assume that once in a while, a pack of shoelaces arrives at the shop and a big parts list along with it. But you don't want -- cgit v1.2.3