| blob | 3014d047fefc6488e60fa63d4908ee013cda5093 |
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 type,
1309 level,
1310 path,
1311 total_bytes,
1312 total_nblocks,
1313 free_bytes,
1314 free_chunks,
1315 used_bytes
1316 FROM pg_get_backend_memory_contexts() pg_get_backend_memory_contexts(name, ident, type, level, path, total_bytes, total_nblocks, free_bytes, free_chunks, used_bytes);
1317 pg_config| SELECT name,
1318 setting
1319 FROM pg_config() pg_config(name, setting);
1320 pg_cursors| SELECT name,
1321 statement,
1322 is_holdable,
1323 is_binary,
1324 is_scrollable,
1325 creation_time
1326 FROM pg_cursor() c(name, statement, is_holdable, is_binary, is_scrollable, creation_time);
1327 pg_file_settings| SELECT sourcefile,
1328 sourceline,
1329 seqno,
1330 name,
1331 setting,
1332 applied,
1333 error
1334 FROM pg_show_all_file_settings() a(sourcefile, sourceline, seqno, name, setting, applied, error);
1335 pg_group| SELECT rolname AS groname,
1336 oid AS grosysid,
1337 ARRAY( SELECT pg_auth_members.member
1338 FROM pg_auth_members
1339 WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist
1340 FROM pg_authid
1341 WHERE (NOT rolcanlogin);
1342 pg_hba_file_rules| SELECT rule_number,
1343 file_name,
1344 line_number,
1345 type,
1346 database,
1347 user_name,
1348 address,
1349 netmask,
1350 auth_method,
1351 options,
1352 error
1353 FROM pg_hba_file_rules() a(rule_number, file_name, line_number, type, database, user_name, address, netmask, auth_method, options, error);
1354 pg_ident_file_mappings| SELECT map_number,
1355 file_name,
1356 line_number,
1357 map_name,
1358 sys_name,
1359 pg_username,
1360 error
1361 FROM pg_ident_file_mappings() a(map_number, file_name, line_number, map_name, sys_name, pg_username, error);
1362 pg_indexes| SELECT n.nspname AS schemaname,
1363 c.relname AS tablename,
1364 i.relname AS indexname,
1365 t.spcname AS tablespace,
1366 pg_get_indexdef(i.oid) AS indexdef
1367 FROM ((((pg_index x
1368 JOIN pg_class c ON ((c.oid = x.indrelid)))
1369 JOIN pg_class i ON ((i.oid = x.indexrelid)))
1370 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
1371 LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace)))
1372 WHERE ((c.relkind = ANY (ARRAY['r'::"char", 'm'::"char", 'p'::"char"])) AND (i.relkind = ANY (ARRAY['i'::"char", 'I'::"char"])));
1373 pg_locks| SELECT locktype,
1374 database,
1375 relation,
1376 page,
1377 tuple,
1378 virtualxid,
1379 transactionid,
1380 classid,
1381 objid,
1382 objsubid,
1383 virtualtransaction,
1384 pid,
1385 mode,
1386 granted,
1387 fastpath,
1388 waitstart
1389 FROM pg_lock_status() l(locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted, fastpath, waitstart);
1390 pg_matviews| SELECT n.nspname AS schemaname,
1391 c.relname AS matviewname,
1392 pg_get_userbyid(c.relowner) AS matviewowner,
1393 t.spcname AS tablespace,
1394 c.relhasindex AS hasindexes,
1395 c.relispopulated AS ispopulated,
1396 pg_get_viewdef(c.oid) AS definition
1397 FROM ((pg_class c
1398 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
1399 LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace)))
1400 WHERE (c.relkind = 'm'::"char");
1401 pg_policies| SELECT n.nspname AS schemaname,
1402 c.relname AS tablename,
1403 pol.polname AS policyname,
1404 CASE
1405 WHEN pol.polpermissive THEN 'PERMISSIVE'::text
1406 ELSE 'RESTRICTIVE'::text
1407 END AS permissive,
1408 CASE
1409 WHEN (pol.polroles = '{0}'::oid[]) THEN (string_to_array('public'::text, ''::text))::name[]
1410 ELSE ARRAY( SELECT pg_authid.rolname
1411 FROM pg_authid
1412 WHERE (pg_authid.oid = ANY (pol.polroles))
1413 ORDER BY pg_authid.rolname)
1414 END AS roles,
1415 CASE pol.polcmd
1416 WHEN 'r'::"char" THEN 'SELECT'::text
1417 WHEN 'a'::"char" THEN 'INSERT'::text
1418 WHEN 'w'::"char" THEN 'UPDATE'::text
1419 WHEN 'd'::"char" THEN 'DELETE'::text
1420 WHEN '*'::"char" THEN 'ALL'::text
1421 ELSE NULL::text
1422 END AS cmd,
1423 pg_get_expr(pol.polqual, pol.polrelid) AS qual,
1424 pg_get_expr(pol.polwithcheck, pol.polrelid) AS with_check
1425 FROM ((pg_policy pol
1426 JOIN pg_class c ON ((c.oid = pol.polrelid)))
1427 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)));
1428 pg_prepared_statements| SELECT name,
1429 statement,
1430 prepare_time,
1431 parameter_types,
1432 result_types,
1433 from_sql,
1434 generic_plans,
1435 custom_plans
1436 FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, result_types, from_sql, generic_plans, custom_plans);
1437 pg_prepared_xacts| SELECT p.transaction,
1438 p.gid,
1439 p.prepared,
1440 u.rolname AS owner,
1441 d.datname AS database
1442 FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid)
1443 LEFT JOIN pg_authid u ON ((p.ownerid = u.oid)))
1444 LEFT JOIN pg_database d ON ((p.dbid = d.oid)));
1445 pg_publication_tables| SELECT p.pubname,
1446 n.nspname AS schemaname,
1447 c.relname AS tablename,
1448 ( SELECT array_agg(a.attname ORDER BY a.attnum) AS array_agg
1449 FROM pg_attribute a
1450 WHERE ((a.attrelid = gpt.relid) AND (a.attnum = ANY ((gpt.attrs)::smallint[])))) AS attnames,
1451 pg_get_expr(gpt.qual, gpt.relid) AS rowfilter
1452 FROM pg_publication p,
1453 LATERAL pg_get_publication_tables(VARIADIC ARRAY[(p.pubname)::text]) gpt(pubid, relid, attrs, qual),
1454 (pg_class c
1455 JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
1456 WHERE (c.oid = gpt.relid);
1457 pg_replication_origin_status| SELECT local_id,
1458 external_id,
1459 remote_lsn,
1460 local_lsn
1461 FROM pg_show_replication_origin_status() pg_show_replication_origin_status(local_id, external_id, remote_lsn, local_lsn);
1462 pg_replication_slots| SELECT l.slot_name,
1463 l.plugin,
1464 l.slot_type,
1465 l.datoid,
1466 d.datname AS database,
1467 l.temporary,
1468 l.active,
1469 l.active_pid,
1470 l.xmin,
1471 l.catalog_xmin,
1472 l.restart_lsn,
1473 l.confirmed_flush_lsn,
1474 l.wal_status,
1475 l.safe_wal_size,
1476 l.two_phase,
1477 l.inactive_since,
1478 l.conflicting,
1479 l.invalidation_reason,
1480 l.failover,
1481 l.synced
1482 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, inactive_since, conflicting, invalidation_reason, failover, synced)
1483 LEFT JOIN pg_database d ON ((l.datoid = d.oid)));
1484 pg_roles| SELECT pg_authid.rolname,
1485 pg_authid.rolsuper,
1486 pg_authid.rolinherit,
1487 pg_authid.rolcreaterole,
1488 pg_authid.rolcreatedb,
1489 pg_authid.rolcanlogin,
1490 pg_authid.rolreplication,
1491 pg_authid.rolconnlimit,
1492 '********'::text AS rolpassword,
1493 pg_authid.rolvaliduntil,
1494 pg_authid.rolbypassrls,
1495 s.setconfig AS rolconfig,
1496 pg_authid.oid
1497 FROM (pg_authid
1498 LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))));
1499 pg_rules| SELECT n.nspname AS schemaname,
1500 c.relname AS tablename,
1501 r.rulename,
1502 pg_get_ruledef(r.oid) AS definition
1503 FROM ((pg_rewrite r
1504 JOIN pg_class c ON ((c.oid = r.ev_class)))
1505 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
1506 WHERE (r.rulename <> '_RETURN'::name);
1507 pg_seclabels| SELECT l.objoid,
1508 l.classoid,
1509 l.objsubid,
1510 CASE
1511 WHEN (rel.relkind = ANY (ARRAY['r'::"char", 'p'::"char"])) THEN 'table'::text
1512 WHEN (rel.relkind = 'v'::"char") THEN 'view'::text
1513 WHEN (rel.relkind = 'm'::"char") THEN 'materialized view'::text
1514 WHEN (rel.relkind = 'S'::"char") THEN 'sequence'::text
1515 WHEN (rel.relkind = 'f'::"char") THEN 'foreign table'::text
1516 ELSE NULL::text
1517 END AS objtype,
1518 rel.relnamespace AS objnamespace,
1519 CASE
1520 WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text)
1521 ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text))
1522 END AS objname,
1523 l.provider,
1524 l.label
1525 FROM ((pg_seclabel l
1526 JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid))))
1527 JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid)))
1528 WHERE (l.objsubid = 0)
1529 UNION ALL
1530 SELECT l.objoid,
1531 l.classoid,
1532 l.objsubid,
1533 'column'::text AS objtype,
1534 rel.relnamespace AS objnamespace,
1535 ((
1536 CASE
1537 WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text)
1538 ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text))
1539 END || '.'::text) || (att.attname)::text) AS objname,
1540 l.provider,
1541 l.label
1542 FROM (((pg_seclabel l
1543 JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid))))
1544 JOIN pg_attribute att ON (((rel.oid = att.attrelid) AND (l.objsubid = att.attnum))))
1545 JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid)))
1546 WHERE (l.objsubid <> 0)
1547 UNION ALL
1548 SELECT l.objoid,
1549 l.classoid,
1550 l.objsubid,
1551 CASE pro.prokind
1552 WHEN 'a'::"char" THEN 'aggregate'::text
1553 WHEN 'f'::"char" THEN 'function'::text
1554 WHEN 'p'::"char" THEN 'procedure'::text
1555 WHEN 'w'::"char" THEN 'window'::text
1556 ELSE NULL::text
1557 END AS objtype,
1558 pro.pronamespace AS objnamespace,
1559 (((
1560 CASE
1561 WHEN pg_function_is_visible(pro.oid) THEN quote_ident((pro.proname)::text)
1562 ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((pro.proname)::text))
1563 END || '('::text) || pg_get_function_arguments(pro.oid)) || ')'::text) AS objname,
1564 l.provider,
1565 l.label
1566 FROM ((pg_seclabel l
1567 JOIN pg_proc pro ON (((l.classoid = pro.tableoid) AND (l.objoid = pro.oid))))
1568 JOIN pg_namespace nsp ON ((pro.pronamespace = nsp.oid)))
1569 WHERE (l.objsubid = 0)
1570 UNION ALL
1571 SELECT l.objoid,
1572 l.classoid,
1573 l.objsubid,
1574 CASE
1575 WHEN (typ.typtype = 'd'::"char") THEN 'domain'::text
1576 ELSE 'type'::text
1577 END AS objtype,
1578 typ.typnamespace AS objnamespace,
1579 CASE
1580 WHEN pg_type_is_visible(typ.oid) THEN quote_ident((typ.typname)::text)
1581 ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((typ.typname)::text))
1582 END AS objname,
1583 l.provider,
1584 l.label
1585 FROM ((pg_seclabel l
1586 JOIN pg_type typ ON (((l.classoid = typ.tableoid) AND (l.objoid = typ.oid))))
1587 JOIN pg_namespace nsp ON ((typ.typnamespace = nsp.oid)))
1588 WHERE (l.objsubid = 0)
1589 UNION ALL
1590 SELECT l.objoid,
1591 l.classoid,
1592 l.objsubid,
1593 'large object'::text AS objtype,
1594 NULL::oid AS objnamespace,
1595 (l.objoid)::text AS objname,
1596 l.provider,
1597 l.label
1598 FROM (pg_seclabel l
1599 JOIN pg_largeobject_metadata lom ON ((l.objoid = lom.oid)))
1600 WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND (l.objsubid = 0))
1601 UNION ALL
1602 SELECT l.objoid,
1603 l.classoid,
1604 l.objsubid,
1605 'language'::text AS objtype,
1606 NULL::oid AS objnamespace,
1607 quote_ident((lan.lanname)::text) AS objname,
1608 l.provider,
1609 l.label
1610 FROM (pg_seclabel l
1611 JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND (l.objoid = lan.oid))))
1612 WHERE (l.objsubid = 0)
1613 UNION ALL
1614 SELECT l.objoid,
1615 l.classoid,
1616 l.objsubid,
1617 'schema'::text AS objtype,
1618 nsp.oid AS objnamespace,
1619 quote_ident((nsp.nspname)::text) AS objname,
1620 l.provider,
1621 l.label
1622 FROM (pg_seclabel l
1623 JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid = nsp.oid))))
1624 WHERE (l.objsubid = 0)
1625 UNION ALL
1626 SELECT l.objoid,
1627 l.classoid,
1628 l.objsubid,
1629 'event trigger'::text AS objtype,
1630 NULL::oid AS objnamespace,
1631 quote_ident((evt.evtname)::text) AS objname,
1632 l.provider,
1633 l.label
1634 FROM (pg_seclabel l
1635 JOIN pg_event_trigger evt ON (((l.classoid = evt.tableoid) AND (l.objoid = evt.oid))))
1636 WHERE (l.objsubid = 0)
1637 UNION ALL
1638 SELECT l.objoid,
1639 l.classoid,
1640 l.objsubid,
1641 'publication'::text AS objtype,
1642 NULL::oid AS objnamespace,
1643 quote_ident((p.pubname)::text) AS objname,
1644 l.provider,
1645 l.label
1646 FROM (pg_seclabel l
1647 JOIN pg_publication p ON (((l.classoid = p.tableoid) AND (l.objoid = p.oid))))
1648 WHERE (l.objsubid = 0)
1649 UNION ALL
1650 SELECT l.objoid,
1651 l.classoid,
1652 0 AS objsubid,
1653 'subscription'::text AS objtype,
1654 NULL::oid AS objnamespace,
1655 quote_ident((s.subname)::text) AS objname,
1656 l.provider,
1657 l.label
1658 FROM (pg_shseclabel l
1659 JOIN pg_subscription s ON (((l.classoid = s.tableoid) AND (l.objoid = s.oid))))
1660 UNION ALL
1661 SELECT l.objoid,
1662 l.classoid,
1663 0 AS objsubid,
1664 'database'::text AS objtype,
1665 NULL::oid AS objnamespace,
1666 quote_ident((dat.datname)::text) AS objname,
1667 l.provider,
1668 l.label
1669 FROM (pg_shseclabel l
1670 JOIN pg_database dat ON (((l.classoid = dat.tableoid) AND (l.objoid = dat.oid))))
1671 UNION ALL
1672 SELECT l.objoid,
1673 l.classoid,
1674 0 AS objsubid,
1675 'tablespace'::text AS objtype,
1676 NULL::oid AS objnamespace,
1677 quote_ident((spc.spcname)::text) AS objname,
1678 l.provider,
1679 l.label
1680 FROM (pg_shseclabel l
1681 JOIN pg_tablespace spc ON (((l.classoid = spc.tableoid) AND (l.objoid = spc.oid))))
1682 UNION ALL
1683 SELECT l.objoid,
1684 l.classoid,
1685 0 AS objsubid,
1686 'role'::text AS objtype,
1687 NULL::oid AS objnamespace,
1688 quote_ident((rol.rolname)::text) AS objname,
1689 l.provider,
1690 l.label
1691 FROM (pg_shseclabel l
1692 JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid))));
1693 pg_sequences| SELECT n.nspname AS schemaname,
1694 c.relname AS sequencename,
1695 pg_get_userbyid(c.relowner) AS sequenceowner,
1696 (s.seqtypid)::regtype AS data_type,
1697 s.seqstart AS start_value,
1698 s.seqmin AS min_value,
1699 s.seqmax AS max_value,
1700 s.seqincrement AS increment_by,
1701 s.seqcycle AS cycle,
1702 s.seqcache AS cache_size,
1703 pg_sequence_last_value((c.oid)::regclass) AS last_value
1704 FROM ((pg_sequence s
1705 JOIN pg_class c ON ((c.oid = s.seqrelid)))
1706 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
1707 WHERE ((NOT pg_is_other_temp_schema(n.oid)) AND (c.relkind = 'S'::"char"));
1708 pg_settings| SELECT name,
1709 setting,
1710 unit,
1711 category,
1712 short_desc,
1713 extra_desc,
1714 context,
1715 vartype,
1716 source,
1717 min_val,
1718 max_val,
1719 enumvals,
1720 boot_val,
1721 reset_val,
1722 sourcefile,
1723 sourceline,
1724 pending_restart
1725 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);
1726 pg_shadow| SELECT pg_authid.rolname AS usename,
1727 pg_authid.oid AS usesysid,
1728 pg_authid.rolcreatedb AS usecreatedb,
1729 pg_authid.rolsuper AS usesuper,
1730 pg_authid.rolreplication AS userepl,
1731 pg_authid.rolbypassrls AS usebypassrls,
1732 pg_authid.rolpassword AS passwd,
1733 pg_authid.rolvaliduntil AS valuntil,
1734 s.setconfig AS useconfig
1735 FROM (pg_authid
1736 LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))))
1737 WHERE pg_authid.rolcanlogin;
1738 pg_shmem_allocations| SELECT name,
1739 off,
1740 size,
1741 allocated_size
1742 FROM pg_get_shmem_allocations() pg_get_shmem_allocations(name, off, size, allocated_size);
1743 pg_stat_activity| SELECT s.datid,
1744 d.datname,
1745 s.pid,
1746 s.leader_pid,
1747 s.usesysid,
1748 u.rolname AS usename,
1749 s.application_name,
1750 s.client_addr,
1751 s.client_hostname,
1752 s.client_port,
1753 s.backend_start,
1754 s.xact_start,
1755 s.query_start,
1756 s.state_change,
1757 s.wait_event_type,
1758 s.wait_event,
1759 s.state,
1760 s.backend_xid,
1761 s.backend_xmin,
1762 s.query_id,
1763 s.query,
1764 s.backend_type
1765 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, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id)
1766 LEFT JOIN pg_database d ON ((s.datid = d.oid)))
1767 LEFT JOIN pg_authid u ON ((s.usesysid = u.oid)));
1768 pg_stat_all_indexes| SELECT c.oid AS relid,
1769 i.oid AS indexrelid,
1770 n.nspname AS schemaname,
1771 c.relname,
1772 i.relname AS indexrelname,
1773 pg_stat_get_numscans(i.oid) AS idx_scan,
1774 pg_stat_get_lastscan(i.oid) AS last_idx_scan,
1775 pg_stat_get_tuples_returned(i.oid) AS idx_tup_read,
1776 pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch
1777 FROM (((pg_class c
1778 JOIN pg_index x ON ((c.oid = x.indrelid)))
1779 JOIN pg_class i ON ((i.oid = x.indexrelid)))
1780 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
1781 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
1782 pg_stat_all_tables| SELECT c.oid AS relid,
1783 n.nspname AS schemaname,
1784 c.relname,
1785 pg_stat_get_numscans(c.oid) AS seq_scan,
1786 pg_stat_get_lastscan(c.oid) AS last_seq_scan,
1787 pg_stat_get_tuples_returned(c.oid) AS seq_tup_read,
1788 (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan,
1789 max(pg_stat_get_lastscan(i.indexrelid)) AS last_idx_scan,
1790 ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch,
1791 pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins,
1792 pg_stat_get_tuples_updated(c.oid) AS n_tup_upd,
1793 pg_stat_get_tuples_deleted(c.oid) AS n_tup_del,
1794 pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd,
1795 pg_stat_get_tuples_newpage_updated(c.oid) AS n_tup_newpage_upd,
1796 pg_stat_get_live_tuples(c.oid) AS n_live_tup,
1797 pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
1798 pg_stat_get_mod_since_analyze(c.oid) AS n_mod_since_analyze,
1799 pg_stat_get_ins_since_vacuum(c.oid) AS n_ins_since_vacuum,
1800 pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum,
1801 pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum,
1802 pg_stat_get_last_analyze_time(c.oid) AS last_analyze,
1803 pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze,
1804 pg_stat_get_vacuum_count(c.oid) AS vacuum_count,
1805 pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count,
1806 pg_stat_get_analyze_count(c.oid) AS analyze_count,
1807 pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count
1808 FROM ((pg_class c
1809 LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
1810 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
1811 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char", 'p'::"char"]))
1812 GROUP BY c.oid, n.nspname, c.relname;
1813 pg_stat_archiver| SELECT archived_count,
1814 last_archived_wal,
1815 last_archived_time,
1816 failed_count,
1817 last_failed_wal,
1818 last_failed_time,
1819 stats_reset
1820 FROM pg_stat_get_archiver() s(archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time, stats_reset);
1821 pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
1822 pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
1823 pg_stat_get_buf_alloc() AS buffers_alloc,
1824 pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
1825 pg_stat_checkpointer| SELECT pg_stat_get_checkpointer_num_timed() AS num_timed,
1826 pg_stat_get_checkpointer_num_requested() AS num_requested,
1827 pg_stat_get_checkpointer_num_performed() AS num_done,
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_slru_written() AS slru_written,
1835 pg_stat_get_checkpointer_stat_reset_time() AS stats_reset;
1836 pg_stat_database| SELECT oid AS datid,
1837 datname,
1838 CASE
1839 WHEN (oid = (0)::oid) THEN 0
1840 ELSE pg_stat_get_db_numbackends(oid)
1841 END AS numbackends,
1842 pg_stat_get_db_xact_commit(oid) AS xact_commit,
1843 pg_stat_get_db_xact_rollback(oid) AS xact_rollback,
1844 (pg_stat_get_db_blocks_fetched(oid) - pg_stat_get_db_blocks_hit(oid)) AS blks_read,
1845 pg_stat_get_db_blocks_hit(oid) AS blks_hit,
1846 pg_stat_get_db_tuples_returned(oid) AS tup_returned,
1847 pg_stat_get_db_tuples_fetched(oid) AS tup_fetched,
1848 pg_stat_get_db_tuples_inserted(oid) AS tup_inserted,
1849 pg_stat_get_db_tuples_updated(oid) AS tup_updated,
1850 pg_stat_get_db_tuples_deleted(oid) AS tup_deleted,
1851 pg_stat_get_db_conflict_all(oid) AS conflicts,
1852 pg_stat_get_db_temp_files(oid) AS temp_files,
1853 pg_stat_get_db_temp_bytes(oid) AS temp_bytes,
1854 pg_stat_get_db_deadlocks(oid) AS deadlocks,
1855 pg_stat_get_db_checksum_failures(oid) AS checksum_failures,
1856 pg_stat_get_db_checksum_last_failure(oid) AS checksum_last_failure,
1857 pg_stat_get_db_blk_read_time(oid) AS blk_read_time,
1858 pg_stat_get_db_blk_write_time(oid) AS blk_write_time,
1859 pg_stat_get_db_session_time(oid) AS session_time,
1860 pg_stat_get_db_active_time(oid) AS active_time,
1861 pg_stat_get_db_idle_in_transaction_time(oid) AS idle_in_transaction_time,
1862 pg_stat_get_db_sessions(oid) AS sessions,
1863 pg_stat_get_db_sessions_abandoned(oid) AS sessions_abandoned,
1864 pg_stat_get_db_sessions_fatal(oid) AS sessions_fatal,
1865 pg_stat_get_db_sessions_killed(oid) AS sessions_killed,
1866 pg_stat_get_db_parallel_workers_to_launch(oid) AS parallel_workers_to_launch,
1867 pg_stat_get_db_parallel_workers_launched(oid) AS parallel_workers_launched,
1868 pg_stat_get_db_stat_reset_time(oid) AS stats_reset
1869 FROM ( SELECT 0 AS oid,
1870 NULL::name AS datname
1871 UNION ALL
1872 SELECT pg_database.oid,
1873 pg_database.datname
1874 FROM pg_database) d;
1875 pg_stat_database_conflicts| SELECT oid AS datid,
1876 datname,
1877 pg_stat_get_db_conflict_tablespace(oid) AS confl_tablespace,
1878 pg_stat_get_db_conflict_lock(oid) AS confl_lock,
1879 pg_stat_get_db_conflict_snapshot(oid) AS confl_snapshot,
1880 pg_stat_get_db_conflict_bufferpin(oid) AS confl_bufferpin,
1881 pg_stat_get_db_conflict_startup_deadlock(oid) AS confl_deadlock,
1882 pg_stat_get_db_conflict_logicalslot(oid) AS confl_active_logicalslot
1883 FROM pg_database d;
1884 pg_stat_gssapi| SELECT pid,
1885 gss_auth AS gss_authenticated,
1886 gss_princ AS principal,
1887 gss_enc AS encrypted,
1888 gss_delegation AS credentials_delegated
1889 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, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id)
1890 WHERE (client_port IS NOT NULL);
1891 pg_stat_io| SELECT backend_type,
1892 object,
1893 context,
1894 reads,
1895 read_time,
1896 writes,
1897 write_time,
1898 writebacks,
1899 writeback_time,
1900 extends,
1901 extend_time,
1902 op_bytes,
1903 hits,
1904 evictions,
1905 reuses,
1906 fsyncs,
1907 fsync_time,
1908 stats_reset
1909 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);
1910 pg_stat_progress_analyze| SELECT s.pid,
1911 s.datid,
1912 d.datname,
1913 s.relid,
1914 CASE s.param1
1915 WHEN 0 THEN 'initializing'::text
1916 WHEN 1 THEN 'acquiring sample rows'::text
1917 WHEN 2 THEN 'acquiring inherited sample rows'::text
1918 WHEN 3 THEN 'computing statistics'::text
1919 WHEN 4 THEN 'computing extended statistics'::text
1920 WHEN 5 THEN 'finalizing analyze'::text
1921 ELSE NULL::text
1922 END AS phase,
1923 s.param2 AS sample_blks_total,
1924 s.param3 AS sample_blks_scanned,
1925 s.param4 AS ext_stats_total,
1926 s.param5 AS ext_stats_computed,
1927 s.param6 AS child_tables_total,
1928 s.param7 AS child_tables_done,
1929 (s.param8)::oid AS current_child_table_relid
1930 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)
1931 LEFT JOIN pg_database d ON ((s.datid = d.oid)));
1932 pg_stat_progress_basebackup| SELECT pid,
1933 CASE param1
1934 WHEN 0 THEN 'initializing'::text
1935 WHEN 1 THEN 'waiting for checkpoint to finish'::text
1936 WHEN 2 THEN 'estimating backup size'::text
1937 WHEN 3 THEN 'streaming database files'::text
1938 WHEN 4 THEN 'waiting for wal archiving to finish'::text
1939 WHEN 5 THEN 'transferring wal files'::text
1940 ELSE NULL::text
1941 END AS phase,
1942 CASE param2
1943 WHEN '-1'::integer THEN NULL::bigint
1944 ELSE param2
1945 END AS backup_total,
1946 param3 AS backup_streamed,
1947 param4 AS tablespaces_total,
1948 param5 AS tablespaces_streamed
1949 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);
1950 pg_stat_progress_cluster| SELECT s.pid,
1951 s.datid,
1952 d.datname,
1953 s.relid,
1954 CASE s.param1
1955 WHEN 1 THEN 'CLUSTER'::text
1956 WHEN 2 THEN 'VACUUM FULL'::text
1957 ELSE NULL::text
1958 END AS command,
1959 CASE s.param2
1960 WHEN 0 THEN 'initializing'::text
1961 WHEN 1 THEN 'seq scanning heap'::text
1962 WHEN 2 THEN 'index scanning heap'::text
1963 WHEN 3 THEN 'sorting tuples'::text
1964 WHEN 4 THEN 'writing new heap'::text
1965 WHEN 5 THEN 'swapping relation files'::text
1966 WHEN 6 THEN 'rebuilding index'::text
1967 WHEN 7 THEN 'performing final cleanup'::text
1968 ELSE NULL::text
1969 END AS phase,
1970 (s.param3)::oid AS cluster_index_relid,
1971 s.param4 AS heap_tuples_scanned,
1972 s.param5 AS heap_tuples_written,
1973 s.param6 AS heap_blks_total,
1974 s.param7 AS heap_blks_scanned,
1975 s.param8 AS index_rebuild_count
1976 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)
1977 LEFT JOIN pg_database d ON ((s.datid = d.oid)));
1978 pg_stat_progress_copy| SELECT s.pid,
1979 s.datid,
1980 d.datname,
1981 s.relid,
1982 CASE s.param5
1983 WHEN 1 THEN 'COPY FROM'::text
1984 WHEN 2 THEN 'COPY TO'::text
1985 ELSE NULL::text
1986 END AS command,
1987 CASE s.param6
1988 WHEN 1 THEN 'FILE'::text
1989 WHEN 2 THEN 'PROGRAM'::text
1990 WHEN 3 THEN 'PIPE'::text
1991 WHEN 4 THEN 'CALLBACK'::text
1992 ELSE NULL::text
1993 END AS type,
1994 s.param1 AS bytes_processed,
1995 s.param2 AS bytes_total,
1996 s.param3 AS tuples_processed,
1997 s.param4 AS tuples_excluded,
1998 s.param7 AS tuples_skipped
1999 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)
2000 LEFT JOIN pg_database d ON ((s.datid = d.oid)));
2001 pg_stat_progress_create_index| SELECT s.pid,
2002 s.datid,
2003 d.datname,
2004 s.relid,
2005 (s.param7)::oid AS index_relid,
2006 CASE s.param1
2007 WHEN 1 THEN 'CREATE INDEX'::text
2008 WHEN 2 THEN 'CREATE INDEX CONCURRENTLY'::text
2009 WHEN 3 THEN 'REINDEX'::text
2010 WHEN 4 THEN 'REINDEX CONCURRENTLY'::text
2011 ELSE NULL::text
2012 END AS command,
2013 CASE s.param10
2014 WHEN 0 THEN 'initializing'::text
2015 WHEN 1 THEN 'waiting for writers before build'::text
2016 WHEN 2 THEN ('building index'::text || COALESCE((': '::text || pg_indexam_progress_phasename((s.param9)::oid, s.param11)), ''::text))
2017 WHEN 3 THEN 'waiting for writers before validation'::text
2018 WHEN 4 THEN 'index validation: scanning index'::text
2019 WHEN 5 THEN 'index validation: sorting tuples'::text
2020 WHEN 6 THEN 'index validation: scanning table'::text
2021 WHEN 7 THEN 'waiting for old snapshots'::text
2022 WHEN 8 THEN 'waiting for readers before marking dead'::text
2023 WHEN 9 THEN 'waiting for readers before dropping'::text
2024 ELSE NULL::text
2025 END AS phase,
2026 s.param4 AS lockers_total,
2027 s.param5 AS lockers_done,
2028 s.param6 AS current_locker_pid,
2029 s.param16 AS blocks_total,
2030 s.param17 AS blocks_done,
2031 s.param12 AS tuples_total,
2032 s.param13 AS tuples_done,
2033 s.param14 AS partitions_total,
2034 s.param15 AS partitions_done
2035 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)
2036 LEFT JOIN pg_database d ON ((s.datid = d.oid)));
2037 pg_stat_progress_vacuum| SELECT s.pid,
2038 s.datid,
2039 d.datname,
2040 s.relid,
2041 CASE s.param1
2042 WHEN 0 THEN 'initializing'::text
2043 WHEN 1 THEN 'scanning heap'::text
2044 WHEN 2 THEN 'vacuuming indexes'::text
2045 WHEN 3 THEN 'vacuuming heap'::text
2046 WHEN 4 THEN 'cleaning up indexes'::text
2047 WHEN 5 THEN 'truncating heap'::text
2048 WHEN 6 THEN 'performing final cleanup'::text
2049 ELSE NULL::text
2050 END AS phase,
2051 s.param2 AS heap_blks_total,
2052 s.param3 AS heap_blks_scanned,
2053 s.param4 AS heap_blks_vacuumed,
2054 s.param5 AS index_vacuum_count,
2055 s.param6 AS max_dead_tuple_bytes,
2056 s.param7 AS dead_tuple_bytes,
2057 s.param8 AS num_dead_item_ids,
2058 s.param9 AS indexes_total,
2059 s.param10 AS indexes_processed
2060 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)
2061 LEFT JOIN pg_database d ON ((s.datid = d.oid)));
2062 pg_stat_recovery_prefetch| SELECT stats_reset,
2063 prefetch,
2064 hit,
2065 skip_init,
2066 skip_new,
2067 skip_fpw,
2068 skip_rep,
2069 wal_distance,
2070 block_distance,
2071 io_depth
2072 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);
2073 pg_stat_replication| SELECT s.pid,
2074 s.usesysid,
2075 u.rolname AS usename,
2076 s.application_name,
2077 s.client_addr,
2078 s.client_hostname,
2079 s.client_port,
2080 s.backend_start,
2081 s.backend_xmin,
2082 w.state,
2083 w.sent_lsn,
2084 w.write_lsn,
2085 w.flush_lsn,
2086 w.replay_lsn,
2087 w.write_lag,
2088 w.flush_lag,
2089 w.replay_lag,
2090 w.sync_priority,
2091 w.sync_state,
2092 w.reply_time
2093 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, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id)
2094 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)))
2095 LEFT JOIN pg_authid u ON ((s.usesysid = u.oid)));
2096 pg_stat_replication_slots| SELECT s.slot_name,
2097 s.spill_txns,
2098 s.spill_count,
2099 s.spill_bytes,
2100 s.stream_txns,
2101 s.stream_count,
2102 s.stream_bytes,
2103 s.total_txns,
2104 s.total_bytes,
2105 s.stats_reset
2106 FROM pg_replication_slots r,
2107 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)
2108 WHERE (r.datoid IS NOT NULL);
2109 pg_stat_slru| SELECT name,
2110 blks_zeroed,
2111 blks_hit,
2112 blks_read,
2113 blks_written,
2114 blks_exists,
2115 flushes,
2116 truncates,
2117 stats_reset
2118 FROM pg_stat_get_slru() s(name, blks_zeroed, blks_hit, blks_read, blks_written, blks_exists, flushes, truncates, stats_reset);
2119 pg_stat_ssl| SELECT pid,
2120 ssl,
2121 sslversion AS version,
2122 sslcipher AS cipher,
2123 sslbits AS bits,
2124 ssl_client_dn AS client_dn,
2125 ssl_client_serial AS client_serial,
2126 ssl_issuer_dn AS issuer_dn
2127 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, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id)
2128 WHERE (client_port IS NOT NULL);
2129 pg_stat_subscription| SELECT su.oid AS subid,
2130 su.subname,
2131 st.worker_type,
2132 st.pid,
2133 st.leader_pid,
2134 st.relid,
2135 st.received_lsn,
2136 st.last_msg_send_time,
2137 st.last_msg_receipt_time,
2138 st.latest_end_lsn,
2139 st.latest_end_time
2140 FROM (pg_subscription su
2141 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)));
2142 pg_stat_subscription_stats| SELECT ss.subid,
2143 s.subname,
2144 ss.apply_error_count,
2145 ss.sync_error_count,
2146 ss.confl_insert_exists,
2147 ss.confl_update_origin_differs,
2148 ss.confl_update_exists,
2149 ss.confl_update_missing,
2150 ss.confl_delete_origin_differs,
2151 ss.confl_delete_missing,
2152 ss.stats_reset
2153 FROM pg_subscription s,
2154 LATERAL pg_stat_get_subscription_stats(s.oid) ss(subid, apply_error_count, sync_error_count, confl_insert_exists, confl_update_origin_differs, confl_update_exists, confl_update_missing, confl_delete_origin_differs, confl_delete_missing, stats_reset);
2155 pg_stat_sys_indexes| SELECT relid,
2156 indexrelid,
2157 schemaname,
2158 relname,
2159 indexrelname,
2160 idx_scan,
2161 last_idx_scan,
2162 idx_tup_read,
2163 idx_tup_fetch
2164 FROM pg_stat_all_indexes
2165 WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
2166 pg_stat_sys_tables| SELECT relid,
2167 schemaname,
2168 relname,
2169 seq_scan,
2170 last_seq_scan,
2171 seq_tup_read,
2172 idx_scan,
2173 last_idx_scan,
2174 idx_tup_fetch,
2175 n_tup_ins,
2176 n_tup_upd,
2177 n_tup_del,
2178 n_tup_hot_upd,
2179 n_tup_newpage_upd,
2180 n_live_tup,
2181 n_dead_tup,
2182 n_mod_since_analyze,
2183 n_ins_since_vacuum,
2184 last_vacuum,
2185 last_autovacuum,
2186 last_analyze,
2187 last_autoanalyze,
2188 vacuum_count,
2189 autovacuum_count,
2190 analyze_count,
2191 autoanalyze_count
2192 FROM pg_stat_all_tables
2193 WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
2194 pg_stat_user_functions| SELECT p.oid AS funcid,
2195 n.nspname AS schemaname,
2196 p.proname AS funcname,
2197 pg_stat_get_function_calls(p.oid) AS calls,
2198 pg_stat_get_function_total_time(p.oid) AS total_time,
2199 pg_stat_get_function_self_time(p.oid) AS self_time
2200 FROM (pg_proc p
2201 LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace)))
2202 WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL));
2203 pg_stat_user_indexes| SELECT relid,
2204 indexrelid,
2205 schemaname,
2206 relname,
2207 indexrelname,
2208 idx_scan,
2209 last_idx_scan,
2210 idx_tup_read,
2211 idx_tup_fetch
2212 FROM pg_stat_all_indexes
2213 WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
2214 pg_stat_user_tables| SELECT relid,
2215 schemaname,
2216 relname,
2217 seq_scan,
2218 last_seq_scan,
2219 seq_tup_read,
2220 idx_scan,
2221 last_idx_scan,
2222 idx_tup_fetch,
2223 n_tup_ins,
2224 n_tup_upd,
2225 n_tup_del,
2226 n_tup_hot_upd,
2227 n_tup_newpage_upd,
2228 n_live_tup,
2229 n_dead_tup,
2230 n_mod_since_analyze,
2231 n_ins_since_vacuum,
2232 last_vacuum,
2233 last_autovacuum,
2234 last_analyze,
2235 last_autoanalyze,
2236 vacuum_count,
2237 autovacuum_count,
2238 analyze_count,
2239 autoanalyze_count
2240 FROM pg_stat_all_tables
2241 WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
2242 pg_stat_wal| SELECT wal_records,
2243 wal_fpi,
2244 wal_bytes,
2245 wal_buffers_full,
2246 wal_write,
2247 wal_sync,
2248 wal_write_time,
2249 wal_sync_time,
2250 stats_reset
2251 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);
2252 pg_stat_wal_receiver| SELECT pid,
2253 status,
2254 receive_start_lsn,
2255 receive_start_tli,
2256 written_lsn,
2257 flushed_lsn,
2258 received_tli,
2259 last_msg_send_time,
2260 last_msg_receipt_time,
2261 latest_end_lsn,
2262 latest_end_time,
2263 slot_name,
2264 sender_host,
2265 sender_port,
2266 conninfo
2267 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)
2268 WHERE (pid IS NOT NULL);
2269 pg_stat_xact_all_tables| SELECT c.oid AS relid,
2270 n.nspname AS schemaname,
2271 c.relname,
2272 pg_stat_get_xact_numscans(c.oid) AS seq_scan,
2273 pg_stat_get_xact_tuples_returned(c.oid) AS seq_tup_read,
2274 (sum(pg_stat_get_xact_numscans(i.indexrelid)))::bigint AS idx_scan,
2275 ((sum(pg_stat_get_xact_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_xact_tuples_fetched(c.oid)) AS idx_tup_fetch,
2276 pg_stat_get_xact_tuples_inserted(c.oid) AS n_tup_ins,
2277 pg_stat_get_xact_tuples_updated(c.oid) AS n_tup_upd,
2278 pg_stat_get_xact_tuples_deleted(c.oid) AS n_tup_del,
2279 pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd,
2280 pg_stat_get_xact_tuples_newpage_updated(c.oid) AS n_tup_newpage_upd
2281 FROM ((pg_class c
2282 LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
2283 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
2284 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char", 'p'::"char"]))
2285 GROUP BY c.oid, n.nspname, c.relname;
2286 pg_stat_xact_sys_tables| SELECT relid,
2287 schemaname,
2288 relname,
2289 seq_scan,
2290 seq_tup_read,
2291 idx_scan,
2292 idx_tup_fetch,
2293 n_tup_ins,
2294 n_tup_upd,
2295 n_tup_del,
2296 n_tup_hot_upd,
2297 n_tup_newpage_upd
2298 FROM pg_stat_xact_all_tables
2299 WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
2300 pg_stat_xact_user_functions| SELECT p.oid AS funcid,
2301 n.nspname AS schemaname,
2302 p.proname AS funcname,
2303 pg_stat_get_xact_function_calls(p.oid) AS calls,
2304 pg_stat_get_xact_function_total_time(p.oid) AS total_time,
2305 pg_stat_get_xact_function_self_time(p.oid) AS self_time
2306 FROM (pg_proc p
2307 LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace)))
2308 WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_xact_function_calls(p.oid) IS NOT NULL));
2309 pg_stat_xact_user_tables| SELECT relid,
2310 schemaname,
2311 relname,
2312 seq_scan,
2313 seq_tup_read,
2314 idx_scan,
2315 idx_tup_fetch,
2316 n_tup_ins,
2317 n_tup_upd,
2318 n_tup_del,
2319 n_tup_hot_upd,
2320 n_tup_newpage_upd
2321 FROM pg_stat_xact_all_tables
2322 WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
2323 pg_statio_all_indexes| SELECT c.oid AS relid,
2324 i.oid AS indexrelid,
2325 n.nspname AS schemaname,
2326 c.relname,
2327 i.relname AS indexrelname,
2328 (pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read,
2329 pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit
2330 FROM (((pg_class c
2331 JOIN pg_index x ON ((c.oid = x.indrelid)))
2332 JOIN pg_class i ON ((i.oid = x.indexrelid)))
2333 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
2334 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
2335 pg_statio_all_sequences| 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 blks_read,
2339 pg_stat_get_blocks_hit(c.oid) AS blks_hit
2340 FROM (pg_class c
2341 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
2342 WHERE (c.relkind = 'S'::"char");
2343 pg_statio_all_tables| SELECT c.oid AS relid,
2344 n.nspname AS schemaname,
2345 c.relname,
2346 (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS heap_blks_read,
2347 pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit,
2348 i.idx_blks_read,
2349 i.idx_blks_hit,
2350 (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read,
2351 pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit,
2352 x.idx_blks_read AS tidx_blks_read,
2353 x.idx_blks_hit AS tidx_blks_hit
2354 FROM ((((pg_class c
2355 LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid)))
2356 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
2357 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,
2358 (sum(pg_stat_get_blocks_hit(pg_index.indexrelid)))::bigint AS idx_blks_hit
2359 FROM pg_index
2360 WHERE (pg_index.indrelid = c.oid)) i ON (true))
2361 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,
2362 (sum(pg_stat_get_blocks_hit(pg_index.indexrelid)))::bigint AS idx_blks_hit
2363 FROM pg_index
2364 WHERE (pg_index.indrelid = t.oid)) x ON (true))
2365 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
2366 pg_statio_sys_indexes| SELECT relid,
2367 indexrelid,
2368 schemaname,
2369 relname,
2370 indexrelname,
2371 idx_blks_read,
2372 idx_blks_hit
2373 FROM pg_statio_all_indexes
2374 WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
2375 pg_statio_sys_sequences| SELECT relid,
2376 schemaname,
2377 relname,
2378 blks_read,
2379 blks_hit
2380 FROM pg_statio_all_sequences
2381 WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
2382 pg_statio_sys_tables| SELECT relid,
2383 schemaname,
2384 relname,
2385 heap_blks_read,
2386 heap_blks_hit,
2387 idx_blks_read,
2388 idx_blks_hit,
2389 toast_blks_read,
2390 toast_blks_hit,
2391 tidx_blks_read,
2392 tidx_blks_hit
2393 FROM pg_statio_all_tables
2394 WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
2395 pg_statio_user_indexes| SELECT relid,
2396 indexrelid,
2397 schemaname,
2398 relname,
2399 indexrelname,
2400 idx_blks_read,
2401 idx_blks_hit
2402 FROM pg_statio_all_indexes
2403 WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
2404 pg_statio_user_sequences| SELECT relid,
2405 schemaname,
2406 relname,
2407 blks_read,
2408 blks_hit
2409 FROM pg_statio_all_sequences
2410 WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
2411 pg_statio_user_tables| SELECT relid,
2412 schemaname,
2413 relname,
2414 heap_blks_read,
2415 heap_blks_hit,
2416 idx_blks_read,
2417 idx_blks_hit,
2418 toast_blks_read,
2419 toast_blks_hit,
2420 tidx_blks_read,
2421 tidx_blks_hit
2422 FROM pg_statio_all_tables
2423 WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
2424 pg_stats| SELECT n.nspname AS schemaname,
2425 c.relname AS tablename,
2426 a.attname,
2427 s.stainherit AS inherited,
2428 s.stanullfrac AS null_frac,
2429 s.stawidth AS avg_width,
2430 s.stadistinct AS n_distinct,
2431 CASE
2432 WHEN (s.stakind1 = 1) THEN s.stavalues1
2433 WHEN (s.stakind2 = 1) THEN s.stavalues2
2434 WHEN (s.stakind3 = 1) THEN s.stavalues3
2435 WHEN (s.stakind4 = 1) THEN s.stavalues4
2436 WHEN (s.stakind5 = 1) THEN s.stavalues5
2437 ELSE NULL::anyarray
2438 END AS most_common_vals,
2439 CASE
2440 WHEN (s.stakind1 = 1) THEN s.stanumbers1
2441 WHEN (s.stakind2 = 1) THEN s.stanumbers2
2442 WHEN (s.stakind3 = 1) THEN s.stanumbers3
2443 WHEN (s.stakind4 = 1) THEN s.stanumbers4
2444 WHEN (s.stakind5 = 1) THEN s.stanumbers5
2445 ELSE NULL::real[]
2446 END AS most_common_freqs,
2447 CASE
2448 WHEN (s.stakind1 = 2) THEN s.stavalues1
2449 WHEN (s.stakind2 = 2) THEN s.stavalues2
2450 WHEN (s.stakind3 = 2) THEN s.stavalues3
2451 WHEN (s.stakind4 = 2) THEN s.stavalues4
2452 WHEN (s.stakind5 = 2) THEN s.stavalues5
2453 ELSE NULL::anyarray
2454 END AS histogram_bounds,
2455 CASE
2456 WHEN (s.stakind1 = 3) THEN s.stanumbers1[1]
2457 WHEN (s.stakind2 = 3) THEN s.stanumbers2[1]
2458 WHEN (s.stakind3 = 3) THEN s.stanumbers3[1]
2459 WHEN (s.stakind4 = 3) THEN s.stanumbers4[1]
2460 WHEN (s.stakind5 = 3) THEN s.stanumbers5[1]
2461 ELSE NULL::real
2462 END AS correlation,
2463 CASE
2464 WHEN (s.stakind1 = 4) THEN s.stavalues1
2465 WHEN (s.stakind2 = 4) THEN s.stavalues2
2466 WHEN (s.stakind3 = 4) THEN s.stavalues3
2467 WHEN (s.stakind4 = 4) THEN s.stavalues4
2468 WHEN (s.stakind5 = 4) THEN s.stavalues5
2469 ELSE NULL::anyarray
2470 END AS most_common_elems,
2471 CASE
2472 WHEN (s.stakind1 = 4) THEN s.stanumbers1
2473 WHEN (s.stakind2 = 4) THEN s.stanumbers2
2474 WHEN (s.stakind3 = 4) THEN s.stanumbers3
2475 WHEN (s.stakind4 = 4) THEN s.stanumbers4
2476 WHEN (s.stakind5 = 4) THEN s.stanumbers5
2477 ELSE NULL::real[]
2478 END AS most_common_elem_freqs,
2479 CASE
2480 WHEN (s.stakind1 = 5) THEN s.stanumbers1
2481 WHEN (s.stakind2 = 5) THEN s.stanumbers2
2482 WHEN (s.stakind3 = 5) THEN s.stanumbers3
2483 WHEN (s.stakind4 = 5) THEN s.stanumbers4
2484 WHEN (s.stakind5 = 5) THEN s.stanumbers5
2485 ELSE NULL::real[]
2486 END AS elem_count_histogram,
2487 CASE
2488 WHEN (s.stakind1 = 6) THEN s.stavalues1
2489 WHEN (s.stakind2 = 6) THEN s.stavalues2
2490 WHEN (s.stakind3 = 6) THEN s.stavalues3
2491 WHEN (s.stakind4 = 6) THEN s.stavalues4
2492 WHEN (s.stakind5 = 6) THEN s.stavalues5
2493 ELSE NULL::anyarray
2494 END AS range_length_histogram,
2495 CASE
2496 WHEN (s.stakind1 = 6) THEN s.stanumbers1[1]
2497 WHEN (s.stakind2 = 6) THEN s.stanumbers2[1]
2498 WHEN (s.stakind3 = 6) THEN s.stanumbers3[1]
2499 WHEN (s.stakind4 = 6) THEN s.stanumbers4[1]
2500 WHEN (s.stakind5 = 6) THEN s.stanumbers5[1]
2501 ELSE NULL::real
2502 END AS range_empty_frac,
2503 CASE
2504 WHEN (s.stakind1 = 7) THEN s.stavalues1
2505 WHEN (s.stakind2 = 7) THEN s.stavalues2
2506 WHEN (s.stakind3 = 7) THEN s.stavalues3
2507 WHEN (s.stakind4 = 7) THEN s.stavalues4
2508 WHEN (s.stakind5 = 7) THEN s.stavalues5
2509 ELSE NULL::anyarray
2510 END AS range_bounds_histogram
2511 FROM (((pg_statistic s
2512 JOIN pg_class c ON ((c.oid = s.starelid)))
2513 JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum))))
2514 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
2515 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))));
2516 pg_stats_ext| SELECT cn.nspname AS schemaname,
2517 c.relname AS tablename,
2518 sn.nspname AS statistics_schemaname,
2519 s.stxname AS statistics_name,
2520 pg_get_userbyid(s.stxowner) AS statistics_owner,
2521 ( SELECT array_agg(a.attname ORDER BY a.attnum) AS array_agg
2522 FROM (unnest(s.stxkeys) k(k)
2523 JOIN pg_attribute a ON (((a.attrelid = s.stxrelid) AND (a.attnum = k.k))))) AS attnames,
2524 pg_get_statisticsobjdef_expressions(s.oid) AS exprs,
2525 s.stxkind AS kinds,
2526 sd.stxdinherit AS inherited,
2527 sd.stxdndistinct AS n_distinct,
2528 sd.stxddependencies AS dependencies,
2529 m.most_common_vals,
2530 m.most_common_val_nulls,
2531 m.most_common_freqs,
2532 m.most_common_base_freqs
2533 FROM (((((pg_statistic_ext s
2534 JOIN pg_class c ON ((c.oid = s.stxrelid)))
2535 JOIN pg_statistic_ext_data sd ON ((s.oid = sd.stxoid)))
2536 LEFT JOIN pg_namespace cn ON ((cn.oid = c.relnamespace)))
2537 LEFT JOIN pg_namespace sn ON ((sn.oid = s.stxnamespace)))
2538 LEFT JOIN LATERAL ( SELECT array_agg(pg_mcv_list_items."values") AS most_common_vals,
2539 array_agg(pg_mcv_list_items.nulls) AS most_common_val_nulls,
2540 array_agg(pg_mcv_list_items.frequency) AS most_common_freqs,
2541 array_agg(pg_mcv_list_items.base_frequency) AS most_common_base_freqs
2542 FROM pg_mcv_list_items(sd.stxdmcv) pg_mcv_list_items(index, "values", nulls, frequency, base_frequency)) m ON ((sd.stxdmcv IS NOT NULL)))
2543 WHERE (pg_has_role(c.relowner, 'USAGE'::text) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid))));
2544 pg_stats_ext_exprs| SELECT cn.nspname AS schemaname,
2545 c.relname AS tablename,
2546 sn.nspname AS statistics_schemaname,
2547 s.stxname AS statistics_name,
2548 pg_get_userbyid(s.stxowner) AS statistics_owner,
2549 stat.expr,
2550 sd.stxdinherit AS inherited,
2551 (stat.a).stanullfrac AS null_frac,
2552 (stat.a).stawidth AS avg_width,
2553 (stat.a).stadistinct AS n_distinct,
2554 CASE
2555 WHEN ((stat.a).stakind1 = 1) THEN (stat.a).stavalues1
2556 WHEN ((stat.a).stakind2 = 1) THEN (stat.a).stavalues2
2557 WHEN ((stat.a).stakind3 = 1) THEN (stat.a).stavalues3
2558 WHEN ((stat.a).stakind4 = 1) THEN (stat.a).stavalues4
2559 WHEN ((stat.a).stakind5 = 1) THEN (stat.a).stavalues5
2560 ELSE NULL::anyarray
2561 END AS most_common_vals,
2562 CASE
2563 WHEN ((stat.a).stakind1 = 1) THEN (stat.a).stanumbers1
2564 WHEN ((stat.a).stakind2 = 1) THEN (stat.a).stanumbers2
2565 WHEN ((stat.a).stakind3 = 1) THEN (stat.a).stanumbers3
2566 WHEN ((stat.a).stakind4 = 1) THEN (stat.a).stanumbers4
2567 WHEN ((stat.a).stakind5 = 1) THEN (stat.a).stanumbers5
2568 ELSE NULL::real[]
2569 END AS most_common_freqs,
2570 CASE
2571 WHEN ((stat.a).stakind1 = 2) THEN (stat.a).stavalues1
2572 WHEN ((stat.a).stakind2 = 2) THEN (stat.a).stavalues2
2573 WHEN ((stat.a).stakind3 = 2) THEN (stat.a).stavalues3
2574 WHEN ((stat.a).stakind4 = 2) THEN (stat.a).stavalues4
2575 WHEN ((stat.a).stakind5 = 2) THEN (stat.a).stavalues5
2576 ELSE NULL::anyarray
2577 END AS histogram_bounds,
2578 CASE
2579 WHEN ((stat.a).stakind1 = 3) THEN (stat.a).stanumbers1[1]
2580 WHEN ((stat.a).stakind2 = 3) THEN (stat.a).stanumbers2[1]
2581 WHEN ((stat.a).stakind3 = 3) THEN (stat.a).stanumbers3[1]
2582 WHEN ((stat.a).stakind4 = 3) THEN (stat.a).stanumbers4[1]
2583 WHEN ((stat.a).stakind5 = 3) THEN (stat.a).stanumbers5[1]
2584 ELSE NULL::real
2585 END AS correlation,
2586 CASE
2587 WHEN ((stat.a).stakind1 = 4) THEN (stat.a).stavalues1
2588 WHEN ((stat.a).stakind2 = 4) THEN (stat.a).stavalues2
2589 WHEN ((stat.a).stakind3 = 4) THEN (stat.a).stavalues3
2590 WHEN ((stat.a).stakind4 = 4) THEN (stat.a).stavalues4
2591 WHEN ((stat.a).stakind5 = 4) THEN (stat.a).stavalues5
2592 ELSE NULL::anyarray
2593 END AS most_common_elems,
2594 CASE
2595 WHEN ((stat.a).stakind1 = 4) THEN (stat.a).stanumbers1
2596 WHEN ((stat.a).stakind2 = 4) THEN (stat.a).stanumbers2
2597 WHEN ((stat.a).stakind3 = 4) THEN (stat.a).stanumbers3
2598 WHEN ((stat.a).stakind4 = 4) THEN (stat.a).stanumbers4
2599 WHEN ((stat.a).stakind5 = 4) THEN (stat.a).stanumbers5
2600 ELSE NULL::real[]
2601 END AS most_common_elem_freqs,
2602 CASE
2603 WHEN ((stat.a).stakind1 = 5) THEN (stat.a).stanumbers1
2604 WHEN ((stat.a).stakind2 = 5) THEN (stat.a).stanumbers2
2605 WHEN ((stat.a).stakind3 = 5) THEN (stat.a).stanumbers3
2606 WHEN ((stat.a).stakind4 = 5) THEN (stat.a).stanumbers4
2607 WHEN ((stat.a).stakind5 = 5) THEN (stat.a).stanumbers5
2608 ELSE NULL::real[]
2609 END AS elem_count_histogram
2610 FROM (((((pg_statistic_ext s
2611 JOIN pg_class c ON ((c.oid = s.stxrelid)))
2612 LEFT JOIN pg_statistic_ext_data sd ON ((s.oid = sd.stxoid)))
2613 LEFT JOIN pg_namespace cn ON ((cn.oid = c.relnamespace)))
2614 LEFT JOIN pg_namespace sn ON ((sn.oid = s.stxnamespace)))
2615 JOIN LATERAL ( SELECT unnest(pg_get_statisticsobjdef_expressions(s.oid)) AS expr,
2616 unnest(sd.stxdexpr) AS a) stat ON ((stat.expr IS NOT NULL)))
2617 WHERE (pg_has_role(c.relowner, 'USAGE'::text) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid))));
2618 pg_tables| SELECT n.nspname AS schemaname,
2619 c.relname AS tablename,
2620 pg_get_userbyid(c.relowner) AS tableowner,
2621 t.spcname AS tablespace,
2622 c.relhasindex AS hasindexes,
2623 c.relhasrules AS hasrules,
2624 c.relhastriggers AS hastriggers,
2625 c.relrowsecurity AS rowsecurity
2626 FROM ((pg_class c
2627 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
2628 LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace)))
2629 WHERE (c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"]));
2630 pg_timezone_abbrevs| SELECT abbrev,
2631 utc_offset,
2632 is_dst
2633 FROM pg_timezone_abbrevs() pg_timezone_abbrevs(abbrev, utc_offset, is_dst);
2634 pg_timezone_names| SELECT name,
2635 abbrev,
2636 utc_offset,
2637 is_dst
2638 FROM pg_timezone_names() pg_timezone_names(name, abbrev, utc_offset, is_dst);
2639 pg_user| SELECT usename,
2640 usesysid,
2641 usecreatedb,
2642 usesuper,
2643 userepl,
2644 usebypassrls,
2645 '********'::text AS passwd,
2646 valuntil,
2647 useconfig
2648 FROM pg_shadow;
2649 pg_user_mappings| SELECT u.oid AS umid,
2650 s.oid AS srvid,
2651 s.srvname,
2652 u.umuser,
2653 CASE
2654 WHEN (u.umuser = (0)::oid) THEN 'public'::name
2655 ELSE a.rolname
2656 END AS usename,
2657 CASE
2658 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
2659 FROM pg_authid
2660 WHERE (pg_authid.rolname = CURRENT_USER))) THEN u.umoptions
2661 ELSE NULL::text[]
2662 END AS umoptions
2663 FROM ((pg_user_mapping u
2664 JOIN pg_foreign_server s ON ((u.umserver = s.oid)))
2665 LEFT JOIN pg_authid a ON ((a.oid = u.umuser)));
2666 pg_views| SELECT n.nspname AS schemaname,
2667 c.relname AS viewname,
2668 pg_get_userbyid(c.relowner) AS viewowner,
2669 pg_get_viewdef(c.oid) AS definition
2670 FROM (pg_class c
2671 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
2672 WHERE (c.relkind = 'v'::"char");
2673 pg_wait_events| SELECT type,
2674 name,
2675 description
2676 FROM pg_get_wait_events() pg_get_wait_events(type, name, description);
2677 SELECT tablename, rulename, definition FROM pg_rules
2678 WHERE schemaname = 'pg_catalog'
2679 ORDER BY tablename, rulename;
2680 pg_settings|pg_settings_n|CREATE RULE pg_settings_n AS
2681 ON UPDATE TO pg_catalog.pg_settings DO INSTEAD NOTHING;
2682 pg_settings|pg_settings_u|CREATE RULE pg_settings_u AS
2683 ON UPDATE TO pg_catalog.pg_settings
2684 WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, false) AS set_config;
2685 -- restore normal output mode
2686 \a\t
2687 --
2688 -- CREATE OR REPLACE RULE
2689 --
2690 CREATE TABLE ruletest_tbl (a int, b int);
2691 CREATE TABLE ruletest_tbl2 (a int, b int);
2692 CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl
2693 DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (10, 10);
2694 INSERT INTO ruletest_tbl VALUES (99, 99);
2695 CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl
2696 DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (1000, 1000);
2697 INSERT INTO ruletest_tbl VALUES (99, 99);
2698 SELECT * FROM ruletest_tbl2;
2699 a | b
2700 ------+------
2701 10 | 10
2702 1000 | 1000
2703 (2 rows)
2705 -- Check that rewrite rules splitting one INSERT into multiple
2706 -- conditional statements does not disable FK checking.
2707 create table rule_and_refint_t1 (
2708 id1a integer,
2709 id1b integer,
2710 primary key (id1a, id1b)
2711 );
2712 create table rule_and_refint_t2 (
2713 id2a integer,
2714 id2c integer,
2715 primary key (id2a, id2c)
2716 );
2717 create table rule_and_refint_t3 (
2718 id3a integer,
2719 id3b integer,
2720 id3c integer,
2721 data text,
2722 primary key (id3a, id3b, id3c),
2723 foreign key (id3a, id3b) references rule_and_refint_t1 (id1a, id1b),
2724 foreign key (id3a, id3c) references rule_and_refint_t2 (id2a, id2c)
2725 );
2726 insert into rule_and_refint_t1 values (1, 11);
2727 insert into rule_and_refint_t1 values (1, 12);
2728 insert into rule_and_refint_t1 values (2, 21);
2729 insert into rule_and_refint_t1 values (2, 22);
2730 insert into rule_and_refint_t2 values (1, 11);
2731 insert into rule_and_refint_t2 values (1, 12);
2732 insert into rule_and_refint_t2 values (2, 21);
2733 insert into rule_and_refint_t2 values (2, 22);
2734 insert into rule_and_refint_t3 values (1, 11, 11, 'row1');
2735 insert into rule_and_refint_t3 values (1, 11, 12, 'row2');
2736 insert into rule_and_refint_t3 values (1, 12, 11, 'row3');
2737 insert into rule_and_refint_t3 values (1, 12, 12, 'row4');
2738 insert into rule_and_refint_t3 values (1, 11, 13, 'row5');
2739 ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_id3c_fkey"
2740 DETAIL: Key (id3a, id3c)=(1, 13) is not present in table "rule_and_refint_t2".
2741 insert into rule_and_refint_t3 values (1, 13, 11, 'row6');
2742 ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_id3b_fkey"
2743 DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1".
2744 -- Ordinary table
2745 insert into rule_and_refint_t3 values (1, 13, 11, 'row6')
2746 on conflict do nothing;
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 not fired, so fk violation
2750 insert into rule_and_refint_t3 values (1, 13, 11, 'row6')
2751 on conflict (id3a, id3b, id3c) do update
2752 set id3b = excluded.id3b;
2753 ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_id3b_fkey"
2754 DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1".
2755 -- rule fired, so unsupported
2756 insert into shoelace values ('sl9', 0, 'pink', 35.0, 'inch', 0.0)
2757 on conflict (sl_name) do update
2758 set sl_avail = excluded.sl_avail;
2759 ERROR: INSERT with ON CONFLICT clause cannot be used with table that has INSERT or UPDATE rules
2760 create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3
2761 where (exists (select 1 from rule_and_refint_t3
2762 where (((rule_and_refint_t3.id3a = new.id3a)
2763 and (rule_and_refint_t3.id3b = new.id3b))
2764 and (rule_and_refint_t3.id3c = new.id3c))))
2765 do instead update rule_and_refint_t3 set data = new.data
2766 where (((rule_and_refint_t3.id3a = new.id3a)
2767 and (rule_and_refint_t3.id3b = new.id3b))
2768 and (rule_and_refint_t3.id3c = new.id3c));
2769 insert into rule_and_refint_t3 values (1, 11, 13, 'row7');
2770 ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_id3c_fkey"
2771 DETAIL: Key (id3a, id3c)=(1, 13) is not present in table "rule_and_refint_t2".
2772 insert into rule_and_refint_t3 values (1, 13, 11, 'row8');
2773 ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_id3b_fkey"
2774 DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1".
2775 --
2776 -- disallow dropping a view's rule (bug #5072)
2777 --
2778 create view rules_fooview as select 'rules_foo'::text;
2779 drop rule "_RETURN" on rules_fooview;
2780 ERROR: cannot drop rule _RETURN on view rules_fooview because view rules_fooview requires it
2781 HINT: You can drop view rules_fooview instead.
2782 drop view rules_fooview;
2783 --
2784 -- We used to allow converting a table to a view by creating a "_RETURN"
2785 -- rule for it, but no more.
2786 --
2787 create table rules_fooview (x int, y text);
2788 create rule "_RETURN" as on select to rules_fooview do instead
2789 select 1 as x, 'aaa'::text as y;
2790 ERROR: relation "rules_fooview" cannot have ON SELECT rules
2791 DETAIL: This operation is not supported for tables.
2792 drop table rules_fooview;
2793 -- likewise, converting a partitioned table or partition to view is not allowed
2794 create table rules_fooview (x int, y text) partition by list (x);
2795 create rule "_RETURN" as on select to rules_fooview do instead
2796 select 1 as x, 'aaa'::text as y;
2797 ERROR: relation "rules_fooview" cannot have ON SELECT rules
2798 DETAIL: This operation is not supported for partitioned tables.
2799 create table rules_fooview_part partition of rules_fooview for values in (1);
2800 create rule "_RETURN" as on select to rules_fooview_part do instead
2801 select 1 as x, 'aaa'::text as y;
2802 ERROR: relation "rules_fooview_part" cannot have ON SELECT rules
2803 DETAIL: This operation is not supported for tables.
2804 drop table rules_fooview;
2805 --
2806 -- check for planner problems with complex inherited UPDATES
2807 --
2808 create table id (id serial primary key, name text);
2809 -- currently, must respecify PKEY for each inherited subtable
2810 create table test_1 (id integer primary key) inherits (id);
2811 NOTICE: merging column "id" with inherited definition
2812 create table test_2 (id integer primary key) inherits (id);
2813 NOTICE: merging column "id" with inherited definition
2814 create table test_3 (id integer primary key) inherits (id);
2815 NOTICE: merging column "id" with inherited definition
2816 insert into test_1 (name) values ('Test 1');
2817 insert into test_1 (name) values ('Test 2');
2818 insert into test_2 (name) values ('Test 3');
2819 insert into test_2 (name) values ('Test 4');
2820 insert into test_3 (name) values ('Test 5');
2821 insert into test_3 (name) values ('Test 6');
2822 create view id_ordered as select * from id order by id;
2823 create rule update_id_ordered as on update to id_ordered
2824 do instead update id set name = new.name where id = old.id;
2825 select * from id_ordered;
2826 id | name
2827 ----+--------
2828 1 | Test 1
2829 2 | Test 2
2830 3 | Test 3
2831 4 | Test 4
2832 5 | Test 5
2833 6 | Test 6
2834 (6 rows)
2836 update id_ordered set name = 'update 2' where id = 2;
2837 update id_ordered set name = 'update 4' where id = 4;
2838 update id_ordered set name = 'update 5' where id = 5;
2839 select * from id_ordered;
2840 id | name
2841 ----+----------
2842 1 | Test 1
2843 2 | update 2
2844 3 | Test 3
2845 4 | update 4
2846 5 | update 5
2847 6 | Test 6
2848 (6 rows)
2850 drop table id cascade;
2851 NOTICE: drop cascades to 4 other objects
2852 DETAIL: drop cascades to table test_1
2853 drop cascades to table test_2
2854 drop cascades to table test_3
2855 drop cascades to view id_ordered
2856 --
2857 -- check corner case where an entirely-dummy subplan is created by
2858 -- constraint exclusion
2859 --
2860 create temp table t1 (a integer primary key);
2861 create temp table t1_1 (check (a >= 0 and a < 10)) inherits (t1);
2862 create temp table t1_2 (check (a >= 10 and a < 20)) inherits (t1);
2863 create rule t1_ins_1 as on insert to t1
2864 where new.a >= 0 and new.a < 10
2865 do instead
2866 insert into t1_1 values (new.a);
2867 create rule t1_ins_2 as on insert to t1
2868 where new.a >= 10 and new.a < 20
2869 do instead
2870 insert into t1_2 values (new.a);
2871 create rule t1_upd_1 as on update to t1
2872 where old.a >= 0 and old.a < 10
2873 do instead
2874 update t1_1 set a = new.a where a = old.a;
2875 create rule t1_upd_2 as on update to t1
2876 where old.a >= 10 and old.a < 20
2877 do instead
2878 update t1_2 set a = new.a where a = old.a;
2879 set constraint_exclusion = on;
2880 insert into t1 select * from generate_series(5,19,1) g;
2881 update t1 set a = 4 where a = 5;
2882 select * from only t1;
2883 a
2884 ---
2885 (0 rows)
2887 select * from only t1_1;
2888 a
2889 ---
2890 6
2891 7
2892 8
2893 9
2894 4
2895 (5 rows)
2897 select * from only t1_2;
2898 a
2899 ----
2900 10
2901 11
2902 12
2903 13
2904 14
2905 15
2906 16
2907 17
2908 18
2909 19
2910 (10 rows)
2912 reset constraint_exclusion;
2913 -- test FOR UPDATE in rules
2914 create table rules_base(f1 int, f2 int);
2915 insert into rules_base values(1,2), (11,12);
2916 create rule r1 as on update to rules_base do instead
2917 select * from rules_base where f1 = 1 for update;
2918 update rules_base set f2 = f2 + 1;
2919 f1 | f2
2920 ----+----
2921 1 | 2
2922 (1 row)
2924 create or replace rule r1 as on update to rules_base do instead
2925 select * from rules_base where f1 = 11 for update of rules_base;
2926 update rules_base set f2 = f2 + 1;
2927 f1 | f2
2928 ----+----
2929 11 | 12
2930 (1 row)
2932 create or replace rule r1 as on update to rules_base do instead
2933 select * from rules_base where f1 = 11 for update of old; -- error
2934 ERROR: relation "old" in FOR UPDATE clause not found in FROM clause
2935 LINE 2: select * from rules_base where f1 = 11 for update of old;
2936 ^
2937 drop table rules_base;
2938 -- test various flavors of pg_get_viewdef()
2939 select pg_get_viewdef('shoe'::regclass) as unpretty;
2940 unpretty
2941 ------------------------------------------------
2942 SELECT sh.shoename, +
2943 sh.sh_avail, +
2944 sh.slcolor, +
2945 sh.slminlen, +
2946 (sh.slminlen * un.un_fact) AS slminlen_cm,+
2947 sh.slmaxlen, +
2948 (sh.slmaxlen * un.un_fact) AS slmaxlen_cm,+
2949 sh.slunit +
2950 FROM shoe_data sh, +
2951 unit un +
2952 WHERE (sh.slunit = un.un_name);
2953 (1 row)
2955 select pg_get_viewdef('shoe'::regclass,true) as pretty;
2956 pretty
2957 ----------------------------------------------
2958 SELECT sh.shoename, +
2959 sh.sh_avail, +
2960 sh.slcolor, +
2961 sh.slminlen, +
2962 sh.slminlen * un.un_fact AS slminlen_cm,+
2963 sh.slmaxlen, +
2964 sh.slmaxlen * un.un_fact AS slmaxlen_cm,+
2965 sh.slunit +
2966 FROM shoe_data sh, +
2967 unit un +
2968 WHERE sh.slunit = un.un_name;
2969 (1 row)
2971 select pg_get_viewdef('shoe'::regclass,0) as prettier;
2972 prettier
2973 ----------------------------------------------
2974 SELECT sh.shoename, +
2975 sh.sh_avail, +
2976 sh.slcolor, +
2977 sh.slminlen, +
2978 sh.slminlen * un.un_fact AS slminlen_cm,+
2979 sh.slmaxlen, +
2980 sh.slmaxlen * un.un_fact AS slmaxlen_cm,+
2981 sh.slunit +
2982 FROM shoe_data sh, +
2983 unit un +
2984 WHERE sh.slunit = un.un_name;
2985 (1 row)
2987 --
2988 -- check multi-row VALUES in rules
2989 --
2990 create table rules_src(f1 int, f2 int default 0);
2991 create table rules_log(f1 int, f2 int, tag text, id serial);
2992 insert into rules_src values(1,2), (11,12);
2993 create rule r1 as on update to rules_src do also
2994 insert into rules_log values(old.*, 'old', default), (new.*, 'new', default);
2995 update rules_src set f2 = f2 + 1;
2996 update rules_src set f2 = f2 * 10;
2997 select * from rules_src;
2998 f1 | f2
2999 ----+-----
3000 1 | 30
3001 11 | 130
3002 (2 rows)
3004 select * from rules_log;
3005 f1 | f2 | tag | id
3006 ----+-----+-----+----
3007 1 | 2 | old | 1
3008 1 | 3 | new | 2
3009 11 | 12 | old | 3
3010 11 | 13 | new | 4
3011 1 | 3 | old | 5
3012 1 | 30 | new | 6
3013 11 | 13 | old | 7
3014 11 | 130 | new | 8
3015 (8 rows)
3017 create rule r2 as on update to rules_src do also
3018 values(old.*, 'old'), (new.*, 'new');
3019 update rules_src set f2 = f2 / 10;
3020 column1 | column2 | column3
3021 ---------+---------+---------
3022 1 | 30 | old
3023 1 | 3 | new
3024 11 | 130 | old
3025 11 | 13 | new
3026 (4 rows)
3028 create rule r3 as on insert to rules_src do also
3029 insert into rules_log values(null, null, '-', default), (new.*, 'new', default);
3030 insert into rules_src values(22,23), (33,default);
3031 select * from rules_src;
3032 f1 | f2
3033 ----+----
3034 1 | 3
3035 11 | 13
3036 22 | 23
3037 33 | 0
3038 (4 rows)
3040 select * from rules_log;
3041 f1 | f2 | tag | id
3042 ----+-----+-----+----
3043 1 | 2 | old | 1
3044 1 | 3 | new | 2
3045 11 | 12 | old | 3
3046 11 | 13 | new | 4
3047 1 | 3 | old | 5
3048 1 | 30 | new | 6
3049 11 | 13 | old | 7
3050 11 | 130 | new | 8
3051 1 | 30 | old | 9
3052 1 | 3 | new | 10
3053 11 | 130 | old | 11
3054 11 | 13 | new | 12
3055 | | - | 13
3056 22 | 23 | new | 14
3057 | | - | 15
3058 33 | 0 | new | 16
3059 (16 rows)
3061 create rule r4 as on delete to rules_src do notify rules_src_deletion;
3062 --
3063 -- Ensure an aliased target relation for insert is correctly deparsed.
3064 --
3065 create rule r5 as on insert to rules_src do instead insert into rules_log AS trgt SELECT NEW.* RETURNING trgt.f1, trgt.f2;
3066 create rule r6 as on update to rules_src do instead UPDATE rules_log AS trgt SET tag = 'updated' WHERE trgt.f1 = new.f1;
3067 --
3068 -- Check deparse disambiguation of INSERT/UPDATE/DELETE targets.
3069 --
3070 create rule r7 as on delete to rules_src do instead
3071 with wins as (insert into int4_tbl as trgt values (0) returning *),
3072 wupd as (update int4_tbl trgt set f1 = f1+1 returning *),
3073 wdel as (delete from int4_tbl trgt where f1 = 0 returning *)
3074 insert into rules_log AS trgt select old.* from wins, wupd, wdel
3075 returning trgt.f1, trgt.f2;
3076 -- check display of all rules added above
3077 \d+ rules_src
3078 Table "public.rules_src"
3079 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
3080 --------+---------+-----------+----------+---------+---------+--------------+-------------
3081 f1 | integer | | | | plain | |
3082 f2 | integer | | | 0 | plain | |
3083 Rules:
3084 r1 AS
3085 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)
3086 r2 AS
3087 ON UPDATE TO rules_src DO VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)
3088 r3 AS
3089 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)
3090 r4 AS
3091 ON DELETE TO rules_src DO
3092 NOTIFY rules_src_deletion
3093 r5 AS
3094 ON INSERT TO rules_src DO INSTEAD INSERT INTO rules_log AS trgt (f1, f2) SELECT new.f1,
3095 new.f2
3096 RETURNING trgt.f1,
3097 trgt.f2
3098 r6 AS
3099 ON UPDATE TO rules_src DO INSTEAD UPDATE rules_log trgt SET tag = 'updated'::text
3100 WHERE trgt.f1 = new.f1
3101 r7 AS
3102 ON DELETE TO rules_src DO INSTEAD WITH wins AS (
3103 INSERT INTO int4_tbl AS trgt_1 (f1)
3104 VALUES (0)
3105 RETURNING trgt_1.f1
3106 ), wupd AS (
3107 UPDATE int4_tbl trgt_1 SET f1 = trgt_1.f1 + 1
3108 RETURNING trgt_1.f1
3109 ), wdel AS (
3110 DELETE FROM int4_tbl trgt_1
3111 WHERE trgt_1.f1 = 0
3112 RETURNING trgt_1.f1
3113 )
3114 INSERT INTO rules_log AS trgt (f1, f2) SELECT old.f1,
3115 old.f2
3116 FROM wins,
3117 wupd,
3118 wdel
3119 RETURNING trgt.f1,
3120 trgt.f2
3122 --
3123 -- Also check multiassignment deparsing.
3124 --
3125 create table rule_t1(f1 int, f2 int);
3126 create table rule_dest(f1 int, f2 int[], tag text);
3127 create rule rr as on update to rule_t1 do instead UPDATE rule_dest trgt
3128 SET (f2[1], f1, tag) = (SELECT new.f2, new.f1, 'updated'::varchar)
3129 WHERE trgt.f1 = new.f1 RETURNING new.*;
3130 \d+ rule_t1
3131 Table "public.rule_t1"
3132 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
3133 --------+---------+-----------+----------+---------+---------+--------------+-------------
3134 f1 | integer | | | | plain | |
3135 f2 | integer | | | | plain | |
3136 Rules:
3137 rr AS
3138 ON UPDATE TO rule_t1 DO INSTEAD UPDATE rule_dest trgt SET (f2[1], f1, tag) = ( SELECT new.f2,
3139 new.f1,
3140 'updated'::character varying AS "varchar")
3141 WHERE trgt.f1 = new.f1
3142 RETURNING new.f1,
3143 new.f2
3145 drop table rule_t1, rule_dest;
3146 --
3147 -- Test implicit LATERAL references to old/new in rules
3148 --
3149 CREATE TABLE rule_t1(a int, b text DEFAULT 'xxx', c int);
3150 CREATE VIEW rule_v1 AS SELECT * FROM rule_t1;
3151 CREATE RULE v1_ins AS ON INSERT TO rule_v1
3152 DO ALSO INSERT INTO rule_t1
3153 SELECT * FROM (SELECT a + 10 FROM rule_t1 WHERE a = NEW.a) tt;
3154 CREATE RULE v1_upd AS ON UPDATE TO rule_v1
3155 DO ALSO UPDATE rule_t1 t
3156 SET c = tt.a * 10
3157 FROM (SELECT a FROM rule_t1 WHERE a = OLD.a) tt WHERE t.a = tt.a;
3158 INSERT INTO rule_v1 VALUES (1, 'a'), (2, 'b');
3159 UPDATE rule_v1 SET b = upper(b);
3160 SELECT * FROM rule_t1;
3161 a | b | c
3162 ----+-----+-----
3163 1 | A | 10
3164 2 | B | 20
3165 11 | XXX | 110
3166 12 | XXX | 120
3167 (4 rows)
3169 DROP TABLE rule_t1 CASCADE;
3170 NOTICE: drop cascades to view rule_v1
3171 --
3172 -- check alter rename rule
3173 --
3174 CREATE TABLE rule_t1 (a INT);
3175 CREATE VIEW rule_v1 AS SELECT * FROM rule_t1;
3176 CREATE RULE InsertRule AS
3177 ON INSERT TO rule_v1
3178 DO INSTEAD
3179 INSERT INTO rule_t1 VALUES(new.a);
3180 ALTER RULE InsertRule ON rule_v1 RENAME to NewInsertRule;
3181 INSERT INTO rule_v1 VALUES(1);
3182 SELECT * FROM rule_v1;
3183 a
3184 ---
3185 1
3186 (1 row)
3188 \d+ rule_v1
3189 View "public.rule_v1"
3190 Column | Type | Collation | Nullable | Default | Storage | Description
3191 --------+---------+-----------+----------+---------+---------+-------------
3192 a | integer | | | | plain |
3193 View definition:
3194 SELECT a
3195 FROM rule_t1;
3196 Rules:
3197 newinsertrule AS
3198 ON INSERT TO rule_v1 DO INSTEAD INSERT INTO rule_t1 (a)
3199 VALUES (new.a)
3201 --
3202 -- error conditions for alter rename rule
3203 --
3204 ALTER RULE InsertRule ON rule_v1 RENAME TO NewInsertRule; -- doesn't exist
3205 ERROR: rule "insertrule" for relation "rule_v1" does not exist
3206 ALTER RULE NewInsertRule ON rule_v1 RENAME TO "_RETURN"; -- already exists
3207 ERROR: rule "_RETURN" for relation "rule_v1" already exists
3208 ALTER RULE "_RETURN" ON rule_v1 RENAME TO abc; -- ON SELECT rule cannot be renamed
3209 ERROR: renaming an ON SELECT rule is not allowed
3210 DROP VIEW rule_v1;
3211 DROP TABLE rule_t1;
3212 --
3213 -- check display of VALUES in view definitions
3214 --
3215 create view rule_v1 as values(1,2);
3216 \d+ rule_v1
3217 View "public.rule_v1"
3218 Column | Type | Collation | Nullable | Default | Storage | Description
3219 ---------+---------+-----------+----------+---------+---------+-------------
3220 column1 | integer | | | | plain |
3221 column2 | integer | | | | plain |
3222 View definition:
3223 VALUES (1,2);
3225 alter table rule_v1 rename column column2 to q2;
3226 \d+ rule_v1
3227 View "public.rule_v1"
3228 Column | Type | Collation | Nullable | Default | Storage | Description
3229 ---------+---------+-----------+----------+---------+---------+-------------
3230 column1 | integer | | | | plain |
3231 q2 | integer | | | | plain |
3232 View definition:
3233 SELECT column1,
3234 column2 AS q2
3235 FROM (VALUES (1,2)) "*VALUES*";
3237 drop view rule_v1;
3238 create view rule_v1(x) as values(1,2);
3239 \d+ rule_v1
3240 View "public.rule_v1"
3241 Column | Type | Collation | Nullable | Default | Storage | Description
3242 ---------+---------+-----------+----------+---------+---------+-------------
3243 x | integer | | | | plain |
3244 column2 | integer | | | | plain |
3245 View definition:
3246 SELECT column1 AS x,
3247 column2
3248 FROM (VALUES (1,2)) "*VALUES*";
3250 drop view rule_v1;
3251 create view rule_v1(x) as select * from (values(1,2)) v;
3252 \d+ rule_v1
3253 View "public.rule_v1"
3254 Column | Type | Collation | Nullable | Default | Storage | Description
3255 ---------+---------+-----------+----------+---------+---------+-------------
3256 x | integer | | | | plain |
3257 column2 | integer | | | | plain |
3258 View definition:
3259 SELECT column1 AS x,
3260 column2
3261 FROM ( VALUES (1,2)) v;
3263 drop view rule_v1;
3264 create view rule_v1(x) as select * from (values(1,2)) v(q,w);
3265 \d+ rule_v1
3266 View "public.rule_v1"
3267 Column | Type | Collation | Nullable | Default | Storage | Description
3268 --------+---------+-----------+----------+---------+---------+-------------
3269 x | integer | | | | plain |
3270 w | integer | | | | plain |
3271 View definition:
3272 SELECT q AS x,
3273 w
3274 FROM ( VALUES (1,2)) v(q, w);
3276 drop view rule_v1;
3277 --
3278 -- Check DO INSTEAD rules with ON CONFLICT
3279 --
3280 CREATE TABLE hats (
3281 hat_name char(10) primary key,
3282 hat_color char(10) -- hat color
3283 );
3284 CREATE TABLE hat_data (
3285 hat_name char(10),
3286 hat_color char(10) -- hat color
3287 );
3288 create unique index hat_data_unique_idx
3289 on hat_data (hat_name COLLATE "C" bpchar_pattern_ops);
3290 -- DO NOTHING with ON CONFLICT
3291 CREATE RULE hat_nosert AS ON INSERT TO hats
3292 DO INSTEAD
3293 INSERT INTO hat_data VALUES (
3294 NEW.hat_name,
3295 NEW.hat_color)
3296 ON CONFLICT (hat_name COLLATE "C" bpchar_pattern_ops) WHERE hat_color = 'green'
3297 DO NOTHING
3298 RETURNING *;
3299 SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename;
3300 definition
3301 ---------------------------------------------------------------------------------------------
3302 CREATE RULE hat_nosert AS +
3303 ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) +
3304 VALUES (new.hat_name, new.hat_color) ON CONFLICT(hat_name COLLATE "C" bpchar_pattern_ops)+
3305 WHERE (hat_color = 'green'::bpchar) DO NOTHING +
3306 RETURNING hat_data.hat_name, +
3307 hat_data.hat_color;
3308 (1 row)
3310 -- Works (projects row)
3311 INSERT INTO hats VALUES ('h7', 'black') RETURNING *;
3312 hat_name | hat_color
3313 ------------+------------
3314 h7 | black
3315 (1 row)
3317 -- Works (does nothing)
3318 INSERT INTO hats VALUES ('h7', 'black') RETURNING *;
3319 hat_name | hat_color
3320 ----------+-----------
3321 (0 rows)
3323 SELECT tablename, rulename, definition FROM pg_rules
3324 WHERE tablename = 'hats';
3325 tablename | rulename | definition
3326 -----------+------------+---------------------------------------------------------------------------------------------
3327 hats | hat_nosert | CREATE RULE hat_nosert AS +
3328 | | ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) +
3329 | | VALUES (new.hat_name, new.hat_color) ON CONFLICT(hat_name COLLATE "C" bpchar_pattern_ops)+
3330 | | WHERE (hat_color = 'green'::bpchar) DO NOTHING +
3331 | | RETURNING hat_data.hat_name, +
3332 | | hat_data.hat_color;
3333 (1 row)
3335 DROP RULE hat_nosert ON hats;
3336 -- DO NOTHING without ON CONFLICT
3337 CREATE RULE hat_nosert_all AS ON INSERT TO hats
3338 DO INSTEAD
3339 INSERT INTO hat_data VALUES (
3340 NEW.hat_name,
3341 NEW.hat_color)
3342 ON CONFLICT
3343 DO NOTHING
3344 RETURNING *;
3345 SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename;
3346 definition
3347 -------------------------------------------------------------------------------------
3348 CREATE RULE hat_nosert_all AS +
3349 ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color)+
3350 VALUES (new.hat_name, new.hat_color) ON CONFLICT DO NOTHING +
3351 RETURNING hat_data.hat_name, +
3352 hat_data.hat_color;
3353 (1 row)
3355 DROP RULE hat_nosert_all ON hats;
3356 -- Works (does nothing)
3357 INSERT INTO hats VALUES ('h7', 'black') RETURNING *;
3358 hat_name | hat_color
3359 ------------+------------
3360 h7 | black
3361 (1 row)
3363 -- DO UPDATE with a WHERE clause
3364 CREATE RULE hat_upsert AS ON INSERT TO hats
3365 DO INSTEAD
3366 INSERT INTO hat_data VALUES (
3367 NEW.hat_name,
3368 NEW.hat_color)
3369 ON CONFLICT (hat_name)
3370 DO UPDATE
3371 SET hat_name = hat_data.hat_name, hat_color = excluded.hat_color
3372 WHERE excluded.hat_color <> 'forbidden' AND hat_data.* != excluded.*
3373 RETURNING *;
3374 SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename;
3375 definition
3376 -----------------------------------------------------------------------------------------------------------------------------------------
3377 CREATE RULE hat_upsert AS +
3378 ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) +
3379 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+
3380 WHERE ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*)) +
3381 RETURNING hat_data.hat_name, +
3382 hat_data.hat_color;
3383 (1 row)
3385 -- Works (does upsert)
3386 INSERT INTO hats VALUES ('h8', 'black') RETURNING *;
3387 hat_name | hat_color
3388 ------------+------------
3389 h8 | black
3390 (1 row)
3392 SELECT * FROM hat_data WHERE hat_name = 'h8';
3393 hat_name | hat_color
3394 ------------+------------
3395 h8 | black
3396 (1 row)
3398 INSERT INTO hats VALUES ('h8', 'white') RETURNING *;
3399 hat_name | hat_color
3400 ------------+------------
3401 h8 | white
3402 (1 row)
3404 SELECT * FROM hat_data WHERE hat_name = 'h8';
3405 hat_name | hat_color
3406 ------------+------------
3407 h8 | white
3408 (1 row)
3410 INSERT INTO hats VALUES ('h8', 'forbidden') RETURNING *;
3411 hat_name | hat_color
3412 ----------+-----------
3413 (0 rows)
3415 SELECT * FROM hat_data WHERE hat_name = 'h8';
3416 hat_name | hat_color
3417 ------------+------------
3418 h8 | white
3419 (1 row)
3421 SELECT tablename, rulename, definition FROM pg_rules
3422 WHERE tablename = 'hats';
3423 tablename | rulename | definition
3424 -----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------
3425 hats | hat_upsert | CREATE RULE hat_upsert AS +
3426 | | ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) +
3427 | | 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+
3428 | | WHERE ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*)) +
3429 | | RETURNING hat_data.hat_name, +
3430 | | hat_data.hat_color;
3431 (1 row)
3433 -- ensure explain works for on insert conflict rules
3434 explain (costs off) INSERT INTO hats VALUES ('h8', 'forbidden') RETURNING *;
3435 QUERY PLAN
3436 -------------------------------------------------------------------------------------------------
3437 Insert on hat_data
3438 Conflict Resolution: UPDATE
3439 Conflict Arbiter Indexes: hat_data_unique_idx
3440 Conflict Filter: ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*))
3441 -> Result
3442 (5 rows)
3444 -- ensure upserting into a rule, with a CTE (different offsets!) works
3445 WITH data(hat_name, hat_color) AS MATERIALIZED (
3446 VALUES ('h8', 'green'),
3447 ('h9', 'blue'),
3448 ('h7', 'forbidden')
3449 )
3450 INSERT INTO hats
3451 SELECT * FROM data
3452 RETURNING *;
3453 hat_name | hat_color
3454 ------------+------------
3455 h8 | green
3456 h9 | blue
3457 (2 rows)
3459 EXPLAIN (costs off)
3460 WITH data(hat_name, hat_color) AS MATERIALIZED (
3461 VALUES ('h8', 'green'),
3462 ('h9', 'blue'),
3463 ('h7', 'forbidden')
3464 )
3465 INSERT INTO hats
3466 SELECT * FROM data
3467 RETURNING *;
3468 QUERY PLAN
3469 -------------------------------------------------------------------------------------------------
3470 Insert on hat_data
3471 Conflict Resolution: UPDATE
3472 Conflict Arbiter Indexes: hat_data_unique_idx
3473 Conflict Filter: ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*))
3474 CTE data
3475 -> Values Scan on "*VALUES*"
3476 -> CTE Scan on data
3477 (7 rows)
3479 SELECT * FROM hat_data WHERE hat_name IN ('h8', 'h9', 'h7') ORDER BY hat_name;
3480 hat_name | hat_color
3481 ------------+------------
3482 h7 | black
3483 h8 | green
3484 h9 | blue
3485 (3 rows)
3487 DROP RULE hat_upsert ON hats;
3488 drop table hats;
3489 drop table hat_data;
3490 -- test for pg_get_functiondef properly regurgitating SET parameters
3491 -- Note that the function is kept around to stress pg_dump.
3492 CREATE FUNCTION func_with_set_params() RETURNS integer
3493 AS 'select 1;'
3494 LANGUAGE SQL
3495 SET search_path TO PG_CATALOG
3496 SET extra_float_digits TO 2
3497 SET work_mem TO '4MB'
3498 SET datestyle to iso, mdy
3499 SET local_preload_libraries TO "Mixed/Case", 'c:/''a"/path', '', '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789'
3500 IMMUTABLE STRICT;
3501 SELECT pg_get_functiondef('func_with_set_params()'::regprocedure);
3502 pg_get_functiondef
3503 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3504 CREATE OR REPLACE FUNCTION public.func_with_set_params() +
3505 RETURNS integer +
3506 LANGUAGE sql +
3507 IMMUTABLE STRICT +
3508 SET search_path TO 'pg_catalog' +
3509 SET extra_float_digits TO '2' +
3510 SET work_mem TO '4MB' +
3511 SET "DateStyle" TO 'iso, mdy' +
3512 SET local_preload_libraries TO 'Mixed/Case', 'c:/''a"/path', '', '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789'+
3513 AS $function$select 1;$function$ +
3515 (1 row)
3517 -- tests for pg_get_*def with invalid objects
3518 SELECT pg_get_constraintdef(0);
3519 pg_get_constraintdef
3520 ----------------------
3522 (1 row)
3524 SELECT pg_get_functiondef(0);
3525 pg_get_functiondef
3526 --------------------
3528 (1 row)
3530 SELECT pg_get_indexdef(0);
3531 pg_get_indexdef
3532 -----------------
3534 (1 row)
3536 SELECT pg_get_ruledef(0);
3537 pg_get_ruledef
3538 ----------------
3540 (1 row)
3542 SELECT pg_get_statisticsobjdef(0);
3543 pg_get_statisticsobjdef
3544 -------------------------
3546 (1 row)
3548 SELECT pg_get_triggerdef(0);
3549 pg_get_triggerdef
3550 -------------------
3552 (1 row)
3554 SELECT pg_get_viewdef(0);
3555 pg_get_viewdef
3556 ----------------
3558 (1 row)
3560 SELECT pg_get_function_arguments(0);
3561 pg_get_function_arguments
3562 ---------------------------
3564 (1 row)
3566 SELECT pg_get_function_identity_arguments(0);
3567 pg_get_function_identity_arguments
3568 ------------------------------------
3570 (1 row)
3572 SELECT pg_get_function_result(0);
3573 pg_get_function_result
3574 ------------------------
3576 (1 row)
3578 SELECT pg_get_function_arg_default(0, 0);
3579 pg_get_function_arg_default
3580 -----------------------------
3582 (1 row)
3584 SELECT pg_get_function_arg_default('pg_class'::regclass, 0);
3585 pg_get_function_arg_default
3586 -----------------------------
3588 (1 row)
3590 SELECT pg_get_partkeydef(0);
3591 pg_get_partkeydef
3592 -------------------
3594 (1 row)
3596 -- test rename for a rule defined on a partitioned table
3597 CREATE TABLE rules_parted_table (a int) PARTITION BY LIST (a);
3598 CREATE TABLE rules_parted_table_1 PARTITION OF rules_parted_table FOR VALUES IN (1);
3599 CREATE RULE rules_parted_table_insert AS ON INSERT to rules_parted_table
3600 DO INSTEAD INSERT INTO rules_parted_table_1 VALUES (NEW.*);
3601 ALTER RULE rules_parted_table_insert ON rules_parted_table RENAME TO rules_parted_table_insert_redirect;
3602 DROP TABLE rules_parted_table;
3603 --
3604 -- test MERGE
3605 --
3606 CREATE TABLE rule_merge1 (a int, b text);
3607 CREATE TABLE rule_merge2 (a int, b text);
3608 CREATE RULE rule1 AS ON INSERT TO rule_merge1
3609 DO INSTEAD INSERT INTO rule_merge2 VALUES (NEW.*);
3610 CREATE RULE rule2 AS ON UPDATE TO rule_merge1
3611 DO INSTEAD UPDATE rule_merge2 SET a = NEW.a, b = NEW.b
3612 WHERE a = OLD.a;
3613 CREATE RULE rule3 AS ON DELETE TO rule_merge1
3614 DO INSTEAD DELETE FROM rule_merge2 WHERE a = OLD.a;
3615 -- MERGE not supported for table with rules
3616 MERGE INTO rule_merge1 t USING (SELECT 1 AS a) s
3617 ON t.a = s.a
3618 WHEN MATCHED AND t.a < 2 THEN
3619 UPDATE SET b = b || ' updated by merge'
3620 WHEN MATCHED AND t.a > 2 THEN
3621 DELETE
3622 WHEN NOT MATCHED THEN
3623 INSERT VALUES (s.a, '');
3624 ERROR: cannot execute MERGE on relation "rule_merge1"
3625 DETAIL: MERGE is not supported for relations with rules.
3626 -- should be ok with the other table though
3627 MERGE INTO rule_merge2 t USING (SELECT 1 AS a) s
3628 ON t.a = s.a
3629 WHEN MATCHED AND t.a < 2 THEN
3630 UPDATE SET b = b || ' updated by merge'
3631 WHEN MATCHED AND t.a > 2 THEN
3632 DELETE
3633 WHEN NOT MATCHED THEN
3634 INSERT VALUES (s.a, '');
3635 -- also ok if the rules are disabled
3636 ALTER TABLE rule_merge1 DISABLE RULE rule1;
3637 ALTER TABLE rule_merge1 DISABLE RULE rule2;
3638 ALTER TABLE rule_merge1 DISABLE RULE rule3;
3639 MERGE INTO rule_merge1 t USING (SELECT 1 AS a) s
3640 ON t.a = s.a
3641 WHEN MATCHED AND t.a < 2 THEN
3642 UPDATE SET b = b || ' updated by merge'
3643 WHEN MATCHED AND t.a > 2 THEN
3644 DELETE
3645 WHEN NOT MATCHED THEN
3646 INSERT VALUES (s.a, '');
3647 -- test deparsing
3648 CREATE TABLE sf_target(id int, data text, filling int[]);
3649 CREATE FUNCTION merge_sf_test()
3650 RETURNS TABLE(action text, a int, b text, id int, data text, filling int[])
3651 LANGUAGE sql
3652 BEGIN ATOMIC
3653 MERGE INTO sf_target t
3654 USING rule_merge1 s
3655 ON (s.a = t.id)
3656 WHEN MATCHED
3657 AND (s.a + t.id) = 42
3658 THEN UPDATE SET data = repeat(t.data, s.a) || s.b, id = length(s.b)
3659 WHEN NOT MATCHED
3660 AND (s.b IS NOT NULL)
3661 THEN INSERT (data, id)
3662 VALUES (s.b, s.a)
3663 WHEN MATCHED
3664 AND length(s.b || t.data) > 10
3665 THEN UPDATE SET data = s.b
3666 WHEN MATCHED
3667 AND s.a > 200
3668 THEN UPDATE SET filling[s.a] = t.id
3669 WHEN MATCHED
3670 AND s.a > 100
3671 THEN DELETE
3672 WHEN MATCHED
3673 THEN DO NOTHING
3674 WHEN NOT MATCHED
3675 AND s.a > 200
3676 THEN INSERT DEFAULT VALUES
3677 WHEN NOT MATCHED
3678 AND s.a > 100
3679 THEN INSERT (id, data) OVERRIDING USER VALUE
3680 VALUES (s.a, DEFAULT)
3681 WHEN NOT MATCHED
3682 AND s.a > 0
3683 THEN INSERT
3684 VALUES (s.a, s.b, DEFAULT)
3685 WHEN NOT MATCHED
3686 THEN INSERT (filling[1], id)
3687 VALUES (s.a, s.a)
3688 RETURNING
3689 merge_action() AS action, *;
3690 END;
3691 \sf merge_sf_test
3692 CREATE OR REPLACE FUNCTION public.merge_sf_test()
3693 RETURNS TABLE(action text, a integer, b text, id integer, data text, filling integer[])
3694 LANGUAGE sql
3695 BEGIN ATOMIC
3696 MERGE INTO sf_target t
3697 USING rule_merge1 s
3698 ON (s.a = t.id)
3699 WHEN MATCHED
3700 AND ((s.a + t.id) = 42)
3701 THEN UPDATE SET data = (repeat(t.data, s.a) || s.b), id = length(s.b)
3702 WHEN NOT MATCHED
3703 AND (s.b IS NOT NULL)
3704 THEN INSERT (data, id)
3705 VALUES (s.b, s.a)
3706 WHEN MATCHED
3707 AND (length((s.b || t.data)) > 10)
3708 THEN UPDATE SET data = s.b
3709 WHEN MATCHED
3710 AND (s.a > 200)
3711 THEN UPDATE SET filling[s.a] = t.id
3712 WHEN MATCHED
3713 AND (s.a > 100)
3714 THEN DELETE
3715 WHEN MATCHED
3716 THEN DO NOTHING
3717 WHEN NOT MATCHED
3718 AND (s.a > 200)
3719 THEN INSERT DEFAULT VALUES
3720 WHEN NOT MATCHED
3721 AND (s.a > 100)
3722 THEN INSERT (id, data) OVERRIDING USER VALUE
3723 VALUES (s.a, DEFAULT)
3724 WHEN NOT MATCHED
3725 AND (s.a > 0)
3726 THEN INSERT (id, data, filling)
3727 VALUES (s.a, s.b, DEFAULT)
3728 WHEN NOT MATCHED
3729 THEN INSERT (filling[1], id)
3730 VALUES (s.a, s.a)
3731 RETURNING MERGE_ACTION() AS action,
3732 s.a,
3733 s.b,
3734 t.id,
3735 t.data,
3736 t.filling;
3737 END
3738 CREATE FUNCTION merge_sf_test2()
3739 RETURNS void
3740 LANGUAGE sql
3741 BEGIN ATOMIC
3742 MERGE INTO sf_target t
3743 USING rule_merge1 s
3744 ON (s.a = t.id)
3745 WHEN NOT MATCHED
3746 THEN INSERT (data, id)
3747 VALUES (s.a, s.a)
3748 WHEN MATCHED
3749 THEN UPDATE SET data = s.b
3750 WHEN NOT MATCHED BY SOURCE
3751 THEN DELETE;
3752 END;
3753 \sf merge_sf_test2
3754 CREATE OR REPLACE FUNCTION public.merge_sf_test2()
3755 RETURNS void
3756 LANGUAGE sql
3757 BEGIN ATOMIC
3758 MERGE INTO sf_target t
3759 USING rule_merge1 s
3760 ON (s.a = t.id)
3761 WHEN NOT MATCHED BY TARGET
3762 THEN INSERT (data, id)
3763 VALUES (s.a, s.a)
3764 WHEN MATCHED
3765 THEN UPDATE SET data = s.b
3766 WHEN NOT MATCHED BY SOURCE
3767 THEN DELETE;
3768 END
3769 DROP FUNCTION merge_sf_test;
3770 DROP FUNCTION merge_sf_test2;
3771 DROP TABLE sf_target;
3772 --
3773 -- Test enabling/disabling
3774 --
3775 CREATE TABLE ruletest1 (a int);
3776 CREATE TABLE ruletest2 (b int);
3777 CREATE RULE rule1 AS ON INSERT TO ruletest1
3778 DO INSTEAD INSERT INTO ruletest2 VALUES (NEW.*);
3779 INSERT INTO ruletest1 VALUES (1);
3780 ALTER TABLE ruletest1 DISABLE RULE rule1;
3781 INSERT INTO ruletest1 VALUES (2);
3782 ALTER TABLE ruletest1 ENABLE RULE rule1;
3783 SET session_replication_role = replica;
3784 INSERT INTO ruletest1 VALUES (3);
3785 ALTER TABLE ruletest1 ENABLE REPLICA RULE rule1;
3786 INSERT INTO ruletest1 VALUES (4);
3787 RESET session_replication_role;
3788 INSERT INTO ruletest1 VALUES (5);
3789 SELECT * FROM ruletest1;
3790 a
3791 ---
3792 2
3793 3
3794 5
3795 (3 rows)
3797 SELECT * FROM ruletest2;
3798 b
3799 ---
3800 1
3801 4
3802 (2 rows)
3804 DROP TABLE ruletest1;
3805 DROP TABLE ruletest2;
3806 --
3807 -- Test non-SELECT rule on security invoker view.
3808 -- Should use view owner's permissions.
3809 --
3810 CREATE USER regress_rule_user1;
3811 CREATE TABLE ruletest_t1 (x int);
3812 CREATE TABLE ruletest_t2 (x int);
3813 CREATE VIEW ruletest_v1 WITH (security_invoker=true) AS
3814 SELECT * FROM ruletest_t1;
3815 GRANT INSERT ON ruletest_v1 TO regress_rule_user1;
3816 CREATE RULE rule1 AS ON INSERT TO ruletest_v1
3817 DO INSTEAD INSERT INTO ruletest_t2 VALUES (NEW.*);
3818 SET SESSION AUTHORIZATION regress_rule_user1;
3819 INSERT INTO ruletest_v1 VALUES (1);
3820 RESET SESSION AUTHORIZATION;
3821 -- Test that main query's relation's permissions are checked before
3822 -- the rule action's relation's.
3823 CREATE TABLE ruletest_t3 (x int);
3824 CREATE RULE rule2 AS ON UPDATE TO ruletest_t1
3825 DO INSTEAD INSERT INTO ruletest_t2 VALUES (OLD.*);
3826 REVOKE ALL ON ruletest_t2 FROM regress_rule_user1;
3827 REVOKE ALL ON ruletest_t3 FROM regress_rule_user1;
3828 ALTER TABLE ruletest_t1 OWNER TO regress_rule_user1;
3829 SET SESSION AUTHORIZATION regress_rule_user1;
3830 UPDATE ruletest_t1 t1 SET x = 0 FROM ruletest_t3 t3 WHERE t1.x = t3.x;
3831 ERROR: permission denied for table ruletest_t3
3832 RESET SESSION AUTHORIZATION;
3833 SELECT * FROM ruletest_t1;
3834 x
3835 ---
3836 (0 rows)
3838 SELECT * FROM ruletest_t2;
3839 x
3840 ---
3841 1
3842 (1 row)
3844 DROP VIEW ruletest_v1;
3845 DROP RULE rule2 ON ruletest_t1;
3846 DROP TABLE ruletest_t3;
3847 DROP TABLE ruletest_t2;
3848 DROP TABLE ruletest_t1;
3849 DROP USER regress_rule_user1;