Re: Convert table to view 9.1

salah jubeh wrote:

> ERROR: could not convert table "b" to a view because it has triggers
> HINT: In particular, the table cannot be involved in any foreign key relationships.
>
> ********** Error **********
>
> ERROR: could not convert table "b" to a view because it has triggers
> SQL state: 55000
> Hint: In particular, the table cannot be involved in any foreign key relationships.
>
>
> Scenario:
>
> 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;

SELECT relhastriggers FROM pg_class WHERE oid = 'b'::regclass;

relhastriggers
----------------
t
(1 row)

http://www.postgresql.org/docs/current/static/catalog-pg-class.html

relhastriggers bool True if table has (or once had) triggers

This is what is queried when you try to convert the table into a view.
So there is no way to convert your table to a view unless you are
wiling to tamper with the pg_class.

Yours,
Laurenz Albe