Fix CREATE VIEW to allow zero-column views.

We should logically have allowed this case when we allowed zero-column
tables, but it was overlooked.

Although this might be thought a feature addition, it's really a bug
fix, because it was possible to create a zero-column view via
the convert-table-to-view code path, and then you'd have a situation
where dump/reload would fail.  Hence, back-patch to all supported
branches.

Arrange the added test cases to provide coverage of the related
pg_dump code paths (since these views will be dumped and reloaded
during the pg_upgrade regression test).  I also made them test
the case where pg_dump has to postpone the view rule into post-data,
which disturbingly had no regression coverage before.

Report and patch by Ashutosh Sharma (test case by me)

Discussion: https://postgr.es/m/CAE9k0PkmHdeSaeZt2ujnb_cKucmK3sDDceDzw7+d5UZoNJPYOg@mail.gmail.com

diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c
index ae73e2d934b9c0ebd4c9d6ec8da7091d7f82874c..9635de409ccc5a45b7eb5c3a7cebfd0a0aa6bb65 100644 (file)
--- a/src/backend/commands/view.c
+++ b/src/backend/commands/view.c
@@ -111,11 +111,6 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace,
        }
    }
 
-   if (attrList == NIL)
-       ereport(ERROR,
-               (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
-                errmsg("view must have at least one column")));
-
    /*
     * Look up, check permissions on, and lock the creation namespace; also
     * check for a preexisting view with the same name.  This will also set

diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index e9a017fecb452551d44cfd440abc7bfa85406f04..1434c2f85aaf1c4f64ad0d10d2553f1506bc6c26 100644 (file)
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -20,6 +20,16 @@ COMMENT ON VIEW noview IS 'no view';
 ERROR:  relation "noview" does not exist
 COMMENT ON VIEW toyemp IS 'is a view';
 COMMENT ON VIEW toyemp IS NULL;
+-- These views are left around mainly to exercise special cases in pg_dump.
+CREATE TABLE view_base_table (key int PRIMARY KEY, data varchar(20));
+CREATE VIEW key_dependent_view AS
+   SELECT * FROM view_base_table GROUP BY key;
+ALTER TABLE view_base_table DROP CONSTRAINT view_base_table_pkey;  -- fails
+ERROR:  cannot drop constraint view_base_table_pkey on table view_base_table because other objects depend on it
+DETAIL:  view key_dependent_view depends on constraint view_base_table_pkey on table view_base_table
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+CREATE VIEW key_dependent_view_no_cols AS
+   SELECT FROM view_base_table GROUP BY key HAVING length(data) > 0;
 --
 -- CREATE OR REPLACE VIEW
 --

diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 6d84703c33a0d0df8b81b73474b13ef473f61026..ef72c3ddb61e742e3e9eebac2220cca58157ea81 100644 (file)
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1289,6 +1289,14 @@ 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
+   FROM view_base_table
+  GROUP BY view_base_table.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);
 pg_available_extension_versions| SELECT e.name,
     e.version,
     (x.extname IS NOT NULL) AS installed,

diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out
index 51412e7e13ed2d0737f41abb30b603013dc97948..36c15598f069700bcd0ccd23f68ac820cb088d7f 100644 (file)
--- a/src/test/regress/expected/sanity_check.out
+++ b/src/test/regress/expected/sanity_check.out
@@ -170,6 +170,7 @@ timestamp_tbl|f
 timestamptz_tbl|f
 timetz_tbl|f
 varchar_tbl|f
+view_base_table|t
 -- restore normal output mode
 \a\t
 --

diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql
index 8a365749fce1712b6633affbcc8fd2596f3e5a7e..11f539fcd36c1a09bbde6c21177ca2f3cda2029c 100644 (file)
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -24,6 +24,18 @@ COMMENT ON VIEW noview IS 'no view';
 COMMENT ON VIEW toyemp IS 'is a view';
 COMMENT ON VIEW toyemp IS NULL;
 
+-- These views are left around mainly to exercise special cases in pg_dump.
+
+CREATE TABLE view_base_table (key int PRIMARY KEY, data varchar(20));
+
+CREATE VIEW key_dependent_view AS
+   SELECT * FROM view_base_table GROUP BY key;
+
+ALTER TABLE view_base_table DROP CONSTRAINT view_base_table_pkey;  -- fails
+
+CREATE VIEW key_dependent_view_no_cols AS
+   SELECT FROM view_base_table GROUP BY key HAVING length(data) > 0;
+
 --
 -- CREATE OR REPLACE VIEW
 --