1-- 2-- CREATE_VIEW 3-- Virtual class definitions 4-- (this also tests the query rewrite system) 5-- 6CREATE VIEW street AS 7 SELECT r.name, r.thepath, c.cname AS cname 8 FROM ONLY road r, real_city c 9 WHERE c.outline ## r.thepath; 10CREATE VIEW iexit AS 11 SELECT ih.name, ih.thepath, 12 interpt_pp(ih.thepath, r.thepath) AS exit 13 FROM ihighway ih, ramp r 14 WHERE ih.thepath ## r.thepath; 15CREATE VIEW toyemp AS 16 SELECT name, age, location, 12*salary AS annualsal 17 FROM emp; 18-- Test comments 19COMMENT ON VIEW noview IS 'no view'; 20ERROR: relation "noview" does not exist 21COMMENT ON VIEW toyemp IS 'is a view'; 22COMMENT ON VIEW toyemp IS NULL; 23-- These views are left around mainly to exercise special cases in pg_dump. 24CREATE TABLE view_base_table (key int PRIMARY KEY, data varchar(20)); 25CREATE VIEW key_dependent_view AS 26 SELECT * FROM view_base_table GROUP BY key; 27ALTER TABLE view_base_table DROP CONSTRAINT view_base_table_pkey; -- fails 28ERROR: cannot drop constraint view_base_table_pkey on table view_base_table because other objects depend on it 29DETAIL: view key_dependent_view depends on constraint view_base_table_pkey on table view_base_table 30HINT: Use DROP ... CASCADE to drop the dependent objects too. 31CREATE VIEW key_dependent_view_no_cols AS 32 SELECT FROM view_base_table GROUP BY key HAVING length(data) > 0; 33-- 34-- CREATE OR REPLACE VIEW 35-- 36CREATE TABLE viewtest_tbl (a int, b int); 37COPY viewtest_tbl FROM stdin; 38CREATE OR REPLACE VIEW viewtest AS 39 SELECT * FROM viewtest_tbl; 40CREATE OR REPLACE VIEW viewtest AS 41 SELECT * FROM viewtest_tbl WHERE a > 10; 42SELECT * FROM viewtest; 43 a | b 44----+---- 45 15 | 20 46 20 | 25 47(2 rows) 48 49CREATE OR REPLACE VIEW viewtest AS 50 SELECT a, b FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC; 51SELECT * FROM viewtest; 52 a | b 53----+---- 54 20 | 25 55 15 | 20 56 10 | 15 57(3 rows) 58 59-- should fail 60CREATE OR REPLACE VIEW viewtest AS 61 SELECT a FROM viewtest_tbl WHERE a <> 20; 62ERROR: cannot drop columns from view 63-- should fail 64CREATE OR REPLACE VIEW viewtest AS 65 SELECT 1, * FROM viewtest_tbl; 66ERROR: cannot change name of view column "a" to "?column?" 67-- should fail 68CREATE OR REPLACE VIEW viewtest AS 69 SELECT a, b::numeric FROM viewtest_tbl; 70ERROR: cannot change data type of view column "b" from integer to numeric 71-- should work 72CREATE OR REPLACE VIEW viewtest AS 73 SELECT a, b, 0 AS c FROM viewtest_tbl; 74DROP VIEW viewtest; 75DROP TABLE viewtest_tbl; 76-- tests for temporary views 77CREATE SCHEMA temp_view_test 78 CREATE TABLE base_table (a int, id int) 79 CREATE TABLE base_table2 (a int, id int); 80SET search_path TO temp_view_test, public; 81CREATE TEMPORARY TABLE temp_table (a int, id int); 82-- should be created in temp_view_test schema 83CREATE VIEW v1 AS SELECT * FROM base_table; 84-- should be created in temp object schema 85CREATE VIEW v1_temp AS SELECT * FROM temp_table; 86NOTICE: view "v1_temp" will be a temporary view 87-- should be created in temp object schema 88CREATE TEMP VIEW v2_temp AS SELECT * FROM base_table; 89-- should be created in temp_views schema 90CREATE VIEW temp_view_test.v2 AS SELECT * FROM base_table; 91-- should fail 92CREATE VIEW temp_view_test.v3_temp AS SELECT * FROM temp_table; 93NOTICE: view "v3_temp" will be a temporary view 94ERROR: cannot create temporary relation in non-temporary schema 95-- should fail 96CREATE SCHEMA test_schema 97 CREATE TEMP VIEW testview AS SELECT 1; 98ERROR: cannot create temporary relation in non-temporary schema 99-- joins: if any of the join relations are temporary, the view 100-- should also be temporary 101-- should be non-temp 102CREATE VIEW v3 AS 103 SELECT t1.a AS t1_a, t2.a AS t2_a 104 FROM base_table t1, base_table2 t2 105 WHERE t1.id = t2.id; 106-- should be temp (one join rel is temp) 107CREATE VIEW v4_temp AS 108 SELECT t1.a AS t1_a, t2.a AS t2_a 109 FROM base_table t1, temp_table t2 110 WHERE t1.id = t2.id; 111NOTICE: view "v4_temp" will be a temporary view 112-- should be temp 113CREATE VIEW v5_temp AS 114 SELECT t1.a AS t1_a, t2.a AS t2_a, t3.a AS t3_a 115 FROM base_table t1, base_table2 t2, temp_table t3 116 WHERE t1.id = t2.id and t2.id = t3.id; 117NOTICE: view "v5_temp" will be a temporary view 118-- subqueries 119CREATE VIEW v4 AS SELECT * FROM base_table WHERE id IN (SELECT id FROM base_table2); 120CREATE VIEW v5 AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM base_table2) t2; 121CREATE VIEW v6 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM base_table2); 122CREATE VIEW v7 AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM base_table2); 123CREATE VIEW v8 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1); 124CREATE VIEW v6_temp AS SELECT * FROM base_table WHERE id IN (SELECT id FROM temp_table); 125NOTICE: view "v6_temp" will be a temporary view 126CREATE VIEW v7_temp AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM temp_table) t2; 127NOTICE: view "v7_temp" will be a temporary view 128CREATE VIEW v8_temp AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM temp_table); 129NOTICE: view "v8_temp" will be a temporary view 130CREATE VIEW v9_temp AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM temp_table); 131NOTICE: view "v9_temp" will be a temporary view 132-- a view should also be temporary if it references a temporary view 133CREATE VIEW v10_temp AS SELECT * FROM v7_temp; 134NOTICE: view "v10_temp" will be a temporary view 135CREATE VIEW v11_temp AS SELECT t1.id, t2.a FROM base_table t1, v10_temp t2; 136NOTICE: view "v11_temp" will be a temporary view 137CREATE VIEW v12_temp AS SELECT true FROM v11_temp; 138NOTICE: view "v12_temp" will be a temporary view 139-- a view should also be temporary if it references a temporary sequence 140CREATE SEQUENCE seq1; 141CREATE TEMPORARY SEQUENCE seq1_temp; 142CREATE VIEW v9 AS SELECT seq1.is_called FROM seq1; 143CREATE VIEW v13_temp AS SELECT seq1_temp.is_called FROM seq1_temp; 144NOTICE: view "v13_temp" will be a temporary view 145SELECT relname FROM pg_class 146 WHERE relname LIKE 'v_' 147 AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'temp_view_test') 148 ORDER BY relname; 149 relname 150--------- 151 v1 152 v2 153 v3 154 v4 155 v5 156 v6 157 v7 158 v8 159 v9 160(9 rows) 161 162SELECT relname FROM pg_class 163 WHERE relname LIKE 'v%' 164 AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%') 165 ORDER BY relname; 166 relname 167---------- 168 v10_temp 169 v11_temp 170 v12_temp 171 v13_temp 172 v1_temp 173 v2_temp 174 v4_temp 175 v5_temp 176 v6_temp 177 v7_temp 178 v8_temp 179 v9_temp 180(12 rows) 181 182CREATE SCHEMA testviewschm2; 183SET search_path TO testviewschm2, public; 184CREATE TABLE t1 (num int, name text); 185CREATE TABLE t2 (num2 int, value text); 186CREATE TEMP TABLE tt (num2 int, value text); 187CREATE VIEW nontemp1 AS SELECT * FROM t1 CROSS JOIN t2; 188CREATE VIEW temporal1 AS SELECT * FROM t1 CROSS JOIN tt; 189NOTICE: view "temporal1" will be a temporary view 190CREATE VIEW nontemp2 AS SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num2; 191CREATE VIEW temporal2 AS SELECT * FROM t1 INNER JOIN tt ON t1.num = tt.num2; 192NOTICE: view "temporal2" will be a temporary view 193CREATE VIEW nontemp3 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2; 194CREATE VIEW temporal3 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2; 195NOTICE: view "temporal3" will be a temporary view 196CREATE VIEW nontemp4 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2 AND t2.value = 'xxx'; 197CREATE VIEW temporal4 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2 AND tt.value = 'xxx'; 198NOTICE: view "temporal4" will be a temporary view 199SELECT relname FROM pg_class 200 WHERE relname LIKE 'nontemp%' 201 AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'testviewschm2') 202 ORDER BY relname; 203 relname 204---------- 205 nontemp1 206 nontemp2 207 nontemp3 208 nontemp4 209(4 rows) 210 211SELECT relname FROM pg_class 212 WHERE relname LIKE 'temporal%' 213 AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%') 214 ORDER BY relname; 215 relname 216----------- 217 temporal1 218 temporal2 219 temporal3 220 temporal4 221(4 rows) 222 223CREATE TABLE tbl1 ( a int, b int); 224CREATE TABLE tbl2 (c int, d int); 225CREATE TABLE tbl3 (e int, f int); 226CREATE TABLE tbl4 (g int, h int); 227CREATE TEMP TABLE tmptbl (i int, j int); 228--Should be in testviewschm2 229CREATE VIEW pubview AS SELECT * FROM tbl1 WHERE tbl1.a 230BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2) 231AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f); 232SELECT count(*) FROM pg_class where relname = 'pubview' 233AND relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname = 'testviewschm2'); 234 count 235------- 236 1 237(1 row) 238 239--Should be in temp object schema 240CREATE VIEW mytempview AS SELECT * FROM tbl1 WHERE tbl1.a 241BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2) 242AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f) 243AND NOT EXISTS (SELECT g FROM tbl4 LEFT JOIN tmptbl ON tbl4.h = tmptbl.j); 244NOTICE: view "mytempview" will be a temporary view 245SELECT count(*) FROM pg_class where relname LIKE 'mytempview' 246And relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname LIKE 'pg_temp%'); 247 count 248------- 249 1 250(1 row) 251 252-- 253-- CREATE VIEW and WITH(...) clause 254-- 255CREATE VIEW mysecview1 256 AS SELECT * FROM tbl1 WHERE a = 0; 257CREATE VIEW mysecview2 WITH (security_barrier=true) 258 AS SELECT * FROM tbl1 WHERE a > 0; 259CREATE VIEW mysecview3 WITH (security_barrier=false) 260 AS SELECT * FROM tbl1 WHERE a < 0; 261CREATE VIEW mysecview4 WITH (security_barrier) 262 AS SELECT * FROM tbl1 WHERE a <> 0; 263CREATE VIEW mysecview5 WITH (security_barrier=100) -- Error 264 AS SELECT * FROM tbl1 WHERE a > 100; 265ERROR: invalid value for boolean option "security_barrier": 100 266CREATE VIEW mysecview6 WITH (invalid_option) -- Error 267 AS SELECT * FROM tbl1 WHERE a < 100; 268ERROR: unrecognized parameter "invalid_option" 269SELECT relname, relkind, reloptions FROM pg_class 270 WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass, 271 'mysecview3'::regclass, 'mysecview4'::regclass) 272 ORDER BY relname; 273 relname | relkind | reloptions 274------------+---------+-------------------------- 275 mysecview1 | v | 276 mysecview2 | v | {security_barrier=true} 277 mysecview3 | v | {security_barrier=false} 278 mysecview4 | v | {security_barrier=true} 279(4 rows) 280 281CREATE OR REPLACE VIEW mysecview1 282 AS SELECT * FROM tbl1 WHERE a = 256; 283CREATE OR REPLACE VIEW mysecview2 284 AS SELECT * FROM tbl1 WHERE a > 256; 285CREATE OR REPLACE VIEW mysecview3 WITH (security_barrier=true) 286 AS SELECT * FROM tbl1 WHERE a < 256; 287CREATE OR REPLACE VIEW mysecview4 WITH (security_barrier=false) 288 AS SELECT * FROM tbl1 WHERE a <> 256; 289SELECT relname, relkind, reloptions FROM pg_class 290 WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass, 291 'mysecview3'::regclass, 'mysecview4'::regclass) 292 ORDER BY relname; 293 relname | relkind | reloptions 294------------+---------+-------------------------- 295 mysecview1 | v | 296 mysecview2 | v | 297 mysecview3 | v | {security_barrier=true} 298 mysecview4 | v | {security_barrier=false} 299(4 rows) 300 301-- Check that unknown literals are converted to "text" in CREATE VIEW, 302-- so that we don't end up with unknown-type columns. 303CREATE VIEW unspecified_types AS 304 SELECT 42 as i, 42.5 as num, 'foo' as u, 'foo'::unknown as u2, null as n; 305\d+ unspecified_types 306 View "testviewschm2.unspecified_types" 307 Column | Type | Collation | Nullable | Default | Storage | Description 308--------+---------+-----------+----------+---------+----------+------------- 309 i | integer | | | | plain | 310 num | numeric | | | | main | 311 u | text | | | | extended | 312 u2 | text | | | | extended | 313 n | text | | | | extended | 314View definition: 315 SELECT 42 AS i, 316 42.5 AS num, 317 'foo'::text AS u, 318 'foo'::text AS u2, 319 NULL::text AS n; 320 321SELECT * FROM unspecified_types; 322 i | num | u | u2 | n 323----+------+-----+-----+--- 324 42 | 42.5 | foo | foo | 325(1 row) 326 327-- This test checks that proper typmods are assigned in a multi-row VALUES 328CREATE VIEW tt1 AS 329 SELECT * FROM ( 330 VALUES 331 ('abc'::varchar(3), '0123456789', 42, 'abcd'::varchar(4)), 332 ('0123456789', 'abc'::varchar(3), 42.12, 'abc'::varchar(4)) 333 ) vv(a,b,c,d); 334\d+ tt1 335 View "testviewschm2.tt1" 336 Column | Type | Collation | Nullable | Default | Storage | Description 337--------+----------------------+-----------+----------+---------+----------+------------- 338 a | character varying | | | | extended | 339 b | character varying | | | | extended | 340 c | numeric | | | | main | 341 d | character varying(4) | | | | extended | 342View definition: 343 SELECT vv.a, 344 vv.b, 345 vv.c, 346 vv.d 347 FROM ( VALUES ('abc'::character varying(3),'0123456789'::character varying,42,'abcd'::character varying(4)), ('0123456789'::character varying,'abc'::character varying(3),42.12,'abc'::character varying(4))) vv(a, b, c, d); 348 349SELECT * FROM tt1; 350 a | b | c | d 351------------+------------+-------+------ 352 abc | 0123456789 | 42 | abcd 353 0123456789 | abc | 42.12 | abc 354(2 rows) 355 356SELECT a::varchar(3) FROM tt1; 357 a 358----- 359 abc 360 012 361(2 rows) 362 363DROP VIEW tt1; 364-- Test view decompilation in the face of relation renaming conflicts 365CREATE TABLE tt1 (f1 int, f2 int, f3 text); 366CREATE TABLE tx1 (x1 int, x2 int, x3 text); 367CREATE TABLE temp_view_test.tt1 (y1 int, f2 int, f3 text); 368CREATE VIEW aliased_view_1 AS 369 select * from tt1 370 where exists (select 1 from tx1 where tt1.f1 = tx1.x1); 371CREATE VIEW aliased_view_2 AS 372 select * from tt1 a1 373 where exists (select 1 from tx1 where a1.f1 = tx1.x1); 374CREATE VIEW aliased_view_3 AS 375 select * from tt1 376 where exists (select 1 from tx1 a2 where tt1.f1 = a2.x1); 377CREATE VIEW aliased_view_4 AS 378 select * from temp_view_test.tt1 379 where exists (select 1 from tt1 where temp_view_test.tt1.y1 = tt1.f1); 380\d+ aliased_view_1 381 View "testviewschm2.aliased_view_1" 382 Column | Type | Collation | Nullable | Default | Storage | Description 383--------+---------+-----------+----------+---------+----------+------------- 384 f1 | integer | | | | plain | 385 f2 | integer | | | | plain | 386 f3 | text | | | | extended | 387View definition: 388 SELECT tt1.f1, 389 tt1.f2, 390 tt1.f3 391 FROM tt1 392 WHERE (EXISTS ( SELECT 1 393 FROM tx1 394 WHERE tt1.f1 = tx1.x1)); 395 396\d+ aliased_view_2 397 View "testviewschm2.aliased_view_2" 398 Column | Type | Collation | Nullable | Default | Storage | Description 399--------+---------+-----------+----------+---------+----------+------------- 400 f1 | integer | | | | plain | 401 f2 | integer | | | | plain | 402 f3 | text | | | | extended | 403View definition: 404 SELECT a1.f1, 405 a1.f2, 406 a1.f3 407 FROM tt1 a1 408 WHERE (EXISTS ( SELECT 1 409 FROM tx1 410 WHERE a1.f1 = tx1.x1)); 411 412\d+ aliased_view_3 413 View "testviewschm2.aliased_view_3" 414 Column | Type | Collation | Nullable | Default | Storage | Description 415--------+---------+-----------+----------+---------+----------+------------- 416 f1 | integer | | | | plain | 417 f2 | integer | | | | plain | 418 f3 | text | | | | extended | 419View definition: 420 SELECT tt1.f1, 421 tt1.f2, 422 tt1.f3 423 FROM tt1 424 WHERE (EXISTS ( SELECT 1 425 FROM tx1 a2 426 WHERE tt1.f1 = a2.x1)); 427 428\d+ aliased_view_4 429 View "testviewschm2.aliased_view_4" 430 Column | Type | Collation | Nullable | Default | Storage | Description 431--------+---------+-----------+----------+---------+----------+------------- 432 y1 | integer | | | | plain | 433 f2 | integer | | | | plain | 434 f3 | text | | | | extended | 435View definition: 436 SELECT tt1.y1, 437 tt1.f2, 438 tt1.f3 439 FROM temp_view_test.tt1 440 WHERE (EXISTS ( SELECT 1 441 FROM tt1 tt1_1 442 WHERE tt1.y1 = tt1_1.f1)); 443 444ALTER TABLE tx1 RENAME TO a1; 445\d+ aliased_view_1 446 View "testviewschm2.aliased_view_1" 447 Column | Type | Collation | Nullable | Default | Storage | Description 448--------+---------+-----------+----------+---------+----------+------------- 449 f1 | integer | | | | plain | 450 f2 | integer | | | | plain | 451 f3 | text | | | | extended | 452View definition: 453 SELECT tt1.f1, 454 tt1.f2, 455 tt1.f3 456 FROM tt1 457 WHERE (EXISTS ( SELECT 1 458 FROM a1 459 WHERE tt1.f1 = a1.x1)); 460 461\d+ aliased_view_2 462 View "testviewschm2.aliased_view_2" 463 Column | Type | Collation | Nullable | Default | Storage | Description 464--------+---------+-----------+----------+---------+----------+------------- 465 f1 | integer | | | | plain | 466 f2 | integer | | | | plain | 467 f3 | text | | | | extended | 468View definition: 469 SELECT a1.f1, 470 a1.f2, 471 a1.f3 472 FROM tt1 a1 473 WHERE (EXISTS ( SELECT 1 474 FROM a1 a1_1 475 WHERE a1.f1 = a1_1.x1)); 476 477\d+ aliased_view_3 478 View "testviewschm2.aliased_view_3" 479 Column | Type | Collation | Nullable | Default | Storage | Description 480--------+---------+-----------+----------+---------+----------+------------- 481 f1 | integer | | | | plain | 482 f2 | integer | | | | plain | 483 f3 | text | | | | extended | 484View definition: 485 SELECT tt1.f1, 486 tt1.f2, 487 tt1.f3 488 FROM tt1 489 WHERE (EXISTS ( SELECT 1 490 FROM a1 a2 491 WHERE tt1.f1 = a2.x1)); 492 493\d+ aliased_view_4 494 View "testviewschm2.aliased_view_4" 495 Column | Type | Collation | Nullable | Default | Storage | Description 496--------+---------+-----------+----------+---------+----------+------------- 497 y1 | integer | | | | plain | 498 f2 | integer | | | | plain | 499 f3 | text | | | | extended | 500View definition: 501 SELECT tt1.y1, 502 tt1.f2, 503 tt1.f3 504 FROM temp_view_test.tt1 505 WHERE (EXISTS ( SELECT 1 506 FROM tt1 tt1_1 507 WHERE tt1.y1 = tt1_1.f1)); 508 509ALTER TABLE tt1 RENAME TO a2; 510\d+ aliased_view_1 511 View "testviewschm2.aliased_view_1" 512 Column | Type | Collation | Nullable | Default | Storage | Description 513--------+---------+-----------+----------+---------+----------+------------- 514 f1 | integer | | | | plain | 515 f2 | integer | | | | plain | 516 f3 | text | | | | extended | 517View definition: 518 SELECT a2.f1, 519 a2.f2, 520 a2.f3 521 FROM a2 522 WHERE (EXISTS ( SELECT 1 523 FROM a1 524 WHERE a2.f1 = a1.x1)); 525 526\d+ aliased_view_2 527 View "testviewschm2.aliased_view_2" 528 Column | Type | Collation | Nullable | Default | Storage | Description 529--------+---------+-----------+----------+---------+----------+------------- 530 f1 | integer | | | | plain | 531 f2 | integer | | | | plain | 532 f3 | text | | | | extended | 533View definition: 534 SELECT a1.f1, 535 a1.f2, 536 a1.f3 537 FROM a2 a1 538 WHERE (EXISTS ( SELECT 1 539 FROM a1 a1_1 540 WHERE a1.f1 = a1_1.x1)); 541 542\d+ aliased_view_3 543 View "testviewschm2.aliased_view_3" 544 Column | Type | Collation | Nullable | Default | Storage | Description 545--------+---------+-----------+----------+---------+----------+------------- 546 f1 | integer | | | | plain | 547 f2 | integer | | | | plain | 548 f3 | text | | | | extended | 549View definition: 550 SELECT a2.f1, 551 a2.f2, 552 a2.f3 553 FROM a2 554 WHERE (EXISTS ( SELECT 1 555 FROM a1 a2_1 556 WHERE a2.f1 = a2_1.x1)); 557 558\d+ aliased_view_4 559 View "testviewschm2.aliased_view_4" 560 Column | Type | Collation | Nullable | Default | Storage | Description 561--------+---------+-----------+----------+---------+----------+------------- 562 y1 | integer | | | | plain | 563 f2 | integer | | | | plain | 564 f3 | text | | | | extended | 565View definition: 566 SELECT tt1.y1, 567 tt1.f2, 568 tt1.f3 569 FROM temp_view_test.tt1 570 WHERE (EXISTS ( SELECT 1 571 FROM a2 572 WHERE tt1.y1 = a2.f1)); 573 574ALTER TABLE a1 RENAME TO tt1; 575\d+ aliased_view_1 576 View "testviewschm2.aliased_view_1" 577 Column | Type | Collation | Nullable | Default | Storage | Description 578--------+---------+-----------+----------+---------+----------+------------- 579 f1 | integer | | | | plain | 580 f2 | integer | | | | plain | 581 f3 | text | | | | extended | 582View definition: 583 SELECT a2.f1, 584 a2.f2, 585 a2.f3 586 FROM a2 587 WHERE (EXISTS ( SELECT 1 588 FROM tt1 589 WHERE a2.f1 = tt1.x1)); 590 591\d+ aliased_view_2 592 View "testviewschm2.aliased_view_2" 593 Column | Type | Collation | Nullable | Default | Storage | Description 594--------+---------+-----------+----------+---------+----------+------------- 595 f1 | integer | | | | plain | 596 f2 | integer | | | | plain | 597 f3 | text | | | | extended | 598View definition: 599 SELECT a1.f1, 600 a1.f2, 601 a1.f3 602 FROM a2 a1 603 WHERE (EXISTS ( SELECT 1 604 FROM tt1 605 WHERE a1.f1 = tt1.x1)); 606 607\d+ aliased_view_3 608 View "testviewschm2.aliased_view_3" 609 Column | Type | Collation | Nullable | Default | Storage | Description 610--------+---------+-----------+----------+---------+----------+------------- 611 f1 | integer | | | | plain | 612 f2 | integer | | | | plain | 613 f3 | text | | | | extended | 614View definition: 615 SELECT a2.f1, 616 a2.f2, 617 a2.f3 618 FROM a2 619 WHERE (EXISTS ( SELECT 1 620 FROM tt1 a2_1 621 WHERE a2.f1 = a2_1.x1)); 622 623\d+ aliased_view_4 624 View "testviewschm2.aliased_view_4" 625 Column | Type | Collation | Nullable | Default | Storage | Description 626--------+---------+-----------+----------+---------+----------+------------- 627 y1 | integer | | | | plain | 628 f2 | integer | | | | plain | 629 f3 | text | | | | extended | 630View definition: 631 SELECT tt1.y1, 632 tt1.f2, 633 tt1.f3 634 FROM temp_view_test.tt1 635 WHERE (EXISTS ( SELECT 1 636 FROM a2 637 WHERE tt1.y1 = a2.f1)); 638 639ALTER TABLE a2 RENAME TO tx1; 640ALTER TABLE tx1 SET SCHEMA temp_view_test; 641\d+ aliased_view_1 642 View "testviewschm2.aliased_view_1" 643 Column | Type | Collation | Nullable | Default | Storage | Description 644--------+---------+-----------+----------+---------+----------+------------- 645 f1 | integer | | | | plain | 646 f2 | integer | | | | plain | 647 f3 | text | | | | extended | 648View definition: 649 SELECT tx1.f1, 650 tx1.f2, 651 tx1.f3 652 FROM temp_view_test.tx1 653 WHERE (EXISTS ( SELECT 1 654 FROM tt1 655 WHERE tx1.f1 = tt1.x1)); 656 657\d+ aliased_view_2 658 View "testviewschm2.aliased_view_2" 659 Column | Type | Collation | Nullable | Default | Storage | Description 660--------+---------+-----------+----------+---------+----------+------------- 661 f1 | integer | | | | plain | 662 f2 | integer | | | | plain | 663 f3 | text | | | | extended | 664View definition: 665 SELECT a1.f1, 666 a1.f2, 667 a1.f3 668 FROM temp_view_test.tx1 a1 669 WHERE (EXISTS ( SELECT 1 670 FROM tt1 671 WHERE a1.f1 = tt1.x1)); 672 673\d+ aliased_view_3 674 View "testviewschm2.aliased_view_3" 675 Column | Type | Collation | Nullable | Default | Storage | Description 676--------+---------+-----------+----------+---------+----------+------------- 677 f1 | integer | | | | plain | 678 f2 | integer | | | | plain | 679 f3 | text | | | | extended | 680View definition: 681 SELECT tx1.f1, 682 tx1.f2, 683 tx1.f3 684 FROM temp_view_test.tx1 685 WHERE (EXISTS ( SELECT 1 686 FROM tt1 a2 687 WHERE tx1.f1 = a2.x1)); 688 689\d+ aliased_view_4 690 View "testviewschm2.aliased_view_4" 691 Column | Type | Collation | Nullable | Default | Storage | Description 692--------+---------+-----------+----------+---------+----------+------------- 693 y1 | integer | | | | plain | 694 f2 | integer | | | | plain | 695 f3 | text | | | | extended | 696View definition: 697 SELECT tt1.y1, 698 tt1.f2, 699 tt1.f3 700 FROM temp_view_test.tt1 701 WHERE (EXISTS ( SELECT 1 702 FROM temp_view_test.tx1 703 WHERE tt1.y1 = tx1.f1)); 704 705ALTER TABLE temp_view_test.tt1 RENAME TO tmp1; 706ALTER TABLE temp_view_test.tmp1 SET SCHEMA testviewschm2; 707ALTER TABLE tmp1 RENAME TO tx1; 708\d+ aliased_view_1 709 View "testviewschm2.aliased_view_1" 710 Column | Type | Collation | Nullable | Default | Storage | Description 711--------+---------+-----------+----------+---------+----------+------------- 712 f1 | integer | | | | plain | 713 f2 | integer | | | | plain | 714 f3 | text | | | | extended | 715View definition: 716 SELECT tx1.f1, 717 tx1.f2, 718 tx1.f3 719 FROM temp_view_test.tx1 720 WHERE (EXISTS ( SELECT 1 721 FROM tt1 722 WHERE tx1.f1 = tt1.x1)); 723 724\d+ aliased_view_2 725 View "testviewschm2.aliased_view_2" 726 Column | Type | Collation | Nullable | Default | Storage | Description 727--------+---------+-----------+----------+---------+----------+------------- 728 f1 | integer | | | | plain | 729 f2 | integer | | | | plain | 730 f3 | text | | | | extended | 731View definition: 732 SELECT a1.f1, 733 a1.f2, 734 a1.f3 735 FROM temp_view_test.tx1 a1 736 WHERE (EXISTS ( SELECT 1 737 FROM tt1 738 WHERE a1.f1 = tt1.x1)); 739 740\d+ aliased_view_3 741 View "testviewschm2.aliased_view_3" 742 Column | Type | Collation | Nullable | Default | Storage | Description 743--------+---------+-----------+----------+---------+----------+------------- 744 f1 | integer | | | | plain | 745 f2 | integer | | | | plain | 746 f3 | text | | | | extended | 747View definition: 748 SELECT tx1.f1, 749 tx1.f2, 750 tx1.f3 751 FROM temp_view_test.tx1 752 WHERE (EXISTS ( SELECT 1 753 FROM tt1 a2 754 WHERE tx1.f1 = a2.x1)); 755 756\d+ aliased_view_4 757 View "testviewschm2.aliased_view_4" 758 Column | Type | Collation | Nullable | Default | Storage | Description 759--------+---------+-----------+----------+---------+----------+------------- 760 y1 | integer | | | | plain | 761 f2 | integer | | | | plain | 762 f3 | text | | | | extended | 763View definition: 764 SELECT tx1.y1, 765 tx1.f2, 766 tx1.f3 767 FROM tx1 768 WHERE (EXISTS ( SELECT 1 769 FROM temp_view_test.tx1 tx1_1 770 WHERE tx1.y1 = tx1_1.f1)); 771 772-- Test aliasing of joins 773create view view_of_joins as 774select * from 775 (select * from (tbl1 cross join tbl2) same) ss, 776 (tbl3 cross join tbl4) same; 777\d+ view_of_joins 778 View "testviewschm2.view_of_joins" 779 Column | Type | Collation | Nullable | Default | Storage | Description 780--------+---------+-----------+----------+---------+---------+------------- 781 a | integer | | | | plain | 782 b | integer | | | | plain | 783 c | integer | | | | plain | 784 d | integer | | | | plain | 785 e | integer | | | | plain | 786 f | integer | | | | plain | 787 g | integer | | | | plain | 788 h | integer | | | | plain | 789View definition: 790 SELECT ss.a, 791 ss.b, 792 ss.c, 793 ss.d, 794 same.e, 795 same.f, 796 same.g, 797 same.h 798 FROM ( SELECT same_1.a, 799 same_1.b, 800 same_1.c, 801 same_1.d 802 FROM (tbl1 803 CROSS JOIN tbl2) same_1) ss, 804 (tbl3 805 CROSS JOIN tbl4) same; 806 807-- Test view decompilation in the face of column addition/deletion/renaming 808create table tt2 (a int, b int, c int); 809create table tt3 (ax int8, b int2, c numeric); 810create table tt4 (ay int, b int, q int); 811create view v1 as select * from tt2 natural join tt3; 812create view v1a as select * from (tt2 natural join tt3) j; 813create view v2 as select * from tt2 join tt3 using (b,c) join tt4 using (b); 814create view v2a as select * from (tt2 join tt3 using (b,c) join tt4 using (b)) j; 815create view v3 as select * from tt2 join tt3 using (b,c) full join tt4 using (b); 816select pg_get_viewdef('v1', true); 817 pg_get_viewdef 818----------------------------- 819 SELECT tt2.b, + 820 tt3.c, + 821 tt2.a, + 822 tt3.ax + 823 FROM tt2 + 824 JOIN tt3 USING (b, c); 825(1 row) 826 827select pg_get_viewdef('v1a', true); 828 pg_get_viewdef 829-------------------------------- 830 SELECT j.b, + 831 j.c, + 832 j.a, + 833 j.ax + 834 FROM (tt2 + 835 JOIN tt3 USING (b, c)) j; 836(1 row) 837 838select pg_get_viewdef('v2', true); 839 pg_get_viewdef 840---------------------------- 841 SELECT tt2.b, + 842 tt3.c, + 843 tt2.a, + 844 tt3.ax, + 845 tt4.ay, + 846 tt4.q + 847 FROM tt2 + 848 JOIN tt3 USING (b, c)+ 849 JOIN tt4 USING (b); 850(1 row) 851 852select pg_get_viewdef('v2a', true); 853 pg_get_viewdef 854----------------------------- 855 SELECT j.b, + 856 j.c, + 857 j.a, + 858 j.ax, + 859 j.ay, + 860 j.q + 861 FROM (tt2 + 862 JOIN tt3 USING (b, c) + 863 JOIN tt4 USING (b)) j; 864(1 row) 865 866select pg_get_viewdef('v3', true); 867 pg_get_viewdef 868------------------------------- 869 SELECT b, + 870 tt3.c, + 871 tt2.a, + 872 tt3.ax, + 873 tt4.ay, + 874 tt4.q + 875 FROM tt2 + 876 JOIN tt3 USING (b, c) + 877 FULL JOIN tt4 USING (b); 878(1 row) 879 880alter table tt2 add column d int; 881alter table tt2 add column e int; 882select pg_get_viewdef('v1', true); 883 pg_get_viewdef 884----------------------------- 885 SELECT tt2.b, + 886 tt3.c, + 887 tt2.a, + 888 tt3.ax + 889 FROM tt2 + 890 JOIN tt3 USING (b, c); 891(1 row) 892 893select pg_get_viewdef('v1a', true); 894 pg_get_viewdef 895-------------------------------- 896 SELECT j.b, + 897 j.c, + 898 j.a, + 899 j.ax + 900 FROM (tt2 + 901 JOIN tt3 USING (b, c)) j; 902(1 row) 903 904select pg_get_viewdef('v2', true); 905 pg_get_viewdef 906---------------------------- 907 SELECT tt2.b, + 908 tt3.c, + 909 tt2.a, + 910 tt3.ax, + 911 tt4.ay, + 912 tt4.q + 913 FROM tt2 + 914 JOIN tt3 USING (b, c)+ 915 JOIN tt4 USING (b); 916(1 row) 917 918select pg_get_viewdef('v2a', true); 919 pg_get_viewdef 920----------------------------- 921 SELECT j.b, + 922 j.c, + 923 j.a, + 924 j.ax, + 925 j.ay, + 926 j.q + 927 FROM (tt2 + 928 JOIN tt3 USING (b, c) + 929 JOIN tt4 USING (b)) j; 930(1 row) 931 932select pg_get_viewdef('v3', true); 933 pg_get_viewdef 934------------------------------- 935 SELECT b, + 936 tt3.c, + 937 tt2.a, + 938 tt3.ax, + 939 tt4.ay, + 940 tt4.q + 941 FROM tt2 + 942 JOIN tt3 USING (b, c) + 943 FULL JOIN tt4 USING (b); 944(1 row) 945 946alter table tt3 rename c to d; 947select pg_get_viewdef('v1', true); 948 pg_get_viewdef 949------------------------------------------- 950 SELECT tt2.b, + 951 tt3.c, + 952 tt2.a, + 953 tt3.ax + 954 FROM tt2 + 955 JOIN tt3 tt3(ax, b, c) USING (b, c); 956(1 row) 957 958select pg_get_viewdef('v1a', true); 959 pg_get_viewdef 960---------------------------------------------- 961 SELECT j.b, + 962 j.c, + 963 j.a, + 964 j.ax + 965 FROM (tt2 + 966 JOIN tt3 tt3(ax, b, c) USING (b, c)) j; 967(1 row) 968 969select pg_get_viewdef('v2', true); 970 pg_get_viewdef 971------------------------------------------ 972 SELECT tt2.b, + 973 tt3.c, + 974 tt2.a, + 975 tt3.ax, + 976 tt4.ay, + 977 tt4.q + 978 FROM tt2 + 979 JOIN tt3 tt3(ax, b, c) USING (b, c)+ 980 JOIN tt4 USING (b); 981(1 row) 982 983select pg_get_viewdef('v2a', true); 984 pg_get_viewdef 985------------------------------------------ 986 SELECT j.b, + 987 j.c, + 988 j.a, + 989 j.ax, + 990 j.ay, + 991 j.q + 992 FROM (tt2 + 993 JOIN tt3 tt3(ax, b, c) USING (b, c)+ 994 JOIN tt4 USING (b)) j; 995(1 row) 996 997select pg_get_viewdef('v3', true); 998 pg_get_viewdef 999------------------------------------------ 1000 SELECT b, + 1001 tt3.c, + 1002 tt2.a, + 1003 tt3.ax, + 1004 tt4.ay, + 1005 tt4.q + 1006 FROM tt2 + 1007 JOIN tt3 tt3(ax, b, c) USING (b, c)+ 1008 FULL JOIN tt4 USING (b); 1009(1 row) 1010 1011alter table tt3 add column c int; 1012alter table tt3 add column e int; 1013select pg_get_viewdef('v1', true); 1014 pg_get_viewdef 1015--------------------------------------------------- 1016 SELECT tt2.b, + 1017 tt3.c, + 1018 tt2.a, + 1019 tt3.ax + 1020 FROM tt2 + 1021 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c); 1022(1 row) 1023 1024select pg_get_viewdef('v1a', true); 1025 pg_get_viewdef 1026----------------------------------------------------------------------------------- 1027 SELECT j.b, + 1028 j.c, + 1029 j.a, + 1030 j.ax + 1031 FROM (tt2 + 1032 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)) j(b, c, a, d, e, ax, c_1, e_1); 1033(1 row) 1034 1035select pg_get_viewdef('v2', true); 1036 pg_get_viewdef 1037-------------------------------------------------- 1038 SELECT tt2.b, + 1039 tt3.c, + 1040 tt2.a, + 1041 tt3.ax, + 1042 tt4.ay, + 1043 tt4.q + 1044 FROM tt2 + 1045 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+ 1046 JOIN tt4 USING (b); 1047(1 row) 1048 1049select pg_get_viewdef('v2a', true); 1050 pg_get_viewdef 1051----------------------------------------------------------------- 1052 SELECT j.b, + 1053 j.c, + 1054 j.a, + 1055 j.ax, + 1056 j.ay, + 1057 j.q + 1058 FROM (tt2 + 1059 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c) + 1060 JOIN tt4 USING (b)) j(b, c, a, d, e, ax, c_1, e_1, ay, q); 1061(1 row) 1062 1063select pg_get_viewdef('v3', true); 1064 pg_get_viewdef 1065-------------------------------------------------- 1066 SELECT b, + 1067 tt3.c, + 1068 tt2.a, + 1069 tt3.ax, + 1070 tt4.ay, + 1071 tt4.q + 1072 FROM tt2 + 1073 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+ 1074 FULL JOIN tt4 USING (b); 1075(1 row) 1076 1077alter table tt2 drop column d; 1078select pg_get_viewdef('v1', true); 1079 pg_get_viewdef 1080--------------------------------------------------- 1081 SELECT tt2.b, + 1082 tt3.c, + 1083 tt2.a, + 1084 tt3.ax + 1085 FROM tt2 + 1086 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c); 1087(1 row) 1088 1089select pg_get_viewdef('v1a', true); 1090 pg_get_viewdef 1091-------------------------------------------------------------------------------- 1092 SELECT j.b, + 1093 j.c, + 1094 j.a, + 1095 j.ax + 1096 FROM (tt2 + 1097 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)) j(b, c, a, e, ax, c_1, e_1); 1098(1 row) 1099 1100select pg_get_viewdef('v2', true); 1101 pg_get_viewdef 1102-------------------------------------------------- 1103 SELECT tt2.b, + 1104 tt3.c, + 1105 tt2.a, + 1106 tt3.ax, + 1107 tt4.ay, + 1108 tt4.q + 1109 FROM tt2 + 1110 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+ 1111 JOIN tt4 USING (b); 1112(1 row) 1113 1114select pg_get_viewdef('v2a', true); 1115 pg_get_viewdef 1116-------------------------------------------------------------- 1117 SELECT j.b, + 1118 j.c, + 1119 j.a, + 1120 j.ax, + 1121 j.ay, + 1122 j.q + 1123 FROM (tt2 + 1124 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c) + 1125 JOIN tt4 USING (b)) j(b, c, a, e, ax, c_1, e_1, ay, q); 1126(1 row) 1127 1128select pg_get_viewdef('v3', true); 1129 pg_get_viewdef 1130-------------------------------------------------- 1131 SELECT b, + 1132 tt3.c, + 1133 tt2.a, + 1134 tt3.ax, + 1135 tt4.ay, + 1136 tt4.q + 1137 FROM tt2 + 1138 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+ 1139 FULL JOIN tt4 USING (b); 1140(1 row) 1141 1142create table tt5 (a int, b int); 1143create table tt6 (c int, d int); 1144create view vv1 as select * from (tt5 cross join tt6) j(aa,bb,cc,dd); 1145select pg_get_viewdef('vv1', true); 1146 pg_get_viewdef 1147----------------------------------------- 1148 SELECT j.aa, + 1149 j.bb, + 1150 j.cc, + 1151 j.dd + 1152 FROM (tt5 + 1153 CROSS JOIN tt6) j(aa, bb, cc, dd); 1154(1 row) 1155 1156alter table tt5 add column c int; 1157select pg_get_viewdef('vv1', true); 1158 pg_get_viewdef 1159-------------------------------------------- 1160 SELECT j.aa, + 1161 j.bb, + 1162 j.cc, + 1163 j.dd + 1164 FROM (tt5 + 1165 CROSS JOIN tt6) j(aa, bb, c, cc, dd); 1166(1 row) 1167 1168alter table tt5 add column cc int; 1169select pg_get_viewdef('vv1', true); 1170 pg_get_viewdef 1171-------------------------------------------------- 1172 SELECT j.aa, + 1173 j.bb, + 1174 j.cc, + 1175 j.dd + 1176 FROM (tt5 + 1177 CROSS JOIN tt6) j(aa, bb, c, cc_1, cc, dd); 1178(1 row) 1179 1180alter table tt5 drop column c; 1181select pg_get_viewdef('vv1', true); 1182 pg_get_viewdef 1183----------------------------------------------- 1184 SELECT j.aa, + 1185 j.bb, + 1186 j.cc, + 1187 j.dd + 1188 FROM (tt5 + 1189 CROSS JOIN tt6) j(aa, bb, cc_1, cc, dd); 1190(1 row) 1191 1192-- Unnamed FULL JOIN USING is lots of fun too 1193create table tt7 (x int, xx int, y int); 1194alter table tt7 drop column xx; 1195create table tt8 (x int, z int); 1196create view vv2 as 1197select * from (values(1,2,3,4,5)) v(a,b,c,d,e) 1198union all 1199select * from tt7 full join tt8 using (x), tt8 tt8x; 1200select pg_get_viewdef('vv2', true); 1201 pg_get_viewdef 1202------------------------------------------------ 1203 SELECT v.a, + 1204 v.b, + 1205 v.c, + 1206 v.d, + 1207 v.e + 1208 FROM ( VALUES (1,2,3,4,5)) v(a, b, c, d, e)+ 1209 UNION ALL + 1210 SELECT x AS a, + 1211 tt7.y AS b, + 1212 tt8.z AS c, + 1213 tt8x.x_1 AS d, + 1214 tt8x.z AS e + 1215 FROM tt7 + 1216 FULL JOIN tt8 USING (x), + 1217 tt8 tt8x(x_1, z); 1218(1 row) 1219 1220create view vv3 as 1221select * from (values(1,2,3,4,5,6)) v(a,b,c,x,e,f) 1222union all 1223select * from 1224 tt7 full join tt8 using (x), 1225 tt7 tt7x full join tt8 tt8x using (x); 1226select pg_get_viewdef('vv3', true); 1227 pg_get_viewdef 1228----------------------------------------------------- 1229 SELECT v.a, + 1230 v.b, + 1231 v.c, + 1232 v.x, + 1233 v.e, + 1234 v.f + 1235 FROM ( VALUES (1,2,3,4,5,6)) v(a, b, c, x, e, f)+ 1236 UNION ALL + 1237 SELECT x AS a, + 1238 tt7.y AS b, + 1239 tt8.z AS c, + 1240 x_1 AS x, + 1241 tt7x.y AS e, + 1242 tt8x.z AS f + 1243 FROM tt7 + 1244 FULL JOIN tt8 USING (x), + 1245 tt7 tt7x(x_1, y) + 1246 FULL JOIN tt8 tt8x(x_1, z) USING (x_1); 1247(1 row) 1248 1249create view vv4 as 1250select * from (values(1,2,3,4,5,6,7)) v(a,b,c,x,e,f,g) 1251union all 1252select * from 1253 tt7 full join tt8 using (x), 1254 tt7 tt7x full join tt8 tt8x using (x) full join tt8 tt8y using (x); 1255select pg_get_viewdef('vv4', true); 1256 pg_get_viewdef 1257---------------------------------------------------------- 1258 SELECT v.a, + 1259 v.b, + 1260 v.c, + 1261 v.x, + 1262 v.e, + 1263 v.f, + 1264 v.g + 1265 FROM ( VALUES (1,2,3,4,5,6,7)) v(a, b, c, x, e, f, g)+ 1266 UNION ALL + 1267 SELECT x AS a, + 1268 tt7.y AS b, + 1269 tt8.z AS c, + 1270 x_1 AS x, + 1271 tt7x.y AS e, + 1272 tt8x.z AS f, + 1273 tt8y.z AS g + 1274 FROM tt7 + 1275 FULL JOIN tt8 USING (x), + 1276 tt7 tt7x(x_1, y) + 1277 FULL JOIN tt8 tt8x(x_1, z) USING (x_1) + 1278 FULL JOIN tt8 tt8y(x_1, z) USING (x_1); 1279(1 row) 1280 1281alter table tt7 add column zz int; 1282alter table tt7 add column z int; 1283alter table tt7 drop column zz; 1284alter table tt8 add column z2 int; 1285select pg_get_viewdef('vv2', true); 1286 pg_get_viewdef 1287------------------------------------------------ 1288 SELECT v.a, + 1289 v.b, + 1290 v.c, + 1291 v.d, + 1292 v.e + 1293 FROM ( VALUES (1,2,3,4,5)) v(a, b, c, d, e)+ 1294 UNION ALL + 1295 SELECT x AS a, + 1296 tt7.y AS b, + 1297 tt8.z AS c, + 1298 tt8x.x_1 AS d, + 1299 tt8x.z AS e + 1300 FROM tt7 + 1301 FULL JOIN tt8 USING (x), + 1302 tt8 tt8x(x_1, z, z2); 1303(1 row) 1304 1305select pg_get_viewdef('vv3', true); 1306 pg_get_viewdef 1307----------------------------------------------------- 1308 SELECT v.a, + 1309 v.b, + 1310 v.c, + 1311 v.x, + 1312 v.e, + 1313 v.f + 1314 FROM ( VALUES (1,2,3,4,5,6)) v(a, b, c, x, e, f)+ 1315 UNION ALL + 1316 SELECT x AS a, + 1317 tt7.y AS b, + 1318 tt8.z AS c, + 1319 x_1 AS x, + 1320 tt7x.y AS e, + 1321 tt8x.z AS f + 1322 FROM tt7 + 1323 FULL JOIN tt8 USING (x), + 1324 tt7 tt7x(x_1, y, z) + 1325 FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1); 1326(1 row) 1327 1328select pg_get_viewdef('vv4', true); 1329 pg_get_viewdef 1330---------------------------------------------------------- 1331 SELECT v.a, + 1332 v.b, + 1333 v.c, + 1334 v.x, + 1335 v.e, + 1336 v.f, + 1337 v.g + 1338 FROM ( VALUES (1,2,3,4,5,6,7)) v(a, b, c, x, e, f, g)+ 1339 UNION ALL + 1340 SELECT x AS a, + 1341 tt7.y AS b, + 1342 tt8.z AS c, + 1343 x_1 AS x, + 1344 tt7x.y AS e, + 1345 tt8x.z AS f, + 1346 tt8y.z AS g + 1347 FROM tt7 + 1348 FULL JOIN tt8 USING (x), + 1349 tt7 tt7x(x_1, y, z) + 1350 FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1) + 1351 FULL JOIN tt8 tt8y(x_1, z, z2) USING (x_1); 1352(1 row) 1353 1354-- Implicit coercions in a JOIN USING create issues similar to FULL JOIN 1355create table tt7a (x date, xx int, y int); 1356alter table tt7a drop column xx; 1357create table tt8a (x timestamptz, z int); 1358create view vv2a as 1359select * from (values(now(),2,3,now(),5)) v(a,b,c,d,e) 1360union all 1361select * from tt7a left join tt8a using (x), tt8a tt8ax; 1362select pg_get_viewdef('vv2a', true); 1363 pg_get_viewdef 1364-------------------------------------------------------- 1365 SELECT v.a, + 1366 v.b, + 1367 v.c, + 1368 v.d, + 1369 v.e + 1370 FROM ( VALUES (now(),2,3,now(),5)) v(a, b, c, d, e)+ 1371 UNION ALL + 1372 SELECT x AS a, + 1373 tt7a.y AS b, + 1374 tt8a.z AS c, + 1375 tt8ax.x_1 AS d, + 1376 tt8ax.z AS e + 1377 FROM tt7a + 1378 LEFT JOIN tt8a USING (x), + 1379 tt8a tt8ax(x_1, z); 1380(1 row) 1381 1382-- 1383-- Also check dropping a column that existed when the view was made 1384-- 1385create table tt9 (x int, xx int, y int); 1386create table tt10 (x int, z int); 1387create view vv5 as select x,y,z from tt9 join tt10 using(x); 1388select pg_get_viewdef('vv5', true); 1389 pg_get_viewdef 1390--------------------------- 1391 SELECT tt9.x, + 1392 tt9.y, + 1393 tt10.z + 1394 FROM tt9 + 1395 JOIN tt10 USING (x); 1396(1 row) 1397 1398alter table tt9 drop column xx; 1399select pg_get_viewdef('vv5', true); 1400 pg_get_viewdef 1401--------------------------- 1402 SELECT tt9.x, + 1403 tt9.y, + 1404 tt10.z + 1405 FROM tt9 + 1406 JOIN tt10 USING (x); 1407(1 row) 1408 1409-- 1410-- Another corner case is that we might add a column to a table below a 1411-- JOIN USING, and thereby make the USING column name ambiguous 1412-- 1413create table tt11 (x int, y int); 1414create table tt12 (x int, z int); 1415create table tt13 (z int, q int); 1416create view vv6 as select x,y,z,q from 1417 (tt11 join tt12 using(x)) join tt13 using(z); 1418select pg_get_viewdef('vv6', true); 1419 pg_get_viewdef 1420--------------------------- 1421 SELECT tt11.x, + 1422 tt11.y, + 1423 tt12.z, + 1424 tt13.q + 1425 FROM tt11 + 1426 JOIN tt12 USING (x) + 1427 JOIN tt13 USING (z); 1428(1 row) 1429 1430alter table tt11 add column z int; 1431select pg_get_viewdef('vv6', true); 1432 pg_get_viewdef 1433------------------------------ 1434 SELECT tt11.x, + 1435 tt11.y, + 1436 tt12.z, + 1437 tt13.q + 1438 FROM tt11 tt11(x, y, z_1)+ 1439 JOIN tt12 USING (x) + 1440 JOIN tt13 USING (z); 1441(1 row) 1442 1443-- 1444-- Check cases involving dropped/altered columns in a function's rowtype result 1445-- 1446create table tt14t (f1 text, f2 text, f3 text, f4 text); 1447insert into tt14t values('foo', 'bar', 'baz', '42'); 1448alter table tt14t drop column f2; 1449create function tt14f() returns setof tt14t as 1450$$ 1451declare 1452 rec1 record; 1453begin 1454 for rec1 in select * from tt14t 1455 loop 1456 return next rec1; 1457 end loop; 1458end; 1459$$ 1460language plpgsql; 1461create view tt14v as select t.* from tt14f() t; 1462select pg_get_viewdef('tt14v', true); 1463 pg_get_viewdef 1464-------------------------------- 1465 SELECT t.f1, + 1466 t.f3, + 1467 t.f4 + 1468 FROM tt14f() t(f1, f3, f4); 1469(1 row) 1470 1471select * from tt14v; 1472 f1 | f3 | f4 1473-----+-----+---- 1474 foo | baz | 42 1475(1 row) 1476 1477begin; 1478-- this perhaps should be rejected, but it isn't: 1479alter table tt14t drop column f3; 1480-- f3 is still in the view ... 1481select pg_get_viewdef('tt14v', true); 1482 pg_get_viewdef 1483-------------------------------- 1484 SELECT t.f1, + 1485 t.f3, + 1486 t.f4 + 1487 FROM tt14f() t(f1, f3, f4); 1488(1 row) 1489 1490-- but will fail at execution 1491select f1, f4 from tt14v; 1492 f1 | f4 1493-----+---- 1494 foo | 42 1495(1 row) 1496 1497select * from tt14v; 1498ERROR: attribute 3 of type record has been dropped 1499rollback; 1500begin; 1501-- this perhaps should be rejected, but it isn't: 1502alter table tt14t alter column f4 type integer using f4::integer; 1503-- f4 is still in the view ... 1504select pg_get_viewdef('tt14v', true); 1505 pg_get_viewdef 1506-------------------------------- 1507 SELECT t.f1, + 1508 t.f3, + 1509 t.f4 + 1510 FROM tt14f() t(f1, f3, f4); 1511(1 row) 1512 1513-- but will fail at execution 1514select f1, f3 from tt14v; 1515 f1 | f3 1516-----+----- 1517 foo | baz 1518(1 row) 1519 1520select * from tt14v; 1521ERROR: attribute 4 of type record has wrong type 1522DETAIL: Table has type integer, but query expects text. 1523rollback; 1524-- check display of whole-row variables in some corner cases 1525create type nestedcomposite as (x int8_tbl); 1526create view tt15v as select row(i)::nestedcomposite from int8_tbl i; 1527select * from tt15v; 1528 row 1529------------------------------------------ 1530 ("(123,456)") 1531 ("(123,4567890123456789)") 1532 ("(4567890123456789,123)") 1533 ("(4567890123456789,4567890123456789)") 1534 ("(4567890123456789,-4567890123456789)") 1535(5 rows) 1536 1537select pg_get_viewdef('tt15v', true); 1538 pg_get_viewdef 1539------------------------------------------------------ 1540 SELECT ROW(i.*::int8_tbl)::nestedcomposite AS "row"+ 1541 FROM int8_tbl i; 1542(1 row) 1543 1544select row(i.*::int8_tbl)::nestedcomposite from int8_tbl i; 1545 row 1546------------------------------------------ 1547 ("(123,456)") 1548 ("(123,4567890123456789)") 1549 ("(4567890123456789,123)") 1550 ("(4567890123456789,4567890123456789)") 1551 ("(4567890123456789,-4567890123456789)") 1552(5 rows) 1553 1554create view tt16v as select * from int8_tbl i, lateral(values(i)) ss; 1555select * from tt16v; 1556 q1 | q2 | column1 1557------------------+-------------------+-------------------------------------- 1558 123 | 456 | (123,456) 1559 123 | 4567890123456789 | (123,4567890123456789) 1560 4567890123456789 | 123 | (4567890123456789,123) 1561 4567890123456789 | 4567890123456789 | (4567890123456789,4567890123456789) 1562 4567890123456789 | -4567890123456789 | (4567890123456789,-4567890123456789) 1563(5 rows) 1564 1565select pg_get_viewdef('tt16v', true); 1566 pg_get_viewdef 1567------------------------------------------- 1568 SELECT i.q1, + 1569 i.q2, + 1570 ss.column1 + 1571 FROM int8_tbl i, + 1572 LATERAL ( VALUES (i.*::int8_tbl)) ss; 1573(1 row) 1574 1575select * from int8_tbl i, lateral(values(i.*::int8_tbl)) ss; 1576 q1 | q2 | column1 1577------------------+-------------------+-------------------------------------- 1578 123 | 456 | (123,456) 1579 123 | 4567890123456789 | (123,4567890123456789) 1580 4567890123456789 | 123 | (4567890123456789,123) 1581 4567890123456789 | 4567890123456789 | (4567890123456789,4567890123456789) 1582 4567890123456789 | -4567890123456789 | (4567890123456789,-4567890123456789) 1583(5 rows) 1584 1585create view tt17v as select * from int8_tbl i where i in (values(i)); 1586select * from tt17v; 1587 q1 | q2 1588------------------+------------------- 1589 123 | 456 1590 123 | 4567890123456789 1591 4567890123456789 | 123 1592 4567890123456789 | 4567890123456789 1593 4567890123456789 | -4567890123456789 1594(5 rows) 1595 1596select pg_get_viewdef('tt17v', true); 1597 pg_get_viewdef 1598--------------------------------------------- 1599 SELECT i.q1, + 1600 i.q2 + 1601 FROM int8_tbl i + 1602 WHERE (i.* IN ( VALUES (i.*::int8_tbl))); 1603(1 row) 1604 1605select * from int8_tbl i where i.* in (values(i.*::int8_tbl)); 1606 q1 | q2 1607------------------+------------------- 1608 123 | 456 1609 123 | 4567890123456789 1610 4567890123456789 | 123 1611 4567890123456789 | 4567890123456789 1612 4567890123456789 | -4567890123456789 1613(5 rows) 1614 1615-- check unique-ification of overlength names 1616create view tt18v as 1617 select * from int8_tbl xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxy 1618 union all 1619 select * from int8_tbl xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxz; 1620NOTICE: identifier "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxy" will be truncated to "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" 1621NOTICE: identifier "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxz" will be truncated to "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" 1622select pg_get_viewdef('tt18v', true); 1623 pg_get_viewdef 1624----------------------------------------------------------------------------------- 1625 SELECT xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.q1, + 1626 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.q2 + 1627 FROM int8_tbl xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx + 1628 UNION ALL + 1629 SELECT xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.q1, + 1630 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.q2 + 1631 FROM int8_tbl xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx; 1632(1 row) 1633 1634explain (costs off) select * from tt18v; 1635 QUERY PLAN 1636-------------------------------------------------------------------------------------------- 1637 Append 1638 -> Seq Scan on int8_tbl xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1639 -> Seq Scan on int8_tbl xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx_1 1640(3 rows) 1641 1642-- check display of ScalarArrayOp with a sub-select 1643select 'foo'::text = any(array['abc','def','foo']::text[]); 1644 ?column? 1645---------- 1646 t 1647(1 row) 1648 1649select 'foo'::text = any((select array['abc','def','foo']::text[])); -- fail 1650ERROR: operator does not exist: text = text[] 1651LINE 1: select 'foo'::text = any((select array['abc','def','foo']::t... 1652 ^ 1653HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. 1654select 'foo'::text = any((select array['abc','def','foo']::text[])::text[]); 1655 ?column? 1656---------- 1657 t 1658(1 row) 1659 1660create view tt19v as 1661select 'foo'::text = any(array['abc','def','foo']::text[]) c1, 1662 'foo'::text = any((select array['abc','def','foo']::text[])::text[]) c2; 1663select pg_get_viewdef('tt19v', true); 1664 pg_get_viewdef 1665------------------------------------------------------------------------------------------------------------ 1666 SELECT 'foo'::text = ANY (ARRAY['abc'::text, 'def'::text, 'foo'::text]) AS c1, + 1667 'foo'::text = ANY ((( SELECT ARRAY['abc'::text, 'def'::text, 'foo'::text] AS "array"))::text[]) AS c2; 1668(1 row) 1669 1670-- check display of assorted RTE_FUNCTION expressions 1671create view tt20v as 1672select * from 1673 coalesce(1,2) as c, 1674 collation for ('x'::text) col, 1675 current_date as d, 1676 localtimestamp(3) as t, 1677 cast(1+2 as int4) as i4, 1678 cast(1+2 as int8) as i8; 1679select pg_get_viewdef('tt20v', true); 1680 pg_get_viewdef 1681--------------------------------------------- 1682 SELECT c.c, + 1683 col.col, + 1684 d.d, + 1685 t.t, + 1686 i4.i4, + 1687 i8.i8 + 1688 FROM COALESCE(1, 2) c(c), + 1689 pg_collation_for('x'::text) col(col), + 1690 CURRENT_DATE d(d), + 1691 LOCALTIMESTAMP(3) t(t), + 1692 CAST(1 + 2 AS integer) i4(i4), + 1693 CAST((1 + 2)::bigint AS bigint) i8(i8); 1694(1 row) 1695 1696-- corner cases with empty join conditions 1697create view tt21v as 1698select * from tt5 natural inner join tt6; 1699select pg_get_viewdef('tt21v', true); 1700 pg_get_viewdef 1701---------------------- 1702 SELECT tt5.a, + 1703 tt5.b, + 1704 tt5.cc, + 1705 tt6.c, + 1706 tt6.d + 1707 FROM tt5 + 1708 CROSS JOIN tt6; 1709(1 row) 1710 1711create view tt22v as 1712select * from tt5 natural left join tt6; 1713select pg_get_viewdef('tt22v', true); 1714 pg_get_viewdef 1715----------------------------- 1716 SELECT tt5.a, + 1717 tt5.b, + 1718 tt5.cc, + 1719 tt6.c, + 1720 tt6.d + 1721 FROM tt5 + 1722 LEFT JOIN tt6 ON TRUE; 1723(1 row) 1724 1725-- check handling of views with immediately-renamed columns 1726create view tt23v (col_a, col_b) as 1727select q1 as other_name1, q2 as other_name2 from int8_tbl 1728union 1729select 42, 43; 1730select pg_get_viewdef('tt23v', true); 1731 pg_get_viewdef 1732------------------------------- 1733 SELECT int8_tbl.q1 AS col_a,+ 1734 int8_tbl.q2 AS col_b + 1735 FROM int8_tbl + 1736 UNION + 1737 SELECT 42 AS col_a, + 1738 43 AS col_b; 1739(1 row) 1740 1741select pg_get_ruledef(oid, true) from pg_rewrite 1742 where ev_class = 'tt23v'::regclass and ev_type = '1'; 1743 pg_get_ruledef 1744----------------------------------------------------------------- 1745 CREATE RULE "_RETURN" AS + 1746 ON SELECT TO tt23v DO INSTEAD SELECT int8_tbl.q1 AS col_a,+ 1747 int8_tbl.q2 AS col_b + 1748 FROM int8_tbl + 1749 UNION + 1750 SELECT 42 AS col_a, + 1751 43 AS col_b; 1752(1 row) 1753 1754-- clean up all the random objects we made above 1755\set VERBOSITY terse \\ -- suppress cascade details 1756DROP SCHEMA temp_view_test CASCADE; 1757NOTICE: drop cascades to 27 other objects 1758DROP SCHEMA testviewschm2 CASCADE; 1759NOTICE: drop cascades to 63 other objects 1760