Re: ON SELECT rule on a table without columns - Mailing list pgsql-hackers

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? 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