Re: Convert table to view 9.1

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