1-- 2-- CREATE_VIEW 3-- Virtual class definitions 4-- (this also tests the query rewrite system) 5-- 6 7CREATE VIEW street AS 8 SELECT r.name, r.thepath, c.cname AS cname 9 FROM ONLY road r, real_city c 10 WHERE c.outline ## r.thepath; 11 12CREATE VIEW iexit AS 13 SELECT ih.name, ih.thepath, 14 interpt_pp(ih.thepath, r.thepath) AS exit 15 FROM ihighway ih, ramp r 16 WHERE ih.thepath ## r.thepath; 17 18CREATE VIEW toyemp AS 19 SELECT name, age, location, 12*salary AS annualsal 20 FROM emp; 21 22-- Test comments 23COMMENT ON VIEW noview IS 'no view'; 24COMMENT ON VIEW toyemp IS 'is a view'; 25COMMENT ON VIEW toyemp IS NULL; 26 27-- These views are left around mainly to exercise special cases in pg_dump. 28 29CREATE TABLE view_base_table (key int PRIMARY KEY, data varchar(20)); 30 31CREATE VIEW key_dependent_view AS 32 SELECT * FROM view_base_table GROUP BY key; 33 34ALTER TABLE view_base_table DROP CONSTRAINT view_base_table_pkey; -- fails 35 36CREATE VIEW key_dependent_view_no_cols AS 37 SELECT FROM view_base_table GROUP BY key HAVING length(data) > 0; 38 39-- 40-- CREATE OR REPLACE VIEW 41-- 42 43CREATE TABLE viewtest_tbl (a int, b int); 44COPY viewtest_tbl FROM stdin; 455 10 4610 15 4715 20 4820 25 49\. 50 51CREATE OR REPLACE VIEW viewtest AS 52 SELECT * FROM viewtest_tbl; 53 54CREATE OR REPLACE VIEW viewtest AS 55 SELECT * FROM viewtest_tbl WHERE a > 10; 56 57SELECT * FROM viewtest; 58 59CREATE OR REPLACE VIEW viewtest AS 60 SELECT a, b FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC; 61 62SELECT * FROM viewtest; 63 64-- should fail 65CREATE OR REPLACE VIEW viewtest AS 66 SELECT a FROM viewtest_tbl WHERE a <> 20; 67 68-- should fail 69CREATE OR REPLACE VIEW viewtest AS 70 SELECT 1, * FROM viewtest_tbl; 71 72-- should fail 73CREATE OR REPLACE VIEW viewtest AS 74 SELECT a, b::numeric FROM viewtest_tbl; 75 76-- should work 77CREATE OR REPLACE VIEW viewtest AS 78 SELECT a, b, 0 AS c FROM viewtest_tbl; 79 80DROP VIEW viewtest; 81DROP TABLE viewtest_tbl; 82 83-- tests for temporary views 84 85CREATE SCHEMA temp_view_test 86 CREATE TABLE base_table (a int, id int) 87 CREATE TABLE base_table2 (a int, id int); 88 89SET search_path TO temp_view_test, public; 90 91CREATE TEMPORARY TABLE temp_table (a int, id int); 92 93-- should be created in temp_view_test schema 94CREATE VIEW v1 AS SELECT * FROM base_table; 95-- should be created in temp object schema 96CREATE VIEW v1_temp AS SELECT * FROM temp_table; 97-- should be created in temp object schema 98CREATE TEMP VIEW v2_temp AS SELECT * FROM base_table; 99-- should be created in temp_views schema 100CREATE VIEW temp_view_test.v2 AS SELECT * FROM base_table; 101-- should fail 102CREATE VIEW temp_view_test.v3_temp AS SELECT * FROM temp_table; 103-- should fail 104CREATE SCHEMA test_view_schema 105 CREATE TEMP VIEW testview AS SELECT 1; 106 107-- joins: if any of the join relations are temporary, the view 108-- should also be temporary 109 110-- should be non-temp 111CREATE VIEW v3 AS 112 SELECT t1.a AS t1_a, t2.a AS t2_a 113 FROM base_table t1, base_table2 t2 114 WHERE t1.id = t2.id; 115-- should be temp (one join rel is temp) 116CREATE VIEW v4_temp AS 117 SELECT t1.a AS t1_a, t2.a AS t2_a 118 FROM base_table t1, temp_table t2 119 WHERE t1.id = t2.id; 120-- should be temp 121CREATE VIEW v5_temp AS 122 SELECT t1.a AS t1_a, t2.a AS t2_a, t3.a AS t3_a 123 FROM base_table t1, base_table2 t2, temp_table t3 124 WHERE t1.id = t2.id and t2.id = t3.id; 125 126-- subqueries 127CREATE VIEW v4 AS SELECT * FROM base_table WHERE id IN (SELECT id FROM base_table2); 128CREATE VIEW v5 AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM base_table2) t2; 129CREATE VIEW v6 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM base_table2); 130CREATE VIEW v7 AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM base_table2); 131CREATE VIEW v8 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1); 132 133CREATE VIEW v6_temp AS SELECT * FROM base_table WHERE id IN (SELECT id FROM temp_table); 134CREATE VIEW v7_temp AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM temp_table) t2; 135CREATE VIEW v8_temp AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM temp_table); 136CREATE VIEW v9_temp AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM temp_table); 137 138-- a view should also be temporary if it references a temporary view 139CREATE VIEW v10_temp AS SELECT * FROM v7_temp; 140CREATE VIEW v11_temp AS SELECT t1.id, t2.a FROM base_table t1, v10_temp t2; 141CREATE VIEW v12_temp AS SELECT true FROM v11_temp; 142 143-- a view should also be temporary if it references a temporary sequence 144CREATE SEQUENCE seq1; 145CREATE TEMPORARY SEQUENCE seq1_temp; 146CREATE VIEW v9 AS SELECT seq1.is_called FROM seq1; 147CREATE VIEW v13_temp AS SELECT seq1_temp.is_called FROM seq1_temp; 148 149SELECT relname FROM pg_class 150 WHERE relname LIKE 'v_' 151 AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'temp_view_test') 152 ORDER BY relname; 153SELECT relname FROM pg_class 154 WHERE relname LIKE 'v%' 155 AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%') 156 ORDER BY relname; 157 158CREATE SCHEMA testviewschm2; 159SET search_path TO testviewschm2, public; 160 161CREATE TABLE t1 (num int, name text); 162CREATE TABLE t2 (num2 int, value text); 163CREATE TEMP TABLE tt (num2 int, value text); 164 165CREATE VIEW nontemp1 AS SELECT * FROM t1 CROSS JOIN t2; 166CREATE VIEW temporal1 AS SELECT * FROM t1 CROSS JOIN tt; 167CREATE VIEW nontemp2 AS SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num2; 168CREATE VIEW temporal2 AS SELECT * FROM t1 INNER JOIN tt ON t1.num = tt.num2; 169CREATE VIEW nontemp3 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2; 170CREATE VIEW temporal3 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2; 171CREATE VIEW nontemp4 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2 AND t2.value = 'xxx'; 172CREATE VIEW temporal4 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2 AND tt.value = 'xxx'; 173 174SELECT relname FROM pg_class 175 WHERE relname LIKE 'nontemp%' 176 AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'testviewschm2') 177 ORDER BY relname; 178SELECT relname FROM pg_class 179 WHERE relname LIKE 'temporal%' 180 AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%') 181 ORDER BY relname; 182 183CREATE TABLE tbl1 ( a int, b int); 184CREATE TABLE tbl2 (c int, d int); 185CREATE TABLE tbl3 (e int, f int); 186CREATE TABLE tbl4 (g int, h int); 187CREATE TEMP TABLE tmptbl (i int, j int); 188 189--Should be in testviewschm2 190CREATE VIEW pubview AS SELECT * FROM tbl1 WHERE tbl1.a 191BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2) 192AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f); 193 194SELECT count(*) FROM pg_class where relname = 'pubview' 195AND relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname = 'testviewschm2'); 196 197--Should be in temp object schema 198CREATE VIEW mytempview AS SELECT * FROM tbl1 WHERE tbl1.a 199BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2) 200AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f) 201AND NOT EXISTS (SELECT g FROM tbl4 LEFT JOIN tmptbl ON tbl4.h = tmptbl.j); 202 203SELECT count(*) FROM pg_class where relname LIKE 'mytempview' 204And relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname LIKE 'pg_temp%'); 205 206-- 207-- CREATE VIEW and WITH(...) clause 208-- 209CREATE VIEW mysecview1 210 AS SELECT * FROM tbl1 WHERE a = 0; 211CREATE VIEW mysecview2 WITH (security_barrier=true) 212 AS SELECT * FROM tbl1 WHERE a > 0; 213CREATE VIEW mysecview3 WITH (security_barrier=false) 214 AS SELECT * FROM tbl1 WHERE a < 0; 215CREATE VIEW mysecview4 WITH (security_barrier) 216 AS SELECT * FROM tbl1 WHERE a <> 0; 217CREATE VIEW mysecview5 WITH (security_barrier=100) -- Error 218 AS SELECT * FROM tbl1 WHERE a > 100; 219CREATE VIEW mysecview6 WITH (invalid_option) -- Error 220 AS SELECT * FROM tbl1 WHERE a < 100; 221SELECT relname, relkind, reloptions FROM pg_class 222 WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass, 223 'mysecview3'::regclass, 'mysecview4'::regclass) 224 ORDER BY relname; 225 226CREATE OR REPLACE VIEW mysecview1 227 AS SELECT * FROM tbl1 WHERE a = 256; 228CREATE OR REPLACE VIEW mysecview2 229 AS SELECT * FROM tbl1 WHERE a > 256; 230CREATE OR REPLACE VIEW mysecview3 WITH (security_barrier=true) 231 AS SELECT * FROM tbl1 WHERE a < 256; 232CREATE OR REPLACE VIEW mysecview4 WITH (security_barrier=false) 233 AS SELECT * FROM tbl1 WHERE a <> 256; 234SELECT relname, relkind, reloptions FROM pg_class 235 WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass, 236 'mysecview3'::regclass, 'mysecview4'::regclass) 237 ORDER BY relname; 238 239-- Check that unknown literals are converted to "text" in CREATE VIEW, 240-- so that we don't end up with unknown-type columns. 241 242CREATE VIEW unspecified_types AS 243 SELECT 42 as i, 42.5 as num, 'foo' as u, 'foo'::unknown as u2, null as n; 244\d+ unspecified_types 245SELECT * FROM unspecified_types; 246 247-- This test checks that proper typmods are assigned in a multi-row VALUES 248 249CREATE VIEW tt1 AS 250 SELECT * FROM ( 251 VALUES 252 ('abc'::varchar(3), '0123456789', 42, 'abcd'::varchar(4)), 253 ('0123456789', 'abc'::varchar(3), 42.12, 'abc'::varchar(4)) 254 ) vv(a,b,c,d); 255\d+ tt1 256SELECT * FROM tt1; 257SELECT a::varchar(3) FROM tt1; 258DROP VIEW tt1; 259 260-- Test view decompilation in the face of relation renaming conflicts 261 262CREATE TABLE tt1 (f1 int, f2 int, f3 text); 263CREATE TABLE tx1 (x1 int, x2 int, x3 text); 264CREATE TABLE temp_view_test.tt1 (y1 int, f2 int, f3 text); 265 266CREATE VIEW aliased_view_1 AS 267 select * from tt1 268 where exists (select 1 from tx1 where tt1.f1 = tx1.x1); 269CREATE VIEW aliased_view_2 AS 270 select * from tt1 a1 271 where exists (select 1 from tx1 where a1.f1 = tx1.x1); 272CREATE VIEW aliased_view_3 AS 273 select * from tt1 274 where exists (select 1 from tx1 a2 where tt1.f1 = a2.x1); 275CREATE VIEW aliased_view_4 AS 276 select * from temp_view_test.tt1 277 where exists (select 1 from tt1 where temp_view_test.tt1.y1 = tt1.f1); 278 279\d+ aliased_view_1 280\d+ aliased_view_2 281\d+ aliased_view_3 282\d+ aliased_view_4 283 284ALTER TABLE tx1 RENAME TO a1; 285 286\d+ aliased_view_1 287\d+ aliased_view_2 288\d+ aliased_view_3 289\d+ aliased_view_4 290 291ALTER TABLE tt1 RENAME TO a2; 292 293\d+ aliased_view_1 294\d+ aliased_view_2 295\d+ aliased_view_3 296\d+ aliased_view_4 297 298ALTER TABLE a1 RENAME TO tt1; 299 300\d+ aliased_view_1 301\d+ aliased_view_2 302\d+ aliased_view_3 303\d+ aliased_view_4 304 305ALTER TABLE a2 RENAME TO tx1; 306ALTER TABLE tx1 SET SCHEMA temp_view_test; 307 308\d+ aliased_view_1 309\d+ aliased_view_2 310\d+ aliased_view_3 311\d+ aliased_view_4 312 313ALTER TABLE temp_view_test.tt1 RENAME TO tmp1; 314ALTER TABLE temp_view_test.tmp1 SET SCHEMA testviewschm2; 315ALTER TABLE tmp1 RENAME TO tx1; 316 317\d+ aliased_view_1 318\d+ aliased_view_2 319\d+ aliased_view_3 320\d+ aliased_view_4 321 322-- Test aliasing of joins 323 324create view view_of_joins as 325select * from 326 (select * from (tbl1 cross join tbl2) same) ss, 327 (tbl3 cross join tbl4) same; 328 329\d+ view_of_joins 330 331-- Test view decompilation in the face of column addition/deletion/renaming 332 333create table tt2 (a int, b int, c int); 334create table tt3 (ax int8, b int2, c numeric); 335create table tt4 (ay int, b int, q int); 336 337create view v1 as select * from tt2 natural join tt3; 338create view v1a as select * from (tt2 natural join tt3) j; 339create view v2 as select * from tt2 join tt3 using (b,c) join tt4 using (b); 340create view v2a as select * from (tt2 join tt3 using (b,c) join tt4 using (b)) j; 341create view v3 as select * from tt2 join tt3 using (b,c) full join tt4 using (b); 342 343select pg_get_viewdef('v1', true); 344select pg_get_viewdef('v1a', true); 345select pg_get_viewdef('v2', true); 346select pg_get_viewdef('v2a', true); 347select pg_get_viewdef('v3', true); 348 349alter table tt2 add column d int; 350alter table tt2 add column e int; 351 352select pg_get_viewdef('v1', true); 353select pg_get_viewdef('v1a', true); 354select pg_get_viewdef('v2', true); 355select pg_get_viewdef('v2a', true); 356select pg_get_viewdef('v3', true); 357 358alter table tt3 rename c to d; 359 360select pg_get_viewdef('v1', true); 361select pg_get_viewdef('v1a', true); 362select pg_get_viewdef('v2', true); 363select pg_get_viewdef('v2a', true); 364select pg_get_viewdef('v3', true); 365 366alter table tt3 add column c int; 367alter table tt3 add column e int; 368 369select pg_get_viewdef('v1', true); 370select pg_get_viewdef('v1a', true); 371select pg_get_viewdef('v2', true); 372select pg_get_viewdef('v2a', true); 373select pg_get_viewdef('v3', true); 374 375alter table tt2 drop column d; 376 377select pg_get_viewdef('v1', true); 378select pg_get_viewdef('v1a', true); 379select pg_get_viewdef('v2', true); 380select pg_get_viewdef('v2a', true); 381select pg_get_viewdef('v3', true); 382 383create table tt5 (a int, b int); 384create table tt6 (c int, d int); 385create view vv1 as select * from (tt5 cross join tt6) j(aa,bb,cc,dd); 386select pg_get_viewdef('vv1', true); 387alter table tt5 add column c int; 388select pg_get_viewdef('vv1', true); 389alter table tt5 add column cc int; 390select pg_get_viewdef('vv1', true); 391alter table tt5 drop column c; 392select pg_get_viewdef('vv1', true); 393 394create view v4 as select * from v1; 395alter view v1 rename column a to x; 396select pg_get_viewdef('v1', true); 397select pg_get_viewdef('v4', true); 398 399 400-- Unnamed FULL JOIN USING is lots of fun too 401 402create table tt7 (x int, xx int, y int); 403alter table tt7 drop column xx; 404create table tt8 (x int, z int); 405 406create view vv2 as 407select * from (values(1,2,3,4,5)) v(a,b,c,d,e) 408union all 409select * from tt7 full join tt8 using (x), tt8 tt8x; 410 411select pg_get_viewdef('vv2', true); 412 413create view vv3 as 414select * from (values(1,2,3,4,5,6)) v(a,b,c,x,e,f) 415union all 416select * from 417 tt7 full join tt8 using (x), 418 tt7 tt7x full join tt8 tt8x using (x); 419 420select pg_get_viewdef('vv3', true); 421 422create view vv4 as 423select * from (values(1,2,3,4,5,6,7)) v(a,b,c,x,e,f,g) 424union all 425select * from 426 tt7 full join tt8 using (x), 427 tt7 tt7x full join tt8 tt8x using (x) full join tt8 tt8y using (x); 428 429select pg_get_viewdef('vv4', true); 430 431alter table tt7 add column zz int; 432alter table tt7 add column z int; 433alter table tt7 drop column zz; 434alter table tt8 add column z2 int; 435 436select pg_get_viewdef('vv2', true); 437select pg_get_viewdef('vv3', true); 438select pg_get_viewdef('vv4', true); 439 440-- Implicit coercions in a JOIN USING create issues similar to FULL JOIN 441 442create table tt7a (x date, xx int, y int); 443alter table tt7a drop column xx; 444create table tt8a (x timestamptz, z int); 445 446create view vv2a as 447select * from (values(now(),2,3,now(),5)) v(a,b,c,d,e) 448union all 449select * from tt7a left join tt8a using (x), tt8a tt8ax; 450 451select pg_get_viewdef('vv2a', true); 452 453-- 454-- Also check dropping a column that existed when the view was made 455-- 456 457create table tt9 (x int, xx int, y int); 458create table tt10 (x int, z int); 459 460create view vv5 as select x,y,z from tt9 join tt10 using(x); 461 462select pg_get_viewdef('vv5', true); 463 464alter table tt9 drop column xx; 465 466select pg_get_viewdef('vv5', true); 467 468-- 469-- Another corner case is that we might add a column to a table below a 470-- JOIN USING, and thereby make the USING column name ambiguous 471-- 472 473create table tt11 (x int, y int); 474create table tt12 (x int, z int); 475create table tt13 (z int, q int); 476 477create view vv6 as select x,y,z,q from 478 (tt11 join tt12 using(x)) join tt13 using(z); 479 480select pg_get_viewdef('vv6', true); 481 482alter table tt11 add column z int; 483 484select pg_get_viewdef('vv6', true); 485 486-- 487-- Check cases involving dropped/altered columns in a function's rowtype result 488-- 489 490create table tt14t (f1 text, f2 text, f3 text, f4 text); 491insert into tt14t values('foo', 'bar', 'baz', '42'); 492 493alter table tt14t drop column f2; 494 495create function tt14f() returns setof tt14t as 496$$ 497declare 498 rec1 record; 499begin 500 for rec1 in select * from tt14t 501 loop 502 return next rec1; 503 end loop; 504end; 505$$ 506language plpgsql; 507 508create view tt14v as select t.* from tt14f() t; 509 510select pg_get_viewdef('tt14v', true); 511select * from tt14v; 512 513begin; 514 515-- this perhaps should be rejected, but it isn't: 516alter table tt14t drop column f3; 517 518-- f3 is still in the view ... 519select pg_get_viewdef('tt14v', true); 520-- but will fail at execution 521select f1, f4 from tt14v; 522select * from tt14v; 523 524rollback; 525 526begin; 527 528-- this perhaps should be rejected, but it isn't: 529alter table tt14t alter column f4 type integer using f4::integer; 530 531-- f4 is still in the view ... 532select pg_get_viewdef('tt14v', true); 533-- but will fail at execution 534select f1, f3 from tt14v; 535select * from tt14v; 536 537rollback; 538 539-- check display of whole-row variables in some corner cases 540 541create type nestedcomposite as (x int8_tbl); 542create view tt15v as select row(i)::nestedcomposite from int8_tbl i; 543select * from tt15v; 544select pg_get_viewdef('tt15v', true); 545select row(i.*::int8_tbl)::nestedcomposite from int8_tbl i; 546 547create view tt16v as select * from int8_tbl i, lateral(values(i)) ss; 548select * from tt16v; 549select pg_get_viewdef('tt16v', true); 550select * from int8_tbl i, lateral(values(i.*::int8_tbl)) ss; 551 552create view tt17v as select * from int8_tbl i where i in (values(i)); 553select * from tt17v; 554select pg_get_viewdef('tt17v', true); 555select * from int8_tbl i where i.* in (values(i.*::int8_tbl)); 556 557-- check unique-ification of overlength names 558 559create view tt18v as 560 select * from int8_tbl xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxy 561 union all 562 select * from int8_tbl xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxz; 563select pg_get_viewdef('tt18v', true); 564explain (costs off) select * from tt18v; 565 566-- check display of ScalarArrayOp with a sub-select 567 568select 'foo'::text = any(array['abc','def','foo']::text[]); 569select 'foo'::text = any((select array['abc','def','foo']::text[])); -- fail 570select 'foo'::text = any((select array['abc','def','foo']::text[])::text[]); 571 572create view tt19v as 573select 'foo'::text = any(array['abc','def','foo']::text[]) c1, 574 'foo'::text = any((select array['abc','def','foo']::text[])::text[]) c2; 575select pg_get_viewdef('tt19v', true); 576 577-- check display of assorted RTE_FUNCTION expressions 578 579create view tt20v as 580select * from 581 coalesce(1,2) as c, 582 collation for ('x'::text) col, 583 current_date as d, 584 localtimestamp(3) as t, 585 cast(1+2 as int4) as i4, 586 cast(1+2 as int8) as i8; 587select pg_get_viewdef('tt20v', true); 588 589-- corner cases with empty join conditions 590 591create view tt21v as 592select * from tt5 natural inner join tt6; 593select pg_get_viewdef('tt21v', true); 594 595create view tt22v as 596select * from tt5 natural left join tt6; 597select pg_get_viewdef('tt22v', true); 598 599-- check handling of views with immediately-renamed columns 600 601create view tt23v (col_a, col_b) as 602select q1 as other_name1, q2 as other_name2 from int8_tbl 603union 604select 42, 43; 605 606select pg_get_viewdef('tt23v', true); 607select pg_get_ruledef(oid, true) from pg_rewrite 608 where ev_class = 'tt23v'::regclass and ev_type = '1'; 609 610-- test extraction of FieldSelect field names (get_name_for_var_field) 611 612create view tt24v as 613with cte as materialized (select r from (values(1,2),(3,4)) r) 614select (r).column2 as col_a, (rr).column2 as col_b from 615 cte join (select rr from (values(1,7),(3,8)) rr limit 2) ss 616 on (r).column1 = (rr).column1; 617select pg_get_viewdef('tt24v', true); 618create view tt25v as 619with cte as materialized (select pg_get_keywords() k) 620select (k).word from cte; 621select pg_get_viewdef('tt25v', true); 622-- also check cases seen only in EXPLAIN 623explain (verbose, costs off) 624select * from tt24v; 625explain (verbose, costs off) 626select (r).column2 from (select r from (values(1,2),(3,4)) r limit 1) ss; 627 628-- test pretty-print parenthesization rules, and SubLink deparsing 629 630create view tt26v as 631select x + y + z as c1, 632 (x * y) + z as c2, 633 x + (y * z) as c3, 634 (x + y) * z as c4, 635 x * (y + z) as c5, 636 x + (y + z) as c6, 637 x + (y # z) as c7, 638 (x > y) AND (y > z OR x > z) as c8, 639 (x > y) OR (y > z AND NOT (x > z)) as c9, 640 (x,y) <> ALL (values(1,2),(3,4)) as c10, 641 (x,y) <= ANY (values(1,2),(3,4)) as c11 642from (values(1,2,3)) v(x,y,z); 643select pg_get_viewdef('tt26v', true); 644 645-- clean up all the random objects we made above 646DROP SCHEMA temp_view_test CASCADE; 647DROP SCHEMA testviewschm2 CASCADE; 648