From 62f63bc271483050a7a38f12698102a28956ea15 Mon Sep 17 00:00:00 2001 From: amitlan Date: Fri, 20 Aug 2021 20:05:26 +0900 Subject: [PATCH v3 2/2] Remove UpdateRangeTableOfViewParse() --- .../postgres_fdw/expected/postgres_fdw.out | 16 +- src/backend/commands/view.c | 78 -- src/backend/rewrite/rewriteHandler.c | 29 +- src/bin/pg_dump/t/002_pg_dump.pl | 12 +- src/test/regress/expected/aggregates.out | 26 +- src/test/regress/expected/alter_table.out | 16 +- .../regress/expected/collate.linux.utf8.out | 24 +- src/test/regress/expected/collate.out | 26 +- src/test/regress/expected/create_view.out | 210 ++--- src/test/regress/expected/expressions.out | 12 +- src/test/regress/expected/groupingsets.out | 20 +- src/test/regress/expected/limit.out | 24 +- src/test/regress/expected/matview.out | 24 +- src/test/regress/expected/polymorphism.out | 8 +- src/test/regress/expected/rangefuncs.out | 34 +- src/test/regress/expected/rules.out | 778 +++++++++--------- src/test/regress/expected/tablesample.out | 4 +- src/test/regress/expected/triggers.out | 4 +- src/test/regress/expected/updatable_views.out | 78 +- src/test/regress/expected/window.out | 56 +- src/test/regress/expected/with.out | 32 +- 21 files changed, 722 insertions(+), 789 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index e3ee30f1aa..5ed3c155cd 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -2478,7 +2478,7 @@ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN v5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1 Foreign Scan Output: ft4.c1, ft5.c2, ft5.c1 Relations: (public.ft4) LEFT JOIN (public.ft5) - Remote SQL: SELECT r6.c1, r9.c2, r9.c1 FROM ("S 1"."T 3" r6 LEFT JOIN "S 1"."T 4" r9 ON (((r6.c1 = r9.c1)))) ORDER BY r6.c1 ASC NULLS LAST, r9.c1 ASC NULLS LAST LIMIT 10::bigint OFFSET 10::bigint + Remote SQL: SELECT r6.c1, r7.c2, r7.c1 FROM ("S 1"."T 3" r6 LEFT JOIN "S 1"."T 4" r7 ON (((r6.c1 = r7.c1)))) ORDER BY r6.c1 ASC NULLS LAST, r7.c1 ASC NULLS LAST LIMIT 10::bigint OFFSET 10::bigint (4 rows) SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN v5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; @@ -2541,7 +2541,7 @@ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c Foreign Scan Output: ft4.c1, t2.c2, t2.c1 Relations: (public.ft4) LEFT JOIN (public.ft5 t2) - Remote SQL: SELECT r6.c1, r2.c2, r2.c1 FROM ("S 1"."T 3" r6 LEFT JOIN "S 1"."T 4" r2 ON (((r6.c1 = r2.c1)))) ORDER BY r6.c1 ASC NULLS LAST, r2.c1 ASC NULLS LAST LIMIT 10::bigint OFFSET 10::bigint + Remote SQL: SELECT r5.c1, r2.c2, r2.c1 FROM ("S 1"."T 3" r5 LEFT JOIN "S 1"."T 4" r2 ON (((r5.c1 = r2.c1)))) ORDER BY r5.c1 ASC NULLS LAST, r2.c1 ASC NULLS LAST LIMIT 10::bigint OFFSET 10::bigint (4 rows) SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10; @@ -6346,10 +6346,10 @@ CREATE VIEW rw_view AS SELECT * FROM foreign_tbl a | integer | | | | plain | b | integer | | | | plain | View definition: - SELECT foreign_tbl.a, - foreign_tbl.b + SELECT a, + b FROM foreign_tbl - WHERE foreign_tbl.a < foreign_tbl.b; + WHERE a < b; Options: check_option=cascaded EXPLAIN (VERBOSE, COSTS OFF) @@ -6437,10 +6437,10 @@ CREATE VIEW rw_view AS SELECT * FROM parent_tbl a | integer | | | | plain | b | integer | | | | plain | View definition: - SELECT parent_tbl.a, - parent_tbl.b + SELECT a, + b FROM parent_tbl - WHERE parent_tbl.a < parent_tbl.b; + WHERE a < b; Options: check_option=cascaded EXPLAIN (VERBOSE, COSTS OFF) diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c index 5bfa730e8a..e596683f27 100644 --- a/src/backend/commands/view.c +++ b/src/backend/commands/view.c @@ -323,78 +323,6 @@ DefineViewRules(Oid viewOid, Query *viewParse, bool replace) */ } -/*--------------------------------------------------------------- - * UpdateRangeTableOfViewParse - * - * Update the range table of the given parsetree. - * This update consists of adding two new entries IN THE BEGINNING - * of the range table (otherwise the rule system will die a slow, - * horrible and painful death, and we do not want that now, do we?) - * one for the OLD relation and one for the NEW one (both of - * them refer in fact to the "view" relation). - * - * Of course we must also increase the 'varnos' of all the Var nodes - * by 2... - * - * These extra RT entries are not actually used in the query, - * except for run-time locking and permission checking. - *--------------------------------------------------------------- - */ -static Query * -UpdateRangeTableOfViewParse(Oid viewOid, Query *viewParse) -{ - Relation viewRel; - List *new_rt; - ParseNamespaceItem *nsitem; - RangeTblEntry *rt_entry1, - *rt_entry2; - ParseState *pstate; - - /* - * Make a copy of the given parsetree. It's not so much that we don't - * want to scribble on our input, it's that the parser has a bad habit of - * outputting multiple links to the same subtree for constructs like - * BETWEEN, and we mustn't have OffsetVarNodes increment the varno of a - * Var node twice. copyObject will expand any multiply-referenced subtree - * into multiple copies. - */ - viewParse = copyObject(viewParse); - - /* Create a dummy ParseState for addRangeTableEntryForRelation */ - pstate = make_parsestate(NULL); - - /* need to open the rel for addRangeTableEntryForRelation */ - viewRel = relation_open(viewOid, AccessShareLock); - - /* - * Create the 2 new range table entries and form the new range table... - * OLD first, then NEW.... - */ - nsitem = addRangeTableEntryForRelation(pstate, viewRel, - AccessShareLock, - makeAlias("old", NIL), - false, false); - rt_entry1 = nsitem->p_rte; - nsitem = addRangeTableEntryForRelation(pstate, viewRel, - AccessShareLock, - makeAlias("new", NIL), - false, false); - rt_entry2 = nsitem->p_rte; - - new_rt = lcons(rt_entry1, lcons(rt_entry2, viewParse->rtable)); - - viewParse->rtable = new_rt; - - /* - * Now offset all var nodes by 2, and jointree RT indexes too. - */ - OffsetVarNodes((Node *) viewParse, 2, 0); - - relation_close(viewRel, AccessShareLock); - - return viewParse; -} - /* * DefineView * Execute a CREATE VIEW command. @@ -557,12 +485,6 @@ DefineView(ViewStmt *stmt, const char *queryString, void StoreViewQuery(Oid viewOid, Query *viewParse, bool replace) { - /* - * The range table of 'viewParse' does not contain entries for the "OLD" - * and "NEW" relations. So... add them! - */ - viewParse = UpdateRangeTableOfViewParse(viewOid, viewParse); - /* * Now create the rules associated with the view. */ diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index 92462daa61..1002f1dc11 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -1685,7 +1685,8 @@ ApplyRetrieveRule(Query *parsetree, List *activeRIRs) { Query *rule_action; - RangeTblEntry *rte; + RangeTblEntry *rte, + *subquery_rte; RowMarkClause *rc; if (list_length(rule->actions) != 1) @@ -1803,16 +1804,26 @@ ApplyRetrieveRule(Query *parsetree, * original RTE to a subquery RTE. */ rte = rt_fetch(rt_index, parsetree->rtable); + subquery_rte = rte; - rte->rtekind = RTE_SUBQUERY; - rte->subquery = rule_action; - rte->security_barrier = RelationIsSecurityView(relation); + /* + * Before modifying, store a copy of itself so as to serve as the entry + * to be used by the executor to lock the view relation and for the + * planner to be able to record the view relation OID in the PlannedStmt + * that it produces for the query. + */ + rte = copyObject(rte); + parsetree->rtable = lappend(parsetree->rtable, rte); + + subquery_rte->rtekind = RTE_SUBQUERY; + subquery_rte->subquery = rule_action; + subquery_rte->security_barrier = RelationIsSecurityView(relation); /* Clear fields that should not be set in a subquery RTE */ - rte->relid = InvalidOid; - rte->relkind = 0; - rte->rellockmode = 0; - rte->tablesample = NULL; - rte->inh = false; /* must not be set for a subquery */ + subquery_rte->relid = InvalidOid; + subquery_rte->relkind = 0; + subquery_rte->rellockmode = 0; + subquery_rte->tablesample = NULL; + subquery_rte->inh = false; /* must not be set for a subquery */ return parsetree; } diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index a4ee54d516..03bc29a046 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -2081,7 +2081,7 @@ my %tests = ( SELECT col1 FROM dump_test.test_table;', regexp => qr/^ \QCREATE MATERIALIZED VIEW dump_test.matview AS\E - \n\s+\QSELECT test_table.col1\E + \n\s+\QSELECT col1\E \n\s+\QFROM dump_test.test_table\E \n\s+\QWITH NO DATA;\E /xm, @@ -2097,7 +2097,7 @@ my %tests = ( SELECT * FROM dump_test.matview;', regexp => qr/^ \QCREATE MATERIALIZED VIEW dump_test.matview_second AS\E - \n\s+\QSELECT matview.col1\E + \n\s+\QSELECT col1\E \n\s+\QFROM dump_test.matview\E \n\s+\QWITH NO DATA;\E /xm, @@ -2113,7 +2113,7 @@ my %tests = ( SELECT * FROM dump_test.matview_second WITH NO DATA;', regexp => qr/^ \QCREATE MATERIALIZED VIEW dump_test.matview_third AS\E - \n\s+\QSELECT matview_second.col1\E + \n\s+\QSELECT col1\E \n\s+\QFROM dump_test.matview_second\E \n\s+\QWITH NO DATA;\E /xm, @@ -2129,7 +2129,7 @@ my %tests = ( SELECT * FROM dump_test.matview_third WITH NO DATA;', regexp => qr/^ \QCREATE MATERIALIZED VIEW dump_test.matview_fourth AS\E - \n\s+\QSELECT matview_third.col1\E + \n\s+\QSELECT col1\E \n\s+\QFROM dump_test.matview_third\E \n\s+\QWITH NO DATA;\E /xm, @@ -2147,7 +2147,7 @@ my %tests = ( ALTER COLUMN col2 SET COMPRESSION lz4;', regexp => qr/^ \QCREATE MATERIALIZED VIEW dump_test.matview_compression AS\E - \n\s+\QSELECT test_table.col2\E + \n\s+\QSELECT col2\E \n\s+\QFROM dump_test.test_table\E \n\s+\QWITH NO DATA;\E .* @@ -2952,7 +2952,7 @@ my %tests = ( SELECT col1 FROM dump_test.test_table;', regexp => qr/^ \QCREATE VIEW dump_test.test_view WITH (security_barrier='true') AS\E - \n\s+\QSELECT test_table.col1\E + \n\s+\QSELECT col1\E \n\s+\QFROM dump_test.test_table\E \n\s+\QWITH LOCAL CHECK OPTION;\E/xm, like => diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index 5949996ebc..57df8f7889 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -1535,7 +1535,7 @@ select * from agg_view1; select pg_get_viewdef('agg_view1'::regclass); pg_get_viewdef --------------------------------------------------------------------------------------------------------------------- - SELECT aggfns(v.a, v.b, v.c) AS aggfns + + SELECT aggfns(a, b, c) AS aggfns + FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); (1 row) @@ -1587,7 +1587,7 @@ select * from agg_view1; select pg_get_viewdef('agg_view1'::regclass); pg_get_viewdef --------------------------------------------------------------------------------------------------------------------- - SELECT aggfns(v.a, v.b, v.c ORDER BY (v.b + 1)) AS aggfns + + SELECT aggfns(a, b, c ORDER BY (b + 1)) AS aggfns + FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); (1 row) @@ -1603,7 +1603,7 @@ select * from agg_view1; select pg_get_viewdef('agg_view1'::regclass); pg_get_viewdef --------------------------------------------------------------------------------------------------------------------- - SELECT aggfns(v.a, v.a, v.c ORDER BY v.b) AS aggfns + + SELECT aggfns(a, a, c ORDER BY b) AS aggfns + FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); (1 row) @@ -1619,7 +1619,7 @@ select * from agg_view1; select pg_get_viewdef('agg_view1'::regclass); pg_get_viewdef --------------------------------------------------------------------------------------------------------------------- - SELECT aggfns(v.a, v.b, v.c ORDER BY v.c USING ~<~ NULLS LAST) AS aggfns + + SELECT aggfns(a, b, c ORDER BY c USING ~<~ NULLS LAST) AS aggfns + FROM ( VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c); (1 row) @@ -2104,15 +2104,15 @@ select ten, from tenk1 group by ten order by ten; select pg_get_viewdef('aggordview1'); - pg_get_viewdef -------------------------------------------------------------------------------------------------------------------------------- - SELECT tenk1.ten, + - percentile_disc((0.5)::double precision) WITHIN GROUP (ORDER BY tenk1.thousand) AS p50, + - percentile_disc((0.5)::double precision) WITHIN GROUP (ORDER BY tenk1.thousand) FILTER (WHERE (tenk1.hundred = 1)) AS px,+ - rank(5, 'AZZZZ'::name, 50) WITHIN GROUP (ORDER BY tenk1.hundred, tenk1.string4 DESC, tenk1.hundred) AS rank + - FROM tenk1 + - GROUP BY tenk1.ten + - ORDER BY tenk1.ten; + pg_get_viewdef +------------------------------------------------------------------------------------------------------------------- + SELECT ten, + + percentile_disc((0.5)::double precision) WITHIN GROUP (ORDER BY thousand) AS p50, + + percentile_disc((0.5)::double precision) WITHIN GROUP (ORDER BY thousand) FILTER (WHERE (hundred = 1)) AS px,+ + rank(5, 'AZZZZ'::name, 50) WITHIN GROUP (ORDER BY hundred, string4 DESC, hundred) AS rank + + FROM tenk1 + + GROUP BY ten + + ORDER BY ten; (1 row) select * from aggordview1 order by ten; diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 8dcb00ac67..8c2f75f29f 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -2414,8 +2414,8 @@ create view at_view_2 as select *, to_json(v1) as j from at_view_1 v1; id | integer | | | | plain | stuff | text | | | | extended | View definition: - SELECT bt.id, - bt.stuff + SELECT id, + stuff FROM at_base_table bt; \d+ at_view_2 @@ -2426,8 +2426,8 @@ View definition: stuff | text | | | | extended | j | json | | | | extended | View definition: - SELECT v1.id, - v1.stuff, + SELECT id, + stuff, to_json(v1.*) AS j FROM at_view_1 v1; @@ -2453,8 +2453,8 @@ create or replace view at_view_1 as select *, 2+2 as more from at_base_table bt; stuff | text | | | | extended | more | integer | | | | plain | View definition: - SELECT bt.id, - bt.stuff, + SELECT id, + stuff, 2 + 2 AS more FROM at_base_table bt; @@ -2466,8 +2466,8 @@ View definition: stuff | text | | | | extended | j | json | | | | extended | View definition: - SELECT v1.id, - v1.stuff, + SELECT id, + stuff, to_json(v1.*) AS j FROM at_view_1 v1; diff --git a/src/test/regress/expected/collate.linux.utf8.out b/src/test/regress/expected/collate.linux.utf8.out index f06ae543e4..5f4c5c0db6 100644 --- a/src/test/regress/expected/collate.linux.utf8.out +++ b/src/test/regress/expected/collate.linux.utf8.out @@ -483,18 +483,18 @@ CREATE VIEW collview2 AS SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C"; CREATE VIEW collview3 AS SELECT a, lower((x || x) COLLATE "C") FROM collate_test10; SELECT table_name, view_definition FROM information_schema.views WHERE table_name LIKE 'collview%' ORDER BY 1; - table_name | view_definition -------------+-------------------------------------------------------------------------- - collview1 | SELECT collate_test1.a, + - | collate_test1.b + - | FROM collate_test1 + - | WHERE ((collate_test1.b COLLATE "C") >= 'bbc'::text); - collview2 | SELECT collate_test1.a, + - | collate_test1.b + - | FROM collate_test1 + - | ORDER BY (collate_test1.b COLLATE "C"); - collview3 | SELECT collate_test10.a, + - | lower(((collate_test10.x || collate_test10.x) COLLATE "C")) AS lower+ + table_name | view_definition +------------+-------------------------------------------- + collview1 | SELECT a, + + | b + + | FROM collate_test1 + + | WHERE ((b COLLATE "C") >= 'bbc'::text); + collview2 | SELECT a, + + | b + + | FROM collate_test1 + + | ORDER BY (b COLLATE "C"); + collview3 | SELECT a, + + | lower(((x || x) COLLATE "C")) AS lower+ | FROM collate_test10; (3 rows) diff --git a/src/test/regress/expected/collate.out b/src/test/regress/expected/collate.out index 246832575c..0649564485 100644 --- a/src/test/regress/expected/collate.out +++ b/src/test/regress/expected/collate.out @@ -194,18 +194,18 @@ CREATE VIEW collview2 AS SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C"; CREATE VIEW collview3 AS SELECT a, lower((x || x) COLLATE "POSIX") FROM collate_test10; SELECT table_name, view_definition FROM information_schema.views WHERE table_name LIKE 'collview%' ORDER BY 1; - table_name | view_definition -------------+------------------------------------------------------------------------------ - collview1 | SELECT collate_test1.a, + - | collate_test1.b + - | FROM collate_test1 + - | WHERE ((collate_test1.b COLLATE "C") >= 'bbc'::text); - collview2 | SELECT collate_test1.a, + - | collate_test1.b + - | FROM collate_test1 + - | ORDER BY (collate_test1.b COLLATE "C"); - collview3 | SELECT collate_test10.a, + - | lower(((collate_test10.x || collate_test10.x) COLLATE "POSIX")) AS lower+ + table_name | view_definition +------------+------------------------------------------------ + collview1 | SELECT a, + + | b + + | FROM collate_test1 + + | WHERE ((b COLLATE "C") >= 'bbc'::text); + collview2 | SELECT a, + + | b + + | FROM collate_test1 + + | ORDER BY (b COLLATE "C"); + collview3 | SELECT a, + + | lower(((x || x) COLLATE "POSIX")) AS lower+ | FROM collate_test10; (3 rows) @@ -698,7 +698,7 @@ SELECT c1+1 AS c1p FROM --------+---------+-----------+----------+---------+---------+------------- c1p | integer | | | | plain | View definition: - SELECT ss.c1 + 1 AS c1p + SELECT c1 + 1 AS c1p FROM ( SELECT 4 AS c1) ss; -- Check conflicting or redundant options in CREATE COLLATION diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out index f50ef76685..dee7928ba2 100644 --- a/src/test/regress/expected/create_view.out +++ b/src/test/regress/expected/create_view.out @@ -341,10 +341,10 @@ CREATE VIEW tt1 AS c | numeric | | | | main | d | character varying(4) | | | | extended | View definition: - SELECT vv.a, - vv.b, - vv.c, - vv.d + SELECT a, + b, + c, + d FROM ( VALUES ('abc'::character varying(3),'0123456789'::character varying,42,'abcd'::character varying(4)), ('0123456789'::character varying,'abc'::character varying(3),42.12,'abc'::character varying(4))) vv(a, b, c, d); SELECT * FROM tt1; @@ -386,9 +386,9 @@ CREATE VIEW aliased_view_4 AS f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT tt1.f1, - tt1.f2, - tt1.f3 + SELECT f1, + f2, + f3 FROM tt1 WHERE (EXISTS ( SELECT 1 FROM tx1 @@ -402,9 +402,9 @@ View definition: f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT a1.f1, - a1.f2, - a1.f3 + SELECT f1, + f2, + f3 FROM tt1 a1 WHERE (EXISTS ( SELECT 1 FROM tx1 @@ -418,9 +418,9 @@ View definition: f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT tt1.f1, - tt1.f2, - tt1.f3 + SELECT f1, + f2, + f3 FROM tt1 WHERE (EXISTS ( SELECT 1 FROM tx1 a2 @@ -434,9 +434,9 @@ View definition: f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT tt1.y1, - tt1.f2, - tt1.f3 + SELECT y1, + f2, + f3 FROM temp_view_test.tt1 WHERE (EXISTS ( SELECT 1 FROM tt1 tt1_1 @@ -451,9 +451,9 @@ ALTER TABLE tx1 RENAME TO a1; f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT tt1.f1, - tt1.f2, - tt1.f3 + SELECT f1, + f2, + f3 FROM tt1 WHERE (EXISTS ( SELECT 1 FROM a1 @@ -467,9 +467,9 @@ View definition: f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT a1.f1, - a1.f2, - a1.f3 + SELECT f1, + f2, + f3 FROM tt1 a1 WHERE (EXISTS ( SELECT 1 FROM a1 a1_1 @@ -483,9 +483,9 @@ View definition: f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT tt1.f1, - tt1.f2, - tt1.f3 + SELECT f1, + f2, + f3 FROM tt1 WHERE (EXISTS ( SELECT 1 FROM a1 a2 @@ -499,9 +499,9 @@ View definition: f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT tt1.y1, - tt1.f2, - tt1.f3 + SELECT y1, + f2, + f3 FROM temp_view_test.tt1 WHERE (EXISTS ( SELECT 1 FROM tt1 tt1_1 @@ -516,9 +516,9 @@ ALTER TABLE tt1 RENAME TO a2; f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT a2.f1, - a2.f2, - a2.f3 + SELECT f1, + f2, + f3 FROM a2 WHERE (EXISTS ( SELECT 1 FROM a1 @@ -532,9 +532,9 @@ View definition: f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT a1.f1, - a1.f2, - a1.f3 + SELECT f1, + f2, + f3 FROM a2 a1 WHERE (EXISTS ( SELECT 1 FROM a1 a1_1 @@ -548,9 +548,9 @@ View definition: f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT a2.f1, - a2.f2, - a2.f3 + SELECT f1, + f2, + f3 FROM a2 WHERE (EXISTS ( SELECT 1 FROM a1 a2_1 @@ -564,9 +564,9 @@ View definition: f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT tt1.y1, - tt1.f2, - tt1.f3 + SELECT y1, + f2, + f3 FROM temp_view_test.tt1 WHERE (EXISTS ( SELECT 1 FROM a2 @@ -581,9 +581,9 @@ ALTER TABLE a1 RENAME TO tt1; f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT a2.f1, - a2.f2, - a2.f3 + SELECT f1, + f2, + f3 FROM a2 WHERE (EXISTS ( SELECT 1 FROM tt1 @@ -597,9 +597,9 @@ View definition: f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT a1.f1, - a1.f2, - a1.f3 + SELECT f1, + f2, + f3 FROM a2 a1 WHERE (EXISTS ( SELECT 1 FROM tt1 @@ -613,9 +613,9 @@ View definition: f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT a2.f1, - a2.f2, - a2.f3 + SELECT f1, + f2, + f3 FROM a2 WHERE (EXISTS ( SELECT 1 FROM tt1 a2_1 @@ -629,9 +629,9 @@ View definition: f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT tt1.y1, - tt1.f2, - tt1.f3 + SELECT y1, + f2, + f3 FROM temp_view_test.tt1 WHERE (EXISTS ( SELECT 1 FROM a2 @@ -647,9 +647,9 @@ ALTER TABLE tx1 SET SCHEMA temp_view_test; f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT tx1.f1, - tx1.f2, - tx1.f3 + SELECT f1, + f2, + f3 FROM temp_view_test.tx1 WHERE (EXISTS ( SELECT 1 FROM tt1 @@ -663,9 +663,9 @@ View definition: f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT a1.f1, - a1.f2, - a1.f3 + SELECT f1, + f2, + f3 FROM temp_view_test.tx1 a1 WHERE (EXISTS ( SELECT 1 FROM tt1 @@ -679,9 +679,9 @@ View definition: f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT tx1.f1, - tx1.f2, - tx1.f3 + SELECT f1, + f2, + f3 FROM temp_view_test.tx1 WHERE (EXISTS ( SELECT 1 FROM tt1 a2 @@ -695,9 +695,9 @@ View definition: f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT tt1.y1, - tt1.f2, - tt1.f3 + SELECT y1, + f2, + f3 FROM temp_view_test.tt1 WHERE (EXISTS ( SELECT 1 FROM temp_view_test.tx1 @@ -714,9 +714,9 @@ ALTER TABLE tmp1 RENAME TO tx1; f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT tx1.f1, - tx1.f2, - tx1.f3 + SELECT f1, + f2, + f3 FROM temp_view_test.tx1 WHERE (EXISTS ( SELECT 1 FROM tt1 @@ -730,9 +730,9 @@ View definition: f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT a1.f1, - a1.f2, - a1.f3 + SELECT f1, + f2, + f3 FROM temp_view_test.tx1 a1 WHERE (EXISTS ( SELECT 1 FROM tt1 @@ -746,9 +746,9 @@ View definition: f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT tx1.f1, - tx1.f2, - tx1.f3 + SELECT f1, + f2, + f3 FROM temp_view_test.tx1 WHERE (EXISTS ( SELECT 1 FROM tt1 a2 @@ -762,9 +762,9 @@ View definition: f2 | integer | | | | plain | f3 | text | | | | extended | View definition: - SELECT tx1.y1, - tx1.f2, - tx1.f3 + SELECT y1, + f2, + f3 FROM tx1 WHERE (EXISTS ( SELECT 1 FROM temp_view_test.tx1 tx1_1 @@ -1251,10 +1251,10 @@ select pg_get_viewdef('v1', true); select pg_get_viewdef('v4', true); pg_get_viewdef ---------------- - SELECT v1.b, + - v1.c, + - v1.x AS a,+ - v1.ax + + SELECT b, + + c, + + x AS a, + + ax + FROM v1; (1 row) @@ -1531,9 +1531,9 @@ create view tt14v as select t.* from tt14f() t; select pg_get_viewdef('tt14v', true); pg_get_viewdef -------------------------------- - SELECT t.f1, + - t.f3, + - t.f4 + + SELECT f1, + + f3, + + f4 + FROM tt14f() t(f1, f3, f4); (1 row) @@ -1550,9 +1550,9 @@ alter table tt14t drop column f3; select pg_get_viewdef('tt14v', true); pg_get_viewdef -------------------------------- - SELECT t.f1, + - t.f3, + - t.f4 + + SELECT f1, + + f3, + + f4 + FROM tt14f() t(f1, f3, f4); (1 row) @@ -1573,9 +1573,9 @@ alter table tt14t alter column f4 type integer using f4::integer; select pg_get_viewdef('tt14v', true); pg_get_viewdef -------------------------------- - SELECT t.f1, + - t.f3, + - t.f4 + + SELECT f1, + + f3, + + f4 + FROM tt14f() t(f1, f3, f4); (1 row) @@ -1665,8 +1665,8 @@ select * from tt17v; select pg_get_viewdef('tt17v', true); pg_get_viewdef --------------------------------------------- - SELECT i.q1, + - i.q2 + + SELECT q1, + + q2 + FROM int8_tbl i + WHERE (i.* IN ( VALUES (i.*::int8_tbl))); (1 row) @@ -1895,7 +1895,7 @@ select pg_get_viewdef('tt25v', true); WITH cte AS MATERIALIZED ( + SELECT pg_get_keywords() AS k+ ) + - SELECT (cte.k).word AS word + + SELECT (k).word AS word + FROM cte; (1 row) @@ -1947,19 +1947,19 @@ select x + y + z as c1, (x,y) <= ANY (values(1,2),(3,4)) as c11 from (values(1,2,3)) v(x,y,z); select pg_get_viewdef('tt26v', true); - pg_get_viewdef --------------------------------------------------------- - SELECT v.x + v.y + v.z AS c1, + - v.x * v.y + v.z AS c2, + - v.x + v.y * v.z AS c3, + - (v.x + v.y) * v.z AS c4, + - v.x * (v.y + v.z) AS c5, + - v.x + (v.y + v.z) AS c6, + - v.x + (v.y # v.z) AS c7, + - v.x > v.y AND (v.y > v.z OR v.x > v.z) AS c8, + - v.x > v.y OR v.y > v.z AND NOT v.x > v.z AS c9, + - ((v.x, v.y) <> ALL ( VALUES (1,2), (3,4))) AS c10,+ - ((v.x, v.y) <= ANY ( VALUES (1,2), (3,4))) AS c11 + + pg_get_viewdef +---------------------------------------------------- + SELECT x + y + z AS c1, + + x * y + z AS c2, + + x + y * z AS c3, + + (x + y) * z AS c4, + + x * (y + z) AS c5, + + x + (y + z) AS c6, + + x + (y # z) AS c7, + + x > y AND (y > z OR x > z) AS c8, + + x > y OR y > z AND NOT x > z AS c9, + + ((x, y) <> ALL ( VALUES (1,2), (3,4))) AS c10,+ + ((x, y) <= ANY ( VALUES (1,2), (3,4))) AS c11 + FROM ( VALUES (1,2,3)) v(x, y, z); (1 row) diff --git a/src/test/regress/expected/expressions.out b/src/test/regress/expected/expressions.out index 7b6b0bb4f9..2b85967c1f 100644 --- a/src/test/regress/expected/expressions.out +++ b/src/test/regress/expected/expressions.out @@ -179,12 +179,12 @@ create view numeric_view as f2164 | numeric(16,4) | | | | main | f2n | numeric | | | | main | View definition: - SELECT numeric_tbl.f1, - numeric_tbl.f1::numeric(16,4) AS f1164, - numeric_tbl.f1::numeric AS f1n, - numeric_tbl.f2, - numeric_tbl.f2::numeric(16,4) AS f2164, - numeric_tbl.f2 AS f2n + SELECT f1, + f1::numeric(16,4) AS f1164, + f1::numeric AS f1n, + f2, + f2::numeric(16,4) AS f2164, + f2 AS f2n FROM numeric_tbl; explain (verbose, costs off) select * from numeric_view; diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out index 4c467c1b15..71d6ec7034 100644 --- a/src/test/regress/expected/groupingsets.out +++ b/src/test/regress/expected/groupingsets.out @@ -506,16 +506,16 @@ CREATE VIEW gstest_view AS select a, b, grouping(a,b), sum(c), count(*), max(c) from gstest2 group by rollup ((a,b,c),(c,d)); NOTICE: view "gstest_view" will be a temporary view select pg_get_viewdef('gstest_view'::regclass, true); - pg_get_viewdef -------------------------------------------------------------------------------- - SELECT gstest2.a, + - gstest2.b, + - GROUPING(gstest2.a, gstest2.b) AS "grouping", + - sum(gstest2.c) AS sum, + - count(*) AS count, + - max(gstest2.c) AS max + - FROM gstest2 + - GROUP BY ROLLUP((gstest2.a, gstest2.b, gstest2.c), (gstest2.c, gstest2.d)); + pg_get_viewdef +--------------------------------------- + SELECT a, + + b, + + GROUPING(a, b) AS "grouping", + + sum(c) AS sum, + + count(*) AS count, + + max(c) AS max + + FROM gstest2 + + GROUP BY ROLLUP((a, b, c), (c, d)); (1 row) -- Nested queries with 3 or more levels of nesting diff --git a/src/test/regress/expected/limit.out b/src/test/regress/expected/limit.out index b75afcc01a..493ad70db8 100644 --- a/src/test/regress/expected/limit.out +++ b/src/test/regress/expected/limit.out @@ -633,10 +633,10 @@ CREATE VIEW limit_thousand_v_1 AS SELECT thousand FROM onek WHERE thousand < 995 ----------+---------+-----------+----------+---------+---------+------------- thousand | integer | | | | plain | View definition: - SELECT onek.thousand + SELECT thousand FROM onek - WHERE onek.thousand < 995 - ORDER BY onek.thousand + WHERE thousand < 995 + ORDER BY thousand OFFSET 10 FETCH FIRST 5 ROWS WITH TIES; @@ -648,10 +648,10 @@ CREATE VIEW limit_thousand_v_2 AS SELECT thousand FROM onek WHERE thousand < 995 ----------+---------+-----------+----------+---------+---------+------------- thousand | integer | | | | plain | View definition: - SELECT onek.thousand + SELECT thousand FROM onek - WHERE onek.thousand < 995 - ORDER BY onek.thousand + WHERE thousand < 995 + ORDER BY thousand OFFSET 10 LIMIT 5; @@ -666,10 +666,10 @@ CREATE VIEW limit_thousand_v_3 AS SELECT thousand FROM onek WHERE thousand < 995 ----------+---------+-----------+----------+---------+---------+------------- thousand | integer | | | | plain | View definition: - SELECT onek.thousand + SELECT thousand FROM onek - WHERE onek.thousand < 995 - ORDER BY onek.thousand + WHERE thousand < 995 + ORDER BY thousand FETCH FIRST (NULL::integer + 1) ROWS WITH TIES; CREATE VIEW limit_thousand_v_4 AS SELECT thousand FROM onek WHERE thousand < 995 @@ -680,10 +680,10 @@ CREATE VIEW limit_thousand_v_4 AS SELECT thousand FROM onek WHERE thousand < 995 ----------+---------+-----------+----------+---------+---------+------------- thousand | integer | | | | plain | View definition: - SELECT onek.thousand + SELECT thousand FROM onek - WHERE onek.thousand < 995 - ORDER BY onek.thousand + WHERE thousand < 995 + ORDER BY thousand LIMIT ALL; -- leave these views diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index 313c72a268..03d2de7d3a 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -100,10 +100,10 @@ CREATE INDEX mvtest_aa ON mvtest_bb (grandtot); type | text | | | | extended | | totamt | numeric | | | | main | | View definition: - SELECT mvtest_tv.type, - mvtest_tv.totamt + SELECT type, + totamt FROM mvtest_tv - ORDER BY mvtest_tv.type; + ORDER BY type; \d+ mvtest_tvm Materialized view "public.mvtest_tvm" @@ -112,10 +112,10 @@ View definition: type | text | | | | extended | | totamt | numeric | | | | main | | View definition: - SELECT mvtest_tv.type, - mvtest_tv.totamt + SELECT type, + totamt FROM mvtest_tv - ORDER BY mvtest_tv.type; + ORDER BY type; \d+ mvtest_tvvm Materialized view "public.mvtest_tvvm" @@ -123,7 +123,7 @@ View definition: ----------+---------+-----------+----------+---------+---------+--------------+------------- grandtot | numeric | | | | main | | View definition: - SELECT mvtest_tvv.grandtot + SELECT grandtot FROM mvtest_tvv; \d+ mvtest_bb @@ -134,7 +134,7 @@ View definition: Indexes: "mvtest_aa" btree (grandtot) View definition: - SELECT mvtest_tvvmv.grandtot + SELECT grandtot FROM mvtest_tvvmv; -- test schema behavior @@ -150,7 +150,7 @@ Indexes: "mvtest_tvmm_expr" UNIQUE, btree ((grandtot > 0::numeric)) "mvtest_tvmm_pred" UNIQUE, btree (grandtot) WHERE grandtot < 0::numeric View definition: - SELECT sum(mvtest_tvm.totamt) AS grandtot + SELECT sum(totamt) AS grandtot FROM mvtest_mvschema.mvtest_tvm; SET search_path = mvtest_mvschema, public; @@ -161,10 +161,10 @@ SET search_path = mvtest_mvschema, public; type | text | | | | extended | | totamt | numeric | | | | main | | View definition: - SELECT mvtest_tv.type, - mvtest_tv.totamt + SELECT type, + totamt FROM mvtest_tv - ORDER BY mvtest_tv.type; + ORDER BY type; -- modify the underlying table data INSERT INTO mvtest_t VALUES (6, 'z', 13); diff --git a/src/test/regress/expected/polymorphism.out b/src/test/regress/expected/polymorphism.out index 1cd558d668..bf08e40ed8 100644 --- a/src/test/regress/expected/polymorphism.out +++ b/src/test/regress/expected/polymorphism.out @@ -1801,10 +1801,10 @@ select * from dfview; c3 | bigint | | | | plain | c4 | bigint | | | | plain | View definition: - SELECT int8_tbl.q1, - int8_tbl.q2, - dfunc(int8_tbl.q1, int8_tbl.q2, flag => int8_tbl.q1 > int8_tbl.q2) AS c3, - dfunc(int8_tbl.q1, flag => int8_tbl.q1 < int8_tbl.q2, b => int8_tbl.q2) AS c4 + SELECT q1, + q2, + dfunc(q1, q2, flag => q1 > q2) AS c3, + dfunc(q1, flag => q1 < q2, b => q2) AS c4 FROM int8_tbl; drop view dfview; diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out index cafca1f9ae..6347c62774 100644 --- a/src/test/regress/expected/rangefuncs.out +++ b/src/test/regress/expected/rangefuncs.out @@ -149,9 +149,9 @@ select * from vw_ord; select definition from pg_views where viewname='vw_ord'; definition ---------------------------------------------------------------------------------------- - SELECT z.a, + - z.b, + - z.c + + SELECT a, + + b, + + c + FROM UNNEST(ARRAY[10, 20], ARRAY['foo'::text, 'bar'::text], ARRAY[1.0]) z(a, b, c); (1 row) @@ -167,9 +167,9 @@ select * from vw_ord; select definition from pg_views where viewname='vw_ord'; definition ---------------------------------------------------------------------------------------- - SELECT z.a, + - z.b, + - z.c + + SELECT a, + + b, + + c + FROM UNNEST(ARRAY[10, 20], ARRAY['foo'::text, 'bar'::text], ARRAY[1.0]) z(a, b, c); (1 row) @@ -185,9 +185,9 @@ select * from vw_ord; select definition from pg_views where viewname='vw_ord'; definition ---------------------------------------------------------------------------------------------------------------------- - SELECT z.a, + - z.b, + - z.c + + SELECT a, + + b, + + c + FROM ROWS FROM(unnest(ARRAY[10, 20]), unnest(ARRAY['foo'::text, 'bar'::text]), generate_series(1, 2)) z(a, b, c); (1 row) @@ -669,14 +669,14 @@ select * from vw_rngfunc; select pg_get_viewdef('vw_rngfunc'); pg_get_viewdef ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ - SELECT t1.a, + - t1.b, + - t1.c, + - t1.d, + - t1.e, + - t1.f, + - t1.g, + - t1.n + + SELECT a, + + b, + + c, + + d, + + e, + + f, + + g, + + n + FROM ROWS FROM(getrngfunc9(1), getrngfunc7(1) AS (rngfuncid integer, rngfuncsubid integer, rngfuncname text), getrngfunc1(1)) WITH ORDINALITY t1(a, b, c, d, e, f, g, n); (1 row) diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 2fa00a3c29..fefad636ca 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1291,21 +1291,21 @@ iexit| SELECT ih.name, FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath); -key_dependent_view| SELECT view_base_table.key, - view_base_table.data +key_dependent_view| SELECT key, + data FROM view_base_table - GROUP BY view_base_table.key; + GROUP BY key; key_dependent_view_no_cols| SELECT FROM view_base_table - GROUP BY view_base_table.key - HAVING (length((view_base_table.data)::text) > 0); -mvtest_tv| SELECT mvtest_t.type, - sum(mvtest_t.amt) AS totamt + GROUP BY key + HAVING (length((data)::text) > 0); +mvtest_tv| SELECT type, + sum(amt) AS totamt FROM mvtest_t - GROUP BY mvtest_t.type; -mvtest_tvv| SELECT sum(mvtest_tv.totamt) AS grandtot + GROUP BY type; +mvtest_tvv| SELECT sum(totamt) AS grandtot FROM mvtest_tv; -mvtest_tvvmv| SELECT mvtest_tvvm.grandtot +mvtest_tvvmv| SELECT grandtot FROM mvtest_tvvm; pg_available_extension_versions| SELECT e.name, e.version, @@ -1324,50 +1324,50 @@ pg_available_extensions| SELECT e.name, e.comment FROM (pg_available_extensions() e(name, default_version, comment) LEFT JOIN pg_extension x ON ((e.name = x.extname))); -pg_backend_memory_contexts| SELECT pg_get_backend_memory_contexts.name, - pg_get_backend_memory_contexts.ident, - pg_get_backend_memory_contexts.parent, - pg_get_backend_memory_contexts.level, - pg_get_backend_memory_contexts.total_bytes, - pg_get_backend_memory_contexts.total_nblocks, - pg_get_backend_memory_contexts.free_bytes, - pg_get_backend_memory_contexts.free_chunks, - pg_get_backend_memory_contexts.used_bytes +pg_backend_memory_contexts| SELECT name, + ident, + parent, + level, + total_bytes, + total_nblocks, + free_bytes, + free_chunks, + used_bytes FROM pg_get_backend_memory_contexts() pg_get_backend_memory_contexts(name, ident, parent, level, total_bytes, total_nblocks, free_bytes, free_chunks, used_bytes); -pg_config| SELECT pg_config.name, - pg_config.setting +pg_config| SELECT name, + setting FROM pg_config() pg_config(name, setting); -pg_cursors| SELECT c.name, - c.statement, - c.is_holdable, - c.is_binary, - c.is_scrollable, - c.creation_time +pg_cursors| SELECT name, + statement, + is_holdable, + is_binary, + is_scrollable, + creation_time FROM pg_cursor() c(name, statement, is_holdable, is_binary, is_scrollable, creation_time); -pg_file_settings| SELECT a.sourcefile, - a.sourceline, - a.seqno, - a.name, - a.setting, - a.applied, - a.error +pg_file_settings| SELECT sourcefile, + sourceline, + seqno, + name, + setting, + applied, + error FROM pg_show_all_file_settings() a(sourcefile, sourceline, seqno, name, setting, applied, error); -pg_group| SELECT pg_authid.rolname AS groname, - pg_authid.oid AS grosysid, +pg_group| SELECT rolname AS groname, + oid AS grosysid, ARRAY( SELECT pg_auth_members.member FROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid - WHERE (NOT pg_authid.rolcanlogin); -pg_hba_file_rules| SELECT a.line_number, - a.type, - a.database, - a.user_name, - a.address, - a.netmask, - a.auth_method, - a.options, - a.error + WHERE (NOT rolcanlogin); +pg_hba_file_rules| SELECT line_number, + type, + database, + user_name, + address, + netmask, + auth_method, + options, + error FROM pg_hba_file_rules() a(line_number, type, database, user_name, address, netmask, auth_method, options, error); pg_indexes| SELECT n.nspname AS schemaname, c.relname AS tablename, @@ -1380,22 +1380,22 @@ pg_indexes| SELECT n.nspname AS schemaname, 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", 'p'::"char"])) AND (i.relkind = ANY (ARRAY['i'::"char", 'I'::"char"]))); -pg_locks| SELECT l.locktype, - l.database, - l.relation, - l.page, - l.tuple, - l.virtualxid, - l.transactionid, - l.classid, - l.objid, - l.objsubid, - l.virtualtransaction, - l.pid, - l.mode, - l.granted, - l.fastpath, - l.waitstart +pg_locks| SELECT locktype, + database, + relation, + page, + tuple, + virtualxid, + transactionid, + classid, + objid, + objsubid, + virtualtransaction, + pid, + mode, + granted, + fastpath, + waitstart FROM pg_lock_status() l(locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted, fastpath, waitstart); pg_matviews| SELECT n.nspname AS schemaname, c.relname AS matviewname, @@ -1435,13 +1435,13 @@ pg_policies| SELECT n.nspname AS schemaname, FROM ((pg_policy pol JOIN pg_class c ON ((c.oid = pol.polrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))); -pg_prepared_statements| SELECT p.name, - p.statement, - p.prepare_time, - p.parameter_types, - p.from_sql, - p.generic_plans, - p.custom_plans +pg_prepared_statements| SELECT name, + statement, + prepare_time, + parameter_types, + from_sql, + generic_plans, + custom_plans FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql, generic_plans, custom_plans); pg_prepared_xacts| SELECT p.transaction, p.gid, @@ -1459,10 +1459,10 @@ pg_publication_tables| SELECT p.pubname, (pg_class c JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.oid = gpt.relid); -pg_replication_origin_status| SELECT pg_show_replication_origin_status.local_id, - pg_show_replication_origin_status.external_id, - pg_show_replication_origin_status.remote_lsn, - pg_show_replication_origin_status.local_lsn +pg_replication_origin_status| SELECT local_id, + external_id, + remote_lsn, + local_lsn FROM pg_show_replication_origin_status() pg_show_replication_origin_status(local_id, external_id, remote_lsn, local_lsn); pg_replication_slots| SELECT l.slot_name, l.plugin, @@ -1708,23 +1708,23 @@ pg_sequences| SELECT n.nspname AS schemaname, JOIN pg_class c ON ((c.oid = s.seqrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((NOT pg_is_other_temp_schema(n.oid)) AND (c.relkind = 'S'::"char")); -pg_settings| SELECT a.name, - a.setting, - a.unit, - a.category, - a.short_desc, - a.extra_desc, - a.context, - a.vartype, - a.source, - a.min_val, - a.max_val, - a.enumvals, - a.boot_val, - a.reset_val, - a.sourcefile, - a.sourceline, - a.pending_restart +pg_settings| SELECT name, + setting, + unit, + category, + short_desc, + extra_desc, + context, + vartype, + source, + min_val, + max_val, + enumvals, + boot_val, + reset_val, + sourcefile, + sourceline, + pending_restart FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline, pending_restart); pg_shadow| SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, @@ -1738,10 +1738,10 @@ pg_shadow| SELECT pg_authid.rolname AS usename, 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_shmem_allocations| SELECT pg_get_shmem_allocations.name, - pg_get_shmem_allocations.off, - pg_get_shmem_allocations.size, - pg_get_shmem_allocations.allocated_size +pg_shmem_allocations| SELECT name, + off, + size, + allocated_size FROM pg_get_shmem_allocations() pg_get_shmem_allocations(name, off, size, allocated_size); pg_stat_activity| SELECT s.datid, d.datname, @@ -1809,13 +1809,13 @@ pg_stat_all_tables| SELECT c.oid AS relid, LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char", 'p'::"char"])) GROUP BY c.oid, n.nspname, c.relname; -pg_stat_archiver| SELECT s.archived_count, - s.last_archived_wal, - s.last_archived_time, - s.failed_count, - s.last_failed_wal, - s.last_failed_time, - s.stats_reset +pg_stat_archiver| SELECT archived_count, + last_archived_wal, + last_archived_time, + failed_count, + last_failed_wal, + last_failed_time, + stats_reset FROM pg_stat_get_archiver() s(archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time, stats_reset); pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, @@ -1828,57 +1828,57 @@ pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, pg_stat_get_buf_alloc() AS buffers_alloc, pg_stat_get_bgwriter_stat_reset_time() AS stats_reset; -pg_stat_database| SELECT d.oid AS datid, - d.datname, +pg_stat_database| SELECT oid AS datid, + datname, CASE - WHEN (d.oid = (0)::oid) THEN 0 - ELSE pg_stat_get_db_numbackends(d.oid) + WHEN (oid = (0)::oid) THEN 0 + ELSE pg_stat_get_db_numbackends(oid) END AS numbackends, - pg_stat_get_db_xact_commit(d.oid) AS xact_commit, - pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, - (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, - pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, - pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, - pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, - pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, - pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, - pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted, - pg_stat_get_db_conflict_all(d.oid) AS conflicts, - pg_stat_get_db_temp_files(d.oid) AS temp_files, - pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes, - pg_stat_get_db_deadlocks(d.oid) AS deadlocks, - pg_stat_get_db_checksum_failures(d.oid) AS checksum_failures, - pg_stat_get_db_checksum_last_failure(d.oid) AS checksum_last_failure, - pg_stat_get_db_blk_read_time(d.oid) AS blk_read_time, - pg_stat_get_db_blk_write_time(d.oid) AS blk_write_time, - pg_stat_get_db_session_time(d.oid) AS session_time, - pg_stat_get_db_active_time(d.oid) AS active_time, - pg_stat_get_db_idle_in_transaction_time(d.oid) AS idle_in_transaction_time, - pg_stat_get_db_sessions(d.oid) AS sessions, - pg_stat_get_db_sessions_abandoned(d.oid) AS sessions_abandoned, - pg_stat_get_db_sessions_fatal(d.oid) AS sessions_fatal, - pg_stat_get_db_sessions_killed(d.oid) AS sessions_killed, - pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset + pg_stat_get_db_xact_commit(oid) AS xact_commit, + pg_stat_get_db_xact_rollback(oid) AS xact_rollback, + (pg_stat_get_db_blocks_fetched(oid) - pg_stat_get_db_blocks_hit(oid)) AS blks_read, + pg_stat_get_db_blocks_hit(oid) AS blks_hit, + pg_stat_get_db_tuples_returned(oid) AS tup_returned, + pg_stat_get_db_tuples_fetched(oid) AS tup_fetched, + pg_stat_get_db_tuples_inserted(oid) AS tup_inserted, + pg_stat_get_db_tuples_updated(oid) AS tup_updated, + pg_stat_get_db_tuples_deleted(oid) AS tup_deleted, + pg_stat_get_db_conflict_all(oid) AS conflicts, + pg_stat_get_db_temp_files(oid) AS temp_files, + pg_stat_get_db_temp_bytes(oid) AS temp_bytes, + pg_stat_get_db_deadlocks(oid) AS deadlocks, + pg_stat_get_db_checksum_failures(oid) AS checksum_failures, + pg_stat_get_db_checksum_last_failure(oid) AS checksum_last_failure, + pg_stat_get_db_blk_read_time(oid) AS blk_read_time, + pg_stat_get_db_blk_write_time(oid) AS blk_write_time, + pg_stat_get_db_session_time(oid) AS session_time, + pg_stat_get_db_active_time(oid) AS active_time, + pg_stat_get_db_idle_in_transaction_time(oid) AS idle_in_transaction_time, + pg_stat_get_db_sessions(oid) AS sessions, + pg_stat_get_db_sessions_abandoned(oid) AS sessions_abandoned, + pg_stat_get_db_sessions_fatal(oid) AS sessions_fatal, + pg_stat_get_db_sessions_killed(oid) AS sessions_killed, + pg_stat_get_db_stat_reset_time(oid) AS stats_reset FROM ( SELECT 0 AS oid, NULL::name AS datname UNION ALL SELECT pg_database.oid, pg_database.datname FROM pg_database) d; -pg_stat_database_conflicts| SELECT d.oid AS datid, - d.datname, - pg_stat_get_db_conflict_tablespace(d.oid) AS confl_tablespace, - pg_stat_get_db_conflict_lock(d.oid) AS confl_lock, - pg_stat_get_db_conflict_snapshot(d.oid) AS confl_snapshot, - pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin, - pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock +pg_stat_database_conflicts| SELECT oid AS datid, + datname, + pg_stat_get_db_conflict_tablespace(oid) AS confl_tablespace, + pg_stat_get_db_conflict_lock(oid) AS confl_lock, + pg_stat_get_db_conflict_snapshot(oid) AS confl_snapshot, + pg_stat_get_db_conflict_bufferpin(oid) AS confl_bufferpin, + pg_stat_get_db_conflict_startup_deadlock(oid) AS confl_deadlock FROM pg_database d; -pg_stat_gssapi| SELECT s.pid, - s.gss_auth AS gss_authenticated, - s.gss_princ AS principal, - s.gss_enc AS encrypted +pg_stat_gssapi| SELECT pid, + gss_auth AS gss_authenticated, + gss_princ AS principal, + gss_enc AS encrypted FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, leader_pid, query_id) - WHERE (s.client_port IS NOT NULL); + WHERE (client_port IS NOT NULL); pg_stat_progress_analyze| SELECT s.pid, s.datid, d.datname, @@ -1901,8 +1901,8 @@ pg_stat_progress_analyze| SELECT s.pid, (s.param8)::oid AS current_child_table_relid FROM (pg_stat_get_progress_info('ANALYZE'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20) LEFT JOIN pg_database d ON ((s.datid = d.oid))); -pg_stat_progress_basebackup| SELECT s.pid, - CASE s.param1 +pg_stat_progress_basebackup| SELECT pid, + CASE param1 WHEN 0 THEN 'initializing'::text WHEN 1 THEN 'waiting for checkpoint to finish'::text WHEN 2 THEN 'estimating backup size'::text @@ -1911,13 +1911,13 @@ pg_stat_progress_basebackup| SELECT s.pid, WHEN 5 THEN 'transferring wal files'::text ELSE NULL::text END AS phase, - CASE s.param2 + CASE param2 WHEN '-1'::integer THEN NULL::bigint - ELSE s.param2 + ELSE param2 END AS backup_total, - s.param3 AS backup_streamed, - s.param4 AS tablespaces_total, - s.param5 AS tablespaces_streamed + param3 AS backup_streamed, + param4 AS tablespaces_total, + param5 AS tablespaces_streamed FROM pg_stat_get_progress_info('BASEBACKUP'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20); pg_stat_progress_cluster| SELECT s.pid, s.datid, @@ -2063,26 +2063,26 @@ pg_stat_replication_slots| SELECT s.slot_name, FROM pg_replication_slots r, LATERAL pg_stat_get_replication_slot((r.slot_name)::text) s(slot_name, spill_txns, spill_count, spill_bytes, stream_txns, stream_count, stream_bytes, total_txns, total_bytes, stats_reset) WHERE (r.datoid IS NOT NULL); -pg_stat_slru| SELECT s.name, - s.blks_zeroed, - s.blks_hit, - s.blks_read, - s.blks_written, - s.blks_exists, - s.flushes, - s.truncates, - s.stats_reset +pg_stat_slru| SELECT name, + blks_zeroed, + blks_hit, + blks_read, + blks_written, + blks_exists, + flushes, + truncates, + stats_reset FROM pg_stat_get_slru() s(name, blks_zeroed, blks_hit, blks_read, blks_written, blks_exists, flushes, truncates, stats_reset); -pg_stat_ssl| SELECT s.pid, - s.ssl, - s.sslversion AS version, - s.sslcipher AS cipher, - s.sslbits AS bits, - s.ssl_client_dn AS client_dn, - s.ssl_client_serial AS client_serial, - s.ssl_issuer_dn AS issuer_dn +pg_stat_ssl| SELECT pid, + ssl, + sslversion AS version, + sslcipher AS cipher, + sslbits AS bits, + ssl_client_dn AS client_dn, + ssl_client_serial AS client_serial, + ssl_issuer_dn AS issuer_dn FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, leader_pid, query_id) - WHERE (s.client_port IS NOT NULL); + WHERE (client_port IS NOT NULL); pg_stat_subscription| SELECT su.oid AS subid, su.subname, st.pid, @@ -2094,41 +2094,41 @@ pg_stat_subscription| SELECT su.oid AS subid, st.latest_end_time FROM (pg_subscription su LEFT JOIN pg_stat_get_subscription(NULL::oid) st(subid, relid, pid, received_lsn, last_msg_send_time, last_msg_receipt_time, latest_end_lsn, latest_end_time) ON ((st.subid = su.oid))); -pg_stat_sys_indexes| SELECT pg_stat_all_indexes.relid, - pg_stat_all_indexes.indexrelid, - pg_stat_all_indexes.schemaname, - pg_stat_all_indexes.relname, - pg_stat_all_indexes.indexrelname, - pg_stat_all_indexes.idx_scan, - pg_stat_all_indexes.idx_tup_read, - pg_stat_all_indexes.idx_tup_fetch +pg_stat_sys_indexes| SELECT relid, + indexrelid, + schemaname, + relname, + indexrelname, + idx_scan, + idx_tup_read, + idx_tup_fetch FROM pg_stat_all_indexes - WHERE ((pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_indexes.schemaname ~ '^pg_toast'::text)); -pg_stat_sys_tables| SELECT pg_stat_all_tables.relid, - pg_stat_all_tables.schemaname, - pg_stat_all_tables.relname, - pg_stat_all_tables.seq_scan, - pg_stat_all_tables.seq_tup_read, - pg_stat_all_tables.idx_scan, - pg_stat_all_tables.idx_tup_fetch, - pg_stat_all_tables.n_tup_ins, - pg_stat_all_tables.n_tup_upd, - pg_stat_all_tables.n_tup_del, - pg_stat_all_tables.n_tup_hot_upd, - pg_stat_all_tables.n_live_tup, - pg_stat_all_tables.n_dead_tup, - pg_stat_all_tables.n_mod_since_analyze, - pg_stat_all_tables.n_ins_since_vacuum, - pg_stat_all_tables.last_vacuum, - pg_stat_all_tables.last_autovacuum, - pg_stat_all_tables.last_analyze, - pg_stat_all_tables.last_autoanalyze, - pg_stat_all_tables.vacuum_count, - pg_stat_all_tables.autovacuum_count, - pg_stat_all_tables.analyze_count, - pg_stat_all_tables.autoanalyze_count + WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text)); +pg_stat_sys_tables| SELECT relid, + schemaname, + relname, + seq_scan, + seq_tup_read, + idx_scan, + idx_tup_fetch, + n_tup_ins, + n_tup_upd, + n_tup_del, + n_tup_hot_upd, + n_live_tup, + n_dead_tup, + n_mod_since_analyze, + n_ins_since_vacuum, + last_vacuum, + last_autovacuum, + last_analyze, + last_autoanalyze, + vacuum_count, + autovacuum_count, + analyze_count, + autoanalyze_count FROM pg_stat_all_tables - WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text)); + WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text)); pg_stat_user_functions| SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname, @@ -2138,68 +2138,68 @@ pg_stat_user_functions| SELECT p.oid AS funcid, 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_all_indexes.schemaname, - pg_stat_all_indexes.relname, - pg_stat_all_indexes.indexrelname, - pg_stat_all_indexes.idx_scan, - pg_stat_all_indexes.idx_tup_read, - pg_stat_all_indexes.idx_tup_fetch +pg_stat_user_indexes| SELECT relid, + indexrelid, + schemaname, + relname, + indexrelname, + idx_scan, + idx_tup_read, + idx_tup_fetch FROM pg_stat_all_indexes - WHERE ((pg_stat_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_indexes.schemaname !~ '^pg_toast'::text)); -pg_stat_user_tables| SELECT pg_stat_all_tables.relid, - pg_stat_all_tables.schemaname, - pg_stat_all_tables.relname, - pg_stat_all_tables.seq_scan, - pg_stat_all_tables.seq_tup_read, - pg_stat_all_tables.idx_scan, - pg_stat_all_tables.idx_tup_fetch, - pg_stat_all_tables.n_tup_ins, - pg_stat_all_tables.n_tup_upd, - pg_stat_all_tables.n_tup_del, - pg_stat_all_tables.n_tup_hot_upd, - pg_stat_all_tables.n_live_tup, - pg_stat_all_tables.n_dead_tup, - pg_stat_all_tables.n_mod_since_analyze, - pg_stat_all_tables.n_ins_since_vacuum, - pg_stat_all_tables.last_vacuum, - pg_stat_all_tables.last_autovacuum, - pg_stat_all_tables.last_analyze, - pg_stat_all_tables.last_autoanalyze, - pg_stat_all_tables.vacuum_count, - pg_stat_all_tables.autovacuum_count, - pg_stat_all_tables.analyze_count, - pg_stat_all_tables.autoanalyze_count + WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); +pg_stat_user_tables| SELECT relid, + schemaname, + relname, + seq_scan, + seq_tup_read, + idx_scan, + idx_tup_fetch, + n_tup_ins, + n_tup_upd, + n_tup_del, + n_tup_hot_upd, + n_live_tup, + n_dead_tup, + n_mod_since_analyze, + n_ins_since_vacuum, + last_vacuum, + last_autovacuum, + last_analyze, + last_autoanalyze, + vacuum_count, + autovacuum_count, + analyze_count, + autoanalyze_count FROM pg_stat_all_tables - WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text)); -pg_stat_wal| SELECT w.wal_records, - w.wal_fpi, - w.wal_bytes, - w.wal_buffers_full, - w.wal_write, - w.wal_sync, - w.wal_write_time, - w.wal_sync_time, - w.stats_reset + WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); +pg_stat_wal| SELECT wal_records, + wal_fpi, + wal_bytes, + wal_buffers_full, + wal_write, + wal_sync, + wal_write_time, + wal_sync_time, + stats_reset FROM pg_stat_get_wal() w(wal_records, wal_fpi, wal_bytes, wal_buffers_full, wal_write, wal_sync, wal_write_time, wal_sync_time, stats_reset); -pg_stat_wal_receiver| SELECT s.pid, - s.status, - s.receive_start_lsn, - s.receive_start_tli, - s.written_lsn, - s.flushed_lsn, - s.received_tli, - s.last_msg_send_time, - s.last_msg_receipt_time, - s.latest_end_lsn, - s.latest_end_time, - s.slot_name, - s.sender_host, - s.sender_port, - s.conninfo +pg_stat_wal_receiver| SELECT pid, + status, + receive_start_lsn, + receive_start_tli, + written_lsn, + flushed_lsn, + received_tli, + last_msg_send_time, + last_msg_receipt_time, + latest_end_lsn, + latest_end_time, + slot_name, + sender_host, + sender_port, + conninfo FROM pg_stat_get_wal_receiver() s(pid, status, receive_start_lsn, receive_start_tli, written_lsn, flushed_lsn, received_tli, last_msg_send_time, last_msg_receipt_time, latest_end_lsn, latest_end_time, slot_name, sender_host, sender_port, conninfo) - WHERE (s.pid IS NOT NULL); + WHERE (pid IS NOT NULL); pg_stat_xact_all_tables| SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, @@ -2216,19 +2216,19 @@ pg_stat_xact_all_tables| SELECT c.oid AS relid, LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char", 'p'::"char"])) GROUP BY c.oid, n.nspname, c.relname; -pg_stat_xact_sys_tables| SELECT pg_stat_xact_all_tables.relid, - pg_stat_xact_all_tables.schemaname, - pg_stat_xact_all_tables.relname, - pg_stat_xact_all_tables.seq_scan, - pg_stat_xact_all_tables.seq_tup_read, - pg_stat_xact_all_tables.idx_scan, - pg_stat_xact_all_tables.idx_tup_fetch, - pg_stat_xact_all_tables.n_tup_ins, - pg_stat_xact_all_tables.n_tup_upd, - pg_stat_xact_all_tables.n_tup_del, - pg_stat_xact_all_tables.n_tup_hot_upd +pg_stat_xact_sys_tables| SELECT relid, + schemaname, + relname, + seq_scan, + seq_tup_read, + idx_scan, + idx_tup_fetch, + n_tup_ins, + n_tup_upd, + n_tup_del, + n_tup_hot_upd FROM pg_stat_xact_all_tables - WHERE ((pg_stat_xact_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_xact_all_tables.schemaname ~ '^pg_toast'::text)); + WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text)); pg_stat_xact_user_functions| SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname, @@ -2238,19 +2238,19 @@ pg_stat_xact_user_functions| SELECT p.oid AS funcid, 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_stat_xact_all_tables.relname, - pg_stat_xact_all_tables.seq_scan, - pg_stat_xact_all_tables.seq_tup_read, - pg_stat_xact_all_tables.idx_scan, - pg_stat_xact_all_tables.idx_tup_fetch, - pg_stat_xact_all_tables.n_tup_ins, - pg_stat_xact_all_tables.n_tup_upd, - pg_stat_xact_all_tables.n_tup_del, - pg_stat_xact_all_tables.n_tup_hot_upd +pg_stat_xact_user_tables| SELECT relid, + schemaname, + relname, + seq_scan, + seq_tup_read, + idx_scan, + idx_tup_fetch, + n_tup_ins, + n_tup_upd, + n_tup_del, + n_tup_hot_upd FROM pg_stat_xact_all_tables - WHERE ((pg_stat_xact_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_xact_all_tables.schemaname !~ '^pg_toast'::text)); + WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); pg_statio_all_indexes| SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, @@ -2289,64 +2289,64 @@ pg_statio_all_tables| SELECT c.oid AS relid, 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.indexrelid; -pg_statio_sys_indexes| SELECT pg_statio_all_indexes.relid, - pg_statio_all_indexes.indexrelid, - pg_statio_all_indexes.schemaname, - pg_statio_all_indexes.relname, - pg_statio_all_indexes.indexrelname, - pg_statio_all_indexes.idx_blks_read, - pg_statio_all_indexes.idx_blks_hit +pg_statio_sys_indexes| SELECT relid, + indexrelid, + schemaname, + relname, + indexrelname, + idx_blks_read, + idx_blks_hit FROM pg_statio_all_indexes - WHERE ((pg_statio_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_indexes.schemaname ~ '^pg_toast'::text)); -pg_statio_sys_sequences| SELECT pg_statio_all_sequences.relid, - pg_statio_all_sequences.schemaname, - pg_statio_all_sequences.relname, - pg_statio_all_sequences.blks_read, - pg_statio_all_sequences.blks_hit + WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text)); +pg_statio_sys_sequences| SELECT relid, + schemaname, + relname, + blks_read, + blks_hit FROM pg_statio_all_sequences - WHERE ((pg_statio_all_sequences.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_sequences.schemaname ~ '^pg_toast'::text)); -pg_statio_sys_tables| SELECT pg_statio_all_tables.relid, - pg_statio_all_tables.schemaname, - pg_statio_all_tables.relname, - pg_statio_all_tables.heap_blks_read, - pg_statio_all_tables.heap_blks_hit, - pg_statio_all_tables.idx_blks_read, - pg_statio_all_tables.idx_blks_hit, - pg_statio_all_tables.toast_blks_read, - pg_statio_all_tables.toast_blks_hit, - pg_statio_all_tables.tidx_blks_read, - pg_statio_all_tables.tidx_blks_hit + WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text)); +pg_statio_sys_tables| SELECT relid, + schemaname, + relname, + heap_blks_read, + heap_blks_hit, + idx_blks_read, + idx_blks_hit, + toast_blks_read, + toast_blks_hit, + tidx_blks_read, + tidx_blks_hit FROM pg_statio_all_tables - WHERE ((pg_statio_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_statio_all_tables.schemaname ~ '^pg_toast'::text)); -pg_statio_user_indexes| SELECT pg_statio_all_indexes.relid, - pg_statio_all_indexes.indexrelid, - pg_statio_all_indexes.schemaname, - pg_statio_all_indexes.relname, - pg_statio_all_indexes.indexrelname, - pg_statio_all_indexes.idx_blks_read, - pg_statio_all_indexes.idx_blks_hit + WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text)); +pg_statio_user_indexes| SELECT relid, + indexrelid, + schemaname, + relname, + indexrelname, + idx_blks_read, + idx_blks_hit FROM pg_statio_all_indexes - WHERE ((pg_statio_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_indexes.schemaname !~ '^pg_toast'::text)); -pg_statio_user_sequences| SELECT pg_statio_all_sequences.relid, - pg_statio_all_sequences.schemaname, - pg_statio_all_sequences.relname, - pg_statio_all_sequences.blks_read, - pg_statio_all_sequences.blks_hit + WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); +pg_statio_user_sequences| SELECT relid, + schemaname, + relname, + blks_read, + blks_hit FROM pg_statio_all_sequences - WHERE ((pg_statio_all_sequences.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_sequences.schemaname !~ '^pg_toast'::text)); -pg_statio_user_tables| SELECT pg_statio_all_tables.relid, - pg_statio_all_tables.schemaname, - pg_statio_all_tables.relname, - pg_statio_all_tables.heap_blks_read, - pg_statio_all_tables.heap_blks_hit, - pg_statio_all_tables.idx_blks_read, - pg_statio_all_tables.idx_blks_hit, - pg_statio_all_tables.toast_blks_read, - pg_statio_all_tables.toast_blks_hit, - pg_statio_all_tables.tidx_blks_read, - pg_statio_all_tables.tidx_blks_hit + WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); +pg_statio_user_tables| SELECT relid, + schemaname, + relname, + heap_blks_read, + heap_blks_hit, + idx_blks_read, + idx_blks_hit, + toast_blks_read, + toast_blks_hit, + tidx_blks_read, + tidx_blks_hit FROM pg_statio_all_tables - WHERE ((pg_statio_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_tables.schemaname !~ '^pg_toast'::text)); + WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text)); pg_stats| SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, @@ -2529,24 +2529,24 @@ pg_tables| SELECT n.nspname AS schemaname, LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"])); -pg_timezone_abbrevs| SELECT pg_timezone_abbrevs.abbrev, - pg_timezone_abbrevs.utc_offset, - pg_timezone_abbrevs.is_dst +pg_timezone_abbrevs| SELECT abbrev, + utc_offset, + is_dst FROM pg_timezone_abbrevs() pg_timezone_abbrevs(abbrev, utc_offset, is_dst); -pg_timezone_names| SELECT pg_timezone_names.name, - pg_timezone_names.abbrev, - pg_timezone_names.utc_offset, - pg_timezone_names.is_dst +pg_timezone_names| SELECT name, + abbrev, + utc_offset, + is_dst FROM pg_timezone_names() pg_timezone_names(name, abbrev, utc_offset, is_dst); -pg_user| SELECT pg_shadow.usename, - pg_shadow.usesysid, - pg_shadow.usecreatedb, - pg_shadow.usesuper, - pg_shadow.userepl, - pg_shadow.usebypassrls, +pg_user| SELECT usename, + usesysid, + usecreatedb, + usesuper, + userepl, + usebypassrls, '********'::text AS passwd, - pg_shadow.valuntil, - pg_shadow.useconfig + valuntil, + useconfig FROM pg_shadow; pg_user_mappings| SELECT u.oid AS umid, s.oid AS srvid, @@ -2572,26 +2572,26 @@ pg_views| SELECT n.nspname AS schemaname, 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 +rtest_v1| SELECT a, + b FROM rtest_t1; rtest_vcomp| SELECT x.part, (x.size * y.factor) AS size_in_cm FROM rtest_comp x, rtest_unitfact y WHERE (x.unit = y.unit); -rtest_vview1| SELECT x.a, - x.b +rtest_vview1| SELECT a, + b FROM rtest_view1 x WHERE (0 < ( SELECT count(*) AS count FROM rtest_view2 y WHERE (y.a = x.a))); -rtest_vview2| SELECT rtest_view1.a, - rtest_view1.b +rtest_vview2| SELECT a, + b FROM rtest_view1 - WHERE rtest_view1.v; -rtest_vview3| SELECT x.a, - x.b + WHERE v; +rtest_vview3| SELECT a, + b FROM rtest_vview2 x WHERE (0 < ( SELECT count(*) AS count FROM rtest_view2 y @@ -2603,9 +2603,9 @@ rtest_vview4| SELECT x.a, rtest_view2 y WHERE (x.a = y.a) GROUP BY x.a, x.b; -rtest_vview5| SELECT rtest_view1.a, - rtest_view1.b, - rtest_viewfunc1(rtest_view1.a) AS refcount +rtest_vview5| SELECT a, + b, + rtest_viewfunc1(a) AS refcount FROM rtest_view1; shoe| SELECT sh.shoename, sh.sh_avail, @@ -2635,20 +2635,20 @@ shoelace| SELECT s.sl_name, FROM shoelace_data s, unit u WHERE (s.sl_unit = u.un_name); -shoelace_candelete| SELECT shoelace_obsolete.sl_name, - shoelace_obsolete.sl_avail, - shoelace_obsolete.sl_color, - shoelace_obsolete.sl_len, - shoelace_obsolete.sl_unit, - shoelace_obsolete.sl_len_cm +shoelace_candelete| SELECT sl_name, + sl_avail, + sl_color, + sl_len, + sl_unit, + sl_len_cm FROM shoelace_obsolete - WHERE (shoelace_obsolete.sl_avail = 0); -shoelace_obsolete| SELECT shoelace.sl_name, - shoelace.sl_avail, - shoelace.sl_color, - shoelace.sl_len, - shoelace.sl_unit, - shoelace.sl_len_cm + WHERE (sl_avail = 0); +shoelace_obsolete| SELECT sl_name, + sl_avail, + sl_color, + sl_len, + sl_unit, + sl_len_cm FROM shoelace WHERE (NOT (EXISTS ( SELECT shoe.shoename FROM shoe @@ -2659,14 +2659,14 @@ street| SELECT r.name, FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath); -test_tablesample_v1| SELECT test_tablesample.id +test_tablesample_v1| SELECT id FROM test_tablesample TABLESAMPLE system ((10 * 2)) REPEATABLE (2); -test_tablesample_v2| SELECT test_tablesample.id +test_tablesample_v2| SELECT id FROM test_tablesample TABLESAMPLE system (99); -toyemp| SELECT emp.name, - emp.age, - emp.location, - (12 * emp.salary) AS annualsal +toyemp| SELECT name, + age, + location, + (12 * salary) AS annualsal FROM emp; SELECT tablename, rulename, definition FROM pg_rules WHERE schemaname IN ('pg_catalog', 'public') @@ -3256,7 +3256,7 @@ SELECT * FROM rule_v1; --------+---------+-----------+----------+---------+---------+------------- a | integer | | | | plain | View definition: - SELECT rule_t1.a + SELECT a FROM rule_t1; Rules: newinsertrule AS @@ -3295,8 +3295,8 @@ alter table rule_v1 rename column column2 to q2; column1 | integer | | | | plain | q2 | integer | | | | plain | View definition: - SELECT "*VALUES*".column1, - "*VALUES*".column2 AS q2 + SELECT column1, + column2 AS q2 FROM (VALUES (1,2)) "*VALUES*"; drop view rule_v1; @@ -3308,8 +3308,8 @@ create view rule_v1(x) as values(1,2); x | integer | | | | plain | column2 | integer | | | | plain | View definition: - SELECT "*VALUES*".column1 AS x, - "*VALUES*".column2 + SELECT column1 AS x, + column2 FROM (VALUES (1,2)) "*VALUES*"; drop view rule_v1; @@ -3321,8 +3321,8 @@ create view rule_v1(x) as select * from (values(1,2)) v; x | integer | | | | plain | column2 | integer | | | | plain | View definition: - SELECT v.column1 AS x, - v.column2 + SELECT column1 AS x, + column2 FROM ( VALUES (1,2)) v; drop view rule_v1; @@ -3334,8 +3334,8 @@ create view rule_v1(x) as select * from (values(1,2)) v(q,w); x | integer | | | | plain | w | integer | | | | plain | View definition: - SELECT v.q AS x, - v.w + SELECT q AS x, + w FROM ( VALUES (1,2)) v(q, w); drop view rule_v1; diff --git a/src/test/regress/expected/tablesample.out b/src/test/regress/expected/tablesample.out index 078358d226..15a64aca0c 100644 --- a/src/test/regress/expected/tablesample.out +++ b/src/test/regress/expected/tablesample.out @@ -74,7 +74,7 @@ CREATE VIEW test_tablesample_v2 AS --------+---------+-----------+----------+---------+---------+------------- id | integer | | | | plain | View definition: - SELECT test_tablesample.id + SELECT id FROM test_tablesample TABLESAMPLE system ((10 * 2)) REPEATABLE (2); \d+ test_tablesample_v2 @@ -83,7 +83,7 @@ View definition: --------+---------+-----------+----------+---------+---------+------------- id | integer | | | | plain | View definition: - SELECT test_tablesample.id + SELECT id FROM test_tablesample TABLESAMPLE system (99); -- check a sampled query doesn't affect cursor in progress diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index 5d124cf96f..ed0f85e113 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -1251,8 +1251,8 @@ DROP TRIGGER instead_of_delete_trig ON main_view; a | integer | | | | plain | b | integer | | | | plain | View definition: - SELECT main_table.a, - main_table.b + SELECT a, + b FROM main_table; Triggers: after_del_stmt_trig AFTER DELETE ON main_view FOR EACH STATEMENT EXECUTE FUNCTION view_trigger('after_view_del_stmt') diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index cdff914b93..a026946fb4 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -1666,19 +1666,19 @@ CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a < b a | integer | | | | plain | b | integer | | | | plain | View definition: - SELECT base_tbl.a, - base_tbl.b + SELECT a, + b FROM base_tbl - WHERE base_tbl.a < base_tbl.b; + WHERE a < b; Options: check_option=local SELECT * FROM information_schema.views WHERE table_name = 'rw_view1'; - table_catalog | table_schema | table_name | view_definition | check_option | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into ----------------+--------------+------------+------------------------------------+--------------+--------------+--------------------+----------------------+----------------------+---------------------------- - regression | public | rw_view1 | SELECT base_tbl.a, +| LOCAL | YES | YES | NO | NO | NO - | | | base_tbl.b +| | | | | | - | | | FROM base_tbl +| | | | | | - | | | WHERE (base_tbl.a < base_tbl.b); | | | | | | + table_catalog | table_schema | table_name | view_definition | check_option | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into +---------------+--------------+------------+------------------+--------------+--------------+--------------------+----------------------+----------------------+---------------------------- + regression | public | rw_view1 | SELECT a, +| LOCAL | YES | YES | NO | NO | NO + | | | b +| | | | | | + | | | FROM base_tbl+| | | | | | + | | | WHERE (a < b); | | | | | | (1 row) INSERT INTO rw_view1 VALUES(3,4); -- ok @@ -1719,17 +1719,17 @@ CREATE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a < 10 --------+---------+-----------+----------+---------+---------+------------- a | integer | | | | plain | View definition: - SELECT rw_view1.a + SELECT a FROM rw_view1 - WHERE rw_view1.a < 10; + WHERE a < 10; Options: check_option=cascaded SELECT * FROM information_schema.views WHERE table_name = 'rw_view2'; - table_catalog | table_schema | table_name | view_definition | check_option | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into ----------------+--------------+------------+----------------------------+--------------+--------------+--------------------+----------------------+----------------------+---------------------------- - regression | public | rw_view2 | SELECT rw_view1.a +| CASCADED | YES | YES | NO | NO | NO - | | | FROM rw_view1 +| | | | | | - | | | WHERE (rw_view1.a < 10); | | | | | | + table_catalog | table_schema | table_name | view_definition | check_option | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into +---------------+--------------+------------+-------------------+--------------+--------------+--------------------+----------------------+----------------------+---------------------------- + regression | public | rw_view2 | SELECT a +| CASCADED | YES | YES | NO | NO | NO + | | | FROM rw_view1 +| | | | | | + | | | WHERE (a < 10); | | | | | | (1 row) INSERT INTO rw_view2 VALUES (-5); -- should fail @@ -1759,17 +1759,17 @@ CREATE OR REPLACE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a < 10 --------+---------+-----------+----------+---------+---------+------------- a | integer | | | | plain | View definition: - SELECT rw_view1.a + SELECT a FROM rw_view1 - WHERE rw_view1.a < 10; + WHERE a < 10; Options: check_option=local SELECT * FROM information_schema.views WHERE table_name = 'rw_view2'; - table_catalog | table_schema | table_name | view_definition | check_option | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into ----------------+--------------+------------+----------------------------+--------------+--------------+--------------------+----------------------+----------------------+---------------------------- - regression | public | rw_view2 | SELECT rw_view1.a +| LOCAL | YES | YES | NO | NO | NO - | | | FROM rw_view1 +| | | | | | - | | | WHERE (rw_view1.a < 10); | | | | | | + table_catalog | table_schema | table_name | view_definition | check_option | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into +---------------+--------------+------------+-------------------+--------------+--------------+--------------------+----------------------+----------------------+---------------------------- + regression | public | rw_view2 | SELECT a +| LOCAL | YES | YES | NO | NO | NO + | | | FROM rw_view1 +| | | | | | + | | | WHERE (a < 10); | | | | | | (1 row) INSERT INTO rw_view2 VALUES (-10); -- ok, but not in view @@ -1800,16 +1800,16 @@ ALTER VIEW rw_view2 RESET (check_option); --------+---------+-----------+----------+---------+---------+------------- a | integer | | | | plain | View definition: - SELECT rw_view1.a + SELECT a FROM rw_view1 - WHERE rw_view1.a < 10; + WHERE a < 10; SELECT * FROM information_schema.views WHERE table_name = 'rw_view2'; - table_catalog | table_schema | table_name | view_definition | check_option | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into ----------------+--------------+------------+----------------------------+--------------+--------------+--------------------+----------------------+----------------------+---------------------------- - regression | public | rw_view2 | SELECT rw_view1.a +| NONE | YES | YES | NO | NO | NO - | | | FROM rw_view1 +| | | | | | - | | | WHERE (rw_view1.a < 10); | | | | | | + table_catalog | table_schema | table_name | view_definition | check_option | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into +---------------+--------------+------------+-------------------+--------------+--------------+--------------------+----------------------+----------------------+---------------------------- + regression | public | rw_view2 | SELECT a +| NONE | YES | YES | NO | NO | NO + | | | FROM rw_view1 +| | | | | | + | | | WHERE (a < 10); | | | | | | (1 row) INSERT INTO rw_view2 VALUES (30); -- ok, but not in view @@ -1831,15 +1831,15 @@ CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WITH CHECK OPTION; CREATE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a > 0; CREATE VIEW rw_view3 AS SELECT * FROM rw_view2 WITH CHECK OPTION; SELECT * FROM information_schema.views WHERE table_name LIKE E'rw\\_view_' ORDER BY table_name; - table_catalog | table_schema | table_name | view_definition | check_option | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into ----------------+--------------+------------+---------------------------+--------------+--------------+--------------------+----------------------+----------------------+---------------------------- - regression | public | rw_view1 | SELECT base_tbl.a +| CASCADED | YES | YES | NO | NO | NO - | | | FROM base_tbl; | | | | | | - regression | public | rw_view2 | SELECT rw_view1.a +| NONE | YES | YES | NO | NO | NO - | | | FROM rw_view1 +| | | | | | - | | | WHERE (rw_view1.a > 0); | | | | | | - regression | public | rw_view3 | SELECT rw_view2.a +| CASCADED | YES | YES | NO | NO | NO - | | | FROM rw_view2; | | | | | | + table_catalog | table_schema | table_name | view_definition | check_option | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into +---------------+--------------+------------+-------------------+--------------+--------------+--------------------+----------------------+----------------------+---------------------------- + regression | public | rw_view1 | SELECT a +| CASCADED | YES | YES | NO | NO | NO + | | | FROM base_tbl; | | | | | | + regression | public | rw_view2 | SELECT a +| NONE | YES | YES | NO | NO | NO + | | | FROM rw_view1 +| | | | | | + | | | WHERE (a > 0); | | | | | | + regression | public | rw_view3 | SELECT a +| CASCADED | YES | YES | NO | NO | NO + | | | FROM rw_view2; | | | | | | (3 rows) INSERT INTO rw_view1 VALUES (-1); -- ok diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out index bb9ff7f07b..5e4612fff1 100644 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@ -1195,10 +1195,10 @@ SELECT * FROM v_window; (10 rows) SELECT pg_get_viewdef('v_window'); - pg_get_viewdef ---------------------------------------------------------------------------------------- - SELECT i.i, + - sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows+ + pg_get_viewdef +----------------------------------------------------------------------------------- + SELECT i, + + sum(i) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows+ FROM generate_series(1, 10) i(i); (1 row) @@ -1221,10 +1221,10 @@ SELECT * FROM v_window; (10 rows) SELECT pg_get_viewdef('v_window'); - pg_get_viewdef ------------------------------------------------------------------------------------------------------------ - SELECT i.i, + - sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) AS sum_rows+ + pg_get_viewdef +------------------------------------------------------------------------------------------------------- + SELECT i, + + sum(i) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) AS sum_rows+ FROM generate_series(1, 10) i(i); (1 row) @@ -1247,10 +1247,10 @@ SELECT * FROM v_window; (10 rows) SELECT pg_get_viewdef('v_window'); - pg_get_viewdef ------------------------------------------------------------------------------------------------------ - SELECT i.i, + - sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE GROUP) AS sum_rows+ + pg_get_viewdef +------------------------------------------------------------------------------------------------- + SELECT i, + + sum(i) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE GROUP) AS sum_rows+ FROM generate_series(1, 10) i(i); (1 row) @@ -1273,10 +1273,10 @@ SELECT * FROM v_window; (10 rows) SELECT pg_get_viewdef('v_window'); - pg_get_viewdef ----------------------------------------------------------------------------------------------------- - SELECT i.i, + - sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE TIES) AS sum_rows+ + pg_get_viewdef +------------------------------------------------------------------------------------------------ + SELECT i, + + sum(i) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE TIES) AS sum_rows+ FROM generate_series(1, 10) i(i); (1 row) @@ -1299,10 +1299,10 @@ SELECT * FROM v_window; (10 rows) SELECT pg_get_viewdef('v_window'); - pg_get_viewdef ---------------------------------------------------------------------------------------- - SELECT i.i, + - sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows+ + pg_get_viewdef +----------------------------------------------------------------------------------- + SELECT i, + + sum(i) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows+ FROM generate_series(1, 10) i(i); (1 row) @@ -1324,10 +1324,10 @@ SELECT * FROM v_window; (10 rows) SELECT pg_get_viewdef('v_window'); - pg_get_viewdef ------------------------------------------------------------------------------------------ - SELECT i.i, + - sum(i.i) OVER (ORDER BY i.i GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows+ + pg_get_viewdef +------------------------------------------------------------------------------------- + SELECT i, + + sum(i) OVER (ORDER BY i GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows+ FROM generate_series(1, 10) i(i); (1 row) @@ -1336,10 +1336,10 @@ CREATE TEMP VIEW v_window AS SELECT i, min(i) over (order by i range between '1 day' preceding and '10 days' following) as min_i FROM generate_series(now(), now()+'100 days'::interval, '1 hour') i; SELECT pg_get_viewdef('v_window'); - pg_get_viewdef ---------------------------------------------------------------------------------------------------------------------------- - SELECT i.i, + - min(i.i) OVER (ORDER BY i.i RANGE BETWEEN '@ 1 day'::interval PRECEDING AND '@ 10 days'::interval FOLLOWING) AS min_i+ + pg_get_viewdef +----------------------------------------------------------------------------------------------------------------------- + SELECT i, + + min(i) OVER (ORDER BY i RANGE BETWEEN '@ 1 day'::interval PRECEDING AND '@ 10 days'::interval FOLLOWING) AS min_i+ FROM generate_series(now(), (now() + '@ 100 days'::interval), '@ 1 hour'::interval) i(i); (1 row) diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out index 3523a7dcc1..d262a7944e 100644 --- a/src/test/regress/expected/with.out +++ b/src/test/regress/expected/with.out @@ -396,9 +396,9 @@ SELECT pg_get_viewdef('vsubdepartment'::regclass); subdepartment sd + WHERE (d.parent_department = sd.id)+ ) + - SELECT subdepartment.id, + - subdepartment.parent_department, + - subdepartment.name + + SELECT id, + + parent_department, + + name + FROM subdepartment; (1 row) @@ -419,9 +419,9 @@ SELECT pg_get_viewdef('vsubdepartment'::regclass, true); subdepartment sd + WHERE d.parent_department = sd.id+ ) + - SELECT subdepartment.id, + - subdepartment.parent_department, + - subdepartment.name + + SELECT id, + + parent_department, + + name + FROM subdepartment; (1 row) @@ -446,7 +446,7 @@ View definition: FROM t t_1 WHERE t_1.n < 100 ) - SELECT sum(t.n) AS sum + SELECT sum(n) AS sum FROM t; -- corner case in which sub-WITH gets initialized first @@ -802,9 +802,9 @@ select pg_get_viewdef('v_search'); search_graph sg + WHERE (g.f = sg.t) + ) SEARCH DEPTH FIRST BY f, t SET seq + - SELECT search_graph.f, + - search_graph.t, + - search_graph.label + + SELECT f, + + t, + + label + FROM search_graph; (1 row) @@ -1291,9 +1291,9 @@ select pg_get_viewdef('v_cycle1'); search_graph sg + WHERE (g.f = sg.t) + ) CYCLE f, t SET is_cycle USING path + - SELECT search_graph.f, + - search_graph.t, + - search_graph.label + + SELECT f, + + t, + + label + FROM search_graph; (1 row) @@ -1313,9 +1313,9 @@ select pg_get_viewdef('v_cycle2'); search_graph sg + WHERE (g.f = sg.t) + ) CYCLE f, t SET is_cycle TO 'Y'::text DEFAULT 'N'::text USING path+ - SELECT search_graph.f, + - search_graph.t, + - search_graph.label + + SELECT f, + + t, + + label + FROM search_graph; (1 row) -- 2.24.1