On 8th of December, Tom Lane committed patch:

Support automatically-updatable views.
 
This patch makes "simple" views automatically updatable, without the need
to create either INSTEAD OF triggers or INSTEAD rules.  "Simple" views
are those classified as updatable according to SQL-92 rules.  The rewriter
transforms INSERT/UPDATE/DELETE commands on such views directly into an
equivalent command on the underlying table, which will generally have
noticeably better performance than is possible with either triggers or
user-written rules.  A view that has INSTEAD OF triggers or INSTEAD rules
continues to operate the same as before.
 
For the moment, security_barrier views are not considered simple.
Also, we do not support WITH CHECK OPTION.  These features may be
added in future.
 
Dean Rasheed, reviewed by Amit Kapila

PostgreSQL has writable views for more or less forever. But, they were achievable using not really userfriendly ways. At first, there were rules. These can do the job, but some people (me included) think that rules should be removed from PostgreSQL, buried and forgotten.

Then, in 9.1 we got INSTEAD triggers on views. Which were great, but it's definitely not an automatic way to handle writes to views.

Now, thanks to this patch, we will be able to have automatically modifiable views. How automatically? Let's see:

$ CREATE TABLE test AS SELECT i AS id, 'depesz #'::text || i AS codename FROM generate_series(1,10) AS i;
SELECT 10
 
$ \d test
      TABLE "public.test"
  COLUMN  |  TYPE   | Modifiers
----------+---------+-----------
 id       | INTEGER |
 codename | text    |
 
$ CREATE VIEW z AS SELECT * FROM test;
CREATE VIEW
 
$ \d z
        VIEW "public.z"
  COLUMN  |  TYPE   | Modifiers
----------+---------+-----------
 id       | INTEGER |
 codename | text    |
 
$ UPDATE z SET codename = 'a' || codename;
UPDATE 10
 
$ SELECT * FROM z;
 id |  codename
----+-------------
  1 | adepesz #1
  2 | adepesz #2
  3 | adepesz #3
  4 | adepesz #4
  5 | adepesz #5
  6 | adepesz #6
  7 | adepesz #7
  8 | adepesz #8
  9 | adepesz #9
 10 | adepesz #10
(10 ROWS)

Looks great. But the commit log mentioned “simple". How simple? In documentation we can find list of rules:

• The view must have exactly one entry in its FROM, which must be a table or another updatable view.
• The view definition must not contain WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clauses at the top level.
• The view definition must not contain set operations (UNION, INTERSECT or EXCEPT) at the top level.
• 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.
• No column of the underlying relation can appear more than once in the view's select list.
• The view must not have the security_barrier property.

Unfortunately, it means that all “cool" views cannot be automatically writable, but that's understandable. Even something as “simple" as:

SELECT * FROM a JOIN b USING (c);

can be ambiguous.

But – as long as your view matches above criteria – it is automatically made writable.

There is also one more cool thing about it. Just as with SELECTs from views – user that uses the view doesn't have to have privileges to underlying table, just has to have privileges to use the view (and view owner has to have privileges to use underlying table).

This means that it is now possible to trivially add rather safe auditing. For example we can store information about who inserted given row, but the user will never see the data:

$ CREATE TABLE DATA (
    id serial PRIMARY KEY,
    inserted_by text NOT NULL DEFAULT CURRENT_USER,
    inserted_on timestamptz NOT NULL DEFAULT now(),
    column1 text
);
CREATE TABLE
 
$ CREATE VIEW data_visible AS
    SELECT id, column1 FROM DATA;
CREATE VIEW

Now, I grant all privileges to data_visible view to test user:

$ GRANT INSERT, UPDATE, DELETE, SELECT ON data_visible TO test;
GRANT
 
$ GRANT usage ON SEQUENCE data_id_seq TO test;
GRANT

Now, when user test inserts rows, we get following situation:

$ \c - test
You are now connected TO DATABASE "depesz" AS USER "test".
 
$ INSERT INTO data_visible (column1) VALUES ('test it');
INSERT 0 1
 
$ SELECT * FROM data_visible;
 id | column1
----+---------
  1 | test it
(1 ROW)
 
$ SELECT * FROM DATA;
ERROR:  permission denied FOR relation DATA
 
$ \c - depesz
You are now connected TO DATABASE "depesz" AS USER "depesz".
 
$ SELECT * FROM DATA;
 id | inserted_by |          inserted_on          | column1
----+-------------+-------------------------------+---------
  1 | test        | 2012-12-11 13:30:11.336758+01 | test it
(1 ROW)

That is – user test can insert data, and it's automatically “stamped", but the stamp is not visible to user.

And that's about it. Great step forward, and a very important feature. Big thanks to author and reviewer – great job!