aboutsummaryrefslogtreecommitdiff
path: root/src/test/regress/expected/create_view.out
diff options
context:
space:
mode:
Diffstat (limited to 'src/test/regress/expected/create_view.out')
-rw-r--r--src/test/regress/expected/create_view.out610
1 files changed, 305 insertions, 305 deletions
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index f6db582afda..d8a67d34b2f 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -706,347 +706,335 @@ create view v2 as select * from tt2 join tt3 using (b,c) join tt4 using (b);
create view v2a as select * from (tt2 join tt3 using (b,c) join tt4 using (b)) j;
create view v3 as select * from tt2 join tt3 using (b,c) full join tt4 using (b);
select pg_get_viewdef('v1', true);
- pg_get_viewdef
----------------------------
- SELECT tt2.b, +
- tt3.c, +
- tt2.a, +
- tt3.ax +
- FROM tt2 +
- JOIN tt3 USING (b, c);
+ pg_get_viewdef
+-----------------------------
+ SELECT tt2.b, +
+ tt3.c, +
+ tt2.a, +
+ tt3.ax +
+ FROM tt2 +
+ JOIN tt3 USING (b, c);
(1 row)
select pg_get_viewdef('v1a', true);
- pg_get_viewdef
-------------------------------
- SELECT j.b, +
- j.c, +
- j.a, +
- j.ax +
- FROM (tt2 +
- JOIN tt3 USING (b, c)) j;
+ pg_get_viewdef
+--------------------------------
+ SELECT j.b, +
+ j.c, +
+ j.a, +
+ j.ax +
+ FROM (tt2 +
+ JOIN tt3 USING (b, c)) j;
(1 row)
select pg_get_viewdef('v2', true);
- pg_get_viewdef
---------------------------
- SELECT tt2.b, +
- tt3.c, +
- tt2.a, +
- tt3.ax, +
- tt4.ay, +
- tt4.q +
- FROM tt2 +
- JOIN tt3 USING (b, c)+
- JOIN tt4 USING (b);
+ pg_get_viewdef
+----------------------------
+ SELECT tt2.b, +
+ tt3.c, +
+ tt2.a, +
+ tt3.ax, +
+ tt4.ay, +
+ tt4.q +
+ FROM tt2 +
+ JOIN tt3 USING (b, c)+
+ JOIN tt4 USING (b);
(1 row)
select pg_get_viewdef('v2a', true);
- pg_get_viewdef
----------------------------
- SELECT j.b, +
- j.c, +
- j.a, +
- j.ax, +
- j.ay, +
- j.q +
- FROM (tt2 +
- JOIN tt3 USING (b, c) +
- JOIN tt4 USING (b)) j;
+ pg_get_viewdef
+-----------------------------
+ SELECT j.b, +
+ j.c, +
+ j.a, +
+ j.ax, +
+ j.ay, +
+ j.q +
+ FROM (tt2 +
+ JOIN tt3 USING (b, c) +
+ JOIN tt4 USING (b)) j;
(1 row)
select pg_get_viewdef('v3', true);
- pg_get_viewdef
------------------------------
- SELECT b, +
- tt3.c, +
- tt2.a, +
- tt3.ax, +
- tt4.ay, +
- tt4.q +
- FROM tt2 +
- JOIN tt3 USING (b, c) +
- FULL JOIN tt4 USING (b);
+ pg_get_viewdef
+-------------------------------
+ SELECT b, +
+ tt3.c, +
+ tt2.a, +
+ tt3.ax, +
+ tt4.ay, +
+ tt4.q +
+ FROM tt2 +
+ JOIN tt3 USING (b, c) +
+ FULL JOIN tt4 USING (b);
(1 row)
alter table tt2 add column d int;
alter table tt2 add column e int;
select pg_get_viewdef('v1', true);
- pg_get_viewdef
----------------------------
- SELECT tt2.b, +
- tt3.c, +
- tt2.a, +
- tt3.ax +
- FROM tt2 +
- JOIN tt3 USING (b, c);
+ pg_get_viewdef
+-----------------------------
+ SELECT tt2.b, +
+ tt3.c, +
+ tt2.a, +
+ tt3.ax +
+ FROM tt2 +
+ JOIN tt3 USING (b, c);
(1 row)
select pg_get_viewdef('v1a', true);
- pg_get_viewdef
-------------------------------
- SELECT j.b, +
- j.c, +
- j.a, +
- j.ax +
- FROM (tt2 +
- JOIN tt3 USING (b, c)) j;
+ pg_get_viewdef
+--------------------------------
+ SELECT j.b, +
+ j.c, +
+ j.a, +
+ j.ax +
+ FROM (tt2 +
+ JOIN tt3 USING (b, c)) j;
(1 row)
select pg_get_viewdef('v2', true);
- pg_get_viewdef
---------------------------
- SELECT tt2.b, +
- tt3.c, +
- tt2.a, +
- tt3.ax, +
- tt4.ay, +
- tt4.q +
- FROM tt2 +
- JOIN tt3 USING (b, c)+
- JOIN tt4 USING (b);
+ pg_get_viewdef
+----------------------------
+ SELECT tt2.b, +
+ tt3.c, +
+ tt2.a, +
+ tt3.ax, +
+ tt4.ay, +
+ tt4.q +
+ FROM tt2 +
+ JOIN tt3 USING (b, c)+
+ JOIN tt4 USING (b);
(1 row)
select pg_get_viewdef('v2a', true);
- pg_get_viewdef
----------------------------
- SELECT j.b, +
- j.c, +
- j.a, +
- j.ax, +
- j.ay, +
- j.q +
- FROM (tt2 +
- JOIN tt3 USING (b, c) +
- JOIN tt4 USING (b)) j;
+ pg_get_viewdef
+-----------------------------
+ SELECT j.b, +
+ j.c, +
+ j.a, +
+ j.ax, +
+ j.ay, +
+ j.q +
+ FROM (tt2 +
+ JOIN tt3 USING (b, c) +
+ JOIN tt4 USING (b)) j;
(1 row)
select pg_get_viewdef('v3', true);
- pg_get_viewdef
------------------------------
- SELECT b, +
- tt3.c, +
- tt2.a, +
- tt3.ax, +
- tt4.ay, +
- tt4.q +
- FROM tt2 +
- JOIN tt3 USING (b, c) +
- FULL JOIN tt4 USING (b);
+ pg_get_viewdef
+-------------------------------
+ SELECT b, +
+ tt3.c, +
+ tt2.a, +
+ tt3.ax, +
+ tt4.ay, +
+ tt4.q +
+ FROM tt2 +
+ JOIN tt3 USING (b, c) +
+ FULL JOIN tt4 USING (b);
(1 row)
alter table tt3 rename c to d;
select pg_get_viewdef('v1', true);
- pg_get_viewdef
------------------------------------------
- SELECT tt2.b, +
- tt3.c, +
- tt2.a, +
- tt3.ax +
- FROM tt2 +
- JOIN tt3 tt3(ax, b, c) USING (b, c);
+ pg_get_viewdef
+-------------------------------------------
+ SELECT tt2.b, +
+ tt3.c, +
+ tt2.a, +
+ tt3.ax +
+ FROM tt2 +
+ JOIN tt3 tt3(ax, b, c) USING (b, c);
(1 row)
select pg_get_viewdef('v1a', true);
- pg_get_viewdef
---------------------------------------------
- SELECT j.b, +
- j.c, +
- j.a, +
- j.ax +
- FROM (tt2 +
- JOIN tt3 tt3(ax, b, c) USING (b, c)) j;
+ pg_get_viewdef
+----------------------------------------------
+ SELECT j.b, +
+ j.c, +
+ j.a, +
+ j.ax +
+ FROM (tt2 +
+ JOIN tt3 tt3(ax, b, c) USING (b, c)) j;
(1 row)
select pg_get_viewdef('v2', true);
- pg_get_viewdef
-----------------------------------------
- SELECT tt2.b, +
- tt3.c, +
- tt2.a, +
- tt3.ax, +
- tt4.ay, +
- tt4.q +
- FROM tt2 +
- JOIN tt3 tt3(ax, b, c) USING (b, c)+
- JOIN tt4 USING (b);
+ pg_get_viewdef
+------------------------------------------
+ SELECT tt2.b, +
+ tt3.c, +
+ tt2.a, +
+ tt3.ax, +
+ tt4.ay, +
+ tt4.q +
+ FROM tt2 +
+ JOIN tt3 tt3(ax, b, c) USING (b, c)+
+ JOIN tt4 USING (b);
(1 row)
select pg_get_viewdef('v2a', true);
- pg_get_viewdef
-----------------------------------------
- SELECT j.b, +
- j.c, +
- j.a, +
- j.ax, +
- j.ay, +
- j.q +
- FROM (tt2 +
- JOIN tt3 tt3(ax, b, c) USING (b, c)+
- JOIN tt4 USING (b)) j;
+ pg_get_viewdef
+------------------------------------------
+ SELECT j.b, +
+ j.c, +
+ j.a, +
+ j.ax, +
+ j.ay, +
+ j.q +
+ FROM (tt2 +
+ JOIN tt3 tt3(ax, b, c) USING (b, c)+
+ JOIN tt4 USING (b)) j;
(1 row)
select pg_get_viewdef('v3', true);
- pg_get_viewdef
-----------------------------------------
- SELECT b, +
- tt3.c, +
- tt2.a, +
- tt3.ax, +
- tt4.ay, +
- tt4.q +
- FROM tt2 +
- JOIN tt3 tt3(ax, b, c) USING (b, c)+
- FULL JOIN tt4 USING (b);
+ pg_get_viewdef
+------------------------------------------
+ SELECT b, +
+ tt3.c, +
+ tt2.a, +
+ tt3.ax, +
+ tt4.ay, +
+ tt4.q +
+ FROM tt2 +
+ JOIN tt3 tt3(ax, b, c) USING (b, c)+
+ FULL JOIN tt4 USING (b);
(1 row)
alter table tt3 add column c int;
alter table tt3 add column e int;
select pg_get_viewdef('v1', true);
- pg_get_viewdef
--------------------------------------------------
- SELECT tt2.b, +
- tt3.c, +
- tt2.a, +
- tt3.ax +
- FROM tt2 +
- JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c);
+ pg_get_viewdef
+---------------------------------------------------
+ SELECT tt2.b, +
+ tt3.c, +
+ tt2.a, +
+ tt3.ax +
+ FROM tt2 +
+ JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c);
(1 row)
select pg_get_viewdef('v1a', true);
- pg_get_viewdef
----------------------------------------------------------------------------------
- SELECT j.b, +
- j.c, +
- j.a, +
- j.ax +
- FROM (tt2 +
- JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)) j(b, c, a, d, e, ax, c_1, e_1);
+ pg_get_viewdef
+-----------------------------------------------------------------------------------
+ SELECT j.b, +
+ j.c, +
+ j.a, +
+ j.ax +
+ FROM (tt2 +
+ JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)) j(b, c, a, d, e, ax, c_1, e_1);
(1 row)
select pg_get_viewdef('v2', true);
- pg_get_viewdef
-------------------------------------------------
- SELECT tt2.b, +
- tt3.c, +
- tt2.a, +
- tt3.ax, +
- tt4.ay, +
- tt4.q +
- FROM tt2 +
- JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
- JOIN tt4 USING (b);
+ pg_get_viewdef
+--------------------------------------------------
+ SELECT tt2.b, +
+ tt3.c, +
+ tt2.a, +
+ tt3.ax, +
+ tt4.ay, +
+ tt4.q +
+ FROM tt2 +
+ JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
+ JOIN tt4 USING (b);
(1 row)
select pg_get_viewdef('v2a', true);
- pg_get_viewdef
----------------------------------------------------------------
- SELECT j.b, +
- j.c, +
- j.a, +
- j.ax, +
- j.ay, +
- j.q +
- FROM (tt2 +
- JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c) +
- JOIN tt4 USING (b)) j(b, c, a, d, e, ax, c_1, e_1, ay, q);
+ pg_get_viewdef
+-----------------------------------------------------------------
+ SELECT j.b, +
+ j.c, +
+ j.a, +
+ j.ax, +
+ j.ay, +
+ j.q +
+ FROM (tt2 +
+ JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c) +
+ JOIN tt4 USING (b)) j(b, c, a, d, e, ax, c_1, e_1, ay, q);
(1 row)
select pg_get_viewdef('v3', true);
- pg_get_viewdef
-------------------------------------------------
- SELECT b, +
- tt3.c, +
- tt2.a, +
- tt3.ax, +
- tt4.ay, +
- tt4.q +
- FROM tt2 +
- JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
- FULL JOIN tt4 USING (b);
+ pg_get_viewdef
+--------------------------------------------------
+ SELECT b, +
+ tt3.c, +
+ tt2.a, +
+ tt3.ax, +
+ tt4.ay, +
+ tt4.q +
+ FROM tt2 +
+ JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
+ FULL JOIN tt4 USING (b);
(1 row)
alter table tt2 drop column d;
select pg_get_viewdef('v1', true);
- pg_get_viewdef
--------------------------------------------------
- SELECT tt2.b, +
- tt3.c, +
- tt2.a, +
- tt3.ax +
- FROM tt2 +
- JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c);
+ pg_get_viewdef
+---------------------------------------------------
+ SELECT tt2.b, +
+ tt3.c, +
+ tt2.a, +
+ tt3.ax +
+ FROM tt2 +
+ JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c);
(1 row)
select pg_get_viewdef('v1a', true);
- pg_get_viewdef
-------------------------------------------------------------------------------
- SELECT j.b, +
- j.c, +
- j.a, +
- j.ax +
- FROM (tt2 +
- JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)) j(b, c, a, e, ax, c_1, e_1);
+ pg_get_viewdef
+--------------------------------------------------------------------------------
+ SELECT j.b, +
+ j.c, +
+ j.a, +
+ j.ax +
+ FROM (tt2 +
+ JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)) j(b, c, a, e, ax, c_1, e_1);
(1 row)
select pg_get_viewdef('v2', true);
- pg_get_viewdef
-------------------------------------------------
- SELECT tt2.b, +
- tt3.c, +
- tt2.a, +
- tt3.ax, +
- tt4.ay, +
- tt4.q +
- FROM tt2 +
- JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
- JOIN tt4 USING (b);
+ pg_get_viewdef
+--------------------------------------------------
+ SELECT tt2.b, +
+ tt3.c, +
+ tt2.a, +
+ tt3.ax, +
+ tt4.ay, +
+ tt4.q +
+ FROM tt2 +
+ JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
+ JOIN tt4 USING (b);
(1 row)
select pg_get_viewdef('v2a', true);
- pg_get_viewdef
-------------------------------------------------------------
- SELECT j.b, +
- j.c, +
- j.a, +
- j.ax, +
- j.ay, +
- j.q +
- FROM (tt2 +
- JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c) +
- JOIN tt4 USING (b)) j(b, c, a, e, ax, c_1, e_1, ay, q);
+ pg_get_viewdef
+--------------------------------------------------------------
+ SELECT j.b, +
+ j.c, +
+ j.a, +
+ j.ax, +
+ j.ay, +
+ j.q +
+ FROM (tt2 +
+ JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c) +
+ JOIN tt4 USING (b)) j(b, c, a, e, ax, c_1, e_1, ay, q);
(1 row)
select pg_get_viewdef('v3', true);
- pg_get_viewdef
-------------------------------------------------
- SELECT b, +
- tt3.c, +
- tt2.a, +
- tt3.ax, +
- tt4.ay, +
- tt4.q +
- FROM tt2 +
- JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
- FULL JOIN tt4 USING (b);
+ pg_get_viewdef
+--------------------------------------------------
+ SELECT b, +
+ tt3.c, +
+ tt2.a, +
+ tt3.ax, +
+ tt4.ay, +
+ tt4.q +
+ FROM tt2 +
+ JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
+ FULL JOIN tt4 USING (b);
(1 row)
create table tt5 (a int, b int);
create table tt6 (c int, d int);
create view vv1 as select * from (tt5 cross join tt6) j(aa,bb,cc,dd);
select pg_get_viewdef('vv1', true);
- pg_get_viewdef
---------------------------------------
- SELECT j.aa, +
- j.bb, +
- j.cc, +
- j.dd +
- FROM (tt5 +
- CROSS JOIN tt6) j(aa, bb, cc, dd);
-(1 row)
-
-alter table tt5 add column c int;
-select pg_get_viewdef('vv1', true);
pg_get_viewdef
-----------------------------------------
SELECT j.aa, +
@@ -1054,11 +1042,35 @@ select pg_get_viewdef('vv1', true);
j.cc, +
j.dd +
FROM (tt5 +
- CROSS JOIN tt6) j(aa, bb, c, cc, dd);
+ CROSS JOIN tt6) j(aa, bb, cc, dd);
+(1 row)
+
+alter table tt5 add column c int;
+select pg_get_viewdef('vv1', true);
+ pg_get_viewdef
+--------------------------------------------
+ SELECT j.aa, +
+ j.bb, +
+ j.cc, +
+ j.dd +
+ FROM (tt5 +
+ CROSS JOIN tt6) j(aa, bb, c, cc, dd);
(1 row)
alter table tt5 add column cc int;
select pg_get_viewdef('vv1', true);
+ pg_get_viewdef
+--------------------------------------------------
+ SELECT j.aa, +
+ j.bb, +
+ j.cc, +
+ j.dd +
+ FROM (tt5 +
+ CROSS JOIN tt6) j(aa, bb, c, cc_1, cc, dd);
+(1 row)
+
+alter table tt5 drop column c;
+select pg_get_viewdef('vv1', true);
pg_get_viewdef
-----------------------------------------------
SELECT j.aa, +
@@ -1066,19 +1078,7 @@ select pg_get_viewdef('vv1', true);
j.cc, +
j.dd +
FROM (tt5 +
- CROSS JOIN tt6) j(aa, bb, c, cc_1, cc, dd);
-(1 row)
-
-alter table tt5 drop column c;
-select pg_get_viewdef('vv1', true);
- pg_get_viewdef
---------------------------------------------
- SELECT j.aa, +
- j.bb, +
- j.cc, +
- j.dd +
- FROM (tt5 +
- CROSS JOIN tt6) j(aa, bb, cc_1, cc, dd);
+ CROSS JOIN tt6) j(aa, bb, cc_1, cc, dd);
(1 row)
-- Unnamed FULL JOIN USING is lots of fun too
@@ -1105,8 +1105,8 @@ select pg_get_viewdef('vv2', true);
tt8x.x_1 AS d, +
tt8x.z AS e +
FROM tt7 +
- FULL JOIN tt8 USING (x), +
- tt8 tt8x(x_1, z);
+ FULL JOIN tt8 USING (x), +
+ tt8 tt8x(x_1, z);
(1 row)
create view vv3 as
@@ -1133,9 +1133,9 @@ select pg_get_viewdef('vv3', true);
tt7x.y AS e, +
tt8x.z AS f +
FROM tt7 +
- FULL JOIN tt8 USING (x), +
- tt7 tt7x(x_1, y) +
- FULL JOIN tt8 tt8x(x_1, z) USING (x_1);
+ FULL JOIN tt8 USING (x), +
+ tt7 tt7x(x_1, y) +
+ FULL JOIN tt8 tt8x(x_1, z) USING (x_1);
(1 row)
create view vv4 as
@@ -1164,10 +1164,10 @@ select pg_get_viewdef('vv4', true);
tt8x.z AS f, +
tt8y.z AS g +
FROM tt7 +
- FULL JOIN tt8 USING (x), +
- tt7 tt7x(x_1, y) +
- FULL JOIN tt8 tt8x(x_1, z) USING (x_1) +
- FULL JOIN tt8 tt8y(x_1, z) USING (x_1);
+ FULL JOIN tt8 USING (x), +
+ tt7 tt7x(x_1, y) +
+ FULL JOIN tt8 tt8x(x_1, z) USING (x_1) +
+ FULL JOIN tt8 tt8y(x_1, z) USING (x_1);
(1 row)
alter table tt7 add column zz int;
@@ -1190,8 +1190,8 @@ select pg_get_viewdef('vv2', true);
tt8x.x_1 AS d, +
tt8x.z AS e +
FROM tt7 +
- FULL JOIN tt8 USING (x), +
- tt8 tt8x(x_1, z, z2);
+ FULL JOIN tt8 USING (x), +
+ tt8 tt8x(x_1, z, z2);
(1 row)
select pg_get_viewdef('vv3', true);
@@ -1212,9 +1212,9 @@ select pg_get_viewdef('vv3', true);
tt7x.y AS e, +
tt8x.z AS f +
FROM tt7 +
- FULL JOIN tt8 USING (x), +
- tt7 tt7x(x_1, y, z) +
- FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1);
+ FULL JOIN tt8 USING (x), +
+ tt7 tt7x(x_1, y, z) +
+ FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1);
(1 row)
select pg_get_viewdef('vv4', true);
@@ -1237,10 +1237,10 @@ select pg_get_viewdef('vv4', true);
tt8x.z AS f, +
tt8y.z AS g +
FROM tt7 +
- FULL JOIN tt8 USING (x), +
- tt7 tt7x(x_1, y, z) +
- FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1) +
- FULL JOIN tt8 tt8y(x_1, z, z2) USING (x_1);
+ FULL JOIN tt8 USING (x), +
+ tt7 tt7x(x_1, y, z) +
+ FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1) +
+ FULL JOIN tt8 tt8y(x_1, z, z2) USING (x_1);
(1 row)
-- Implicit coercions in a JOIN USING create issues similar to FULL JOIN
@@ -1267,8 +1267,8 @@ select pg_get_viewdef('vv2a', true);
tt8ax.x_1 AS d, +
tt8ax.z AS e +
FROM tt7a +
- LEFT JOIN tt8a USING (x), +
- tt8a tt8ax(x_1, z);
+ LEFT JOIN tt8a USING (x), +
+ tt8a tt8ax(x_1, z);
(1 row)
--
@@ -1278,24 +1278,24 @@ create table tt9 (x int, xx int, y int);
create table tt10 (x int, z int);
create view vv5 as select x,y,z from tt9 join tt10 using(x);
select pg_get_viewdef('vv5', true);
- pg_get_viewdef
--------------------------
- SELECT tt9.x, +
- tt9.y, +
- tt10.z +
- FROM tt9 +
- JOIN tt10 USING (x);
+ pg_get_viewdef
+---------------------------
+ SELECT tt9.x, +
+ tt9.y, +
+ tt10.z +
+ FROM tt9 +
+ JOIN tt10 USING (x);
(1 row)
alter table tt9 drop column xx;
select pg_get_viewdef('vv5', true);
- pg_get_viewdef
--------------------------
- SELECT tt9.x, +
- tt9.y, +
- tt10.z +
- FROM tt9 +
- JOIN tt10 USING (x);
+ pg_get_viewdef
+---------------------------
+ SELECT tt9.x, +
+ tt9.y, +
+ tt10.z +
+ FROM tt9 +
+ JOIN tt10 USING (x);
(1 row)
-- clean up all the random objects we made above