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-- 9create table rtest_t1 (a int4, b int4); 10create table rtest_t2 (a int4, b int4); 11create table rtest_t3 (a int4, b int4); 12create view rtest_v1 as select * from rtest_t1; 13create rule rtest_v1_ins as on insert to rtest_v1 do instead 14 insert into rtest_t1 values (new.a, new.b); 15create 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; 18create rule rtest_v1_del as on delete to rtest_v1 do instead 19 delete from rtest_t1 where a = old.a; 20-- Test comments 21COMMENT ON RULE rtest_v1_bad ON rtest_v1 IS 'bad rule'; 22ERROR: rule "rtest_v1_bad" for relation "rtest_v1" does not exist 23COMMENT ON RULE rtest_v1_del ON rtest_v1 IS 'delete rule'; 24COMMENT 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-- 33create table rtest_system (sysname text, sysdesc text); 34create table rtest_interface (sysname text, ifname text); 35create table rtest_person (pname text, pdesc text); 36create table rtest_admin (pname text, sysname text); 37create 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 ); 43create 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 ); 47create rule rtest_pers_upd as on update to rtest_person do also 48 update rtest_admin set pname = new.pname where pname = old.pname; 49create 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-- 54create table rtest_emp (ename char(20), salary money); 55create table rtest_emplog (ename char(20), who name, action char(10), newsal money, oldsal money); 56create table rtest_empmass (ename char(20), salary money); 57create 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'); 60create 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); 63create 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-- 70create table rtest_t4 (a int4, b text); 71create table rtest_t5 (a int4, b text); 72create table rtest_t6 (a int4, b text); 73create table rtest_t7 (a int4, b text); 74create table rtest_t8 (a int4, b text); 75create table rtest_t9 (a int4, b text); 76create 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); 79create 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); 82create 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); 85create 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-- 94create table rtest_order1 (a int4); 95create table rtest_order2 (a int4, b int4, c text); 96create sequence rtest_seq; 97create 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'); 100create 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'); 104create 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'); 107create 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-- 113create table rtest_nothn1 (a int4, b text); 114create table rtest_nothn2 (a int4, b text); 115create table rtest_nothn3 (a int4, b text); 116create table rtest_nothn4 (a int4, b text); 117create rule rtest_nothn_r1 as on insert to rtest_nothn1 118 where new.a >= 10 and new.a < 20 do instead nothing; 119create rule rtest_nothn_r2 as on insert to rtest_nothn1 120 where new.a >= 30 and new.a < 40 do instead nothing; 121create 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); 124create 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-- 134insert into rtest_t2 values (1, 21); 135insert into rtest_t2 values (2, 22); 136insert into rtest_t2 values (3, 23); 137insert into rtest_t3 values (1, 31); 138insert into rtest_t3 values (2, 32); 139insert into rtest_t3 values (3, 33); 140insert into rtest_t3 values (4, 34); 141insert into rtest_t3 values (5, 35); 142-- insert values 143insert into rtest_v1 values (1, 11); 144insert into rtest_v1 values (2, 12); 145select * from rtest_v1; 146 a | b 147---+---- 148 1 | 11 149 2 | 12 150(2 rows) 151 152-- delete with constant expression 153delete from rtest_v1 where a = 1; 154select * from rtest_v1; 155 a | b 156---+---- 157 2 | 12 158(1 row) 159 160insert into rtest_v1 values (1, 11); 161delete from rtest_v1 where b = 12; 162select * from rtest_v1; 163 a | b 164---+---- 165 1 | 11 166(1 row) 167 168insert into rtest_v1 values (2, 12); 169insert into rtest_v1 values (2, 13); 170select * from rtest_v1; 171 a | b 172---+---- 173 1 | 11 174 2 | 12 175 2 | 13 176(3 rows) 177 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 186delete from rtest_v1 where b = 12; 187select * from rtest_v1; 188 a | b 189---+---- 190 1 | 11 191(1 row) 192 193delete from rtest_v1; 194-- insert select 195insert into rtest_v1 select * from rtest_t2; 196select * from rtest_v1; 197 a | b 198---+---- 199 1 | 21 200 2 | 22 201 3 | 23 202(3 rows) 203 204delete from rtest_v1; 205-- same with swapped targetlist 206insert into rtest_v1 (b, a) select b, a from rtest_t2; 207select * from rtest_v1; 208 a | b 209---+---- 210 1 | 21 211 2 | 22 212 3 | 23 213(3 rows) 214 215-- now with only one target attribute 216insert into rtest_v1 (a) select a from rtest_t3; 217select * 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) 229 230select * from rtest_v1 where b isnull; 231 a | b 232---+--- 233 1 | 234 2 | 235 3 | 236 4 | 237 5 | 238(5 rows) 239 240-- let attribute a differ (must be done on rtest_t1 - see above) 241update rtest_t1 set a = a + 10 where b isnull; 242delete from rtest_v1 where b isnull; 243select * from rtest_v1; 244 a | b 245---+---- 246 1 | 21 247 2 | 22 248 3 | 23 249(3 rows) 250 251-- now updates with constant expression 252update rtest_v1 set b = 42 where a = 2; 253select * from rtest_v1; 254 a | b 255---+---- 256 1 | 21 257 3 | 23 258 2 | 42 259(3 rows) 260 261update rtest_v1 set b = 99 where b = 42; 262select * from rtest_v1; 263 a | b 264---+---- 265 1 | 21 266 3 | 23 267 2 | 99 268(3 rows) 269 270update rtest_v1 set b = 88 where b < 50; 271select * from rtest_v1; 272 a | b 273---+---- 274 2 | 99 275 1 | 88 276 3 | 88 277(3 rows) 278 279delete from rtest_v1; 280insert into rtest_v1 select rtest_t2.a, rtest_t3.b 281 from rtest_t2, rtest_t3 282 where rtest_t2.a = rtest_t3.a; 283select * from rtest_v1; 284 a | b 285---+---- 286 1 | 31 287 2 | 32 288 3 | 33 289(3 rows) 290 291-- updates in a mergejoin 292update rtest_v1 set b = rtest_t2.b from rtest_t2 where rtest_v1.a = rtest_t2.a; 293select * from rtest_v1; 294 a | b 295---+---- 296 1 | 21 297 2 | 22 298 3 | 23 299(3 rows) 300 301insert into rtest_v1 select * from rtest_t3; 302select * 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) 314 315update rtest_t1 set a = a + 10 where b > 30; 316select * 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) 328 329update rtest_v1 set a = rtest_t3.a + 20 from rtest_t3 where rtest_v1.b = rtest_t3.b; 330select * 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) 342 343-- 344-- Test for constraint updates/deletes 345-- 346insert into rtest_system values ('orion', 'Linux Jan Wieck'); 347insert into rtest_system values ('notjw', 'WinNT Jan Wieck (notebook)'); 348insert into rtest_system values ('neptun', 'Fileserver'); 349insert into rtest_interface values ('orion', 'eth0'); 350insert into rtest_interface values ('orion', 'eth1'); 351insert into rtest_interface values ('notjw', 'eth0'); 352insert into rtest_interface values ('neptun', 'eth0'); 353insert into rtest_person values ('jw', 'Jan Wieck'); 354insert into rtest_person values ('bm', 'Bruce Momjian'); 355insert into rtest_admin values ('jw', 'orion'); 356insert into rtest_admin values ('jw', 'notjw'); 357insert into rtest_admin values ('bm', 'neptun'); 358update rtest_system set sysname = 'pluto' where sysname = 'neptun'; 359select * from rtest_interface; 360 sysname | ifname 361---------+-------- 362 orion | eth0 363 orion | eth1 364 notjw | eth0 365 pluto | eth0 366(4 rows) 367 368select * from rtest_admin; 369 pname | sysname 370-------+--------- 371 jw | orion 372 jw | notjw 373 bm | pluto 374(3 rows) 375 376update 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(). 380select * from rtest_admin order by pname, sysname; 381 pname | sysname 382--------+--------- 383 bm | pluto 384 jwieck | notjw 385 jwieck | orion 386(3 rows) 387 388delete from rtest_system where sysname = 'orion'; 389select * from rtest_interface; 390 sysname | ifname 391---------+-------- 392 notjw | eth0 393 pluto | eth0 394(2 rows) 395 396select * from rtest_admin; 397 pname | sysname 398--------+--------- 399 bm | pluto 400 jwieck | notjw 401(2 rows) 402 403-- 404-- Rule qualification test 405-- 406insert into rtest_emp values ('wiecc', '5000.00'); 407insert into rtest_emp values ('gates', '80000.00'); 408update rtest_emp set ename = 'wiecx' where ename = 'wiecc'; 409update rtest_emp set ename = 'wieck', salary = '6000.00' where ename = 'wiecx'; 410update rtest_emp set salary = '7000.00' where ename = 'wieck'; 411delete from rtest_emp where ename = 'gates'; 412select 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) 421 422insert into rtest_empmass values ('meyer', '4000.00'); 423insert into rtest_empmass values ('maier', '5000.00'); 424insert into rtest_empmass values ('mayr', '6000.00'); 425insert into rtest_emp select * from rtest_empmass; 426select 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) 438 439update rtest_empmass set salary = salary + '1000.00'; 440update rtest_emp set salary = rtest_empmass.salary from rtest_empmass where rtest_emp.ename = rtest_empmass.ename; 441select 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) 456 457delete from rtest_emp using rtest_empmass where rtest_emp.ename = rtest_empmass.ename; 458select 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) 476 477-- 478-- Multiple cascaded qualified instead rule test 479-- 480insert into rtest_t4 values (1, 'Record should go to rtest_t4'); 481insert into rtest_t4 values (2, 'Record should go to rtest_t4'); 482insert into rtest_t4 values (10, 'Record should go to rtest_t5'); 483insert into rtest_t4 values (15, 'Record should go to rtest_t5'); 484insert into rtest_t4 values (19, 'Record should go to rtest_t5 and t7'); 485insert into rtest_t4 values (20, 'Record should go to rtest_t4 and t6'); 486insert into rtest_t4 values (26, 'Record should go to rtest_t4 and t8'); 487insert into rtest_t4 values (28, 'Record should go to rtest_t4 and t8'); 488insert into rtest_t4 values (30, 'Record should go to rtest_t4'); 489insert into rtest_t4 values (40, 'Record should go to rtest_t4'); 490select * 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) 501 502select * 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) 509 510select * from rtest_t6; 511 a | b 512----+------------------------------------- 513 20 | Record should go to rtest_t4 and t6 514(1 row) 515 516select * from rtest_t7; 517 a | b 518----+------------------------------------- 519 19 | Record should go to rtest_t5 and t7 520(1 row) 521 522select * 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) 528 529delete from rtest_t4; 530delete from rtest_t5; 531delete from rtest_t6; 532delete from rtest_t7; 533delete from rtest_t8; 534insert into rtest_t9 values (1, 'Record should go to rtest_t4'); 535insert into rtest_t9 values (2, 'Record should go to rtest_t4'); 536insert into rtest_t9 values (10, 'Record should go to rtest_t5'); 537insert into rtest_t9 values (15, 'Record should go to rtest_t5'); 538insert into rtest_t9 values (19, 'Record should go to rtest_t5 and t7'); 539insert into rtest_t9 values (20, 'Record should go to rtest_t4 and t6'); 540insert into rtest_t9 values (26, 'Record should go to rtest_t4 and t8'); 541insert into rtest_t9 values (28, 'Record should go to rtest_t4 and t8'); 542insert into rtest_t9 values (30, 'Record should go to rtest_t4'); 543insert into rtest_t9 values (40, 'Record should go to rtest_t4'); 544insert into rtest_t4 select * from rtest_t9 where a < 20; 545select * 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) 551 552select * 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) 559 560select * from rtest_t6; 561 a | b 562---+--- 563(0 rows) 564 565select * from rtest_t7; 566 a | b 567----+------------------------------------- 568 19 | Record should go to rtest_t5 and t7 569(1 row) 570 571select * from rtest_t8; 572 a | b 573---+--- 574(0 rows) 575 576insert into rtest_t4 select * from rtest_t9 where b ~ 'and t8'; 577select * 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) 585 586select * 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) 593 594select * from rtest_t6; 595 a | b 596---+--- 597(0 rows) 598 599select * from rtest_t7; 600 a | b 601----+------------------------------------- 602 19 | Record should go to rtest_t5 and t7 603(1 row) 604 605select * 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) 611 612insert into rtest_t4 select a + 1, b from rtest_t9 where a in (20, 30, 40); 613select * 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) 624 625select * 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) 632 633select * from rtest_t6; 634 a | b 635----+------------------------------------- 636 21 | Record should go to rtest_t4 and t6 637(1 row) 638 639select * from rtest_t7; 640 a | b 641----+------------------------------------- 642 19 | Record should go to rtest_t5 and t7 643(1 row) 644 645select * 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) 651 652-- 653-- Check that the ordering of rules fired is correct 654-- 655insert into rtest_order1 values (1); 656select * 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) 664 665-- 666-- Check if instead nothing w/without qualification works 667-- 668insert into rtest_nothn1 values (1, 'want this'); 669insert into rtest_nothn1 values (2, 'want this'); 670insert into rtest_nothn1 values (10, 'don''t want this'); 671insert into rtest_nothn1 values (19, 'don''t want this'); 672insert into rtest_nothn1 values (20, 'want this'); 673insert into rtest_nothn1 values (29, 'want this'); 674insert into rtest_nothn1 values (30, 'don''t want this'); 675insert into rtest_nothn1 values (39, 'don''t want this'); 676insert into rtest_nothn1 values (40, 'want this'); 677insert into rtest_nothn1 values (50, 'want this'); 678insert into rtest_nothn1 values (60, 'want this'); 679select * 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) 690 691insert into rtest_nothn2 values (10, 'too small'); 692insert into rtest_nothn2 values (50, 'too small'); 693insert into rtest_nothn2 values (100, 'OK'); 694insert into rtest_nothn2 values (200, 'OK'); 695select * from rtest_nothn2; 696 a | b 697---+--- 698(0 rows) 699 700select * from rtest_nothn3; 701 a | b 702-----+---- 703 100 | OK 704 200 | OK 705(2 rows) 706 707delete from rtest_nothn1; 708delete from rtest_nothn2; 709delete from rtest_nothn3; 710insert into rtest_nothn4 values (1, 'want this'); 711insert into rtest_nothn4 values (2, 'want this'); 712insert into rtest_nothn4 values (10, 'don''t want this'); 713insert into rtest_nothn4 values (19, 'don''t want this'); 714insert into rtest_nothn4 values (20, 'want this'); 715insert into rtest_nothn4 values (29, 'want this'); 716insert into rtest_nothn4 values (30, 'don''t want this'); 717insert into rtest_nothn4 values (39, 'don''t want this'); 718insert into rtest_nothn4 values (40, 'want this'); 719insert into rtest_nothn4 values (50, 'want this'); 720insert into rtest_nothn4 values (60, 'want this'); 721insert into rtest_nothn1 select * from rtest_nothn4; 722select * 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) 733 734delete from rtest_nothn4; 735insert into rtest_nothn4 values (10, 'too small'); 736insert into rtest_nothn4 values (50, 'too small'); 737insert into rtest_nothn4 values (100, 'OK'); 738insert into rtest_nothn4 values (200, 'OK'); 739insert into rtest_nothn2 select * from rtest_nothn4; 740select * from rtest_nothn2; 741 a | b 742---+--- 743(0 rows) 744 745select * from rtest_nothn3; 746 a | b 747-----+---- 748 100 | OK 749 200 | OK 750(2 rows) 751 752create table rtest_view1 (a int4, b text, v bool); 753create table rtest_view2 (a int4); 754create table rtest_view3 (a int4, b text); 755create table rtest_view4 (a int4, b text, c int4); 756create 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); 758create view rtest_vview2 as select a, b from rtest_view1 where v; 759create 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); 761create 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; 765create function rtest_viewfunc1(int4) returns int4 as 766 'select count(*)::int4 from rtest_view2 where a = $1' 767 language sql; 768create view rtest_vview5 as select a, b, rtest_viewfunc1(a) as refcount 769 from rtest_view1; 770insert into rtest_view1 values (1, 'item 1', 't'); 771insert into rtest_view1 values (2, 'item 2', 't'); 772insert into rtest_view1 values (3, 'item 3', 't'); 773insert into rtest_view1 values (4, 'item 4', 'f'); 774insert into rtest_view1 values (5, 'item 5', 't'); 775insert into rtest_view1 values (6, 'item 6', 'f'); 776insert into rtest_view1 values (7, 'item 7', 't'); 777insert into rtest_view1 values (8, 'item 8', 't'); 778insert into rtest_view2 values (2); 779insert into rtest_view2 values (2); 780insert into rtest_view2 values (4); 781insert into rtest_view2 values (5); 782insert into rtest_view2 values (7); 783insert into rtest_view2 values (7); 784insert into rtest_view2 values (7); 785insert into rtest_view2 values (7); 786select * 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) 794 795select * 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) 805 806select * from rtest_vview3; 807 a | b 808---+-------- 809 2 | item 2 810 5 | item 5 811 7 | item 7 812(3 rows) 813 814select * 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) 822 823select * 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) 835 836insert into rtest_view3 select * from rtest_vview1 where a < 7; 837select * from rtest_view3; 838 a | b 839---+-------- 840 2 | item 2 841 4 | item 4 842 5 | item 5 843(3 rows) 844 845delete from rtest_view3; 846insert into rtest_view3 select * from rtest_vview2 where a != 5 and b !~ '2'; 847select * 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) 855 856delete from rtest_view3; 857insert into rtest_view3 select * from rtest_vview3; 858select * from rtest_view3; 859 a | b 860---+-------- 861 2 | item 2 862 5 | item 5 863 7 | item 7 864(3 rows) 865 866delete from rtest_view3; 867insert into rtest_view4 select * from rtest_vview4 where 3 > refcount; 868select * 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) 875 876delete from rtest_view4; 877insert into rtest_view4 select * from rtest_vview5 where a > 2 and refcount = 0; 878select * 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) 885 886delete from rtest_view4; 887-- 888-- Test for computations in views 889-- 890create table rtest_comp ( 891 part text, 892 unit char(4), 893 size float 894); 895create table rtest_unitfact ( 896 unit char(4), 897 factor float 898); 899create 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; 903insert into rtest_unitfact values ('m', 100.0); 904insert into rtest_unitfact values ('cm', 1.0); 905insert into rtest_unitfact values ('inch', 2.54); 906insert into rtest_comp values ('p1', 'm', 5.0); 907insert into rtest_comp values ('p2', 'm', 3.0); 908insert into rtest_comp values ('p3', 'cm', 5.0); 909insert into rtest_comp values ('p4', 'cm', 15.0); 910insert into rtest_comp values ('p5', 'inch', 7.0); 911insert into rtest_comp values ('p6', 'inch', 4.4); 912select * 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.176 921(6 rows) 922 923select * 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.176 931(5 rows) 932 933-- 934-- In addition run the (slightly modified) queries from the 935-- programmers manual section on the rule system. 936-- 937CREATE 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); 945CREATE 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); 952CREATE TABLE unit ( 953 un_name char(8), -- the primary key 954 un_fact float -- factor to transform to cm 955); 956CREATE 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; 967CREATE 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; 976CREATE 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; 986INSERT INTO unit VALUES ('cm', 1.0); 987INSERT INTO unit VALUES ('m', 100.0); 988INSERT INTO unit VALUES ('inch', 2.54); 989INSERT INTO shoe_data VALUES ('sh1', 2, 'black', 70.0, 90.0, 'cm'); 990INSERT INTO shoe_data VALUES ('sh2', 0, 'black', 30.0, 40.0, 'inch'); 991INSERT INTO shoe_data VALUES ('sh3', 4, 'brown', 50.0, 65.0, 'cm'); 992INSERT INTO shoe_data VALUES ('sh4', 3, 'brown', 40.0, 50.0, 'inch'); 993INSERT INTO shoelace_data VALUES ('sl1', 5, 'black', 80.0, 'cm'); 994INSERT INTO shoelace_data VALUES ('sl2', 6, 'black', 100.0, 'cm'); 995INSERT INTO shoelace_data VALUES ('sl3', 0, 'black', 35.0 , 'inch'); 996INSERT INTO shoelace_data VALUES ('sl4', 8, 'black', 40.0 , 'inch'); 997INSERT INTO shoelace_data VALUES ('sl5', 4, 'brown', 1.0 , 'm'); 998INSERT INTO shoelace_data VALUES ('sl6', 0, 'brown', 0.9 , 'm'); 999INSERT INTO shoelace_data VALUES ('sl7', 7, 'brown', 60 , 'cm'); 1000INSERT INTO shoelace_data VALUES ('sl8', 1, 'brown', 40 , 'inch'); 1001-- SELECTs in doc 1002SELECT * 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) 1014 1015SELECT * 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) 1021 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 ); 1039UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7'; 1040SELECT * 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) 1045 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; 1080INSERT INTO shoelace_arrive VALUES ('sl3', 10); 1081INSERT INTO shoelace_arrive VALUES ('sl6', 20); 1082INSERT INTO shoelace_arrive VALUES ('sl8', 20); 1083SELECT * 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) 1095 1096insert into shoelace_ok select * from shoelace_arrive; 1097SELECT * 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) 1109 1110SELECT * 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) 1118 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; 1124insert into shoelace values ('sl9', 0, 'pink', 35.0, 'inch', 0.0); 1125insert into shoelace values ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0); 1126-- Unsupported (even though a similar updatable view construct is) 1127insert into shoelace values ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0) 1128 on conflict do nothing; 1129ERROR: INSERT with ON CONFLICT clause cannot be used with table that has INSERT or UPDATE rules 1130SELECT * 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) 1136 1137SELECT * 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) 1142 1143DELETE FROM shoelace WHERE EXISTS 1144 (SELECT * FROM shoelace_candelete 1145 WHERE sl_name = shoelace.sl_name); 1146SELECT * 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) 1159 1160SELECT * 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) 1168 1169SELECT count(*) FROM shoe; 1170 count 1171------- 1172 4 1173(1 row) 1174 1175-- 1176-- Simple test of qualified ON INSERT ... this did not work in 7.0 ... 1177-- 1178create table foo (f1 int); 1179create table foo2 (f1 int); 1180create rule foorule as on insert to foo where f1 < 100 1181do instead nothing; 1182insert into foo values(1); 1183insert into foo values(1001); 1184select * from foo; 1185 f1 1186------ 1187 1001 1188(1 row) 1189 1190drop rule foorule on foo; 1191-- this should fail because f1 is not exposed for unqualified reference: 1192create rule foorule as on insert to foo where f1 < 100 1193do instead insert into foo2 values (f1); 1194ERROR: column "f1" does not exist 1195LINE 2: do instead insert into foo2 values (f1); 1196 ^ 1197HINT: 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: 1199create rule foorule as on insert to foo where f1 < 100 1200do instead insert into foo2 values (new.f1); 1201insert into foo values(2); 1202insert into foo values(100); 1203select * from foo; 1204 f1 1205------ 1206 1001 1207 100 1208(2 rows) 1209 1210select * from foo2; 1211 f1 1212---- 1213 2 1214(1 row) 1215 1216drop rule foorule on foo; 1217drop table foo; 1218drop table 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-- 1223create table pparent (pid int, txt text); 1224insert into pparent values (1,'parent1'); 1225insert into pparent values (2,'parent2'); 1226create table cchild (pid int, descrip text); 1227insert into cchild values (1,'descrip1'); 1228create view vview as 1229 select pparent.pid, txt, descrip from 1230 pparent left join cchild using (pid); 1231create 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); 1238select * from vview; 1239 pid | txt | descrip 1240-----+---------+---------- 1241 1 | parent1 | descrip1 1242 2 | parent2 | 1243(2 rows) 1244 1245update vview set descrip='test1' where pid=1; 1246select * from vview; 1247 pid | txt | descrip 1248-----+---------+--------- 1249 1 | parent1 | test1 1250 2 | parent2 | 1251(2 rows) 1252 1253update vview set descrip='test2' where pid=2; 1254select * from vview; 1255 pid | txt | descrip 1256-----+---------+--------- 1257 1 | parent1 | test1 1258 2 | parent2 | test2 1259(2 rows) 1260 1261update vview set descrip='test3' where pid=3; 1262select * from vview; 1263 pid | txt | descrip 1264-----+---------+--------- 1265 1 | parent1 | test1 1266 2 | parent2 | test2 1267(2 rows) 1268 1269select * from cchild; 1270 pid | descrip 1271-----+--------- 1272 1 | test1 1273 2 | test2 1274(2 rows) 1275 1276drop rule rrule on vview; 1277drop view vview; 1278drop table pparent; 1279drop 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 1285SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schema' ORDER BY viewname; 1286iexit| SELECT ih.name, 1287 ih.thepath, 1288 interpt_pp(ih.thepath, r.thepath) AS exit 1289 FROM ihighway ih, 1290 ramp r 1291 WHERE (ih.thepath ## r.thepath); 1292key_dependent_view| SELECT view_base_table.key, 1293 view_base_table.data 1294 FROM view_base_table 1295 GROUP BY view_base_table.key; 1296key_dependent_view_no_cols| SELECT 1297 FROM view_base_table 1298 GROUP BY view_base_table.key 1299 HAVING (length((view_base_table.data)::text) > 0); 1300mvtest_tv| SELECT mvtest_t.type, 1301 sum(mvtest_t.amt) AS totamt 1302 FROM mvtest_t 1303 GROUP BY mvtest_t.type; 1304mvtest_tvv| SELECT sum(mvtest_tv.totamt) AS grandtot 1305 FROM mvtest_tv; 1306mvtest_tvvmv| SELECT mvtest_tvvm.grandtot 1307 FROM mvtest_tvvm; 1308pg_available_extension_versions| SELECT e.name, 1309 e.version, 1310 (x.extname IS NOT NULL) AS installed, 1311 e.superuser, 1312 e.relocatable, 1313 e.schema, 1314 e.requires, 1315 e.comment 1316 FROM (pg_available_extension_versions() e(name, version, superuser, relocatable, schema, requires, comment) 1317 LEFT JOIN pg_extension x ON (((e.name = x.extname) AND (e.version = x.extversion)))); 1318pg_available_extensions| SELECT e.name, 1319 e.default_version, 1320 x.extversion AS installed_version, 1321 e.comment 1322 FROM (pg_available_extensions() e(name, default_version, comment) 1323 LEFT JOIN pg_extension x ON ((e.name = x.extname))); 1324pg_config| SELECT pg_config.name, 1325 pg_config.setting 1326 FROM pg_config() pg_config(name, setting); 1327pg_cursors| SELECT c.name, 1328 c.statement, 1329 c.is_holdable, 1330 c.is_binary, 1331 c.is_scrollable, 1332 c.creation_time 1333 FROM pg_cursor() c(name, statement, is_holdable, is_binary, is_scrollable, creation_time); 1334pg_file_settings| SELECT a.sourcefile, 1335 a.sourceline, 1336 a.seqno, 1337 a.name, 1338 a.setting, 1339 a.applied, 1340 a.error 1341 FROM pg_show_all_file_settings() a(sourcefile, sourceline, seqno, name, setting, applied, error); 1342pg_group| SELECT pg_authid.rolname AS groname, 1343 pg_authid.oid AS grosysid, 1344 ARRAY( SELECT pg_auth_members.member 1345 FROM pg_auth_members 1346 WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist 1347 FROM pg_authid 1348 WHERE (NOT pg_authid.rolcanlogin); 1349pg_indexes| SELECT n.nspname AS schemaname, 1350 c.relname AS tablename, 1351 i.relname AS indexname, 1352 t.spcname AS tablespace, 1353 pg_get_indexdef(i.oid) AS indexdef 1354 FROM ((((pg_index x 1355 JOIN pg_class c ON ((c.oid = x.indrelid))) 1356 JOIN pg_class i ON ((i.oid = x.indexrelid))) 1357 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 1358 LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) 1359 WHERE ((c.relkind = ANY (ARRAY['r'::"char", 'm'::"char"])) AND (i.relkind = 'i'::"char")); 1360pg_locks| SELECT l.locktype, 1361 l.database, 1362 l.relation, 1363 l.page, 1364 l.tuple, 1365 l.virtualxid, 1366 l.transactionid, 1367 l.classid, 1368 l.objid, 1369 l.objsubid, 1370 l.virtualtransaction, 1371 l.pid, 1372 l.mode, 1373 l.granted, 1374 l.fastpath 1375 FROM pg_lock_status() l(locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted, fastpath); 1376pg_matviews| SELECT n.nspname AS schemaname, 1377 c.relname AS matviewname, 1378 pg_get_userbyid(c.relowner) AS matviewowner, 1379 t.spcname AS tablespace, 1380 c.relhasindex AS hasindexes, 1381 c.relispopulated AS ispopulated, 1382 pg_get_viewdef(c.oid) AS definition 1383 FROM ((pg_class c 1384 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 1385 LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) 1386 WHERE (c.relkind = 'm'::"char"); 1387pg_policies| SELECT n.nspname AS schemaname, 1388 c.relname AS tablename, 1389 pol.polname AS policyname, 1390 CASE 1391 WHEN (pol.polroles = '{0}'::oid[]) THEN (string_to_array('public'::text, ''::text))::name[] 1392 ELSE ARRAY( SELECT pg_authid.rolname 1393 FROM pg_authid 1394 WHERE (pg_authid.oid = ANY (pol.polroles)) 1395 ORDER BY pg_authid.rolname) 1396 END AS roles, 1397 CASE pol.polcmd 1398 WHEN 'r'::"char" THEN 'SELECT'::text 1399 WHEN 'a'::"char" THEN 'INSERT'::text 1400 WHEN 'w'::"char" THEN 'UPDATE'::text 1401 WHEN 'd'::"char" THEN 'DELETE'::text 1402 WHEN '*'::"char" THEN 'ALL'::text 1403 ELSE NULL::text 1404 END AS cmd, 1405 pg_get_expr(pol.polqual, pol.polrelid) AS qual, 1406 pg_get_expr(pol.polwithcheck, pol.polrelid) AS with_check 1407 FROM ((pg_policy pol 1408 JOIN pg_class c ON ((c.oid = pol.polrelid))) 1409 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))); 1410pg_prepared_statements| SELECT p.name, 1411 p.statement, 1412 p.prepare_time, 1413 p.parameter_types, 1414 p.from_sql 1415 FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql); 1416pg_prepared_xacts| SELECT p.transaction, 1417 p.gid, 1418 p.prepared, 1419 u.rolname AS owner, 1420 d.datname AS database 1421 FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid) 1422 LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) 1423 LEFT JOIN pg_database d ON ((p.dbid = d.oid))); 1424pg_replication_origin_status| SELECT pg_show_replication_origin_status.local_id, 1425 pg_show_replication_origin_status.external_id, 1426 pg_show_replication_origin_status.remote_lsn, 1427 pg_show_replication_origin_status.local_lsn 1428 FROM pg_show_replication_origin_status() pg_show_replication_origin_status(local_id, external_id, remote_lsn, local_lsn); 1429pg_replication_slots| SELECT l.slot_name, 1430 l.plugin, 1431 l.slot_type, 1432 l.datoid, 1433 d.datname AS database, 1434 l.active, 1435 l.active_pid, 1436 l.xmin, 1437 l.catalog_xmin, 1438 l.restart_lsn, 1439 l.confirmed_flush_lsn 1440 FROM (pg_get_replication_slots() l(slot_name, plugin, slot_type, datoid, active, active_pid, xmin, catalog_xmin, restart_lsn, confirmed_flush_lsn) 1441 LEFT JOIN pg_database d ON ((l.datoid = d.oid))); 1442pg_roles| SELECT pg_authid.rolname, 1443 pg_authid.rolsuper, 1444 pg_authid.rolinherit, 1445 pg_authid.rolcreaterole, 1446 pg_authid.rolcreatedb, 1447 pg_authid.rolcanlogin, 1448 pg_authid.rolreplication, 1449 pg_authid.rolconnlimit, 1450 '********'::text AS rolpassword, 1451 pg_authid.rolvaliduntil, 1452 pg_authid.rolbypassrls, 1453 s.setconfig AS rolconfig, 1454 pg_authid.oid 1455 FROM (pg_authid 1456 LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))); 1457pg_rules| SELECT n.nspname AS schemaname, 1458 c.relname AS tablename, 1459 r.rulename, 1460 pg_get_ruledef(r.oid) AS definition 1461 FROM ((pg_rewrite r 1462 JOIN pg_class c ON ((c.oid = r.ev_class))) 1463 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 1464 WHERE (r.rulename <> '_RETURN'::name); 1465pg_seclabels| SELECT l.objoid, 1466 l.classoid, 1467 l.objsubid, 1468 CASE 1469 WHEN (rel.relkind = 'r'::"char") THEN 'table'::text 1470 WHEN (rel.relkind = 'v'::"char") THEN 'view'::text 1471 WHEN (rel.relkind = 'm'::"char") THEN 'materialized view'::text 1472 WHEN (rel.relkind = 'S'::"char") THEN 'sequence'::text 1473 WHEN (rel.relkind = 'f'::"char") THEN 'foreign table'::text 1474 ELSE NULL::text 1475 END AS objtype, 1476 rel.relnamespace AS objnamespace, 1477 CASE 1478 WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) 1479 ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) 1480 END AS objname, 1481 l.provider, 1482 l.label 1483 FROM ((pg_seclabel l 1484 JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid)))) 1485 JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) 1486 WHERE (l.objsubid = 0) 1487UNION ALL 1488 SELECT l.objoid, 1489 l.classoid, 1490 l.objsubid, 1491 'column'::text AS objtype, 1492 rel.relnamespace AS objnamespace, 1493 (( 1494 CASE 1495 WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) 1496 ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) 1497 END || '.'::text) || (att.attname)::text) AS objname, 1498 l.provider, 1499 l.label 1500 FROM (((pg_seclabel l 1501 JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid)))) 1502 JOIN pg_attribute att ON (((rel.oid = att.attrelid) AND (l.objsubid = att.attnum)))) 1503 JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) 1504 WHERE (l.objsubid <> 0) 1505UNION ALL 1506 SELECT l.objoid, 1507 l.classoid, 1508 l.objsubid, 1509 CASE 1510 WHEN (pro.proisagg = true) THEN 'aggregate'::text 1511 WHEN (pro.proisagg = false) THEN 'function'::text 1512 ELSE NULL::text 1513 END AS objtype, 1514 pro.pronamespace AS objnamespace, 1515 ((( 1516 CASE 1517 WHEN pg_function_is_visible(pro.oid) THEN quote_ident((pro.proname)::text) 1518 ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((pro.proname)::text)) 1519 END || '('::text) || pg_get_function_arguments(pro.oid)) || ')'::text) AS objname, 1520 l.provider, 1521 l.label 1522 FROM ((pg_seclabel l 1523 JOIN pg_proc pro ON (((l.classoid = pro.tableoid) AND (l.objoid = pro.oid)))) 1524 JOIN pg_namespace nsp ON ((pro.pronamespace = nsp.oid))) 1525 WHERE (l.objsubid = 0) 1526UNION ALL 1527 SELECT l.objoid, 1528 l.classoid, 1529 l.objsubid, 1530 CASE 1531 WHEN (typ.typtype = 'd'::"char") THEN 'domain'::text 1532 ELSE 'type'::text 1533 END AS objtype, 1534 typ.typnamespace AS objnamespace, 1535 CASE 1536 WHEN pg_type_is_visible(typ.oid) THEN quote_ident((typ.typname)::text) 1537 ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((typ.typname)::text)) 1538 END AS objname, 1539 l.provider, 1540 l.label 1541 FROM ((pg_seclabel l 1542 JOIN pg_type typ ON (((l.classoid = typ.tableoid) AND (l.objoid = typ.oid)))) 1543 JOIN pg_namespace nsp ON ((typ.typnamespace = nsp.oid))) 1544 WHERE (l.objsubid = 0) 1545UNION ALL 1546 SELECT l.objoid, 1547 l.classoid, 1548 l.objsubid, 1549 'large object'::text AS objtype, 1550 NULL::oid AS objnamespace, 1551 (l.objoid)::text AS objname, 1552 l.provider, 1553 l.label 1554 FROM (pg_seclabel l 1555 JOIN pg_largeobject_metadata lom ON ((l.objoid = lom.oid))) 1556 WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND (l.objsubid = 0)) 1557UNION ALL 1558 SELECT l.objoid, 1559 l.classoid, 1560 l.objsubid, 1561 'language'::text AS objtype, 1562 NULL::oid AS objnamespace, 1563 quote_ident((lan.lanname)::text) AS objname, 1564 l.provider, 1565 l.label 1566 FROM (pg_seclabel l 1567 JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND (l.objoid = lan.oid)))) 1568 WHERE (l.objsubid = 0) 1569UNION ALL 1570 SELECT l.objoid, 1571 l.classoid, 1572 l.objsubid, 1573 'schema'::text AS objtype, 1574 nsp.oid AS objnamespace, 1575 quote_ident((nsp.nspname)::text) AS objname, 1576 l.provider, 1577 l.label 1578 FROM (pg_seclabel l 1579 JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid = nsp.oid)))) 1580 WHERE (l.objsubid = 0) 1581UNION ALL 1582 SELECT l.objoid, 1583 l.classoid, 1584 l.objsubid, 1585 'event trigger'::text AS objtype, 1586 NULL::oid AS objnamespace, 1587 quote_ident((evt.evtname)::text) AS objname, 1588 l.provider, 1589 l.label 1590 FROM (pg_seclabel l 1591 JOIN pg_event_trigger evt ON (((l.classoid = evt.tableoid) AND (l.objoid = evt.oid)))) 1592 WHERE (l.objsubid = 0) 1593UNION ALL 1594 SELECT l.objoid, 1595 l.classoid, 1596 0 AS objsubid, 1597 'database'::text AS objtype, 1598 NULL::oid AS objnamespace, 1599 quote_ident((dat.datname)::text) AS objname, 1600 l.provider, 1601 l.label 1602 FROM (pg_shseclabel l 1603 JOIN pg_database dat ON (((l.classoid = dat.tableoid) AND (l.objoid = dat.oid)))) 1604UNION ALL 1605 SELECT l.objoid, 1606 l.classoid, 1607 0 AS objsubid, 1608 'tablespace'::text AS objtype, 1609 NULL::oid AS objnamespace, 1610 quote_ident((spc.spcname)::text) AS objname, 1611 l.provider, 1612 l.label 1613 FROM (pg_shseclabel l 1614 JOIN pg_tablespace spc ON (((l.classoid = spc.tableoid) AND (l.objoid = spc.oid)))) 1615UNION ALL 1616 SELECT l.objoid, 1617 l.classoid, 1618 0 AS objsubid, 1619 'role'::text AS objtype, 1620 NULL::oid AS objnamespace, 1621 quote_ident((rol.rolname)::text) AS objname, 1622 l.provider, 1623 l.label 1624 FROM (pg_shseclabel l 1625 JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid)))); 1626pg_settings| SELECT a.name, 1627 a.setting, 1628 a.unit, 1629 a.category, 1630 a.short_desc, 1631 a.extra_desc, 1632 a.context, 1633 a.vartype, 1634 a.source, 1635 a.min_val, 1636 a.max_val, 1637 a.enumvals, 1638 a.boot_val, 1639 a.reset_val, 1640 a.sourcefile, 1641 a.sourceline, 1642 a.pending_restart 1643 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); 1644pg_shadow| SELECT pg_authid.rolname AS usename, 1645 pg_authid.oid AS usesysid, 1646 pg_authid.rolcreatedb AS usecreatedb, 1647 pg_authid.rolsuper AS usesuper, 1648 pg_authid.rolreplication AS userepl, 1649 pg_authid.rolbypassrls AS usebypassrls, 1650 pg_authid.rolpassword AS passwd, 1651 (pg_authid.rolvaliduntil)::abstime AS valuntil, 1652 s.setconfig AS useconfig 1653 FROM (pg_authid 1654 LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) 1655 WHERE pg_authid.rolcanlogin; 1656pg_stat_activity| SELECT s.datid, 1657 d.datname, 1658 s.pid, 1659 s.usesysid, 1660 u.rolname AS usename, 1661 s.application_name, 1662 s.client_addr, 1663 s.client_hostname, 1664 s.client_port, 1665 s.backend_start, 1666 s.xact_start, 1667 s.query_start, 1668 s.state_change, 1669 s.wait_event_type, 1670 s.wait_event, 1671 s.state, 1672 s.backend_xid, 1673 s.backend_xmin, 1674 s.query 1675 FROM pg_database d, 1676 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, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn), 1677 pg_authid u 1678 WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid)); 1679pg_stat_all_indexes| SELECT c.oid AS relid, 1680 i.oid AS indexrelid, 1681 n.nspname AS schemaname, 1682 c.relname, 1683 i.relname AS indexrelname, 1684 pg_stat_get_numscans(i.oid) AS idx_scan, 1685 pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, 1686 pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch 1687 FROM (((pg_class c 1688 JOIN pg_index x ON ((c.oid = x.indrelid))) 1689 JOIN pg_class i ON ((i.oid = x.indexrelid))) 1690 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 1691 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])); 1692pg_stat_all_tables| SELECT c.oid AS relid, 1693 n.nspname AS schemaname, 1694 c.relname, 1695 pg_stat_get_numscans(c.oid) AS seq_scan, 1696 pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, 1697 (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, 1698 ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, 1699 pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, 1700 pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, 1701 pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, 1702 pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, 1703 pg_stat_get_live_tuples(c.oid) AS n_live_tup, 1704 pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, 1705 pg_stat_get_mod_since_analyze(c.oid) AS n_mod_since_analyze, 1706 pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, 1707 pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, 1708 pg_stat_get_last_analyze_time(c.oid) AS last_analyze, 1709 pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, 1710 pg_stat_get_vacuum_count(c.oid) AS vacuum_count, 1711 pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, 1712 pg_stat_get_analyze_count(c.oid) AS analyze_count, 1713 pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count 1714 FROM ((pg_class c 1715 LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) 1716 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 1717 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])) 1718 GROUP BY c.oid, n.nspname, c.relname; 1719pg_stat_archiver| SELECT s.archived_count, 1720 s.last_archived_wal, 1721 s.last_archived_time, 1722 s.failed_count, 1723 s.last_failed_wal, 1724 s.last_failed_time, 1725 s.stats_reset 1726 FROM pg_stat_get_archiver() s(archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time, stats_reset); 1727pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, 1728 pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, 1729 pg_stat_get_checkpoint_write_time() AS checkpoint_write_time, 1730 pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time, 1731 pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, 1732 pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, 1733 pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, 1734 pg_stat_get_buf_written_backend() AS buffers_backend, 1735 pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, 1736 pg_stat_get_buf_alloc() AS buffers_alloc, 1737 pg_stat_get_bgwriter_stat_reset_time() AS stats_reset; 1738pg_stat_database| SELECT d.oid AS datid, 1739 d.datname, 1740 pg_stat_get_db_numbackends(d.oid) AS numbackends, 1741 pg_stat_get_db_xact_commit(d.oid) AS xact_commit, 1742 pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, 1743 (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, 1744 pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, 1745 pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, 1746 pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, 1747 pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, 1748 pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, 1749 pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted, 1750 pg_stat_get_db_conflict_all(d.oid) AS conflicts, 1751 pg_stat_get_db_temp_files(d.oid) AS temp_files, 1752 pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes, 1753 pg_stat_get_db_deadlocks(d.oid) AS deadlocks, 1754 pg_stat_get_db_blk_read_time(d.oid) AS blk_read_time, 1755 pg_stat_get_db_blk_write_time(d.oid) AS blk_write_time, 1756 pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset 1757 FROM pg_database d; 1758pg_stat_database_conflicts| SELECT d.oid AS datid, 1759 d.datname, 1760 pg_stat_get_db_conflict_tablespace(d.oid) AS confl_tablespace, 1761 pg_stat_get_db_conflict_lock(d.oid) AS confl_lock, 1762 pg_stat_get_db_conflict_snapshot(d.oid) AS confl_snapshot, 1763 pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin, 1764 pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock 1765 FROM pg_database d; 1766pg_stat_progress_vacuum| SELECT s.pid, 1767 s.datid, 1768 d.datname, 1769 s.relid, 1770 CASE s.param1 1771 WHEN 0 THEN 'initializing'::text 1772 WHEN 1 THEN 'scanning heap'::text 1773 WHEN 2 THEN 'vacuuming indexes'::text 1774 WHEN 3 THEN 'vacuuming heap'::text 1775 WHEN 4 THEN 'cleaning up indexes'::text 1776 WHEN 5 THEN 'truncating heap'::text 1777 WHEN 6 THEN 'performing final cleanup'::text 1778 ELSE NULL::text 1779 END AS phase, 1780 s.param2 AS heap_blks_total, 1781 s.param3 AS heap_blks_scanned, 1782 s.param4 AS heap_blks_vacuumed, 1783 s.param5 AS index_vacuum_count, 1784 s.param6 AS max_dead_tuples, 1785 s.param7 AS num_dead_tuples 1786 FROM (pg_stat_get_progress_info('VACUUM'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10) 1787 JOIN pg_database d ON ((s.datid = d.oid))); 1788pg_stat_replication| SELECT s.pid, 1789 s.usesysid, 1790 u.rolname AS usename, 1791 s.application_name, 1792 s.client_addr, 1793 s.client_hostname, 1794 s.client_port, 1795 s.backend_start, 1796 s.backend_xmin, 1797 w.state, 1798 w.sent_location, 1799 w.write_location, 1800 w.flush_location, 1801 w.replay_location, 1802 w.sync_priority, 1803 w.sync_state 1804 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, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn), 1805 pg_authid u, 1806 pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) 1807 WHERE ((s.usesysid = u.oid) AND (s.pid = w.pid)); 1808pg_stat_ssl| SELECT s.pid, 1809 s.ssl, 1810 s.sslversion AS version, 1811 s.sslcipher AS cipher, 1812 s.sslbits AS bits, 1813 s.sslcompression AS compression, 1814 s.sslclientdn AS clientdn 1815 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, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn); 1816pg_stat_sys_indexes| SELECT pg_stat_all_indexes.relid, 1817 pg_stat_all_indexes.indexrelid, 1818 pg_stat_all_indexes.schemaname, 1819 pg_stat_all_indexes.relname, 1820 pg_stat_all_indexes.indexrelname, 1821 pg_stat_all_indexes.idx_scan, 1822 pg_stat_all_indexes.idx_tup_read, 1823 pg_stat_all_indexes.idx_tup_fetch 1824 FROM pg_stat_all_indexes 1825 WHERE ((pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_indexes.schemaname ~ '^pg_toast'::text)); 1826pg_stat_sys_tables| SELECT pg_stat_all_tables.relid, 1827 pg_stat_all_tables.schemaname, 1828 pg_stat_all_tables.relname, 1829 pg_stat_all_tables.seq_scan, 1830 pg_stat_all_tables.seq_tup_read, 1831 pg_stat_all_tables.idx_scan, 1832 pg_stat_all_tables.idx_tup_fetch, 1833 pg_stat_all_tables.n_tup_ins, 1834 pg_stat_all_tables.n_tup_upd, 1835 pg_stat_all_tables.n_tup_del, 1836 pg_stat_all_tables.n_tup_hot_upd, 1837 pg_stat_all_tables.n_live_tup, 1838 pg_stat_all_tables.n_dead_tup, 1839 pg_stat_all_tables.n_mod_since_analyze, 1840 pg_stat_all_tables.last_vacuum, 1841 pg_stat_all_tables.last_autovacuum, 1842 pg_stat_all_tables.last_analyze, 1843 pg_stat_all_tables.last_autoanalyze, 1844 pg_stat_all_tables.vacuum_count, 1845 pg_stat_all_tables.autovacuum_count, 1846 pg_stat_all_tables.analyze_count, 1847 pg_stat_all_tables.autoanalyze_count 1848 FROM pg_stat_all_tables 1849 WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text)); 1850pg_stat_user_functions| SELECT p.oid AS funcid, 1851 n.nspname AS schemaname, 1852 p.proname AS funcname, 1853 pg_stat_get_function_calls(p.oid) AS calls, 1854 pg_stat_get_function_total_time(p.oid) AS total_time, 1855 pg_stat_get_function_self_time(p.oid) AS self_time 1856 FROM (pg_proc p 1857 LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) 1858 WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL)); 1859pg_stat_user_indexes| SELECT pg_stat_all_indexes.relid, 1860 pg_stat_all_indexes.indexrelid, 1861 pg_stat_all_indexes.schemaname, 1862 pg_stat_all_indexes.relname, 1863 pg_stat_all_indexes.indexrelname, 1864 pg_stat_all_indexes.idx_scan, 1865 pg_stat_all_indexes.idx_tup_read, 1866 pg_stat_all_indexes.idx_tup_fetch 1867 FROM pg_stat_all_indexes 1868 WHERE ((pg_stat_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_indexes.schemaname !~ '^pg_toast'::text)); 1869pg_stat_user_tables| SELECT pg_stat_all_tables.relid, 1870 pg_stat_all_tables.schemaname, 1871 pg_stat_all_tables.relname, 1872 pg_stat_all_tables.seq_scan, 1873 pg_stat_all_tables.seq_tup_read, 1874 pg_stat_all_tables.idx_scan, 1875 pg_stat_all_tables.idx_tup_fetch, 1876 pg_stat_all_tables.n_tup_ins, 1877 pg_stat_all_tables.n_tup_upd, 1878 pg_stat_all_tables.n_tup_del, 1879 pg_stat_all_tables.n_tup_hot_upd, 1880 pg_stat_all_tables.n_live_tup, 1881 pg_stat_all_tables.n_dead_tup, 1882 pg_stat_all_tables.n_mod_since_analyze, 1883 pg_stat_all_tables.last_vacuum, 1884 pg_stat_all_tables.last_autovacuum, 1885 pg_stat_all_tables.last_analyze, 1886 pg_stat_all_tables.last_autoanalyze, 1887 pg_stat_all_tables.vacuum_count, 1888 pg_stat_all_tables.autovacuum_count, 1889 pg_stat_all_tables.analyze_count, 1890 pg_stat_all_tables.autoanalyze_count 1891 FROM pg_stat_all_tables 1892 WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text)); 1893pg_stat_wal_receiver| SELECT s.pid, 1894 s.status, 1895 s.receive_start_lsn, 1896 s.receive_start_tli, 1897 s.received_lsn, 1898 s.received_tli, 1899 s.last_msg_send_time, 1900 s.last_msg_receipt_time, 1901 s.latest_end_lsn, 1902 s.latest_end_time, 1903 s.slot_name, 1904 s.conninfo 1905 FROM pg_stat_get_wal_receiver() s(pid, status, receive_start_lsn, receive_start_tli, received_lsn, received_tli, last_msg_send_time, last_msg_receipt_time, latest_end_lsn, latest_end_time, slot_name, conninfo) 1906 WHERE (s.pid IS NOT NULL); 1907pg_stat_xact_all_tables| SELECT c.oid AS relid, 1908 n.nspname AS schemaname, 1909 c.relname, 1910 pg_stat_get_xact_numscans(c.oid) AS seq_scan, 1911 pg_stat_get_xact_tuples_returned(c.oid) AS seq_tup_read, 1912 (sum(pg_stat_get_xact_numscans(i.indexrelid)))::bigint AS idx_scan, 1913 ((sum(pg_stat_get_xact_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_xact_tuples_fetched(c.oid)) AS idx_tup_fetch, 1914 pg_stat_get_xact_tuples_inserted(c.oid) AS n_tup_ins, 1915 pg_stat_get_xact_tuples_updated(c.oid) AS n_tup_upd, 1916 pg_stat_get_xact_tuples_deleted(c.oid) AS n_tup_del, 1917 pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd 1918 FROM ((pg_class c 1919 LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) 1920 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 1921 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])) 1922 GROUP BY c.oid, n.nspname, c.relname; 1923pg_stat_xact_sys_tables| SELECT pg_stat_xact_all_tables.relid, 1924 pg_stat_xact_all_tables.schemaname, 1925 pg_stat_xact_all_tables.relname, 1926 pg_stat_xact_all_tables.seq_scan, 1927 pg_stat_xact_all_tables.seq_tup_read, 1928 pg_stat_xact_all_tables.idx_scan, 1929 pg_stat_xact_all_tables.idx_tup_fetch, 1930 pg_stat_xact_all_tables.n_tup_ins, 1931 pg_stat_xact_all_tables.n_tup_upd, 1932 pg_stat_xact_all_tables.n_tup_del, 1933 pg_stat_xact_all_tables.n_tup_hot_upd 1934 FROM pg_stat_xact_all_tables 1935 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)); 1936pg_stat_xact_user_functions| SELECT p.oid AS funcid, 1937 n.nspname AS schemaname, 1938 p.proname AS funcname, 1939 pg_stat_get_xact_function_calls(p.oid) AS calls, 1940 pg_stat_get_xact_function_total_time(p.oid) AS total_time, 1941 pg_stat_get_xact_function_self_time(p.oid) AS self_time 1942 FROM (pg_proc p 1943 LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) 1944 WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_xact_function_calls(p.oid) IS NOT NULL)); 1945pg_stat_xact_user_tables| SELECT pg_stat_xact_all_tables.relid, 1946 pg_stat_xact_all_tables.schemaname, 1947 pg_stat_xact_all_tables.relname, 1948 pg_stat_xact_all_tables.seq_scan, 1949 pg_stat_xact_all_tables.seq_tup_read, 1950 pg_stat_xact_all_tables.idx_scan, 1951 pg_stat_xact_all_tables.idx_tup_fetch, 1952 pg_stat_xact_all_tables.n_tup_ins, 1953 pg_stat_xact_all_tables.n_tup_upd, 1954 pg_stat_xact_all_tables.n_tup_del, 1955 pg_stat_xact_all_tables.n_tup_hot_upd 1956 FROM pg_stat_xact_all_tables 1957 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)); 1958pg_statio_all_indexes| SELECT c.oid AS relid, 1959 i.oid AS indexrelid, 1960 n.nspname AS schemaname, 1961 c.relname, 1962 i.relname AS indexrelname, 1963 (pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read, 1964 pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit 1965 FROM (((pg_class c 1966 JOIN pg_index x ON ((c.oid = x.indrelid))) 1967 JOIN pg_class i ON ((i.oid = x.indexrelid))) 1968 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 1969 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])); 1970pg_statio_all_sequences| SELECT c.oid AS relid, 1971 n.nspname AS schemaname, 1972 c.relname, 1973 (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read, 1974 pg_stat_get_blocks_hit(c.oid) AS blks_hit 1975 FROM (pg_class c 1976 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 1977 WHERE (c.relkind = 'S'::"char"); 1978pg_statio_all_tables| SELECT c.oid AS relid, 1979 n.nspname AS schemaname, 1980 c.relname, 1981 (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS heap_blks_read, 1982 pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit, 1983 (sum((pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))))::bigint AS idx_blks_read, 1984 (sum(pg_stat_get_blocks_hit(i.indexrelid)))::bigint AS idx_blks_hit, 1985 (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read, 1986 pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit, 1987 (sum((pg_stat_get_blocks_fetched(x.indexrelid) - pg_stat_get_blocks_hit(x.indexrelid))))::bigint AS tidx_blks_read, 1988 (sum(pg_stat_get_blocks_hit(x.indexrelid)))::bigint AS tidx_blks_hit 1989 FROM ((((pg_class c 1990 LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) 1991 LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid))) 1992 LEFT JOIN pg_index x ON ((t.oid = x.indrelid))) 1993 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 1994 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])) 1995 GROUP BY c.oid, n.nspname, c.relname, t.oid, x.indrelid; 1996pg_statio_sys_indexes| SELECT pg_statio_all_indexes.relid, 1997 pg_statio_all_indexes.indexrelid, 1998 pg_statio_all_indexes.schemaname, 1999 pg_statio_all_indexes.relname, 2000 pg_statio_all_indexes.indexrelname, 2001 pg_statio_all_indexes.idx_blks_read, 2002 pg_statio_all_indexes.idx_blks_hit 2003 FROM pg_statio_all_indexes 2004 WHERE ((pg_statio_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_indexes.schemaname ~ '^pg_toast'::text)); 2005pg_statio_sys_sequences| SELECT pg_statio_all_sequences.relid, 2006 pg_statio_all_sequences.schemaname, 2007 pg_statio_all_sequences.relname, 2008 pg_statio_all_sequences.blks_read, 2009 pg_statio_all_sequences.blks_hit 2010 FROM pg_statio_all_sequences 2011 WHERE ((pg_statio_all_sequences.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_sequences.schemaname ~ '^pg_toast'::text)); 2012pg_statio_sys_tables| SELECT pg_statio_all_tables.relid, 2013 pg_statio_all_tables.schemaname, 2014 pg_statio_all_tables.relname, 2015 pg_statio_all_tables.heap_blks_read, 2016 pg_statio_all_tables.heap_blks_hit, 2017 pg_statio_all_tables.idx_blks_read, 2018 pg_statio_all_tables.idx_blks_hit, 2019 pg_statio_all_tables.toast_blks_read, 2020 pg_statio_all_tables.toast_blks_hit, 2021 pg_statio_all_tables.tidx_blks_read, 2022 pg_statio_all_tables.tidx_blks_hit 2023 FROM pg_statio_all_tables 2024 WHERE ((pg_statio_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_tables.schemaname ~ '^pg_toast'::text)); 2025pg_statio_user_indexes| SELECT pg_statio_all_indexes.relid, 2026 pg_statio_all_indexes.indexrelid, 2027 pg_statio_all_indexes.schemaname, 2028 pg_statio_all_indexes.relname, 2029 pg_statio_all_indexes.indexrelname, 2030 pg_statio_all_indexes.idx_blks_read, 2031 pg_statio_all_indexes.idx_blks_hit 2032 FROM pg_statio_all_indexes 2033 WHERE ((pg_statio_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_indexes.schemaname !~ '^pg_toast'::text)); 2034pg_statio_user_sequences| SELECT pg_statio_all_sequences.relid, 2035 pg_statio_all_sequences.schemaname, 2036 pg_statio_all_sequences.relname, 2037 pg_statio_all_sequences.blks_read, 2038 pg_statio_all_sequences.blks_hit 2039 FROM pg_statio_all_sequences 2040 WHERE ((pg_statio_all_sequences.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_sequences.schemaname !~ '^pg_toast'::text)); 2041pg_statio_user_tables| SELECT pg_statio_all_tables.relid, 2042 pg_statio_all_tables.schemaname, 2043 pg_statio_all_tables.relname, 2044 pg_statio_all_tables.heap_blks_read, 2045 pg_statio_all_tables.heap_blks_hit, 2046 pg_statio_all_tables.idx_blks_read, 2047 pg_statio_all_tables.idx_blks_hit, 2048 pg_statio_all_tables.toast_blks_read, 2049 pg_statio_all_tables.toast_blks_hit, 2050 pg_statio_all_tables.tidx_blks_read, 2051 pg_statio_all_tables.tidx_blks_hit 2052 FROM pg_statio_all_tables 2053 WHERE ((pg_statio_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_tables.schemaname !~ '^pg_toast'::text)); 2054pg_stats| SELECT n.nspname AS schemaname, 2055 c.relname AS tablename, 2056 a.attname, 2057 s.stainherit AS inherited, 2058 s.stanullfrac AS null_frac, 2059 s.stawidth AS avg_width, 2060 s.stadistinct AS n_distinct, 2061 CASE 2062 WHEN (s.stakind1 = 1) THEN s.stavalues1 2063 WHEN (s.stakind2 = 1) THEN s.stavalues2 2064 WHEN (s.stakind3 = 1) THEN s.stavalues3 2065 WHEN (s.stakind4 = 1) THEN s.stavalues4 2066 WHEN (s.stakind5 = 1) THEN s.stavalues5 2067 ELSE NULL::anyarray 2068 END AS most_common_vals, 2069 CASE 2070 WHEN (s.stakind1 = 1) THEN s.stanumbers1 2071 WHEN (s.stakind2 = 1) THEN s.stanumbers2 2072 WHEN (s.stakind3 = 1) THEN s.stanumbers3 2073 WHEN (s.stakind4 = 1) THEN s.stanumbers4 2074 WHEN (s.stakind5 = 1) THEN s.stanumbers5 2075 ELSE NULL::real[] 2076 END AS most_common_freqs, 2077 CASE 2078 WHEN (s.stakind1 = 2) THEN s.stavalues1 2079 WHEN (s.stakind2 = 2) THEN s.stavalues2 2080 WHEN (s.stakind3 = 2) THEN s.stavalues3 2081 WHEN (s.stakind4 = 2) THEN s.stavalues4 2082 WHEN (s.stakind5 = 2) THEN s.stavalues5 2083 ELSE NULL::anyarray 2084 END AS histogram_bounds, 2085 CASE 2086 WHEN (s.stakind1 = 3) THEN s.stanumbers1[1] 2087 WHEN (s.stakind2 = 3) THEN s.stanumbers2[1] 2088 WHEN (s.stakind3 = 3) THEN s.stanumbers3[1] 2089 WHEN (s.stakind4 = 3) THEN s.stanumbers4[1] 2090 WHEN (s.stakind5 = 3) THEN s.stanumbers5[1] 2091 ELSE NULL::real 2092 END AS correlation, 2093 CASE 2094 WHEN (s.stakind1 = 4) THEN s.stavalues1 2095 WHEN (s.stakind2 = 4) THEN s.stavalues2 2096 WHEN (s.stakind3 = 4) THEN s.stavalues3 2097 WHEN (s.stakind4 = 4) THEN s.stavalues4 2098 WHEN (s.stakind5 = 4) THEN s.stavalues5 2099 ELSE NULL::anyarray 2100 END AS most_common_elems, 2101 CASE 2102 WHEN (s.stakind1 = 4) THEN s.stanumbers1 2103 WHEN (s.stakind2 = 4) THEN s.stanumbers2 2104 WHEN (s.stakind3 = 4) THEN s.stanumbers3 2105 WHEN (s.stakind4 = 4) THEN s.stanumbers4 2106 WHEN (s.stakind5 = 4) THEN s.stanumbers5 2107 ELSE NULL::real[] 2108 END AS most_common_elem_freqs, 2109 CASE 2110 WHEN (s.stakind1 = 5) THEN s.stanumbers1 2111 WHEN (s.stakind2 = 5) THEN s.stanumbers2 2112 WHEN (s.stakind3 = 5) THEN s.stanumbers3 2113 WHEN (s.stakind4 = 5) THEN s.stanumbers4 2114 WHEN (s.stakind5 = 5) THEN s.stanumbers5 2115 ELSE NULL::real[] 2116 END AS elem_count_histogram 2117 FROM (((pg_statistic s 2118 JOIN pg_class c ON ((c.oid = s.starelid))) 2119 JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) 2120 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 2121 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)))); 2122pg_tables| SELECT n.nspname AS schemaname, 2123 c.relname AS tablename, 2124 pg_get_userbyid(c.relowner) AS tableowner, 2125 t.spcname AS tablespace, 2126 c.relhasindex AS hasindexes, 2127 c.relhasrules AS hasrules, 2128 c.relhastriggers AS hastriggers, 2129 c.relrowsecurity AS rowsecurity 2130 FROM ((pg_class c 2131 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 2132 LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) 2133 WHERE (c.relkind = 'r'::"char"); 2134pg_timezone_abbrevs| SELECT pg_timezone_abbrevs.abbrev, 2135 pg_timezone_abbrevs.utc_offset, 2136 pg_timezone_abbrevs.is_dst 2137 FROM pg_timezone_abbrevs() pg_timezone_abbrevs(abbrev, utc_offset, is_dst); 2138pg_timezone_names| SELECT pg_timezone_names.name, 2139 pg_timezone_names.abbrev, 2140 pg_timezone_names.utc_offset, 2141 pg_timezone_names.is_dst 2142 FROM pg_timezone_names() pg_timezone_names(name, abbrev, utc_offset, is_dst); 2143pg_user| SELECT pg_shadow.usename, 2144 pg_shadow.usesysid, 2145 pg_shadow.usecreatedb, 2146 pg_shadow.usesuper, 2147 pg_shadow.userepl, 2148 pg_shadow.usebypassrls, 2149 '********'::text AS passwd, 2150 pg_shadow.valuntil, 2151 pg_shadow.useconfig 2152 FROM pg_shadow; 2153pg_user_mappings| SELECT u.oid AS umid, 2154 s.oid AS srvid, 2155 s.srvname, 2156 u.umuser, 2157 CASE 2158 WHEN (u.umuser = (0)::oid) THEN 'public'::name 2159 ELSE a.rolname 2160 END AS usename, 2161 CASE 2162 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 2163 FROM pg_authid 2164 WHERE (pg_authid.rolname = "current_user"()))) THEN u.umoptions 2165 ELSE NULL::text[] 2166 END AS umoptions 2167 FROM ((pg_user_mapping u 2168 LEFT JOIN pg_authid a ON ((a.oid = u.umuser))) 2169 JOIN pg_foreign_server s ON ((u.umserver = s.oid))); 2170pg_views| SELECT n.nspname AS schemaname, 2171 c.relname AS viewname, 2172 pg_get_userbyid(c.relowner) AS viewowner, 2173 pg_get_viewdef(c.oid) AS definition 2174 FROM (pg_class c 2175 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 2176 WHERE (c.relkind = 'v'::"char"); 2177rtest_v1| SELECT rtest_t1.a, 2178 rtest_t1.b 2179 FROM rtest_t1; 2180rtest_vcomp| SELECT x.part, 2181 (x.size * y.factor) AS size_in_cm 2182 FROM rtest_comp x, 2183 rtest_unitfact y 2184 WHERE (x.unit = y.unit); 2185rtest_vview1| SELECT x.a, 2186 x.b 2187 FROM rtest_view1 x 2188 WHERE (0 < ( SELECT count(*) AS count 2189 FROM rtest_view2 y 2190 WHERE (y.a = x.a))); 2191rtest_vview2| SELECT rtest_view1.a, 2192 rtest_view1.b 2193 FROM rtest_view1 2194 WHERE rtest_view1.v; 2195rtest_vview3| SELECT x.a, 2196 x.b 2197 FROM rtest_vview2 x 2198 WHERE (0 < ( SELECT count(*) AS count 2199 FROM rtest_view2 y 2200 WHERE (y.a = x.a))); 2201rtest_vview4| SELECT x.a, 2202 x.b, 2203 count(y.a) AS refcount 2204 FROM rtest_view1 x, 2205 rtest_view2 y 2206 WHERE (x.a = y.a) 2207 GROUP BY x.a, x.b; 2208rtest_vview5| SELECT rtest_view1.a, 2209 rtest_view1.b, 2210 rtest_viewfunc1(rtest_view1.a) AS refcount 2211 FROM rtest_view1; 2212shoe| SELECT sh.shoename, 2213 sh.sh_avail, 2214 sh.slcolor, 2215 sh.slminlen, 2216 (sh.slminlen * un.un_fact) AS slminlen_cm, 2217 sh.slmaxlen, 2218 (sh.slmaxlen * un.un_fact) AS slmaxlen_cm, 2219 sh.slunit 2220 FROM shoe_data sh, 2221 unit un 2222 WHERE (sh.slunit = un.un_name); 2223shoe_ready| SELECT rsh.shoename, 2224 rsh.sh_avail, 2225 rsl.sl_name, 2226 rsl.sl_avail, 2227 int4smaller(rsh.sh_avail, rsl.sl_avail) AS total_avail 2228 FROM shoe rsh, 2229 shoelace rsl 2230 WHERE ((rsl.sl_color = rsh.slcolor) AND (rsl.sl_len_cm >= rsh.slminlen_cm) AND (rsl.sl_len_cm <= rsh.slmaxlen_cm)); 2231shoelace| SELECT s.sl_name, 2232 s.sl_avail, 2233 s.sl_color, 2234 s.sl_len, 2235 s.sl_unit, 2236 (s.sl_len * u.un_fact) AS sl_len_cm 2237 FROM shoelace_data s, 2238 unit u 2239 WHERE (s.sl_unit = u.un_name); 2240shoelace_candelete| SELECT shoelace_obsolete.sl_name, 2241 shoelace_obsolete.sl_avail, 2242 shoelace_obsolete.sl_color, 2243 shoelace_obsolete.sl_len, 2244 shoelace_obsolete.sl_unit, 2245 shoelace_obsolete.sl_len_cm 2246 FROM shoelace_obsolete 2247 WHERE (shoelace_obsolete.sl_avail = 0); 2248shoelace_obsolete| SELECT shoelace.sl_name, 2249 shoelace.sl_avail, 2250 shoelace.sl_color, 2251 shoelace.sl_len, 2252 shoelace.sl_unit, 2253 shoelace.sl_len_cm 2254 FROM shoelace 2255 WHERE (NOT (EXISTS ( SELECT shoe.shoename 2256 FROM shoe 2257 WHERE (shoe.slcolor = shoelace.sl_color)))); 2258street| SELECT r.name, 2259 r.thepath, 2260 c.cname 2261 FROM ONLY road r, 2262 real_city c 2263 WHERE (c.outline ## r.thepath); 2264test_tablesample_v1| SELECT test_tablesample.id 2265 FROM test_tablesample TABLESAMPLE system ((10 * 2)) REPEATABLE (2); 2266test_tablesample_v2| SELECT test_tablesample.id 2267 FROM test_tablesample TABLESAMPLE system (99); 2268toyemp| SELECT emp.name, 2269 emp.age, 2270 emp.location, 2271 (12 * emp.salary) AS annualsal 2272 FROM emp; 2273SELECT tablename, rulename, definition FROM pg_rules 2274 ORDER BY tablename, rulename; 2275pg_settings|pg_settings_n|CREATE RULE pg_settings_n AS 2276 ON UPDATE TO pg_catalog.pg_settings DO INSTEAD NOTHING; 2277pg_settings|pg_settings_u|CREATE RULE pg_settings_u AS 2278 ON UPDATE TO pg_catalog.pg_settings 2279 WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, false) AS set_config; 2280rtest_emp|rtest_emp_del|CREATE RULE rtest_emp_del AS 2281 ON DELETE TO public.rtest_emp DO INSERT INTO rtest_emplog (ename, who, action, newsal, oldsal) 2282 VALUES (old.ename, "current_user"(), 'fired'::bpchar, '$0.00'::money, old.salary); 2283rtest_emp|rtest_emp_ins|CREATE RULE rtest_emp_ins AS 2284 ON INSERT TO public.rtest_emp DO INSERT INTO rtest_emplog (ename, who, action, newsal, oldsal) 2285 VALUES (new.ename, "current_user"(), 'hired'::bpchar, new.salary, '$0.00'::money); 2286rtest_emp|rtest_emp_upd|CREATE RULE rtest_emp_upd AS 2287 ON UPDATE TO public.rtest_emp 2288 WHERE (new.salary <> old.salary) DO INSERT INTO rtest_emplog (ename, who, action, newsal, oldsal) 2289 VALUES (new.ename, "current_user"(), 'honored'::bpchar, new.salary, old.salary); 2290rtest_nothn1|rtest_nothn_r1|CREATE RULE rtest_nothn_r1 AS 2291 ON INSERT TO public.rtest_nothn1 2292 WHERE ((new.a >= 10) AND (new.a < 20)) DO INSTEAD NOTHING; 2293rtest_nothn1|rtest_nothn_r2|CREATE RULE rtest_nothn_r2 AS 2294 ON INSERT TO public.rtest_nothn1 2295 WHERE ((new.a >= 30) AND (new.a < 40)) DO INSTEAD NOTHING; 2296rtest_nothn2|rtest_nothn_r3|CREATE RULE rtest_nothn_r3 AS 2297 ON INSERT TO public.rtest_nothn2 2298 WHERE (new.a >= 100) DO INSTEAD INSERT INTO rtest_nothn3 (a, b) 2299 VALUES (new.a, new.b); 2300rtest_nothn2|rtest_nothn_r4|CREATE RULE rtest_nothn_r4 AS 2301 ON INSERT TO public.rtest_nothn2 DO INSTEAD NOTHING; 2302rtest_order1|rtest_order_r1|CREATE RULE rtest_order_r1 AS 2303 ON INSERT TO public.rtest_order1 DO INSTEAD INSERT INTO rtest_order2 (a, b, c) 2304 VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 1 - this should run 1st'::text); 2305rtest_order1|rtest_order_r2|CREATE RULE rtest_order_r2 AS 2306 ON INSERT TO public.rtest_order1 DO INSERT INTO rtest_order2 (a, b, c) 2307 VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 2 - this should run 2nd'::text); 2308rtest_order1|rtest_order_r3|CREATE RULE rtest_order_r3 AS 2309 ON INSERT TO public.rtest_order1 DO INSTEAD INSERT INTO rtest_order2 (a, b, c) 2310 VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 3 - this should run 3rd'::text); 2311rtest_order1|rtest_order_r4|CREATE RULE rtest_order_r4 AS 2312 ON INSERT TO public.rtest_order1 2313 WHERE (new.a < 100) DO INSTEAD INSERT INTO rtest_order2 (a, b, c) 2314 VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 4 - this should run 4th'::text); 2315rtest_person|rtest_pers_del|CREATE RULE rtest_pers_del AS 2316 ON DELETE TO public.rtest_person DO DELETE FROM rtest_admin 2317 WHERE (rtest_admin.pname = old.pname); 2318rtest_person|rtest_pers_upd|CREATE RULE rtest_pers_upd AS 2319 ON UPDATE TO public.rtest_person DO UPDATE rtest_admin SET pname = new.pname 2320 WHERE (rtest_admin.pname = old.pname); 2321rtest_system|rtest_sys_del|CREATE RULE rtest_sys_del AS 2322 ON DELETE TO public.rtest_system DO ( DELETE FROM rtest_interface 2323 WHERE (rtest_interface.sysname = old.sysname); 2324 DELETE FROM rtest_admin 2325 WHERE (rtest_admin.sysname = old.sysname); 2326); 2327rtest_system|rtest_sys_upd|CREATE RULE rtest_sys_upd AS 2328 ON UPDATE TO public.rtest_system DO ( UPDATE rtest_interface SET sysname = new.sysname 2329 WHERE (rtest_interface.sysname = old.sysname); 2330 UPDATE rtest_admin SET sysname = new.sysname 2331 WHERE (rtest_admin.sysname = old.sysname); 2332); 2333rtest_t4|rtest_t4_ins1|CREATE RULE rtest_t4_ins1 AS 2334 ON INSERT TO public.rtest_t4 2335 WHERE ((new.a >= 10) AND (new.a < 20)) DO INSTEAD INSERT INTO rtest_t5 (a, b) 2336 VALUES (new.a, new.b); 2337rtest_t4|rtest_t4_ins2|CREATE RULE rtest_t4_ins2 AS 2338 ON INSERT TO public.rtest_t4 2339 WHERE ((new.a >= 20) AND (new.a < 30)) DO INSERT INTO rtest_t6 (a, b) 2340 VALUES (new.a, new.b); 2341rtest_t5|rtest_t5_ins|CREATE RULE rtest_t5_ins AS 2342 ON INSERT TO public.rtest_t5 2343 WHERE (new.a > 15) DO INSERT INTO rtest_t7 (a, b) 2344 VALUES (new.a, new.b); 2345rtest_t6|rtest_t6_ins|CREATE RULE rtest_t6_ins AS 2346 ON INSERT TO public.rtest_t6 2347 WHERE (new.a > 25) DO INSTEAD INSERT INTO rtest_t8 (a, b) 2348 VALUES (new.a, new.b); 2349rtest_v1|rtest_v1_del|CREATE RULE rtest_v1_del AS 2350 ON DELETE TO public.rtest_v1 DO INSTEAD DELETE FROM rtest_t1 2351 WHERE (rtest_t1.a = old.a); 2352rtest_v1|rtest_v1_ins|CREATE RULE rtest_v1_ins AS 2353 ON INSERT TO public.rtest_v1 DO INSTEAD INSERT INTO rtest_t1 (a, b) 2354 VALUES (new.a, new.b); 2355rtest_v1|rtest_v1_upd|CREATE RULE rtest_v1_upd AS 2356 ON UPDATE TO public.rtest_v1 DO INSTEAD UPDATE rtest_t1 SET a = new.a, b = new.b 2357 WHERE (rtest_t1.a = old.a); 2358shoelace|shoelace_del|CREATE RULE shoelace_del AS 2359 ON DELETE TO public.shoelace DO INSTEAD DELETE FROM shoelace_data 2360 WHERE (shoelace_data.sl_name = old.sl_name); 2361shoelace|shoelace_ins|CREATE RULE shoelace_ins AS 2362 ON INSERT TO public.shoelace DO INSTEAD INSERT INTO shoelace_data (sl_name, sl_avail, sl_color, sl_len, sl_unit) 2363 VALUES (new.sl_name, new.sl_avail, new.sl_color, new.sl_len, new.sl_unit); 2364shoelace|shoelace_upd|CREATE RULE shoelace_upd AS 2365 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 2366 WHERE (shoelace_data.sl_name = old.sl_name); 2367shoelace_data|log_shoelace|CREATE RULE log_shoelace AS 2368 ON UPDATE TO public.shoelace_data 2369 WHERE (new.sl_avail <> old.sl_avail) DO INSERT INTO shoelace_log (sl_name, sl_avail, log_who, log_when) 2370 VALUES (new.sl_name, new.sl_avail, 'Al Bundy'::name, 'Thu Jan 01 00:00:00 1970'::timestamp without time zone); 2371shoelace_ok|shoelace_ok_ins|CREATE RULE shoelace_ok_ins AS 2372 ON INSERT TO public.shoelace_ok DO INSTEAD UPDATE shoelace SET sl_avail = (shoelace.sl_avail + new.ok_quant) 2373 WHERE (shoelace.sl_name = new.ok_name); 2374-- restore normal output mode 2375\a\t 2376-- 2377-- CREATE OR REPLACE RULE 2378-- 2379CREATE TABLE ruletest_tbl (a int, b int); 2380CREATE TABLE ruletest_tbl2 (a int, b int); 2381CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl 2382 DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (10, 10); 2383INSERT INTO ruletest_tbl VALUES (99, 99); 2384CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl 2385 DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (1000, 1000); 2386INSERT INTO ruletest_tbl VALUES (99, 99); 2387SELECT * FROM ruletest_tbl2; 2388 a | b 2389------+------ 2390 10 | 10 2391 1000 | 1000 2392(2 rows) 2393 2394-- Check that rewrite rules splitting one INSERT into multiple 2395-- conditional statements does not disable FK checking. 2396create table rule_and_refint_t1 ( 2397 id1a integer, 2398 id1b integer, 2399 primary key (id1a, id1b) 2400); 2401create table rule_and_refint_t2 ( 2402 id2a integer, 2403 id2c integer, 2404 primary key (id2a, id2c) 2405); 2406create table rule_and_refint_t3 ( 2407 id3a integer, 2408 id3b integer, 2409 id3c integer, 2410 data text, 2411 primary key (id3a, id3b, id3c), 2412 foreign key (id3a, id3b) references rule_and_refint_t1 (id1a, id1b), 2413 foreign key (id3a, id3c) references rule_and_refint_t2 (id2a, id2c) 2414); 2415insert into rule_and_refint_t1 values (1, 11); 2416insert into rule_and_refint_t1 values (1, 12); 2417insert into rule_and_refint_t1 values (2, 21); 2418insert into rule_and_refint_t1 values (2, 22); 2419insert into rule_and_refint_t2 values (1, 11); 2420insert into rule_and_refint_t2 values (1, 12); 2421insert into rule_and_refint_t2 values (2, 21); 2422insert into rule_and_refint_t2 values (2, 22); 2423insert into rule_and_refint_t3 values (1, 11, 11, 'row1'); 2424insert into rule_and_refint_t3 values (1, 11, 12, 'row2'); 2425insert into rule_and_refint_t3 values (1, 12, 11, 'row3'); 2426insert into rule_and_refint_t3 values (1, 12, 12, 'row4'); 2427insert into rule_and_refint_t3 values (1, 11, 13, 'row5'); 2428ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey1" 2429DETAIL: Key (id3a, id3c)=(1, 13) is not present in table "rule_and_refint_t2". 2430insert into rule_and_refint_t3 values (1, 13, 11, 'row6'); 2431ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey" 2432DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1". 2433-- Ordinary table 2434insert into rule_and_refint_t3 values (1, 13, 11, 'row6') 2435 on conflict do nothing; 2436ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey" 2437DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1". 2438-- rule not fired, so fk violation 2439insert into rule_and_refint_t3 values (1, 13, 11, 'row6') 2440 on conflict (id3a, id3b, id3c) do update 2441 set id3b = excluded.id3b; 2442ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey" 2443DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1". 2444-- rule fired, so unsupported 2445insert into shoelace values ('sl9', 0, 'pink', 35.0, 'inch', 0.0) 2446 on conflict (sl_name) do update 2447 set sl_avail = excluded.sl_avail; 2448ERROR: INSERT with ON CONFLICT clause cannot be used with table that has INSERT or UPDATE rules 2449create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3 2450 where (exists (select 1 from rule_and_refint_t3 2451 where (((rule_and_refint_t3.id3a = new.id3a) 2452 and (rule_and_refint_t3.id3b = new.id3b)) 2453 and (rule_and_refint_t3.id3c = new.id3c)))) 2454 do instead update rule_and_refint_t3 set data = new.data 2455 where (((rule_and_refint_t3.id3a = new.id3a) 2456 and (rule_and_refint_t3.id3b = new.id3b)) 2457 and (rule_and_refint_t3.id3c = new.id3c)); 2458insert into rule_and_refint_t3 values (1, 11, 13, 'row7'); 2459ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey1" 2460DETAIL: Key (id3a, id3c)=(1, 13) is not present in table "rule_and_refint_t2". 2461insert into rule_and_refint_t3 values (1, 13, 11, 'row8'); 2462ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey" 2463DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1". 2464-- 2465-- disallow dropping a view's rule (bug #5072) 2466-- 2467create view fooview as select 'foo'::text; 2468drop rule "_RETURN" on fooview; 2469ERROR: cannot drop rule _RETURN on view fooview because view fooview requires it 2470HINT: You can drop view fooview instead. 2471drop view fooview; 2472-- 2473-- test conversion of table to view (needed to load some pg_dump files) 2474-- 2475create table fooview (x int, y text); 2476select xmin, * from fooview; 2477 xmin | x | y 2478------+---+--- 2479(0 rows) 2480 2481create rule "_RETURN" as on select to fooview do instead 2482 select 1 as x, 'aaa'::text as y; 2483select * from fooview; 2484 x | y 2485---+----- 2486 1 | aaa 2487(1 row) 2488 2489select xmin, * from fooview; -- fail, views don't have such a column 2490ERROR: column "xmin" does not exist 2491LINE 1: select xmin, * from fooview; 2492 ^ 2493select reltoastrelid, relkind, relfrozenxid 2494 from pg_class where oid = 'fooview'::regclass; 2495 reltoastrelid | relkind | relfrozenxid 2496---------------+---------+-------------- 2497 0 | v | 0 2498(1 row) 2499 2500drop view fooview; 2501-- cannot convert an inheritance parent or child to a view, though 2502create table fooview (x int, y text); 2503create table fooview_child () inherits (fooview); 2504create rule "_RETURN" as on select to fooview do instead 2505 select 1 as x, 'aaa'::text as y; 2506ERROR: could not convert table "fooview" to a view because it has child tables 2507create rule "_RETURN" as on select to fooview_child do instead 2508 select 1 as x, 'aaa'::text as y; 2509ERROR: could not convert table "fooview_child" to a view because it has parent tables 2510drop table fooview cascade; 2511NOTICE: drop cascades to table fooview_child 2512-- 2513-- check for planner problems with complex inherited UPDATES 2514-- 2515create table id (id serial primary key, name text); 2516-- currently, must respecify PKEY for each inherited subtable 2517create table test_1 (id integer primary key) inherits (id); 2518NOTICE: merging column "id" with inherited definition 2519create table test_2 (id integer primary key) inherits (id); 2520NOTICE: merging column "id" with inherited definition 2521create table test_3 (id integer primary key) inherits (id); 2522NOTICE: merging column "id" with inherited definition 2523insert into test_1 (name) values ('Test 1'); 2524insert into test_1 (name) values ('Test 2'); 2525insert into test_2 (name) values ('Test 3'); 2526insert into test_2 (name) values ('Test 4'); 2527insert into test_3 (name) values ('Test 5'); 2528insert into test_3 (name) values ('Test 6'); 2529create view id_ordered as select * from id order by id; 2530create rule update_id_ordered as on update to id_ordered 2531 do instead update id set name = new.name where id = old.id; 2532select * from id_ordered; 2533 id | name 2534----+-------- 2535 1 | Test 1 2536 2 | Test 2 2537 3 | Test 3 2538 4 | Test 4 2539 5 | Test 5 2540 6 | Test 6 2541(6 rows) 2542 2543update id_ordered set name = 'update 2' where id = 2; 2544update id_ordered set name = 'update 4' where id = 4; 2545update id_ordered set name = 'update 5' where id = 5; 2546select * from id_ordered; 2547 id | name 2548----+---------- 2549 1 | Test 1 2550 2 | update 2 2551 3 | Test 3 2552 4 | update 4 2553 5 | update 5 2554 6 | Test 6 2555(6 rows) 2556 2557set client_min_messages to warning; -- suppress cascade notices 2558drop table id cascade; 2559reset client_min_messages; 2560-- 2561-- check corner case where an entirely-dummy subplan is created by 2562-- constraint exclusion 2563-- 2564create temp table t1 (a integer primary key); 2565create temp table t1_1 (check (a >= 0 and a < 10)) inherits (t1); 2566create temp table t1_2 (check (a >= 10 and a < 20)) inherits (t1); 2567create rule t1_ins_1 as on insert to t1 2568 where new.a >= 0 and new.a < 10 2569 do instead 2570 insert into t1_1 values (new.a); 2571create rule t1_ins_2 as on insert to t1 2572 where new.a >= 10 and new.a < 20 2573 do instead 2574 insert into t1_2 values (new.a); 2575create rule t1_upd_1 as on update to t1 2576 where old.a >= 0 and old.a < 10 2577 do instead 2578 update t1_1 set a = new.a where a = old.a; 2579create rule t1_upd_2 as on update to t1 2580 where old.a >= 10 and old.a < 20 2581 do instead 2582 update t1_2 set a = new.a where a = old.a; 2583set constraint_exclusion = on; 2584insert into t1 select * from generate_series(5,19,1) g; 2585update t1 set a = 4 where a = 5; 2586select * from only t1; 2587 a 2588--- 2589(0 rows) 2590 2591select * from only t1_1; 2592 a 2593--- 2594 6 2595 7 2596 8 2597 9 2598 4 2599(5 rows) 2600 2601select * from only t1_2; 2602 a 2603---- 2604 10 2605 11 2606 12 2607 13 2608 14 2609 15 2610 16 2611 17 2612 18 2613 19 2614(10 rows) 2615 2616reset constraint_exclusion; 2617-- test FOR UPDATE in rules 2618create table rules_base(f1 int, f2 int); 2619insert into rules_base values(1,2), (11,12); 2620create rule r1 as on update to rules_base do instead 2621 select * from rules_base where f1 = 1 for update; 2622update rules_base set f2 = f2 + 1; 2623 f1 | f2 2624----+---- 2625 1 | 2 2626(1 row) 2627 2628create or replace rule r1 as on update to rules_base do instead 2629 select * from rules_base where f1 = 11 for update of rules_base; 2630update rules_base set f2 = f2 + 1; 2631 f1 | f2 2632----+---- 2633 11 | 12 2634(1 row) 2635 2636create or replace rule r1 as on update to rules_base do instead 2637 select * from rules_base where f1 = 11 for update of old; -- error 2638ERROR: relation "old" in FOR UPDATE clause not found in FROM clause 2639LINE 2: select * from rules_base where f1 = 11 for update of old; 2640 ^ 2641drop table rules_base; 2642-- test various flavors of pg_get_viewdef() 2643select pg_get_viewdef('shoe'::regclass) as unpretty; 2644 unpretty 2645------------------------------------------------ 2646 SELECT sh.shoename, + 2647 sh.sh_avail, + 2648 sh.slcolor, + 2649 sh.slminlen, + 2650 (sh.slminlen * un.un_fact) AS slminlen_cm,+ 2651 sh.slmaxlen, + 2652 (sh.slmaxlen * un.un_fact) AS slmaxlen_cm,+ 2653 sh.slunit + 2654 FROM shoe_data sh, + 2655 unit un + 2656 WHERE (sh.slunit = un.un_name); 2657(1 row) 2658 2659select pg_get_viewdef('shoe'::regclass,true) as pretty; 2660 pretty 2661---------------------------------------------- 2662 SELECT sh.shoename, + 2663 sh.sh_avail, + 2664 sh.slcolor, + 2665 sh.slminlen, + 2666 sh.slminlen * un.un_fact AS slminlen_cm,+ 2667 sh.slmaxlen, + 2668 sh.slmaxlen * un.un_fact AS slmaxlen_cm,+ 2669 sh.slunit + 2670 FROM shoe_data sh, + 2671 unit un + 2672 WHERE sh.slunit = un.un_name; 2673(1 row) 2674 2675select pg_get_viewdef('shoe'::regclass,0) as prettier; 2676 prettier 2677---------------------------------------------- 2678 SELECT sh.shoename, + 2679 sh.sh_avail, + 2680 sh.slcolor, + 2681 sh.slminlen, + 2682 sh.slminlen * un.un_fact AS slminlen_cm,+ 2683 sh.slmaxlen, + 2684 sh.slmaxlen * un.un_fact AS slmaxlen_cm,+ 2685 sh.slunit + 2686 FROM shoe_data sh, + 2687 unit un + 2688 WHERE sh.slunit = un.un_name; 2689(1 row) 2690 2691-- 2692-- check multi-row VALUES in rules 2693-- 2694create table rules_src(f1 int, f2 int); 2695create table rules_log(f1 int, f2 int, tag text); 2696insert into rules_src values(1,2), (11,12); 2697create rule r1 as on update to rules_src do also 2698 insert into rules_log values(old.*, 'old'), (new.*, 'new'); 2699update rules_src set f2 = f2 + 1; 2700update rules_src set f2 = f2 * 10; 2701select * from rules_src; 2702 f1 | f2 2703----+----- 2704 1 | 30 2705 11 | 130 2706(2 rows) 2707 2708select * from rules_log; 2709 f1 | f2 | tag 2710----+-----+----- 2711 1 | 2 | old 2712 1 | 3 | new 2713 11 | 12 | old 2714 11 | 13 | new 2715 1 | 3 | old 2716 1 | 30 | new 2717 11 | 13 | old 2718 11 | 130 | new 2719(8 rows) 2720 2721create rule r2 as on update to rules_src do also 2722 values(old.*, 'old'), (new.*, 'new'); 2723update rules_src set f2 = f2 / 10; 2724 column1 | column2 | column3 2725---------+---------+--------- 2726 1 | 30 | old 2727 1 | 3 | new 2728 11 | 130 | old 2729 11 | 13 | new 2730(4 rows) 2731 2732select * from rules_src; 2733 f1 | f2 2734----+---- 2735 1 | 3 2736 11 | 13 2737(2 rows) 2738 2739select * from rules_log; 2740 f1 | f2 | tag 2741----+-----+----- 2742 1 | 2 | old 2743 1 | 3 | new 2744 11 | 12 | old 2745 11 | 13 | new 2746 1 | 3 | old 2747 1 | 30 | new 2748 11 | 13 | old 2749 11 | 130 | new 2750 1 | 30 | old 2751 1 | 3 | new 2752 11 | 130 | old 2753 11 | 13 | new 2754(12 rows) 2755 2756create rule r3 as on delete to rules_src do notify rules_src_deletion; 2757\d+ rules_src 2758 Table "public.rules_src" 2759 Column | Type | Modifiers | Storage | Stats target | Description 2760--------+---------+-----------+---------+--------------+------------- 2761 f1 | integer | | plain | | 2762 f2 | integer | | plain | | 2763Rules: 2764 r1 AS 2765 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) 2766 r2 AS 2767 ON UPDATE TO rules_src DO VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text) 2768 r3 AS 2769 ON DELETE TO rules_src DO 2770 NOTIFY rules_src_deletion 2771 2772-- 2773-- Ensure an aliased target relation for insert is correctly deparsed. 2774-- 2775create rule r4 as on insert to rules_src do instead insert into rules_log AS trgt SELECT NEW.* RETURNING trgt.f1, trgt.f2; 2776create rule r5 as on update to rules_src do instead UPDATE rules_log AS trgt SET tag = 'updated' WHERE trgt.f1 = new.f1; 2777\d+ rules_src 2778 Table "public.rules_src" 2779 Column | Type | Modifiers | Storage | Stats target | Description 2780--------+---------+-----------+---------+--------------+------------- 2781 f1 | integer | | plain | | 2782 f2 | integer | | plain | | 2783Rules: 2784 r1 AS 2785 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) 2786 r2 AS 2787 ON UPDATE TO rules_src DO VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text) 2788 r3 AS 2789 ON DELETE TO rules_src DO 2790 NOTIFY rules_src_deletion 2791 r4 AS 2792 ON INSERT TO rules_src DO INSTEAD INSERT INTO rules_log AS trgt (f1, f2) SELECT new.f1, 2793 new.f2 2794 RETURNING trgt.f1, 2795 trgt.f2 2796 r5 AS 2797 ON UPDATE TO rules_src DO INSTEAD UPDATE rules_log trgt SET tag = 'updated'::text 2798 WHERE trgt.f1 = new.f1 2799 2800-- 2801-- check alter rename rule 2802-- 2803CREATE TABLE rule_t1 (a INT); 2804CREATE VIEW rule_v1 AS SELECT * FROM rule_t1; 2805CREATE RULE InsertRule AS 2806 ON INSERT TO rule_v1 2807 DO INSTEAD 2808 INSERT INTO rule_t1 VALUES(new.a); 2809ALTER RULE InsertRule ON rule_v1 RENAME to NewInsertRule; 2810INSERT INTO rule_v1 VALUES(1); 2811SELECT * FROM rule_v1; 2812 a 2813--- 2814 1 2815(1 row) 2816 2817\d+ rule_v1 2818 View "public.rule_v1" 2819 Column | Type | Modifiers | Storage | Description 2820--------+---------+-----------+---------+------------- 2821 a | integer | | plain | 2822View definition: 2823 SELECT rule_t1.a 2824 FROM rule_t1; 2825Rules: 2826 newinsertrule AS 2827 ON INSERT TO rule_v1 DO INSTEAD INSERT INTO rule_t1 (a) 2828 VALUES (new.a) 2829 2830-- 2831-- error conditions for alter rename rule 2832-- 2833ALTER RULE InsertRule ON rule_v1 RENAME TO NewInsertRule; -- doesn't exist 2834ERROR: rule "insertrule" for relation "rule_v1" does not exist 2835ALTER RULE NewInsertRule ON rule_v1 RENAME TO "_RETURN"; -- already exists 2836ERROR: rule "_RETURN" for relation "rule_v1" already exists 2837ALTER RULE "_RETURN" ON rule_v1 RENAME TO abc; -- ON SELECT rule cannot be renamed 2838ERROR: renaming an ON SELECT rule is not allowed 2839DROP VIEW rule_v1; 2840DROP TABLE rule_t1; 2841-- 2842-- check display of VALUES in view definitions 2843-- 2844create view rule_v1 as values(1,2); 2845\d+ rule_v1 2846 View "public.rule_v1" 2847 Column | Type | Modifiers | Storage | Description 2848---------+---------+-----------+---------+------------- 2849 column1 | integer | | plain | 2850 column2 | integer | | plain | 2851View definition: 2852 VALUES (1,2); 2853 2854alter table rule_v1 rename column column2 to q2; 2855\d+ rule_v1 2856 View "public.rule_v1" 2857 Column | Type | Modifiers | Storage | Description 2858---------+---------+-----------+---------+------------- 2859 column1 | integer | | plain | 2860 q2 | integer | | plain | 2861View definition: 2862 SELECT "*VALUES*".column1, 2863 "*VALUES*".column2 AS q2 2864 FROM (VALUES (1,2)) "*VALUES*"; 2865 2866drop view rule_v1; 2867create view rule_v1(x) as values(1,2); 2868\d+ rule_v1 2869 View "public.rule_v1" 2870 Column | Type | Modifiers | Storage | Description 2871---------+---------+-----------+---------+------------- 2872 x | integer | | plain | 2873 column2 | integer | | plain | 2874View definition: 2875 SELECT "*VALUES*".column1 AS x, 2876 "*VALUES*".column2 2877 FROM (VALUES (1,2)) "*VALUES*"; 2878 2879drop view rule_v1; 2880create view rule_v1(x) as select * from (values(1,2)) v; 2881\d+ rule_v1 2882 View "public.rule_v1" 2883 Column | Type | Modifiers | Storage | Description 2884---------+---------+-----------+---------+------------- 2885 x | integer | | plain | 2886 column2 | integer | | plain | 2887View definition: 2888 SELECT v.column1 AS x, 2889 v.column2 2890 FROM ( VALUES (1,2)) v; 2891 2892drop view rule_v1; 2893create view rule_v1(x) as select * from (values(1,2)) v(q,w); 2894\d+ rule_v1 2895 View "public.rule_v1" 2896 Column | Type | Modifiers | Storage | Description 2897--------+---------+-----------+---------+------------- 2898 x | integer | | plain | 2899 w | integer | | plain | 2900View definition: 2901 SELECT v.q AS x, 2902 v.w 2903 FROM ( VALUES (1,2)) v(q, w); 2904 2905drop view rule_v1; 2906-- 2907-- Check DO INSTEAD rules with ON CONFLICT 2908-- 2909CREATE TABLE hats ( 2910 hat_name char(10) primary key, 2911 hat_color char(10) -- hat color 2912); 2913CREATE TABLE hat_data ( 2914 hat_name char(10), 2915 hat_color char(10) -- hat color 2916); 2917create unique index hat_data_unique_idx 2918 on hat_data (hat_name COLLATE "C" bpchar_pattern_ops); 2919-- DO NOTHING with ON CONFLICT 2920CREATE RULE hat_nosert AS ON INSERT TO hats 2921 DO INSTEAD 2922 INSERT INTO hat_data VALUES ( 2923 NEW.hat_name, 2924 NEW.hat_color) 2925 ON CONFLICT (hat_name COLLATE "C" bpchar_pattern_ops) WHERE hat_color = 'green' 2926 DO NOTHING 2927 RETURNING *; 2928SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename; 2929 definition 2930--------------------------------------------------------------------------------------------- 2931 CREATE RULE hat_nosert AS + 2932 ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) + 2933 VALUES (new.hat_name, new.hat_color) ON CONFLICT(hat_name COLLATE "C" bpchar_pattern_ops)+ 2934 WHERE (hat_color = 'green'::bpchar) DO NOTHING + 2935 RETURNING hat_data.hat_name, + 2936 hat_data.hat_color; 2937(1 row) 2938 2939-- Works (projects row) 2940INSERT INTO hats VALUES ('h7', 'black') RETURNING *; 2941 hat_name | hat_color 2942------------+------------ 2943 h7 | black 2944(1 row) 2945 2946-- Works (does nothing) 2947INSERT INTO hats VALUES ('h7', 'black') RETURNING *; 2948 hat_name | hat_color 2949----------+----------- 2950(0 rows) 2951 2952SELECT tablename, rulename, definition FROM pg_rules 2953 WHERE tablename = 'hats'; 2954 tablename | rulename | definition 2955-----------+------------+--------------------------------------------------------------------------------------------- 2956 hats | hat_nosert | CREATE RULE hat_nosert AS + 2957 | | ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) + 2958 | | VALUES (new.hat_name, new.hat_color) ON CONFLICT(hat_name COLLATE "C" bpchar_pattern_ops)+ 2959 | | WHERE (hat_color = 'green'::bpchar) DO NOTHING + 2960 | | RETURNING hat_data.hat_name, + 2961 | | hat_data.hat_color; 2962(1 row) 2963 2964DROP RULE hat_nosert ON hats; 2965-- DO NOTHING without ON CONFLICT 2966CREATE RULE hat_nosert_all AS ON INSERT TO hats 2967 DO INSTEAD 2968 INSERT INTO hat_data VALUES ( 2969 NEW.hat_name, 2970 NEW.hat_color) 2971 ON CONFLICT 2972 DO NOTHING 2973 RETURNING *; 2974SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename; 2975 definition 2976------------------------------------------------------------------------------------- 2977 CREATE RULE hat_nosert_all AS + 2978 ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color)+ 2979 VALUES (new.hat_name, new.hat_color) ON CONFLICT DO NOTHING + 2980 RETURNING hat_data.hat_name, + 2981 hat_data.hat_color; 2982(1 row) 2983 2984DROP RULE hat_nosert_all ON hats; 2985-- Works (does nothing) 2986INSERT INTO hats VALUES ('h7', 'black') RETURNING *; 2987 hat_name | hat_color 2988------------+------------ 2989 h7 | black 2990(1 row) 2991 2992-- DO UPDATE with a WHERE clause 2993CREATE RULE hat_upsert AS ON INSERT TO hats 2994 DO INSTEAD 2995 INSERT INTO hat_data VALUES ( 2996 NEW.hat_name, 2997 NEW.hat_color) 2998 ON CONFLICT (hat_name) 2999 DO UPDATE 3000 SET hat_name = hat_data.hat_name, hat_color = excluded.hat_color 3001 WHERE excluded.hat_color <> 'forbidden' AND hat_data.* != excluded.* 3002 RETURNING *; 3003SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename; 3004 definition 3005----------------------------------------------------------------------------------------------------------------------------------------- 3006 CREATE RULE hat_upsert AS + 3007 ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) + 3008 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+ 3009 WHERE ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*)) + 3010 RETURNING hat_data.hat_name, + 3011 hat_data.hat_color; 3012(1 row) 3013 3014-- Works (does upsert) 3015INSERT INTO hats VALUES ('h8', 'black') RETURNING *; 3016 hat_name | hat_color 3017------------+------------ 3018 h8 | black 3019(1 row) 3020 3021SELECT * FROM hat_data WHERE hat_name = 'h8'; 3022 hat_name | hat_color 3023------------+------------ 3024 h8 | black 3025(1 row) 3026 3027INSERT INTO hats VALUES ('h8', 'white') RETURNING *; 3028 hat_name | hat_color 3029------------+------------ 3030 h8 | white 3031(1 row) 3032 3033SELECT * FROM hat_data WHERE hat_name = 'h8'; 3034 hat_name | hat_color 3035------------+------------ 3036 h8 | white 3037(1 row) 3038 3039INSERT INTO hats VALUES ('h8', 'forbidden') RETURNING *; 3040 hat_name | hat_color 3041----------+----------- 3042(0 rows) 3043 3044SELECT * FROM hat_data WHERE hat_name = 'h8'; 3045 hat_name | hat_color 3046------------+------------ 3047 h8 | white 3048(1 row) 3049 3050SELECT tablename, rulename, definition FROM pg_rules 3051 WHERE tablename = 'hats'; 3052 tablename | rulename | definition 3053-----------+------------+----------------------------------------------------------------------------------------------------------------------------------------- 3054 hats | hat_upsert | CREATE RULE hat_upsert AS + 3055 | | ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) + 3056 | | 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+ 3057 | | WHERE ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*)) + 3058 | | RETURNING hat_data.hat_name, + 3059 | | hat_data.hat_color; 3060(1 row) 3061 3062-- ensure explain works for on insert conflict rules 3063explain (costs off) INSERT INTO hats VALUES ('h8', 'forbidden') RETURNING *; 3064 QUERY PLAN 3065------------------------------------------------------------------------------------------------- 3066 Insert on hat_data 3067 Conflict Resolution: UPDATE 3068 Conflict Arbiter Indexes: hat_data_unique_idx 3069 Conflict Filter: ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*)) 3070 -> Result 3071(5 rows) 3072 3073-- ensure upserting into a rule, with a CTE (different offsets!) works 3074WITH data(hat_name, hat_color) AS ( 3075 VALUES ('h8', 'green'), 3076 ('h9', 'blue'), 3077 ('h7', 'forbidden') 3078) 3079INSERT INTO hats 3080 SELECT * FROM data 3081RETURNING *; 3082 hat_name | hat_color 3083------------+------------ 3084 h8 | green 3085 h9 | blue 3086(2 rows) 3087 3088EXPLAIN (costs off) WITH data(hat_name, hat_color) AS ( 3089 VALUES ('h8', 'green'), 3090 ('h9', 'blue'), 3091 ('h7', 'forbidden') 3092) 3093INSERT INTO hats 3094 SELECT * FROM data 3095RETURNING *; 3096 QUERY PLAN 3097------------------------------------------------------------------------------------------------- 3098 Insert on hat_data 3099 Conflict Resolution: UPDATE 3100 Conflict Arbiter Indexes: hat_data_unique_idx 3101 Conflict Filter: ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*)) 3102 CTE data 3103 -> Values Scan on "*VALUES*" 3104 -> CTE Scan on data 3105(7 rows) 3106 3107SELECT * FROM hat_data WHERE hat_name IN ('h8', 'h9', 'h7') ORDER BY hat_name; 3108 hat_name | hat_color 3109------------+------------ 3110 h7 | black 3111 h8 | green 3112 h9 | blue 3113(3 rows) 3114 3115DROP RULE hat_upsert ON hats; 3116drop table hats; 3117drop table hat_data; 3118-- test for pg_get_functiondef properly regurgitating SET parameters 3119-- Note that the function is kept around to stress pg_dump. 3120CREATE FUNCTION func_with_set_params() RETURNS integer 3121 AS 'select 1;' 3122 LANGUAGE SQL 3123 SET search_path TO PG_CATALOG 3124 SET extra_float_digits TO 2 3125 SET work_mem TO '4MB' 3126 SET datestyle to iso, mdy 3127 SET local_preload_libraries TO "Mixed/Case", 'c:/''a"/path', '', '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789' 3128 IMMUTABLE STRICT; 3129SELECT pg_get_functiondef('func_with_set_params()'::regprocedure); 3130 pg_get_functiondef 3131-------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 3132 CREATE OR REPLACE FUNCTION public.func_with_set_params() + 3133 RETURNS integer + 3134 LANGUAGE sql + 3135 IMMUTABLE STRICT + 3136 SET search_path TO 'pg_catalog' + 3137 SET extra_float_digits TO '2' + 3138 SET work_mem TO '4MB' + 3139 SET "DateStyle" TO 'iso, mdy' + 3140 SET local_preload_libraries TO 'Mixed/Case', 'c:/''a"/path', '', '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789'+ 3141 AS $function$select 1;$function$ + 3142 3143(1 row) 3144 3145-- tests for pg_get_*def with invalid objects 3146SELECT pg_get_constraintdef(0); 3147 pg_get_constraintdef 3148---------------------- 3149 3150(1 row) 3151 3152SELECT pg_get_functiondef(0); 3153 pg_get_functiondef 3154-------------------- 3155 3156(1 row) 3157 3158SELECT pg_get_indexdef(0); 3159 pg_get_indexdef 3160----------------- 3161 3162(1 row) 3163 3164SELECT pg_get_ruledef(0); 3165 pg_get_ruledef 3166---------------- 3167 3168(1 row) 3169 3170SELECT pg_get_triggerdef(0); 3171 pg_get_triggerdef 3172------------------- 3173 3174(1 row) 3175 3176SELECT pg_get_viewdef(0); 3177 pg_get_viewdef 3178---------------- 3179 3180(1 row) 3181 3182SELECT pg_get_function_arguments(0); 3183 pg_get_function_arguments 3184--------------------------- 3185 3186(1 row) 3187 3188SELECT pg_get_function_identity_arguments(0); 3189 pg_get_function_identity_arguments 3190------------------------------------ 3191 3192(1 row) 3193 3194SELECT pg_get_function_result(0); 3195 pg_get_function_result 3196------------------------ 3197 3198(1 row) 3199 3200SELECT pg_get_function_arg_default(0, 0); 3201 pg_get_function_arg_default 3202----------------------------- 3203 3204(1 row) 3205 3206SELECT pg_get_function_arg_default('pg_class'::regclass, 0); 3207 pg_get_function_arg_default 3208----------------------------- 3209 3210(1 row) 3211 3212