Re: ON SELECT rule on a table without columns

On Fri, Feb 8, 2019 at 12:18 PM Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com>
wrote:

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

Yes, it's looks like a bug to me.

>
> If it is a bug, then, attached is the patch that fixes it.
>

I had quick glance to the patch - here are few commits:

1)

+ if (event_relation->rd_rel->relnatts == 0)

Can't use direct relnatts - as need to consider attisdropped.

2)
I think you may like to change the error message to be in-line with
the other error message in the similar code area.

May be something like:
"could not convert table \"%s\" to a view because table does not have any
column"

Regards,
Rushabh Lathia
www.EnterpriseDB.com