2.4. The PostgreSQL Rule System

PostgreSQL supports a powerful rule system for the specification of views and ambiguous view updates. Originally the PostgreSQL rule system consisted of two implementations:

The first one worked using tuple level processing and was implemented deep in the executor. The rule system was called whenever an individual tuple had been accessed. This implementation was removed in 1995 when the last official release of the PostgreSQL project was transformed into Postgres95.
The second implementation of the rule system is a technique called query rewriting. The rewrite system} is a module that exists between the parser stage and the planner/optimizer. This technique is still implemented.

For information on the syntax and creation of rules in the PostgreSQL system refer to The PostgreSQL User's Guide.

2.4.1. The Rewrite System

The query rewrite system is a module between the parser stage and the planner/optimizer. It processes the tree handed back by the parser stage (which represents a user query) and if there is a rule present that has to be applied to the query it rewrites the tree to an alternate form.

2.4.1.1. Techniques To Implement Views

Now we will sketch the algorithm of the query rewrite system. For better illustration we show how to implement views using rules as an example.

Let the following rule be given:

  create rule view_rule
   as on select 
   to test_view
   do instead
      select s.sname, p.pname
      from supplier s, sells se, part p
      where s.sno = se.sno and
            p.pno = se.pno;

The given rule will be fired whenever a select against the relation test_view is detected. Instead of selecting the tuples from test_view the select statement given in the action part of the rule is executed.

Let the following user-query against test_view be given:

  select sname 
   from test_view
   where sname <> 'Smith';

Here is a list of the steps performed by the query rewrite system whenever a user-query against test_view appears. (The following listing is a very informal description of the algorithm just intended for basic understanding. For a detailed description refer to A commentary on the POSTGRES rules system).

test_view Rewrite

Take the query given in the action part of the rule.
Adapt the targetlist to meet the number and order of attributes given in the user-query.
Add the qualification given in the where clause of the user-query to the qualification of the query given in the action part of the rule.

Given the rule definition above, the user-query will be rewritten to the following form (Note that the rewriting is done on the internal representation of the user-query handed back by the parser stage but the derived new data structure will represent the following query):

  select s.sname
   from supplier s, sells se, part p
   where s.sno = se.sno and
         p.pno = se.pno and
         s.sname <> 'Smith';