Re: BUG #3597: CREATE OR REPLACE VIEW

Luiz K. Matsumura wrote:
> Heikki Linnakangas wrote:
>> Luiz K. Matsumura wrote:
>>
>>> When we do a command Create or Replace View that change columns of
>>> previous
>>> view we got a error.
>>
>> Right. You can't change the data types of an existing view. You'll have
>> to drop and recreate it.
>>
> But, with the 'replace' command, this isn't implicit ?
> If they found a view, replace the existing view with the new one (on the
> other words, drop and create again?)

Replacing is not exactly the same thing as dropping and recreating it.
If the view has dependencies, you can't drop it without dropping the
dependent objects first, and likewise you can't change its datatypes
because it would affect the dependent objects as well (hence the
limitation on CREATE OR REPLACE VIEW). But you can replace the
definition CREATE OR REPLACE VIEW, even when there's dependencies. For
example:

CREATE VIEW foo AS SELECT 10::integer;
CREATE VIEW bar AS SELECT * FROM foo;

CREATE OR REPLACE VIEW foo AS SELECT 'foobar'::text; -- Fails, can't
change data type of an existing view
DROP VIEW foo; -- Fails because 'bar' depends on foo
CREATE OR REPLACE VIEW foo AS SELECT 20::integer; -- Succeeds.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com