Re: [GENERAL] pg_dump query about views

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