1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
|
<!-- manual page source format generated by PolyglotMan v3.0.4, -->
<!-- available via anonymous ftp from ftp.cs.berkeley.edu:/ucb/people/phelps/tcltk/rman.tar.Z -->
<HTML>
<HEAD>
<TITLE>"CREATE(RULE") manual page</TITLE>
</HEAD>
<BODY>
<A HREF="sql.html">SQL Reference Contents</A>
<H2><A NAME="sect0" HREF="#toc0">NAME </A></H2>
create rule - define a new rule
<H2><A NAME="sect1" HREF="#toc1">SYNOPSIS </A></H2>
<B>create </B> <B>rule </B> rule_name
<BR>
<B>as </B> <B>on </B> event <BR>
<B>to </B> object [<B>where </B> clause] <BR>
<B>do </B> [<B>instead
</B>] <BR>
[action | nothing | <B>[ </B>actions...<B>] </B>] <BR>
<H2><A NAME="sect2" HREF="#toc2">DESCRIPTION </A></H2>
<I>The current rule system
implementation is very brittle and is unstable. Users are discouraged
from using rules at this time.</I> <P>
<B>Create rule</B> is used to define a new rule.
<P>
Here, <I>event</I> is one of <I>select</I>, <I>update</I>, <I>delete</I> or <I>insert</I>. <I>Object</I> is either:
a class name <BR>
<I>or </I> <BR>
class.column <BR>
The <B>from</B> clause, the <B>where</B> clause,
and the <I>action</I> are respectively normal SQL <B>from</B> clauses, <B>where</B> clauses
and collections of SQL commands with the following change:
<DL>
<DT><B>new</B> </DT>
<DD>or <B>current</B>
can appear instead of an instance variable whenever an instance variable
is permissible in SQL. </DD>
</DL>
<P>
The semantics of a rule is that at the time an individual
instance is accessed, updated, inserted or deleted, there is a <B>current</B>
instance (for retrieves, updates and deletes) and a <B>new</B> instance (for
updates and appends). If the event specified in the <B>on</B> clause and the
condition specified in the <B>where</B> clause are true for the current instance,
then the <I>action</I> part of the rule is executed. First, however, values
from fields in the current instance and/or the new instance are substituted
for: current.attribute-name <BR>
new.attribute-name <BR>
The <I>action</I> part of the rule
executes with same command and transaction identifier as the user command
that caused activation. <P>
A note of caution about SQL rules is in order.
If the same class name or instance variable appears in the event, <B>where</B>
clause and the <I>action</I> parts of a rule, they are all considered different
tuple variables. More accurately, <B>new</B> and <B>current</B> are the only tuple variables
that are shared between these clauses. For example, the following two rules
have the same semantics: on update to EMP.salary where EMP.name = "Joe"
<BR>
<tt> </tt><tt> </tt>do update EMP ( ... ) where ... <BR>
<P>
on update to EMP-1.salary where EMP-2.name =
"Joe" <BR>
<tt> </tt><tt> </tt>do update EMP-3 ( ... ) where ... <BR>
Each rule can have the optional tag
<B>instead</B>. Without this tag <I>action</I> will be performed in addition to the
user command when the event in the condition part of the rule occurs.
Alternately, the <I>action</I> part will be done instead of the user command.
In this later case, the action can be the keyword <B>nothing</B>. <P>
When choosing
between the rewrite and instance rule systems for a particular rule application,
remember that in the rewrite system <B>current</B> refers to a relation and some
qualifiers whereas in the instance system it refers to an instance (tuple).
<P>
It is very important to note that the <B>rewrite</B> rule system will neither
detect nor process circular rules. For example, though each of the following
two rule definitions are accepted by Postgres, the <I>retrieve</I> command
will cause Postgres to <I>crash</I>: -- <BR>
--Example of a circular rewrite rule combination.
<BR>
-- <BR>
create rule bad_rule_combination_1 is <BR>
<tt> </tt><tt> </tt>on select to EMP <BR>
<tt> </tt><tt> </tt>do instead
select to TOYEMP <BR>
<P>
create rule bad_rule_combination_2 is <BR>
<tt> </tt><tt> </tt>on select to
TOYEMP <BR>
<tt> </tt><tt> </tt>do instead select to EMP <BR>
<P>
-- <BR>
--This attempt to retrieve from EMP
will cause Postgres to crash. <BR>
-- <BR>
select * from EMP <BR>
<P>
You must have <I>rule
definition</I> access to a class in order to define a rule on it (see <I>change
<A HREF="acl.l.html">acl</I>(l)</A>
.
<H2><A NAME="sect3" HREF="#toc3">EXAMPLES </A></H2>
-- <BR>
--Make Sam get the same salary adjustment as Joe <BR>
-- <BR>
create
rule example_1 is <BR>
on update EMP.salary where current.name = "Joe" <BR>
do update EMP (salary = new.salary) <BR>
<tt> </tt><tt> </tt>where EMP.name = "Sam" <BR>
At the
time Joe receives a salary adjustment, the event will become true and
Joe's current instance and proposed new instance are available to the execution
routines. Hence, his new salary is substituted into the <I>action</I> part of
the rule which is subsequently executed. This propagates Joe's salary on
to Sam. -- <BR>
--Make Bill get Joe's salary when it is accessed <BR>
-- <BR>
create rule
example_2 is <BR>
on select to EMP.salary <BR>
where current.name =
"Bill" <BR>
do instead <BR>
<tt> </tt><tt> </tt>select (EMP.salary) from EMP where EMP.name = "Joe"
<BR>
-- <BR>
--Deny Joe access to the salary of employees in the shoe <BR>
--department.
(pg_username() returns the name of the current user) <BR>
-- <BR>
create rule example_3
is <BR>
on select to EMP.salary <BR>
<tt> </tt><tt> </tt>where current.dept = "shoe" <BR>
and pg_username() = "Joe" <BR>
do instead nothing <BR>
-- <BR>
--Create a view
of the employees working in the toy department. <BR>
-- <BR>
create TOYEMP(name =
char16, salary = int4) <BR>
<P>
create rule example_4 is <BR>
on select to TOYEMP
<BR>
do instead select (EMP.name, EMP.salary) from EMP <BR>
<tt> </tt><tt> </tt>where EMP.dept =
"toy" <BR>
-- <BR>
--All new employees must make 5,000 or less <BR>
-- <BR>
create rule example_5
is <BR>
<tt> </tt><tt> </tt>on insert to EMP where new.salary > 5000 <BR>
<tt> </tt><tt> </tt>do update newset salary =
5000 <BR>
<H2><A NAME="sect4" HREF="#toc4">SEE ALSO </A></H2>
drop <A HREF="rule.l.html">rule(l)</A>
, create <A HREF="view.l.html">view(l)</A>
.
<H2><A NAME="sect5" HREF="#toc5">BUGS </A></H2>
<P>
<B>instead</B> rules do not
work properly. <P>
The object in a SQL rule cannot be an array reference and
cannot have parameters. <P>
Aside from the `oid' field, system attributes cannot
be referenced anywhere in a rule. Among other things, this means that
functions of instances (e.g., `foo(emp)' where `emp' is a class) cannot be called
anywhere in a rule. <P>
The rule system store the rule text and query plans
as text attributes. This implies that creation of rules may fail if the
rule plus its various internal representations exceed some value that
is on the order of one page (8KB). <P>
<HR><P>
<A NAME="toc"><B>Table of Contents</B></A><P>
<UL>
<LI><A NAME="toc0" HREF="#sect0">NAME</A></LI>
<LI><A NAME="toc1" HREF="#sect1">SYNOPSIS</A></LI>
<LI><A NAME="toc2" HREF="#sect2">DESCRIPTION</A></LI>
<LI><A NAME="toc3" HREF="#sect3">EXAMPLES</A></LI>
<LI><A NAME="toc4" HREF="#sect4">SEE ALSO</A></LI>
<LI><A NAME="toc5" HREF="#sect5">BUGS</A></LI>
</UL>
</BODY></HTML>
|
