8.2 Working with Views

The way you look at your data affects the way you can process the data in your database. Looking at your data starts with building a model of a piece of reality and does not end with writing queries.

Certain database models allow certain ways of accessing the data. If the model of your data is a good one, you will be able to retrieve the information you need in most cases. However, in some situations it is necessary to change the way you look at your data. In that case, you should consider working with views.

Views are virtual tables that can be used to select data just as you would with ordinary tables. For the user a view is a table that contains the result of a SELECT statement. This way SELECT statements can be used on top of SELECT statements that appear as views. In this section you will learn to work with views efficiently and you will see how you can query a database efficiently using views.

8.2.1 Creating and Removing Views

To create a view, PostgreSQL provides a command called CREATE VIEW. The syntax of this command is displayed in the next listing:

 phpbook=# \h CREATE VIEW Command:     CREATE VIEW Description: define a new view Syntax: CREATE VIEW view [ ( column name list ) ] AS SELECT query 

As you can see, the view is defined as the result of a SELECT statement. Let's try an example:

 phpbook=# CREATE TABLE product (id int4, name text, stored int4); CREATE 

The table consists of three columns. The first column contains a product id, the second column is used to store the name of a product, and the third column contains the number of products that are still in the store.

Let's insert some records into the table:

 phpbook=# INSERT INTO product VALUES (1, 'umbrella', 23); INSERT 24891 1 phpbook=# INSERT INTO product VALUES (2, 'carpet', 0); INSERT 24892 1 phpbook=# INSERT INTO product VALUES (3, 'flowerpot', 3); INSERT 24893 1 

Three products have been inserted into the table:

 phpbook=# SELECT * FROM product;  id |   name    | stored ----+-----------+--------   1 | umbrella  |     23   2 | carpet    |      0   3 | flowerpot |      3 (3 rows) 

One of the products is already sold out and can no longer be bought. To retrieve only those records that can still be bought, you have to add a WHERE clause to the query:

 phpbook=# SELECT * FROM product WHERE stored > 0;  id |   name    | stored ----+-----------+--------   1 | umbrella  |     23   3 | flowerpot |      3 (2 rows) 

In more complex operations, it might be tedious to add a WHERE clause every time you want to retrieve all products that can still be bought. Therefore it is possible to define a view:

 phpbook=# CREATE VIEW product_instock AS SELECT * FROM product WHERE stored > 0; CREATE 

The view is called product_instock and contains all records where the number of products in the store is higher than 0. Take a look at the definition of the view:

 phpbook=# \d product_instock     View "product_instock"  Column |  Type   | Modifiers --------+---------+-----------  id     | integer |  name   | text    |  stored | integer | View definition: SELECT product.id, product.name, product.stored FROM product WHERE (product.stored > 0); 

As you can see, the columns of the view have the same structure as the original table. After you've defined the view, it can be accessed just like any real table:

 phpbook=# SELECT * FROM product_instock;  id |   name    | stored ----+-----------+--------   1 | umbrella  |     23   3 | flowerpot |      3 (2 rows) 

If all records are selected from the view, there won't be products that can no longer be bought in the result.

When you're working with join operations, views can be used just like any other table. In the next step you will see how to retrieve all values that can be found in product and product_instock:

 SELECT product_instock.*         FROM product_instock, product         WHERE product_instock.id=product.id; 

The result is not surprising:

  id |   name    | stored ----+-----------+--------   1 | umbrella  |     23   3 | flowerpot |      3 (2 rows) 

The most interesting thing about this example is that views can be joined with tables even if the table and the view access the same piece of data.

To remove a view, you can use the DROP VIEW command:

 phpbook=# \h DROP VIEW Command:     DROP VIEW Description: remove a view Syntax: DROP VIEW name [, ...] 

8.2.2 Defining Rules

So far you have seen how to define views and retrieve data from a view. These are the basic operations that can be performed. In addition, PostgreSQL provides more useful and more sophisticated ways of dealing with views.

Take a look at the next example:

 phpbook=# UPDATE product SET stored=22 WHERE id=1; UPDATE 1 

It is an easy task to update a record, but what happens if you want to update a record in the view?

 phpbook=# UPDATE product_instock SET stored=21 WHERE id=1; ERROR:  Cannot update a view without an appropriate rule 

