Thread: pg_dump dumps views as tables???

pg_dump dumps views as tables???

From
"Gerard Mason"
Date:
I am running postgresql-server-7.0.3-12mdk (Mandrake 8.0). The relevant part
of my build script looks like this:

create view included_works
as
  select uuid, title as lhs
       , trim(to_char(cast(year_of_publication as int), '0000')) as rhs
    from works
   where include=true
;

What pg_dump actually outputs is this:

CREATE TABLE "included_works" (
 "uuid" character(36),
 "lhs" character varying(80),
 "rhs" text
);


I would be more appalled if I was less gobsmacked. Surely I'm doing
something wrong? But I can't find any reference to this in either the Admin
Guide or the Reference Manual (though admittedly I'm looking at the 7.1
ones -- the 7.0 ones didn't have any info on it at all, I think).

Help.




Re: pg_dump dumps views as tables???

From
"Gerard Mason"
Date:
Anybody?


"Gerard Mason" <gerardmason@hotmail.com> wrote in message
news:9gbd3s$2d6$1@news.tht.net...
> I am running postgresql-server-7.0.3-12mdk (Mandrake 8.0). The relevant
part
> of my build script looks like this:
>
> create view included_works
> as
>   select uuid, title as lhs
>        , trim(to_char(cast(year_of_publication as int), '0000')) as rhs
>     from works
>    where include=true
> ;
>
> What pg_dump actually outputs is this:
>
> CREATE TABLE "included_works" (
>  "uuid" character(36),
>  "lhs" character varying(80),
>  "rhs" text
> );
>
>
> I would be more appalled if I was less gobsmacked. Surely I'm doing
> something wrong? But I can't find any reference to this in either the
Admin
> Guide or the Reference Manual (though admittedly I'm looking at the 7.1
> ones -- the 7.0 ones didn't have any info on it at all, I think).
>
> Help.
>
>
>



Re: Re: pg_dump dumps views as tables???

From
Stephan Szabo
Date:
My guess would be that it's being dumped as a table with
an ON SELECT rule (the internal form of the view). IIRC,
7.1's pg_dump is smarter about this.

Check for a CREATE RULE for the table.

On Tue, 19 Jun 2001, Gerard Mason wrote:

> Anybody?
>
>
> "Gerard Mason" <gerardmason@hotmail.com> wrote in message
> news:9gbd3s$2d6$1@news.tht.net...
> > I am running postgresql-server-7.0.3-12mdk (Mandrake 8.0). The relevant
> part
> > of my build script looks like this:
> >
> > create view included_works
> > as
> >   select uuid, title as lhs
> >        , trim(to_char(cast(year_of_publication as int), '0000')) as rhs
> >     from works
> >    where include=true
> > ;
> >
> > What pg_dump actually outputs is this:
> >
> > CREATE TABLE "included_works" (
> >  "uuid" character(36),
> >  "lhs" character varying(80),
> >  "rhs" text
> > );
> >
> >
> > I would be more appalled if I was less gobsmacked. Surely I'm doing
> > something wrong? But I can't find any reference to this in either the
> Admin
> > Guide or the Reference Manual (though admittedly I'm looking at the 7.1
> > ones -- the 7.0 ones didn't have any info on it at all, I think).



Re: Re: pg_dump dumps views as tables???

From
"Ross J. Reedstrom"
Date:
If you look farther down that same dump, you'll see a CREATE RULE
that looks something like:

CREATE RULE AS ON SELECT


CREATE RULE "_RETincluded_works" AS ON SELECT TO "included_works" DO
INSTEAD SELECT "works"."uuid", "works"."title" as "lhs" [...]

In other words, pg_dump, especially before 7.1, exposes the details of
how views are implemented, rather than recreating the SQL you wrote to
create the schema.

Ross


On Tue, Jun 19, 2001 at 09:12:32AM +0100, Gerard Mason wrote:
> Anybody?
>
>
> "Gerard Mason" <gerardmason@hotmail.com> wrote in message
> news:9gbd3s$2d6$1@news.tht.net...
> > I am running postgresql-server-7.0.3-12mdk (Mandrake 8.0). The relevant
> part
> > of my build script looks like this:
> >
> > create view included_works
> > as
> >   select uuid, title as lhs
> >        , trim(to_char(cast(year_of_publication as int), '0000')) as rhs
> >     from works
> >    where include=true
> > ;
> >
> > What pg_dump actually outputs is this:
> >
> > CREATE TABLE "included_works" (
> >  "uuid" character(36),
> >  "lhs" character varying(80),
> >  "rhs" text
> > );
> >
> >
> > I would be more appalled if I was less gobsmacked. Surely I'm doing
> > something wrong? But I can't find any reference to this in either the
> Admin
> > Guide or the Reference Manual (though admittedly I'm looking at the 7.1
> > ones -- the 7.0 ones didn't have any info on it at all, I think).
> >
> > Help.
> >
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Re: pg_dump dumps views as tables???

From
Gerard Mason
Date:
My warmest thanks to Ross J. Reedstrom and Andrew W. Schmeder. I had
overestimated the ability of my reader (Mozilla) to thread replies to my
question, and so missed yours until I did a search for 'pg_dump'.


Re: pg_dump dumps views as tables???

From
Gerard Mason
Date:
Thank you both *very* much!


Ross J. Reedstrom wrote:

> If you look farther down that same dump, you'll see a CREATE RULE
> that looks something like:
>
> CREATE RULE AS ON SELECT
>
>
> CREATE RULE "_RETincluded_works" AS ON SELECT TO "included_works" DO
> INSTEAD SELECT "works"."uuid", "works"."title" as "lhs" [...]
>
> In other words, pg_dump, especially before 7.1, exposes the details of
> how views are implemented, rather than recreating the SQL you wrote to
> create the schema.
>
> Ross
>
>
> On Tue, Jun 19, 2001 at 09:12:32AM +0100, Gerard Mason wrote:
>
>>Anybody?
>>
>>
>>"Gerard Mason" <gerardmason@hotmail.com> wrote in message
>>news:9gbd3s$2d6$1@news.tht.net...
>>
>>>I am running postgresql-server-7.0.3-12mdk (Mandrake 8.0). The relevant
>>>
>>part
>>
>>>of my build script looks like this:
>>>
>>>create view included_works
>>>as
>>>  select uuid, title as lhs
>>>       , trim(to_char(cast(year_of_publication as int), '0000')) as rhs
>>>    from works
>>>   where include=true
>>>;
>>>
>>>What pg_dump actually outputs is this:
>>>
>>>CREATE TABLE "included_works" (
>>> "uuid" character(36),
>>> "lhs" character varying(80),
>>> "rhs" text
>>>);
>>>
>>>
>>>I would be more appalled if I was less gobsmacked. Surely I'm doing
>>>something wrong? But I can't find any reference to this in either the
>>>
>>Admin
>>
>>>Guide or the Reference Manual (though admittedly I'm looking at the 7.1
>>>ones -- the 7.0 ones didn't have any info on it at all, I think).
>>>
>>>Help.
>>>
>>>
>>>
>>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 5: Have you checked our extensive FAQ?
>>
>>http://www.postgresql.org/users-lounge/docs/faq.html
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>