diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/intro.sgml | 2 | ||||
| -rw-r--r-- | doc/src/sgml/ref/alter_table.sgml | 8 | ||||
| -rw-r--r-- | doc/src/sgml/ref/alter_view.sgml | 9 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_rule.sgml | 25 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_view.sgml | 125 | ||||
| -rw-r--r-- | doc/src/sgml/rules.sgml | 47 |
6 files changed, 175 insertions, 41 deletions
diff --git a/doc/src/sgml/intro.sgml b/doc/src/sgml/intro.sgml index 4d3f93f3174..f0dba6f56fb 100644 --- a/doc/src/sgml/intro.sgml +++ b/doc/src/sgml/intro.sgml @@ -110,7 +110,7 @@ <simpara>triggers</simpara> </listitem> <listitem> - <simpara>views</simpara> + <simpara>updatable views</simpara> </listitem> <listitem> <simpara>transactional integrity</simpara> diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 356419e2d08..5437626c3fe 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -147,11 +147,9 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> <listitem> <para> These forms set or remove the default value for a column. - The default values only apply to subsequent <command>INSERT</command> - commands; they do not cause rows already in the table to change. - Defaults can also be created for views, in which case they are - inserted into <command>INSERT</> statements on the view before - the view's <literal>ON INSERT</literal> rule is applied. + Default values only apply in subsequent <command>INSERT</command> + or <command>UPDATE</> commands; they do not cause rows already in the + table to change. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/alter_view.sgml b/doc/src/sgml/ref/alter_view.sgml index 521f05b84a1..0e2b140241e 100644 --- a/doc/src/sgml/ref/alter_view.sgml +++ b/doc/src/sgml/ref/alter_view.sgml @@ -80,10 +80,11 @@ ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RESET <listitem> <para> These forms set or remove the default value for a column. - A default value associated with a view column is - inserted into <command>INSERT</> statements on the view before - the view's <literal>ON INSERT</literal> rule is applied, if - the <command>INSERT</> does not specify a value for the column. + A view column's default value is substituted into any + <command>INSERT</> or <command>UPDATE</> command whose target is the + view, before applying any rules or triggers for the view. The view's + default will therefore take precedence over any default values from + underlying relations. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/create_rule.sgml b/doc/src/sgml/ref/create_rule.sgml index d4c33921290..381ea3ed6b4 100644 --- a/doc/src/sgml/ref/create_rule.sgml +++ b/doc/src/sgml/ref/create_rule.sgml @@ -45,10 +45,10 @@ CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS additional commands to be executed when a given command on a given table is executed. Alternatively, an <literal>INSTEAD</literal> rule can replace a given command by another, or cause a command - not to be executed at all. Rules are used to implement table + not to be executed at all. Rules are used to implement SQL views as well. It is important to realize that a rule is really a command transformation mechanism, or command macro. The - transformation happens before the execution of the commands starts. + transformation happens before the execution of the command starts. If you actually want an operation that fires independently for each physical row, you probably want to use a trigger, not a rule. More information about the rules system is in <xref linkend="rules">. @@ -73,13 +73,11 @@ CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS sufficient for your purposes) to replace update actions on the view with appropriate updates on other tables. If you want to support <command>INSERT RETURNING</> and so on, then be sure to put a suitable - <literal>RETURNING</> clause into each of these rules. Alternatively, - an updatable view can be implemented using <literal>INSTEAD OF</> - triggers (see <xref linkend="sql-createtrigger">). + <literal>RETURNING</> clause into each of these rules. </para> <para> - There is a catch if you try to use conditional rules for view + There is a catch if you try to use conditional rules for complex view updates: there <emphasis>must</> be an unconditional <literal>INSTEAD</literal> rule for each action you wish to allow on the view. If the rule is conditional, or is not @@ -95,6 +93,21 @@ CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS <literal>INSTEAD NOTHING</literal> action. (This method does not currently work to support <literal>RETURNING</> queries, however.) </para> + + <note> + <para> + A view that is simple enough to be automatically updatable (see <xref + linkend="sql-createview">) does not require a user-created rule in + order to be updatable. While you can create an explicit rule anyway, + the automatic update transformation will generally outperform an + explicit rule. + </para> + + <para> + Another alternative worth considering is to use <literal>INSTEAD OF</> + triggers (see <xref linkend="sql-createtrigger">) in place of rules. + </para> + </note> </refsect1> <refsect1> diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml index 9e3bc2954f2..abbde94772c 100644 --- a/doc/src/sgml/ref/create_view.sgml +++ b/doc/src/sgml/ref/create_view.sgml @@ -128,17 +128,6 @@ CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW <replaceable class="PARAMETER">n <title>Notes</title> <para> - 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 <literal>INSTEAD</> triggers on the view, which - must convert attempted inserts, etc. on the view into - appropriate actions on other tables. For more information see - <xref linkend="sql-createtrigger">. Another possibility is to create - rules (see <xref linkend="sql-createrule">), but in practice triggers - are easier to understand and use correctly. - </para> - - <para> Use the <xref linkend="sql-dropview"> statement to drop views. </para> @@ -175,6 +164,105 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello; to replace it (this includes being a member of the owning role). </para> + <refsect2 id="SQL-CREATEVIEW-updatable-views"> + <title id="SQL-CREATEVIEW-updatable-views-title">Updatable Views</title> + + <indexterm zone="sql-createview-updatable-views"> + <primary>updatable views</primary> + </indexterm> + + <para> + Simple views are automatically updatable: the system will allow + <command>INSERT</>, <command>UPDATE</> and <command>DELETE</> statements + to be used on the view in the same way as on a regular table. A view is + automatically updatable if it satisfies all of the following conditions: + + <itemizedlist> + <listitem> + <para> + The view must have exactly one entry in its <literal>FROM</> list, + which must be a table or another updatable view. + </para> + </listitem> + + <listitem> + <para> + The view definition must not contain <literal>WITH</>, + <literal>DISTINCT</>, <literal>GROUP BY</>, <literal>HAVING</>, + <literal>LIMIT</>, or <literal>OFFSET</> clauses at the top level. + </para> + </listitem> + + <listitem> + <para> + The view definition must not contain set operations (<literal>UNION</>, + <literal>INTERSECT</> or <literal>EXCEPT</>) at the top level. + </para> + </listitem> + + <listitem> + <para> + All columns in the view's select list must be simple references to + columns of the underlying relation. They cannot be expressions, + literals or functions. System columns cannot be referenced, either. + </para> + </listitem> + + <listitem> + <para> + No column of the underlying relation can appear more than once in + the view's select list. + </para> + </listitem> + + <listitem> + <para> + The view must not have the <literal>security_barrier</> property. + </para> + </listitem> + </itemizedlist> + </para> + + <para> + If the view is automatically updatable the system will convert any + <command>INSERT</>, <command>UPDATE</> or <command>DELETE</> statement + on the view into the corresponding statement on the underlying base + relation. + </para> + + <para> + If an automatically updatable view contains a <literal>WHERE</> + condition, the condition restricts which rows of the base relation are + available to be modified by <command>UPDATE</> and <command>DELETE</> + statements on the view. However, an <command>UPDATE</> is allowed to + change a row so that it no longer satisfies the <literal>WHERE</> + condition, and thus is no longer visible through the view. Similarly, + an <command>INSERT</> command can potentially insert base-relation rows + that do not satisfy the <literal>WHERE</> condition and thus are not + visible through the view. + </para> + + <para> + A more complex view that does not satisfy all these conditions is + read-only by default: the system will not allow an insert, update, or + delete on the view. You can get the effect of an updatable view by + creating <literal>INSTEAD OF</> triggers on the view, which must + convert attempted inserts, etc. on the view into appropriate actions + on other tables. For more information see <xref + linkend="sql-createtrigger">. Another possibility is to create rules + (see <xref linkend="sql-createrule">), but in practice triggers are + easier to understand and use correctly. + </para> + + <para> + Note that the user performing the insert, update or delete on the view + must have the corresponding insert, update or delete privilege on the + view. In addition the view's owner must have the relevant privileges on + the underlying base relations, but the user performing the update does + not need any permissions on the underlying base relations (see + <xref linkend="rules-privileges">). + </para> + </refsect2> </refsect1> <refsect1> @@ -217,11 +305,15 @@ CREATE VIEW <replaceable class="parameter">name</replaceable> [ ( <replaceable c <term><literal>CHECK OPTION</literal></term> <listitem> <para> - This option has to do with updatable views. All - <command>INSERT</> and <command>UPDATE</> commands on the view - will be checked to ensure data satisfy the view-defining - condition (that is, the new data would be visible through the - view). If they do not, the update will be rejected. + This option controls the behavior of automatically updatable views. + When given, <command>INSERT</> and <command>UPDATE</> commands on + the view will be checked to ensure new rows satisfy the + view-defining condition (that is, the new rows would be visible + through the view). If they do not, the update will be rejected. + Without <literal>CHECK OPTION</literal>, <command>INSERT</> and + <command>UPDATE</> commands on the view are allowed to create rows + that are not visible through the view. (The latter behavior is the + only one currently provided by <productname>PostgreSQL</>.) </para> </listitem> </varlistentry> @@ -252,6 +344,7 @@ CREATE VIEW <replaceable class="parameter">name</replaceable> [ ( <replaceable c <command>CREATE OR REPLACE VIEW</command> is a <productname>PostgreSQL</productname> language extension. So is the concept of a temporary view. + The <literal>WITH</> clause is an extension as well. </para> </refsect1> diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml index cc02ada7c71..5811de7942f 100644 --- a/doc/src/sgml/rules.sgml +++ b/doc/src/sgml/rules.sgml @@ -808,13 +808,28 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; <para> What happens if a view is named as the target relation for an <command>INSERT</command>, <command>UPDATE</command>, or - <command>DELETE</command>? Simply doing the substitutions + <command>DELETE</command>? Doing the substitutions described above would give a query tree in which the result relation points at a subquery range-table entry, which will not - work. Instead, the rewriter assumes that the operation will be - handled by an <literal>INSTEAD OF</> trigger on the view. - (If there is no such trigger, the executor will throw an error - when execution starts.) Rewriting works slightly differently + work. There are several ways in which <productname>PostgreSQL</> + can support the appearance of updating a view, however. +</para> + +<para> + If the subquery selects from a single base relation and is simple + enough, the rewriter can automatically replace the subquery with the + underlying base relation so that the <command>INSERT</command>, + <command>UPDATE</command>, or <command>DELETE</command> is applied to + the base relation in the appropriate way. Views that are + <quote>simple enough</> for this are called <firstterm>automatically + updatable</>. For detailed information on the kinds of view that can + be automatically updated, see <xref linkend="sql-createview">. +</para> + +<para> + Alternatively, the operation may be handled by a user-provided + <literal>INSTEAD OF</> trigger on the view. + Rewriting works slightly differently in this case. For <command>INSERT</command>, the rewriter does nothing at all with the view, leaving it as the result relation for the query. For <command>UPDATE</command> and @@ -842,10 +857,8 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; </para> <para> - If there are no <literal>INSTEAD OF</> triggers to update the view, - the executor will throw an error, because it cannot automatically - update a view by itself. To change this, we can define rules that - modify the behavior of <command>INSERT</command>, + Another possibility is for the user to define <literal>INSTEAD</> + rules that specify substitute actions for <command>INSERT</command>, <command>UPDATE</command>, and <command>DELETE</command> commands on a view. These rules will rewrite the command, typically into a command that updates one or more tables, rather than views. That is the topic @@ -860,6 +873,22 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a; evaluated first, and depending on the result, the triggers may not be used at all. </para> + +<para> + Automatic rewriting of an <command>INSERT</command>, + <command>UPDATE</command>, or <command>DELETE</command> query on a + simple view is always tried last. Therefore, if a view has rules or + triggers, they will override the default behavior of automatically + updatable views. +</para> + +<para> + If there are no <literal>INSTEAD</> rules or <literal>INSTEAD OF</> + triggers for the view, and the rewriter cannot automatically rewrite + the query as an update on the underlying base relation, an error will + be thrown because the executor cannot update a view as such. +</para> + </sect2> </sect1> |