Re: [GENERAL] pg_dump query about views
От | jose' soares |
---|---|
Тема | Re: [GENERAL] pg_dump query about views |
Дата | |
Msg-id | 36C846FB.86689822@bo.nettuno.it обсуждение исходный текст |
Список | pgsql-hackers |
"Colin Price (EML)" ha scritto: > > -----Original Message----- > > From: jose' soares [mailto:sferac@bo.nettuno.it] > > Sent: Friday, February 12, 1999 1:10 PM > > To: Colin Price (EML) > > Subject: Re: [GENERAL] pg_dump query about views > > > > > > > > > > "Colin Price (EML)" ha scritto: > > > > > Again, apologies if this is a duplication from the past but > > I can't it in > > > pgsql-questions : > > > > > > ------------------------------- > > > In the reference section, it states there are problems with > > dumping views > > > and rules. A pg_dumpall/pg_dump stores the view as a table > > with a rule. > > > Therefore, when loaded back in, the view is now a table and > > not loaded into > > > pg_view. > > > > > > To change this, do I create a simple script to remove the > > 'CREATE TABLE' and > > > transform the 'CREATE RULE' into a create view statement> > > > --------------------------------- > > > > > > As always, thank you in advance, > > > Colin PRICE. > > > > Tables and views are the same thing for PostgreSQL but views > > have a rule called > > "_RETtablename" > > to fetch rows from tablename instead of view. AFAIK > > pg_dump/pg_dumpall should > > work well in v6.4. > > > > - Jose' - > ========================================================================== > Cheers for your response. I agree, pg_dump/pg_dumpall works fine. > It seems I was looking at this problem from the wrong direction. > > I thought this was a pg_dump problem. > I now believe this to be a view storage issue and was hoping you could > complete the following steps to confirm my findings. It should only take > you 2 minutes to cut and paste the code. > > I would be very grateful for your help on this matter. > Thank you in advance, > Colin PRICE > > ============================================================================ > == > - 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. This is a bug. Report it to hackers. -- - Jose' - And behold, I tell you these things that ye may learn wisdom; that ye may learn that when ye are in the service of your fellow beings ye are only in the service of your God. - Mosiah 2:17 -
В списке pgsql-hackers по дате отправления:
Предыдущее
От: jwieck@debis.com (Jan Wieck)Дата:
Сообщение: Re: [HACKERS] Failures in 'rules' regression test