Views

Views are pseudo-tables. That is, they are not real tables, but nevertheless appear as ordinary tables to SELECT. A view can represent a subset of a real table, selecting certain columns or certain rows from an ordinary table. A view can even represent joined tables. Because views are assigned separate permissions, you can use them to restrict table access so that users see only specific rows or columns of a table.

Views are created using the CREATE VIEW command. Figure  shows the creation of several views.  

        test=> CREATE VIEW customer_ohio AS                
        test-> SELECT *
        test-> FROM customer
        test-> WHERE state = 'OH';
        CREATE 18908 1
        test=>
        test=> -- let sanders see only Ohio customers
        test=> GRANT SELECT ON customer_ohio TO sanders;
        CHANGE
        test=>
        test=> -- create view to show only certain columns
        test=> CREATE VIEW customer_address AS                
        test-> SELECT customer_id, name, street, city, state, zipcode, country
        test-> FROM customer;
        CREATE 18909 1
        test=>
        test=> -- create view that combines fields from two tables
        test=> CREATE VIEW customer_finance AS                
        test-> SELECT customer.customer_id, customer.name, finance.credit_limit
        test-> FROM customer, finance
        test-> WHERE customer.customer_id = finance.customer_id;
        CREATE 18910 1  

The view customer_ohio selects only customers from Ohio. SELECTs on it will therefore show only Ohio customers. The user sanders is then given SELECT access to the view. The customer_address will show only address information. The customer_finance view is a join  of customer and finance, showing columns from both tables.

DROP VIEW removes a view. Because views are not ordinary tables, INSERTs , UPDATEs , and DELETEs  on views have no effect. The next section shows how rules can correct this problem.