Thread

Re: [GENERAL] pg_dump query about views

jose' soares <sferac@bo.nettuno.it> — 1999-02-15T16:10:35Z

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