Hi All, 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),