| blob | 2fa00a3c29a584e50341019977cffc45b737b4ed |
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 money);
55 create table rtest_emplog (ename char(20), who name, action char(10), newsal money, oldsal money);
56 create table rtest_empmass (ename char(20), salary money);
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 | $80,000.00
416 gates | t | hired | $80,000.00 | $0.00
417 wiecc | t | hired | $5,000.00 | $0.00
418 wieck | t | honored | $6,000.00 | $5,000.00
419 wieck | t | honored | $7,000.00 | $6,000.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 | $80,000.00
430 gates | t | hired | $80,000.00 | $0.00
431 maier | t | hired | $5,000.00 | $0.00
432 mayr | t | hired | $6,000.00 | $0.00
433 meyer | t | hired | $4,000.00 | $0.00
434 wiecc | t | hired | $5,000.00 | $0.00
435 wieck | t | honored | $6,000.00 | $5,000.00
436 wieck | t | honored | $7,000.00 | $6,000.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 | $80,000.00
445 gates | t | hired | $80,000.00 | $0.00
446 maier | t | hired | $5,000.00 | $0.00
447 maier | t | honored | $6,000.00 | $5,000.00
448 mayr | t | hired | $6,000.00 | $0.00
449 mayr | t | honored | $7,000.00 | $6,000.00
450 meyer | t | hired | $4,000.00 | $0.00
451 meyer | t | honored | $5,000.00 | $4,000.00
452 wiecc | t | hired | $5,000.00 | $0.00
453 wieck | t | honored | $6,000.00 | $5,000.00
454 wieck | t | honored | $7,000.00 | $6,000.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 | $80,000.00
462 gates | t | hired | $80,000.00 | $0.00
463 maier | t | fired | $0.00 | $6,000.00
464 maier | t | hired | $5,000.00 | $0.00
465 maier | t | honored | $6,000.00 | $5,000.00
466 mayr | t | fired | $0.00 | $7,000.00
467 mayr | t | hired | $6,000.00 | $0.00
468 mayr | t | honored | $7,000.00 | $6,000.00
469 meyer | t | fired | $0.00 | $5,000.00
470 meyer | t | hired | $4,000.00 | $0.00
471 meyer | t | honored | $5,000.00 | $4,000.00
472 wiecc | t | hired | $5,000.00 | $0.00
473 wieck | t | honored | $6,000.00 | $5,000.00
474 wieck | t | honored | $7,000.00 | $6,000.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 HINT: There is a column named "f1" in table "old", but it cannot be referenced from this part of the query.
1198 -- this is the correct way:
1199 create rule rules_foorule as on insert to rules_foo where f1 < 100
1200 do instead insert into rules_foo2 values (new.f1);
1201 insert into rules_foo values(2);
1202 insert into rules_foo values(100);
1203 select * from rules_foo;
1204 f1
1205 ------
1206 1001
1207 100
1208 (2 rows)
1210 select * from rules_foo2;
1211 f1
1212 ----
1213 2
1214 (1 row)
1216 drop rule rules_foorule on rules_foo;
1217 drop table rules_foo;
1218 drop table rules_foo2;
1219 --
1220 -- Test rules containing INSERT ... SELECT, which is a very ugly special
1221 -- case as of 7.1. Example is based on bug report from Joel Burton.
1222 --
1223 create table pparent (pid int, txt text);
1224 insert into pparent values (1,'parent1');
1225 insert into pparent values (2,'parent2');
1226 create table cchild (pid int, descrip text);
1227 insert into cchild values (1,'descrip1');
1228 create view vview as
1229 select pparent.pid, txt, descrip from
1230 pparent left join cchild using (pid);
1231 create rule rrule as
1232 on update to vview do instead
1233 (
1234 insert into cchild (pid, descrip)
1235 select old.pid, new.descrip where old.descrip isnull;
1236 update cchild set descrip = new.descrip where cchild.pid = old.pid;
1237 );
1238 select * from vview;
1239 pid | txt | descrip
1240 -----+---------+----------
1241 1 | parent1 | descrip1
1242 2 | parent2 |
1243 (2 rows)
1245 update vview set descrip='test1' where pid=1;
1246 select * from vview;
1247 pid | txt | descrip
1248 -----+---------+---------
1249 1 | parent1 | test1
1250 2 | parent2 |
1251 (2 rows)
1253 update vview set descrip='test2' where pid=2;
1254 select * from vview;
1255 pid | txt | descrip
1256 -----+---------+---------
1257 1 | parent1 | test1
1258 2 | parent2 | test2
1259 (2 rows)
1261 update vview set descrip='test3' where pid=3;
1262 select * from vview;
1263 pid | txt | descrip
1264 -----+---------+---------
1265 1 | parent1 | test1
1266 2 | parent2 | test2
1267 (2 rows)
1269 select * from cchild;
1270 pid | descrip
1271 -----+---------
1272 1 | test1
1273 2 | test2
1274 (2 rows)
1276 drop rule rrule on vview;
1277 drop view vview;
1278 drop table pparent;
1279 drop table cchild;
1280 --
1281 -- Check that ruleutils are working
1282 --
1283 -- temporarily disable fancy output, so view changes create less diff noise
1284 \a\t
1285 SELECT viewname, definition FROM pg_views
1286 WHERE schemaname IN ('pg_catalog', 'public')
1287 ORDER BY viewname;
1288 iexit| SELECT ih.name,
1289 ih.thepath,
1290 interpt_pp(ih.thepath, r.thepath) AS exit
1291 FROM ihighway ih,
1292 ramp r
1293 WHERE (ih.thepath ## r.thepath);
1294 key_dependent_view| SELECT view_base_table.key,
1295 view_base_table.data
1296 FROM view_base_table
1297 GROUP BY view_base_table.key;
1298 key_dependent_view_no_cols| SELECT
1299 FROM view_base_table
1300 GROUP BY view_base_table.key
1301 HAVING (length((view_base_table.data)::text) > 0);
1302 mvtest_tv| SELECT mvtest_t.type,
1303 sum(mvtest_t.amt) AS totamt
1304 FROM mvtest_t
1305 GROUP BY mvtest_t.type;
1306 mvtest_tvv| SELECT sum(mvtest_tv.totamt) AS grandtot
1307 FROM mvtest_tv;
1308 mvtest_tvvmv| SELECT mvtest_tvvm.grandtot
1309 FROM mvtest_tvvm;
1310 pg_available_extension_versions| SELECT e.name,
1311 e.version,
1312 (x.extname IS NOT NULL) AS installed,
1313 e.superuser,
1314 e.trusted,
1315 e.relocatable,
1316 e.schema,
1317 e.requires,
1318 e.comment
1319 FROM (pg_available_extension_versions() e(name, version, superuser, trusted, relocatable, schema, requires, comment)
1320 LEFT JOIN pg_extension x ON (((e.name = x.extname) AND (e.version = x.extversion))));
1321 pg_available_extensions| SELECT e.name,
1322 e.default_version,
1323 x.extversion AS installed_version,
1324 e.comment
1325 FROM (pg_available_extensions() e(name, default_version, comment)
1326 LEFT JOIN pg_extension x ON ((e.name = x.extname)));
1327 pg_backend_memory_contexts| SELECT pg_get_backend_memory_contexts.name,
1328 pg_get_backend_memory_contexts.ident,
1329 pg_get_backend_memory_contexts.parent,
1330 pg_get_backend_memory_contexts.level,
1331 pg_get_backend_memory_contexts.total_bytes,
1332 pg_get_backend_memory_contexts.total_nblocks,
1333 pg_get_backend_memory_contexts.free_bytes,
1334 pg_get_backend_memory_contexts.free_chunks,
1335 pg_get_backend_memory_contexts.used_bytes
1336 FROM pg_get_backend_memory_contexts() pg_get_backend_memory_contexts(name, ident, parent, level, total_bytes, total_nblocks, free_bytes, free_chunks, used_bytes);
1337 pg_config| SELECT pg_config.name,
1338 pg_config.setting
1339 FROM pg_config() pg_config(name, setting);
1340 pg_cursors| SELECT c.name,
1341 c.statement,
1342 c.is_holdable,
1343 c.is_binary,
1344 c.is_scrollable,
1345 c.creation_time
1346 FROM pg_cursor() c(name, statement, is_holdable, is_binary, is_scrollable, creation_time);
1347 pg_file_settings| SELECT a.sourcefile,
1348 a.sourceline,
1349 a.seqno,
1350 a.name,
1351 a.setting,
1352 a.applied,
1353 a.error
1354 FROM pg_show_all_file_settings() a(sourcefile, sourceline, seqno, name, setting, applied, error);
1355 pg_group| SELECT pg_authid.rolname AS groname,
1356 pg_authid.oid AS grosysid,
1357 ARRAY( SELECT pg_auth_members.member
1358 FROM pg_auth_members
1359 WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist
1360 FROM pg_authid
1361 WHERE (NOT pg_authid.rolcanlogin);
1362 pg_hba_file_rules| SELECT a.line_number,
1363 a.type,
1364 a.database,
1365 a.user_name,
1366 a.address,
1367 a.netmask,
1368 a.auth_method,
1369 a.options,
1370 a.error
1371 FROM pg_hba_file_rules() a(line_number, type, database, user_name, address, netmask, auth_method, options, error);
1372 pg_indexes| SELECT n.nspname AS schemaname,
1373 c.relname AS tablename,
1374 i.relname AS indexname,
1375 t.spcname AS tablespace,
1376 pg_get_indexdef(i.oid) AS indexdef
1377 FROM ((((pg_index x
1378 JOIN pg_class c ON ((c.oid = x.indrelid)))
1379 JOIN pg_class i ON ((i.oid = x.indexrelid)))
1380 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
1381 LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace)))
1382 WHERE ((c.relkind = ANY (ARRAY['r'::"char", 'm'::"char", 'p'::"char"])) AND (i.relkind = ANY (ARRAY['i'::"char", 'I'::"char"])));
1383 pg_locks| SELECT l.locktype,
1384 l.database,
1385 l.relation,
1386 l.page,
1387 l.tuple,
1388 l.virtualxid,
1389 l.transactionid,
1390 l.classid,
1391 l.objid,
1392 l.objsubid,
1393 l.virtualtransaction,
1394 l.pid,
1395 l.mode,
1396 l.granted,
1397 l.fastpath,
1398 l.waitstart
1399 FROM pg_lock_status() l(locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted, fastpath, waitstart);
1400 pg_matviews| SELECT n.nspname AS schemaname,
1401 c.relname AS matviewname,
1402 pg_get_userbyid(c.relowner) AS matviewowner,
1403 t.spcname AS tablespace,
1404 c.relhasindex AS hasindexes,
1405 c.relispopulated AS ispopulated,
1406 pg_get_viewdef(c.oid) AS definition
1407 FROM ((pg_class c
1408 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
1409 LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace)))
1410 WHERE (c.relkind = 'm'::"char");
1411 pg_policies| SELECT n.nspname AS schemaname,
1412 c.relname AS tablename,
1413 pol.polname AS policyname,
1414 CASE
1415 WHEN pol.polpermissive THEN 'PERMISSIVE'::text
1416 ELSE 'RESTRICTIVE'::text
1417 END AS permissive,
1418 CASE
1419 WHEN (pol.polroles = '{0}'::oid[]) THEN (string_to_array('public'::text, ''::text))::name[]
1420 ELSE ARRAY( SELECT pg_authid.rolname
1421 FROM pg_authid
1422 WHERE (pg_authid.oid = ANY (pol.polroles))
1423 ORDER BY pg_authid.rolname)
1424 END AS roles,
1425 CASE pol.polcmd
1426 WHEN 'r'::"char" THEN 'SELECT'::text
1427 WHEN 'a'::"char" THEN 'INSERT'::text
1428 WHEN 'w'::"char" THEN 'UPDATE'::text
1429 WHEN 'd'::"char" THEN 'DELETE'::text
1430 WHEN '*'::"char" THEN 'ALL'::text
1431 ELSE NULL::text
1432 END AS cmd,
1433 pg_get_expr(pol.polqual, pol.polrelid) AS qual,
1434 pg_get_expr(pol.polwithcheck, pol.polrelid) AS with_check
1435 FROM ((pg_policy pol
1436 JOIN pg_class c ON ((c.oid = pol.polrelid)))
1437 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)));
1438 pg_prepared_statements| SELECT p.name,
1439 p.statement,
1440 p.prepare_time,
1441 p.parameter_types,
1442 p.from_sql,
1443 p.generic_plans,
1444 p.custom_plans
1445 FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql, generic_plans, custom_plans);
1446 pg_prepared_xacts| SELECT p.transaction,
1447 p.gid,
1448 p.prepared,
1449 u.rolname AS owner,
1450 d.datname AS database
1451 FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid)
1452 LEFT JOIN pg_authid u ON ((p.ownerid = u.oid)))
1453 LEFT JOIN pg_database d ON ((p.dbid = d.oid)));
1454 pg_publication_tables| SELECT p.pubname,
1455 n.nspname AS schemaname,
1456 c.relname AS tablename
1457 FROM pg_publication p,
1458 LATERAL pg_get_publication_tables((p.pubname)::text) gpt(relid),
1459 (pg_class c
1460 JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
1461 WHERE (c.oid = gpt.relid);
1462 pg_replication_origin_status| SELECT pg_show_replication_origin_status.local_id,
1463 pg_show_replication_origin_status.external_id,
1464 pg_show_replication_origin_status.remote_lsn,
1465 pg_show_replication_origin_status.local_lsn
1466 FROM pg_show_replication_origin_status() pg_show_replication_origin_status(local_id, external_id, remote_lsn, local_lsn);
1467 pg_replication_slots| SELECT l.slot_name,
1468 l.plugin,
1469 l.slot_type,
1470 l.datoid,
1471 d.datname AS database,
1472 l.temporary,
1473 l.active,
1474 l.active_pid,
1475 l.xmin,
1476 l.catalog_xmin,
1477 l.restart_lsn,
1478 l.confirmed_flush_lsn,
1479 l.wal_status,
1480 l.safe_wal_size,
1481 l.two_phase
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)
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 CASE
1704 WHEN has_sequence_privilege(c.oid, 'SELECT,USAGE'::text) THEN pg_sequence_last_value((c.oid)::regclass)
1705 ELSE NULL::bigint
1706 END AS last_value
1707 FROM ((pg_sequence s
1708 JOIN pg_class c ON ((c.oid = s.seqrelid)))
1709 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
1710 WHERE ((NOT pg_is_other_temp_schema(n.oid)) AND (c.relkind = 'S'::"char"));
1711 pg_settings| SELECT a.name,
1712 a.setting,
1713 a.unit,
1714 a.category,
1715 a.short_desc,
1716 a.extra_desc,
1717 a.context,
1718 a.vartype,
1719 a.source,
1720 a.min_val,
1721 a.max_val,
1722 a.enumvals,
1723 a.boot_val,
1724 a.reset_val,
1725 a.sourcefile,
1726 a.sourceline,
1727 a.pending_restart
1728 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);
1729 pg_shadow| SELECT pg_authid.rolname AS usename,
1730 pg_authid.oid AS usesysid,
1731 pg_authid.rolcreatedb AS usecreatedb,
1732 pg_authid.rolsuper AS usesuper,
1733 pg_authid.rolreplication AS userepl,
1734 pg_authid.rolbypassrls AS usebypassrls,
1735 pg_authid.rolpassword AS passwd,
1736 pg_authid.rolvaliduntil AS valuntil,
1737 s.setconfig AS useconfig
1738 FROM (pg_authid
1739 LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))))
1740 WHERE pg_authid.rolcanlogin;
1741 pg_shmem_allocations| SELECT pg_get_shmem_allocations.name,
1742 pg_get_shmem_allocations.off,
1743 pg_get_shmem_allocations.size,
1744 pg_get_shmem_allocations.allocated_size
1745 FROM pg_get_shmem_allocations() pg_get_shmem_allocations(name, off, size, allocated_size);
1746 pg_stat_activity| SELECT s.datid,
1747 d.datname,
1748 s.pid,
1749 s.leader_pid,
1750 s.usesysid,
1751 u.rolname AS usename,
1752 s.application_name,
1753 s.client_addr,
1754 s.client_hostname,
1755 s.client_port,
1756 s.backend_start,
1757 s.xact_start,
1758 s.query_start,
1759 s.state_change,
1760 s.wait_event_type,
1761 s.wait_event,
1762 s.state,
1763 s.backend_xid,
1764 s.backend_xmin,
1765 s.query_id,
1766 s.query,
1767 s.backend_type
1768 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, leader_pid, query_id)
1769 LEFT JOIN pg_database d ON ((s.datid = d.oid)))
1770 LEFT JOIN pg_authid u ON ((s.usesysid = u.oid)));
1771 pg_stat_all_indexes| SELECT c.oid AS relid,
1772 i.oid AS indexrelid,
1773 n.nspname AS schemaname,
1774 c.relname,
1775 i.relname AS indexrelname,
1776 pg_stat_get_numscans(i.oid) AS idx_scan,
1777 pg_stat_get_tuples_returned(i.oid) AS idx_tup_read,
1778 pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch
1779 FROM (((pg_class c
1780 JOIN pg_index x ON ((c.oid = x.indrelid)))
1781 JOIN pg_class i ON ((i.oid = x.indexrelid)))
1782 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
1783 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
1784 pg_stat_all_tables| SELECT c.oid AS relid,
1785 n.nspname AS schemaname,
1786 c.relname,
1787 pg_stat_get_numscans(c.oid) AS seq_scan,
1788 pg_stat_get_tuples_returned(c.oid) AS seq_tup_read,
1789 (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan,
1790 ((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_live_tuples(c.oid) AS n_live_tup,
1796 pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
1797 pg_stat_get_mod_since_analyze(c.oid) AS n_mod_since_analyze,
1798 pg_stat_get_ins_since_vacuum(c.oid) AS n_ins_since_vacuum,
1799 pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum,
1800 pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum,
1801 pg_stat_get_last_analyze_time(c.oid) AS last_analyze,
1802 pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze,
1803 pg_stat_get_vacuum_count(c.oid) AS vacuum_count,
1804 pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count,
1805 pg_stat_get_analyze_count(c.oid) AS analyze_count,
1806 pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count
1807 FROM ((pg_class c
1808 LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
1809 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
1810 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char", 'p'::"char"]))
1811 GROUP BY c.oid, n.nspname, c.relname;
1812 pg_stat_archiver| SELECT s.archived_count,
1813 s.last_archived_wal,
1814 s.last_archived_time,
1815 s.failed_count,
1816 s.last_failed_wal,
1817 s.last_failed_time,
1818 s.stats_reset
1819 FROM pg_stat_get_archiver() s(archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time, stats_reset);
1820 pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
1821 pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,
1822 pg_stat_get_checkpoint_write_time() AS checkpoint_write_time,
1823 pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time,
1824 pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint,
1825 pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
1826 pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
1827 pg_stat_get_buf_written_backend() AS buffers_backend,
1828 pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync,
1829 pg_stat_get_buf_alloc() AS buffers_alloc,
1830 pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
1831 pg_stat_database| SELECT d.oid AS datid,
1832 d.datname,
1833 CASE
1834 WHEN (d.oid = (0)::oid) THEN 0
1835 ELSE pg_stat_get_db_numbackends(d.oid)
1836 END AS numbackends,
1837 pg_stat_get_db_xact_commit(d.oid) AS xact_commit,
1838 pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback,
1839 (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read,
1840 pg_stat_get_db_blocks_hit(d.oid) AS blks_hit,
1841 pg_stat_get_db_tuples_returned(d.oid) AS tup_returned,
1842 pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched,
1843 pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted,
1844 pg_stat_get_db_tuples_updated(d.oid) AS tup_updated,
1845 pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted,
1846 pg_stat_get_db_conflict_all(d.oid) AS conflicts,
1847 pg_stat_get_db_temp_files(d.oid) AS temp_files,
1848 pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes,
1849 pg_stat_get_db_deadlocks(d.oid) AS deadlocks,
1850 pg_stat_get_db_checksum_failures(d.oid) AS checksum_failures,
1851 pg_stat_get_db_checksum_last_failure(d.oid) AS checksum_last_failure,
1852 pg_stat_get_db_blk_read_time(d.oid) AS blk_read_time,
1853 pg_stat_get_db_blk_write_time(d.oid) AS blk_write_time,
1854 pg_stat_get_db_session_time(d.oid) AS session_time,
1855 pg_stat_get_db_active_time(d.oid) AS active_time,
1856 pg_stat_get_db_idle_in_transaction_time(d.oid) AS idle_in_transaction_time,
1857 pg_stat_get_db_sessions(d.oid) AS sessions,
1858 pg_stat_get_db_sessions_abandoned(d.oid) AS sessions_abandoned,
1859 pg_stat_get_db_sessions_fatal(d.oid) AS sessions_fatal,
1860 pg_stat_get_db_sessions_killed(d.oid) AS sessions_killed,
1861 pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset
1862 FROM ( SELECT 0 AS oid,
1863 NULL::name AS datname
1864 UNION ALL
1865 SELECT pg_database.oid,
1866 pg_database.datname
1867 FROM pg_database) d;
1868 pg_stat_database_conflicts| SELECT d.oid AS datid,
1869 d.datname,
1870 pg_stat_get_db_conflict_tablespace(d.oid) AS confl_tablespace,
1871 pg_stat_get_db_conflict_lock(d.oid) AS confl_lock,
1872 pg_stat_get_db_conflict_snapshot(d.oid) AS confl_snapshot,
1873 pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin,
1874 pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock
1875 FROM pg_database d;
1876 pg_stat_gssapi| SELECT s.pid,
1877 s.gss_auth AS gss_authenticated,
1878 s.gss_princ AS principal,
1879 s.gss_enc AS encrypted
1880 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, leader_pid, query_id)
1881 WHERE (s.client_port IS NOT NULL);
1882 pg_stat_progress_analyze| SELECT s.pid,
1883 s.datid,
1884 d.datname,
1885 s.relid,
1886 CASE s.param1
1887 WHEN 0 THEN 'initializing'::text
1888 WHEN 1 THEN 'acquiring sample rows'::text
1889 WHEN 2 THEN 'acquiring inherited sample rows'::text
1890 WHEN 3 THEN 'computing statistics'::text
1891 WHEN 4 THEN 'computing extended statistics'::text
1892 WHEN 5 THEN 'finalizing analyze'::text
1893 ELSE NULL::text
1894 END AS phase,
1895 s.param2 AS sample_blks_total,
1896 s.param3 AS sample_blks_scanned,
1897 s.param4 AS ext_stats_total,
1898 s.param5 AS ext_stats_computed,
1899 s.param6 AS child_tables_total,
1900 s.param7 AS child_tables_done,
1901 (s.param8)::oid AS current_child_table_relid
1902 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)
1903 LEFT JOIN pg_database d ON ((s.datid = d.oid)));
1904 pg_stat_progress_basebackup| SELECT s.pid,
1905 CASE s.param1
1906 WHEN 0 THEN 'initializing'::text
1907 WHEN 1 THEN 'waiting for checkpoint to finish'::text
1908 WHEN 2 THEN 'estimating backup size'::text
1909 WHEN 3 THEN 'streaming database files'::text
1910 WHEN 4 THEN 'waiting for wal archiving to finish'::text
1911 WHEN 5 THEN 'transferring wal files'::text
1912 ELSE NULL::text
1913 END AS phase,
1914 CASE s.param2
1915 WHEN '-1'::integer THEN NULL::bigint
1916 ELSE s.param2
1917 END AS backup_total,
1918 s.param3 AS backup_streamed,
1919 s.param4 AS tablespaces_total,
1920 s.param5 AS tablespaces_streamed
1921 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);
1922 pg_stat_progress_cluster| SELECT s.pid,
1923 s.datid,
1924 d.datname,
1925 s.relid,
1926 CASE s.param1
1927 WHEN 1 THEN 'CLUSTER'::text
1928 WHEN 2 THEN 'VACUUM FULL'::text
1929 ELSE NULL::text
1930 END AS command,
1931 CASE s.param2
1932 WHEN 0 THEN 'initializing'::text
1933 WHEN 1 THEN 'seq scanning heap'::text
1934 WHEN 2 THEN 'index scanning heap'::text
1935 WHEN 3 THEN 'sorting tuples'::text
1936 WHEN 4 THEN 'writing new heap'::text
1937 WHEN 5 THEN 'swapping relation files'::text
1938 WHEN 6 THEN 'rebuilding index'::text
1939 WHEN 7 THEN 'performing final cleanup'::text
1940 ELSE NULL::text
1941 END AS phase,
1942 (s.param3)::oid AS cluster_index_relid,
1943 s.param4 AS heap_tuples_scanned,
1944 s.param5 AS heap_tuples_written,
1945 s.param6 AS heap_blks_total,
1946 s.param7 AS heap_blks_scanned,
1947 s.param8 AS index_rebuild_count
1948 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)
1949 LEFT JOIN pg_database d ON ((s.datid = d.oid)));
1950 pg_stat_progress_copy| SELECT s.pid,
1951 s.datid,
1952 d.datname,
1953 s.relid,
1954 CASE s.param5
1955 WHEN 1 THEN 'COPY FROM'::text
1956 WHEN 2 THEN 'COPY TO'::text
1957 ELSE NULL::text
1958 END AS command,
1959 CASE s.param6
1960 WHEN 1 THEN 'FILE'::text
1961 WHEN 2 THEN 'PROGRAM'::text
1962 WHEN 3 THEN 'PIPE'::text
1963 WHEN 4 THEN 'CALLBACK'::text
1964 ELSE NULL::text
1965 END AS type,
1966 s.param1 AS bytes_processed,
1967 s.param2 AS bytes_total,
1968 s.param3 AS tuples_processed,
1969 s.param4 AS tuples_excluded
1970 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)
1971 LEFT JOIN pg_database d ON ((s.datid = d.oid)));
1972 pg_stat_progress_create_index| SELECT s.pid,
1973 s.datid,
1974 d.datname,
1975 s.relid,
1976 (s.param7)::oid AS index_relid,
1977 CASE s.param1
1978 WHEN 1 THEN 'CREATE INDEX'::text
1979 WHEN 2 THEN 'CREATE INDEX CONCURRENTLY'::text
1980 WHEN 3 THEN 'REINDEX'::text
1981 WHEN 4 THEN 'REINDEX CONCURRENTLY'::text
1982 ELSE NULL::text
1983 END AS command,
1984 CASE s.param10
1985 WHEN 0 THEN 'initializing'::text
1986 WHEN 1 THEN 'waiting for writers before build'::text
1987 WHEN 2 THEN ('building index'::text || COALESCE((': '::text || pg_indexam_progress_phasename((s.param9)::oid, s.param11)), ''::text))
1988 WHEN 3 THEN 'waiting for writers before validation'::text
1989 WHEN 4 THEN 'index validation: scanning index'::text
1990 WHEN 5 THEN 'index validation: sorting tuples'::text
1991 WHEN 6 THEN 'index validation: scanning table'::text
1992 WHEN 7 THEN 'waiting for old snapshots'::text
1993 WHEN 8 THEN 'waiting for readers before marking dead'::text
1994 WHEN 9 THEN 'waiting for readers before dropping'::text
1995 ELSE NULL::text
1996 END AS phase,
1997 s.param4 AS lockers_total,
1998 s.param5 AS lockers_done,
1999 s.param6 AS current_locker_pid,
2000 s.param16 AS blocks_total,
2001 s.param17 AS blocks_done,
2002 s.param12 AS tuples_total,
2003 s.param13 AS tuples_done,
2004 s.param14 AS partitions_total,
2005 s.param15 AS partitions_done
2006 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)
2007 LEFT JOIN pg_database d ON ((s.datid = d.oid)));
2008 pg_stat_progress_vacuum| SELECT s.pid,
2009 s.datid,
2010 d.datname,
2011 s.relid,
2012 CASE s.param1
2013 WHEN 0 THEN 'initializing'::text
2014 WHEN 1 THEN 'scanning heap'::text
2015 WHEN 2 THEN 'vacuuming indexes'::text
2016 WHEN 3 THEN 'vacuuming heap'::text
2017 WHEN 4 THEN 'cleaning up indexes'::text
2018 WHEN 5 THEN 'truncating heap'::text
2019 WHEN 6 THEN 'performing final cleanup'::text
2020 ELSE NULL::text
2021 END AS phase,
2022 s.param2 AS heap_blks_total,
2023 s.param3 AS heap_blks_scanned,
2024 s.param4 AS heap_blks_vacuumed,
2025 s.param5 AS index_vacuum_count,
2026 s.param6 AS max_dead_tuples,
2027 s.param7 AS num_dead_tuples
2028 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)
2029 LEFT JOIN pg_database d ON ((s.datid = d.oid)));
2030 pg_stat_replication| SELECT s.pid,
2031 s.usesysid,
2032 u.rolname AS usename,
2033 s.application_name,
2034 s.client_addr,
2035 s.client_hostname,
2036 s.client_port,
2037 s.backend_start,
2038 s.backend_xmin,
2039 w.state,
2040 w.sent_lsn,
2041 w.write_lsn,
2042 w.flush_lsn,
2043 w.replay_lsn,
2044 w.write_lag,
2045 w.flush_lag,
2046 w.replay_lag,
2047 w.sync_priority,
2048 w.sync_state,
2049 w.reply_time
2050 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, leader_pid, query_id)
2051 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)))
2052 LEFT JOIN pg_authid u ON ((s.usesysid = u.oid)));
2053 pg_stat_replication_slots| SELECT s.slot_name,
2054 s.spill_txns,
2055 s.spill_count,
2056 s.spill_bytes,
2057 s.stream_txns,
2058 s.stream_count,
2059 s.stream_bytes,
2060 s.total_txns,
2061 s.total_bytes,
2062 s.stats_reset
2063 FROM pg_replication_slots r,
2064 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)
2065 WHERE (r.datoid IS NOT NULL);
2066 pg_stat_slru| SELECT s.name,
2067 s.blks_zeroed,
2068 s.blks_hit,
2069 s.blks_read,
2070 s.blks_written,
2071 s.blks_exists,
2072 s.flushes,
2073 s.truncates,
2074 s.stats_reset
2075 FROM pg_stat_get_slru() s(name, blks_zeroed, blks_hit, blks_read, blks_written, blks_exists, flushes, truncates, stats_reset);
2076 pg_stat_ssl| SELECT s.pid,
2077 s.ssl,
2078 s.sslversion AS version,
2079 s.sslcipher AS cipher,
2080 s.sslbits AS bits,
2081 s.ssl_client_dn AS client_dn,
2082 s.ssl_client_serial AS client_serial,
2083 s.ssl_issuer_dn AS issuer_dn
2084 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, leader_pid, query_id)
2085 WHERE (s.client_port IS NOT NULL);
2086 pg_stat_subscription| SELECT su.oid AS subid,
2087 su.subname,
2088 st.pid,
2089 st.relid,
2090 st.received_lsn,
2091 st.last_msg_send_time,
2092 st.last_msg_receipt_time,
2093 st.latest_end_lsn,
2094 st.latest_end_time
2095 FROM (pg_subscription su
2096 LEFT JOIN pg_stat_get_subscription(NULL::oid) st(subid, relid, pid, received_lsn, last_msg_send_time, last_msg_receipt_time, latest_end_lsn, latest_end_time) ON ((st.subid = su.oid)));
2097 pg_stat_sys_indexes| SELECT pg_stat_all_indexes.relid,
2098 pg_stat_all_indexes.indexrelid,
2099 pg_stat_all_indexes.schemaname,
2100 pg_stat_all_indexes.relname,
2101 pg_stat_all_indexes.indexrelname,
2102 pg_stat_all_indexes.idx_scan,
2103 pg_stat_all_indexes.idx_tup_read,
2104 pg_stat_all_indexes.idx_tup_fetch
2105 FROM pg_stat_all_indexes
2106 WHERE ((pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_indexes.schemaname ~ '^pg_toast'::text));
2107 pg_stat_sys_tables| SELECT pg_stat_all_tables.relid,
2108 pg_stat_all_tables.schemaname,
2109 pg_stat_all_tables.relname,
2110 pg_stat_all_tables.seq_scan,
2111 pg_stat_all_tables.seq_tup_read,
2112 pg_stat_all_tables.idx_scan,
2113 pg_stat_all_tables.idx_tup_fetch,
2114 pg_stat_all_tables.n_tup_ins,
2115 pg_stat_all_tables.n_tup_upd,
2116 pg_stat_all_tables.n_tup_del,
2117 pg_stat_all_tables.n_tup_hot_upd,
2118 pg_stat_all_tables.n_live_tup,
2119 pg_stat_all_tables.n_dead_tup,
2120 pg_stat_all_tables.n_mod_since_analyze,
2121 pg_stat_all_tables.n_ins_since_vacuum,
2122 pg_stat_all_tables.last_vacuum,
2123 pg_stat_all_tables.last_autovacuum,
2124 pg_stat_all_tables.last_analyze,
2125 pg_stat_all_tables.last_autoanalyze,
2126 pg_stat_all_tables.vacuum_count,
2127 pg_stat_all_tables.autovacuum_count,
2128 pg_stat_all_tables.analyze_count,
2129 pg_stat_all_tables.autoanalyze_count
2130 FROM pg_stat_all_tables
2131 WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text));
2132 pg_stat_user_functions| SELECT p.oid AS funcid,
2133 n.nspname AS schemaname,
2134 p.proname AS funcname,
2135 pg_stat_get_function_calls(p.oid) AS calls,
2136 pg_stat_get_function_total_time(p.oid) AS total_time,
2137 pg_stat_get_function_self_time(p.oid) AS self_time
2138 FROM (pg_proc p
2139 LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace)))
2140 WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL));
2141 pg_stat_user_indexes| SELECT pg_stat_all_indexes.relid,
2142 pg_stat_all_indexes.indexrelid,
2143 pg_stat_all_indexes.schemaname,
2144 pg_stat_all_indexes.relname,
2145 pg_stat_all_indexes.indexrelname,
2146 pg_stat_all_indexes.idx_scan,
2147 pg_stat_all_indexes.idx_tup_read,
2148 pg_stat_all_indexes.idx_tup_fetch
2149 FROM pg_stat_all_indexes
2150 WHERE ((pg_stat_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_indexes.schemaname !~ '^pg_toast'::text));
2151 pg_stat_user_tables| SELECT pg_stat_all_tables.relid,
2152 pg_stat_all_tables.schemaname,
2153 pg_stat_all_tables.relname,
2154 pg_stat_all_tables.seq_scan,
2155 pg_stat_all_tables.seq_tup_read,
2156 pg_stat_all_tables.idx_scan,
2157 pg_stat_all_tables.idx_tup_fetch,
2158 pg_stat_all_tables.n_tup_ins,
2159 pg_stat_all_tables.n_tup_upd,
2160 pg_stat_all_tables.n_tup_del,
2161 pg_stat_all_tables.n_tup_hot_upd,
2162 pg_stat_all_tables.n_live_tup,
2163 pg_stat_all_tables.n_dead_tup,
2164 pg_stat_all_tables.n_mod_since_analyze,
2165 pg_stat_all_tables.n_ins_since_vacuum,
2166 pg_stat_all_tables.last_vacuum,
2167 pg_stat_all_tables.last_autovacuum,
2168 pg_stat_all_tables.last_analyze,
2169 pg_stat_all_tables.last_autoanalyze,
2170 pg_stat_all_tables.vacuum_count,
2171 pg_stat_all_tables.autovacuum_count,
2172 pg_stat_all_tables.analyze_count,
2173 pg_stat_all_tables.autoanalyze_count
2174 FROM pg_stat_all_tables
2175 WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text));
2176 pg_stat_wal| SELECT w.wal_records,
2177 w.wal_fpi,
2178 w.wal_bytes,
2179 w.wal_buffers_full,
2180 w.wal_write,
2181 w.wal_sync,
2182 w.wal_write_time,
2183 w.wal_sync_time,
2184 w.stats_reset
2185 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);
2186 pg_stat_wal_receiver| SELECT s.pid,
2187 s.status,
2188 s.receive_start_lsn,
2189 s.receive_start_tli,
2190 s.written_lsn,
2191 s.flushed_lsn,
2192 s.received_tli,
2193 s.last_msg_send_time,
2194 s.last_msg_receipt_time,
2195 s.latest_end_lsn,
2196 s.latest_end_time,
2197 s.slot_name,
2198 s.sender_host,
2199 s.sender_port,
2200 s.conninfo
2201 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)
2202 WHERE (s.pid IS NOT NULL);
2203 pg_stat_xact_all_tables| SELECT c.oid AS relid,
2204 n.nspname AS schemaname,
2205 c.relname,
2206 pg_stat_get_xact_numscans(c.oid) AS seq_scan,
2207 pg_stat_get_xact_tuples_returned(c.oid) AS seq_tup_read,
2208 (sum(pg_stat_get_xact_numscans(i.indexrelid)))::bigint AS idx_scan,
2209 ((sum(pg_stat_get_xact_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_xact_tuples_fetched(c.oid)) AS idx_tup_fetch,
2210 pg_stat_get_xact_tuples_inserted(c.oid) AS n_tup_ins,
2211 pg_stat_get_xact_tuples_updated(c.oid) AS n_tup_upd,
2212 pg_stat_get_xact_tuples_deleted(c.oid) AS n_tup_del,
2213 pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd
2214 FROM ((pg_class c
2215 LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
2216 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
2217 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char", 'p'::"char"]))
2218 GROUP BY c.oid, n.nspname, c.relname;
2219 pg_stat_xact_sys_tables| SELECT pg_stat_xact_all_tables.relid,
2220 pg_stat_xact_all_tables.schemaname,
2221 pg_stat_xact_all_tables.relname,
2222 pg_stat_xact_all_tables.seq_scan,
2223 pg_stat_xact_all_tables.seq_tup_read,
2224 pg_stat_xact_all_tables.idx_scan,
2225 pg_stat_xact_all_tables.idx_tup_fetch,
2226 pg_stat_xact_all_tables.n_tup_ins,
2227 pg_stat_xact_all_tables.n_tup_upd,
2228 pg_stat_xact_all_tables.n_tup_del,
2229 pg_stat_xact_all_tables.n_tup_hot_upd
2230 FROM pg_stat_xact_all_tables
2231 WHERE ((pg_stat_xact_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_xact_all_tables.schemaname ~ '^pg_toast'::text));
2232 pg_stat_xact_user_functions| SELECT p.oid AS funcid,
2233 n.nspname AS schemaname,
2234 p.proname AS funcname,
2235 pg_stat_get_xact_function_calls(p.oid) AS calls,
2236 pg_stat_get_xact_function_total_time(p.oid) AS total_time,
2237 pg_stat_get_xact_function_self_time(p.oid) AS self_time
2238 FROM (pg_proc p
2239 LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace)))
2240 WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_xact_function_calls(p.oid) IS NOT NULL));
2241 pg_stat_xact_user_tables| SELECT pg_stat_xact_all_tables.relid,
2242 pg_stat_xact_all_tables.schemaname,
2243 pg_stat_xact_all_tables.relname,
2244 pg_stat_xact_all_tables.seq_scan,
2245 pg_stat_xact_all_tables.seq_tup_read,
2246 pg_stat_xact_all_tables.idx_scan,
2247 pg_stat_xact_all_tables.idx_tup_fetch,
2248 pg_stat_xact_all_tables.n_tup_ins,
2249 pg_stat_xact_all_tables.n_tup_upd,
2250 pg_stat_xact_all_tables.n_tup_del,
2251 pg_stat_xact_all_tables.n_tup_hot_upd
2252 FROM pg_stat_xact_all_tables
2253 WHERE ((pg_stat_xact_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_xact_all_tables.schemaname !~ '^pg_toast'::text));
2254 pg_statio_all_indexes| SELECT c.oid AS relid,
2255 i.oid AS indexrelid,
2256 n.nspname AS schemaname,
2257 c.relname,
2258 i.relname AS indexrelname,
2259 (pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read,
2260 pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit
2261 FROM (((pg_class c
2262 JOIN pg_index x ON ((c.oid = x.indrelid)))
2263 JOIN pg_class i ON ((i.oid = x.indexrelid)))
2264 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
2265 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
2266 pg_statio_all_sequences| SELECT c.oid AS relid,
2267 n.nspname AS schemaname,
2268 c.relname,
2269 (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read,
2270 pg_stat_get_blocks_hit(c.oid) AS blks_hit
2271 FROM (pg_class c
2272 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
2273 WHERE (c.relkind = 'S'::"char");
2274 pg_statio_all_tables| SELECT c.oid AS relid,
2275 n.nspname AS schemaname,
2276 c.relname,
2277 (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS heap_blks_read,
2278 pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit,
2279 (sum((pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))))::bigint AS idx_blks_read,
2280 (sum(pg_stat_get_blocks_hit(i.indexrelid)))::bigint AS idx_blks_hit,
2281 (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read,
2282 pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit,
2283 (pg_stat_get_blocks_fetched(x.indexrelid) - pg_stat_get_blocks_hit(x.indexrelid)) AS tidx_blks_read,
2284 pg_stat_get_blocks_hit(x.indexrelid) AS tidx_blks_hit
2285 FROM ((((pg_class c
2286 LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
2287 LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid)))
2288 LEFT JOIN pg_index x ON ((t.oid = x.indrelid)))
2289 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
2290 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
2291 GROUP BY c.oid, n.nspname, c.relname, t.oid, x.indexrelid;
2292 pg_statio_sys_indexes| SELECT pg_statio_all_indexes.relid,
2293 pg_statio_all_indexes.indexrelid,
2294 pg_statio_all_indexes.schemaname,
2295 pg_statio_all_indexes.relname,
2296 pg_statio_all_indexes.indexrelname,
2297 pg_statio_all_indexes.idx_blks_read,
2298 pg_statio_all_indexes.idx_blks_hit
2299 FROM pg_statio_all_indexes
2300 WHERE ((pg_statio_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_indexes.schemaname ~ '^pg_toast'::text));
2301 pg_statio_sys_sequences| SELECT pg_statio_all_sequences.relid,
2302 pg_statio_all_sequences.schemaname,
2303 pg_statio_all_sequences.relname,
2304 pg_statio_all_sequences.blks_read,
2305 pg_statio_all_sequences.blks_hit
2306 FROM pg_statio_all_sequences
2307 WHERE ((pg_statio_all_sequences.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_sequences.schemaname ~ '^pg_toast'::text));
2308 pg_statio_sys_tables| SELECT pg_statio_all_tables.relid,
2309 pg_statio_all_tables.schemaname,
2310 pg_statio_all_tables.relname,
2311 pg_statio_all_tables.heap_blks_read,
2312 pg_statio_all_tables.heap_blks_hit,
2313 pg_statio_all_tables.idx_blks_read,
2314 pg_statio_all_tables.idx_blks_hit,
2315 pg_statio_all_tables.toast_blks_read,
2316 pg_statio_all_tables.toast_blks_hit,
2317 pg_statio_all_tables.tidx_blks_read,
2318 pg_statio_all_tables.tidx_blks_hit
2319 FROM pg_statio_all_tables
2320 WHERE ((pg_statio_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_tables.schemaname ~ '^pg_toast'::text));
2321 pg_statio_user_indexes| SELECT pg_statio_all_indexes.relid,
2322 pg_statio_all_indexes.indexrelid,
2323 pg_statio_all_indexes.schemaname,
2324 pg_statio_all_indexes.relname,
2325 pg_statio_all_indexes.indexrelname,
2326 pg_statio_all_indexes.idx_blks_read,
2327 pg_statio_all_indexes.idx_blks_hit
2328 FROM pg_statio_all_indexes
2329 WHERE ((pg_statio_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_indexes.schemaname !~ '^pg_toast'::text));
2330 pg_statio_user_sequences| SELECT pg_statio_all_sequences.relid,
2331 pg_statio_all_sequences.schemaname,
2332 pg_statio_all_sequences.relname,
2333 pg_statio_all_sequences.blks_read,
2334 pg_statio_all_sequences.blks_hit
2335 FROM pg_statio_all_sequences
2336 WHERE ((pg_statio_all_sequences.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_sequences.schemaname !~ '^pg_toast'::text));
2337 pg_statio_user_tables| SELECT pg_statio_all_tables.relid,
2338 pg_statio_all_tables.schemaname,
2339 pg_statio_all_tables.relname,
2340 pg_statio_all_tables.heap_blks_read,
2341 pg_statio_all_tables.heap_blks_hit,
2342 pg_statio_all_tables.idx_blks_read,
2343 pg_statio_all_tables.idx_blks_hit,
2344 pg_statio_all_tables.toast_blks_read,
2345 pg_statio_all_tables.toast_blks_hit,
2346 pg_statio_all_tables.tidx_blks_read,
2347 pg_statio_all_tables.tidx_blks_hit
2348 FROM pg_statio_all_tables
2349 WHERE ((pg_statio_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_tables.schemaname !~ '^pg_toast'::text));
2350 pg_stats| SELECT n.nspname AS schemaname,
2351 c.relname AS tablename,
2352 a.attname,
2353 s.stainherit AS inherited,
2354 s.stanullfrac AS null_frac,
2355 s.stawidth AS avg_width,
2356 s.stadistinct AS n_distinct,
2357 CASE
2358 WHEN (s.stakind1 = 1) THEN s.stavalues1
2359 WHEN (s.stakind2 = 1) THEN s.stavalues2
2360 WHEN (s.stakind3 = 1) THEN s.stavalues3
2361 WHEN (s.stakind4 = 1) THEN s.stavalues4
2362 WHEN (s.stakind5 = 1) THEN s.stavalues5
2363 ELSE NULL::anyarray
2364 END AS most_common_vals,
2365 CASE
2366 WHEN (s.stakind1 = 1) THEN s.stanumbers1
2367 WHEN (s.stakind2 = 1) THEN s.stanumbers2
2368 WHEN (s.stakind3 = 1) THEN s.stanumbers3
2369 WHEN (s.stakind4 = 1) THEN s.stanumbers4
2370 WHEN (s.stakind5 = 1) THEN s.stanumbers5
2371 ELSE NULL::real[]
2372 END AS most_common_freqs,
2373 CASE
2374 WHEN (s.stakind1 = 2) THEN s.stavalues1
2375 WHEN (s.stakind2 = 2) THEN s.stavalues2
2376 WHEN (s.stakind3 = 2) THEN s.stavalues3
2377 WHEN (s.stakind4 = 2) THEN s.stavalues4
2378 WHEN (s.stakind5 = 2) THEN s.stavalues5
2379 ELSE NULL::anyarray
2380 END AS histogram_bounds,
2381 CASE
2382 WHEN (s.stakind1 = 3) THEN s.stanumbers1[1]
2383 WHEN (s.stakind2 = 3) THEN s.stanumbers2[1]
2384 WHEN (s.stakind3 = 3) THEN s.stanumbers3[1]
2385 WHEN (s.stakind4 = 3) THEN s.stanumbers4[1]
2386 WHEN (s.stakind5 = 3) THEN s.stanumbers5[1]
2387 ELSE NULL::real
2388 END AS correlation,
2389 CASE
2390 WHEN (s.stakind1 = 4) THEN s.stavalues1
2391 WHEN (s.stakind2 = 4) THEN s.stavalues2
2392 WHEN (s.stakind3 = 4) THEN s.stavalues3
2393 WHEN (s.stakind4 = 4) THEN s.stavalues4
2394 WHEN (s.stakind5 = 4) THEN s.stavalues5
2395 ELSE NULL::anyarray
2396 END AS most_common_elems,
2397 CASE
2398 WHEN (s.stakind1 = 4) THEN s.stanumbers1
2399 WHEN (s.stakind2 = 4) THEN s.stanumbers2
2400 WHEN (s.stakind3 = 4) THEN s.stanumbers3
2401 WHEN (s.stakind4 = 4) THEN s.stanumbers4
2402 WHEN (s.stakind5 = 4) THEN s.stanumbers5
2403 ELSE NULL::real[]
2404 END AS most_common_elem_freqs,
2405 CASE
2406 WHEN (s.stakind1 = 5) THEN s.stanumbers1
2407 WHEN (s.stakind2 = 5) THEN s.stanumbers2
2408 WHEN (s.stakind3 = 5) THEN s.stanumbers3
2409 WHEN (s.stakind4 = 5) THEN s.stanumbers4
2410 WHEN (s.stakind5 = 5) THEN s.stanumbers5
2411 ELSE NULL::real[]
2412 END AS elem_count_histogram
2413 FROM (((pg_statistic s
2414 JOIN pg_class c ON ((c.oid = s.starelid)))
2415 JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum))))
2416 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
2417 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))));
2418 pg_stats_ext| SELECT cn.nspname AS schemaname,
2419 c.relname AS tablename,
2420 sn.nspname AS statistics_schemaname,
2421 s.stxname AS statistics_name,
2422 pg_get_userbyid(s.stxowner) AS statistics_owner,
2423 ( SELECT array_agg(a.attname ORDER BY a.attnum) AS array_agg
2424 FROM (unnest(s.stxkeys) k(k)
2425 JOIN pg_attribute a ON (((a.attrelid = s.stxrelid) AND (a.attnum = k.k))))) AS attnames,
2426 pg_get_statisticsobjdef_expressions(s.oid) AS exprs,
2427 s.stxkind AS kinds,
2428 sd.stxdndistinct AS n_distinct,
2429 sd.stxddependencies AS dependencies,
2430 m.most_common_vals,
2431 m.most_common_val_nulls,
2432 m.most_common_freqs,
2433 m.most_common_base_freqs
2434 FROM (((((pg_statistic_ext s
2435 JOIN pg_class c ON ((c.oid = s.stxrelid)))
2436 JOIN pg_statistic_ext_data sd ON ((s.oid = sd.stxoid)))
2437 LEFT JOIN pg_namespace cn ON ((cn.oid = c.relnamespace)))
2438 LEFT JOIN pg_namespace sn ON ((sn.oid = s.stxnamespace)))
2439 LEFT JOIN LATERAL ( SELECT array_agg(pg_mcv_list_items."values") AS most_common_vals,
2440 array_agg(pg_mcv_list_items.nulls) AS most_common_val_nulls,
2441 array_agg(pg_mcv_list_items.frequency) AS most_common_freqs,
2442 array_agg(pg_mcv_list_items.base_frequency) AS most_common_base_freqs
2443 FROM pg_mcv_list_items(sd.stxdmcv) pg_mcv_list_items(index, "values", nulls, frequency, base_frequency)) m ON ((sd.stxdmcv IS NOT NULL)))
2444 WHERE ((NOT (EXISTS ( SELECT 1
2445 FROM (unnest(s.stxkeys) k(k)
2446 JOIN pg_attribute a ON (((a.attrelid = s.stxrelid) AND (a.attnum = k.k))))
2447 WHERE (NOT has_column_privilege(c.oid, a.attnum, 'select'::text))))) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid))));
2448 pg_stats_ext_exprs| SELECT cn.nspname AS schemaname,
2449 c.relname AS tablename,
2450 sn.nspname AS statistics_schemaname,
2451 s.stxname AS statistics_name,
2452 pg_get_userbyid(s.stxowner) AS statistics_owner,
2453 stat.expr,
2454 (stat.a).stanullfrac AS null_frac,
2455 (stat.a).stawidth AS avg_width,
2456 (stat.a).stadistinct AS n_distinct,
2457 CASE
2458 WHEN ((stat.a).stakind1 = 1) THEN (stat.a).stavalues1
2459 WHEN ((stat.a).stakind2 = 1) THEN (stat.a).stavalues2
2460 WHEN ((stat.a).stakind3 = 1) THEN (stat.a).stavalues3
2461 WHEN ((stat.a).stakind4 = 1) THEN (stat.a).stavalues4
2462 WHEN ((stat.a).stakind5 = 1) THEN (stat.a).stavalues5
2463 ELSE NULL::anyarray
2464 END AS most_common_vals,
2465 CASE
2466 WHEN ((stat.a).stakind1 = 1) THEN (stat.a).stanumbers1
2467 WHEN ((stat.a).stakind2 = 1) THEN (stat.a).stanumbers2
2468 WHEN ((stat.a).stakind3 = 1) THEN (stat.a).stanumbers3
2469 WHEN ((stat.a).stakind4 = 1) THEN (stat.a).stanumbers4
2470 WHEN ((stat.a).stakind5 = 1) THEN (stat.a).stanumbers5
2471 ELSE NULL::real[]
2472 END AS most_common_freqs,
2473 CASE
2474 WHEN ((stat.a).stakind1 = 2) THEN (stat.a).stavalues1
2475 WHEN ((stat.a).stakind2 = 2) THEN (stat.a).stavalues2
2476 WHEN ((stat.a).stakind3 = 2) THEN (stat.a).stavalues3
2477 WHEN ((stat.a).stakind4 = 2) THEN (stat.a).stavalues4
2478 WHEN ((stat.a).stakind5 = 2) THEN (stat.a).stavalues5
2479 ELSE NULL::anyarray
2480 END AS histogram_bounds,
2481 CASE
2482 WHEN ((stat.a).stakind1 = 3) THEN (stat.a).stanumbers1[1]
2483 WHEN ((stat.a).stakind2 = 3) THEN (stat.a).stanumbers2[1]
2484 WHEN ((stat.a).stakind3 = 3) THEN (stat.a).stanumbers3[1]
2485 WHEN ((stat.a).stakind4 = 3) THEN (stat.a).stanumbers4[1]
2486 WHEN ((stat.a).stakind5 = 3) THEN (stat.a).stanumbers5[1]
2487 ELSE NULL::real
2488 END AS correlation,
2489 CASE
2490 WHEN ((stat.a).stakind1 = 4) THEN (stat.a).stavalues1
2491 WHEN ((stat.a).stakind2 = 4) THEN (stat.a).stavalues2
2492 WHEN ((stat.a).stakind3 = 4) THEN (stat.a).stavalues3
2493 WHEN ((stat.a).stakind4 = 4) THEN (stat.a).stavalues4
2494 WHEN ((stat.a).stakind5 = 4) THEN (stat.a).stavalues5
2495 ELSE NULL::anyarray
2496 END AS most_common_elems,
2497 CASE
2498 WHEN ((stat.a).stakind1 = 4) THEN (stat.a).stanumbers1
2499 WHEN ((stat.a).stakind2 = 4) THEN (stat.a).stanumbers2
2500 WHEN ((stat.a).stakind3 = 4) THEN (stat.a).stanumbers3
2501 WHEN ((stat.a).stakind4 = 4) THEN (stat.a).stanumbers4
2502 WHEN ((stat.a).stakind5 = 4) THEN (stat.a).stanumbers5
2503 ELSE NULL::real[]
2504 END AS most_common_elem_freqs,
2505 CASE
2506 WHEN ((stat.a).stakind1 = 5) THEN (stat.a).stanumbers1
2507 WHEN ((stat.a).stakind2 = 5) THEN (stat.a).stanumbers2
2508 WHEN ((stat.a).stakind3 = 5) THEN (stat.a).stanumbers3
2509 WHEN ((stat.a).stakind4 = 5) THEN (stat.a).stanumbers4
2510 WHEN ((stat.a).stakind5 = 5) THEN (stat.a).stanumbers5
2511 ELSE NULL::real[]
2512 END AS elem_count_histogram
2513 FROM (((((pg_statistic_ext s
2514 JOIN pg_class c ON ((c.oid = s.stxrelid)))
2515 LEFT JOIN pg_statistic_ext_data sd ON ((s.oid = sd.stxoid)))
2516 LEFT JOIN pg_namespace cn ON ((cn.oid = c.relnamespace)))
2517 LEFT JOIN pg_namespace sn ON ((sn.oid = s.stxnamespace)))
2518 JOIN LATERAL ( SELECT unnest(pg_get_statisticsobjdef_expressions(s.oid)) AS expr,
2519 unnest(sd.stxdexpr) AS a) stat ON ((stat.expr IS NOT NULL)));
2520 pg_tables| SELECT n.nspname AS schemaname,
2521 c.relname AS tablename,
2522 pg_get_userbyid(c.relowner) AS tableowner,
2523 t.spcname AS tablespace,
2524 c.relhasindex AS hasindexes,
2525 c.relhasrules AS hasrules,
2526 c.relhastriggers AS hastriggers,
2527 c.relrowsecurity AS rowsecurity
2528 FROM ((pg_class c
2529 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
2530 LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace)))
2531 WHERE (c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"]));
2532 pg_timezone_abbrevs| SELECT pg_timezone_abbrevs.abbrev,
2533 pg_timezone_abbrevs.utc_offset,
2534 pg_timezone_abbrevs.is_dst
2535 FROM pg_timezone_abbrevs() pg_timezone_abbrevs(abbrev, utc_offset, is_dst);
2536 pg_timezone_names| SELECT pg_timezone_names.name,
2537 pg_timezone_names.abbrev,
2538 pg_timezone_names.utc_offset,
2539 pg_timezone_names.is_dst
2540 FROM pg_timezone_names() pg_timezone_names(name, abbrev, utc_offset, is_dst);
2541 pg_user| SELECT pg_shadow.usename,
2542 pg_shadow.usesysid,
2543 pg_shadow.usecreatedb,
2544 pg_shadow.usesuper,
2545 pg_shadow.userepl,
2546 pg_shadow.usebypassrls,
2547 '********'::text AS passwd,
2548 pg_shadow.valuntil,
2549 pg_shadow.useconfig
2550 FROM pg_shadow;
2551 pg_user_mappings| SELECT u.oid AS umid,
2552 s.oid AS srvid,
2553 s.srvname,
2554 u.umuser,
2555 CASE
2556 WHEN (u.umuser = (0)::oid) THEN 'public'::name
2557 ELSE a.rolname
2558 END AS usename,
2559 CASE
2560 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
2561 FROM pg_authid
2562 WHERE (pg_authid.rolname = CURRENT_USER))) THEN u.umoptions
2563 ELSE NULL::text[]
2564 END AS umoptions
2565 FROM ((pg_user_mapping u
2566 JOIN pg_foreign_server s ON ((u.umserver = s.oid)))
2567 LEFT JOIN pg_authid a ON ((a.oid = u.umuser)));
2568 pg_views| SELECT n.nspname AS schemaname,
2569 c.relname AS viewname,
2570 pg_get_userbyid(c.relowner) AS viewowner,
2571 pg_get_viewdef(c.oid) AS definition
2572 FROM (pg_class c
2573 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
2574 WHERE (c.relkind = 'v'::"char");
2575 rtest_v1| SELECT rtest_t1.a,
2576 rtest_t1.b
2577 FROM rtest_t1;
2578 rtest_vcomp| SELECT x.part,
2579 (x.size * y.factor) AS size_in_cm
2580 FROM rtest_comp x,
2581 rtest_unitfact y
2582 WHERE (x.unit = y.unit);
2583 rtest_vview1| SELECT x.a,
2584 x.b
2585 FROM rtest_view1 x
2586 WHERE (0 < ( SELECT count(*) AS count
2587 FROM rtest_view2 y
2588 WHERE (y.a = x.a)));
2589 rtest_vview2| SELECT rtest_view1.a,
2590 rtest_view1.b
2591 FROM rtest_view1
2592 WHERE rtest_view1.v;
2593 rtest_vview3| SELECT x.a,
2594 x.b
2595 FROM rtest_vview2 x
2596 WHERE (0 < ( SELECT count(*) AS count
2597 FROM rtest_view2 y
2598 WHERE (y.a = x.a)));
2599 rtest_vview4| SELECT x.a,
2600 x.b,
2601 count(y.a) AS refcount
2602 FROM rtest_view1 x,
2603 rtest_view2 y
2604 WHERE (x.a = y.a)
2605 GROUP BY x.a, x.b;
2606 rtest_vview5| SELECT rtest_view1.a,
2607 rtest_view1.b,
2608 rtest_viewfunc1(rtest_view1.a) AS refcount
2609 FROM rtest_view1;
2610 shoe| SELECT sh.shoename,
2611 sh.sh_avail,
2612 sh.slcolor,
2613 sh.slminlen,
2614 (sh.slminlen * un.un_fact) AS slminlen_cm,
2615 sh.slmaxlen,
2616 (sh.slmaxlen * un.un_fact) AS slmaxlen_cm,
2617 sh.slunit
2618 FROM shoe_data sh,
2619 unit un
2620 WHERE (sh.slunit = un.un_name);
2621 shoe_ready| SELECT rsh.shoename,
2622 rsh.sh_avail,
2623 rsl.sl_name,
2624 rsl.sl_avail,
2625 int4smaller(rsh.sh_avail, rsl.sl_avail) AS total_avail
2626 FROM shoe rsh,
2627 shoelace rsl
2628 WHERE ((rsl.sl_color = rsh.slcolor) AND (rsl.sl_len_cm >= rsh.slminlen_cm) AND (rsl.sl_len_cm <= rsh.slmaxlen_cm));
2629 shoelace| SELECT s.sl_name,
2630 s.sl_avail,
2631 s.sl_color,
2632 s.sl_len,
2633 s.sl_unit,
2634 (s.sl_len * u.un_fact) AS sl_len_cm
2635 FROM shoelace_data s,
2636 unit u
2637 WHERE (s.sl_unit = u.un_name);
2638 shoelace_candelete| SELECT shoelace_obsolete.sl_name,
2639 shoelace_obsolete.sl_avail,
2640 shoelace_obsolete.sl_color,
2641 shoelace_obsolete.sl_len,
2642 shoelace_obsolete.sl_unit,
2643 shoelace_obsolete.sl_len_cm
2644 FROM shoelace_obsolete
2645 WHERE (shoelace_obsolete.sl_avail = 0);
2646 shoelace_obsolete| SELECT shoelace.sl_name,
2647 shoelace.sl_avail,
2648 shoelace.sl_color,
2649 shoelace.sl_len,
2650 shoelace.sl_unit,
2651 shoelace.sl_len_cm
2652 FROM shoelace
2653 WHERE (NOT (EXISTS ( SELECT shoe.shoename
2654 FROM shoe
2655 WHERE (shoe.slcolor = shoelace.sl_color))));
2656 street| SELECT r.name,
2657 r.thepath,
2658 c.cname
2659 FROM ONLY road r,
2660 real_city c
2661 WHERE (c.outline ## r.thepath);
2662 test_tablesample_v1| SELECT test_tablesample.id
2663 FROM test_tablesample TABLESAMPLE system ((10 * 2)) REPEATABLE (2);
2664 test_tablesample_v2| SELECT test_tablesample.id
2665 FROM test_tablesample TABLESAMPLE system (99);
2666 toyemp| SELECT emp.name,
2667 emp.age,
2668 emp.location,
2669 (12 * emp.salary) AS annualsal
2670 FROM emp;
2671 SELECT tablename, rulename, definition FROM pg_rules
2672 WHERE schemaname IN ('pg_catalog', 'public')
2673 ORDER BY tablename, rulename;
2674 pg_settings|pg_settings_n|CREATE RULE pg_settings_n AS
2675 ON UPDATE TO pg_catalog.pg_settings DO INSTEAD NOTHING;
2676 pg_settings|pg_settings_u|CREATE RULE pg_settings_u AS
2677 ON UPDATE TO pg_catalog.pg_settings
2678 WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, false) AS set_config;
2679 rtest_emp|rtest_emp_del|CREATE RULE rtest_emp_del AS
2680 ON DELETE TO public.rtest_emp DO INSERT INTO rtest_emplog (ename, who, action, newsal, oldsal)
2681 VALUES (old.ename, CURRENT_USER, 'fired'::bpchar, '$0.00'::money, old.salary);
2682 rtest_emp|rtest_emp_ins|CREATE RULE rtest_emp_ins AS
2683 ON INSERT TO public.rtest_emp DO INSERT INTO rtest_emplog (ename, who, action, newsal, oldsal)
2684 VALUES (new.ename, CURRENT_USER, 'hired'::bpchar, new.salary, '$0.00'::money);
2685 rtest_emp|rtest_emp_upd|CREATE RULE rtest_emp_upd AS
2686 ON UPDATE TO public.rtest_emp
2687 WHERE (new.salary <> old.salary) DO INSERT INTO rtest_emplog (ename, who, action, newsal, oldsal)
2688 VALUES (new.ename, CURRENT_USER, 'honored'::bpchar, new.salary, old.salary);
2689 rtest_nothn1|rtest_nothn_r1|CREATE RULE rtest_nothn_r1 AS
2690 ON INSERT TO public.rtest_nothn1
2691 WHERE ((new.a >= 10) AND (new.a < 20)) DO INSTEAD NOTHING;
2692 rtest_nothn1|rtest_nothn_r2|CREATE RULE rtest_nothn_r2 AS
2693 ON INSERT TO public.rtest_nothn1
2694 WHERE ((new.a >= 30) AND (new.a < 40)) DO INSTEAD NOTHING;
2695 rtest_nothn2|rtest_nothn_r3|CREATE RULE rtest_nothn_r3 AS
2696 ON INSERT TO public.rtest_nothn2
2697 WHERE (new.a >= 100) DO INSTEAD INSERT INTO rtest_nothn3 (a, b)
2698 VALUES (new.a, new.b);
2699 rtest_nothn2|rtest_nothn_r4|CREATE RULE rtest_nothn_r4 AS
2700 ON INSERT TO public.rtest_nothn2 DO INSTEAD NOTHING;
2701 rtest_order1|rtest_order_r1|CREATE RULE rtest_order_r1 AS
2702 ON INSERT TO public.rtest_order1 DO INSTEAD INSERT INTO rtest_order2 (a, b, c)
2703 VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 1 - this should run 1st'::text);
2704 rtest_order1|rtest_order_r2|CREATE RULE rtest_order_r2 AS
2705 ON INSERT TO public.rtest_order1 DO INSERT INTO rtest_order2 (a, b, c)
2706 VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 2 - this should run 2nd'::text);
2707 rtest_order1|rtest_order_r3|CREATE RULE rtest_order_r3 AS
2708 ON INSERT TO public.rtest_order1 DO INSTEAD INSERT INTO rtest_order2 (a, b, c)
2709 VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 3 - this should run 3rd'::text);
2710 rtest_order1|rtest_order_r4|CREATE RULE rtest_order_r4 AS
2711 ON INSERT TO public.rtest_order1
2712 WHERE (new.a < 100) DO INSTEAD INSERT INTO rtest_order2 (a, b, c)
2713 VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 4 - this should run 4th'::text);
2714 rtest_person|rtest_pers_del|CREATE RULE rtest_pers_del AS
2715 ON DELETE TO public.rtest_person DO DELETE FROM rtest_admin
2716 WHERE (rtest_admin.pname = old.pname);
2717 rtest_person|rtest_pers_upd|CREATE RULE rtest_pers_upd AS
2718 ON UPDATE TO public.rtest_person DO UPDATE rtest_admin SET pname = new.pname
2719 WHERE (rtest_admin.pname = old.pname);
2720 rtest_system|rtest_sys_del|CREATE RULE rtest_sys_del AS
2721 ON DELETE TO public.rtest_system DO ( DELETE FROM rtest_interface
2722 WHERE (rtest_interface.sysname = old.sysname);
2723 DELETE FROM rtest_admin
2724 WHERE (rtest_admin.sysname = old.sysname);
2725 );
2726 rtest_system|rtest_sys_upd|CREATE RULE rtest_sys_upd AS
2727 ON UPDATE TO public.rtest_system DO ( UPDATE rtest_interface SET sysname = new.sysname
2728 WHERE (rtest_interface.sysname = old.sysname);
2729 UPDATE rtest_admin SET sysname = new.sysname
2730 WHERE (rtest_admin.sysname = old.sysname);
2731 );
2732 rtest_t4|rtest_t4_ins1|CREATE RULE rtest_t4_ins1 AS
2733 ON INSERT TO public.rtest_t4
2734 WHERE ((new.a >= 10) AND (new.a < 20)) DO INSTEAD INSERT INTO rtest_t5 (a, b)
2735 VALUES (new.a, new.b);
2736 rtest_t4|rtest_t4_ins2|CREATE RULE rtest_t4_ins2 AS
2737 ON INSERT TO public.rtest_t4
2738 WHERE ((new.a >= 20) AND (new.a < 30)) DO INSERT INTO rtest_t6 (a, b)
2739 VALUES (new.a, new.b);
2740 rtest_t5|rtest_t5_ins|CREATE RULE rtest_t5_ins AS
2741 ON INSERT TO public.rtest_t5
2742 WHERE (new.a > 15) DO INSERT INTO rtest_t7 (a, b)
2743 VALUES (new.a, new.b);
2744 rtest_t6|rtest_t6_ins|CREATE RULE rtest_t6_ins AS
2745 ON INSERT TO public.rtest_t6
2746 WHERE (new.a > 25) DO INSTEAD INSERT INTO rtest_t8 (a, b)
2747 VALUES (new.a, new.b);
2748 rtest_v1|rtest_v1_del|CREATE RULE rtest_v1_del AS
2749 ON DELETE TO public.rtest_v1 DO INSTEAD DELETE FROM rtest_t1
2750 WHERE (rtest_t1.a = old.a);
2751 rtest_v1|rtest_v1_ins|CREATE RULE rtest_v1_ins AS
2752 ON INSERT TO public.rtest_v1 DO INSTEAD INSERT INTO rtest_t1 (a, b)
2753 VALUES (new.a, new.b);
2754 rtest_v1|rtest_v1_upd|CREATE RULE rtest_v1_upd AS
2755 ON UPDATE TO public.rtest_v1 DO INSTEAD UPDATE rtest_t1 SET a = new.a, b = new.b
2756 WHERE (rtest_t1.a = old.a);
2757 shoelace|shoelace_del|CREATE RULE shoelace_del AS
2758 ON DELETE TO public.shoelace DO INSTEAD DELETE FROM shoelace_data
2759 WHERE (shoelace_data.sl_name = old.sl_name);
2760 shoelace|shoelace_ins|CREATE RULE shoelace_ins AS
2761 ON INSERT TO public.shoelace DO INSTEAD INSERT INTO shoelace_data (sl_name, sl_avail, sl_color, sl_len, sl_unit)
2762 VALUES (new.sl_name, new.sl_avail, new.sl_color, new.sl_len, new.sl_unit);
2763 shoelace|shoelace_upd|CREATE RULE shoelace_upd AS
2764 ON UPDATE TO public.shoelace DO INSTEAD UPDATE shoelace_data SET sl_name = new.sl_name, sl_avail = new.sl_avail, sl_color = new.sl_color, sl_len = new.sl_len, sl_unit = new.sl_unit
2765 WHERE (shoelace_data.sl_name = old.sl_name);
2766 shoelace_data|log_shoelace|CREATE RULE log_shoelace AS
2767 ON UPDATE TO public.shoelace_data
2768 WHERE (new.sl_avail <> old.sl_avail) DO INSERT INTO shoelace_log (sl_name, sl_avail, log_who, log_when)
2769 VALUES (new.sl_name, new.sl_avail, 'Al Bundy'::name, 'Thu Jan 01 00:00:00 1970'::timestamp without time zone);
2770 shoelace_ok|shoelace_ok_ins|CREATE RULE shoelace_ok_ins AS
2771 ON INSERT TO public.shoelace_ok DO INSTEAD UPDATE shoelace SET sl_avail = (shoelace.sl_avail + new.ok_quant)
2772 WHERE (shoelace.sl_name = new.ok_name);
2773 -- restore normal output mode
2774 \a\t
2775 --
2776 -- CREATE OR REPLACE RULE
2777 --
2778 CREATE TABLE ruletest_tbl (a int, b int);
2779 CREATE TABLE ruletest_tbl2 (a int, b int);
2780 CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl
2781 DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (10, 10);
2782 INSERT INTO ruletest_tbl VALUES (99, 99);
2783 CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl
2784 DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (1000, 1000);
2785 INSERT INTO ruletest_tbl VALUES (99, 99);
2786 SELECT * FROM ruletest_tbl2;
2787 a | b
2788 ------+------
2789 10 | 10
2790 1000 | 1000
2791 (2 rows)
2793 -- Check that rewrite rules splitting one INSERT into multiple
2794 -- conditional statements does not disable FK checking.
2795 create table rule_and_refint_t1 (
2796 id1a integer,
2797 id1b integer,
2798 primary key (id1a, id1b)
2799 );
2800 create table rule_and_refint_t2 (
2801 id2a integer,
2802 id2c integer,
2803 primary key (id2a, id2c)
2804 );
2805 create table rule_and_refint_t3 (
2806 id3a integer,
2807 id3b integer,
2808 id3c integer,
2809 data text,
2810 primary key (id3a, id3b, id3c),
2811 foreign key (id3a, id3b) references rule_and_refint_t1 (id1a, id1b),
2812 foreign key (id3a, id3c) references rule_and_refint_t2 (id2a, id2c)
2813 );
2814 insert into rule_and_refint_t1 values (1, 11);
2815 insert into rule_and_refint_t1 values (1, 12);
2816 insert into rule_and_refint_t1 values (2, 21);
2817 insert into rule_and_refint_t1 values (2, 22);
2818 insert into rule_and_refint_t2 values (1, 11);
2819 insert into rule_and_refint_t2 values (1, 12);
2820 insert into rule_and_refint_t2 values (2, 21);
2821 insert into rule_and_refint_t2 values (2, 22);
2822 insert into rule_and_refint_t3 values (1, 11, 11, 'row1');
2823 insert into rule_and_refint_t3 values (1, 11, 12, 'row2');
2824 insert into rule_and_refint_t3 values (1, 12, 11, 'row3');
2825 insert into rule_and_refint_t3 values (1, 12, 12, 'row4');
2826 insert into rule_and_refint_t3 values (1, 11, 13, 'row5');
2827 ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_id3c_fkey"
2828 DETAIL: Key (id3a, id3c)=(1, 13) is not present in table "rule_and_refint_t2".
2829 insert into rule_and_refint_t3 values (1, 13, 11, 'row6');
2830 ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_id3b_fkey"
2831 DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1".
2832 -- Ordinary table
2833 insert into rule_and_refint_t3 values (1, 13, 11, 'row6')
2834 on conflict do nothing;
2835 ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_id3b_fkey"
2836 DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1".
2837 -- rule not fired, so fk violation
2838 insert into rule_and_refint_t3 values (1, 13, 11, 'row6')
2839 on conflict (id3a, id3b, id3c) do update
2840 set id3b = excluded.id3b;
2841 ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_id3b_fkey"
2842 DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1".
2843 -- rule fired, so unsupported
2844 insert into shoelace values ('sl9', 0, 'pink', 35.0, 'inch', 0.0)
2845 on conflict (sl_name) do update
2846 set sl_avail = excluded.sl_avail;
2847 ERROR: INSERT with ON CONFLICT clause cannot be used with table that has INSERT or UPDATE rules
2848 create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3
2849 where (exists (select 1 from rule_and_refint_t3
2850 where (((rule_and_refint_t3.id3a = new.id3a)
2851 and (rule_and_refint_t3.id3b = new.id3b))
2852 and (rule_and_refint_t3.id3c = new.id3c))))
2853 do instead update rule_and_refint_t3 set data = new.data
2854 where (((rule_and_refint_t3.id3a = new.id3a)
2855 and (rule_and_refint_t3.id3b = new.id3b))
2856 and (rule_and_refint_t3.id3c = new.id3c));
2857 insert into rule_and_refint_t3 values (1, 11, 13, 'row7');
2858 ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_id3c_fkey"
2859 DETAIL: Key (id3a, id3c)=(1, 13) is not present in table "rule_and_refint_t2".
2860 insert into rule_and_refint_t3 values (1, 13, 11, 'row8');
2861 ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_id3b_fkey"
2862 DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1".
2863 --
2864 -- disallow dropping a view's rule (bug #5072)
2865 --
2866 create view rules_fooview as select 'rules_foo'::text;
2867 drop rule "_RETURN" on rules_fooview;
2868 ERROR: cannot drop rule _RETURN on view rules_fooview because view rules_fooview requires it
2869 HINT: You can drop view rules_fooview instead.
2870 drop view rules_fooview;
2871 --
2872 -- test conversion of table to view (needed to load some pg_dump files)
2873 --
2874 create table rules_fooview (x int, y text);
2875 select xmin, * from rules_fooview;
2876 xmin | x | y
2877 ------+---+---
2878 (0 rows)
2880 create rule "_RETURN" as on select to rules_fooview do instead
2881 select 1 as x, 'aaa'::text as y;
2882 select * from rules_fooview;
2883 x | y
2884 ---+-----
2885 1 | aaa
2886 (1 row)
2888 select xmin, * from rules_fooview; -- fail, views don't have such a column
2889 ERROR: column "xmin" does not exist
2890 LINE 1: select xmin, * from rules_fooview;
2891 ^
2892 select reltoastrelid, relkind, relfrozenxid
2893 from pg_class where oid = 'rules_fooview'::regclass;
2894 reltoastrelid | relkind | relfrozenxid
2895 ---------------+---------+--------------
2896 0 | v | 0
2897 (1 row)
2899 drop view rules_fooview;
2900 -- cannot convert an inheritance parent or child to a view, though
2901 create table rules_fooview (x int, y text);
2902 create table rules_fooview_child () inherits (rules_fooview);
2903 create rule "_RETURN" as on select to rules_fooview do instead
2904 select 1 as x, 'aaa'::text as y;
2905 ERROR: could not convert table "rules_fooview" to a view because it has child tables
2906 create rule "_RETURN" as on select to rules_fooview_child do instead
2907 select 1 as x, 'aaa'::text as y;
2908 ERROR: could not convert table "rules_fooview_child" to a view because it has parent tables
2909 drop table rules_fooview cascade;
2910 NOTICE: drop cascades to table rules_fooview_child
2911 -- likewise, converting a partitioned table or partition to view is not allowed
2912 create table rules_fooview (x int, y text) partition by list (x);
2913 create rule "_RETURN" as on select to rules_fooview do instead
2914 select 1 as x, 'aaa'::text as y;
2915 ERROR: cannot convert partitioned table "rules_fooview" to a view
2916 create table rules_fooview_part partition of rules_fooview for values in (1);
2917 create rule "_RETURN" as on select to rules_fooview_part do instead
2918 select 1 as x, 'aaa'::text as y;
2919 ERROR: cannot convert partition "rules_fooview_part" to a view
2920 drop table rules_fooview;
2921 --
2922 -- check for planner problems with complex inherited UPDATES
2923 --
2924 create table id (id serial primary key, name text);
2925 -- currently, must respecify PKEY for each inherited subtable
2926 create table test_1 (id integer primary key) inherits (id);
2927 NOTICE: merging column "id" with inherited definition
2928 create table test_2 (id integer primary key) inherits (id);
2929 NOTICE: merging column "id" with inherited definition
2930 create table test_3 (id integer primary key) inherits (id);
2931 NOTICE: merging column "id" with inherited definition
2932 insert into test_1 (name) values ('Test 1');
2933 insert into test_1 (name) values ('Test 2');
2934 insert into test_2 (name) values ('Test 3');
2935 insert into test_2 (name) values ('Test 4');
2936 insert into test_3 (name) values ('Test 5');
2937 insert into test_3 (name) values ('Test 6');
2938 create view id_ordered as select * from id order by id;
2939 create rule update_id_ordered as on update to id_ordered
2940 do instead update id set name = new.name where id = old.id;
2941 select * from id_ordered;
2942 id | name
2943 ----+--------
2944 1 | Test 1
2945 2 | Test 2
2946 3 | Test 3
2947 4 | Test 4
2948 5 | Test 5
2949 6 | Test 6
2950 (6 rows)
2952 update id_ordered set name = 'update 2' where id = 2;
2953 update id_ordered set name = 'update 4' where id = 4;
2954 update id_ordered set name = 'update 5' where id = 5;
2955 select * from id_ordered;
2956 id | name
2957 ----+----------
2958 1 | Test 1
2959 2 | update 2
2960 3 | Test 3
2961 4 | update 4
2962 5 | update 5
2963 6 | Test 6
2964 (6 rows)
2966 drop table id cascade;
2967 NOTICE: drop cascades to 4 other objects
2968 DETAIL: drop cascades to table test_1
2969 drop cascades to table test_2
2970 drop cascades to table test_3
2971 drop cascades to view id_ordered
2972 --
2973 -- check corner case where an entirely-dummy subplan is created by
2974 -- constraint exclusion
2975 --
2976 create temp table t1 (a integer primary key);
2977 create temp table t1_1 (check (a >= 0 and a < 10)) inherits (t1);
2978 create temp table t1_2 (check (a >= 10 and a < 20)) inherits (t1);
2979 create rule t1_ins_1 as on insert to t1
2980 where new.a >= 0 and new.a < 10
2981 do instead
2982 insert into t1_1 values (new.a);
2983 create rule t1_ins_2 as on insert to t1
2984 where new.a >= 10 and new.a < 20
2985 do instead
2986 insert into t1_2 values (new.a);
2987 create rule t1_upd_1 as on update to t1
2988 where old.a >= 0 and old.a < 10
2989 do instead
2990 update t1_1 set a = new.a where a = old.a;
2991 create rule t1_upd_2 as on update to t1
2992 where old.a >= 10 and old.a < 20
2993 do instead
2994 update t1_2 set a = new.a where a = old.a;
2995 set constraint_exclusion = on;
2996 insert into t1 select * from generate_series(5,19,1) g;
2997 update t1 set a = 4 where a = 5;
2998 select * from only t1;
2999 a
3000 ---
3001 (0 rows)
3003 select * from only t1_1;
3004 a
3005 ---
3006 6
3007 7
3008 8
3009 9
3010 4
3011 (5 rows)
3013 select * from only t1_2;
3014 a
3015 ----
3016 10
3017 11
3018 12
3019 13
3020 14
3021 15
3022 16
3023 17
3024 18
3025 19
3026 (10 rows)
3028 reset constraint_exclusion;
3029 -- test FOR UPDATE in rules
3030 create table rules_base(f1 int, f2 int);
3031 insert into rules_base values(1,2), (11,12);
3032 create rule r1 as on update to rules_base do instead
3033 select * from rules_base where f1 = 1 for update;
3034 update rules_base set f2 = f2 + 1;
3035 f1 | f2
3036 ----+----
3037 1 | 2
3038 (1 row)
3040 create or replace rule r1 as on update to rules_base do instead
3041 select * from rules_base where f1 = 11 for update of rules_base;
3042 update rules_base set f2 = f2 + 1;
3043 f1 | f2
3044 ----+----
3045 11 | 12
3046 (1 row)
3048 create or replace rule r1 as on update to rules_base do instead
3049 select * from rules_base where f1 = 11 for update of old; -- error
3050 ERROR: relation "old" in FOR UPDATE clause not found in FROM clause
3051 LINE 2: select * from rules_base where f1 = 11 for update of old;
3052 ^
3053 drop table rules_base;
3054 -- test various flavors of pg_get_viewdef()
3055 select pg_get_viewdef('shoe'::regclass) as unpretty;
3056 unpretty
3057 ------------------------------------------------
3058 SELECT sh.shoename, +
3059 sh.sh_avail, +
3060 sh.slcolor, +
3061 sh.slminlen, +
3062 (sh.slminlen * un.un_fact) AS slminlen_cm,+
3063 sh.slmaxlen, +
3064 (sh.slmaxlen * un.un_fact) AS slmaxlen_cm,+
3065 sh.slunit +
3066 FROM shoe_data sh, +
3067 unit un +
3068 WHERE (sh.slunit = un.un_name);
3069 (1 row)
3071 select pg_get_viewdef('shoe'::regclass,true) as pretty;
3072 pretty
3073 ----------------------------------------------
3074 SELECT sh.shoename, +
3075 sh.sh_avail, +
3076 sh.slcolor, +
3077 sh.slminlen, +
3078 sh.slminlen * un.un_fact AS slminlen_cm,+
3079 sh.slmaxlen, +
3080 sh.slmaxlen * un.un_fact AS slmaxlen_cm,+
3081 sh.slunit +
3082 FROM shoe_data sh, +
3083 unit un +
3084 WHERE sh.slunit = un.un_name;
3085 (1 row)
3087 select pg_get_viewdef('shoe'::regclass,0) as prettier;
3088 prettier
3089 ----------------------------------------------
3090 SELECT sh.shoename, +
3091 sh.sh_avail, +
3092 sh.slcolor, +
3093 sh.slminlen, +
3094 sh.slminlen * un.un_fact AS slminlen_cm,+
3095 sh.slmaxlen, +
3096 sh.slmaxlen * un.un_fact AS slmaxlen_cm,+
3097 sh.slunit +
3098 FROM shoe_data sh, +
3099 unit un +
3100 WHERE sh.slunit = un.un_name;
3101 (1 row)
3103 --
3104 -- check multi-row VALUES in rules
3105 --
3106 create table rules_src(f1 int, f2 int);
3107 create table rules_log(f1 int, f2 int, tag text);
3108 insert into rules_src values(1,2), (11,12);
3109 create rule r1 as on update to rules_src do also
3110 insert into rules_log values(old.*, 'old'), (new.*, 'new');
3111 update rules_src set f2 = f2 + 1;
3112 update rules_src set f2 = f2 * 10;
3113 select * from rules_src;
3114 f1 | f2
3115 ----+-----
3116 1 | 30
3117 11 | 130
3118 (2 rows)
3120 select * from rules_log;
3121 f1 | f2 | tag
3122 ----+-----+-----
3123 1 | 2 | old
3124 1 | 3 | new
3125 11 | 12 | old
3126 11 | 13 | new
3127 1 | 3 | old
3128 1 | 30 | new
3129 11 | 13 | old
3130 11 | 130 | new
3131 (8 rows)
3133 create rule r2 as on update to rules_src do also
3134 values(old.*, 'old'), (new.*, 'new');
3135 update rules_src set f2 = f2 / 10;
3136 column1 | column2 | column3
3137 ---------+---------+---------
3138 1 | 30 | old
3139 1 | 3 | new
3140 11 | 130 | old
3141 11 | 13 | new
3142 (4 rows)
3144 select * from rules_src;
3145 f1 | f2
3146 ----+----
3147 1 | 3
3148 11 | 13
3149 (2 rows)
3151 select * from rules_log;
3152 f1 | f2 | tag
3153 ----+-----+-----
3154 1 | 2 | old
3155 1 | 3 | new
3156 11 | 12 | old
3157 11 | 13 | new
3158 1 | 3 | old
3159 1 | 30 | new
3160 11 | 13 | old
3161 11 | 130 | new
3162 1 | 30 | old
3163 1 | 3 | new
3164 11 | 130 | old
3165 11 | 13 | new
3166 (12 rows)
3168 create rule r3 as on delete to rules_src do notify rules_src_deletion;
3169 \d+ rules_src
3170 Table "public.rules_src"
3171 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
3172 --------+---------+-----------+----------+---------+---------+--------------+-------------
3173 f1 | integer | | | | plain | |
3174 f2 | integer | | | | plain | |
3175 Rules:
3176 r1 AS
3177 ON UPDATE TO rules_src DO INSERT INTO rules_log (f1, f2, tag) VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)
3178 r2 AS
3179 ON UPDATE TO rules_src DO VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)
3180 r3 AS
3181 ON DELETE TO rules_src DO
3182 NOTIFY rules_src_deletion
3184 --
3185 -- Ensure an aliased target relation for insert is correctly deparsed.
3186 --
3187 create rule r4 as on insert to rules_src do instead insert into rules_log AS trgt SELECT NEW.* RETURNING trgt.f1, trgt.f2;
3188 create rule r5 as on update to rules_src do instead UPDATE rules_log AS trgt SET tag = 'updated' WHERE trgt.f1 = new.f1;
3189 \d+ rules_src
3190 Table "public.rules_src"
3191 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
3192 --------+---------+-----------+----------+---------+---------+--------------+-------------
3193 f1 | integer | | | | plain | |
3194 f2 | integer | | | | plain | |
3195 Rules:
3196 r1 AS
3197 ON UPDATE TO rules_src DO INSERT INTO rules_log (f1, f2, tag) VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)
3198 r2 AS
3199 ON UPDATE TO rules_src DO VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)
3200 r3 AS
3201 ON DELETE TO rules_src DO
3202 NOTIFY rules_src_deletion
3203 r4 AS
3204 ON INSERT TO rules_src DO INSTEAD INSERT INTO rules_log AS trgt (f1, f2) SELECT new.f1,
3205 new.f2
3206 RETURNING trgt.f1,
3207 trgt.f2
3208 r5 AS
3209 ON UPDATE TO rules_src DO INSTEAD UPDATE rules_log trgt SET tag = 'updated'::text
3210 WHERE trgt.f1 = new.f1
3212 --
3213 -- Also check multiassignment deparsing.
3214 --
3215 create table rule_t1(f1 int, f2 int);
3216 create table rule_dest(f1 int, f2 int[], tag text);
3217 create rule rr as on update to rule_t1 do instead UPDATE rule_dest trgt
3218 SET (f2[1], f1, tag) = (SELECT new.f2, new.f1, 'updated'::varchar)
3219 WHERE trgt.f1 = new.f1 RETURNING new.*;
3220 \d+ rule_t1
3221 Table "public.rule_t1"
3222 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
3223 --------+---------+-----------+----------+---------+---------+--------------+-------------
3224 f1 | integer | | | | plain | |
3225 f2 | integer | | | | plain | |
3226 Rules:
3227 rr AS
3228 ON UPDATE TO rule_t1 DO INSTEAD UPDATE rule_dest trgt SET (f2[1], f1, tag) = ( SELECT new.f2,
3229 new.f1,
3230 'updated'::character varying AS "varchar")
3231 WHERE trgt.f1 = new.f1
3232 RETURNING new.f1,
3233 new.f2
3235 drop table rule_t1, rule_dest;
3236 --
3237 -- check alter rename rule
3238 --
3239 CREATE TABLE rule_t1 (a INT);
3240 CREATE VIEW rule_v1 AS SELECT * FROM rule_t1;
3241 CREATE RULE InsertRule AS
3242 ON INSERT TO rule_v1
3243 DO INSTEAD
3244 INSERT INTO rule_t1 VALUES(new.a);
3245 ALTER RULE InsertRule ON rule_v1 RENAME to NewInsertRule;
3246 INSERT INTO rule_v1 VALUES(1);
3247 SELECT * FROM rule_v1;
3248 a
3249 ---
3250 1
3251 (1 row)
3253 \d+ rule_v1
3254 View "public.rule_v1"
3255 Column | Type | Collation | Nullable | Default | Storage | Description
3256 --------+---------+-----------+----------+---------+---------+-------------
3257 a | integer | | | | plain |
3258 View definition:
3259 SELECT rule_t1.a
3260 FROM rule_t1;
3261 Rules:
3262 newinsertrule AS
3263 ON INSERT TO rule_v1 DO INSTEAD INSERT INTO rule_t1 (a)
3264 VALUES (new.a)
3266 --
3267 -- error conditions for alter rename rule
3268 --
3269 ALTER RULE InsertRule ON rule_v1 RENAME TO NewInsertRule; -- doesn't exist
3270 ERROR: rule "insertrule" for relation "rule_v1" does not exist
3271 ALTER RULE NewInsertRule ON rule_v1 RENAME TO "_RETURN"; -- already exists
3272 ERROR: rule "_RETURN" for relation "rule_v1" already exists
3273 ALTER RULE "_RETURN" ON rule_v1 RENAME TO abc; -- ON SELECT rule cannot be renamed
3274 ERROR: renaming an ON SELECT rule is not allowed
3275 DROP VIEW rule_v1;
3276 DROP TABLE rule_t1;
3277 --
3278 -- check display of VALUES in view definitions
3279 --
3280 create view rule_v1 as values(1,2);
3281 \d+ rule_v1
3282 View "public.rule_v1"
3283 Column | Type | Collation | Nullable | Default | Storage | Description
3284 ---------+---------+-----------+----------+---------+---------+-------------
3285 column1 | integer | | | | plain |
3286 column2 | integer | | | | plain |
3287 View definition:
3288 VALUES (1,2);
3290 alter table rule_v1 rename column column2 to q2;
3291 \d+ rule_v1
3292 View "public.rule_v1"
3293 Column | Type | Collation | Nullable | Default | Storage | Description
3294 ---------+---------+-----------+----------+---------+---------+-------------
3295 column1 | integer | | | | plain |
3296 q2 | integer | | | | plain |
3297 View definition:
3298 SELECT "*VALUES*".column1,
3299 "*VALUES*".column2 AS q2
3300 FROM (VALUES (1,2)) "*VALUES*";
3302 drop view rule_v1;
3303 create view rule_v1(x) as values(1,2);
3304 \d+ rule_v1
3305 View "public.rule_v1"
3306 Column | Type | Collation | Nullable | Default | Storage | Description
3307 ---------+---------+-----------+----------+---------+---------+-------------
3308 x | integer | | | | plain |
3309 column2 | integer | | | | plain |
3310 View definition:
3311 SELECT "*VALUES*".column1 AS x,
3312 "*VALUES*".column2
3313 FROM (VALUES (1,2)) "*VALUES*";
3315 drop view rule_v1;
3316 create view rule_v1(x) as select * from (values(1,2)) v;
3317 \d+ rule_v1
3318 View "public.rule_v1"
3319 Column | Type | Collation | Nullable | Default | Storage | Description
3320 ---------+---------+-----------+----------+---------+---------+-------------
3321 x | integer | | | | plain |
3322 column2 | integer | | | | plain |
3323 View definition:
3324 SELECT v.column1 AS x,
3325 v.column2
3326 FROM ( VALUES (1,2)) v;
3328 drop view rule_v1;
3329 create view rule_v1(x) as select * from (values(1,2)) v(q,w);
3330 \d+ rule_v1
3331 View "public.rule_v1"
3332 Column | Type | Collation | Nullable | Default | Storage | Description
3333 --------+---------+-----------+----------+---------+---------+-------------
3334 x | integer | | | | plain |
3335 w | integer | | | | plain |
3336 View definition:
3337 SELECT v.q AS x,
3338 v.w
3339 FROM ( VALUES (1,2)) v(q, w);
3341 drop view rule_v1;
3342 --
3343 -- Check DO INSTEAD rules with ON CONFLICT
3344 --
3345 CREATE TABLE hats (
3346 hat_name char(10) primary key,
3347 hat_color char(10) -- hat color
3348 );
3349 CREATE TABLE hat_data (
3350 hat_name char(10),
3351 hat_color char(10) -- hat color
3352 );
3353 create unique index hat_data_unique_idx
3354 on hat_data (hat_name COLLATE "C" bpchar_pattern_ops);
3355 -- DO NOTHING with ON CONFLICT
3356 CREATE RULE hat_nosert AS ON INSERT TO hats
3357 DO INSTEAD
3358 INSERT INTO hat_data VALUES (
3359 NEW.hat_name,
3360 NEW.hat_color)
3361 ON CONFLICT (hat_name COLLATE "C" bpchar_pattern_ops) WHERE hat_color = 'green'
3362 DO NOTHING
3363 RETURNING *;
3364 SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename;
3365 definition
3366 ---------------------------------------------------------------------------------------------
3367 CREATE RULE hat_nosert AS +
3368 ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) +
3369 VALUES (new.hat_name, new.hat_color) ON CONFLICT(hat_name COLLATE "C" bpchar_pattern_ops)+
3370 WHERE (hat_color = 'green'::bpchar) DO NOTHING +
3371 RETURNING hat_data.hat_name, +
3372 hat_data.hat_color;
3373 (1 row)
3375 -- Works (projects row)
3376 INSERT INTO hats VALUES ('h7', 'black') RETURNING *;
3377 hat_name | hat_color
3378 ------------+------------
3379 h7 | black
3380 (1 row)
3382 -- Works (does nothing)
3383 INSERT INTO hats VALUES ('h7', 'black') RETURNING *;
3384 hat_name | hat_color
3385 ----------+-----------
3386 (0 rows)
3388 SELECT tablename, rulename, definition FROM pg_rules
3389 WHERE tablename = 'hats';
3390 tablename | rulename | definition
3391 -----------+------------+---------------------------------------------------------------------------------------------
3392 hats | hat_nosert | CREATE RULE hat_nosert AS +
3393 | | ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) +
3394 | | VALUES (new.hat_name, new.hat_color) ON CONFLICT(hat_name COLLATE "C" bpchar_pattern_ops)+
3395 | | WHERE (hat_color = 'green'::bpchar) DO NOTHING +
3396 | | RETURNING hat_data.hat_name, +
3397 | | hat_data.hat_color;
3398 (1 row)
3400 DROP RULE hat_nosert ON hats;
3401 -- DO NOTHING without ON CONFLICT
3402 CREATE RULE hat_nosert_all AS ON INSERT TO hats
3403 DO INSTEAD
3404 INSERT INTO hat_data VALUES (
3405 NEW.hat_name,
3406 NEW.hat_color)
3407 ON CONFLICT
3408 DO NOTHING
3409 RETURNING *;
3410 SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename;
3411 definition
3412 -------------------------------------------------------------------------------------
3413 CREATE RULE hat_nosert_all AS +
3414 ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color)+
3415 VALUES (new.hat_name, new.hat_color) ON CONFLICT DO NOTHING +
3416 RETURNING hat_data.hat_name, +
3417 hat_data.hat_color;
3418 (1 row)
3420 DROP RULE hat_nosert_all ON hats;
3421 -- Works (does nothing)
3422 INSERT INTO hats VALUES ('h7', 'black') RETURNING *;
3423 hat_name | hat_color
3424 ------------+------------
3425 h7 | black
3426 (1 row)
3428 -- DO UPDATE with a WHERE clause
3429 CREATE RULE hat_upsert AS ON INSERT TO hats
3430 DO INSTEAD
3431 INSERT INTO hat_data VALUES (
3432 NEW.hat_name,
3433 NEW.hat_color)
3434 ON CONFLICT (hat_name)
3435 DO UPDATE
3436 SET hat_name = hat_data.hat_name, hat_color = excluded.hat_color
3437 WHERE excluded.hat_color <> 'forbidden' AND hat_data.* != excluded.*
3438 RETURNING *;
3439 SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename;
3440 definition
3441 -----------------------------------------------------------------------------------------------------------------------------------------
3442 CREATE RULE hat_upsert AS +
3443 ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) +
3444 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+
3445 WHERE ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*)) +
3446 RETURNING hat_data.hat_name, +
3447 hat_data.hat_color;
3448 (1 row)
3450 -- Works (does upsert)
3451 INSERT INTO hats VALUES ('h8', 'black') RETURNING *;
3452 hat_name | hat_color
3453 ------------+------------
3454 h8 | black
3455 (1 row)
3457 SELECT * FROM hat_data WHERE hat_name = 'h8';
3458 hat_name | hat_color
3459 ------------+------------
3460 h8 | black
3461 (1 row)
3463 INSERT INTO hats VALUES ('h8', 'white') RETURNING *;
3464 hat_name | hat_color
3465 ------------+------------
3466 h8 | white
3467 (1 row)
3469 SELECT * FROM hat_data WHERE hat_name = 'h8';
3470 hat_name | hat_color
3471 ------------+------------
3472 h8 | white
3473 (1 row)
3475 INSERT INTO hats VALUES ('h8', 'forbidden') RETURNING *;
3476 hat_name | hat_color
3477 ----------+-----------
3478 (0 rows)
3480 SELECT * FROM hat_data WHERE hat_name = 'h8';
3481 hat_name | hat_color
3482 ------------+------------
3483 h8 | white
3484 (1 row)
3486 SELECT tablename, rulename, definition FROM pg_rules
3487 WHERE tablename = 'hats';
3488 tablename | rulename | definition
3489 -----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------
3490 hats | hat_upsert | CREATE RULE hat_upsert AS +
3491 | | ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) +
3492 | | 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+
3493 | | WHERE ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*)) +
3494 | | RETURNING hat_data.hat_name, +
3495 | | hat_data.hat_color;
3496 (1 row)
3498 -- ensure explain works for on insert conflict rules
3499 explain (costs off) INSERT INTO hats VALUES ('h8', 'forbidden') RETURNING *;
3500 QUERY PLAN
3501 -------------------------------------------------------------------------------------------------
3502 Insert on hat_data
3503 Conflict Resolution: UPDATE
3504 Conflict Arbiter Indexes: hat_data_unique_idx
3505 Conflict Filter: ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*))
3506 -> Result
3507 (5 rows)
3509 -- ensure upserting into a rule, with a CTE (different offsets!) works
3510 WITH data(hat_name, hat_color) AS MATERIALIZED (
3511 VALUES ('h8', 'green'),
3512 ('h9', 'blue'),
3513 ('h7', 'forbidden')
3514 )
3515 INSERT INTO hats
3516 SELECT * FROM data
3517 RETURNING *;
3518 hat_name | hat_color
3519 ------------+------------
3520 h8 | green
3521 h9 | blue
3522 (2 rows)
3524 EXPLAIN (costs off)
3525 WITH data(hat_name, hat_color) AS MATERIALIZED (
3526 VALUES ('h8', 'green'),
3527 ('h9', 'blue'),
3528 ('h7', 'forbidden')
3529 )
3530 INSERT INTO hats
3531 SELECT * FROM data
3532 RETURNING *;
3533 QUERY PLAN
3534 -------------------------------------------------------------------------------------------------
3535 Insert on hat_data
3536 Conflict Resolution: UPDATE
3537 Conflict Arbiter Indexes: hat_data_unique_idx
3538 Conflict Filter: ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*))
3539 CTE data
3540 -> Values Scan on "*VALUES*"
3541 -> CTE Scan on data
3542 (7 rows)
3544 SELECT * FROM hat_data WHERE hat_name IN ('h8', 'h9', 'h7') ORDER BY hat_name;
3545 hat_name | hat_color
3546 ------------+------------
3547 h7 | black
3548 h8 | green
3549 h9 | blue
3550 (3 rows)
3552 DROP RULE hat_upsert ON hats;
3553 drop table hats;
3554 drop table hat_data;
3555 -- test for pg_get_functiondef properly regurgitating SET parameters
3556 -- Note that the function is kept around to stress pg_dump.
3557 CREATE FUNCTION func_with_set_params() RETURNS integer
3558 AS 'select 1;'
3559 LANGUAGE SQL
3560 SET search_path TO PG_CATALOG
3561 SET extra_float_digits TO 2
3562 SET work_mem TO '4MB'
3563 SET datestyle to iso, mdy
3564 SET local_preload_libraries TO "Mixed/Case", 'c:/''a"/path', '', '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789'
3565 IMMUTABLE STRICT;
3566 SELECT pg_get_functiondef('func_with_set_params()'::regprocedure);
3567 pg_get_functiondef
3568 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3569 CREATE OR REPLACE FUNCTION public.func_with_set_params() +
3570 RETURNS integer +
3571 LANGUAGE sql +
3572 IMMUTABLE STRICT +
3573 SET search_path TO 'pg_catalog' +
3574 SET extra_float_digits TO '2' +
3575 SET work_mem TO '4MB' +
3576 SET "DateStyle" TO 'iso, mdy' +
3577 SET local_preload_libraries TO 'Mixed/Case', 'c:/''a"/path', '', '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789'+
3578 AS $function$select 1;$function$ +
3580 (1 row)
3582 -- tests for pg_get_*def with invalid objects
3583 SELECT pg_get_constraintdef(0);
3584 pg_get_constraintdef
3585 ----------------------
3587 (1 row)
3589 SELECT pg_get_functiondef(0);
3590 pg_get_functiondef
3591 --------------------
3593 (1 row)
3595 SELECT pg_get_indexdef(0);
3596 pg_get_indexdef
3597 -----------------
3599 (1 row)
3601 SELECT pg_get_ruledef(0);
3602 pg_get_ruledef
3603 ----------------
3605 (1 row)
3607 SELECT pg_get_statisticsobjdef(0);
3608 pg_get_statisticsobjdef
3609 -------------------------
3611 (1 row)
3613 SELECT pg_get_triggerdef(0);
3614 pg_get_triggerdef
3615 -------------------
3617 (1 row)
3619 SELECT pg_get_viewdef(0);
3620 pg_get_viewdef
3621 ----------------
3623 (1 row)
3625 SELECT pg_get_function_arguments(0);
3626 pg_get_function_arguments
3627 ---------------------------
3629 (1 row)
3631 SELECT pg_get_function_identity_arguments(0);
3632 pg_get_function_identity_arguments
3633 ------------------------------------
3635 (1 row)
3637 SELECT pg_get_function_result(0);
3638 pg_get_function_result
3639 ------------------------
3641 (1 row)
3643 SELECT pg_get_function_arg_default(0, 0);
3644 pg_get_function_arg_default
3645 -----------------------------
3647 (1 row)
3649 SELECT pg_get_function_arg_default('pg_class'::regclass, 0);
3650 pg_get_function_arg_default
3651 -----------------------------
3653 (1 row)
3655 SELECT pg_get_partkeydef(0);
3656 pg_get_partkeydef
3657 -------------------
3659 (1 row)
3661 -- test rename for a rule defined on a partitioned table
3662 CREATE TABLE rules_parted_table (a int) PARTITION BY LIST (a);
3663 CREATE TABLE rules_parted_table_1 PARTITION OF rules_parted_table FOR VALUES IN (1);
3664 CREATE RULE rules_parted_table_insert AS ON INSERT to rules_parted_table
3665 DO INSTEAD INSERT INTO rules_parted_table_1 VALUES (NEW.*);
3666 ALTER RULE rules_parted_table_insert ON rules_parted_table RENAME TO rules_parted_table_insert_redirect;
3667 DROP TABLE rules_parted_table;
3668 --
3669 -- Test enabling/disabling
3670 --
3671 CREATE TABLE ruletest1 (a int);
3672 CREATE TABLE ruletest2 (b int);
3673 CREATE RULE rule1 AS ON INSERT TO ruletest1
3674 DO INSTEAD INSERT INTO ruletest2 VALUES (NEW.*);
3675 INSERT INTO ruletest1 VALUES (1);
3676 ALTER TABLE ruletest1 DISABLE RULE rule1;
3677 INSERT INTO ruletest1 VALUES (2);
3678 ALTER TABLE ruletest1 ENABLE RULE rule1;
3679 SET session_replication_role = replica;
3680 INSERT INTO ruletest1 VALUES (3);
3681 ALTER TABLE ruletest1 ENABLE REPLICA RULE rule1;
3682 INSERT INTO ruletest1 VALUES (4);
3683 RESET session_replication_role;
3684 INSERT INTO ruletest1 VALUES (5);
3685 SELECT * FROM ruletest1;
3686 a
3687 ---
3688 2
3689 3
3690 5
3691 (3 rows)
3693 SELECT * FROM ruletest2;
3694 b
3695 ---
3696 1
3697 4
3698 (2 rows)
3700 DROP TABLE ruletest1;
3701 DROP TABLE ruletest2;