rules.sgml

关系型数据库 Postgresql 6.5.2

         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;
</ProgramListing>

    The CREATE VIEW command for the <Filename>shoelace</Filename> 
    view (which is the simplest one we have)
    will create a relation shoelace and an entry 
    in <FileName>pg_rewrite</FileName>
    that tells that there is a rewrite rule that must be applied
    whenever the relation shoelace is referenced in a queries rangetable.
    The rule has no rule qualification (discussed in 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 rules action has a qualification.
</Para>

<Para>
    The rules action is one querytree that is an exact copy of the
    SELECT statement in the view creation command.
    
    <Note>
    <Title>Note</Title>
    <Para>
    The two extra range
    table entries for NEW and OLD (named *NEW* and *CURRENT* for
    historical reasons in the printed querytree) you can see in
    the <Filename>pg_rewrite</Filename> entry aren't of interest
    for SELECT rules.
    </Para>
    </Note>

    Now we populate <Filename>unit</Filename>, <Filename>shoe_data</Filename>
    and <Filename>shoelace_data</Filename> and Al types the first 
    SELECT in his life:

<ProgramListing>
    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)
</ProgramListing>

    It's the simplest SELECT Al can do on our views, so we take this
    to explain the basics of view rules.
    The 'SELECT * FROM shoelace' was interpreted by the parser and
    produced the parsetree

<ProgramListing>
    SELECT shoelace.sl_name, shoelace.sl_avail,
           shoelace.sl_color, shoelace.sl_len,
           shoelace.sl_unit, shoelace.sl_len_cm
      FROM shoelace shoelace;
</ProgramListing>

    and this is given to the rule system. The rule system walks through the
    rangetable and checks if there are rules in <Filename>pg_rewrite</Filename>
    for any relation. When processing the rangetable entry for
    <Filename>shoelace</Filename> (the only one up to now) it finds the
    rule '_RETshoelace' with the parsetree

<ProgramListing>
    <FirstTerm>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);</FirstTerm>
</ProgramListing>

    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

<ProgramListing>
    SELECT shoelace.sl_name, shoelace.sl_avail,
           shoelace.sl_color, shoelace.sl_len,
           shoelace.sl_unit, shoelace.sl_len_cm
      FROM shoelace shoelace, <FirstTerm>shoelace *OLD*</FirstTerm>,
           <FirstTerm>shoelace *NEW*</FirstTerm>, <FirstTerm>shoelace_data s</FirstTerm>,
           <FirstTerm>unit u</FirstTerm>;
</ProgramListing>

    In step 2 it adds the qualification from the rule action to the
    parsetree resulting in

<ProgramListing>
    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
     <FirstTerm>WHERE bpchareq(s.sl_unit, u.un_name)</FirstTerm>;
</ProgramListing>

    And in step 3 it replaces all the variables in the parsetree, that
    reference the rangetable entry (the one for
    <Filename>shoelace</Filename> that is currently processed)
    by the corresponding targetlist expressions
    from the rule action. This results in the final query

<ProgramListing>
    SELECT <FirstTerm>s.sl_name</FirstTerm>, <FirstTerm>s.sl_avail</FirstTerm>, 
           <FirstTerm>s.sl_color</FirstTerm>, <FirstTerm>s.sl_len</FirstTerm>, 
           <FirstTerm>s.sl_unit</FirstTerm>, <FirstTerm>float8mul(s.sl_len, u.un_fact)</FirstTerm> AS sl_len_cm
      FROM shoelace shoelace, shoelace *OLD*,
           shoelace *NEW*, shoelace_data s,
           unit u
     WHERE bpchareq(s.sl_unit, u.un_name);
</ProgramListing>

    Turning this back into a real <Acronym>SQL</Acronym> statement a human
    user would type reads

<ProgramListing>
    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;
</ProgramListing>

    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 <Filename>shoelace</Filename>
    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
    <Filename>pg_rewrite</Filename> 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 planner/optimizer would be exactly the same as if Al had typed
    the above SELECT query instead of the view selection.
</Para>

<Para>
    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.
</Para>

<Para>
    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 theach him how to do and he asks his database:

<ProgramListing>
    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)
</ProgramListing>

    Al is a shoe guru and so he knows that only shoes of type sh1
    would fit (shoelace sl7 is brown and shoes that need brown shoelaces
    aren't shoes the Blues Brothers would ever wear).
</Para>

<Para>
    The output of the parser this time is the parsetree

<ProgramListing>
    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);
</ProgramListing>

    The first rule applied will be that one for the 
    <Filename>shoe_ready</Filename> relation and it results in the
    parsetree

<ProgramListing>
    SELECT <FirstTerm>rsh.shoename</FirstTerm>, <FirstTerm>rsh.sh_avail</FirstTerm>,
           <FirstTerm>rsl.sl_name</FirstTerm>, <FirstTerm>rsl.sl_avail</FirstTerm>,
           <FirstTerm>min(rsh.sh_avail, rsl.sl_avail) AS total_avail</FirstTerm>
      FROM shoe_ready shoe_ready, <FirstTerm>shoe_ready *OLD*</FirstTerm>,
           <FirstTerm>shoe_ready *NEW*</FirstTerm>, <FirstTerm>shoe rsh</FirstTerm>,
           <FirstTerm>shoelace rsl</FirstTerm>
     WHERE int4ge(<FirstTerm>min(rsh.sh_avail, rsl.sl_avail)</FirstTerm>, 2)
       <FirstTerm>AND (bpchareq(rsl.sl_color, rsh.slcolor)
            AND float8ge(rsl.sl_len_cm, rsh.slminlen_cm)
            AND float8le(rsl.sl_len_cm, rsh.slmaxlen_cm)
           )</FirstTerm>;
