On Fri, Feb 8, 2019 at 12:48 PM Andres Freund <and...@anarazel.de> wrote:
> Hi, > > On 2019-02-08 12:18:32 +0530, Ashutosh Sharma wrote: > > When "ON SELECT" rule is created on a table without columns, it > > successfully converts a table into the view. However, when the same is > > done using CREATE VIEW command, it fails with an error saying: "view > > must have at least one column". Here is what I'm trying to say: > > > > -- create table t1 without columns > > create table t1(); > > > > -- create table t2 without columns > > create table t2(); > > > > -- create ON SELECT rule on t1 - this would convert t1 from table to view > > create rule "_RETURN" as on select to t1 do instead select * from t2; > > > > -- now check the definition of t1 > > \d t1 > > > > postgres=# \d+ t1 > > View "public.t1" > > Column | Type | Collation | Nullable | Default | Storage | Description > > --------+------+-----------+----------+---------+---------+------------- > > View definition: > > SELECT > > FROM t2; > > > > The output of "\d+ t1" shows the definition of converted view t1 which > > doesn't have any columns in the select query. > > > > Now, when i try creating another view with the same definition using > > CREATE VIEW command, it fails with the error -> ERROR: view must have > > at least one column. See below > > > > postgres=# create view v1 as select from t2; > > ERROR: view must have at least one column > > > > OR, > > > > postgres=# create view v1 as select * from t2; > > ERROR: view must have at least one column > > > > Isn't that a bug in create rule command or am i missing something here ? > > > > If it is a bug, then, attached is the patch that fixes it. > > > > -- > > With Regards, > > Ashutosh Sharma > > EnterpriseDB:http://www.enterprisedb.com > > > diff --git a/src/backend/rewrite/rewriteDefine.c > b/src/backend/rewrite/rewriteDefine.c > > index 3496e6f..cb51955 100644 > > --- a/src/backend/rewrite/rewriteDefine.c > > +++ b/src/backend/rewrite/rewriteDefine.c > > @@ -473,6 +473,11 @@ DefineQueryRewrite(const char *rulename, > > errmsg("could not convert > table \"%s\" to a view because it has row security enabled", > > > RelationGetRelationName(event_relation)))); > > > > + if (event_relation->rd_rel->relnatts == 0) > > + ereport(ERROR, > > + > (errcode(ERRCODE_INVALID_TABLE_DEFINITION), > > + errmsg("view must have at > least one column"))); > > + > > if (relation_has_policies(event_relation)) > > ereport(ERROR, > > > (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), > > Maybe I'm missing something, but why do we want to forbid this? Because pg_dump - produce the output for such case as: CREATE VIEW public.foo AS SELECT FROM public.bar; which fails to restore because we forbid this in create view: postgres@20625=#CREATE VIEW public.foo AS postgres-# SELECT postgres-# FROM public.bar; ERROR: view must have at least one column postgres@20625=# Given > that we these days allows selects without columns, I see no reason to > require this for views. The view error check long predates allowing > SELECT and CREATE TABLE without columns. I think it's existence is just > an oversight. Tom, you did relaxed the permissive cases, any opinion? > > Greetings, > > Andres Freund > > -- Rushabh Lathia
