RE: [GENERAL] I can't drop view?! - Mailing list pgsql-general

> -----Original Message-----
> From: Adriaan Joubert [mailto:a.joubert@albourne.com]
> Sent: Friday, March 05, 1999 6:33 AM
> To: Viktor A.
> Cc: Postgres-General
> Subject: Re: [GENERAL] I can't drop view?!
>
>
> >
> > I just DROPped the mentioned VIEWs with the
> > DROP TABLE
> > Command! DROP VIEW doesn't work... on any of them...
> >
> > The problem with the view I can't create, because it once
> had existed
> >  still bugs, though.......
> >
>
> I had the same problem. I spent a day digging through source code and
> the admin tables, and ended up having to dump the data, drop the
> database and redo the whole thing from scratch. There does not seem to
> be any other way....
>
> Adriaan
>
not sure if helpful but .....

A view is created through a CREATE TABLE and CREATE RULE being executed (do
a pg_dump
for the exact code). Maybe you can 'play' with them.

Also, I had/have a problem with views when joining two tables with the same
fieldnames. When a
pg_dump was loaded into postgres, it failed on the view due to 'duplicate
field name'
although they were expressed uniquely (see below).
- Colin

-----------------------

============================================================================
==
- 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.