Re: Convert table to view 9.1 - Mailing list pgsql-general
| From | salah jubeh |
|---|---|
| Subject | Re: Convert table to view 9.1 |
| Date | |
| Msg-id | 1386769013.94708.YahooMailNeo@web122202.mail.ne1.yahoo.com Whole thread Raw |
| In response to | Re: Convert table to view 9.1 (Albe Laurenz <laurenz.albe@wien.gv.at>) |
| Responses |
Re: Convert table to view 9.1
(Albe Laurenz <laurenz.albe@wien.gv.at>)
|
| List | pgsql-general |
>> 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;
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@postgresql.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.albe@wien.gv.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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
pgsql-general by date: