v3-0002-Remove-UpdateRangeTableOfViewParse.patch
application/octet-stream
Filename: v3-0002-Remove-UpdateRangeTableOfViewParse.patch
Type: application/octet-stream
Part: 0
Patch
Format: format-patch
Series: patch v3-0002
Subject: Remove UpdateRangeTableOfViewParse()
| File | + | − |
|---|---|---|
| contrib/postgres_fdw/expected/postgres_fdw.out | 8 | 8 |
| src/backend/commands/view.c | 0 | 78 |
| src/backend/rewrite/rewriteHandler.c | 20 | 9 |
| src/bin/pg_dump/t/002_pg_dump.pl | 6 | 6 |
| src/test/regress/expected/aggregates.out | 13 | 13 |
| src/test/regress/expected/alter_table.out | 8 | 8 |
| src/test/regress/expected/collate.linux.utf8.out | 12 | 12 |
| src/test/regress/expected/collate.out | 13 | 13 |
| src/test/regress/expected/create_view.out | 105 | 105 |
| src/test/regress/expected/expressions.out | 6 | 6 |
| src/test/regress/expected/groupingsets.out | 10 | 10 |
| src/test/regress/expected/limit.out | 12 | 12 |
| src/test/regress/expected/matview.out | 12 | 12 |
| src/test/regress/expected/polymorphism.out | 4 | 4 |
| src/test/regress/expected/rangefuncs.out | 17 | 17 |
| src/test/regress/expected/rules.out | 389 | 389 |
| src/test/regress/expected/tablesample.out | 2 | 2 |
| src/test/regress/expected/triggers.out | 2 | 2 |
| src/test/regress/expected/updatable_views.out | 39 | 39 |
| src/test/regress/expected/window.out | 28 | 28 |
| src/test/regress/expected/with.out | 16 | 16 |
From 62f63bc271483050a7a38f12698102a28956ea15 Mon Sep 17 00:00:00 2001
From: amitlan <amitlangote09@gmail.com>
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