This page in other versions: 9.0 / 9.1 / 9.2 / 9.3 / 9.4  |  Development versions: devel  |  Unsupported versions: 7.2 / 7.3 / 7.4 / 8.0 / 8.1 / 8.2 / 8.3 / 8.4

3.2. Views

Refer back to the queries in Section 2.6. Suppose the combined listing of weather records and city location is of particular interest to your application, but you do not want to type the query each time you need it. You can create a view over the query, which gives a name to the query that you can refer to like an ordinary table.

CREATE VIEW myview AS
    SELECT city, temp_lo, temp_hi, prcp, date, location
        FROM weather, cities
        WHERE city = name;

SELECT * FROM myview;

Making liberal use of views is a key aspect of good SQL database design. Views allow you to encapsulate the details of the structure of your tables, which may change as your application evolves, behind consistent interfaces.

Views can be used in almost any place a real table can be used. Building views upon other views is not uncommon.

Comments


Nov. 25, 2005, 2:34 a.m.

In addition to the description above, it is worth mentioning that liberal use of views, combined with users and proper use of restrictive permissions also increases the security side of applications; this is particularly for DB-enabled web-based applications.

You can restrict the data that a certain application can access by assigning a user to it, creating views, and revoking all access to the tables for that user -- you only grant SELECT privilege on the specific views that you create for each user.

Views help provide an "extra layer" of protection (in addition to good data validation and escaping to avoid SQL injection) -- if a clever enough malicious hacker does find a way to circumvent the validation measures you took, you can at least limit the amount of information that the SQL server will allow them to read. You could look at it as Views helping you enforce the least-privilege principle: restrict access to the absolutely minimum required for the application to work; not a single byte more.


April 12, 2006, 10:28 a.m.

Quote: Views can be used in almost any place a real table can be used.

Note: You cannot insert data into a table using a view for a number of reasons. Views are strong for SELECTion of data. They cannot be used to change data - the primary key columns which require values may not be part of the view.

This may add some interesting conclusions to the security discussion, like "don't expose your tables, only the views, which should be constructed so that they protect the sensitive key data in the tables".

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group