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;