The following is the syntax for creating a view:
CREATE VIEW view AS query
Table 4-7. CREATE VIEW Syntax
SQL command (keyword)
The name (identifier) of the view that you wish to create
SQL clause (keyword)
The complete SQL SELECT query which defines the content of the view
For example, imagine that you have a table called shipments , which relates a unique shipping identifier with
Table "invoice" Attribute | Type | Modifier -------------+---------+---------- invoice_num | integer | total_cost | money | date | date |
There are several ways that we can achieve the results we are looking for, but to keep things simple, we will use a query like this:
select count(oid) from invoice;
The previous query will result in the following output:
booktown=# SELECT COUNT(oid) FROM invoice; count ------- 7 (1 row)
The oid column is a system column that represents a unique identifier for each record inserted into the table. The query counts the number of oid results that are returned from the query and thus the number of records.
The next query we will create expands on the previous query. This time we are going to count the number of sales and display the total dollars that those sales are worth.
SELECT COUNT(oid),SUM(total_cost) AS total_sales FROM invoice;
This query will display the following:
count | total_sales -------+------------- 7 | $323.00 (1 row)
Using this query we now know how many sales we have made and the total worth of those sales. This is a simple but useful query. However, what could potentially make this more powerful is to make it a view. Using the create view command we will create a view called inv_totals. Initially the view will perform the same function as the previous query.
CREATE VIEW inv_totals AS SELECT COUNT(oid),SUM(total_cost) AS total_sales FROM invoice;
We now have a view called inv_totals that will show us the us the number of sales and the total dollars made from those sales. The difference is that instead of having to type a long query, we only have to type the following simple command:
SELECT * FROM inv_totals
The view is currently pretty useful if a bit simple. We can expand the usefulness of the View by adding the date column to the query as well. You can't alter views therefore we will have to drop the view and recreate the view.
Example 4-34. The inv_totals View
CREATE VIEW inv_totals AS SELECT COUNT(oid), SUM(total_cost), date AS total_sales FROM invoice GROUP BY date;
We added two items to this view. The first was the addition of the date column and the second was the edition of the GROUP BY clause. We had to add the GROUP BY because COUNT() and SUM() are both aggregate functions. The date column with out an aggregrate function is not valid due to the grouping that occurs within an aggregate function. By adding GROUP BY we have created an aggregate with date which makes the query valid.
To give you an illustrated example of the aggregation caused by the view lets look at the records within the invoice table.
Example 4-35. Aggregating View
booktown=# select * from invoice; invoice_num | total_cost | date -------------+------------+------------ 0 | $56.00 | 2001-07-26 0 | $56.00 | 2001-07-26 0 | $56.00 | 2001-07-25 0 | $46.00 | 2001-07-25 0 | $47.00 | 2001-07-25 0 | $50.00 | 2001-07-27 1 | $12.00 | 2001-07-27 (7 rows) booktown=# select * from inv_totals; count | sum | total_sales -------+---------+------------- 3 | $149.00 | 2001-07-25 2 | $112.00 | 2001-07-26 2 | $62.00 | 2001-07-27 (3 rows)
If you look at the data within the table you will notice that their are several values within the date column that are similar. Next, Example 4-35 views the output of the view that was created.
As you can see, the view shows the different information than the query. This is caused by the way a view aggregates information. Since we grouped the date column, the view will automatically take each row that has the same date and group them together.