Re: Rule for updating through a view.
От | Christoph Haller |
---|---|
Тема | Re: Rule for updating through a view. |
Дата | |
Msg-id | 3E7F2C01.E012A174@rodos.fzk.de обсуждение исходный текст |
Ответ на | Rule for updating through a view. (Mike Meyer <mwm@mired.org>) |
Ответы |
Re: Rule for updating through a view.
(Mike Meyer <mwm@mired.org>)
|
Список | pgsql-sql |
> > Ok, I found the documentation on using views to udpate the underlying > database. But I can't seem to get my head around making it actually > work, because updates may change only a few columns, and the columns > in my views come from multiple tables. > > Could someone provide an example (CC'ing me, please, as I'm not on the > list) of setting up a rule on a joined view to udpated both the > underlying tables? > PostgreSQL 7.3.2 Documentation CREATE VIEW ... Currently, views are read only: the system will not allow an insert, update, or delete on a view. You can get the effect of an updatable view by creating rules that rewrite inserts, etc. on the view into appropriate actions on other tables. For more information see CREATE RULE. CREATE RULE ... Rules and Views Presently, ON SELECT rules must be unconditional INSTEAD rules and must have actions that consist of a single SELECT query. Thus, an ON SELECT rule effectively turns the table into a view, whose visible contents are the rows returned by the rule's SELECT query rather than whatever had been stored in the table (if anything). It is considered better style to write a CREATE VIEW command than to create a real table and define an ON SELECT rule for it. CREATE VIEW creates a dummy table (with no underlying storage) and associates an ON SELECT rule with it. The system will not allow updates to the view, since it knows there is no real table there. You can create the illusion of an updatable view by defining 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. 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. If the rule is conditional, or is not INSTEAD, then the system will still reject attempts to perform the update action, because it thinks it might end up trying to perform the action on the dummy table in some cases. If you want to handle all the useful cases in conditional rules, you can; just add an unconditional DO INSTEAD NOTHING rule to ensure that the system understands it will never be called on to update the dummy table. Then make the conditional rules non-INSTEAD; in the cases where they fire, they add to the default INSTEAD NOTHING action. Is it this what you've found and can't get to work? Regards, Christoph
В списке pgsql-sql по дате отправления: