RE: [GENERAL] I can't drop view?!
От | Colin Price (EML) |
---|---|
Тема | RE: [GENERAL] I can't drop view?! |
Дата | |
Msg-id | 5F052F2A01FBD11184F00008C7A4A8000194331E@eukbant101.ericsson.se обсуждение исходный текст |
Список | pgsql-general |
> -----Original Message----- > From: Adriaan Joubert [mailto:a.joubert@albourne.com] > Sent: Friday, March 05, 1999 6:33 AM > To: Viktor A. > Cc: Postgres-General > Subject: Re: [GENERAL] I can't drop view?! > > > > > > I just DROPped the mentioned VIEWs with the > > DROP TABLE > > Command! DROP VIEW doesn't work... on any of them... > > > > The problem with the view I can't create, because it once > had existed > > still bugs, though....... > > > > I had the same problem. I spent a day digging through source code and > the admin tables, and ended up having to dump the data, drop the > database and redo the whole thing from scratch. There does not seem to > be any other way.... > > Adriaan > not sure if helpful but ..... A view is created through a CREATE TABLE and CREATE RULE being executed (do a pg_dump for the exact code). Maybe you can 'play' with them. Also, I had/have a problem with views when joining two tables with the same fieldnames. When a pg_dump was loaded into postgres, it failed on the view due to 'duplicate field name' although they were expressed uniquely (see below). - Colin ----------------------- ============================================================================ == - Object : To confirm that pg stores ambiguious fieldnames when creating views 1.. Create table 1 and populate it DROP TABLE "useraccount"; CREATE TABLE "useraccount" ( "id" int4 NOT NULL, "login" character varying(20) NOT NULL, "usertypeid" int4 NOT NULL, "rowstatusid" int2 DEFAULT 0 NOT NULL); INSERT INTO "useraccount" values (1, 'cprice', 2, 0); INSERT INTO "useraccount" values (2, 'cprice2', 1, 0); INSERT INTO "useraccount" values (3, 'cprice3', 1, 1); 2.. Create table 2 and populate it DROP TABLE "usertype"; CREATE TABLE "usertype" ( "id" int4 NOT NULL, "description" character varying(255) NOT NULL, "rowstatusid" int2 NOT NULL); INSERT INTO "usertype" values (1, 'Standard user', 0); INSERT INTO "usertype" values (2, 'Manager', 0); 3.. Create view : drop view v_usertype; create view v_usertype as select usertype.description as usertypedescription, useraccount.login as login from usertype, useraccount where usertype.id = useraccount.usertypeid and useraccount.rowstatusid = 0; 4.. View the storage of the view. select * from pg_views where viewname like 'v_usertype'; The output should be : =================================================== viewname |viewowner|definition ----------+---------+---------- v_usertype|postgres |SELECT "description" AS "usertypedescription", "login" FROM "usertype", "useraccount" WHERE ("id" = "usertypeid") AND ("rowstatusid" = '0': :"int4"); (1 row) =================================================== Note the rowstatusid fieldname has now become ambiguous since it is present within both tables. Therefore, when exported with pg_dump and re-loaded, the table 'v_usertype' is created but the rule fails. I would be grateful if the above could be confirmed or I could be pointed in the right direction.
В списке pgsql-general по дате отправления:
Следующее
От: Silvio Emanuel Barbosa de MacedoДата:
Сообщение: Re: query buffer exceed - Solution for common error