Re: Convert table to view 9.1

Hello Tom,

>Patient: Doctor, it hurts when I do this.
>Doctor: So, don't do that.

>Why would you think this is a good thing to do?  Why not just rename
>table b to c, and then create the view as b?
>(For context, it's not even considered a supported operation to
>manually create _RETURN rules like that. 

I have stumbled upon this in the documentation, http://www.postgresql.org/docs/9.1/static/rules-views.html, and it seems an option to solve a problem in a legacy system. This might not the best approch, but it gives me the chance to refactor a node in  complex tree, without dropping and creating the subtree which depends on this certain node. In my case the table b  has a  bad design and refactoring is requiered . Still, since this is a legacy application, and the table b is used in hundreds of views, and the code in not maintained in git repository ....etc. It would be easier for me just to replace it with updatable view without dropping the views.

Normally, I do not convert a table to view using this approch. But, since this approach is mentioned in the docs. I think it would be nice to either have more clarification. Still, I think there is data inconsistency, I have queried in the past for example pg_class to determine if a table has no index to determine misusage or bad designs.

Regards

On Wednesday, December 11, 2013 4:50 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

salah jubeh <s_jubeh(at)yahoo(dot)com> writes:

> create table a (id int primary key);
> create table b (id int primary key, a_id int references a (id));

> insert into  a values (1);
> insert into  b values (1,1);

> create table c AS SELECT * FROM b;

> TRUNCATE b;
> ALTER TABLE b DROP CONSTRAINT
b_a_id_fkey;
> ALTER TABLE b DROP CONSTRAINT b_pkey;
> ALTER TABLE b ALTER COLUMN id DROP NOT NULL;
>  
> CREATE RULE "_RETURN" AS ON SELECT TO b DO INSTEAD SELECT * FROM C;

Patient: Doctor, it hurts when I do this.
Doctor: So, don't do that.

Why would you think this is a good thing to do?  Why not just rename
table b to c, and then create the view as b?

(For context, it's not even considered a supported operation to
manually create _RETURN rules like that.  Any arbitrary restrictions
we might put on transforming tables to views are perfectly legitimate
IMHO, because the only case we care about supporting is pg_dump's
usage of this hack to break circular dependencies
between views.
And in that case, the "table" never had any table-only features.)

            regards, tom lane