</ProgramListing>

    In reality the AND clauses in the qualification will be
    operator nodes of type AND with a left and right expression. But
    that makes it lesser readable as it already is, and there are more
    rules to apply. So I only put them into some parantheses to group
    them into logical units in the order they where added and we continue
    with the rule for relation
    <Filename>shoe</Filename> as it is the next rangetable entry
    that is referenced and has a rule. The result of applying it is

<ProgramListing>
    SELECT <FirstTerm>sh.shoename</FirstTerm>, <FirstTerm>sh.sh_avail</FirstTerm>,
           rsl.sl_name, rsl.sl_avail,
           min(<FirstTerm>sh.sh_avail</FirstTerm>, rsl.sl_avail) AS total_avail,
      FROM shoe_ready shoe_ready, shoe_ready *OLD*,
           shoe_ready *NEW*, shoe rsh,
           shoelace rsl, <FirstTerm>shoe *OLD*</FirstTerm>,
           <FirstTerm>shoe *NEW*</FirstTerm>, <FirstTerm>shoe_data sh</FirstTerm>,
           <FirstTerm>unit un</FirstTerm>
     WHERE (int4ge(min(<FirstTerm>sh.sh_avail</FirstTerm>, rsl.sl_avail), 2)
            AND (bpchareq(rsl.sl_color, <FirstTerm>sh.slcolor</FirstTerm>)
                 AND float8ge(rsl.sl_len_cm, 
                              <FirstTerm>float8mul(sh.slminlen, un.un_fact)</FirstTerm>)
                 AND float8le(rsl.sl_len_cm, 
                              <FirstTerm>float8mul(sh.slmaxlen, un.un_fact)</FirstTerm>)
                )
           )
       <FirstTerm>AND bpchareq(sh.slunit, un.un_name)</FirstTerm>;
</ProgramListing>

    And finally we apply the already well known rule for
    <Filename>shoelace</Filename> (this time on a parsetree that is
    a little more complex) and get

<ProgramListing>
    SELECT sh.shoename, sh.sh_avail,
           <FirstTerm>s.sl_name</FirstTerm>, <FirstTerm>s.sl_avail</FirstTerm>,
           min(sh.sh_avail, <FirstTerm>s.sl_avail</FirstTerm>) AS total_avail
      FROM shoe_ready shoe_ready, shoe_ready *OLD*,
           shoe_ready *NEW*, shoe rsh,
           shoelace rsl, shoe *OLD*,
           shoe *NEW*, shoe_data sh,
           unit un, <FirstTerm>shoelace *OLD*</FirstTerm>,
           <FirstTerm>shoelace *NEW*</FirstTerm>, <FirstTerm>shoelace_data s</FirstTerm>,
           <FirstTerm>unit u</FirstTerm>
     WHERE (    (int4ge(min(sh.sh_avail, <FirstTerm>s.sl_avail</FirstTerm>), 2)
                 AND (bpchareq(<FirstTerm>s.sl_color</FirstTerm>, sh.slcolor)
                      AND float8ge(<FirstTerm>float8mul(s.sl_len, u.un_fact)</FirstTerm>, 
                                   float8mul(sh.slminlen, un.un_fact))
                      AND float8le(<FirstTerm>float8mul(s.sl_len, u.un_fact)</FirstTerm>, 
                                   float8mul(sh.slmaxlen, un.un_fact))
                     )
                )
            AND bpchareq(sh.slunit, un.un_name)
           )
       <FirstTerm>AND bpchareq(s.sl_unit, u.un_name)</FirstTerm>;
</ProgramListing>

    Again we reduce it to a real <Acronym>SQL</Acronym> statement
    that is equivalent to the final output of the rule system:

<ProgramListing>
    SELECT sh.shoename, sh.sh_avail,
           s.sl_name, s.sl_avail,
           min(sh.sh_avail, s.sl_avail) AS total_avail
      FROM shoe_data sh, shoelace_data s, unit u, unit un
     WHERE min(sh.sh_avail, s.sl_avail) >= 2
       AND s.sl_color = sh.slcolor
       AND s.sl_len * u.un_fact >= sh.slminlen * un.un_fact
       AND s.sl_len * u.un_fact <= sh.slmaxlen * un.un_fact
       AND sh.sl_unit = un.un_name
       AND s.sl_unit = u.un_name;
</ProgramListing>

    Recursive processing of rules rewrote one SELECT from a view
    into a parsetree, that is equivalent to exactly that what Al
    had to type if there would be no views at all.

    <Note>
    <Title>Note</Title>
    <Para>
    There is currently no recursion stopping mechanism for view
    rules in the rule system (only for the other rules).
    This doesn't 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
    on the first CREATE VIEW, the second relation does not exist
    and thus the first view cannot select from the second.
    </Para>
    </Note>
</Para>

</Sect2>

<Sect2>
<Title>View Rules in Non-SELECT Statements</Title>

<Para>
    Two details of the parsetree aren't touched in the description of
    view rules above. These are the commandtype and the resultrelation.
    In fact, view rules don't need these informations.
</Para>