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 rules_foo (f1 int); 1179create table rules_foo2 (f1 int); 1180create rule rules_foorule as on insert to rules_foo where f1 < 100 1181do instead nothing; 1182insert into rules_foo values(1); 1183insert into rules_foo values(1001); 1184select * from rules_foo; 1185 f1 1186------ 1187 1001 1188(1 row) 1189 1190drop rule rules_foorule on rules_foo; 1191-- this should fail because f1 is not exposed for unqualified reference: 1192create rule rules_foorule as on insert to rules_foo where f1 < 100 1193do instead insert into rules_foo2 values (f1); 1194ERROR: column "f1" does not exist 1195LINE 2: do instead insert into rules_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 rules_foorule as on insert to rules_foo where f1 < 100 1200do instead insert into rules_foo2 values (new.f1); 1201insert into rules_foo values(2); 1202insert into rules_foo values(100); 1203select * from rules_foo; 1204 f1 1205------ 1206 1001 1207 100 1208(2 rows) 1209 1210select * from rules_foo2; 1211 f1 1212---- 1213 2 1214(1 row) 1215 1216drop rule rules_foorule on rules_foo; 1217drop table rules_foo; 1218drop table rules_foo2; 1219-- 1220-- Test rules containing INSERT ... SELECT, which is a very ugly special 1221-- case as of 7.1. Example is based on bug report from Joel Burton. 1222-- 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_hba_file_rules| SELECT a.line_number, 1350 a.type, 1351 a.database, 1352 a.user_name, 1353 a.address, 1354 a.netmask, 1355 a.auth_method, 1356 a.options, 1357 a.error 1358 FROM pg_hba_file_rules() a(line_number, type, database, user_name, address, netmask, auth_method, options, error); 1359pg_indexes| SELECT n.nspname AS schemaname, 1360 c.relname AS tablename, 1361 i.relname AS indexname, 1362 t.spcname AS tablespace, 1363 pg_get_indexdef(i.oid) AS indexdef 1364 FROM ((((pg_index x 1365 JOIN pg_class c ON ((c.oid = x.indrelid))) 1366 JOIN pg_class i ON ((i.oid = x.indexrelid))) 1367 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 1368 LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) 1369 WHERE ((c.relkind = ANY (ARRAY['r'::"char", 'm'::"char"])) AND (i.relkind = 'i'::"char")); 1370pg_locks| SELECT l.locktype, 1371 l.database, 1372 l.relation, 1373 l.page, 1374 l.tuple, 1375 l.virtualxid, 1376 l.transactionid, 1377 l.classid, 1378 l.objid, 1379 l.objsubid, 1380 l.virtualtransaction, 1381 l.pid, 1382 l.mode, 1383 l.granted, 1384 l.fastpath 1385 FROM pg_lock_status() l(locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted, fastpath); 1386pg_matviews| SELECT n.nspname AS schemaname, 1387 c.relname AS matviewname, 1388 pg_get_userbyid(c.relowner) AS matviewowner, 1389 t.spcname AS tablespace, 1390 c.relhasindex AS hasindexes, 1391 c.relispopulated AS ispopulated, 1392 pg_get_viewdef(c.oid) AS definition 1393 FROM ((pg_class c 1394 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 1395 LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) 1396 WHERE (c.relkind = 'm'::"char"); 1397pg_policies| SELECT n.nspname AS schemaname, 1398 c.relname AS tablename, 1399 pol.polname AS policyname, 1400 CASE 1401 WHEN pol.polpermissive THEN 'PERMISSIVE'::text 1402 ELSE 'RESTRICTIVE'::text 1403 END AS permissive, 1404 CASE 1405 WHEN (pol.polroles = '{0}'::oid[]) THEN (string_to_array('public'::text, ''::text))::name[] 1406 ELSE ARRAY( SELECT pg_authid.rolname 1407 FROM pg_authid 1408 WHERE (pg_authid.oid = ANY (pol.polroles)) 1409 ORDER BY pg_authid.rolname) 1410 END AS roles, 1411 CASE pol.polcmd 1412 WHEN 'r'::"char" THEN 'SELECT'::text 1413 WHEN 'a'::"char" THEN 'INSERT'::text 1414 WHEN 'w'::"char" THEN 'UPDATE'::text 1415 WHEN 'd'::"char" THEN 'DELETE'::text 1416 WHEN '*'::"char" THEN 'ALL'::text 1417 ELSE NULL::text 1418 END AS cmd, 1419 pg_get_expr(pol.polqual, pol.polrelid) AS qual, 1420 pg_get_expr(pol.polwithcheck, pol.polrelid) AS with_check 1421 FROM ((pg_policy pol 1422 JOIN pg_class c ON ((c.oid = pol.polrelid))) 1423 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))); 1424pg_prepared_statements| SELECT p.name, 1425 p.statement, 1426 p.prepare_time, 1427 p.parameter_types, 1428 p.from_sql 1429 FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql); 1430pg_prepared_xacts| SELECT p.transaction, 1431 p.gid, 1432 p.prepared, 1433 u.rolname AS owner, 1434 d.datname AS database 1435 FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid) 1436 LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) 1437 LEFT JOIN pg_database d ON ((p.dbid = d.oid))); 1438pg_publication_tables| SELECT p.pubname, 1439 n.nspname AS schemaname, 1440 c.relname AS tablename 1441 FROM pg_publication p, 1442 (pg_class c 1443 JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 1444 WHERE (c.oid IN ( SELECT pg_get_publication_tables.relid 1445 FROM pg_get_publication_tables((p.pubname)::text) pg_get_publication_tables(relid))); 1446pg_replication_origin_status| SELECT pg_show_replication_origin_status.local_id, 1447 pg_show_replication_origin_status.external_id, 1448 pg_show_replication_origin_status.remote_lsn, 1449 pg_show_replication_origin_status.local_lsn 1450 FROM pg_show_replication_origin_status() pg_show_replication_origin_status(local_id, external_id, remote_lsn, local_lsn); 1451pg_replication_slots| SELECT l.slot_name, 1452 l.plugin, 1453 l.slot_type, 1454 l.datoid, 1455 d.datname AS database, 1456 l.temporary, 1457 l.active, 1458 l.active_pid, 1459 l.xmin, 1460 l.catalog_xmin, 1461 l.restart_lsn, 1462 l.confirmed_flush_lsn 1463 FROM (pg_get_replication_slots() l(slot_name, plugin, slot_type, datoid, temporary, active, active_pid, xmin, catalog_xmin, restart_lsn, confirmed_flush_lsn) 1464 LEFT JOIN pg_database d ON ((l.datoid = d.oid))); 1465pg_roles| SELECT pg_authid.rolname, 1466 pg_authid.rolsuper, 1467 pg_authid.rolinherit, 1468 pg_authid.rolcreaterole, 1469 pg_authid.rolcreatedb, 1470 pg_authid.rolcanlogin, 1471 pg_authid.rolreplication, 1472 pg_authid.rolconnlimit, 1473 '********'::text AS rolpassword, 1474 pg_authid.rolvaliduntil, 1475 pg_authid.rolbypassrls, 1476 s.setconfig AS rolconfig, 1477 pg_authid.oid 1478 FROM (pg_authid 1479 LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))); 1480pg_rules| SELECT n.nspname AS schemaname, 1481 c.relname AS tablename, 1482 r.rulename, 1483 pg_get_ruledef(r.oid) AS definition 1484 FROM ((pg_rewrite r 1485 JOIN pg_class c ON ((c.oid = r.ev_class))) 1486 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 1487 WHERE (r.rulename <> '_RETURN'::name); 1488pg_seclabels| SELECT l.objoid, 1489 l.classoid, 1490 l.objsubid, 1491 CASE 1492 WHEN (rel.relkind = ANY (ARRAY['r'::"char", 'p'::"char"])) THEN 'table'::text 1493 WHEN (rel.relkind = 'v'::"char") THEN 'view'::text 1494 WHEN (rel.relkind = 'm'::"char") THEN 'materialized view'::text 1495 WHEN (rel.relkind = 'S'::"char") THEN 'sequence'::text 1496 WHEN (rel.relkind = 'f'::"char") THEN 'foreign table'::text 1497 ELSE NULL::text 1498 END AS objtype, 1499 rel.relnamespace AS objnamespace, 1500 CASE 1501 WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) 1502 ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) 1503 END AS objname, 1504 l.provider, 1505 l.label 1506 FROM ((pg_seclabel l 1507 JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid)))) 1508 JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) 1509 WHERE (l.objsubid = 0) 1510UNION ALL 1511 SELECT l.objoid, 1512 l.classoid, 1513 l.objsubid, 1514 'column'::text AS objtype, 1515 rel.relnamespace AS objnamespace, 1516 (( 1517 CASE 1518 WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) 1519 ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) 1520 END || '.'::text) || (att.attname)::text) AS objname, 1521 l.provider, 1522 l.label 1523 FROM (((pg_seclabel l 1524 JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid)))) 1525 JOIN pg_attribute att ON (((rel.oid = att.attrelid) AND (l.objsubid = att.attnum)))) 1526 JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) 1527 WHERE (l.objsubid <> 0) 1528UNION ALL 1529 SELECT l.objoid, 1530 l.classoid, 1531 l.objsubid, 1532 CASE pro.prokind 1533 WHEN 'a'::"char" THEN 'aggregate'::text 1534 WHEN 'f'::"char" THEN 'function'::text 1535 WHEN 'p'::"char" THEN 'procedure'::text 1536 WHEN 'w'::"char" THEN 'window'::text 1537 ELSE NULL::text 1538 END AS objtype, 1539 pro.pronamespace AS objnamespace, 1540 ((( 1541 CASE 1542 WHEN pg_function_is_visible(pro.oid) THEN quote_ident((pro.proname)::text) 1543 ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((pro.proname)::text)) 1544 END || '('::text) || pg_get_function_arguments(pro.oid)) || ')'::text) AS objname, 1545 l.provider, 1546 l.label 1547 FROM ((pg_seclabel l 1548 JOIN pg_proc pro ON (((l.classoid = pro.tableoid) AND (l.objoid = pro.oid)))) 1549 JOIN pg_namespace nsp ON ((pro.pronamespace = nsp.oid))) 1550 WHERE (l.objsubid = 0) 1551UNION ALL 1552 SELECT l.objoid, 1553 l.classoid, 1554 l.objsubid, 1555 CASE 1556 WHEN (typ.typtype = 'd'::"char") THEN 'domain'::text 1557 ELSE 'type'::text 1558 END AS objtype, 1559 typ.typnamespace AS objnamespace, 1560 CASE 1561 WHEN pg_type_is_visible(typ.oid) THEN quote_ident((typ.typname)::text) 1562 ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((typ.typname)::text)) 1563 END AS objname, 1564 l.provider, 1565 l.label 1566 FROM ((pg_seclabel l 1567 JOIN pg_type typ ON (((l.classoid = typ.tableoid) AND (l.objoid = typ.oid)))) 1568 JOIN pg_namespace nsp ON ((typ.typnamespace = nsp.oid))) 1569 WHERE (l.objsubid = 0) 1570UNION ALL 1571 SELECT l.objoid, 1572 l.classoid, 1573 l.objsubid, 1574 'large object'::text AS objtype, 1575 NULL::oid AS objnamespace, 1576 (l.objoid)::text AS objname, 1577 l.provider, 1578 l.label 1579 FROM (pg_seclabel l 1580 JOIN pg_largeobject_metadata lom ON ((l.objoid = lom.oid))) 1581 WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND (l.objsubid = 0)) 1582UNION ALL 1583 SELECT l.objoid, 1584 l.classoid, 1585 l.objsubid, 1586 'language'::text AS objtype, 1587 NULL::oid AS objnamespace, 1588 quote_ident((lan.lanname)::text) AS objname, 1589 l.provider, 1590 l.label 1591 FROM (pg_seclabel l 1592 JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND (l.objoid = lan.oid)))) 1593 WHERE (l.objsubid = 0) 1594UNION ALL 1595 SELECT l.objoid, 1596 l.classoid, 1597 l.objsubid, 1598 'schema'::text AS objtype, 1599 nsp.oid AS objnamespace, 1600 quote_ident((nsp.nspname)::text) AS objname, 1601 l.provider, 1602 l.label 1603 FROM (pg_seclabel l 1604 JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid = nsp.oid)))) 1605 WHERE (l.objsubid = 0) 1606UNION ALL 1607 SELECT l.objoid, 1608 l.classoid, 1609 l.objsubid, 1610 'event trigger'::text AS objtype, 1611 NULL::oid AS objnamespace, 1612 quote_ident((evt.evtname)::text) AS objname, 1613 l.provider, 1614 l.label 1615 FROM (pg_seclabel l 1616 JOIN pg_event_trigger evt ON (((l.classoid = evt.tableoid) AND (l.objoid = evt.oid)))) 1617 WHERE (l.objsubid = 0) 1618UNION ALL 1619 SELECT l.objoid, 1620 l.classoid, 1621 l.objsubid, 1622 'publication'::text AS objtype, 1623 NULL::oid AS objnamespace, 1624 quote_ident((p.pubname)::text) AS objname, 1625 l.provider, 1626 l.label 1627 FROM (pg_seclabel l 1628 JOIN pg_publication p ON (((l.classoid = p.tableoid) AND (l.objoid = p.oid)))) 1629 WHERE (l.objsubid = 0) 1630UNION ALL 1631 SELECT l.objoid, 1632 l.classoid, 1633 0 AS objsubid, 1634 'subscription'::text AS objtype, 1635 NULL::oid AS objnamespace, 1636 quote_ident((s.subname)::text) AS objname, 1637 l.provider, 1638 l.label 1639 FROM (pg_shseclabel l 1640 JOIN pg_subscription s ON (((l.classoid = s.tableoid) AND (l.objoid = s.oid)))) 1641UNION ALL 1642 SELECT l.objoid, 1643 l.classoid, 1644 0 AS objsubid, 1645 'database'::text AS objtype, 1646 NULL::oid AS objnamespace, 1647 quote_ident((dat.datname)::text) AS objname, 1648 l.provider, 1649 l.label 1650 FROM (pg_shseclabel l 1651 JOIN pg_database dat ON (((l.classoid = dat.tableoid) AND (l.objoid = dat.oid)))) 1652UNION ALL 1653 SELECT l.objoid, 1654 l.classoid, 1655 0 AS objsubid, 1656 'tablespace'::text AS objtype, 1657 NULL::oid AS objnamespace, 1658 quote_ident((spc.spcname)::text) AS objname, 1659 l.provider, 1660 l.label 1661 FROM (pg_shseclabel l 1662 JOIN pg_tablespace spc ON (((l.classoid = spc.tableoid) AND (l.objoid = spc.oid)))) 1663UNION ALL 1664 SELECT l.objoid, 1665 l.classoid, 1666 0 AS objsubid, 1667 'role'::text AS objtype, 1668 NULL::oid AS objnamespace, 1669 quote_ident((rol.rolname)::text) AS objname, 1670 l.provider, 1671 l.label 1672 FROM (pg_shseclabel l 1673 JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid)))); 1674pg_sequences| SELECT n.nspname AS schemaname, 1675 c.relname AS sequencename, 1676 pg_get_userbyid(c.relowner) AS sequenceowner, 1677 (s.seqtypid)::regtype AS data_type, 1678 s.seqstart AS start_value, 1679 s.seqmin AS min_value, 1680 s.seqmax AS max_value, 1681 s.seqincrement AS increment_by, 1682 s.seqcycle AS cycle, 1683 s.seqcache AS cache_size, 1684 CASE 1685 WHEN has_sequence_privilege(c.oid, 'SELECT,USAGE'::text) THEN pg_sequence_last_value((c.oid)::regclass) 1686 ELSE NULL::bigint 1687 END AS last_value 1688 FROM ((pg_sequence s 1689 JOIN pg_class c ON ((c.oid = s.seqrelid))) 1690 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 1691 WHERE ((NOT pg_is_other_temp_schema(n.oid)) AND (c.relkind = 'S'::"char")); 1692pg_settings| SELECT a.name, 1693 a.setting, 1694 a.unit, 1695 a.category, 1696 a.short_desc, 1697 a.extra_desc, 1698 a.context, 1699 a.vartype, 1700 a.source, 1701 a.min_val, 1702 a.max_val, 1703 a.enumvals, 1704 a.boot_val, 1705 a.reset_val, 1706 a.sourcefile, 1707 a.sourceline, 1708 a.pending_restart 1709 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); 1710pg_shadow| SELECT pg_authid.rolname AS usename, 1711 pg_authid.oid AS usesysid, 1712 pg_authid.rolcreatedb AS usecreatedb, 1713 pg_authid.rolsuper AS usesuper, 1714 pg_authid.rolreplication AS userepl, 1715 pg_authid.rolbypassrls AS usebypassrls, 1716 pg_authid.rolpassword AS passwd, 1717 (pg_authid.rolvaliduntil)::abstime AS valuntil, 1718 s.setconfig AS useconfig 1719 FROM (pg_authid 1720 LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) 1721 WHERE pg_authid.rolcanlogin; 1722pg_stat_activity| SELECT s.datid, 1723 d.datname, 1724 s.pid, 1725 s.usesysid, 1726 u.rolname AS usename, 1727 s.application_name, 1728 s.client_addr, 1729 s.client_hostname, 1730 s.client_port, 1731 s.backend_start, 1732 s.xact_start, 1733 s.query_start, 1734 s.state_change, 1735 s.wait_event_type, 1736 s.wait_event, 1737 s.state, 1738 s.backend_xid, 1739 s.backend_xmin, 1740 s.query, 1741 s.backend_type 1742 FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn) 1743 LEFT JOIN pg_database d ON ((s.datid = d.oid))) 1744 LEFT JOIN pg_authid u ON ((s.usesysid = u.oid))); 1745pg_stat_all_indexes| SELECT c.oid AS relid, 1746 i.oid AS indexrelid, 1747 n.nspname AS schemaname, 1748 c.relname, 1749 i.relname AS indexrelname, 1750 pg_stat_get_numscans(i.oid) AS idx_scan, 1751 pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, 1752 pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch 1753 FROM (((pg_class c 1754 JOIN pg_index x ON ((c.oid = x.indrelid))) 1755 JOIN pg_class i ON ((i.oid = x.indexrelid))) 1756 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 1757 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])); 1758pg_stat_all_tables| SELECT c.oid AS relid, 1759 n.nspname AS schemaname, 1760 c.relname, 1761 pg_stat_get_numscans(c.oid) AS seq_scan, 1762 pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, 1763 (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, 1764 ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, 1765 pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, 1766 pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, 1767 pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, 1768 pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, 1769 pg_stat_get_live_tuples(c.oid) AS n_live_tup, 1770 pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, 1771 pg_stat_get_mod_since_analyze(c.oid) AS n_mod_since_analyze, 1772 pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, 1773 pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, 1774 pg_stat_get_last_analyze_time(c.oid) AS last_analyze, 1775 pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, 1776 pg_stat_get_vacuum_count(c.oid) AS vacuum_count, 1777 pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, 1778 pg_stat_get_analyze_count(c.oid) AS analyze_count, 1779 pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count 1780 FROM ((pg_class c 1781 LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) 1782 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 1783 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])) 1784 GROUP BY c.oid, n.nspname, c.relname; 1785pg_stat_archiver| SELECT s.archived_count, 1786 s.last_archived_wal, 1787 s.last_archived_time, 1788 s.failed_count, 1789 s.last_failed_wal, 1790 s.last_failed_time, 1791 s.stats_reset 1792 FROM pg_stat_get_archiver() s(archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time, stats_reset); 1793pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, 1794 pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, 1795 pg_stat_get_checkpoint_write_time() AS checkpoint_write_time, 1796 pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time, 1797 pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, 1798 pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, 1799 pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, 1800 pg_stat_get_buf_written_backend() AS buffers_backend, 1801 pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, 1802 pg_stat_get_buf_alloc() AS buffers_alloc, 1803 pg_stat_get_bgwriter_stat_reset_time() AS stats_reset; 1804pg_stat_database| SELECT d.oid AS datid, 1805 d.datname, 1806 pg_stat_get_db_numbackends(d.oid) AS numbackends, 1807 pg_stat_get_db_xact_commit(d.oid) AS xact_commit, 1808 pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, 1809 (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, 1810 pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, 1811 pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, 1812 pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, 1813 pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, 1814 pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, 1815 pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted, 1816 pg_stat_get_db_conflict_all(d.oid) AS conflicts, 1817 pg_stat_get_db_temp_files(d.oid) AS temp_files, 1818 pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes, 1819 pg_stat_get_db_deadlocks(d.oid) AS deadlocks, 1820 pg_stat_get_db_blk_read_time(d.oid) AS blk_read_time, 1821 pg_stat_get_db_blk_write_time(d.oid) AS blk_write_time, 1822 pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset 1823 FROM pg_database d; 1824pg_stat_database_conflicts| SELECT d.oid AS datid, 1825 d.datname, 1826 pg_stat_get_db_conflict_tablespace(d.oid) AS confl_tablespace, 1827 pg_stat_get_db_conflict_lock(d.oid) AS confl_lock, 1828 pg_stat_get_db_conflict_snapshot(d.oid) AS confl_snapshot, 1829 pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin, 1830 pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock 1831 FROM pg_database d; 1832pg_stat_progress_vacuum| SELECT s.pid, 1833 s.datid, 1834 d.datname, 1835 s.relid, 1836 CASE s.param1 1837 WHEN 0 THEN 'initializing'::text 1838 WHEN 1 THEN 'scanning heap'::text 1839 WHEN 2 THEN 'vacuuming indexes'::text 1840 WHEN 3 THEN 'vacuuming heap'::text 1841 WHEN 4 THEN 'cleaning up indexes'::text 1842 WHEN 5 THEN 'truncating heap'::text 1843 WHEN 6 THEN 'performing final cleanup'::text 1844 ELSE NULL::text 1845 END AS phase, 1846 s.param2 AS heap_blks_total, 1847 s.param3 AS heap_blks_scanned, 1848 s.param4 AS heap_blks_vacuumed, 1849 s.param5 AS index_vacuum_count, 1850 s.param6 AS max_dead_tuples, 1851 s.param7 AS num_dead_tuples 1852 FROM (pg_stat_get_progress_info('VACUUM'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10) 1853 LEFT JOIN pg_database d ON ((s.datid = d.oid))); 1854pg_stat_replication| SELECT s.pid, 1855 s.usesysid, 1856 u.rolname AS usename, 1857 s.application_name, 1858 s.client_addr, 1859 s.client_hostname, 1860 s.client_port, 1861 s.backend_start, 1862 s.backend_xmin, 1863 w.state, 1864 w.sent_lsn, 1865 w.write_lsn, 1866 w.flush_lsn, 1867 w.replay_lsn, 1868 w.write_lag, 1869 w.flush_lag, 1870 w.replay_lag, 1871 w.sync_priority, 1872 w.sync_state 1873 FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn) 1874 JOIN pg_stat_get_wal_senders() w(pid, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag, sync_priority, sync_state) ON ((s.pid = w.pid))) 1875 LEFT JOIN pg_authid u ON ((s.usesysid = u.oid))); 1876pg_stat_ssl| SELECT s.pid, 1877 s.ssl, 1878 s.sslversion AS version, 1879 s.sslcipher AS cipher, 1880 s.sslbits AS bits, 1881 s.sslcompression AS compression, 1882 s.sslclientdn AS clientdn 1883 FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn); 1884pg_stat_subscription| SELECT su.oid AS subid, 1885 su.subname, 1886 st.pid, 1887 st.relid, 1888 st.received_lsn, 1889 st.last_msg_send_time, 1890 st.last_msg_receipt_time, 1891 st.latest_end_lsn, 1892 st.latest_end_time 1893 FROM (pg_subscription su 1894 LEFT JOIN pg_stat_get_subscription(NULL::oid) st(subid, relid, pid, received_lsn, last_msg_send_time, last_msg_receipt_time, latest_end_lsn, latest_end_time) ON ((st.subid = su.oid))); 1895pg_stat_sys_indexes| SELECT pg_stat_all_indexes.relid, 1896 pg_stat_all_indexes.indexrelid, 1897 pg_stat_all_indexes.schemaname, 1898 pg_stat_all_indexes.relname, 1899 pg_stat_all_indexes.indexrelname, 1900 pg_stat_all_indexes.idx_scan, 1901 pg_stat_all_indexes.idx_tup_read, 1902 pg_stat_all_indexes.idx_tup_fetch 1903 FROM pg_stat_all_indexes 1904 WHERE ((pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_indexes.schemaname ~ '^pg_toast'::text)); 1905pg_stat_sys_tables| SELECT pg_stat_all_tables.relid, 1906 pg_stat_all_tables.schemaname, 1907 pg_stat_all_tables.relname, 1908 pg_stat_all_tables.seq_scan, 1909 pg_stat_all_tables.seq_tup_read, 1910 pg_stat_all_tables.idx_scan, 1911 pg_stat_all_tables.idx_tup_fetch, 1912 pg_stat_all_tables.n_tup_ins, 1913 pg_stat_all_tables.n_tup_upd, 1914 pg_stat_all_tables.n_tup_del, 1915 pg_stat_all_tables.n_tup_hot_upd, 1916 pg_stat_all_tables.n_live_tup, 1917 pg_stat_all_tables.n_dead_tup, 1918 pg_stat_all_tables.n_mod_since_analyze, 1919 pg_stat_all_tables.last_vacuum, 1920 pg_stat_all_tables.last_autovacuum, 1921 pg_stat_all_tables.last_analyze, 1922 pg_stat_all_tables.last_autoanalyze, 1923 pg_stat_all_tables.vacuum_count, 1924 pg_stat_all_tables.autovacuum_count, 1925 pg_stat_all_tables.analyze_count, 1926 pg_stat_all_tables.autoanalyze_count 1927 FROM pg_stat_all_tables 1928 WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text)); 1929pg_stat_user_functions| SELECT p.oid AS funcid, 1930 n.nspname AS schemaname, 1931 p.proname AS funcname, 1932 pg_stat_get_function_calls(p.oid) AS calls, 1933 pg_stat_get_function_total_time(p.oid) AS total_time, 1934 pg_stat_get_function_self_time(p.oid) AS self_time 1935 FROM (pg_proc p 1936 LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) 1937 WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL)); 1938pg_stat_user_indexes| SELECT pg_stat_all_indexes.relid, 1939 pg_stat_all_indexes.indexrelid, 1940 pg_stat_all_indexes.schemaname, 1941 pg_stat_all_indexes.relname, 1942 pg_stat_all_indexes.indexrelname, 1943 pg_stat_all_indexes.idx_scan, 1944 pg_stat_all_indexes.idx_tup_read, 1945 pg_stat_all_indexes.idx_tup_fetch 1946 FROM pg_stat_all_indexes 1947 WHERE ((pg_stat_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_indexes.schemaname !~ '^pg_toast'::text)); 1948pg_stat_user_tables| SELECT pg_stat_all_tables.relid, 1949 pg_stat_all_tables.schemaname, 1950 pg_stat_all_tables.relname, 1951 pg_stat_all_tables.seq_scan, 1952 pg_stat_all_tables.seq_tup_read, 1953 pg_stat_all_tables.idx_scan, 1954 pg_stat_all_tables.idx_tup_fetch, 1955 pg_stat_all_tables.n_tup_ins, 1956 pg_stat_all_tables.n_tup_upd, 1957 pg_stat_all_tables.n_tup_del, 1958 pg_stat_all_tables.n_tup_hot_upd, 1959 pg_stat_all_tables.n_live_tup, 1960 pg_stat_all_tables.n_dead_tup, 1961 pg_stat_all_tables.n_mod_since_analyze, 1962 pg_stat_all_tables.last_vacuum, 1963 pg_stat_all_tables.last_autovacuum, 1964 pg_stat_all_tables.last_analyze, 1965 pg_stat_all_tables.last_autoanalyze, 1966 pg_stat_all_tables.vacuum_count, 1967 pg_stat_all_tables.autovacuum_count, 1968 pg_stat_all_tables.analyze_count, 1969 pg_stat_all_tables.autoanalyze_count 1970 FROM pg_stat_all_tables 1971 WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text)); 1972pg_stat_wal_receiver| SELECT s.pid, 1973 s.status, 1974 s.receive_start_lsn, 1975 s.receive_start_tli, 1976 s.received_lsn, 1977 s.received_tli, 1978 s.last_msg_send_time, 1979 s.last_msg_receipt_time, 1980 s.latest_end_lsn, 1981 s.latest_end_time, 1982 s.slot_name, 1983 s.sender_host, 1984 s.sender_port, 1985 s.conninfo 1986 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, sender_host, sender_port, conninfo) 1987 WHERE (s.pid IS NOT NULL); 1988pg_stat_xact_all_tables| SELECT c.oid AS relid, 1989 n.nspname AS schemaname, 1990 c.relname, 1991 pg_stat_get_xact_numscans(c.oid) AS seq_scan, 1992 pg_stat_get_xact_tuples_returned(c.oid) AS seq_tup_read, 1993 (sum(pg_stat_get_xact_numscans(i.indexrelid)))::bigint AS idx_scan, 1994 ((sum(pg_stat_get_xact_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_xact_tuples_fetched(c.oid)) AS idx_tup_fetch, 1995 pg_stat_get_xact_tuples_inserted(c.oid) AS n_tup_ins, 1996 pg_stat_get_xact_tuples_updated(c.oid) AS n_tup_upd, 1997 pg_stat_get_xact_tuples_deleted(c.oid) AS n_tup_del, 1998 pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd 1999 FROM ((pg_class c 2000 LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) 2001 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 2002 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])) 2003 GROUP BY c.oid, n.nspname, c.relname; 2004pg_stat_xact_sys_tables| SELECT pg_stat_xact_all_tables.relid, 2005 pg_stat_xact_all_tables.schemaname, 2006 pg_stat_xact_all_tables.relname, 2007 pg_stat_xact_all_tables.seq_scan, 2008 pg_stat_xact_all_tables.seq_tup_read, 2009 pg_stat_xact_all_tables.idx_scan, 2010 pg_stat_xact_all_tables.idx_tup_fetch, 2011 pg_stat_xact_all_tables.n_tup_ins, 2012 pg_stat_xact_all_tables.n_tup_upd, 2013 pg_stat_xact_all_tables.n_tup_del, 2014 pg_stat_xact_all_tables.n_tup_hot_upd 2015 FROM pg_stat_xact_all_tables 2016 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)); 2017pg_stat_xact_user_functions| SELECT p.oid AS funcid, 2018 n.nspname AS schemaname, 2019 p.proname AS funcname, 2020 pg_stat_get_xact_function_calls(p.oid) AS calls, 2021 pg_stat_get_xact_function_total_time(p.oid) AS total_time, 2022 pg_stat_get_xact_function_self_time(p.oid) AS self_time 2023 FROM (pg_proc p 2024 LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) 2025 WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_xact_function_calls(p.oid) IS NOT NULL)); 2026pg_stat_xact_user_tables| SELECT pg_stat_xact_all_tables.relid, 2027 pg_stat_xact_all_tables.schemaname, 2028 pg_stat_xact_all_tables.relname, 2029 pg_stat_xact_all_tables.seq_scan, 2030 pg_stat_xact_all_tables.seq_tup_read, 2031 pg_stat_xact_all_tables.idx_scan, 2032 pg_stat_xact_all_tables.idx_tup_fetch, 2033 pg_stat_xact_all_tables.n_tup_ins, 2034 pg_stat_xact_all_tables.n_tup_upd, 2035 pg_stat_xact_all_tables.n_tup_del, 2036 pg_stat_xact_all_tables.n_tup_hot_upd 2037 FROM pg_stat_xact_all_tables 2038 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)); 2039pg_statio_all_indexes| SELECT c.oid AS relid, 2040 i.oid AS indexrelid, 2041 n.nspname AS schemaname, 2042 c.relname, 2043 i.relname AS indexrelname, 2044 (pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read, 2045 pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit 2046 FROM (((pg_class c 2047 JOIN pg_index x ON ((c.oid = x.indrelid))) 2048 JOIN pg_class i ON ((i.oid = x.indexrelid))) 2049 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 2050 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])); 2051pg_statio_all_sequences| SELECT c.oid AS relid, 2052 n.nspname AS schemaname, 2053 c.relname, 2054 (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read, 2055 pg_stat_get_blocks_hit(c.oid) AS blks_hit 2056 FROM (pg_class c 2057 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 2058 WHERE (c.relkind = 'S'::"char"); 2059pg_statio_all_tables| SELECT c.oid AS relid, 2060 n.nspname AS schemaname, 2061 c.relname, 2062 (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS heap_blks_read, 2063 pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit, 2064 (sum((pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))))::bigint AS idx_blks_read, 2065 (sum(pg_stat_get_blocks_hit(i.indexrelid)))::bigint AS idx_blks_hit, 2066 (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read, 2067 pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit, 2068 (sum((pg_stat_get_blocks_fetched(x.indexrelid) - pg_stat_get_blocks_hit(x.indexrelid))))::bigint AS tidx_blks_read, 2069 (sum(pg_stat_get_blocks_hit(x.indexrelid)))::bigint AS tidx_blks_hit 2070 FROM ((((pg_class c 2071 LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) 2072 LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid))) 2073 LEFT JOIN pg_index x ON ((t.oid = x.indrelid))) 2074 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 2075 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])) 2076 GROUP BY c.oid, n.nspname, c.relname, t.oid, x.indrelid; 2077pg_statio_sys_indexes| SELECT pg_statio_all_indexes.relid, 2078 pg_statio_all_indexes.indexrelid, 2079 pg_statio_all_indexes.schemaname, 2080 pg_statio_all_indexes.relname, 2081 pg_statio_all_indexes.indexrelname, 2082 pg_statio_all_indexes.idx_blks_read, 2083 pg_statio_all_indexes.idx_blks_hit 2084 FROM pg_statio_all_indexes 2085 WHERE ((pg_statio_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_indexes.schemaname ~ '^pg_toast'::text)); 2086pg_statio_sys_sequences| SELECT pg_statio_all_sequences.relid, 2087 pg_statio_all_sequences.schemaname, 2088 pg_statio_all_sequences.relname, 2089 pg_statio_all_sequences.blks_read, 2090 pg_statio_all_sequences.blks_hit 2091 FROM pg_statio_all_sequences 2092 WHERE ((pg_statio_all_sequences.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_sequences.schemaname ~ '^pg_toast'::text)); 2093pg_statio_sys_tables| SELECT pg_statio_all_tables.relid, 2094 pg_statio_all_tables.schemaname, 2095 pg_statio_all_tables.relname, 2096 pg_statio_all_tables.heap_blks_read, 2097 pg_statio_all_tables.heap_blks_hit, 2098 pg_statio_all_tables.idx_blks_read, 2099 pg_statio_all_tables.idx_blks_hit, 2100 pg_statio_all_tables.toast_blks_read, 2101 pg_statio_all_tables.toast_blks_hit, 2102 pg_statio_all_tables.tidx_blks_read, 2103 pg_statio_all_tables.tidx_blks_hit 2104 FROM pg_statio_all_tables 2105 WHERE ((pg_statio_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_tables.schemaname ~ '^pg_toast'::text)); 2106pg_statio_user_indexes| SELECT pg_statio_all_indexes.relid, 2107 pg_statio_all_indexes.indexrelid, 2108 pg_statio_all_indexes.schemaname, 2109 pg_statio_all_indexes.relname, 2110 pg_statio_all_indexes.indexrelname, 2111 pg_statio_all_indexes.idx_blks_read, 2112 pg_statio_all_indexes.idx_blks_hit 2113 FROM pg_statio_all_indexes 2114 WHERE ((pg_statio_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_indexes.schemaname !~ '^pg_toast'::text)); 2115pg_statio_user_sequences| SELECT pg_statio_all_sequences.relid, 2116 pg_statio_all_sequences.schemaname, 2117 pg_statio_all_sequences.relname, 2118 pg_statio_all_sequences.blks_read, 2119 pg_statio_all_sequences.blks_hit 2120 FROM pg_statio_all_sequences 2121 WHERE ((pg_statio_all_sequences.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_sequences.schemaname !~ '^pg_toast'::text)); 2122pg_statio_user_tables| SELECT pg_statio_all_tables.relid, 2123 pg_statio_all_tables.schemaname, 2124 pg_statio_all_tables.relname, 2125 pg_statio_all_tables.heap_blks_read, 2126 pg_statio_all_tables.heap_blks_hit, 2127 pg_statio_all_tables.idx_blks_read, 2128 pg_statio_all_tables.idx_blks_hit, 2129 pg_statio_all_tables.toast_blks_read, 2130 pg_statio_all_tables.toast_blks_hit, 2131 pg_statio_all_tables.tidx_blks_read, 2132 pg_statio_all_tables.tidx_blks_hit 2133 FROM pg_statio_all_tables 2134 WHERE ((pg_statio_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_tables.schemaname !~ '^pg_toast'::text)); 2135pg_stats| SELECT n.nspname AS schemaname, 2136 c.relname AS tablename, 2137 a.attname, 2138 s.stainherit AS inherited, 2139 s.stanullfrac AS null_frac, 2140 s.stawidth AS avg_width, 2141 s.stadistinct AS n_distinct, 2142 CASE 2143 WHEN (s.stakind1 = 1) THEN s.stavalues1 2144 WHEN (s.stakind2 = 1) THEN s.stavalues2 2145 WHEN (s.stakind3 = 1) THEN s.stavalues3 2146 WHEN (s.stakind4 = 1) THEN s.stavalues4 2147 WHEN (s.stakind5 = 1) THEN s.stavalues5 2148 ELSE NULL::anyarray 2149 END AS most_common_vals, 2150 CASE 2151 WHEN (s.stakind1 = 1) THEN s.stanumbers1 2152 WHEN (s.stakind2 = 1) THEN s.stanumbers2 2153 WHEN (s.stakind3 = 1) THEN s.stanumbers3 2154 WHEN (s.stakind4 = 1) THEN s.stanumbers4 2155 WHEN (s.stakind5 = 1) THEN s.stanumbers5 2156 ELSE NULL::real[] 2157 END AS most_common_freqs, 2158 CASE 2159 WHEN (s.stakind1 = 2) THEN s.stavalues1 2160 WHEN (s.stakind2 = 2) THEN s.stavalues2 2161 WHEN (s.stakind3 = 2) THEN s.stavalues3 2162 WHEN (s.stakind4 = 2) THEN s.stavalues4 2163 WHEN (s.stakind5 = 2) THEN s.stavalues5 2164 ELSE NULL::anyarray 2165 END AS histogram_bounds, 2166 CASE 2167 WHEN (s.stakind1 = 3) THEN s.stanumbers1[1] 2168 WHEN (s.stakind2 = 3) THEN s.stanumbers2[1] 2169 WHEN (s.stakind3 = 3) THEN s.stanumbers3[1] 2170 WHEN (s.stakind4 = 3) THEN s.stanumbers4[1] 2171 WHEN (s.stakind5 = 3) THEN s.stanumbers5[1] 2172 ELSE NULL::real 2173 END AS correlation, 2174 CASE 2175 WHEN (s.stakind1 = 4) THEN s.stavalues1 2176 WHEN (s.stakind2 = 4) THEN s.stavalues2 2177 WHEN (s.stakind3 = 4) THEN s.stavalues3 2178 WHEN (s.stakind4 = 4) THEN s.stavalues4 2179 WHEN (s.stakind5 = 4) THEN s.stavalues5 2180 ELSE NULL::anyarray 2181 END AS most_common_elems, 2182 CASE 2183 WHEN (s.stakind1 = 4) THEN s.stanumbers1 2184 WHEN (s.stakind2 = 4) THEN s.stanumbers2 2185 WHEN (s.stakind3 = 4) THEN s.stanumbers3 2186 WHEN (s.stakind4 = 4) THEN s.stanumbers4 2187 WHEN (s.stakind5 = 4) THEN s.stanumbers5 2188 ELSE NULL::real[] 2189 END AS most_common_elem_freqs, 2190 CASE 2191 WHEN (s.stakind1 = 5) THEN s.stanumbers1 2192 WHEN (s.stakind2 = 5) THEN s.stanumbers2 2193 WHEN (s.stakind3 = 5) THEN s.stanumbers3 2194 WHEN (s.stakind4 = 5) THEN s.stanumbers4 2195 WHEN (s.stakind5 = 5) THEN s.stanumbers5 2196 ELSE NULL::real[] 2197 END AS elem_count_histogram 2198 FROM (((pg_statistic s 2199 JOIN pg_class c ON ((c.oid = s.starelid))) 2200 JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) 2201 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 2202 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)))); 2203pg_tables| SELECT n.nspname AS schemaname, 2204 c.relname AS tablename, 2205 pg_get_userbyid(c.relowner) AS tableowner, 2206 t.spcname AS tablespace, 2207 c.relhasindex AS hasindexes, 2208 c.relhasrules AS hasrules, 2209 c.relhastriggers AS hastriggers, 2210 c.relrowsecurity AS rowsecurity 2211 FROM ((pg_class c 2212 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 2213 LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) 2214 WHERE (c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"])); 2215pg_timezone_abbrevs| SELECT pg_timezone_abbrevs.abbrev, 2216 pg_timezone_abbrevs.utc_offset, 2217 pg_timezone_abbrevs.is_dst 2218 FROM pg_timezone_abbrevs() pg_timezone_abbrevs(abbrev, utc_offset, is_dst); 2219pg_timezone_names| SELECT pg_timezone_names.name, 2220 pg_timezone_names.abbrev, 2221 pg_timezone_names.utc_offset, 2222 pg_timezone_names.is_dst 2223 FROM pg_timezone_names() pg_timezone_names(name, abbrev, utc_offset, is_dst); 2224pg_user| SELECT pg_shadow.usename, 2225 pg_shadow.usesysid, 2226 pg_shadow.usecreatedb, 2227 pg_shadow.usesuper, 2228 pg_shadow.userepl, 2229 pg_shadow.usebypassrls, 2230 '********'::text AS passwd, 2231 pg_shadow.valuntil, 2232 pg_shadow.useconfig 2233 FROM pg_shadow; 2234pg_user_mappings| SELECT u.oid AS umid, 2235 s.oid AS srvid, 2236 s.srvname, 2237 u.umuser, 2238 CASE 2239 WHEN (u.umuser = (0)::oid) THEN 'public'::name 2240 ELSE a.rolname 2241 END AS usename, 2242 CASE 2243 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 2244 FROM pg_authid 2245 WHERE (pg_authid.rolname = CURRENT_USER))) THEN u.umoptions 2246 ELSE NULL::text[] 2247 END AS umoptions 2248 FROM ((pg_user_mapping u 2249 JOIN pg_foreign_server s ON ((u.umserver = s.oid))) 2250 LEFT JOIN pg_authid a ON ((a.oid = u.umuser))); 2251pg_views| SELECT n.nspname AS schemaname, 2252 c.relname AS viewname, 2253 pg_get_userbyid(c.relowner) AS viewowner, 2254 pg_get_viewdef(c.oid) AS definition 2255 FROM (pg_class c 2256 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 2257 WHERE (c.relkind = 'v'::"char"); 2258rtest_v1| SELECT rtest_t1.a, 2259 rtest_t1.b 2260 FROM rtest_t1; 2261rtest_vcomp| SELECT x.part, 2262 (x.size * y.factor) AS size_in_cm 2263 FROM rtest_comp x, 2264 rtest_unitfact y 2265 WHERE (x.unit = y.unit); 2266rtest_vview1| SELECT x.a, 2267 x.b 2268 FROM rtest_view1 x 2269 WHERE (0 < ( SELECT count(*) AS count 2270 FROM rtest_view2 y 2271 WHERE (y.a = x.a))); 2272rtest_vview2| SELECT rtest_view1.a, 2273 rtest_view1.b 2274 FROM rtest_view1 2275 WHERE rtest_view1.v; 2276rtest_vview3| SELECT x.a, 2277 x.b 2278 FROM rtest_vview2 x 2279 WHERE (0 < ( SELECT count(*) AS count 2280 FROM rtest_view2 y 2281 WHERE (y.a = x.a))); 2282rtest_vview4| SELECT x.a, 2283 x.b, 2284 count(y.a) AS refcount 2285 FROM rtest_view1 x, 2286 rtest_view2 y 2287 WHERE (x.a = y.a) 2288 GROUP BY x.a, x.b; 2289rtest_vview5| SELECT rtest_view1.a, 2290 rtest_view1.b, 2291 rtest_viewfunc1(rtest_view1.a) AS refcount 2292 FROM rtest_view1; 2293shoe| SELECT sh.shoename, 2294 sh.sh_avail, 2295 sh.slcolor, 2296 sh.slminlen, 2297 (sh.slminlen * un.un_fact) AS slminlen_cm, 2298 sh.slmaxlen, 2299 (sh.slmaxlen * un.un_fact) AS slmaxlen_cm, 2300 sh.slunit 2301 FROM shoe_data sh, 2302 unit un 2303 WHERE (sh.slunit = un.un_name); 2304shoe_ready| SELECT rsh.shoename, 2305 rsh.sh_avail, 2306 rsl.sl_name, 2307 rsl.sl_avail, 2308 int4smaller(rsh.sh_avail, rsl.sl_avail) AS total_avail 2309 FROM shoe rsh, 2310 shoelace rsl 2311 WHERE ((rsl.sl_color = rsh.slcolor) AND (rsl.sl_len_cm >= rsh.slminlen_cm) AND (rsl.sl_len_cm <= rsh.slmaxlen_cm)); 2312shoelace| SELECT s.sl_name, 2313 s.sl_avail, 2314 s.sl_color, 2315 s.sl_len, 2316 s.sl_unit, 2317 (s.sl_len * u.un_fact) AS sl_len_cm 2318 FROM shoelace_data s, 2319 unit u 2320 WHERE (s.sl_unit = u.un_name); 2321shoelace_candelete| SELECT shoelace_obsolete.sl_name, 2322 shoelace_obsolete.sl_avail, 2323 shoelace_obsolete.sl_color, 2324 shoelace_obsolete.sl_len, 2325 shoelace_obsolete.sl_unit, 2326 shoelace_obsolete.sl_len_cm 2327 FROM shoelace_obsolete 2328 WHERE (shoelace_obsolete.sl_avail = 0); 2329shoelace_obsolete| SELECT shoelace.sl_name, 2330 shoelace.sl_avail, 2331 shoelace.sl_color, 2332 shoelace.sl_len, 2333 shoelace.sl_unit, 2334 shoelace.sl_len_cm 2335 FROM shoelace 2336 WHERE (NOT (EXISTS ( SELECT shoe.shoename 2337 FROM shoe 2338 WHERE (shoe.slcolor = shoelace.sl_color)))); 2339street| SELECT r.name, 2340 r.thepath, 2341 c.cname 2342 FROM ONLY road r, 2343 real_city c 2344 WHERE (c.outline ## r.thepath); 2345test_tablesample_v1| SELECT test_tablesample.id 2346 FROM test_tablesample TABLESAMPLE system ((10 * 2)) REPEATABLE (2); 2347test_tablesample_v2| SELECT test_tablesample.id 2348 FROM test_tablesample TABLESAMPLE system (99); 2349toyemp| SELECT emp.name, 2350 emp.age, 2351 emp.location, 2352 (12 * emp.salary) AS annualsal 2353 FROM emp; 2354SELECT tablename, rulename, definition FROM pg_rules 2355 ORDER BY tablename, rulename; 2356pg_settings|pg_settings_n|CREATE RULE pg_settings_n AS 2357 ON UPDATE TO pg_catalog.pg_settings DO INSTEAD NOTHING; 2358pg_settings|pg_settings_u|CREATE RULE pg_settings_u AS 2359 ON UPDATE TO pg_catalog.pg_settings 2360 WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, false) AS set_config; 2361rtest_emp|rtest_emp_del|CREATE RULE rtest_emp_del AS 2362 ON DELETE TO public.rtest_emp DO INSERT INTO rtest_emplog (ename, who, action, newsal, oldsal) 2363 VALUES (old.ename, CURRENT_USER, 'fired'::bpchar, '$0.00'::money, old.salary); 2364rtest_emp|rtest_emp_ins|CREATE RULE rtest_emp_ins AS 2365 ON INSERT TO public.rtest_emp DO INSERT INTO rtest_emplog (ename, who, action, newsal, oldsal) 2366 VALUES (new.ename, CURRENT_USER, 'hired'::bpchar, new.salary, '$0.00'::money); 2367rtest_emp|rtest_emp_upd|CREATE RULE rtest_emp_upd AS 2368 ON UPDATE TO public.rtest_emp 2369 WHERE (new.salary <> old.salary) DO INSERT INTO rtest_emplog (ename, who, action, newsal, oldsal) 2370 VALUES (new.ename, CURRENT_USER, 'honored'::bpchar, new.salary, old.salary); 2371rtest_nothn1|rtest_nothn_r1|CREATE RULE rtest_nothn_r1 AS 2372 ON INSERT TO public.rtest_nothn1 2373 WHERE ((new.a >= 10) AND (new.a < 20)) DO INSTEAD NOTHING; 2374rtest_nothn1|rtest_nothn_r2|CREATE RULE rtest_nothn_r2 AS 2375 ON INSERT TO public.rtest_nothn1 2376 WHERE ((new.a >= 30) AND (new.a < 40)) DO INSTEAD NOTHING; 2377rtest_nothn2|rtest_nothn_r3|CREATE RULE rtest_nothn_r3 AS 2378 ON INSERT TO public.rtest_nothn2 2379 WHERE (new.a >= 100) DO INSTEAD INSERT INTO rtest_nothn3 (a, b) 2380 VALUES (new.a, new.b); 2381rtest_nothn2|rtest_nothn_r4|CREATE RULE rtest_nothn_r4 AS 2382 ON INSERT TO public.rtest_nothn2 DO INSTEAD NOTHING; 2383rtest_order1|rtest_order_r1|CREATE RULE rtest_order_r1 AS 2384 ON INSERT TO public.rtest_order1 DO INSTEAD INSERT INTO rtest_order2 (a, b, c) 2385 VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 1 - this should run 1st'::text); 2386rtest_order1|rtest_order_r2|CREATE RULE rtest_order_r2 AS 2387 ON INSERT TO public.rtest_order1 DO INSERT INTO rtest_order2 (a, b, c) 2388 VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 2 - this should run 2nd'::text); 2389rtest_order1|rtest_order_r3|CREATE RULE rtest_order_r3 AS 2390 ON INSERT TO public.rtest_order1 DO INSTEAD INSERT INTO rtest_order2 (a, b, c) 2391 VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 3 - this should run 3rd'::text); 2392rtest_order1|rtest_order_r4|CREATE RULE rtest_order_r4 AS 2393 ON INSERT TO public.rtest_order1 2394 WHERE (new.a < 100) DO INSTEAD INSERT INTO rtest_order2 (a, b, c) 2395 VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 4 - this should run 4th'::text); 2396rtest_person|rtest_pers_del|CREATE RULE rtest_pers_del AS 2397 ON DELETE TO public.rtest_person DO DELETE FROM rtest_admin 2398 WHERE (rtest_admin.pname = old.pname); 2399rtest_person|rtest_pers_upd|CREATE RULE rtest_pers_upd AS 2400 ON UPDATE TO public.rtest_person DO UPDATE rtest_admin SET pname = new.pname 2401 WHERE (rtest_admin.pname = old.pname); 2402rtest_system|rtest_sys_del|CREATE RULE rtest_sys_del AS 2403 ON DELETE TO public.rtest_system DO ( DELETE FROM rtest_interface 2404 WHERE (rtest_interface.sysname = old.sysname); 2405 DELETE FROM rtest_admin 2406 WHERE (rtest_admin.sysname = old.sysname); 2407); 2408rtest_system|rtest_sys_upd|CREATE RULE rtest_sys_upd AS 2409 ON UPDATE TO public.rtest_system DO ( UPDATE rtest_interface SET sysname = new.sysname 2410 WHERE (rtest_interface.sysname = old.sysname); 2411 UPDATE rtest_admin SET sysname = new.sysname 2412 WHERE (rtest_admin.sysname = old.sysname); 2413); 2414rtest_t4|rtest_t4_ins1|CREATE RULE rtest_t4_ins1 AS 2415 ON INSERT TO public.rtest_t4 2416 WHERE ((new.a >= 10) AND (new.a < 20)) DO INSTEAD INSERT INTO rtest_t5 (a, b) 2417 VALUES (new.a, new.b); 2418rtest_t4|rtest_t4_ins2|CREATE RULE rtest_t4_ins2 AS 2419 ON INSERT TO public.rtest_t4 2420 WHERE ((new.a >= 20) AND (new.a < 30)) DO INSERT INTO rtest_t6 (a, b) 2421 VALUES (new.a, new.b); 2422rtest_t5|rtest_t5_ins|CREATE RULE rtest_t5_ins AS 2423 ON INSERT TO public.rtest_t5 2424 WHERE (new.a > 15) DO INSERT INTO rtest_t7 (a, b) 2425 VALUES (new.a, new.b); 2426rtest_t6|rtest_t6_ins|CREATE RULE rtest_t6_ins AS 2427 ON INSERT TO public.rtest_t6 2428 WHERE (new.a > 25) DO INSTEAD INSERT INTO rtest_t8 (a, b) 2429 VALUES (new.a, new.b); 2430rtest_v1|rtest_v1_del|CREATE RULE rtest_v1_del AS 2431 ON DELETE TO public.rtest_v1 DO INSTEAD DELETE FROM rtest_t1 2432 WHERE (rtest_t1.a = old.a); 2433rtest_v1|rtest_v1_ins|CREATE RULE rtest_v1_ins AS 2434 ON INSERT TO public.rtest_v1 DO INSTEAD INSERT INTO rtest_t1 (a, b) 2435 VALUES (new.a, new.b); 2436rtest_v1|rtest_v1_upd|CREATE RULE rtest_v1_upd AS 2437 ON UPDATE TO public.rtest_v1 DO INSTEAD UPDATE rtest_t1 SET a = new.a, b = new.b 2438 WHERE (rtest_t1.a = old.a); 2439shoelace|shoelace_del|CREATE RULE shoelace_del AS 2440 ON DELETE TO public.shoelace DO INSTEAD DELETE FROM shoelace_data 2441 WHERE (shoelace_data.sl_name = old.sl_name); 2442shoelace|shoelace_ins|CREATE RULE shoelace_ins AS 2443 ON INSERT TO public.shoelace DO INSTEAD INSERT INTO shoelace_data (sl_name, sl_avail, sl_color, sl_len, sl_unit) 2444 VALUES (new.sl_name, new.sl_avail, new.sl_color, new.sl_len, new.sl_unit); 2445shoelace|shoelace_upd|CREATE RULE shoelace_upd AS 2446 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 2447 WHERE (shoelace_data.sl_name = old.sl_name); 2448shoelace_data|log_shoelace|CREATE RULE log_shoelace AS 2449 ON UPDATE TO public.shoelace_data 2450 WHERE (new.sl_avail <> old.sl_avail) DO INSERT INTO shoelace_log (sl_name, sl_avail, log_who, log_when) 2451 VALUES (new.sl_name, new.sl_avail, 'Al Bundy'::name, 'Thu Jan 01 00:00:00 1970'::timestamp without time zone); 2452shoelace_ok|shoelace_ok_ins|CREATE RULE shoelace_ok_ins AS 2453 ON INSERT TO public.shoelace_ok DO INSTEAD UPDATE shoelace SET sl_avail = (shoelace.sl_avail + new.ok_quant) 2454 WHERE (shoelace.sl_name = new.ok_name); 2455-- restore normal output mode 2456\a\t 2457-- 2458-- CREATE OR REPLACE RULE 2459-- 2460CREATE TABLE ruletest_tbl (a int, b int); 2461CREATE TABLE ruletest_tbl2 (a int, b int); 2462CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl 2463 DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (10, 10); 2464INSERT INTO ruletest_tbl VALUES (99, 99); 2465CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl 2466 DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (1000, 1000); 2467INSERT INTO ruletest_tbl VALUES (99, 99); 2468SELECT * FROM ruletest_tbl2; 2469 a | b 2470------+------ 2471 10 | 10 2472 1000 | 1000 2473(2 rows) 2474 2475-- Check that rewrite rules splitting one INSERT into multiple 2476-- conditional statements does not disable FK checking. 2477create table rule_and_refint_t1 ( 2478 id1a integer, 2479 id1b integer, 2480 primary key (id1a, id1b) 2481); 2482create table rule_and_refint_t2 ( 2483 id2a integer, 2484 id2c integer, 2485 primary key (id2a, id2c) 2486); 2487create table rule_and_refint_t3 ( 2488 id3a integer, 2489 id3b integer, 2490 id3c integer, 2491 data text, 2492 primary key (id3a, id3b, id3c), 2493 foreign key (id3a, id3b) references rule_and_refint_t1 (id1a, id1b), 2494 foreign key (id3a, id3c) references rule_and_refint_t2 (id2a, id2c) 2495); 2496insert into rule_and_refint_t1 values (1, 11); 2497insert into rule_and_refint_t1 values (1, 12); 2498insert into rule_and_refint_t1 values (2, 21); 2499insert into rule_and_refint_t1 values (2, 22); 2500insert into rule_and_refint_t2 values (1, 11); 2501insert into rule_and_refint_t2 values (1, 12); 2502insert into rule_and_refint_t2 values (2, 21); 2503insert into rule_and_refint_t2 values (2, 22); 2504insert into rule_and_refint_t3 values (1, 11, 11, 'row1'); 2505insert into rule_and_refint_t3 values (1, 11, 12, 'row2'); 2506insert into rule_and_refint_t3 values (1, 12, 11, 'row3'); 2507insert into rule_and_refint_t3 values (1, 12, 12, 'row4'); 2508insert into rule_and_refint_t3 values (1, 11, 13, 'row5'); 2509ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey1" 2510DETAIL: Key (id3a, id3c)=(1, 13) is not present in table "rule_and_refint_t2". 2511insert into rule_and_refint_t3 values (1, 13, 11, 'row6'); 2512ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey" 2513DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1". 2514-- Ordinary table 2515insert into rule_and_refint_t3 values (1, 13, 11, 'row6') 2516 on conflict do nothing; 2517ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey" 2518DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1". 2519-- rule not fired, so fk violation 2520insert into rule_and_refint_t3 values (1, 13, 11, 'row6') 2521 on conflict (id3a, id3b, id3c) do update 2522 set id3b = excluded.id3b; 2523ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey" 2524DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1". 2525-- rule fired, so unsupported 2526insert into shoelace values ('sl9', 0, 'pink', 35.0, 'inch', 0.0) 2527 on conflict (sl_name) do update 2528 set sl_avail = excluded.sl_avail; 2529ERROR: INSERT with ON CONFLICT clause cannot be used with table that has INSERT or UPDATE rules 2530create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3 2531 where (exists (select 1 from rule_and_refint_t3 2532 where (((rule_and_refint_t3.id3a = new.id3a) 2533 and (rule_and_refint_t3.id3b = new.id3b)) 2534 and (rule_and_refint_t3.id3c = new.id3c)))) 2535 do instead update rule_and_refint_t3 set data = new.data 2536 where (((rule_and_refint_t3.id3a = new.id3a) 2537 and (rule_and_refint_t3.id3b = new.id3b)) 2538 and (rule_and_refint_t3.id3c = new.id3c)); 2539insert into rule_and_refint_t3 values (1, 11, 13, 'row7'); 2540ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey1" 2541DETAIL: Key (id3a, id3c)=(1, 13) is not present in table "rule_and_refint_t2". 2542insert into rule_and_refint_t3 values (1, 13, 11, 'row8'); 2543ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey" 2544DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1". 2545-- 2546-- disallow dropping a view's rule (bug #5072) 2547-- 2548create view rules_fooview as select 'rules_foo'::text; 2549drop rule "_RETURN" on rules_fooview; 2550ERROR: cannot drop rule _RETURN on view rules_fooview because view rules_fooview requires it 2551HINT: You can drop view rules_fooview instead. 2552drop view rules_fooview; 2553-- 2554-- test conversion of table to view (needed to load some pg_dump files) 2555-- 2556create table rules_fooview (x int, y text); 2557select xmin, * from rules_fooview; 2558 xmin | x | y 2559------+---+--- 2560(0 rows) 2561 2562create rule "_RETURN" as on select to rules_fooview do instead 2563 select 1 as x, 'aaa'::text as y; 2564select * from rules_fooview; 2565 x | y 2566---+----- 2567 1 | aaa 2568(1 row) 2569 2570select xmin, * from rules_fooview; -- fail, views don't have such a column 2571ERROR: column "xmin" does not exist 2572LINE 1: select xmin, * from rules_fooview; 2573 ^ 2574select reltoastrelid, relkind, relfrozenxid 2575 from pg_class where oid = 'rules_fooview'::regclass; 2576 reltoastrelid | relkind | relfrozenxid 2577---------------+---------+-------------- 2578 0 | v | 0 2579(1 row) 2580 2581drop view rules_fooview; 2582-- cannot convert an inheritance parent or child to a view, though 2583create table rules_fooview (x int, y text); 2584create table rules_fooview_child () inherits (rules_fooview); 2585create rule "_RETURN" as on select to rules_fooview do instead 2586 select 1 as x, 'aaa'::text as y; 2587ERROR: could not convert table "rules_fooview" to a view because it has child tables 2588create rule "_RETURN" as on select to rules_fooview_child do instead 2589 select 1 as x, 'aaa'::text as y; 2590ERROR: could not convert table "rules_fooview_child" to a view because it has parent tables 2591drop table rules_fooview cascade; 2592NOTICE: drop cascades to table rules_fooview_child 2593-- likewise, converting a partitioned table or partition to view is not allowed 2594create table rules_fooview (x int, y text) partition by list (x); 2595create rule "_RETURN" as on select to rules_fooview do instead 2596 select 1 as x, 'aaa'::text as y; 2597ERROR: cannot convert partitioned table "rules_fooview" to a view 2598create table rules_fooview_part partition of rules_fooview for values in (1); 2599create rule "_RETURN" as on select to rules_fooview_part do instead 2600 select 1 as x, 'aaa'::text as y; 2601ERROR: cannot convert partition "rules_fooview_part" to a view 2602drop table rules_fooview; 2603-- 2604-- check for planner problems with complex inherited UPDATES 2605-- 2606create table id (id serial primary key, name text); 2607-- currently, must respecify PKEY for each inherited subtable 2608create table test_1 (id integer primary key) inherits (id); 2609NOTICE: merging column "id" with inherited definition 2610create table test_2 (id integer primary key) inherits (id); 2611NOTICE: merging column "id" with inherited definition 2612create table test_3 (id integer primary key) inherits (id); 2613NOTICE: merging column "id" with inherited definition 2614insert into test_1 (name) values ('Test 1'); 2615insert into test_1 (name) values ('Test 2'); 2616insert into test_2 (name) values ('Test 3'); 2617insert into test_2 (name) values ('Test 4'); 2618insert into test_3 (name) values ('Test 5'); 2619insert into test_3 (name) values ('Test 6'); 2620create view id_ordered as select * from id order by id; 2621create rule update_id_ordered as on update to id_ordered 2622 do instead update id set name = new.name where id = old.id; 2623select * from id_ordered; 2624 id | name 2625----+-------- 2626 1 | Test 1 2627 2 | Test 2 2628 3 | Test 3 2629 4 | Test 4 2630 5 | Test 5 2631 6 | Test 6 2632(6 rows) 2633 2634update id_ordered set name = 'update 2' where id = 2; 2635update id_ordered set name = 'update 4' where id = 4; 2636update id_ordered set name = 'update 5' where id = 5; 2637select * from id_ordered; 2638 id | name 2639----+---------- 2640 1 | Test 1 2641 2 | update 2 2642 3 | Test 3 2643 4 | update 4 2644 5 | update 5 2645 6 | Test 6 2646(6 rows) 2647 2648\set VERBOSITY terse \\ -- suppress cascade details 2649drop table id cascade; 2650NOTICE: drop cascades to 4 other objects 2651\set VERBOSITY default 2652-- 2653-- check corner case where an entirely-dummy subplan is created by 2654-- constraint exclusion 2655-- 2656create temp table t1 (a integer primary key); 2657create temp table t1_1 (check (a >= 0 and a < 10)) inherits (t1); 2658create temp table t1_2 (check (a >= 10 and a < 20)) inherits (t1); 2659create rule t1_ins_1 as on insert to t1 2660 where new.a >= 0 and new.a < 10 2661 do instead 2662 insert into t1_1 values (new.a); 2663create rule t1_ins_2 as on insert to t1 2664 where new.a >= 10 and new.a < 20 2665 do instead 2666 insert into t1_2 values (new.a); 2667create rule t1_upd_1 as on update to t1 2668 where old.a >= 0 and old.a < 10 2669 do instead 2670 update t1_1 set a = new.a where a = old.a; 2671create rule t1_upd_2 as on update to t1 2672 where old.a >= 10 and old.a < 20 2673 do instead 2674 update t1_2 set a = new.a where a = old.a; 2675set constraint_exclusion = on; 2676insert into t1 select * from generate_series(5,19,1) g; 2677update t1 set a = 4 where a = 5; 2678select * from only t1; 2679 a 2680--- 2681(0 rows) 2682 2683select * from only t1_1; 2684 a 2685--- 2686 6 2687 7 2688 8 2689 9 2690 4 2691(5 rows) 2692 2693select * from only t1_2; 2694 a 2695---- 2696 10 2697 11 2698 12 2699 13 2700 14 2701 15 2702 16 2703 17 2704 18 2705 19 2706(10 rows) 2707 2708reset constraint_exclusion; 2709-- test FOR UPDATE in rules 2710create table rules_base(f1 int, f2 int); 2711insert into rules_base values(1,2), (11,12); 2712create rule r1 as on update to rules_base do instead 2713 select * from rules_base where f1 = 1 for update; 2714update rules_base set f2 = f2 + 1; 2715 f1 | f2 2716----+---- 2717 1 | 2 2718(1 row) 2719 2720create or replace rule r1 as on update to rules_base do instead 2721 select * from rules_base where f1 = 11 for update of rules_base; 2722update rules_base set f2 = f2 + 1; 2723 f1 | f2 2724----+---- 2725 11 | 12 2726(1 row) 2727 2728create or replace rule r1 as on update to rules_base do instead 2729 select * from rules_base where f1 = 11 for update of old; -- error 2730ERROR: relation "old" in FOR UPDATE clause not found in FROM clause 2731LINE 2: select * from rules_base where f1 = 11 for update of old; 2732 ^ 2733drop table rules_base; 2734-- test various flavors of pg_get_viewdef() 2735select pg_get_viewdef('shoe'::regclass) as unpretty; 2736 unpretty 2737------------------------------------------------ 2738 SELECT sh.shoename, + 2739 sh.sh_avail, + 2740 sh.slcolor, + 2741 sh.slminlen, + 2742 (sh.slminlen * un.un_fact) AS slminlen_cm,+ 2743 sh.slmaxlen, + 2744 (sh.slmaxlen * un.un_fact) AS slmaxlen_cm,+ 2745 sh.slunit + 2746 FROM shoe_data sh, + 2747 unit un + 2748 WHERE (sh.slunit = un.un_name); 2749(1 row) 2750 2751select pg_get_viewdef('shoe'::regclass,true) as pretty; 2752 pretty 2753---------------------------------------------- 2754 SELECT sh.shoename, + 2755 sh.sh_avail, + 2756 sh.slcolor, + 2757 sh.slminlen, + 2758 sh.slminlen * un.un_fact AS slminlen_cm,+ 2759 sh.slmaxlen, + 2760 sh.slmaxlen * un.un_fact AS slmaxlen_cm,+ 2761 sh.slunit + 2762 FROM shoe_data sh, + 2763 unit un + 2764 WHERE sh.slunit = un.un_name; 2765(1 row) 2766 2767select pg_get_viewdef('shoe'::regclass,0) as prettier; 2768 prettier 2769---------------------------------------------- 2770 SELECT sh.shoename, + 2771 sh.sh_avail, + 2772 sh.slcolor, + 2773 sh.slminlen, + 2774 sh.slminlen * un.un_fact AS slminlen_cm,+ 2775 sh.slmaxlen, + 2776 sh.slmaxlen * un.un_fact AS slmaxlen_cm,+ 2777 sh.slunit + 2778 FROM shoe_data sh, + 2779 unit un + 2780 WHERE sh.slunit = un.un_name; 2781(1 row) 2782 2783-- 2784-- check multi-row VALUES in rules 2785-- 2786create table rules_src(f1 int, f2 int); 2787create table rules_log(f1 int, f2 int, tag text); 2788insert into rules_src values(1,2), (11,12); 2789create rule r1 as on update to rules_src do also 2790 insert into rules_log values(old.*, 'old'), (new.*, 'new'); 2791update rules_src set f2 = f2 + 1; 2792update rules_src set f2 = f2 * 10; 2793select * from rules_src; 2794 f1 | f2 2795----+----- 2796 1 | 30 2797 11 | 130 2798(2 rows) 2799 2800select * from rules_log; 2801 f1 | f2 | tag 2802----+-----+----- 2803 1 | 2 | old 2804 1 | 3 | new 2805 11 | 12 | old 2806 11 | 13 | new 2807 1 | 3 | old 2808 1 | 30 | new 2809 11 | 13 | old 2810 11 | 130 | new 2811(8 rows) 2812 2813create rule r2 as on update to rules_src do also 2814 values(old.*, 'old'), (new.*, 'new'); 2815update rules_src set f2 = f2 / 10; 2816 column1 | column2 | column3 2817---------+---------+--------- 2818 1 | 30 | old 2819 1 | 3 | new 2820 11 | 130 | old 2821 11 | 13 | new 2822(4 rows) 2823 2824select * from rules_src; 2825 f1 | f2 2826----+---- 2827 1 | 3 2828 11 | 13 2829(2 rows) 2830 2831select * from rules_log; 2832 f1 | f2 | tag 2833----+-----+----- 2834 1 | 2 | old 2835 1 | 3 | new 2836 11 | 12 | old 2837 11 | 13 | new 2838 1 | 3 | old 2839 1 | 30 | new 2840 11 | 13 | old 2841 11 | 130 | new 2842 1 | 30 | old 2843 1 | 3 | new 2844 11 | 130 | old 2845 11 | 13 | new 2846(12 rows) 2847 2848create rule r3 as on delete to rules_src do notify rules_src_deletion; 2849\d+ rules_src 2850 Table "public.rules_src" 2851 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 2852--------+---------+-----------+----------+---------+---------+--------------+------------- 2853 f1 | integer | | | | plain | | 2854 f2 | integer | | | | plain | | 2855Rules: 2856 r1 AS 2857 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) 2858 r2 AS 2859 ON UPDATE TO rules_src DO VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text) 2860 r3 AS 2861 ON DELETE TO rules_src DO 2862 NOTIFY rules_src_deletion 2863 2864-- 2865-- Ensure an aliased target relation for insert is correctly deparsed. 2866-- 2867create rule r4 as on insert to rules_src do instead insert into rules_log AS trgt SELECT NEW.* RETURNING trgt.f1, trgt.f2; 2868create rule r5 as on update to rules_src do instead UPDATE rules_log AS trgt SET tag = 'updated' WHERE trgt.f1 = new.f1; 2869\d+ rules_src 2870 Table "public.rules_src" 2871 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 2872--------+---------+-----------+----------+---------+---------+--------------+------------- 2873 f1 | integer | | | | plain | | 2874 f2 | integer | | | | plain | | 2875Rules: 2876 r1 AS 2877 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) 2878 r2 AS 2879 ON UPDATE TO rules_src DO VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text) 2880 r3 AS 2881 ON DELETE TO rules_src DO 2882 NOTIFY rules_src_deletion 2883 r4 AS 2884 ON INSERT TO rules_src DO INSTEAD INSERT INTO rules_log AS trgt (f1, f2) SELECT new.f1, 2885 new.f2 2886 RETURNING trgt.f1, 2887 trgt.f2 2888 r5 AS 2889 ON UPDATE TO rules_src DO INSTEAD UPDATE rules_log trgt SET tag = 'updated'::text 2890 WHERE trgt.f1 = new.f1 2891 2892-- 2893-- check alter rename rule 2894-- 2895CREATE TABLE rule_t1 (a INT); 2896CREATE VIEW rule_v1 AS SELECT * FROM rule_t1; 2897CREATE RULE InsertRule AS 2898 ON INSERT TO rule_v1 2899 DO INSTEAD 2900 INSERT INTO rule_t1 VALUES(new.a); 2901ALTER RULE InsertRule ON rule_v1 RENAME to NewInsertRule; 2902INSERT INTO rule_v1 VALUES(1); 2903SELECT * FROM rule_v1; 2904 a 2905--- 2906 1 2907(1 row) 2908 2909\d+ rule_v1 2910 View "public.rule_v1" 2911 Column | Type | Collation | Nullable | Default | Storage | Description 2912--------+---------+-----------+----------+---------+---------+------------- 2913 a | integer | | | | plain | 2914View definition: 2915 SELECT rule_t1.a 2916 FROM rule_t1; 2917Rules: 2918 newinsertrule AS 2919 ON INSERT TO rule_v1 DO INSTEAD INSERT INTO rule_t1 (a) 2920 VALUES (new.a) 2921 2922-- 2923-- error conditions for alter rename rule 2924-- 2925ALTER RULE InsertRule ON rule_v1 RENAME TO NewInsertRule; -- doesn't exist 2926ERROR: rule "insertrule" for relation "rule_v1" does not exist 2927ALTER RULE NewInsertRule ON rule_v1 RENAME TO "_RETURN"; -- already exists 2928ERROR: rule "_RETURN" for relation "rule_v1" already exists 2929ALTER RULE "_RETURN" ON rule_v1 RENAME TO abc; -- ON SELECT rule cannot be renamed 2930ERROR: renaming an ON SELECT rule is not allowed 2931DROP VIEW rule_v1; 2932DROP TABLE rule_t1; 2933-- 2934-- check display of VALUES in view definitions 2935-- 2936create view rule_v1 as values(1,2); 2937\d+ rule_v1 2938 View "public.rule_v1" 2939 Column | Type | Collation | Nullable | Default | Storage | Description 2940---------+---------+-----------+----------+---------+---------+------------- 2941 column1 | integer | | | | plain | 2942 column2 | integer | | | | plain | 2943View definition: 2944 VALUES (1,2); 2945 2946alter table rule_v1 rename column column2 to q2; 2947\d+ rule_v1 2948 View "public.rule_v1" 2949 Column | Type | Collation | Nullable | Default | Storage | Description 2950---------+---------+-----------+----------+---------+---------+------------- 2951 column1 | integer | | | | plain | 2952 q2 | integer | | | | plain | 2953View definition: 2954 SELECT "*VALUES*".column1, 2955 "*VALUES*".column2 AS q2 2956 FROM (VALUES (1,2)) "*VALUES*"; 2957 2958drop view rule_v1; 2959create view rule_v1(x) as values(1,2); 2960\d+ rule_v1 2961 View "public.rule_v1" 2962 Column | Type | Collation | Nullable | Default | Storage | Description 2963---------+---------+-----------+----------+---------+---------+------------- 2964 x | integer | | | | plain | 2965 column2 | integer | | | | plain | 2966View definition: 2967 SELECT "*VALUES*".column1 AS x, 2968 "*VALUES*".column2 2969 FROM (VALUES (1,2)) "*VALUES*"; 2970 2971drop view rule_v1; 2972create view rule_v1(x) as select * from (values(1,2)) v; 2973\d+ rule_v1 2974 View "public.rule_v1" 2975 Column | Type | Collation | Nullable | Default | Storage | Description 2976---------+---------+-----------+----------+---------+---------+------------- 2977 x | integer | | | | plain | 2978 column2 | integer | | | | plain | 2979View definition: 2980 SELECT v.column1 AS x, 2981 v.column2 2982 FROM ( VALUES (1,2)) v; 2983 2984drop view rule_v1; 2985create view rule_v1(x) as select * from (values(1,2)) v(q,w); 2986\d+ rule_v1 2987 View "public.rule_v1" 2988 Column | Type | Collation | Nullable | Default | Storage | Description 2989--------+---------+-----------+----------+---------+---------+------------- 2990 x | integer | | | | plain | 2991 w | integer | | | | plain | 2992View definition: 2993 SELECT v.q AS x, 2994 v.w 2995 FROM ( VALUES (1,2)) v(q, w); 2996 2997drop view rule_v1; 2998-- 2999-- Check DO INSTEAD rules with ON CONFLICT 3000-- 3001CREATE TABLE hats ( 3002 hat_name char(10) primary key, 3003 hat_color char(10) -- hat color 3004); 3005CREATE TABLE hat_data ( 3006 hat_name char(10), 3007 hat_color char(10) -- hat color 3008); 3009create unique index hat_data_unique_idx 3010 on hat_data (hat_name COLLATE "C" bpchar_pattern_ops); 3011-- DO NOTHING with ON CONFLICT 3012CREATE RULE hat_nosert AS ON INSERT TO hats 3013 DO INSTEAD 3014 INSERT INTO hat_data VALUES ( 3015 NEW.hat_name, 3016 NEW.hat_color) 3017 ON CONFLICT (hat_name COLLATE "C" bpchar_pattern_ops) WHERE hat_color = 'green' 3018 DO NOTHING 3019 RETURNING *; 3020SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename; 3021 definition 3022--------------------------------------------------------------------------------------------- 3023 CREATE RULE hat_nosert AS + 3024 ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) + 3025 VALUES (new.hat_name, new.hat_color) ON CONFLICT(hat_name COLLATE "C" bpchar_pattern_ops)+ 3026 WHERE (hat_color = 'green'::bpchar) DO NOTHING + 3027 RETURNING hat_data.hat_name, + 3028 hat_data.hat_color; 3029(1 row) 3030 3031-- Works (projects row) 3032INSERT INTO hats VALUES ('h7', 'black') RETURNING *; 3033 hat_name | hat_color 3034------------+------------ 3035 h7 | black 3036(1 row) 3037 3038-- Works (does nothing) 3039INSERT INTO hats VALUES ('h7', 'black') RETURNING *; 3040 hat_name | hat_color 3041----------+----------- 3042(0 rows) 3043 3044SELECT tablename, rulename, definition FROM pg_rules 3045 WHERE tablename = 'hats'; 3046 tablename | rulename | definition 3047-----------+------------+--------------------------------------------------------------------------------------------- 3048 hats | hat_nosert | CREATE RULE hat_nosert AS + 3049 | | ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) + 3050 | | VALUES (new.hat_name, new.hat_color) ON CONFLICT(hat_name COLLATE "C" bpchar_pattern_ops)+ 3051 | | WHERE (hat_color = 'green'::bpchar) DO NOTHING + 3052 | | RETURNING hat_data.hat_name, + 3053 | | hat_data.hat_color; 3054(1 row) 3055 3056DROP RULE hat_nosert ON hats; 3057-- DO NOTHING without ON CONFLICT 3058CREATE RULE hat_nosert_all AS ON INSERT TO hats 3059 DO INSTEAD 3060 INSERT INTO hat_data VALUES ( 3061 NEW.hat_name, 3062 NEW.hat_color) 3063 ON CONFLICT 3064 DO NOTHING 3065 RETURNING *; 3066SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename; 3067 definition 3068------------------------------------------------------------------------------------- 3069 CREATE RULE hat_nosert_all AS + 3070 ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color)+ 3071 VALUES (new.hat_name, new.hat_color) ON CONFLICT DO NOTHING + 3072 RETURNING hat_data.hat_name, + 3073 hat_data.hat_color; 3074(1 row) 3075 3076DROP RULE hat_nosert_all ON hats; 3077-- Works (does nothing) 3078INSERT INTO hats VALUES ('h7', 'black') RETURNING *; 3079 hat_name | hat_color 3080------------+------------ 3081 h7 | black 3082(1 row) 3083 3084-- DO UPDATE with a WHERE clause 3085CREATE RULE hat_upsert AS ON INSERT TO hats 3086 DO INSTEAD 3087 INSERT INTO hat_data VALUES ( 3088 NEW.hat_name, 3089 NEW.hat_color) 3090 ON CONFLICT (hat_name) 3091 DO UPDATE 3092 SET hat_name = hat_data.hat_name, hat_color = excluded.hat_color 3093 WHERE excluded.hat_color <> 'forbidden' AND hat_data.* != excluded.* 3094 RETURNING *; 3095SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename; 3096 definition 3097----------------------------------------------------------------------------------------------------------------------------------------- 3098 CREATE RULE hat_upsert AS + 3099 ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) + 3100 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+ 3101 WHERE ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*)) + 3102 RETURNING hat_data.hat_name, + 3103 hat_data.hat_color; 3104(1 row) 3105 3106-- Works (does upsert) 3107INSERT INTO hats VALUES ('h8', 'black') RETURNING *; 3108 hat_name | hat_color 3109------------+------------ 3110 h8 | black 3111(1 row) 3112 3113SELECT * FROM hat_data WHERE hat_name = 'h8'; 3114 hat_name | hat_color 3115------------+------------ 3116 h8 | black 3117(1 row) 3118 3119INSERT INTO hats VALUES ('h8', 'white') RETURNING *; 3120 hat_name | hat_color 3121------------+------------ 3122 h8 | white 3123(1 row) 3124 3125SELECT * FROM hat_data WHERE hat_name = 'h8'; 3126 hat_name | hat_color 3127------------+------------ 3128 h8 | white 3129(1 row) 3130 3131INSERT INTO hats VALUES ('h8', 'forbidden') RETURNING *; 3132 hat_name | hat_color 3133----------+----------- 3134(0 rows) 3135 3136SELECT * FROM hat_data WHERE hat_name = 'h8'; 3137 hat_name | hat_color 3138------------+------------ 3139 h8 | white 3140(1 row) 3141 3142SELECT tablename, rulename, definition FROM pg_rules 3143 WHERE tablename = 'hats'; 3144 tablename | rulename | definition 3145-----------+------------+----------------------------------------------------------------------------------------------------------------------------------------- 3146 hats | hat_upsert | CREATE RULE hat_upsert AS + 3147 | | ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) + 3148 | | 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+ 3149 | | WHERE ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*)) + 3150 | | RETURNING hat_data.hat_name, + 3151 | | hat_data.hat_color; 3152(1 row) 3153 3154-- ensure explain works for on insert conflict rules 3155explain (costs off) INSERT INTO hats VALUES ('h8', 'forbidden') RETURNING *; 3156 QUERY PLAN 3157------------------------------------------------------------------------------------------------- 3158 Insert on hat_data 3159 Conflict Resolution: UPDATE 3160 Conflict Arbiter Indexes: hat_data_unique_idx 3161 Conflict Filter: ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*)) 3162 -> Result 3163(5 rows) 3164 3165-- ensure upserting into a rule, with a CTE (different offsets!) works 3166WITH data(hat_name, hat_color) AS ( 3167 VALUES ('h8', 'green'), 3168 ('h9', 'blue'), 3169 ('h7', 'forbidden') 3170) 3171INSERT INTO hats 3172 SELECT * FROM data 3173RETURNING *; 3174 hat_name | hat_color 3175------------+------------ 3176 h8 | green 3177 h9 | blue 3178(2 rows) 3179 3180EXPLAIN (costs off) WITH data(hat_name, hat_color) AS ( 3181 VALUES ('h8', 'green'), 3182 ('h9', 'blue'), 3183 ('h7', 'forbidden') 3184) 3185INSERT INTO hats 3186 SELECT * FROM data 3187RETURNING *; 3188 QUERY PLAN 3189------------------------------------------------------------------------------------------------- 3190 Insert on hat_data 3191 Conflict Resolution: UPDATE 3192 Conflict Arbiter Indexes: hat_data_unique_idx 3193 Conflict Filter: ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*)) 3194 CTE data 3195 -> Values Scan on "*VALUES*" 3196 -> CTE Scan on data 3197(7 rows) 3198 3199SELECT * FROM hat_data WHERE hat_name IN ('h8', 'h9', 'h7') ORDER BY hat_name; 3200 hat_name | hat_color 3201------------+------------ 3202 h7 | black 3203 h8 | green 3204 h9 | blue 3205(3 rows) 3206 3207DROP RULE hat_upsert ON hats; 3208drop table hats; 3209drop table hat_data; 3210-- test for pg_get_functiondef properly regurgitating SET parameters 3211-- Note that the function is kept around to stress pg_dump. 3212CREATE FUNCTION func_with_set_params() RETURNS integer 3213 AS 'select 1;' 3214 LANGUAGE SQL 3215 SET search_path TO PG_CATALOG 3216 SET extra_float_digits TO 2 3217 SET work_mem TO '4MB' 3218 SET datestyle to iso, mdy 3219 SET local_preload_libraries TO "Mixed/Case", 'c:/''a"/path', '', '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789' 3220 IMMUTABLE STRICT; 3221SELECT pg_get_functiondef('func_with_set_params()'::regprocedure); 3222 pg_get_functiondef 3223-------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 3224 CREATE OR REPLACE FUNCTION public.func_with_set_params() + 3225 RETURNS integer + 3226 LANGUAGE sql + 3227 IMMUTABLE STRICT + 3228 SET search_path TO 'pg_catalog' + 3229 SET extra_float_digits TO '2' + 3230 SET work_mem TO '4MB' + 3231 SET "DateStyle" TO 'iso, mdy' + 3232 SET local_preload_libraries TO 'Mixed/Case', 'c:/''a"/path', '', '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789'+ 3233 AS $function$select 1;$function$ + 3234 3235(1 row) 3236 3237-- tests for pg_get_*def with invalid objects 3238SELECT pg_get_constraintdef(0); 3239 pg_get_constraintdef 3240---------------------- 3241 3242(1 row) 3243 3244SELECT pg_get_functiondef(0); 3245 pg_get_functiondef 3246-------------------- 3247 3248(1 row) 3249 3250SELECT pg_get_indexdef(0); 3251 pg_get_indexdef 3252----------------- 3253 3254(1 row) 3255 3256SELECT pg_get_ruledef(0); 3257 pg_get_ruledef 3258---------------- 3259 3260(1 row) 3261 3262SELECT pg_get_statisticsobjdef(0); 3263 pg_get_statisticsobjdef 3264------------------------- 3265 3266(1 row) 3267 3268SELECT pg_get_triggerdef(0); 3269 pg_get_triggerdef 3270------------------- 3271 3272(1 row) 3273 3274SELECT pg_get_viewdef(0); 3275 pg_get_viewdef 3276---------------- 3277 3278(1 row) 3279 3280SELECT pg_get_function_arguments(0); 3281 pg_get_function_arguments 3282--------------------------- 3283 3284(1 row) 3285 3286SELECT pg_get_function_identity_arguments(0); 3287 pg_get_function_identity_arguments 3288------------------------------------ 3289 3290(1 row) 3291 3292SELECT pg_get_function_result(0); 3293 pg_get_function_result 3294------------------------ 3295 3296(1 row) 3297 3298SELECT pg_get_function_arg_default(0, 0); 3299 pg_get_function_arg_default 3300----------------------------- 3301 3302(1 row) 3303 3304SELECT pg_get_function_arg_default('pg_class'::regclass, 0); 3305 pg_get_function_arg_default 3306----------------------------- 3307 3308(1 row) 3309 3310SELECT pg_get_partkeydef(0); 3311 pg_get_partkeydef 3312------------------- 3313 3314(1 row) 3315 3316-- test rename for a rule defined on a partitioned table 3317CREATE TABLE rules_parted_table (a int) PARTITION BY LIST (a); 3318CREATE TABLE rules_parted_table_1 PARTITION OF rules_parted_table FOR VALUES IN (1); 3319CREATE RULE rules_parted_table_insert AS ON INSERT to rules_parted_table 3320 DO INSTEAD INSERT INTO rules_parted_table_1 VALUES (NEW.*); 3321ALTER RULE rules_parted_table_insert ON rules_parted_table RENAME TO rules_parted_table_insert_redirect; 3322DROP TABLE rules_parted_table; 3323-- 3324-- Test enabling/disabling 3325-- 3326CREATE TABLE ruletest1 (a int); 3327CREATE TABLE ruletest2 (b int); 3328CREATE RULE rule1 AS ON INSERT TO ruletest1 3329 DO INSTEAD INSERT INTO ruletest2 VALUES (NEW.*); 3330INSERT INTO ruletest1 VALUES (1); 3331ALTER TABLE ruletest1 DISABLE RULE rule1; 3332INSERT INTO ruletest1 VALUES (2); 3333ALTER TABLE ruletest1 ENABLE RULE rule1; 3334SET session_replication_role = replica; 3335INSERT INTO ruletest1 VALUES (3); 3336ALTER TABLE ruletest1 ENABLE REPLICA RULE rule1; 3337INSERT INTO ruletest1 VALUES (4); 3338RESET session_replication_role; 3339INSERT INTO ruletest1 VALUES (5); 3340SELECT * FROM ruletest1; 3341 a 3342--- 3343 2 3344 3 3345 5 3346(3 rows) 3347 3348SELECT * FROM ruletest2; 3349 b 3350--- 3351 1 3352 4 3353(2 rows) 3354 3355DROP TABLE ruletest1; 3356DROP TABLE ruletest2; 3357