> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Friday, February 12, 1999 2:24 PM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: Re: [ADMIN] pg_dump query about views.
>
>
> >
> >
> > 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.
>
> I don't see the point here. pg_views itself is a view, which
> uses a special support function to display the views
> definition from the ON SELECT rule that is defined on the
> table. There is no difference! A view is a class with an
> INSTEAD rule on event SELECT in Postgres. You might not have
> noticed, that after creating the rule, the table appears in
> pg_views.
>
> In fact, the CREATE TABLE, CREATE RULE is nearly what the
> backend internally does when you issue CREATE VIEW. Only that
> you don't see it. Again, in Postgres views are implemented as
> tables with a rewrite rule.
>
> The reason this is splitted in the dump is, that rules on
> other event's than SELECT would affect the reloaded result if
> you dump the data as INSERT statements instead of COPY data.
> pg_dump just don't make any difference between rule events.
>
> The problems with dumping views and rules is, that pg_dump
> uses the mentioned special support function to parse back the
> internal (querytree) representation of the rule action into
> something the parser accepts to generate the same querytree
> again. This backparsing isn't tested very much. So you might
> be able to define views/rules, that pg_dump will fail to
> output the correct statement that could reinstall the rule.
> Up to now, the rules create command isn't stored (I plan
> change this in the future). So the try to parse back the
> querytree is up to now the only chance pg_dump has. As long
> as the views pg_views and pg_rules work and don't produce an
> error, pg_dump will output a reloadable dump and you aren't
> hit so far by that problem.
>
>
> Jan
>
> --
>
> #=============================================================
> =========#
> # It's easier to get forgiveness for being wrong than for
> being right. #
> # Let's break this rule - forgive me.
> #
> #======================================== [EMAIL PROTECTED]
> (Jan Wieck) #
>
>
==========================================================================
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 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.
