Thread: pg_dump dumps views as tables???
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.
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. > > >
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).
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
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'.
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 >