PostgreSQL complains that a rule is missing. It is not possible for the database to find out what to do if a record should be updated in the view. This behavior of PostgreSQL seems obvious because it is not possible for the database to find out what to do in case of multiple tables. The same applies to INSERT statements rules must be defined in order to tell PostgreSQL what to do in case of an INSERT statement.

To create rules, use the CREATE RULE command:

 phpbook=# \h CREATE RULE Command:     CREATE RULE Description: define a new rewrite rule Syntax: CREATE RULE name AS ON event     TO object [ WHERE condition ]     DO [ INSTEAD ] action where action can be: NOTHING | query | ( query ; query ... ) | [ query ; query ... ] 

As the syntax overview of the CREATE RULE command shows, rules are defined on a certain event. In this case the event is either an INSERT or an UPDATE operation on a view. In the next example you will see how a rule for INSERT statements can be defined:

 CREATE RULE rule_insert_product_instock AS ON INSERT         TO product_instock         DO INSTEAD INSERT INTO product VALUES (                 NEW.id,                 NEW.name,                 NEW.stored); 

The rule called rule_insert_product_instock will be applied if somebody tries to insert data into product_instock. Instead of inserting data into the view, PostgreSQL has to insert the data into product. The values inserted into the table will be taken from a virtual relation called NEW. Just as when working with triggers written in PL/pgSQL, NEW contains the data passed to the INSERT or UPDATE statement. In this example all three values should be inserted into product. This is just a simple example because there is only a view built on one table involved. In more complex views, the rules will be far more complex as well.

Let's insert the rule into the database:

 [postgres@athlon rules]$ psql phpbook < rule.sql CREATE 

If no syntax error occurred, the rule has been added to the database successfully. Let's try to insert a record into the view:

 phpbook=# INSERT INTO product_instock VALUES(4, 'dictionary', 49); INSERT 24899 1 

Everything worked just fine and no errors occurred. Now the new value can be found in the view and the underlying table:

 phpbook=# SELECT * FROM product WHERE id=4;  id |    name    | stored ----+------------+--------   4 | dictionary |     49 (1 row) phpbook=# SELECT * FROM product_instock WHERE id=4;  id |    name    | stored ----+------------+--------   4 | dictionary |     49 (1 row) 

To perform UPDATE operations, an additional rule must be defined:

 CREATE RULE rule_update_product_instock AS ON UPDATE         TO product_instock         DO INSTEAD UPDATE product                 SET                         id=NEW.id,                         name=NEW.name,                         stored=NEW.stored                 WHERE                         id=OLD.id                         AND name=OLD.name                         AND stored=OLD.stored; 

The rule is already slightly more complex because the WHERE clause of the UPDATE operation must have the appropriate values to perform the operation correctly. To tell PostgreSQL which values to use in the WHERE clause, a virtual relation called OLD is used. In contrast to NEW, OLD contains the values that were valid before the operation. In this example OLD will contain the values you have used in the WHERE clause of the UPDATE operation the rule is used for.

Recall the content of the view:

 phpbook=# SELECT * FROM product_instock;  id |    name    | stored ----+------------+--------   3 | flowerpot  |      3   1 | umbrella   |     22   4 | dictionary |     49 (3 rows) 

In the next step you can use an UPDATE operation to modify one of the values:

 phpbook=# UPDATE product_instock SET stored=2 WHERE id=3; UPDATE 1 

The operation has been executed successfully. Let's see if the values have been changed correctly:

 phpbook=# SELECT * FROM product_instock;  id |    name    | stored ----+------------+--------   1 | umbrella   |     22   4 | dictionary |     49   3 | flowerpot  |      2 (3 rows) 

The third column of the table has been set to the new value and only one row was affected by the query.

To remove the rules from the database, use the DROP RULE command:

 DROP RULE rule_insert_product_instock; DROP RULE rule_update_product_instock; 

Simply pass the name of the rule to the command and PostgreSQL will take care of the rest. DROP RULE is an important command because in contrast to PL/pgSQL functions, DROP RULE does not support the CREATE OR REPLACE command, so DROP RULE must be used before creating a rule with a name that is already in use.

In some cases it can be useful to define rules that do nothing. If you are building prototype applications or if you just want to turn off certain operations without affecting the applications built on top of your database, rules can be useful. In the next example you can see a rule that neglects an INSERT statement:

 CREATE RULE rule_insert_product_instock AS ON INSERT         TO product_instock         DO INSTEAD NOTHING; 

