From cab5dc5daf2f6f5da0ce79deb399633b4bb443b5 Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Fri, 18 Oct 2013 10:35:36 -0400
Subject: Allow only some columns of a view to be auto-updateable.

Previously, unless all columns were auto-updateable, we wouldn't
inserts, updates, or deletes, or at least not without a rule or trigger;
now, we'll allow inserts and updates that target only the auto-updateable
columns, and deletes even if there are no auto-updateable columns at
all provided the view definition is otherwise suitable.

Dean Rasheed, reviewed by Marko Tiikkaja
---
 doc/src/sgml/ref/create_view.sgml | 39 +++++++++++++++++++++++++++++----------
 1 file changed, 29 insertions(+), 10 deletions(-)

(limited to 'doc/src')

diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml
index 8102ec2fd1b..e0fbe1e5e50 100644
--- a/doc/src/sgml/ref/create_view.sgml
+++ b/doc/src/sgml/ref/create_view.sgml
@@ -319,16 +319,8 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
 
      <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.
+       The view's select list must not contain any aggregates, window functions
+       or set-returning functions.
       </para>
      </listitem>
 
@@ -340,6 +332,14 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
     </itemizedlist>
    </para>
 
+   <para>
+    An automatically updatable view may contain a mix of updatable and
+    non-updatable columns.  A column is updatable if it is a simple reference
+    to an updatable column of the underlying base relation; otherwise the
+    column is read-only, and an error will be raised if an <command>INSERT</>
+    or <command>UPDATE</> statement attempts to assign a value to it.
+   </para>
+
    <para>
     If the view is automatically updatable the system will convert any
     <command>INSERT</>, <command>UPDATE</> or <command>DELETE</> statement
@@ -434,6 +434,25 @@ CREATE VIEW pg_comedies AS
    <literal>classification</> of new rows.
   </para>
 
+  <para>
+   Create a view with a mix of updatable and non-updatable columns:
+
+<programlisting>
+CREATE VIEW comedies AS
+    SELECT f.*,
+           country_code_to_name(f.country_code) AS country,
+           (SELECT avg(r.rating)
+            FROM user_ratings r
+            WHERE r.film_id = f.id) AS avg_rating
+    FROM films f
+    WHERE f.kind = 'Comedy';
+</programlisting>
+   This view will support <command>INSERT</>, <command>UPDATE</> and
+   <command>DELETE</>.  All the columns from the <literal>films</> table will
+   be updatable, whereas the computed columns <literal>country</> and
+   <literal>avg_rating</> will be read-only.
+  </para>
+
   <para>
    Create a recursive view consisting of the numbers from 1 to 100:
 <programlisting>
-- 
cgit v1.2.3