because this is exactly what the CREATE
VIEW command does internally. This has some side effects.
One of them is that the information about a view in the
PostgreSQL system catalogs is
exactly the same as it is for a table. So for the parser, there
is absolutely no difference between a table and a view. They are
the same thing: relations.

Rules ON SELECT are applied to all
queries as the last step, even if the command given is an
INSERT, UPDATE or DELETE. And
they have different semantics from rules on the other command
types in that they modify the query tree in place instead of
creating a new one. So SELECT rules
are described first.

Currently, there can be only one action in an ON SELECT rule, and it must be an unconditional
SELECT action that is INSTEAD. This restriction was required to make
rules safe enough to open them for ordinary users, and it
restricts ON SELECT rules to act like
views.

The examples for this chapter are two join views that do
some calculations and some more views using them in turn. One
of the two first views is customized later by adding rules for
INSERT, UPDATE, and DELETE
operations so that the final result will be a view that behaves
like a real table with some magic functionality. This is not
such a simple example to start from and this makes things
harder to get into. But it's better to have one example that
covers all the points discussed step by step rather than having
many different ones that might mix up in mind.

For the example, we need a little min function that returns the lower of 2 integer
values. We create that as:

The CREATE VIEW command for the
shoelace view (which is the simplest
one we have) will create a relation shoelace and an entry in pg_rewrite that tells that there is a rewrite
rule that must be applied whenever the relation shoelace is referenced in a query's range table.
The rule has no rule qualification (discussed later, with the
non-SELECT rules, since SELECT rules currently cannot have them) and it
is INSTEAD. Note that rule
qualifications are not the same as query qualifications. The
action of our rule has a query qualification. The action of the
rule is one query tree that is a copy of the SELECT statement in the view creation
command.

Note: The two extra range table entries for
NEW and OLD that you can see in the pg_rewrite entry aren't of interest for
SELECT rules.

Now we populate unit, shoe_data and shoelace_data and run a simple query on a
view:

This is the simplest SELECT you can
do on our views, so we take this opportunity to explain the
basics of view rules. The SELECT * FROM
shoelace was interpreted by the parser and produced the
query tree:

and this is given to the rule system. The rule system walks
through the range table and checks if there are rules for any
relation. When processing the range table entry for shoelace (the only one up to now) it finds the
_RETURN rule with the query tree:

To expand the view, the rewriter simply creates a subquery
range-table entry containing the rule's action query tree, and
substitutes this range table entry for the original one that
referenced the view. The resulting rewritten query tree is
almost the same as if you had typed:

There is one difference however: the subquery's range table
has two extra entries shoelace old and
shoelace new. These entries don't
participate directly in the query, since they aren't referenced
by the subquery's join tree or target list. The rewriter uses
them to store the access privilege check information that was
originally present in the range-table entry that referenced the
view. In this way, the executor will still check that the user
has proper privileges to access the view, even though there's
no direct use of the view in the rewritten query.

That was the first rule applied. The rule system will
continue checking the remaining range-table entries in the top
query (in this example there are no more), and it will
recursively check the range-table entries in the added subquery
to see if any of them reference views. (But it won't expand
old or new —
otherwise we'd have infinite recursion!) In this example, there
are no rewrite rules for shoelace_data
or unit, so rewriting is complete and
the above is the final result given to the planner.

Now we want to write a query that finds out for which shoes
currently in the store we have the matching shoelaces (color
and length) and where the total number of exactly matching
pairs is greater or equal to two.

It turns out that the planner will collapse this tree into a
two-level query tree: the bottommost SELECT commands will be "pulled up" into the middle SELECT since there's no need to process them
separately. But the middle SELECT will
remain separate from the top, because it contains aggregate
functions. If we pulled those up it would change the behavior
of the topmost SELECT, which we don't
want. However, collapsing the query tree is an optimization
that the rewrite system doesn't have to concern itself
with.

Two details of the query tree aren't touched in the
description of view rules above. These are the command type and
the result relation. In fact, the command type is not needed by
view rules, but the result relation may affect the way in which
the query rewriter works, because special care needs to be
taken if the result relation is a view.

There are only a few differences between a query tree for a
SELECT and one for any other command.
Obviously, they have a different command type and for a command
other than a SELECT, the result
relation points to the range-table entry where the result
should go. Everything else is absolutely the same. So having
two tables t1 and t2 with columns a and
b, the query trees for the two
statements:

The target lists contain one variable that points to
column b of the range table entry
for table t2.

The qualification expressions compare the columns
a of both range-table entries for
equality.

The join trees show a simple join between t1 and t2.

The consequence is, that both query trees result in similar
execution plans: They are both joins over the two tables. For
the UPDATE the missing columns from
t1 are added to the target list by the
planner and the final query tree will read as:

UPDATE t1 SET a = t1.a, b = t2.b FROM t2 WHERE t1.a = t2.a;

and thus the executor run over the join will produce exactly
the same result set as:

SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a;

But there is a little problem in UPDATE: the part of the executor plan that does
the join does not care what the results from the join are meant
for. It just produces a result set of rows. The fact that one
is a SELECT command and the other is
an UPDATE is handled higher up in the
executor, where it knows that this is an UPDATE, and it knows that this result should go
into table t1. But which of the rows
that are there has to be replaced by the new row?

To resolve this problem, another entry is added to the
target list in UPDATE (and also in
DELETE) statements: the current tuple
ID (CTID). This is a system
column containing the file block number and position in the
block for the row. Knowing the table, the CTID can be used to retrieve the original
row of t1 to be updated. After adding
the CTID to the target list,
the query actually looks like:

SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;

Now another detail of PostgreSQL enters the stage. Old table
rows aren't overwritten, and this is why ROLLBACK is fast. In an UPDATE, the new result row is inserted into the
table (after stripping the CTID) and in the row header of the old row,
which the CTID pointed to,
the cmax and xmax entries are set to the current command
counter and current transaction ID. Thus the old row is hidden,
and after the transaction commits the vacuum cleaner can
eventually remove the dead row.

Knowing all that, we can simply apply view rules in
absolutely the same way to any command. There is no
difference.

The above demonstrates how the rule system incorporates view
definitions into the original query tree. In the second
example, a simple SELECT from one view
created a final query tree that is a join of 4 tables
(unit was used twice with different
names).

The benefit of implementing views with the rule system is,
that the planner has all the information about which tables
have to be scanned plus the relationships between these tables
plus the restrictive qualifications from the views plus the
qualifications from the original query in one single query
tree. And this is still the situation when the original query
is already a join over views. The planner has to decide which
is the best path to execute the query, and the more information
the planner has, the better this decision can be. And the rule
system as implemented in PostgreSQL ensures, that this is all
information available about the query up to that point.

What happens if a view is named as the target relation for
an INSERT, UPDATE, or DELETE?
Doing the substitutions described above would give a query tree
in which the result relation points at a subquery range-table
entry, which will not work. There are several ways in which
PostgreSQL can support the
appearance of updating a view, however.

If the subquery selects from a single base relation and is
simple enough, the rewriter can automatically replace the
subquery with the underlying base relation so that the
INSERT, UPDATE, or DELETE is
applied to the base relation in the appropriate way. Views that
are "simple enough" for this are
called automatically updatable. For
detailed information on the kinds of view that can be
automatically updated, see CREATE
VIEW.

Alternatively, the operation may be handled by a
user-provided INSTEAD OF trigger on
the view. Rewriting works slightly differently in this case.
For INSERT, the rewriter does nothing
at all with the view, leaving it as the result relation for the
query. For UPDATE and DELETE, it's still necessary to expand the view
query to produce the "old" rows that
the command will attempt to update or delete. So the view is
expanded as normal, but another unexpanded range-table entry is
added to the query to represent the view in its capacity as the
result relation.

The problem that now arises is how to identify the rows to
be updated in the view. Recall that when the result relation is
a table, a special CTID
entry is added to the target list to identify the physical
locations of the rows to be updated. This does not work if the
result relation is a view, because a view does not have any
CTID, since its rows do not
have actual physical locations. Instead, for an UPDATE or DELETE
operation, a special wholerow entry is
added to the target list, which expands to include all columns
from the view. The executor uses this value to supply the
"old" row to the INSTEAD OF trigger. It is up to the trigger to
work out what to update based on the old and new row
values.

Another possibility is for the user to define INSTEAD rules that specify substitute actions
for INSERT, UPDATE, and DELETE
commands on a view. These rules will rewrite the command,
typically into a command that updates one or more tables,
rather than views. That is the topic of the next section.

Note that rules are evaluated first, rewriting the original
query before it is planned and executed. Therefore, if a view
has INSTEAD OF triggers as well as
rules on INSERT, UPDATE, or DELETE, then
the rules will be evaluated first, and depending on the result,
the triggers may not be used at all.

Automatic rewriting of an INSERT,
UPDATE, or DELETE query on a simple view is always tried
last. Therefore, if a view has rules or triggers, they will
override the default behavior of automatically updatable
views.

If there are no INSTEAD rules or
INSTEAD OF triggers for the view, and
the rewriter cannot automatically rewrite the query as an
update on the underlying base relation, an error will be thrown
because the executor cannot update a view as such.