<entry>SSSS</entry>

<entry>seconds past midnight (0-86399)</entry>

</row>

<row>

<entry>AM or A.M. or PM or P.M.</entry>

<entry>meridian indicator (upper case)</entry>

</row>

<row>

<entry>am or a.m. or pm or p.m.</entry>

<entry>meridian indicator (lower case)</entry>

</row>

<row>

<entry>Y,YYY</entry>

<entry>year (4 and more digits) with comma</entry>

<entry>Y</entry>

<entry>last digit of year</entry>

</row>

<row>

<entry>BC or B.C. or AD or A.D.</entry>

<entry>year indicator (upper case)</entry>

</row>

<row>

<entry>bc or b.c. or ad or a.d.</entry>

<entry>year indicator (lower case)</entry>

</row>

<row>

<entry>MONTH</entry>

<entry>full upper case month name (9 chars)</entry>

</row>

<row>

<entry>RM</entry>

<entry>month in Roman Numerals (I-XII; I=JAN)</entry>

<entry>month in Roman Numerals (I-XII; I=JAN) - upper case</entry>

</row>

<row>

<entry>rn</entry>

<entry>month in Roman Numerals (I-XII; I=JAN) - lower case</entry>

</row>

</tbody>

</tgroup>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_rule.sgml,v 1.13 2000/04/07 17:37:24 momjian Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_rule.sgml,v 1.14 2000/04/0719:17:30 momjian Exp $

Postgres documentation

-->

<para>

The <productname>Postgres</productname>

<firstterm>rule system</firstterm> allows one to define an

alternate action to be performed onupdates, inserts, or deletions

alternate action to be performed oninserts, updates, or deletions

from database tables or classes. Currently, rules are used to

implement table views.

</para>

<para>

The semantics of a rule is that at the time an individual instance is

accessed, updated, inserted or deleted, there is a current instance (for

retrieves, updates and deletes) and a new instance (for updates and

appends). If the <replaceable class="parameter">event</replaceable>

accessed, inserted, updated, or deleted, there is a current instance (for

selects, updates and deletes) and a new instance (for inserts and

updates).

If the <replaceable class="parameter">event</replaceable>

specified in the ON clause and the

<replaceable class="parameter">condition</replaceable> specified in the

WHERE clause are true for the current instance, the

<para>

A caution about SQL rules is in order. If the same class name

or instance variable appears in the

<replaceable class="parameter">event</replaceable>, the

<replaceable class="parameter">condition</replaceable> and the

<replaceable class="parameter">event</replaceable>,

<replaceable class="parameter">condition</replaceable> and

<replaceable class="parameter">action</replaceable> parts of a rule,

they are all considered different tuple variables. More accurately,

<literal>new</literal> and <literal>current</literal> are the only tuple

<programlisting>

ON UPDATE TO emp.salary WHERE emp.name = "Joe"

DO

UPDATE emp( ... ) WHERE ...

UPDATE empSET ... WHERE ...

</programlisting>

<programlisting>

ON UPDATE TO emp-1.salary WHERE emp-2.name = "Joe"

DO

UPDATE emp-3( ...) WHERE ...

UPDATE emp-3SET ... WHERE ...

</programlisting>

Each rule can have the optional tag INSTEAD.

<literal>NOTHING</literal>.

</para>

<para>

It is very important to notethat the rewrite rule system

will neither detect nor process circular rules.For example, though each

It is very important to noteto avoid circular rules.

For example, though each

of the following two rule definitions are accepted by

<productname>Postgres</productname>, the

retrieve command will cause <productname>Postgres</productname> to crash:

select command will cause <productname>Postgres</productname> to

report an error because the query cycled too many times:

<example>

<title>Example of a circular rewrite rule combination.</title>

SELECT TO emp;

</programlisting>

<para>

This attempt to retrieve from EMP will cause

<productname>Postgres</productname> to crash.

This attempt to select from EMP will cause

<productname>Postgres</productname> to issue an error

because the queries cycled too many times.

<programlisting>

SELECT * FROM emp;

</programlisting></para>

CREATE RULE example_4 AS

ON SELECT TO toyemp

DO INSTEAD

SELECT(emp.name, emp.salary)

SELECT emp.name, emp.salary

FROM emp

WHERE emp.dept = "toy";

</programlisting>

CREATE RULE example_5 AS

ON INERT TO emp WHERE new.salary > 5000

DO

UPDATE NEWSET salary = 5000;

UPDATE NEWSETSETsalary = 5000;

</programlisting>

</para>

</refsect1>