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

From Rushabh Lathia
Subject Re: ON SELECT rule on a table without columns
Date
Msg-id CAGPqQf0q8t3J_FJEfnsEhf1tPv0-mvqZBtXJnsSOcfANOis2bg@mail.gmail.com
Whole thread Raw
In response to ON SELECT rule on a table without columns  (Ashutosh Sharma <ashu.coek88@gmail.com>)
List pgsql-hackers


On Fri, Feb 8, 2019 at 12:18 PM Ashutosh Sharma <ashu.coek88@gmail.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

pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Re: libpq compression
Next
From: Andres Freund
Date:
Subject: Re: libpq compression