Skip site navigation (1) Skip section navigation (2)

Re: BUG #3597: CREATE OR REPLACE VIEW

From: "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>
To: "Luiz K(dot) Matsumura" <luiz(at)planit(dot)com(dot)br>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #3597: CREATE OR REPLACE VIEW
Date: 2007-09-04 08:22:07
Message-ID: 46DD15AF.5000809@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

Responses

pgsql-bugs by date

Next:From: Heikki LinnakangasDate: 2007-09-04 08:35:19
Subject: Re: BUG #3598: Strange behaviour of character columns in select with views
Previous:From: Luiz K. MatsumuraDate: 2007-09-04 08:07:32
Subject: Re: BUG #3597: CREATE OR REPLACE VIEW

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group