1-- 2-- RULES 3-- From Jan's original setup_ruletest.sql and run_ruletest.sql 4-- - thomas 1998-09-13 5-- 6-- 7-- Tables and rules for the view test 8-- 9create table rtest_t1 (a int4, b int4); 10create table rtest_t2 (a int4, b int4); 11create table rtest_t3 (a int4, b int4); 12create view rtest_v1 as select * from rtest_t1; 13create rule rtest_v1_ins as on insert to rtest_v1 do instead 14 insert into rtest_t1 values (new.a, new.b); 15create rule rtest_v1_upd as on update to rtest_v1 do instead 16 update rtest_t1 set a = new.a, b = new.b 17 where a = old.a; 18create rule rtest_v1_del as on delete to rtest_v1 do instead 19 delete from rtest_t1 where a = old.a; 20-- Test comments 21COMMENT ON RULE rtest_v1_bad ON rtest_v1 IS 'bad rule'; 22ERROR: rule "rtest_v1_bad" for relation "rtest_v1" does not exist 23COMMENT ON RULE rtest_v1_del ON rtest_v1 IS 'delete rule'; 24COMMENT ON RULE rtest_v1_del ON rtest_v1 IS NULL; 25-- 26-- Tables and rules for the constraint update/delete test 27-- 28-- Note: 29-- Now that we have multiple action rule support, we check 30-- both possible syntaxes to define them (The last action 31-- can but must not have a semicolon at the end). 32-- 33create table rtest_system (sysname text, sysdesc text); 34create table rtest_interface (sysname text, ifname text); 35create table rtest_person (pname text, pdesc text); 36create table rtest_admin (pname text, sysname text); 37create rule rtest_sys_upd as on update to rtest_system do also ( 38 update rtest_interface set sysname = new.sysname 39 where sysname = old.sysname; 40 update rtest_admin set sysname = new.sysname 41 where sysname = old.sysname 42 ); 43create rule rtest_sys_del as on delete to rtest_system do also ( 44 delete from rtest_interface where sysname = old.sysname; 45 delete from rtest_admin where sysname = old.sysname; 46 ); 47create rule rtest_pers_upd as on update to rtest_person do also 48 update rtest_admin set pname = new.pname where pname = old.pname; 49create rule rtest_pers_del as on delete to rtest_person do also 50 delete from rtest_admin where pname = old.pname; 51-- 52-- Tables and rules for the logging test 53-- 54create table rtest_emp (ename char(20), salary money); 55create table rtest_emplog (ename char(20), who name, action char(10), newsal money, oldsal money); 56create table rtest_empmass (ename char(20), salary money); 57create rule rtest_emp_ins as on insert to rtest_emp do 58 insert into rtest_emplog values (new.ename, current_user, 59 'hired', new.salary, '0.00'); 60create rule rtest_emp_upd as on update to rtest_emp where new.salary != old.salary do 61 insert into rtest_emplog values (new.ename, current_user, 62 'honored', new.salary, old.salary); 63create rule rtest_emp_del as on delete to rtest_emp do 64 insert into rtest_emplog values (old.ename, current_user, 65 'fired', '0.00', old.salary); 66-- 67-- Tables and rules for the multiple cascaded qualified instead 68-- rule test 69-- 70create table rtest_t4 (a int4, b text); 71create table rtest_t5 (a int4, b text); 72create table rtest_t6 (a int4, b text); 73create table rtest_t7 (a int4, b text); 74create table rtest_t8 (a int4, b text); 75create table rtest_t9 (a int4, b text); 76create rule rtest_t4_ins1 as on insert to rtest_t4 77 where new.a >= 10 and new.a < 20 do instead 78 insert into rtest_t5 values (new.a, new.b); 79create rule rtest_t4_ins2 as on insert to rtest_t4 80 where new.a >= 20 and new.a < 30 do 81 insert into rtest_t6 values (new.a, new.b); 82create rule rtest_t5_ins as on insert to rtest_t5 83 where new.a > 15 do 84 insert into rtest_t7 values (new.a, new.b); 85create rule rtest_t6_ins as on insert to rtest_t6 86 where new.a > 25 do instead 87 insert into rtest_t8 values (new.a, new.b); 88-- 89-- Tables and rules for the rule fire order test 90-- 91-- As of PG 7.3, the rules should fire in order by name, regardless 92-- of INSTEAD attributes or creation order. 93-- 94create table rtest_order1 (a int4); 95create table rtest_order2 (a int4, b int4, c text); 96create sequence rtest_seq; 97create rule rtest_order_r3 as on insert to rtest_order1 do instead 98 insert into rtest_order2 values (new.a, nextval('rtest_seq'), 99 'rule 3 - this should run 3rd'); 100create rule rtest_order_r4 as on insert to rtest_order1 101 where a < 100 do instead 102 insert into rtest_order2 values (new.a, nextval('rtest_seq'), 103 'rule 4 - this should run 4th'); 104create rule rtest_order_r2 as on insert to rtest_order1 do 105 insert into rtest_order2 values (new.a, nextval('rtest_seq'), 106 'rule 2 - this should run 2nd'); 107create rule rtest_order_r1 as on insert to rtest_order1 do instead 108 insert into rtest_order2 values (new.a, nextval('rtest_seq'), 109 'rule 1 - this should run 1st'); 110-- 111-- Tables and rules for the instead nothing test 112-- 113create table rtest_nothn1 (a int4, b text); 114create table rtest_nothn2 (a int4, b text); 115create table rtest_nothn3 (a int4, b text); 116create table rtest_nothn4 (a int4, b text); 117create rule rtest_nothn_r1 as on insert to rtest_nothn1 118 where new.a >= 10 and new.a < 20 do instead nothing; 119create rule rtest_nothn_r2 as on insert to rtest_nothn1 120 where new.a >= 30 and new.a < 40 do instead nothing; 121create rule rtest_nothn_r3 as on insert to rtest_nothn2 122 where new.a >= 100 do instead 123 insert into rtest_nothn3 values (new.a, new.b); 124create rule rtest_nothn_r4 as on insert to rtest_nothn2 125 do instead nothing; 126-- 127-- Tests on a view that is select * of a table 128-- and has insert/update/delete instead rules to 129-- behave close like the real table. 130-- 131-- 132-- We need test date later 133-- 134insert into rtest_t2 values (1, 21); 135insert into rtest_t2 values (2, 22); 136insert into rtest_t2 values (3, 23); 137insert into rtest_t3 values (1, 31); 138insert into rtest_t3 values (2, 32); 139insert into rtest_t3 values (3, 33); 140insert into rtest_t3 values (4, 34); 141insert into rtest_t3 values (5, 35); 142-- insert values 143insert into rtest_v1 values (1, 11); 144insert into rtest_v1 values (2, 12); 145select * from rtest_v1; 146 a | b 147---+---- 148 1 | 11 149 2 | 12 150(2 rows) 151 152-- delete with constant expression 153delete from rtest_v1 where a = 1; 154select * from rtest_v1; 155 a | b 156---+---- 157 2 | 12 158(1 row) 159 160insert into rtest_v1 values (1, 11); 161delete from rtest_v1 where b = 12; 162select * from rtest_v1; 163 a | b 164---+---- 165 1 | 11 166(1 row) 167 168insert into rtest_v1 values (2, 12); 169insert into rtest_v1 values (2, 13); 170select * from rtest_v1; 171 a | b 172---+---- 173 1 | 11 174 2 | 12 175 2 | 13 176(3 rows) 177 178** Remember the delete rule on rtest_v1: It says 179** DO INSTEAD DELETE FROM rtest_t1 WHERE a = old.a 180** So this time both rows with a = 2 must get deleted 181\p 182** Remember the delete rule on rtest_v1: It says 183** DO INSTEAD DELETE FROM rtest_t1 WHERE a = old.a 184** So this time both rows with a = 2 must get deleted 185\r 186delete from rtest_v1 where b = 12; 187select * from rtest_v1; 188 a | b 189---+---- 190 1 | 11 191(1 row) 192 193delete from rtest_v1; 194-- insert select 195insert into rtest_v1 select * from rtest_t2; 196select * from rtest_v1; 197 a | b 198---+---- 199 1 | 21 200 2 | 22 201 3 | 23 202(3 rows) 203 204delete from rtest_v1; 205-- same with swapped targetlist 206insert into rtest_v1 (b, a) select b, a from rtest_t2; 207select * from rtest_v1; 208 a | b 209---+---- 210 1 | 21 211 2 | 22 212 3 | 23 213(3 rows) 214 215-- now with only one target attribute 216insert into rtest_v1 (a) select a from rtest_t3; 217select * from rtest_v1; 218 a | b 219---+---- 220 1 | 21 221 2 | 22 222 3 | 23 223 1 | 224 2 | 225 3 | 226 4 | 227 5 | 228(8 rows) 229 230select * from rtest_v1 where b isnull; 231 a | b 232---+--- 233 1 | 234 2 | 235 3 | 236 4 | 237 5 | 238(5 rows) 239 240-- let attribute a differ (must be done on rtest_t1 - see above) 241update rtest_t1 set a = a + 10 where b isnull; 242delete from rtest_v1 where b isnull; 243select * from rtest_v1; 244 a | b 245---+---- 246 1 | 21 247 2 | 22 248 3 | 23 249(3 rows) 250 251-- now updates with constant expression 252update rtest_v1 set b = 42 where a = 2; 253select * from rtest_v1; 254 a | b 255---+---- 256 1 | 21 257 3 | 23 258 2 | 42 259(3 rows) 260 261update rtest_v1 set b = 99 where b = 42; 262select * from rtest_v1; 263 a | b 264---+---- 265 1 | 21 266 3 | 23 267 2 | 99 268(3 rows) 269 270update rtest_v1 set b = 88 where b < 50; 271select * from rtest_v1; 272 a | b 273---+---- 274 2 | 99 275 1 | 88 276 3 | 88 277(3 rows) 278 279delete from rtest_v1; 280insert into rtest_v1 select rtest_t2.a, rtest_t3.b 281 from rtest_t2, rtest_t3 282 where rtest_t2.a = rtest_t3.a; 283select * from rtest_v1; 284 a | b 285---+---- 286 1 | 31 287 2 | 32 288 3 | 33 289(3 rows) 290 291-- updates in a mergejoin 292update rtest_v1 set b = rtest_t2.b from rtest_t2 where rtest_v1.a = rtest_t2.a; 293select * from rtest_v1; 294 a | b 295---+---- 296 1 | 21 297 2 | 22 298 3 | 23 299(3 rows) 300 301insert into rtest_v1 select * from rtest_t3; 302select * from rtest_v1; 303 a | b 304---+---- 305 1 | 21 306 2 | 22 307 3 | 23 308 1 | 31 309 2 | 32 310 3 | 33 311 4 | 34 312 5 | 35 313(8 rows) 314 315update rtest_t1 set a = a + 10 where b > 30; 316select * from rtest_v1; 317 a | b 318----+---- 319 1 | 21 320 2 | 22 321 3 | 23 322 11 | 31 323 12 | 32 324 13 | 33 325 14 | 34 326 15 | 35 327(8 rows) 328 329update rtest_v1 set a = rtest_t3.a + 20 from rtest_t3 where rtest_v1.b = rtest_t3.b; 330select * from rtest_v1; 331 a | b 332----+---- 333 1 | 21 334 2 | 22 335 3 | 23 336 21 | 31 337 22 | 32 338 23 | 33 339 24 | 34 340 25 | 35 341(8 rows) 342 343-- 344-- Test for constraint updates/deletes 345-- 346insert into rtest_system values ('orion', 'Linux Jan Wieck'); 347insert into rtest_system values ('notjw', 'WinNT Jan Wieck (notebook)'); 348insert into rtest_system values ('neptun', 'Fileserver'); 349insert into rtest_interface values ('orion', 'eth0'); 350insert into rtest_interface values ('orion', 'eth1'); 351insert into rtest_interface values ('notjw', 'eth0'); 352insert into rtest_interface values ('neptun', 'eth0'); 353insert into rtest_person values ('jw', 'Jan Wieck'); 354insert into rtest_person values ('bm', 'Bruce Momjian'); 355insert into rtest_admin values ('jw', 'orion'); 356insert into rtest_admin values ('jw', 'notjw'); 357insert into rtest_admin values ('bm', 'neptun'); 358update rtest_system set sysname = 'pluto' where sysname = 'neptun'; 359select * from rtest_interface; 360 sysname | ifname 361---------+-------- 362 orion | eth0 363 orion | eth1 364 notjw | eth0 365 pluto | eth0 366(4 rows) 367 368select * from rtest_admin; 369 pname | sysname 370-------+--------- 371 jw | orion 372 jw | notjw 373 bm | pluto 374(3 rows) 375 376update rtest_person set pname = 'jwieck' where pdesc = 'Jan Wieck'; 377-- Note: use ORDER BY here to ensure consistent output across all systems. 378-- The above UPDATE affects two rows with equal keys, so they could be 379-- updated in either order depending on the whim of the local qsort(). 380select * from rtest_admin order by pname, sysname; 381 pname | sysname 382--------+--------- 383 bm | pluto 384 jwieck | notjw 385 jwieck | orion 386(3 rows) 387 388delete from rtest_system where sysname = 'orion'; 389select * from rtest_interface; 390 sysname | ifname 391---------+-------- 392 notjw | eth0 393 pluto | eth0 394(2 rows) 395 396select * from rtest_admin; 397 pname | sysname 398--------+--------- 399 bm | pluto 400 jwieck | notjw 401(2 rows) 402 403-- 404-- Rule qualification test 405-- 406insert into rtest_emp values ('wiecc', '5000.00'); 407insert into rtest_emp values ('gates', '80000.00'); 408update rtest_emp set ename = 'wiecx' where ename = 'wiecc'; 409update rtest_emp set ename = 'wieck', salary = '6000.00' where ename = 'wiecx'; 410update rtest_emp set salary = '7000.00' where ename = 'wieck'; 411delete from rtest_emp where ename = 'gates'; 412select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal; 413 ename | matches user | action | newsal | oldsal 414----------------------+--------------+------------+------------+------------ 415 gates | t | fired | $0.00 | $80,000.00 416 gates | t | hired | $80,000.00 | $0.00 417 wiecc | t | hired | $5,000.00 | $0.00 418 wieck | t | honored | $6,000.00 | $5,000.00 419 wieck | t | honored | $7,000.00 | $6,000.00 420(5 rows) 421 422insert into rtest_empmass values ('meyer', '4000.00'); 423insert into rtest_empmass values ('maier', '5000.00'); 424insert into rtest_empmass values ('mayr', '6000.00'); 425insert into rtest_emp select * from rtest_empmass; 426select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal; 427 ename | matches user | action | newsal | oldsal 428----------------------+--------------+------------+------------+------------ 429 gates | t | fired | $0.00 | $80,000.00 430 gates | t | hired | $80,000.00 | $0.00 431 maier | t | hired | $5,000.00 | $0.00 432 mayr | t | hired | $6,000.00 | $0.00 433 meyer | t | hired | $4,000.00 | $0.00 434 wiecc | t | hired | $5,000.00 | $0.00 435 wieck | t | honored | $6,000.00 | $5,000.00 436 wieck | t | honored | $7,000.00 | $6,000.00 437(8 rows) 438 439update rtest_empmass set salary = salary + '1000.00'; 440update rtest_emp set salary = rtest_empmass.salary from rtest_empmass where rtest_emp.ename = rtest_empmass.ename; 441select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal; 442 ename | matches user | action | newsal | oldsal 443----------------------+--------------+------------+------------+------------ 444 gates | t | fired | $0.00 | $80,000.00 445 gates | t | hired | $80,000.00 | $0.00 446 maier | t | hired | $5,000.00 | $0.00 447 maier | t | honored | $6,000.00 | $5,000.00 448 mayr | t | hired | $6,000.00 | $0.00 449 mayr | t | honored | $7,000.00 | $6,000.00 450 meyer | t | hired | $4,000.00 | $0.00 451 meyer | t | honored | $5,000.00 | $4,000.00 452 wiecc | t | hired | $5,000.00 | $0.00 453 wieck | t | honored | $6,000.00 | $5,000.00 454 wieck | t | honored | $7,000.00 | $6,000.00 455(11 rows) 456 457delete from rtest_emp using rtest_empmass where rtest_emp.ename = rtest_empmass.ename; 458select ename, who = current_user as "matches user", action, newsal, oldsal from rtest_emplog order by ename, action, newsal; 459 ename | matches user | action | newsal | oldsal 460----------------------+--------------+------------+------------+------------ 461 gates | t | fired | $0.00 | $80,000.00 462 gates | t | hired | $80,000.00 | $0.00 463 maier | t | fired | $0.00 | $6,000.00 464 maier | t | hired | $5,000.00 | $0.00 465 maier | t | honored | $6,000.00 | $5,000.00 466 mayr | t | fired | $0.00 | $7,000.00 467 mayr | t | hired | $6,000.00 | $0.00 468 mayr | t | honored | $7,000.00 | $6,000.00 469 meyer | t | fired | $0.00 | $5,000.00 470 meyer | t | hired | $4,000.00 | $0.00 471 meyer | t | honored | $5,000.00 | $4,000.00 472 wiecc | t | hired | $5,000.00 | $0.00 473 wieck | t | honored | $6,000.00 | $5,000.00 474 wieck | t | honored | $7,000.00 | $6,000.00 475(14 rows) 476 477-- 478-- Multiple cascaded qualified instead rule test 479-- 480insert into rtest_t4 values (1, 'Record should go to rtest_t4'); 481insert into rtest_t4 values (2, 'Record should go to rtest_t4'); 482insert into rtest_t4 values (10, 'Record should go to rtest_t5'); 483insert into rtest_t4 values (15, 'Record should go to rtest_t5'); 484insert into rtest_t4 values (19, 'Record should go to rtest_t5 and t7'); 485insert into rtest_t4 values (20, 'Record should go to rtest_t4 and t6'); 486insert into rtest_t4 values (26, 'Record should go to rtest_t4 and t8'); 487insert into rtest_t4 values (28, 'Record should go to rtest_t4 and t8'); 488insert into rtest_t4 values (30, 'Record should go to rtest_t4'); 489insert into rtest_t4 values (40, 'Record should go to rtest_t4'); 490select * from rtest_t4; 491 a | b 492----+------------------------------------- 493 1 | Record should go to rtest_t4 494 2 | Record should go to rtest_t4 495 20 | Record should go to rtest_t4 and t6 496 26 | Record should go to rtest_t4 and t8 497 28 | Record should go to rtest_t4 and t8 498 30 | Record should go to rtest_t4 499 40 | Record should go to rtest_t4 500(7 rows) 501 502select * from rtest_t5; 503 a | b 504----+------------------------------------- 505 10 | Record should go to rtest_t5 506 15 | Record should go to rtest_t5 507 19 | Record should go to rtest_t5 and t7 508(3 rows) 509 510select * from rtest_t6; 511 a | b 512----+------------------------------------- 513 20 | Record should go to rtest_t4 and t6 514(1 row) 515 516select * from rtest_t7; 517 a | b 518----+------------------------------------- 519 19 | Record should go to rtest_t5 and t7 520(1 row) 521 522select * from rtest_t8; 523 a | b 524----+------------------------------------- 525 26 | Record should go to rtest_t4 and t8 526 28 | Record should go to rtest_t4 and t8 527(2 rows) 528 529delete from rtest_t4; 530delete from rtest_t5; 531delete from rtest_t6; 532delete from rtest_t7; 533delete from rtest_t8; 534insert into rtest_t9 values (1, 'Record should go to rtest_t4'); 535insert into rtest_t9 values (2, 'Record should go to rtest_t4'); 536insert into rtest_t9 values (10, 'Record should go to rtest_t5'); 537insert into rtest_t9 values (15, 'Record should go to rtest_t5'); 538insert into rtest_t9 values (19, 'Record should go to rtest_t5 and t7'); 539insert into rtest_t9 values (20, 'Record should go to rtest_t4 and t6'); 540insert into rtest_t9 values (26, 'Record should go to rtest_t4 and t8'); 541insert into rtest_t9 values (28, 'Record should go to rtest_t4 and t8'); 542insert into rtest_t9 values (30, 'Record should go to rtest_t4'); 543insert into rtest_t9 values (40, 'Record should go to rtest_t4'); 544insert into rtest_t4 select * from rtest_t9 where a < 20; 545select * from rtest_t4; 546 a | b 547---+------------------------------ 548 1 | Record should go to rtest_t4 549 2 | Record should go to rtest_t4 550(2 rows) 551 552select * from rtest_t5; 553 a | b 554----+------------------------------------- 555 10 | Record should go to rtest_t5 556 15 | Record should go to rtest_t5 557 19 | Record should go to rtest_t5 and t7 558(3 rows) 559 560select * from rtest_t6; 561 a | b 562---+--- 563(0 rows) 564 565select * from rtest_t7; 566 a | b 567----+------------------------------------- 568 19 | Record should go to rtest_t5 and t7 569(1 row) 570 571select * from rtest_t8; 572 a | b 573---+--- 574(0 rows) 575 576insert into rtest_t4 select * from rtest_t9 where b ~ 'and t8'; 577select * from rtest_t4; 578 a | b 579----+------------------------------------- 580 1 | Record should go to rtest_t4 581 2 | Record should go to rtest_t4 582 26 | Record should go to rtest_t4 and t8 583 28 | Record should go to rtest_t4 and t8 584(4 rows) 585 586select * from rtest_t5; 587 a | b 588----+------------------------------------- 589 10 | Record should go to rtest_t5 590 15 | Record should go to rtest_t5 591 19 | Record should go to rtest_t5 and t7 592(3 rows) 593 594select * from rtest_t6; 595 a | b 596---+--- 597(0 rows) 598 599select * from rtest_t7; 600 a | b 601----+------------------------------------- 602 19 | Record should go to rtest_t5 and t7 603(1 row) 604 605select * from rtest_t8; 606 a | b 607----+------------------------------------- 608 26 | Record should go to rtest_t4 and t8 609 28 | Record should go to rtest_t4 and t8 610(2 rows) 611 612insert into rtest_t4 select a + 1, b from rtest_t9 where a in (20, 30, 40); 613select * from rtest_t4; 614 a | b 615----+------------------------------------- 616 1 | Record should go to rtest_t4 617 2 | Record should go to rtest_t4 618 26 | Record should go to rtest_t4 and t8 619 28 | Record should go to rtest_t4 and t8 620 21 | Record should go to rtest_t4 and t6 621 31 | Record should go to rtest_t4 622 41 | Record should go to rtest_t4 623(7 rows) 624 625select * from rtest_t5; 626 a | b 627----+------------------------------------- 628 10 | Record should go to rtest_t5 629 15 | Record should go to rtest_t5 630 19 | Record should go to rtest_t5 and t7 631(3 rows) 632 633select * from rtest_t6; 634 a | b 635----+------------------------------------- 636 21 | Record should go to rtest_t4 and t6 637(1 row) 638 639select * from rtest_t7; 640 a | b 641----+------------------------------------- 642 19 | Record should go to rtest_t5 and t7 643(1 row) 644 645select * from rtest_t8; 646 a | b 647----+------------------------------------- 648 26 | Record should go to rtest_t4 and t8 649 28 | Record should go to rtest_t4 and t8 650(2 rows) 651 652-- 653-- Check that the ordering of rules fired is correct 654-- 655insert into rtest_order1 values (1); 656select * from rtest_order2; 657 a | b | c 658---+---+------------------------------ 659 1 | 1 | rule 1 - this should run 1st 660 1 | 2 | rule 2 - this should run 2nd 661 1 | 3 | rule 3 - this should run 3rd 662 1 | 4 | rule 4 - this should run 4th 663(4 rows) 664 665-- 666-- Check if instead nothing w/without qualification works 667-- 668insert into rtest_nothn1 values (1, 'want this'); 669insert into rtest_nothn1 values (2, 'want this'); 670insert into rtest_nothn1 values (10, 'don''t want this'); 671insert into rtest_nothn1 values (19, 'don''t want this'); 672insert into rtest_nothn1 values (20, 'want this'); 673insert into rtest_nothn1 values (29, 'want this'); 674insert into rtest_nothn1 values (30, 'don''t want this'); 675insert into rtest_nothn1 values (39, 'don''t want this'); 676insert into rtest_nothn1 values (40, 'want this'); 677insert into rtest_nothn1 values (50, 'want this'); 678insert into rtest_nothn1 values (60, 'want this'); 679select * from rtest_nothn1; 680 a | b 681----+----------- 682 1 | want this 683 2 | want this 684 20 | want this 685 29 | want this 686 40 | want this 687 50 | want this 688 60 | want this 689(7 rows) 690 691insert into rtest_nothn2 values (10, 'too small'); 692insert into rtest_nothn2 values (50, 'too small'); 693insert into rtest_nothn2 values (100, 'OK'); 694insert into rtest_nothn2 values (200, 'OK'); 695select * from rtest_nothn2; 696 a | b 697---+--- 698(0 rows) 699 700select * from rtest_nothn3; 701 a | b 702-----+---- 703 100 | OK 704 200 | OK 705(2 rows) 706 707delete from rtest_nothn1; 708delete from rtest_nothn2; 709delete from rtest_nothn3; 710insert into rtest_nothn4 values (1, 'want this'); 711insert into rtest_nothn4 values (2, 'want this'); 712insert into rtest_nothn4 values (10, 'don''t want this'); 713insert into rtest_nothn4 values (19, 'don''t want this'); 714insert into rtest_nothn4 values (20, 'want this'); 715insert into rtest_nothn4 values (29, 'want this'); 716insert into rtest_nothn4 values (30, 'don''t want this'); 717insert into rtest_nothn4 values (39, 'don''t want this'); 718insert into rtest_nothn4 values (40, 'want this'); 719insert into rtest_nothn4 values (50, 'want this'); 720insert into rtest_nothn4 values (60, 'want this'); 721insert into rtest_nothn1 select * from rtest_nothn4; 722select * from rtest_nothn1; 723 a | b 724----+----------- 725 1 | want this 726 2 | want this 727 20 | want this 728 29 | want this 729 40 | want this 730 50 | want this 731 60 | want this 732(7 rows) 733 734delete from rtest_nothn4; 735insert into rtest_nothn4 values (10, 'too small'); 736insert into rtest_nothn4 values (50, 'too small'); 737insert into rtest_nothn4 values (100, 'OK'); 738insert into rtest_nothn4 values (200, 'OK'); 739insert into rtest_nothn2 select * from rtest_nothn4; 740select * from rtest_nothn2; 741 a | b 742---+--- 743(0 rows) 744 745select * from rtest_nothn3; 746 a | b 747-----+---- 748 100 | OK 749 200 | OK 750(2 rows) 751 752create table rtest_view1 (a int4, b text, v bool); 753create table rtest_view2 (a int4); 754create table rtest_view3 (a int4, b text); 755create table rtest_view4 (a int4, b text, c int4); 756create view rtest_vview1 as select a, b from rtest_view1 X 757 where 0 < (select count(*) from rtest_view2 Y where Y.a = X.a); 758create view rtest_vview2 as select a, b from rtest_view1 where v; 759create view rtest_vview3 as select a, b from rtest_vview2 X 760 where 0 < (select count(*) from rtest_view2 Y where Y.a = X.a); 761create view rtest_vview4 as select X.a, X.b, count(Y.a) as refcount 762 from rtest_view1 X, rtest_view2 Y 763 where X.a = Y.a 764 group by X.a, X.b; 765create function rtest_viewfunc1(int4) returns int4 as 766 'select count(*)::int4 from rtest_view2 where a = $1' 767 language sql; 768create view rtest_vview5 as select a, b, rtest_viewfunc1(a) as refcount 769 from rtest_view1; 770insert into rtest_view1 values (1, 'item 1', 't'); 771insert into rtest_view1 values (2, 'item 2', 't'); 772insert into rtest_view1 values (3, 'item 3', 't'); 773insert into rtest_view1 values (4, 'item 4', 'f'); 774insert into rtest_view1 values (5, 'item 5', 't'); 775insert into rtest_view1 values (6, 'item 6', 'f'); 776insert into rtest_view1 values (7, 'item 7', 't'); 777insert into rtest_view1 values (8, 'item 8', 't'); 778insert into rtest_view2 values (2); 779insert into rtest_view2 values (2); 780insert into rtest_view2 values (4); 781insert into rtest_view2 values (5); 782insert into rtest_view2 values (7); 783insert into rtest_view2 values (7); 784insert into rtest_view2 values (7); 785insert into rtest_view2 values (7); 786select * from rtest_vview1; 787 a | b 788---+-------- 789 2 | item 2 790 4 | item 4 791 5 | item 5 792 7 | item 7 793(4 rows) 794 795select * from rtest_vview2; 796 a | b 797---+-------- 798 1 | item 1 799 2 | item 2 800 3 | item 3 801 5 | item 5 802 7 | item 7 803 8 | item 8 804(6 rows) 805 806select * from rtest_vview3; 807 a | b 808---+-------- 809 2 | item 2 810 5 | item 5 811 7 | item 7 812(3 rows) 813 814select * from rtest_vview4 order by a, b; 815 a | b | refcount 816---+--------+---------- 817 2 | item 2 | 2 818 4 | item 4 | 1 819 5 | item 5 | 1 820 7 | item 7 | 4 821(4 rows) 822 823select * from rtest_vview5; 824 a | b | refcount 825---+--------+---------- 826 1 | item 1 | 0 827 2 | item 2 | 2 828 3 | item 3 | 0 829 4 | item 4 | 1 830 5 | item 5 | 1 831 6 | item 6 | 0 832 7 | item 7 | 4 833 8 | item 8 | 0 834(8 rows) 835 836insert into rtest_view3 select * from rtest_vview1 where a < 7; 837select * from rtest_view3; 838 a | b 839---+-------- 840 2 | item 2 841 4 | item 4 842 5 | item 5 843(3 rows) 844 845delete from rtest_view3; 846insert into rtest_view3 select * from rtest_vview2 where a != 5 and b !~ '2'; 847select * from rtest_view3; 848 a | b 849---+-------- 850 1 | item 1 851 3 | item 3 852 7 | item 7 853 8 | item 8 854(4 rows) 855 856delete from rtest_view3; 857insert into rtest_view3 select * from rtest_vview3; 858select * from rtest_view3; 859 a | b 860---+-------- 861 2 | item 2 862 5 | item 5 863 7 | item 7 864(3 rows) 865 866delete from rtest_view3; 867insert into rtest_view4 select * from rtest_vview4 where 3 > refcount; 868select * from rtest_view4 order by a, b; 869 a | b | c 870---+--------+--- 871 2 | item 2 | 2 872 4 | item 4 | 1 873 5 | item 5 | 1 874(3 rows) 875 876delete from rtest_view4; 877insert into rtest_view4 select * from rtest_vview5 where a > 2 and refcount = 0; 878select * from rtest_view4; 879 a | b | c 880---+--------+--- 881 3 | item 3 | 0 882 6 | item 6 | 0 883 8 | item 8 | 0 884(3 rows) 885 886delete from rtest_view4; 887-- 888-- Test for computations in views 889-- 890create table rtest_comp ( 891 part text, 892 unit char(4), 893 size float 894); 895create table rtest_unitfact ( 896 unit char(4), 897 factor float 898); 899create view rtest_vcomp as 900 select X.part, (X.size * Y.factor) as size_in_cm 901 from rtest_comp X, rtest_unitfact Y 902 where X.unit = Y.unit; 903insert into rtest_unitfact values ('m', 100.0); 904insert into rtest_unitfact values ('cm', 1.0); 905insert into rtest_unitfact values ('inch', 2.54); 906insert into rtest_comp values ('p1', 'm', 5.0); 907insert into rtest_comp values ('p2', 'm', 3.0); 908insert into rtest_comp values ('p3', 'cm', 5.0); 909insert into rtest_comp values ('p4', 'cm', 15.0); 910insert into rtest_comp values ('p5', 'inch', 7.0); 911insert into rtest_comp values ('p6', 'inch', 4.4); 912select * from rtest_vcomp order by part; 913 part | size_in_cm 914------+------------ 915 p1 | 500 916 p2 | 300 917 p3 | 5 918 p4 | 15 919 p5 | 17.78 920 p6 | 11.176 921(6 rows) 922 923select * from rtest_vcomp where size_in_cm > 10.0 order by size_in_cm using >; 924 part | size_in_cm 925------+------------ 926 p1 | 500 927 p2 | 300 928 p5 | 17.78 929 p4 | 15 930 p6 | 11.176 931(5 rows) 932 933-- 934-- In addition run the (slightly modified) queries from the 935-- programmers manual section on the rule system. 936-- 937CREATE TABLE shoe_data ( 938 shoename char(10), -- primary key 939 sh_avail integer, -- available # of pairs 940 slcolor char(10), -- preferred shoelace color 941 slminlen float, -- minimum shoelace length 942 slmaxlen float, -- maximum shoelace length 943 slunit char(8) -- length unit 944); 945CREATE TABLE shoelace_data ( 946 sl_name char(10), -- primary key 947 sl_avail integer, -- available # of pairs 948 sl_color char(10), -- shoelace color 949 sl_len float, -- shoelace length 950 sl_unit char(8) -- length unit 951); 952CREATE TABLE unit ( 953 un_name char(8), -- the primary key 954 un_fact float -- factor to transform to cm 955); 956CREATE VIEW shoe AS 957 SELECT sh.shoename, 958 sh.sh_avail, 959 sh.slcolor, 960 sh.slminlen, 961 sh.slminlen * un.un_fact AS slminlen_cm, 962 sh.slmaxlen, 963 sh.slmaxlen * un.un_fact AS slmaxlen_cm, 964 sh.slunit 965 FROM shoe_data sh, unit un 966 WHERE sh.slunit = un.un_name; 967CREATE VIEW shoelace AS 968 SELECT s.sl_name, 969 s.sl_avail, 970 s.sl_color, 971 s.sl_len, 972 s.sl_unit, 973 s.sl_len * u.un_fact AS sl_len_cm 974 FROM shoelace_data s, unit u 975 WHERE s.sl_unit = u.un_name; 976CREATE VIEW shoe_ready AS 977 SELECT rsh.shoename, 978 rsh.sh_avail, 979 rsl.sl_name, 980 rsl.sl_avail, 981 int4smaller(rsh.sh_avail, rsl.sl_avail) AS total_avail 982 FROM shoe rsh, shoelace rsl 983 WHERE rsl.sl_color = rsh.slcolor 984 AND rsl.sl_len_cm >= rsh.slminlen_cm 985 AND rsl.sl_len_cm <= rsh.slmaxlen_cm; 986INSERT INTO unit VALUES ('cm', 1.0); 987INSERT INTO unit VALUES ('m', 100.0); 988INSERT INTO unit VALUES ('inch', 2.54); 989INSERT INTO shoe_data VALUES ('sh1', 2, 'black', 70.0, 90.0, 'cm'); 990INSERT INTO shoe_data VALUES ('sh2', 0, 'black', 30.0, 40.0, 'inch'); 991INSERT INTO shoe_data VALUES ('sh3', 4, 'brown', 50.0, 65.0, 'cm'); 992INSERT INTO shoe_data VALUES ('sh4', 3, 'brown', 40.0, 50.0, 'inch'); 993INSERT INTO shoelace_data VALUES ('sl1', 5, 'black', 80.0, 'cm'); 994INSERT INTO shoelace_data VALUES ('sl2', 6, 'black', 100.0, 'cm'); 995INSERT INTO shoelace_data VALUES ('sl3', 0, 'black', 35.0 , 'inch'); 996INSERT INTO shoelace_data VALUES ('sl4', 8, 'black', 40.0 , 'inch'); 997INSERT INTO shoelace_data VALUES ('sl5', 4, 'brown', 1.0 , 'm'); 998INSERT INTO shoelace_data VALUES ('sl6', 0, 'brown', 0.9 , 'm'); 999INSERT INTO shoelace_data VALUES ('sl7', 7, 'brown', 60 , 'cm'); 1000INSERT INTO shoelace_data VALUES ('sl8', 1, 'brown', 40 , 'inch'); 1001-- SELECTs in doc 1002SELECT * FROM shoelace ORDER BY sl_name; 1003 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm 1004------------+----------+------------+--------+----------+----------- 1005 sl1 | 5 | black | 80 | cm | 80 1006 sl2 | 6 | black | 100 | cm | 100 1007 sl3 | 0 | black | 35 | inch | 88.9 1008 sl4 | 8 | black | 40 | inch | 101.6 1009 sl5 | 4 | brown | 1 | m | 100 1010 sl6 | 0 | brown | 0.9 | m | 90 1011 sl7 | 7 | brown | 60 | cm | 60 1012 sl8 | 1 | brown | 40 | inch | 101.6 1013(8 rows) 1014 1015SELECT * FROM shoe_ready WHERE total_avail >= 2 ORDER BY 1; 1016 shoename | sh_avail | sl_name | sl_avail | total_avail 1017------------+----------+------------+----------+------------- 1018 sh1 | 2 | sl1 | 5 | 2 1019 sh3 | 4 | sl7 | 7 | 4 1020(2 rows) 1021 1022 CREATE TABLE shoelace_log ( 1023 sl_name char(10), -- shoelace changed 1024 sl_avail integer, -- new available value 1025 log_who name, -- who did it 1026 log_when timestamp -- when 1027 ); 1028-- Want "log_who" to be CURRENT_USER, 1029-- but that is non-portable for the regression test 1030-- - thomas 1999-02-21 1031 CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data 1032 WHERE NEW.sl_avail != OLD.sl_avail 1033 DO INSERT INTO shoelace_log VALUES ( 1034 NEW.sl_name, 1035 NEW.sl_avail, 1036 'Al Bundy', 1037 'epoch' 1038 ); 1039UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7'; 1040SELECT * FROM shoelace_log; 1041 sl_name | sl_avail | log_who | log_when 1042------------+----------+----------+-------------------------- 1043 sl7 | 6 | Al Bundy | Thu Jan 01 00:00:00 1970 1044(1 row) 1045 1046 CREATE RULE shoelace_ins AS ON INSERT TO shoelace 1047 DO INSTEAD 1048 INSERT INTO shoelace_data VALUES ( 1049 NEW.sl_name, 1050 NEW.sl_avail, 1051 NEW.sl_color, 1052 NEW.sl_len, 1053 NEW.sl_unit); 1054 CREATE RULE shoelace_upd AS ON UPDATE TO shoelace 1055 DO INSTEAD 1056 UPDATE shoelace_data SET 1057 sl_name = NEW.sl_name, 1058 sl_avail = NEW.sl_avail, 1059 sl_color = NEW.sl_color, 1060 sl_len = NEW.sl_len, 1061 sl_unit = NEW.sl_unit 1062 WHERE sl_name = OLD.sl_name; 1063 CREATE RULE shoelace_del AS ON DELETE TO shoelace 1064 DO INSTEAD 1065 DELETE FROM shoelace_data 1066 WHERE sl_name = OLD.sl_name; 1067 CREATE TABLE shoelace_arrive ( 1068 arr_name char(10), 1069 arr_quant integer 1070 ); 1071 CREATE TABLE shoelace_ok ( 1072 ok_name char(10), 1073 ok_quant integer 1074 ); 1075 CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok 1076 DO INSTEAD 1077 UPDATE shoelace SET 1078 sl_avail = sl_avail + NEW.ok_quant 1079 WHERE sl_name = NEW.ok_name; 1080INSERT INTO shoelace_arrive VALUES ('sl3', 10); 1081INSERT INTO shoelace_arrive VALUES ('sl6', 20); 1082INSERT INTO shoelace_arrive VALUES ('sl8', 20); 1083SELECT * FROM shoelace ORDER BY sl_name; 1084 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm 1085------------+----------+------------+--------+----------+----------- 1086 sl1 | 5 | black | 80 | cm | 80 1087 sl2 | 6 | black | 100 | cm | 100 1088 sl3 | 0 | black | 35 | inch | 88.9 1089 sl4 | 8 | black | 40 | inch | 101.6 1090 sl5 | 4 | brown | 1 | m | 100 1091 sl6 | 0 | brown | 0.9 | m | 90 1092 sl7 | 6 | brown | 60 | cm | 60 1093 sl8 | 1 | brown | 40 | inch | 101.6 1094(8 rows) 1095 1096insert into shoelace_ok select * from shoelace_arrive; 1097SELECT * FROM shoelace ORDER BY sl_name; 1098 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm 1099------------+----------+------------+--------+----------+----------- 1100 sl1 | 5 | black | 80 | cm | 80 1101 sl2 | 6 | black | 100 | cm | 100 1102 sl3 | 10 | black | 35 | inch | 88.9 1103 sl4 | 8 | black | 40 | inch | 101.6 1104 sl5 | 4 | brown | 1 | m | 100 1105 sl6 | 20 | brown | 0.9 | m | 90 1106 sl7 | 6 | brown | 60 | cm | 60 1107 sl8 | 21 | brown | 40 | inch | 101.6 1108(8 rows) 1109 1110SELECT * FROM shoelace_log ORDER BY sl_name; 1111 sl_name | sl_avail | log_who | log_when 1112------------+----------+----------+-------------------------- 1113 sl3 | 10 | Al Bundy | Thu Jan 01 00:00:00 1970 1114 sl6 | 20 | Al Bundy | Thu Jan 01 00:00:00 1970 1115 sl7 | 6 | Al Bundy | Thu Jan 01 00:00:00 1970 1116 sl8 | 21 | Al Bundy | Thu Jan 01 00:00:00 1970 1117(4 rows) 1118 1119 CREATE VIEW shoelace_obsolete AS 1120 SELECT * FROM shoelace WHERE NOT EXISTS 1121 (SELECT shoename FROM shoe WHERE slcolor = sl_color); 1122 CREATE VIEW shoelace_candelete AS 1123 SELECT * FROM shoelace_obsolete WHERE sl_avail = 0; 1124insert into shoelace values ('sl9', 0, 'pink', 35.0, 'inch', 0.0); 1125insert into shoelace values ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0); 1126-- Unsupported (even though a similar updatable view construct is) 1127insert into shoelace values ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0) 1128 on conflict do nothing; 1129ERROR: INSERT with ON CONFLICT clause cannot be used with table that has INSERT or UPDATE rules 1130SELECT * FROM shoelace_obsolete ORDER BY sl_len_cm; 1131 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm 1132------------+----------+------------+--------+----------+----------- 1133 sl9 | 0 | pink | 35 | inch | 88.9 1134 sl10 | 1000 | magenta | 40 | inch | 101.6 1135(2 rows) 1136 1137SELECT * FROM shoelace_candelete; 1138 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm 1139------------+----------+------------+--------+----------+----------- 1140 sl9 | 0 | pink | 35 | inch | 88.9 1141(1 row) 1142 1143DELETE FROM shoelace WHERE EXISTS 1144 (SELECT * FROM shoelace_candelete 1145 WHERE sl_name = shoelace.sl_name); 1146SELECT * FROM shoelace ORDER BY sl_name; 1147 sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm 1148------------+----------+------------+--------+----------+----------- 1149 sl1 | 5 | black | 80 | cm | 80 1150 sl10 | 1000 | magenta | 40 | inch | 101.6 1151 sl2 | 6 | black | 100 | cm | 100 1152 sl3 | 10 | black | 35 | inch | 88.9 1153 sl4 | 8 | black | 40 | inch | 101.6 1154 sl5 | 4 | brown | 1 | m | 100 1155 sl6 | 20 | brown | 0.9 | m | 90 1156 sl7 | 6 | brown | 60 | cm | 60 1157 sl8 | 21 | brown | 40 | inch | 101.6 1158(9 rows) 1159 1160SELECT * FROM shoe ORDER BY shoename; 1161 shoename | sh_avail | slcolor | slminlen | slminlen_cm | slmaxlen | slmaxlen_cm | slunit 1162------------+----------+------------+----------+-------------+----------+-------------+---------- 1163 sh1 | 2 | black | 70 | 70 | 90 | 90 | cm 1164 sh2 | 0 | black | 30 | 76.2 | 40 | 101.6 | inch 1165 sh3 | 4 | brown | 50 | 50 | 65 | 65 | cm 1166 sh4 | 3 | brown | 40 | 101.6 | 50 | 127 | inch 1167(4 rows) 1168 1169SELECT count(*) FROM shoe; 1170 count 1171------- 1172 4 1173(1 row) 1174 1175-- 1176-- Simple test of qualified ON INSERT ... this did not work in 7.0 ... 1177-- 1178create table foo (f1 int); 1179create table foo2 (f1 int); 1180create rule foorule as on insert to foo where f1 < 100 1181do instead nothing; 1182insert into foo values(1); 1183insert into foo values(1001); 1184select * from foo; 1185 f1 1186------ 1187 1001 1188(1 row) 1189 1190drop rule foorule on foo; 1191-- this should fail because f1 is not exposed for unqualified reference: 1192create rule foorule as on insert to foo where f1 < 100 1193do instead insert into foo2 values (f1); 1194ERROR: column "f1" does not exist 1195LINE 2: do instead insert into foo2 values (f1); 1196 ^ 1197HINT: There is a column named "f1" in table "old", but it cannot be referenced from this part of the query. 1198-- this is the correct way: 1199create rule foorule as on insert to foo where f1 < 100 1200do instead insert into foo2 values (new.f1); 1201insert into foo values(2); 1202insert into foo values(100); 1203select * from foo; 1204 f1 1205------ 1206 1001 1207 100 1208(2 rows) 1209 1210select * from foo2; 1211 f1 1212---- 1213 2 1214(1 row) 1215 1216drop rule foorule on foo; 1217drop table foo; 1218drop table foo2; 1219-- 1220-- Test rules containing INSERT ... SELECT, which is a very ugly special 1221-- case as of 7.1. Example is based on bug report from Joel Burton. 1222-- 1223create table pparent (pid int, txt text); 1224insert into pparent values (1,'parent1'); 1225insert into pparent values (2,'parent2'); 1226create table cchild (pid int, descrip text); 1227insert into cchild values (1,'descrip1'); 1228create view vview as 1229 select pparent.pid, txt, descrip from 1230 pparent left join cchild using (pid); 1231create rule rrule as 1232 on update to vview do instead 1233( 1234 insert into cchild (pid, descrip) 1235 select old.pid, new.descrip where old.descrip isnull; 1236 update cchild set descrip = new.descrip where cchild.pid = old.pid; 1237); 1238select * from vview; 1239 pid | txt | descrip 1240-----+---------+---------- 1241 1 | parent1 | descrip1 1242 2 | parent2 | 1243(2 rows) 1244 1245update vview set descrip='test1' where pid=1; 1246select * from vview; 1247 pid | txt | descrip 1248-----+---------+--------- 1249 1 | parent1 | test1 1250 2 | parent2 | 1251(2 rows) 1252 1253update vview set descrip='test2' where pid=2; 1254select * from vview; 1255 pid | txt | descrip 1256-----+---------+--------- 1257 1 | parent1 | test1 1258 2 | parent2 | test2 1259(2 rows) 1260 1261update vview set descrip='test3' where pid=3; 1262select * from vview; 1263 pid | txt | descrip 1264-----+---------+--------- 1265 1 | parent1 | test1 1266 2 | parent2 | test2 1267(2 rows) 1268 1269select * from cchild; 1270 pid | descrip 1271-----+--------- 1272 1 | test1 1273 2 | test2 1274(2 rows) 1275 1276drop rule rrule on vview; 1277drop view vview; 1278drop table pparent; 1279drop table cchild; 1280-- 1281-- Check that ruleutils are working 1282-- 1283-- temporarily disable fancy output, so view changes create less diff noise 1284\a\t 1285SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schema' ORDER BY viewname; 1286iexit| SELECT ih.name, 1287 ih.thepath, 1288 interpt_pp(ih.thepath, r.thepath) AS exit 1289 FROM ihighway ih, 1290 ramp r 1291 WHERE (ih.thepath ## r.thepath); 1292key_dependent_view| SELECT view_base_table.key, 1293 view_base_table.data 1294 FROM view_base_table 1295 GROUP BY view_base_table.key; 1296key_dependent_view_no_cols| SELECT 1297 FROM view_base_table 1298 GROUP BY view_base_table.key 1299 HAVING (length((view_base_table.data)::text) > 0); 1300mvtest_tv| SELECT mvtest_t.type, 1301 sum(mvtest_t.amt) AS totamt 1302 FROM mvtest_t 1303 GROUP BY mvtest_t.type; 1304mvtest_tvv| SELECT sum(mvtest_tv.totamt) AS grandtot 1305 FROM mvtest_tv; 1306mvtest_tvvmv| SELECT mvtest_tvvm.grandtot 1307 FROM mvtest_tvvm; 1308pg_available_extension_versions| SELECT e.name, 1309 e.version, 1310 (x.extname IS NOT NULL) AS installed, 1311 e.superuser, 1312 e.relocatable, 1313 e.schema, 1314 e.requires, 1315 e.comment 1316 FROM (pg_available_extension_versions() e(name, version, superuser, relocatable, schema, requires, comment) 1317 LEFT JOIN pg_extension x ON (((e.name = x.extname) AND (e.version = x.extversion)))); 1318pg_available_extensions| SELECT e.name, 1319 e.default_version, 1320 x.extversion AS installed_version, 1321 e.comment 1322 FROM (pg_available_extensions() e(name, default_version, comment) 1323 LEFT JOIN pg_extension x ON ((e.name = x.extname))); 1324pg_config| SELECT pg_config.name, 1325 pg_config.setting 1326 FROM pg_config() pg_config(name, setting); 1327pg_cursors| SELECT c.name, 1328 c.statement, 1329 c.is_holdable, 1330 c.is_binary, 1331 c.is_scrollable, 1332 c.creation_time 1333 FROM pg_cursor() c(name, statement, is_holdable, is_binary, is_scrollable, creation_time); 1334pg_file_settings| SELECT a.sourcefile, 1335 a.sourceline, 1336 a.seqno, 1337 a.name, 1338 a.setting, 1339 a.applied, 1340 a.error 1341 FROM pg_show_all_file_settings() a(sourcefile, sourceline, seqno, name, setting, applied, error); 1342pg_group| SELECT pg_authid.rolname AS groname, 1343 pg_authid.oid AS grosysid, 1344 ARRAY( SELECT pg_auth_members.member 1345 FROM pg_auth_members 1346 WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist 1347 FROM pg_authid 1348 WHERE (NOT pg_authid.rolcanlogin); 1349pg_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 1533 WHEN (pro.proisagg = true) THEN 'aggregate'::text 1534 WHEN (pro.proisagg = false) THEN 'function'::text 1535 ELSE NULL::text 1536 END AS objtype, 1537 pro.pronamespace AS objnamespace, 1538 ((( 1539 CASE 1540 WHEN pg_function_is_visible(pro.oid) THEN quote_ident((pro.proname)::text) 1541 ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((pro.proname)::text)) 1542 END || '('::text) || pg_get_function_arguments(pro.oid)) || ')'::text) AS objname, 1543 l.provider, 1544 l.label 1545 FROM ((pg_seclabel l 1546 JOIN pg_proc pro ON (((l.classoid = pro.tableoid) AND (l.objoid = pro.oid)))) 1547 JOIN pg_namespace nsp ON ((pro.pronamespace = nsp.oid))) 1548 WHERE (l.objsubid = 0) 1549UNION ALL 1550 SELECT l.objoid, 1551 l.classoid, 1552 l.objsubid, 1553 CASE 1554 WHEN (typ.typtype = 'd'::"char") THEN 'domain'::text 1555 ELSE 'type'::text 1556 END AS objtype, 1557 typ.typnamespace AS objnamespace, 1558 CASE 1559 WHEN pg_type_is_visible(typ.oid) THEN quote_ident((typ.typname)::text) 1560 ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((typ.typname)::text)) 1561 END AS objname, 1562 l.provider, 1563 l.label 1564 FROM ((pg_seclabel l 1565 JOIN pg_type typ ON (((l.classoid = typ.tableoid) AND (l.objoid = typ.oid)))) 1566 JOIN pg_namespace nsp ON ((typ.typnamespace = nsp.oid))) 1567 WHERE (l.objsubid = 0) 1568UNION ALL 1569 SELECT l.objoid, 1570 l.classoid, 1571 l.objsubid, 1572 'large object'::text AS objtype, 1573 NULL::oid AS objnamespace, 1574 (l.objoid)::text AS objname, 1575 l.provider, 1576 l.label 1577 FROM (pg_seclabel l 1578 JOIN pg_largeobject_metadata lom ON ((l.objoid = lom.oid))) 1579 WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND (l.objsubid = 0)) 1580UNION ALL 1581 SELECT l.objoid, 1582 l.classoid, 1583 l.objsubid, 1584 'language'::text AS objtype, 1585 NULL::oid AS objnamespace, 1586 quote_ident((lan.lanname)::text) AS objname, 1587 l.provider, 1588 l.label 1589 FROM (pg_seclabel l 1590 JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND (l.objoid = lan.oid)))) 1591 WHERE (l.objsubid = 0) 1592UNION ALL 1593 SELECT l.objoid, 1594 l.classoid, 1595 l.objsubid, 1596 'schema'::text AS objtype, 1597 nsp.oid AS objnamespace, 1598 quote_ident((nsp.nspname)::text) AS objname, 1599 l.provider, 1600 l.label 1601 FROM (pg_seclabel l 1602 JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid = nsp.oid)))) 1603 WHERE (l.objsubid = 0) 1604UNION ALL 1605 SELECT l.objoid, 1606 l.classoid, 1607 l.objsubid, 1608 'event trigger'::text AS objtype, 1609 NULL::oid AS objnamespace, 1610 quote_ident((evt.evtname)::text) AS objname, 1611 l.provider, 1612 l.label 1613 FROM (pg_seclabel l 1614 JOIN pg_event_trigger evt ON (((l.classoid = evt.tableoid) AND (l.objoid = evt.oid)))) 1615 WHERE (l.objsubid = 0) 1616UNION ALL 1617 SELECT l.objoid, 1618 l.classoid, 1619 l.objsubid, 1620 'publication'::text AS objtype, 1621 NULL::oid AS objnamespace, 1622 quote_ident((p.pubname)::text) AS objname, 1623 l.provider, 1624 l.label 1625 FROM (pg_seclabel l 1626 JOIN pg_publication p ON (((l.classoid = p.tableoid) AND (l.objoid = p.oid)))) 1627 WHERE (l.objsubid = 0) 1628UNION ALL 1629 SELECT l.objoid, 1630 l.classoid, 1631 0 AS objsubid, 1632 'subscription'::text AS objtype, 1633 NULL::oid AS objnamespace, 1634 quote_ident((s.subname)::text) AS objname, 1635 l.provider, 1636 l.label 1637 FROM (pg_shseclabel l 1638 JOIN pg_subscription s ON (((l.classoid = s.tableoid) AND (l.objoid = s.oid)))) 1639UNION ALL 1640 SELECT l.objoid, 1641 l.classoid, 1642 0 AS objsubid, 1643 'database'::text AS objtype, 1644 NULL::oid AS objnamespace, 1645 quote_ident((dat.datname)::text) AS objname, 1646 l.provider, 1647 l.label 1648 FROM (pg_shseclabel l 1649 JOIN pg_database dat ON (((l.classoid = dat.tableoid) AND (l.objoid = dat.oid)))) 1650UNION ALL 1651 SELECT l.objoid, 1652 l.classoid, 1653 0 AS objsubid, 1654 'tablespace'::text AS objtype, 1655 NULL::oid AS objnamespace, 1656 quote_ident((spc.spcname)::text) AS objname, 1657 l.provider, 1658 l.label 1659 FROM (pg_shseclabel l 1660 JOIN pg_tablespace spc ON (((l.classoid = spc.tableoid) AND (l.objoid = spc.oid)))) 1661UNION ALL 1662 SELECT l.objoid, 1663 l.classoid, 1664 0 AS objsubid, 1665 'role'::text AS objtype, 1666 NULL::oid AS objnamespace, 1667 quote_ident((rol.rolname)::text) AS objname, 1668 l.provider, 1669 l.label 1670 FROM (pg_shseclabel l 1671 JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid)))); 1672pg_sequences| SELECT n.nspname AS schemaname, 1673 c.relname AS sequencename, 1674 pg_get_userbyid(c.relowner) AS sequenceowner, 1675 (s.seqtypid)::regtype AS data_type, 1676 s.seqstart AS start_value, 1677 s.seqmin AS min_value, 1678 s.seqmax AS max_value, 1679 s.seqincrement AS increment_by, 1680 s.seqcycle AS cycle, 1681 s.seqcache AS cache_size, 1682 CASE 1683 WHEN has_sequence_privilege(c.oid, 'SELECT,USAGE'::text) THEN pg_sequence_last_value((c.oid)::regclass) 1684 ELSE NULL::bigint 1685 END AS last_value 1686 FROM ((pg_sequence s 1687 JOIN pg_class c ON ((c.oid = s.seqrelid))) 1688 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 1689 WHERE ((NOT pg_is_other_temp_schema(n.oid)) AND (c.relkind = 'S'::"char")); 1690pg_settings| SELECT a.name, 1691 a.setting, 1692 a.unit, 1693 a.category, 1694 a.short_desc, 1695 a.extra_desc, 1696 a.context, 1697 a.vartype, 1698 a.source, 1699 a.min_val, 1700 a.max_val, 1701 a.enumvals, 1702 a.boot_val, 1703 a.reset_val, 1704 a.sourcefile, 1705 a.sourceline, 1706 a.pending_restart 1707 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); 1708pg_shadow| SELECT pg_authid.rolname AS usename, 1709 pg_authid.oid AS usesysid, 1710 pg_authid.rolcreatedb AS usecreatedb, 1711 pg_authid.rolsuper AS usesuper, 1712 pg_authid.rolreplication AS userepl, 1713 pg_authid.rolbypassrls AS usebypassrls, 1714 pg_authid.rolpassword AS passwd, 1715 (pg_authid.rolvaliduntil)::abstime AS valuntil, 1716 s.setconfig AS useconfig 1717 FROM (pg_authid 1718 LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) 1719 WHERE pg_authid.rolcanlogin; 1720pg_stat_activity| SELECT s.datid, 1721 d.datname, 1722 s.pid, 1723 s.usesysid, 1724 u.rolname AS usename, 1725 s.application_name, 1726 s.client_addr, 1727 s.client_hostname, 1728 s.client_port, 1729 s.backend_start, 1730 s.xact_start, 1731 s.query_start, 1732 s.state_change, 1733 s.wait_event_type, 1734 s.wait_event, 1735 s.state, 1736 s.backend_xid, 1737 s.backend_xmin, 1738 s.query, 1739 s.backend_type 1740 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) 1741 LEFT JOIN pg_database d ON ((s.datid = d.oid))) 1742 LEFT JOIN pg_authid u ON ((s.usesysid = u.oid))); 1743pg_stat_all_indexes| SELECT c.oid AS relid, 1744 i.oid AS indexrelid, 1745 n.nspname AS schemaname, 1746 c.relname, 1747 i.relname AS indexrelname, 1748 pg_stat_get_numscans(i.oid) AS idx_scan, 1749 pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, 1750 pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch 1751 FROM (((pg_class c 1752 JOIN pg_index x ON ((c.oid = x.indrelid))) 1753 JOIN pg_class i ON ((i.oid = x.indexrelid))) 1754 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 1755 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])); 1756pg_stat_all_tables| SELECT c.oid AS relid, 1757 n.nspname AS schemaname, 1758 c.relname, 1759 pg_stat_get_numscans(c.oid) AS seq_scan, 1760 pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, 1761 (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, 1762 ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, 1763 pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, 1764 pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, 1765 pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, 1766 pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, 1767 pg_stat_get_live_tuples(c.oid) AS n_live_tup, 1768 pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, 1769 pg_stat_get_mod_since_analyze(c.oid) AS n_mod_since_analyze, 1770 pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, 1771 pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, 1772 pg_stat_get_last_analyze_time(c.oid) AS last_analyze, 1773 pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, 1774 pg_stat_get_vacuum_count(c.oid) AS vacuum_count, 1775 pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, 1776 pg_stat_get_analyze_count(c.oid) AS analyze_count, 1777 pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count 1778 FROM ((pg_class c 1779 LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) 1780 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 1781 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])) 1782 GROUP BY c.oid, n.nspname, c.relname; 1783pg_stat_archiver| SELECT s.archived_count, 1784 s.last_archived_wal, 1785 s.last_archived_time, 1786 s.failed_count, 1787 s.last_failed_wal, 1788 s.last_failed_time, 1789 s.stats_reset 1790 FROM pg_stat_get_archiver() s(archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time, stats_reset); 1791pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, 1792 pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, 1793 pg_stat_get_checkpoint_write_time() AS checkpoint_write_time, 1794 pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time, 1795 pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, 1796 pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, 1797 pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, 1798 pg_stat_get_buf_written_backend() AS buffers_backend, 1799 pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, 1800 pg_stat_get_buf_alloc() AS buffers_alloc, 1801 pg_stat_get_bgwriter_stat_reset_time() AS stats_reset; 1802pg_stat_database| SELECT d.oid AS datid, 1803 d.datname, 1804 pg_stat_get_db_numbackends(d.oid) AS numbackends, 1805 pg_stat_get_db_xact_commit(d.oid) AS xact_commit, 1806 pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, 1807 (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, 1808 pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, 1809 pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, 1810 pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, 1811 pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, 1812 pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, 1813 pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted, 1814 pg_stat_get_db_conflict_all(d.oid) AS conflicts, 1815 pg_stat_get_db_temp_files(d.oid) AS temp_files, 1816 pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes, 1817 pg_stat_get_db_deadlocks(d.oid) AS deadlocks, 1818 pg_stat_get_db_blk_read_time(d.oid) AS blk_read_time, 1819 pg_stat_get_db_blk_write_time(d.oid) AS blk_write_time, 1820 pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset 1821 FROM pg_database d; 1822pg_stat_database_conflicts| SELECT d.oid AS datid, 1823 d.datname, 1824 pg_stat_get_db_conflict_tablespace(d.oid) AS confl_tablespace, 1825 pg_stat_get_db_conflict_lock(d.oid) AS confl_lock, 1826 pg_stat_get_db_conflict_snapshot(d.oid) AS confl_snapshot, 1827 pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin, 1828 pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock 1829 FROM pg_database d; 1830pg_stat_progress_vacuum| SELECT s.pid, 1831 s.datid, 1832 d.datname, 1833 s.relid, 1834 CASE s.param1 1835 WHEN 0 THEN 'initializing'::text 1836 WHEN 1 THEN 'scanning heap'::text 1837 WHEN 2 THEN 'vacuuming indexes'::text 1838 WHEN 3 THEN 'vacuuming heap'::text 1839 WHEN 4 THEN 'cleaning up indexes'::text 1840 WHEN 5 THEN 'truncating heap'::text 1841 WHEN 6 THEN 'performing final cleanup'::text 1842 ELSE NULL::text 1843 END AS phase, 1844 s.param2 AS heap_blks_total, 1845 s.param3 AS heap_blks_scanned, 1846 s.param4 AS heap_blks_vacuumed, 1847 s.param5 AS index_vacuum_count, 1848 s.param6 AS max_dead_tuples, 1849 s.param7 AS num_dead_tuples 1850 FROM (pg_stat_get_progress_info('VACUUM'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10) 1851 LEFT JOIN pg_database d ON ((s.datid = d.oid))); 1852pg_stat_replication| SELECT s.pid, 1853 s.usesysid, 1854 u.rolname AS usename, 1855 s.application_name, 1856 s.client_addr, 1857 s.client_hostname, 1858 s.client_port, 1859 s.backend_start, 1860 s.backend_xmin, 1861 w.state, 1862 w.sent_lsn, 1863 w.write_lsn, 1864 w.flush_lsn, 1865 w.replay_lsn, 1866 w.write_lag, 1867 w.flush_lag, 1868 w.replay_lag, 1869 w.sync_priority, 1870 w.sync_state 1871 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) 1872 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))) 1873 LEFT JOIN pg_authid u ON ((s.usesysid = u.oid))); 1874pg_stat_ssl| SELECT s.pid, 1875 s.ssl, 1876 s.sslversion AS version, 1877 s.sslcipher AS cipher, 1878 s.sslbits AS bits, 1879 s.sslcompression AS compression, 1880 s.sslclientdn AS clientdn 1881 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); 1882pg_stat_subscription| SELECT su.oid AS subid, 1883 su.subname, 1884 st.pid, 1885 st.relid, 1886 st.received_lsn, 1887 st.last_msg_send_time, 1888 st.last_msg_receipt_time, 1889 st.latest_end_lsn, 1890 st.latest_end_time 1891 FROM (pg_subscription su 1892 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))); 1893pg_stat_sys_indexes| SELECT pg_stat_all_indexes.relid, 1894 pg_stat_all_indexes.indexrelid, 1895 pg_stat_all_indexes.schemaname, 1896 pg_stat_all_indexes.relname, 1897 pg_stat_all_indexes.indexrelname, 1898 pg_stat_all_indexes.idx_scan, 1899 pg_stat_all_indexes.idx_tup_read, 1900 pg_stat_all_indexes.idx_tup_fetch 1901 FROM pg_stat_all_indexes 1902 WHERE ((pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_indexes.schemaname ~ '^pg_toast'::text)); 1903pg_stat_sys_tables| SELECT pg_stat_all_tables.relid, 1904 pg_stat_all_tables.schemaname, 1905 pg_stat_all_tables.relname, 1906 pg_stat_all_tables.seq_scan, 1907 pg_stat_all_tables.seq_tup_read, 1908 pg_stat_all_tables.idx_scan, 1909 pg_stat_all_tables.idx_tup_fetch, 1910 pg_stat_all_tables.n_tup_ins, 1911 pg_stat_all_tables.n_tup_upd, 1912 pg_stat_all_tables.n_tup_del, 1913 pg_stat_all_tables.n_tup_hot_upd, 1914 pg_stat_all_tables.n_live_tup, 1915 pg_stat_all_tables.n_dead_tup, 1916 pg_stat_all_tables.n_mod_since_analyze, 1917 pg_stat_all_tables.last_vacuum, 1918 pg_stat_all_tables.last_autovacuum, 1919 pg_stat_all_tables.last_analyze, 1920 pg_stat_all_tables.last_autoanalyze, 1921 pg_stat_all_tables.vacuum_count, 1922 pg_stat_all_tables.autovacuum_count, 1923 pg_stat_all_tables.analyze_count, 1924 pg_stat_all_tables.autoanalyze_count 1925 FROM pg_stat_all_tables 1926 WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text)); 1927pg_stat_user_functions| SELECT p.oid AS funcid, 1928 n.nspname AS schemaname, 1929 p.proname AS funcname, 1930 pg_stat_get_function_calls(p.oid) AS calls, 1931 pg_stat_get_function_total_time(p.oid) AS total_time, 1932 pg_stat_get_function_self_time(p.oid) AS self_time 1933 FROM (pg_proc p 1934 LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) 1935 WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL)); 1936pg_stat_user_indexes| SELECT pg_stat_all_indexes.relid, 1937 pg_stat_all_indexes.indexrelid, 1938 pg_stat_all_indexes.schemaname, 1939 pg_stat_all_indexes.relname, 1940 pg_stat_all_indexes.indexrelname, 1941 pg_stat_all_indexes.idx_scan, 1942 pg_stat_all_indexes.idx_tup_read, 1943 pg_stat_all_indexes.idx_tup_fetch 1944 FROM pg_stat_all_indexes 1945 WHERE ((pg_stat_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_indexes.schemaname !~ '^pg_toast'::text)); 1946pg_stat_user_tables| SELECT pg_stat_all_tables.relid, 1947 pg_stat_all_tables.schemaname, 1948 pg_stat_all_tables.relname, 1949 pg_stat_all_tables.seq_scan, 1950 pg_stat_all_tables.seq_tup_read, 1951 pg_stat_all_tables.idx_scan, 1952 pg_stat_all_tables.idx_tup_fetch, 1953 pg_stat_all_tables.n_tup_ins, 1954 pg_stat_all_tables.n_tup_upd, 1955 pg_stat_all_tables.n_tup_del, 1956 pg_stat_all_tables.n_tup_hot_upd, 1957 pg_stat_all_tables.n_live_tup, 1958 pg_stat_all_tables.n_dead_tup, 1959 pg_stat_all_tables.n_mod_since_analyze, 1960 pg_stat_all_tables.last_vacuum, 1961 pg_stat_all_tables.last_autovacuum, 1962 pg_stat_all_tables.last_analyze, 1963 pg_stat_all_tables.last_autoanalyze, 1964 pg_stat_all_tables.vacuum_count, 1965 pg_stat_all_tables.autovacuum_count, 1966 pg_stat_all_tables.analyze_count, 1967 pg_stat_all_tables.autoanalyze_count 1968 FROM pg_stat_all_tables 1969 WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text)); 1970pg_stat_wal_receiver| SELECT s.pid, 1971 s.status, 1972 s.receive_start_lsn, 1973 s.receive_start_tli, 1974 s.received_lsn, 1975 s.received_tli, 1976 s.last_msg_send_time, 1977 s.last_msg_receipt_time, 1978 s.latest_end_lsn, 1979 s.latest_end_time, 1980 s.slot_name, 1981 s.conninfo 1982 FROM pg_stat_get_wal_receiver() s(pid, status, receive_start_lsn, receive_start_tli, received_lsn, received_tli, last_msg_send_time, last_msg_receipt_time, latest_end_lsn, latest_end_time, slot_name, conninfo) 1983 WHERE (s.pid IS NOT NULL); 1984pg_stat_xact_all_tables| SELECT c.oid AS relid, 1985 n.nspname AS schemaname, 1986 c.relname, 1987 pg_stat_get_xact_numscans(c.oid) AS seq_scan, 1988 pg_stat_get_xact_tuples_returned(c.oid) AS seq_tup_read, 1989 (sum(pg_stat_get_xact_numscans(i.indexrelid)))::bigint AS idx_scan, 1990 ((sum(pg_stat_get_xact_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_xact_tuples_fetched(c.oid)) AS idx_tup_fetch, 1991 pg_stat_get_xact_tuples_inserted(c.oid) AS n_tup_ins, 1992 pg_stat_get_xact_tuples_updated(c.oid) AS n_tup_upd, 1993 pg_stat_get_xact_tuples_deleted(c.oid) AS n_tup_del, 1994 pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd 1995 FROM ((pg_class c 1996 LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) 1997 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 1998 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])) 1999 GROUP BY c.oid, n.nspname, c.relname; 2000pg_stat_xact_sys_tables| SELECT pg_stat_xact_all_tables.relid, 2001 pg_stat_xact_all_tables.schemaname, 2002 pg_stat_xact_all_tables.relname, 2003 pg_stat_xact_all_tables.seq_scan, 2004 pg_stat_xact_all_tables.seq_tup_read, 2005 pg_stat_xact_all_tables.idx_scan, 2006 pg_stat_xact_all_tables.idx_tup_fetch, 2007 pg_stat_xact_all_tables.n_tup_ins, 2008 pg_stat_xact_all_tables.n_tup_upd, 2009 pg_stat_xact_all_tables.n_tup_del, 2010 pg_stat_xact_all_tables.n_tup_hot_upd 2011 FROM pg_stat_xact_all_tables 2012 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)); 2013pg_stat_xact_user_functions| SELECT p.oid AS funcid, 2014 n.nspname AS schemaname, 2015 p.proname AS funcname, 2016 pg_stat_get_xact_function_calls(p.oid) AS calls, 2017 pg_stat_get_xact_function_total_time(p.oid) AS total_time, 2018 pg_stat_get_xact_function_self_time(p.oid) AS self_time 2019 FROM (pg_proc p 2020 LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) 2021 WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_xact_function_calls(p.oid) IS NOT NULL)); 2022pg_stat_xact_user_tables| SELECT pg_stat_xact_all_tables.relid, 2023 pg_stat_xact_all_tables.schemaname, 2024 pg_stat_xact_all_tables.relname, 2025 pg_stat_xact_all_tables.seq_scan, 2026 pg_stat_xact_all_tables.seq_tup_read, 2027 pg_stat_xact_all_tables.idx_scan, 2028 pg_stat_xact_all_tables.idx_tup_fetch, 2029 pg_stat_xact_all_tables.n_tup_ins, 2030 pg_stat_xact_all_tables.n_tup_upd, 2031 pg_stat_xact_all_tables.n_tup_del, 2032 pg_stat_xact_all_tables.n_tup_hot_upd 2033 FROM pg_stat_xact_all_tables 2034 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)); 2035pg_statio_all_indexes| SELECT c.oid AS relid, 2036 i.oid AS indexrelid, 2037 n.nspname AS schemaname, 2038 c.relname, 2039 i.relname AS indexrelname, 2040 (pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read, 2041 pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit 2042 FROM (((pg_class c 2043 JOIN pg_index x ON ((c.oid = x.indrelid))) 2044 JOIN pg_class i ON ((i.oid = x.indexrelid))) 2045 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 2046 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])); 2047pg_statio_all_sequences| SELECT c.oid AS relid, 2048 n.nspname AS schemaname, 2049 c.relname, 2050 (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read, 2051 pg_stat_get_blocks_hit(c.oid) AS blks_hit 2052 FROM (pg_class c 2053 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 2054 WHERE (c.relkind = 'S'::"char"); 2055pg_statio_all_tables| SELECT c.oid AS relid, 2056 n.nspname AS schemaname, 2057 c.relname, 2058 (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS heap_blks_read, 2059 pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit, 2060 (sum((pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))))::bigint AS idx_blks_read, 2061 (sum(pg_stat_get_blocks_hit(i.indexrelid)))::bigint AS idx_blks_hit, 2062 (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read, 2063 pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit, 2064 (sum((pg_stat_get_blocks_fetched(x.indexrelid) - pg_stat_get_blocks_hit(x.indexrelid))))::bigint AS tidx_blks_read, 2065 (sum(pg_stat_get_blocks_hit(x.indexrelid)))::bigint AS tidx_blks_hit 2066 FROM ((((pg_class c 2067 LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) 2068 LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid))) 2069 LEFT JOIN pg_index x ON ((t.oid = x.indrelid))) 2070 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 2071 WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])) 2072 GROUP BY c.oid, n.nspname, c.relname, t.oid, x.indrelid; 2073pg_statio_sys_indexes| SELECT pg_statio_all_indexes.relid, 2074 pg_statio_all_indexes.indexrelid, 2075 pg_statio_all_indexes.schemaname, 2076 pg_statio_all_indexes.relname, 2077 pg_statio_all_indexes.indexrelname, 2078 pg_statio_all_indexes.idx_blks_read, 2079 pg_statio_all_indexes.idx_blks_hit 2080 FROM pg_statio_all_indexes 2081 WHERE ((pg_statio_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_indexes.schemaname ~ '^pg_toast'::text)); 2082pg_statio_sys_sequences| SELECT pg_statio_all_sequences.relid, 2083 pg_statio_all_sequences.schemaname, 2084 pg_statio_all_sequences.relname, 2085 pg_statio_all_sequences.blks_read, 2086 pg_statio_all_sequences.blks_hit 2087 FROM pg_statio_all_sequences 2088 WHERE ((pg_statio_all_sequences.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_sequences.schemaname ~ '^pg_toast'::text)); 2089pg_statio_sys_tables| SELECT pg_statio_all_tables.relid, 2090 pg_statio_all_tables.schemaname, 2091 pg_statio_all_tables.relname, 2092 pg_statio_all_tables.heap_blks_read, 2093 pg_statio_all_tables.heap_blks_hit, 2094 pg_statio_all_tables.idx_blks_read, 2095 pg_statio_all_tables.idx_blks_hit, 2096 pg_statio_all_tables.toast_blks_read, 2097 pg_statio_all_tables.toast_blks_hit, 2098 pg_statio_all_tables.tidx_blks_read, 2099 pg_statio_all_tables.tidx_blks_hit 2100 FROM pg_statio_all_tables 2101 WHERE ((pg_statio_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_tables.schemaname ~ '^pg_toast'::text)); 2102pg_statio_user_indexes| SELECT pg_statio_all_indexes.relid, 2103 pg_statio_all_indexes.indexrelid, 2104 pg_statio_all_indexes.schemaname, 2105 pg_statio_all_indexes.relname, 2106 pg_statio_all_indexes.indexrelname, 2107 pg_statio_all_indexes.idx_blks_read, 2108 pg_statio_all_indexes.idx_blks_hit 2109 FROM pg_statio_all_indexes 2110 WHERE ((pg_statio_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_indexes.schemaname !~ '^pg_toast'::text)); 2111pg_statio_user_sequences| SELECT pg_statio_all_sequences.relid, 2112 pg_statio_all_sequences.schemaname, 2113 pg_statio_all_sequences.relname, 2114 pg_statio_all_sequences.blks_read, 2115 pg_statio_all_sequences.blks_hit 2116 FROM pg_statio_all_sequences 2117 WHERE ((pg_statio_all_sequences.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_sequences.schemaname !~ '^pg_toast'::text)); 2118pg_statio_user_tables| SELECT pg_statio_all_tables.relid, 2119 pg_statio_all_tables.schemaname, 2120 pg_statio_all_tables.relname, 2121 pg_statio_all_tables.heap_blks_read, 2122 pg_statio_all_tables.heap_blks_hit, 2123 pg_statio_all_tables.idx_blks_read, 2124 pg_statio_all_tables.idx_blks_hit, 2125 pg_statio_all_tables.toast_blks_read, 2126 pg_statio_all_tables.toast_blks_hit, 2127 pg_statio_all_tables.tidx_blks_read, 2128 pg_statio_all_tables.tidx_blks_hit 2129 FROM pg_statio_all_tables 2130 WHERE ((pg_statio_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_tables.schemaname !~ '^pg_toast'::text)); 2131pg_stats| SELECT n.nspname AS schemaname, 2132 c.relname AS tablename, 2133 a.attname, 2134 s.stainherit AS inherited, 2135 s.stanullfrac AS null_frac, 2136 s.stawidth AS avg_width, 2137 s.stadistinct AS n_distinct, 2138 CASE 2139 WHEN (s.stakind1 = 1) THEN s.stavalues1 2140 WHEN (s.stakind2 = 1) THEN s.stavalues2 2141 WHEN (s.stakind3 = 1) THEN s.stavalues3 2142 WHEN (s.stakind4 = 1) THEN s.stavalues4 2143 WHEN (s.stakind5 = 1) THEN s.stavalues5 2144 ELSE NULL::anyarray 2145 END AS most_common_vals, 2146 CASE 2147 WHEN (s.stakind1 = 1) THEN s.stanumbers1 2148 WHEN (s.stakind2 = 1) THEN s.stanumbers2 2149 WHEN (s.stakind3 = 1) THEN s.stanumbers3 2150 WHEN (s.stakind4 = 1) THEN s.stanumbers4 2151 WHEN (s.stakind5 = 1) THEN s.stanumbers5 2152 ELSE NULL::real[] 2153 END AS most_common_freqs, 2154 CASE 2155 WHEN (s.stakind1 = 2) THEN s.stavalues1 2156 WHEN (s.stakind2 = 2) THEN s.stavalues2 2157 WHEN (s.stakind3 = 2) THEN s.stavalues3 2158 WHEN (s.stakind4 = 2) THEN s.stavalues4 2159 WHEN (s.stakind5 = 2) THEN s.stavalues5 2160 ELSE NULL::anyarray 2161 END AS histogram_bounds, 2162 CASE 2163 WHEN (s.stakind1 = 3) THEN s.stanumbers1[1] 2164 WHEN (s.stakind2 = 3) THEN s.stanumbers2[1] 2165 WHEN (s.stakind3 = 3) THEN s.stanumbers3[1] 2166 WHEN (s.stakind4 = 3) THEN s.stanumbers4[1] 2167 WHEN (s.stakind5 = 3) THEN s.stanumbers5[1] 2168 ELSE NULL::real 2169 END AS correlation, 2170 CASE 2171 WHEN (s.stakind1 = 4) THEN s.stavalues1 2172 WHEN (s.stakind2 = 4) THEN s.stavalues2 2173 WHEN (s.stakind3 = 4) THEN s.stavalues3 2174 WHEN (s.stakind4 = 4) THEN s.stavalues4 2175 WHEN (s.stakind5 = 4) THEN s.stavalues5 2176 ELSE NULL::anyarray 2177 END AS most_common_elems, 2178 CASE 2179 WHEN (s.stakind1 = 4) THEN s.stanumbers1 2180 WHEN (s.stakind2 = 4) THEN s.stanumbers2 2181 WHEN (s.stakind3 = 4) THEN s.stanumbers3 2182 WHEN (s.stakind4 = 4) THEN s.stanumbers4 2183 WHEN (s.stakind5 = 4) THEN s.stanumbers5 2184 ELSE NULL::real[] 2185 END AS most_common_elem_freqs, 2186 CASE 2187 WHEN (s.stakind1 = 5) THEN s.stanumbers1 2188 WHEN (s.stakind2 = 5) THEN s.stanumbers2 2189 WHEN (s.stakind3 = 5) THEN s.stanumbers3 2190 WHEN (s.stakind4 = 5) THEN s.stanumbers4 2191 WHEN (s.stakind5 = 5) THEN s.stanumbers5 2192 ELSE NULL::real[] 2193 END AS elem_count_histogram 2194 FROM (((pg_statistic s 2195 JOIN pg_class c ON ((c.oid = s.starelid))) 2196 JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) 2197 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 2198 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)))); 2199pg_tables| SELECT n.nspname AS schemaname, 2200 c.relname AS tablename, 2201 pg_get_userbyid(c.relowner) AS tableowner, 2202 t.spcname AS tablespace, 2203 c.relhasindex AS hasindexes, 2204 c.relhasrules AS hasrules, 2205 c.relhastriggers AS hastriggers, 2206 c.relrowsecurity AS rowsecurity 2207 FROM ((pg_class c 2208 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 2209 LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) 2210 WHERE (c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"])); 2211pg_timezone_abbrevs| SELECT pg_timezone_abbrevs.abbrev, 2212 pg_timezone_abbrevs.utc_offset, 2213 pg_timezone_abbrevs.is_dst 2214 FROM pg_timezone_abbrevs() pg_timezone_abbrevs(abbrev, utc_offset, is_dst); 2215pg_timezone_names| SELECT pg_timezone_names.name, 2216 pg_timezone_names.abbrev, 2217 pg_timezone_names.utc_offset, 2218 pg_timezone_names.is_dst 2219 FROM pg_timezone_names() pg_timezone_names(name, abbrev, utc_offset, is_dst); 2220pg_user| SELECT pg_shadow.usename, 2221 pg_shadow.usesysid, 2222 pg_shadow.usecreatedb, 2223 pg_shadow.usesuper, 2224 pg_shadow.userepl, 2225 pg_shadow.usebypassrls, 2226 '********'::text AS passwd, 2227 pg_shadow.valuntil, 2228 pg_shadow.useconfig 2229 FROM pg_shadow; 2230pg_user_mappings| SELECT u.oid AS umid, 2231 s.oid AS srvid, 2232 s.srvname, 2233 u.umuser, 2234 CASE 2235 WHEN (u.umuser = (0)::oid) THEN 'public'::name 2236 ELSE a.rolname 2237 END AS usename, 2238 CASE 2239 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 2240 FROM pg_authid 2241 WHERE (pg_authid.rolname = CURRENT_USER))) THEN u.umoptions 2242 ELSE NULL::text[] 2243 END AS umoptions 2244 FROM ((pg_user_mapping u 2245 JOIN pg_foreign_server s ON ((u.umserver = s.oid))) 2246 LEFT JOIN pg_authid a ON ((a.oid = u.umuser))); 2247pg_views| SELECT n.nspname AS schemaname, 2248 c.relname AS viewname, 2249 pg_get_userbyid(c.relowner) AS viewowner, 2250 pg_get_viewdef(c.oid) AS definition 2251 FROM (pg_class c 2252 LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) 2253 WHERE (c.relkind = 'v'::"char"); 2254rtest_v1| SELECT rtest_t1.a, 2255 rtest_t1.b 2256 FROM rtest_t1; 2257rtest_vcomp| SELECT x.part, 2258 (x.size * y.factor) AS size_in_cm 2259 FROM rtest_comp x, 2260 rtest_unitfact y 2261 WHERE (x.unit = y.unit); 2262rtest_vview1| SELECT x.a, 2263 x.b 2264 FROM rtest_view1 x 2265 WHERE (0 < ( SELECT count(*) AS count 2266 FROM rtest_view2 y 2267 WHERE (y.a = x.a))); 2268rtest_vview2| SELECT rtest_view1.a, 2269 rtest_view1.b 2270 FROM rtest_view1 2271 WHERE rtest_view1.v; 2272rtest_vview3| SELECT x.a, 2273 x.b 2274 FROM rtest_vview2 x 2275 WHERE (0 < ( SELECT count(*) AS count 2276 FROM rtest_view2 y 2277 WHERE (y.a = x.a))); 2278rtest_vview4| SELECT x.a, 2279 x.b, 2280 count(y.a) AS refcount 2281 FROM rtest_view1 x, 2282 rtest_view2 y 2283 WHERE (x.a = y.a) 2284 GROUP BY x.a, x.b; 2285rtest_vview5| SELECT rtest_view1.a, 2286 rtest_view1.b, 2287 rtest_viewfunc1(rtest_view1.a) AS refcount 2288 FROM rtest_view1; 2289shoe| SELECT sh.shoename, 2290 sh.sh_avail, 2291 sh.slcolor, 2292 sh.slminlen, 2293 (sh.slminlen * un.un_fact) AS slminlen_cm, 2294 sh.slmaxlen, 2295 (sh.slmaxlen * un.un_fact) AS slmaxlen_cm, 2296 sh.slunit 2297 FROM shoe_data sh, 2298 unit un 2299 WHERE (sh.slunit = un.un_name); 2300shoe_ready| SELECT rsh.shoename, 2301 rsh.sh_avail, 2302 rsl.sl_name, 2303 rsl.sl_avail, 2304 int4smaller(rsh.sh_avail, rsl.sl_avail) AS total_avail 2305 FROM shoe rsh, 2306 shoelace rsl 2307 WHERE ((rsl.sl_color = rsh.slcolor) AND (rsl.sl_len_cm >= rsh.slminlen_cm) AND (rsl.sl_len_cm <= rsh.slmaxlen_cm)); 2308shoelace| SELECT s.sl_name, 2309 s.sl_avail, 2310 s.sl_color, 2311 s.sl_len, 2312 s.sl_unit, 2313 (s.sl_len * u.un_fact) AS sl_len_cm 2314 FROM shoelace_data s, 2315 unit u 2316 WHERE (s.sl_unit = u.un_name); 2317shoelace_candelete| SELECT shoelace_obsolete.sl_name, 2318 shoelace_obsolete.sl_avail, 2319 shoelace_obsolete.sl_color, 2320 shoelace_obsolete.sl_len, 2321 shoelace_obsolete.sl_unit, 2322 shoelace_obsolete.sl_len_cm 2323 FROM shoelace_obsolete 2324 WHERE (shoelace_obsolete.sl_avail = 0); 2325shoelace_obsolete| SELECT shoelace.sl_name, 2326 shoelace.sl_avail, 2327 shoelace.sl_color, 2328 shoelace.sl_len, 2329 shoelace.sl_unit, 2330 shoelace.sl_len_cm 2331 FROM shoelace 2332 WHERE (NOT (EXISTS ( SELECT shoe.shoename 2333 FROM shoe 2334 WHERE (shoe.slcolor = shoelace.sl_color)))); 2335street| SELECT r.name, 2336 r.thepath, 2337 c.cname 2338 FROM ONLY road r, 2339 real_city c 2340 WHERE (c.outline ## r.thepath); 2341test_tablesample_v1| SELECT test_tablesample.id 2342 FROM test_tablesample TABLESAMPLE system ((10 * 2)) REPEATABLE (2); 2343test_tablesample_v2| SELECT test_tablesample.id 2344 FROM test_tablesample TABLESAMPLE system (99); 2345toyemp| SELECT emp.name, 2346 emp.age, 2347 emp.location, 2348 (12 * emp.salary) AS annualsal 2349 FROM emp; 2350SELECT tablename, rulename, definition FROM pg_rules 2351 ORDER BY tablename, rulename; 2352pg_settings|pg_settings_n|CREATE RULE pg_settings_n AS 2353 ON UPDATE TO pg_catalog.pg_settings DO INSTEAD NOTHING; 2354pg_settings|pg_settings_u|CREATE RULE pg_settings_u AS 2355 ON UPDATE TO pg_catalog.pg_settings 2356 WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, false) AS set_config; 2357rtest_emp|rtest_emp_del|CREATE RULE rtest_emp_del AS 2358 ON DELETE TO public.rtest_emp DO INSERT INTO rtest_emplog (ename, who, action, newsal, oldsal) 2359 VALUES (old.ename, CURRENT_USER, 'fired'::bpchar, '$0.00'::money, old.salary); 2360rtest_emp|rtest_emp_ins|CREATE RULE rtest_emp_ins AS 2361 ON INSERT TO public.rtest_emp DO INSERT INTO rtest_emplog (ename, who, action, newsal, oldsal) 2362 VALUES (new.ename, CURRENT_USER, 'hired'::bpchar, new.salary, '$0.00'::money); 2363rtest_emp|rtest_emp_upd|CREATE RULE rtest_emp_upd AS 2364 ON UPDATE TO public.rtest_emp 2365 WHERE (new.salary <> old.salary) DO INSERT INTO rtest_emplog (ename, who, action, newsal, oldsal) 2366 VALUES (new.ename, CURRENT_USER, 'honored'::bpchar, new.salary, old.salary); 2367rtest_nothn1|rtest_nothn_r1|CREATE RULE rtest_nothn_r1 AS 2368 ON INSERT TO public.rtest_nothn1 2369 WHERE ((new.a >= 10) AND (new.a < 20)) DO INSTEAD NOTHING; 2370rtest_nothn1|rtest_nothn_r2|CREATE RULE rtest_nothn_r2 AS 2371 ON INSERT TO public.rtest_nothn1 2372 WHERE ((new.a >= 30) AND (new.a < 40)) DO INSTEAD NOTHING; 2373rtest_nothn2|rtest_nothn_r3|CREATE RULE rtest_nothn_r3 AS 2374 ON INSERT TO public.rtest_nothn2 2375 WHERE (new.a >= 100) DO INSTEAD INSERT INTO rtest_nothn3 (a, b) 2376 VALUES (new.a, new.b); 2377rtest_nothn2|rtest_nothn_r4|CREATE RULE rtest_nothn_r4 AS 2378 ON INSERT TO public.rtest_nothn2 DO INSTEAD NOTHING; 2379rtest_order1|rtest_order_r1|CREATE RULE rtest_order_r1 AS 2380 ON INSERT TO public.rtest_order1 DO INSTEAD INSERT INTO rtest_order2 (a, b, c) 2381 VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 1 - this should run 1st'::text); 2382rtest_order1|rtest_order_r2|CREATE RULE rtest_order_r2 AS 2383 ON INSERT TO public.rtest_order1 DO INSERT INTO rtest_order2 (a, b, c) 2384 VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 2 - this should run 2nd'::text); 2385rtest_order1|rtest_order_r3|CREATE RULE rtest_order_r3 AS 2386 ON INSERT TO public.rtest_order1 DO INSTEAD INSERT INTO rtest_order2 (a, b, c) 2387 VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 3 - this should run 3rd'::text); 2388rtest_order1|rtest_order_r4|CREATE RULE rtest_order_r4 AS 2389 ON INSERT TO public.rtest_order1 2390 WHERE (new.a < 100) DO INSTEAD INSERT INTO rtest_order2 (a, b, c) 2391 VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 4 - this should run 4th'::text); 2392rtest_person|rtest_pers_del|CREATE RULE rtest_pers_del AS 2393 ON DELETE TO public.rtest_person DO DELETE FROM rtest_admin 2394 WHERE (rtest_admin.pname = old.pname); 2395rtest_person|rtest_pers_upd|CREATE RULE rtest_pers_upd AS 2396 ON UPDATE TO public.rtest_person DO UPDATE rtest_admin SET pname = new.pname 2397 WHERE (rtest_admin.pname = old.pname); 2398rtest_system|rtest_sys_del|CREATE RULE rtest_sys_del AS 2399 ON DELETE TO public.rtest_system DO ( DELETE FROM rtest_interface 2400 WHERE (rtest_interface.sysname = old.sysname); 2401 DELETE FROM rtest_admin 2402 WHERE (rtest_admin.sysname = old.sysname); 2403); 2404rtest_system|rtest_sys_upd|CREATE RULE rtest_sys_upd AS 2405 ON UPDATE TO public.rtest_system DO ( UPDATE rtest_interface SET sysname = new.sysname 2406 WHERE (rtest_interface.sysname = old.sysname); 2407 UPDATE rtest_admin SET sysname = new.sysname 2408 WHERE (rtest_admin.sysname = old.sysname); 2409); 2410rtest_t4|rtest_t4_ins1|CREATE RULE rtest_t4_ins1 AS 2411 ON INSERT TO public.rtest_t4 2412 WHERE ((new.a >= 10) AND (new.a < 20)) DO INSTEAD INSERT INTO rtest_t5 (a, b) 2413 VALUES (new.a, new.b); 2414rtest_t4|rtest_t4_ins2|CREATE RULE rtest_t4_ins2 AS 2415 ON INSERT TO public.rtest_t4 2416 WHERE ((new.a >= 20) AND (new.a < 30)) DO INSERT INTO rtest_t6 (a, b) 2417 VALUES (new.a, new.b); 2418rtest_t5|rtest_t5_ins|CREATE RULE rtest_t5_ins AS 2419 ON INSERT TO public.rtest_t5 2420 WHERE (new.a > 15) DO INSERT INTO rtest_t7 (a, b) 2421 VALUES (new.a, new.b); 2422rtest_t6|rtest_t6_ins|CREATE RULE rtest_t6_ins AS 2423 ON INSERT TO public.rtest_t6 2424 WHERE (new.a > 25) DO INSTEAD INSERT INTO rtest_t8 (a, b) 2425 VALUES (new.a, new.b); 2426rtest_v1|rtest_v1_del|CREATE RULE rtest_v1_del AS 2427 ON DELETE TO public.rtest_v1 DO INSTEAD DELETE FROM rtest_t1 2428 WHERE (rtest_t1.a = old.a); 2429rtest_v1|rtest_v1_ins|CREATE RULE rtest_v1_ins AS 2430 ON INSERT TO public.rtest_v1 DO INSTEAD INSERT INTO rtest_t1 (a, b) 2431 VALUES (new.a, new.b); 2432rtest_v1|rtest_v1_upd|CREATE RULE rtest_v1_upd AS 2433 ON UPDATE TO public.rtest_v1 DO INSTEAD UPDATE rtest_t1 SET a = new.a, b = new.b 2434 WHERE (rtest_t1.a = old.a); 2435shoelace|shoelace_del|CREATE RULE shoelace_del AS 2436 ON DELETE TO public.shoelace DO INSTEAD DELETE FROM shoelace_data 2437 WHERE (shoelace_data.sl_name = old.sl_name); 2438shoelace|shoelace_ins|CREATE RULE shoelace_ins AS 2439 ON INSERT TO public.shoelace DO INSTEAD INSERT INTO shoelace_data (sl_name, sl_avail, sl_color, sl_len, sl_unit) 2440 VALUES (new.sl_name, new.sl_avail, new.sl_color, new.sl_len, new.sl_unit); 2441shoelace|shoelace_upd|CREATE RULE shoelace_upd AS 2442 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 2443 WHERE (shoelace_data.sl_name = old.sl_name); 2444shoelace_data|log_shoelace|CREATE RULE log_shoelace AS 2445 ON UPDATE TO public.shoelace_data 2446 WHERE (new.sl_avail <> old.sl_avail) DO INSERT INTO shoelace_log (sl_name, sl_avail, log_who, log_when) 2447 VALUES (new.sl_name, new.sl_avail, 'Al Bundy'::name, 'Thu Jan 01 00:00:00 1970'::timestamp without time zone); 2448shoelace_ok|shoelace_ok_ins|CREATE RULE shoelace_ok_ins AS 2449 ON INSERT TO public.shoelace_ok DO INSTEAD UPDATE shoelace SET sl_avail = (shoelace.sl_avail + new.ok_quant) 2450 WHERE (shoelace.sl_name = new.ok_name); 2451-- restore normal output mode 2452\a\t 2453-- 2454-- CREATE OR REPLACE RULE 2455-- 2456CREATE TABLE ruletest_tbl (a int, b int); 2457CREATE TABLE ruletest_tbl2 (a int, b int); 2458CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl 2459 DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (10, 10); 2460INSERT INTO ruletest_tbl VALUES (99, 99); 2461CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl 2462 DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (1000, 1000); 2463INSERT INTO ruletest_tbl VALUES (99, 99); 2464SELECT * FROM ruletest_tbl2; 2465 a | b 2466------+------ 2467 10 | 10 2468 1000 | 1000 2469(2 rows) 2470 2471-- Check that rewrite rules splitting one INSERT into multiple 2472-- conditional statements does not disable FK checking. 2473create table rule_and_refint_t1 ( 2474 id1a integer, 2475 id1b integer, 2476 primary key (id1a, id1b) 2477); 2478create table rule_and_refint_t2 ( 2479 id2a integer, 2480 id2c integer, 2481 primary key (id2a, id2c) 2482); 2483create table rule_and_refint_t3 ( 2484 id3a integer, 2485 id3b integer, 2486 id3c integer, 2487 data text, 2488 primary key (id3a, id3b, id3c), 2489 foreign key (id3a, id3b) references rule_and_refint_t1 (id1a, id1b), 2490 foreign key (id3a, id3c) references rule_and_refint_t2 (id2a, id2c) 2491); 2492insert into rule_and_refint_t1 values (1, 11); 2493insert into rule_and_refint_t1 values (1, 12); 2494insert into rule_and_refint_t1 values (2, 21); 2495insert into rule_and_refint_t1 values (2, 22); 2496insert into rule_and_refint_t2 values (1, 11); 2497insert into rule_and_refint_t2 values (1, 12); 2498insert into rule_and_refint_t2 values (2, 21); 2499insert into rule_and_refint_t2 values (2, 22); 2500insert into rule_and_refint_t3 values (1, 11, 11, 'row1'); 2501insert into rule_and_refint_t3 values (1, 11, 12, 'row2'); 2502insert into rule_and_refint_t3 values (1, 12, 11, 'row3'); 2503insert into rule_and_refint_t3 values (1, 12, 12, 'row4'); 2504insert into rule_and_refint_t3 values (1, 11, 13, 'row5'); 2505ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey1" 2506DETAIL: Key (id3a, id3c)=(1, 13) is not present in table "rule_and_refint_t2". 2507insert into rule_and_refint_t3 values (1, 13, 11, 'row6'); 2508ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey" 2509DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1". 2510-- Ordinary table 2511insert into rule_and_refint_t3 values (1, 13, 11, 'row6') 2512 on conflict do nothing; 2513ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey" 2514DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1". 2515-- rule not fired, so fk violation 2516insert into rule_and_refint_t3 values (1, 13, 11, 'row6') 2517 on conflict (id3a, id3b, id3c) do update 2518 set id3b = excluded.id3b; 2519ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey" 2520DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1". 2521-- rule fired, so unsupported 2522insert into shoelace values ('sl9', 0, 'pink', 35.0, 'inch', 0.0) 2523 on conflict (sl_name) do update 2524 set sl_avail = excluded.sl_avail; 2525ERROR: INSERT with ON CONFLICT clause cannot be used with table that has INSERT or UPDATE rules 2526create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3 2527 where (exists (select 1 from rule_and_refint_t3 2528 where (((rule_and_refint_t3.id3a = new.id3a) 2529 and (rule_and_refint_t3.id3b = new.id3b)) 2530 and (rule_and_refint_t3.id3c = new.id3c)))) 2531 do instead update rule_and_refint_t3 set data = new.data 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)); 2535insert into rule_and_refint_t3 values (1, 11, 13, 'row7'); 2536ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey1" 2537DETAIL: Key (id3a, id3c)=(1, 13) is not present in table "rule_and_refint_t2". 2538insert into rule_and_refint_t3 values (1, 13, 11, 'row8'); 2539ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey" 2540DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1". 2541-- 2542-- disallow dropping a view's rule (bug #5072) 2543-- 2544create view fooview as select 'foo'::text; 2545drop rule "_RETURN" on fooview; 2546ERROR: cannot drop rule _RETURN on view fooview because view fooview requires it 2547HINT: You can drop view fooview instead. 2548drop view fooview; 2549-- 2550-- test conversion of table to view (needed to load some pg_dump files) 2551-- 2552create table fooview (x int, y text); 2553select xmin, * from fooview; 2554 xmin | x | y 2555------+---+--- 2556(0 rows) 2557 2558create rule "_RETURN" as on select to fooview do instead 2559 select 1 as x, 'aaa'::text as y; 2560select * from fooview; 2561 x | y 2562---+----- 2563 1 | aaa 2564(1 row) 2565 2566select xmin, * from fooview; -- fail, views don't have such a column 2567ERROR: column "xmin" does not exist 2568LINE 1: select xmin, * from fooview; 2569 ^ 2570select reltoastrelid, relkind, relfrozenxid 2571 from pg_class where oid = 'fooview'::regclass; 2572 reltoastrelid | relkind | relfrozenxid 2573---------------+---------+-------------- 2574 0 | v | 0 2575(1 row) 2576 2577drop view fooview; 2578-- cannot convert an inheritance parent or child to a view, though 2579create table fooview (x int, y text); 2580create table fooview_child () inherits (fooview); 2581create rule "_RETURN" as on select to fooview do instead 2582 select 1 as x, 'aaa'::text as y; 2583ERROR: could not convert table "fooview" to a view because it has child tables 2584create rule "_RETURN" as on select to fooview_child do instead 2585 select 1 as x, 'aaa'::text as y; 2586ERROR: could not convert table "fooview_child" to a view because it has parent tables 2587drop table fooview cascade; 2588NOTICE: drop cascades to table fooview_child 2589-- likewise, converting a partitioned table or partition to view is not allowed 2590create table fooview (x int, y text) partition by list (x); 2591create rule "_RETURN" as on select to fooview do instead 2592 select 1 as x, 'aaa'::text as y; 2593ERROR: cannot convert partitioned table "fooview" to a view 2594create table fooview_part partition of fooview for values in (1); 2595create rule "_RETURN" as on select to fooview_part do instead 2596 select 1 as x, 'aaa'::text as y; 2597ERROR: cannot convert partition "fooview_part" to a view 2598drop table fooview; 2599-- 2600-- check for planner problems with complex inherited UPDATES 2601-- 2602create table id (id serial primary key, name text); 2603-- currently, must respecify PKEY for each inherited subtable 2604create table test_1 (id integer primary key) inherits (id); 2605NOTICE: merging column "id" with inherited definition 2606create table test_2 (id integer primary key) inherits (id); 2607NOTICE: merging column "id" with inherited definition 2608create table test_3 (id integer primary key) inherits (id); 2609NOTICE: merging column "id" with inherited definition 2610insert into test_1 (name) values ('Test 1'); 2611insert into test_1 (name) values ('Test 2'); 2612insert into test_2 (name) values ('Test 3'); 2613insert into test_2 (name) values ('Test 4'); 2614insert into test_3 (name) values ('Test 5'); 2615insert into test_3 (name) values ('Test 6'); 2616create view id_ordered as select * from id order by id; 2617create rule update_id_ordered as on update to id_ordered 2618 do instead update id set name = new.name where id = old.id; 2619select * from id_ordered; 2620 id | name 2621----+-------- 2622 1 | Test 1 2623 2 | Test 2 2624 3 | Test 3 2625 4 | Test 4 2626 5 | Test 5 2627 6 | Test 6 2628(6 rows) 2629 2630update id_ordered set name = 'update 2' where id = 2; 2631update id_ordered set name = 'update 4' where id = 4; 2632update id_ordered set name = 'update 5' where id = 5; 2633select * from id_ordered; 2634 id | name 2635----+---------- 2636 1 | Test 1 2637 2 | update 2 2638 3 | Test 3 2639 4 | update 4 2640 5 | update 5 2641 6 | Test 6 2642(6 rows) 2643 2644\set VERBOSITY terse \\ -- suppress cascade details 2645drop table id cascade; 2646NOTICE: drop cascades to 4 other objects 2647\set VERBOSITY default 2648-- 2649-- check corner case where an entirely-dummy subplan is created by 2650-- constraint exclusion 2651-- 2652create temp table t1 (a integer primary key); 2653create temp table t1_1 (check (a >= 0 and a < 10)) inherits (t1); 2654create temp table t1_2 (check (a >= 10 and a < 20)) inherits (t1); 2655create rule t1_ins_1 as on insert to t1 2656 where new.a >= 0 and new.a < 10 2657 do instead 2658 insert into t1_1 values (new.a); 2659create rule t1_ins_2 as on insert to t1 2660 where new.a >= 10 and new.a < 20 2661 do instead 2662 insert into t1_2 values (new.a); 2663create rule t1_upd_1 as on update to t1 2664 where old.a >= 0 and old.a < 10 2665 do instead 2666 update t1_1 set a = new.a where a = old.a; 2667create rule t1_upd_2 as on update to t1 2668 where old.a >= 10 and old.a < 20 2669 do instead 2670 update t1_2 set a = new.a where a = old.a; 2671set constraint_exclusion = on; 2672insert into t1 select * from generate_series(5,19,1) g; 2673update t1 set a = 4 where a = 5; 2674select * from only t1; 2675 a 2676--- 2677(0 rows) 2678 2679select * from only t1_1; 2680 a 2681--- 2682 6 2683 7 2684 8 2685 9 2686 4 2687(5 rows) 2688 2689select * from only t1_2; 2690 a 2691---- 2692 10 2693 11 2694 12 2695 13 2696 14 2697 15 2698 16 2699 17 2700 18 2701 19 2702(10 rows) 2703 2704reset constraint_exclusion; 2705-- test FOR UPDATE in rules 2706create table rules_base(f1 int, f2 int); 2707insert into rules_base values(1,2), (11,12); 2708create rule r1 as on update to rules_base do instead 2709 select * from rules_base where f1 = 1 for update; 2710update rules_base set f2 = f2 + 1; 2711 f1 | f2 2712----+---- 2713 1 | 2 2714(1 row) 2715 2716create or replace rule r1 as on update to rules_base do instead 2717 select * from rules_base where f1 = 11 for update of rules_base; 2718update rules_base set f2 = f2 + 1; 2719 f1 | f2 2720----+---- 2721 11 | 12 2722(1 row) 2723 2724create or replace rule r1 as on update to rules_base do instead 2725 select * from rules_base where f1 = 11 for update of old; -- error 2726ERROR: relation "old" in FOR UPDATE clause not found in FROM clause 2727LINE 2: select * from rules_base where f1 = 11 for update of old; 2728 ^ 2729drop table rules_base; 2730-- test various flavors of pg_get_viewdef() 2731select pg_get_viewdef('shoe'::regclass) as unpretty; 2732 unpretty 2733------------------------------------------------ 2734 SELECT sh.shoename, + 2735 sh.sh_avail, + 2736 sh.slcolor, + 2737 sh.slminlen, + 2738 (sh.slminlen * un.un_fact) AS slminlen_cm,+ 2739 sh.slmaxlen, + 2740 (sh.slmaxlen * un.un_fact) AS slmaxlen_cm,+ 2741 sh.slunit + 2742 FROM shoe_data sh, + 2743 unit un + 2744 WHERE (sh.slunit = un.un_name); 2745(1 row) 2746 2747select pg_get_viewdef('shoe'::regclass,true) as pretty; 2748 pretty 2749---------------------------------------------- 2750 SELECT sh.shoename, + 2751 sh.sh_avail, + 2752 sh.slcolor, + 2753 sh.slminlen, + 2754 sh.slminlen * un.un_fact AS slminlen_cm,+ 2755 sh.slmaxlen, + 2756 sh.slmaxlen * un.un_fact AS slmaxlen_cm,+ 2757 sh.slunit + 2758 FROM shoe_data sh, + 2759 unit un + 2760 WHERE sh.slunit = un.un_name; 2761(1 row) 2762 2763select pg_get_viewdef('shoe'::regclass,0) as prettier; 2764 prettier 2765---------------------------------------------- 2766 SELECT sh.shoename, + 2767 sh.sh_avail, + 2768 sh.slcolor, + 2769 sh.slminlen, + 2770 sh.slminlen * un.un_fact AS slminlen_cm,+ 2771 sh.slmaxlen, + 2772 sh.slmaxlen * un.un_fact AS slmaxlen_cm,+ 2773 sh.slunit + 2774 FROM shoe_data sh, + 2775 unit un + 2776 WHERE sh.slunit = un.un_name; 2777(1 row) 2778 2779-- 2780-- check multi-row VALUES in rules 2781-- 2782create table rules_src(f1 int, f2 int); 2783create table rules_log(f1 int, f2 int, tag text); 2784insert into rules_src values(1,2), (11,12); 2785create rule r1 as on update to rules_src do also 2786 insert into rules_log values(old.*, 'old'), (new.*, 'new'); 2787update rules_src set f2 = f2 + 1; 2788update rules_src set f2 = f2 * 10; 2789select * from rules_src; 2790 f1 | f2 2791----+----- 2792 1 | 30 2793 11 | 130 2794(2 rows) 2795 2796select * from rules_log; 2797 f1 | f2 | tag 2798----+-----+----- 2799 1 | 2 | old 2800 1 | 3 | new 2801 11 | 12 | old 2802 11 | 13 | new 2803 1 | 3 | old 2804 1 | 30 | new 2805 11 | 13 | old 2806 11 | 130 | new 2807(8 rows) 2808 2809create rule r2 as on update to rules_src do also 2810 values(old.*, 'old'), (new.*, 'new'); 2811update rules_src set f2 = f2 / 10; 2812 column1 | column2 | column3 2813---------+---------+--------- 2814 1 | 30 | old 2815 1 | 3 | new 2816 11 | 130 | old 2817 11 | 13 | new 2818(4 rows) 2819 2820select * from rules_src; 2821 f1 | f2 2822----+---- 2823 1 | 3 2824 11 | 13 2825(2 rows) 2826 2827select * from rules_log; 2828 f1 | f2 | tag 2829----+-----+----- 2830 1 | 2 | old 2831 1 | 3 | new 2832 11 | 12 | old 2833 11 | 13 | new 2834 1 | 3 | old 2835 1 | 30 | new 2836 11 | 13 | old 2837 11 | 130 | new 2838 1 | 30 | old 2839 1 | 3 | new 2840 11 | 130 | old 2841 11 | 13 | new 2842(12 rows) 2843 2844create rule r3 as on delete to rules_src do notify rules_src_deletion; 2845\d+ rules_src 2846 Table "public.rules_src" 2847 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 2848--------+---------+-----------+----------+---------+---------+--------------+------------- 2849 f1 | integer | | | | plain | | 2850 f2 | integer | | | | plain | | 2851Rules: 2852 r1 AS 2853 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) 2854 r2 AS 2855 ON UPDATE TO rules_src DO VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text) 2856 r3 AS 2857 ON DELETE TO rules_src DO 2858 NOTIFY rules_src_deletion 2859 2860-- 2861-- Ensure an aliased target relation for insert is correctly deparsed. 2862-- 2863create rule r4 as on insert to rules_src do instead insert into rules_log AS trgt SELECT NEW.* RETURNING trgt.f1, trgt.f2; 2864create rule r5 as on update to rules_src do instead UPDATE rules_log AS trgt SET tag = 'updated' WHERE trgt.f1 = new.f1; 2865\d+ rules_src 2866 Table "public.rules_src" 2867 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 2868--------+---------+-----------+----------+---------+---------+--------------+------------- 2869 f1 | integer | | | | plain | | 2870 f2 | integer | | | | plain | | 2871Rules: 2872 r1 AS 2873 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) 2874 r2 AS 2875 ON UPDATE TO rules_src DO VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text) 2876 r3 AS 2877 ON DELETE TO rules_src DO 2878 NOTIFY rules_src_deletion 2879 r4 AS 2880 ON INSERT TO rules_src DO INSTEAD INSERT INTO rules_log AS trgt (f1, f2) SELECT new.f1, 2881 new.f2 2882 RETURNING trgt.f1, 2883 trgt.f2 2884 r5 AS 2885 ON UPDATE TO rules_src DO INSTEAD UPDATE rules_log trgt SET tag = 'updated'::text 2886 WHERE trgt.f1 = new.f1 2887 2888-- 2889-- check alter rename rule 2890-- 2891CREATE TABLE rule_t1 (a INT); 2892CREATE VIEW rule_v1 AS SELECT * FROM rule_t1; 2893CREATE RULE InsertRule AS 2894 ON INSERT TO rule_v1 2895 DO INSTEAD 2896 INSERT INTO rule_t1 VALUES(new.a); 2897ALTER RULE InsertRule ON rule_v1 RENAME to NewInsertRule; 2898INSERT INTO rule_v1 VALUES(1); 2899SELECT * FROM rule_v1; 2900 a 2901--- 2902 1 2903(1 row) 2904 2905\d+ rule_v1 2906 View "public.rule_v1" 2907 Column | Type | Collation | Nullable | Default | Storage | Description 2908--------+---------+-----------+----------+---------+---------+------------- 2909 a | integer | | | | plain | 2910View definition: 2911 SELECT rule_t1.a 2912 FROM rule_t1; 2913Rules: 2914 newinsertrule AS 2915 ON INSERT TO rule_v1 DO INSTEAD INSERT INTO rule_t1 (a) 2916 VALUES (new.a) 2917 2918-- 2919-- error conditions for alter rename rule 2920-- 2921ALTER RULE InsertRule ON rule_v1 RENAME TO NewInsertRule; -- doesn't exist 2922ERROR: rule "insertrule" for relation "rule_v1" does not exist 2923ALTER RULE NewInsertRule ON rule_v1 RENAME TO "_RETURN"; -- already exists 2924ERROR: rule "_RETURN" for relation "rule_v1" already exists 2925ALTER RULE "_RETURN" ON rule_v1 RENAME TO abc; -- ON SELECT rule cannot be renamed 2926ERROR: renaming an ON SELECT rule is not allowed 2927DROP VIEW rule_v1; 2928DROP TABLE rule_t1; 2929-- 2930-- check display of VALUES in view definitions 2931-- 2932create view rule_v1 as values(1,2); 2933\d+ rule_v1 2934 View "public.rule_v1" 2935 Column | Type | Collation | Nullable | Default | Storage | Description 2936---------+---------+-----------+----------+---------+---------+------------- 2937 column1 | integer | | | | plain | 2938 column2 | integer | | | | plain | 2939View definition: 2940 VALUES (1,2); 2941 2942alter table rule_v1 rename column column2 to q2; 2943\d+ rule_v1 2944 View "public.rule_v1" 2945 Column | Type | Collation | Nullable | Default | Storage | Description 2946---------+---------+-----------+----------+---------+---------+------------- 2947 column1 | integer | | | | plain | 2948 q2 | integer | | | | plain | 2949View definition: 2950 SELECT "*VALUES*".column1, 2951 "*VALUES*".column2 AS q2 2952 FROM (VALUES (1,2)) "*VALUES*"; 2953 2954drop view rule_v1; 2955create view rule_v1(x) as values(1,2); 2956\d+ rule_v1 2957 View "public.rule_v1" 2958 Column | Type | Collation | Nullable | Default | Storage | Description 2959---------+---------+-----------+----------+---------+---------+------------- 2960 x | integer | | | | plain | 2961 column2 | integer | | | | plain | 2962View definition: 2963 SELECT "*VALUES*".column1 AS x, 2964 "*VALUES*".column2 2965 FROM (VALUES (1,2)) "*VALUES*"; 2966 2967drop view rule_v1; 2968create view rule_v1(x) as select * from (values(1,2)) v; 2969\d+ rule_v1 2970 View "public.rule_v1" 2971 Column | Type | Collation | Nullable | Default | Storage | Description 2972---------+---------+-----------+----------+---------+---------+------------- 2973 x | integer | | | | plain | 2974 column2 | integer | | | | plain | 2975View definition: 2976 SELECT v.column1 AS x, 2977 v.column2 2978 FROM ( VALUES (1,2)) v; 2979 2980drop view rule_v1; 2981create view rule_v1(x) as select * from (values(1,2)) v(q,w); 2982\d+ rule_v1 2983 View "public.rule_v1" 2984 Column | Type | Collation | Nullable | Default | Storage | Description 2985--------+---------+-----------+----------+---------+---------+------------- 2986 x | integer | | | | plain | 2987 w | integer | | | | plain | 2988View definition: 2989 SELECT v.q AS x, 2990 v.w 2991 FROM ( VALUES (1,2)) v(q, w); 2992 2993drop view rule_v1; 2994-- 2995-- Check DO INSTEAD rules with ON CONFLICT 2996-- 2997CREATE TABLE hats ( 2998 hat_name char(10) primary key, 2999 hat_color char(10) -- hat color 3000); 3001CREATE TABLE hat_data ( 3002 hat_name char(10), 3003 hat_color char(10) -- hat color 3004); 3005create unique index hat_data_unique_idx 3006 on hat_data (hat_name COLLATE "C" bpchar_pattern_ops); 3007-- DO NOTHING with ON CONFLICT 3008CREATE RULE hat_nosert AS ON INSERT TO hats 3009 DO INSTEAD 3010 INSERT INTO hat_data VALUES ( 3011 NEW.hat_name, 3012 NEW.hat_color) 3013 ON CONFLICT (hat_name COLLATE "C" bpchar_pattern_ops) WHERE hat_color = 'green' 3014 DO NOTHING 3015 RETURNING *; 3016SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename; 3017 definition 3018--------------------------------------------------------------------------------------------- 3019 CREATE RULE hat_nosert AS + 3020 ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) + 3021 VALUES (new.hat_name, new.hat_color) ON CONFLICT(hat_name COLLATE "C" bpchar_pattern_ops)+ 3022 WHERE (hat_color = 'green'::bpchar) DO NOTHING + 3023 RETURNING hat_data.hat_name, + 3024 hat_data.hat_color; 3025(1 row) 3026 3027-- Works (projects row) 3028INSERT INTO hats VALUES ('h7', 'black') RETURNING *; 3029 hat_name | hat_color 3030------------+------------ 3031 h7 | black 3032(1 row) 3033 3034-- Works (does nothing) 3035INSERT INTO hats VALUES ('h7', 'black') RETURNING *; 3036 hat_name | hat_color 3037----------+----------- 3038(0 rows) 3039 3040SELECT tablename, rulename, definition FROM pg_rules 3041 WHERE tablename = 'hats'; 3042 tablename | rulename | definition 3043-----------+------------+--------------------------------------------------------------------------------------------- 3044 hats | hat_nosert | CREATE RULE hat_nosert AS + 3045 | | ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) + 3046 | | VALUES (new.hat_name, new.hat_color) ON CONFLICT(hat_name COLLATE "C" bpchar_pattern_ops)+ 3047 | | WHERE (hat_color = 'green'::bpchar) DO NOTHING + 3048 | | RETURNING hat_data.hat_name, + 3049 | | hat_data.hat_color; 3050(1 row) 3051 3052DROP RULE hat_nosert ON hats; 3053-- DO NOTHING without ON CONFLICT 3054CREATE RULE hat_nosert_all AS ON INSERT TO hats 3055 DO INSTEAD 3056 INSERT INTO hat_data VALUES ( 3057 NEW.hat_name, 3058 NEW.hat_color) 3059 ON CONFLICT 3060 DO NOTHING 3061 RETURNING *; 3062SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename; 3063 definition 3064------------------------------------------------------------------------------------- 3065 CREATE RULE hat_nosert_all AS + 3066 ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color)+ 3067 VALUES (new.hat_name, new.hat_color) ON CONFLICT DO NOTHING + 3068 RETURNING hat_data.hat_name, + 3069 hat_data.hat_color; 3070(1 row) 3071 3072DROP RULE hat_nosert_all ON hats; 3073-- Works (does nothing) 3074INSERT INTO hats VALUES ('h7', 'black') RETURNING *; 3075 hat_name | hat_color 3076------------+------------ 3077 h7 | black 3078(1 row) 3079 3080-- DO UPDATE with a WHERE clause 3081CREATE RULE hat_upsert AS ON INSERT TO hats 3082 DO INSTEAD 3083 INSERT INTO hat_data VALUES ( 3084 NEW.hat_name, 3085 NEW.hat_color) 3086 ON CONFLICT (hat_name) 3087 DO UPDATE 3088 SET hat_name = hat_data.hat_name, hat_color = excluded.hat_color 3089 WHERE excluded.hat_color <> 'forbidden' AND hat_data.* != excluded.* 3090 RETURNING *; 3091SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename; 3092 definition 3093----------------------------------------------------------------------------------------------------------------------------------------- 3094 CREATE RULE hat_upsert AS + 3095 ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) + 3096 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+ 3097 WHERE ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*)) + 3098 RETURNING hat_data.hat_name, + 3099 hat_data.hat_color; 3100(1 row) 3101 3102-- Works (does upsert) 3103INSERT INTO hats VALUES ('h8', 'black') RETURNING *; 3104 hat_name | hat_color 3105------------+------------ 3106 h8 | black 3107(1 row) 3108 3109SELECT * FROM hat_data WHERE hat_name = 'h8'; 3110 hat_name | hat_color 3111------------+------------ 3112 h8 | black 3113(1 row) 3114 3115INSERT INTO hats VALUES ('h8', 'white') RETURNING *; 3116 hat_name | hat_color 3117------------+------------ 3118 h8 | white 3119(1 row) 3120 3121SELECT * FROM hat_data WHERE hat_name = 'h8'; 3122 hat_name | hat_color 3123------------+------------ 3124 h8 | white 3125(1 row) 3126 3127INSERT INTO hats VALUES ('h8', 'forbidden') RETURNING *; 3128 hat_name | hat_color 3129----------+----------- 3130(0 rows) 3131 3132SELECT * FROM hat_data WHERE hat_name = 'h8'; 3133 hat_name | hat_color 3134------------+------------ 3135 h8 | white 3136(1 row) 3137 3138SELECT tablename, rulename, definition FROM pg_rules 3139 WHERE tablename = 'hats'; 3140 tablename | rulename | definition 3141-----------+------------+----------------------------------------------------------------------------------------------------------------------------------------- 3142 hats | hat_upsert | CREATE RULE hat_upsert AS + 3143 | | ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) + 3144 | | 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+ 3145 | | WHERE ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*)) + 3146 | | RETURNING hat_data.hat_name, + 3147 | | hat_data.hat_color; 3148(1 row) 3149 3150-- ensure explain works for on insert conflict rules 3151explain (costs off) INSERT INTO hats VALUES ('h8', 'forbidden') RETURNING *; 3152 QUERY PLAN 3153------------------------------------------------------------------------------------------------- 3154 Insert on hat_data 3155 Conflict Resolution: UPDATE 3156 Conflict Arbiter Indexes: hat_data_unique_idx 3157 Conflict Filter: ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*)) 3158 -> Result 3159(5 rows) 3160 3161-- ensure upserting into a rule, with a CTE (different offsets!) works 3162WITH data(hat_name, hat_color) AS ( 3163 VALUES ('h8', 'green'), 3164 ('h9', 'blue'), 3165 ('h7', 'forbidden') 3166) 3167INSERT INTO hats 3168 SELECT * FROM data 3169RETURNING *; 3170 hat_name | hat_color 3171------------+------------ 3172 h8 | green 3173 h9 | blue 3174(2 rows) 3175 3176EXPLAIN (costs off) WITH data(hat_name, hat_color) AS ( 3177 VALUES ('h8', 'green'), 3178 ('h9', 'blue'), 3179 ('h7', 'forbidden') 3180) 3181INSERT INTO hats 3182 SELECT * FROM data 3183RETURNING *; 3184 QUERY PLAN 3185------------------------------------------------------------------------------------------------- 3186 Insert on hat_data 3187 Conflict Resolution: UPDATE 3188 Conflict Arbiter Indexes: hat_data_unique_idx 3189 Conflict Filter: ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*)) 3190 CTE data 3191 -> Values Scan on "*VALUES*" 3192 -> CTE Scan on data 3193(7 rows) 3194 3195SELECT * FROM hat_data WHERE hat_name IN ('h8', 'h9', 'h7') ORDER BY hat_name; 3196 hat_name | hat_color 3197------------+------------ 3198 h7 | black 3199 h8 | green 3200 h9 | blue 3201(3 rows) 3202 3203DROP RULE hat_upsert ON hats; 3204drop table hats; 3205drop table hat_data; 3206-- test for pg_get_functiondef properly regurgitating SET parameters 3207-- Note that the function is kept around to stress pg_dump. 3208CREATE FUNCTION func_with_set_params() RETURNS integer 3209 AS 'select 1;' 3210 LANGUAGE SQL 3211 SET search_path TO PG_CATALOG 3212 SET extra_float_digits TO 2 3213 SET work_mem TO '4MB' 3214 SET datestyle to iso, mdy 3215 SET local_preload_libraries TO "Mixed/Case", 'c:/''a"/path', '', '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789' 3216 IMMUTABLE STRICT; 3217SELECT pg_get_functiondef('func_with_set_params()'::regprocedure); 3218 pg_get_functiondef 3219-------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 3220 CREATE OR REPLACE FUNCTION public.func_with_set_params() + 3221 RETURNS integer + 3222 LANGUAGE sql + 3223 IMMUTABLE STRICT + 3224 SET search_path TO 'pg_catalog' + 3225 SET extra_float_digits TO '2' + 3226 SET work_mem TO '4MB' + 3227 SET "DateStyle" TO 'iso, mdy' + 3228 SET local_preload_libraries TO 'Mixed/Case', 'c:/''a"/path', '', '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789'+ 3229 AS $function$select 1;$function$ + 3230 3231(1 row) 3232 3233-- tests for pg_get_*def with invalid objects 3234SELECT pg_get_constraintdef(0); 3235 pg_get_constraintdef 3236---------------------- 3237 3238(1 row) 3239 3240SELECT pg_get_functiondef(0); 3241 pg_get_functiondef 3242-------------------- 3243 3244(1 row) 3245 3246SELECT pg_get_indexdef(0); 3247 pg_get_indexdef 3248----------------- 3249 3250(1 row) 3251 3252SELECT pg_get_ruledef(0); 3253 pg_get_ruledef 3254---------------- 3255 3256(1 row) 3257 3258SELECT pg_get_statisticsobjdef(0); 3259 pg_get_statisticsobjdef 3260------------------------- 3261 3262(1 row) 3263 3264SELECT pg_get_triggerdef(0); 3265 pg_get_triggerdef 3266------------------- 3267 3268(1 row) 3269 3270SELECT pg_get_viewdef(0); 3271 pg_get_viewdef 3272---------------- 3273 3274(1 row) 3275 3276SELECT pg_get_function_arguments(0); 3277 pg_get_function_arguments 3278--------------------------- 3279 3280(1 row) 3281 3282SELECT pg_get_function_identity_arguments(0); 3283 pg_get_function_identity_arguments 3284------------------------------------ 3285 3286(1 row) 3287 3288SELECT pg_get_function_result(0); 3289 pg_get_function_result 3290------------------------ 3291 3292(1 row) 3293 3294SELECT pg_get_function_arg_default(0, 0); 3295 pg_get_function_arg_default 3296----------------------------- 3297 3298(1 row) 3299 3300SELECT pg_get_function_arg_default('pg_class'::regclass, 0); 3301 pg_get_function_arg_default 3302----------------------------- 3303 3304(1 row) 3305 3306SELECT pg_get_partkeydef(0); 3307 pg_get_partkeydef 3308------------------- 3309 3310(1 row) 3311 3312-- test rename for a rule defined on a partitioned table 3313CREATE TABLE parted_table (a int) PARTITION BY LIST (a); 3314CREATE TABLE parted_table_1 PARTITION OF parted_table FOR VALUES IN (1); 3315CREATE RULE parted_table_insert AS ON INSERT to parted_table 3316 DO INSTEAD INSERT INTO parted_table_1 VALUES (NEW.*); 3317ALTER RULE parted_table_insert ON parted_table RENAME TO parted_table_insert_redirect; 3318DROP TABLE parted_table; 3319