| blob | f551624afb3a3cd2b66955910bc1983123dac5ad |
1 --
2 -- CREATE_VIEW
3 -- Virtual class definitions
4 -- (this also tests the query rewrite system)
5 --
6 -- directory paths and dlsuffix are passed to us in environment variables
7 \getenv abs_srcdir PG_ABS_SRCDIR
8 \getenv libdir PG_LIBDIR
9 \getenv dlsuffix PG_DLSUFFIX
10 \set regresslib :libdir '/regress' :dlsuffix
11 CREATE FUNCTION interpt_pp(path, path)
12 RETURNS point
13 AS :'regresslib'
14 LANGUAGE C STRICT;
15 CREATE TABLE real_city (
16 pop int4,
17 cname text,
18 outline path
19 );
20 \set filename :abs_srcdir '/data/real_city.data'
21 COPY real_city FROM :'filename';
22 ANALYZE real_city;
23 SELECT *
24 INTO TABLE ramp
25 FROM ONLY road
26 WHERE name ~ '.*Ramp';
27 CREATE VIEW street AS
28 SELECT r.name, r.thepath, c.cname AS cname
29 FROM ONLY road r, real_city c
30 WHERE c.outline ?# r.thepath;
31 CREATE VIEW iexit AS
32 SELECT ih.name, ih.thepath,
33 interpt_pp(ih.thepath, r.thepath) AS exit
34 FROM ihighway ih, ramp r
35 WHERE ih.thepath ?# r.thepath;
36 CREATE VIEW toyemp AS
37 SELECT name, age, location, 12*salary AS annualsal
38 FROM emp;
39 -- Test comments
40 COMMENT ON VIEW noview IS 'no view';
41 ERROR: relation "noview" does not exist
42 COMMENT ON VIEW toyemp IS 'is a view';
43 COMMENT ON VIEW toyemp IS NULL;
44 -- These views are left around mainly to exercise special cases in pg_dump.
45 CREATE TABLE view_base_table (key int PRIMARY KEY, data varchar(20));
46 CREATE VIEW key_dependent_view AS
47 SELECT * FROM view_base_table GROUP BY key;
48 ALTER TABLE view_base_table DROP CONSTRAINT view_base_table_pkey; -- fails
49 ERROR: cannot drop constraint view_base_table_pkey on table view_base_table because other objects depend on it
50 DETAIL: view key_dependent_view depends on constraint view_base_table_pkey on table view_base_table
51 HINT: Use DROP ... CASCADE to drop the dependent objects too.
52 CREATE VIEW key_dependent_view_no_cols AS
53 SELECT FROM view_base_table GROUP BY key HAVING length(data) > 0;
54 --
55 -- CREATE OR REPLACE VIEW
56 --
57 CREATE TABLE viewtest_tbl (a int, b int, c numeric(10,1), d text COLLATE "C");
58 COPY viewtest_tbl FROM stdin;
59 CREATE OR REPLACE VIEW viewtest AS
60 SELECT * FROM viewtest_tbl;
61 CREATE OR REPLACE VIEW viewtest AS
62 SELECT * FROM viewtest_tbl WHERE a > 10;
63 SELECT * FROM viewtest;
64 a | b | c | d
65 ----+----+-----+-------
66 15 | 20 | 3.3 | xyzz
67 20 | 25 | 4.4 | xyzzy
68 (2 rows)
70 CREATE OR REPLACE VIEW viewtest AS
71 SELECT a, b, c, d FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC;
72 SELECT * FROM viewtest;
73 a | b | c | d
74 ----+----+-----+-------
75 20 | 25 | 4.4 | xyzzy
76 15 | 20 | 3.3 | xyzz
77 10 | 15 | 2.2 | xyz
78 (3 rows)
80 -- should fail
81 CREATE OR REPLACE VIEW viewtest AS
82 SELECT a FROM viewtest_tbl WHERE a <> 20;
83 ERROR: cannot drop columns from view
84 -- should fail
85 CREATE OR REPLACE VIEW viewtest AS
86 SELECT 1, * FROM viewtest_tbl;
87 ERROR: cannot change name of view column "a" to "?column?"
88 HINT: Use ALTER VIEW ... RENAME COLUMN ... to change name of view column instead.
89 -- should fail
90 CREATE OR REPLACE VIEW viewtest AS
91 SELECT a, b::numeric, c, d FROM viewtest_tbl;
92 ERROR: cannot change data type of view column "b" from integer to numeric
93 -- should fail
94 CREATE OR REPLACE VIEW viewtest AS
95 SELECT a, b, c::numeric(10,2), d FROM viewtest_tbl;
96 ERROR: cannot change data type of view column "c" from numeric(10,1) to numeric(10,2)
97 -- should fail
98 CREATE OR REPLACE VIEW viewtest AS
99 SELECT a, b, c, d COLLATE "POSIX" FROM viewtest_tbl;
100 ERROR: cannot change collation of view column "d" from "C" to "POSIX"
101 -- should work
102 CREATE OR REPLACE VIEW viewtest AS
103 SELECT a, b, c, d, 0 AS e FROM viewtest_tbl;
104 DROP VIEW viewtest;
105 DROP TABLE viewtest_tbl;
106 -- tests for temporary views
107 CREATE SCHEMA temp_view_test
108 CREATE TABLE base_table (a int, id int)
109 CREATE TABLE base_table2 (a int, id int);
110 SET search_path TO temp_view_test, public;
111 CREATE TEMPORARY TABLE temp_table (a int, id int);
112 -- should be created in temp_view_test schema
113 CREATE VIEW v1 AS SELECT * FROM base_table;
114 -- should be created in temp object schema
115 CREATE VIEW v1_temp AS SELECT * FROM temp_table;
116 NOTICE: view "v1_temp" will be a temporary view
117 -- should be created in temp object schema
118 CREATE TEMP VIEW v2_temp AS SELECT * FROM base_table;
119 -- should be created in temp_views schema
120 CREATE VIEW temp_view_test.v2 AS SELECT * FROM base_table;
121 -- should fail
122 CREATE VIEW temp_view_test.v3_temp AS SELECT * FROM temp_table;
123 NOTICE: view "v3_temp" will be a temporary view
124 ERROR: cannot create temporary relation in non-temporary schema
125 -- should fail
126 CREATE SCHEMA test_view_schema
127 CREATE TEMP VIEW testview AS SELECT 1;
128 ERROR: cannot create temporary relation in non-temporary schema
129 -- joins: if any of the join relations are temporary, the view
130 -- should also be temporary
131 -- should be non-temp
132 CREATE VIEW v3 AS
133 SELECT t1.a AS t1_a, t2.a AS t2_a
134 FROM base_table t1, base_table2 t2
135 WHERE t1.id = t2.id;
136 -- should be temp (one join rel is temp)
137 CREATE VIEW v4_temp AS
138 SELECT t1.a AS t1_a, t2.a AS t2_a
139 FROM base_table t1, temp_table t2
140 WHERE t1.id = t2.id;
141 NOTICE: view "v4_temp" will be a temporary view
142 -- should be temp
143 CREATE VIEW v5_temp AS
144 SELECT t1.a AS t1_a, t2.a AS t2_a, t3.a AS t3_a
145 FROM base_table t1, base_table2 t2, temp_table t3
146 WHERE t1.id = t2.id and t2.id = t3.id;
147 NOTICE: view "v5_temp" will be a temporary view
148 -- subqueries
149 CREATE VIEW v4 AS SELECT * FROM base_table WHERE id IN (SELECT id FROM base_table2);
150 CREATE VIEW v5 AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM base_table2) t2;
151 CREATE VIEW v6 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM base_table2);
152 CREATE VIEW v7 AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM base_table2);
153 CREATE VIEW v8 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1);
154 CREATE VIEW v6_temp AS SELECT * FROM base_table WHERE id IN (SELECT id FROM temp_table);
155 NOTICE: view "v6_temp" will be a temporary view
156 CREATE VIEW v7_temp AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM temp_table) t2;
157 NOTICE: view "v7_temp" will be a temporary view
158 CREATE VIEW v8_temp AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM temp_table);
159 NOTICE: view "v8_temp" will be a temporary view
160 CREATE VIEW v9_temp AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM temp_table);
161 NOTICE: view "v9_temp" will be a temporary view
162 -- a view should also be temporary if it references a temporary view
163 CREATE VIEW v10_temp AS SELECT * FROM v7_temp;
164 NOTICE: view "v10_temp" will be a temporary view
165 CREATE VIEW v11_temp AS SELECT t1.id, t2.a FROM base_table t1, v10_temp t2;
166 NOTICE: view "v11_temp" will be a temporary view
167 CREATE VIEW v12_temp AS SELECT true FROM v11_temp;
168 NOTICE: view "v12_temp" will be a temporary view
169 -- a view should also be temporary if it references a temporary sequence
170 CREATE SEQUENCE seq1;
171 CREATE TEMPORARY SEQUENCE seq1_temp;
172 CREATE VIEW v9 AS SELECT seq1.is_called FROM seq1;
173 CREATE VIEW v13_temp AS SELECT seq1_temp.is_called FROM seq1_temp;
174 NOTICE: view "v13_temp" will be a temporary view
175 SELECT relname FROM pg_class
176 WHERE relname LIKE 'v_'
177 AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'temp_view_test')
178 ORDER BY relname;
179 relname
180 ---------
181 v1
182 v2
183 v3
184 v4
185 v5
186 v6
187 v7
188 v8
189 v9
190 (9 rows)
192 SELECT relname FROM pg_class
193 WHERE relname LIKE 'v%'
194 AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%')
195 ORDER BY relname;
196 relname
197 ----------
198 v10_temp
199 v11_temp
200 v12_temp
201 v13_temp
202 v1_temp
203 v2_temp
204 v4_temp
205 v5_temp
206 v6_temp
207 v7_temp
208 v8_temp
209 v9_temp
210 (12 rows)
212 CREATE SCHEMA testviewschm2;
213 SET search_path TO testviewschm2, public;
214 CREATE TABLE t1 (num int, name text);
215 CREATE TABLE t2 (num2 int, value text);
216 CREATE TEMP TABLE tt (num2 int, value text);
217 CREATE VIEW nontemp1 AS SELECT * FROM t1 CROSS JOIN t2;
218 CREATE VIEW temporal1 AS SELECT * FROM t1 CROSS JOIN tt;
219 NOTICE: view "temporal1" will be a temporary view
220 CREATE VIEW nontemp2 AS SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num2;
221 CREATE VIEW temporal2 AS SELECT * FROM t1 INNER JOIN tt ON t1.num = tt.num2;
222 NOTICE: view "temporal2" will be a temporary view
223 CREATE VIEW nontemp3 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2;
224 CREATE VIEW temporal3 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2;
225 NOTICE: view "temporal3" will be a temporary view
226 CREATE VIEW nontemp4 AS SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num2 AND t2.value = 'xxx';
227 CREATE VIEW temporal4 AS SELECT * FROM t1 LEFT JOIN tt ON t1.num = tt.num2 AND tt.value = 'xxx';
228 NOTICE: view "temporal4" will be a temporary view
229 SELECT relname FROM pg_class
230 WHERE relname LIKE 'nontemp%'
231 AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'testviewschm2')
232 ORDER BY relname;
233 relname
234 ----------
235 nontemp1
236 nontemp2
237 nontemp3
238 nontemp4
239 (4 rows)
241 SELECT relname FROM pg_class
242 WHERE relname LIKE 'temporal%'
243 AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname LIKE 'pg_temp%')
244 ORDER BY relname;
245 relname
246 -----------
247 temporal1
248 temporal2
249 temporal3
250 temporal4
251 (4 rows)
253 CREATE TABLE tbl1 ( a int, b int);
254 CREATE TABLE tbl2 (c int, d int);
255 CREATE TABLE tbl3 (e int, f int);
256 CREATE TABLE tbl4 (g int, h int);
257 CREATE TEMP TABLE tmptbl (i int, j int);
258 --Should be in testviewschm2
259 CREATE VIEW pubview AS SELECT * FROM tbl1 WHERE tbl1.a
260 BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2)
261 AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f);
262 SELECT count(*) FROM pg_class where relname = 'pubview'
263 AND relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname = 'testviewschm2');
264 count
265 -------
266 1
267 (1 row)
269 --Should be in temp object schema
270 CREATE VIEW mytempview AS SELECT * FROM tbl1 WHERE tbl1.a
271 BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2)
272 AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f)
273 AND NOT EXISTS (SELECT g FROM tbl4 LEFT JOIN tmptbl ON tbl4.h = tmptbl.j);
274 NOTICE: view "mytempview" will be a temporary view
275 SELECT count(*) FROM pg_class where relname LIKE 'mytempview'
276 And relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname LIKE 'pg_temp%');
277 count
278 -------
279 1
280 (1 row)
282 --
283 -- CREATE VIEW and WITH(...) clause
284 --
285 CREATE VIEW mysecview1
286 AS SELECT * FROM tbl1 WHERE a = 0;
287 CREATE VIEW mysecview2 WITH (security_barrier=true)
288 AS SELECT * FROM tbl1 WHERE a > 0;
289 CREATE VIEW mysecview3 WITH (security_barrier=false)
290 AS SELECT * FROM tbl1 WHERE a < 0;
291 CREATE VIEW mysecview4 WITH (security_barrier)
292 AS SELECT * FROM tbl1 WHERE a <> 0;
293 CREATE VIEW mysecview5 WITH (security_barrier=100) -- Error
294 AS SELECT * FROM tbl1 WHERE a > 100;
295 ERROR: invalid value for boolean option "security_barrier": 100
296 CREATE VIEW mysecview6 WITH (invalid_option) -- Error
297 AS SELECT * FROM tbl1 WHERE a < 100;
298 ERROR: unrecognized parameter "invalid_option"
299 CREATE VIEW mysecview7 WITH (security_invoker=true)
300 AS SELECT * FROM tbl1 WHERE a = 100;
301 CREATE VIEW mysecview8 WITH (security_invoker=false, security_barrier=true)
302 AS SELECT * FROM tbl1 WHERE a > 100;
303 CREATE VIEW mysecview9 WITH (security_invoker)
304 AS SELECT * FROM tbl1 WHERE a < 100;
305 CREATE VIEW mysecview10 WITH (security_invoker=100) -- Error
306 AS SELECT * FROM tbl1 WHERE a <> 100;
307 ERROR: invalid value for boolean option "security_invoker": 100
308 SELECT relname, relkind, reloptions FROM pg_class
309 WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass,
310 'mysecview3'::regclass, 'mysecview4'::regclass,
311 'mysecview7'::regclass, 'mysecview8'::regclass,
312 'mysecview9'::regclass)
313 ORDER BY relname;
314 relname | relkind | reloptions
315 ------------+---------+------------------------------------------------
316 mysecview1 | v |
317 mysecview2 | v | {security_barrier=true}
318 mysecview3 | v | {security_barrier=false}
319 mysecview4 | v | {security_barrier=true}
320 mysecview7 | v | {security_invoker=true}
321 mysecview8 | v | {security_invoker=false,security_barrier=true}
322 mysecview9 | v | {security_invoker=true}
323 (7 rows)
325 CREATE OR REPLACE VIEW mysecview1
326 AS SELECT * FROM tbl1 WHERE a = 256;
327 CREATE OR REPLACE VIEW mysecview2
328 AS SELECT * FROM tbl1 WHERE a > 256;
329 CREATE OR REPLACE VIEW mysecview3 WITH (security_barrier=true)
330 AS SELECT * FROM tbl1 WHERE a < 256;
331 CREATE OR REPLACE VIEW mysecview4 WITH (security_barrier=false)
332 AS SELECT * FROM tbl1 WHERE a <> 256;
333 CREATE OR REPLACE VIEW mysecview7
334 AS SELECT * FROM tbl1 WHERE a > 256;
335 CREATE OR REPLACE VIEW mysecview8 WITH (security_invoker=true)
336 AS SELECT * FROM tbl1 WHERE a < 256;
337 CREATE OR REPLACE VIEW mysecview9 WITH (security_invoker=false, security_barrier=true)
338 AS SELECT * FROM tbl1 WHERE a <> 256;
339 SELECT relname, relkind, reloptions FROM pg_class
340 WHERE oid in ('mysecview1'::regclass, 'mysecview2'::regclass,
341 'mysecview3'::regclass, 'mysecview4'::regclass,
342 'mysecview7'::regclass, 'mysecview8'::regclass,
343 'mysecview9'::regclass)
344 ORDER BY relname;
345 relname | relkind | reloptions
346 ------------+---------+------------------------------------------------
347 mysecview1 | v |
348 mysecview2 | v |
349 mysecview3 | v | {security_barrier=true}
350 mysecview4 | v | {security_barrier=false}
351 mysecview7 | v |
352 mysecview8 | v | {security_invoker=true}
353 mysecview9 | v | {security_invoker=false,security_barrier=true}
354 (7 rows)
356 -- Check that unknown literals are converted to "text" in CREATE VIEW,
357 -- so that we don't end up with unknown-type columns.
358 CREATE VIEW unspecified_types AS
359 SELECT 42 as i, 42.5 as num, 'foo' as u, 'foo'::unknown as u2, null as n;
360 \d+ unspecified_types
361 View "testviewschm2.unspecified_types"
362 Column | Type | Collation | Nullable | Default | Storage | Description
363 --------+---------+-----------+----------+---------+----------+-------------
364 i | integer | | | | plain |
365 num | numeric | | | | main |
366 u | text | | | | extended |
367 u2 | text | | | | extended |
368 n | text | | | | extended |
369 View definition:
370 SELECT 42 AS i,
371 42.5 AS num,
372 'foo'::text AS u,
373 'foo'::text AS u2,
374 NULL::text AS n;
376 SELECT * FROM unspecified_types;
377 i | num | u | u2 | n
378 ----+------+-----+-----+---
379 42 | 42.5 | foo | foo |
380 (1 row)
382 -- This test checks that proper typmods are assigned in a multi-row VALUES
383 CREATE VIEW tt1 AS
384 SELECT * FROM (
385 VALUES
386 ('abc'::varchar(3), '0123456789', 42, 'abcd'::varchar(4)),
387 ('0123456789', 'abc'::varchar(3), 42.12, 'abc'::varchar(4))
388 ) vv(a,b,c,d);
389 \d+ tt1
390 View "testviewschm2.tt1"
391 Column | Type | Collation | Nullable | Default | Storage | Description
392 --------+----------------------+-----------+----------+---------+----------+-------------
393 a | character varying | | | | extended |
394 b | character varying | | | | extended |
395 c | numeric | | | | main |
396 d | character varying(4) | | | | extended |
397 View definition:
398 SELECT a,
399 b,
400 c,
401 d
402 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);
404 SELECT * FROM tt1;
405 a | b | c | d
406 ------------+------------+-------+------
407 abc | 0123456789 | 42 | abcd
408 0123456789 | abc | 42.12 | abc
409 (2 rows)
411 SELECT a::varchar(3) FROM tt1;
412 a
413 -----
414 abc
415 012
416 (2 rows)
418 DROP VIEW tt1;
419 -- Test view decompilation in the face of relation renaming conflicts
420 CREATE TABLE tt1 (f1 int, f2 int, f3 text);
421 CREATE TABLE tx1 (x1 int, x2 int, x3 text);
422 CREATE TABLE temp_view_test.tt1 (y1 int, f2 int, f3 text);
423 CREATE VIEW aliased_view_1 AS
424 select * from tt1
425 where exists (select 1 from tx1 where tt1.f1 = tx1.x1);
426 CREATE VIEW aliased_view_2 AS
427 select * from tt1 a1
428 where exists (select 1 from tx1 where a1.f1 = tx1.x1);
429 CREATE VIEW aliased_view_3 AS
430 select * from tt1
431 where exists (select 1 from tx1 a2 where tt1.f1 = a2.x1);
432 CREATE VIEW aliased_view_4 AS
433 select * from temp_view_test.tt1
434 where exists (select 1 from tt1 where temp_view_test.tt1.y1 = tt1.f1);
435 \d+ aliased_view_1
436 View "testviewschm2.aliased_view_1"
437 Column | Type | Collation | Nullable | Default | Storage | Description
438 --------+---------+-----------+----------+---------+----------+-------------
439 f1 | integer | | | | plain |
440 f2 | integer | | | | plain |
441 f3 | text | | | | extended |
442 View definition:
443 SELECT f1,
444 f2,
445 f3
446 FROM tt1
447 WHERE (EXISTS ( SELECT 1
448 FROM tx1
449 WHERE tt1.f1 = tx1.x1));
451 \d+ aliased_view_2
452 View "testviewschm2.aliased_view_2"
453 Column | Type | Collation | Nullable | Default | Storage | Description
454 --------+---------+-----------+----------+---------+----------+-------------
455 f1 | integer | | | | plain |
456 f2 | integer | | | | plain |
457 f3 | text | | | | extended |
458 View definition:
459 SELECT f1,
460 f2,
461 f3
462 FROM tt1 a1
463 WHERE (EXISTS ( SELECT 1
464 FROM tx1
465 WHERE a1.f1 = tx1.x1));
467 \d+ aliased_view_3
468 View "testviewschm2.aliased_view_3"
469 Column | Type | Collation | Nullable | Default | Storage | Description
470 --------+---------+-----------+----------+---------+----------+-------------
471 f1 | integer | | | | plain |
472 f2 | integer | | | | plain |
473 f3 | text | | | | extended |
474 View definition:
475 SELECT f1,
476 f2,
477 f3
478 FROM tt1
479 WHERE (EXISTS ( SELECT 1
480 FROM tx1 a2
481 WHERE tt1.f1 = a2.x1));
483 \d+ aliased_view_4
484 View "testviewschm2.aliased_view_4"
485 Column | Type | Collation | Nullable | Default | Storage | Description
486 --------+---------+-----------+----------+---------+----------+-------------
487 y1 | integer | | | | plain |
488 f2 | integer | | | | plain |
489 f3 | text | | | | extended |
490 View definition:
491 SELECT y1,
492 f2,
493 f3
494 FROM temp_view_test.tt1
495 WHERE (EXISTS ( SELECT 1
496 FROM tt1 tt1_1
497 WHERE tt1.y1 = tt1_1.f1));
499 ALTER TABLE tx1 RENAME TO a1;
500 \d+ aliased_view_1
501 View "testviewschm2.aliased_view_1"
502 Column | Type | Collation | Nullable | Default | Storage | Description
503 --------+---------+-----------+----------+---------+----------+-------------
504 f1 | integer | | | | plain |
505 f2 | integer | | | | plain |
506 f3 | text | | | | extended |
507 View definition:
508 SELECT f1,
509 f2,
510 f3
511 FROM tt1
512 WHERE (EXISTS ( SELECT 1
513 FROM a1
514 WHERE tt1.f1 = a1.x1));
516 \d+ aliased_view_2
517 View "testviewschm2.aliased_view_2"
518 Column | Type | Collation | Nullable | Default | Storage | Description
519 --------+---------+-----------+----------+---------+----------+-------------
520 f1 | integer | | | | plain |
521 f2 | integer | | | | plain |
522 f3 | text | | | | extended |
523 View definition:
524 SELECT f1,
525 f2,
526 f3
527 FROM tt1 a1
528 WHERE (EXISTS ( SELECT 1
529 FROM a1 a1_1
530 WHERE a1.f1 = a1_1.x1));
532 \d+ aliased_view_3
533 View "testviewschm2.aliased_view_3"
534 Column | Type | Collation | Nullable | Default | Storage | Description
535 --------+---------+-----------+----------+---------+----------+-------------
536 f1 | integer | | | | plain |
537 f2 | integer | | | | plain |
538 f3 | text | | | | extended |
539 View definition:
540 SELECT f1,
541 f2,
542 f3
543 FROM tt1
544 WHERE (EXISTS ( SELECT 1
545 FROM a1 a2
546 WHERE tt1.f1 = a2.x1));
548 \d+ aliased_view_4
549 View "testviewschm2.aliased_view_4"
550 Column | Type | Collation | Nullable | Default | Storage | Description
551 --------+---------+-----------+----------+---------+----------+-------------
552 y1 | integer | | | | plain |
553 f2 | integer | | | | plain |
554 f3 | text | | | | extended |
555 View definition:
556 SELECT y1,
557 f2,
558 f3
559 FROM temp_view_test.tt1
560 WHERE (EXISTS ( SELECT 1
561 FROM tt1 tt1_1
562 WHERE tt1.y1 = tt1_1.f1));
564 ALTER TABLE tt1 RENAME TO a2;
565 \d+ aliased_view_1
566 View "testviewschm2.aliased_view_1"
567 Column | Type | Collation | Nullable | Default | Storage | Description
568 --------+---------+-----------+----------+---------+----------+-------------
569 f1 | integer | | | | plain |
570 f2 | integer | | | | plain |
571 f3 | text | | | | extended |
572 View definition:
573 SELECT f1,
574 f2,
575 f3
576 FROM a2
577 WHERE (EXISTS ( SELECT 1
578 FROM a1
579 WHERE a2.f1 = a1.x1));
581 \d+ aliased_view_2
582 View "testviewschm2.aliased_view_2"
583 Column | Type | Collation | Nullable | Default | Storage | Description
584 --------+---------+-----------+----------+---------+----------+-------------
585 f1 | integer | | | | plain |
586 f2 | integer | | | | plain |
587 f3 | text | | | | extended |
588 View definition:
589 SELECT f1,
590 f2,
591 f3
592 FROM a2 a1
593 WHERE (EXISTS ( SELECT 1
594 FROM a1 a1_1
595 WHERE a1.f1 = a1_1.x1));
597 \d+ aliased_view_3
598 View "testviewschm2.aliased_view_3"
599 Column | Type | Collation | Nullable | Default | Storage | Description
600 --------+---------+-----------+----------+---------+----------+-------------
601 f1 | integer | | | | plain |
602 f2 | integer | | | | plain |
603 f3 | text | | | | extended |
604 View definition:
605 SELECT f1,
606 f2,
607 f3
608 FROM a2
609 WHERE (EXISTS ( SELECT 1
610 FROM a1 a2_1
611 WHERE a2.f1 = a2_1.x1));
613 \d+ aliased_view_4
614 View "testviewschm2.aliased_view_4"
615 Column | Type | Collation | Nullable | Default | Storage | Description
616 --------+---------+-----------+----------+---------+----------+-------------
617 y1 | integer | | | | plain |
618 f2 | integer | | | | plain |
619 f3 | text | | | | extended |
620 View definition:
621 SELECT y1,
622 f2,
623 f3
624 FROM temp_view_test.tt1
625 WHERE (EXISTS ( SELECT 1
626 FROM a2
627 WHERE tt1.y1 = a2.f1));
629 ALTER TABLE a1 RENAME TO tt1;
630 \d+ aliased_view_1
631 View "testviewschm2.aliased_view_1"
632 Column | Type | Collation | Nullable | Default | Storage | Description
633 --------+---------+-----------+----------+---------+----------+-------------
634 f1 | integer | | | | plain |
635 f2 | integer | | | | plain |
636 f3 | text | | | | extended |
637 View definition:
638 SELECT f1,
639 f2,
640 f3
641 FROM a2
642 WHERE (EXISTS ( SELECT 1
643 FROM tt1
644 WHERE a2.f1 = tt1.x1));
646 \d+ aliased_view_2
647 View "testviewschm2.aliased_view_2"
648 Column | Type | Collation | Nullable | Default | Storage | Description
649 --------+---------+-----------+----------+---------+----------+-------------
650 f1 | integer | | | | plain |
651 f2 | integer | | | | plain |
652 f3 | text | | | | extended |
653 View definition:
654 SELECT f1,
655 f2,
656 f3
657 FROM a2 a1
658 WHERE (EXISTS ( SELECT 1
659 FROM tt1
660 WHERE a1.f1 = tt1.x1));
662 \d+ aliased_view_3
663 View "testviewschm2.aliased_view_3"
664 Column | Type | Collation | Nullable | Default | Storage | Description
665 --------+---------+-----------+----------+---------+----------+-------------
666 f1 | integer | | | | plain |
667 f2 | integer | | | | plain |
668 f3 | text | | | | extended |
669 View definition:
670 SELECT f1,
671 f2,
672 f3
673 FROM a2
674 WHERE (EXISTS ( SELECT 1
675 FROM tt1 a2_1
676 WHERE a2.f1 = a2_1.x1));
678 \d+ aliased_view_4
679 View "testviewschm2.aliased_view_4"
680 Column | Type | Collation | Nullable | Default | Storage | Description
681 --------+---------+-----------+----------+---------+----------+-------------
682 y1 | integer | | | | plain |
683 f2 | integer | | | | plain |
684 f3 | text | | | | extended |
685 View definition:
686 SELECT y1,
687 f2,
688 f3
689 FROM temp_view_test.tt1
690 WHERE (EXISTS ( SELECT 1
691 FROM a2
692 WHERE tt1.y1 = a2.f1));
694 ALTER TABLE a2 RENAME TO tx1;
695 ALTER TABLE tx1 SET SCHEMA temp_view_test;
696 \d+ aliased_view_1
697 View "testviewschm2.aliased_view_1"
698 Column | Type | Collation | Nullable | Default | Storage | Description
699 --------+---------+-----------+----------+---------+----------+-------------
700 f1 | integer | | | | plain |
701 f2 | integer | | | | plain |
702 f3 | text | | | | extended |
703 View definition:
704 SELECT f1,
705 f2,
706 f3
707 FROM temp_view_test.tx1
708 WHERE (EXISTS ( SELECT 1
709 FROM tt1
710 WHERE tx1.f1 = tt1.x1));
712 \d+ aliased_view_2
713 View "testviewschm2.aliased_view_2"
714 Column | Type | Collation | Nullable | Default | Storage | Description
715 --------+---------+-----------+----------+---------+----------+-------------
716 f1 | integer | | | | plain |
717 f2 | integer | | | | plain |
718 f3 | text | | | | extended |
719 View definition:
720 SELECT f1,
721 f2,
722 f3
723 FROM temp_view_test.tx1 a1
724 WHERE (EXISTS ( SELECT 1
725 FROM tt1
726 WHERE a1.f1 = tt1.x1));
728 \d+ aliased_view_3
729 View "testviewschm2.aliased_view_3"
730 Column | Type | Collation | Nullable | Default | Storage | Description
731 --------+---------+-----------+----------+---------+----------+-------------
732 f1 | integer | | | | plain |
733 f2 | integer | | | | plain |
734 f3 | text | | | | extended |
735 View definition:
736 SELECT f1,
737 f2,
738 f3
739 FROM temp_view_test.tx1
740 WHERE (EXISTS ( SELECT 1
741 FROM tt1 a2
742 WHERE tx1.f1 = a2.x1));
744 \d+ aliased_view_4
745 View "testviewschm2.aliased_view_4"
746 Column | Type | Collation | Nullable | Default | Storage | Description
747 --------+---------+-----------+----------+---------+----------+-------------
748 y1 | integer | | | | plain |
749 f2 | integer | | | | plain |
750 f3 | text | | | | extended |
751 View definition:
752 SELECT y1,
753 f2,
754 f3
755 FROM temp_view_test.tt1
756 WHERE (EXISTS ( SELECT 1
757 FROM temp_view_test.tx1
758 WHERE tt1.y1 = tx1.f1));
760 ALTER TABLE temp_view_test.tt1 RENAME TO tmp1;
761 ALTER TABLE temp_view_test.tmp1 SET SCHEMA testviewschm2;
762 ALTER TABLE tmp1 RENAME TO tx1;
763 \d+ aliased_view_1
764 View "testviewschm2.aliased_view_1"
765 Column | Type | Collation | Nullable | Default | Storage | Description
766 --------+---------+-----------+----------+---------+----------+-------------
767 f1 | integer | | | | plain |
768 f2 | integer | | | | plain |
769 f3 | text | | | | extended |
770 View definition:
771 SELECT f1,
772 f2,
773 f3
774 FROM temp_view_test.tx1
775 WHERE (EXISTS ( SELECT 1
776 FROM tt1
777 WHERE tx1.f1 = tt1.x1));
779 \d+ aliased_view_2
780 View "testviewschm2.aliased_view_2"
781 Column | Type | Collation | Nullable | Default | Storage | Description
782 --------+---------+-----------+----------+---------+----------+-------------
783 f1 | integer | | | | plain |
784 f2 | integer | | | | plain |
785 f3 | text | | | | extended |
786 View definition:
787 SELECT f1,
788 f2,
789 f3
790 FROM temp_view_test.tx1 a1
791 WHERE (EXISTS ( SELECT 1
792 FROM tt1
793 WHERE a1.f1 = tt1.x1));
795 \d+ aliased_view_3
796 View "testviewschm2.aliased_view_3"
797 Column | Type | Collation | Nullable | Default | Storage | Description
798 --------+---------+-----------+----------+---------+----------+-------------
799 f1 | integer | | | | plain |
800 f2 | integer | | | | plain |
801 f3 | text | | | | extended |
802 View definition:
803 SELECT f1,
804 f2,
805 f3
806 FROM temp_view_test.tx1
807 WHERE (EXISTS ( SELECT 1
808 FROM tt1 a2
809 WHERE tx1.f1 = a2.x1));
811 \d+ aliased_view_4
812 View "testviewschm2.aliased_view_4"
813 Column | Type | Collation | Nullable | Default | Storage | Description
814 --------+---------+-----------+----------+---------+----------+-------------
815 y1 | integer | | | | plain |
816 f2 | integer | | | | plain |
817 f3 | text | | | | extended |
818 View definition:
819 SELECT y1,
820 f2,
821 f3
822 FROM tx1
823 WHERE (EXISTS ( SELECT 1
824 FROM temp_view_test.tx1 tx1_1
825 WHERE tx1.y1 = tx1_1.f1));
827 -- Test correct deparsing of ORDER BY when there is an output name conflict
828 create view aliased_order_by as
829 select x1 as x2, x2 as x1, x3 from tt1
830 order by x2; -- this is interpreted per SQL92, so really ordering by x1
831 \d+ aliased_order_by
832 View "testviewschm2.aliased_order_by"
833 Column | Type | Collation | Nullable | Default | Storage | Description
834 --------+---------+-----------+----------+---------+----------+-------------
835 x2 | integer | | | | plain |
836 x1 | integer | | | | plain |
837 x3 | text | | | | extended |
838 View definition:
839 SELECT x1 AS x2,
840 x2 AS x1,
841 x3
842 FROM tt1
843 ORDER BY tt1.x1;
845 alter view aliased_order_by rename column x1 to x0;
846 \d+ aliased_order_by
847 View "testviewschm2.aliased_order_by"
848 Column | Type | Collation | Nullable | Default | Storage | Description
849 --------+---------+-----------+----------+---------+----------+-------------
850 x2 | integer | | | | plain |
851 x0 | integer | | | | plain |
852 x3 | text | | | | extended |
853 View definition:
854 SELECT x1 AS x2,
855 x2 AS x0,
856 x3
857 FROM tt1
858 ORDER BY x1;
860 alter view aliased_order_by rename column x3 to x1;
861 \d+ aliased_order_by
862 View "testviewschm2.aliased_order_by"
863 Column | Type | Collation | Nullable | Default | Storage | Description
864 --------+---------+-----------+----------+---------+----------+-------------
865 x2 | integer | | | | plain |
866 x0 | integer | | | | plain |
867 x1 | text | | | | extended |
868 View definition:
869 SELECT x1 AS x2,
870 x2 AS x0,
871 x3 AS x1
872 FROM tt1
873 ORDER BY tt1.x1;
875 -- Test aliasing of joins
876 create view view_of_joins as
877 select * from
878 (select * from (tbl1 cross join tbl2) same) ss,
879 (tbl3 cross join tbl4) same;
880 \d+ view_of_joins
881 View "testviewschm2.view_of_joins"
882 Column | Type | Collation | Nullable | Default | Storage | Description
883 --------+---------+-----------+----------+---------+---------+-------------
884 a | integer | | | | plain |
885 b | integer | | | | plain |
886 c | integer | | | | plain |
887 d | integer | | | | plain |
888 e | integer | | | | plain |
889 f | integer | | | | plain |
890 g | integer | | | | plain |
891 h | integer | | | | plain |
892 View definition:
893 SELECT ss.a,
894 ss.b,
895 ss.c,
896 ss.d,
897 same.e,
898 same.f,
899 same.g,
900 same.h
901 FROM ( SELECT same_1.a,
902 same_1.b,
903 same_1.c,
904 same_1.d
905 FROM (tbl1
906 CROSS JOIN tbl2) same_1) ss,
907 (tbl3
908 CROSS JOIN tbl4) same;
910 create table tbl1a (a int, c int);
911 create view view_of_joins_2a as select * from tbl1 join tbl1a using (a);
912 create view view_of_joins_2b as select * from tbl1 join tbl1a using (a) as x;
913 create view view_of_joins_2c as select * from (tbl1 join tbl1a using (a)) as y;
914 create view view_of_joins_2d as select * from (tbl1 join tbl1a using (a) as x) as y;
915 select pg_get_viewdef('view_of_joins_2a', true);
916 pg_get_viewdef
917 ----------------------------
918 SELECT tbl1.a, +
919 tbl1.b, +
920 tbl1a.c +
921 FROM tbl1 +
922 JOIN tbl1a USING (a);
923 (1 row)
925 select pg_get_viewdef('view_of_joins_2b', true);
926 pg_get_viewdef
927 ---------------------------------
928 SELECT tbl1.a, +
929 tbl1.b, +
930 tbl1a.c +
931 FROM tbl1 +
932 JOIN tbl1a USING (a) AS x;
933 (1 row)
935 select pg_get_viewdef('view_of_joins_2c', true);
936 pg_get_viewdef
937 -------------------------------
938 SELECT y.a, +
939 y.b, +
940 y.c +
941 FROM (tbl1 +
942 JOIN tbl1a USING (a)) y;
943 (1 row)
945 select pg_get_viewdef('view_of_joins_2d', true);
946 pg_get_viewdef
947 ------------------------------------
948 SELECT y.a, +
949 y.b, +
950 y.c +
951 FROM (tbl1 +
952 JOIN tbl1a USING (a) AS x) y;
953 (1 row)
955 -- Test view decompilation in the face of column addition/deletion/renaming
956 create table tt2 (a int, b int, c int);
957 create table tt3 (ax int8, b int2, c numeric);
958 create table tt4 (ay int, b int, q int);
959 create view v1 as select * from tt2 natural join tt3;
960 create view v1a as select * from (tt2 natural join tt3) j;
961 create view v2 as select * from tt2 join tt3 using (b,c) join tt4 using (b);
962 create view v2a as select * from (tt2 join tt3 using (b,c) join tt4 using (b)) j;
963 create view v3 as select * from tt2 join tt3 using (b,c) full join tt4 using (b);
964 select pg_get_viewdef('v1', true);
965 pg_get_viewdef
966 -----------------------------
967 SELECT tt2.b, +
968 tt3.c, +
969 tt2.a, +
970 tt3.ax +
971 FROM tt2 +
972 JOIN tt3 USING (b, c);
973 (1 row)
975 select pg_get_viewdef('v1a', true);
976 pg_get_viewdef
977 --------------------------------
978 SELECT j.b, +
979 j.c, +
980 j.a, +
981 j.ax +
982 FROM (tt2 +
983 JOIN tt3 USING (b, c)) j;
984 (1 row)
986 select pg_get_viewdef('v2', true);
987 pg_get_viewdef
988 ----------------------------
989 SELECT tt2.b, +
990 tt3.c, +
991 tt2.a, +
992 tt3.ax, +
993 tt4.ay, +
994 tt4.q +
995 FROM tt2 +
996 JOIN tt3 USING (b, c)+
997 JOIN tt4 USING (b);
998 (1 row)
1000 select pg_get_viewdef('v2a', true);
1001 pg_get_viewdef
1002 -----------------------------
1003 SELECT j.b, +
1004 j.c, +
1005 j.a, +
1006 j.ax, +
1007 j.ay, +
1008 j.q +
1009 FROM (tt2 +
1010 JOIN tt3 USING (b, c) +
1011 JOIN tt4 USING (b)) j;
1012 (1 row)
1014 select pg_get_viewdef('v3', true);
1015 pg_get_viewdef
1016 -------------------------------
1017 SELECT b, +
1018 tt3.c, +
1019 tt2.a, +
1020 tt3.ax, +
1021 tt4.ay, +
1022 tt4.q +
1023 FROM tt2 +
1024 JOIN tt3 USING (b, c) +
1025 FULL JOIN tt4 USING (b);
1026 (1 row)
1028 alter table tt2 add column d int;
1029 alter table tt2 add column e int;
1030 select pg_get_viewdef('v1', true);
1031 pg_get_viewdef
1032 -----------------------------
1033 SELECT tt2.b, +
1034 tt3.c, +
1035 tt2.a, +
1036 tt3.ax +
1037 FROM tt2 +
1038 JOIN tt3 USING (b, c);
1039 (1 row)
1041 select pg_get_viewdef('v1a', true);
1042 pg_get_viewdef
1043 --------------------------------
1044 SELECT j.b, +
1045 j.c, +
1046 j.a, +
1047 j.ax +
1048 FROM (tt2 +
1049 JOIN tt3 USING (b, c)) j;
1050 (1 row)
1052 select pg_get_viewdef('v2', true);
1053 pg_get_viewdef
1054 ----------------------------
1055 SELECT tt2.b, +
1056 tt3.c, +
1057 tt2.a, +
1058 tt3.ax, +
1059 tt4.ay, +
1060 tt4.q +
1061 FROM tt2 +
1062 JOIN tt3 USING (b, c)+
1063 JOIN tt4 USING (b);
1064 (1 row)
1066 select pg_get_viewdef('v2a', true);
1067 pg_get_viewdef
1068 -----------------------------
1069 SELECT j.b, +
1070 j.c, +
1071 j.a, +
1072 j.ax, +
1073 j.ay, +
1074 j.q +
1075 FROM (tt2 +
1076 JOIN tt3 USING (b, c) +
1077 JOIN tt4 USING (b)) j;
1078 (1 row)
1080 select pg_get_viewdef('v3', true);
1081 pg_get_viewdef
1082 -------------------------------
1083 SELECT b, +
1084 tt3.c, +
1085 tt2.a, +
1086 tt3.ax, +
1087 tt4.ay, +
1088 tt4.q +
1089 FROM tt2 +
1090 JOIN tt3 USING (b, c) +
1091 FULL JOIN tt4 USING (b);
1092 (1 row)
1094 alter table tt3 rename c to d;
1095 select pg_get_viewdef('v1', true);
1096 pg_get_viewdef
1097 -------------------------------------------
1098 SELECT tt2.b, +
1099 tt3.c, +
1100 tt2.a, +
1101 tt3.ax +
1102 FROM tt2 +
1103 JOIN tt3 tt3(ax, b, c) USING (b, c);
1104 (1 row)
1106 select pg_get_viewdef('v1a', true);
1107 pg_get_viewdef
1108 ----------------------------------------------
1109 SELECT j.b, +
1110 j.c, +
1111 j.a, +
1112 j.ax +
1113 FROM (tt2 +
1114 JOIN tt3 tt3(ax, b, c) USING (b, c)) j;
1115 (1 row)
1117 select pg_get_viewdef('v2', true);
1118 pg_get_viewdef
1119 ------------------------------------------
1120 SELECT tt2.b, +
1121 tt3.c, +
1122 tt2.a, +
1123 tt3.ax, +
1124 tt4.ay, +
1125 tt4.q +
1126 FROM tt2 +
1127 JOIN tt3 tt3(ax, b, c) USING (b, c)+
1128 JOIN tt4 USING (b);
1129 (1 row)
1131 select pg_get_viewdef('v2a', true);
1132 pg_get_viewdef
1133 ------------------------------------------
1134 SELECT j.b, +
1135 j.c, +
1136 j.a, +
1137 j.ax, +
1138 j.ay, +
1139 j.q +
1140 FROM (tt2 +
1141 JOIN tt3 tt3(ax, b, c) USING (b, c)+
1142 JOIN tt4 USING (b)) j;
1143 (1 row)
1145 select pg_get_viewdef('v3', true);
1146 pg_get_viewdef
1147 ------------------------------------------
1148 SELECT b, +
1149 tt3.c, +
1150 tt2.a, +
1151 tt3.ax, +
1152 tt4.ay, +
1153 tt4.q +
1154 FROM tt2 +
1155 JOIN tt3 tt3(ax, b, c) USING (b, c)+
1156 FULL JOIN tt4 USING (b);
1157 (1 row)
1159 alter table tt3 add column c int;
1160 alter table tt3 add column e int;
1161 select pg_get_viewdef('v1', true);
1162 pg_get_viewdef
1163 ---------------------------------------------------
1164 SELECT tt2.b, +
1165 tt3.c, +
1166 tt2.a, +
1167 tt3.ax +
1168 FROM tt2 +
1169 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c);
1170 (1 row)
1172 select pg_get_viewdef('v1a', true);
1173 pg_get_viewdef
1174 -----------------------------------------------------------------------------------
1175 SELECT j.b, +
1176 j.c, +
1177 j.a, +
1178 j.ax +
1179 FROM (tt2 +
1180 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)) j(b, c, a, d, e, ax, c_1, e_1);
1181 (1 row)
1183 select pg_get_viewdef('v2', true);
1184 pg_get_viewdef
1185 --------------------------------------------------
1186 SELECT tt2.b, +
1187 tt3.c, +
1188 tt2.a, +
1189 tt3.ax, +
1190 tt4.ay, +
1191 tt4.q +
1192 FROM tt2 +
1193 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
1194 JOIN tt4 USING (b);
1195 (1 row)
1197 select pg_get_viewdef('v2a', true);
1198 pg_get_viewdef
1199 -----------------------------------------------------------------
1200 SELECT j.b, +
1201 j.c, +
1202 j.a, +
1203 j.ax, +
1204 j.ay, +
1205 j.q +
1206 FROM (tt2 +
1207 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c) +
1208 JOIN tt4 USING (b)) j(b, c, a, d, e, ax, c_1, e_1, ay, q);
1209 (1 row)
1211 select pg_get_viewdef('v3', true);
1212 pg_get_viewdef
1213 --------------------------------------------------
1214 SELECT b, +
1215 tt3.c, +
1216 tt2.a, +
1217 tt3.ax, +
1218 tt4.ay, +
1219 tt4.q +
1220 FROM tt2 +
1221 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
1222 FULL JOIN tt4 USING (b);
1223 (1 row)
1225 alter table tt2 drop column d;
1226 select pg_get_viewdef('v1', true);
1227 pg_get_viewdef
1228 ---------------------------------------------------
1229 SELECT tt2.b, +
1230 tt3.c, +
1231 tt2.a, +
1232 tt3.ax +
1233 FROM tt2 +
1234 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c);
1235 (1 row)
1237 select pg_get_viewdef('v1a', true);
1238 pg_get_viewdef
1239 --------------------------------------------------------------------------------
1240 SELECT j.b, +
1241 j.c, +
1242 j.a, +
1243 j.ax +
1244 FROM (tt2 +
1245 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)) j(b, c, a, e, ax, c_1, e_1);
1246 (1 row)
1248 select pg_get_viewdef('v2', true);
1249 pg_get_viewdef
1250 --------------------------------------------------
1251 SELECT tt2.b, +
1252 tt3.c, +
1253 tt2.a, +
1254 tt3.ax, +
1255 tt4.ay, +
1256 tt4.q +
1257 FROM tt2 +
1258 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
1259 JOIN tt4 USING (b);
1260 (1 row)
1262 select pg_get_viewdef('v2a', true);
1263 pg_get_viewdef
1264 --------------------------------------------------------------
1265 SELECT j.b, +
1266 j.c, +
1267 j.a, +
1268 j.ax, +
1269 j.ay, +
1270 j.q +
1271 FROM (tt2 +
1272 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c) +
1273 JOIN tt4 USING (b)) j(b, c, a, e, ax, c_1, e_1, ay, q);
1274 (1 row)
1276 select pg_get_viewdef('v3', true);
1277 pg_get_viewdef
1278 --------------------------------------------------
1279 SELECT b, +
1280 tt3.c, +
1281 tt2.a, +
1282 tt3.ax, +
1283 tt4.ay, +
1284 tt4.q +
1285 FROM tt2 +
1286 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
1287 FULL JOIN tt4 USING (b);
1288 (1 row)
1290 create table tt5 (a int, b int);
1291 create table tt6 (c int, d int);
1292 create view vv1 as select * from (tt5 cross join tt6) j(aa,bb,cc,dd);
1293 select pg_get_viewdef('vv1', true);
1294 pg_get_viewdef
1295 -----------------------------------------
1296 SELECT j.aa, +
1297 j.bb, +
1298 j.cc, +
1299 j.dd +
1300 FROM (tt5 +
1301 CROSS JOIN tt6) j(aa, bb, cc, dd);
1302 (1 row)
1304 alter table tt5 add column c int;
1305 select pg_get_viewdef('vv1', true);
1306 pg_get_viewdef
1307 --------------------------------------------
1308 SELECT j.aa, +
1309 j.bb, +
1310 j.cc, +
1311 j.dd +
1312 FROM (tt5 +
1313 CROSS JOIN tt6) j(aa, bb, c, cc, dd);
1314 (1 row)
1316 alter table tt5 add column cc int;
1317 select pg_get_viewdef('vv1', true);
1318 pg_get_viewdef
1319 --------------------------------------------------
1320 SELECT j.aa, +
1321 j.bb, +
1322 j.cc, +
1323 j.dd +
1324 FROM (tt5 +
1325 CROSS JOIN tt6) j(aa, bb, c, cc_1, cc, dd);
1326 (1 row)
1328 alter table tt5 drop column c;
1329 select pg_get_viewdef('vv1', true);
1330 pg_get_viewdef
1331 -----------------------------------------------
1332 SELECT j.aa, +
1333 j.bb, +
1334 j.cc, +
1335 j.dd +
1336 FROM (tt5 +
1337 CROSS JOIN tt6) j(aa, bb, cc_1, cc, dd);
1338 (1 row)
1340 create view v4 as select * from v1;
1341 alter view v1 rename column a to x;
1342 select pg_get_viewdef('v1', true);
1343 pg_get_viewdef
1344 ---------------------------------------------------
1345 SELECT tt2.b, +
1346 tt3.c, +
1347 tt2.a AS x, +
1348 tt3.ax +
1349 FROM tt2 +
1350 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c);
1351 (1 row)
1353 select pg_get_viewdef('v4', true);
1354 pg_get_viewdef
1355 ----------------
1356 SELECT b, +
1357 c, +
1358 x AS a, +
1359 ax +
1360 FROM v1;
1361 (1 row)
1363 -- Unnamed FULL JOIN USING is lots of fun too
1364 create table tt7 (x int, xx int, y int);
1365 alter table tt7 drop column xx;
1366 create table tt8 (x int, z int);
1367 create view vv2 as
1368 select * from (values(1,2,3,4,5)) v(a,b,c,d,e)
1369 union all
1370 select * from tt7 full join tt8 using (x), tt8 tt8x;
1371 select pg_get_viewdef('vv2', true);
1372 pg_get_viewdef
1373 ------------------------------------------------
1374 SELECT v.a, +
1375 v.b, +
1376 v.c, +
1377 v.d, +
1378 v.e +
1379 FROM ( VALUES (1,2,3,4,5)) v(a, b, c, d, e)+
1380 UNION ALL +
1381 SELECT x AS a, +
1382 tt7.y AS b, +
1383 tt8.z AS c, +
1384 tt8x.x_1 AS d, +
1385 tt8x.z AS e +
1386 FROM tt7 +
1387 FULL JOIN tt8 USING (x), +
1388 tt8 tt8x(x_1, z);
1389 (1 row)
1391 create view vv3 as
1392 select * from (values(1,2,3,4,5,6)) v(a,b,c,x,e,f)
1393 union all
1394 select * from
1395 tt7 full join tt8 using (x),
1396 tt7 tt7x full join tt8 tt8x using (x);
1397 select pg_get_viewdef('vv3', true);
1398 pg_get_viewdef
1399 -----------------------------------------------------
1400 SELECT v.a, +
1401 v.b, +
1402 v.c, +
1403 v.x, +
1404 v.e, +
1405 v.f +
1406 FROM ( VALUES (1,2,3,4,5,6)) v(a, b, c, x, e, f)+
1407 UNION ALL +
1408 SELECT x AS a, +
1409 tt7.y AS b, +
1410 tt8.z AS c, +
1411 x_1 AS x, +
1412 tt7x.y AS e, +
1413 tt8x.z AS f +
1414 FROM tt7 +
1415 FULL JOIN tt8 USING (x), +
1416 tt7 tt7x(x_1, y) +
1417 FULL JOIN tt8 tt8x(x_1, z) USING (x_1);
1418 (1 row)
1420 create view vv4 as
1421 select * from (values(1,2,3,4,5,6,7)) v(a,b,c,x,e,f,g)
1422 union all
1423 select * from
1424 tt7 full join tt8 using (x),
1425 tt7 tt7x full join tt8 tt8x using (x) full join tt8 tt8y using (x);
1426 select pg_get_viewdef('vv4', true);
1427 pg_get_viewdef
1428 ----------------------------------------------------------
1429 SELECT v.a, +
1430 v.b, +
1431 v.c, +
1432 v.x, +
1433 v.e, +
1434 v.f, +
1435 v.g +
1436 FROM ( VALUES (1,2,3,4,5,6,7)) v(a, b, c, x, e, f, g)+
1437 UNION ALL +
1438 SELECT x AS a, +
1439 tt7.y AS b, +
1440 tt8.z AS c, +
1441 x_1 AS x, +
1442 tt7x.y AS e, +
1443 tt8x.z AS f, +
1444 tt8y.z AS g +
1445 FROM tt7 +
1446 FULL JOIN tt8 USING (x), +
1447 tt7 tt7x(x_1, y) +
1448 FULL JOIN tt8 tt8x(x_1, z) USING (x_1) +
1449 FULL JOIN tt8 tt8y(x_1, z) USING (x_1);
1450 (1 row)
1452 alter table tt7 add column zz int;
1453 alter table tt7 add column z int;
1454 alter table tt7 drop column zz;
1455 alter table tt8 add column z2 int;
1456 select pg_get_viewdef('vv2', true);
1457 pg_get_viewdef
1458 ------------------------------------------------
1459 SELECT v.a, +
1460 v.b, +
1461 v.c, +
1462 v.d, +
1463 v.e +
1464 FROM ( VALUES (1,2,3,4,5)) v(a, b, c, d, e)+
1465 UNION ALL +
1466 SELECT x AS a, +
1467 tt7.y AS b, +
1468 tt8.z AS c, +
1469 tt8x.x_1 AS d, +
1470 tt8x.z AS e +
1471 FROM tt7 +
1472 FULL JOIN tt8 USING (x), +
1473 tt8 tt8x(x_1, z, z2);
1474 (1 row)
1476 select pg_get_viewdef('vv3', true);
1477 pg_get_viewdef
1478 -----------------------------------------------------
1479 SELECT v.a, +
1480 v.b, +
1481 v.c, +
1482 v.x, +
1483 v.e, +
1484 v.f +
1485 FROM ( VALUES (1,2,3,4,5,6)) v(a, b, c, x, e, f)+
1486 UNION ALL +
1487 SELECT x AS a, +
1488 tt7.y AS b, +
1489 tt8.z AS c, +
1490 x_1 AS x, +
1491 tt7x.y AS e, +
1492 tt8x.z AS f +
1493 FROM tt7 +
1494 FULL JOIN tt8 USING (x), +
1495 tt7 tt7x(x_1, y, z) +
1496 FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1);
1497 (1 row)
1499 select pg_get_viewdef('vv4', true);
1500 pg_get_viewdef
1501 ----------------------------------------------------------
1502 SELECT v.a, +
1503 v.b, +
1504 v.c, +
1505 v.x, +
1506 v.e, +
1507 v.f, +
1508 v.g +
1509 FROM ( VALUES (1,2,3,4,5,6,7)) v(a, b, c, x, e, f, g)+
1510 UNION ALL +
1511 SELECT x AS a, +
1512 tt7.y AS b, +
1513 tt8.z AS c, +
1514 x_1 AS x, +
1515 tt7x.y AS e, +
1516 tt8x.z AS f, +
1517 tt8y.z AS g +
1518 FROM tt7 +
1519 FULL JOIN tt8 USING (x), +
1520 tt7 tt7x(x_1, y, z) +
1521 FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1) +
1522 FULL JOIN tt8 tt8y(x_1, z, z2) USING (x_1);
1523 (1 row)
1525 -- Implicit coercions in a JOIN USING create issues similar to FULL JOIN
1526 create table tt7a (x date, xx int, y int);
1527 alter table tt7a drop column xx;
1528 create table tt8a (x timestamptz, z int);
1529 create view vv2a as
1530 select * from (values(now(),2,3,now(),5)) v(a,b,c,d,e)
1531 union all
1532 select * from tt7a left join tt8a using (x), tt8a tt8ax;
1533 select pg_get_viewdef('vv2a', true);
1534 pg_get_viewdef
1535 --------------------------------------------------------
1536 SELECT v.a, +
1537 v.b, +
1538 v.c, +
1539 v.d, +
1540 v.e +
1541 FROM ( VALUES (now(),2,3,now(),5)) v(a, b, c, d, e)+
1542 UNION ALL +
1543 SELECT x AS a, +
1544 tt7a.y AS b, +
1545 tt8a.z AS c, +
1546 tt8ax.x_1 AS d, +
1547 tt8ax.z AS e +
1548 FROM tt7a +
1549 LEFT JOIN tt8a USING (x), +
1550 tt8a tt8ax(x_1, z);
1551 (1 row)
1553 --
1554 -- Also check dropping a column that existed when the view was made
1555 --
1556 create table tt9 (x int, xx int, y int);
1557 create table tt10 (x int, z int);
1558 create view vv5 as select x,y,z from tt9 join tt10 using(x);
1559 select pg_get_viewdef('vv5', true);
1560 pg_get_viewdef
1561 ---------------------------
1562 SELECT tt9.x, +
1563 tt9.y, +
1564 tt10.z +
1565 FROM tt9 +
1566 JOIN tt10 USING (x);
1567 (1 row)
1569 alter table tt9 drop column xx;
1570 select pg_get_viewdef('vv5', true);
1571 pg_get_viewdef
1572 ---------------------------
1573 SELECT tt9.x, +
1574 tt9.y, +
1575 tt10.z +
1576 FROM tt9 +
1577 JOIN tt10 USING (x);
1578 (1 row)
1580 --
1581 -- Another corner case is that we might add a column to a table below a
1582 -- JOIN USING, and thereby make the USING column name ambiguous
1583 --
1584 create table tt11 (x int, y int);
1585 create table tt12 (x int, z int);
1586 create table tt13 (z int, q int);
1587 create view vv6 as select x,y,z,q from
1588 (tt11 join tt12 using(x)) join tt13 using(z);
1589 select pg_get_viewdef('vv6', true);
1590 pg_get_viewdef
1591 ---------------------------
1592 SELECT tt11.x, +
1593 tt11.y, +
1594 tt12.z, +
1595 tt13.q +
1596 FROM tt11 +
1597 JOIN tt12 USING (x) +
1598 JOIN tt13 USING (z);
1599 (1 row)
1601 alter table tt11 add column z int;
1602 select pg_get_viewdef('vv6', true);
1603 pg_get_viewdef
1604 ------------------------------
1605 SELECT tt11.x, +
1606 tt11.y, +
1607 tt12.z, +
1608 tt13.q +
1609 FROM tt11 tt11(x, y, z_1)+
1610 JOIN tt12 USING (x) +
1611 JOIN tt13 USING (z);
1612 (1 row)
1614 --
1615 -- Check cases involving dropped/altered columns in a function's rowtype result
1616 --
1617 create table tt14t (f1 text, f2 text, f3 text, f4 text);
1618 insert into tt14t values('foo', 'bar', 'baz', '42');
1619 alter table tt14t drop column f2;
1620 create function tt14f() returns setof tt14t as
1621 $$
1622 declare
1623 rec1 record;
1624 begin
1625 for rec1 in select * from tt14t
1626 loop
1627 return next rec1;
1628 end loop;
1629 end;
1630 $$
1631 language plpgsql;
1632 create view tt14v as select t.* from tt14f() t;
1633 select pg_get_viewdef('tt14v', true);
1634 pg_get_viewdef
1635 --------------------------------
1636 SELECT f1, +
1637 f3, +
1638 f4 +
1639 FROM tt14f() t(f1, f3, f4);
1640 (1 row)
1642 select * from tt14v;
1643 f1 | f3 | f4
1644 -----+-----+----
1645 foo | baz | 42
1646 (1 row)
1648 alter table tt14t drop column f3; -- fail, view has explicit reference to f3
1649 ERROR: cannot drop column f3 of table tt14t because other objects depend on it
1650 DETAIL: view tt14v depends on column f3 of table tt14t
1651 HINT: Use DROP ... CASCADE to drop the dependent objects too.
1652 -- We used to have a bug that would allow the above to succeed, posing
1653 -- hazards for later execution of the view. Check that the internal
1654 -- defenses for those hazards haven't bit-rotted, in case some other
1655 -- bug with similar symptoms emerges.
1656 begin;
1657 -- destroy the dependency entry that prevents the DROP:
1658 delete from pg_depend where
1659 objid = (select oid from pg_rewrite
1660 where ev_class = 'tt14v'::regclass and rulename = '_RETURN')
1661 and refobjsubid = 3
1662 returning pg_describe_object(classid, objid, objsubid) as obj,
1663 pg_describe_object(refclassid, refobjid, refobjsubid) as ref,
1664 deptype;
1665 obj | ref | deptype
1666 ----------------------------+--------------------------+---------
1667 rule _RETURN on view tt14v | column f3 of table tt14t | n
1668 (1 row)
1670 -- this will now succeed:
1671 alter table tt14t drop column f3;
1672 -- column f3 is still in the view, sort of ...
1673 select pg_get_viewdef('tt14v', true);
1674 pg_get_viewdef
1675 -------------------------------
1676 SELECT f1, +
1677 "?dropped?column?" AS f3,+
1678 f4 +
1679 FROM tt14f() t(f1, f4);
1680 (1 row)
1682 -- ... and you can even EXPLAIN it ...
1683 explain (verbose, costs off) select * from tt14v;
1684 QUERY PLAN
1685 ----------------------------------------
1686 Function Scan on testviewschm2.tt14f t
1687 Output: t.f1, t.f3, t.f4
1688 Function Call: tt14f()
1689 (3 rows)
1691 -- but it will fail at execution
1692 select f1, f4 from tt14v;
1693 f1 | f4
1694 -----+----
1695 foo | 42
1696 (1 row)
1698 select * from tt14v;
1699 ERROR: attribute 3 of type record has been dropped
1700 rollback;
1701 -- likewise, altering a referenced column's type is prohibited ...
1702 alter table tt14t alter column f4 type integer using f4::integer; -- fail
1703 ERROR: cannot alter type of a column used by a view or rule
1704 DETAIL: rule _RETURN on view tt14v depends on column "f4"
1705 -- ... but some bug might let it happen, so check defenses
1706 begin;
1707 -- destroy the dependency entry that prevents the ALTER:
1708 delete from pg_depend where
1709 objid = (select oid from pg_rewrite
1710 where ev_class = 'tt14v'::regclass and rulename = '_RETURN')
1711 and refobjsubid = 4
1712 returning pg_describe_object(classid, objid, objsubid) as obj,
1713 pg_describe_object(refclassid, refobjid, refobjsubid) as ref,
1714 deptype;
1715 obj | ref | deptype
1716 ----------------------------+--------------------------+---------
1717 rule _RETURN on view tt14v | column f4 of table tt14t | n
1718 (1 row)
1720 -- this will now succeed:
1721 alter table tt14t alter column f4 type integer using f4::integer;
1722 -- f4 is still in the view ...
1723 select pg_get_viewdef('tt14v', true);
1724 pg_get_viewdef
1725 --------------------------------
1726 SELECT f1, +
1727 f3, +
1728 f4 +
1729 FROM tt14f() t(f1, f3, f4);
1730 (1 row)
1732 -- but will fail at execution
1733 select f1, f3 from tt14v;
1734 f1 | f3
1735 -----+-----
1736 foo | baz
1737 (1 row)
1739 select * from tt14v;
1740 ERROR: attribute 4 of type record has wrong type
1741 DETAIL: Table has type integer, but query expects text.
1742 rollback;
1743 drop view tt14v;
1744 create view tt14v as select t.f1, t.f4 from tt14f() t;
1745 select pg_get_viewdef('tt14v', true);
1746 pg_get_viewdef
1747 --------------------------------
1748 SELECT f1, +
1749 f4 +
1750 FROM tt14f() t(f1, f3, f4);
1751 (1 row)
1753 select * from tt14v;
1754 f1 | f4
1755 -----+----
1756 foo | 42
1757 (1 row)
1759 alter table tt14t drop column f3; -- ok
1760 select pg_get_viewdef('tt14v', true);
1761 pg_get_viewdef
1762 ----------------------------
1763 SELECT f1, +
1764 f4 +
1765 FROM tt14f() t(f1, f4);
1766 (1 row)
1768 explain (verbose, costs off) select * from tt14v;
1769 QUERY PLAN
1770 ----------------------------------------
1771 Function Scan on testviewschm2.tt14f t
1772 Output: t.f1, t.f4
1773 Function Call: tt14f()
1774 (3 rows)
1776 select * from tt14v;
1777 f1 | f4
1778 -----+----
1779 foo | 42
1780 (1 row)
1782 -- check display of whole-row variables in some corner cases
1783 create type nestedcomposite as (x int8_tbl);
1784 create view tt15v as select row(i)::nestedcomposite from int8_tbl i;
1785 select * from tt15v;
1786 row
1787 ------------------------------------------
1788 ("(123,456)")
1789 ("(123,4567890123456789)")
1790 ("(4567890123456789,123)")
1791 ("(4567890123456789,4567890123456789)")
1792 ("(4567890123456789,-4567890123456789)")
1793 (5 rows)
1795 select pg_get_viewdef('tt15v', true);
1796 pg_get_viewdef
1797 ------------------------------------------------------
1798 SELECT ROW(i.*::int8_tbl)::nestedcomposite AS "row"+
1799 FROM int8_tbl i;
1800 (1 row)
1802 select row(i.*::int8_tbl)::nestedcomposite from int8_tbl i;
1803 row
1804 ------------------------------------------
1805 ("(123,456)")
1806 ("(123,4567890123456789)")
1807 ("(4567890123456789,123)")
1808 ("(4567890123456789,4567890123456789)")
1809 ("(4567890123456789,-4567890123456789)")
1810 (5 rows)
1812 create view tt16v as select * from int8_tbl i, lateral(values(i)) ss;
1813 select * from tt16v;
1814 q1 | q2 | column1
1815 ------------------+-------------------+--------------------------------------
1816 123 | 456 | (123,456)
1817 123 | 4567890123456789 | (123,4567890123456789)
1818 4567890123456789 | 123 | (4567890123456789,123)
1819 4567890123456789 | 4567890123456789 | (4567890123456789,4567890123456789)
1820 4567890123456789 | -4567890123456789 | (4567890123456789,-4567890123456789)
1821 (5 rows)
1823 select pg_get_viewdef('tt16v', true);
1824 pg_get_viewdef
1825 -------------------------------------------
1826 SELECT i.q1, +
1827 i.q2, +
1828 ss.column1 +
1829 FROM int8_tbl i, +
1830 LATERAL ( VALUES (i.*::int8_tbl)) ss;
1831 (1 row)
1833 select * from int8_tbl i, lateral(values(i.*::int8_tbl)) ss;
1834 q1 | q2 | column1
1835 ------------------+-------------------+--------------------------------------
1836 123 | 456 | (123,456)
1837 123 | 4567890123456789 | (123,4567890123456789)
1838 4567890123456789 | 123 | (4567890123456789,123)
1839 4567890123456789 | 4567890123456789 | (4567890123456789,4567890123456789)
1840 4567890123456789 | -4567890123456789 | (4567890123456789,-4567890123456789)
1841 (5 rows)
1843 create view tt17v as select * from int8_tbl i where i in (values(i));
1844 select * from tt17v;
1845 q1 | q2
1846 ------------------+-------------------
1847 123 | 456
1848 123 | 4567890123456789
1849 4567890123456789 | 123
1850 4567890123456789 | 4567890123456789
1851 4567890123456789 | -4567890123456789
1852 (5 rows)
1854 select pg_get_viewdef('tt17v', true);
1855 pg_get_viewdef
1856 ---------------------------------------------
1857 SELECT q1, +
1858 q2 +
1859 FROM int8_tbl i +
1860 WHERE (i.* IN ( VALUES (i.*::int8_tbl)));
1861 (1 row)
1863 select * from int8_tbl i where i.* in (values(i.*::int8_tbl));
1864 q1 | q2
1865 ------------------+-------------------
1866 123 | 456
1867 123 | 4567890123456789
1868 4567890123456789 | 123
1869 4567890123456789 | 4567890123456789
1870 4567890123456789 | -4567890123456789
1871 (5 rows)
1873 create table tt15v_log(o tt15v, n tt15v, incr bool);
1874 create rule updlog as on update to tt15v do also
1875 insert into tt15v_log values(old, new, row(old,old) < row(new,new));
1876 \d+ tt15v
1877 View "testviewschm2.tt15v"
1878 Column | Type | Collation | Nullable | Default | Storage | Description
1879 --------+-----------------+-----------+----------+---------+----------+-------------
1880 row | nestedcomposite | | | | extended |
1881 View definition:
1882 SELECT ROW(i.*::int8_tbl)::nestedcomposite AS "row"
1883 FROM int8_tbl i;
1884 Rules:
1885 updlog AS
1886 ON UPDATE TO tt15v DO INSERT INTO tt15v_log (o, n, incr)
1887 VALUES (old.*::tt15v, new.*::tt15v, (ROW(old.*::tt15v, old.*::tt15v) < ROW(new.*::tt15v, new.*::tt15v)))
1889 -- check unique-ification of overlength names
1890 create view tt18v as
1891 select * from int8_tbl xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxy
1892 union all
1893 select * from int8_tbl xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxz;
1894 NOTICE: identifier "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxy" will be truncated to "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
1895 NOTICE: identifier "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxz" will be truncated to "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
1896 select pg_get_viewdef('tt18v', true);
1897 pg_get_viewdef
1898 -----------------------------------------------------------------------------------
1899 SELECT xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.q1, +
1900 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.q2 +
1901 FROM int8_tbl xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx +
1902 UNION ALL +
1903 SELECT xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.q1, +
1904 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.q2 +
1905 FROM int8_tbl xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx;
1906 (1 row)
1908 explain (costs off) select * from tt18v;
1909 QUERY PLAN
1910 --------------------------------------------------------------------------------------------
1911 Append
1912 -> Seq Scan on int8_tbl xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
1913 -> Seq Scan on int8_tbl xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx_1
1914 (3 rows)
1916 -- check display of ScalarArrayOp with a sub-select
1917 select 'foo'::text = any(array['abc','def','foo']::text[]);
1918 ?column?
1919 ----------
1920 t
1921 (1 row)
1923 select 'foo'::text = any((select array['abc','def','foo']::text[])); -- fail
1924 ERROR: operator does not exist: text = text[]
1925 LINE 1: select 'foo'::text = any((select array['abc','def','foo']::t...
1926 ^
1927 HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
1928 select 'foo'::text = any((select array['abc','def','foo']::text[])::text[]);
1929 ?column?
1930 ----------
1931 t
1932 (1 row)
1934 create view tt19v as
1935 select 'foo'::text = any(array['abc','def','foo']::text[]) c1,
1936 'foo'::text = any((select array['abc','def','foo']::text[])::text[]) c2;
1937 select pg_get_viewdef('tt19v', true);
1938 pg_get_viewdef
1939 ------------------------------------------------------------------------------------------------------------
1940 SELECT 'foo'::text = ANY (ARRAY['abc'::text, 'def'::text, 'foo'::text]) AS c1, +
1941 'foo'::text = ANY ((( SELECT ARRAY['abc'::text, 'def'::text, 'foo'::text] AS "array"))::text[]) AS c2;
1942 (1 row)
1944 -- check display of assorted RTE_FUNCTION expressions
1945 create view tt20v as
1946 select * from
1947 coalesce(1,2) as c,
1948 collation for ('x'::text) col,
1949 current_date as d,
1950 localtimestamp(3) as t,
1951 cast(1+2 as int4) as i4,
1952 cast(1+2 as int8) as i8;
1953 select pg_get_viewdef('tt20v', true);
1954 pg_get_viewdef
1955 ---------------------------------------------
1956 SELECT c.c, +
1957 col.col, +
1958 d.d, +
1959 t.t, +
1960 i4.i4, +
1961 i8.i8 +
1962 FROM COALESCE(1, 2) c(c), +
1963 COLLATION FOR ('x'::text) col(col), +
1964 CURRENT_DATE d(d), +
1965 LOCALTIMESTAMP(3) t(t), +
1966 CAST(1 + 2 AS integer) i4(i4), +
1967 CAST((1 + 2)::bigint AS bigint) i8(i8);
1968 (1 row)
1970 -- reverse-listing of various special function syntaxes required by SQL
1971 create view tt201v as
1972 select
1973 ('2022-12-01'::date + '1 day'::interval) at time zone 'UTC' as atz,
1974 extract(day from now()) as extr,
1975 (now(), '1 day'::interval) overlaps
1976 (current_timestamp(2), '1 day'::interval) as o,
1977 'foo' is normalized isn,
1978 'foo' is nfkc normalized isnn,
1979 normalize('foo') as n,
1980 normalize('foo', nfkd) as nfkd,
1981 overlay('foo' placing 'bar' from 2) as ovl,
1982 overlay('foo' placing 'bar' from 2 for 3) as ovl2,
1983 position('foo' in 'foobar') as p,
1984 substring('foo' from 2 for 3) as s,
1985 substring('foo' similar 'f' escape '#') as ss,
1986 substring('foo' from 'oo') as ssf, -- historically-permitted abuse
1987 trim(' ' from ' foo ') as bt,
1988 trim(leading ' ' from ' foo ') as lt,
1989 trim(trailing ' foo ') as rt,
1990 trim(E'\\000'::bytea from E'\\000Tom\\000'::bytea) as btb,
1991 trim(leading E'\\000'::bytea from E'\\000Tom\\000'::bytea) as ltb,
1992 trim(trailing E'\\000'::bytea from E'\\000Tom\\000'::bytea) as rtb,
1993 CURRENT_DATE as cd,
1994 (select * from CURRENT_DATE) as cd2,
1995 CURRENT_TIME as ct,
1996 (select * from CURRENT_TIME) as ct2,
1997 CURRENT_TIME (1) as ct3,
1998 (select * from CURRENT_TIME (1)) as ct4,
1999 CURRENT_TIMESTAMP as ct5,
2000 (select * from CURRENT_TIMESTAMP) as ct6,
2001 CURRENT_TIMESTAMP (1) as ct7,
2002 (select * from CURRENT_TIMESTAMP (1)) as ct8,
2003 LOCALTIME as lt1,
2004 (select * from LOCALTIME) as lt2,
2005 LOCALTIME (1) as lt3,
2006 (select * from LOCALTIME (1)) as lt4,
2007 LOCALTIMESTAMP as lt5,
2008 (select * from LOCALTIMESTAMP) as lt6,
2009 LOCALTIMESTAMP (1) as lt7,
2010 (select * from LOCALTIMESTAMP (1)) as lt8,
2011 CURRENT_CATALOG as ca,
2012 (select * from CURRENT_CATALOG) as ca2,
2013 CURRENT_ROLE as cr,
2014 (select * from CURRENT_ROLE) as cr2,
2015 CURRENT_SCHEMA as cs,
2016 (select * from CURRENT_SCHEMA) as cs2,
2017 CURRENT_USER as cu,
2018 (select * from CURRENT_USER) as cu2,
2019 USER as us,
2020 (select * from USER) as us2,
2021 SESSION_USER seu,
2022 (select * from SESSION_USER) as seu2,
2023 SYSTEM_USER as su,
2024 (select * from SYSTEM_USER) as su2;
2025 select pg_get_viewdef('tt201v', true);
2026 pg_get_viewdef
2027 -----------------------------------------------------------------------------------------------
2028 SELECT (('12-01-2022'::date + '@ 1 day'::interval) AT TIME ZONE 'UTC'::text) AS atz, +
2029 EXTRACT(day FROM now()) AS extr, +
2030 ((now(), '@ 1 day'::interval) OVERLAPS (CURRENT_TIMESTAMP(2), '@ 1 day'::interval)) AS o,+
2031 ('foo'::text IS NORMALIZED) AS isn, +
2032 ('foo'::text IS NFKC NORMALIZED) AS isnn, +
2033 NORMALIZE('foo'::text) AS n, +
2034 NORMALIZE('foo'::text, NFKD) AS nfkd, +
2035 OVERLAY('foo'::text PLACING 'bar'::text FROM 2) AS ovl, +
2036 OVERLAY('foo'::text PLACING 'bar'::text FROM 2 FOR 3) AS ovl2, +
2037 POSITION(('foo'::text) IN ('foobar'::text)) AS p, +
2038 SUBSTRING('foo'::text FROM 2 FOR 3) AS s, +
2039 SUBSTRING('foo'::text SIMILAR 'f'::text ESCAPE '#'::text) AS ss, +
2040 "substring"('foo'::text, 'oo'::text) AS ssf, +
2041 TRIM(BOTH ' '::text FROM ' foo '::text) AS bt, +
2042 TRIM(LEADING ' '::text FROM ' foo '::text) AS lt, +
2043 TRIM(TRAILING FROM ' foo '::text) AS rt, +
2044 TRIM(BOTH '\x00'::bytea FROM '\x00546f6d00'::bytea) AS btb, +
2045 TRIM(LEADING '\x00'::bytea FROM '\x00546f6d00'::bytea) AS ltb, +
2046 TRIM(TRAILING '\x00'::bytea FROM '\x00546f6d00'::bytea) AS rtb, +
2047 CURRENT_DATE AS cd, +
2048 ( SELECT "current_date"."current_date" +
2049 FROM CURRENT_DATE "current_date"("current_date")) AS cd2, +
2050 CURRENT_TIME AS ct, +
2051 ( SELECT "current_time"."current_time" +
2052 FROM CURRENT_TIME "current_time"("current_time")) AS ct2, +
2053 CURRENT_TIME(1) AS ct3, +
2054 ( SELECT "current_time"."current_time" +
2055 FROM CURRENT_TIME(1) "current_time"("current_time")) AS ct4, +
2056 CURRENT_TIMESTAMP AS ct5, +
2057 ( SELECT "current_timestamp"."current_timestamp" +
2058 FROM CURRENT_TIMESTAMP "current_timestamp"("current_timestamp")) AS ct6, +
2059 CURRENT_TIMESTAMP(1) AS ct7, +
2060 ( SELECT "current_timestamp"."current_timestamp" +
2061 FROM CURRENT_TIMESTAMP(1) "current_timestamp"("current_timestamp")) AS ct8, +
2062 LOCALTIME AS lt1, +
2063 ( SELECT "localtime"."localtime" +
2064 FROM LOCALTIME "localtime"("localtime")) AS lt2, +
2065 LOCALTIME(1) AS lt3, +
2066 ( SELECT "localtime"."localtime" +
2067 FROM LOCALTIME(1) "localtime"("localtime")) AS lt4, +
2068 LOCALTIMESTAMP AS lt5, +
2069 ( SELECT "localtimestamp"."localtimestamp" +
2070 FROM LOCALTIMESTAMP "localtimestamp"("localtimestamp")) AS lt6, +
2071 LOCALTIMESTAMP(1) AS lt7, +
2072 ( SELECT "localtimestamp"."localtimestamp" +
2073 FROM LOCALTIMESTAMP(1) "localtimestamp"("localtimestamp")) AS lt8, +
2074 CURRENT_CATALOG AS ca, +
2075 ( SELECT "current_catalog"."current_catalog" +
2076 FROM CURRENT_CATALOG "current_catalog"("current_catalog")) AS ca2, +
2077 CURRENT_ROLE AS cr, +
2078 ( SELECT "current_role"."current_role" +
2079 FROM CURRENT_ROLE "current_role"("current_role")) AS cr2, +
2080 CURRENT_SCHEMA AS cs, +
2081 ( SELECT "current_schema"."current_schema" +
2082 FROM CURRENT_SCHEMA "current_schema"("current_schema")) AS cs2, +
2083 CURRENT_USER AS cu, +
2084 ( SELECT "current_user"."current_user" +
2085 FROM CURRENT_USER "current_user"("current_user")) AS cu2, +
2086 USER AS us, +
2087 ( SELECT "user"."user" +
2088 FROM USER "user"("user")) AS us2, +
2089 SESSION_USER AS seu, +
2090 ( SELECT "session_user"."session_user" +
2091 FROM SESSION_USER "session_user"("session_user")) AS seu2, +
2092 SYSTEM_USER AS su, +
2093 ( SELECT "system_user"."system_user" +
2094 FROM SYSTEM_USER "system_user"("system_user")) AS su2;
2095 (1 row)
2097 -- corner cases with empty join conditions
2098 create view tt21v as
2099 select * from tt5 natural inner join tt6;
2100 select pg_get_viewdef('tt21v', true);
2101 pg_get_viewdef
2102 ----------------------
2103 SELECT tt5.a, +
2104 tt5.b, +
2105 tt5.cc, +
2106 tt6.c, +
2107 tt6.d +
2108 FROM tt5 +
2109 CROSS JOIN tt6;
2110 (1 row)
2112 create view tt22v as
2113 select * from tt5 natural left join tt6;
2114 select pg_get_viewdef('tt22v', true);
2115 pg_get_viewdef
2116 -----------------------------
2117 SELECT tt5.a, +
2118 tt5.b, +
2119 tt5.cc, +
2120 tt6.c, +
2121 tt6.d +
2122 FROM tt5 +
2123 LEFT JOIN tt6 ON TRUE;
2124 (1 row)
2126 -- check handling of views with immediately-renamed columns
2127 create view tt23v (col_a, col_b) as
2128 select q1 as other_name1, q2 as other_name2 from int8_tbl
2129 union
2130 select 42, 43;
2131 select pg_get_viewdef('tt23v', true);
2132 pg_get_viewdef
2133 -------------------------------
2134 SELECT int8_tbl.q1 AS col_a,+
2135 int8_tbl.q2 AS col_b +
2136 FROM int8_tbl +
2137 UNION +
2138 SELECT 42 AS col_a, +
2139 43 AS col_b;
2140 (1 row)
2142 select pg_get_ruledef(oid, true) from pg_rewrite
2143 where ev_class = 'tt23v'::regclass and ev_type = '1';
2144 pg_get_ruledef
2145 -----------------------------------------------------------------
2146 CREATE RULE "_RETURN" AS +
2147 ON SELECT TO tt23v DO INSTEAD SELECT int8_tbl.q1 AS col_a,+
2148 int8_tbl.q2 AS col_b +
2149 FROM int8_tbl +
2150 UNION +
2151 SELECT 42 AS col_a, +
2152 43 AS col_b;
2153 (1 row)
2155 -- test extraction of FieldSelect field names (get_name_for_var_field)
2156 create view tt24v as
2157 with cte as materialized (select r from (values(1,2),(3,4)) r)
2158 select (r).column2 as col_a, (rr).column2 as col_b from
2159 cte join (select rr from (values(1,7),(3,8)) rr limit 2) ss
2160 on (r).column1 = (rr).column1;
2161 select pg_get_viewdef('tt24v', true);
2162 pg_get_viewdef
2163 ------------------------------------------------------------
2164 WITH cte AS MATERIALIZED ( +
2165 SELECT r.*::record AS r +
2166 FROM ( VALUES (1,2), (3,4)) r +
2167 ) +
2168 SELECT (cte.r).column2 AS col_a, +
2169 (ss.rr).column2 AS col_b +
2170 FROM cte +
2171 JOIN ( SELECT rr.*::record AS rr +
2172 FROM ( VALUES (1,7), (3,8)) rr +
2173 LIMIT 2) ss ON (cte.r).column1 = (ss.rr).column1;
2174 (1 row)
2176 create view tt25v as
2177 with cte as materialized (select pg_get_keywords() k)
2178 select (k).word from cte;
2179 select pg_get_viewdef('tt25v', true);
2180 pg_get_viewdef
2181 ----------------------------------------
2182 WITH cte AS MATERIALIZED ( +
2183 SELECT pg_get_keywords() AS k+
2184 ) +
2185 SELECT (k).word AS word +
2186 FROM cte;
2187 (1 row)
2189 -- also check cases seen only in EXPLAIN
2190 explain (verbose, costs off)
2191 select * from tt24v;
2192 QUERY PLAN
2193 ------------------------------------------------------------------------------------------
2194 Hash Join
2195 Output: (cte.r).column2, ((ROW("*VALUES*".column1, "*VALUES*".column2))).column2
2196 Hash Cond: ((cte.r).column1 = ((ROW("*VALUES*".column1, "*VALUES*".column2))).column1)
2197 CTE cte
2198 -> Values Scan on "*VALUES*_1"
2199 Output: ROW("*VALUES*_1".column1, "*VALUES*_1".column2)
2200 -> CTE Scan on cte
2201 Output: cte.r
2202 -> Hash
2203 Output: (ROW("*VALUES*".column1, "*VALUES*".column2))
2204 -> Limit
2205 Output: (ROW("*VALUES*".column1, "*VALUES*".column2))
2206 -> Values Scan on "*VALUES*"
2207 Output: ROW("*VALUES*".column1, "*VALUES*".column2)
2208 (14 rows)
2210 explain (verbose, costs off)
2211 select (r).column2 from (select r from (values(1,2),(3,4)) r limit 1) ss;
2212 QUERY PLAN
2213 -------------------------------------------------------------------
2214 Subquery Scan on ss
2215 Output: (ss.r).column2
2216 -> Limit
2217 Output: (ROW("*VALUES*".column1, "*VALUES*".column2))
2218 -> Values Scan on "*VALUES*"
2219 Output: ROW("*VALUES*".column1, "*VALUES*".column2)
2220 (6 rows)
2222 -- test pretty-print parenthesization rules, and SubLink deparsing
2223 create view tt26v as
2224 select x + y + z as c1,
2225 (x * y) + z as c2,
2226 x + (y * z) as c3,
2227 (x + y) * z as c4,
2228 x * (y + z) as c5,
2229 x + (y + z) as c6,
2230 x + (y # z) as c7,
2231 (x > y) AND (y > z OR x > z) as c8,
2232 (x > y) OR (y > z AND NOT (x > z)) as c9,
2233 (x,y) <> ALL (values(1,2),(3,4)) as c10,
2234 (x,y) <= ANY (values(1,2),(3,4)) as c11
2235 from (values(1,2,3)) v(x,y,z);
2236 select pg_get_viewdef('tt26v', true);
2237 pg_get_viewdef
2238 ----------------------------------------------------
2239 SELECT x + y + z AS c1, +
2240 x * y + z AS c2, +
2241 x + y * z AS c3, +
2242 (x + y) * z AS c4, +
2243 x * (y + z) AS c5, +
2244 x + (y + z) AS c6, +
2245 x + (y # z) AS c7, +
2246 x > y AND (y > z OR x > z) AS c8, +
2247 x > y OR y > z AND NOT x > z AS c9, +
2248 ((x, y) <> ALL ( VALUES (1,2), (3,4))) AS c10,+
2249 ((x, y) <= ANY ( VALUES (1,2), (3,4))) AS c11 +
2250 FROM ( VALUES (1,2,3)) v(x, y, z);
2251 (1 row)
2253 -- test restriction on non-system view expansion.
2254 create table tt27v_tbl (a int);
2255 create view tt27v as select a from tt27v_tbl;
2256 set restrict_nonsystem_relation_kind to 'view';
2257 select a from tt27v where a > 0; -- Error
2258 ERROR: access to non-system view "tt27v" is restricted
2259 insert into tt27v values (1); -- Error
2260 ERROR: access to non-system view "tt27v" is restricted
2261 select viewname from pg_views where viewname = 'tt27v'; -- Ok to access a system view.
2262 viewname
2263 ----------
2264 tt27v
2265 (1 row)
2267 reset restrict_nonsystem_relation_kind;
2268 -- clean up all the random objects we made above
2269 DROP SCHEMA temp_view_test CASCADE;
2270 NOTICE: drop cascades to 27 other objects
2271 DETAIL: drop cascades to table temp_view_test.base_table
2272 drop cascades to view v2_temp
2273 drop cascades to view v4_temp
2274 drop cascades to view v6_temp
2275 drop cascades to view v7_temp
2276 drop cascades to view v10_temp
2277 drop cascades to view v8_temp
2278 drop cascades to view v9_temp
2279 drop cascades to view v11_temp
2280 drop cascades to view v12_temp
2281 drop cascades to table temp_view_test.base_table2
2282 drop cascades to view v5_temp
2283 drop cascades to view temp_view_test.v1
2284 drop cascades to view temp_view_test.v2
2285 drop cascades to view temp_view_test.v3
2286 drop cascades to view temp_view_test.v4
2287 drop cascades to view temp_view_test.v5
2288 drop cascades to view temp_view_test.v6
2289 drop cascades to view temp_view_test.v7
2290 drop cascades to view temp_view_test.v8
2291 drop cascades to sequence temp_view_test.seq1
2292 drop cascades to view temp_view_test.v9
2293 drop cascades to table temp_view_test.tx1
2294 drop cascades to view aliased_view_1
2295 drop cascades to view aliased_view_2
2296 drop cascades to view aliased_view_3
2297 drop cascades to view aliased_view_4
2298 DROP SCHEMA testviewschm2 CASCADE;
2299 NOTICE: drop cascades to 80 other objects
2300 DETAIL: drop cascades to table t1
2301 drop cascades to view temporal1
2302 drop cascades to view temporal2
2303 drop cascades to view temporal3
2304 drop cascades to view temporal4
2305 drop cascades to table t2
2306 drop cascades to view nontemp1
2307 drop cascades to view nontemp2
2308 drop cascades to view nontemp3
2309 drop cascades to view nontemp4
2310 drop cascades to table tbl1
2311 drop cascades to table tbl2
2312 drop cascades to table tbl3
2313 drop cascades to table tbl4
2314 drop cascades to view mytempview
2315 drop cascades to view pubview
2316 drop cascades to view mysecview1
2317 drop cascades to view mysecview2
2318 drop cascades to view mysecview3
2319 drop cascades to view mysecview4
2320 drop cascades to view mysecview7
2321 drop cascades to view mysecview8
2322 drop cascades to view mysecview9
2323 drop cascades to view unspecified_types
2324 drop cascades to table tt1
2325 drop cascades to table tx1
2326 drop cascades to view aliased_order_by
2327 drop cascades to view view_of_joins
2328 drop cascades to table tbl1a
2329 drop cascades to view view_of_joins_2a
2330 drop cascades to view view_of_joins_2b
2331 drop cascades to view view_of_joins_2c
2332 drop cascades to view view_of_joins_2d
2333 drop cascades to table tt2
2334 drop cascades to table tt3
2335 drop cascades to table tt4
2336 drop cascades to view v1
2337 drop cascades to view v1a
2338 drop cascades to view v2
2339 drop cascades to view v2a
2340 drop cascades to view v3
2341 drop cascades to table tt5
2342 drop cascades to table tt6
2343 drop cascades to view vv1
2344 drop cascades to view v4
2345 drop cascades to table tt7
2346 drop cascades to table tt8
2347 drop cascades to view vv2
2348 drop cascades to view vv3
2349 drop cascades to view vv4
2350 drop cascades to table tt7a
2351 drop cascades to table tt8a
2352 drop cascades to view vv2a
2353 drop cascades to table tt9
2354 drop cascades to table tt10
2355 drop cascades to view vv5
2356 drop cascades to table tt11
2357 drop cascades to table tt12
2358 drop cascades to table tt13
2359 drop cascades to view vv6
2360 drop cascades to table tt14t
2361 drop cascades to function tt14f()
2362 drop cascades to view tt14v
2363 drop cascades to type nestedcomposite
2364 drop cascades to view tt15v
2365 drop cascades to view tt16v
2366 drop cascades to view tt17v
2367 drop cascades to table tt15v_log
2368 drop cascades to view tt18v
2369 drop cascades to view tt19v
2370 drop cascades to view tt20v
2371 drop cascades to view tt201v
2372 drop cascades to view tt21v
2373 drop cascades to view tt22v
2374 drop cascades to view tt23v
2375 drop cascades to view tt24v
2376 drop cascades to view tt25v
2377 drop cascades to view tt26v
2378 drop cascades to table tt27v_tbl
2379 drop cascades to view tt27v