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
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>40.4. Rules on INSERT, UPDATE, and DELETE</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="prev" href="rules-materializedviews.html" title="40.3. Materialized Views" /><link rel="next" href="rules-privileges.html" title="40.5. Rules and Privileges" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">40.4. Rules on <code xmlns="http://www.w3.org/1999/xhtml" class="command">INSERT</code>, <code xmlns="http://www.w3.org/1999/xhtml" class="command">UPDATE</code>, and <code xmlns="http://www.w3.org/1999/xhtml" class="command">DELETE</code></th></tr><tr><td width="10%" align="left"><a accesskey="p" href="rules-materializedviews.html" title="40.3. Materialized Views">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="rules.html" title="Chapter 40. The Rule System">Up</a></td><th width="60%" align="center">Chapter 40. The Rule System</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="rules-privileges.html" title="40.5. Rules and Privileges">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="RULES-UPDATE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">40.4. Rules on <code class="command">INSERT</code>, <code class="command">UPDATE</code>, and <code class="command">DELETE</code></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="rules-update.html#id-1.8.6.9.7">40.4.1. How Update Rules Work</a></span></dt><dt><span class="sect2"><a href="rules-update.html#RULES-UPDATE-VIEWS">40.4.2. Cooperation with Views</a></span></dt></dl></div><a id="id-1.8.6.9.2" class="indexterm"></a><a id="id-1.8.6.9.3" class="indexterm"></a><a id="id-1.8.6.9.4" class="indexterm"></a><p>
Rules that are defined on <code class="command">INSERT</code>, <code class="command">UPDATE</code>,
and <code class="command">DELETE</code> are significantly different from the view rules
described in the previous section. First, their <code class="command">CREATE
RULE</code> command allows more:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
They are allowed to have no action.
</p></li><li class="listitem"><p>
They can have multiple actions.
</p></li><li class="listitem"><p>
They can be <code class="literal">INSTEAD</code> or <code class="literal">ALSO</code> (the default).
</p></li><li class="listitem"><p>
The pseudorelations <code class="literal">NEW</code> and <code class="literal">OLD</code> become useful.
</p></li><li class="listitem"><p>
They can have rule qualifications.
</p></li></ul></div><p>
Second, they don't modify the query tree in place. Instead they
create zero or more new query trees and can throw away the
original one.
</p><div class="caution"><h3 class="title">Caution</h3><p>
In many cases, tasks that could be performed by rules
on <code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code> are better done
with triggers. Triggers are notationally a bit more complicated, but their
semantics are much simpler to understand. Rules tend to have surprising
results when the original query contains volatile functions: volatile
functions may get executed more times than expected in the process of
carrying out the rules.
</p><p>
Also, there are some cases that are not supported by these types of rules at
all, notably including <code class="literal">WITH</code> clauses in the original query and
multiple-assignment sub-<code class="literal">SELECT</code>s in the <code class="literal">SET</code> list
of <code class="command">UPDATE</code> queries. This is because copying these constructs
into a rule query would result in multiple evaluations of the sub-query,
contrary to the express intent of the query's author.
</p></div><div class="sect2" id="id-1.8.6.9.7"><div class="titlepage"><div><div><h3 class="title">40.4.1. How Update Rules Work</h3></div></div></div><p>
Keep the syntax:
</p><pre class="programlisting">
CREATE [ OR REPLACE ] RULE <em class="replaceable"><code>name</code></em> AS ON <em class="replaceable"><code>event</code></em>
TO <em class="replaceable"><code>table</code></em> [ WHERE <em class="replaceable"><code>condition</code></em> ]
DO [ ALSO | INSTEAD ] { NOTHING | <em class="replaceable"><code>command</code></em> | ( <em class="replaceable"><code>command</code></em> ; <em class="replaceable"><code>command</code></em> ... ) }
</pre><p>
in mind.
In the following, <em class="firstterm">update rules</em> means rules that are defined
on <code class="command">INSERT</code>, <code class="command">UPDATE</code>, or <code class="command">DELETE</code>.
</p><p>
Update rules get applied by the rule system when the result
relation and the command type of a query tree are equal to the
object and event given in the <code class="command">CREATE RULE</code> command.
For update rules, the rule system creates a list of query trees.
Initially the query-tree list is empty.
There can be zero (<code class="literal">NOTHING</code> key word), one, or multiple actions.
To simplify, we will look at a rule with one action. This rule
can have a qualification or not and it can be <code class="literal">INSTEAD</code> or
<code class="literal">ALSO</code> (the default).
</p><p>
What is a rule qualification? It is a restriction that tells
when the actions of the rule should be done and when not. This
qualification can only reference the pseudorelations <code class="literal">NEW</code> and/or <code class="literal">OLD</code>,
which basically represent the relation that was given as object (but with a
special meaning).
</p><p>
So we have three cases that produce the following query trees for
a one-action rule.
</p><div class="variablelist"><dl class="variablelist"><dt><span class="term">No qualification, with either <code class="literal">ALSO</code> or
<code class="literal">INSTEAD</code></span></dt><dd><p>
the query tree from the rule action with the original query
tree's qualification added
</p></dd><dt><span class="term">Qualification given and <code class="literal">ALSO</code></span></dt><dd><p>
the query tree from the rule action with the rule
qualification and the original query tree's qualification
added
</p></dd><dt><span class="term">Qualification given and <code class="literal">INSTEAD</code></span></dt><dd><p>
the query tree from the rule action with the rule
qualification and the original query tree's qualification; and
the original query tree with the negated rule qualification
added
</p></dd></dl></div><p>
Finally, if the rule is <code class="literal">ALSO</code>, the unchanged original query tree is
added to the list. Since only qualified <code class="literal">INSTEAD</code> rules already add the
original query tree, we end up with either one or two output query trees
for a rule with one action.
</p><p>
For <code class="literal">ON INSERT</code> rules, the original query (if not suppressed by <code class="literal">INSTEAD</code>)
is done before any actions added by rules. This allows the actions to
see the inserted row(s). But for <code class="literal">ON UPDATE</code> and <code class="literal">ON
DELETE</code> rules, the original query is done after the actions added by rules.
This ensures that the actions can see the to-be-updated or to-be-deleted
rows; otherwise, the actions might do nothing because they find no rows
matching their qualifications.
</p><p>
The query trees generated from rule actions are thrown into the
rewrite system again, and maybe more rules get applied resulting
in additional or fewer query trees.
So a rule's actions must have either a different
command type or a different result relation than the rule itself is
on, otherwise this recursive process will end up in an infinite loop.
(Recursive expansion of a rule will be detected and reported as an
error.)
</p><p>
The query trees found in the actions of the
<code class="structname">pg_rewrite</code> system catalog are only
templates. Since they can reference the range-table entries for
<code class="literal">NEW</code> and <code class="literal">OLD</code>, some substitutions have to be made before they can be
used. For any reference to <code class="literal">NEW</code>, the target list of the original
query is searched for a corresponding entry. If found, that
entry's expression replaces the reference. Otherwise, <code class="literal">NEW</code> means the
same as <code class="literal">OLD</code> (for an <code class="command">UPDATE</code>) or is replaced by
a null value (for an <code class="command">INSERT</code>). Any reference to <code class="literal">OLD</code> is
replaced by a reference to the range-table entry that is the
result relation.
</p><p>
After the system is done applying update rules, it applies view rules to the
produced query tree(s). Views cannot insert new update actions so
there is no need to apply update rules to the output of view rewriting.
</p><div class="sect3" id="id-1.8.6.9.7.10"><div class="titlepage"><div><div><h4 class="title">40.4.1.1. A First Rule Step by Step</h4></div></div></div><p>
Say we want to trace changes to the <code class="literal">sl_avail</code> column in the
<code class="literal">shoelace_data</code> relation. So we set up a log table
and a rule that conditionally writes a log entry when an
<code class="command">UPDATE</code> is performed on
<code class="literal">shoelace_data</code>.
</p><pre class="programlisting">
CREATE TABLE shoelace_log (
sl_name text, -- shoelace changed
sl_avail integer, -- new available value
log_who text, -- who did it
log_when timestamp -- when
);
CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
WHERE NEW.sl_avail <> OLD.sl_avail
DO INSERT INTO shoelace_log VALUES (
NEW.sl_name,
NEW.sl_avail,
current_user,
current_timestamp
);
</pre><p>
</p><p>
Now someone does:
</p><pre class="programlisting">
UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';
</pre><p>
and we look at the log table:
</p><pre class="programlisting">
SELECT * FROM shoelace_log;
sl_name | sl_avail | log_who | log_when
---------+----------+---------+----------------------------------
sl7 | 6 | Al | Tue Oct 20 16:14:45 1998 MET DST
(1 row)
</pre><p>
</p><p>
That's what we expected. What happened in the background is the following.
The parser created the query tree:
</p><pre class="programlisting">
UPDATE shoelace_data SET sl_avail = 6
FROM shoelace_data shoelace_data
WHERE shoelace_data.sl_name = 'sl7';
</pre><p>
There is a rule <code class="literal">log_shoelace</code> that is <code class="literal">ON UPDATE</code> with the rule
qualification expression:
</p><pre class="programlisting">
NEW.sl_avail <> OLD.sl_avail
</pre><p>
and the action:
</p><pre class="programlisting">
INSERT INTO shoelace_log VALUES (
new.sl_name, new.sl_avail,
current_user, current_timestamp )
FROM shoelace_data new, shoelace_data old;
</pre><p>
(This looks a little strange since you cannot normally write
<code class="literal">INSERT ... VALUES ... FROM</code>. The <code class="literal">FROM</code>
clause here is just to indicate that there are range-table entries
in the query tree for <code class="literal">new</code> and <code class="literal">old</code>.
These are needed so that they can be referenced by variables in
the <code class="command">INSERT</code> command's query tree.)
</p><p>
The rule is a qualified <code class="literal">ALSO</code> rule, so the rule system
has to return two query trees: the modified rule action and the original
query tree. In step 1, the range table of the original query is
incorporated into the rule's action query tree. This results in:
</p><pre class="programlisting">
INSERT INTO shoelace_log VALUES (
new.sl_name, new.sl_avail,
current_user, current_timestamp )
FROM shoelace_data new, shoelace_data old,
<span class="emphasis"><strong>shoelace_data shoelace_data</strong></span>;
</pre><p>
In step 2, the rule qualification is added to it, so the result set
is restricted to rows where <code class="literal">sl_avail</code> changes:
</p><pre class="programlisting">
INSERT INTO shoelace_log VALUES (
new.sl_name, new.sl_avail,
current_user, current_timestamp )
FROM shoelace_data new, shoelace_data old,
shoelace_data shoelace_data
<span class="emphasis"><strong>WHERE new.sl_avail <> old.sl_avail</strong></span>;
</pre><p>
(This looks even stranger, since <code class="literal">INSERT ... VALUES</code> doesn't have
a <code class="literal">WHERE</code> clause either, but the planner and executor will have no
difficulty with it. They need to support this same functionality
anyway for <code class="literal">INSERT ... SELECT</code>.)
</p><p>
In step 3, the original query tree's qualification is added,
restricting the result set further to only the rows that would have been touched
by the original query:
</p><pre class="programlisting">
INSERT INTO shoelace_log VALUES (
new.sl_name, new.sl_avail,
current_user, current_timestamp )
FROM shoelace_data new, shoelace_data old,
shoelace_data shoelace_data
WHERE new.sl_avail <> old.sl_avail
<span class="emphasis"><strong>AND shoelace_data.sl_name = 'sl7'</strong></span>;
</pre><p>
</p><p>
Step 4 replaces references to <code class="literal">NEW</code> by the target list entries from the
original query tree or by the matching variable references
from the result relation:
</p><pre class="programlisting">
INSERT INTO shoelace_log VALUES (
<span class="emphasis"><strong>shoelace_data.sl_name</strong></span>, <span class="emphasis"><strong>6</strong></span>,
current_user, current_timestamp )
FROM shoelace_data new, shoelace_data old,
shoelace_data shoelace_data
WHERE <span class="emphasis"><strong>6</strong></span> <> old.sl_avail
AND shoelace_data.sl_name = 'sl7';
</pre><p>
</p><p>
Step 5 changes <code class="literal">OLD</code> references into result relation references:
</p><pre class="programlisting">
INSERT INTO shoelace_log VALUES (
shoelace_data.sl_name, 6,
current_user, current_timestamp )
FROM shoelace_data new, shoelace_data old,
shoelace_data shoelace_data
WHERE 6 <> <span class="emphasis"><strong>shoelace_data.sl_avail</strong></span>
AND shoelace_data.sl_name = 'sl7';
</pre><p>
</p><p>
That's it. Since the rule is <code class="literal">ALSO</code>, we also output the
original query tree. In short, the output from the rule system
is a list of two query trees that correspond to these statements:
</p><pre class="programlisting">
INSERT INTO shoelace_log VALUES (
shoelace_data.sl_name, 6,
current_user, current_timestamp )
FROM shoelace_data
WHERE 6 <> shoelace_data.sl_avail
AND shoelace_data.sl_name = 'sl7';
UPDATE shoelace_data SET sl_avail = 6
WHERE sl_name = 'sl7';
</pre><p>
These are executed in this order, and that is exactly what
the rule was meant to do.
</p><p>
The substitutions and the added qualifications
ensure that, if the original query would be, say:
</p><pre class="programlisting">
UPDATE shoelace_data SET sl_color = 'green'
WHERE sl_name = 'sl7';
</pre><p>
no log entry would get written. In that case, the original query
tree does not contain a target list entry for
<code class="literal">sl_avail</code>, so <code class="literal">NEW.sl_avail</code> will get
replaced by <code class="literal">shoelace_data.sl_avail</code>. Thus, the extra
command generated by the rule is:
</p><pre class="programlisting">
INSERT INTO shoelace_log VALUES (
shoelace_data.sl_name, <span class="emphasis"><strong>shoelace_data.sl_avail</strong></span>,
current_user, current_timestamp )
FROM shoelace_data
WHERE <span class="emphasis"><strong>shoelace_data.sl_avail</strong></span> <> shoelace_data.sl_avail
AND shoelace_data.sl_name = 'sl7';
</pre><p>
and that qualification will never be true.
</p><p>
It will also work if the original query modifies multiple rows. So
if someone issued the command:
</p><pre class="programlisting">
UPDATE shoelace_data SET sl_avail = 0
WHERE sl_color = 'black';
</pre><p>
four rows in fact get updated (<code class="literal">sl1</code>, <code class="literal">sl2</code>, <code class="literal">sl3</code>, and <code class="literal">sl4</code>).
But <code class="literal">sl3</code> already has <code class="literal">sl_avail = 0</code>. In this case, the original
query trees qualification is different and that results
in the extra query tree:
</p><pre class="programlisting">
INSERT INTO shoelace_log
SELECT shoelace_data.sl_name, 0,
current_user, current_timestamp
FROM shoelace_data
WHERE 0 <> shoelace_data.sl_avail
AND <span class="emphasis"><strong>shoelace_data.sl_color = 'black'</strong></span>;
</pre><p>
being generated by the rule. This query tree will surely insert
three new log entries. And that's absolutely correct.
</p><p>
Here we can see why it is important that the original query tree
is executed last. If the <code class="command">UPDATE</code> had been
executed first, all the rows would have already been set to zero, so the
logging <code class="command">INSERT</code> would not find any row where
<code class="literal">0 <> shoelace_data.sl_avail</code>.
</p></div></div><div class="sect2" id="RULES-UPDATE-VIEWS"><div class="titlepage"><div><div><h3 class="title">40.4.2. Cooperation with Views</h3></div></div></div><a id="id-1.8.6.9.8.2" class="indexterm"></a><p>
A simple way to protect view relations from the mentioned
possibility that someone can try to run <code class="command">INSERT</code>,
<code class="command">UPDATE</code>, or <code class="command">DELETE</code> on them is
to let those query trees get thrown away. So we could create the rules:
</p><pre class="programlisting">
CREATE RULE shoe_ins_protect AS ON INSERT TO shoe
DO INSTEAD NOTHING;
CREATE RULE shoe_upd_protect AS ON UPDATE TO shoe
DO INSTEAD NOTHING;
CREATE RULE shoe_del_protect AS ON DELETE TO shoe
DO INSTEAD NOTHING;
</pre><p>
If someone now tries to do any of these operations on the view
relation <code class="literal">shoe</code>, the rule system will
apply these rules. Since the rules have
no actions and are <code class="literal">INSTEAD</code>, the resulting list of
query trees will be empty and the whole query will become
nothing because there is nothing left to be optimized or
executed after the rule system is done with it.
</p><p>
A more sophisticated way to use the rule system is to
create rules that rewrite the query tree into one that
does the right operation on the real tables. To do that
on the <code class="literal">shoelace</code> view, we create
the following rules:
</p><pre class="programlisting">
CREATE RULE shoelace_ins AS ON INSERT TO shoelace
DO INSTEAD
INSERT INTO shoelace_data VALUES (
NEW.sl_name,
NEW.sl_avail,
NEW.sl_color,
NEW.sl_len,
NEW.sl_unit
);
CREATE RULE shoelace_upd AS ON UPDATE TO shoelace
DO INSTEAD
UPDATE shoelace_data
SET sl_name = NEW.sl_name,
sl_avail = NEW.sl_avail,
sl_color = NEW.sl_color,
sl_len = NEW.sl_len,
sl_unit = NEW.sl_unit
WHERE sl_name = OLD.sl_name;
CREATE RULE shoelace_del AS ON DELETE TO shoelace
DO INSTEAD
DELETE FROM shoelace_data
WHERE sl_name = OLD.sl_name;
</pre><p>
</p><p>
If you want to support <code class="literal">RETURNING</code> queries on the view,
you need to make the rules include <code class="literal">RETURNING</code> clauses that
compute the view rows. This is usually pretty trivial for views on a
single table, but it's a bit tedious for join views such as
<code class="literal">shoelace</code>. An example for the insert case is:
</p><pre class="programlisting">
CREATE RULE shoelace_ins AS ON INSERT TO shoelace
DO INSTEAD
INSERT INTO shoelace_data VALUES (
NEW.sl_name,
NEW.sl_avail,
NEW.sl_color,
NEW.sl_len,
NEW.sl_unit
)
RETURNING
shoelace_data.*,
(SELECT shoelace_data.sl_len * u.un_fact
FROM unit u WHERE shoelace_data.sl_unit = u.un_name);
</pre><p>
Note that this one rule supports both <code class="command">INSERT</code> and
<code class="command">INSERT RETURNING</code> queries on the view — the
<code class="literal">RETURNING</code> clause is simply ignored for <code class="command">INSERT</code>.
</p><p>
Now assume that once in a while, a pack of shoelaces arrives at
the shop and a big parts list along with it. But you don't want
to manually update the <code class="literal">shoelace</code> view every
time. Instead we set up two little tables: one where you can
insert the items from the part list, and one with a special
trick. The creation commands for these are:
</p><pre class="programlisting">
CREATE TABLE shoelace_arrive (
arr_name text,
arr_quant integer
);
CREATE TABLE shoelace_ok (
ok_name text,
ok_quant integer
);
CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok
DO INSTEAD
UPDATE shoelace
SET sl_avail = sl_avail + NEW.ok_quant
WHERE sl_name = NEW.ok_name;
</pre><p>
Now you can fill the table <code class="literal">shoelace_arrive</code> with
the data from the parts list:
</p><pre class="programlisting">
SELECT * FROM shoelace_arrive;
arr_name | arr_quant
----------+-----------
sl3 | 10
sl6 | 20
sl8 | 20
(3 rows)
</pre><p>
Take a quick look at the current data:
</p><pre class="programlisting">
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 | 6 | 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)
</pre><p>
Now move the arrived shoelaces in:
</p><pre class="programlisting">
INSERT INTO shoelace_ok SELECT * FROM shoelace_arrive;
</pre><p>
and check the results:
</p><pre class="programlisting">
SELECT * FROM shoelace ORDER BY sl_name;
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 | 6 | brown | 60 | cm | 60
sl4 | 8 | black | 40 | inch | 101.6
sl3 | 10 | black | 35 | inch | 88.9
sl8 | 21 | brown | 40 | inch | 101.6
sl5 | 4 | brown | 1 | m | 100
sl6 | 20 | brown | 0.9 | m | 90
(8 rows)
SELECT * FROM shoelace_log;
sl_name | sl_avail | log_who| log_when
---------+----------+--------+----------------------------------
sl7 | 6 | Al | Tue Oct 20 19:14:45 1998 MET DST
sl3 | 10 | Al | Tue Oct 20 19:25:16 1998 MET DST
sl6 | 20 | Al | Tue Oct 20 19:25:16 1998 MET DST
sl8 | 21 | Al | Tue Oct 20 19:25:16 1998 MET DST
(4 rows)
</pre><p>
</p><p>
It's a long way from the one <code class="literal">INSERT ... SELECT</code>
to these results. And the description of the query-tree
transformation will be the last in this chapter. First, there is
the parser's output:
</p><pre class="programlisting">
INSERT INTO shoelace_ok
SELECT shoelace_arrive.arr_name, shoelace_arrive.arr_quant
FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok;
</pre><p>
Now the first rule <code class="literal">shoelace_ok_ins</code> is applied and turns this
into:
</p><pre class="programlisting">
UPDATE shoelace
SET sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant
FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
shoelace_ok old, shoelace_ok new,
shoelace shoelace
WHERE shoelace.sl_name = shoelace_arrive.arr_name;
</pre><p>
and throws away the original <code class="command">INSERT</code> on
<code class="literal">shoelace_ok</code>. This rewritten query is passed to
the rule system again, and the second applied rule
<code class="literal">shoelace_upd</code> produces:
</p><pre class="programlisting">
UPDATE shoelace_data
SET sl_name = shoelace.sl_name,
sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant,
sl_color = shoelace.sl_color,
sl_len = shoelace.sl_len,
sl_unit = shoelace.sl_unit
FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
shoelace_ok old, shoelace_ok new,
shoelace shoelace, shoelace old,
shoelace new, shoelace_data shoelace_data
WHERE shoelace.sl_name = shoelace_arrive.arr_name
AND shoelace_data.sl_name = shoelace.sl_name;
</pre><p>
Again it's an <code class="literal">INSTEAD</code> rule and the previous query tree is trashed.
Note that this query still uses the view <code class="literal">shoelace</code>.
But the rule system isn't finished with this step, so it continues
and applies the <code class="literal">_RETURN</code> rule on it, and we get:
</p><pre class="programlisting">
UPDATE shoelace_data
SET sl_name = s.sl_name,
sl_avail = s.sl_avail + shoelace_arrive.arr_quant,
sl_color = s.sl_color,
sl_len = s.sl_len,
sl_unit = s.sl_unit
FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
shoelace_ok old, shoelace_ok new,
shoelace shoelace, shoelace old,
shoelace new, shoelace_data shoelace_data,
shoelace old, shoelace new,
shoelace_data s, unit u
WHERE s.sl_name = shoelace_arrive.arr_name
AND shoelace_data.sl_name = s.sl_name;
</pre><p>
Finally, the rule <code class="literal">log_shoelace</code> gets applied,
producing the extra query tree:
</p><pre class="programlisting">
INSERT INTO shoelace_log
SELECT s.sl_name,
s.sl_avail + shoelace_arrive.arr_quant,
current_user,
current_timestamp
FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,
shoelace_ok old, shoelace_ok new,
shoelace shoelace, shoelace old,
shoelace new, shoelace_data shoelace_data,
shoelace old, shoelace new,
shoelace_data s, unit u,
shoelace_data old, shoelace_data new
shoelace_log shoelace_log
WHERE s.sl_name = shoelace_arrive.arr_name
AND shoelace_data.sl_name = s.sl_name
AND (s.sl_avail + shoelace_arrive.arr_quant) <> s.sl_avail;
</pre><p>
After that the rule system runs out of rules and returns the
generated query trees.
</p><p>
So we end up with two final query trees that are equivalent to the
<acronym class="acronym">SQL</acronym> statements:
</p><pre class="programlisting">
INSERT INTO shoelace_log
SELECT s.sl_name,
s.sl_avail + shoelace_arrive.arr_quant,
current_user,
current_timestamp
FROM shoelace_arrive shoelace_arrive, shoelace_data shoelace_data,
shoelace_data s
WHERE s.sl_name = shoelace_arrive.arr_name
AND shoelace_data.sl_name = s.sl_name
AND s.sl_avail + shoelace_arrive.arr_quant <> s.sl_avail;
UPDATE shoelace_data
SET sl_avail = shoelace_data.sl_avail + shoelace_arrive.arr_quant
FROM shoelace_arrive shoelace_arrive,
shoelace_data shoelace_data,
shoelace_data s
WHERE s.sl_name = shoelace_arrive.sl_name
AND shoelace_data.sl_name = s.sl_name;
</pre><p>
The result is that data coming from one relation inserted into another,
changed into updates on a third, changed into updating
a fourth plus logging that final update in a fifth
gets reduced into two queries.
</p><p>
There is a little detail that's a bit ugly. Looking at the two
queries, it turns out that the <code class="literal">shoelace_data</code>
relation appears twice in the range table where it could
definitely be reduced to one. The planner does not handle it and
so the execution plan for the rule systems output of the
<code class="command">INSERT</code> will be
</p><pre class="literallayout">
Nested Loop
-> Merge Join
-> Seq Scan
-> Sort
-> Seq Scan on s
-> Seq Scan
-> Sort
-> Seq Scan on shoelace_arrive
-> Seq Scan on shoelace_data
</pre><p>
while omitting the extra range table entry would result in a
</p><pre class="literallayout">
Merge Join
-> Seq Scan
-> Sort
-> Seq Scan on s
-> Seq Scan
-> Sort
-> Seq Scan on shoelace_arrive
</pre><p>
which produces exactly the same entries in the log table. Thus,
the rule system caused one extra scan on the table
<code class="literal">shoelace_data</code> that is absolutely not
necessary. And the same redundant scan is done once more in the
<code class="command">UPDATE</code>. But it was a really hard job to make
that all possible at all.
</p><p>
Now we make a final demonstration of the
<span class="productname">PostgreSQL</span> rule system and its power.
Say you add some shoelaces with extraordinary colors to your
database:
</p><pre class="programlisting">
INSERT INTO shoelace VALUES ('sl9', 0, 'pink', 35.0, 'inch', 0.0);
INSERT INTO shoelace VALUES ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0);
</pre><p>
We would like to make a view to check which
<code class="literal">shoelace</code> entries do not fit any shoe in color.
The view for this is:
</p><pre class="programlisting">
CREATE VIEW shoelace_mismatch AS
SELECT * FROM shoelace WHERE NOT EXISTS
(SELECT shoename FROM shoe WHERE slcolor = sl_color);
</pre><p>
Its output is:
</p><pre class="programlisting">
SELECT * FROM shoelace_mismatch;
sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
---------+----------+----------+--------+---------+-----------
sl9 | 0 | pink | 35 | inch | 88.9
sl10 | 1000 | magenta | 40 | inch | 101.6
</pre><p>
</p><p>
Now we want to set it up so that mismatching shoelaces that are
not in stock are deleted from the database.
To make it a little harder for <span class="productname">PostgreSQL</span>,
we don't delete it directly. Instead we create one more view:
</p><pre class="programlisting">
CREATE VIEW shoelace_can_delete AS
SELECT * FROM shoelace_mismatch WHERE sl_avail = 0;
</pre><p>
and do it this way:
</p><pre class="programlisting">
DELETE FROM shoelace WHERE EXISTS
(SELECT * FROM shoelace_can_delete
WHERE sl_name = shoelace.sl_name);
</pre><p>
<span class="foreignphrase"><em class="foreignphrase">Voilà</em></span>:
</p><pre class="programlisting">
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 | 6 | brown | 60 | cm | 60
sl4 | 8 | black | 40 | inch | 101.6
sl3 | 10 | black | 35 | inch | 88.9
sl8 | 21 | brown | 40 | inch | 101.6
sl10 | 1000 | magenta | 40 | inch | 101.6
sl5 | 4 | brown | 1 | m | 100
sl6 | 20 | brown | 0.9 | m | 90
(9 rows)
</pre><p>
</p><p>
A <code class="command">DELETE</code> on a view, with a subquery qualification that
in total uses 4 nesting/joined views, where one of them
itself has a subquery qualification containing a view
and where calculated view columns are used,
gets rewritten into
one single query tree that deletes the requested data
from a real table.
</p><p>
There are probably only a few situations out in the real world
where such a construct is necessary. But it makes you feel
comfortable that it works.
</p></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="rules-materializedviews.html" title="40.3. Materialized Views">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="rules.html" title="Chapter 40. The Rule System">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="rules-privileges.html" title="40.5. Rules and Privileges">Next</a></td></tr><tr><td width="40%" align="left" valign="top">40.3. Materialized Views </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 40.5. Rules and Privileges</td></tr></table></div></body></html>
|
