Re: pg_dump --pretty-print-views - Mailing list pgsql-hackers
| From | Tom Lane |
|---|---|
| Subject | Re: pg_dump --pretty-print-views |
| Date | |
| Msg-id | 24926.1398806574@sss.pgh.pa.us Whole thread Raw |
| In response to | Re: pg_dump --pretty-print-views (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: pg_dump --pretty-print-views
Re: pg_dump --pretty-print-views |
| List | pgsql-hackers |
I wrote:
> I'm still a bit skeptical about this being a catastrophic problem in
> practice ... but anyway, I thought there were two somewhat different
> proposals on the table in that thread:
> 1. Arrange for "x UNION y UNION z ..." to put all the UNION arms at
> the same indentation level.
> 2. Change the indentation rules globally, in one or another fashion
> as Greg mentions above, to prevent ruleutils from ever prepending
> silly amounts of whitespace.
> These are not mutually exclusive, and I think we should do both.
Here's a draft patch tackling point 1. This gets rid of a whole lot
of parenthesization, as well as indentation, for simple UNION lists.
You can see the results in the changed regression test outputs.
There are still a few oddities in the printout format, such as the
forced space between ( and SELECT at the start of a subquery;
but changing that would require touching more than just
get_setop_query(), and I'm not too sure what else would be affected,
so I let it be.
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.
Comments?
regards, tom lane
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index ea7b8c5..73e09ae 100644
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
*************** get_setop_query(Node *setOp, Query *quer
*** 4713,4754 ****
else if (IsA(setOp, SetOperationStmt))
{
SetOperationStmt *op = (SetOperationStmt *) setOp;
!
! if (PRETTY_INDENT(context))
! {
! context->indentLevel += PRETTYINDENT_STD;
! appendStringInfoSpaces(buf, PRETTYINDENT_STD);
! }
/*
! * We force parens whenever nesting two SetOperationStmts. There are
! * some cases in which parens are needed around a leaf query too, but
! * those are more easily handled at the next level down (see code
! * above).
*/
! need_paren = !IsA(op->larg, RangeTblRef);
if (need_paren)
appendStringInfoChar(buf, '(');
get_setop_query(op->larg, query, context, resultDesc);
- if (need_paren)
- appendStringInfoChar(buf, ')');
! if (!PRETTY_INDENT(context))
appendStringInfoChar(buf, ' ');
switch (op->op)
{
case SETOP_UNION:
! appendContextKeyword(context, "UNION ",
! -PRETTYINDENT_STD, PRETTYINDENT_STD, 0);
break;
case SETOP_INTERSECT:
! appendContextKeyword(context, "INTERSECT ",
! -PRETTYINDENT_STD, PRETTYINDENT_STD, 0);
break;
case SETOP_EXCEPT:
! appendContextKeyword(context, "EXCEPT ",
! -PRETTYINDENT_STD, PRETTYINDENT_STD, 0);
break;
default:
elog(ERROR, "unrecognized set op: %d",
--- 4713,4767 ----
else if (IsA(setOp, SetOperationStmt))
{
SetOperationStmt *op = (SetOperationStmt *) setOp;
! int subindent;
/*
! * We force parens when nesting two SetOperationStmts, except when the
! * lefthand input is another setop of the same kind. Syntactically,
! * we could omit parens in rather more cases, but it seems best to use
! * parens to flag cases where the setop operator changes.
! *
! * There are some cases in which parens are needed around a leaf query
! * too, but those are more easily handled at the next level down (see
! * code above).
*/
! if (IsA(op->larg, SetOperationStmt))
! {
! SetOperationStmt *lop = (SetOperationStmt *) op->larg;
!
! if (op->op == lop->op && op->all == lop->all)
! need_paren = false;
! else
! need_paren = true;
! }
! else
! need_paren = false;
if (need_paren)
+ {
appendStringInfoChar(buf, '(');
+ appendContextKeyword(context, "", PRETTYINDENT_STD, 0, 0);
+ }
+
get_setop_query(op->larg, query, context, resultDesc);
! if (need_paren)
! appendContextKeyword(context, ") ", -PRETTYINDENT_STD, 0, 0);
! else if (PRETTY_INDENT(context))
! appendContextKeyword(context, "", 0, 0, 0);
! else
appendStringInfoChar(buf, ' ');
+
switch (op->op)
{
case SETOP_UNION:
! appendStringInfoString(buf, "UNION ");
break;
case SETOP_INTERSECT:
! appendStringInfoString(buf, "INTERSECT ");
break;
case SETOP_EXCEPT:
! appendStringInfoString(buf, "EXCEPT ");
break;
default:
elog(ERROR, "unrecognized set op: %d",
*************** get_setop_query(Node *setOp, Query *quer
*** 4757,4775 ****
if (op->all)
appendStringInfoString(buf, "ALL ");
- if (PRETTY_INDENT(context))
- appendContextKeyword(context, "", 0, 0, 0);
-
need_paren = !IsA(op->rarg, RangeTblRef);
if (need_paren)
appendStringInfoChar(buf, '(');
get_setop_query(op->rarg, query, context, resultDesc);
- if (need_paren)
- appendStringInfoChar(buf, ')');
-
if (PRETTY_INDENT(context))
! context->indentLevel -= PRETTYINDENT_STD;
}
else
{
--- 4770,4791 ----
if (op->all)
appendStringInfoString(buf, "ALL ");
need_paren = !IsA(op->rarg, RangeTblRef);
if (need_paren)
+ {
appendStringInfoChar(buf, '(');
+ subindent = PRETTYINDENT_STD;
+ }
+ else
+ subindent = 0;
+ if (PRETTY_INDENT(context))
+ appendContextKeyword(context, "", subindent, 0, 0);
get_setop_query(op->rarg, query, context, resultDesc);
if (PRETTY_INDENT(context))
! context->indentLevel -= subindent;
! if (need_paren)
! appendContextKeyword(context, ")", 0, 0, 0);
}
else
{
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index f6db582..bf3c187 100644
*** a/src/test/regress/expected/create_view.out
--- b/src/test/regress/expected/create_view.out
*************** select * from (values(1,2,3,4,5)) v(a,b,
*** 1090,1112 ****
union all
select * from tt7 full join tt8 using (x), tt8 tt8x;
select pg_get_viewdef('vv2', true);
! pg_get_viewdef
! --------------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.d, +
! v.e +
! FROM ( VALUES (1,2,3,4,5)) v(a, b, c, d, e)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! 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
--- 1090,1112 ----
union all
select * from tt7 full join tt8 using (x), tt8 tt8x;
select pg_get_viewdef('vv2', true);
! pg_get_viewdef
! ------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.d, +
! v.e +
! FROM ( VALUES (1,2,3,4,5)) v(a, b, c, d, e)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! 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 * from
*** 1116,1140 ****
tt7 full join tt8 using (x),
tt7 tt7x full join tt8 tt8x using (x);
select pg_get_viewdef('vv3', true);
! pg_get_viewdef
! -------------------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.x, +
! v.e, +
! v.f +
! FROM ( VALUES (1,2,3,4,5,6)) v(a, b, c, x, e, f)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! x_1 AS x, +
! 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)
--- 1116,1140 ----
tt7 full join tt8 using (x),
tt7 tt7x full join tt8 tt8x using (x);
select pg_get_viewdef('vv3', true);
! pg_get_viewdef
! -----------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.x, +
! v.e, +
! v.f +
! FROM ( VALUES (1,2,3,4,5,6)) v(a, b, c, x, e, f)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! x_1 AS x, +
! 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)
*************** select * from
*** 1145,1172 ****
tt7 full join tt8 using (x),
tt7 tt7x full join tt8 tt8x using (x) full join tt8 tt8y using (x);
select pg_get_viewdef('vv4', true);
! pg_get_viewdef
! ------------------------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.x, +
! v.e, +
! v.f, +
! v.g +
! FROM ( VALUES (1,2,3,4,5,6,7)) v(a, b, c, x, e, f, g)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! x_1 AS x, +
! tt7x.y AS e, +
! 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)
--- 1145,1172 ----
tt7 full join tt8 using (x),
tt7 tt7x full join tt8 tt8x using (x) full join tt8 tt8y using (x);
select pg_get_viewdef('vv4', true);
! pg_get_viewdef
! ----------------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.x, +
! v.e, +
! v.f, +
! v.g +
! FROM ( VALUES (1,2,3,4,5,6,7)) v(a, b, c, x, e, f, g)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! x_1 AS x, +
! tt7x.y AS e, +
! 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 z int;
*** 1175,1245 ****
alter table tt7 drop column zz;
alter table tt8 add column z2 int;
select pg_get_viewdef('vv2', true);
! pg_get_viewdef
! --------------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.d, +
! v.e +
! FROM ( VALUES (1,2,3,4,5)) v(a, b, c, d, e)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! 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);
! pg_get_viewdef
! -------------------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.x, +
! v.e, +
! v.f +
! FROM ( VALUES (1,2,3,4,5,6)) v(a, b, c, x, e, f)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! x_1 AS x, +
! 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);
! pg_get_viewdef
! ------------------------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.x, +
! v.e, +
! v.f, +
! v.g +
! FROM ( VALUES (1,2,3,4,5,6,7)) v(a, b, c, x, e, f, g)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! x_1 AS x, +
! tt7x.y AS e, +
! 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)
--- 1175,1245 ----
alter table tt7 drop column zz;
alter table tt8 add column z2 int;
select pg_get_viewdef('vv2', true);
! pg_get_viewdef
! ------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.d, +
! v.e +
! FROM ( VALUES (1,2,3,4,5)) v(a, b, c, d, e)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! 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);
! pg_get_viewdef
! -----------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.x, +
! v.e, +
! v.f +
! FROM ( VALUES (1,2,3,4,5,6)) v(a, b, c, x, e, f)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! x_1 AS x, +
! 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);
! pg_get_viewdef
! ----------------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.x, +
! v.e, +
! v.f, +
! v.g +
! FROM ( VALUES (1,2,3,4,5,6,7)) v(a, b, c, x, e, f, g)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! x_1 AS x, +
! tt7x.y AS e, +
! 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)
*************** select * from (values(now(),2,3,now(),5)
*** 1252,1274 ****
union all
select * from tt7a left join tt8a using (x), tt8a tt8ax;
select pg_get_viewdef('vv2a', true);
! pg_get_viewdef
! ----------------------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.d, +
! v.e +
! FROM ( VALUES (now(),2,3,now(),5)) v(a, b, c, d, e)+
! UNION ALL +
! SELECT x AS a, +
! tt7a.y AS b, +
! tt8a.z AS c, +
! tt8ax.x_1 AS d, +
! tt8ax.z AS e +
! FROM tt7a +
! LEFT JOIN tt8a USING (x), +
! tt8a tt8ax(x_1, z);
(1 row)
--
--- 1252,1274 ----
union all
select * from tt7a left join tt8a using (x), tt8a tt8ax;
select pg_get_viewdef('vv2a', true);
! pg_get_viewdef
! --------------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.d, +
! v.e +
! FROM ( VALUES (now(),2,3,now(),5)) v(a, b, c, d, e)+
! UNION ALL +
! SELECT x AS a, +
! tt7a.y AS b, +
! tt8a.z AS c, +
! 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/matview.out b/src/test/regress/expected/matview.out
index daf3b9e..ddac97b 100644
*** a/src/test/regress/expected/matview.out
--- b/src/test/regress/expected/matview.out
*************** CREATE VIEW v_test2 AS SELECT moo, 2*moo
*** 346,358 ****
moo | integer | | plain |
?column? | integer | | plain |
View definition:
! SELECT v_test1.moo,
! 2 * v_test1.moo
! FROM v_test1
UNION ALL
! SELECT v_test1.moo,
! 3 * v_test1.moo
! FROM v_test1;
CREATE MATERIALIZED VIEW mv_test2 AS SELECT moo, 2*moo FROM v_test2 UNION ALL SELECT moo, 3*moo FROM v_test2;
\d+ mv_test2
--- 346,358 ----
moo | integer | | plain |
?column? | integer | | plain |
View definition:
! SELECT v_test1.moo,
! 2 * v_test1.moo
! FROM v_test1
UNION ALL
! SELECT v_test1.moo,
! 3 * v_test1.moo
! FROM v_test1;
CREATE MATERIALIZED VIEW mv_test2 AS SELECT moo, 2*moo FROM v_test2 UNION ALL SELECT moo, 3*moo FROM v_test2;
\d+ mv_test2
*************** CREATE MATERIALIZED VIEW mv_test2 AS SEL
*** 362,374 ****
moo | integer | | plain | |
?column? | integer | | plain | |
View definition:
! SELECT v_test2.moo,
! 2 * v_test2.moo
! FROM v_test2
UNION ALL
! SELECT v_test2.moo,
! 3 * v_test2.moo
! FROM v_test2;
CREATE MATERIALIZED VIEW mv_test3 AS SELECT * FROM mv_test2 WHERE moo = 12345;
SELECT relispopulated FROM pg_class WHERE oid = 'mv_test3'::regclass;
--- 362,374 ----
moo | integer | | plain | |
?column? | integer | | plain | |
View definition:
! SELECT v_test2.moo,
! 2 * v_test2.moo
! FROM v_test2
UNION ALL
! SELECT v_test2.moo,
! 3 * v_test2.moo
! FROM v_test2;
CREATE MATERIALIZED VIEW mv_test3 AS SELECT * FROM mv_test2 WHERE moo = 12345;
SELECT relispopulated FROM pg_class WHERE oid = 'mv_test3'::regclass;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 6c51d0d..eb6066c 100644
*** a/src/test/regress/expected/rules.out
--- b/src/test/regress/expected/rules.out
*************** pg_rules| SELECT n.nspname AS schemaname
*** 1401,1567 ****
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,
! l.objsubid,
! CASE
! WHEN (rel.relkind =
'r'::"char")THEN 'table'::text
! WHEN (rel.relkind =
'v'::"char")THEN 'view'::text
! WHEN (rel.relkind =
'm'::"char")THEN 'materialized view'::text
! WHEN (rel.relkind =
'S'::"char")THEN 'sequence'::text
! WHEN (rel.relkind =
'f'::"char")THEN 'foreign table'::text
! ELSE NULL::text
! END AS objtype,
! rel.relnamespace AS objnamespace,
! CASE
! WHEN
pg_table_is_visible(rel.oid)THEN quote_ident((rel.relname)::text)
! ELSE
((quote_ident((nsp.nspname)::text)|| '.'::text) || quote_ident((rel.relname)::text))
! END AS objname,
! 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,
! l.objsubid,
! 'column'::text AS objtype,
! rel.relnamespace AS objnamespace,
! ((
! CASE
! WHEN
pg_table_is_visible(rel.oid)THEN quote_ident((rel.relname)::text)
! ELSE
((quote_ident((nsp.nspname)::text)|| '.'::text) || quote_ident((rel.relname)::text))
! END || '.'::text) ||
(att.attname)::text)AS objname,
! 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,
! l.objsubid,
! CASE
! WHEN (pro.proisagg = true) THEN
'aggregate'::text
! WHEN (pro.proisagg = false) THEN
'function'::text
! ELSE NULL::text
! END AS objtype,
! pro.pronamespace AS objnamespace,
! (((
! CASE
! WHEN
pg_function_is_visible(pro.oid)THEN quote_ident((pro.proname)::text)
! ELSE
((quote_ident((nsp.nspname)::text)|| '.'::text) || quote_ident((pro.proname)::text))
! END || '('::text) ||
pg_get_function_arguments(pro.oid))|| ')'::text) AS objname,
! 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,
! l.objsubid,
! CASE
! WHEN (typ.typtype = 'd'::"char") THEN
'domain'::text
! ELSE 'type'::text
! END AS objtype,
! typ.typnamespace AS objnamespace,
! CASE
! WHEN pg_type_is_visible(typ.oid) THEN
quote_ident((typ.typname)::text)
! ELSE ((quote_ident((nsp.nspname)::text)
||'.'::text) || quote_ident((typ.typname)::text))
! END AS objname,
! 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,
! l.objsubid,
! 'large object'::text AS objtype,
! NULL::oid AS objnamespace,
! (l.objoid)::text AS objname,
! 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,
! l.objsubid,
! 'language'::text AS objtype,
! NULL::oid AS objnamespace,
! quote_ident((lan.lanname)::text) AS objname,
! 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,
! l.objsubid,
! 'schema'::text AS objtype,
! nsp.oid AS objnamespace,
! quote_ident((nsp.nspname)::text) AS objname,
! 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,
! l.objsubid,
! 'event trigger'::text AS objtype,
! NULL::oid AS objnamespace,
! quote_ident((evt.evtname)::text) AS objname,
! 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,
! 0 AS objsubid,
! 'database'::text AS objtype,
! NULL::oid AS objnamespace,
! quote_ident((dat.datname)::text) AS objname,
! 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,
! 0 AS objsubid,
! 'tablespace'::text AS objtype,
! NULL::oid AS objnamespace,
! quote_ident((spc.spcname)::text) AS objname,
! 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,
! 0 AS objsubid,
! 'role'::text AS objtype,
! NULL::oid AS objnamespace,
! quote_ident((rol.rolname)::text) AS objname,
! 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,
--- 1401,1567 ----
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,
! l.objsubid,
! CASE
! WHEN (rel.relkind = 'r'::"char") THEN 'table'::text
! WHEN (rel.relkind = 'v'::"char") THEN 'view'::text
! WHEN (rel.relkind = 'm'::"char") THEN 'materialized view'::text
! WHEN (rel.relkind = 'S'::"char") THEN 'sequence'::text
! WHEN (rel.relkind = 'f'::"char") THEN 'foreign table'::text
! ELSE NULL::text
! END AS objtype,
! rel.relnamespace AS objnamespace,
! CASE
! WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text)
! ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text))
! END AS objname,
! 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,
! l.objsubid,
! 'column'::text AS objtype,
! rel.relnamespace AS objnamespace,
! ((
! CASE
! WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text)
! ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text))
! END || '.'::text) || (att.attname)::text) AS objname,
! 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,
! l.objsubid,
! CASE
! WHEN (pro.proisagg = true) THEN 'aggregate'::text
! WHEN (pro.proisagg = false) THEN 'function'::text
! ELSE NULL::text
! END AS objtype,
! pro.pronamespace AS objnamespace,
! (((
! CASE
! WHEN pg_function_is_visible(pro.oid) THEN quote_ident((pro.proname)::text)
! ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((pro.proname)::text))
! END || '('::text) || pg_get_function_arguments(pro.oid)) || ')'::text) AS objname,
! 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,
! l.objsubid,
! CASE
! WHEN (typ.typtype = 'd'::"char") THEN 'domain'::text
! ELSE 'type'::text
! END AS objtype,
! typ.typnamespace AS objnamespace,
! CASE
! WHEN pg_type_is_visible(typ.oid) THEN quote_ident((typ.typname)::text)
! ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((typ.typname)::text))
! END AS objname,
! 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,
! l.objsubid,
! 'large object'::text AS objtype,
! NULL::oid AS objnamespace,
! (l.objoid)::text AS objname,
! 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,
! l.objsubid,
! 'language'::text AS objtype,
! NULL::oid AS objnamespace,
! quote_ident((lan.lanname)::text) AS objname,
! 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,
! l.objsubid,
! 'schema'::text AS objtype,
! nsp.oid AS objnamespace,
! quote_ident((nsp.nspname)::text) AS objname,
! 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,
! l.objsubid,
! 'event trigger'::text AS objtype,
! NULL::oid AS objnamespace,
! quote_ident((evt.evtname)::text) AS objname,
! 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,
! 0 AS objsubid,
! 'database'::text AS objtype,
! NULL::oid AS objnamespace,
! quote_ident((dat.datname)::text) AS objname,
! 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,
! 0 AS objsubid,
! 'tablespace'::text AS objtype,
! NULL::oid AS objnamespace,
! quote_ident((spc.spcname)::text) AS objname,
! 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,
! 0 AS objsubid,
! 'role'::text AS objtype,
! NULL::oid AS objnamespace,
! quote_ident((rol.rolname)::text) AS objname,
! 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/test/regress/expected/with.out b/src/test/regress/expected/with.out
index d76ef4e..06b372b 100644
*** a/src/test/regress/expected/with.out
--- b/src/test/regress/expected/with.out
*************** SELECT * FROM vsubdepartment ORDER BY na
*** 300,347 ****
-- Check reverse listing
SELECT pg_get_viewdef('vsubdepartment'::regclass);
! pg_get_viewdef
! -------------------------------------------------------
! WITH RECURSIVE subdepartment AS ( +
! SELECT department.id, +
! department.parent_department, +
! department.name +
! FROM department +
! WHERE (department.name = 'A'::text)+
! UNION ALL +
! SELECT d.id, +
! d.parent_department, +
! d.name +
! FROM department d, +
! subdepartment sd +
! WHERE (d.parent_department = sd.id)+
! ) +
! SELECT subdepartment.id, +
! subdepartment.parent_department, +
! subdepartment.name +
FROM subdepartment;
(1 row)
SELECT pg_get_viewdef('vsubdepartment'::regclass, true);
! pg_get_viewdef
! -----------------------------------------------------
! WITH RECURSIVE subdepartment AS ( +
! SELECT department.id, +
! department.parent_department, +
! department.name +
! FROM department +
! WHERE department.name = 'A'::text+
! UNION ALL +
! SELECT d.id, +
! d.parent_department, +
! d.name +
! FROM department d, +
! subdepartment sd +
! WHERE d.parent_department = sd.id+
! ) +
! SELECT subdepartment.id, +
! subdepartment.parent_department, +
! subdepartment.name +
FROM subdepartment;
(1 row)
--- 300,347 ----
-- Check reverse listing
SELECT pg_get_viewdef('vsubdepartment'::regclass);
! pg_get_viewdef
! -----------------------------------------------
! WITH RECURSIVE subdepartment AS ( +
! SELECT department.id, +
! department.parent_department, +
! department.name +
! FROM department +
! WHERE (department.name = 'A'::text)+
! UNION ALL +
! SELECT d.id, +
! d.parent_department, +
! d.name +
! FROM department d, +
! subdepartment sd +
! WHERE (d.parent_department = sd.id)+
! ) +
! SELECT subdepartment.id, +
! subdepartment.parent_department, +
! subdepartment.name +
FROM subdepartment;
(1 row)
SELECT pg_get_viewdef('vsubdepartment'::regclass, true);
! pg_get_viewdef
! ---------------------------------------------
! WITH RECURSIVE subdepartment AS ( +
! SELECT department.id, +
! department.parent_department, +
! department.name +
! FROM department +
! WHERE department.name = 'A'::text+
! UNION ALL +
! SELECT d.id, +
! d.parent_department, +
! d.name +
! FROM department d, +
! subdepartment sd +
! WHERE d.parent_department = sd.id+
! ) +
! SELECT subdepartment.id, +
! subdepartment.parent_department, +
! subdepartment.name +
FROM subdepartment;
(1 row)
*************** SELECT sum(n) FROM t;
*** 360,370 ****
sum | bigint | | plain |
View definition:
WITH RECURSIVE t(n) AS (
! VALUES (1)
UNION ALL
! SELECT t_1.n + 1
! FROM t t_1
! WHERE t_1.n < 100
)
SELECT sum(t.n) AS sum
FROM t;
--- 360,370 ----
sum | bigint | | plain |
View definition:
WITH RECURSIVE t(n) AS (
! VALUES (1)
UNION ALL
! SELECT t_1.n + 1
! FROM t t_1
! WHERE t_1.n < 100
)
SELECT sum(t.n) AS sum
FROM t;
pgsql-hackers by date: