when does CREATE VIEW not create a view?

From: Brook Milligan <brook(at)biology(dot)nmsu(dot)edu>
To: pgsql-hackers(at)hub(dot)org
Subject: when does CREATE VIEW not create a view?
Date: 2000-08-22 18:40:21
Message-ID: 200008221840.MAA06211@biology.nmsu.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

I am trying to create a view and have run across a, to me, bizarre
occurance. One CREATE VIEW statement creates the view fine; changing
the name of the view and repeating the same statement does not. This
has nothing to do with conflicting names as appropriate DROP commands
are issued first.

To be specific, here are the queries and the results:

-- precipitation_xxx_verify view created fine (see below)

drop view precipitation_xxx_verify;
create view precipitation_xxx_verify as
select p.id, p.verified,
w.name,
w.country, w.state, w.county,
p.date, p.precipitation / 2.54 as precipitation, -- 2.54 mm/inch
p.inserted_by, p.inserted_on, p.verified_by, p.verified_on
from precipitation_data p, weather_stations w
where w.id = p.weather_station_id
and verified != true;

-- precipitation_english_verify view is not created as a view (see below)

drop view precipitation_english_verify; -- XXX - fails because a view is not
created (see below)
drop table precipitation_english_verify; -- XXX - why not a view?
create view precipitation_english_verify as
select p.id, p.verified,
w.name,
w.country, w.state, w.county,
p.date, p.precipitation / 2.54 as precipitation, -- 2.54 mm/inch
p.inserted_by, p.inserted_on, p.verified_by, p.verified_on
from precipitation_data p, weather_stations w
where w.id = p.weather_station_id
and verified != true;

\d precipitation_xxx_verify
\d precipitation_english_verify

View "precipitation_xxx_verify"
Attribute | Type | Modifier
---------------+-----------+----------
id | integer |
verified | boolean |
name | text |
country | text |
state | text |
county | text |
date | timestamp |
precipitation | float8 |
inserted_by | name |
inserted_on | timestamp |
verified_by | name |
verified_on | timestamp |
View definition: SELECT p.id, p.verified, w.name, w.country, w.state, w.county, p.date, (p.precipitation / 2.54) AS precipitation, p.inserted_by, p.inserted_on, p.verified_by, p.verified_on FROM precipitation_data p, weather_stations w WHERE ((w.id = p.weather_station_id) AND (p.verified <> 't'::bool));

View "precipitation_english_verify"
Attribute | Type | Modifier
---------------+-----------+----------
id | integer |
verified | boolean |
name | text |
country | text |
state | text |
county | text |
date | timestamp |
precipitation | float8 |
inserted_by | name |
inserted_on | timestamp |
verified_by | name |
verified_on | timestamp |
View definition: Not a view

It seems that the problem is with the word "english" as part of the
view name. Variants of the name that lack it (e.g., replacing xxx
above with eng, englih, etc.) seem to work fine, but variants that
include it (e.g., replacing xxx with english, eenglish, englishh)
suffer as above.

Is there something special involved in handling view names that would
preclude such names?

Any explanations for this behavior are welcome.

Thanks for your help.

Cheers,
Brook

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Brook Milligan 2000-08-22 18:55:29 Re: when does CREATE VIEW not create a view?
Previous Message Tom Lane 2000-08-22 17:52:25 Re: [Solved] SQL Server to PostgreSQL

Browse pgsql-patches by date

  From Date Subject
Next Message Brook Milligan 2000-08-22 18:55:29 Re: when does CREATE VIEW not create a view?
Previous Message Mark Hollomon 2000-08-22 12:43:03 Re: functional index arg matching patch