<secondary sortas="SELECT">for SELECT</secondary>
</indexterm>

<para>
    Rules <literal>ON SELECT</literal> are applied to all queries as the last step, even
    if the command given is an <command>INSERT</command>,
    <command>UPDATE</command> or <command>DELETE</command>. 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
    <command>SELECT</command> rules are described first.
</para>

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

<para>
    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
    <command>INSERT</command>, <command>UPDATE</command>, and
    <command>DELETE</command> 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.
</para>

<para>
    The real tables we need in the first two rule system descriptions
    are these:

<programlisting>
CREATE TABLE shoe_data (
    shoename   text,          -- primary key
    sh_avail   integer,       -- available number of pairs
    slcolor    text,          -- preferred shoelace color
    slminlen   real,          -- minimum shoelace length
    slmaxlen   real,          -- maximum shoelace length
    slunit     text           -- length unit
);

CREATE TABLE shoelace_data (
    sl_name    text,          -- primary key
    sl_avail   integer,       -- available number of pairs
    sl_color   text,          -- shoelace color
    sl_len     real,          -- shoelace length
    sl_unit    text           -- length unit
);

CREATE TABLE unit (
    un_name    text,          -- primary key
    un_fact    real           -- factor to transform to cm
);
</programlisting>

    As you can see, they represent shoe-store data.
</para>

<para>
    The views are created as:

<programlisting>
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,
           least(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 &gt;= rsh.slminlen_cm
       AND rsl.sl_len_cm &lt;= rsh.slmaxlen_cm;
</programlisting>

    The <command>CREATE VIEW</command> command for the
    <literal>shoelace</literal> view (which is the simplest one we
    have) will create a relation <literal>shoelace</literal> and an entry in
    <structname>pg_rewrite</structname> that tells that there is a
    rewrite rule that must be applied whenever the relation <literal>shoelace</literal>
    is referenced in a query's range table.  The rule has no rule
    qualification (discussed later, with the non-<command>SELECT</command>