Thread: 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