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=> -- let sanders see only Ohio customers
test=> GRANT SELECT ON customer_ohio TO sanders;
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=> -- 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.