Altering Views
Hi,
If we want to "add new column" to the view - the only one way (we have
found) is:
-drop view
-create it again ( with new column )
Now, if some other view depends on the view we want change - it will not
allow us to drop the view - what is fine, of course - but it does not allow
us to change it either (add column) even our change has not any effect on
other views what depend on this one...
So what we are doing is:
1. Take create scripts of all others views what depends on this one
2. Take create scripts of all others views what depends on any of views in
point 1 (recursively)
3. Drop the view (cascaded)
4. Create the view with the new column
5. Run create scripts taken from point 1 and 2...
Lot of hassle - just because of adding one more column to the view...
Is there any better way to alter view without hassle ?
If not - Are there any plans to allow it?
Many Thanks,
Misa
On 2013-02-20 17:25:41 +0100, Misa Simic wrote:
Hi,
If we want to "add new column" to the view - the only one way (we have
found) is:-drop view
-create it again ( with new column )
Since 8.4 you can add new columns to the end of a view definition using
CREATE OR REPLACE VIEW.
http://www.postgresql.org/docs/current/interactive/sql-createview.html :
CREATE OR REPLACE VIEW is similar, but if a view of the same name
already exists, it is replaced. The new query must generate the same
columns that were generated by the existing view query (that is, the
same column names in the same order and with the same data types), but
it may add additional columns to the end of the list. The calculations
giving rise to the output columns may be completely different.
Btw, this is more of a pgsql-general type of question...
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers