Download PostgreSQL Programmer`s Guide The

Chapter 8. The Postgres Rule System
shoelace.sl_unit, shoelace.sl_len_cm
FROM shoelace shoelace;
and this is given to the rule system. The rule system walks through the rangetable and checks if
there are rules in pg_rewrite for any relation. When processing the rangetable entry for
shoelace (the only one up to now) it finds the rule ’_RETshoelace’ with the parsetree
SELECT s.sl_name, s.sl_avail,
s.sl_color, s.sl_len, s.sl_unit,
float8mul(s.sl_len, u.un_fact) AS sl_len_cm
FROM shoelace *OLD*, shoelace *NEW*,
shoelace_data s, unit u
WHERE bpchareq(s.sl_unit, u.un_name);
Note that the parser changed the calculation and qualification into calls to the appropriate
functions. But in fact this changes nothing. The first step in rewriting is merging the two
rangetables. The resulting parsetree then reads
SELECT shoelace.sl_name, shoelace.sl_avail,
shoelace.sl_color, shoelace.sl_len,
shoelace.sl_unit, shoelace.sl_len_cm
FROM shoelace shoelace, shoelace *OLD*,
shoelace *NEW*, shoelace_data s,
unit u;
In step 2 it adds the qualification from the rule action to the parsetree resulting in
SELECT shoelace.sl_name, shoelace.sl_avail,
shoelace.sl_color, shoelace.sl_len,
shoelace.sl_unit, shoelace.sl_len_cm
FROM shoelace shoelace, shoelace *OLD*,
shoelace *NEW*, shoelace_data s,
unit u
WHERE bpchareq(s.sl_unit, u.un_name);
And in step 3 it replaces all the variables in the parsetree, that reference the rangetable entry
(the one for shoelace that is currently processed) by the corresponding targetlist expressions
from the rule action. This results in the final query
SELECT s.sl_name, s.sl_avail,
s.sl_color, s.sl_len,
s.sl_unit, float8mul(s.sl_len, u.un_fact) AS sl_len_cm
FROM shoelace shoelace, shoelace *OLD*,
shoelace *NEW*, shoelace_data s,
unit u
WHERE bpchareq(s.sl_unit, u.un_name);
Turning this back into a real SQL statement a human user would type reads
SELECT s.sl_name, s.sl_avail,
s.sl_color, s.sl_len,
s.sl_unit, s.sl_len * u.un_fact AS sl_len_cm
FROM shoelace_data s, unit u
WHERE s.sl_unit = u.un_name;
That was the first rule applied. While this was done, the rangetable has grown. So the rule
system continues checking the range table entries. The next one is number 2 (shoelace *OLD*).
Relation shoelace has a rule, but this rangetable entry isn’t referenced in any of the variables of
the parsetree, so it is ignored. Since all the remaining rangetable entries either have no rules in
pg_rewrite or aren’t referenced, it reaches the end of the rangetable. Rewriting is complete and
the above is the final result given into the optimizer. The optimizer ignores the extra rangetable
entries that aren’t referenced by variables in the parsetree and the plan produced by the
33