Views in PostgreSQL are implemented using the Rule System. In fact there is absolutely no difference between a
CREATE VIEW myview AS SELECT * FROM mytab; |
CREATE TABLE myview (same attribute list as for mytab); CREATE RULE "_RETmyview" AS ON SELECT TO myview DO INSTEAD SELECT * FROM mytab; |
Rules ON SELECT are applied to all queries as the last step, even if the command given is an INSERT, UPDATE, or DELETE, 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 for INSTEAD. This restriction was required to make rules safe enough to open them for ordinary users and it explicitly restricts rules ON SELECT to real view rules.
The examples for this chapter are two join views that do some calculations and some other views that use them. One of the two 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.
The database needed for the examples is named al_bundy. You will see soon why this is the database name. And it needs the procedural language PL/pgSQL installed because we need a little min() function returning the lower of 2 integer values. We create that as:
CREATE FUNCTION min(integer, integer) RETURNS integer AS ' BEGIN IF $1 < $2 THEN RETURN $1; END IF; RETURN $2; END; ' LANGUAGE plpgsql; |
The real tables we need in the first two Rule System descriptions are these:
CREATE TABLE shoe_data ( shoename char(10), -- primary key sh_avail integer, -- available # of pairs slcolor char(10), -- preferred shoelace color slminlen float, -- miminum shoelace length slmaxlen float, -- maximum shoelace length slunit char(8) -- length unit ); CREATE TABLE shoelace_data ( sl_name char(10), -- primary key sl_avail integer, -- available # of pairs sl_color char(10), -- shoelace color sl_len float, -- shoelace length sl_unit char(8) -- length unit ); CREATE TABLE unit ( un_name char(8), -- the primary key un_fact float -- factor to transform to cm ); |
The views are created as:
CREATE VIEW shoe AS SELECT sh.shoename, sh.sh_avail, sh.slcolor, sh.slminlen, sh.slminlen * un.un_fact AS slminlen_cm, sh.slmaxlen, sh.slmaxlen * un.un_fact AS slmaxlen_cm, sh.slunit FROM shoe_data sh, unit un WHERE sh.slunit = un.un_name; CREATE VIEW shoelace AS 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; CREATE VIEW shoe_ready AS SELECT rsh.shoename, rsh.sh_avail, rsl.sl_name, rsl.sl_avail, min(rsh.sh_avail, rsl.sl_avail) AS total_avail FROM shoe rsh, shoelace rsl WHERE rsl.sl_color = rsh.slcolor AND rsl.sl_len_cm >= rsh.slminlen_cm AND rsl.sl_len_cm <= rsh.slmaxlen_cm; |
The rule's action 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 (named *NEW* and *CURRENT* for historical reasons in the printed query tree) that you can see in the pg_rewrite entry are not of interest for SELECT rules. |
al_bundy=> INSERT INTO unit VALUES ('cm', 1.0); al_bundy=> INSERT INTO unit VALUES ('m', 100.0); al_bundy=> INSERT INTO unit VALUES ('inch', 2.54); al_bundy=> al_bundy=> INSERT INTO shoe_data VALUES al_bundy-> ('sh1', 2, 'black', 70.0, 90.0, 'cm'); al_bundy=> INSERT INTO shoe_data VALUES al_bundy-> ('sh2', 0, 'black', 30.0, 40.0, 'inch'); al_bundy=> INSERT INTO shoe_data VALUES al_bundy-> ('sh3', 4, 'brown', 50.0, 65.0, 'cm'); al_bundy=> INSERT INTO shoe_data VALUES al_bundy-> ('sh4', 3, 'brown', 40.0, 50.0, 'inch'); al_bundy=> al_bundy=> INSERT INTO shoelace_data VALUES al_bundy-> ('sl1', 5, 'black', 80.0, 'cm'); al_bundy=> INSERT INTO shoelace_data VALUES al_bundy-> ('sl2', 6, 'black', 100.0, 'cm'); al_bundy=> INSERT INTO shoelace_data VALUES al_bundy-> ('sl3', 0, 'black', 35.0 , 'inch'); al_bundy=> INSERT INTO shoelace_data VALUES al_bundy-> ('sl4', 8, 'black', 40.0 , 'inch'); al_bundy=> INSERT INTO shoelace_data VALUES al_bundy-> ('sl5', 4, 'brown', 1.0 , 'm'); al_bundy=> INSERT INTO shoelace_data VALUES al_bundy-> ('sl6', 0, 'brown', 0.9 , 'm'); al_bundy=> INSERT INTO shoelace_data VALUES al_bundy-> ('sl7', 7, 'brown', 60 , 'cm'); al_bundy=> INSERT INTO shoelace_data VALUES al_bundy-> ('sl8', 1, 'brown', 40 , 'inch'); al_bundy=> al_bundy=> SELECT * FROM shoelace; sl_name |sl_avail|sl_color |sl_len|sl_unit |sl_len_cm ----------+--------+----------+------+--------+--------- sl1 | 5|black | 80|cm | 80 sl2 | 6|black | 100|cm | 100 sl7 | 7|brown | 60|cm | 60 sl3 | 0|black | 35|inch | 88.9 sl4 | 8|black | 40|inch | 101.6 sl8 | 1|brown | 40|inch | 101.6 sl5 | 4|brown | 1|m | 100 sl6 | 0|brown | 0.9|m | 90 (8 rows) |
SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace shoelace; |
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); |
To expand the view, the rewriter simply creates a subselect range-table entry containing the rule's action parse tree, and substitutes this range table entry for the original one that referenced the view. The resulting rewritten parse tree is almost the same as if Al had typed:
SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM (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) shoelace; |
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 sub-query to see if any of them reference views. (But it will not 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 face Al with the problem that the Blues Brothers appear in his shop and want to buy some new shoes, and as the Blues Brothers are, they want to wear the same shoes. And they want to wear them immediately, so they need shoelaces too.
Al needs to know for which shoes currently in the store he has the matching shoelaces (color and size) and where the total number of exactly matching pairs is greater or equal to two. We teach him what to do and he asks his database:
al_bundy=> SELECT * FROM shoe_ready WHERE total_avail >= 2; shoename |sh_avail|sl_name |sl_avail|total_avail ----------+--------+----------+--------+----------- sh1 | 2|sl1 | 5| 2 sh3 | 4|sl7 | 7| 4 (2 rows) |
The output of the parser this time is the parse tree:
SELECT shoe_ready.shoename, shoe_ready.sh_avail, shoe_ready.sl_name, shoe_ready.sl_avail, shoe_ready.total_avail FROM shoe_ready shoe_ready WHERE int4ge(shoe_ready.total_avail, 2); |
SELECT shoe_ready.shoename, shoe_ready.sh_avail, shoe_ready.sl_name, shoe_ready.sl_avail, shoe_ready.total_avail FROM (SELECT rsh.shoename, rsh.sh_avail, rsl.sl_name, rsl.sl_avail, min(rsh.sh_avail, rsl.sl_avail) AS total_avail FROM shoe rsh, shoelace rsl WHERE rsl.sl_color = rsh.slcolor AND rsl.sl_len_cm >= rsh.slminlen_cm AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready WHERE int4ge(shoe_ready.total_avail, 2); |
SELECT shoe_ready.shoename, shoe_ready.sh_avail, shoe_ready.sl_name, shoe_ready.sl_avail, shoe_ready.total_avail FROM (SELECT rsh.shoename, rsh.sh_avail, rsl.sl_name, rsl.sl_avail, min(rsh.sh_avail, rsl.sl_avail) AS total_avail FROM (SELECT sh.shoename, sh.sh_avail, sh.slcolor, sh.slminlen, sh.slminlen * un.un_fact AS slminlen_cm, sh.slmaxlen, sh.slmaxlen * un.un_fact AS slmaxlen_cm, sh.slunit FROM shoe_data sh, unit un WHERE sh.slunit = un.un_name) rsh, (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) rsl WHERE rsl.sl_color = rsh.slcolor AND rsl.sl_len_cm >= rsh.slminlen_cm AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready WHERE int4ge(shoe_ready.total_avail, 2); |
Note | |
---|---|
There is currently no recursion stopping mechanism for view rules in the Rule System (only for the other kinds of rules). This does not hurt much, because the only way to push this into an endless loop (blowing up the backend until it reaches the memory limit) is to create tables and then setup the view rules by hand with CREATE RULE in such a way that one selects from the other that selects from the one. This could never happen if CREATE VIEW is used because for the first CREATE VIEW, the second relation does not exist and thus the first view cannot select from the second. |
The above demonstrates how the Rule System incorporates view definitions into the original parse tree. In the second example a simple SELECT from one view created a final parse tree that is a join of 4 tables (unit is 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 parse tree. And this is still the situation when the original query is already a join over views. Now the planner has to decide which is the best path to execute the query. 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 now.