Updatable views

Posted by Ivan / on 01/23/2009 / 1 Comment

PostgreSQL has had updatable views for a long time, via the rules system, but a commit from yesterdat (by Bernd Helmle) provides some automatic view update rules. It means that in PostgreSQL some views will be automatically updatable and commands like INSERT, UPDATE, and DELETE can be used on the view directly. A view is updatable if it does not contain:

  • more than one underlying table (joins) or no underlying table at all
  • underlying tables/views that are themselves not updatable
  • subqueries in the FROM list
  • items in the select list that are not direct references to a column of the underlying table, such as literals or any nontrivial value expression
  • references to system columns in the select list
  • more than one reference to the same column in the select list
  • aggregate function calls
  • window function calls
  • WITH or WITH RECURSIVE clauses
  • DISTINCT, GROUP BY, or HAVING clauses
  • UNION, INTERSECT, or EXCEPT clauses ; LIMIT or OFFSET clauses

A nice twist is that a view can be based on another view and still be updatable, e.g.

CREATE TABLE vutest1 (a integer, b text); -- the next three view are updatable and the last two are based on another view CREATE VIEW vutestv1 AS SELECT a, b FROM vutest1; CREATE VIEW vutestv20 AS SELECT a AS x, b AS y FROM vutestv1; CREATE VIEW vutestv21 AS SELECT x AS a FROM vutestv20 WHERE x % 2 = 0; INSERT INTO vutestv21 VALUES (2); INSERT INTO vutestv21 VALUES (3); -- will fail

 



 

 

 

RSS Feed for this Blog    Comments Feed for this Post   

Comments

  • Ivan says:

    As of Jan. 28 the commit has been reversed :)

    January 28, 2009 at 2:09 PM | Permalink

 

Join this Group Now!

Forgot Password?

Bulgarian PostgreSQL User Group
Powered by Groupsite.com

Visibility Limited Membership By Invitation or Approved Request Default Profile Professional

Your Status Not Logged-In