1
by Martin Pitt
Import upstream version 9.1~beta1 |
1 |
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
|
2 |
<HTML |
|
3 |
><HEAD |
|
4 |
><TITLE |
|
5 |
>Views and the Rule System</TITLE |
|
6 |
><META |
|
7 |
NAME="GENERATOR" |
|
8 |
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK |
|
9 |
REV="MADE" |
|
10 |
HREF="mailto:pgsql-docs@postgresql.org"><LINK |
|
11 |
REL="HOME" |
|
1.1.11
by Martin Pitt
Import upstream version 9.1.9 |
12 |
TITLE="PostgreSQL 9.1.9 Documentation" |
1
by Martin Pitt
Import upstream version 9.1~beta1 |
13 |
HREF="index.html"><LINK |
14 |
REL="UP" |
|
15 |
TITLE="The Rule System" |
|
16 |
HREF="rules.html"><LINK |
|
17 |
REL="PREVIOUS" |
|
18 |
TITLE="The Query Tree" |
|
19 |
HREF="querytree.html"><LINK |
|
20 |
REL="NEXT" |
|
21 |
TITLE="Rules on INSERT, UPDATE, and DELETE" |
|
22 |
HREF="rules-update.html"><LINK |
|
23 |
REL="STYLESHEET" |
|
24 |
TYPE="text/css" |
|
25 |
HREF="stylesheet.css"><META |
|
26 |
HTTP-EQUIV="Content-Type" |
|
27 |
CONTENT="text/html; charset=ISO-8859-1"><META |
|
28 |
NAME="creation" |
|
1.1.11
by Martin Pitt
Import upstream version 9.1.9 |
29 |
CONTENT="2013-04-01T18:35:08"></HEAD |
1
by Martin Pitt
Import upstream version 9.1~beta1 |
30 |
><BODY |
31 |
CLASS="SECT1" |
|
32 |
><DIV |
|
33 |
CLASS="NAVHEADER" |
|
34 |
><TABLE |
|
35 |
SUMMARY="Header navigation table" |
|
36 |
WIDTH="100%" |
|
37 |
BORDER="0" |
|
38 |
CELLPADDING="0" |
|
39 |
CELLSPACING="0" |
|
40 |
><TR |
|
41 |
><TH |
|
42 |
COLSPAN="5" |
|
43 |
ALIGN="center" |
|
44 |
VALIGN="bottom" |
|
45 |
><A |
|
46 |
HREF="index.html" |
|
1.1.11
by Martin Pitt
Import upstream version 9.1.9 |
47 |
>PostgreSQL 9.1.9 Documentation</A |
1
by Martin Pitt
Import upstream version 9.1~beta1 |
48 |
></TH |
49 |
></TR |
|
50 |
><TR |
|
51 |
><TD |
|
52 |
WIDTH="10%" |
|
53 |
ALIGN="left" |
|
54 |
VALIGN="top" |
|
55 |
><A |
|
56 |
TITLE="The Query Tree" |
|
57 |
HREF="querytree.html" |
|
58 |
ACCESSKEY="P" |
|
59 |
>Prev</A |
|
60 |
></TD |
|
61 |
><TD |
|
62 |
WIDTH="10%" |
|
63 |
ALIGN="left" |
|
64 |
VALIGN="top" |
|
65 |
><A |
|
66 |
HREF="rules.html" |
|
1.1.6
by Martin Pitt
Import upstream version 9.1.2 |
67 |
ACCESSKEY="U" |
68 |
>Up</A |
|
1
by Martin Pitt
Import upstream version 9.1~beta1 |
69 |
></TD |
70 |
><TD |
|
71 |
WIDTH="60%" |
|
72 |
ALIGN="center" |
|
73 |
VALIGN="bottom" |
|
74 |
>Chapter 37. The Rule System</TD |
|
75 |
><TD |
|
1.1.6
by Martin Pitt
Import upstream version 9.1.2 |
76 |
WIDTH="20%" |
1
by Martin Pitt
Import upstream version 9.1~beta1 |
77 |
ALIGN="right" |
78 |
VALIGN="top" |
|
79 |
><A |
|
80 |
TITLE="Rules on INSERT, UPDATE, and DELETE" |
|
81 |
HREF="rules-update.html" |
|
82 |
ACCESSKEY="N" |
|
83 |
>Next</A |
|
84 |
></TD |
|
85 |
></TR |
|
86 |
></TABLE |
|
87 |
><HR |
|
88 |
ALIGN="LEFT" |
|
89 |
WIDTH="100%"></DIV |
|
90 |
><DIV |
|
91 |
CLASS="SECT1" |
|
92 |
><H1 |
|
93 |
CLASS="SECT1" |
|
94 |
><A |
|
95 |
NAME="RULES-VIEWS" |
|
96 |
>37.2. Views and the Rule System</A |
|
97 |
></H1 |
|
98 |
><P |
|
99 |
> Views in <SPAN |
|
100 |
CLASS="PRODUCTNAME" |
|
101 |
>PostgreSQL</SPAN |
|
102 |
> are implemented
|
|
103 |
using the rule system. In fact, there is essentially no difference |
|
104 |
between: |
|
105 |
||
106 |
</P><PRE |
|
107 |
CLASS="PROGRAMLISTING" |
|
108 |
>CREATE VIEW myview AS SELECT * FROM mytab;</PRE |
|
109 |
><P> |
|
110 |
||
111 |
compared against the two commands: |
|
112 |
||
113 |
</P><PRE |
|
114 |
CLASS="PROGRAMLISTING" |
|
115 |
>CREATE TABLE myview (<TT |
|
116 |
CLASS="REPLACEABLE" |
|
117 |
><I |
|
118 |
>same column list as mytab</I |
|
119 |
></TT |
|
120 |
>);
|
|
121 |
CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD |
|
122 |
SELECT * FROM mytab;</PRE |
|
123 |
><P> |
|
124 |
||
125 |
because this is exactly what the <TT |
|
126 |
CLASS="COMMAND" |
|
127 |
>CREATE VIEW</TT |
|
128 |
>
|
|
129 |
command does internally. This has some side effects. One of them |
|
130 |
is that the information about a view in the |
|
131 |
<SPAN |
|
132 |
CLASS="PRODUCTNAME" |
|
133 |
>PostgreSQL</SPAN |
|
134 |
> system catalogs is exactly
|
|
135 |
the same as it is for a table. So for the parser, there is |
|
136 |
absolutely no difference between a table and a view. They are the |
|
137 |
same thing: relations.</P |
|
138 |
><DIV |
|
139 |
CLASS="SECT2" |
|
140 |
><H2 |
|
141 |
CLASS="SECT2" |
|
142 |
><A |
|
143 |
NAME="RULES-SELECT" |
|
144 |
>37.2.1. How <TT |
|
145 |
CLASS="COMMAND" |
|
146 |
>SELECT</TT |
|
147 |
> Rules Work</A |
|
148 |
></H2 |
|
149 |
><P |
|
150 |
> Rules <TT |
|
151 |
CLASS="LITERAL" |
|
152 |
>ON SELECT</TT |
|
153 |
> are applied to all queries as the last step, even
|
|
154 |
if the command given is an <TT |
|
155 |
CLASS="COMMAND" |
|
156 |
>INSERT</TT |
|
157 |
>,
|
|
158 |
<TT |
|
159 |
CLASS="COMMAND" |
|
160 |
>UPDATE</TT |
|
161 |
> or <TT |
|
162 |
CLASS="COMMAND" |
|
163 |
>DELETE</TT |
|
164 |
>. And they
|
|
165 |
have different semantics from rules on the other command types in that they modify the |
|
166 |
query tree in place instead of creating a new one. So |
|
167 |
<TT |
|
168 |
CLASS="COMMAND" |
|
169 |
>SELECT</TT |
|
170 |
> rules are described first.</P |
|
171 |
><P |
|
172 |
> Currently, there can be only one action in an <TT |
|
173 |
CLASS="LITERAL" |
|
174 |
>ON SELECT</TT |
|
175 |
> rule, and it must
|
|
176 |
be an unconditional <TT |
|
177 |
CLASS="COMMAND" |
|
178 |
>SELECT</TT |
|
179 |
> action that is <TT |
|
180 |
CLASS="LITERAL" |
|
181 |
>INSTEAD</TT |
|
182 |
>. This restriction was
|
|
183 |
required to make rules safe enough to open them for ordinary users, and |
|
184 |
it restricts <TT |
|
185 |
CLASS="LITERAL" |
|
186 |
>ON SELECT</TT |
|
187 |
> rules to act like views.</P |
|
188 |
><P |
|
189 |
> The examples for this chapter are two join views that do some
|
|
190 |
calculations and some more views using them in turn. One of the |
|
191 |
two first views is customized later by adding rules for |
|
192 |
<TT |
|
193 |
CLASS="COMMAND" |
|
194 |
>INSERT</TT |
|
195 |
>, <TT |
|
196 |
CLASS="COMMAND" |
|
197 |
>UPDATE</TT |
|
198 |
>, and
|
|
199 |
<TT |
|
200 |
CLASS="COMMAND" |
|
201 |
>DELETE</TT |
|
202 |
> operations so that the final result will
|
|
203 |
be a view that behaves like a real table with some magic |
|
204 |
functionality. This is not such a simple example to start from and |
|
205 |
this makes things harder to get into. But it's better to have one |
|
206 |
example that covers all the points discussed step by step rather |
|
207 |
than having many different ones that might mix up in mind.</P |
|
208 |
><P |
|
209 |
>For the example, we need a little <TT |
|
210 |
CLASS="LITERAL" |
|
211 |
>min</TT |
|
212 |
> function that
|
|
213 |
returns the lower of 2 integer values. We create that as: |
|
214 |
||
215 |
</P><PRE |
|
216 |
CLASS="PROGRAMLISTING" |
|
217 |
>CREATE FUNCTION min(integer, integer) RETURNS integer AS $$
|
|
218 |
SELECT CASE WHEN $1 < $2 THEN $1 ELSE $2 END
|
|
219 |
$$ LANGUAGE SQL STRICT;</PRE |
|
220 |
><P></P |
|
221 |
><P |
|
222 |
> The real tables we need in the first two rule system descriptions
|
|
223 |
are these: |
|
224 |
||
225 |
</P><PRE |
|
226 |
CLASS="PROGRAMLISTING" |
|
227 |
>CREATE TABLE shoe_data (
|
|
228 |
shoename text, -- primary key |
|
229 |
sh_avail integer, -- available number of pairs |
|
230 |
slcolor text, -- preferred shoelace color |
|
231 |
slminlen real, -- minimum shoelace length |
|
232 |
slmaxlen real, -- maximum shoelace length |
|
233 |
slunit text -- length unit |
|
234 |
); |
|
235 |
||
236 |
CREATE TABLE shoelace_data ( |
|
237 |
sl_name text, -- primary key |
|
238 |
sl_avail integer, -- available number of pairs |
|
239 |
sl_color text, -- shoelace color |
|
240 |
sl_len real, -- shoelace length |
|
241 |
sl_unit text -- length unit |
|
242 |
); |
|
243 |
||
244 |
CREATE TABLE unit ( |
|
245 |
un_name text, -- primary key |
|
246 |
un_fact real -- factor to transform to cm |
|
247 |
);</PRE |
|
248 |
><P> |
|
249 |
||
250 |
As you can see, they represent shoe-store data.</P |
|
251 |
><P |
|
252 |
> The views are created as:
|
|
253 |
||
254 |
</P><PRE |
|
255 |
CLASS="PROGRAMLISTING" |
|
256 |
>CREATE VIEW shoe AS
|
|
257 |
SELECT sh.shoename, |
|
258 |
sh.sh_avail, |
|
259 |
sh.slcolor, |
|
260 |
sh.slminlen, |
|
261 |
sh.slminlen * un.un_fact AS slminlen_cm, |
|
262 |
sh.slmaxlen, |
|
263 |
sh.slmaxlen * un.un_fact AS slmaxlen_cm, |
|
264 |
sh.slunit |
|
265 |
FROM shoe_data sh, unit un |
|
266 |
WHERE sh.slunit = un.un_name; |
|
267 |
||
268 |
CREATE VIEW shoelace AS |
|
269 |
SELECT s.sl_name, |
|
270 |
s.sl_avail, |
|
271 |
s.sl_color, |
|
272 |
s.sl_len, |
|
273 |
s.sl_unit, |
|
274 |
s.sl_len * u.un_fact AS sl_len_cm |
|
275 |
FROM shoelace_data s, unit u |
|
276 |
WHERE s.sl_unit = u.un_name; |
|
277 |
||
278 |
CREATE VIEW shoe_ready AS |
|
279 |
SELECT rsh.shoename, |
|
280 |
rsh.sh_avail, |
|
281 |
rsl.sl_name, |
|
282 |
rsl.sl_avail, |
|
283 |
min(rsh.sh_avail, rsl.sl_avail) AS total_avail |
|
284 |
FROM shoe rsh, shoelace rsl |
|
285 |
WHERE rsl.sl_color = rsh.slcolor |
|
286 |
AND rsl.sl_len_cm >= rsh.slminlen_cm
|
|
287 |
AND rsl.sl_len_cm <= rsh.slmaxlen_cm;</PRE |
|
288 |
><P> |
|
289 |
||
290 |
The <TT |
|
291 |
CLASS="COMMAND" |
|
292 |
>CREATE VIEW</TT |
|
293 |
> command for the
|
|
294 |
<TT |
|
295 |
CLASS="LITERAL" |
|
296 |
>shoelace</TT |
|
297 |
> view (which is the simplest one we
|
|
298 |
have) will create a relation <TT |
|
299 |
CLASS="LITERAL" |
|
300 |
>shoelace</TT |
|
301 |
> and an entry in
|
|
302 |
<TT |
|
303 |
CLASS="STRUCTNAME" |
|
304 |
>pg_rewrite</TT |
|
305 |
> that tells that there is a
|
|
306 |
rewrite rule that must be applied whenever the relation <TT |
|
307 |
CLASS="LITERAL" |
|
308 |
>shoelace</TT |
|
309 |
>
|
|
310 |
is referenced in a query's range table. The rule has no rule |
|
311 |
qualification (discussed later, with the non-<TT |
|
312 |
CLASS="COMMAND" |
|
313 |
>SELECT</TT |
|
314 |
> rules, since
|
|
315 |
<TT |
|
316 |
CLASS="COMMAND" |
|
317 |
>SELECT</TT |
|
318 |
> rules currently cannot have them) and it is <TT |
|
319 |
CLASS="LITERAL" |
|
320 |
>INSTEAD</TT |
|
321 |
>. Note
|
|
322 |
that rule qualifications are not the same as query qualifications. |
|
323 |
The action of our rule has a query qualification. |
|
324 |
The action of the rule is one query tree that is a copy of the |
|
325 |
<TT |
|
326 |
CLASS="COMMAND" |
|
327 |
>SELECT</TT |
|
328 |
> statement in the view creation command.</P |
|
329 |
><DIV |
|
330 |
CLASS="NOTE" |
|
331 |
><BLOCKQUOTE |
|
332 |
CLASS="NOTE" |
|
333 |
><P |
|
334 |
><B |
|
335 |
>Note: </B |
|
336 |
> The two extra range
|
|
337 |
table entries for <TT |
|
338 |
CLASS="LITERAL" |
|
339 |
>NEW</TT |
|
340 |
> and <TT |
|
341 |
CLASS="LITERAL" |
|
342 |
>OLD</TT |
|
343 |
> that you can see in
|
|
344 |
the <TT |
|
345 |
CLASS="STRUCTNAME" |
|
346 |
>pg_rewrite</TT |
|
347 |
> entry aren't of interest
|
|
348 |
for <TT |
|
349 |
CLASS="COMMAND" |
|
350 |
>SELECT</TT |
|
351 |
> rules.
|
|
352 |
</P |
|
353 |
></BLOCKQUOTE |
|
354 |
></DIV |
|
355 |
><P |
|
356 |
> Now we populate <TT |
|
357 |
CLASS="LITERAL" |
|
358 |
>unit</TT |
|
359 |
>, <TT |
|
360 |
CLASS="LITERAL" |
|
361 |
>shoe_data</TT |
|
362 |
>
|
|
363 |
and <TT |
|
364 |
CLASS="LITERAL" |
|
365 |
>shoelace_data</TT |
|
366 |
> and run a simple query on a view:
|
|
367 |
||
368 |
</P><PRE |
|
369 |
CLASS="PROGRAMLISTING" |
|
370 |
>INSERT INTO unit VALUES ('cm', 1.0);
|
|
371 |
INSERT INTO unit VALUES ('m', 100.0); |
|
372 |
INSERT INTO unit VALUES ('inch', 2.54); |
|
373 |
||
374 |
INSERT INTO shoe_data VALUES ('sh1', 2, 'black', 70.0, 90.0, 'cm'); |
|
375 |
INSERT INTO shoe_data VALUES ('sh2', 0, 'black', 30.0, 40.0, 'inch'); |
|
376 |
INSERT INTO shoe_data VALUES ('sh3', 4, 'brown', 50.0, 65.0, 'cm'); |
|
377 |
INSERT INTO shoe_data VALUES ('sh4', 3, 'brown', 40.0, 50.0, 'inch'); |
|
378 |
||
379 |
INSERT INTO shoelace_data VALUES ('sl1', 5, 'black', 80.0, 'cm'); |
|
380 |
INSERT INTO shoelace_data VALUES ('sl2', 6, 'black', 100.0, 'cm'); |
|
381 |
INSERT INTO shoelace_data VALUES ('sl3', 0, 'black', 35.0 , 'inch'); |
|
382 |
INSERT INTO shoelace_data VALUES ('sl4', 8, 'black', 40.0 , 'inch'); |
|
383 |
INSERT INTO shoelace_data VALUES ('sl5', 4, 'brown', 1.0 , 'm'); |
|
384 |
INSERT INTO shoelace_data VALUES ('sl6', 0, 'brown', 0.9 , 'm'); |
|
385 |
INSERT INTO shoelace_data VALUES ('sl7', 7, 'brown', 60 , 'cm'); |
|
386 |
INSERT INTO shoelace_data VALUES ('sl8', 1, 'brown', 40 , 'inch'); |
|
387 |
||
388 |
SELECT * FROM shoelace; |
|
389 |
||
390 |
sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm |
|
391 |
-----------+----------+----------+--------+---------+----------- |
|
392 |
sl1 | 5 | black | 80 | cm | 80 |
|
393 |
sl2 | 6 | black | 100 | cm | 100 |
|
394 |
sl7 | 7 | brown | 60 | cm | 60 |
|
395 |
sl3 | 0 | black | 35 | inch | 88.9 |
|
396 |
sl4 | 8 | black | 40 | inch | 101.6 |
|
397 |
sl8 | 1 | brown | 40 | inch | 101.6 |
|
398 |
sl5 | 4 | brown | 1 | m | 100 |
|
399 |
sl6 | 0 | brown | 0.9 | m | 90 |
|
400 |
(8 rows)</PRE |
|
401 |
><P> |
|
402 |
</P |
|
403 |
><P |
|
404 |
> This is the simplest <TT |
|
405 |
CLASS="COMMAND" |
|
406 |
>SELECT</TT |
|
407 |
> you can do on our
|
|
408 |
views, so we take this opportunity to explain the basics of view |
|
409 |
rules. The <TT |
|
410 |
CLASS="LITERAL" |
|
411 |
>SELECT * FROM shoelace</TT |
|
412 |
> was
|
|
413 |
interpreted by the parser and produced the query tree: |
|
414 |
||
415 |
</P><PRE |
|
416 |
CLASS="PROGRAMLISTING" |
|
417 |
>SELECT shoelace.sl_name, shoelace.sl_avail,
|
|
418 |
shoelace.sl_color, shoelace.sl_len, |
|
419 |
shoelace.sl_unit, shoelace.sl_len_cm |
|
420 |
FROM shoelace shoelace;</PRE |
|
421 |
><P> |
|
422 |
||
423 |
and this is given to the rule system. The rule system walks through the |
|
424 |
range table and checks if there are rules |
|
425 |
for any relation. When processing the range table entry for |
|
426 |
<TT |
|
427 |
CLASS="LITERAL" |
|
428 |
>shoelace</TT |
|
429 |
> (the only one up to now) it finds the
|
|
430 |
<TT |
|
431 |
CLASS="LITERAL" |
|
432 |
>_RETURN</TT |
|
433 |
> rule with the query tree:
|
|
434 |
||
435 |
</P><PRE |
|
436 |
CLASS="PROGRAMLISTING" |
|
437 |
>SELECT s.sl_name, s.sl_avail,
|
|
438 |
s.sl_color, s.sl_len, s.sl_unit, |
|
439 |
s.sl_len * u.un_fact AS sl_len_cm |
|
440 |
FROM shoelace old, shoelace new, |
|
441 |
shoelace_data s, unit u |
|
442 |
WHERE s.sl_unit = u.un_name;</PRE |
|
443 |
><P></P |
|
444 |
><P |
|
445 |
> To expand the view, the rewriter simply creates a subquery range-table
|
|
446 |
entry containing the rule's action query tree, and substitutes this |
|
447 |
range table entry for the original one that referenced the view. The |
|
448 |
resulting rewritten query tree is almost the same as if you had typed: |
|
449 |
||
450 |
</P><PRE |
|
451 |
CLASS="PROGRAMLISTING" |
|
452 |
>SELECT shoelace.sl_name, shoelace.sl_avail,
|
|
453 |
shoelace.sl_color, shoelace.sl_len, |
|
454 |
shoelace.sl_unit, shoelace.sl_len_cm |
|
455 |
FROM (SELECT s.sl_name, |
|
456 |
s.sl_avail, |
|
457 |
s.sl_color, |
|
458 |
s.sl_len, |
|
459 |
s.sl_unit, |
|
460 |
s.sl_len * u.un_fact AS sl_len_cm |
|
461 |
FROM shoelace_data s, unit u |
|
462 |
WHERE s.sl_unit = u.un_name) shoelace;</PRE |
|
463 |
><P> |
|
464 |
||
465 |
There is one difference however: the subquery's range table has two |
|
466 |
extra entries <TT |
|
467 |
CLASS="LITERAL" |
|
468 |
>shoelace old</TT |
|
469 |
> and <TT |
|
470 |
CLASS="LITERAL" |
|
471 |
>shoelace new</TT |
|
472 |
>. These entries don't
|
|
473 |
participate directly in the query, since they aren't referenced by |
|
474 |
the subquery's join tree or target list. The rewriter uses them |
|
475 |
to store the access privilege check information that was originally present |
|
476 |
in the range-table entry that referenced the view. In this way, the |
|
477 |
executor will still check that the user has proper privileges to access |
|
478 |
the view, even though there's no direct use of the view in the rewritten |
|
479 |
query.</P |
|
480 |
><P |
|
481 |
> That was the first rule applied. The rule system will continue checking
|
|
482 |
the remaining range-table entries in the top query (in this example there |
|
483 |
are no more), and it will recursively check the range-table entries in |
|
484 |
the added subquery to see if any of them reference views. (But it |
|
485 |
won't expand <TT |
|
486 |
CLASS="LITERAL" |
|
487 |
>old</TT |
|
488 |
> or <TT |
|
489 |
CLASS="LITERAL" |
|
490 |
>new</TT |
|
491 |
> — otherwise we'd have infinite recursion!) |
|
492 |
In this example, there are no rewrite rules for <TT |
|
493 |
CLASS="LITERAL" |
|
494 |
>shoelace_data</TT |
|
495 |
> or <TT |
|
496 |
CLASS="LITERAL" |
|
497 |
>unit</TT |
|
498 |
>,
|
|
499 |
so rewriting is complete and the above is the final result given to |
|
500 |
the planner.</P |
|
501 |
><P |
|
502 |
> Now we want to write a query that finds out for which shoes currently in the store
|
|
503 |
we have the matching shoelaces (color and length) and where the |
|
504 |
total number of exactly matching pairs is greater or equal to two. |
|
505 |
||
506 |
</P><PRE |
|
507 |
CLASS="PROGRAMLISTING" |
|
508 |
>SELECT * FROM shoe_ready WHERE total_avail >= 2; |
|
509 |
||
510 |
shoename | sh_avail | sl_name | sl_avail | total_avail |
|
511 |
----------+----------+---------+----------+------------- |
|
512 |
sh1 | 2 | sl1 | 5 | 2 |
|
513 |
sh3 | 4 | sl7 | 7 | 4 |
|
514 |
(2 rows)</PRE |
|
515 |
><P></P |
|
516 |
><P |
|
517 |
> The output of the parser this time is the query tree:
|
|
518 |
||
519 |
</P><PRE |
|
520 |
CLASS="PROGRAMLISTING" |
|
521 |
>SELECT shoe_ready.shoename, shoe_ready.sh_avail,
|
|
522 |
shoe_ready.sl_name, shoe_ready.sl_avail, |
|
523 |
shoe_ready.total_avail |
|
524 |
FROM shoe_ready shoe_ready |
|
525 |
WHERE shoe_ready.total_avail >= 2;</PRE |
|
526 |
><P> |
|
527 |
||
528 |
The first rule applied will be the one for the |
|
529 |
<TT |
|
530 |
CLASS="LITERAL" |
|
531 |
>shoe_ready</TT |
|
532 |
> view and it results in the
|
|
533 |
query tree: |
|
534 |
||
535 |
</P><PRE |
|
536 |
CLASS="PROGRAMLISTING" |
|
537 |
>SELECT shoe_ready.shoename, shoe_ready.sh_avail,
|
|
538 |
shoe_ready.sl_name, shoe_ready.sl_avail, |
|
539 |
shoe_ready.total_avail |
|
540 |
FROM (SELECT rsh.shoename, |
|
541 |
rsh.sh_avail, |
|
542 |
rsl.sl_name, |
|
543 |
rsl.sl_avail, |
|
544 |
min(rsh.sh_avail, rsl.sl_avail) AS total_avail |
|
545 |
FROM shoe rsh, shoelace rsl |
|
546 |
WHERE rsl.sl_color = rsh.slcolor |
|
547 |
AND rsl.sl_len_cm >= rsh.slminlen_cm
|
|
548 |
AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready
|
|
549 |
WHERE shoe_ready.total_avail >= 2;</PRE |
|
550 |
><P> |
|
551 |
||
552 |
Similarly, the rules for <TT |
|
553 |
CLASS="LITERAL" |
|
554 |
>shoe</TT |
|
555 |
> and
|
|
556 |
<TT |
|
557 |
CLASS="LITERAL" |
|
558 |
>shoelace</TT |
|
559 |
> are substituted into the range table of
|
|
560 |
the subquery, leading to a three-level final query tree: |
|
561 |
||
562 |
</P><PRE |
|
563 |
CLASS="PROGRAMLISTING" |
|
564 |
>SELECT shoe_ready.shoename, shoe_ready.sh_avail,
|
|
565 |
shoe_ready.sl_name, shoe_ready.sl_avail, |
|
566 |
shoe_ready.total_avail |
|
567 |
FROM (SELECT rsh.shoename, |
|
568 |
rsh.sh_avail, |
|
569 |
rsl.sl_name, |
|
570 |
rsl.sl_avail, |
|
571 |
min(rsh.sh_avail, rsl.sl_avail) AS total_avail |
|
572 |
FROM (SELECT sh.shoename, |
|
573 |
sh.sh_avail, |
|
574 |
sh.slcolor, |
|
575 |
sh.slminlen, |
|
576 |
sh.slminlen * un.un_fact AS slminlen_cm, |
|
577 |
sh.slmaxlen, |
|
578 |
sh.slmaxlen * un.un_fact AS slmaxlen_cm, |
|
579 |
sh.slunit |
|
580 |
FROM shoe_data sh, unit un |
|
581 |
WHERE sh.slunit = un.un_name) rsh, |
|
582 |
(SELECT s.sl_name, |
|
583 |
s.sl_avail, |
|
584 |
s.sl_color, |
|
585 |
s.sl_len, |
|
586 |
s.sl_unit, |
|
587 |
s.sl_len * u.un_fact AS sl_len_cm |
|
588 |
FROM shoelace_data s, unit u |
|
589 |
WHERE s.sl_unit = u.un_name) rsl |
|
590 |
WHERE rsl.sl_color = rsh.slcolor |
|
591 |
AND rsl.sl_len_cm >= rsh.slminlen_cm
|
|
592 |
AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready
|
|
593 |
WHERE shoe_ready.total_avail > 2;</PRE |
|
594 |
><P> |
|
595 |
</P |
|
596 |
><P |
|
597 |
> It turns out that the planner will collapse this tree into a
|
|
598 |
two-level query tree: the bottommost <TT |
|
599 |
CLASS="COMMAND" |
|
600 |
>SELECT</TT |
|
601 |
>
|
|
602 |
commands will be <SPAN |
|
603 |
CLASS="QUOTE" |
|
604 |
>"pulled up"</SPAN |
|
605 |
> into the middle
|
|
606 |
<TT |
|
607 |
CLASS="COMMAND" |
|
608 |
>SELECT</TT |
|
609 |
> since there's no need to process them
|
|
610 |
separately. But the middle <TT |
|
611 |
CLASS="COMMAND" |
|
612 |
>SELECT</TT |
|
613 |
> will remain
|
|
614 |
separate from the top, because it contains aggregate functions. |
|
615 |
If we pulled those up it would change the behavior of the topmost |
|
616 |
<TT |
|
617 |
CLASS="COMMAND" |
|
618 |
>SELECT</TT |
|
619 |
>, which we don't want. However,
|
|
620 |
collapsing the query tree is an optimization that the rewrite |
|
621 |
system doesn't have to concern itself with. |
|
622 |
</P |
|
623 |
></DIV |
|
624 |
><DIV |
|
625 |
CLASS="SECT2" |
|
626 |
><H2 |
|
627 |
CLASS="SECT2" |
|
628 |
><A |
|
1.1.11
by Martin Pitt
Import upstream version 9.1.9 |
629 |
NAME="AEN52852" |
1
by Martin Pitt
Import upstream version 9.1~beta1 |
630 |
>37.2.2. View Rules in Non-<TT |
631 |
CLASS="COMMAND" |
|
632 |
>SELECT</TT |
|
633 |
> Statements</A |
|
634 |
></H2 |
|
635 |
><P |
|
636 |
> Two details of the query tree aren't touched in the description of
|
|
637 |
view rules above. These are the command type and the result relation. |
|
638 |
In fact, the command type is not needed by view rules, but the result |
|
639 |
relation may affect the way in which the query rewriter works, because |
|
640 |
special care needs to be taken if the result relation is a view.</P |
|
641 |
><P |
|
642 |
> There are only a few differences between a query tree for a
|
|
643 |
<TT |
|
644 |
CLASS="COMMAND" |
|
645 |
>SELECT</TT |
|
646 |
> and one for any other
|
|
647 |
command. Obviously, they have a different command type and for a |
|
648 |
command other than a <TT |
|
649 |
CLASS="COMMAND" |
|
650 |
>SELECT</TT |
|
651 |
>, the result
|
|
652 |
relation points to the range-table entry where the result should |
|
653 |
go. Everything else is absolutely the same. So having two tables |
|
654 |
<TT |
|
655 |
CLASS="LITERAL" |
|
656 |
>t1</TT |
|
657 |
> and <TT |
|
658 |
CLASS="LITERAL" |
|
659 |
>t2</TT |
|
660 |
> with columns <TT |
|
661 |
CLASS="LITERAL" |
|
662 |
>a</TT |
|
663 |
> and
|
|
664 |
<TT |
|
665 |
CLASS="LITERAL" |
|
666 |
>b</TT |
|
667 |
>, the query trees for the two statements:
|
|
668 |
||
669 |
</P><PRE |
|
670 |
CLASS="PROGRAMLISTING" |
|
671 |
>SELECT t2.b FROM t1, t2 WHERE t1.a = t2.a;
|
|
672 |
||
673 |
UPDATE t1 SET b = t2.b FROM t2 WHERE t1.a = t2.a;</PRE |
|
674 |
><P> |
|
675 |
||
676 |
are nearly identical. In particular: |
|
677 |
||
678 |
<P |
|
679 |
></P |
|
680 |
></P><UL |
|
681 |
><LI |
|
682 |
><P |
|
683 |
> The range tables contain entries for the tables <TT |
|
684 |
CLASS="LITERAL" |
|
685 |
>t1</TT |
|
686 |
> and <TT |
|
687 |
CLASS="LITERAL" |
|
688 |
>t2</TT |
|
689 |
>.
|
|
690 |
</P |
|
691 |
></LI |
|
692 |
><LI |
|
693 |
><P |
|
694 |
> The target lists contain one variable that points to column
|
|
695 |
<TT |
|
696 |
CLASS="LITERAL" |
|
697 |
>b</TT |
|
698 |
> of the range table entry for table <TT |
|
699 |
CLASS="LITERAL" |
|
700 |
>t2</TT |
|
701 |
>.
|
|
702 |
</P |
|
703 |
></LI |
|
704 |
><LI |
|
705 |
><P |
|
706 |
> The qualification expressions compare the columns <TT |
|
707 |
CLASS="LITERAL" |
|
708 |
>a</TT |
|
709 |
> of both
|
|
710 |
range-table entries for equality. |
|
711 |
</P |
|
712 |
></LI |
|
713 |
><LI |
|
714 |
><P |
|
715 |
> The join trees show a simple join between <TT |
|
716 |
CLASS="LITERAL" |
|
717 |
>t1</TT |
|
718 |
> and <TT |
|
719 |
CLASS="LITERAL" |
|
720 |
>t2</TT |
|
721 |
>.
|
|
722 |
</P |
|
723 |
></LI |
|
724 |
></UL |
|
725 |
><P> |
|
726 |
</P |
|
727 |
><P |
|
728 |
> The consequence is, that both query trees result in similar
|
|
729 |
execution plans: They are both joins over the two tables. For the |
|
730 |
<TT |
|
731 |
CLASS="COMMAND" |
|
732 |
>UPDATE</TT |
|
733 |
> the missing columns from <TT |
|
734 |
CLASS="LITERAL" |
|
735 |
>t1</TT |
|
736 |
> are added to
|
|
737 |
the target list by the planner and the final query tree will read |
|
738 |
as: |
|
739 |
||
740 |
</P><PRE |
|
741 |
CLASS="PROGRAMLISTING" |
|
742 |
>UPDATE t1 SET a = t1.a, b = t2.b FROM t2 WHERE t1.a = t2.a;</PRE |
|
743 |
><P> |
|
744 |
||
745 |
and thus the executor run over the join will produce exactly the |
|
746 |
same result set as: |
|
747 |
||
748 |
</P><PRE |
|
749 |
CLASS="PROGRAMLISTING" |
|
750 |
>SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a;</PRE |
|
751 |
><P> |
|
752 |
||
753 |
But there is a little problem in |
|
754 |
<TT |
|
755 |
CLASS="COMMAND" |
|
756 |
>UPDATE</TT |
|
757 |
>: the part of the executor plan that does
|
|
758 |
the join does not care what the results from the join are |
|
759 |
meant for. It just produces a result set of rows. The fact that |
|
760 |
one is a <TT |
|
761 |
CLASS="COMMAND" |
|
762 |
>SELECT</TT |
|
763 |
> command and the other is an
|
|
764 |
<TT |
|
765 |
CLASS="COMMAND" |
|
766 |
>UPDATE</TT |
|
767 |
> is handled higher up in the executor, where
|
|
768 |
it knows that this is an <TT |
|
769 |
CLASS="COMMAND" |
|
770 |
>UPDATE</TT |
|
771 |
>, and it knows that
|
|
772 |
this result should go into table <TT |
|
773 |
CLASS="LITERAL" |
|
774 |
>t1</TT |
|
775 |
>. But which of the rows
|
|
776 |
that are there has to be replaced by the new row?</P |
|
777 |
><P |
|
778 |
> To resolve this problem, another entry is added to the target list
|
|
779 |
in <TT |
|
780 |
CLASS="COMMAND" |
|
781 |
>UPDATE</TT |
|
782 |
> (and also in
|
|
783 |
<TT |
|
784 |
CLASS="COMMAND" |
|
785 |
>DELETE</TT |
|
786 |
>) statements: the current tuple ID
|
|
787 |
(<ACRONYM |
|
788 |
CLASS="ACRONYM" |
|
789 |
>CTID</ACRONYM |
|
790 |
>).
|
|
791 |
This is a system column containing the |
|
792 |
file block number and position in the block for the row. Knowing |
|
793 |
the table, the <ACRONYM |
|
794 |
CLASS="ACRONYM" |
|
795 |
>CTID</ACRONYM |
|
796 |
> can be used to retrieve the
|
|
797 |
original row of <TT |
|
798 |
CLASS="LITERAL" |
|
799 |
>t1</TT |
|
800 |
> to be updated. After adding the
|
|
801 |
<ACRONYM |
|
802 |
CLASS="ACRONYM" |
|
803 |
>CTID</ACRONYM |
|
804 |
> to the target list, the query actually looks like:
|
|
805 |
||
806 |
</P><PRE |
|
807 |
CLASS="PROGRAMLISTING" |
|
808 |
>SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;</PRE |
|
809 |
><P> |
|
810 |
||
811 |
Now another detail of <SPAN |
|
812 |
CLASS="PRODUCTNAME" |
|
813 |
>PostgreSQL</SPAN |
|
814 |
> enters
|
|
815 |
the stage. Old table rows aren't overwritten, and this |
|
816 |
is why <TT |
|
817 |
CLASS="COMMAND" |
|
818 |
>ROLLBACK</TT |
|
819 |
> is fast. In an <TT |
|
820 |
CLASS="COMMAND" |
|
821 |
>UPDATE</TT |
|
822 |
>,
|
|
823 |
the new result row is inserted into the table (after stripping the |
|
824 |
<ACRONYM |
|
825 |
CLASS="ACRONYM" |
|
826 |
>CTID</ACRONYM |
|
827 |
>) and in the row header of the old row, which the
|
|
828 |
<ACRONYM |
|
829 |
CLASS="ACRONYM" |
|
830 |
>CTID</ACRONYM |
|
831 |
> pointed to, the <TT |
|
832 |
CLASS="LITERAL" |
|
833 |
>cmax</TT |
|
834 |
> and
|
|
835 |
<TT |
|
836 |
CLASS="LITERAL" |
|
837 |
>xmax</TT |
|
838 |
> entries are set to the current command counter
|
|
839 |
and current transaction ID. Thus the old row is hidden, and after |
|
840 |
the transaction commits the vacuum cleaner can eventually remove |
|
841 |
the dead row.</P |
|
842 |
><P |
|
843 |
> Knowing all that, we can simply apply view rules in absolutely
|
|
844 |
the same way to any command. There is no difference.</P |
|
845 |
></DIV |
|
846 |
><DIV |
|
847 |
CLASS="SECT2" |
|
848 |
><H2 |
|
849 |
CLASS="SECT2" |
|
850 |
><A |
|
1.1.11
by Martin Pitt
Import upstream version 9.1.9 |
851 |
NAME="AEN52908" |
1
by Martin Pitt
Import upstream version 9.1~beta1 |
852 |
>37.2.3. The Power of Views in <SPAN |
853 |
CLASS="PRODUCTNAME" |
|
854 |
>PostgreSQL</SPAN |
|
855 |
></A |
|
856 |
></H2 |
|
857 |
><P |
|
858 |
> The above demonstrates how the rule system incorporates view
|
|
859 |
definitions into the original query tree. In the second example, a |
|
860 |
simple <TT |
|
861 |
CLASS="COMMAND" |
|
862 |
>SELECT</TT |
|
863 |
> from one view created a final
|
|
864 |
query tree that is a join of 4 tables (<TT |
|
865 |
CLASS="LITERAL" |
|
866 |
>unit</TT |
|
867 |
> was used twice with
|
|
868 |
different names).</P |
|
869 |
><P |
|
870 |
> The benefit of implementing views with the rule system is,
|
|
871 |
that the planner has all |
|
872 |
the information about which tables have to be scanned plus the |
|
873 |
relationships between these tables plus the restrictive |
|
874 |
qualifications from the views plus the qualifications from |
|
875 |
the original query |
|
876 |
in one single query tree. And this is still the situation |
|
877 |
when the original query is already a join over views. |
|
878 |
The planner has to decide which is |
|
879 |
the best path to execute the query, and the more information |
|
880 |
the planner has, the better this decision can be. And |
|
881 |
the rule system as implemented in <SPAN |
|
882 |
CLASS="PRODUCTNAME" |
|
883 |
>PostgreSQL</SPAN |
|
884 |
>
|
|
885 |
ensures, that this is all information available about the query |
|
886 |
up to that point.</P |
|
887 |
></DIV |
|
888 |
><DIV |
|
889 |
CLASS="SECT2" |
|
890 |
><H2 |
|
891 |
CLASS="SECT2" |
|
892 |
><A |
|
893 |
NAME="RULES-VIEWS-UPDATE" |
|
894 |
>37.2.4. Updating a View</A |
|
895 |
></H2 |
|
896 |
><P |
|
897 |
> What happens if a view is named as the target relation for an
|
|
898 |
<TT |
|
899 |
CLASS="COMMAND" |
|
900 |
>INSERT</TT |
|
901 |
>, <TT |
|
902 |
CLASS="COMMAND" |
|
903 |
>UPDATE</TT |
|
904 |
>, or
|
|
905 |
<TT |
|
906 |
CLASS="COMMAND" |
|
907 |
>DELETE</TT |
|
908 |
>? Simply doing the substitutions
|
|
909 |
described above would give a query tree in which the result |
|
910 |
relation points at a subquery range-table entry, which will not |
|
911 |
work. Instead, the rewriter assumes that the operation will be |
|
912 |
handled by an <TT |
|
913 |
CLASS="LITERAL" |
|
914 |
>INSTEAD OF</TT |
|
915 |
> trigger on the view.
|
|
916 |
(If there is no such trigger, the executor will throw an error |
|
917 |
when execution starts.) Rewriting works slightly differently |
|
918 |
in this case. For <TT |
|
919 |
CLASS="COMMAND" |
|
920 |
>INSERT</TT |
|
921 |
>, the rewriter does
|
|
922 |
nothing at all with the view, leaving it as the result relation |
|
923 |
for the query. For <TT |
|
924 |
CLASS="COMMAND" |
|
925 |
>UPDATE</TT |
|
926 |
> and
|
|
927 |
<TT |
|
928 |
CLASS="COMMAND" |
|
929 |
>DELETE</TT |
|
930 |
>, it's still necessary to expand the
|
|
931 |
view query to produce the <SPAN |
|
932 |
CLASS="QUOTE" |
|
933 |
>"old"</SPAN |
|
934 |
> rows that the command will
|
|
935 |
attempt to update or delete. So the view is expanded as normal, |
|
936 |
but another unexpanded range-table entry is added to the query |
|
937 |
to represent the view in its capacity as the result relation.</P |
|
938 |
><P |
|
939 |
> The problem that now arises is how to identify the rows to be
|
|
940 |
updated in the view. Recall that when the result relation |
|
941 |
is a table, a special <ACRONYM |
|
942 |
CLASS="ACRONYM" |
|
943 |
>CTID</ACRONYM |
|
944 |
> entry is added to the target
|
|
945 |
list to identify the physical locations of the rows to be updated. |
|
946 |
This does not work if the result relation is a view, because a view |
|
947 |
does not have any <ACRONYM |
|
948 |
CLASS="ACRONYM" |
|
949 |
>CTID</ACRONYM |
|
950 |
>, since its rows do not have
|
|
951 |
actual physical locations. Instead, for an <TT |
|
952 |
CLASS="COMMAND" |
|
953 |
>UPDATE</TT |
|
954 |
>
|
|
955 |
or <TT |
|
956 |
CLASS="COMMAND" |
|
957 |
>DELETE</TT |
|
958 |
> operation, a special <TT |
|
959 |
CLASS="LITERAL" |
|
960 |
>wholerow</TT |
|
961 |
>
|
|
962 |
entry is added to the target list, which expands to include all |
|
963 |
columns from the view. The executor uses this value to supply the |
|
964 |
<SPAN |
|
965 |
CLASS="QUOTE" |
|
966 |
>"old"</SPAN |
|
967 |
> row to the <TT |
|
968 |
CLASS="LITERAL" |
|
969 |
>INSTEAD OF</TT |
|
970 |
> trigger. It is
|
|
971 |
up to the trigger to work out what to update based on the old and |
|
972 |
new row values.</P |
|
973 |
><P |
|
974 |
> If there are no <TT |
|
975 |
CLASS="LITERAL" |
|
976 |
>INSTEAD OF</TT |
|
977 |
> triggers to update the view,
|
|
978 |
the executor will throw an error, because it cannot automatically |
|
979 |
update a view by itself. To change this, we can define rules that |
|
980 |
modify the behavior of <TT |
|
981 |
CLASS="COMMAND" |
|
982 |
>INSERT</TT |
|
983 |
>,
|
|
984 |
<TT |
|
985 |
CLASS="COMMAND" |
|
986 |
>UPDATE</TT |
|
987 |
>, and <TT |
|
988 |
CLASS="COMMAND" |
|
989 |
>DELETE</TT |
|
990 |
> commands on
|
|
991 |
a view. These rules will rewrite the command, typically into a command |
|
992 |
that updates one or more tables, rather than views. That is the topic |
|
993 |
of the next section.</P |
|
994 |
><P |
|
995 |
> Note that rules are evaluated first, rewriting the original query
|
|
996 |
before it is planned and executed. Therefore, if a view has |
|
997 |
<TT |
|
998 |
CLASS="LITERAL" |
|
999 |
>INSTEAD OF</TT |
|
1000 |
> triggers as well as rules on <TT |
|
1001 |
CLASS="COMMAND" |
|
1002 |
>INSERT</TT |
|
1003 |
>,
|
|
1004 |
<TT |
|
1005 |
CLASS="COMMAND" |
|
1006 |
>UPDATE</TT |
|
1007 |
>, or <TT |
|
1008 |
CLASS="COMMAND" |
|
1009 |
>DELETE</TT |
|
1010 |
>, then the rules will be
|
|
1011 |
evaluated first, and depending on the result, the triggers may not be |
|
1012 |
used at all.</P |
|
1013 |
></DIV |
|
1014 |
></DIV |
|
1015 |
><DIV |
|
1016 |
CLASS="NAVFOOTER" |
|
1017 |
><HR |
|
1018 |
ALIGN="LEFT" |
|
1019 |
WIDTH="100%"><TABLE |
|
1020 |
SUMMARY="Footer navigation table" |
|
1021 |
WIDTH="100%" |
|
1022 |
BORDER="0" |
|
1023 |
CELLPADDING="0" |
|
1024 |
CELLSPACING="0" |
|
1025 |
><TR |
|
1026 |
><TD |
|
1027 |
WIDTH="33%" |
|
1028 |
ALIGN="left" |
|
1029 |
VALIGN="top" |
|
1030 |
><A |
|
1031 |
HREF="querytree.html" |
|
1032 |
ACCESSKEY="P" |
|
1033 |
>Prev</A |
|
1034 |
></TD |
|
1035 |
><TD |
|
1036 |
WIDTH="34%" |
|
1037 |
ALIGN="center" |
|
1038 |
VALIGN="top" |
|
1039 |
><A |
|
1040 |
HREF="index.html" |
|
1041 |
ACCESSKEY="H" |
|
1042 |
>Home</A |
|
1043 |
></TD |
|
1044 |
><TD |
|
1045 |
WIDTH="33%" |
|
1046 |
ALIGN="right" |
|
1047 |
VALIGN="top" |
|
1048 |
><A |
|
1049 |
HREF="rules-update.html" |
|
1050 |
ACCESSKEY="N" |
|
1051 |
>Next</A |
|
1052 |
></TD |
|
1053 |
></TR |
|
1054 |
><TR |
|
1055 |
><TD |
|
1056 |
WIDTH="33%" |
|
1057 |
ALIGN="left" |
|
1058 |
VALIGN="top" |
|
1059 |
>The Query Tree</TD |
|
1060 |
><TD |
|
1061 |
WIDTH="34%" |
|
1062 |
ALIGN="center" |
|
1063 |
VALIGN="top" |
|
1064 |
><A |
|
1065 |
HREF="rules.html" |
|
1066 |
ACCESSKEY="U" |
|
1067 |
>Up</A |
|
1068 |
></TD |
|
1069 |
><TD |
|
1070 |
WIDTH="33%" |
|
1071 |
ALIGN="right" |
|
1072 |
VALIGN="top" |
|
1073 |
>Rules on <TT |
|
1074 |
CLASS="COMMAND" |
|
1075 |
>INSERT</TT |
|
1076 |
>, <TT |
|
1077 |
CLASS="COMMAND" |
|
1078 |
>UPDATE</TT |
|
1079 |
>, and <TT |
|
1080 |
CLASS="COMMAND" |
|
1081 |
>DELETE</TT |
|
1082 |
></TD |
|
1083 |
></TR |
|
1084 |
></TABLE |
|
1085 |
></DIV |
|
1086 |
></BODY |
|
1087 |
></HTML |
|
1088 |
>
|