Re: pg_dump --pretty-print-views
| От | Tom Lane |
|---|---|
| Тема | Re: pg_dump --pretty-print-views |
| Дата | |
| Msg-id | 4508.1398815073@sss.pgh.pa.us обсуждение исходный текст |
| Ответ на | Re: pg_dump --pretty-print-views (Tom Lane <tgl@sss.pgh.pa.us>) |
| Список | pgsql-hackers |
I wrote:
> While I was testing this I noticed that there's something thoroughly
> busted about the indentation of outer JOIN constructs, too --- you
> can see this in some of the regression test queries that are touched
> by this patch, where the JOINs are actually outdented to the left of
> their FROM clause in the unpatched output. (They'd be outdented in
> the new output, too, if negative indentation were possible...)
> That seems like material for a separate patch though.
I poked into that too, and found that it seems to have been busted from
the very beginning. There's a unmatched subtraction of
PRETTYINDENT_JOIN_ON from the indentLevel, which I suppose must be an
accidental leftover from some previous version of the logic. And the code
is also trying to outdent JOIN clauses further than the corresponding
FROM, which seems unintuitive to me, even when the FROM is indented far
enough to make it possible (which it generally isn't in simple views).
Attached are two separate versions of a repair patch. The first one
causes JOIN clauses to be indented the same as their parent FROM.
The second one moves them over an additional 5 spaces. I find the
second layout more sensible, but it does result in significantly
more changes in the regression test outputs, as you can see.
Comments?
regards, tom lane
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index ea7b8c5..f6bf2c3 100644
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 68,75 ****
/* Indent counts */
#define PRETTYINDENT_STD 8
! #define PRETTYINDENT_JOIN 13
! #define PRETTYINDENT_JOIN_ON (PRETTYINDENT_JOIN-PRETTYINDENT_STD)
#define PRETTYINDENT_VAR 4
/* Pretty flags */
--- 68,74 ----
/* Indent counts */
#define PRETTYINDENT_STD 8
! #define PRETTYINDENT_JOIN 2
#define PRETTYINDENT_VAR 4
/* Pretty flags */
*************** get_from_clause_item(Node *jtnode, Query
*** 8378,8404 ****
case JOIN_INNER:
if (j->quals)
appendContextKeyword(context, " JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 2);
else
appendContextKeyword(context, " CROSS JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 1);
break;
case JOIN_LEFT:
appendContextKeyword(context, " LEFT JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 2);
break;
case JOIN_FULL:
appendContextKeyword(context, " FULL JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 2);
break;
case JOIN_RIGHT:
appendContextKeyword(context, " RIGHT JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 2);
break;
default:
elog(ERROR, "unrecognized join type: %d",
--- 8377,8408 ----
case JOIN_INNER:
if (j->quals)
appendContextKeyword(context, " JOIN ",
! -PRETTYINDENT_STD,
! PRETTYINDENT_STD,
! PRETTYINDENT_JOIN);
else
appendContextKeyword(context, " CROSS JOIN ",
! -PRETTYINDENT_STD,
! PRETTYINDENT_STD,
! PRETTYINDENT_JOIN);
break;
case JOIN_LEFT:
appendContextKeyword(context, " LEFT JOIN ",
! -PRETTYINDENT_STD,
! PRETTYINDENT_STD,
! PRETTYINDENT_JOIN);
break;
case JOIN_FULL:
appendContextKeyword(context, " FULL JOIN ",
! -PRETTYINDENT_STD,
! PRETTYINDENT_STD,
! PRETTYINDENT_JOIN);
break;
case JOIN_RIGHT:
appendContextKeyword(context, " RIGHT JOIN ",
! -PRETTYINDENT_STD,
! PRETTYINDENT_STD,
! PRETTYINDENT_JOIN);
break;
default:
elog(ERROR, "unrecognized join type: %d",
*************** get_from_clause_item(Node *jtnode, Query
*** 8411,8418 ****
if (need_paren_on_right)
appendStringInfoChar(buf, ')');
- context->indentLevel -= PRETTYINDENT_JOIN_ON;
-
if (j->usingClause)
{
ListCell *lc;
--- 8415,8420 ----
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index f6db582..3835f12 100644
*** a/src/test/regress/expected/create_view.out
--- b/src/test/regress/expected/create_view.out
*************** create table tt5 (a int, b int);
*** 1035,1084 ****
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, +
! 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, +
! j.bb, +
! j.cc, +
! j.dd +
! FROM (tt5 +
! CROSS JOIN tt6) j(aa, bb, cc_1, cc, dd);
(1 row)
-- Unnamed FULL JOIN USING is lots of fun too
--- 1035,1084 ----
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, +
! 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, +
! j.bb, +
! j.cc, +
! j.dd +
! FROM (tt5 +
! CROSS JOIN tt6) j(aa, bb, cc_1, cc, dd);
(1 row)
-- Unnamed FULL JOIN USING is lots of fun too
*************** select pg_get_viewdef('vv2', true);
*** 1105,1112 ****
tt8x.x_1 AS d, +
tt8x.z AS e +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt8 tt8x(x_1, z);
(1 row)
create view vv3 as
--- 1105,1112 ----
tt8x.x_1 AS d, +
tt8x.z AS e +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt8 tt8x(x_1, z);
(1 row)
create view vv3 as
*************** select pg_get_viewdef('vv3', true);
*** 1133,1141 ****
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);
(1 row)
create view vv4 as
--- 1133,1141 ----
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);
(1 row)
create view vv4 as
*************** select pg_get_viewdef('vv4', true);
*** 1164,1173 ****
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);
(1 row)
alter table tt7 add column zz int;
--- 1164,1173 ----
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);
(1 row)
alter table tt7 add column zz int;
*************** select pg_get_viewdef('vv2', true);
*** 1190,1197 ****
tt8x.x_1 AS d, +
tt8x.z AS e +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt8 tt8x(x_1, z, z2);
(1 row)
select pg_get_viewdef('vv3', true);
--- 1190,1197 ----
tt8x.x_1 AS d, +
tt8x.z AS e +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt8 tt8x(x_1, z, z2);
(1 row)
select pg_get_viewdef('vv3', true);
*************** select pg_get_viewdef('vv3', true);
*** 1212,1220 ****
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);
(1 row)
select pg_get_viewdef('vv4', true);
--- 1212,1220 ----
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);
(1 row)
select pg_get_viewdef('vv4', true);
*************** select pg_get_viewdef('vv4', true);
*** 1237,1246 ****
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);
(1 row)
-- Implicit coercions in a JOIN USING create issues similar to FULL JOIN
--- 1237,1246 ----
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);
(1 row)
-- Implicit coercions in a JOIN USING create issues similar to FULL JOIN
*************** select pg_get_viewdef('vv2a', true);
*** 1267,1274 ****
tt8ax.x_1 AS d, +
tt8ax.z AS e +
FROM tt7a +
! LEFT JOIN tt8a USING (x), +
! tt8a tt8ax(x_1, z);
(1 row)
--
--- 1267,1274 ----
tt8ax.x_1 AS d, +
tt8ax.z AS e +
FROM tt7a +
! LEFT JOIN tt8a USING (x), +
! tt8a tt8ax(x_1, z);
(1 row)
--
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 6c51d0d..976a7b2 100644
*** a/src/test/regress/expected/rules.out
--- b/src/test/regress/expected/rules.out
*************** pg_seclabels| ( ( (
*** 1420,1428 ****
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_class rel ON (((l.classoid =
rel.tableoid)AND (l.objoid = rel.oid))))
! JOIN pg_namespace nsp ON ((rel.relnamespace
=nsp.oid)))
! WHERE (l.objsubid = 0)
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1420,1428 ----
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_class rel ON (((l.classoid
=rel.tableoid) AND (l.objoid = rel.oid))))
! JOIN pg_namespace nsp ON
((rel.relnamespace= nsp.oid)))
! WHERE (l.objsubid = 0)
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1437,1446 ****
l.provider,
l.label
FROM (((pg_seclabel l
! JOIN pg_class rel ON (((l.classoid =
rel.tableoid)AND (l.objoid = rel.oid))))
! JOIN pg_attribute att ON (((rel.oid =
att.attrelid)AND (l.objsubid = att.attnum))))
! JOIN pg_namespace nsp ON ((rel.relnamespace =
nsp.oid)))
! WHERE (l.objsubid <> 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1437,1446 ----
l.provider,
l.label
FROM (((pg_seclabel l
! JOIN pg_class rel ON (((l.classoid
=rel.tableoid) AND (l.objoid = rel.oid))))
! JOIN pg_attribute att ON
(((rel.oid= att.attrelid) AND (l.objsubid = att.attnum))))
! JOIN pg_namespace nsp ON
((rel.relnamespace= nsp.oid)))
! WHERE (l.objsubid <> 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1459,1467 ****
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_proc pro ON (((l.classoid =
pro.tableoid)AND (l.objoid = pro.oid))))
! JOIN pg_namespace nsp ON ((pro.pronamespace =
nsp.oid)))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1459,1467 ----
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_proc pro ON (((l.classoid =
pro.tableoid)AND (l.objoid = pro.oid))))
! JOIN pg_namespace nsp ON
((pro.pronamespace= nsp.oid)))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1478,1486 ****
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_type typ ON (((l.classoid = typ.tableoid) AND
(l.objoid= typ.oid))))
! JOIN pg_namespace nsp ON ((typ.typnamespace = nsp.oid)))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1478,1486 ----
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_type typ ON (((l.classoid = typ.tableoid)
AND(l.objoid = typ.oid))))
! JOIN pg_namespace nsp ON ((typ.typnamespace =
nsp.oid)))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1491,1498 ****
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_largeobject_metadata lom ON ((l.objoid = lom.oid)))
! WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND
(l.objsubid= 0)))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1491,1498 ----
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_largeobject_metadata lom ON ((l.objoid =
lom.oid)))
! WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND
(l.objsubid= 0)))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1503,1510 ****
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND (l.objoid =
lan.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1503,1510 ----
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND
(l.objoid= lan.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1515,1522 ****
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid =
nsp.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1515,1522 ----
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid =
nsp.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1527,1534 ****
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_event_trigger evt ON (((l.classoid = evt.tableoid) AND (l.objoid = evt.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1527,1534 ----
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_event_trigger evt ON (((l.classoid = evt.tableoid) AND (l.objoid =
evt.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1539,1545 ****
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_database dat ON (((l.classoid = dat.tableoid) AND (l.objoid = dat.oid)))))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1539,1545 ----
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_database dat ON (((l.classoid = dat.tableoid) AND (l.objoid = dat.oid)))))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1550,1556 ****
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_tablespace spc ON (((l.classoid = spc.tableoid) AND (l.objoid = spc.oid)))))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1550,1556 ----
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_tablespace spc ON (((l.classoid = spc.tableoid) AND (l.objoid = spc.oid)))))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** UNION ALL
*** 1561,1567 ****
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid))));
pg_settings| SELECT a.name,
a.setting,
a.unit,
--- 1561,1567 ----
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid))));
pg_settings| SELECT a.name,
a.setting,
a.unit,
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index ea7b8c5..27db21e 100644
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 68,75 ****
/* Indent counts */
#define PRETTYINDENT_STD 8
! #define PRETTYINDENT_JOIN 13
! #define PRETTYINDENT_JOIN_ON (PRETTYINDENT_JOIN-PRETTYINDENT_STD)
#define PRETTYINDENT_VAR 4
/* Pretty flags */
--- 68,74 ----
/* Indent counts */
#define PRETTYINDENT_STD 8
! #define PRETTYINDENT_JOIN 7
#define PRETTYINDENT_VAR 4
/* Pretty flags */
*************** get_from_clause_item(Node *jtnode, Query
*** 8378,8404 ****
case JOIN_INNER:
if (j->quals)
appendContextKeyword(context, " JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 2);
else
appendContextKeyword(context, " CROSS JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 1);
break;
case JOIN_LEFT:
appendContextKeyword(context, " LEFT JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 2);
break;
case JOIN_FULL:
appendContextKeyword(context, " FULL JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 2);
break;
case JOIN_RIGHT:
appendContextKeyword(context, " RIGHT JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 2);
break;
default:
elog(ERROR, "unrecognized join type: %d",
--- 8377,8408 ----
case JOIN_INNER:
if (j->quals)
appendContextKeyword(context, " JOIN ",
! -PRETTYINDENT_STD,
! PRETTYINDENT_STD,
! PRETTYINDENT_JOIN);
else
appendContextKeyword(context, " CROSS JOIN ",
! -PRETTYINDENT_STD,
! PRETTYINDENT_STD,
! PRETTYINDENT_JOIN);
break;
case JOIN_LEFT:
appendContextKeyword(context, " LEFT JOIN ",
! -PRETTYINDENT_STD,
! PRETTYINDENT_STD,
! PRETTYINDENT_JOIN);
break;
case JOIN_FULL:
appendContextKeyword(context, " FULL JOIN ",
! -PRETTYINDENT_STD,
! PRETTYINDENT_STD,
! PRETTYINDENT_JOIN);
break;
case JOIN_RIGHT:
appendContextKeyword(context, " RIGHT JOIN ",
! -PRETTYINDENT_STD,
! PRETTYINDENT_STD,
! PRETTYINDENT_JOIN);
break;
default:
elog(ERROR, "unrecognized join type: %d",
*************** get_from_clause_item(Node *jtnode, Query
*** 8411,8418 ****
if (need_paren_on_right)
appendStringInfoChar(buf, ')');
- context->indentLevel -= PRETTYINDENT_JOIN_ON;
-
if (j->usingClause)
{
ListCell *lc;
--- 8415,8420 ----
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index f6db582..d29d0c1 100644
*** a/src/test/regress/expected/create_view.out
--- b/src/test/regress/expected/create_view.out
*************** create view v2 as select * from tt2 join
*** 706,1064 ****
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);
(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;
(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);
(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;
(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);
(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);
(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;
(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);
(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;
(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);
(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);
(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;
(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);
(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;
(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);
(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);
(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);
(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);
(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);
(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);
(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);
(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);
(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);
(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);
(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);
(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, +
- 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, +
--- 706,1052 ----
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);
(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;
(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);
(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;
(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);
(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);
(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;
(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);
(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;
(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);
(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);
(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;
(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);
(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;
(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);
(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);
(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);
(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);
(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);
(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);
(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);
(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);
(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);
(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);
(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);
(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, +
*************** select pg_get_viewdef('vv1', true);
*** 1066,1084 ****
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);
(1 row)
-- Unnamed FULL JOIN USING is lots of fun too
--- 1054,1084 ----
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, +
! j.bb, +
! j.cc, +
! j.dd +
! FROM (tt5 +
! CROSS JOIN tt6) j(aa, bb, cc_1, cc, dd);
(1 row)
-- Unnamed FULL JOIN USING is lots of fun too
*************** select pg_get_viewdef('vv2', true);
*** 1105,1112 ****
tt8x.x_1 AS d, +
tt8x.z AS e +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt8 tt8x(x_1, z);
(1 row)
create view vv3 as
--- 1105,1112 ----
tt8x.x_1 AS d, +
tt8x.z AS e +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt8 tt8x(x_1, z);
(1 row)
create view vv3 as
*************** select pg_get_viewdef('vv3', true);
*** 1133,1141 ****
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);
(1 row)
create view vv4 as
--- 1133,1141 ----
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);
(1 row)
create view vv4 as
*************** select pg_get_viewdef('vv4', true);
*** 1164,1173 ****
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);
(1 row)
alter table tt7 add column zz int;
--- 1164,1173 ----
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);
(1 row)
alter table tt7 add column zz int;
*************** select pg_get_viewdef('vv2', true);
*** 1190,1197 ****
tt8x.x_1 AS d, +
tt8x.z AS e +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt8 tt8x(x_1, z, z2);
(1 row)
select pg_get_viewdef('vv3', true);
--- 1190,1197 ----
tt8x.x_1 AS d, +
tt8x.z AS e +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt8 tt8x(x_1, z, z2);
(1 row)
select pg_get_viewdef('vv3', true);
*************** select pg_get_viewdef('vv3', true);
*** 1212,1220 ****
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);
(1 row)
select pg_get_viewdef('vv4', true);
--- 1212,1220 ----
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);
(1 row)
select pg_get_viewdef('vv4', true);
*************** select pg_get_viewdef('vv4', true);
*** 1237,1246 ****
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);
(1 row)
-- Implicit coercions in a JOIN USING create issues similar to FULL JOIN
--- 1237,1246 ----
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);
(1 row)
-- Implicit coercions in a JOIN USING create issues similar to FULL JOIN
*************** select pg_get_viewdef('vv2a', true);
*** 1267,1274 ****
tt8ax.x_1 AS d, +
tt8ax.z AS e +
FROM tt7a +
! LEFT JOIN tt8a USING (x), +
! tt8a tt8ax(x_1, z);
(1 row)
--
--- 1267,1274 ----
tt8ax.x_1 AS d, +
tt8ax.z AS e +
FROM tt7a +
! LEFT JOIN tt8a USING (x), +
! tt8a tt8ax(x_1, z);
(1 row)
--
*************** create table tt9 (x int, xx int, y int);
*** 1278,1301 ****
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);
(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);
(1 row)
-- clean up all the random objects we made above
--- 1278,1301 ----
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);
(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);
(1 row)
-- clean up all the random objects we made above
diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out
index 9d40510..cb20b6b 100644
*** a/src/test/regress/expected/rangefuncs.out
--- b/src/test/regress/expected/rangefuncs.out
*************** select * from vw_ord;
*** 85,98 ****
(1 row)
select definition from pg_views where viewname='vw_ord';
! definition
! -------------------------------------------------------------------
! SELECT v.n, +
! z.a, +
! z.b, +
! z.ord +
! FROM (( VALUES (1)) v(n) +
! JOIN foot(1) WITH ORDINALITY z(a, b, ord) ON ((v.n = z.ord)));
(1 row)
drop view vw_ord;
--- 85,98 ----
(1 row)
select definition from pg_views where viewname='vw_ord';
! definition
! ------------------------------------------------------------------------
! SELECT v.n, +
! z.a, +
! z.b, +
! z.ord +
! FROM (( VALUES (1)) v(n) +
! JOIN foot(1) WITH ORDINALITY z(a, b, ord) ON ((v.n = z.ord)));
(1 row)
drop view vw_ord;
*************** select * from vw_ord;
*** 112,127 ****
(1 row)
select definition from pg_views where viewname='vw_ord';
! definition
! ---------------------------------------------------------------------------------------------
! SELECT v.n, +
! z.a, +
! z.b, +
! z.c, +
! z.d, +
! z.ord +
! FROM (( VALUES (1)) v(n) +
! JOIN ROWS FROM(foot(1), foot(2)) WITH ORDINALITY z(a, b, c, d, ord) ON ((v.n = z.ord)));
(1 row)
drop view vw_ord;
--- 112,127 ----
(1 row)
select definition from pg_views where viewname='vw_ord';
! definition
! --------------------------------------------------------------------------------------------------
! SELECT v.n, +
! z.a, +
! z.b, +
! z.c, +
! z.d, +
! z.ord +
! FROM (( VALUES (1)) v(n) +
! JOIN ROWS FROM(foot(1), foot(2)) WITH ORDINALITY z(a, b, c, d, ord) ON ((v.n = z.ord)));
(1 row)
drop view vw_ord;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 6c51d0d..da5c836 100644
*** a/src/test/regress/expected/rules.out
--- b/src/test/regress/expected/rules.out
*************** pg_available_extension_versions| SELECT
*** 1294,1306 ****
e.requires,
e.comment
FROM (pg_available_extension_versions() e(name, version, superuser, relocatable, schema, requires, comment)
! LEFT JOIN pg_extension x ON (((e.name = x.extname) AND (e.version = x.extversion))));
pg_available_extensions| SELECT e.name,
e.default_version,
x.extversion AS installed_version,
e.comment
FROM (pg_available_extensions() e(name, default_version, comment)
! LEFT JOIN pg_extension x ON ((e.name = x.extname)));
pg_cursors| SELECT c.name,
c.statement,
c.is_holdable,
--- 1294,1306 ----
e.requires,
e.comment
FROM (pg_available_extension_versions() e(name, version, superuser, relocatable, schema, requires, comment)
! LEFT JOIN pg_extension x ON (((e.name = x.extname) AND (e.version = x.extversion))));
pg_available_extensions| SELECT e.name,
e.default_version,
x.extversion AS installed_version,
e.comment
FROM (pg_available_extensions() e(name, default_version, comment)
! LEFT JOIN pg_extension x ON ((e.name = x.extname)));
pg_cursors| SELECT c.name,
c.statement,
c.is_holdable,
*************** pg_indexes| SELECT n.nspname AS schemana
*** 1321,1330 ****
t.spcname AS tablespace,
pg_get_indexdef(i.oid) AS indexdef
FROM ((((pg_index x
! JOIN pg_class c ON ((c.oid = x.indrelid)))
! JOIN pg_class i ON ((i.oid = x.indexrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
! LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace)))
WHERE ((c.relkind = ANY (ARRAY['r'::"char", 'm'::"char"])) AND (i.relkind = 'i'::"char"));
pg_locks| SELECT l.locktype,
l.database,
--- 1321,1330 ----
t.spcname AS tablespace,
pg_get_indexdef(i.oid) AS indexdef
FROM ((((pg_index x
! JOIN pg_class c ON ((c.oid = x.indrelid)))
! JOIN pg_class i ON ((i.oid = x.indexrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
! LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace)))
WHERE ((c.relkind = ANY (ARRAY['r'::"char", 'm'::"char"])) AND (i.relkind = 'i'::"char"));
pg_locks| SELECT l.locktype,
l.database,
*************** pg_matviews| SELECT n.nspname AS scheman
*** 1350,1357 ****
c.relispopulated AS ispopulated,
pg_get_viewdef(c.oid) AS definition
FROM ((pg_class c
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
! LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace)))
WHERE (c.relkind = 'm'::"char");
pg_prepared_statements| SELECT p.name,
p.statement,
--- 1350,1357 ----
c.relispopulated AS ispopulated,
pg_get_viewdef(c.oid) AS definition
FROM ((pg_class c
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
! LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace)))
WHERE (c.relkind = 'm'::"char");
pg_prepared_statements| SELECT p.name,
p.statement,
*************** pg_prepared_xacts| SELECT p.transaction,
*** 1365,1372 ****
u.rolname AS owner,
d.datname AS database
FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid)
! LEFT JOIN pg_authid u ON ((p.ownerid = u.oid)))
! LEFT JOIN pg_database d ON ((p.dbid = d.oid)));
pg_replication_slots| SELECT l.slot_name,
l.plugin,
l.slot_type,
--- 1365,1372 ----
u.rolname AS owner,
d.datname AS database
FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid)
! LEFT JOIN pg_authid u ON ((p.ownerid = u.oid)))
! LEFT JOIN pg_database d ON ((p.dbid = d.oid)));
pg_replication_slots| SELECT l.slot_name,
l.plugin,
l.slot_type,
*************** pg_replication_slots| SELECT l.slot_name
*** 1377,1383 ****
l.catalog_xmin,
l.restart_lsn
FROM (pg_get_replication_slots() l(slot_name, plugin, slot_type, datoid, active, xmin, catalog_xmin, restart_lsn)
! LEFT JOIN pg_database d ON ((l.datoid = d.oid)));
pg_roles| SELECT pg_authid.rolname,
pg_authid.rolsuper,
pg_authid.rolinherit,
--- 1377,1383 ----
l.catalog_xmin,
l.restart_lsn
FROM (pg_get_replication_slots() l(slot_name, plugin, slot_type, datoid, active, xmin, catalog_xmin, restart_lsn)
! LEFT JOIN pg_database d ON ((l.datoid = d.oid)));
pg_roles| SELECT pg_authid.rolname,
pg_authid.rolsuper,
pg_authid.rolinherit,
*************** pg_roles| SELECT pg_authid.rolname,
*** 1392,1405 ****
s.setconfig AS rolconfig,
pg_authid.oid
FROM (pg_authid
! LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))));
pg_rules| SELECT n.nspname AS schemaname,
c.relname AS tablename,
r.rulename,
pg_get_ruledef(r.oid) AS definition
FROM ((pg_rewrite r
! JOIN pg_class c ON ((c.oid = r.ev_class)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (r.rulename <> '_RETURN'::name);
pg_seclabels| ( ( ( ( ( ( ( ( ( SELECT
l.objoid,
l.classoid,
--- 1392,1405 ----
s.setconfig AS rolconfig,
pg_authid.oid
FROM (pg_authid
! LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))));
pg_rules| SELECT n.nspname AS schemaname,
c.relname AS tablename,
r.rulename,
pg_get_ruledef(r.oid) AS definition
FROM ((pg_rewrite r
! JOIN pg_class c ON ((c.oid = r.ev_class)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (r.rulename <> '_RETURN'::name);
pg_seclabels| ( ( ( ( ( ( ( ( ( SELECT
l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1420,1428 ****
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_class rel ON (((l.classoid =
rel.tableoid)AND (l.objoid = rel.oid))))
! JOIN pg_namespace nsp ON ((rel.relnamespace
=nsp.oid)))
! WHERE (l.objsubid = 0)
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1420,1428 ----
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_class rel ON
(((l.classoid= rel.tableoid) AND (l.objoid = rel.oid))))
! JOIN pg_namespace nsp ON
((rel.relnamespace= nsp.oid)))
! WHERE (l.objsubid = 0)
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1437,1446 ****
l.provider,
l.label
FROM (((pg_seclabel l
! JOIN pg_class rel ON (((l.classoid =
rel.tableoid)AND (l.objoid = rel.oid))))
! JOIN pg_attribute att ON (((rel.oid =
att.attrelid)AND (l.objsubid = att.attnum))))
! JOIN pg_namespace nsp ON ((rel.relnamespace =
nsp.oid)))
! WHERE (l.objsubid <> 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1437,1446 ----
l.provider,
l.label
FROM (((pg_seclabel l
! JOIN pg_class rel ON
(((l.classoid= rel.tableoid) AND (l.objoid = rel.oid))))
! JOIN pg_attribute att ON
(((rel.oid= att.attrelid) AND (l.objsubid = att.attnum))))
! JOIN pg_namespace nsp ON
((rel.relnamespace= nsp.oid)))
! WHERE (l.objsubid <> 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1459,1467 ****
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_proc pro ON (((l.classoid =
pro.tableoid)AND (l.objoid = pro.oid))))
! JOIN pg_namespace nsp ON ((pro.pronamespace =
nsp.oid)))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1459,1467 ----
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_proc pro ON (((l.classoid =
pro.tableoid)AND (l.objoid = pro.oid))))
! JOIN pg_namespace nsp ON
((pro.pronamespace= nsp.oid)))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1478,1486 ****
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_type typ ON (((l.classoid = typ.tableoid) AND
(l.objoid= typ.oid))))
! JOIN pg_namespace nsp ON ((typ.typnamespace = nsp.oid)))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1478,1486 ----
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_type typ ON (((l.classoid =
typ.tableoid)AND (l.objoid = typ.oid))))
! JOIN pg_namespace nsp ON ((typ.typnamespace =
nsp.oid)))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1491,1498 ****
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_largeobject_metadata lom ON ((l.objoid = lom.oid)))
! WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND
(l.objsubid= 0)))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1491,1498 ----
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_largeobject_metadata lom ON ((l.objoid =
lom.oid)))
! WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND
(l.objsubid= 0)))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1503,1510 ****
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND (l.objoid =
lan.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1503,1510 ----
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND
(l.objoid= lan.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1515,1522 ****
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid =
nsp.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1515,1522 ----
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid
=nsp.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1527,1534 ****
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_event_trigger evt ON (((l.classoid = evt.tableoid) AND (l.objoid = evt.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1527,1534 ----
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_event_trigger evt ON (((l.classoid = evt.tableoid) AND (l.objoid =
evt.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1539,1545 ****
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_database dat ON (((l.classoid = dat.tableoid) AND (l.objoid = dat.oid)))))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1539,1545 ----
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_database dat ON (((l.classoid = dat.tableoid) AND (l.objoid = dat.oid)))))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1550,1556 ****
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_tablespace spc ON (((l.classoid = spc.tableoid) AND (l.objoid = spc.oid)))))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1550,1556 ----
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_tablespace spc ON (((l.classoid = spc.tableoid) AND (l.objoid = spc.oid)))))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** UNION ALL
*** 1561,1567 ****
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid))));
pg_settings| SELECT a.name,
a.setting,
a.unit,
--- 1561,1567 ----
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid))));
pg_settings| SELECT a.name,
a.setting,
a.unit,
*************** pg_shadow| SELECT pg_authid.rolname AS u
*** 1589,1595 ****
(pg_authid.rolvaliduntil)::abstime AS valuntil,
s.setconfig AS useconfig
FROM (pg_authid
! LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))))
WHERE pg_authid.rolcanlogin;
pg_stat_activity| SELECT s.datid,
d.datname,
--- 1589,1595 ----
(pg_authid.rolvaliduntil)::abstime AS valuntil,
s.setconfig AS useconfig
FROM (pg_authid
! LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))))
WHERE pg_authid.rolcanlogin;
pg_stat_activity| SELECT s.datid,
d.datname,
*************** pg_stat_all_indexes| SELECT c.oid AS rel
*** 1622,1630 ****
pg_stat_get_tuples_returned(i.oid) AS idx_tup_read,
pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch
FROM (((pg_class c
! JOIN pg_index x ON ((c.oid = x.indrelid)))
! JOIN pg_class i ON ((i.oid = x.indexrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
pg_stat_all_tables| SELECT c.oid AS relid,
n.nspname AS schemaname,
--- 1622,1630 ----
pg_stat_get_tuples_returned(i.oid) AS idx_tup_read,
pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch
FROM (((pg_class c
! JOIN pg_index x ON ((c.oid = x.indrelid)))
! JOIN pg_class i ON ((i.oid = x.indexrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
pg_stat_all_tables| SELECT c.oid AS relid,
n.nspname AS schemaname,
*************** pg_stat_all_tables| SELECT c.oid AS reli
*** 1649,1656 ****
pg_stat_get_analyze_count(c.oid) AS analyze_count,
pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count
FROM ((pg_class c
! LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
GROUP BY c.oid, n.nspname, c.relname;
pg_stat_archiver| SELECT s.archived_count,
--- 1649,1656 ----
pg_stat_get_analyze_count(c.oid) AS analyze_count,
pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count
FROM ((pg_class c
! LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
GROUP BY c.oid, n.nspname, c.relname;
pg_stat_archiver| SELECT s.archived_count,
*************** pg_stat_user_functions| SELECT p.oid AS
*** 1761,1767 ****
pg_stat_get_function_total_time(p.oid) AS total_time,
pg_stat_get_function_self_time(p.oid) AS self_time
FROM (pg_proc p
! LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace)))
WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL));
pg_stat_user_indexes| SELECT pg_stat_all_indexes.relid,
pg_stat_all_indexes.indexrelid,
--- 1761,1767 ----
pg_stat_get_function_total_time(p.oid) AS total_time,
pg_stat_get_function_self_time(p.oid) AS self_time
FROM (pg_proc p
! LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace)))
WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL));
pg_stat_user_indexes| SELECT pg_stat_all_indexes.relid,
pg_stat_all_indexes.indexrelid,
*************** pg_stat_xact_all_tables| SELECT c.oid AS
*** 1809,1816 ****
pg_stat_get_xact_tuples_deleted(c.oid) AS n_tup_del,
pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd
FROM ((pg_class c
! LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
GROUP BY c.oid, n.nspname, c.relname;
pg_stat_xact_sys_tables| SELECT pg_stat_xact_all_tables.relid,
--- 1809,1816 ----
pg_stat_get_xact_tuples_deleted(c.oid) AS n_tup_del,
pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd
FROM ((pg_class c
! LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
GROUP BY c.oid, n.nspname, c.relname;
pg_stat_xact_sys_tables| SELECT pg_stat_xact_all_tables.relid,
*************** pg_stat_xact_user_functions| SELECT p.oi
*** 1833,1839 ****
pg_stat_get_xact_function_total_time(p.oid) AS total_time,
pg_stat_get_xact_function_self_time(p.oid) AS self_time
FROM (pg_proc p
! LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace)))
WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_xact_function_calls(p.oid) IS NOT NULL));
pg_stat_xact_user_tables| SELECT pg_stat_xact_all_tables.relid,
pg_stat_xact_all_tables.schemaname,
--- 1833,1839 ----
pg_stat_get_xact_function_total_time(p.oid) AS total_time,
pg_stat_get_xact_function_self_time(p.oid) AS self_time
FROM (pg_proc p
! LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace)))
WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_xact_function_calls(p.oid) IS NOT NULL));
pg_stat_xact_user_tables| SELECT pg_stat_xact_all_tables.relid,
pg_stat_xact_all_tables.schemaname,
*************** pg_statio_all_indexes| SELECT c.oid AS r
*** 1856,1864 ****
(pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read,
pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit
FROM (((pg_class c
! JOIN pg_index x ON ((c.oid = x.indrelid)))
! JOIN pg_class i ON ((i.oid = x.indexrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
pg_statio_all_sequences| SELECT c.oid AS relid,
n.nspname AS schemaname,
--- 1856,1864 ----
(pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read,
pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit
FROM (((pg_class c
! JOIN pg_index x ON ((c.oid = x.indrelid)))
! JOIN pg_class i ON ((i.oid = x.indexrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
pg_statio_all_sequences| SELECT c.oid AS relid,
n.nspname AS schemaname,
*************** pg_statio_all_sequences| SELECT c.oid AS
*** 1866,1872 ****
(pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read,
pg_stat_get_blocks_hit(c.oid) AS blks_hit
FROM (pg_class c
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = 'S'::"char");
pg_statio_all_tables| SELECT c.oid AS relid,
n.nspname AS schemaname,
--- 1866,1872 ----
(pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read,
pg_stat_get_blocks_hit(c.oid) AS blks_hit
FROM (pg_class c
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = 'S'::"char");
pg_statio_all_tables| SELECT c.oid AS relid,
n.nspname AS schemaname,
*************** pg_statio_all_tables| SELECT c.oid AS re
*** 1880,1889 ****
(sum((pg_stat_get_blocks_fetched(x.indexrelid) - pg_stat_get_blocks_hit(x.indexrelid))))::bigint AS
tidx_blks_read,
(sum(pg_stat_get_blocks_hit(x.indexrelid)))::bigint AS tidx_blks_hit
FROM ((((pg_class c
! LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
! LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid)))
! LEFT JOIN pg_index x ON ((t.oid = x.indrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
GROUP BY c.oid, n.nspname, c.relname, t.oid, x.indrelid;
pg_statio_sys_indexes| SELECT pg_statio_all_indexes.relid,
--- 1880,1889 ----
(sum((pg_stat_get_blocks_fetched(x.indexrelid) - pg_stat_get_blocks_hit(x.indexrelid))))::bigint AS
tidx_blks_read,
(sum(pg_stat_get_blocks_hit(x.indexrelid)))::bigint AS tidx_blks_hit
FROM ((((pg_class c
! LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
! LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid)))
! LEFT JOIN pg_index x ON ((t.oid = x.indrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
GROUP BY c.oid, n.nspname, c.relname, t.oid, x.indrelid;
pg_statio_sys_indexes| SELECT pg_statio_all_indexes.relid,
*************** pg_stats| SELECT n.nspname AS schemaname
*** 2008,2016 ****
ELSE NULL::real[]
END AS elem_count_histogram
FROM (((pg_statistic s
! JOIN pg_class c ON ((c.oid = s.starelid)))
! JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum))))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text));
pg_tables| SELECT n.nspname AS schemaname,
c.relname AS tablename,
--- 2008,2016 ----
ELSE NULL::real[]
END AS elem_count_histogram
FROM (((pg_statistic s
! JOIN pg_class c ON ((c.oid = s.starelid)))
! JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum))))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text));
pg_tables| SELECT n.nspname AS schemaname,
c.relname AS tablename,
*************** pg_tables| SELECT n.nspname AS schemanam
*** 2020,2027 ****
c.relhasrules AS hasrules,
c.relhastriggers AS hastriggers
FROM ((pg_class c
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
! LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace)))
WHERE (c.relkind = 'r'::"char");
pg_timezone_abbrevs| SELECT pg_timezone_abbrevs.abbrev,
pg_timezone_abbrevs.utc_offset,
--- 2020,2027 ----
c.relhasrules AS hasrules,
c.relhastriggers AS hastriggers
FROM ((pg_class c
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
! LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace)))
WHERE (c.relkind = 'r'::"char");
pg_timezone_abbrevs| SELECT pg_timezone_abbrevs.abbrev,
pg_timezone_abbrevs.utc_offset,
*************** pg_user_mappings| SELECT u.oid AS umid,
*** 2055,2068 ****
ELSE NULL::text[]
END AS umoptions
FROM ((pg_user_mapping u
! LEFT JOIN pg_authid a ON ((a.oid = u.umuser)))
! JOIN pg_foreign_server s ON ((u.umserver = s.oid)));
pg_views| SELECT n.nspname AS schemaname,
c.relname AS viewname,
pg_get_userbyid(c.relowner) AS viewowner,
pg_get_viewdef(c.oid) AS definition
FROM (pg_class c
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = 'v'::"char");
rtest_v1| SELECT rtest_t1.a,
rtest_t1.b
--- 2055,2068 ----
ELSE NULL::text[]
END AS umoptions
FROM ((pg_user_mapping u
! LEFT JOIN pg_authid a ON ((a.oid = u.umuser)))
! JOIN pg_foreign_server s ON ((u.umserver = s.oid)));
pg_views| SELECT n.nspname AS schemaname,
c.relname AS viewname,
pg_get_userbyid(c.relowner) AS viewowner,
pg_get_viewdef(c.oid) AS definition
FROM (pg_class c
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = 'v'::"char");
rtest_v1| SELECT rtest_t1.a,
rtest_t1.b
В списке pgsql-hackers по дате отправления: