I can't drop view?!

....
When I tried to drop it:
drop view view_anrufe_gesamt_tn11_1tag

It stated:
ERROR: RewriteGetRuleEventRel: rule "_RETview_anrufe_gesamt_tn11_1tag" not
found

Needless to say, that it is still there.....
How can I get rid of it? Is there a reason for this? Maybe a workaround?
....

Hi again!

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

Bye,
Viktor

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

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

Hi Colin,

I get the same result as you when trying to create the view the same
way you do.

The following looks as if it may work:

create view v_usertype as
select
usertype.description as usertypedescription,
useraccount.login as login
from usertype a, useraccount b
where usertype.id = useraccount.usertypeid
and b.rowstatusid = 0;

as this gives

test=> select * from pg_views where viewname like 'v_usertype';
viewname
|viewowner|definition
----------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
v_usertype|adriaan |SELECT "description" AS "usertypedescription",
"login" FROM "useraccount" "b", "usertype", "useraccount" WHERE ("id" =
"usertypeid") AND ("b"."rowstatusid" = '0'::"int4");
(1 row)

and rowstatusid is now properly qualified with b.

Hope it works for you,

Adriaan