Thread: alter view, add/drop column
I googled around and found that adding/dropping columns from views is not available. (if not true, I'm all ears).
Given that, what's the best way to do this? I was thinking along the lines of a stored procedure that reads the view's definition, modifies it, drops the view, recreates the view. The main concern here is that the view drop may fail if someone is using it. (is that a valid concern?). The procedure would take as args a verb (add/delete/rename), an object (column to operate on), new name (optional, not used for delete), maybe a table name to know where in the view def to add the new column.
But I'm all ears for other suggestions.
Thanks !
Dave, On Wednesday, 5 September 2012 16:16:32 UTC+2, "Gauthier, Dave" wrote: > I googled around and found that adding/dropping columns from views is not available. (if not true, I'm all ears). > Given that, what's the best way to do this? I was thinking along the lines of a stored procedure that reads the view'sdefinition, modifies it, drops the view, recreates the view. The main concern here is that the view drop may failif > someone is using it. (is that a valid concern?). The procedure would take as args a verb (add/delete/rename), an object(column to operate on), new name (optional, not used for delete), maybe a table name to know where in the view defto add the new column. You can do from pgAdmin by right-clicking on the view and select CREATE SCRIPT uncomment: DROP VIEW <viewname>; and edit the CREATE VIEW <viewname> to drop the column. However if the view is used in other views you have to drop them all and recreate in sequence. Alternatively you can delete the columns from the pg_catalog.pg_attribute table. Make sure however that you know you deletingonly the one's you want to delete and not other tables columns... using the below: delete from pg_attribute where attrelid = regclass 'yourviewname' and attname = 'columnnametodrop' Best to first do couple of selects until you have the selection correct: select attrelid::regclass as whatever, * from pg_attribute where attname = 'columnnametodrop' Johan Nel.