Re: Convert table to view 9.1

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

I have tried the follwoing and itworks, I need to update also relhasindex

UPDATE  pg_class SET relhastriggers = FALSE WHERE oid = 'b'::regclass;
UPDATE  pg_class SET relhasindex = FALSE WHERE oid = 'b'::regclass;

To be honest I do not like to play with catalog tables, so my question would be, what are the reason for "(or recently had)" in the case of index, or (or once had) in the case of triggers. I find the ability to convert a table to a view an extremly handy in applications were buisnes logic is modelled as views. For example, I need to refactor b, but keep it for backward compatability as updatabale view.

Regards

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wednesday, December 11, 2013 2:18 PM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:

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