| blob | 037e83b2ad73d9f060df3466381b57e9d8fa7881 |
1 --
2 -- RULES
3 -- From Jan's original setup_ruletest.sql and run_ruletest.sql
4 -- - thomas 1998-09-13
5 --
6 --
7 -- Tables and rules for the view test
8 --
9 create table rtest_t1 (a int4, b int4);
10 create table rtest_t2 (a int4, b int4);
11 create table rtest_t3 (a int4, b int4);
12 create view rtest_v1 as select * from rtest_t1;
13 create rule rtest_v1_ins as on insert to rtest_v1 do instead
14 insert into rtest_t1 values (new.a, new.b);
15 create rule rtest_v1_upd as on update to rtest_v1 do instead
16 update rtest_t1 set a = new.a, b = new.b
17 where a = old.a;
18 create rule rtest_v1_del as on delete to rtest_v1 do instead
19 delete from rtest_t1 where a = old.a;
20 -- Test comments
21 COMMENT ON RULE rtest_v1_bad ON rtest_v1 IS 'bad rule';
22 ERROR: rule "rtest_v1_bad" for relation "rtest_v1" does not exist
23 COMMENT ON RULE rtest_v1_del ON rtest_v1 IS 'delete rule';
24 COMMENT ON RULE rtest_v1_del ON rtest_v1 IS NULL;
25 --
26 -- Tables and rules for the constraint update/delete test
27 --
28 -- Note:
29 -- Now that we have multiple action rule support, we check
30 -- both possible syntaxes to define them (The last action
31 -- can but must not have a semicolon at the end).
32 --
33 create table rtest_system (sysname text, sysdesc text);
34 create table rtest_interface (sysname text, ifname text);
35 create table rtest_person (pname text, pdesc text);
36 create table rtest_admin (pname text, sysname text);
37 create rule rtest_sys_upd as on update to rtest_system do also (
38 update rtest_interface set sysname = new.sysname
39 where sysname = old.sysname;
40 update rtest_admin set sysname = new.sysname
41 where sysname = old.sysname
42 );
43 create rule rtest_sys_del as on delete to rtest_system do also (
44 delete from rtest_interface where sysname = old.sysname;
45 delete from rtest_admin where sysname = old.sysname;
46 );
47 create rule rtest_pers_upd as on update to rtest_person do also
48 update rtest_admin set pname = new.pname where pname = old.pname;
49 create rule rtest_pers_del as on delete to rtest_person do also
50 delete from rtest_admin where pname = old.pname;
51 --
52 -- Tables and rules for the logging test
53 --
54 create table rtest_emp (ename char(20), salary numeric);
55 create table rtest_emplog (ename char(20), who name, action char(10), newsal numeric, oldsal numeric);
56 create table rtest_empmass (ename char(20), salary numeric);
57 create rule rtest_emp_ins as on insert to rtest_emp do
58 insert into rtest_emplog values (new.ename, current_user,
59 'hired', new.salary, '0.00');
60 create rule rtest_emp_upd as on update to rtest_emp where new.salary != old.salary do
61 insert into rtest_emplog values (new.ename, current_user,
62 'honored', new.salary, old.salary);
63 create rule rtest_emp_del as on delete to rtest_emp do
64 insert into rtest_emplog values (old.ename, current_user,
65 'fired', '0.00', old.salary);
66 --
67 -- Tables and rules for the multiple cascaded qualified instead
68 -- rule test
69 --
70 create table rtest_t4 (a int4, b text);
71 create table rtest_t5 (a int4, b text);
72 create table rtest_t6 (a int4, b text);
73 create table rtest_t7 (a int4, b text);
74 create table rtest_t8 (a int4, b text);
75 create table rtest_t9 (a int4, b text);
76 create rule rtest_t4_ins1 as on insert to rtest_t4
77 where new.a >= 10 and new.a < 20 do instead
78 insert into rtest_t5 values (new.a, new.b);
79 create rule rtest_t4_ins2 as on insert to rtest_t4
80 where new.a >= 20 and new.a < 30 do
81 insert into rtest_t6 values (new.a, new.b);
82 create rule rtest_t5_ins as on insert to rtest_t5
83 where new.a > 15 do
84 insert into rtest_t7 values (new.a, new.b);
85 create rule rtest_t6_ins as on insert to rtest_t6
86 where new.a > 25 do instead
87 insert into rtest_t8 values (new.a, new.b);
88 --
89 -- Tables and rules for the rule fire order test
90 --
91 -- As of PG 7.3, the rules should fire in order by name, regardless
92 -- of INSTEAD attributes or creation order.
93 --
94 create table rtest_order1 (a int4);
95 create table rtest_order2 (a int4, b int4, c text);
96 create sequence rtest_seq;
97 create rule rtest_order_r3 as on insert to rtest_order1 do instead
98 insert into rtest_order2 values (new.a, nextval('rtest_seq'),
99 'rule 3 - this should run 3rd');
100 create rule rtest_order_r4 as on insert to rtest_order1
101 where a < 100 do instead
102 insert into rtest_order2 values (new.a, nextval('rtest_seq'),
103 'rule 4 - this should run 4th');
104 create rule rtest_order_r2 as on insert to rtest_order1 do
105 insert into rtest_order2 values (new.a, nextval('rtest_seq'),
106 'rule 2 - this should run 2nd');
107 create rule rtest_order_r1 as on insert to rtest_order1 do instead
108 insert into rtest_order2 values (new.a, nextval('rtest_seq'),
109 'rule 1 - this should run 1st');
110 --
111 -- Tables and rules for the instead nothing test
112 --
113 create table rtest_nothn1 (a int4, b text);
114 create table rtest_nothn2 (a int4, b text);
115 create table rtest_nothn3 (a int4, b text);
116 create table rtest_nothn4 (a int4, b text);
117 create rule rtest_nothn_r1 as on insert to rtest_nothn1
118 where new.a >= 10 and new.a < 20 do instead nothing;
119 create rule rtest_nothn_r2 as on insert to rtest_nothn1
120 where new.a >= 30 and new.a < 40 do instead nothing;
121 create rule rtest_nothn_r3 as on insert to rtest_nothn2
122 where new.a >= 100 do instead
123 insert into rtest_nothn3 values (new.a, new.b);
124 create rule rtest_nothn_r4 as on insert to rtest_nothn2
125 do instead nothing;
126 --
127 -- Tests on a view that is select * of a table
128 -- and has insert/update/delete instead rules to
129 -- behave close like the real table.
130 --
131 --
132 -- We need test date later
133 --
134 insert into rtest_t2 values (1, 21);
135 insert into rtest_t2 values (2, 22);
136 insert into rtest_t2 values (3, 23);
137 insert into rtest_t3 values (1, 31);
138 insert into rtest_t3 values (2, 32);
139 insert into rtest_t3 values (3, 33);
140 insert into rtest_t3 values (4, 34);
141 insert into rtest_t3 values (5, 35);
142 -- insert values
143 insert into rtest_v1 values (1, 11);
144 insert into rtest_v1 values (2, 12);
145 select * from rtest_v1;
146 a | b
147 ---+----
148 1 | 11
149 2 | 12
150 (2 rows)
152 -- delete with constant expression
153 delete from rtest_v1 where a = 1;
154 select * from rtest_v1;
155 a | b
156 ---+----
157 2 | 12
158 (1 row)
160 insert into rtest_v1 values (1, 11);
161 delete from rtest_v1 where b = 12;
162 select * from rtest_v1;
163 a | b
164 ---+----
165 1 | 11
166 (1 row)
168 insert into rtest_v1 values (2, 12);
169 insert into rtest_v1 values (2, 13);
170 select * from rtest_v1;
171 a | b
172 ---+----
173 1 | 11
174 2 | 12
175 2 | 13
176 (3 rows)
178 ** Remember the delete rule on rtest_v1: It says
179 ** DO INSTEAD DELETE FROM rtest_t1 WHERE a = old.a
180 ** So this time both rows with a = 2 must get deleted
181 \p
182 ** Remember the delete rule on rtest_v1: It says
183 ** DO INSTEAD DELETE FROM rtest_t1 WHERE a = old.a
184 ** So this time both rows with a = 2 must get deleted
185 \r
186 delete from rtest_v1 where b = 12;
187 select * from rtest_v1;
188 a | b
189 ---+----
190 1 | 11
191 (1 row)
193 delete from rtest_v1;
194 -- insert select
195 insert into rtest_v1 select * from rtest_t2;
196 select * from rtest_v1;
197 a | b
198 ---+----
199 1 | 21
200 2 | 22
201 3 | 23
202 (3 rows)
204 delete from rtest_v1;
205 -- same with swapped targetlist
206 insert into rtest_v1 (b, a) select b, a from rtest_t2;
207 select * from rtest_v1;
208 a | b
209 ---+----
210 1 | 21
211 2 | 22
212 3 | 23
213 (3 rows)
215 -- now with only one target attribute
216 insert into rtest_v1 (a) select a from rtest_t3;
217 select * from rtest_v1;
218 a | b
219 ---+----
220 1 | 21
221 2 | 22
222 3 | 23
223 1 |
224 2 |
225 3 |
226 4 |
227 5 |
228 (8 rows)
230 select * from rtest_v1 where b isnull;
231 a | b
232 ---+---
233 1 |
234 2 |
235 3 |
236 4 |
237 5 |
238 (5 rows)
240 -- let attribute a differ (must be done on rtest_t1 - see above)
241 update rtest_t1 set a = a + 10 where b isnull;
242 delete from rtest_v1 where b isnull;
243 select * from rtest_v1;
244 a | b
245 ---+----
246 1 | 21
247 2 | 22
248 3 | 23
249 (3 rows)
251 -- now updates with constant expression
252 update rtest_v1 set b = 42 where a = 2;
253 select * from rtest_v1;
254 a | b
255 ---+----
256 1 | 21
257 3 | 23
258 2 | 42
259 (3 rows)
261 update rtest_v1 set b = 99 where b = 42;
262 select * from rtest_v1;
263 a | b
264 ---+----
265 1 | 21
266 3 | 23
267 2 | 99
268 (3 rows)
270 update rtest_v1 set b = 88 where b < 50;
271 select * from rtest_v1;
272 a | b
273 ---+----
274 2 | 99
275 1 | 88
276 3 | 88
277 (3 rows)
279 delete from rtest_v1;
280 insert into rtest_v1 select rtest_t2.a, rtest_t3.b
281 from rtest_t2, rtest_t3
282 where rtest_t2.a = rtest_t3.a;
283 select * from rtest_v1;
284 a | b
285 ---+----
286 1 | 31
287 2 | 32
288 3 | 33
289 (3 rows)
291 -- updates in a mergejoin
292 update rtest_v1 set b = rtest_t2.b from rtest_t2 where rtest_v1.a = rtest_t2.a;
293 select * from rtest_v1;
294 a | b
295 ---+----
296 1 | 21
297 2 | 22
298 3 | 23
299 (3 rows)
301 insert into rtest_v1 select * from rtest_t3;
302 select * from rtest_v1;
303 a | b
304 ---+----
305 1 | 21
306 2 | 22
307 3 | 23
308 1 | 31
309 2 | 32
310 3 | 33
311 4 | 34
312 5 | 35
313 (8 rows)
315 update rtest_t1 set a = a + 10 where b > 30;
316 select * from rtest_v1;
317 a | b
318 ----+----
319 1 | 21
320 2 | 22
321 3 | 23
322 11 | 31
323 12 | 32
324 13 | 33
325 14 | 34
326 15 | 35
327 (8 rows)
329 update rtest_v1 set a = rtest_t3.a + 20 from rtest_t3 where rtest_v1.b = rtest_t3.b;
330 select * from rtest_v1;
331 a | b
332 ----+----
333 1 | 21
334 2 | 22
335 3 | 23
336 21 | 31
337 22 | 32
338 23 | 33
339 24 | 34
340 25 | 35
341 (8 rows)
343 --
344 -- Test for constraint updates/deletes
345 --
346 insert into rtest_system values ('orion', 'Linux Jan Wieck');
347 insert into rtest_system values ('notjw', 'WinNT Jan Wieck (notebook)');
348 insert into rtest_system values ('neptun', 'Fileserver');
349 insert into rtest_interface values ('orion', 'eth0');
350 insert into rtest_interface values ('orion', 'eth1');
351 insert into rtest_interface values ('notjw', 'eth0');
352 insert into rtest_interface values ('neptun', 'eth0');
353 insert into rtest_person values ('jw', 'Jan Wieck');
354 insert into rtest_person values ('bm', 'Bruce Momjian');
355 insert into rtest_admin values ('jw', 'orion');
356 insert into rtest_admin values ('jw', 'notjw');
357 insert into rtest_admin values ('bm', 'neptun');
358 update rtest_system set sysname = 'pluto' where sysname = 'neptun';
359 select * from rtest_interface;
360 sysname | ifname
361 ---------+--------
362 orion | eth0
363 orion | eth1
364 notjw | eth0
365 pluto | eth0
366 (4 rows)
368 select * from rtest_admin;
369 pname | sysname
370 -------+---------
371 jw | orion
372 jw | notjw
373 bm | pluto
374 (3 rows)
376 update rtest_person set pname = 'jwieck' where pdesc = 'Jan Wieck';
377 -- Note: use ORDER BY here to ensure consistent output across all systems.
378 -- The above UPDATE affects two rows with equal keys, so they could be
379 -- updated in either order depending on the whim of the local qsort().
380 select * from rtest_admin order by pname, sysname;
381 pname | sysname
382 --------+---------
383 bm | pluto
384 jwieck | notjw
385 jwieck | orion
386 (3 rows)
388 delete from rtest_system where sysname = 'orion';
389 select * from rtest_interface;
390 sysname | ifname
391 ---------+--------
392 notjw | eth0
393 pluto | eth0
394 (2 rows)
396 select * from rtest_admin;
397 pname | sysname
398 --------+---------
399 bm | pluto
400 jwieck | notjw
401 (2 rows)
403 --
404 -- Rule qualification test
405 --
406 insert into rtest_emp values ('wiecc', '5000.00');
407 insert into rtest_emp values ('gates', '80000.00');
408 update rtest_emp set ename = 'wiecx' where ename = 'wiecc';
409 update rtest_emp set ename = 'wieck', salary = '6000.00' where ename = 'wiecx';
410 update rtest_emp set salary = '7000.00' where ename = 'wieck';
411 delete from rtest_emp where ename = 'gates';
412 select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal;
413 ename | matches user | action | newsal | oldsal
414 ----------------------+--------------+------------+----------+----------
415 gates | t | fired | 0.00 | 80000.00
416 gates | t | hired | 80000.00 | 0.00
417 wiecc | t | hired | 5000.00 | 0.00
418 wieck | t | honored | 6000.00 | 5000.00
419 wieck | t | honored | 7000.00 | 6000.00
420 (5 rows)
422 insert into rtest_empmass values ('meyer', '4000.00');
423 insert into rtest_empmass values ('maier', '5000.00');
424 insert into rtest_empmass values ('mayr', '6000.00');
425 insert into rtest_emp select * from rtest_empmass;
426 select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal;
427 ename | matches user | action | newsal | oldsal
428 ----------------------+--------------+------------+----------+----------
429 gates | t | fired | 0.00 | 80000.00
430 gates | t | hired | 80000.00 | 0.00
431 maier | t | hired | 5000.00 | 0.00
432 mayr | t | hired | 6000.00 | 0.00
433 meyer | t | hired | 4000.00 | 0.00
434 wiecc | t | hired | 5000.00 | 0.00
435 wieck | t | honored | 6000.00 | 5000.00
436 wieck | t | honored | 7000.00 | 6000.00
437 (8 rows)
439 update rtest_empmass set salary = salary + '1000.00';
440 update rtest_emp set salary = rtest_empmass.salary from rtest_empmass where rtest_emp.ename = rtest_empmass.ename;
441 select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal;
442 ename | matches user | action | newsal | oldsal
443 ----------------------+--------------+------------+----------+----------
444 gates | t | fired | 0.00 | 80000.00
445 gates | t | hired | 80000.00 | 0.00
446 maier | t | hired | 5000.00 | 0.00
447 maier | t | honored | 6000.00 | 5000.00
448 mayr | t | hired | 6000.00 | 0.00
449 mayr | t | honored | 7000.00 | 6000.00
450 meyer | t | hired | 4000.00 | 0.00
451 meyer | t | honored | 5000.00 | 4000.00
452 wiecc | t | hired | 5000.00 | 0.00
453 wieck | t | honored | 6000.00 | 5000.00
454 wieck | t | honored | 7000.00 | 6000.00
455 (11 rows)
457 delete from rtest_emp using rtest_empmass where rtest_emp.ename = rtest_empmass.ename;
458 select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal;
459 ename | matches user | action | newsal | oldsal
460 ----------------------+--------------+------------+----------+----------
461 gates | t | fired | 0.00 | 80000.00
462 gates | t | hired | 80000.00 | 0.00
463 maier | t | fired | 0.00 | 6000.00
464 maier | t | hired | 5000.00 | 0.00
465 maier | t | honored | 6000.00 | 5000.00
466 mayr | t | fired | 0.00 | 7000.00
467 mayr | t | hired | 6000.00 | 0.00
468 mayr | t | honored | 7000.00 | 6000.00
469 meyer | t | fired | 0.00 | 5000.00
470 meyer | t | hired | 4000.00 | 0.00
471 meyer | t | honored | 5000.00 | 4000.00
472 wiecc | t | hired | 5000.00 | 0.00
473 wieck | t | honored | 6000.00 | 5000.00
474 wieck | t | honored | 7000.00 | 6000.00
475 (14 rows)
477 --
478 -- Multiple cascaded qualified instead rule test
479 --
480 insert into rtest_t4 values (1, 'Record should go to rtest_t4');
481 insert into rtest_t4 values (2, 'Record should go to rtest_t4');
482 insert into rtest_t4 values (10, 'Record should go to rtest_t5');
483 insert into rtest_t4 values (15, 'Record should go to rtest_t5');
484 insert into rtest_t4 values (19, 'Record should go to rtest_t5 and t7');
485 insert into rtest_t4 values (20, 'Record should go to rtest_t4 and t6');
486 insert into rtest_t4 values (26, 'Record should go to rtest_t4 and t8');
487 insert into rtest_t4 values (28, 'Record should go to rtest_t4 and t8');
488 insert into rtest_t4 values (30, 'Record should go to rtest_t4');
489 insert into rtest_t4 values (40, 'Record should go to rtest_t4');
490 select * from rtest_t4;
491 a | b
492 ----+-------------------------------------
493 1 | Record should go to rtest_t4
494 2 | Record should go to rtest_t4
495 20 | Record should go to rtest_t4 and t6
496 26 | Record should go to rtest_t4 and t8
497 28 | Record should go to rtest_t4 and t8
498 30 | Record should go to rtest_t4
499 40 | Record should go to rtest_t4
500 (7 rows)
502 select * from rtest_t5;
503 a | b
504 ----+-------------------------------------
505 10 | Record should go to rtest_t5
506 15 | Record should go to rtest_t5
507 19 | Record should go to rtest_t5 and t7
508 (3 rows)
510 select * from rtest_t6;
511 a | b
512 ----+-------------------------------------
513 20 | Record should go to rtest_t4 and t6
514 (1 row)
516 select * from rtest_t7;
517 a | b
518 ----+-------------------------------------
519 19 | Record should go to rtest_t5 and t7
520 (1 row)
522 select * from rtest_t8;
523 a | b
524 ----+-------------------------------------
525 26 | Record should go to rtest_t4 and t8
526 28 | Record should go to rtest_t4 and t8
527 (2 rows)
529 delete from rtest_t4;
530 delete from rtest_t5;
531 delete from rtest_t6;
532 delete from rtest_t7;
533 delete from rtest_t8;
534 insert into rtest_t9 values (1, 'Record should go to rtest_t4');
535 insert into rtest_t9 values (2, 'Record should go to rtest_t4');
536 insert into rtest_t9 values (10, 'Record should go to rtest_t5');
537 insert into rtest_t9 values (15, 'Record should go to rtest_t5');
538 insert into rtest_t9 values (19, 'Record should go to rtest_t5 and t7');
539 insert into rtest_t9 values (20, 'Record should go to rtest_t4 and t6');
540 insert into rtest_t9 values (26, 'Record should go to rtest_t4 and t8');
541 insert into rtest_t9 values (28, 'Record should go to rtest_t4 and t8');
542 insert into rtest_t9 values (30, 'Record should go to rtest_t4');
543 insert into rtest_t9 values (40, 'Record should go to rtest_t4');
544 insert into rtest_t4 select * from rtest_t9 where a < 20;
545 select * from rtest_t4;
546 a | b
547 ---+------------------------------
548 1 | Record should go to rtest_t4
549 2 | Record should go to rtest_t4
550 (2 rows)
552 select * from rtest_t5;
553 a | b
554 ----+-------------------------------------
555 10 | Record should go to rtest_t5
556 15 | Record should go to rtest_t5
557 19 | Record should go to rtest_t5 and t7
558 (3 rows)
560 select * from rtest_t6;
561 a | b
562 ---+---
563 (0 rows)
565 select * from rtest_t7;
566 a | b
567 ----+-------------------------------------
568 19 | Record should go to rtest_t5 and t7
569 (1 row)
571 select * from rtest_t8;
572 a | b
573 ---+---
574 (0 rows)
576 insert into rtest_t4 select * from rtest_t9 where b ~ 'and t8';
577 select * from rtest_t4;
578 a | b
579 ----+-------------------------------------
580 1 | Record should go to rtest_t4
581 2 | Record should go to rtest_t4
582 26 | Record should go to rtest_t4 and t8
583 28 | Record should go to rtest_t4 and t8
584 (4 rows)
586 select * from rtest_t5;
587 a | b
588 ----+-------------------------------------
589 10 | Record should go to rtest_t5
590 15 | Record should go to rtest_t5
591 19 | Record should go to rtest_t5 and t7
592 (3 rows)
594 select * from rtest_t6;
595 a | b
596 ---+---
597 (0 rows)
599 select * from rtest_t7;
600 a | b
601 ----+-------------------------------------
602 19 | Record should go to rtest_t5 and t7
603 (1 row)
605 select * from rtest_t8;
606 a | b
607 ----+-------------------------------------
608 26 | Record should go to rtest_t4 and t8
609 28 | Record should go to rtest_t4 and t8
610 (2 rows)
612 insert into rtest_t4 select a + 1, b from rtest_t9 where a in (20, 30, 40);
613 select * from rtest_t4;
614 a | b
615 ----+-------------------------------------
616 1 | Record should go to rtest_t4
617 2 | Record should go to rtest_t4
618 26 | Record should go to rtest_t4 and t8
619 28 | Record should go to rtest_t4 and t8
620 21 | Record should go to rtest_t4 and t6
621 31 | Record should go to rtest_t4
622 41 | Record should go to rtest_t4
623 (7 rows)
625 select * from rtest_t5;
626 a | b
627 ----+-------------------------------------
628 10 | Record should go to rtest_t5
629 15 | Record should go to rtest_t5
630 19 | Record should go to rtest_t5 and t7
631 (3 rows)
633 select * from rtest_t6;
634 a | b
635 ----+-------------------------------------
636 21 | Record should go to rtest_t4 and t6
637 (1 row)
639 select * from rtest_t7;
640 a | b
641 ----+-------------------------------------
642 19 | Record should go to rtest_t5 and t7
643 (1 row)
645 select * from rtest_t8;
646 a | b
647 ----+-------------------------------------
648 26 | Record should go to rtest_t4 and t8
649 28 | Record should go to rtest_t4 and t8
650 (2 rows)
652 --
653 -- Check that the ordering of rules fired is correct
654 --
655 insert into rtest_order1 values (1);
656 select * from rtest_order2;
657 a | b | c
658 ---+---+------------------------------
659 1 | 1 | rule 1 - this should run 1st
660 1 | 2 | rule 2 - this should run 2nd
661 1 | 3 | rule 3 - this should run 3rd
662 1 | 4 | rule 4 - this should run 4th
663 (4 rows)
665 --
666 -- Check if instead nothing w/without qualification works
667 --
668 insert into rtest_nothn1 values (1, 'want this');
669 insert into rtest_nothn1 values (2, 'want this');
670 insert into rtest_nothn1 values (10, 'don''t want this');
671 insert into rtest_nothn1 values (19, 'don''t want this');
672 insert into rtest_nothn1 values (20, 'want this');
673 insert into rtest_nothn1 values (29, 'want this');
674 insert into rtest_nothn1 values (30, 'don''t want this');
675 insert into rtest_nothn1 values (39, 'don''t want this');
676 insert into rtest_nothn1 values (40, 'want this');
677 insert into rtest_nothn1 values (50, 'want this');
678 insert into rtest_nothn1 values (60, 'want this');
679 select * from rtest_nothn1;
680 a | b
681 ----+-----------
682 1 | want this
683 2 | want this
684 20 | want this
685 29 | want this
686 40 | want this
687 50 | want this
688 60 | want this
689 (7 rows)
691 insert into rtest_nothn2 values (10, 'too small');
692 insert into rtest_nothn2 values (50, 'too small');
693 insert into rtest_nothn2 values (100, 'OK');
694 insert into rtest_nothn2 values (200, 'OK');
695 select * from rtest_nothn2;
696 a | b
697 ---+---
698 (0 rows)
700 select * from rtest_nothn3;
701 a | b
702 -----+----
703 100 | OK
704 200 | OK
705 (2 rows)
707 delete from rtest_nothn1;
708 delete from rtest_nothn2;
709 delete from rtest_nothn3;
710 insert into rtest_nothn4 values (1, 'want this');
711 insert into rtest_nothn4 values (2, 'want this');
712 insert into rtest_nothn4 values (10, 'don''t want this');
713 insert into rtest_nothn4 values (19, 'don''t want this');
714 insert into rtest_nothn4 values (20, 'want this');
715 insert into rtest_nothn4 values (29, 'want this');
716 insert into rtest_nothn4 values (30, 'don''t want this');
717 insert into rtest_nothn4 values (39, 'don''t want this');
718 insert into rtest_nothn4 values (40, 'want this');
719 insert into rtest_nothn4 values (50, 'want this');
720 insert into rtest_nothn4 values (60, 'want this');
721 insert into rtest_nothn1 select * from rtest_nothn4;
722 select * from rtest_nothn1;
723 a | b
724 ----+-----------
725 1 | want this
726 2 | want this
727 20 | want this
728 29 | want this
729 40 | want this
730 50 | want this
731 60 | want this
732 (7 rows)
734 delete from rtest_nothn4;
735 insert into rtest_nothn4 values (10, 'too small');
736 insert into rtest_nothn4 values (50, 'too small');
737 insert into rtest_nothn4 values (100, 'OK');
738 insert into rtest_nothn4 values (200, 'OK');
739 insert into rtest_nothn2 select * from rtest_nothn4;
740 select * from rtest_nothn2;
741 a | b
742 ---+---
743 (0 rows)
745 select * from rtest_nothn3;
746 a | b
747 -----+----
748 100 | OK
749 200 | OK
750 (2 rows)
752 create table rtest_view1 (a int4, b text, v bool);
753 create table rtest_view2 (a int4);
754 create table rtest_view3 (a int4, b text);
755 create table rtest_view4 (a int4, b text, c int4);
756 create view rtest_vview1 as select a, b from rtest_view1 X
757 where 0 < (select count(*) from rtest_view2 Y where Y.a = X.a);
758 create view rtest_vview2 as select a, b from rtest_view1 where v;
759 create view rtest_vview3 as select a, b from rtest_vview2 X
760 where 0 < (select count(*) from rtest_view2 Y where Y.a = X.a);
761 create view rtest_vview4 as select X.a, X.b, count(Y.a) as refcount
762 from rtest_view1 X, rtest_view2 Y
763 where X.a = Y.a
764 group by X.a, X.b;
765 create function rtest_viewfunc1(int4) returns int4 as
766 'select count(*)::int4 from rtest_view2 where a = $1'
767 language sql;
768 create view rtest_vview5 as select a, b, rtest_viewfunc1(a) as refcount
769 from rtest_view1;
770 insert into rtest_view1 values (1, 'item 1', 't');
771 insert into rtest_view1 values (2, 'item 2', 't');
772 insert into rtest_view1 values (3, 'item 3', 't');
773 insert into rtest_view1 values (4, 'item 4', 'f');
774 insert into rtest_view1 values (5, 'item 5', 't');
775 insert into rtest_view1 values (6, 'item 6', 'f');
776 insert into rtest_view1 values (7, 'item 7', 't');
777 insert into rtest_view1 values (8, 'item 8', 't');
778 insert into rtest_view2 values (2);
779 insert into rtest_view2 values (2);
780 insert into rtest_view2 values (4);
781 insert into rtest_view2 values (5);
782 insert into rtest_view2 values (7);
783 insert into rtest_view2 values (7);
784 insert into rtest_view2 values (7);
785 insert into rtest_view2 values (7);
786 select * from rtest_vview1;
787 a | b
788 ---+--------
789 2 | item 2
790 4 | item 4
791 5 | item 5
792 7 | item 7
793 (4 rows)
795 select * from rtest_vview2;
796 a | b
797 ---+--------
798 1 | item 1
799 2 | item 2
800 3 | item 3
801 5 | item 5
802 7 | item 7
803 8 | item 8
804 (6 rows)
806 select * from rtest_vview3;
807 a | b
808 ---+--------
809 2 | item 2
810 5 | item 5
811 7 | item 7
812 (3 rows)
814 select * from rtest_vview4 order by a, b;
815 a | b | refcount
816 ---+--------+----------
817 2 | item 2 | 2
818 4 | item 4 | 1
819 5 | item 5 | 1
820 7 | item 7 | 4
821 (4 rows)
823 select * from rtest_vview5;
824 a | b | refcount
825 ---+--------+----------
826 1 | item 1 | 0
827 2 | item 2 | 2
828 3 | item 3 | 0
829 4 | item 4 | 1
830 5 | item 5 | 1
831 6 | item 6 | 0
832 7 | item 7 | 4
833 8 | item 8 | 0
834 (8 rows)
836 insert into rtest_view3 select * from rtest_vview1 where a < 7;
837 select * from rtest_view3;
838 a | b
839 ---+--------
840 2 | item 2
841 4 | item 4
842 5 | item 5
843 (3 rows)
845 delete from rtest_view3;
846 insert into rtest_view3 select * from rtest_vview2 where a != 5 and b !~ '2';
847 select * from rtest_view3;
848 a | b
849 ---+--------
850 1 | item 1
851 3 | item 3
852 7 | item 7
853 8 | item 8
854 (4 rows)
856 delete from rtest_view3;
857 insert into rtest_view3 select * from rtest_vview3;
858 select * from rtest_view3;
859 a | b
860 ---+--------
861 2 | item 2
862 5 | item 5
863 7 | item 7
864 (3 rows)
866 delete from rtest_view3;
867 insert into rtest_view4 select * from rtest_vview4 where 3 > refcount;
868 select * from rtest_view4 order by a, b;
869 a | b | c
870 ---+--------+---
871 2 | item 2 | 2
872 4 | item 4 | 1
873 5 | item 5 | 1
874 (3 rows)
876 delete from rtest_view4;
877 insert into rtest_view4 select * from rtest_vview5 where a > 2 and refcount = 0;
878 select * from rtest_view4;
879 a | b | c
880 ---+--------+---
881 3 | item 3 | 0
882 6 | item 6 | 0
883 8 | item 8 | 0
884 (3 rows)
886 delete from rtest_view4;
887 --
888 -- Test for computations in views
889 --
890 create table rtest_comp (
891 part text,
892 unit char(4),
893 size float
894 );
895 create table rtest_unitfact (
896 unit char(4),
897 factor float
898 );
899 create view rtest_vcomp as
900 select X.part, (X.size * Y.factor) as size_in_cm
901 from rtest_comp X, rtest_unitfact Y
902 where X.unit = Y.unit;
903 insert into rtest_unitfact values ('m', 100.0);
904 insert into rtest_unitfact values ('cm', 1.0);
905 insert into rtest_unitfact values ('inch', 2.54);
906 insert into rtest_comp values ('p1', 'm', 5.0);
907 insert into rtest_comp values ('p2', 'm', 3.0);
908 insert into rtest_comp values ('p3', 'cm', 5.0);
909 insert into rtest_comp values ('p4', 'cm', 15.0);
910 insert into rtest_comp values ('p5', 'inch', 7.0);
911 insert into rtest_comp values ('p6', 'inch', 4.4);
912 select * from rtest_vcomp order by part;
913 part | size_in_cm
914 ------+--------------------
915 p1 | 500
916 p2 | 300
917 p3 | 5
918 p4 | 15
919 p5 | 17.78
920 p6 | 11.176000000000002
921 (6 rows)
923 select * from rtest_vcomp where size_in_cm > 10.0 order by size_in_cm using >;
924 part | size_in_cm
925 ------+--------------------
926 p1 | 500
927 p2 | 300
928 p5 | 17.78
929 p4 | 15
930 p6 | 11.176000000000002
931 (5 rows)
933 --
934 -- In addition run the (slightly modified) queries from the
935 -- programmers manual section on the rule system.
936 --
937 CREATE TABLE shoe_data (
938 shoename char(10), -- primary key
939 sh_avail integer, -- available # of pairs
940 slcolor char(10), -- preferred shoelace color
941 slminlen float, -- minimum shoelace length
942 slmaxlen float, -- maximum shoelace length
943 slunit char(8) -- length unit
944 );
945 CREATE TABLE shoelace_data (
946 sl_name char(10), -- primary key
947 sl_avail integer, -- available # of pairs
948 sl_color char(10), -- shoelace color
949 sl_len float, -- shoelace length
950 sl_unit char(8) -- length unit
951 );
952 CREATE TABLE unit (
953 un_name char(8), -- the primary key
954 un_fact float -- factor to transform to cm
955 );
956 CREATE VIEW shoe AS
957 SELECT sh.shoename,
958 sh.sh_avail,
959 sh.slcolor,
960 sh.slminlen,
961 sh.slminlen * un.un_fact AS slminlen_cm,
962 sh.slmaxlen,
963 sh.slmaxlen * un.un_fact AS slmaxlen_cm,
964 sh.slunit
965 FROM shoe_data sh, unit un
966 WHERE sh.slunit = un.un_name;
967 CREATE VIEW shoelace AS
968 SELECT s.sl_name,
969 s.sl_avail,
970 s.sl_color,
971 s.sl_len,
972 s.sl_unit,
973 s.sl_len * u.un_fact AS sl_len_cm
974 FROM shoelace_data s, unit u
975 WHERE s.sl_unit = u.un_name;
976 CREATE VIEW shoe_ready AS
977 SELECT rsh.shoename,
978 rsh.sh_avail,
979 rsl.sl_name,
980 rsl.sl_avail,
981 int4smaller(rsh.sh_avail, rsl.sl_avail) AS total_avail
982 FROM shoe rsh, shoelace rsl
983 WHERE rsl.sl_color = rsh.slcolor
984 AND rsl.sl_len_cm >= rsh.slminlen_cm
985 AND rsl.sl_len_cm <= rsh.slmaxlen_cm;
986 INSERT INTO unit VALUES ('cm', 1.0);
987 INSERT INTO unit VALUES ('m', 100.0);
988 INSERT INTO unit VALUES ('inch', 2.54);
989 INSERT INTO shoe_data VALUES ('sh1', 2, 'black', 70.0, 90.0, 'cm');
990 INSERT INTO shoe_data VALUES ('sh2', 0, 'black', 30.0, 40.0, 'inch');
991 INSERT INTO shoe_data VALUES ('sh3', 4, 'brown', 50.0, 65.0, 'cm');
992 INSERT INTO shoe_data VALUES ('sh4', 3, 'brown', 40.0, 50.0, 'inch');
993 INSERT INTO shoelace_data VALUES ('sl1', 5, 'black', 80.0, 'cm');
994 INSERT INTO shoelace_data VALUES ('sl2', 6, 'black', 100.0, 'cm');
995 INSERT INTO shoelace_data VALUES ('sl3', 0, 'black', 35.0 , 'inch');
996 INSERT INTO shoelace_data VALUES ('sl4', 8, 'black', 40.0 , 'inch');
997 INSERT INTO shoelace_data VALUES ('sl5', 4, 'brown', 1.0 , 'm');
998 INSERT INTO shoelace_data VALUES ('sl6', 0, 'brown', 0.9 , 'm');
999 INSERT INTO shoelace_data VALUES ('sl7', 7, 'brown', 60 , 'cm');
1000 INSERT INTO shoelace_data VALUES ('sl8', 1, 'brown', 40 , 'inch');
1001 -- SELECTs in doc
1002 SELECT * FROM shoelace ORDER BY sl_name;
1003 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
1004 ------------+----------+------------+--------+----------+-----------
1005 sl1 | 5 | black | 80 | cm | 80
1006 sl2 | 6 | black | 100 | cm | 100
1007 sl3 | 0 | black | 35 | inch | 88.9
1008 sl4 | 8 | black | 40 | inch | 101.6
1009 sl5 | 4 | brown | 1 | m | 100
1010 sl6 | 0 | brown | 0.9 | m | 90
1011 sl7 | 7 | brown | 60 | cm | 60
1012 sl8 | 1 | brown | 40 | inch | 101.6
1013 (8 rows)
1015 SELECT * FROM shoe_ready WHERE total_avail >= 2 ORDER BY 1;
1016 shoename | sh_avail | sl_name | sl_avail | total_avail
1017 ------------+----------+------------+----------+-------------
1018 sh1 | 2 | sl1 | 5 | 2
1019 sh3 | 4 | sl7 | 7 | 4
1020 (2 rows)
1022 CREATE TABLE shoelace_log (
1023 sl_name char(10), -- shoelace changed
1024 sl_avail integer, -- new available value
1025 log_who name, -- who did it
1026 log_when timestamp -- when
1027 );
1028 -- Want "log_who" to be CURRENT_USER,
1029 -- but that is non-portable for the regression test
1030 -- - thomas 1999-02-21
1031 CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
1032 WHERE NEW.sl_avail != OLD.sl_avail
1033 DO INSERT INTO shoelace_log VALUES (
1034 NEW.sl_name,
1035 NEW.sl_avail,
1036 'Al Bundy',
1037 'epoch'
1038 );
1039 UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';
1040 SELECT * FROM shoelace_log;
1041 sl_name | sl_avail | log_who | log_when
1042 ------------+----------+----------+--------------------------
1043 sl7 | 6 | Al Bundy | Thu Jan 01 00:00:00 1970
1044 (1 row)
1046 CREATE RULE shoelace_ins AS ON INSERT TO shoelace
1047 DO INSTEAD
1048 INSERT INTO shoelace_data VALUES (
1049 NEW.sl_name,
1050 NEW.sl_avail,
1051 NEW.sl_color,
1052 NEW.sl_len,
1053 NEW.sl_unit);
1054 CREATE RULE shoelace_upd AS ON UPDATE TO shoelace
1055 DO INSTEAD
1056 UPDATE shoelace_data SET
1057 sl_name = NEW.sl_name,
1058 sl_avail = NEW.sl_avail,
1059 sl_color = NEW.sl_color,
1060 sl_len = NEW.sl_len,
1061 sl_unit = NEW.sl_unit
1062 WHERE sl_name = OLD.sl_name;
1063 CREATE RULE shoelace_del AS ON DELETE TO shoelace
1064 DO INSTEAD
1065 DELETE FROM shoelace_data
1066 WHERE sl_name = OLD.sl_name;
1067 CREATE TABLE shoelace_arrive (
1068 arr_name char(10),
1069 arr_quant integer
1070 );
1071 CREATE TABLE shoelace_ok (
1072 ok_name char(10),
1073 ok_quant integer
1074 );
1075 CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok
1076 DO INSTEAD
1077 UPDATE shoelace SET
1078 sl_avail = sl_avail + NEW.ok_quant
1079 WHERE sl_name = NEW.ok_name;
1080 INSERT INTO shoelace_arrive VALUES ('sl3', 10);
1081 INSERT INTO shoelace_arrive VALUES ('sl6', 20);
1082 INSERT INTO shoelace_arrive VALUES ('sl8', 20);
1083 SELECT * FROM shoelace ORDER BY sl_name;
1084 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
1085 ------------+----------+------------+--------+----------+-----------
1086 sl1 | 5 | black | 80 | cm | 80
1087 sl2 | 6 | black | 100 | cm | 100
1088 sl3 | 0 | black | 35 | inch | 88.9
1089 sl4 | 8 | black | 40 | inch | 101.6
1090 sl5 | 4 | brown | 1 | m | 100
1091 sl6 | 0 | brown | 0.9 | m | 90
1092 sl7 | 6 | brown | 60 | cm | 60
1093 sl8 | 1 | brown | 40 | inch | 101.6
1094 (8 rows)
1096 insert into shoelace_ok select * from shoelace_arrive;
1097 SELECT * FROM shoelace ORDER BY sl_name;
1098 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
1099 ------------+----------+------------+--------+----------+-----------
1100 sl1 | 5 | black | 80 | cm | 80
1101 sl2 | 6 | black | 100 | cm | 100
1102 sl3 | 10 | black | 35 | inch | 88.9
1103 sl4 | 8 | black | 40 | inch | 101.6
1104 sl5 | 4 | brown | 1 | m | 100
1105 sl6 | 20 | brown | 0.9 | m | 90
1106 sl7 | 6 | brown | 60 | cm | 60
1107 sl8 | 21 | brown | 40 | inch | 101.6
1108 (8 rows)
1110 SELECT * FROM shoelace_log ORDER BY sl_name;
1111 sl_name | sl_avail | log_who | log_when
1112 ------------+----------+----------+--------------------------
1113 sl3 | 10 | Al Bundy | Thu Jan 01 00:00:00 1970
1114 sl6 | 20 | Al Bundy | Thu Jan 01 00:00:00 1970
1115 sl7 | 6 | Al Bundy | Thu Jan 01 00:00:00 1970
1116 sl8 | 21 | Al Bundy | Thu Jan 01 00:00:00 1970
1117 (4 rows)
1119 CREATE VIEW shoelace_obsolete AS
1120 SELECT * FROM shoelace WHERE NOT EXISTS
1121 (SELECT shoename FROM shoe WHERE slcolor = sl_color);
1122 CREATE VIEW shoelace_candelete AS
1123 SELECT * FROM shoelace_obsolete WHERE sl_avail = 0;
1124 insert into shoelace values ('sl9', 0, 'pink', 35.0, 'inch', 0.0);
1125 insert into shoelace values ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0);
1126 -- Unsupported (even though a similar updatable view construct is)
1127 insert into shoelace values ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0)
1128 on conflict do nothing;
1129 ERROR: INSERT with ON CONFLICT clause cannot be used with table that has INSERT or UPDATE rules
1130 SELECT * FROM shoelace_obsolete ORDER BY sl_len_cm;
1131 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
1132 ------------+----------+------------+--------+----------+-----------
1133 sl9 | 0 | pink | 35 | inch | 88.9
1134 sl10 | 1000 | magenta | 40 | inch | 101.6
1135 (2 rows)
1137 SELECT * FROM shoelace_candelete;
1138 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
1139 ------------+----------+------------+--------+----------+-----------
1140 sl9 | 0 | pink | 35 | inch | 88.9
1141 (1 row)
1143 DELETE FROM shoelace WHERE EXISTS
1144 (SELECT * FROM shoelace_candelete
1145 WHERE sl_name = shoelace.sl_name);
1146 SELECT * FROM shoelace ORDER BY sl_name;
1147 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
1148 ------------+----------+------------+--------+----------+-----------
1149 sl1 | 5 | black | 80 | cm | 80
1150 sl10 | 1000 | magenta | 40 | inch | 101.6
1151 sl2 | 6 | black | 100 | cm | 100
1152 sl3 | 10 | black | 35 | inch | 88.9
1153 sl4 | 8 | black | 40 | inch | 101.6
1154 sl5 | 4 | brown | 1 | m | 100
1155 sl6 | 20 | brown | 0.9 | m | 90
1156 sl7 | 6 | brown | 60 | cm | 60
1157 sl8 | 21 | brown | 40 | inch | 101.6
1158 (9 rows)
1160 SELECT * FROM shoe ORDER BY shoename;
1161 shoename | sh_avail | slcolor | slminlen | slminlen_cm | slmaxlen | slmaxlen_cm | slunit
1162 ------------+----------+------------+----------+-------------+----------+-------------+----------
1163 sh1 | 2 | black | 70 | 70 | 90 | 90 | cm
1164 sh2 | 0 | black | 30 | 76.2 | 40 | 101.6 | inch
1165 sh3 | 4 | brown | 50 | 50 | 65 | 65 | cm
1166 sh4 | 3 | brown | 40 | 101.6 | 50 | 127 | inch
1167 (4 rows)
1169 SELECT count(*) FROM shoe;
1170 count
1171 -------
1172 4
1173 (1 row)
1175 --
1176 -- Simple test of qualified ON INSERT ... this did not work in 7.0 ...
1177 --
1178 create table rules_foo (f1 int);
1179 create table rules_foo2 (f1 int);
1180 create rule rules_foorule as on insert to rules_foo where f1 < 100
1181 do instead nothing;
1182 insert into rules_foo values(1);
1183 insert into rules_foo values(1001);
1184 select * from rules_foo;
1185 f1
1186 ------
1187 1001
1188 (1 row)
1190 drop rule rules_foorule on rules_foo;
1191 -- this should fail because f1 is not exposed for unqualified reference:
1192 create rule rules_foorule as on insert to rules_foo where f1 < 100
1193 do instead insert into rules_foo2 values (f1);
1194 ERROR: column "f1" does not exist
1195 LINE 2: do instead insert into rules_foo2 values (f1);
1196 ^
1197 DETAIL: There are columns named "f1", but they are in tables that cannot be referenced from this part of the query.
1198 HINT: Try using a table-qualified name.
1199 -- this is the correct way:
1200 create rule rules_foorule as on insert to rules_foo where f1 < 100
1201 do instead insert into rules_foo2 values (new.f1);
1202 insert into rules_foo values(2);
1203 insert into rules_foo values(100);
1204 select * from rules_foo;
1205 f1
1206 ------
1207 1001
1208 100
1209 (2 rows)
1211 select * from rules_foo2;
1212 f1
1213 ----
1214 2
1215 (1 row)
1217 drop rule rules_foorule on rules_foo;
1218 drop table rules_foo;
1219 drop table rules_foo2;
1220 --
1221 -- Test rules containing INSERT ... SELECT, which is a very ugly special
1222 -- case as of 7.1. Example is based on bug report from Joel Burton.
1223 --
1224 create table pparent (pid int, txt text);
1225 insert into pparent values (1,'parent1');
1226 insert into pparent values (2,'parent2');
1227 create table cchild (pid int, descrip text);
1228 insert into cchild values (1,'descrip1');
1229 create view vview as
1230 select pparent.pid, txt, descrip from
1231 pparent left join cchild using (pid);
1232 create rule rrule as
1233 on update to vview do instead
1234 (
1235 insert into cchild (pid, descrip)
1236 select old.pid, new.descrip where old.descrip isnull;
1237 update cchild set descrip = new.descrip where cchild.pid = old.pid;
1238 );
1239 select * from vview;
1240 pid | txt | descrip
1241 -----+---------+----------
1242 1 | parent1 | descrip1
1243 2 | parent2 |
1244 (2 rows)
1246 update vview set descrip='test1' where pid=1;
1247 select * from vview;
1248 pid | txt | descrip
1249 -----+---------+---------
1250 1 | parent1 | test1
1251 2 | parent2 |
1252 (2 rows)
1254 update vview set descrip='test2' where pid=2;
1255 select * from vview;
1256 pid | txt | descrip
1257 -----+---------+---------
1258 1 | parent1 | test1
1259 2 | parent2 | test2
1260 (2 rows)
1262 update vview set descrip='test3' where pid=3;
1263 select * from vview;
1264 pid | txt | descrip
1265 -----+---------+---------
1266 1 | parent1 | test1
1267 2 | parent2 | test2
1268 (2 rows)
1270 select * from cchild;
1271 pid | descrip
1272 -----+---------
1273 1 | test1
1274 2 | test2
1275 (2 rows)
1277 drop rule rrule on vview;
1278 drop view vview;
1279 drop table pparent;
1280 drop table cchild;
1281 --
1282 -- Check that ruleutils are working
1283 --
1284 -- temporarily disable fancy output, so view changes create less diff noise
1285 \a\t
1286 SELECT viewname, definition FROM pg_views
1287 WHERE schemaname = 'pg_catalog'
1288 ORDER BY viewname;
1289 pg_available_extension_versions| SELECT e.name,
1290 e.version,
1291 (x.extname IS NOT NULL) AS installed,
1292 e.superuser,
1293 e.trusted,
1294 e.relocatable,
1295 e.schema,
1296 e.requires,
1297 e.comment
1298 FROM (pg_available_extension_versions() e(name, version, superuser, trusted, relocatable, schema, requires, comment)
1299 LEFT JOIN pg_extension x ON (((e.name = x.extname) AND (e.version = x.extversion))));
1300 pg_available_extensions| SELECT e.name,
1301 e.default_version,
1302 x.extversion AS installed_version,
1303 e.comment
1304 FROM (pg_available_extensions() e(name, default_version, comment)
1305 LEFT JOIN pg_extension x ON ((e.name = x.extname)));
1306 pg_backend_memory_contexts| SELECT name,
1307 ident,
1308 parent,
1309 level,
1310 total_bytes,
1311 total_nblocks,
1312 free_bytes,
1313 free_chunks,
1314 used_bytes
1315 FROM pg_get_backend_memory_contexts() pg_get_backend_memory_contexts(name, ident, parent, level, total_bytes, total_nblocks, free_bytes, free_chunks, used_bytes);
1316 pg_config| SELECT name,
1317 setting
1318 FROM pg_config() pg_config(name, setting);
1319 pg_cursors| SELECT name,
1320 statement,
1321 is_holdable,
1322 is_binary,
1323 is_scrollable,
1324 creation_time
1325 FROM pg_cursor() c(name, statement, is_holdable, is_binary, is_scrollable, creation_time);
1326 pg_file_settings| SELECT sourcefile,
1327 sourceline,
1328 seqno,
1329 name,
1330 setting,
1331 applied,
1332 error
1333 FROM pg_show_all_file_settings() a(sourcefile, sourceline, seqno, name, setting, applied, error);
1334 pg_group| SELECT rolname AS groname,
1335 oid AS grosysid,
1336 ARRAY( SELECT pg_auth_members.member
1337 FROM pg_auth_members
1338 WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist
1339 FROM pg_authid
1340 WHERE (NOT rolcanlogin);
1341 pg_hba_file_rules| SELECT rule_number,
1342 file_name,
1343 line_number,
1344 type,
1345 database,
1346 user_name,
1347 address,
1348 netmask,
1349 auth_method,
1350 options,
1351 error
1352 FROM pg_hba_file_rules() a(rule_number, file_name, line_number, type, database, user_name, address, netmask, auth_method, options, error);
1353 pg_ident_file_mappings| SELECT map_number,
1354 file_name,
1355 line_number,
1356 map_name,
1357 sys_name,
1358 pg_username,
1359 error
1360 FROM pg_ident_file_mappings() a(map_number, file_name, line_number, map_name, sys_name, pg_username, error);
1361 pg_indexes| SELECT n.nspname AS schemaname,
1362 c.relname AS tablename,
1363 i.relname AS indexname,
1364 t.spcname AS tablespace,
1365 pg_get_indexdef(i.oid) AS indexdef
1366 FROM ((((pg_index x
1367 JOIN pg_class c ON ((c.oid = x.indrelid)))
1368 JOIN pg_class i ON ((i.oid = x.indexrelid)))
1369 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
1370 LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace)))
1371 WHERE ((c.relkind = ANY (ARRAY['r'::"char", 'm'::"char", 'p'::"char"])) AND (i.relkind = ANY (ARRAY['i'::"char", 'I'::"char"])));
1372 pg_locks| SELECT locktype,
1373 database,
1374 relation,
1375 page,
1376 tuple,
1377 virtualxid,
1378 transactionid,
1379 classid,
1380 objid,
1381 objsubid,
1382 virtualtransaction,
1383 pid,
1384 mode,
1385 granted,
1386 fastpath,
1387 waitstart
1388 FROM pg_lock_status() l(locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted, fastpath, waitstart);
1389 pg_matviews| SELECT n.nspname AS schemaname,
1390 c.relname AS matviewname,
1391 pg_get_userbyid(c.relowner) AS matviewowner,
1392 t.spcname AS tablespace,
1393 c.relhasindex AS hasindexes,
1394 c.relispopulated AS ispopulated,
1395 pg_get_viewdef(c.oid) AS definition
1396 FROM ((pg_class c
1397 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
1398 LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace)))
1399 WHERE (c.relkind = 'm'::"char");
1400 pg_policies| SELECT n.nspname AS schemaname,
1401 c.relname AS tablename,
1402 pol.polname AS policyname,
1403 CASE
1404 WHEN pol.polpermissive THEN 'PERMISSIVE'::text
1405 ELSE 'RESTRICTIVE'::text
1406 END AS permissive,
1407 CASE
1408 WHEN (pol.polroles = '{0}'::oid[]) THEN (string_to_array('public'::text, ''::text))::name[]
1409 ELSE ARRAY( SELECT pg_authid.rolname
1410 FROM pg_authid
1411 WHERE (pg_authid.oid = ANY (pol.polroles))
1412 ORDER BY pg_authid.rolname)
1413 END AS roles,
1414 CASE pol.polcmd
1415 WHEN 'r'::"char" THEN 'SELECT'::text
1416 WHEN 'a'::"char" THEN 'INSERT'::text
1417 WHEN 'w'::"char" THEN 'UPDATE'::text
1418 WHEN 'd'::"char" THEN 'DELETE'::text
1419 WHEN '*'::"char" THEN 'ALL'::text
1420 ELSE NULL::text
1421 END AS cmd,
1422 pg_get_expr(pol.polqual, pol.polrelid) AS qual,
1423 pg_get_expr(pol.polwithcheck, pol.polrelid) AS with_check
1424 FROM ((pg_policy pol
1425 JOIN pg_class c ON ((c.oid = pol.polrelid)))
1426 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)));
1427 pg_prepared_statements| SELECT name,
1428 statement,
1429 prepare_time,
1430 parameter_types,
1431 result_types,
1432 from_sql,
1433 generic_plans,
1434 custom_plans
1435 FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, result_types, from_sql, generic_plans, custom_plans);
1436 pg_prepared_xacts| SELECT p.transaction,
1437 p.gid,
1438 p.prepared,
1439 u.rolname AS owner,
1440 d.datname AS database
1441 FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid)
1442 LEFT JOIN pg_authid u ON ((p.ownerid = u.oid)))
1443 LEFT JOIN pg_database d ON ((p.dbid = d.oid)));
1444 pg_publication_tables| SELECT p.pubname,
1445 n.nspname AS schemaname,
1446 c.relname AS tablename,
1447 ( SELECT array_agg(a.attname ORDER BY a.attnum) AS array_agg
1448 FROM pg_attribute a
1449 WHERE ((a.attrelid = gpt.relid) AND (a.attnum = ANY ((gpt.attrs)::smallint[])))) AS attnames,
1450 pg_get_expr(gpt.qual, gpt.relid) AS rowfilter
1451 FROM pg_publication p,
1452 LATERAL pg_get_publication_tables(VARIADIC ARRAY[(p.pubname)::text]) gpt(pubid, relid, attrs, qual),
1453 (pg_class c
1454 JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
1455 WHERE (c.oid = gpt.relid);
1456 pg_replication_origin_status| SELECT local_id,
1457 external_id,
1458 remote_lsn,
1459 local_lsn
1460 FROM pg_show_replication_origin_status() pg_show_replication_origin_status(local_id, external_id, remote_lsn, local_lsn);
1461 pg_replication_slots| SELECT l.slot_name,
1462 l.plugin,
1463 l.slot_type,
1464 l.datoid,
1465 d.datname AS database,
1466 l.temporary,
1467 l.active,
1468 l.active_pid,
1469 l.xmin,
1470 l.catalog_xmin,
1471 l.restart_lsn,
1472 l.confirmed_flush_lsn,
1473 l.wal_status,
1474 l.safe_wal_size,
1475 l.two_phase,
1476 l.conflicting,
1477 l.invalidation_reason,
1478 l.failover,
1479 l.synced
1480 FROM (pg_get_replication_slots() l(slot_name, plugin, slot_type, datoid, temporary, active, active_pid, xmin, catalog_xmin, restart_lsn, confirmed_flush_lsn, wal_status, safe_wal_size, two_phase, conflicting, invalidation_reason, failover, synced)
1481 LEFT JOIN pg_database d ON ((l.datoid = d.oid)));
1482 pg_roles| SELECT pg_authid.rolname,
1483 pg_authid.rolsuper,
1484 pg_authid.rolinherit,
1485 pg_authid.rolcreaterole,
1486 pg_authid.rolcreatedb,
1487 pg_authid.rolcanlogin,
1488 pg_authid.rolreplication,
1489 pg_authid.rolconnlimit,
1490 '********'::text AS rolpassword,
1491 pg_authid.rolvaliduntil,
1492 pg_authid.rolbypassrls,
1493 s.setconfig AS rolconfig,
1494 pg_authid.oid
1495 FROM (pg_authid
1496 LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))));
1497 pg_rules| SELECT n.nspname AS schemaname,
1498 c.relname AS tablename,
1499 r.rulename,
1500 pg_get_ruledef(r.oid) AS definition
1501 FROM ((pg_rewrite r
1502 JOIN pg_class c ON ((c.oid = r.ev_class)))
1503 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
1504 WHERE (r.rulename <> '_RETURN'::name);
1505 pg_seclabels| SELECT l.objoid,
1506 l.classoid,
1507 l.objsubid,
1508 CASE
1509 WHEN (rel.relkind = ANY (ARRAY['r'::"char", 'p'::"char"])) THEN 'table'::text
1510 WHEN (rel.relkind = 'v'::"char") THEN 'view'::text
1511 WHEN (rel.relkind = 'm'::"char") THEN 'materialized view'::text
1512 WHEN (rel.relkind = 'S'::"char") THEN 'sequence'::text
1513 WHEN (rel.relkind = 'f'::"char") THEN 'foreign table'::text
1514 ELSE NULL::text
1515 END AS objtype,
1516 rel.relnamespace AS objnamespace,
1517 CASE
1518 WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text)
1519 ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text))
1520 END AS objname,
1521 l.provider,
1522 l.label
1523 FROM ((pg_seclabel l
1524 JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid))))
1525 JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid)))
1526 WHERE (l.objsubid = 0)
1527 UNION ALL
1528 SELECT l.objoid,
1529 l.classoid,
1530 l.objsubid,
1531 'column'::text AS objtype,
1532 rel.relnamespace AS objnamespace,
1533 ((
1534 CASE
1535 WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text)
1536 ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text))
1537 END || '.'::text) || (att.attname)::text) AS objname,
1538 l.provider,
1539 l.label
1540 FROM (((pg_seclabel l
1541 JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid))))
1542 JOIN pg_attribute att ON (((rel.oid = att.attrelid) AND (l.objsubid = att.attnum))))
1543 JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid)))
1544 WHERE (l.objsubid <> 0)
1545 UNION ALL
1546 SELECT l.objoid,
1547 l.classoid,
1548 l.objsubid,
1549 CASE pro.prokind
1550 WHEN 'a'::"char" THEN 'aggregate'::text
1551 WHEN 'f'::"char" THEN 'function'::text
1552 WHEN 'p'::"char" THEN 'procedure'::text
1553 WHEN 'w'::"char" THEN 'window'::text
1554 ELSE NULL::text
1555 END AS objtype,
1556 pro.pronamespace AS objnamespace,
1557 (((
1558 CASE
1559 WHEN pg_function_is_visible(pro.oid) THEN quote_ident((pro.proname)::text)
1560 ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((pro.proname)::text))
1561 END || '('::text) || pg_get_function_arguments(pro.oid)) || ')'::text) AS objname,
1562 l.provider,
1563 l.label
1564 FROM ((pg_seclabel l
1565 JOIN pg_proc pro ON (((l.classoid = pro.tableoid) AND (l.objoid = pro.oid))))
1566 JOIN pg_namespace nsp ON ((pro.pronamespace = nsp.oid)))
1567 WHERE (l.objsubid = 0)
1568 UNION ALL
1569 SELECT l.objoid,
1570 l.classoid,
1571 l.objsubid,
1572 CASE
1573 WHEN (typ.typtype = 'd'::"char") THEN 'domain'::text
1574 ELSE 'type'::text
1575 END AS objtype,
1576 typ.typnamespace AS objnamespace,
1577 CASE
1578 WHEN pg_type_is_visible(typ.oid) THEN quote_ident((typ.typname)::text)
1579 ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((typ.typname)::text))
1580 END AS objname,
1581 l.provider,
1582 l.label
1583 FROM ((pg_seclabel l
1584 JOIN pg_type typ ON (((l.classoid = typ.tableoid) AND (l.objoid = typ.oid))))
1585 JOIN pg_namespace nsp ON ((typ.typnamespace = nsp.oid)))
1586 WHERE (l.objsubid = 0)
1587 UNION ALL
1588 SELECT l.objoid,
1589 l.classoid,
1590 l.objsubid,
1591 'large object'::text AS objtype,
1592 NULL::oid AS objnamespace,
1593 (l.objoid)::text AS objname,
1594 l.provider,
1595 l.label
1596 FROM (pg_seclabel l
1597 JOIN pg_largeobject_metadata lom ON ((l.objoid = lom.oid)))
1598 WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND (l.objsubid = 0))
1599 UNION ALL
1600 SELECT l.objoid,
1601 l.classoid,
1602 l.objsubid,
1603 'language'::text AS objtype,
1604 NULL::oid AS objnamespace,
1605 quote_ident((lan.lanname)::text) AS objname,
1606 l.provider,
1607 l.label
1608 FROM (pg_seclabel l
1609 JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND (l.objoid = lan.oid))))
1610 WHERE (l.objsubid = 0)
1611 UNION ALL
1612 SELECT l.objoid,
1613 l.classoid,
1614 l.objsubid,
1615 'schema'::text AS objtype,
1616 nsp.oid AS objnamespace,
1617 quote_ident((nsp.nspname)::text) AS objname,
1618 l.provider,
1619 l.label
1620 FROM (pg_seclabel l
1621 JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid = nsp.oid))))
1622 WHERE (l.objsubid = 0)
1623 UNION ALL
1624 SELECT l.objoid,
1625 l.classoid,
1626 l.objsubid,
1627 'event trigger'::text AS objtype,
1628 NULL::oid AS objnamespace,
1629 quote_ident((evt.evtname)::text) AS objname,
1630 l.provider,
1631 l.label
1632 FROM (pg_seclabel l
1633 JOIN pg_event_trigger evt ON (((l.classoid = evt.tableoid) AND (l.objoid = evt.oid))))
1634 WHERE (l.objsubid = 0)
1635 UNION ALL
1636 SELECT l.objoid,
1637 l.classoid,
1638 l.objsubid,
1639 'publication'::text AS objtype,
1640 NULL::oid AS objnamespace,
1641 quote_ident((p.pubname)::text) AS objname,
1642 l.provider,
1643 l.label
1644 FROM (pg_seclabel l
1645 JOIN pg_publication p ON (((l.classoid = p.tableoid) AND (l.objoid = p.oid))))
1646 WHERE (l.objsubid = 0)
1647 UNION ALL
1648 SELECT l.objoid,
1649 l.classoid,
1650 0 AS objsubid,
1651 'subscription'::text AS objtype,
1652 NULL::oid AS objnamespace,
1653 quote_ident((s.subname)::text) AS objname,
1654 l.provider,
1655 l.label
1656 FROM (pg_shseclabel l
1657 JOIN pg_subscription s ON (((l.classoid = s.tableoid) AND (l.objoid = s.oid))))
1658 UNION ALL
1659 SELECT l.objoid,
1660 l.classoid,
1661 0 AS objsubid,
1662 'database'::text AS objtype,
1663 NULL::oid AS objnamespace,
1664 quote_ident((dat.datname)::text) AS objname,
1665 l.provider,
1666 l.label
1667 FROM (pg_shseclabel l
1668 JOIN pg_database dat ON (((l.classoid = dat.tableoid) AND (l.objoid = dat.oid))))
1669 UNION ALL
1670 SELECT l.objoid,
1671 l.classoid,
1672 0 AS objsubid,
1673 'tablespace'::text AS objtype,
1674 NULL::oid AS objnamespace,
1675 quote_ident((spc.spcname)::text) AS objname,
1676 l.provider,
1677 l.label
1678 FROM (pg_shseclabel l
1679 JOIN pg_tablespace spc ON (((l.classoid = spc.tableoid) AND (l.objoid = spc.oid))))
1680 UNION ALL
1681 SELECT l.objoid,
1682 l.classoid,
1683 0 AS objsubid,
1684 'role'::text AS objtype,
1685 NULL::oid AS objnamespace,
1686 quote_ident((rol.rolname)::text) AS objname,
1687 l.provider,
1688 l.label
1689 FROM (pg_shseclabel l
1690 JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid))));
1691 pg_sequences| SELECT n.nspname AS schemaname,
1692 c.relname AS sequencename,
1693 pg_get_userbyid(c.relowner) AS sequenceowner,
1694 (s.seqtypid)::regtype AS data_type,
1695 s.seqstart AS start_value,
1696 s.seqmin AS min_value,
1697 s.seqmax AS max_value,
1698 s.seqincrement AS increment_by,
1699 s.seqcycle AS cycle,
1700 s.seqcache AS cache_size,
1701 CASE
1702 WHEN has_sequence_privilege(c.oid, 'SELECT,USAGE'::text) THEN pg_sequence_last_value((c.oid)::regclass)
1703 ELSE NULL::bigint
1704 END AS last_value
1705 FROM ((pg_sequence s
1706 JOIN pg_class c ON ((c.oid = s.seqrelid)))
1707 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
1708 WHERE ((NOT pg_is_other_temp_schema(n.oid)) AND (c.relkind = 'S'::"char"));
1709 pg_settings| SELECT name,
1710 setting,
1711 unit,
1712 category,
1713 short_desc,
1714 extra_desc,
1715 context,
1716 vartype,
1717 source,
1718 min_val,
1719 max_val,
1720 enumvals,
1721 boot_val,
1722 reset_val,
1723 sourcefile,
1724 sourceline,
1725 pending_restart
1726 FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline, pending_restart);
1727 pg_shadow| SELECT pg_authid.rolname AS usename,
1728 pg_authid.oid AS usesysid,
1729 pg_authid.rolcreatedb AS usecreatedb,
1730 pg_authid.rolsuper AS usesuper,
1731 pg_authid.rolreplication AS userepl,
1732 pg_authid.rolbypassrls AS usebypassrls,
1733 pg_authid.rolpassword AS passwd,
1734 pg_authid.rolvaliduntil AS valuntil,
1735 s.setconfig AS useconfig
1736 FROM (pg_authid
1737 LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))))
1738 WHERE pg_authid.rolcanlogin;
1739 pg_shmem_allocations| SELECT name,
1740 off,
1741 size,
1742 allocated_size
1743 FROM pg_get_shmem_allocations() pg_get_shmem_allocations(name, off, size, allocated_size);
1744 pg_stat_activity| SELECT s.datid,
1745 d.datname,
1746 s.pid,
1747 s.leader_pid,
1748 s.usesysid,
1749 u.rolname AS usename,
1750 s.application_name,
1751 s.client_addr,
1752 s.client_hostname,
1753 s.client_port,
1754 s.backend_start,
1755 s.xact_start,
1756 s.query_start,
1757 s.state_change,
1758 s.wait_event_type,
1759 s.wait_event,
1760 s.state,
1761 s.backend_xid,
1762 s.backend_xmin,
1763 s.query_id,
1764 s.query,
1765 s.backend_type
1766 FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, ssl_not_before, ssl_not_after, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id)
1767 LEFT JOIN pg_database d ON ((s.datid = d.oid)))
1768 LEFT JOIN pg_authid u ON ((s.usesysid = u.oid)));
1769 pg_stat_all_indexes| SELECT c.oid AS relid,
1770 i.oid AS indexrelid,
1771 n.nspname AS schemaname,
1772 c.relname,
1773 i.relname AS indexrelname,
1774 pg_stat_get_numscans(i.oid) AS idx_scan,
1775 pg_stat_get_lastscan(i.oid) AS last_idx_scan,
1776 pg_stat_get_tuples_returned(i.oid) AS idx_tup_read,
1777 pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch
1778 FROM (((pg_class c
1779 JOIN pg_index x ON ((c.oid = x.indrelid)))
1780 JOIN pg_class i ON ((i.oid = x.indexrelid)))
1781 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
1782 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
1783 pg_stat_all_tables| SELECT c.oid AS relid,
1784 n.nspname AS schemaname,
1785 c.relname,
1786 pg_stat_get_numscans(c.oid) AS seq_scan,
1787 pg_stat_get_lastscan(c.oid) AS last_seq_scan,
1788 pg_stat_get_tuples_returned(c.oid) AS seq_tup_read,
1789 (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan,
1790 max(pg_stat_get_lastscan(i.indexrelid)) AS last_idx_scan,
1791 ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch,
1792 pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins,
1793 pg_stat_get_tuples_updated(c.oid) AS n_tup_upd,
1794 pg_stat_get_tuples_deleted(c.oid) AS n_tup_del,
1795 pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd,
1796 pg_stat_get_tuples_newpage_updated(c.oid) AS n_tup_newpage_upd,
1797 pg_stat_get_live_tuples(c.oid) AS n_live_tup,
1798 pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
1799 pg_stat_get_mod_since_analyze(c.oid) AS n_mod_since_analyze,
1800 pg_stat_get_ins_since_vacuum(c.oid) AS n_ins_since_vacuum,
1801 pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum,
1802 pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum,
1803 pg_stat_get_last_analyze_time(c.oid) AS last_analyze,
1804 pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze,
1805 pg_stat_get_vacuum_count(c.oid) AS vacuum_count,
1806 pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count,
1807 pg_stat_get_analyze_count(c.oid) AS analyze_count,
1808 pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count
1809 FROM ((pg_class c
1810 LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
1811 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
1812 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char", 'p'::"char"]))
1813 GROUP BY c.oid, n.nspname, c.relname;
1814 pg_stat_archiver| SELECT archived_count,
1815 last_archived_wal,
1816 last_archived_time,
1817 failed_count,
1818 last_failed_wal,
1819 last_failed_time,
1820 stats_reset
1821 FROM pg_stat_get_archiver() s(archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time, stats_reset);
1822 pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
1823 pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
1824 pg_stat_get_buf_alloc() AS buffers_alloc,
1825 pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
1826 pg_stat_checkpointer| SELECT pg_stat_get_checkpointer_num_timed() AS num_timed,
1827 pg_stat_get_checkpointer_num_requested() AS num_requested,
1828 pg_stat_get_checkpointer_restartpoints_timed() AS restartpoints_timed,
1829 pg_stat_get_checkpointer_restartpoints_requested() AS restartpoints_req,
1830 pg_stat_get_checkpointer_restartpoints_performed() AS restartpoints_done,
1831 pg_stat_get_checkpointer_write_time() AS write_time,
1832 pg_stat_get_checkpointer_sync_time() AS sync_time,
1833 pg_stat_get_checkpointer_buffers_written() AS buffers_written,
1834 pg_stat_get_checkpointer_stat_reset_time() AS stats_reset;
1835 pg_stat_database| SELECT oid AS datid,
1836 datname,
1837 CASE
1838 WHEN (oid = (0)::oid) THEN 0
1839 ELSE pg_stat_get_db_numbackends(oid)
1840 END AS numbackends,
1841 pg_stat_get_db_xact_commit(oid) AS xact_commit,
1842 pg_stat_get_db_xact_rollback(oid) AS xact_rollback,
1843 (pg_stat_get_db_blocks_fetched(oid) - pg_stat_get_db_blocks_hit(oid)) AS blks_read,
1844 pg_stat_get_db_blocks_hit(oid) AS blks_hit,
1845 pg_stat_get_db_tuples_returned(oid) AS tup_returned,
1846 pg_stat_get_db_tuples_fetched(oid) AS tup_fetched,
1847 pg_stat_get_db_tuples_inserted(oid) AS tup_inserted,
1848 pg_stat_get_db_tuples_updated(oid) AS tup_updated,
1849 pg_stat_get_db_tuples_deleted(oid) AS tup_deleted,
1850 pg_stat_get_db_conflict_all(oid) AS conflicts,
1851 pg_stat_get_db_temp_files(oid) AS temp_files,
1852 pg_stat_get_db_temp_bytes(oid) AS temp_bytes,
1853 pg_stat_get_db_deadlocks(oid) AS deadlocks,
1854 pg_stat_get_db_checksum_failures(oid) AS checksum_failures,
1855 pg_stat_get_db_checksum_last_failure(oid) AS checksum_last_failure,
1856 pg_stat_get_db_blk_read_time(oid) AS blk_read_time,
1857 pg_stat_get_db_blk_write_time(oid) AS blk_write_time,
1858 pg_stat_get_db_session_time(oid) AS session_time,
1859 pg_stat_get_db_active_time(oid) AS active_time,
1860 pg_stat_get_db_idle_in_transaction_time(oid) AS idle_in_transaction_time,
1861 pg_stat_get_db_sessions(oid) AS sessions,
1862 pg_stat_get_db_sessions_abandoned(oid) AS sessions_abandoned,
1863 pg_stat_get_db_sessions_fatal(oid) AS sessions_fatal,
1864 pg_stat_get_db_sessions_killed(oid) AS sessions_killed,
1865 pg_stat_get_db_stat_reset_time(oid) AS stats_reset
1866 FROM ( SELECT 0 AS oid,
1867 NULL::name AS datname
1868 UNION ALL
1869 SELECT pg_database.oid,
1870 pg_database.datname
1871 FROM pg_database) d;
1872 pg_stat_database_conflicts| SELECT oid AS datid,
1873 datname,
1874 pg_stat_get_db_conflict_tablespace(oid) AS confl_tablespace,
1875 pg_stat_get_db_conflict_lock(oid) AS confl_lock,
1876 pg_stat_get_db_conflict_snapshot(oid) AS confl_snapshot,
1877 pg_stat_get_db_conflict_bufferpin(oid) AS confl_bufferpin,
1878 pg_stat_get_db_conflict_startup_deadlock(oid) AS confl_deadlock,
1879 pg_stat_get_db_conflict_logicalslot(oid) AS confl_active_logicalslot
1880 FROM pg_database d;
1881 pg_stat_gssapi| SELECT pid,
1882 gss_auth AS gss_authenticated,
1883 gss_princ AS principal,
1884 gss_enc AS encrypted,
1885 gss_delegation AS credentials_delegated
1886 FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, ssl_not_before, ssl_not_after, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id)
1887 WHERE (client_port IS NOT NULL);
1888 pg_stat_io| SELECT backend_type,
1889 object,
1890 context,
1891 reads,
1892 read_time,
1893 writes,
1894 write_time,
1895 writebacks,
1896 writeback_time,
1897 extends,
1898 extend_time,
1899 op_bytes,
1900 hits,
1901 evictions,
1902 reuses,
1903 fsyncs,
1904 fsync_time,
1905 stats_reset
1906 FROM pg_stat_get_io() b(backend_type, object, context, reads, read_time, writes, write_time, writebacks, writeback_time, extends, extend_time, op_bytes, hits, evictions, reuses, fsyncs, fsync_time, stats_reset);
1907 pg_stat_progress_analyze| SELECT s.pid,
1908 s.datid,
1909 d.datname,
1910 s.relid,
1911 CASE s.param1
1912 WHEN 0 THEN 'initializing'::text
1913 WHEN 1 THEN 'acquiring sample rows'::text
1914 WHEN 2 THEN 'acquiring inherited sample rows'::text
1915 WHEN 3 THEN 'computing statistics'::text
1916 WHEN 4 THEN 'computing extended statistics'::text
1917 WHEN 5 THEN 'finalizing analyze'::text
1918 ELSE NULL::text
1919 END AS phase,
1920 s.param2 AS sample_blks_total,
1921 s.param3 AS sample_blks_scanned,
1922 s.param4 AS ext_stats_total,
1923 s.param5 AS ext_stats_computed,
1924 s.param6 AS child_tables_total,
1925 s.param7 AS child_tables_done,
1926 (s.param8)::oid AS current_child_table_relid
1927 FROM (pg_stat_get_progress_info('ANALYZE'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20)
1928 LEFT JOIN pg_database d ON ((s.datid = d.oid)));
1929 pg_stat_progress_basebackup| SELECT pid,
1930 CASE param1
1931 WHEN 0 THEN 'initializing'::text
1932 WHEN 1 THEN 'waiting for checkpoint to finish'::text
1933 WHEN 2 THEN 'estimating backup size'::text
1934 WHEN 3 THEN 'streaming database files'::text
1935 WHEN 4 THEN 'waiting for wal archiving to finish'::text
1936 WHEN 5 THEN 'transferring wal files'::text
1937 ELSE NULL::text
1938 END AS phase,
1939 CASE param2
1940 WHEN '-1'::integer THEN NULL::bigint
1941 ELSE param2
1942 END AS backup_total,
1943 param3 AS backup_streamed,
1944 param4 AS tablespaces_total,
1945 param5 AS tablespaces_streamed
1946 FROM pg_stat_get_progress_info('BASEBACKUP'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20);
1947 pg_stat_progress_cluster| SELECT s.pid,
1948 s.datid,
1949 d.datname,
1950 s.relid,
1951 CASE s.param1
1952 WHEN 1 THEN 'CLUSTER'::text
1953 WHEN 2 THEN 'VACUUM FULL'::text
1954 ELSE NULL::text
1955 END AS command,
1956 CASE s.param2
1957 WHEN 0 THEN 'initializing'::text
1958 WHEN 1 THEN 'seq scanning heap'::text
1959 WHEN 2 THEN 'index scanning heap'::text
1960 WHEN 3 THEN 'sorting tuples'::text
1961 WHEN 4 THEN 'writing new heap'::text
1962 WHEN 5 THEN 'swapping relation files'::text
1963 WHEN 6 THEN 'rebuilding index'::text
1964 WHEN 7 THEN 'performing final cleanup'::text
1965 ELSE NULL::text
1966 END AS phase,
1967 (s.param3)::oid AS cluster_index_relid,
1968 s.param4 AS heap_tuples_scanned,
1969 s.param5 AS heap_tuples_written,
1970 s.param6 AS heap_blks_total,
1971 s.param7 AS heap_blks_scanned,
1972 s.param8 AS index_rebuild_count
1973 FROM (pg_stat_get_progress_info('CLUSTER'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20)
1974 LEFT JOIN pg_database d ON ((s.datid = d.oid)));
1975 pg_stat_progress_copy| SELECT s.pid,
1976 s.datid,
1977 d.datname,
1978 s.relid,
1979 CASE s.param5
1980 WHEN 1 THEN 'COPY FROM'::text
1981 WHEN 2 THEN 'COPY TO'::text
1982 ELSE NULL::text
1983 END AS command,
1984 CASE s.param6
1985 WHEN 1 THEN 'FILE'::text
1986 WHEN 2 THEN 'PROGRAM'::text
1987 WHEN 3 THEN 'PIPE'::text
1988 WHEN 4 THEN 'CALLBACK'::text
1989 ELSE NULL::text
1990 END AS type,
1991 s.param1 AS bytes_processed,
1992 s.param2 AS bytes_total,
1993 s.param3 AS tuples_processed,
1994 s.param4 AS tuples_excluded,
1995 s.param7 AS tuples_skipped
1996 FROM (pg_stat_get_progress_info('COPY'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20)
1997 LEFT JOIN pg_database d ON ((s.datid = d.oid)));
1998 pg_stat_progress_create_index| SELECT s.pid,
1999 s.datid,
2000 d.datname,
2001 s.relid,
2002 (s.param7)::oid AS index_relid,
2003 CASE s.param1
2004 WHEN 1 THEN 'CREATE INDEX'::text
2005 WHEN 2 THEN 'CREATE INDEX CONCURRENTLY'::text
2006 WHEN 3 THEN 'REINDEX'::text
2007 WHEN 4 THEN 'REINDEX CONCURRENTLY'::text
2008 ELSE NULL::text
2009 END AS command,
2010 CASE s.param10
2011 WHEN 0 THEN 'initializing'::text
2012 WHEN 1 THEN 'waiting for writers before build'::text
2013 WHEN 2 THEN ('building index'::text || COALESCE((': '::text || pg_indexam_progress_phasename((s.param9)::oid, s.param11)), ''::text))
2014 WHEN 3 THEN 'waiting for writers before validation'::text
2015 WHEN 4 THEN 'index validation: scanning index'::text
2016 WHEN 5 THEN 'index validation: sorting tuples'::text
2017 WHEN 6 THEN 'index validation: scanning table'::text
2018 WHEN 7 THEN 'waiting for old snapshots'::text
2019 WHEN 8 THEN 'waiting for readers before marking dead'::text
2020 WHEN 9 THEN 'waiting for readers before dropping'::text
2021 ELSE NULL::text
2022 END AS phase,
2023 s.param4 AS lockers_total,
2024 s.param5 AS lockers_done,
2025 s.param6 AS current_locker_pid,
2026 s.param16 AS blocks_total,
2027 s.param17 AS blocks_done,
2028 s.param12 AS tuples_total,
2029 s.param13 AS tuples_done,
2030 s.param14 AS partitions_total,
2031 s.param15 AS partitions_done
2032 FROM (pg_stat_get_progress_info('CREATE INDEX'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20)
2033 LEFT JOIN pg_database d ON ((s.datid = d.oid)));
2034 pg_stat_progress_vacuum| SELECT s.pid,
2035 s.datid,
2036 d.datname,
2037 s.relid,
2038 CASE s.param1
2039 WHEN 0 THEN 'initializing'::text
2040 WHEN 1 THEN 'scanning heap'::text
2041 WHEN 2 THEN 'vacuuming indexes'::text
2042 WHEN 3 THEN 'vacuuming heap'::text
2043 WHEN 4 THEN 'cleaning up indexes'::text
2044 WHEN 5 THEN 'truncating heap'::text
2045 WHEN 6 THEN 'performing final cleanup'::text
2046 ELSE NULL::text
2047 END AS phase,
2048 s.param2 AS heap_blks_total,
2049 s.param3 AS heap_blks_scanned,
2050 s.param4 AS heap_blks_vacuumed,
2051 s.param5 AS index_vacuum_count,
2052 s.param6 AS max_dead_tuples,
2053 s.param7 AS num_dead_tuples,
2054 s.param8 AS indexes_total,
2055 s.param9 AS indexes_processed
2056 FROM (pg_stat_get_progress_info('VACUUM'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20)
2057 LEFT JOIN pg_database d ON ((s.datid = d.oid)));
2058 pg_stat_recovery_prefetch| SELECT stats_reset,
2059 prefetch,
2060 hit,
2061 skip_init,
2062 skip_new,
2063 skip_fpw,
2064 skip_rep,
2065 wal_distance,
2066 block_distance,
2067 io_depth
2068 FROM pg_stat_get_recovery_prefetch() s(stats_reset, prefetch, hit, skip_init, skip_new, skip_fpw, skip_rep, wal_distance, block_distance, io_depth);
2069 pg_stat_replication| SELECT s.pid,
2070 s.usesysid,
2071 u.rolname AS usename,
2072 s.application_name,
2073 s.client_addr,
2074 s.client_hostname,
2075 s.client_port,
2076 s.backend_start,
2077 s.backend_xmin,
2078 w.state,
2079 w.sent_lsn,
2080 w.write_lsn,
2081 w.flush_lsn,
2082 w.replay_lsn,
2083 w.write_lag,
2084 w.flush_lag,
2085 w.replay_lag,
2086 w.sync_priority,
2087 w.sync_state,
2088 w.reply_time
2089 FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, ssl_not_before, ssl_not_after, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id)
2090 JOIN pg_stat_get_wal_senders() w(pid, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag, sync_priority, sync_state, reply_time) ON ((s.pid = w.pid)))
2091 LEFT JOIN pg_authid u ON ((s.usesysid = u.oid)));
2092 pg_stat_replication_slots| SELECT s.slot_name,
2093 s.spill_txns,
2094 s.spill_count,
2095 s.spill_bytes,
2096 s.stream_txns,
2097 s.stream_count,
2098 s.stream_bytes,
2099 s.total_txns,
2100 s.total_bytes,
2101 s.stats_reset
2102 FROM pg_replication_slots r,
2103 LATERAL pg_stat_get_replication_slot((r.slot_name)::text) s(slot_name, spill_txns, spill_count, spill_bytes, stream_txns, stream_count, stream_bytes, total_txns, total_bytes, stats_reset)
2104 WHERE (r.datoid IS NOT NULL);
2105 pg_stat_slru| SELECT name,
2106 blks_zeroed,
2107 blks_hit,
2108 blks_read,
2109 blks_written,
2110 blks_exists,
2111 flushes,
2112 truncates,
2113 stats_reset
2114 FROM pg_stat_get_slru() s(name, blks_zeroed, blks_hit, blks_read, blks_written, blks_exists, flushes, truncates, stats_reset);
2115 pg_stat_ssl| SELECT pid,
2116 ssl,
2117 sslversion AS version,
2118 sslcipher AS cipher,
2119 sslbits AS bits,
2120 ssl_client_dn AS client_dn,
2121 ssl_client_serial AS client_serial,
2122 ssl_issuer_dn AS issuer_dn,
2123 ssl_not_before AS not_before,
2124 ssl_not_after AS not_after
2125 FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, ssl_not_before, ssl_not_after, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id)
2126 WHERE (client_port IS NOT NULL);
2127 pg_stat_subscription| SELECT su.oid AS subid,
2128 su.subname,
2129 st.worker_type,
2130 st.pid,
2131 st.leader_pid,
2132 st.relid,
2133 st.received_lsn,
2134 st.last_msg_send_time,
2135 st.last_msg_receipt_time,
2136 st.latest_end_lsn,
2137 st.latest_end_time
2138 FROM (pg_subscription su
2139 LEFT JOIN pg_stat_get_subscription(NULL::oid) st(subid, relid, pid, leader_pid, received_lsn, last_msg_send_time, last_msg_receipt_time, latest_end_lsn, latest_end_time, worker_type) ON ((st.subid = su.oid)));
2140 pg_stat_subscription_stats| SELECT ss.subid,
2141 s.subname,
2142 ss.apply_error_count,
2143 ss.sync_error_count,
2144 ss.stats_reset
2145 FROM pg_subscription s,
2146 LATERAL pg_stat_get_subscription_stats(s.oid) ss(subid, apply_error_count, sync_error_count, stats_reset);
2147 pg_stat_sys_indexes| SELECT relid,
2148 indexrelid,
2149 schemaname,
2150 relname,
2151 indexrelname,
2152 idx_scan,
2153 last_idx_scan,
2154 idx_tup_read,
2155 idx_tup_fetch
2156 FROM pg_stat_all_indexes
2157 WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
2158 pg_stat_sys_tables| SELECT relid,
2159 schemaname,
2160 relname,
2161 seq_scan,
2162 last_seq_scan,
2163 seq_tup_read,
2164 idx_scan,
2165 last_idx_scan,
2166 idx_tup_fetch,
2167 n_tup_ins,
2168 n_tup_upd,
2169 n_tup_del,
2170 n_tup_hot_upd,
2171 n_tup_newpage_upd,
2172 n_live_tup,
2173 n_dead_tup,
2174 n_mod_since_analyze,
2175 n_ins_since_vacuum,
2176 last_vacuum,
2177 last_autovacuum,
2178 last_analyze,
2179 last_autoanalyze,
2180 vacuum_count,
2181 autovacuum_count,
2182 analyze_count,
2183 autoanalyze_count
2184 FROM pg_stat_all_tables
2185 WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
2186 pg_stat_user_functions| SELECT p.oid AS funcid,
2187 n.nspname AS schemaname,
2188 p.proname AS funcname,
2189 pg_stat_get_function_calls(p.oid) AS calls,
2190 pg_stat_get_function_total_time(p.oid) AS total_time,
2191 pg_stat_get_function_self_time(p.oid) AS self_time
2192 FROM (pg_proc p
2193 LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace)))
2194 WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL));
2195 pg_stat_user_indexes| SELECT relid,
2196 indexrelid,
2197 schemaname,
2198 relname,
2199 indexrelname,
2200 idx_scan,
2201 last_idx_scan,
2202 idx_tup_read,
2203 idx_tup_fetch
2204 FROM pg_stat_all_indexes
2205 WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
2206 pg_stat_user_tables| SELECT relid,
2207 schemaname,
2208 relname,
2209 seq_scan,
2210 last_seq_scan,
2211 seq_tup_read,
2212 idx_scan,
2213 last_idx_scan,
2214 idx_tup_fetch,
2215 n_tup_ins,
2216 n_tup_upd,
2217 n_tup_del,
2218 n_tup_hot_upd,
2219 n_tup_newpage_upd,
2220 n_live_tup,
2221 n_dead_tup,
2222 n_mod_since_analyze,
2223 n_ins_since_vacuum,
2224 last_vacuum,
2225 last_autovacuum,
2226 last_analyze,
2227 last_autoanalyze,
2228 vacuum_count,
2229 autovacuum_count,
2230 analyze_count,
2231 autoanalyze_count
2232 FROM pg_stat_all_tables
2233 WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
2234 pg_stat_wal| SELECT wal_records,
2235 wal_fpi,
2236 wal_bytes,
2237 wal_buffers_full,
2238 wal_write,
2239 wal_sync,
2240 wal_write_time,
2241 wal_sync_time,
2242 stats_reset
2243 FROM pg_stat_get_wal() w(wal_records, wal_fpi, wal_bytes, wal_buffers_full, wal_write, wal_sync, wal_write_time, wal_sync_time, stats_reset);
2244 pg_stat_wal_receiver| SELECT pid,
2245 status,
2246 receive_start_lsn,
2247 receive_start_tli,
2248 written_lsn,
2249 flushed_lsn,
2250 received_tli,
2251 last_msg_send_time,
2252 last_msg_receipt_time,
2253 latest_end_lsn,
2254 latest_end_time,
2255 slot_name,
2256 sender_host,
2257 sender_port,
2258 conninfo
2259 FROM pg_stat_get_wal_receiver() s(pid, status, receive_start_lsn, receive_start_tli, written_lsn, flushed_lsn, received_tli, last_msg_send_time, last_msg_receipt_time, latest_end_lsn, latest_end_time, slot_name, sender_host, sender_port, conninfo)
2260 WHERE (pid IS NOT NULL);
2261 pg_stat_xact_all_tables| SELECT c.oid AS relid,
2262 n.nspname AS schemaname,
2263 c.relname,
2264 pg_stat_get_xact_numscans(c.oid) AS seq_scan,
2265 pg_stat_get_xact_tuples_returned(c.oid) AS seq_tup_read,
2266 (sum(pg_stat_get_xact_numscans(i.indexrelid)))::bigint AS idx_scan,
2267 ((sum(pg_stat_get_xact_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_xact_tuples_fetched(c.oid)) AS idx_tup_fetch,
2268 pg_stat_get_xact_tuples_inserted(c.oid) AS n_tup_ins,
2269 pg_stat_get_xact_tuples_updated(c.oid) AS n_tup_upd,
2270 pg_stat_get_xact_tuples_deleted(c.oid) AS n_tup_del,
2271 pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd,
2272 pg_stat_get_xact_tuples_newpage_updated(c.oid) AS n_tup_newpage_upd
2273 FROM ((pg_class c
2274 LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
2275 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
2276 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char", 'p'::"char"]))
2277 GROUP BY c.oid, n.nspname, c.relname;
2278 pg_stat_xact_sys_tables| SELECT relid,
2279 schemaname,
2280 relname,
2281 seq_scan,
2282 seq_tup_read,
2283 idx_scan,
2284 idx_tup_fetch,
2285 n_tup_ins,
2286 n_tup_upd,
2287 n_tup_del,
2288 n_tup_hot_upd,
2289 n_tup_newpage_upd
2290 FROM pg_stat_xact_all_tables
2291 WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
2292 pg_stat_xact_user_functions| SELECT p.oid AS funcid,
2293 n.nspname AS schemaname,
2294 p.proname AS funcname,
2295 pg_stat_get_xact_function_calls(p.oid) AS calls,
2296 pg_stat_get_xact_function_total_time(p.oid) AS total_time,
2297 pg_stat_get_xact_function_self_time(p.oid) AS self_time
2298 FROM (pg_proc p
2299 LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace)))
2300 WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_xact_function_calls(p.oid) IS NOT NULL));
2301 pg_stat_xact_user_tables| SELECT relid,
2302 schemaname,
2303 relname,
2304 seq_scan,
2305 seq_tup_read,
2306 idx_scan,
2307 idx_tup_fetch,
2308 n_tup_ins,
2309 n_tup_upd,
2310 n_tup_del,
2311 n_tup_hot_upd,
2312 n_tup_newpage_upd
2313 FROM pg_stat_xact_all_tables
2314 WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
2315 pg_statio_all_indexes| SELECT c.oid AS relid,
2316 i.oid AS indexrelid,
2317 n.nspname AS schemaname,
2318 c.relname,
2319 i.relname AS indexrelname,
2320 (pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read,
2321 pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit
2322 FROM (((pg_class c
2323 JOIN pg_index x ON ((c.oid = x.indrelid)))
2324 JOIN pg_class i ON ((i.oid = x.indexrelid)))
2325 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
2326 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
2327 pg_statio_all_sequences| SELECT c.oid AS relid,
2328 n.nspname AS schemaname,
2329 c.relname,
2330 (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read,
2331 pg_stat_get_blocks_hit(c.oid) AS blks_hit
2332 FROM (pg_class c
2333 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
2334 WHERE (c.relkind = 'S'::"char");
2335 pg_statio_all_tables| SELECT c.oid AS relid,
2336 n.nspname AS schemaname,
2337 c.relname,
2338 (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS heap_blks_read,
2339 pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit,
2340 i.idx_blks_read,
2341 i.idx_blks_hit,
2342 (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read,
2343 pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit,
2344 x.idx_blks_read AS tidx_blks_read,
2345 x.idx_blks_hit AS tidx_blks_hit
2346 FROM ((((pg_class c
2347 LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid)))
2348 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
2349 LEFT JOIN LATERAL ( SELECT (sum((pg_stat_get_blocks_fetched(pg_index.indexrelid) - pg_stat_get_blocks_hit(pg_index.indexrelid))))::bigint AS idx_blks_read,
2350 (sum(pg_stat_get_blocks_hit(pg_index.indexrelid)))::bigint AS idx_blks_hit
2351 FROM pg_index
2352 WHERE (pg_index.indrelid = c.oid)) i ON (true))
2353 LEFT JOIN LATERAL ( SELECT (sum((pg_stat_get_blocks_fetched(pg_index.indexrelid) - pg_stat_get_blocks_hit(pg_index.indexrelid))))::bigint AS idx_blks_read,
2354 (sum(pg_stat_get_blocks_hit(pg_index.indexrelid)))::bigint AS idx_blks_hit
2355 FROM pg_index
2356 WHERE (pg_index.indrelid = t.oid)) x ON (true))
2357 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
2358 pg_statio_sys_indexes| SELECT relid,
2359 indexrelid,
2360 schemaname,
2361 relname,
2362 indexrelname,
2363 idx_blks_read,
2364 idx_blks_hit
2365 FROM pg_statio_all_indexes
2366 WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
2367 pg_statio_sys_sequences| SELECT relid,
2368 schemaname,
2369 relname,
2370 blks_read,
2371 blks_hit
2372 FROM pg_statio_all_sequences
2373 WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
2374 pg_statio_sys_tables| SELECT relid,
2375 schemaname,
2376 relname,
2377 heap_blks_read,
2378 heap_blks_hit,
2379 idx_blks_read,
2380 idx_blks_hit,
2381 toast_blks_read,
2382 toast_blks_hit,
2383 tidx_blks_read,
2384 tidx_blks_hit
2385 FROM pg_statio_all_tables
2386 WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
2387 pg_statio_user_indexes| SELECT relid,
2388 indexrelid,
2389 schemaname,
2390 relname,
2391 indexrelname,
2392 idx_blks_read,
2393 idx_blks_hit
2394 FROM pg_statio_all_indexes
2395 WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
2396 pg_statio_user_sequences| SELECT relid,
2397 schemaname,
2398 relname,
2399 blks_read,
2400 blks_hit
2401 FROM pg_statio_all_sequences
2402 WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
2403 pg_statio_user_tables| SELECT relid,
2404 schemaname,
2405 relname,
2406 heap_blks_read,
2407 heap_blks_hit,
2408 idx_blks_read,
2409 idx_blks_hit,
2410 toast_blks_read,
2411 toast_blks_hit,
2412 tidx_blks_read,
2413 tidx_blks_hit
2414 FROM pg_statio_all_tables
2415 WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
2416 pg_stats| SELECT n.nspname AS schemaname,
2417 c.relname AS tablename,
2418 a.attname,
2419 s.stainherit AS inherited,
2420 s.stanullfrac AS null_frac,
2421 s.stawidth AS avg_width,
2422 s.stadistinct AS n_distinct,
2423 CASE
2424 WHEN (s.stakind1 = 1) THEN s.stavalues1
2425 WHEN (s.stakind2 = 1) THEN s.stavalues2
2426 WHEN (s.stakind3 = 1) THEN s.stavalues3
2427 WHEN (s.stakind4 = 1) THEN s.stavalues4
2428 WHEN (s.stakind5 = 1) THEN s.stavalues5
2429 ELSE NULL::anyarray
2430 END AS most_common_vals,
2431 CASE
2432 WHEN (s.stakind1 = 1) THEN s.stanumbers1
2433 WHEN (s.stakind2 = 1) THEN s.stanumbers2
2434 WHEN (s.stakind3 = 1) THEN s.stanumbers3
2435 WHEN (s.stakind4 = 1) THEN s.stanumbers4
2436 WHEN (s.stakind5 = 1) THEN s.stanumbers5
2437 ELSE NULL::real[]
2438 END AS most_common_freqs,
2439 CASE
2440 WHEN (s.stakind1 = 2) THEN s.stavalues1
2441 WHEN (s.stakind2 = 2) THEN s.stavalues2
2442 WHEN (s.stakind3 = 2) THEN s.stavalues3
2443 WHEN (s.stakind4 = 2) THEN s.stavalues4
2444 WHEN (s.stakind5 = 2) THEN s.stavalues5
2445 ELSE NULL::anyarray
2446 END AS histogram_bounds,
2447 CASE
2448 WHEN (s.stakind1 = 3) THEN s.stanumbers1[1]
2449 WHEN (s.stakind2 = 3) THEN s.stanumbers2[1]
2450 WHEN (s.stakind3 = 3) THEN s.stanumbers3[1]
2451 WHEN (s.stakind4 = 3) THEN s.stanumbers4[1]
2452 WHEN (s.stakind5 = 3) THEN s.stanumbers5[1]
2453 ELSE NULL::real
2454 END AS correlation,
2455 CASE
2456 WHEN (s.stakind1 = 4) THEN s.stavalues1
2457 WHEN (s.stakind2 = 4) THEN s.stavalues2
2458 WHEN (s.stakind3 = 4) THEN s.stavalues3
2459 WHEN (s.stakind4 = 4) THEN s.stavalues4
2460 WHEN (s.stakind5 = 4) THEN s.stavalues5
2461 ELSE NULL::anyarray
2462 END AS most_common_elems,
2463 CASE
2464 WHEN (s.stakind1 = 4) THEN s.stanumbers1
2465 WHEN (s.stakind2 = 4) THEN s.stanumbers2
2466 WHEN (s.stakind3 = 4) THEN s.stanumbers3
2467 WHEN (s.stakind4 = 4) THEN s.stanumbers4
2468 WHEN (s.stakind5 = 4) THEN s.stanumbers5
2469 ELSE NULL::real[]
2470 END AS most_common_elem_freqs,
2471 CASE
2472 WHEN (s.stakind1 = 5) THEN s.stanumbers1
2473 WHEN (s.stakind2 = 5) THEN s.stanumbers2
2474 WHEN (s.stakind3 = 5) THEN s.stanumbers3
2475 WHEN (s.stakind4 = 5) THEN s.stanumbers4
2476 WHEN (s.stakind5 = 5) THEN s.stanumbers5
2477 ELSE NULL::real[]
2478 END AS elem_count_histogram,
2479 CASE
2480 WHEN (s.stakind1 = 6) THEN s.stavalues1
2481 WHEN (s.stakind2 = 6) THEN s.stavalues2
2482 WHEN (s.stakind3 = 6) THEN s.stavalues3
2483 WHEN (s.stakind4 = 6) THEN s.stavalues4
2484 WHEN (s.stakind5 = 6) THEN s.stavalues5
2485 ELSE NULL::anyarray
2486 END AS range_length_histogram,
2487 CASE
2488 WHEN (s.stakind1 = 6) THEN s.stanumbers1[1]
2489 WHEN (s.stakind2 = 6) THEN s.stanumbers2[1]
2490 WHEN (s.stakind3 = 6) THEN s.stanumbers3[1]
2491 WHEN (s.stakind4 = 6) THEN s.stanumbers4[1]
2492 WHEN (s.stakind5 = 6) THEN s.stanumbers5[1]
2493 ELSE NULL::real
2494 END AS range_empty_frac,
2495 CASE
2496 WHEN (s.stakind1 = 7) THEN s.stavalues1
2497 WHEN (s.stakind2 = 7) THEN s.stavalues2
2498 WHEN (s.stakind3 = 7) THEN s.stavalues3
2499 WHEN (s.stakind4 = 7) THEN s.stavalues4
2500 WHEN (s.stakind5 = 7) THEN s.stavalues5
2501 ELSE NULL::anyarray
2502 END AS range_bounds_histogram
2503 FROM (((pg_statistic s
2504 JOIN pg_class c ON ((c.oid = s.starelid)))
2505 JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum))))
2506 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
2507 WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid))));
2508 pg_stats_ext| SELECT cn.nspname AS schemaname,
2509 c.relname AS tablename,
2510 sn.nspname AS statistics_schemaname,
2511 s.stxname AS statistics_name,
2512 pg_get_userbyid(s.stxowner) AS statistics_owner,
2513 ( SELECT array_agg(a.attname ORDER BY a.attnum) AS array_agg
2514 FROM (unnest(s.stxkeys) k(k)
2515 JOIN pg_attribute a ON (((a.attrelid = s.stxrelid) AND (a.attnum = k.k))))) AS attnames,
2516 pg_get_statisticsobjdef_expressions(s.oid) AS exprs,
2517 s.stxkind AS kinds,
2518 sd.stxdinherit AS inherited,
2519 sd.stxdndistinct AS n_distinct,
2520 sd.stxddependencies AS dependencies,
2521 m.most_common_vals,
2522 m.most_common_val_nulls,
2523 m.most_common_freqs,
2524 m.most_common_base_freqs
2525 FROM (((((pg_statistic_ext s
2526 JOIN pg_class c ON ((c.oid = s.stxrelid)))
2527 JOIN pg_statistic_ext_data sd ON ((s.oid = sd.stxoid)))
2528 LEFT JOIN pg_namespace cn ON ((cn.oid = c.relnamespace)))
2529 LEFT JOIN pg_namespace sn ON ((sn.oid = s.stxnamespace)))
2530 LEFT JOIN LATERAL ( SELECT array_agg(pg_mcv_list_items."values") AS most_common_vals,
2531 array_agg(pg_mcv_list_items.nulls) AS most_common_val_nulls,
2532 array_agg(pg_mcv_list_items.frequency) AS most_common_freqs,
2533 array_agg(pg_mcv_list_items.base_frequency) AS most_common_base_freqs
2534 FROM pg_mcv_list_items(sd.stxdmcv) pg_mcv_list_items(index, "values", nulls, frequency, base_frequency)) m ON ((sd.stxdmcv IS NOT NULL)))
2535 WHERE ((NOT (EXISTS ( SELECT 1
2536 FROM (unnest(s.stxkeys) k(k)
2537 JOIN pg_attribute a ON (((a.attrelid = s.stxrelid) AND (a.attnum = k.k))))
2538 WHERE (NOT has_column_privilege(c.oid, a.attnum, 'select'::text))))) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid))));
2539 pg_stats_ext_exprs| SELECT cn.nspname AS schemaname,
2540 c.relname AS tablename,
2541 sn.nspname AS statistics_schemaname,
2542 s.stxname AS statistics_name,
2543 pg_get_userbyid(s.stxowner) AS statistics_owner,
2544 stat.expr,
2545 sd.stxdinherit AS inherited,
2546 (stat.a).stanullfrac AS null_frac,
2547 (stat.a).stawidth AS avg_width,
2548 (stat.a).stadistinct AS n_distinct,
2549 CASE
2550 WHEN ((stat.a).stakind1 = 1) THEN (stat.a).stavalues1
2551 WHEN ((stat.a).stakind2 = 1) THEN (stat.a).stavalues2
2552 WHEN ((stat.a).stakind3 = 1) THEN (stat.a).stavalues3
2553 WHEN ((stat.a).stakind4 = 1) THEN (stat.a).stavalues4
2554 WHEN ((stat.a).stakind5 = 1) THEN (stat.a).stavalues5
2555 ELSE NULL::anyarray
2556 END AS most_common_vals,
2557 CASE
2558 WHEN ((stat.a).stakind1 = 1) THEN (stat.a).stanumbers1
2559 WHEN ((stat.a).stakind2 = 1) THEN (stat.a).stanumbers2
2560 WHEN ((stat.a).stakind3 = 1) THEN (stat.a).stanumbers3
2561 WHEN ((stat.a).stakind4 = 1) THEN (stat.a).stanumbers4
2562 WHEN ((stat.a).stakind5 = 1) THEN (stat.a).stanumbers5
2563 ELSE NULL::real[]
2564 END AS most_common_freqs,
2565 CASE
2566 WHEN ((stat.a).stakind1 = 2) THEN (stat.a).stavalues1
2567 WHEN ((stat.a).stakind2 = 2) THEN (stat.a).stavalues2
2568 WHEN ((stat.a).stakind3 = 2) THEN (stat.a).stavalues3
2569 WHEN ((stat.a).stakind4 = 2) THEN (stat.a).stavalues4
2570 WHEN ((stat.a).stakind5 = 2) THEN (stat.a).stavalues5
2571 ELSE NULL::anyarray
2572 END AS histogram_bounds,
2573 CASE
2574 WHEN ((stat.a).stakind1 = 3) THEN (stat.a).stanumbers1[1]
2575 WHEN ((stat.a).stakind2 = 3) THEN (stat.a).stanumbers2[1]
2576 WHEN ((stat.a).stakind3 = 3) THEN (stat.a).stanumbers3[1]
2577 WHEN ((stat.a).stakind4 = 3) THEN (stat.a).stanumbers4[1]
2578 WHEN ((stat.a).stakind5 = 3) THEN (stat.a).stanumbers5[1]
2579 ELSE NULL::real
2580 END AS correlation,
2581 CASE
2582 WHEN ((stat.a).stakind1 = 4) THEN (stat.a).stavalues1
2583 WHEN ((stat.a).stakind2 = 4) THEN (stat.a).stavalues2
2584 WHEN ((stat.a).stakind3 = 4) THEN (stat.a).stavalues3
2585 WHEN ((stat.a).stakind4 = 4) THEN (stat.a).stavalues4
2586 WHEN ((stat.a).stakind5 = 4) THEN (stat.a).stavalues5
2587 ELSE NULL::anyarray
2588 END AS most_common_elems,
2589 CASE
2590 WHEN ((stat.a).stakind1 = 4) THEN (stat.a).stanumbers1
2591 WHEN ((stat.a).stakind2 = 4) THEN (stat.a).stanumbers2
2592 WHEN ((stat.a).stakind3 = 4) THEN (stat.a).stanumbers3
2593 WHEN ((stat.a).stakind4 = 4) THEN (stat.a).stanumbers4
2594 WHEN ((stat.a).stakind5 = 4) THEN (stat.a).stanumbers5
2595 ELSE NULL::real[]
2596 END AS most_common_elem_freqs,
2597 CASE
2598 WHEN ((stat.a).stakind1 = 5) THEN (stat.a).stanumbers1
2599 WHEN ((stat.a).stakind2 = 5) THEN (stat.a).stanumbers2
2600 WHEN ((stat.a).stakind3 = 5) THEN (stat.a).stanumbers3
2601 WHEN ((stat.a).stakind4 = 5) THEN (stat.a).stanumbers4
2602 WHEN ((stat.a).stakind5 = 5) THEN (stat.a).stanumbers5
2603 ELSE NULL::real[]
2604 END AS elem_count_histogram
2605 FROM (((((pg_statistic_ext s
2606 JOIN pg_class c ON ((c.oid = s.stxrelid)))
2607 LEFT JOIN pg_statistic_ext_data sd ON ((s.oid = sd.stxoid)))
2608 LEFT JOIN pg_namespace cn ON ((cn.oid = c.relnamespace)))
2609 LEFT JOIN pg_namespace sn ON ((sn.oid = s.stxnamespace)))
2610 JOIN LATERAL ( SELECT unnest(pg_get_statisticsobjdef_expressions(s.oid)) AS expr,
2611 unnest(sd.stxdexpr) AS a) stat ON ((stat.expr IS NOT NULL)));
2612 pg_tables| SELECT n.nspname AS schemaname,
2613 c.relname AS tablename,
2614 pg_get_userbyid(c.relowner) AS tableowner,
2615 t.spcname AS tablespace,
2616 c.relhasindex AS hasindexes,
2617 c.relhasrules AS hasrules,
2618 c.relhastriggers AS hastriggers,
2619 c.relrowsecurity AS rowsecurity
2620 FROM ((pg_class c
2621 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
2622 LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace)))
2623 WHERE (c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"]));
2624 pg_timezone_abbrevs| SELECT abbrev,
2625 utc_offset,
2626 is_dst
2627 FROM pg_timezone_abbrevs() pg_timezone_abbrevs(abbrev, utc_offset, is_dst);
2628 pg_timezone_names| SELECT name,
2629 abbrev,
2630 utc_offset,
2631 is_dst
2632 FROM pg_timezone_names() pg_timezone_names(name, abbrev, utc_offset, is_dst);
2633 pg_user| SELECT usename,
2634 usesysid,
2635 usecreatedb,
2636 usesuper,
2637 userepl,
2638 usebypassrls,
2639 '********'::text AS passwd,
2640 valuntil,
2641 useconfig
2642 FROM pg_shadow;
2643 pg_user_mappings| SELECT u.oid AS umid,
2644 s.oid AS srvid,
2645 s.srvname,
2646 u.umuser,
2647 CASE
2648 WHEN (u.umuser = (0)::oid) THEN 'public'::name
2649 ELSE a.rolname
2650 END AS usename,
2651 CASE
2652 WHEN (((u.umuser <> (0)::oid) AND (a.rolname = CURRENT_USER) AND (pg_has_role(s.srvowner, 'USAGE'::text) OR has_server_privilege(s.oid, 'USAGE'::text))) OR ((u.umuser = (0)::oid) AND pg_has_role(s.srvowner, 'USAGE'::text)) OR ( SELECT pg_authid.rolsuper
2653 FROM pg_authid
2654 WHERE (pg_authid.rolname = CURRENT_USER))) THEN u.umoptions
2655 ELSE NULL::text[]
2656 END AS umoptions
2657 FROM ((pg_user_mapping u
2658 JOIN pg_foreign_server s ON ((u.umserver = s.oid)))
2659 LEFT JOIN pg_authid a ON ((a.oid = u.umuser)));
2660 pg_views| SELECT n.nspname AS schemaname,
2661 c.relname AS viewname,
2662 pg_get_userbyid(c.relowner) AS viewowner,
2663 pg_get_viewdef(c.oid) AS definition
2664 FROM (pg_class c
2665 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
2666 WHERE (c.relkind = 'v'::"char");
2667 pg_wait_events| SELECT type,
2668 name,
2669 description
2670 FROM pg_get_wait_events() pg_get_wait_events(type, name, description);
2671 SELECT tablename, rulename, definition FROM pg_rules
2672 WHERE schemaname = 'pg_catalog'
2673 ORDER BY tablename, rulename;
2674 pg_settings|pg_settings_n|CREATE RULE pg_settings_n AS
2675 ON UPDATE TO pg_catalog.pg_settings DO INSTEAD NOTHING;
2676 pg_settings|pg_settings_u|CREATE RULE pg_settings_u AS
2677 ON UPDATE TO pg_catalog.pg_settings
2678 WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, false) AS set_config;
2679 -- restore normal output mode
2680 \a\t
2681 --
2682 -- CREATE OR REPLACE RULE
2683 --
2684 CREATE TABLE ruletest_tbl (a int, b int);
2685 CREATE TABLE ruletest_tbl2 (a int, b int);
2686 CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl
2687 DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (10, 10);
2688 INSERT INTO ruletest_tbl VALUES (99, 99);
2689 CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl
2690 DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (1000, 1000);
2691 INSERT INTO ruletest_tbl VALUES (99, 99);
2692 SELECT * FROM ruletest_tbl2;
2693 a | b
2694 ------+------
2695 10 | 10
2696 1000 | 1000
2697 (2 rows)
2699 -- Check that rewrite rules splitting one INSERT into multiple
2700 -- conditional statements does not disable FK checking.
2701 create table rule_and_refint_t1 (
2702 id1a integer,
2703 id1b integer,
2704 primary key (id1a, id1b)
2705 );
2706 create table rule_and_refint_t2 (
2707 id2a integer,
2708 id2c integer,
2709 primary key (id2a, id2c)
2710 );
2711 create table rule_and_refint_t3 (
2712 id3a integer,
2713 id3b integer,
2714 id3c integer,
2715 data text,
2716 primary key (id3a, id3b, id3c),
2717 foreign key (id3a, id3b) references rule_and_refint_t1 (id1a, id1b),
2718 foreign key (id3a, id3c) references rule_and_refint_t2 (id2a, id2c)
2719 );
2720 insert into rule_and_refint_t1 values (1, 11);
2721 insert into rule_and_refint_t1 values (1, 12);
2722 insert into rule_and_refint_t1 values (2, 21);
2723 insert into rule_and_refint_t1 values (2, 22);
2724 insert into rule_and_refint_t2 values (1, 11);
2725 insert into rule_and_refint_t2 values (1, 12);
2726 insert into rule_and_refint_t2 values (2, 21);
2727 insert into rule_and_refint_t2 values (2, 22);
2728 insert into rule_and_refint_t3 values (1, 11, 11, 'row1');
2729 insert into rule_and_refint_t3 values (1, 11, 12, 'row2');
2730 insert into rule_and_refint_t3 values (1, 12, 11, 'row3');
2731 insert into rule_and_refint_t3 values (1, 12, 12, 'row4');
2732 insert into rule_and_refint_t3 values (1, 11, 13, 'row5');
2733 ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_id3c_fkey"
2734 DETAIL: Key (id3a, id3c)=(1, 13) is not present in table "rule_and_refint_t2".
2735 insert into rule_and_refint_t3 values (1, 13, 11, 'row6');
2736 ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_id3b_fkey"
2737 DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1".
2738 -- Ordinary table
2739 insert into rule_and_refint_t3 values (1, 13, 11, 'row6')
2740 on conflict do nothing;
2741 ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_id3b_fkey"
2742 DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1".
2743 -- rule not fired, so fk violation
2744 insert into rule_and_refint_t3 values (1, 13, 11, 'row6')
2745 on conflict (id3a, id3b, id3c) do update
2746 set id3b = excluded.id3b;
2747 ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_id3b_fkey"
2748 DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1".
2749 -- rule fired, so unsupported
2750 insert into shoelace values ('sl9', 0, 'pink', 35.0, 'inch', 0.0)
2751 on conflict (sl_name) do update
2752 set sl_avail = excluded.sl_avail;
2753 ERROR: INSERT with ON CONFLICT clause cannot be used with table that has INSERT or UPDATE rules
2754 create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3
2755 where (exists (select 1 from rule_and_refint_t3
2756 where (((rule_and_refint_t3.id3a = new.id3a)
2757 and (rule_and_refint_t3.id3b = new.id3b))
2758 and (rule_and_refint_t3.id3c = new.id3c))))
2759 do instead update rule_and_refint_t3 set data = new.data
2760 where (((rule_and_refint_t3.id3a = new.id3a)
2761 and (rule_and_refint_t3.id3b = new.id3b))
2762 and (rule_and_refint_t3.id3c = new.id3c));
2763 insert into rule_and_refint_t3 values (1, 11, 13, 'row7');
2764 ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_id3c_fkey"
2765 DETAIL: Key (id3a, id3c)=(1, 13) is not present in table "rule_and_refint_t2".
2766 insert into rule_and_refint_t3 values (1, 13, 11, 'row8');
2767 ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_id3b_fkey"
2768 DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1".
2769 --
2770 -- disallow dropping a view's rule (bug #5072)
2771 --
2772 create view rules_fooview as select 'rules_foo'::text;
2773 drop rule "_RETURN" on rules_fooview;
2774 ERROR: cannot drop rule _RETURN on view rules_fooview because view rules_fooview requires it
2775 HINT: You can drop view rules_fooview instead.
2776 drop view rules_fooview;
2777 --
2778 -- We used to allow converting a table to a view by creating a "_RETURN"
2779 -- rule for it, but no more.
2780 --
2781 create table rules_fooview (x int, y text);
2782 create rule "_RETURN" as on select to rules_fooview do instead
2783 select 1 as x, 'aaa'::text as y;
2784 ERROR: relation "rules_fooview" cannot have ON SELECT rules
2785 DETAIL: This operation is not supported for tables.
2786 drop table rules_fooview;
2787 -- likewise, converting a partitioned table or partition to view is not allowed
2788 create table rules_fooview (x int, y text) partition by list (x);
2789 create rule "_RETURN" as on select to rules_fooview do instead
2790 select 1 as x, 'aaa'::text as y;
2791 ERROR: relation "rules_fooview" cannot have ON SELECT rules
2792 DETAIL: This operation is not supported for partitioned tables.
2793 create table rules_fooview_part partition of rules_fooview for values in (1);
2794 create rule "_RETURN" as on select to rules_fooview_part do instead
2795 select 1 as x, 'aaa'::text as y;
2796 ERROR: relation "rules_fooview_part" cannot have ON SELECT rules
2797 DETAIL: This operation is not supported for tables.
2798 drop table rules_fooview;
2799 --
2800 -- check for planner problems with complex inherited UPDATES
2801 --
2802 create table id (id serial primary key, name text);
2803 -- currently, must respecify PKEY for each inherited subtable
2804 create table test_1 (id integer primary key) inherits (id);
2805 NOTICE: merging column "id" with inherited definition
2806 create table test_2 (id integer primary key) inherits (id);
2807 NOTICE: merging column "id" with inherited definition
2808 create table test_3 (id integer primary key) inherits (id);
2809 NOTICE: merging column "id" with inherited definition
2810 insert into test_1 (name) values ('Test 1');
2811 insert into test_1 (name) values ('Test 2');
2812 insert into test_2 (name) values ('Test 3');
2813 insert into test_2 (name) values ('Test 4');
2814 insert into test_3 (name) values ('Test 5');
2815 insert into test_3 (name) values ('Test 6');
2816 create view id_ordered as select * from id order by id;
2817 create rule update_id_ordered as on update to id_ordered
2818 do instead update id set name = new.name where id = old.id;
2819 select * from id_ordered;
2820 id | name
2821 ----+--------
2822 1 | Test 1
2823 2 | Test 2
2824 3 | Test 3
2825 4 | Test 4
2826 5 | Test 5
2827 6 | Test 6
2828 (6 rows)
2830 update id_ordered set name = 'update 2' where id = 2;
2831 update id_ordered set name = 'update 4' where id = 4;
2832 update id_ordered set name = 'update 5' where id = 5;
2833 select * from id_ordered;
2834 id | name
2835 ----+----------
2836 1 | Test 1
2837 2 | update 2
2838 3 | Test 3
2839 4 | update 4
2840 5 | update 5
2841 6 | Test 6
2842 (6 rows)
2844 drop table id cascade;
2845 NOTICE: drop cascades to 4 other objects
2846 DETAIL: drop cascades to table test_1
2847 drop cascades to table test_2
2848 drop cascades to table test_3
2849 drop cascades to view id_ordered
2850 --
2851 -- check corner case where an entirely-dummy subplan is created by
2852 -- constraint exclusion
2853 --
2854 create temp table t1 (a integer primary key);
2855 create temp table t1_1 (check (a >= 0 and a < 10)) inherits (t1);
2856 create temp table t1_2 (check (a >= 10 and a < 20)) inherits (t1);
2857 create rule t1_ins_1 as on insert to t1
2858 where new.a >= 0 and new.a < 10
2859 do instead
2860 insert into t1_1 values (new.a);
2861 create rule t1_ins_2 as on insert to t1
2862 where new.a >= 10 and new.a < 20
2863 do instead
2864 insert into t1_2 values (new.a);
2865 create rule t1_upd_1 as on update to t1
2866 where old.a >= 0 and old.a < 10
2867 do instead
2868 update t1_1 set a = new.a where a = old.a;
2869 create rule t1_upd_2 as on update to t1
2870 where old.a >= 10 and old.a < 20
2871 do instead
2872 update t1_2 set a = new.a where a = old.a;
2873 set constraint_exclusion = on;
2874 insert into t1 select * from generate_series(5,19,1) g;
2875 update t1 set a = 4 where a = 5;
2876 select * from only t1;
2877 a
2878 ---
2879 (0 rows)
2881 select * from only t1_1;
2882 a
2883 ---
2884 6
2885 7
2886 8
2887 9
2888 4
2889 (5 rows)
2891 select * from only t1_2;
2892 a
2893 ----
2894 10
2895 11
2896 12
2897 13
2898 14
2899 15
2900 16
2901 17
2902 18
2903 19
2904 (10 rows)
2906 reset constraint_exclusion;
2907 -- test FOR UPDATE in rules
2908 create table rules_base(f1 int, f2 int);
2909 insert into rules_base values(1,2), (11,12);
2910 create rule r1 as on update to rules_base do instead
2911 select * from rules_base where f1 = 1 for update;
2912 update rules_base set f2 = f2 + 1;
2913 f1 | f2
2914 ----+----
2915 1 | 2
2916 (1 row)
2918 create or replace rule r1 as on update to rules_base do instead
2919 select * from rules_base where f1 = 11 for update of rules_base;
2920 update rules_base set f2 = f2 + 1;
2921 f1 | f2
2922 ----+----
2923 11 | 12
2924 (1 row)
2926 create or replace rule r1 as on update to rules_base do instead
2927 select * from rules_base where f1 = 11 for update of old; -- error
2928 ERROR: relation "old" in FOR UPDATE clause not found in FROM clause
2929 LINE 2: select * from rules_base where f1 = 11 for update of old;
2930 ^
2931 drop table rules_base;
2932 -- test various flavors of pg_get_viewdef()
2933 select pg_get_viewdef('shoe'::regclass) as unpretty;
2934 unpretty
2935 ------------------------------------------------
2936 SELECT sh.shoename, +
2937 sh.sh_avail, +
2938 sh.slcolor, +
2939 sh.slminlen, +
2940 (sh.slminlen * un.un_fact) AS slminlen_cm,+
2941 sh.slmaxlen, +
2942 (sh.slmaxlen * un.un_fact) AS slmaxlen_cm,+
2943 sh.slunit +
2944 FROM shoe_data sh, +
2945 unit un +
2946 WHERE (sh.slunit = un.un_name);
2947 (1 row)
2949 select pg_get_viewdef('shoe'::regclass,true) as pretty;
2950 pretty
2951 ----------------------------------------------
2952 SELECT sh.shoename, +
2953 sh.sh_avail, +
2954 sh.slcolor, +
2955 sh.slminlen, +
2956 sh.slminlen * un.un_fact AS slminlen_cm,+
2957 sh.slmaxlen, +
2958 sh.slmaxlen * un.un_fact AS slmaxlen_cm,+
2959 sh.slunit +
2960 FROM shoe_data sh, +
2961 unit un +
2962 WHERE sh.slunit = un.un_name;
2963 (1 row)
2965 select pg_get_viewdef('shoe'::regclass,0) as prettier;
2966 prettier
2967 ----------------------------------------------
2968 SELECT sh.shoename, +
2969 sh.sh_avail, +
2970 sh.slcolor, +
2971 sh.slminlen, +
2972 sh.slminlen * un.un_fact AS slminlen_cm,+
2973 sh.slmaxlen, +
2974 sh.slmaxlen * un.un_fact AS slmaxlen_cm,+
2975 sh.slunit +
2976 FROM shoe_data sh, +
2977 unit un +
2978 WHERE sh.slunit = un.un_name;
2979 (1 row)
2981 --
2982 -- check multi-row VALUES in rules
2983 --
2984 create table rules_src(f1 int, f2 int default 0);
2985 create table rules_log(f1 int, f2 int, tag text, id serial);
2986 insert into rules_src values(1,2), (11,12);
2987 create rule r1 as on update to rules_src do also
2988 insert into rules_log values(old.*, 'old', default), (new.*, 'new', default);
2989 update rules_src set f2 = f2 + 1;
2990 update rules_src set f2 = f2 * 10;
2991 select * from rules_src;
2992 f1 | f2
2993 ----+-----
2994 1 | 30
2995 11 | 130
2996 (2 rows)
2998 select * from rules_log;
2999 f1 | f2 | tag | id
3000 ----+-----+-----+----
3001 1 | 2 | old | 1
3002 1 | 3 | new | 2
3003 11 | 12 | old | 3
3004 11 | 13 | new | 4
3005 1 | 3 | old | 5
3006 1 | 30 | new | 6
3007 11 | 13 | old | 7
3008 11 | 130 | new | 8
3009 (8 rows)
3011 create rule r2 as on update to rules_src do also
3012 values(old.*, 'old'), (new.*, 'new');
3013 update rules_src set f2 = f2 / 10;
3014 column1 | column2 | column3
3015 ---------+---------+---------
3016 1 | 30 | old
3017 1 | 3 | new
3018 11 | 130 | old
3019 11 | 13 | new
3020 (4 rows)
3022 create rule r3 as on insert to rules_src do also
3023 insert into rules_log values(null, null, '-', default), (new.*, 'new', default);
3024 insert into rules_src values(22,23), (33,default);
3025 select * from rules_src;
3026 f1 | f2
3027 ----+----
3028 1 | 3
3029 11 | 13
3030 22 | 23
3031 33 | 0
3032 (4 rows)
3034 select * from rules_log;
3035 f1 | f2 | tag | id
3036 ----+-----+-----+----
3037 1 | 2 | old | 1
3038 1 | 3 | new | 2
3039 11 | 12 | old | 3
3040 11 | 13 | new | 4
3041 1 | 3 | old | 5
3042 1 | 30 | new | 6
3043 11 | 13 | old | 7
3044 11 | 130 | new | 8
3045 1 | 30 | old | 9
3046 1 | 3 | new | 10
3047 11 | 130 | old | 11
3048 11 | 13 | new | 12
3049 | | - | 13
3050 22 | 23 | new | 14
3051 | | - | 15
3052 33 | 0 | new | 16
3053 (16 rows)
3055 create rule r4 as on delete to rules_src do notify rules_src_deletion;
3056 --
3057 -- Ensure an aliased target relation for insert is correctly deparsed.
3058 --
3059 create rule r5 as on insert to rules_src do instead insert into rules_log AS trgt SELECT NEW.* RETURNING trgt.f1, trgt.f2;
3060 create rule r6 as on update to rules_src do instead UPDATE rules_log AS trgt SET tag = 'updated' WHERE trgt.f1 = new.f1;
3061 --
3062 -- Check deparse disambiguation of INSERT/UPDATE/DELETE targets.
3063 --
3064 create rule r7 as on delete to rules_src do instead
3065 with wins as (insert into int4_tbl as trgt values (0) returning *),
3066 wupd as (update int4_tbl trgt set f1 = f1+1 returning *),
3067 wdel as (delete from int4_tbl trgt where f1 = 0 returning *)
3068 insert into rules_log AS trgt select old.* from wins, wupd, wdel
3069 returning trgt.f1, trgt.f2;
3070 -- check display of all rules added above
3071 \d+ rules_src
3072 Table "public.rules_src"
3073 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
3074 --------+---------+-----------+----------+---------+---------+--------------+-------------
3075 f1 | integer | | | | plain | |
3076 f2 | integer | | | 0 | plain | |
3077 Rules:
3078 r1 AS
3079 ON UPDATE TO rules_src DO INSERT INTO rules_log (f1, f2, tag, id) VALUES (old.f1,old.f2,'old'::text,DEFAULT), (new.f1,new.f2,'new'::text,DEFAULT)
3080 r2 AS
3081 ON UPDATE TO rules_src DO VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)
3082 r3 AS
3083 ON INSERT TO rules_src DO INSERT INTO rules_log (f1, f2, tag, id) VALUES (NULL::integer,NULL::integer,'-'::text,DEFAULT), (new.f1,new.f2,'new'::text,DEFAULT)
3084 r4 AS
3085 ON DELETE TO rules_src DO
3086 NOTIFY rules_src_deletion
3087 r5 AS
3088 ON INSERT TO rules_src DO INSTEAD INSERT INTO rules_log AS trgt (f1, f2) SELECT new.f1,
3089 new.f2
3090 RETURNING trgt.f1,
3091 trgt.f2
3092 r6 AS
3093 ON UPDATE TO rules_src DO INSTEAD UPDATE rules_log trgt SET tag = 'updated'::text
3094 WHERE trgt.f1 = new.f1
3095 r7 AS
3096 ON DELETE TO rules_src DO INSTEAD WITH wins AS (
3097 INSERT INTO int4_tbl AS trgt_1 (f1)
3098 VALUES (0)
3099 RETURNING trgt_1.f1
3100 ), wupd AS (
3101 UPDATE int4_tbl trgt_1 SET f1 = trgt_1.f1 + 1
3102 RETURNING trgt_1.f1
3103 ), wdel AS (
3104 DELETE FROM int4_tbl trgt_1
3105 WHERE trgt_1.f1 = 0
3106 RETURNING trgt_1.f1
3107 )
3108 INSERT INTO rules_log AS trgt (f1, f2) SELECT old.f1,
3109 old.f2
3110 FROM wins,
3111 wupd,
3112 wdel
3113 RETURNING trgt.f1,
3114 trgt.f2
3116 --
3117 -- Also check multiassignment deparsing.
3118 --
3119 create table rule_t1(f1 int, f2 int);
3120 create table rule_dest(f1 int, f2 int[], tag text);
3121 create rule rr as on update to rule_t1 do instead UPDATE rule_dest trgt
3122 SET (f2[1], f1, tag) = (SELECT new.f2, new.f1, 'updated'::varchar)
3123 WHERE trgt.f1 = new.f1 RETURNING new.*;
3124 \d+ rule_t1
3125 Table "public.rule_t1"
3126 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
3127 --------+---------+-----------+----------+---------+---------+--------------+-------------
3128 f1 | integer | | | | plain | |
3129 f2 | integer | | | | plain | |
3130 Rules:
3131 rr AS
3132 ON UPDATE TO rule_t1 DO INSTEAD UPDATE rule_dest trgt SET (f2[1], f1, tag) = ( SELECT new.f2,
3133 new.f1,
3134 'updated'::character varying AS "varchar")
3135 WHERE trgt.f1 = new.f1
3136 RETURNING new.f1,
3137 new.f2
3139 drop table rule_t1, rule_dest;
3140 --
3141 -- Test implicit LATERAL references to old/new in rules
3142 --
3143 CREATE TABLE rule_t1(a int, b text DEFAULT 'xxx', c int);
3144 CREATE VIEW rule_v1 AS SELECT * FROM rule_t1;
3145 CREATE RULE v1_ins AS ON INSERT TO rule_v1
3146 DO ALSO INSERT INTO rule_t1
3147 SELECT * FROM (SELECT a + 10 FROM rule_t1 WHERE a = NEW.a) tt;
3148 CREATE RULE v1_upd AS ON UPDATE TO rule_v1
3149 DO ALSO UPDATE rule_t1 t
3150 SET c = tt.a * 10
3151 FROM (SELECT a FROM rule_t1 WHERE a = OLD.a) tt WHERE t.a = tt.a;
3152 INSERT INTO rule_v1 VALUES (1, 'a'), (2, 'b');
3153 UPDATE rule_v1 SET b = upper(b);
3154 SELECT * FROM rule_t1;
3155 a | b | c
3156 ----+-----+-----
3157 1 | A | 10
3158 2 | B | 20
3159 11 | XXX | 110
3160 12 | XXX | 120
3161 (4 rows)
3163 DROP TABLE rule_t1 CASCADE;
3164 NOTICE: drop cascades to view rule_v1
3165 --
3166 -- check alter rename rule
3167 --
3168 CREATE TABLE rule_t1 (a INT);
3169 CREATE VIEW rule_v1 AS SELECT * FROM rule_t1;
3170 CREATE RULE InsertRule AS
3171 ON INSERT TO rule_v1
3172 DO INSTEAD
3173 INSERT INTO rule_t1 VALUES(new.a);
3174 ALTER RULE InsertRule ON rule_v1 RENAME to NewInsertRule;
3175 INSERT INTO rule_v1 VALUES(1);
3176 SELECT * FROM rule_v1;
3177 a
3178 ---
3179 1
3180 (1 row)
3182 \d+ rule_v1
3183 View "public.rule_v1"
3184 Column | Type | Collation | Nullable | Default | Storage | Description
3185 --------+---------+-----------+----------+---------+---------+-------------
3186 a | integer | | | | plain |
3187 View definition:
3188 SELECT a
3189 FROM rule_t1;
3190 Rules:
3191 newinsertrule AS
3192 ON INSERT TO rule_v1 DO INSTEAD INSERT INTO rule_t1 (a)
3193 VALUES (new.a)
3195 --
3196 -- error conditions for alter rename rule
3197 --
3198 ALTER RULE InsertRule ON rule_v1 RENAME TO NewInsertRule; -- doesn't exist
3199 ERROR: rule "insertrule" for relation "rule_v1" does not exist
3200 ALTER RULE NewInsertRule ON rule_v1 RENAME TO "_RETURN"; -- already exists
3201 ERROR: rule "_RETURN" for relation "rule_v1" already exists
3202 ALTER RULE "_RETURN" ON rule_v1 RENAME TO abc; -- ON SELECT rule cannot be renamed
3203 ERROR: renaming an ON SELECT rule is not allowed
3204 DROP VIEW rule_v1;
3205 DROP TABLE rule_t1;
3206 --
3207 -- check display of VALUES in view definitions
3208 --
3209 create view rule_v1 as values(1,2);
3210 \d+ rule_v1
3211 View "public.rule_v1"
3212 Column | Type | Collation | Nullable | Default | Storage | Description
3213 ---------+---------+-----------+----------+---------+---------+-------------
3214 column1 | integer | | | | plain |
3215 column2 | integer | | | | plain |
3216 View definition:
3217 VALUES (1,2);
3219 alter table rule_v1 rename column column2 to q2;
3220 \d+ rule_v1
3221 View "public.rule_v1"
3222 Column | Type | Collation | Nullable | Default | Storage | Description
3223 ---------+---------+-----------+----------+---------+---------+-------------
3224 column1 | integer | | | | plain |
3225 q2 | integer | | | | plain |
3226 View definition:
3227 SELECT column1,
3228 column2 AS q2
3229 FROM (VALUES (1,2)) "*VALUES*";
3231 drop view rule_v1;
3232 create view rule_v1(x) as values(1,2);
3233 \d+ rule_v1
3234 View "public.rule_v1"
3235 Column | Type | Collation | Nullable | Default | Storage | Description
3236 ---------+---------+-----------+----------+---------+---------+-------------
3237 x | integer | | | | plain |
3238 column2 | integer | | | | plain |
3239 View definition:
3240 SELECT column1 AS x,
3241 column2
3242 FROM (VALUES (1,2)) "*VALUES*";
3244 drop view rule_v1;
3245 create view rule_v1(x) as select * from (values(1,2)) v;
3246 \d+ rule_v1
3247 View "public.rule_v1"
3248 Column | Type | Collation | Nullable | Default | Storage | Description
3249 ---------+---------+-----------+----------+---------+---------+-------------
3250 x | integer | | | | plain |
3251 column2 | integer | | | | plain |
3252 View definition:
3253 SELECT column1 AS x,
3254 column2
3255 FROM ( VALUES (1,2)) v;
3257 drop view rule_v1;
3258 create view rule_v1(x) as select * from (values(1,2)) v(q,w);
3259 \d+ rule_v1
3260 View "public.rule_v1"
3261 Column | Type | Collation | Nullable | Default | Storage | Description
3262 --------+---------+-----------+----------+---------+---------+-------------
3263 x | integer | | | | plain |
3264 w | integer | | | | plain |
3265 View definition:
3266 SELECT q AS x,
3267 w
3268 FROM ( VALUES (1,2)) v(q, w);
3270 drop view rule_v1;
3271 --
3272 -- Check DO INSTEAD rules with ON CONFLICT
3273 --
3274 CREATE TABLE hats (
3275 hat_name char(10) primary key,
3276 hat_color char(10) -- hat color
3277 );
3278 CREATE TABLE hat_data (
3279 hat_name char(10),
3280 hat_color char(10) -- hat color
3281 );
3282 create unique index hat_data_unique_idx
3283 on hat_data (hat_name COLLATE "C" bpchar_pattern_ops);
3284 -- DO NOTHING with ON CONFLICT
3285 CREATE RULE hat_nosert AS ON INSERT TO hats
3286 DO INSTEAD
3287 INSERT INTO hat_data VALUES (
3288 NEW.hat_name,
3289 NEW.hat_color)
3290 ON CONFLICT (hat_name COLLATE "C" bpchar_pattern_ops) WHERE hat_color = 'green'
3291 DO NOTHING
3292 RETURNING *;
3293 SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename;
3294 definition
3295 ---------------------------------------------------------------------------------------------
3296 CREATE RULE hat_nosert AS +
3297 ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) +
3298 VALUES (new.hat_name, new.hat_color) ON CONFLICT(hat_name COLLATE "C" bpchar_pattern_ops)+
3299 WHERE (hat_color = 'green'::bpchar) DO NOTHING +
3300 RETURNING hat_data.hat_name, +
3301 hat_data.hat_color;
3302 (1 row)
3304 -- Works (projects row)
3305 INSERT INTO hats VALUES ('h7', 'black') RETURNING *;
3306 hat_name | hat_color
3307 ------------+------------
3308 h7 | black
3309 (1 row)
3311 -- Works (does nothing)
3312 INSERT INTO hats VALUES ('h7', 'black') RETURNING *;
3313 hat_name | hat_color
3314 ----------+-----------
3315 (0 rows)
3317 SELECT tablename, rulename, definition FROM pg_rules
3318 WHERE tablename = 'hats';
3319 tablename | rulename | definition
3320 -----------+------------+---------------------------------------------------------------------------------------------
3321 hats | hat_nosert | CREATE RULE hat_nosert AS +
3322 | | ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) +
3323 | | VALUES (new.hat_name, new.hat_color) ON CONFLICT(hat_name COLLATE "C" bpchar_pattern_ops)+
3324 | | WHERE (hat_color = 'green'::bpchar) DO NOTHING +
3325 | | RETURNING hat_data.hat_name, +
3326 | | hat_data.hat_color;
3327 (1 row)
3329 DROP RULE hat_nosert ON hats;
3330 -- DO NOTHING without ON CONFLICT
3331 CREATE RULE hat_nosert_all AS ON INSERT TO hats
3332 DO INSTEAD
3333 INSERT INTO hat_data VALUES (
3334 NEW.hat_name,
3335 NEW.hat_color)
3336 ON CONFLICT
3337 DO NOTHING
3338 RETURNING *;
3339 SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename;
3340 definition
3341 -------------------------------------------------------------------------------------
3342 CREATE RULE hat_nosert_all AS +
3343 ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color)+
3344 VALUES (new.hat_name, new.hat_color) ON CONFLICT DO NOTHING +
3345 RETURNING hat_data.hat_name, +
3346 hat_data.hat_color;
3347 (1 row)
3349 DROP RULE hat_nosert_all ON hats;
3350 -- Works (does nothing)
3351 INSERT INTO hats VALUES ('h7', 'black') RETURNING *;
3352 hat_name | hat_color
3353 ------------+------------
3354 h7 | black
3355 (1 row)
3357 -- DO UPDATE with a WHERE clause
3358 CREATE RULE hat_upsert AS ON INSERT TO hats
3359 DO INSTEAD
3360 INSERT INTO hat_data VALUES (
3361 NEW.hat_name,
3362 NEW.hat_color)
3363 ON CONFLICT (hat_name)
3364 DO UPDATE
3365 SET hat_name = hat_data.hat_name, hat_color = excluded.hat_color
3366 WHERE excluded.hat_color <> 'forbidden' AND hat_data.* != excluded.*
3367 RETURNING *;
3368 SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename;
3369 definition
3370 -----------------------------------------------------------------------------------------------------------------------------------------
3371 CREATE RULE hat_upsert AS +
3372 ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) +
3373 VALUES (new.hat_name, new.hat_color) ON CONFLICT(hat_name) DO UPDATE SET hat_name = hat_data.hat_name, hat_color = excluded.hat_color+
3374 WHERE ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*)) +
3375 RETURNING hat_data.hat_name, +
3376 hat_data.hat_color;
3377 (1 row)
3379 -- Works (does upsert)
3380 INSERT INTO hats VALUES ('h8', 'black') RETURNING *;
3381 hat_name | hat_color
3382 ------------+------------
3383 h8 | black
3384 (1 row)
3386 SELECT * FROM hat_data WHERE hat_name = 'h8';
3387 hat_name | hat_color
3388 ------------+------------
3389 h8 | black
3390 (1 row)
3392 INSERT INTO hats VALUES ('h8', 'white') RETURNING *;
3393 hat_name | hat_color
3394 ------------+------------
3395 h8 | white
3396 (1 row)
3398 SELECT * FROM hat_data WHERE hat_name = 'h8';
3399 hat_name | hat_color
3400 ------------+------------
3401 h8 | white
3402 (1 row)
3404 INSERT INTO hats VALUES ('h8', 'forbidden') RETURNING *;
3405 hat_name | hat_color
3406 ----------+-----------
3407 (0 rows)
3409 SELECT * FROM hat_data WHERE hat_name = 'h8';
3410 hat_name | hat_color
3411 ------------+------------
3412 h8 | white
3413 (1 row)
3415 SELECT tablename, rulename, definition FROM pg_rules
3416 WHERE tablename = 'hats';
3417 tablename | rulename | definition
3418 -----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------
3419 hats | hat_upsert | CREATE RULE hat_upsert AS +
3420 | | ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) +
3421 | | VALUES (new.hat_name, new.hat_color) ON CONFLICT(hat_name) DO UPDATE SET hat_name = hat_data.hat_name, hat_color = excluded.hat_color+
3422 | | WHERE ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*)) +
3423 | | RETURNING hat_data.hat_name, +
3424 | | hat_data.hat_color;
3425 (1 row)
3427 -- ensure explain works for on insert conflict rules
3428 explain (costs off) INSERT INTO hats VALUES ('h8', 'forbidden') RETURNING *;
3429 QUERY PLAN
3430 -------------------------------------------------------------------------------------------------
3431 Insert on hat_data
3432 Conflict Resolution: UPDATE
3433 Conflict Arbiter Indexes: hat_data_unique_idx
3434 Conflict Filter: ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*))
3435 -> Result
3436 (5 rows)
3438 -- ensure upserting into a rule, with a CTE (different offsets!) works
3439 WITH data(hat_name, hat_color) AS MATERIALIZED (
3440 VALUES ('h8', 'green'),
3441 ('h9', 'blue'),
3442 ('h7', 'forbidden')
3443 )
3444 INSERT INTO hats
3445 SELECT * FROM data
3446 RETURNING *;
3447 hat_name | hat_color
3448 ------------+------------
3449 h8 | green
3450 h9 | blue
3451 (2 rows)
3453 EXPLAIN (costs off)
3454 WITH data(hat_name, hat_color) AS MATERIALIZED (
3455 VALUES ('h8', 'green'),
3456 ('h9', 'blue'),
3457 ('h7', 'forbidden')
3458 )
3459 INSERT INTO hats
3460 SELECT * FROM data
3461 RETURNING *;
3462 QUERY PLAN
3463 -------------------------------------------------------------------------------------------------
3464 Insert on hat_data
3465 Conflict Resolution: UPDATE
3466 Conflict Arbiter Indexes: hat_data_unique_idx
3467 Conflict Filter: ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*))
3468 CTE data
3469 -> Values Scan on "*VALUES*"
3470 -> CTE Scan on data
3471 (7 rows)
3473 SELECT * FROM hat_data WHERE hat_name IN ('h8', 'h9', 'h7') ORDER BY hat_name;
3474 hat_name | hat_color
3475 ------------+------------
3476 h7 | black
3477 h8 | green
3478 h9 | blue
3479 (3 rows)
3481 DROP RULE hat_upsert ON hats;
3482 drop table hats;
3483 drop table hat_data;
3484 -- test for pg_get_functiondef properly regurgitating SET parameters
3485 -- Note that the function is kept around to stress pg_dump.
3486 CREATE FUNCTION func_with_set_params() RETURNS integer
3487 AS 'select 1;'
3488 LANGUAGE SQL
3489 SET search_path TO PG_CATALOG
3490 SET extra_float_digits TO 2
3491 SET work_mem TO '4MB'
3492 SET datestyle to iso, mdy
3493 SET local_preload_libraries TO "Mixed/Case", 'c:/''a"/path', '', '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789'
3494 IMMUTABLE STRICT;
3495 SELECT pg_get_functiondef('func_with_set_params()'::regprocedure);
3496 pg_get_functiondef
3497 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3498 CREATE OR REPLACE FUNCTION public.func_with_set_params() +
3499 RETURNS integer +
3500 LANGUAGE sql +
3501 IMMUTABLE STRICT +
3502 SET search_path TO 'pg_catalog' +
3503 SET extra_float_digits TO '2' +
3504 SET work_mem TO '4MB' +
3505 SET "DateStyle" TO 'iso, mdy' +
3506 SET local_preload_libraries TO 'Mixed/Case', 'c:/''a"/path', '', '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789'+
3507 AS $function$select 1;$function$ +
3509 (1 row)
3511 -- tests for pg_get_*def with invalid objects
3512 SELECT pg_get_constraintdef(0);
3513 pg_get_constraintdef
3514 ----------------------
3516 (1 row)
3518 SELECT pg_get_functiondef(0);
3519 pg_get_functiondef
3520 --------------------
3522 (1 row)
3524 SELECT pg_get_indexdef(0);
3525 pg_get_indexdef
3526 -----------------
3528 (1 row)
3530 SELECT pg_get_ruledef(0);
3531 pg_get_ruledef
3532 ----------------
3534 (1 row)
3536 SELECT pg_get_statisticsobjdef(0);
3537 pg_get_statisticsobjdef
3538 -------------------------
3540 (1 row)
3542 SELECT pg_get_triggerdef(0);
3543 pg_get_triggerdef
3544 -------------------
3546 (1 row)
3548 SELECT pg_get_viewdef(0);
3549 pg_get_viewdef
3550 ----------------
3552 (1 row)
3554 SELECT pg_get_function_arguments(0);
3555 pg_get_function_arguments
3556 ---------------------------
3558 (1 row)
3560 SELECT pg_get_function_identity_arguments(0);
3561 pg_get_function_identity_arguments
3562 ------------------------------------
3564 (1 row)
3566 SELECT pg_get_function_result(0);
3567 pg_get_function_result
3568 ------------------------
3570 (1 row)
3572 SELECT pg_get_function_arg_default(0, 0);
3573 pg_get_function_arg_default
3574 -----------------------------
3576 (1 row)
3578 SELECT pg_get_function_arg_default('pg_class'::regclass, 0);
3579 pg_get_function_arg_default
3580 -----------------------------
3582 (1 row)
3584 SELECT pg_get_partkeydef(0);
3585 pg_get_partkeydef
3586 -------------------
3588 (1 row)
3590 -- test rename for a rule defined on a partitioned table
3591 CREATE TABLE rules_parted_table (a int) PARTITION BY LIST (a);
3592 CREATE TABLE rules_parted_table_1 PARTITION OF rules_parted_table FOR VALUES IN (1);
3593 CREATE RULE rules_parted_table_insert AS ON INSERT to rules_parted_table
3594 DO INSTEAD INSERT INTO rules_parted_table_1 VALUES (NEW.*);
3595 ALTER RULE rules_parted_table_insert ON rules_parted_table RENAME TO rules_parted_table_insert_redirect;
3596 DROP TABLE rules_parted_table;
3597 --
3598 -- test MERGE
3599 --
3600 CREATE TABLE rule_merge1 (a int, b text);
3601 CREATE TABLE rule_merge2 (a int, b text);
3602 CREATE RULE rule1 AS ON INSERT TO rule_merge1
3603 DO INSTEAD INSERT INTO rule_merge2 VALUES (NEW.*);
3604 CREATE RULE rule2 AS ON UPDATE TO rule_merge1
3605 DO INSTEAD UPDATE rule_merge2 SET a = NEW.a, b = NEW.b
3606 WHERE a = OLD.a;
3607 CREATE RULE rule3 AS ON DELETE TO rule_merge1
3608 DO INSTEAD DELETE FROM rule_merge2 WHERE a = OLD.a;
3609 -- MERGE not supported for table with rules
3610 MERGE INTO rule_merge1 t USING (SELECT 1 AS a) s
3611 ON t.a = s.a
3612 WHEN MATCHED AND t.a < 2 THEN
3613 UPDATE SET b = b || ' updated by merge'
3614 WHEN MATCHED AND t.a > 2 THEN
3615 DELETE
3616 WHEN NOT MATCHED THEN
3617 INSERT VALUES (s.a, '');
3618 ERROR: cannot execute MERGE on relation "rule_merge1"
3619 DETAIL: MERGE is not supported for relations with rules.
3620 -- should be ok with the other table though
3621 MERGE INTO rule_merge2 t USING (SELECT 1 AS a) s
3622 ON t.a = s.a
3623 WHEN MATCHED AND t.a < 2 THEN
3624 UPDATE SET b = b || ' updated by merge'
3625 WHEN MATCHED AND t.a > 2 THEN
3626 DELETE
3627 WHEN NOT MATCHED THEN
3628 INSERT VALUES (s.a, '');
3629 -- also ok if the rules are disabled
3630 ALTER TABLE rule_merge1 DISABLE RULE rule1;
3631 ALTER TABLE rule_merge1 DISABLE RULE rule2;
3632 ALTER TABLE rule_merge1 DISABLE RULE rule3;
3633 MERGE INTO rule_merge1 t USING (SELECT 1 AS a) s
3634 ON t.a = s.a
3635 WHEN MATCHED AND t.a < 2 THEN
3636 UPDATE SET b = b || ' updated by merge'
3637 WHEN MATCHED AND t.a > 2 THEN
3638 DELETE
3639 WHEN NOT MATCHED THEN
3640 INSERT VALUES (s.a, '');
3641 -- test deparsing
3642 CREATE TABLE sf_target(id int, data text, filling int[]);
3643 CREATE FUNCTION merge_sf_test()
3644 RETURNS TABLE(action text, a int, b text, id int, data text, filling int[])
3645 LANGUAGE sql
3646 BEGIN ATOMIC
3647 MERGE INTO sf_target t
3648 USING rule_merge1 s
3649 ON (s.a = t.id)
3650 WHEN MATCHED
3651 AND (s.a + t.id) = 42
3652 THEN UPDATE SET data = repeat(t.data, s.a) || s.b, id = length(s.b)
3653 WHEN NOT MATCHED
3654 AND (s.b IS NOT NULL)
3655 THEN INSERT (data, id)
3656 VALUES (s.b, s.a)
3657 WHEN MATCHED
3658 AND length(s.b || t.data) > 10
3659 THEN UPDATE SET data = s.b
3660 WHEN MATCHED
3661 AND s.a > 200
3662 THEN UPDATE SET filling[s.a] = t.id
3663 WHEN MATCHED
3664 AND s.a > 100
3665 THEN DELETE
3666 WHEN MATCHED
3667 THEN DO NOTHING
3668 WHEN NOT MATCHED
3669 AND s.a > 200
3670 THEN INSERT DEFAULT VALUES
3671 WHEN NOT MATCHED
3672 AND s.a > 100
3673 THEN INSERT (id, data) OVERRIDING USER VALUE
3674 VALUES (s.a, DEFAULT)
3675 WHEN NOT MATCHED
3676 AND s.a > 0
3677 THEN INSERT
3678 VALUES (s.a, s.b, DEFAULT)
3679 WHEN NOT MATCHED
3680 THEN INSERT (filling[1], id)
3681 VALUES (s.a, s.a)
3682 RETURNING
3683 merge_action() AS action, *;
3684 END;
3685 \sf merge_sf_test
3686 CREATE OR REPLACE FUNCTION public.merge_sf_test()
3687 RETURNS TABLE(action text, a integer, b text, id integer, data text, filling integer[])
3688 LANGUAGE sql
3689 BEGIN ATOMIC
3690 MERGE INTO sf_target t
3691 USING rule_merge1 s
3692 ON (s.a = t.id)
3693 WHEN MATCHED
3694 AND ((s.a + t.id) = 42)
3695 THEN UPDATE SET data = (repeat(t.data, s.a) || s.b), id = length(s.b)
3696 WHEN NOT MATCHED
3697 AND (s.b IS NOT NULL)
3698 THEN INSERT (data, id)
3699 VALUES (s.b, s.a)
3700 WHEN MATCHED
3701 AND (length((s.b || t.data)) > 10)
3702 THEN UPDATE SET data = s.b
3703 WHEN MATCHED
3704 AND (s.a > 200)
3705 THEN UPDATE SET filling[s.a] = t.id
3706 WHEN MATCHED
3707 AND (s.a > 100)
3708 THEN DELETE
3709 WHEN MATCHED
3710 THEN DO NOTHING
3711 WHEN NOT MATCHED
3712 AND (s.a > 200)
3713 THEN INSERT DEFAULT VALUES
3714 WHEN NOT MATCHED
3715 AND (s.a > 100)
3716 THEN INSERT (id, data) OVERRIDING USER VALUE
3717 VALUES (s.a, DEFAULT)
3718 WHEN NOT MATCHED
3719 AND (s.a > 0)
3720 THEN INSERT (id, data, filling)
3721 VALUES (s.a, s.b, DEFAULT)
3722 WHEN NOT MATCHED
3723 THEN INSERT (filling[1], id)
3724 VALUES (s.a, s.a)
3725 RETURNING MERGE_ACTION() AS action,
3726 s.a,
3727 s.b,
3728 t.id,
3729 t.data,
3730 t.filling;
3731 END
3732 DROP FUNCTION merge_sf_test;
3733 DROP TABLE sf_target;
3734 --
3735 -- Test enabling/disabling
3736 --
3737 CREATE TABLE ruletest1 (a int);
3738 CREATE TABLE ruletest2 (b int);
3739 CREATE RULE rule1 AS ON INSERT TO ruletest1
3740 DO INSTEAD INSERT INTO ruletest2 VALUES (NEW.*);
3741 INSERT INTO ruletest1 VALUES (1);
3742 ALTER TABLE ruletest1 DISABLE RULE rule1;
3743 INSERT INTO ruletest1 VALUES (2);
3744 ALTER TABLE ruletest1 ENABLE RULE rule1;
3745 SET session_replication_role = replica;
3746 INSERT INTO ruletest1 VALUES (3);
3747 ALTER TABLE ruletest1 ENABLE REPLICA RULE rule1;
3748 INSERT INTO ruletest1 VALUES (4);
3749 RESET session_replication_role;
3750 INSERT INTO ruletest1 VALUES (5);
3751 SELECT * FROM ruletest1;
3752 a
3753 ---
3754 2
3755 3
3756 5
3757 (3 rows)
3759 SELECT * FROM ruletest2;
3760 b
3761 ---
3762 1
3763 4
3764 (2 rows)
3766 DROP TABLE ruletest1;
3767 DROP TABLE ruletest2;
3768 --
3769 -- Test non-SELECT rule on security invoker view.
3770 -- Should use view owner's permissions.
3771 --
3772 CREATE USER regress_rule_user1;
3773 CREATE TABLE ruletest_t1 (x int);
3774 CREATE TABLE ruletest_t2 (x int);
3775 CREATE VIEW ruletest_v1 WITH (security_invoker=true) AS
3776 SELECT * FROM ruletest_t1;
3777 GRANT INSERT ON ruletest_v1 TO regress_rule_user1;
3778 CREATE RULE rule1 AS ON INSERT TO ruletest_v1
3779 DO INSTEAD INSERT INTO ruletest_t2 VALUES (NEW.*);
3780 SET SESSION AUTHORIZATION regress_rule_user1;
3781 INSERT INTO ruletest_v1 VALUES (1);
3782 RESET SESSION AUTHORIZATION;
3783 -- Test that main query's relation's permissions are checked before
3784 -- the rule action's relation's.
3785 CREATE TABLE ruletest_t3 (x int);
3786 CREATE RULE rule2 AS ON UPDATE TO ruletest_t1
3787 DO INSTEAD INSERT INTO ruletest_t2 VALUES (OLD.*);
3788 REVOKE ALL ON ruletest_t2 FROM regress_rule_user1;
3789 REVOKE ALL ON ruletest_t3 FROM regress_rule_user1;
3790 ALTER TABLE ruletest_t1 OWNER TO regress_rule_user1;
3791 SET SESSION AUTHORIZATION regress_rule_user1;
3792 UPDATE ruletest_t1 t1 SET x = 0 FROM ruletest_t3 t3 WHERE t1.x = t3.x;
3793 ERROR: permission denied for table ruletest_t3
3794 RESET SESSION AUTHORIZATION;
3795 SELECT * FROM ruletest_t1;
3796 x
3797 ---
3798 (0 rows)
3800 SELECT * FROM ruletest_t2;
3801 x
3802 ---
3803 1
3804 (1 row)
3806 DROP VIEW ruletest_v1;
3807 DROP RULE rule2 ON ruletest_t1;
3808 DROP TABLE ruletest_t3;
3809 DROP TABLE ruletest_t2;
3810 DROP TABLE ruletest_t1;
3811 DROP USER regress_rule_user1;