The advantage of this rule is that PostgreSQL does not complain that a rule is missing and silently omits the INSERT operation, as you can see in the next listing:

 phpbook=# INSERT INTO product_instock VALUES(5, 'orange juice', 8); phpbook=# 

No SQL statement has been performed:

 phpbook=# SELECT * FROM product_instock;  id |    name    | stored ----+------------+--------   1 | umbrella   |     22   4 | dictionary |     49   3 | flowerpot  |      2 (3 rows) 

The content of the table is still the same as it was before the INSERT statement.

Rules also make it possible to execute more than one statement at once. This is necessary when a view is based on a complex join or when other important operations must be performed. The following example shows what rules with multiple SQL statements can be good for:

 phpbook=# ALTER TABLE product ADD COLUMN valid bool; ALTER 

First, a column called valid is added to the table. This column defines whether the record stored for a product is still valid. Take a look at the data structure of the table:

 phpbook=# \d product        Table "product"  Column |  Type   | Modifiers --------+---------+-----------  id     | integer |  name   | text    |  stored | integer |  valid  | boolean | 

The table contains four columns. After that you can create a second table for storing logging information:

 phpbook=# CREATE TABLE logtab(tstamp timestamp, action text); CREATE 

This table contains the time when a change has taken place and text describing the action. In the next step you can write a rule that will be used with DELETE statements:

 CREATE RULE rule_delete_product AS ON DELETE         TO product         DO INSTEAD                 ( INSERT INTO logtab VALUES (now(), 'deleting from product');                 UPDATE product SET valid='f'                         WHERE id=OLD.id; ); 

Every time a record in product should be deleted, PostgreSQL will execute two SQL statements. The first one inserts a record into the logging table. The second one updates the record in product and sets it to invalid. This way it is possible to change the behavior of certain operations without changing the code on the application level. However, we recommend that you use this feature carefully; otherwise, your applications will become increasingly complicated and difficult to understand.

Let's test the rule:

 phpbook=# DELETE FROM product WHERE id=3; UPDATE 1 

One record will be changed, as you can see in the next listing.

 phpbook=# SELECT * FROM product;  id |    name    | stored | valid ----+------------+--------+-------   2 | carpet     |      0 |   1 | umbrella   |     22 |   4 | dictionary |     49 |   3 | flowerpot  |      2 | f (4 rows) 

In addition, one record has been added to logtab:

 phpbook=# SELECT * FROM logtab;             tstamp             |        action -------------------------------+-----------------------  2001-11-27 17:09:31.498421+01 | deleting from product (1 row) 

To understand the next example, it is necessary to have a closer look at the rule: In the rule it says id=OLD.id. This means that the UPDATE operation is performed when the id is equal to the id in the virtual relation called OLD. What happens if a different operator than the = operator is used in the DELETE statement?

 phpbook=# DELETE FROM product WHERE id<3; UPDATE 2 

Two UPDATE operations are performed because the rule is executed for every record being processed. logtab will contain two additional records now:

 phpbook=# SELECT * FROM logtab;             tstamp             |        action -------------------------------+-----------------------  2001-11-27 17:09:31.498421+01 | deleting from product  2001-11-27 17:10:30.809778+01 | deleting from product  2001-11-27 17:10:30.809778+01 | deleting from product (3 rows) 

Two records have been updated in table product:

 phpbook=# SELECT * FROM product;  id |    name    | stored | valid ----+------------+--------+-------   4 | dictionary |     49 |   3 | flowerpot  |      2 | f   1 | umbrella   |     22 | f   2 | carpet     |      0 | f (4 rows) 

So far, you have seen how to define rules on views and tables. You have also seen how to perform certain operations instead of other operations. However, sometimes it is not necessary to perform an operation instead of another operation because an additional SQL statement must be executed. In the next example you will learn to define a rule that performs an additional SQL statement:

 CREATE RULE rule_insert_product AS ON INSERT         TO product         DO                 INSERT INTO logtab VALUES (now(), 'inserting into product'); 

The only thing you have to do is to omit the keyword INSTEAD and PostgreSQL will automatically perform the original operation without modifications.

To find out if rules have been defined, you can use \d:

 phpbook=# \d product        Table "product"  Column |  Type   | Modifiers --------+---------+-----------  id     | integer |  name   | text    |  stored | integer |  valid  | boolean | Rules: rule_delete_product,        rule_insert_product 

Because rules are defined on tables and events, a list of rules is displayed at the end of the listing generated by \d.